-- ============================================================================
-- DELETE ALL DATA FOR USERS WHERE EMAIL IS NOT 'admin@example.com'
-- ============================================================================
-- WARNING: This script will permanently delete data. Make a backup first!
-- Run: pg_dump -U taf -d taf > backup_before_deletion_$(date +%Y%m%d_%H%M%S).sql
-- ============================================================================

BEGIN;

-- First, identify the admin user_id to preserve
DO $$
DECLARE
    admin_user_id UUID;
BEGIN
    -- Find the admin user ID
    SELECT u.user_id INTO admin_user_id
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
    LIMIT 1;

    IF admin_user_id IS NULL THEN
        RAISE NOTICE 'WARNING: No user found with email admin@example.com';
    ELSE
        RAISE NOTICE 'Admin user_id to preserve: %', admin_user_id;
    END IF;
END $$;

-- ============================================================================
-- STEP 1: Delete from tables WITHOUT CASCADE that reference users
-- (Must be done before deleting users)
-- ============================================================================

-- Delete time_clocks for non-admin users
DELETE FROM time_clocks
WHERE user_id NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- Delete time_clock_aggregates for non-admin users
DELETE FROM time_clock_aggregates
WHERE user_id NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- Delete case_instructions created by non-admin users
DELETE FROM case_instructions
WHERE created_by NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- Delete clients linked to non-admin users
DELETE FROM clients
WHERE user_id NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- Delete customers linked to non-admin users
DELETE FROM customers
WHERE user_id NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- Delete inventory_txn for non-admin users
DELETE FROM inventory_txn
WHERE user_id NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- Delete invitations from non-admin inviters
DELETE FROM invitations
WHERE inviter_user_id NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- Delete notification_queue for non-admin users
DELETE FROM notification_queue
WHERE user_id NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- Delete rfq_headers tender box opened by non-admin
DELETE FROM rfq_headers
WHERE tender_box_opened_by NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- Delete rfq_templates created by non-admin users
DELETE FROM rfq_templates
WHERE created_by NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- Delete vendor_performance_metrics reviewed by non-admin
DELETE FROM vendor_performance_metrics
WHERE reviewer_id NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- ============================================================================
-- STEP 2: Set NULL for tables with ON DELETE SET NULL
-- (These will be set to NULL automatically, but we can do it explicitly)
-- ============================================================================

-- These tables have ON DELETE SET NULL, so they'll be handled automatically:
-- - import_export_configs.created_by
-- - leave_requests.user_id
-- - rfq_invites.vendor_user_id
-- - rfq_scoring_notes.created_by, updated_by
-- - rfq_shortlists.created_by, updated_by

-- ============================================================================
-- STEP 3: Delete users (CASCADE will handle related tables)
-- ============================================================================

-- Delete from users table (this will CASCADE to many related tables)
-- Tables with ON DELETE CASCADE:
--   - user_emails
--   - user_faces
--   - user_group_memberships
--   - users_profiles
--   - user_identities
--   - user_mfa_factors
--   - user_passwords
--   - user_phones
--   - user_recovery_codes
--   - user_branch_memberships
--   - user_column_permissions
--   - user_module_permissions
--   - user_org_memberships
--   - user_table_permissions
--   - sessions
--   - one_time_tokens
--   - salaries
--   - payslips
--   - leave_balances
--   - time_entries
--   - inventory_receive
--   - interviewers
--   - cases (user_id and lead_rep_id)
--   - rfq_approval_levels

DELETE FROM users
WHERE user_id NOT IN (
    SELECT u.user_id 
    FROM users u
    INNER JOIN user_emails ue ON u.user_id = ue.user_id
    WHERE ue.email = 'admin@example.com'
);

-- ============================================================================
-- STEP 4: Report what was kept
-- ============================================================================

DO $$
DECLARE
    remaining_users INTEGER;
    remaining_emails INTEGER;
BEGIN
    SELECT COUNT(*) INTO remaining_users FROM users;
    SELECT COUNT(*) INTO remaining_emails FROM user_emails;
    
    RAISE NOTICE '============================================';
    RAISE NOTICE 'Deletion complete!';
    RAISE NOTICE 'Remaining users: %', remaining_users;
    RAISE NOTICE 'Remaining user_emails: %', remaining_emails;
    RAISE NOTICE '============================================';
END $$;

-- Uncomment the next line to commit the changes
COMMIT;

-- Keep this line if you want to review first
--ROLLBACK;

-- ============================================================================
-- INSTRUCTIONS:
-- 1. Review this script carefully
-- 2. Make a backup: pg_dump -U taf -d taf > backup.sql
-- 3. Run this script to test (it will ROLLBACK by default)
-- 4. If results look good, change ROLLBACK to COMMIT
-- 5. Run again to make changes permanent
-- ============================================================================
