-- ============================================
-- COMPREHENSIVE DATE/TIME FIX SCRIPT
-- Fixes all date and time format issues in OptionsFlow_monthly and options_flow tables
-- ============================================
-- This script:
-- 1. Identifies problematic date/time entries
-- 2. Normalizes CreatedDate to YYYY-MM-DD format
-- 3. Normalizes CreatedTime to HH:MM:SS AM/PM format (with consistent spacing)
-- 4. Normalizes ExpirationDate to YYYY-MM-DD format
-- 5. Fixes timestamp issues in options_flow table
-- ============================================

-- ============================================
-- STEP 1: ANALYSIS - Identify problematic entries
-- ============================================

-- Check CreatedDate formats
SELECT 
  'CreatedDate Analysis' as check_type,
  COUNT(*) FILTER (WHERE "CreatedDate"::text ~ '^\d{4}-\d{2}-\d{2}$') as valid_iso_date,
  COUNT(*) FILTER (WHERE "CreatedDate"::text ~ '^\d{1,2}/\d{1,2}/\d{2,4}$') as slash_format,
  COUNT(*) FILTER (WHERE "CreatedDate" IS NOT NULL AND "CreatedDate"::text !~ '^\d{4}-\d{2}-\d{2}$' AND "CreatedDate"::text !~ '^\d{1,2}/\d{1,2}/\d{2,4}$') as invalid_format,
  COUNT(*) FILTER (WHERE "CreatedDate" IS NULL) as null_dates
FROM "OptionsFlow_monthly";

-- Check CreatedTime formats
SELECT 
  'CreatedTime Analysis' as check_type,
  COUNT(*) FILTER (WHERE "CreatedTime"::text ~ '^\d{1,2}:\d{2}:\d{2}\s+[AP]M$') as valid_with_space,
  COUNT(*) FILTER (WHERE "CreatedTime"::text ~ '^\d{1,2}:\d{2}:\d{2}[AP]M$') as valid_no_space,
  COUNT(*) FILTER (WHERE "CreatedTime"::text ~ '^\d{1,2}:\d{2}\s+[AP]M$') as valid_minutes_only_space,
  COUNT(*) FILTER (WHERE "CreatedTime"::text ~ '^\d{1,2}:\d{2}[AP]M$') as valid_minutes_only_no_space,
  COUNT(*) FILTER (WHERE "CreatedTime" IS NOT NULL AND "CreatedTime"::text !~* '(AM|PM)') as no_ampm,
  COUNT(*) FILTER (WHERE "CreatedTime"::text ~ '^\d{1,2}\s*[AP]M$' AND "CreatedTime"::text !~ ':') as missing_colon,
  COUNT(*) FILTER (WHERE "CreatedTime" IS NULL) as null_times
FROM "OptionsFlow_monthly";

-- Check ExpirationDate formats
SELECT 
  'ExpirationDate Analysis' as check_type,
  COUNT(*) FILTER (WHERE "ExpirationDate"::text ~ '^\d{4}-\d{2}-\d{2}$') as valid_iso_date,
  COUNT(*) FILTER (WHERE "ExpirationDate"::text ~ '^\d{1,2}/\d{1,2}/\d{2,4}$') as slash_format,
  COUNT(*) FILTER (WHERE "ExpirationDate" IS NOT NULL AND "ExpirationDate"::text !~ '^\d{4}-\d{2}-\d{2}$' AND "ExpirationDate"::text !~ '^\d{1,2}/\d{1,2}/\d{2,4}$') as invalid_format,
  COUNT(*) FILTER (WHERE "ExpirationDate" IS NULL) as null_dates
FROM "OptionsFlow_monthly";

-- ============================================
-- STEP 2: FIX CreatedDate - Normalize to YYYY-MM-DD
-- ============================================

