-- ============================================================================
-- Migration Template: Standardize TIMESTAMP to TIMESTAMPTZ
-- ============================================================================
--
-- Purpose:
--   This template is used to migrate columns from TIMESTAMP (without timezone)
--   to TIMESTAMPTZ (with timezone) to ensure proper timezone handling across
--   the distributed trading-platform system.
--
-- Why Migrate:
--   - TIMESTAMP loses timezone information (ambiguous with DST)
--   - TIMESTAMPTZ preserves timezone context (required for financial data)
--   - Prevents time-based query issues in multi-region deployments
--   - Critical for audit logs and compliance
--
-- Process Overview:
--   1. Create new column with TIMESTAMPTZ type
--   2. Copy data from old column to new column with timezone conversion
--   3. Drop old column and rename new column
--   4. Update dependent code and indexes
--   5. Validate in staging before production
--
-- ============================================================================

-- ============================================================================
-- STEP 1: Create new column with TIMESTAMPTZ type
-- ============================================================================
-- Replace:
--   - schema_name: The schema where the table is located (e.g., 'trading')
--   - table_name: The table that needs migration (e.g., 'orders')
--   - column_name: The column to migrate (e.g., 'created_at')
--
-- Note: Add new column with '_new' suffix to preserve original during transition

ALTER TABLE schema_name.table_name
ADD COLUMN column_name_new TIMESTAMPTZ;

-- ============================================================================
-- STEP 2: Copy data with timezone conversion
-- ============================================================================
-- Option A: If the old TIMESTAMP is in UTC (most common case)
--           Treat naive timestamps as UTC and convert to TIMESTAMPTZ

UPDATE schema_name.table_name
SET column_name_new = column_name AT TIME ZONE 'UTC'
WHERE column_name IS NOT NULL;

-- ============================================================================
-- Option B: If the old TIMESTAMP is in a different timezone
--           Replace 'America/New_York' with the appropriate timezone
--
-- UPDATE schema_name.table_name
-- SET column_name_new = column_name AT TIME ZONE 'America/New_York'
-- WHERE column_name IS NOT NULL;
--
-- ============================================================================

-- Option C: If you need to apply a specific offset (not recommended)
--           This example applies +5:30 offset (for India Standard Time)
--
-- UPDATE schema_name.table_name
-- SET column_name_new = (column_name AT TIME ZONE '+05:30')
-- WHERE column_name IS NOT NULL;
--
-- ============================================================================

-- ============================================================================
-- STEP 3: Verify data integrity before dropping
-- ============================================================================
-- Run these queries to ensure data was copied correctly:

