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

use App\Database\Connection;
use App\Repositories\KpiRepository;

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

$ownerDidarId = '75f9d286-0d05-43e0-a6ed-9fdb8ad89a0f';
$dateFrom = '2026-02-01';
$dateTo = '2026-02-18';

echo "=== Comprehensive Test for Deals Without Payment ===\n";
echo "User: شایسته ({$ownerDidarId})\n";
echo "Date Range: {$dateFrom} to {$dateTo}\n\n";

$filters = [
    'date_from' => $dateFrom,
    'date_to' => $dateTo,
    'owner_didar_id' => $ownerDidarId,
    'owner_didar_ids_csv' => $ownerDidarId,
];

$kpiRepo = new KpiRepository($db);

// 1. Test getDealsWithoutPayments (card calculation)
$withoutPayments = $kpiRepo->getDealsWithoutPayments($filters);
echo "1. Card (getDealsWithoutPayments): " . ($withoutPayments['deal_count'] ?? 0) . " deals\n";

// 2. Test via getPerformanceDashboardData
$dashboardData = $kpiRepo->getPerformanceDashboardData($filters);
echo "2. Dashboard without_payment_count: " . ($dashboardData['summary_cards']['without_payment_count'] ?? 0) . " deals\n";

// 3. Test advanced tables
$advancedTables = $dashboardData['tabs']['sales_deals']['deal_status'] ?? [];
$totalWithoutSales = 0;
foreach ($advancedTables as $row) {
    $totalWithoutSales += (int)($row['deal_without_sales_total'] ?? 0);
}
echo "3. deal_status table sum (deal_without_sales_total): {$totalWithoutSales} deals\n";

// 4. Direct SQL verification
$paymentsByDealSql = "
    SELECT deal_id, SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) as total_paid
    FROM transactions
    GROUP BY deal_id
";

$directSql = "
    SELECT COUNT(*) as cnt
    FROM deals d
    LEFT JOIN ({$paymentsByDealSql}) tp ON tp.deal_id = d.id
    WHERE d.status NOT IN ('Won', 'Lost')
      AND (tp.total_paid IS NULL OR tp.total_paid = 0)
      AND d.owner_didar_id = ?
      AND DATE(COALESCE(d.register_time, d.created_at)) BETWEEN ? AND ?
      AND COALESCE(d.is_legacy_didar, 0) = 0
";
$stmt = $pdo->prepare($directSql);
$stmt->execute([$ownerDidarId, $dateFrom, $dateTo]);
$direct = $stmt->fetch(PDO::FETCH_ASSOC);
echo "4. Direct SQL verification: " . ($direct['cnt'] ?? 0) . " deals\n";

// 5. Test drilldown via getDealBasedDrilldown (reflection since it's private)
$reflection = new ReflectionMethod($kpiRepo, 'getDealBasedDrilldown');
$reflection->setAccessible(true);
$drilldown = $reflection->invoke($kpiRepo, $filters, 'deal_status', 'deal_without_sales_total', '', $ownerDidarId, '', '', '');
echo "5. Drilldown (deal_without_sales_total): " . count($drilldown['deals']) . " deals, " . count($drilldown['leads']) . " leads\n";

// Summary
echo "\n=== Summary ===\n";
$cardCount = (int)($withoutPayments['deal_count'] ?? 0);
$allMatch = (
    $cardCount == ($dashboardData['summary_cards']['without_payment_count'] ?? 0) &&
    $cardCount == $totalWithoutSales &&
    $cardCount == count($drilldown['deals'])
);
if ($allMatch) {
    echo "SUCCESS: All calculations match at {$cardCount} deals!\n";
} else {
    echo "ERROR: Mismatch detected!\n";
    echo "  Card: {$cardCount}\n";
    echo "  Dashboard: " . ($dashboardData['summary_cards']['without_payment_count'] ?? 0) . "\n";
    echo "  Table sum: {$totalWithoutSales}\n";
    echo "  Drilldown: " . count($drilldown['deals']) . "\n";
}
