-- ============================================
-- Fix Deal KPI Issue - Complete Diagnostic & Auto-Fix
-- Date: January 4, 2025
-- Deal ID: 54660
-- ============================================

SET @deal_id = 54660;

-- Step 1: Show Current Transaction Status
SELECT 
    '=== CURRENT TRANSACTION STATUS ===' as section,
    t.id as trans_id,
    t.amount,
    t.payment_date,
    t.payment_time,
    t.is_first_payment,
    t.status,
    CASE 
        WHEN t.is_first_payment = 1 AND t.status = 'confirmed' AND t.payment_date IS NOT NULL
        THEN '✅ READY FOR KPI'
        WHEN t.is_first_payment = 0 THEN '❌ is_first_payment = 0'
        WHEN t.status != 'confirmed' THEN '❌ status != confirmed'
        WHEN t.payment_date IS NULL THEN '❌ payment_date IS NULL'
        ELSE '❌ UNKNOWN ISSUE'
    END as issue
FROM transactions t
WHERE t.deal_id = @deal_id
ORDER BY t.payment_date, t.payment_time;

-- Step 2: Auto-Fix Issues
-- Fix 1: Set is_first_payment = 1 for earliest payment
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 
        AND t2.payment_date IS NOT NULL
  )
  AND t.is_first_payment = 0;

-- Fix 2: Set all other payments to is_first_payment = 0
UPDATE transactions t
SET t.is_first_payment = 0
WHERE t.deal_id = @deal_id
  AND t.payment_date != (
      SELECT MIN(t2.payment_date) 
      FROM transactions t2 
      WHERE t2.deal_id = @deal_id 
        AND t2.payment_date IS NOT NULL
  )
  AND t.is_first_payment = 1;

-- Fix 3: Set status = 'confirmed' if not already
UPDATE transactions
SET status = 'confirmed'
WHERE deal_id = @deal_id
  AND status != 'confirmed';

-- Fix 4: Set payment_date from payment_time if NULL
UPDATE transactions
SET payment_date = DATE(payment_time)
WHERE deal_id = @deal_id
  AND payment_date IS NULL
  AND payment_time IS NOT NULL;

-- Fix 5: Set payment_date from deal register_time if still NULL
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;

-- Step 3: Verify Fixes
SELECT 
    '=== AFTER FIXES ===' as section,
    t.id as trans_id,
    t.amount,
    t.payment_date,
    t.is_first_payment,
    t.status,
    CASE 
        WHEN t.is_first_payment = 1 AND t.status = 'confirmed' AND t.payment_date IS NOT NULL
        THEN '✅ FIXED - Will show in KPI'
        ELSE '❌ STILL HAS ISSUES'
    END as status_after_fix
FROM transactions t
WHERE t.deal_id = @deal_id
ORDER BY t.payment_date, t.payment_time;

-- Step 4: Final KPI Check
SELECT 
    '=== FINAL KPI CHECK ===' as section,
    'Will this deal show in Sales KPI?' 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 d.payable_amount > 0
        ) 
        THEN '✅ YES - Deal will show in KPI dashboard'
        ELSE '❌ NO - Still has issues (check above)'
    END as result;