-- ============================================
-- Check New Deal 54662 - Why Not Showing in KPI
-- Date: January 4, 2025
-- ============================================

SET @deal_id = 54662;

-- Step 1: Deal Information
SELECT 
    '=== DEAL INFO ===' as section,
    d.id,
    d.didar_deal_id,
    d.title,
    d.status,
    d.payable_amount,
    d.payment_amount,
    d.owner_didar_id,
    d.register_time,
    DATE(d.register_time) as deal_date
FROM deals d
WHERE d.id = @deal_id;

-- Step 2: All Transactions for This Deal
SELECT 
    '=== ALL TRANSACTIONS ===' 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 payment_date'
        WHEN t.payment_date < '2025-12-22' THEN '❌ OUT OF RANGE - Before filter'
        WHEN t.payment_date > '2026-01-20' THEN '❌ OUT OF RANGE - After filter'
        ELSE '✅ IN RANGE'
    END as date_status,
    CASE 
        WHEN t.is_first_payment = 1 THEN '✅ YES'
        ELSE '❌ NO'
    END as first_payment_status,
    CASE 
        WHEN t.status = 'confirmed' THEN '✅ YES'
        ELSE '❌ NO'
    END as confirmed_status
FROM transactions t
WHERE t.deal_id = @deal_id
ORDER BY t.payment_date, t.payment_time;

-- Step 3: Check Sales KPI Requirements
SELECT 
    '=== SALES KPI REQUIREMENTS ===' as section,
    'Requirement' as requirement,
    'Status' as status,
    'Result' as result
FROM (
    SELECT 
        'payable_amount > 0' as requirement,
        CASE WHEN d.payable_amount > 0 THEN 'YES ✅' ELSE 'NO ❌' END as status,
        CASE WHEN d.payable_amount > 0 THEN 'PASS' ELSE 'FAIL' END as result
    FROM deals d WHERE d.id = @deal_id
    
    UNION ALL
    
    SELECT 
        'Has confirmed first payment' as requirement,
        CASE WHEN EXISTS (
            SELECT 1 FROM transactions t 
            WHERE t.deal_id = @deal_id 
              AND t.is_first_payment = 1 
              AND t.status = 'confirmed'
        ) THEN 'YES ✅' ELSE 'NO ❌' END as status,
        CASE WHEN EXISTS (
            SELECT 1 FROM transactions t 
            WHERE t.deal_id = @deal_id 
              AND t.is_first_payment = 1 
              AND t.status = 'confirmed'
        ) THEN 'PASS' ELSE 'FAIL - No first payment' END as result
    
    UNION ALL
    
    SELECT 
        'payment_date NOT NULL' as requirement,
        CASE WHEN EXISTS (
            SELECT 1 FROM transactions t 
            WHERE t.deal_id = @deal_id 
              AND t.payment_date IS NOT NULL
        ) THEN 'YES ✅' ELSE 'NO ❌' END as status,
        CASE WHEN EXISTS (
            SELECT 1 FROM transactions t 
            WHERE t.deal_id = @deal_id 
              AND t.payment_date IS NOT NULL
        ) THEN 'PASS' ELSE 'FAIL - NULL payment_date' END as result
    
    UNION ALL
    
    SELECT 
        'payment_date in range (2025-12-22 to 2026-01-20)' as requirement,
        CASE WHEN EXISTS (
            SELECT 1 FROM transactions t 
            WHERE t.deal_id = @deal_id 
              AND t.payment_date >= '2025-12-22' 
              AND t.payment_date <= '2026-01-20'
        ) THEN 'YES ✅' ELSE 'NO ❌' END as status,
        CASE WHEN EXISTS (
            SELECT 1 FROM transactions t 
            WHERE t.deal_id = @deal_id 
              AND t.payment_date >= '2025-12-22' 
              AND t.payment_date <= '2026-01-20'
        ) THEN 'PASS' ELSE 'FAIL - Date out of range' END as result
    
    UNION ALL
    
    SELECT 
        'owner_didar_id matches filter' as requirement,
        CASE WHEN d.owner_didar_id = '6f307e57-ce8e-4ec1-bc7a-d17fd39001fa' 
        THEN 'YES ✅' ELSE 'NO ❌' END as status,
        CASE WHEN d.owner_didar_id = '6f307e57-ce8e-4ec1-bc7a-d17fd39001fa' 
        THEN 'PASS' ELSE 'FAIL - Owner mismatch' END as result
    FROM deals d WHERE d.id = @deal_id
) requirements;

