<?php
session_start();
require_once '../config/database.php';
require_once '../includes/functions.php';

if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit();
}

$db = new Database();
$conn = $db->getConnection();

header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="dashboard_report_' . date('Y-m-d_H-i-s') . '.xls"');
header('Pragma: no-cache');
header('Expires: 0');
echo "\xEF\xBB\xBF";

try {
    $stmt = $conn->query("SELECT COUNT(*) as total_patients, SUM(CASE WHEN DATE(created_at)=CURDATE() THEN 1 ELSE 0 END) as today_patients, SUM(CASE WHEN MONTH(created_at)=MONTH(CURDATE()) THEN 1 ELSE 0 END) as month_patients FROM patients");
    $patient_stats = $stmt->fetch(PDO::FETCH_ASSOC);

    $stmt = $conn->query("SELECT COUNT(*) as total_reports, SUM(CASE WHEN status='pending' THEN 1 ELSE 0 END) as pending_reports, SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END) as completed_reports, SUM(CASE WHEN DATE(created_at)=CURDATE() THEN 1 ELSE 0 END) as today_reports, SUM(CASE WHEN MONTH(created_at)=MONTH(CURDATE()) THEN 1 ELSE 0 END) as month_reports FROM antibiotic_reports");
    $report_stats = $stmt->fetch(PDO::FETCH_ASSOC);

    $stmt = $conn->query("SELECT u.unit_name, u.unit_code, COUNT(DISTINCT ar.patient_id) as patient_count, COUNT(ar.id) as report_count, SUM(CASE WHEN ar.status='pending' THEN 1 ELSE 0 END) as pending_count, SUM(CASE WHEN ar.status='completed' THEN 1 ELSE 0 END) as completed_count FROM units u LEFT JOIN antibiotic_reports ar ON u.id=ar.unit_id WHERE u.is_active=1 GROUP BY u.id,u.unit_name,u.unit_code ORDER BY report_count DESC");
    $unit_details = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $conn->query("SELECT a.antibiotic_name, a.category, COUNT(ar.id) as usage_count FROM antibiotics a LEFT JOIN antibiotic_reports ar ON a.id=ar.antibiotic_id WHERE a.is_active=1 GROUP BY a.id,a.antibiotic_name,a.category ORDER BY usage_count DESC LIMIT 15");
    $top_antibiotics = $stmt->fetchAll(PDO::FETCH_ASSOC);

} catch (Exception $e) {
    die('خطأ في جلب البيانات');
}
?>
<!DOCTYPE html>
<html dir="rtl" lang="ar">
<head>
<meta charset="UTF-8">
<style>
    body { font-family: Arial, sans-serif; direction: rtl; font-size: 11pt; }
    table { border-collapse: collapse; width: 100%; margin-bottom: 15px; }
    th, td { border: 1px solid #000; padding: 6px 10px; text-align: center; vertical-align: middle; }
    .h-title { background-color: #2E5C8A; color: #ffffff; font-size: 16pt; font-weight: bold; text-align: center; padding: 12px; }
    .h-sub   { background-color: #2E5C8A; color: #ffffff; font-size: 11pt; text-align: center; padding: 6px; }
    .h-date  { background-color: #3A6EA5; color: #ffffff; font-size: 10pt; text-align: center; padding: 5px; }
    .sec-hdr { background-color: #D9E1F2; color: #1F3864; font-weight: bold; font-size: 12pt; text-align: right; padding: 8px 12px; border: 2px solid #4472C4; }
    .col-hdr { background-color: #4472C4; color: #ffffff; font-weight: bold; font-size: 10pt; }
    .total-row { background-color: #FFF2CC; font-weight: bold; }
    .even-row { background-color: #F2F7FF; }
    .odd-row  { background-color: #FFFFFF; }
    .footer-row { background-color: #F2F2F2; font-size: 9pt; color: #555; }
    td.text-right { text-align: right; }
</style>
</head>
<body>

<!-- ===== HEADER ===== -->
<table>
    <tr><td colspan="3" class="h-title">تقرير لوحة التحكم الشامل</td></tr>
    <tr><td colspan="3" class="h-sub">مستشفى مدينة نصر التخصصي &nbsp;|&nbsp; نظام الصيدلة الإكلينيكية المتكامل (ICS)</td></tr>
    <tr><td colspan="3" class="h-date">تاريخ التقرير: <?php echo date('Y-m-d  H:i:s'); ?> &nbsp;|&nbsp; أعده: <?php echo htmlspecialchars($_SESSION['full_name']); ?></td></tr>
    <tr><td colspan="3" style="height:8px; border:none;"></td></tr>
</table>

<!-- ===== STATISTICS ===== -->
<table>
    <tr><td colspan="3" class="sec-hdr">📊 الإحصائيات العامة</td></tr>
    <tr>
        <th class="col-hdr">البيان</th>
        <th class="col-hdr">العدد</th>
        <th class="col-hdr">ملاحظات</th>
    </tr>
    <tr class="odd-row">
        <td class="text-right">إجمالي المرضى</td>
        <td><?php echo $patient_stats['total_patients']; ?></td>
        <td><?php echo $patient_stats['month_patients']; ?> هذا الشهر</td>
    </tr>
    <tr class="even-row">
        <td class="text-right">مرضى جدد اليوم</td>
        <td><?php echo $patient_stats['today_patients']; ?></td>
        <td>-</td>
    </tr>
    <tr class="odd-row">
        <td class="text-right">إجمالي التقارير</td>
        <td><?php echo $report_stats['total_reports']; ?></td>
        <td><?php echo $report_stats['month_reports']; ?> هذا الشهر</td>
    </tr>
    <tr class="even-row">
        <td class="text-right">التقارير المكتملة</td>
        <td><?php echo $report_stats['completed_reports']; ?></td>
        <td><?php echo round(($report_stats['completed_reports'] / max($report_stats['total_reports'],1))*100,1); ?>%</td>
    </tr>
    <tr class="odd-row">
        <td class="text-right">التقارير المعلقة</td>
        <td><?php echo $report_stats['pending_reports']; ?></td>
        <td>تحتاج متابعة</td>
    </tr>
    <tr class="even-row">
        <td class="text-right">تقارير اليوم</td>
        <td><?php echo $report_stats['today_reports']; ?></td>
        <td>-</td>
    </tr>
    <tr><td colspan="3" style="height:8px; border:none;"></td></tr>
</table>

<!-- ===== UNITS ===== -->
<table>
    <tr><td colspan="8" class="sec-hdr">🏥 تقرير الأقسام التفصيلي</td></tr>
    <tr>
        <th class="col-hdr">#</th>
        <th class="col-hdr">اسم القسم</th>
        <th class="col-hdr">كود القسم</th>
        <th class="col-hdr">عدد المرضى</th>
        <th class="col-hdr">عدد التقارير</th>
        <th class="col-hdr">التقارير المكتملة</th>
        <th class="col-hdr">التقارير المعلقة</th>
        <th class="col-hdr">نسبة الإنجاز</th>
    </tr>
    <?php
    $tp=0; $tr=0; $tc=0; $tpn=0;
    foreach ($unit_details as $i => $unit):
        $tp  += $unit['patient_count'];
        $tr  += $unit['report_count'];
        $tc  += $unit['completed_count'];
        $tpn += $unit['pending_count'];
        $rate = $unit['report_count']>0 ? round(($unit['completed_count']/$unit['report_count'])*100,1) : 0;
        $cls  = $i%2==0 ? 'odd-row' : 'even-row';
    ?>
    <tr class="<?php echo $cls; ?>">
        <td><?php echo $i+1; ?></td>
        <td class="text-right"><?php echo htmlspecialchars($unit['unit_name']); ?></td>
        <td><?php echo htmlspecialchars($unit['unit_code']); ?></td>
        <td><?php echo $unit['patient_count']; ?></td>
        <td><?php echo $unit['report_count']; ?></td>
        <td><?php echo $unit['completed_count']; ?></td>
        <td><?php echo $unit['pending_count']; ?></td>
        <td><?php echo $rate; ?>%</td>
    </tr>
    <?php endforeach; ?>
    <tr class="total-row">
        <td colspan="3">الإجمالي</td>
        <td><?php echo $tp; ?></td>
        <td><?php echo $tr; ?></td>
        <td><?php echo $tc; ?></td>
        <td><?php echo $tpn; ?></td>
        <td><?php echo $tr>0 ? round(($tc/$tr)*100,1) : 0; ?>%</td>
    </tr>
    <tr><td colspan="8" style="height:8px; border:none;"></td></tr>
</table>

<!-- ===== TOP ANTIBIOTICS ===== -->
<table>
    <tr><td colspan="4" class="sec-hdr">💊 أكثر المضادات الحيوية استخداماً</td></tr>
    <tr>
        <th class="col-hdr">#</th>
        <th class="col-hdr">اسم المضاد الحيوي</th>
        <th class="col-hdr">الفئة</th>
        <th class="col-hdr">عدد مرات الاستخدام</th>
    </tr>
    <?php foreach ($top_antibiotics as $i => $ab): ?>
    <tr class="<?php echo $i%2==0?'odd-row':'even-row'; ?>">
        <td><?php echo $i+1; ?></td>
        <td class="text-right"><?php echo htmlspecialchars($ab['antibiotic_name']); ?></td>
        <td><?php echo htmlspecialchars($ab['category']); ?></td>
        <td><?php echo $ab['usage_count']; ?></td>
    </tr>
    <?php endforeach; ?>
    <tr><td colspan="4" style="height:8px; border:none;"></td></tr>
</table>

<!-- ===== PERFORMANCE ===== -->
<table>
    <tr><td colspan="3" class="sec-hdr">📈 ملخص الأداء</td></tr>
    <tr>
        <th class="col-hdr">المؤشر</th>
        <th class="col-hdr">القيمة</th>
        <th class="col-hdr">التقييم</th>
    </tr>
    <tr class="odd-row">
        <td class="text-right">معدل إنجاز التقارير</td>
        <td><?php echo round(($report_stats['completed_reports']/max($report_stats['total_reports'],1))*100,1); ?>%</td>
        <td><?php echo ($report_stats['completed_reports']/max($report_stats['total_reports'],1))>0.7?'ممتاز':'يحتاج تحسين'; ?></td>
    </tr>
    <tr class="even-row">
        <td class="text-right">متوسط التقارير اليومية</td>
        <td><?php echo round($report_stats['month_reports']/30,1); ?></td>
        <td>-</td>
    </tr>
    <tr class="odd-row">
        <td class="text-right">عدد الأقسام النشطة</td>
        <td><?php echo count($unit_details); ?></td>
        <td>-</td>
    </tr>
    <tr class="even-row">
        <td class="text-right">متوسط المرضى لكل قسم</td>
        <td><?php echo count($unit_details)>0 ? round($tp/count($unit_details),1) : 0; ?></td>
        <td>-</td>
    </tr>
    <tr><td colspan="3" style="height:8px; border:none;"></td></tr>
</table>

<!-- ===== FOOTER ===== -->
<table>
    <tr>
        <td colspan="3" class="footer-row" style="text-align:center; padding:10px;">
            مستشفى مدينة نصر التخصصي &nbsp;|&nbsp; نظام الصيدلة الإكلينيكية المتكامل (ICS) &nbsp;|&nbsp;
            تم الإنشاء: <?php echo date('Y-m-d H:i:s'); ?> &nbsp;|&nbsp;
            بواسطة: <?php echo htmlspecialchars($_SESSION['full_name']); ?>
        </td>
    </tr>
</table>

</body>
</html>
