<?php
/**
 * Test script to verify Performance Dashboard Data Integrity
 * 
 * Issues to verify:
 * 1. "مانده حساب" shows 2 deals but drill-down shows 1 deal
 * 2. نرخ تبدیل shows 32 deals but sum of (فروش کل 7 + بدون پرداخت 18 + ناموفق 3) = 28, missing 4 deals
 * 
 * Date range: 1404/11/11 to 1404/11/28 (Jalali) = 2026-02-01 to 2026-02-18 Gregorian
 * User: شایسته (didar_user_id: 75f9d286-0d05-43e0-a6ed-9fdb8ad89a0f)
 */

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

use App\Database\Connection;
use App\Utils\Logger;

echo "=== Performance Dashboard Data Integrity Test ===\n\n";

// Get database connection
$db = Connection::getInstance();
$pdo = $db->getPdo();

// User شایسته
$shaystehDidarId = '75f9d286-0d05-43e0-a6ed-9fdb8ad89a0f';

// Date range: 1404/11/11 to 1404/11/28 Jalali
// Convert Jalali to Gregorian: 1404/11/11 = 2026-01-31, 1404/11/28 = 2026-02-17
// But since backup is from 2025, let's check what dates are actually in the database
// Let me check the actual date range in the backup first

echo "Step 1: Finding user شایسته\n";
$stmt = $pdo->prepare("SELECT * FROM users WHERE didar_user_id = ? OR display_name LIKE '%شایسته%'");
$stmt->execute([$shaystehDidarId]);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "Users found:\n";
foreach ($users as $u) {
    echo "  - ID: {$u['id']}, DidarID: {$u['didar_user_id']}, Name: {$u['display_name']}\n";
}
echo "\n";