UPDATE "OptionsFlow_monthly"
SET "CreatedDate" = 
  CASE
    -- Already in YYYY-MM-DD format - keep as-is
    WHEN "CreatedDate"::text ~ '^\d{4}-\d{2}-\d{2}$' 
      THEN "CreatedDate"::text
    -- MM/DD/YYYY format - convert to YYYY-MM-DD
    WHEN "CreatedDate"::text ~ '^(\d{1,2})/(\d{1,2})/(\d{2,4})$'
      THEN 
        TO_CHAR(
          to_date("CreatedDate"::text, 'MM/DD/YYYY'),
          'YYYY-MM-DD'
        )
    -- M/D/YYYY format - convert to YYYY-MM-DD
    WHEN "CreatedDate"::text ~ '^(\d{1,2})/(\d{1,2})/(\d{4})$'
      THEN 
        TO_CHAR(
          to_date("CreatedDate"::text, 'MM/DD/YYYY'),
          'YYYY-MM-DD'
        )
    -- Try to parse as date and reformat
    ELSE 
      CASE 
        WHEN to_date("CreatedDate"::text, 'YYYY-MM-DD') IS NOT NULL
          THEN TO_CHAR(to_date("CreatedDate"::text, 'YYYY-MM-DD'), 'YYYY-MM-DD')
        WHEN to_date("CreatedDate"::text, 'MM/DD/YYYY') IS NOT NULL
          THEN TO_CHAR(to_date("CreatedDate"::text, 'MM/DD/YYYY'), 'YYYY-MM-DD')
        ELSE "CreatedDate"::text  -- Keep original if can't parse
      END
  END
WHERE "CreatedDate" IS NOT NULL
  AND "CreatedDate"::text !~ '^\d{4}-\d{2}-\d{2}$';  -- Only update if not already correct

-- ============================================
-- STEP 3: FIX CreatedTime - Normalize spacing and format
-- ============================================

UPDATE "OptionsFlow_monthly"
SET "CreatedTime" = 
  CASE
    -- Already properly formatted with space: "HH:MM:SS AM" or "H:MM:SS AM"
    WHEN "CreatedTime"::text ~ '^\d{1,2}:\d{2}:\d{2}\s+[AP]M$'
      THEN "CreatedTime"::text  -- Keep as-is
    
    -- Missing space before AM/PM: "HH:MM:SSAM" -> "HH:MM:SS AM"
    WHEN "CreatedTime"::text ~ '^(\d{1,2}:\d{2}:\d{2})([AP]M)$'
      THEN REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2}:\d{2}:\d{2})([AP]M)$', '\1 \2', 'i')
    
    -- Minutes only with space: "HH:MM AM" -> "HH:MM:00 AM"
    WHEN "CreatedTime"::text ~ '^(\d{1,2}:\d{2})\s+([AP]M)$'
      THEN REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2}:\d{2})\s+([AP]M)$', '\1:00 \2', 'i')
    
    -- Minutes only without space: "HH:MMAM" -> "HH:MM:00 AM"
    WHEN "CreatedTime"::text ~ '^(\d{1,2}:\d{2})([AP]M)$'
      THEN REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2}:\d{2})([AP]M)$', '\1:00 \2', 'i')
    
    -- Just hour with space: "H AM" or "HH AM" -> "H:00:00 AM" or "HH:00:00 AM"
    WHEN "CreatedTime"::text ~ '^(\d{1,2})\s+([AP]M)$'
      THEN LPAD(REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2})\s+([AP]M)$', '\1', 'i'), 2, '0') || ':00:00 ' || 
           UPPER(REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2})\s+([AP]M)$', '\2', 'i'))
    
    -- Just hour without space: "HAM" or "HHAM" -> "H:00:00 AM" or "HH:00:00 AM"
    WHEN "CreatedTime"::text ~ '^(\d{1,2})([AP]M)$'
      THEN LPAD(REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2})([AP]M)$', '\1', 'i'), 2, '0') || ':00:00 ' || 
           UPPER(REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2})([AP]M)$', '\2', 'i'))
    
    -- Single digit hour with colon: ":MM AM" -> "H:MM:00 AM" (default to 9 AM or 1 PM)
    WHEN "CreatedTime"::text ~ '^:(\d{1,2})\s+([AP]M)$'
      THEN 
        CASE 
          WHEN UPPER(REGEXP_REPLACE("CreatedTime"::text, '^:(\d{1,2})\s+([AP]M)$', '\2', 'i')) = 'AM'
            THEN '9:' || LPAD(REGEXP_REPLACE("CreatedTime"::text, '^:(\d{1,2})\s+([AP]M)$', '\1', 'i'), 2, '0') || ':00 AM'
          ELSE '1:' || LPAD(REGEXP_REPLACE("CreatedTime"::text, '^:(\d{1,2})\s+([AP]M)$', '\1', 'i'), 2, '0') || ':00 PM'
        END
    
    -- Missing colon, just number and AM/PM (e.g., "34AM", "12PM")
    -- If > 12, assume it's minutes; otherwise could be hour or minute
    WHEN "CreatedTime"::text ~ '^(\d{2})([AP]M)$' AND (REGEXP_REPLACE("CreatedTime"::text, '^(\d{2})([AP]M)$', '\1', 'i'))::int > 12
      THEN 
        CASE 
          WHEN UPPER(REGEXP_REPLACE("CreatedTime"::text, '^(\d{2})([AP]M)$', '\2', 'i')) = 'AM'
            THEN '9:' || REGEXP_REPLACE("CreatedTime"::text, '^(\d{2})([AP]M)$', '\1', 'i') || ':00 AM'
          ELSE '1:' || REGEXP_REPLACE("CreatedTime"::text, '^(\d{2})([AP]M)$', '\1', 'i') || ':00 PM'
        END
    
    -- Single or double digit <= 12 with AM/PM - ambiguous, default to hour
    WHEN "CreatedTime"::text ~ '^(\d{1,2})([AP]M)$' AND (REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2})([AP]M)$', '\1', 'i'))::int <= 12
      THEN LPAD(REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2})([AP]M)$', '\1', 'i'), 2, '0') || ':00:00 ' || 
           UPPER(REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2})([AP]M)$', '\2', 'i'))
    
    -- Keep as-is if doesn't match any pattern
    ELSE "CreatedTime"::text
  END
