-- ============================================
-- Check Why Deal Is Not Showing in KPI Dashboard
-- Date: January 4, 2025
-- Purpose: Diagnostic query to find why deal with payments doesn't appear in KPI
-- ============================================

-- Replace 'local_695a4eb14b2ab1.16113397' with your actual deal didar_deal_id
SET @deal_didar_id = 'local_695a4eb14b2ab1.16113397';

-- Step 1: Check deal information
SELECT 
    '=== DEAL INFO ===' as section,
    d.id as deal_id,
    d.didar_deal_id,
    d.title,
    d.status,
    d.payable_amount,
    d.payment_amount,
    d.register_time,
    d.owner_didar_id,
    d.contact_didar_id
FROM deals d
WHERE d.didar_deal_id = @deal_didar_id;

-- Step 2: Check all transactions for this deal
SELECT 
    '=== TRANSACTIONS ===' as section,
    t.id as trans_id,
    t.deal_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 - WILL EXCLUDE FROM KPI'
        WHEN t.payment_date < '2025-01-01' THEN 'OUT OF RANGE - Before 2025'
        WHEN t.payment_date > '2025-12-31' THEN 'OUT OF RANGE - After 2025'
        ELSE 'IN RANGE'
    END as date_status
FROM transactions t
INNER JOIN deals d ON t.deal_id = d.id
WHERE d.didar_deal_id = @deal_didar_id
ORDER BY t.payment_date, t.payment_time;

-- Step 3: Check if deal would be in Sales KPI
SELECT 
    '=== SALES KPI CHECK ===' as section,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM deals d
            INNER JOIN transactions t ON d.id = t.deal_id
            WHERE d.didar_deal_id = @deal_didar_id
              AND t.is_first_payment = 1
              AND t.status = 'confirmed'
              AND t.payment_date IS NOT NULL
        ) THEN 'YES - Would be counted'
        ELSE 'NO - Missing requirements'
    END as in_sales_kpi,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM transactions t
            INNER JOIN deals d ON t.deal_id = d.id
            WHERE d.didar_deal_id = @deal_didar_id
              AND t.is_first_payment = 1
        ) THEN 'YES'
        ELSE 'NO - No first payment flag'
    END as has_first_payment,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM transactions t
            INNER JOIN deals d ON t.deal_id = d.id
            WHERE d.didar_deal_id = @deal_didar_id
              AND t.status = 'confirmed'
        ) THEN 'YES'
        ELSE 'NO - No confirmed payments'
    END as has_confirmed_payment,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM transactions t
            INNER JOIN deals d ON t.deal_id = d.id
            WHERE d.didar_deal_id = @deal_didar_id
              AND t.payment_date IS NOT NULL
        ) THEN 'YES'
        ELSE 'NO - NULL payment_date'
    END as has_payment_date;

-- Step 4: Check date range (adjust these dates to match your KPI filter)
SELECT 
    '=== DATE RANGE CHECK ===' as section,
    'Your KPI Filter' as filter_type,
    '2025-01-01' as date_from,
    '2025-12-31' as date_to,
    MIN(t.payment_date) as earliest_payment,
    MAX(t.payment_date) as latest_payment,
    CASE 
        WHEN MIN(t.payment_date) >= '2025-01-01' AND MAX(t.payment_date) <= '2025-12-31' 
        THEN 'IN RANGE'
        ELSE 'OUT OF RANGE'
    END as range_status
FROM transactions t
INNER JOIN deals d ON t.deal_id = d.id
WHERE d.didar_deal_id = @deal_didar_id
  AND t.payment_date IS NOT NULL;

-- Step 5: Fix recommendations
SELECT 
    '=== FIX RECOMMENDATIONS ===' as section,
    'Issue' as issue,
    'Fix SQL' as fix_sql
FROM (
    SELECT 
        'NULL payment_date' as issue,
        CONCAT('UPDATE transactions SET payment_date = DATE(created_at) WHERE deal_id = (SELECT id FROM deals WHERE didar_deal_id = ''', @deal_didar_id, ''') AND payment_date IS NULL') as fix_sql
    WHERE EXISTS (
        SELECT 1 FROM transactions t
        INNER JOIN deals d ON t.deal_id = d.id
        WHERE d.didar_deal_id = @deal_didar_id
          AND t.payment_date IS NULL
    )
    
    UNION ALL
    
    SELECT 
        'Missing first_payment flag' as issue,
        CONCAT('UPDATE transactions t SET t.is_first_payment = 1 WHERE t.deal_id = (SELECT id FROM deals WHERE didar_deal_id = ''', @deal_didar_id, ''') AND t.payment_date = (SELECT MIN(t2.payment_date) FROM transactions t2 WHERE t2.deal_id = t.deal_id)') as fix_sql
    WHERE EXISTS (
        SELECT 1 FROM transactions t
        INNER JOIN deals d ON t.deal_id = d.id
        WHERE d.didar_deal_id = @deal_didar_id
          AND t.is_first_payment = 0
          AND t.payment_date = (
              SELECT MIN(t2.payment_date) 
              FROM transactions t2 
              WHERE t2.deal_id = t.deal_id
          )
    )
    
    UNION ALL
    
    SELECT 
        'Status not confirmed' as issue,
        CONCAT('UPDATE transactions SET status = ''confirmed'' WHERE deal_id = (SELECT id FROM deals WHERE didar_deal_id = ''', @deal_didar_id, ''') AND status != ''confirmed''') as fix_sql
    WHERE EXISTS (
        SELECT 1 FROM transactions t
        INNER JOIN deals d ON t.deal_id = d.id
        WHERE d.didar_deal_id = @deal_didar_id
          AND t.status != 'confirmed'
    )
) fixes;