<?php
/**
 * Deep Dive Analysis - Performance Dashboard Data Integrity
 * 
 * Finding the exact discrepancies for:
 * 1. Outstanding Balance showing 2 deals but drill-down shows 1
 * 2. Conversion rate sum mismatch
 */

require_once __DIR__ . '/../../vendor/autoload.php';
require_once __DIR__ . '/../../app/bootstrap.php';

use App\Database\Connection;

echo "=== Deep Dive: Performance Dashboard Analysis ===\n\n";

$db = Connection::getInstance();
$pdo = $db->getPdo();

$shaystehDidarId = '75f9d286-0d05-43e0-a6ed-9fdb8ad89a0f';

// Build unified payment queries
$paymentsByDealSql = "
    SELECT 
        t.deal_id,
        SUM(t.amount) as total_paid,
        MIN(t.payment_date) as first_payment_date
    FROM transactions t
    WHERE t.status = 'confirmed'
    GROUP BY t.deal_id
";

$paymentsByDealDateSql = "
    SELECT 
        t.deal_id,
        t.payment_date,
        SUM(t.amount) as amount
    FROM transactions t
    WHERE t.status = 'confirmed'
    GROUP BY t.deal_id, t.payment_date
";

echo "=== Finding ALL missing deals in the sum ===\n\n";

// Let's categorize ALL deals to find the gap
$allDealsSql = "
    SELECT
        d.id,
        d.title,
        d.status,
        d.payable_amount,
        COALESCE(tp.total_paid, 0) as total_paid,
        d.lost_time,
        d.won_time,
        CASE 
            WHEN d.status = 'Won' THEN 'won'
            WHEN d.status = 'Lost' THEN 'lost'
            ELSE 'pending'
        END as status_group,
        CASE WHEN COALESCE(tp.total_paid, 0) > 0 THEN 'with_payment' ELSE 'without_payment' END as payment_status
    FROM deals d
    LEFT JOIN ({$paymentsByDealSql}) tp ON tp.deal_id = d.id
    WHERE d.owner_didar_id = ?
      AND COALESCE(d.is_legacy_didar, 0) = 0
    ORDER BY d.status, tp.total_paid DESC
";

$stmt = $pdo->prepare($allDealsSql);
$stmt->execute([$shaystehDidarId]);
$allDeals = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "Total non-legacy deals: " . count($allDeals) . "\n\n";

// Categorize each deal
$categories = [
    'sales_with_payment' => [],           // Included in فروش کل
    'sales_won_with_payment' => [],       // Won with payment
    'without_payment_pending_payable' => [], // Included in بدون پرداخت
    'without_payment_pending_no_payable' => [], // NOT included - this is a gap!
    'lost_with_lost_time' => [],          // Included in ناموفق
    'lost_without_lost_time' => [],       // NOT included - this is a gap!
    'won_without_payment' => [],          // NOT included - this is a gap!
];

foreach ($allDeals as $d) {
    $statusGroup = $d['status_group'];
    $hasPayment = $d['total_paid'] > 0;
    $hasPayable = $d['payable_amount'] > 0;
    $hasLostTime = !empty($d['lost_time']);
    
    if ($hasPayment) {
        $categories['sales_with_payment'][] = $d;
        if ($statusGroup === 'won') {
            $categories['sales_won_with_payment'][] = $d;
        }
    } else {
        // No payment
        if ($statusGroup === 'pending') {
            // Now include ALL pending deals without payment (regardless of payable_amount)
            $categories['without_payment_pending_payable'][] = $d;
        } elseif ($statusGroup === 'lost') {
            // Now include ALL lost deals without payment (regardless of lost_time)
            $categories['lost_with_lost_time'][] = $d;
        } elseif ($statusGroup === 'won') {
            $categories['won_without_payment'][] = $d;
        }
    }
}

echo "=== Category Breakdown ===\n\n";
foreach ($categories as $key => $deals) {
    echo "$key: " . count($deals) . " deals\n";
}

echo "\n=== Verification ===\n";
$includedInSum = count($categories['sales_with_payment']) 
    + count($categories['without_payment_pending_payable']) 
    + count($categories['lost_with_lost_time']);
