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

use App\Database\Connection;

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

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

echo "=== Testing Deals Without Payment for شایسته ===\n";
echo "Date Range: $dateFrom to $dateTo\n\n";

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

// Card calculation (getDealsWithoutPayments)
$cardSql = "
    SELECT
        COUNT(DISTINCT d.id) as deal_count
    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($cardSql);
$stmt->execute([$ownerDidarId, $dateFrom, $dateTo]);
$card = $stmt->fetch(PDO::FETCH_ASSOC);
echo "Card (getDealsWithoutPayments): " . ($card['deal_count'] ?? 0) . " deals\n\n";

// Drilldown base
$drilldownBaseSql = "
    SELECT
        d.id as deal_id,
        d.title,
        d.status,
        CASE
            WHEN d.status = 'Won' THEN 'won'
            WHEN d.status = 'Lost' THEN 'lost'
            ELSE 'pending'
        END as deal_status_group,
        CASE WHEN COALESCE(pay.total_paid, 0) > 0 THEN 1 ELSE 0 END as has_payment
    FROM deals d
    LEFT JOIN ({$paymentsByDealSql}) pay ON pay.deal_id = d.id
    WHERE d.owner_didar_id = ?
      AND DATE(COALESCE(d.register_time, d.created_at)) BETWEEN ? AND ?
      AND COALESCE(d.is_legacy_didar, 0) = 0
";

// Drilldown calculation - OLD (with has_payment = 0 only)
$oldDrilldownSql = "
    SELECT COUNT(*) as deal_count
    FROM ({$drilldownBaseSql}) b
    WHERE b.has_payment = 0
";
$stmt = $pdo->prepare($oldDrilldownSql);
$stmt->execute([$ownerDidarId, $dateFrom, $dateTo]);
$old = $stmt->fetch(PDO::FETCH_ASSOC);
echo "Drilldown OLD (has_payment = 0): " . ($old['deal_count'] ?? 0) . " deals\n\n";

// Drilldown calculation - NEW (with has_payment = 0 AND deal_status_group = 'pending')
$newDrilldownSql = "
    SELECT COUNT(*) as deal_count
    FROM ({$drilldownBaseSql}) b
    WHERE b.has_payment = 0 AND b.deal_status_group = 'pending'
";
$stmt = $pdo->prepare($newDrilldownSql);
$stmt->execute([$ownerDidarId, $dateFrom, $dateTo]);
$new = $stmt->fetch(PDO::FETCH_ASSOC);
echo "Drilldown NEW (has_payment = 0 AND status = 'pending'): " . ($new['deal_count'] ?? 0) . " deals\n\n";

// Show the difference
$diffSql = "
    SELECT
        b.deal_id,
        b.title,
        b.status,
        b.deal_status_group,
        b.has_payment
    FROM ({$drilldownBaseSql}) b
    WHERE b.has_payment = 0 AND b.deal_status_group <> 'pending'
";
$stmt = $pdo->prepare($diffSql);
$stmt->execute([$ownerDidarId, $dateFrom, $dateTo]);
$diff = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "Deals in OLD but not in NEW (Lost deals with no payment):\n";
foreach ($diff as $row) {
    echo "  Deal ID: " . $row['deal_id'] . ", Status: " . $row['deal_status_group'] . " (" . $row['status'] . "), Title: " . substr($row['title'], 0, 50) . "\n";
}
echo "\nTotal extra deals in OLD: " . count($diff) . "\n";

// Verify match
echo "\n=== Verification ===\n";
if ($card['deal_count'] == $new['deal_count']) {
    echo "SUCCESS: Card and NEW drilldown match! (" . $card['deal_count'] . " deals)\n";
} else {
    echo "ERROR: Card (" . $card['deal_count'] . ") != NEW drilldown (" . $new['deal_count'] . ")\n";
}
