-- Fix Timezone Double Conversion
-- File: fix_timezone_double_conversion.sql
--
-- PROBLEM: The migration script converted EST times to UTC, but the original times
-- were already in EST (not UTC as assumed). This resulted in times being converted
-- backwards, making events appear 4-5 hours earlier than they should be.
--
-- SOLUTION: Restore original times from backup tables. These original times were
-- already in the correct EST format that the V1 API expects.
--
-- VALIDATION RESULTS SHOWING DOUBLE CONVERSION:
-- - Original: 2025-06-01 15:00:00 (3 PM EST - correct)
-- - Current:  2025-06-01 11:00:00 (11 AM UTC → 7 AM EDT display - wrong!)
-- - Offset:   -4.0 hours (confirms backwards conversion)

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Start transaction for atomic restoration
BEGIN;

-- ================================
-- VALIDATION BEFORE RESTORATION
-- ================================

DO $$
DECLARE
    backup_count INTEGER;
    current_sample RECORD;
BEGIN
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TIMEZONE DOUBLE CONVERSION FIX';
    RAISE NOTICE 'Started at: %', NOW();
    RAISE NOTICE '========================================';
    
    -- Check backup tables exist
    SELECT COUNT(*) INTO backup_count 
    FROM information_schema.tables 
    WHERE table_name LIKE '%timezone_backup';
    
    RAISE NOTICE 'Found % backup tables', backup_count;
    
    IF backup_count < 8 THEN
        RAISE EXCEPTION 'Insufficient backup tables found (%). Cannot proceed without backups.', backup_count;
    END IF;
    
    -- Show current problematic times
    RAISE NOTICE '';
    RAISE NOTICE 'CURRENT PROBLEMATIC TIMES (Before Fix):';
    FOR current_sample IN
        SELECT 
            e.title,
            e.start_time as current_utc,
            e.start_time AT TIME ZONE 'America/New_York' as current_display,
            eb.start_time as original_est
        FROM events e
        JOIN events_timezone_backup eb ON e.id = eb.id
        WHERE e.start_time IS NOT NULL
        ORDER BY e.start_time
        LIMIT 3
    LOOP
        RAISE NOTICE 'Event: %', current_sample.title;
        RAISE NOTICE '  Current UTC: %', current_sample.current_utc;
        RAISE NOTICE '  Current Display: %', current_sample.current_display;
        RAISE NOTICE '  Original EST: %', current_sample.original_est;
        RAISE NOTICE '';
    END LOOP;
END $$;

-- ================================
-- RESTORE ORIGINAL TIMES
-- ================================

RAISE NOTICE 'RESTORING ORIGINAL TIMES FROM BACKUPS...';
RAISE NOTICE '';

-- Restore events table
UPDATE events 
SET 
    start_time = eb.start_time,
    end_time = eb.end_time,
    created_at = eb.created_at,
    updated_at = eb.updated_at
FROM events_timezone_backup eb
WHERE events.id = eb.id;

-- Get count of restored events
DO $$
DECLARE
    events_restored INTEGER;
BEGIN
    SELECT COUNT(*) INTO events_restored 
    FROM events e
    JOIN events_timezone_backup eb ON e.id = eb.id
    WHERE e.start_time IS NOT NULL;
    
    RAISE NOTICE 'Events restored: %', events_restored;
END $$;

-- Restore pending_events table
UPDATE pending_events 
SET 
    start_time = peb.start_time,
    end_time = peb.end_time,
    submitted_at = peb.submitted_at,
    created_at = peb.created_at,
    updated_at = peb.updated_at
FROM pending_events_timezone_backup peb
WHERE pending_events.id = peb.id;

-- Get count of restored pending events
DO $$
DECLARE
    pending_restored INTEGER;
BEGIN
    SELECT COUNT(*) INTO pending_restored 
    FROM pending_events pe
    JOIN pending_events_timezone_backup peb ON pe.id = peb.id
    WHERE pe.start_time IS NOT NULL;
    
    RAISE NOTICE 'Pending events restored: %', pending_restored;
END $$;

-- Restore bulletins table
UPDATE bulletins 
SET 
    created_at = bb.created_at,
    updated_at = bb.updated_at
FROM bulletins_timezone_backup bb
WHERE bulletins.id = bb.id;

-- Restore users table
UPDATE users 
SET 
    created_at = ub.created_at,
    updated_at = ub.updated_at
FROM users_timezone_backup ub
WHERE users.id = ub.id;

-- Restore church_config table
UPDATE church_config 
SET 
    created_at = ccb.created_at,
    updated_at = ccb.updated_at
FROM church_config_timezone_backup ccb
WHERE church_config.id = ccb.id;

-- Restore schedules table (if exists)
DO $$
BEGIN
    IF EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'schedules') THEN
        UPDATE schedules 
        SET 
            created_at = sb.created_at,
            updated_at = sb.updated_at
        FROM schedules_timezone_backup sb
        WHERE schedules.id = sb.id;
    END IF;
END $$;

-- Restore bible_verses table (if exists)
DO $$
BEGIN
    IF EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'bible_verses') THEN
        UPDATE bible_verses 
        SET 
            created_at = bvb.created_at,
            updated_at = bvb.updated_at
        FROM bible_verses_timezone_backup bvb
        WHERE bible_verses.id = bvb.id;
    END IF;
END $$;