$notIncludedInSum = count($categories['without_payment_pending_no_payable'])
    + count($categories['lost_without_lost_time'])
    + count($categories['won_without_payment']);
    
echo "Included in sum (فروش کل + بدون پرداخت + ناموفق): $includedInSum\n";
echo "NOT included in sum: $notIncludedInSum\n";
echo "Total: " . ($includedInSum + $notIncludedInSum) . "\n\n";

echo "=== Gap Analysis ===\n";
echo "Deals NOT included in the sum:\n";
echo "  - Pending without payable_amount: " . count($categories['without_payment_pending_no_payable']) . "\n";
echo "  - Lost without lost_time: " . count($categories['lost_without_lost_time']) . "\n";
echo "  - Won without payment: " . count($categories['won_without_payment']) . "\n\n";

if (count($categories['lost_without_lost_time']) > 0) {
    echo "Lost deals WITHOUT lost_time (NOT included in ناموفق):\n";
    foreach ($categories['lost_without_lost_time'] as $d) {
        echo "  - ID {$d['id']}: {$d['title']}\n";
        echo "    Status: {$d['status']}, Lost Time: " . ($d['lost_time'] ?? 'NULL') . "\n";
    }
    echo "\n";
}

if (count($categories['without_payment_pending_no_payable']) > 0) {
    echo "Pending deals WITHOUT payable_amount (NOT included in بدون پرداخت):\n";
    foreach ($categories['without_payment_pending_no_payable'] as $d) {
        echo "  - ID {$d['id']}: {$d['title']}\n";
        echo "    Status: {$d['status']}, Payable: {$d['payable_amount']}\n";
    }
    echo "\n";
}

if (count($categories['won_without_payment']) > 0) {
    echo "Won deals WITHOUT payment (NOT included anywhere):\n";
    foreach ($categories['won_without_payment'] as $d) {
        echo "  - ID {$d['id']}: {$d['title']}\n";
        echo "    Status: {$d['status']}, Payable: {$d['payable_amount']}, Paid: {$d['total_paid']}\n";
    }
    echo "\n";
}

// Now analyze Issue 1: Outstanding Balance with DATE FILTER
echo "=== Issue 1: Outstanding Balance with Date Filter ===\n\n";

// The user mentioned date range 1404/11/11 to 1404/11/28 Jalali
// This converts to approximately 2026-01-31 to 2026-02-17 Gregorian
// BUT the backup is from 2025, so let's check what dates are in the database

echo "First, let's check the date range of deals:\n";
$dateCheckSql = "
    SELECT 
        MIN(register_time) as min_date,
        MAX(register_time) as max_date
    FROM deals
    WHERE owner_didar_id = ?
      AND COALESCE(is_legacy_didar, 0) = 0
";
$stmt = $pdo->prepare($dateCheckSql);
$stmt->execute([$shaystehDidarId]);
$dateRange = $stmt->fetch(PDO::FETCH_ASSOC);
echo "Deal date range: {$dateRange['min_date']} to {$dateRange['max_date']}\n\n";

// Let's check outstanding deals with their registration dates
echo "Outstanding deals with payment dates:\n";
$outstandingWithDatesSql = "
    SELECT
        d.id,
        d.title,
        d.status,
        d.payable_amount,
        COALESCE(tp.total_paid, 0) as total_paid,
        tp.first_payment_date,
        d.register_time,
        (d.payable_amount - COALESCE(tp.total_paid, 0)) as outstanding_amount
    FROM deals d
    LEFT JOIN ({$paymentsByDealSql}) tp ON tp.deal_id = d.id
    WHERE d.payable_amount IS NOT NULL
      AND d.payable_amount > 0
      AND COALESCE(tp.total_paid, 0) > 0
      AND COALESCE(tp.total_paid, 0) < d.payable_amount
      AND d.owner_didar_id = ?
      AND COALESCE(d.is_legacy_didar, 0) = 0
    ORDER BY tp.first_payment_date DESC
    LIMIT 10
";
$stmt = $pdo->prepare($outstandingWithDatesSql);
$stmt->execute([$shaystehDidarId]);
$outstandingWithDates = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($outstandingWithDates as $d) {
    echo "  ID {$d['id']}: {$d['title']}\n";
    echo "    Register: {$d['register_time']}, First Payment: {$d['first_payment_date']}\n";
    echo "    Payable: {$d['payable_amount']}, Paid: {$d['total_paid']}, Outstanding: {$d['outstanding_amount']}\n\n";
}

