<?php
/**
 * تقارير شهرية وسنوية متقدمة - Smart Doctor
 */
$reportType = $_GET['type'] ?? 'monthly';
$year = (int)($_GET['year'] ?? date('Y'));
$month = (int)($_GET['month'] ?? date('n'));

$monthlyData = [];
$yearlyData = [];
$comparisonData = [];
$monthTotalOrders = 0;
$monthTotalPatients = 0;
$monthTotalAppointments = 0;
$monthAbnormal = 0;
$monthCritical = 0;

try {
    if ($reportType === 'monthly') {
        $daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $ordersByDay = $patientsByDay = $appointmentsByDay = [];
        for ($d = 1; $d <= $daysInMonth; $d++) {
            $date = sprintf('%04d-%02d-%02d', $year, $month, $d);
            $ordersByDay[$date] = ['orders' => 0, 'patients' => 0];
            $appointmentsByDay[$date] = 0;
        }

        $stmt = $db->prepare("SELECT DATE(o.order_date) AS d, COUNT(*) AS orders, COUNT(DISTINCT o.patient_id) AS patients
            FROM lab_orders o
            WHERE o.tenant_id = ? AND o.doctor_id = ? AND YEAR(o.order_date) = ? AND MONTH(o.order_date) = ?
            GROUP BY DATE(o.order_date)
            ORDER BY d");
        $stmt->execute([$tenantId, $doctorId, $year, $month]);
        foreach ($stmt->fetchAll() as $r) {
            $date = $r['d'];
            if (isset($ordersByDay[$date])) {
                $ordersByDay[$date] = ['orders' => (int)$r['orders'], 'patients' => (int)$r['patients']];
            }
        }

        try {
            $db->query('SELECT id FROM doctor_appointments LIMIT 1');
            $stmt = $db->prepare("SELECT DATE(appointment_date) AS d, COUNT(*) AS cnt FROM doctor_appointments WHERE doctor_account_id = ? AND YEAR(appointment_date) = ? AND MONTH(appointment_date) = ? GROUP BY DATE(appointment_date)");
            $stmt->execute([$doctorAuth->accountId(), $year, $month]);
            foreach ($stmt->fetchAll() as $r) {
                $appointmentsByDay[$r['d']] = (int)$r['cnt'];
            }
        } catch (Throwable $e) {
            try {
                $db->query('SELECT id FROM patient_doctor_bookings LIMIT 1');
                $stmt = $db->prepare("SELECT DATE(appointment_date) AS d, COUNT(*) AS cnt FROM patient_doctor_bookings WHERE doctor_account_id = ? AND YEAR(appointment_date) = ? AND MONTH(appointment_date) = ? GROUP BY DATE(appointment_date)");
                $stmt->execute([$doctorAuth->accountId(), $year, $month]);
                foreach ($stmt->fetchAll() as $r) {
                    $appointmentsByDay[$r['d']] = (int)$r['cnt'];
                }
            } catch (Throwable $e2) {}
        }

        for ($d = 1; $d <= $daysInMonth; $d++) {
            $date = sprintf('%04d-%02d-%02d', $year, $month, $d);
            $o = $ordersByDay[$date] ?? ['orders' => 0, 'patients' => 0];
            $monthlyData[] = ['d' => $date, 'orders' => $o['orders'], 'patients' => $o['patients'], 'appointments' => $appointmentsByDay[$date] ?? 0];
        }

        $stmt = $db->prepare("SELECT COUNT(*) FROM lab_orders o WHERE o.tenant_id = ? AND o.doctor_id = ? AND YEAR(o.order_date) = ? AND MONTH(o.order_date) = ?");
        $stmt->execute([$tenantId, $doctorId, $year, $month]);
        $monthTotalOrders = (int)$stmt->fetchColumn();

        $stmt = $db->prepare("SELECT COUNT(DISTINCT patient_id) FROM lab_orders o WHERE o.tenant_id = ? AND o.doctor_id = ? AND YEAR(o.order_date) = ? AND MONTH(o.order_date) = ?");
        $stmt->execute([$tenantId, $doctorId, $year, $month]);
        $monthTotalPatients = (int)$stmt->fetchColumn();

        try {
            $db->query('SELECT id FROM doctor_appointments LIMIT 1');
            $stmt = $db->prepare("SELECT COUNT(*) FROM doctor_appointments WHERE doctor_account_id = ? AND YEAR(appointment_date) = ? AND MONTH(appointment_date) = ?");
            $stmt->execute([$doctorAuth->accountId(), $year, $month]);
            $monthTotalAppointments = (int)$stmt->fetchColumn();
        } catch (Throwable $e) {
            try {
                $db->query('SELECT id FROM patient_doctor_bookings LIMIT 1');
                $stmt = $db->prepare("SELECT COUNT(*) FROM patient_doctor_bookings WHERE doctor_account_id = ? AND YEAR(appointment_date) = ? AND MONTH(appointment_date) = ?");
                $stmt->execute([$doctorAuth->accountId(), $year, $month]);
                $monthTotalAppointments = (int)$stmt->fetchColumn();
            } catch (Throwable $e2) {}
        }

        $stmt = $db->prepare("SELECT 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 o.tenant_id = ? AND o.doctor_id = ? AND r.flag IN ('L','H','C') AND YEAR(o.order_date) = ? AND MONTH(o.order_date) = ?");
        $stmt->execute([$tenantId, $doctorId, $year, $month]);
        $monthAbnormal = (int)$stmt->fetchColumn();

        $stmt = $db->prepare("SELECT 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 o.tenant_id = ? AND o.doctor_id = ? AND r.flag = 'C' AND YEAR(o.order_date) = ? AND MONTH(o.order_date) = ?");
        $stmt->execute([$tenantId, $doctorId, $year, $month]);
        $monthCritical = (int)$stmt->fetchColumn();
    } else {
        $stmt = $db->prepare("SELECT MONTH(o.order_date) AS m, COUNT(*) AS orders, COUNT(DISTINCT o.patient_id) AS patients
            FROM lab_orders o
            WHERE o.tenant_id = ? AND o.doctor_id = ? AND YEAR(o.order_date) = ?
            GROUP BY MONTH(o.order_date)
            ORDER BY m");
        $stmt->execute([$tenantId, $doctorId, $year]);
        $yearlyOrdersData = $stmt->fetchAll();

        $appointmentsByMonth = array_fill(1, 12, 0);
        try {
            $db->query('SELECT id FROM doctor_appointments LIMIT 1');
            $stmt = $db->prepare("SELECT MONTH(appointment_date) AS m, COUNT(*) AS cnt FROM doctor_appointments WHERE doctor_account_id = ? AND YEAR(appointment_date) = ? GROUP BY MONTH(appointment_date)");
            $stmt->execute([$doctorAuth->accountId(), $year]);
            foreach ($stmt->fetchAll() as $r) { $appointmentsByMonth[(int)$r['m']] = (int)$r['cnt']; }
        } catch (Throwable $e) {
            try {
                $db->query('SELECT id FROM patient_doctor_bookings LIMIT 1');
                $stmt = $db->prepare("SELECT MONTH(appointment_date) AS m, COUNT(*) AS cnt FROM patient_doctor_bookings WHERE doctor_account_id = ? AND YEAR(appointment_date) = ? GROUP BY MONTH(appointment_date)");
                $stmt->execute([$doctorAuth->accountId(), $year]);
                foreach ($stmt->fetchAll() as $r) { $appointmentsByMonth[(int)$r['m']] = (int)$r['cnt']; }
            } catch (Throwable $e2) {}
        }

        $yearlyData = [];
        for ($m = 1; $m <= 12; $m++) {
            $found = null;
            foreach ($yearlyOrdersData as $r) { if ((int)$r['m'] === $m) { $found = $r; break; } }
            $yearlyData[] = [
                'm' => $m,
                'orders' => $found ? (int)$found['orders'] : 0,
                'patients' => $found ? (int)$found['patients'] : 0,
                'appointments' => $appointmentsByMonth[$m] ?? 0
            ];
        }

        $stmt = $db->prepare("SELECT COUNT(*) FROM lab_orders o WHERE o.tenant_id = ? AND o.doctor_id = ? AND YEAR(o.order_date) = ?");
        $stmt->execute([$tenantId, $doctorId, $year]);
        $yearTotalOrders = (int)$stmt->fetchColumn();

        $stmt = $db->prepare("SELECT COUNT(DISTINCT patient_id) FROM lab_orders o WHERE o.tenant_id = ? AND o.doctor_id = ? AND YEAR(o.order_date) = ?");
        $stmt->execute([$tenantId, $doctorId, $year]);
        $yearTotalPatients = (int)$stmt->fetchColumn();

        $yearTotalAppointments = 0;
        try {
            $db->query('SELECT id FROM doctor_appointments LIMIT 1');
            $stmt = $db->prepare("SELECT COUNT(*) FROM doctor_appointments WHERE doctor_account_id = ? AND YEAR(appointment_date) = ?");
            $stmt->execute([$doctorAuth->accountId(), $year]);
            $yearTotalAppointments = (int)$stmt->fetchColumn();
        } catch (Throwable $e) {
            try {
                $db->query('SELECT id FROM patient_doctor_bookings LIMIT 1');
                $stmt = $db->prepare("SELECT COUNT(*) FROM patient_doctor_bookings WHERE doctor_account_id = ? AND YEAR(appointment_date) = ?");
                $stmt->execute([$doctorAuth->accountId(), $year]);
                $yearTotalAppointments = (int)$stmt->fetchColumn();
            } catch (Throwable $e2) {}
        }

        $stmt = $db->prepare("SELECT t.name, COUNT(*) AS cnt 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 o.tenant_id = ? AND o.doctor_id = ? AND r.flag IN ('L','H','C') AND YEAR(o.order_date) = ? GROUP BY oi.test_id ORDER BY cnt DESC LIMIT 10");
        $stmt->execute([$tenantId, $doctorId, $year]);
        $topAbnormalTests = $stmt->fetchAll();
    }

    $stmt = $db->prepare("SELECT YEAR(o.order_date) AS y, COUNT(*) AS c FROM lab_orders o WHERE o.tenant_id = ? AND o.doctor_id = ? GROUP BY YEAR(o.order_date) ORDER BY y DESC LIMIT 5");
    $stmt->execute([$tenantId, $doctorId]);
    $comparisonData = $stmt->fetchAll();
} catch (Throwable $e) {}

$monthNames = ['', 'يناير', 'فبراير', 'مارس', 'أبريل', 'مايو', 'يونيو', 'يوليو', 'أغسطس', 'سبتمبر', 'أكتوبر', 'نوفمبر', 'ديسمبر'];
?>
<!DOCTYPE html>
<html lang="ar" dir="rtl">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>التقارير - Smart Doctor</title>
    <link rel="stylesheet" href="styles.css">
    <link rel="stylesheet" href="doctor.css">
    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
</head>
<body>
<?php include __DIR__ . '/_layout.php'; ?>
<main class="content">
    <div class="page-header">
        <h1>📊 التقارير الشهرية والسنوية</h1>
        <form method="get" style="display:flex;gap:12px;flex-wrap:wrap;align-items:center;">
            <input type="hidden" name="page" value="reports">
            <select name="type">
                <option value="monthly" <?php echo $reportType === 'monthly' ? 'selected' : ''; ?>>تقرير شهري</option>
                <option value="yearly" <?php echo $reportType === 'yearly' ? 'selected' : ''; ?>>تقرير سنوي</option>
            </select>
            <select name="year">
                <?php for ($y = date('Y') + 1; $y >= date('Y') - 5; $y--): ?>
                <option value="<?php echo $y; ?>" <?php echo $year === $y ? 'selected' : ''; ?>><?php echo $y; ?></option>
                <?php endfor; ?>
            </select>
            <?php if ($reportType === 'monthly'): ?>
            <select name="month">
                <?php for ($m = 1; $m <= 12; $m++): ?>
                <option value="<?php echo $m; ?>" <?php echo $month === $m ? 'selected' : ''; ?>><?php echo $monthNames[$m]; ?></option>
                <?php endfor; ?>
            </select>
            <?php endif; ?>
            <button type="submit" class="btn-primary">عرض</button>
        </form>
    </div>

    <?php if ($reportType === 'monthly'): ?>
    <div class="card" style="margin-bottom:24px;">
        <h2>شهر <?php echo $monthNames[$month]; ?> <?php echo $year; ?></h2>
        <div style="display:grid;grid-template-columns:repeat(auto-fit, minmax(150px, 1fr));gap:16px;margin:20px 0;">
            <div style="padding:16px;background:linear-gradient(135deg,#0d948820,#fff);border-radius:8px;border-right:4px solid #0d9488;">
                <div style="font-size:2rem;font-weight:bold;color:#0d9488;"><?php echo number_format($monthTotalOrders ?? 0); ?></div>
                <div style="color:var(--text-muted);">الطلبات</div>
            </div>
            <div style="padding:16px;background:linear-gradient(135deg,#8b5cf620,#fff);border-radius:8px;border-right:4px solid #8b5cf6;">
                <div style="font-size:2rem;font-weight:bold;color:#8b5cf6;"><?php echo number_format($monthTotalAppointments ?? 0); ?></div>
                <div style="color:var(--text-muted);">المواعيد</div>
            </div>
            <div style="padding:16px;background:linear-gradient(135deg,#3b82f620,#fff);border-radius:8px;border-right:4px solid #3b82f6;">
                <div style="font-size:2rem;font-weight:bold;color:#3b82f6;"><?php echo number_format($monthTotalPatients ?? 0); ?></div>
                <div style="color:var(--text-muted);">المرضى</div>
            </div>
            <div style="padding:16px;background:linear-gradient(135deg,#f59e0b20,#fff);border-radius:8px;border-right:4px solid #f59e0b;">
                <div style="font-size:2rem;font-weight:bold;color:#f59e0b;"><?php echo number_format($monthAbnormal ?? 0); ?></div>
                <div style="color:var(--text-muted);">نتائج غير طبيعية</div>
            </div>
            <div style="padding:16px;background:linear-gradient(135deg,#dc262620,#fff);border-radius:8px;border-right:4px solid #dc2626;">
                <div style="font-size:2rem;font-weight:bold;color:#dc2626;"><?php echo number_format($monthCritical ?? 0); ?></div>
                <div style="color:var(--text-muted);">نتائج حرجة</div>
            </div>
        </div>
        <canvas id="monthlyChart" style="max-height:300px;"></canvas>
        <script>
        (function(){
            var ctx = document.getElementById('monthlyChart');
            if (!ctx) return;
            var data = <?php echo json_encode($monthlyData); ?>;
            new Chart(ctx, {
                type: 'bar',
                data: {
                    labels: data.map(function(d){ return d.d; }),
                    datasets: [
                        { label: 'الطلبات', data: data.map(function(d){ return d.orders; }), backgroundColor: 'rgba(13,148,136,0.6)' },
                        { label: 'المواعيد', data: data.map(function(d){ return d.appointments || 0; }), backgroundColor: 'rgba(139,92,246,0.6)' },
                        { label: 'المرضى', data: data.map(function(d){ return d.patients; }), backgroundColor: 'rgba(59,130,246,0.6)' }
                    ]
                },
                options: { responsive: true, scales: { y: { beginAtZero: true } }, plugins: { legend: { position: 'top' } } }
            });
        })();
        </script>
    </div>
    <?php else: ?>
    <div class="card" style="margin-bottom:24px;">
        <h2>السنة <?php echo $year; ?></h2>
        <div style="display:grid;grid-template-columns:repeat(auto-fit, minmax(150px, 1fr));gap:16px;margin:20px 0;">
            <div style="padding:16px;background:linear-gradient(135deg,#0d948820,#fff);border-radius:8px;border-right:4px solid #0d9488;">
                <div style="font-size:2rem;font-weight:bold;color:#0d9488;"><?php echo number_format($yearTotalOrders ?? 0); ?></div>
                <div style="color:var(--text-muted);">إجمالي الطلبات</div>
            </div>
            <div style="padding:16px;background:linear-gradient(135deg,#8b5cf620,#fff);border-radius:8px;border-right:4px solid #8b5cf6;">
                <div style="font-size:2rem;font-weight:bold;color:#8b5cf6;"><?php echo number_format($yearTotalAppointments ?? 0); ?></div>
                <div style="color:var(--text-muted);">المواعيد</div>
            </div>
            <div style="padding:16px;background:linear-gradient(135deg,#3b82f620,#fff);border-radius:8px;border-right:4px solid #3b82f6;">
                <div style="font-size:2rem;font-weight:bold;color:#3b82f6;"><?php echo number_format($yearTotalPatients ?? 0); ?></div>
                <div style="color:var(--text-muted);">المرضى</div>
            </div>
        </div>
        <canvas id="yearlyChart" style="max-height:300px;"></canvas>
        <?php if (!empty($topAbnormalTests)): ?>
        <h3 style="margin-top:24px;">أكثر الفحوصات غير الطبيعية</h3>
        <table class="table">
            <thead><tr><th>الفحص</th><th>عدد النتائج غير الطبيعية</th></tr></thead>
            <tbody>
            <?php foreach ($topAbnormalTests as $t): ?>
            <tr><td><?php echo htmlspecialchars($t['name'], ENT_QUOTES, 'UTF-8'); ?></td><td><?php echo (int)$t['cnt']; ?></td></tr>
            <?php endforeach; ?>
            </tbody>
        </table>
        <?php endif; ?>
        <script>
        (function(){
            var ctx = document.getElementById('yearlyChart');
            if (!ctx) return;
            var data = <?php echo json_encode($yearlyData); ?>;
            var monthNames = ['','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر'];
            new Chart(ctx, {
                type: 'line',
                data: {
                    labels: data.map(function(d){ return monthNames[d.m]; }),
                    datasets: [
                        { label: 'الطلبات', data: data.map(function(d){ return d.orders; }), borderColor: '#0d9488', fill: true, tension: 0.4 },
                        { label: 'المواعيد', data: data.map(function(d){ return d.appointments || 0; }), borderColor: '#8b5cf6', fill: true, tension: 0.4 },
                        { label: 'المرضى', data: data.map(function(d){ return d.patients; }), borderColor: '#3b82f6', fill: true, tension: 0.4 }
                    ]
                },
                options: { responsive: true, scales: { y: { beginAtZero: true } }, plugins: { legend: { position: 'top' } } }
            });
        })();
        </script>
    </div>
    <?php endif; ?>
</main>
</body>
</html>
