-- ============================================
-- KPI Debug: Owner Filter & Date Range Issue
-- Date: January 4, 2025
-- Purpose: Find why new deal doesn't show when owner filter is applied
-- ============================================

-- Step 1: Find your new deal (replace with your actual deal didar_deal_id)
SET @deal_id = 'local_695a4eb14b2ab1.16113397';
SET @owner_name = 'حمیدرضا مکرمی';

-- Step 2: Check deal owner information
SELECT 
    '=== DEAL OWNER INFO ===' as section,
    d.id as deal_id,
    d.didar_deal_id,
    d.title,
    d.owner_didar_id as deal_owner_id,
    d.register_time as deal_created,
    DATE(d.register_time) as deal_date,
    u.didar_user_id as user_didar_id,
    u.first_name,
    u.last_name,
    CONCAT(u.first_name, ' ', u.last_name) as full_name
FROM deals d
LEFT JOIN users u ON d.owner_didar_id = u.didar_user_id
WHERE d.didar_deal_id = @deal_id
   OR d.id = (SELECT MAX(id) FROM deals ORDER BY register_time DESC LIMIT 1);

-- Step 3: Check all users with similar name (to find owner ID mismatch)
SELECT 
    '=== USERS WITH SIMILAR NAME ===' as section,
    u.id as user_id,
    u.didar_user_id,
    u.first_name,
    u.last_name,
    CONCAT(u.first_name, ' ', u.last_name) as full_name,
    u.is_active,
    'This is the ID used in KPI filter dropdown' as note
FROM users u
WHERE u.first_name LIKE '%حمید%' 
   OR u.last_name LIKE '%مکرمی%'
   OR CONCAT(u.first_name, ' ', u.last_name) LIKE '%حمیدرضا%'
ORDER BY u.id DESC;

-- Step 4: Check transactions for this deal
SELECT 
    '=== TRANSACTIONS FOR DEAL ===' as section,
    t.id as trans_id,
    t.deal_id,
    t.amount,
    t.payment_date,
    t.payment_time,
    t.is_first_payment,
    t.status,
    CASE 
        WHEN t.payment_date IS NULL THEN 'NULL - WILL EXCLUDE FROM KPI'
        WHEN t.payment_date < '2025-12-20' THEN 'OUT OF RANGE - Before filter'
        WHEN t.payment_date > '2025-12-31' THEN 'OUT OF RANGE - After filter'
        ELSE 'IN RANGE'
    END as date_status,
    'Persian 1404/10/01-30 = Gregorian 2025-12-22 to 2026-01-20' as date_note
FROM transactions t
INNER JOIN deals d ON t.deal_id = d.id
WHERE d.didar_deal_id = @deal_id
   OR d.id = (SELECT MAX(id) FROM deals ORDER BY register_time DESC LIMIT 1)
ORDER BY t.payment_date, t.payment_time;

-- Step 5: Check if deal would be counted with owner filter
SELECT 
    '=== OWNER FILTER CHECK ===' as section,
    'Deal Owner ID' as deal_owner,
    'Filter Owner ID' as filter_owner,
    'Match?' as matches,
    CASE 
        WHEN d.owner_didar_id = u.didar_user_id THEN 'YES - Will be counted'
        WHEN d.owner_didar_id IS NULL THEN 'NO - Deal has no owner'
        WHEN d.owner_didar_id != u.didar_user_id THEN 'NO - Owner mismatch'
        ELSE 'UNKNOWN'
    END as result
FROM deals d
LEFT JOIN users u ON CONCAT(u.first_name, ' ', u.last_name) LIKE CONCAT('%', @owner_name, '%')
WHERE d.didar_deal_id = @deal_id
   OR d.id = (SELECT MAX(id) FROM deals ORDER BY register_time DESC LIMIT 1)
LIMIT 1;

-- Step 6: Date range conversion check
SELECT 
    '=== DATE RANGE CONVERSION ===' as section,
    'Persian Date' as persian_date,
    'Gregorian Date' as gregorian_date,
    'Deal Date' as deal_date,
    'In Range?' as in_range
FROM (
    SELECT 
        '1404/10/01' as persian_date,
        '2025-12-22' as gregorian_date,
        DATE(d.register_time) as deal_date,
        CASE 
            WHEN DATE(d.register_time) >= '2025-12-22' AND DATE(d.register_time) <= '2026-01-20' 
            THEN 'YES'
            ELSE 'NO'
        END as in_range
    FROM deals d
    WHERE d.didar_deal_id = @deal_id
       OR d.id = (SELECT MAX(id) FROM deals ORDER BY register_time DESC LIMIT 1)
    LIMIT 1
) date_check;

-- Step 7: Complete KPI inclusion check
SELECT 
    '=== COMPLETE KPI CHECK ===' as section,
    'Has payable_amount > 0?' as has_payable,
    'Has confirmed first payment?' as has_first_payment,
    'Payment date in range?' as payment_in_range,
    'Owner matches filter?' as owner_matches,
    'Will show in Sales KPI?' as will_show
FROM (
    SELECT 
        CASE WHEN d.payable_amount > 0 THEN 'YES' ELSE 'NO' END as has_payable,
        CASE WHEN EXISTS (
            SELECT 1 FROM transactions t 
            WHERE t.deal_id = d.id 
              AND t.is_first_payment = 1 
              AND t.status = 'confirmed'
        ) THEN 'YES' ELSE 'NO' END as has_first_payment,
        CASE WHEN EXISTS (
            SELECT 1 FROM transactions t 
            WHERE t.deal_id = d.id 
              AND t.payment_date >= '2025-12-22' 
              AND t.payment_date <= '2026-01-20'
        ) THEN 'YES' ELSE 'NO' END as payment_in_range,
        CASE WHEN d.owner_didar_id = (
            SELECT didar_user_id FROM users 
            WHERE CONCAT(first_name, ' ', last_name) LIKE '%حمیدرضا مکرمی%'
            LIMIT 1
        ) THEN 'YES' ELSE 'NO' END as owner_matches,
        CASE 
            WHEN d.payable_amount > 0 
             AND EXISTS (SELECT 1 FROM transactions t WHERE t.deal_id = d.id AND t.is_first_payment = 1 AND t.status = 'confirmed')
             AND EXISTS (SELECT 1 FROM transactions t WHERE t.deal_id = d.id AND t.payment_date >= '2025-12-22' AND t.payment_date <= '2026-01-20')
             AND d.owner_didar_id = (SELECT didar_user_id FROM users WHERE CONCAT(first_name, ' ', last_name) LIKE '%حمیدرضا مکرمی%' LIMIT 1)
            THEN 'YES - Will show'
            ELSE 'NO - Missing requirements'
        END as will_show
    FROM deals d
    WHERE d.didar_deal_id = @deal_id
       OR d.id = (SELECT MAX(id) FROM deals ORDER BY register_time DESC LIMIT 1)
    LIMIT 1
) kpi_check;