da06dae89d
- Remove entire utils/db_operations.rs file and all *Operations patterns - Comment out imports to dead operations - This breaks compilation temporarily but eliminates the maintenance nightmare - Next: rewrite db:: modules to use direct SQL instead of operations - Goal: Clean Handler → Service → Database pattern only
334 lines
10 KiB
PL/PgSQL
334 lines
10 KiB
PL/PgSQL
-- 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; |