// Now let's check the actual deals for شایسته
echo "Step 2: Checking deals for شایسته\n";
$stmt = $pdo->prepare("
    SELECT 
        d.id,
        d.didar_deal_id,
        d.title,
        d.status,
        d.payable_amount,
        d.register_time,
        d.is_legacy_didar,
        d.pipeline_stage
    FROM deals d
    WHERE d.owner_didar_id = ?
    ORDER BY d.register_time DESC
");
$stmt->execute([$shaystehDidarId]);
$deals = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "Total deals for شایسته: " . count($deals) . "\n\n";

// Check transactions for these deals
echo "Step 3: Checking transactions for شایسته's deals\n";
$dealIds = array_column($deals, 'id');
if (!empty($dealIds)) {
    $placeholders = implode(',', array_fill(0, count($dealIds), '?'));
    $stmt = $pdo->prepare("
        SELECT 
            t.id,
            t.deal_id,
            t.amount,
            t.payment_date,
            t.method,
            t.status
        FROM transactions t
        WHERE t.deal_id IN ($placeholders)
        ORDER BY t.payment_date DESC
    ");
    $stmt->execute($dealIds);
    $transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "Total transactions: " . count($transactions) . "\n\n";
}

// Now let's analyze the KPI calculations
echo "=== Analyzing KPI Calculations ===\n\n";

// Build the unified payments SQL (similar to KpiRepository)
$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
";

// 1. OUTSTANDING BALANCE (مانده حساب)
echo "=== Issue 1: OUTSTANDING BALANCE (مانده حساب) ===\n";

$outstandingSql = "
    SELECT
        d.id,
        d.title,
        d.status,
        d.payable_amount,
        COALESCE(tp.total_paid, 0) as total_paid,
        (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
";

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

echo "Outstanding deals for شایسته:\n";
echo "Count: " . count($outstandingDeals) . "\n";
foreach ($outstandingDeals as $d) {
    echo "  - Deal ID: {$d['id']}, Title: {$d['title']}\n";
    echo "    Payable: {$d['payable_amount']}, Paid: {$d['total_paid']}, Outstanding: {$d['outstanding_amount']}\n";
}
echo "\n";

// 2. DEAL CONVERSION RATE (نرخ تبدیل معاملات)
echo "=== Issue 2: DEAL CONVERSION RATE (نرخ تبدیل معاملات) ===\n";

$dealConversionSql = "
    SELECT
        d.id,
        d.title,
        d.status,
        d.payable_amount,
        COALESCE(tp.total_paid, 0) as total_paid,
        CASE WHEN COALESCE(tp.total_paid, 0) > 0 THEN 'converted' ELSE 'not_converted' END as conversion_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
";

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

$totalDeals = count($allDealsStatus);
$convertedDeals = 0;
$notConvertedDeals = 0;
$wonDeals = 0;
$lostDeals = 0;
$pendingDeals = 0;
$withPaymentDeals = 0;
$withoutPaymentDeals = 0;

foreach ($allDealsStatus as $d) {
    if ($d['conversion_status'] === 'converted') {
        $convertedDeals++;
        $withPaymentDeals++;
    } else {
        $notConvertedDeals++;
    }
    
    if ($d['status'] === 'Won') {
        $wonDeals++;
        if ($d['conversion_status'] === 'converted') {
            // Won with payment
        }
    } elseif ($d['status'] === 'Lost') {
        $lostDeals++;
    } else {
        $pendingDeals++;
        if ($d['conversion_status'] === 'not_converted') {
            $withoutPaymentDeals++;
        }
    }
}

echo "Deal Conversion Analysis:\n";
echo "  Total deals: $totalDeals\n";
echo "  Converted (with payment): $convertedDeals\n";
echo "  Not converted (without payment): $notConvertedDeals\n";
echo "  ---\n";
echo "  Won deals: $wonDeals\n";
echo "  Lost deals: $lostDeals\n";
echo "  Pending deals: $pendingDeals\n";
echo "  ---\n";
echo "  With payment (فروش کل): $withPaymentDeals\n";
echo "  Without payment (بدون پرداخت): $withoutPaymentDeals\n";
echo "\n";

// Check the sum
$calculatedSum = $withPaymentDeals + $withoutPaymentDeals + $lostDeals;
echo "Verification:\n";
echo "  Sum of (فروش کل + بدون پرداخت + ناموفق): $calculatedSum\n";
echo "  Total deals: $totalDeals\n";
if ($calculatedSum != $totalDeals) {
    echo "  ⚠️ DISCREPANCY: Difference = " . ($totalDeals - $calculatedSum) . " deals\n";
}
echo "\n";

// Now let's find the missing deals
echo "=== Finding Missing/Double-Counted Deals ===\n";

$missingDealsSql = "
    SELECT
        d.id,
        d.title,
        d.status,
        d.payable_amount,
        COALESCE(tp.total_paid, 0) as total_paid,
        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($missingDealsSql);
$stmt->execute([$shaystehDidarId]);
$detailedDeals = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "Detailed breakdown:\n";
$categories = [
    'won_with_payment' => [],
    'won_without_payment' => [],
    'lost_with_payment' => [],
    'lost_without_payment' => [],
    'pending_with_payment' => [],
    'pending_without_payment' => [],
];

foreach ($detailedDeals as $d) {
    $statusGroup = $d['status_group'];
    $paymentStatus = $d['payment_status'];
    $key = $statusGroup . '_' . $paymentStatus;
    if (isset($categories[$key])) {
        $categories[$key][] = $d;
    }
}

foreach ($categories as $key => $deals) {
    echo "  $key: " . count($deals) . " deals\n";
    if (count($deals) <= 5) {
        foreach ($deals as $d) {
            echo "    - ID {$d['id']}: {$d['title']}\n";
        }
    }
}
echo "\n";

// Now let's check what the KPI actually shows
echo "=== Simulating KPI Calculation ===\n";

// GetSales (فروش کل)
$salesSql = "
    SELECT
        COUNT(DISTINCT d.id) as deal_count,
        SUM(d.payable_amount) as total_contract_amount,
        SUM(COALESCE(tp.total_paid, 0)) as total_collected_amount
    FROM deals d
    LEFT JOIN ({$paymentsByDealSql}) tp ON tp.deal_id = d.id
    WHERE COALESCE(tp.total_paid, 0) > 0
      AND d.owner_didar_id = ?
      AND COALESCE(d.is_legacy_didar, 0) = 0
";
$stmt = $pdo->prepare($salesSql);
$stmt->execute([$shaystehDidarId]);
$sales = $stmt->fetch(PDO::FETCH_ASSOC);
echo "فروش کل (Sales): " . ($sales['deal_count'] ?? 0) . " deals\n";

// GetDealsWithoutPayments (بدون پرداخت)
$withoutPaymentSql = "
    SELECT
        COUNT(DISTINCT d.id) as deal_count,
        SUM(d.payable_amount) as total_potential_amount
    FROM deals d
    LEFT JOIN ({$paymentsByDealSql}) tp ON tp.deal_id = d.id
    WHERE d.status NOT IN ('Won', 'Lost')
      AND d.payable_amount IS NOT NULL
      AND d.payable_amount > 0
      AND (tp.total_paid IS NULL OR tp.total_paid = 0)
      AND d.owner_didar_id = ?
      AND COALESCE(d.is_legacy_didar, 0) = 0
";
$stmt = $pdo->prepare($withoutPaymentSql);
$stmt->execute([$shaystehDidarId]);
$withoutPayment = $stmt->fetch(PDO::FETCH_ASSOC);
echo "بدون پرداخت (Without Payment): " . ($withoutPayment['deal_count'] ?? 0) . " deals\n";

// GetLostSales (ناموفق)
$lostSql = "
    SELECT
        COUNT(*) as total_lost,
        SUM(d.payable_amount) as total_amount
    FROM deals d
    WHERE d.status = 'Lost'
      AND d.lost_time IS NOT NULL
      AND d.owner_didar_id = ?
      AND COALESCE(d.is_legacy_didar, 0) = 0
";
$stmt = $pdo->prepare($lostSql);
$stmt->execute([$shaystehDidarId]);
$lost = $stmt->fetch(PDO::FETCH_ASSOC);
echo "فروش ناموفق (Lost): " . ($lost['total_lost'] ?? 0) . " deals\n";

// GetDealConversionRate (نرخ تبدیل)
$conversionSql = "
    SELECT
        COUNT(DISTINCT d.id) as total_deals,
        COUNT(DISTINCT CASE WHEN COALESCE(tp.total_paid, 0) > 0 THEN d.id END) as converted_deals
    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
";
$stmt = $pdo->prepare($conversionSql);
$stmt->execute([$shaystehDidarId]);
$conversion = $stmt->fetch(PDO::FETCH_ASSOC);
echo "نرخ تبدیل (Total deals for conversion rate): " . ($conversion['total_deals'] ?? 0) . " deals\n";

echo "\n=== Summary ===\n";
$salesCount = (int)($sales['deal_count'] ?? 0);
$withoutPaymentCount = (int)($withoutPayment['deal_count'] ?? 0);
$lostCount = (int)($lost['total_lost'] ?? 0);
$conversionTotal = (int)($conversion['total_deals'] ?? 0);

echo "فروش کل: $salesCount\n";
echo "بدون پرداخت: $withoutPaymentCount\n";
echo "ناموفق: $lostCount\n";
echo "Sum: " . ($salesCount + $withoutPaymentCount + $lostCount) . "\n";
echo "نرخ تبدیل total: $conversionTotal\n";

if (($salesCount + $withoutPaymentCount + $lostCount) != $conversionTotal) {
    echo "\n⚠️ DISCREPANCY DETECTED!\n";
    echo "The sum does not match the conversion rate total.\n";
    echo "Missing deals: " . ($conversionTotal - $salesCount - $withoutPaymentCount - $lostCount) . "\n";
}

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