-- Step 4: Simulate Exact Sales KPI Query
SELECT 
    '=== SIMULATE SALES KPI QUERY ===' as section,
    'Would this deal be counted?' as question,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM deals d
            INNER JOIN transactions t ON d.id = t.deal_id
            WHERE d.id = @deal_id
              AND t.is_first_payment = 1
              AND t.status = 'confirmed'
              AND t.payment_date IS NOT NULL
              AND DATE(COALESCE(t.payment_date, t.payment_time)) >= '2025-12-22'
              AND DATE(COALESCE(t.payment_date, t.payment_time)) <= '2026-01-20'
              AND d.owner_didar_id = '6f307e57-ce8e-4ec1-bc7a-d17fd39001fa'
              AND d.payable_amount > 0
        ) 
        THEN '✅ YES - Should show in KPI'
        ELSE '❌ NO - Will NOT show in KPI'
    END as result;

-- Step 5: Find the Exact Problem
SELECT 
    '=== EXACT PROBLEM ===' as section,
    'Issue' as issue,
    'Details' as details,
    'Fix SQL' as fix_sql
FROM (
    SELECT 
        'No transactions at all' as issue,
        'This deal has no transactions' as details,
        CONCAT('INSERT INTO transactions (deal_id, amount, payment_date, payment_time, is_first_payment, status, created_at) VALUES (', @deal_id, ', 4000000, DATE(NOW()), NOW(), 1, ''confirmed'', NOW())') as fix_sql
    WHERE NOT EXISTS (SELECT 1 FROM transactions t WHERE t.deal_id = @deal_id)
    
    UNION ALL
    
    SELECT 
        'No confirmed first payment' as issue,
        CONCAT('Transaction ID: ', t.id, ' | is_first_payment: ', t.is_first_payment, ' | status: ', t.status) as details,
        CONCAT('UPDATE transactions SET is_first_payment = 1, status = ''confirmed'' WHERE id = ', t.id) as fix_sql
    FROM transactions t
    WHERE t.deal_id = @deal_id
      AND (t.is_first_payment != 1 OR t.status != 'confirmed')
    LIMIT 1
    
    UNION ALL
    
    SELECT 
        'NULL payment_date' as issue,
        CONCAT('Transaction ID: ', t.id, ' | payment_date: ', IFNULL(t.payment_date, 'NULL'), ' | payment_time: ', IFNULL(t.payment_time, 'NULL')) as details,
        CONCAT('UPDATE transactions SET payment_date = DATE(payment_time) WHERE id = ', t.id, ' AND payment_time IS NOT NULL') as fix_sql
    FROM transactions t
    WHERE t.deal_id = @deal_id
      AND t.payment_date IS NULL
    LIMIT 1
    
    UNION ALL
    
    SELECT 
        'Payment date out of range' as issue,
        CONCAT('Transaction ID: ', t.id, ' | payment_date: ', t.payment_date, ' | Range: 2025-12-22 to 2026-01-20') as details,
        CONCAT('UPDATE transactions SET payment_date = ''2026-01-04'' WHERE id = ', t.id) as fix_sql
    FROM transactions t
    WHERE t.deal_id = @deal_id
      AND t.payment_date IS NOT NULL
      AND (t.payment_date < '2025-12-22' OR t.payment_date > '2026-01-20')
    LIMIT 1
) problems;