-- ============================================================================
-- SCRIPT DE LIMPEZA: Remover referências a user_id > 1
-- ============================================================================
-- Data: 2025-11-17
-- Propósito: Limpar registros órfãos após exclusão de usuários (mantém apenas user_id=1)
-- ============================================================================

BEGIN;

-- Registro de início
DO $$
BEGIN
    RAISE NOTICE '=================================================================';
    RAISE NOTICE 'INICIANDO LIMPEZA DE REGISTROS COM user_id > 1';
    RAISE NOTICE 'Data/Hora: %', NOW();
    RAISE NOTICE '=================================================================';
END $$;

-- ============================================================================
-- 1. USERS (Tabela Principal)
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM users WHERE id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[1/20] users: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 2. USERS_CREDITS
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM users_credits WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[2/20] users_credits: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 3. USERS_DEVICES
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM users_devices WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[3/20] users_devices: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 4. USERS_BILLING
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM users_billing WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[4/20] users_billing: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 5. USERS_SETTINGS
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM users_settings WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[5/20] users_settings: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 6. USERS_CREDIT_TRANSACTIONS
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM users_credit_transactions WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[6/20] users_credit_transactions: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 7. JOBS
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM jobs WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[7/20] jobs: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 8. FRAUD_DETECTION_EVENTS
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM fraud_detection_events WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[8/20] fraud_detection_events: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 9. WEBSOCKETS_EVENTS_QUEUE
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM websockets_events_queue WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[9/20] websockets_events_queue: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 10. USERS_PROFILE
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM users_profile WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[10/20] users_profile: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 11. USERS_EMAIL_VERIFICATIONS
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM users_email_verifications WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[11/20] users_email_verifications: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 12. USERS_PASSWORD_RESETS
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM users_password_resets WHERE user_id > 1;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RAISE NOTICE '[12/20] users_password_resets: % registros removidos', deleted_count;
END $$;

-- ============================================================================
-- 13. DEVICE_LINK_TOKENS (se ainda existir - pode ter sido dropada)
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
    table_exists BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = 'public' AND table_name = 'device_link_tokens'
    ) INTO table_exists;

    IF table_exists THEN
        DELETE FROM device_link_tokens WHERE user_id > 1;
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        RAISE NOTICE '[13/20] device_link_tokens: % registros removidos', deleted_count;
    ELSE
        RAISE NOTICE '[13/20] device_link_tokens: TABELA NÃO EXISTE (já foi dropada)';
    END IF;
END $$;

-- ============================================================================
-- 14. USERS_LOGIN_TOKENS
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
    table_exists BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = 'public' AND table_name = 'users_login_tokens'
    ) INTO table_exists;

    IF table_exists THEN
        DELETE FROM users_login_tokens WHERE user_id > 1;
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        RAISE NOTICE '[14/20] users_login_tokens: % registros removidos', deleted_count;
    ELSE
        RAISE NOTICE '[14/20] users_login_tokens: TABELA NÃO EXISTE';
    END IF;
END $$;

-- ============================================================================
-- 15. USERS_PROFILE_PROGRESS
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
    table_exists BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = 'public' AND table_name = 'users_profile_progress'
    ) INTO table_exists;

    IF table_exists THEN
        DELETE FROM users_profile_progress WHERE user_id > 1;
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        RAISE NOTICE '[15/20] users_profile_progress: % registros removidos', deleted_count;
    ELSE
        RAISE NOTICE '[15/20] users_profile_progress: TABELA NÃO EXISTE';
    END IF;
END $$;

-- ============================================================================
-- 16. USERS_BLOCKED
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
    table_exists BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = 'public' AND table_name = 'users_blocked'
    ) INTO table_exists;

    IF table_exists THEN
        DELETE FROM users_blocked WHERE user_id > 1;
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        RAISE NOTICE '[16/20] users_blocked: % registros removidos', deleted_count;
    ELSE
        RAISE NOTICE '[16/20] users_blocked: TABELA NÃO EXISTE';
    END IF;
END $$;

-- ============================================================================
-- 17. ADMIN_TEAM_USERS
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
    table_exists BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = 'public' AND table_name = 'admin_users'
    ) INTO table_exists;

    IF table_exists THEN
        DELETE FROM admin_users WHERE user_id > 1;
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        RAISE NOTICE '[17/20] admin_users: % registros removidos', deleted_count;
    ELSE
        RAISE NOTICE '[17/20] admin_users: TABELA NÃO EXISTE';
    END IF;