-- Check for NULL mismatches (old had value but new doesn't)
-- SELECT COUNT(*) as null_mismatch
-- FROM schema_name.table_name
-- WHERE (column_name IS NULL AND column_name_new IS NOT NULL)
--    OR (column_name IS NOT NULL AND column_name_new IS NULL);
--
-- Expected result: 0 rows

-- Check sample data to verify timezone conversion
-- SELECT
--   column_name as old_value,
--   column_name_new as new_value,
--   (column_name AT TIME ZONE 'UTC') as expected
-- FROM schema_name.table_name
-- LIMIT 10;

-- ============================================================================
-- STEP 4: Drop old column and rename new column
-- ============================================================================
-- After verification, perform the column swap

ALTER TABLE schema_name.table_name
DROP COLUMN column_name;

ALTER TABLE schema_name.table_name
RENAME COLUMN column_name_new TO column_name;

-- ============================================================================
-- STEP 5: Update any constraints or defaults if needed
-- ============================================================================
-- If the original column had a DEFAULT clause, re-add it:

-- For current timestamp default:
-- ALTER TABLE schema_name.table_name
-- ALTER COLUMN column_name SET DEFAULT NOW();

-- For specific timezone default:
-- ALTER TABLE schema_name.table_name
-- ALTER COLUMN column_name SET DEFAULT now() AT TIME ZONE 'UTC';

-- For NOT NULL constraint (if it existed):
-- ALTER TABLE schema_name.table_name
-- ALTER COLUMN column_name SET NOT NULL;

-- ============================================================================
-- STEP 6: Recreate indexes and constraints if needed
-- ============================================================================
-- After migration, recreate any indexes that were dropped:

-- Example: If there was an index on created_at
-- CREATE INDEX idx_table_name_column_name
-- ON schema_name.table_name(column_name DESC);

-- ============================================================================
-- EXAMPLE: Complete migration for a real table
-- ============================================================================
-- This example shows how to migrate 'trading.orders.created_at'
--
-- -- Step 1: Add new column
-- ALTER TABLE trading.orders
-- ADD COLUMN created_at_new TIMESTAMPTZ;
--
-- -- Step 2: Copy data (assuming UTC)
-- UPDATE trading.orders
-- SET created_at_new = created_at AT TIME ZONE 'UTC'
-- WHERE created_at IS NOT NULL;
--
-- -- Step 3: Verify (optional)
-- SELECT COUNT(*) FROM trading.orders
-- WHERE created_at_new IS NULL AND created_at IS NOT NULL;
--
-- -- Step 4: Drop and rename
-- ALTER TABLE trading.orders
-- DROP COLUMN created_at;
--
-- ALTER TABLE trading.orders
-- RENAME COLUMN created_at_new TO created_at;
--
-- -- Step 5: Add back constraints/defaults
-- ALTER TABLE trading.orders
-- ALTER COLUMN created_at SET NOT NULL;
--
-- ALTER TABLE trading.orders
-- ALTER COLUMN created_at SET DEFAULT NOW();
--
-- -- Step 6: Recreate indexes
-- CREATE INDEX idx_orders_created ON trading.orders(created_at DESC);

-- ============================================================================
-- BATCH MIGRATION: Multiple columns in same table
-- ============================================================================
-- To migrate multiple timestamp columns in the same table:
--
-- -- Step 1: Add all new columns
-- ALTER TABLE schema_name.table_name
-- ADD COLUMN created_at_new TIMESTAMPTZ,
-- ADD COLUMN updated_at_new TIMESTAMPTZ,
-- ADD COLUMN verified_at_new TIMESTAMPTZ;
--
-- -- Step 2: Copy all data
-- UPDATE schema_name.table_name
-- SET
--   created_at_new = created_at AT TIME ZONE 'UTC',
--   updated_at_new = updated_at AT TIME ZONE 'UTC',
--   verified_at_new = verified_at AT TIME ZONE 'UTC'
-- WHERE created_at IS NOT NULL OR updated_at IS NOT NULL OR verified_at IS NOT NULL;
--
-- -- Step 3: Verify
-- SELECT COUNT(*) FROM schema_name.table_name
-- WHERE (created_at IS NOT NULL AND created_at_new IS NULL)
--    OR (updated_at IS NOT NULL AND updated_at_new IS NULL)
--    OR (verified_at IS NOT NULL AND verified_at_new IS NULL);
--
-- -- Step 4: Drop and rename
-- ALTER TABLE schema_name.table_name
-- DROP COLUMN created_at,
-- DROP COLUMN updated_at,
-- DROP COLUMN verified_at;
--
-- ALTER TABLE schema_name.table_name
-- RENAME COLUMN created_at_new TO created_at;
--
-- ALTER TABLE schema_name.table_name
-- RENAME COLUMN updated_at_new TO updated_at;
--
-- ALTER TABLE schema_name.table_name
-- RENAME COLUMN verified_at_new TO verified_at;

-- ============================================================================
-- TESTING IN STAGING
-- ============================================================================
-- Before running in production:
--
-- 1. Create backup:
--    pg_dump -d trading_platform -t schema_name.table_name > backup.sql
--
-- 2. Test migration:
--    psql -d trading_platform_staging < migration.sql
--
-- 3. Verify data:
--    SELECT * FROM schema_name.table_name LIMIT 100;
--
-- 4. Test application:
--    - Run application tests
--    - Check date/time queries work correctly
--    - Verify timezone conversions in UI
--
-- 5. Run application in staging:
--    - Full integration test
--    - Check for any time-related bugs
--
-- 6. Only then run in production

-- ============================================================================
-- ROLLBACK PROCEDURE (if needed)
-- ============================================================================
-- If something goes wrong and you need to rollback:
--
-- 1. Restore from backup:
--    psql -d trading_platform < backup.sql
--
-- 2. Or manually recreate:
--    ALTER TABLE schema_name.table_name
--    ADD COLUMN column_name TIMESTAMP;
--
--    UPDATE schema_name.table_name
--    SET column_name = column_name_new AT TIME ZONE 'UTC'
--    WHERE column_name_new IS NOT NULL;
--
--    ALTER TABLE schema_name.table_name
--    DROP COLUMN column_name_new;

-- ============================================================================
-- APPLICATION CODE CHANGES (TypeScript example)
-- ============================================================================
-- After database migration, update your application code:
--
-- OLD CODE (expects naive TIMESTAMP):
-- const createdAt = new Date(row.created_at);
--
-- NEW CODE (receives TIMESTAMPTZ):
-- const createdAt = new Date(row.created_at); // Already has timezone info!
--
-- Or with more explicit handling:
-- const createdAt = new Date(row.created_at);
-- const userTimeZone = 'America/New_York';
-- const formattedDate = createdAt.toLocaleString('en-US',
--   { timeZone: userTimeZone });

-- ============================================================================
-- NOTES
-- ============================================================================
-- - This template is designed to be safe and reversible
-- - Always test in staging before running in production
-- - Communicate with your team before migrating
-- - Update documentation after migration
-- - Consider application downtime for large tables
-- - For very large tables, consider doing migration in batches

-- ============================================================================
-- CURRENT STATUS: NOT NEEDED
-- ============================================================================
-- As of 2026-02-03, all timestamp columns in trading-platform already use
-- TIMESTAMPTZ and are fully compliant with standards. This template is
-- provided for future reference and for other projects.
-- See: migrations/README-timestamp-standardization.md