-- ============================================
-- Check Payment Date Issue
-- Date: January 4, 2025
-- Deal ID: 54660
-- ============================================

SET @deal_id = 54660;

-- Step 1: Show All Transaction Dates
SELECT 
    '=== TRANSACTION DATES ===' as section,
    t.id as trans_id,
    t.amount,
    t.payment_date,
    t.payment_time,
    t.is_first_payment,
    t.status,
    t.created_at,
    CASE 
        WHEN t.payment_date IS NULL THEN 'NULL ❌'
        WHEN t.payment_date < '2025-01-01' THEN 'OLD DATE ❌ - ' || t.payment_date
        WHEN t.payment_date > '2026-12-31' THEN 'FUTURE DATE ❌ - ' || t.payment_date
        WHEN t.payment_date >= '2025-12-22' AND t.payment_date <= '2026-01-20' THEN 'IN RANGE ✅'
        ELSE 'OUT OF RANGE ❌ - ' || t.payment_date
    END as date_status,
    'Filter Range: 2025-12-22 to 2026-01-20 (Persian 1404/10/01-30)' as filter_note
FROM transactions t
WHERE t.deal_id = @deal_id
ORDER BY t.payment_date, t.payment_time;

-- Step 2: Check Deal Registration Date
SELECT 
    '=== DEAL REGISTRATION DATE ===' as section,
    d.id,
    d.didar_deal_id,
    d.register_time,
    DATE(d.register_time) as deal_date,
    'This is when deal was created' as note
FROM deals d
WHERE d.id = @deal_id;

-- Step 3: Compare Dates
SELECT 
    '=== DATE COMPARISON ===' as section,
    'Deal Created' as deal_created,
    DATE(d.register_time) as deal_date,
    'Payment Date' as payment_date,
    t.payment_date,
    'Difference' as difference,
    DATEDIFF(t.payment_date, DATE(d.register_time)) as days_diff,
    CASE 
        WHEN t.payment_date < DATE(d.register_time) THEN '❌ Payment BEFORE deal creation (WRONG!)'
        WHEN t.payment_date = DATE(d.register_time) THEN '✅ Payment on same day as deal (OK)'
        WHEN t.payment_date > DATE(d.register_time) THEN '✅ Payment AFTER deal creation (OK)'
        ELSE '❌ NULL payment_date'
    END as date_logic
FROM deals d
INNER JOIN transactions t ON d.id = t.deal_id
WHERE d.id = @deal_id
ORDER BY t.payment_date
LIMIT 1;

-- Step 4: Fix Payment Date (if needed)
-- Option A: Use deal register_time
SELECT 
    '=== FIX OPTION 1: Use Deal Register Time ===' as section,
    'Current payment_date' as current,
    t.payment_date,
    'Suggested payment_date' as suggested,
    DATE(d.register_time) as new_date,
    'SQL to fix:' as fix_sql,
    CONCAT('UPDATE transactions SET payment_date = DATE(register_time) WHERE deal_id = ', @deal_id, ' AND payment_date IS NULL OR payment_date < ''2025-01-01''') as sql_command
FROM deals d
INNER JOIN transactions t ON d.id = t.deal_id
WHERE d.id = @deal_id
LIMIT 1;

-- Option B: Use payment_time
SELECT 
    '=== FIX OPTION 2: Use Payment Time ===' as section,
    'Current payment_date' as current,
    t.payment_date,
    'payment_time' as payment_time,
    t.payment_time,
    'Suggested payment_date' as suggested,
    DATE(t.payment_time) as new_date,
    'SQL to fix:' as fix_sql,
    CONCAT('UPDATE transactions SET payment_date = DATE(payment_time) WHERE deal_id = ', @deal_id, ' AND payment_date IS NULL OR payment_date < ''2025-01-01''') as sql_command
FROM transactions t
WHERE t.deal_id = @deal_id
LIMIT 1;

-- Step 5: Auto-Fix Query (Run this to fix)
SELECT 
    '=== AUTO-FIX QUERY ===' as section,
    'Run this UPDATE to fix payment_date:' as instruction,
    CONCAT('
UPDATE transactions t
INNER JOIN deals d ON t.deal_id = d.id
SET t.payment_date = COALESCE(
    DATE(t.payment_time),
    DATE(d.register_time),
    CURDATE()
)
WHERE t.deal_id = ', @deal_id, '
  AND (t.payment_date IS NULL 
       OR t.payment_date < ''2025-01-01''
       OR t.payment_date > ''2026-12-31'')
') as fix_query;