-- ============================================
-- Debug Query: Deal Not Showing in KPI Dashboard
-- Date: January 4, 2025
-- Purpose: Verify why new deal with payments doesn't appear in KPI
-- ============================================

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

-- Display recent deal info
SELECT 
    'Recent Deal ID' as query,
    'Deal ID' as deal_id,
    'Didar Deal ID' as didar_deal_id,
    'Title' as title,
    'Status' as status,
    'Payable Amount' as payable_amount,
    'Payment Amount' as payment_amount,
    'Register Time' as register_time,
    'Won Time' as won_time,
    'Lost Time' as lost_time,
    'Owner ID' as owner_didar_id,
    'Contact ID' as contact_didar_id
FROM deals 
WHERE id = @recent_deal_id;

-- Step 2: Check all payments for this deal
SELECT 
    '=== DEAL PAYMENTS ===' as section,
    'Transaction ID' as id,
    'Deal ID' as deal_id,
    'Amount' as amount,
    'Payment Date' as payment_date,
    'Payment Time' as payment_time,
    'Is First Payment' as is_first_payment,
    'Status' as status,
    'Created At' as created_at
FROM transactions 
WHERE deal_id = @recent_deal_id
ORDER BY payment_date, payment_time;

-- Step 3: Check why deal might not be in KPI
SELECT 
    '=== KPI FILTER CHECK ===' as section,
    'Filter Type' as filter_type,
    'Condition' as condition,
    'Result' as result
FROM (
    SELECT 
        'Sales KPI' as filter_type,
        'is_first_payment = 1' as condition,
        CASE WHEN EXISTS (
            SELECT 1 FROM transactions t 
            WHERE t.deal_id = @recent_deal_id 
              AND t.is_first_payment = 1 
              AND t.status = 'confirmed'
        ) THEN 'WOULD BE COUNTED' 
        ELSE 'NOT COUNTED' 
        END as result
    
    UNION ALL
    
    SELECT 
        'Sales KPI' as filter_type,
        'payable_amount > 0' as condition,
        CASE WHEN EXISTS (
            SELECT 1 FROM deals d 
            WHERE d.id = @recent_deal_id 
              AND d.payable_amount IS NOT NULL 
              AND d.payable_amount > 0
        ) THEN 'WOULD BE COUNTED' 
        ELSE 'NOT COUNTED' 
        END as result
    
    UNION ALL
    
    SELECT 
        'Settled KPI' as filter_type,
        'settled = 1' as condition,
        CASE WHEN EXISTS (
            SELECT 1 FROM deals d
            WHERE d.id = @recent_deal_id
              AND d.payable_amount IS NOT NULL
              AND d.payable_amount > 0
              AND EXISTS (
                  SELECT 1 FROM transactions t 
                  WHERE t.deal_id = d.id
                    AND t.status = 'confirmed'
                    AND (
                        SELECT COALESCE(SUM(t2.amount), 0) FROM transactions t2
                        WHERE t2.deal_id = d.id
                        AND t2.status = 'confirmed'
                    ) >= d.payable_amount
              )
        ) THEN 'WOULD BE COUNTED' 
        ELSE 'NOT COUNTED' 
        END as result
    
    UNION ALL
    
    SELECT 
        'Outstanding KPI' as filter_type,
        'outstanding = 1' as condition,
        CASE WHEN EXISTS (
            SELECT 1 FROM deals d
            WHERE d.id = @recent_deal_id
              AND d.payable_amount IS NOT NULL
              AND d.payable_amount > 0
              AND EXISTS (
                  SELECT 1 FROM transactions t 
                  WHERE t.deal_id = d.id
                    AND t.status = 'confirmed'
                    AND (
                        SELECT COALESCE(SUM(t2.amount), 0) FROM transactions t2
                        WHERE t2.deal_id = d.id
                        AND t2.status = 'confirmed'
                    ) < d.payable_amount
              )
        ) THEN 'WOULD BE COUNTED' 
        ELSE 'NOT COUNTED' 
        END as result
    
    UNION ALL
    
    SELECT 
        'Lost Sales KPI' as filter_type,
        'status = Lost' as condition,
        CASE WHEN EXISTS (
            SELECT 1 FROM deals d
            WHERE d.id = @recent_deal_id
              AND d.status = 'Lost'
              AND d.lost_time IS NOT NULL
        ) THEN 'WOULD BE COUNTED' 
        ELSE 'NOT COUNTED' 
        END as result
    
    UNION ALL
    
    SELECT 
        'Open Pipeline KPI' as filter_type,
        'status NOT IN (Won, Lost)' as condition,
        CASE WHEN EXISTS (
            SELECT 1 FROM deals d
            WHERE d.id = @recent_deal_id
              AND d.status NOT IN ('Won', 'Lost')
        ) THEN 'WOULD BE COUNTED' 
        ELSE 'NOT COUNTED' 
        END as result
    
    UNION ALL
    
    SELECT 
        'Lead Conversion KPI' as filter_type,
        'has transaction with is_first_payment = 1' as condition,
        CASE WHEN EXISTS (
            SELECT 1 FROM transactions t 
            WHERE t.deal_id = @recent_deal_id
              AND t.is_first_payment = 1 
              AND t.status = 'confirmed'
        ) THEN 'WOULD BE COUNTED' 
        ELSE 'NOT COUNTED' 
        END as result
    
    UNION ALL
    
    SELECT 
        'Time to Conversion KPI' as filter_type,
        'has transaction with is_first_payment = 1' as condition,
        CASE WHEN EXISTS (
            SELECT 1 FROM deals d 
            INNER JOIN transactions t ON d.id = t.deal_id
            WHERE d.id = @recent_deal_id
              AND t.is_first_payment = 1 
              AND t.status = 'confirmed'
        ) THEN 'WOULD BE COUNTED' 
        ELSE 'NOT COUNTED' 
        END as result
) checks;

