-- ============================================
-- Complete Fix for Deal 54662
-- Date: January 4, 2025
-- Purpose: Fix all issues preventing deal from showing in KPI
-- ============================================

SET @deal_id = 54662;
SET @deal_didar_id = 'local_695a6f482e23a8.38369213';

-- Step 1: Check if transactions exist
SELECT 
    '=== TRANSACTION CHECK ===' as section,
    COUNT(*) as transaction_count,
    CASE 
        WHEN COUNT(*) = 0 THEN '❌ NO TRANSACTIONS - This is the problem!'
        ELSE CONCAT('✅ ', COUNT(*), ' transactions found')
    END as status
FROM transactions
WHERE deal_id = @deal_id;

-- Step 2: If no transactions, create one from deal data
-- This will fix the immediate issue
INSERT INTO transactions (
    deal_id, 
    amount, 
    payment_date, 
    payment_time, 
    is_first_payment, 
    status, 
    created_at
)
SELECT 
    d.id,
    d.payable_amount,
    DATE(d.register_time),
    d.register_time,
    1,
    'confirmed',
    NOW()
FROM deals d
WHERE d.id = @deal_id
  AND NOT EXISTS (
      SELECT 1 FROM transactions t WHERE t.deal_id = d.id
  );

-- Step 3: Fix existing transactions if they exist
-- Fix NULL payment_date
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 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;

-- Fix is_first_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;

-- 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 status
UPDATE transactions
SET status = 'confirmed'
WHERE deal_id = @deal_id
  AND (status != 'confirmed' OR status IS NULL);

-- Step 4: Verify all fixes
SELECT 
    '=== VERIFICATION ===' as section,
    t.id as trans_id,
    t.amount,
    t.payment_date,
    t.is_first_payment,
    t.status,
    CASE 
        WHEN t.payment_date IS NOT NULL 
         AND t.is_first_payment = 1 
         AND t.status = 'confirmed'
         AND t.payment_date >= '2025-12-22'
         AND t.payment_date <= '2026-01-20'
        THEN '✅ READY FOR KPI'
        ELSE '❌ STILL HAS ISSUES'
    END as kpi_status
FROM transactions t
WHERE t.deal_id = @deal_id
ORDER BY t.payment_date, t.payment_time;

-- Step 5: 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 t.payment_date >= '2025-12-22'
              AND t.payment_date <= '2026-01-20'
              AND d.owner_didar_id = '6f307e57-ce8e-4ec1-bc7a-d17fd39001fa'
              AND d.payable_amount > 0
        ) 
        THEN '✅ YES - Deal will show in KPI dashboard'
        ELSE '❌ NO - Still has issues (check above)'
    END as result;