-- =====================================================
-- Live Database Migration Script
-- Date: 2025-10-05
-- Purpose: Migrate users, cases, and related data to new system
-- =====================================================
-- 
-- STRATEGY:
-- PRESERVE: users, clients, cases, time_entries, expenses, invoices, payments, case_instructions
-- DROP & RECREATE: documents, document_metadata, modules, and other non-critical tables
-- 
-- CRITICAL: READ BEFORE RUNNING
-- 1. BACKUP your current database first!
--    pg_dump -U postgres -d your_db_name > backup_$(date +%Y%m%d_%H%M%S).sql
-- 2. Test this script on a copy of your database first
-- 3. Run during low-traffic period
-- 4. Have rollback plan ready
-- 
-- =====================================================

BEGIN;

-- =====================================================
-- STEP 0: Ensure all tables exist (create missing tables)
-- =====================================================
DO $$
BEGIN
    RAISE NOTICE 'Step 0: Creating any missing tables from schema...';
    RAISE NOTICE 'This allows migration to work on older database versions';
END $$;

-- This will be run OUTSIDE transaction to allow \i command
COMMIT;

-- Import full schema (will create tables that don't exist, skip existing ones)
\i TAFDB.pgsql

-- Now start the migration transaction
BEGIN;

DO $$
BEGIN
    RAISE NOTICE 'Step 0 complete - all tables now exist';
    RAISE NOTICE 'Proceeding with data migration...';
END $$;

-- =====================================================
-- STEP 1: Create backup tables (ONLY for preserved tables)
-- =====================================================
DO $$
BEGIN
    RAISE NOTICE 'Creating backup tables for critical data...';
END $$;

-- Backup core tables
CREATE TABLE IF NOT EXISTS _backup_users AS SELECT * FROM users;
CREATE TABLE IF NOT EXISTS _backup_clients AS SELECT * FROM clients;
CREATE TABLE IF NOT EXISTS _backup_cases AS SELECT * FROM cases;
CREATE TABLE IF NOT EXISTS _backup_time_entries AS SELECT * FROM time_entries;
CREATE TABLE IF NOT EXISTS _backup_expenses AS SELECT * FROM expenses;
CREATE TABLE IF NOT EXISTS _backup_invoices AS SELECT * FROM invoices;
CREATE TABLE IF NOT EXISTS _backup_payments AS SELECT * FROM payments;
CREATE TABLE IF NOT EXISTS _backup_case_instructions AS SELECT * FROM case_instructions;

-- Backup all user_* tables (authentication, permissions, settings)
CREATE TABLE IF NOT EXISTS _backup_user_emails AS SELECT * FROM user_emails;
CREATE TABLE IF NOT EXISTS _backup_user_phones AS SELECT * FROM user_phones;
CREATE TABLE IF NOT EXISTS _backup_user_passwords AS SELECT * FROM user_passwords;
CREATE TABLE IF NOT EXISTS _backup_user_mfa_factors AS SELECT * FROM user_mfa_factors;
CREATE TABLE IF NOT EXISTS _backup_user_mfa_totp AS SELECT * FROM user_mfa_totp;
CREATE TABLE IF NOT EXISTS _backup_user_mfa_webauthn AS SELECT * FROM user_mfa_webauthn;
CREATE TABLE IF NOT EXISTS _backup_user_recovery_codes AS SELECT * FROM user_recovery_codes;
CREATE TABLE IF NOT EXISTS _backup_user_identities AS SELECT * FROM user_identities;
CREATE TABLE IF NOT EXISTS _backup_user_group_memberships AS SELECT * FROM user_group_memberships;
CREATE TABLE IF NOT EXISTS _backup_user_module_permissions AS SELECT * FROM user_module_permissions;
CREATE TABLE IF NOT EXISTS _backup_user_table_permissions AS SELECT * FROM user_table_permissions;
CREATE TABLE IF NOT EXISTS _backup_user_column_permissions AS SELECT * FROM user_column_permissions;
CREATE TABLE IF NOT EXISTS _backup_user_org_memberships AS SELECT * FROM user_org_memberships;
CREATE TABLE IF NOT EXISTS _backup_user_branch_memberships AS SELECT * FROM user_branch_memberships;
CREATE TABLE IF NOT EXISTS _backup_user_2fa AS SELECT * FROM user_2fa;
CREATE TABLE IF NOT EXISTS _backup_user_passkeys AS SELECT * FROM user_passkeys;
CREATE TABLE IF NOT EXISTS _backup_user_tokens AS SELECT * FROM user_tokens;
CREATE TABLE IF NOT EXISTS _backup_user_faces AS SELECT * FROM user_faces;
CREATE TABLE IF NOT EXISTS _backup_user_settings AS SELECT * FROM user_settings;
CREATE TABLE IF NOT EXISTS _backup_user_board_settings AS SELECT * FROM user_board_settings;

DO $$
DECLARE
    user_count INTEGER;
    case_count INTEGER;
    user_table_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO user_count FROM _backup_users;
    SELECT COUNT(*) INTO case_count FROM _backup_cases;
    SELECT COUNT(*) INTO user_table_count FROM _backup_user_emails;
    RAISE NOTICE 'Backup complete - Users: %, Cases: %, User emails: %', user_count, case_count, user_table_count;
    RAISE NOTICE 'All 28 tables backed up successfully';
END $$;

-- =====================================================
-- STEP 2: Drop and recreate non-critical tables
-- =====================================================
DO $$
BEGIN
    RAISE NOTICE 'Dropping and recreating documents, modules, and other non-critical tables...';
END $$;

-- Drop documents and related tables (no data to preserve)

-- Drop modules table (will be recreated from sql/modules.sql)

DO $$
BEGIN
    RAISE NOTICE 'Non-critical tables dropped successfully';
END $$;

-- =====================================================
-- STEP 3: Ensure UUID v7 function exists
-- =====================================================
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'uuid_generate_v7') THEN
        CREATE OR REPLACE FUNCTION uuid_generate_v7() RETURNS uuid AS $func$
        DECLARE
            unix_ts_ms bytea;
            uuid_bytes bytea;
        BEGIN
            unix_ts_ms = substring(int8send(floor(extract(epoch from clock_timestamp()) * 1000)::bigint) from 3);
            uuid_bytes = unix_ts_ms || gen_random_bytes(10);
            RETURN encode(
                set_byte(
                    set_byte(uuid_bytes, 6, (get_byte(uuid_bytes, 6) & 15) | 112),
                    8, (get_byte(uuid_bytes, 8) & 63) | 128
                ),
                'hex'
            )::uuid;
        END;
        $func$ LANGUAGE plpgsql;
        RAISE NOTICE 'Created uuid_generate_v7 function';
    END IF;