-- Step 4: Show date filter status
SELECT 
    '=== DATE FILTER CHECK ===' as section,
    'KPI Date Range' as kpi_date_range,
    'Deal Register Date' as deal_register_date,
    'Payment Date' as payment_date,
    'Payment in Range?' as in_range,
    'Result' as result
FROM (
    SELECT 
        'Your KPI Filter' as kpi_date_range,
        '2025-01-01' as deal_register_date,
        t.payment_date as payment_date,
        CASE WHEN t.payment_date BETWEEN '2025-01-01' AND '2025-12-31'
        THEN 'YES - In Range'
        ELSE 'NO - Out of Range'
        END as result
FROM deals d
INNER JOIN transactions t ON d.id = t.deal_id
WHERE d.id = @recent_deal_id
  AND t.is_first_payment = 1
LIMIT 1;

-- Step 5: Check for NULL payment dates (this would exclude from KPI!)
SELECT 
    '=== NULL DATE CHECK ===' as section,
    'Transaction ID' as id,
    'Payment Date' as payment_date,
    'Payment Time' as payment_time,
    'Amount' as amount,
    'Is First Payment' as is_first_payment,
    'Status' as status
    'NULL Payment Date?' as is_null_date,
    'Would Exclude from KPI?' as would_exclude
FROM transactions 
WHERE deal_id = @recent_deal_id
ORDER BY payment_date;

-- Summary diagnosis
SELECT 
    '=== DIAGNOSIS SUMMARY ===' as section,
    'Potential Issue' as potential_issue,
    'Likely Cause' as likely_cause,
    'Recommended Fix' as recommended_fix
FROM (
    SELECT 
        'NULL payment_date exists' as potential_issue,
        'First payment date not set to 0-7 days' as likely_cause,
        'UPDATE deal to set register_time correctly or check payment date format' as recommended_fix
    WHERE deal_id = @recent_deal_id
      AND (
          SELECT COUNT(*) FROM transactions 
          WHERE deal_id = @recent_deal_id 
          AND payment_date IS NULL
      ) > 0
    LIMIT 1
    
    UNION ALL
    
    SELECT 
        'payment_date before deal registration' as potential_issue,
        'Retroactive deal: Payment date older than deal.register_time' as likely_cause,
        'First payment flag should be 0 (days to conversion = 0)' as recommended_fix
    WHERE deal_id = @recent_deal_id
      AND (
          SELECT COUNT(*) FROM transactions t 
          WHERE deal_id = @recent_deal_id 
          AND payment_date < (
              SELECT MIN(DATE(d.register_time)) FROM deals WHERE id = @recent_deal_id
          )
      ) > 0
    LIMIT 1
    
    UNION ALL
    
    SELECT 
        'No confirmed payments' as potential_issue,
        'All payments have status != confirmed' as likely_cause,
        'Update transaction statuses to confirmed' as recommended_fix
    WHERE deal_id = @recent_deal_id
      AND (
          SELECT COUNT(*) FROM transactions 
          WHERE deal_id = @recent_deal_id 
          AND status != 'confirmed'
      ) > 0
    LIMIT 1
);