<?php

use App\Core\Auth;
use App\Core\Tenant;

if (!($auth instanceof Auth) || !$auth->can('reports.view')) {
    http_response_code(403);
    echo 'غير مسموح بالوصول إلى التقارير';
    return;
}

$tenantId = Tenant::id();
$tenantCond = ' AND o.tenant_id = ' . (int)$tenantId;
$tenantPat = ' AND p.tenant_id = ' . (int)$tenantId;
$tenantDoc = ' AND d.tenant_id = ' . (int)$tenantId;
$tenantTest = ' AND t.tenant_id = ' . (int)$tenantId;
$tenantBr = ' AND b.tenant_id = ' . (int)$tenantId;

$ordersByDay = [];
$revenueSummary = [];
$topTests = [];
$statsRows = [];
$statsTotals = [
    'total_amount' => 0,
    'discount_amount' => 0,
    'paid_amount' => 0,
    'remaining_amount' => 0,
    'patients_count' => 0
];
$doctorSummary = [];
$paymentMethodSummary = [];
$paymentStatusSummary = [];
$branchSummary = [];
$categorySummary = [];
$qualityStats = [
    'tat_total_completed' => 0,
    'tat_avg_hours' => null,
    'tat_within_24h' => 0,
    'tat_above_24h' => 0,
    'critical_total_results' => 0,
    'critical_count' => 0
];
$topCriticalTests = [];
$doctors = [];
$patients = [];
$testsList = [];
$fromDate = $_GET['from'] ?? date('Y-m-01');
$toDate = $_GET['to'] ?? date('Y-m-d');
$doctorId = isset($_GET['doctor_id']) ? (int)$_GET['doctor_id'] : 0;
$patientId = isset($_GET['patient_id']) ? (int)$_GET['patient_id'] : 0;
$testId = isset($_GET['test_id']) ? (int)$_GET['test_id'] : 0;
$paymentStatus = isset($_GET['payment_status']) ? (string)$_GET['payment_status'] : '';
$branchIdFilter = isset($_GET['branch_id']) ? (int)$_GET['branch_id'] : 0;
$export = $_GET['export'] ?? '';