// Check the getPaymentWindowDealClassification logic
echo "=== Testing getPaymentWindowDealClassification logic ===\n\n";

// This method filters by payment_date, not register_time
// So deals are selected if they have a payment in the date range

// Test with a specific date range (let's use 2025-12-01 to 2025-12-31 as example)
$testDateFrom = '2025-12-01';
$testDateTo = '2025-12-31';

echo "Testing with date range: $testDateFrom to $testDateTo\n";
echo "(Filtering by PAYMENT DATE, not register time)\n\n";

$windowClassificationSql = "
    SELECT
        d.id,
        d.title,
        d.status,
        d.payable_amount,
        COALESCE(tp.total_paid, 0) as total_paid,
        pay.payment_date,
        CASE
            WHEN COALESCE(d.payable_amount, 0) > 0 AND COALESCE(tp.total_paid, 0) < COALESCE(d.payable_amount, 0)
            THEN 'outstanding'
            ELSE 'settled'
        END as classification
    FROM deals d
    INNER JOIN (
        SELECT DISTINCT pbd.deal_id
        FROM ({$paymentsByDealDateSql}) pbd
        WHERE pbd.payment_date >= ?
          AND pbd.payment_date <= ?
    ) pw ON pw.deal_id = d.id
    LEFT JOIN ({$paymentsByDealSql}) tp ON tp.deal_id = d.id
    LEFT JOIN ({$paymentsByDealDateSql}) pay ON pay.deal_id = d.id
    WHERE d.owner_didar_id = ?
      AND COALESCE(d.is_legacy_didar, 0) = 0
    ORDER BY d.id, pay.payment_date
";

$stmt = $pdo->prepare($windowClassificationSql);
$stmt->execute([$testDateFrom, $testDateTo, $shaystehDidarId]);
$windowDeals = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Group by deal_id
$groupedDeals = [];
foreach ($windowDeals as $d) {
    if (!isset($groupedDeals[$d['id']])) {
        $groupedDeals[$d['id']] = [
            'id' => $d['id'],
            'title' => $d['title'],
            'status' => $d['status'],
            'payable_amount' => $d['payable_amount'],
            'total_paid' => $d['total_paid'],
            'classification' => $d['classification'],
            'payment_dates' => []
        ];
    }
    if ($d['payment_date']) {
        $groupedDeals[$d['id']]['payment_dates'][] = $d['payment_date'];
    }
}

echo "Deals with payments in date range $testDateFrom to $testDateTo:\n";
echo "Total unique deals: " . count($groupedDeals) . "\n\n";

$outstandingCount = 0;
$settledCount = 0;
foreach ($groupedDeals as $d) {
    if ($d['classification'] === 'outstanding') {
        $outstandingCount++;
    } else {
        $settledCount++;
    }
}
echo "Outstanding: $outstandingCount\n";
echo "Settled: $settledCount\n";

echo "\n=== ROOT CAUSE ANALYSIS ===\n\n";

echo "Issue 2: Conversion Rate Sum Mismatch\n";
echo "--------------------------------------\n";
echo "The issue is that different KPI metrics use DIFFERENT filtering criteria:\n\n";
echo "1. فروش کل (Sales): Counts deals WITH payment (any status)\n";
echo "2. بدون پرداخت (Without Payment): Counts PENDING deals with payable_amount > 0 and NO payment\n";
echo "3. ناموفق (Lost): Counts LOST deals with lost_time IS NOT NULL\n";
echo "4. نرخ تبدیل (Conversion Rate): Counts ALL deals\n\n";
echo "MISSING categories:\n";
echo "- Lost deals WITHOUT lost_time: " . count($categories['lost_without_lost_time']) . "\n";
echo "- Pending deals with payable_amount = 0 or NULL: " . count($categories['without_payment_pending_no_payable']) . "\n";
echo "- Won deals without payment: " . count($categories['won_without_payment']) . "\n";
echo "\nThese deals are counted in conversion rate but NOT in the sum!\n";

echo "\n=== Test Complete ===\n";