-- Restore app_versions table (if exists)
DO $$
BEGIN
    IF EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'app_versions') THEN
        UPDATE app_versions 
        SET 
            created_at = avb.created_at,
            updated_at = avb.updated_at
        FROM app_versions_timezone_backup avb
        WHERE app_versions.id = avb.id;
    END IF;
END $$;

-- ================================
-- POST-RESTORATION VALIDATION
-- ================================

DO $$
DECLARE
    restored_sample RECORD;
    total_events INTEGER;
    total_pending INTEGER;
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE 'POST-RESTORATION VALIDATION:';
    RAISE NOTICE '';
    
    -- Show restored times
    FOR restored_sample IN
        SELECT 
            title,
            start_time as restored_est,
            start_time AT TIME ZONE 'America/New_York' as display_time
        FROM events 
        WHERE start_time IS NOT NULL
        ORDER BY start_time
        LIMIT 3
    LOOP
        RAISE NOTICE 'Event: %', restored_sample.title;
        RAISE NOTICE '  Restored EST: %', restored_sample.restored_est;
        RAISE NOTICE '  Display Time: %', restored_sample.display_time;
        RAISE NOTICE '';
    END LOOP;
    
    -- Get totals
    SELECT COUNT(*) INTO total_events FROM events WHERE start_time IS NOT NULL;
    SELECT COUNT(*) INTO total_pending FROM pending_events WHERE start_time IS NOT NULL;
    
    RAISE NOTICE 'RESTORATION SUMMARY:';
    RAISE NOTICE '- Events with times: %', total_events;
    RAISE NOTICE '- Pending with times: %', total_pending;
    RAISE NOTICE '';
END $$;

-- ================================
-- UPDATE MIGRATION LOG
-- ================================

-- Record the fix in migration log
INSERT INTO migration_log (migration_name, description)
VALUES (
    'fix_timezone_double_conversion',
    'Fixed double timezone conversion by restoring original EST times from backup tables. The original migration incorrectly assumed UTC times when they were already in EST, causing events to display 4-5 hours earlier than intended.'
);

-- ================================
-- FINAL VALIDATION QUERIES
-- ================================

-- Create validation queries for manual verification
CREATE TEMP TABLE post_fix_validation AS
SELECT 1 as query_num, 
       'Verify event times now display correctly' as description,
       $val1$
SELECT 
    title,
    start_time as est_time,
    start_time AT TIME ZONE 'America/New_York' as ny_display,
    EXTRACT(hour FROM start_time) as hour_est
FROM events 
WHERE start_time IS NOT NULL
ORDER BY start_time
LIMIT 10;
$val1$ as query_sql

UNION ALL

SELECT 2 as query_num,
       'Check that event hours are reasonable (6 AM - 11 PM)' as description,
       $val2$
SELECT 
    title,
    start_time,
    EXTRACT(hour FROM start_time) as event_hour,
    CASE 
        WHEN EXTRACT(hour FROM start_time) BETWEEN 6 AND 23 THEN 'REASONABLE'
        ELSE 'UNUSUAL'
    END as time_assessment
FROM events 
WHERE start_time IS NOT NULL
ORDER BY start_time;
$val2$ as query_sql

UNION ALL

SELECT 3 as query_num,
       'Verify V1 API will return correct times' as description,
       $val3$
-- This simulates what the V1 API timezone conversion will produce
SELECT 
    title,
    start_time as stored_est,
    start_time AT TIME ZONE 'America/New_York' as v1_display_equivalent
FROM events 
WHERE start_time IS NOT NULL
ORDER BY start_time
LIMIT 5;
$val3$ as query_sql;

-- Display validation queries
DO $$
DECLARE
    val_record RECORD;
BEGIN
    RAISE NOTICE '========================================';
    RAISE NOTICE 'VALIDATION QUERIES - RUN THESE TO VERIFY:';
    RAISE NOTICE '========================================';
    
    FOR val_record IN SELECT * FROM post_fix_validation ORDER BY query_num LOOP
        RAISE NOTICE 'Query %: %', val_record.query_num, val_record.description;
        RAISE NOTICE '%', val_record.query_sql;
        RAISE NOTICE '----------------------------------------';
    END LOOP;
END $$;

-- ================================
-- COMPLETION MESSAGE
-- ================================

DO $$
BEGIN
    RAISE NOTICE '========================================';
    RAISE NOTICE 'TIMEZONE DOUBLE CONVERSION FIX COMPLETED';
    RAISE NOTICE 'Completed at: %', NOW();
    RAISE NOTICE '========================================';
    RAISE NOTICE 'WHAT WAS FIXED:';
    RAISE NOTICE '- Restored original EST times from backup tables';
    RAISE NOTICE '- Fixed events showing at midnight/early morning hours';
    RAISE NOTICE '- V1 API will now return correct EST times to frontend';
    RAISE NOTICE '- V2 API logic should be updated to handle EST times properly';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'NEXT STEPS:';
    RAISE NOTICE '1. Run the validation queries above';
    RAISE NOTICE '2. Test the frontend clients to confirm times display correctly';
    RAISE NOTICE '3. Update V2 API to properly convert EST to UTC if needed';
    RAISE NOTICE '4. Consider keeping backup tables until fully verified';
    RAISE NOTICE '========================================';
END $$;

-- Commit the transaction
COMMIT;