-- ============================================
-- Complete KPI Debug - Find Why Deal Doesn't Show
-- Date: January 4, 2025
-- ============================================

-- Your new deal ID (from query result)
SET @deal_id = 54660;
SET @deal_didar_id = 'local_695a5ed62e8877.22112625';

-- Step 1: Complete Deal Information
SELECT 
    '=== COMPLETE DEAL INFO ===' as section,
    d.id,
    d.didar_deal_id,
    d.title,
    d.status as deal_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 as trans_status,
    t.created_at,
    CASE 
        WHEN t.payment_date IS NULL THEN 'NULL ❌ - WILL EXCLUDE'
        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 ❌ - Will not show in Sales KPI'
    END as first_payment_status,
    CASE 
        WHEN t.status = 'confirmed' THEN 'YES ✅'
        ELSE 'NO ❌ - Will not show in KPI'
    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 - Deal excluded' 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 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
FROM (
    SELECT 
        'No confirmed first payment' as issue,
        CONCAT('Transaction ID: ', t.id, ' | is_first_payment: ', t.is_first_payment, ' | status: ', t.status) as details
    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
    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
    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
    
    UNION ALL
    
    SELECT 
        'payable_amount is 0 or NULL' as issue,
        CONCAT('Deal ID: ', d.id, ' | payable_amount: ', IFNULL(d.payable_amount, 'NULL')) as details
    FROM deals d
    WHERE d.id = @deal_id
      AND (d.payable_amount IS NULL OR d.payable_amount = 0)
    
    UNION ALL
    
    SELECT 
        'No transactions at all' as issue,
        'This deal has no transactions' as details
    WHERE NOT EXISTS (
        SELECT 1 FROM transactions t WHERE t.deal_id = @deal_id
    )
) problems;

-- Step 6: Fix Recommendations
SELECT 
    '=== FIX RECOMMENDATIONS ===' as section,
    'Problem' as problem,
    'Fix SQL' as fix_sql
FROM (
    SELECT 
        'Set is_first_payment = 1 for earliest payment' as problem,
        CONCAT('UPDATE transactions t SET t.is_first_payment = 1 WHERE t.deal_id = ', @deal_id, ' AND t.payment_date = (SELECT MIN(t2.payment_date) FROM transactions t2 WHERE t2.deal_id = ', @deal_id, ')') as fix_sql
    WHERE EXISTS (
        SELECT 1 FROM transactions t 
        WHERE t.deal_id = @deal_id 
          AND t.is_first_payment = 0
          AND t.payment_date = (SELECT MIN(t2.payment_date) FROM transactions t2 WHERE t2.deal_id = @deal_id)
    )
    
    UNION ALL
    
    SELECT 
        'Set status = confirmed' as problem,
        CONCAT('UPDATE transactions SET status = ''confirmed'' WHERE deal_id = ', @deal_id, ' AND status != ''confirmed''') as fix_sql
    WHERE EXISTS (
        SELECT 1 FROM transactions t 
        WHERE t.deal_id = @deal_id 
          AND t.status != 'confirmed'
    )
    
    UNION ALL
    
    SELECT 
        'Set payment_date from payment_time' as problem,
        CONCAT('UPDATE transactions SET payment_date = DATE(payment_time) WHERE deal_id = ', @deal_id, ' AND payment_date IS NULL AND payment_time IS NOT NULL') as fix_sql
    WHERE EXISTS (
        SELECT 1 FROM transactions t 
        WHERE t.deal_id = @deal_id 
          AND t.payment_date IS NULL
          AND t.payment_time IS NOT NULL
    )
    
    UNION ALL
    
    SELECT 
        'Set payment_date to deal register_time' as problem,
        CONCAT('UPDATE transactions t INNER JOIN deals d ON t.deal_id = d.id SET t.payment_date = DATE(d.register_time) WHERE t.deal_id = ', @deal_id, ' AND t.payment_date IS NULL') as fix_sql
    WHERE EXISTS (
        SELECT 1 FROM transactions t 
        WHERE t.deal_id = @deal_id 
          AND t.payment_date IS NULL
    )
) fixes;