-- Cleanup Orphaned Test Schemas
-- This script removes all test schemas left behind from previous test runs

-- First, show what we're about to delete
SELECT 'Found orphaned test schemas:' as info;

SELECT schema_name, 
       pg_size_pretty(sum(pg_total_relation_size(quote_ident(schema_name)||'.'||quote_ident(tablename)))::bigint) as size
FROM information_schema.schemata
JOIN pg_tables ON schemaname = schema_name
WHERE schema_name LIKE 'test_schema_%'
GROUP BY schema_name
ORDER BY schema_name;

-- Generate DROP commands for all test schemas
SELECT 'Dropping schema: ' || schema_name as action,
       'DROP SCHEMA IF EXISTS ' || quote_ident(schema_name) || ' CASCADE;' as command
FROM information_schema.schemata
WHERE schema_name LIKE 'test_schema_%'
ORDER BY schema_name;

-- Execute the drops (uncomment to run)
DO $$
DECLARE
    schema_record RECORD;
    drop_count INTEGER := 0;
BEGIN
    FOR schema_record IN 
        SELECT schema_name 
        FROM information_schema.schemata 
        WHERE schema_name LIKE 'test_schema_%'
    LOOP
        EXECUTE format('DROP SCHEMA IF EXISTS %I CASCADE', schema_record.schema_name);
        drop_count := drop_count + 1;
        RAISE NOTICE 'Dropped schema: %', schema_record.schema_name;
    END LOOP;
    
    RAISE NOTICE 'Total schemas dropped: %', drop_count;
END $$;

-- Verify cleanup
SELECT COUNT(*) as remaining_test_schemas
FROM information_schema.schemata
WHERE schema_name LIKE 'test_schema_%';

SELECT 'Cleanup completed!' as status;