END $$;

-- ============================================================================
-- 18. EMAIL_PROVIDERS (verificar se tem user_id)
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
    column_exists BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'email_providers'
          AND column_name = 'user_id'
    ) INTO column_exists;

    IF column_exists THEN
        DELETE FROM email_providers WHERE user_id > 1;
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        RAISE NOTICE '[18/20] email_providers: % registros removidos', deleted_count;
    ELSE
        RAISE NOTICE '[18/20] email_providers: NÃO TEM COLUNA user_id';
    END IF;
END $$;

-- ============================================================================
-- 19. EMAIL_PROVIDER_USAGE (verificar se tem user_id)
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
    column_exists BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'email_provider_usage'
          AND column_name = 'user_id'
    ) INTO column_exists;

    IF column_exists THEN
        DELETE FROM email_provider_usage WHERE user_id > 1;
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        RAISE NOTICE '[19/20] email_provider_usage: % registros removidos', deleted_count;
    ELSE
        RAISE NOTICE '[19/20] email_provider_usage: NÃO TEM COLUNA user_id';
    END IF;
END $$;

-- ============================================================================
-- 20. EMAIL_SEND_EVENTS (verificar se tem user_id)
-- ============================================================================
DO $$
DECLARE
    deleted_count INTEGER;
    column_exists BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'email_send_events'
          AND column_name = 'user_id'
    ) INTO column_exists;

    IF column_exists THEN
        DELETE FROM email_send_events WHERE user_id > 1;
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        RAISE NOTICE '[20/20] email_send_events: % registros removidos', deleted_count;
    ELSE
        RAISE NOTICE '[20/20] email_send_events: NÃO TEM COLUNA user_id';
    END IF;
END $$;

-- ============================================================================
-- VERIFICAÇÃO DE INTEGRIDADE
-- ============================================================================
DO $$
DECLARE
    orphan_count INTEGER;
BEGIN
    RAISE NOTICE '=================================================================';
    RAISE NOTICE 'VERIFICANDO INTEGRIDADE REFERENCIAL';
    RAISE NOTICE '=================================================================';

    -- Verificar users_devices órfãos
    SELECT COUNT(*) INTO orphan_count
    FROM users_devices ud
    WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = ud.user_id);

    RAISE NOTICE 'users_devices órfãos: %', orphan_count;

    -- Verificar jobs órfãos
    SELECT COUNT(*) INTO orphan_count
    FROM jobs j
    WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = j.user_id);

    RAISE NOTICE 'jobs órfãos: %', orphan_count;

    -- Verificar users_credits órfãos
    SELECT COUNT(*) INTO orphan_count
    FROM users_credits uc
    WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = uc.user_id);

    RAISE NOTICE 'users_credits órfãos: %', orphan_count;
END $$;

-- ============================================================================
-- RELATÓRIO FINAL
-- ============================================================================
DO $$
DECLARE
    total_users INTEGER;
    total_devices_links INTEGER;
    total_jobs INTEGER;
    total_credits_txn INTEGER;
BEGIN
    RAISE NOTICE '=================================================================';
    RAISE NOTICE 'RELATÓRIO FINAL';
    RAISE NOTICE '=================================================================';

    SELECT COUNT(*) INTO total_users FROM users;
    RAISE NOTICE 'Total de usuários restantes: %', total_users;

    SELECT COUNT(*) INTO total_devices_links FROM users_devices;
    RAISE NOTICE 'Total de vinculações device-user restantes: %', total_devices_links;

    SELECT COUNT(*) INTO total_jobs FROM jobs;
    RAISE NOTICE 'Total de jobs restantes: %', total_jobs;

    SELECT COUNT(*) INTO total_credits_txn FROM users_credit_transactions;
    RAISE NOTICE 'Total de transações de crédito restantes: %', total_credits_txn;

    RAISE NOTICE '=================================================================';
    RAISE NOTICE 'LIMPEZA CONCLUÍDA COM SUCESSO!';
    RAISE NOTICE 'Data/Hora: %', NOW();
    RAISE NOTICE '=================================================================';
END $$;

COMMIT;