WHERE "CreatedTime" IS NOT NULL
  AND (
    -- Update if missing space before AM/PM
    "CreatedTime"::text ~ '^\d{1,2}:\d{2}(:[0-9]{2})?[AP]M$'
    -- Or if missing colon or seconds
    OR ("CreatedTime"::text ~ '^\d{1,2}\s*[AP]M$' AND "CreatedTime"::text !~ ':')
    OR "CreatedTime"::text ~ '^:\d{1,2}\s*[AP]M$'
    OR ("CreatedTime"::text ~ '^\d{1,2}:\d{2}\s*[AP]M$' AND "CreatedTime"::text !~ ':\d{2}\s*[AP]M$')
  );

-- ============================================
-- STEP 4: FIX ExpirationDate - Normalize to YYYY-MM-DD
-- ============================================

UPDATE "OptionsFlow_monthly"
SET "ExpirationDate" = 
  CASE
    -- Already in YYYY-MM-DD format - keep as-is
    WHEN "ExpirationDate"::text ~ '^\d{4}-\d{2}-\d{2}$' 
      THEN "ExpirationDate"::text
    -- MM/DD/YYYY format - convert to YYYY-MM-DD
    WHEN "ExpirationDate"::text ~ '^(\d{1,2})/(\d{1,2})/(\d{2,4})$'
      THEN 
        TO_CHAR(
          to_date("ExpirationDate"::text, 'MM/DD/YYYY'),
          'YYYY-MM-DD'
        )
    -- Try to parse as date and reformat
    ELSE 
      CASE 
        WHEN to_date("ExpirationDate"::text, 'YYYY-MM-DD') IS NOT NULL
          THEN TO_CHAR(to_date("ExpirationDate"::text, 'YYYY-MM-DD'), 'YYYY-MM-DD')
        WHEN to_date("ExpirationDate"::text, 'MM/DD/YYYY') IS NOT NULL
          THEN TO_CHAR(to_date("ExpirationDate"::text, 'MM/DD/YYYY'), 'YYYY-MM-DD')
        ELSE "ExpirationDate"::text  -- Keep original if can't parse
      END
  END
