-- ============================================
-- Fix Wrong Payment Date (0784-07-15)
-- Date: January 4, 2025
-- Purpose: Fix transaction with wrong date from Jalali conversion bug
-- ============================================

SET @deal_id = 54660;
SET @transaction_id = 62;

-- Step 1: Show current wrong date
SELECT 
    '=== CURRENT WRONG DATE ===' as section,
    t.id,
    t.payment_date as wrong_date,
    t.payment_time,
    d.register_time as deal_created
FROM transactions t
INNER JOIN deals d ON t.deal_id = d.id
WHERE t.id = @transaction_id;

-- Step 2: Fix payment_date to deal register_time (most accurate)
UPDATE transactions t
INNER JOIN deals d ON t.deal_id = d.id
SET t.payment_date = DATE(d.register_time)
WHERE t.id = @transaction_id
  AND (t.payment_date < '1900-01-01' OR t.payment_date > '2100-12-31');

-- Step 3: If payment_time is available and more recent, use that
UPDATE transactions t
INNER JOIN deals d ON t.deal_id = d.id
SET t.payment_date = DATE(t.payment_time)
WHERE t.id = @transaction_id
  AND t.payment_time IS NOT NULL
  AND t.payment_time > d.register_time
  AND (t.payment_date < '1900-01-01' OR t.payment_date > '2100-12-31');

-- Step 4: Verify fix
SELECT 
    '=== AFTER FIX ===' as section,
    t.id,
    t.payment_date as fixed_date,
    t.payment_time,
    d.register_time as deal_created,
    CASE 
        WHEN t.payment_date >= '2025-01-01' AND t.payment_date <= '2026-12-31' 
        THEN '✅ VALID DATE'
        ELSE '❌ STILL WRONG'
    END as date_status
FROM transactions t
INNER JOIN deals d ON t.deal_id = d.id
WHERE t.id = @transaction_id;

-- Step 5: Fix all transactions with wrong dates (for all deals)
UPDATE transactions t
INNER JOIN deals d ON t.deal_id = d.id
SET t.payment_date = DATE(COALESCE(t.payment_time, d.register_time))
WHERE (t.payment_date < '1900-01-01' OR t.payment_date > '2100-12-31')
  AND t.payment_date IS NOT NULL;

-- Step 6: Show summary of fixes
SELECT 
    '=== FIX SUMMARY ===' as section,
    COUNT(*) as transactions_fixed,
    'Transactions with dates < 1900 or > 2100 have been fixed' as note
FROM transactions
WHERE payment_date >= '1900-01-01' 
  AND payment_date <= '2100-12-31'
  AND payment_date IS NOT NULL;