try {
    $tenantOrd = '';
    try { $db->query('SELECT tenant_id FROM lab_orders LIMIT 1'); $tenantOrd = ' WHERE tenant_id = ' . (int)$tenantId; } catch (Throwable $e) {}
    $stmt = $db->query(
        "SELECT DATE(order_date) AS d, COUNT(*) AS c
         FROM lab_orders" . $tenantOrd . "
         GROUP BY DATE(order_date)
         ORDER BY d DESC
         LIMIT 14"
    );
    $ordersByDay = $stmt->fetchAll();

    $revenueByDay = [];
    try {
        $stmt = $db->query(
            "SELECT DATE(i.issue_date) AS d, COALESCE(SUM(i.paid_amount), 0) AS revenue
             FROM invoices i JOIN lab_orders o ON o.id = i.order_id
             WHERE o.tenant_id = " . (int)$tenantId . " AND i.issue_date >= DATE_SUB(CURDATE(), INTERVAL 14 DAY)
             GROUP BY DATE(i.issue_date) ORDER BY d DESC LIMIT 14"
        );
        $revenueByDay = $stmt->fetchAll();
    } catch (Throwable $e) {}

    $stmt = $db->query(
        "SELECT
             SUM(total_amount) AS total_amount,
             SUM(discount_amount) AS discount_amount,
             SUM(net_amount) AS net_amount
         FROM lab_orders" . $tenantOrd
    );
    $row = $stmt->fetch();
    if ($row) {
        $revenueSummary = $row;
    }

    $stmt = $db->query(
        "SELECT t.name, COUNT(*) AS c
         FROM order_items oi
         JOIN lab_orders o ON o.id = oi.order_id
         JOIN tests t ON t.id = oi.test_id
         WHERE o.tenant_id = " . (int)$tenantId . " AND t.tenant_id = " . (int)$tenantId . " "
         . "GROUP BY t.id, t.name
         ORDER BY c DESC
         LIMIT 10"
    );
    $topTests = $stmt->fetchAll();

    $stmtDoc = $db->prepare('SELECT id, full_name FROM doctors WHERE tenant_id = ? ORDER BY full_name ASC');
    $stmtDoc->execute([$tenantId]);
    $doctors = $stmtDoc->fetchAll();

    $stmtPat = $db->prepare('SELECT id, full_name FROM patients WHERE tenant_id = ? ORDER BY id DESC LIMIT 200');
    $stmtPat->execute([$tenantId]);
    $patients = $stmtPat->fetchAll();

    $stmtTest = $db->prepare('SELECT id, name FROM tests WHERE tenant_id = ? ORDER BY name ASC LIMIT 200');
    $stmtTest->execute([$tenantId]);
    $testsList = $stmtTest->fetchAll();

    $branchesList = [];
    try {
        $stmt = $db->prepare('SELECT id, name FROM branches WHERE is_active = 1 AND tenant_id = ? ORDER BY name');
        $stmt->execute([$tenantId]);
        $branchesList = $stmt->fetchAll();
    } catch (Throwable $e) {}

    $statsSql =
        "SELECT
            i.issue_date,
            p.full_name AS patient_name,
            d.full_name AS doctor_name,
            i.total_amount,
            i.discount_amount,
            i.net_amount,
            i.paid_amount,
            (i.net_amount - i.paid_amount) AS remaining_amount,
            o.patient_id,
            o.payment_status,
            o.branch_name,
            i.payment_method
         FROM invoices i
         JOIN lab_orders o ON o.id = i.order_id
         JOIN patients p ON p.id = o.patient_id
         LEFT JOIN doctors d ON d.id = o.doctor_id
         WHERE DATE(i.issue_date) BETWEEN :from AND :to AND o.tenant_id = :tid";

    $params = [
        'from' => $fromDate,
        'to' => $toDate,
        'tid' => $tenantId
    ];

    if ($doctorId > 0) {
        $statsSql .= " AND o.doctor_id = :doctor_id";
        $params['doctor_id'] = $doctorId;
    }

    if ($patientId > 0) {
        $statsSql .= " AND o.patient_id = :patient_id";
        $params['patient_id'] = $patientId;
    }

    if ($paymentStatus !== '') {
        $statsSql .= " AND o.payment_status = :payment_status";
        $params['payment_status'] = $paymentStatus;
    }

    if ($testId > 0) {
        $statsSql .= " AND EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.id AND oi.test_id = :test_id)";
        $params['test_id'] = $testId;
    }
    if ($branchIdFilter > 0) {
        $statsSql .= " AND o.branch_id = :branch_id";
        $params['branch_id'] = $branchIdFilter;
    }

    $statsSql .= " ORDER BY i.issue_date DESC, i.id DESC";

    $stmt = $db->prepare($statsSql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll();

    $catSql =
        "SELECT
            COALESCE(c.name, 'غير مصنف') AS category_name,
            COUNT(*) AS items_count,
            SUM(oi.price) AS total_amount
         FROM order_items oi
         JOIN lab_orders o ON o.id = oi.order_id
         LEFT JOIN tests t ON t.id = oi.test_id
         LEFT JOIN test_categories c ON c.id = t.category_id
         WHERE DATE(o.order_date) BETWEEN :from AND :to AND o.tenant_id = :tid";

    $catParams = [
        'from' => $fromDate,
        'to' => $toDate,
        'tid' => $tenantId
    ];

    if ($doctorId > 0) {
        $catSql .= " AND o.doctor_id = :doctor_id";
        $catParams['doctor_id'] = $doctorId;
    }

    if ($patientId > 0) {
        $catSql .= " AND o.patient_id = :patient_id";
        $catParams['patient_id'] = $patientId;
    }

    if ($paymentStatus !== '') {
        $catSql .= " AND o.payment_status = :payment_status";
        $catParams['payment_status'] = $paymentStatus;
    }

    if ($testId > 0) {
        $catSql .= " AND oi.test_id = :test_id";
        $catParams['test_id'] = $testId;
    }
    if ($branchIdFilter > 0) {
        $catSql .= " AND o.branch_id = :branch_id";
        $catParams['branch_id'] = $branchIdFilter;
    }

    $catSql .= " GROUP BY category_name ORDER BY total_amount DESC";

    $stmt = $db->prepare($catSql);
    $stmt->execute($catParams);
    $categorySummary = $stmt->fetchAll();

    $stmt = $db->prepare(
        "SELECT
             COUNT(*) AS total_completed,
             AVG(TIMESTAMPDIFF(HOUR, order_date, completed_at)) AS avg_hours,
             SUM(CASE WHEN TIMESTAMPDIFF(HOUR, order_date, completed_at) <= 24 THEN 1 ELSE 0 END) AS within_24h,
             SUM(CASE WHEN TIMESTAMPDIFF(HOUR, order_date, completed_at) > 24 THEN 1 ELSE 0 END) AS above_24h
         FROM lab_orders
         WHERE status = 'completed'
           AND completed_at IS NOT NULL
           AND DATE(order_date) BETWEEN :from AND :to
           AND tenant_id = :tid"
    );
    $stmt->execute(['from' => $fromDate, 'to' => $toDate, 'tid' => $tenantId]);
    $row = $stmt->fetch();
    if ($row) {
        $qualityStats['tat_total_completed'] = (int)($row['total_completed'] ?? 0);
        $qualityStats['tat_avg_hours'] = $row['avg_hours'] !== null ? (float)$row['avg_hours'] : null;
        $qualityStats['tat_within_24h'] = (int)($row['within_24h'] ?? 0);
        $qualityStats['tat_above_24h'] = (int)($row['above_24h'] ?? 0);
    }

    try {
        $db->query('SELECT tenant_id FROM lab_orders LIMIT 1');
        $stmt = $db->prepare(
            "SELECT COUNT(*) AS total_results, SUM(CASE WHEN r.flag = 'C' THEN 1 ELSE 0 END) AS critical_count
             FROM results r
             JOIN order_items oi ON oi.id = r.order_item_id
             JOIN lab_orders o ON o.id = oi.order_id
             WHERE r.result_date IS NOT NULL AND DATE(r.result_date) BETWEEN :from AND :to AND o.tenant_id = :tid"
        );
        $stmt->execute(['from' => $fromDate, 'to' => $toDate, 'tid' => $tenantId]);
    } catch (Throwable $e) {
        $stmt = $db->prepare(
            "SELECT COUNT(*) AS total_results, SUM(CASE WHEN flag = 'C' THEN 1 ELSE 0 END) AS critical_count FROM results WHERE result_date IS NOT NULL AND DATE(result_date) BETWEEN :from AND :to"
        );
        $stmt->execute(['from' => $fromDate, 'to' => $toDate]);
    }
    $row = $stmt->fetch();
    if ($row) {
        $qualityStats['critical_total_results'] = (int)($row['total_results'] ?? 0);
        $qualityStats['critical_count'] = (int)($row['critical_count'] ?? 0);
    }

    $topCritParams = ['from' => $fromDate, 'to' => $toDate, 'tid' => $tenantId];
    try {
        $db->query('SELECT tenant_id FROM lab_orders LIMIT 1');
        $stmt = $db->prepare(
            "SELECT t.name, COUNT(*) AS c
             FROM results r
             JOIN order_items oi ON oi.id = r.order_item_id
             JOIN lab_orders o ON o.id = oi.order_id
             JOIN tests t ON t.id = oi.test_id
             WHERE r.flag = 'C' AND r.result_date IS NOT NULL AND DATE(r.result_date) BETWEEN :from AND :to AND o.tenant_id = :tid
             GROUP BY t.id, t.name
             ORDER BY c DESC
             LIMIT 10"
        );
        $stmt->execute($topCritParams);
    } catch (Throwable $e) {
        $stmt = $db->prepare("SELECT t.name, COUNT(*) AS c FROM results r JOIN order_items oi ON oi.id = r.order_item_id JOIN tests t ON t.id = oi.test_id WHERE r.flag = 'C' AND r.result_date IS NOT NULL AND DATE(r.result_date) BETWEEN :from AND :to GROUP BY t.id, t.name ORDER BY c DESC LIMIT 10");
        $stmt->execute(['from' => $fromDate, 'to' => $toDate]);
    }
    $topCriticalTests = $stmt->fetchAll();

    $topDoctorsByOrders = [];
    $abnormalStats = ['total' => 0, 'abnormal' => 0, 'pct' => 0];
    try {
        $stmt = $db->prepare(
            "SELECT d.id, d.full_name, COUNT(o.id) AS orders_count
             FROM lab_orders o
             JOIN doctors d ON d.id = o.doctor_id
             WHERE DATE(o.order_date) BETWEEN :from AND :to AND o.doctor_id IS NOT NULL AND o.tenant_id = :tid
             GROUP BY o.doctor_id, d.id, d.full_name
             ORDER BY orders_count DESC
             LIMIT 10"
        );
        $stmt->execute(['from' => $fromDate, 'to' => $toDate, 'tid' => $tenantId]);
        $topDoctorsByOrders = $stmt->fetchAll();
    } catch (Throwable $e) {}
    try {
        $db->query('SELECT tenant_id FROM lab_orders LIMIT 1');
        $stmt = $db->prepare(
            "SELECT COUNT(*) AS total, SUM(CASE WHEN r.flag IN ('L','H','C') THEN 1 ELSE 0 END) AS abnormal
             FROM results r JOIN order_items oi ON oi.id = r.order_item_id JOIN lab_orders o ON o.id = oi.order_id
             WHERE r.result_date IS NOT NULL AND DATE(r.result_date) BETWEEN :from AND :to AND o.tenant_id = :tid"
        );
        $stmt->execute(['from' => $fromDate, 'to' => $toDate, 'tid' => $tenantId]);
        $row = $stmt->fetch();
        if ($row && (int)$row['total'] > 0) {
            $abnormalStats['total'] = (int)$row['total'];
            $abnormalStats['abnormal'] = (int)$row['abnormal'];
            $abnormalStats['pct'] = round(100 * $abnormalStats['abnormal'] / $abnormalStats['total'], 1);
        }
    } catch (Throwable $e) {}

    if ($export === 'stats_csv') {
        header('Content-Type: text/csv; charset=UTF-8');
        $fileName = 'stats_' . $fromDate . '_to_' . $toDate . '.csv';
        header('Content-Disposition: attachment; filename="' . $fileName . '"');
        echo "\xEF\xBB\xBF";
        echo "Date,Patient,Doctor,Total,Discount,Net,Paid,Remaining,Payment Status\n";
        foreach ($rows as $r) {
            $line = [
                $r['issue_date'],
                $r['patient_name'],
                $r['doctor_name'] ?? '',
                $r['total_amount'],
                $r['discount_amount'],
                $r['net_amount'],
                $r['paid_amount'],
                $r['remaining_amount'],
                $r['payment_status']
            ];
            $escaped = [];
            foreach ($line as $v) {
                $v = (string)$v;
                if (strpbrk($v, ",\"\n\r") !== false) {
                    $v = '"' . str_replace('"', '""', $v) . '"';
                }
                $escaped[] = $v;
            }
            echo implode(',', $escaped) . "\n";
        }
        exit;
    }
    $patientIds = [];
    foreach ($rows as $r) {
        $statsRows[] = $r;
        $statsTotals['total_amount'] += (float)$r['total_amount'];
        $statsTotals['discount_amount'] += (float)$r['discount_amount'];
        $statsTotals['paid_amount'] += (float)$r['paid_amount'];
        $statsTotals['remaining_amount'] += (float)$r['remaining_amount'];
        $pid = (int)$r['patient_id'];
        if ($pid > 0) {
            $patientIds[$pid] = true;
        }
        $docName = $r['doctor_name'] !== null && $r['doctor_name'] !== '' ? $r['doctor_name'] : 'بدون طبيب';
        if (!isset($doctorSummary[$docName])) {
            $doctorSummary[$docName] = [
                'total_amount' => 0,
                'discount_amount' => 0,
                'net_amount' => 0,
                'paid_amount' => 0,
                'remaining_amount' => 0
            ];
        }
        $doctorSummary[$docName]['total_amount'] += (float)$r['total_amount'];
        $doctorSummary[$docName]['discount_amount'] += (float)$r['discount_amount'];
        $doctorSummary[$docName]['net_amount'] += (float)$r['net_amount'];
        $doctorSummary[$docName]['paid_amount'] += (float)$r['paid_amount'];
        $doctorSummary[$docName]['remaining_amount'] += (float)$r['remaining_amount'];

        $payMethod = $r['payment_method'] ?? '';
        if ($payMethod === null || $payMethod === '') {
            $payMethod = 'غير محدد';
        }
        if (!isset($paymentMethodSummary[$payMethod])) {
            $paymentMethodSummary[$payMethod] = [
                'count' => 0,
                'net_amount' => 0,
                'paid_amount' => 0
            ];
        }
        $paymentMethodSummary[$payMethod]['count']++;
        $paymentMethodSummary[$payMethod]['net_amount'] += (float)$r['net_amount'];
        $paymentMethodSummary[$payMethod]['paid_amount'] += (float)$r['paid_amount'];

        $statusKey = $r['payment_status'] ?? '';
        if ($statusKey === null || $statusKey === '') {
            $statusKey = 'غير محدد';
        }
        if (!isset($paymentStatusSummary[$statusKey])) {
            $paymentStatusSummary[$statusKey] = [
                'count' => 0,
                'net_amount' => 0,
                'paid_amount' => 0,
                'remaining_amount' => 0
            ];
        }
        $paymentStatusSummary[$statusKey]['count']++;
        $paymentStatusSummary[$statusKey]['net_amount'] += (float)$r['net_amount'];
        $paymentStatusSummary[$statusKey]['paid_amount'] += (float)$r['paid_amount'];
        $paymentStatusSummary[$statusKey]['remaining_amount'] += (float)$r['remaining_amount'];

        $branchName = $r['branch_name'] ?? '';
        if ($branchName === null || $branchName === '') {
            $branchName = 'الفرع الرئيسي';
        }
        if (!isset($branchSummary[$branchName])) {
            $branchSummary[$branchName] = [
                'total_amount' => 0,
                'discount_amount' => 0,
                'net_amount' => 0,
                'paid_amount' => 0,
                'remaining_amount' => 0
            ];
        }
        $branchSummary[$branchName]['total_amount'] += (float)$r['total_amount'];
        $branchSummary[$branchName]['discount_amount'] += (float)$r['discount_amount'];
        $branchSummary[$branchName]['net_amount'] += (float)$r['net_amount'];
        $branchSummary[$branchName]['paid_amount'] += (float)$r['paid_amount'];
        $branchSummary[$branchName]['remaining_amount'] += (float)$r['remaining_amount'];
    }
    $statsTotals['patients_count'] = count($patientIds);
} catch (Throwable $e) {
}
?>
<div class="page-header">
    <h1>التقارير الإحصائية</h1>
</div>
<div class="section-card">
<h3>عدد الطلبات والإيرادات حسب اليوم (آخر 14 يوم)</h3>
<div style="display:flex;flex-wrap:wrap;gap:24px;margin-bottom:20px;">
    <div style="flex:1;min-width:300px;">
        <canvas id="chartOrdersRevenue" style="max-height:280px;"></canvas>
    </div>
</div>
<div class="table-wrapper">
<table class="table">
    <thead>
        <tr>
            <th>التاريخ</th>
            <th>عدد الطلبات</th>
            <th>الإيرادات (مدفوعة)</th>
        </tr>
    </thead>
    <tbody>
    <?php
    $revMap = [];
    foreach ($revenueByDay as $r) { $revMap[$r['d']] = (float)$r['revenue']; }
    foreach ($ordersByDay as $row):
        $dayRev = $revMap[$row['d']] ?? 0;
    ?>
        <tr>
            <td><?php echo htmlspecialchars($row['d'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo (int)$row['c']; ?></td>
            <td><?php echo number_format($dayRev, 2); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
    var ordData = <?php echo json_encode(array_reverse(array_map(function($r){return (int)$r['c'];}, $ordersByDay))); ?>;
    var revData = <?php
        $labels = array_reverse(array_column($ordersByDay, 'd'));
        $revs = [];
        foreach ($labels as $d) { $revs[] = $revMap[$d] ?? 0; }
        echo json_encode($revs);
    ?>;
    var lab = <?php echo json_encode(array_reverse(array_column($ordersByDay, 'd'))); ?>;
    var ctx = document.getElementById('chartOrdersRevenue');
    if (ctx && typeof Chart !== 'undefined') {
        new Chart(ctx.getContext('2d'), {
            type: 'bar',
            data: {
                labels: lab,
                datasets: [
                    { label: 'عدد الطلبات', data: ordData, backgroundColor: 'rgba(13, 148, 136, 0.6)' },
                    { label: 'الإيرادات', data: revData, backgroundColor: 'rgba(34, 197, 94, 0.6)', yAxisID: 'y1' }
                ]
            },
            options: {
                responsive: true,
                plugins: { legend: { position: 'top' } },
                scales: {
                    y: { beginAtZero: true },
                    y1: { beginAtZero: true, position: 'right', grid: { drawOnChartArea: false } }
                }
            }
        });
    }
})();
</script>

<div class="section-card">
<h3>ملخص الإيرادات الكلية</h3>
<div class="table-wrapper">
<table class="table">
    <thead>
        <tr>
            <th>الإجمالي قبل الخصم</th>
            <th>إجمالي الخصومات</th>
            <th>الصافي</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td><?php echo htmlspecialchars($revenueSummary['total_amount'] ?? 0, ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($revenueSummary['discount_amount'] ?? 0, ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($revenueSummary['net_amount'] ?? 0, ENT_QUOTES, 'UTF-8'); ?></td>
        </tr>
    </tbody>
</table>
</div>
</div>

<div class="section-card">
<h3>تقارير ذكية</h3>
<div class="stats-grid" style="grid-template-columns: repeat(auto-fit, minmax(220px, 1fr));">
    <div class="stat-card">
        <div class="stat-label">أكثر طبيب تحويلاً</div>
        <div class="stat-value" style="font-size:1rem;">
            <?php echo !empty($topDoctorsByOrders) ? htmlspecialchars($topDoctorsByOrders[0]['full_name'], ENT_QUOTES, 'UTF-8') . ' (' . $topDoctorsByOrders[0]['orders_count'] . ' طلب)' : '-'; ?>
        </div>
    </div>
    <div class="stat-card">
        <div class="stat-label">نسبة النتائج غير الطبيعية</div>
        <div class="stat-value"><?php echo $abnormalStats['pct']; ?>%</div>
        <small><?php echo $abnormalStats['abnormal']; ?> / <?php echo $abnormalStats['total']; ?></small>
    </div>
</div>
<div class="table-wrapper" style="margin-top:16px;">
    <h4>أكثر الأطباء تحويلاً</h4>
    <table class="table">
        <thead><tr><th>الطبيب</th><th>عدد الطلبات</th></tr></thead>
        <tbody>
        <?php foreach ($topDoctorsByOrders as $d): ?>
            <tr><td><a href="index.php?page=reports&doctor_id=<?php echo (int)$d['id']; ?>&from=<?php echo urlencode($fromDate); ?>&to=<?php echo urlencode($toDate); ?>"><?php echo htmlspecialchars($d['full_name'], ENT_QUOTES, 'UTF-8'); ?></a></td><td><?php echo (int)$d['orders_count']; ?></td></tr>
        <?php endforeach; ?>
        </tbody>
    </table>
</div>
<?php
$doctorReport = null;
if ($doctorId > 0) {
    $stmt = $db->prepare('SELECT full_name FROM doctors WHERE id = ?');
    $stmt->execute([$doctorId]);
    $dr = $stmt->fetch();
    if ($dr) {
        $stmt = $db->prepare("SELECT COUNT(o.id) AS orders_count, COALESCE(SUM(o.net_amount), 0) AS total_net FROM lab_orders o WHERE o.doctor_id = ? AND DATE(o.order_date) BETWEEN ? AND ? AND o.tenant_id = ?");
        $stmt->execute([$doctorId, $fromDate, $toDate, $tenantId]);
        $docStats = $stmt->fetch();
        $stmt = $db->prepare("SELECT t.name, COUNT(oi.id) AS test_count FROM order_items oi JOIN lab_orders o ON o.id = oi.order_id JOIN tests t ON t.id = oi.test_id WHERE o.doctor_id = ? AND DATE(o.order_date) BETWEEN ? AND ? AND o.tenant_id = ? GROUP BY t.id, t.name ORDER BY test_count DESC LIMIT 15");
        $stmt->execute([$doctorId, $fromDate, $toDate, $tenantId]);
        $docTopTests = $stmt->fetchAll();
        $doctorReport = ['name' => $dr['full_name'], 'stats' => $docStats, 'top_tests' => $docTopTests];
    }
}
if ($doctorReport): ?>
<div class="section-card" style="margin-top:16px;">
    <h3>📋 تقرير الطبيب: <?php echo htmlspecialchars($doctorReport['name'], ENT_QUOTES, 'UTF-8'); ?></h3>
    <div class="stats-grid" style="grid-template-columns: repeat(auto-fit, minmax(180px, 1fr)); margin-bottom:16px;">
        <div class="stat-card"><div class="stat-label">عدد الطلبات</div><div class="stat-value"><?php echo (int)($doctorReport['stats']['orders_count'] ?? 0); ?></div></div>
        <div class="stat-card"><div class="stat-label">إجمالي المبالغ (صافي)</div><div class="stat-value"><?php echo number_format((float)($doctorReport['stats']['total_net'] ?? 0), 2); ?></div></div>
    </div>
    <h4>الفحوصات الأكثر طلباً لهذا الطبيب</h4>
    <table class="table">
        <thead><tr><th>الفحص</th><th>عدد المرات</th></tr></thead>
        <tbody>
        <?php foreach ($doctorReport['top_tests'] as $tt): ?>
            <tr><td><?php echo htmlspecialchars($tt['name'], ENT_QUOTES, 'UTF-8'); ?></td><td><?php echo (int)$tt['test_count']; ?></td></tr>
        <?php endforeach; ?>
        </tbody>
    </table>
</div>
<?php endif; ?>
</div>

<div class="section-card">
<h3>أكثر الفحوصات طلباً</h3>
<div class="table-wrapper">
<table class="table">
    <thead>
        <tr>
            <th>الفحص</th>
            <th>عدد المرات</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($topTests as $row): ?>
        <tr>
            <td><?php echo htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['c'], ENT_QUOTES, 'UTF-8'); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</div>
</div>

<div class="section-card">
<h3>مؤشرات الجودة (TAT والنتائج الحرجة)</h3>
<div class="stats-grid">
    <div class="stat-card">
        <div class="stat-label">عدد الطلبات المكتملة في الفترة</div>
        <div class="stat-value"><?php echo (int)$qualityStats['tat_total_completed']; ?></div>
    </div>
    <div class="stat-card">
        <div class="stat-label">متوسط زمن الإنجاز (ساعة)</div>
        <div class="stat-value">
            <?php
            echo $qualityStats['tat_avg_hours'] !== null
                ? number_format($qualityStats['tat_avg_hours'], 1, '.', '')
                : '-';
            ?>
        </div>
    </div>
    <div class="stat-card">
        <div class="stat-label">طلبات أنجزت خلال 24 ساعة</div>
        <div class="stat-value"><?php echo (int)$qualityStats['tat_within_24h']; ?></div>
    </div>
    <div class="stat-card">
        <div class="stat-label">طلبات تجاوزت 24 ساعة</div>
        <div class="stat-value"><?php echo (int)$qualityStats['tat_above_24h']; ?></div>
    </div>
    <div class="stat-card">
        <div class="stat-label">عدد النتائج الحرجة</div>
        <div class="stat-value"><?php echo (int)$qualityStats['critical_count']; ?></div>
    </div>
    <div class="stat-card">
        <div class="stat-label">إجمالي النتائج المسجلة</div>
        <div class="stat-value"><?php echo (int)$qualityStats['critical_total_results']; ?></div>
    </div>
</div>
</div>

<div class="section-card">
<h3>إحصائيات مالية تفصيلية حسب الفترة</h3>
<form method="get" class="form-grid" style="margin-bottom:16px;">
    <input type="hidden" name="page" value="reports">
    <div>
        <label>من تاريخ</label>
        <input type="date" name="from" value="<?php echo htmlspecialchars($fromDate, ENT_QUOTES, 'UTF-8'); ?>">
    </div>
    <div>
        <label>إلى تاريخ</label>
        <input type="date" name="to" value="<?php echo htmlspecialchars($toDate, ENT_QUOTES, 'UTF-8'); ?>">
    </div>
    <div>
        <label>الطبيب المحوِّل</label>
        <select name="doctor_id">
            <option value="0">الكل</option>
            <?php foreach ($doctors as $doc): ?>
                <option value="<?php echo (int)$doc['id']; ?>" <?php echo $doctorId === (int)$doc['id'] ? 'selected' : ''; ?>>
                    <?php echo htmlspecialchars($doc['full_name'], ENT_QUOTES, 'UTF-8'); ?>
                </option>
            <?php endforeach; ?>
        </select>
    </div>
    <div>
        <label>المريض</label>
        <select name="patient_id">
            <option value="0">الكل</option>
            <?php foreach ($patients as $p): ?>
                <option value="<?php echo (int)$p['id']; ?>" <?php echo $patientId === (int)$p['id'] ? 'selected' : ''; ?>>
                    <?php echo htmlspecialchars($p['full_name'], ENT_QUOTES, 'UTF-8'); ?>
                </option>
            <?php endforeach; ?>
        </select>
    </div>
    <div>
        <label>الفحص</label>
        <select name="test_id">
            <option value="0">الكل</option>
            <?php foreach ($testsList as $t): ?>
                <option value="<?php echo (int)$t['id']; ?>" <?php echo $testId === (int)$t['id'] ? 'selected' : ''; ?>>
                    <?php echo htmlspecialchars($t['name'], ENT_QUOTES, 'UTF-8'); ?>
                </option>
            <?php endforeach; ?>
        </select>
    </div>
    <div>
        <label>حالة الدفع</label>
        <select name="payment_status">
            <option value="">الكل</option>
            <option value="unpaid" <?php echo $paymentStatus === 'unpaid' ? 'selected' : ''; ?>>غير مدفوع</option>
            <option value="partial" <?php echo $paymentStatus === 'partial' ? 'selected' : ''; ?>>مدفوع جزئياً</option>
            <option value="paid" <?php echo $paymentStatus === 'paid' ? 'selected' : ''; ?>>مدفوع بالكامل</option>
        </select>
    </div>
    <?php if (count($branchesList) > 0): ?>
    <div>
        <label>الفرع</label>
        <select name="branch_id">
            <option value="0">الكل</option>
            <?php foreach ($branchesList as $br): ?>
                <option value="<?php echo (int)$br['id']; ?>" <?php echo $branchIdFilter === (int)$br['id'] ? 'selected' : ''; ?>><?php echo htmlspecialchars($br['name'], ENT_QUOTES, 'UTF-8'); ?></option>
            <?php endforeach; ?>
        </select>
    </div>
    <?php endif; ?>
    <div class="full-width form-actions">
        <button type="submit">تطبيق الفلتر</button>
        <button type="submit" name="export" value="stats_csv">تصدير إلى Excel</button>
    </div>
</form>
<div class="table-wrapper">
<table class="table">
    <thead>
        <tr>
            <th>التاريخ</th>
            <th>المريض</th>
            <th>الطبيب</th>
            <th>الإجمالي</th>
            <th>الخصم</th>
            <th>الصافي</th>
            <th>المدفوع</th>
            <th>الباقي</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($statsRows as $row): ?>
        <tr>
            <td><?php echo htmlspecialchars($row['issue_date'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['patient_name'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['doctor_name'] ?? '-', ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['total_amount'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['discount_amount'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['net_amount'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['paid_amount'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['remaining_amount'], ENT_QUOTES, 'UTF-8'); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</div>
<div class="stats-grid" style="margin-top:16px;">
    <div class="stat-card">
        <div class="stat-label">الباقي</div>
        <div class="stat-value"><?php echo number_format($statsTotals['remaining_amount'], 2, '.', ''); ?></div>
    </div>
    <div class="stat-card">
        <div class="stat-label">المدفوع</div>
        <div class="stat-value"><?php echo number_format($statsTotals['paid_amount'], 2, '.', ''); ?></div>
    </div>
    <div class="stat-card">
        <div class="stat-label">الخصم</div>
        <div class="stat-value"><?php echo number_format($statsTotals['discount_amount'], 2, '.', ''); ?></div>
    </div>
    <div class="stat-card">
        <div class="stat-label">الإجمالي</div>
        <div class="stat-value"><?php echo number_format($statsTotals['total_amount'], 2, '.', ''); ?></div>
    </div>
    <div class="stat-card">
        <div class="stat-label">عدد المرضى</div>
        <div class="stat-value"><?php echo (int)$statsTotals['patients_count']; ?></div>
    </div>
</div>
</div>

<div class="section-card">
<h3>ملخص مالي حسب الطبيب داخل الفترة المحددة</h3>
<div class="table-wrapper">
<table class="table">
    <thead>
        <tr>
            <th>الطبيب</th>
            <th>الإجمالي</th>
            <th>الخصم</th>
            <th>الصافي</th>
            <th>المدفوع</th>
            <th>الباقي</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($doctorSummary as $docName => $s): ?>
        <tr>
            <td><?php echo htmlspecialchars($docName, ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo number_format($s['total_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['discount_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['net_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['paid_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['remaining_amount'], 2, '.', ''); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</div>
</div>

<div class="section-card">
<h3>ملخص حسب طريقة الدفع داخل الفترة المحددة</h3>
<div class="table-wrapper">
<table class="table">
    <thead>
        <tr>
            <th>طريقة الدفع</th>
            <th>عدد الفواتير</th>
            <th>الصافي</th>
            <th>المدفوع</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($paymentMethodSummary as $method => $s): ?>
        <tr>
            <td><?php echo htmlspecialchars($method, ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo (int)$s['count']; ?></td>
            <td><?php echo number_format($s['net_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['paid_amount'], 2, '.', ''); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</div>
</div>

<div class="section-card">
<h3>ملخص حسب حالة الدفع داخل الفترة المحددة</h3>
<div class="table-wrapper">
<table class="table">
    <thead>
        <tr>
            <th>حالة الدفع</th>
            <th>عدد الفواتير</th>
            <th>الصافي</th>
            <th>المدفوع</th>
            <th>الباقي</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($paymentStatusSummary as $statusName => $s): ?>
        <tr>
            <td><?php echo htmlspecialchars($statusName, ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo (int)$s['count']; ?></td>
            <td><?php echo number_format($s['net_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['paid_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['remaining_amount'], 2, '.', ''); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</div>
</div>

<div class="section-card">
<h3>ملخص الفروع داخل الفترة المحددة</h3>
<div class="table-wrapper">
<table class="table">
    <thead>
        <tr>
            <th>الفرع</th>
            <th>الإجمالي</th>
            <th>الخصم</th>
            <th>الصافي</th>
            <th>المدفوع</th>
            <th>الباقي</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($branchSummary as $branchName => $s): ?>
        <tr>
            <td><?php echo htmlspecialchars($branchName, ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo number_format($s['total_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['discount_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['net_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['paid_amount'], 2, '.', ''); ?></td>
            <td><?php echo number_format($s['remaining_amount'], 2, '.', ''); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</div>
</div>

<div class="section-card">
<h3>ملخص حسب فئات الفحوصات داخل الفترة المحددة</h3>
<div class="table-wrapper">
<table class="table">
    <thead>
        <tr>
            <th>فئة الفحص</th>
            <th>عدد العناصر</th>
            <th>إجمالي الأسعار</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($categorySummary as $row): ?>
        <tr>
            <td><?php echo htmlspecialchars($row['category_name'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['items_count'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['total_amount'], ENT_QUOTES, 'UTF-8'); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</div>
</div>

<div class="section-card">
<h3>أكثر الفحوصات ذات نتائج حرجة</h3>
<div class="table-wrapper">
<table class="table">
    <thead>
        <tr>
            <th>الفحص</th>
            <th>عدد النتائج الحرجة</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($topCriticalTests as $row): ?>
        <tr>
            <td><?php echo htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlspecialchars($row['c'], ENT_QUOTES, 'UTF-8'); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</div>
</div>