WHERE "ExpirationDate" IS NOT NULL
  AND "ExpirationDate"::text !~ '^\d{4}-\d{2}-\d{2}$';  -- Only update if not already correct

-- ============================================
-- STEP 5: FIX options_flow table timestamps (if they exist)
-- ============================================

-- Fix expiration dates that might be NULL or invalid
UPDATE options_flow
SET expiration = NULL
WHERE expiration IS NOT NULL
  AND (expiration < '1970-01-01'::timestamp OR expiration > '2100-01-01'::timestamp);

-- Fix created_at dates that might be invalid
UPDATE options_flow
SET created_at = NOW()
WHERE created_at IS NOT NULL
  AND (created_at < '1970-01-01'::timestamp OR created_at > '2100-01-01'::timestamp);

-- ============================================
-- STEP 6: VERIFICATION - Check results
-- ============================================

-- Verify CreatedDate fixes
SELECT 
  'CreatedDate Verification' as check_type,
  COUNT(*) FILTER (WHERE "CreatedDate"::text ~ '^\d{4}-\d{2}-\d{2}$') as valid_format,
  COUNT(*) FILTER (WHERE "CreatedDate" IS NOT NULL AND "CreatedDate"::text !~ '^\d{4}-\d{2}-\d{2}$') as still_invalid,
  COUNT(*) FILTER (WHERE "CreatedDate" IS NULL) as null_count
FROM "OptionsFlow_monthly";

-- Verify CreatedTime fixes
SELECT 
  'CreatedTime Verification' as check_type,
  COUNT(*) FILTER (WHERE "CreatedTime"::text ~ '^\d{1,2}:\d{2}:\d{2}\s+[AP]M$') as valid_format,
  COUNT(*) FILTER (WHERE "CreatedTime"::text ~ '^\d{1,2}:\d{2}\s+[AP]M$') as minutes_only_valid,
  COUNT(*) FILTER (WHERE "CreatedTime" IS NOT NULL AND "CreatedTime"::text !~ '^\d{1,2}:\d{2}(:\d{2})?\s+[AP]M$') as still_invalid,
  COUNT(*) FILTER (WHERE "CreatedTime" IS NULL) as null_count
FROM "OptionsFlow_monthly";

-- Verify ExpirationDate fixes
SELECT 
  'ExpirationDate Verification' as check_type,
  COUNT(*) FILTER (WHERE "ExpirationDate"::text ~ '^\d{4}-\d{2}-\d{2}$') as valid_format,
  COUNT(*) FILTER (WHERE "ExpirationDate" IS NOT NULL AND "ExpirationDate"::text !~ '^\d{4}-\d{2}-\d{2}$') as still_invalid,
  COUNT(*) FILTER (WHERE "ExpirationDate" IS NULL) as null_count
FROM "OptionsFlow_monthly";

-- Show sample of fixed entries
SELECT 
  "CreatedDate",
  "CreatedTime",
  "ExpirationDate",
  "Symbol",
  COUNT(*) as count
FROM "OptionsFlow_monthly"
WHERE "CreatedDate"::text ~ '^\d{4}-\d{2}-\d{2}$'
  AND ("CreatedTime"::text ~ '^\d{1,2}:\d{2}(:\d{2})?\s+[AP]M$' OR "CreatedTime" IS NULL)
GROUP BY "CreatedDate", "CreatedTime", "ExpirationDate", "Symbol"
ORDER BY "CreatedDate" DESC, "CreatedTime" DESC
LIMIT 20;

-- ============================================
-- SUMMARY
-- ============================================
SELECT 
  '✅ Date/Time Fix Complete' as status,
  'Run the verification queries above to check results' as next_step;