END $$;

-- =====================================================
-- STEP 4: Recreate documents and modules tables with new structure
-- =====================================================
DO $$
BEGIN
    RAISE NOTICE 'Recreating documents and modules tables...';
END $$;

-- Recreate documents table with folder support
CREATE TABLE documents (
    document_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    title VARCHAR(255),
    description TEXT,
    entity_type VARCHAR(50),
    entity_id UUID,
    parent_document_id UUID REFERENCES documents(document_id) ON DELETE SET NULL,
    uploaded_by UUID REFERENCES users(user_id),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    file_path TEXT,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Recreate document_metadata table
CREATE TABLE document_metadata (
    metadata_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    document_id UUID REFERENCES documents(document_id) ON DELETE CASCADE,
    metadata_key VARCHAR(255) NOT NULL,
    metadata_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(document_id, metadata_key)
);

-- Recreate modules table
CREATE TABLE modules (
    module_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    name VARCHAR(255) UNIQUE NOT NULL,
    config JSONB NOT NULL,
    org_id UUID,
    branch_id UUID,
    icon VARCHAR(100),
    aria_label VARCHAR(255),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DO $$
BEGIN
    RAISE NOTICE 'Tables recreated with new structure';
END $$;

-- =====================================================
-- STEP 5: Create indexes for performance
-- =====================================================
DO $$
BEGIN
    RAISE NOTICE 'Creating indexes...';
END $$;

-- Documents indexes
CREATE INDEX idx_documents_parent_id ON documents(parent_document_id);
CREATE INDEX idx_documents_entity ON documents(entity_type, entity_id);
CREATE INDEX idx_documents_uploaded_by ON documents(uploaded_by);
CREATE INDEX idx_document_metadata_document_id ON document_metadata(document_id);
CREATE INDEX idx_document_metadata_key ON document_metadata(metadata_key);

-- Case-related indexes
CREATE INDEX IF NOT EXISTS idx_cases_client_id ON cases(client_id);
CREATE INDEX IF NOT EXISTS idx_cases_status ON cases(status);
CREATE INDEX IF NOT EXISTS idx_time_entries_case ON time_entries(reference_id) WHERE reference_table = 'cases';
CREATE INDEX IF NOT EXISTS idx_expenses_case ON expenses(reference_id) WHERE reference_table = 'cases';
CREATE INDEX IF NOT EXISTS idx_invoices_case ON invoices(reference_id) WHERE reference_table = 'cases';
CREATE INDEX IF NOT EXISTS idx_payments_case ON payments(reference_id) WHERE reference_table = 'cases';

-- Module index
CREATE INDEX idx_modules_name ON modules(name);

DO $$
BEGIN
    RAISE NOTICE 'Indexes created successfully';
END $$;

-- =====================================================
-- STEP 6: Validate data integrity
-- =====================================================
DO $$
DECLARE
    user_count INTEGER;
    case_count INTEGER;
    backup_user_count INTEGER;
    backup_case_count INTEGER;
    backup_user_emails_count INTEGER;
    backup_user_passwords_count INTEGER;
    backup_tables_count INTEGER;
BEGIN
    RAISE NOTICE 'Validating data integrity...';
    
    SELECT COUNT(*) INTO backup_user_count FROM _backup_users;
    SELECT COUNT(*) INTO backup_case_count FROM _backup_cases;
    SELECT COUNT(*) INTO backup_user_emails_count FROM _backup_user_emails;
    SELECT COUNT(*) INTO backup_user_passwords_count FROM _backup_user_passwords;
    
    -- Count backup tables
    SELECT COUNT(*) INTO backup_tables_count 
    FROM pg_tables 
    WHERE tablename LIKE '_backup_%';
    
    RAISE NOTICE 'Backup verification - Users: %, Cases: %, User emails: %, Passwords: %', 
                 backup_user_count, backup_case_count, backup_user_emails_count, backup_user_passwords_count;
    RAISE NOTICE 'Total backup tables created: %', backup_tables_count;
    
    IF backup_user_count = 0 THEN
        RAISE EXCEPTION 'CRITICAL: No users found in backup table!';
    END IF;
    
    IF backup_user_passwords_count = 0 THEN
        RAISE EXCEPTION 'CRITICAL: No user passwords found in backup table!';
    END IF;
    
    IF backup_tables_count < 28 THEN
        RAISE WARNING 'Expected 28 backup tables, found %', backup_tables_count;
    END IF;
    
    IF backup_case_count = 0 THEN
        RAISE WARNING 'No cases found in backup - this may be expected for new installations';
    END IF;
END $$;

-- =====================================================
-- STEP 7: Populate modules from sql/modules.sql
-- =====================================================
DO $$
BEGIN
    RAISE NOTICE '========================================';
    RAISE NOTICE 'IMPORTANT: After running this migration,';
    RAISE NOTICE 'you MUST run: psql -d taf_db -f sql/modules.sql';
    RAISE NOTICE 'to populate the modules table.';
    RAISE NOTICE '========================================';
END $$;

-- Note: The modules table is now empty and ready to be populated
-- Run this after the migration completes:
-- psql -U postgres -d taf_db -f sql/modules.sql

-- =====================================================
-- STEP 8: Create migration log
-- =====================================================
CREATE TABLE IF NOT EXISTS migration_log (
    migration_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    migration_name VARCHAR(255) NOT NULL,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'success',
    notes TEXT
);

INSERT INTO migration_log (migration_name, status, notes)
VALUES (
    '20251005_live_db_migration',
    'success',
    'Migrated users, cases, clients, and all related data to new system'
);

-- =====================================================
-- STEP 9: Grant necessary permissions
-- =====================================================
DO $$
BEGIN
    RAISE NOTICE 'Granting permissions...';
END $$;

-- Grant permissions on new tables/columns (adjust as needed for your roles)
-- GRANT ALL ON ALL TABLES IN SCHEMA public TO your_app_user;
-- GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO your_app_user;

-- =====================================================
-- COMMIT TRANSACTION
-- =====================================================
DO $$
BEGIN
    RAISE NOTICE '========================================';
    RAISE NOTICE 'Migration completed successfully!';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'Next steps:';
    RAISE NOTICE '1. Run: psql -U postgres -d taf_db -f sql/modules.sql';
    RAISE NOTICE '2. Hard refresh browser (Ctrl+Shift+R)';
    RAISE NOTICE '3. Verify user login works';
    RAISE NOTICE '4. Test Case Management module';
    RAISE NOTICE '5. Test document upload and folder functionality';
    RAISE NOTICE '6. If issues arise, restore from backup tables:';
    RAISE NOTICE '   DROP TABLE users CASCADE;';
    RAISE NOTICE '   ALTER TABLE _backup_users RENAME TO users;';
    RAISE NOTICE '7. After 7 days of confirmed success, drop backup tables:';
    RAISE NOTICE '   DROP TABLE _backup_users, _backup_clients, _backup_cases,';
    RAISE NOTICE '            _backup_time_entries, _backup_expenses, _backup_invoices,';
    RAISE NOTICE '            _backup_payments, _backup_case_instructions CASCADE;';
    RAISE NOTICE '========================================';
END $$;

COMMIT;

-- =====================================================
-- ROLLBACK PROCEDURE (Run separately if needed)
-- =====================================================
-- If you need to rollback, run these commands:
-- 
-- BEGIN;
-- 
-- -- Drop current tables
-- DROP TABLE IF EXISTS users CASCADE;
-- DROP TABLE IF EXISTS clients CASCADE;
-- DROP TABLE IF EXISTS cases CASCADE;
-- DROP TABLE IF EXISTS time_entries CASCADE;
-- DROP TABLE IF EXISTS expenses CASCADE;
-- DROP TABLE IF EXISTS invoices CASCADE;
-- DROP TABLE IF EXISTS payments CASCADE;
-- DROP TABLE IF EXISTS case_instructions CASCADE;
-- 
-- -- Restore from backup
-- ALTER TABLE _backup_users RENAME TO users;
-- ALTER TABLE _backup_clients RENAME TO clients;
-- ALTER TABLE _backup_cases RENAME TO cases;
-- ALTER TABLE _backup_time_entries RENAME TO time_entries;
-- ALTER TABLE _backup_expenses RENAME TO expenses;
-- ALTER TABLE _backup_invoices RENAME TO invoices;
-- ALTER TABLE _backup_payments RENAME TO payments;
-- ALTER TABLE _backup_case_instructions RENAME TO case_instructions;
-- 
-- -- Recreate foreign keys and constraints as needed
-- -- ALTER TABLE cases ADD CONSTRAINT fk_cases_client FOREIGN KEY (client_id) REFERENCES clients(client_id);
-- -- etc.
-- 
-- COMMIT;
