-- ============================================
-- Simple Debug: Deal Not Showing in KPI Dashboard
-- Date: January 4, 2025
-- Purpose: Simple query to verify deal and payment data
-- ============================================

-- Step 1: Find your most recent deal
SET @deal_id = NULL;
SELECT MAX(id) INTO @deal_id FROM deals ORDER BY register_time DESC LIMIT 1;

-- Step 2: Show deal information
SELECT 
    'DEAL INFO' as section,
    id AS deal_id,
    didar_deal_id AS deal_guid,
    title AS deal_title,
    status AS deal_status,
    payable_amount AS deal_payable,
    register_time AS deal_registered,
    owner_didar_id AS owner_id,
    contact_didar_id AS contact_id
FROM deals 
WHERE id = @deal_id;

-- Step 3: Show all transactions for this deal
SELECT 
    'ALL TRANSACTIONS' as section,
    id AS trans_id,
    amount AS trans_amount,
    DATE(payment_date) AS payment_date,
    payment_time AS payment_time,
    is_first_payment AS first_flag,
    status AS trans_status
FROM transactions 
WHERE deal_id = @deal_id
ORDER BY payment_date, payment_time;

-- Step 4: KPI inclusion check (Sales KPI)
SELECT 
    'SALES KPI CHECK' as section,
    'Will be in Sales KPI?' AS result,
    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 - has confirmed first payment'
        ELSE 'NO - no confirmed first payment'
    END AS check_result;

-- Step 5: KPI inclusion check (Settled KPI)
SELECT 
    'SETTLED KPI CHECK' as section,
    'Will be in Settled KPI?' AS result,
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM transactions t
            WHERE t.deal_id = @deal_id
              AND t.status = 'confirmed'
              AND (
                  SELECT COALESCE(SUM(t2.amount), 0)
                  FROM transactions t2
                  WHERE t2.deal_id = @deal_id 
                    AND t2.status = 'confirmed'
              ) >= COALESCE(d.payable_amount, 0)
        )
        THEN 'YES - fully paid'
        ELSE 'NO - not fully paid'
    END AS check_result;

-- Step 6: Date range check
SELECT 
    'DATE RANGE CHECK' as section,
    'Your Filter (Jan 1-31, 2025)' AS your_filter,
    'Deal Registration Date' AS deal_date,
    'Payment Date' AS payment_date,
    'Payment in Range?' AS in_range
FROM deals d
LEFT JOIN transactions t ON d.id = t.deal_id
WHERE d.id = @deal_id
  AND t.is_first_payment = 1
  AND t.status = 'confirmed';

-- Step 7: Summary diagnosis
SELECT 
    'DIAGNOSIS SUMMARY' as section,
    'Issue' AS potential_issue,
    'Evidence' AS evidence,
    'Fix' AS recommended_fix
FROM (
    SELECT 
        'NULL payment_date' AS issue,
        'Transaction has NULL payment date' AS evidence,
        'Set payment_date = DATE(payment_date)' AS recommended_fix
        COUNT(*) AS issue_count
    FROM transactions 
    WHERE deal_id = @deal_id 
      AND payment_date IS NULL
    
    UNION ALL
    
    SELECT 
        'Payable amount is NULL' AS issue,
        'Deal has no payable amount' AS evidence,
        'Update deal payable_amount = 0 or real value' AS recommended_fix,
        COUNT(*) AS issue_count
    FROM deals 
    WHERE id = @deal_id 
      AND payable_amount IS NULL
    
    UNION ALL
    
    SELECT 
        'Payable amount is 0' AS issue,
        'Deal has payable_amount = 0 (default value)' AS evidence,
        'Set real payment amount in deal' AS recommended_fix,
        COUNT(*) AS issue_count
    FROM deals 
    WHERE id = @deal_id 
      AND payable_amount = 0
);