<?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();

// Get filter parameters
$dashboard_from_date = $_GET['dashboard_from'] ?? date('Y-m-01');
$dashboard_to_date = $_GET['dashboard_to'] ?? date('Y-m-t');
$dashboard_unit = $_GET['dashboard_unit'] ?? '';

// Build query conditions for antibiotic reports
$ar_date_condition = "ar.report_date BETWEEN :ar_from_date AND :ar_to_date";
$ar_unit_condition = $dashboard_unit ? " AND ar.unit_id = :ar_unit_id" : "";

// Build query conditions for patient medication tracking
$pmt_date_condition = "pmt.tracking_date BETWEEN :pmt_from_date AND :pmt_to_date";
$pmt_unit_condition = $dashboard_unit ? " AND pmt.unit_id = :pmt_unit_id" : "";

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

try {
    // --- ANTIBIOTIC REPORTS STATS ---
    $stmt = $conn->prepare("SELECT COUNT(*) as total_reports FROM antibiotic_reports ar WHERE $ar_date_condition $ar_unit_condition");
    $stmt->bindParam(':ar_from_date', $dashboard_from_date);
    $stmt->bindParam(':ar_to_date', $dashboard_to_date);
    if ($dashboard_unit) {
        $stmt->bindParam(':ar_unit_id', $dashboard_unit, PDO::PARAM_INT);
    }
    $stmt->execute();
    $total_reports = $stmt->fetch(PDO::FETCH_ASSOC)['count'] ?? 0;

    $stmt = $conn->prepare("SELECT COUNT(*) as pending_reports FROM antibiotic_reports ar WHERE $ar_date_condition AND ar.status='pending' $ar_unit_condition");
    $stmt->bindParam(':ar_from_date', $dashboard_from_date);
    $stmt->bindParam(':ar_to_date', $dashboard_to_date);
    if ($dashboard_unit) {
        $stmt->bindParam(':ar_unit_id', $dashboard_unit, PDO::PARAM_INT);
    }
    $stmt->execute();
    $pending_reports = $stmt->fetch(PDO::FETCH_ASSOC)['count'] ?? 0;

    $stmt = $conn->prepare("SELECT COUNT(*) as completed_reports FROM antibiotic_reports ar WHERE $ar_date_condition AND ar.status='completed' $ar_unit_condition");
    $stmt->bindParam(':ar_from_date', $dashboard_from_date);
    $stmt->bindParam(':ar_to_date', $dashboard_to_date);
    if ($dashboard_unit) {
        $stmt->bindParam(':ar_unit_id', $dashboard_unit, PDO::PARAM_INT);
    }
    $stmt->execute();
    $completed_reports = $stmt->fetch(PDO::FETCH_ASSOC)['count'] ?? 0;

    // --- PATIENT MEDICATION TRACKING STATS ---
    $stmt = $conn->prepare("SELECT COUNT(DISTINCT medical_number) as total_patients FROM patient_medication_tracking pmt WHERE $pmt_date_condition $pmt_unit_condition");
    $stmt->bindParam(':pmt_from_date', $dashboard_from_date);
    $stmt->bindParam(':pmt_to_date', $dashboard_to_date);
    if ($dashboard_unit) {
        $stmt->bindParam(':pmt_unit_id', $dashboard_unit, PDO::PARAM_INT);
    }
    $stmt->execute();
    $total_patients = $stmt->fetch(PDO::FETCH_ASSOC)['count'] ?? 0;

    $stmt = $conn->prepare("
        SELECT 
            SUM(drug_compatibility) as drug_compatibility,
            SUM(antibiotic_program) as antibiotic_program,
            SUM(high_risk_drugs) as high_risk_drugs,
            SUM(strict_priority) as strict_priority,
            SUM(error_documentation) as error_documentation,
            SUM(drug_definition) as drug_definition
        FROM patient_medication_tracking pmt 
        WHERE $pmt_date_condition $pmt_unit_condition
    ");
    $stmt->bindParam(':pmt_from_date', $dashboard_from_date);
    $stmt->bindParam(':pmt_to_date', $dashboard_to_date);
    if ($dashboard_unit) {
        $stmt->bindParam(':pmt_unit_id', $dashboard_unit, PDO::PARAM_INT);
    }
    $stmt->execute();
    $interventions = $stmt->fetch(PDO::FETCH_ASSOC);
    $total_interventions = (
        ($interventions['drug_compatibility'] ?? 0) +
        ($interventions['antibiotic_program'] ?? 0) +
        ($interventions['high_risk_drugs'] ?? 0) +
        ($interventions['strict_priority'] ?? 0) +
        ($interventions['error_documentation'] ?? 0) +
        ($interventions['drug_definition'] ?? 0)
    );

    // --- REPORTS PER UNIT ---
    $stmt = $conn->prepare("SELECT u.unit_name, COUNT(*) as count FROM antibiotic_reports ar JOIN units u ON ar.unit_id = u.id WHERE $ar_date_condition $ar_unit_condition GROUP BY u.id, u.unit_name ORDER BY count DESC LIMIT 15");
    $stmt->bindParam(':ar_from_date', $dashboard_from_date);
    $stmt->bindParam(':ar_to_date', $dashboard_to_date);
    if ($dashboard_unit) {
        $stmt->bindParam(':ar_unit_id', $dashboard_unit, PDO::PARAM_INT);
    }
    $stmt->execute();
    $reports_per_unit = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // --- REPORTS OVER TIME ---
    $stmt = $conn->prepare("SELECT DATE(ar.report_date) as date, COUNT(*) as count FROM antibiotic_reports ar WHERE $ar_date_condition $ar_unit_condition GROUP BY DATE(ar.report_date) ORDER BY date");
    $stmt->bindParam(':ar_from_date', $dashboard_from_date);
    $stmt->bindParam(':ar_to_date', $dashboard_to_date);
    if ($dashboard_unit) {
        $stmt->bindParam(':ar_unit_id', $dashboard_unit, PDO::PARAM_INT);
    }
    $stmt->execute();
    $reports_over_time = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // --- GET UNIT NAME IF FILTERED ---
    $selected_unit_name = '';
    if ($dashboard_unit) {
        $stmt = $conn->prepare("SELECT unit_name FROM units WHERE id = ?");
        $stmt->execute([$dashboard_unit]);
        $selected_unit_name = $stmt->fetch(PDO::FETCH_ASSOC)['unit_name'] ?? '';
    }
    
    // --- GET PATIENT MEDICATION TRACKING DETAILS ---
    $stmt = $conn->prepare("SELECT * FROM patient_medication_tracking pmt WHERE $pmt_date_condition $pmt_unit_condition ORDER BY pmt.tracking_date, pmt.id");
    $stmt->bindParam(':pmt_from_date', $dashboard_from_date);
    $stmt->bindParam(':pmt_to_date', $dashboard_to_date);
    if ($dashboard_unit) {
        $stmt->bindParam(':pmt_unit_id', $dashboard_unit, PDO::PARAM_INT);
    }
    $stmt->execute();
    $patient_details = $stmt->fetchAll(PDO::FETCH_ASSOC);

} catch (Exception $e) {
    die('خطأ في جلب البيانات: ' . $e->getMessage());
}
?>
<!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: #667eea; color: #ffffff; font-size: 16pt; font-weight: bold; text-align: center; padding: 12px; }
    .h-sub   { background-color: #667eea; color: #ffffff; font-size: 11pt; text-align: center; padding: 6px; }
    .h-date  { background-color: #764ba2; 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="4" class="h-title">تقرير لوحة التحكم KPI - الصيدلة الإكلينيكية</td></tr>
    <tr><td colspan="4" class="h-sub">مستشفى مدينة نصر التخصصي &nbsp;|&nbsp; نظام الصيدلة الإكلينيكية المتكامل (ICS)</td></tr>
    <tr>
        <td colspan="4" class="h-date">
            تاريخ التقرير: <?php echo date('Y-m-d  H:i:s'); ?>
            &nbsp;|&nbsp;
            الفترة: من <?php echo htmlspecialchars($dashboard_from_date); ?> إلى <?php echo htmlspecialchars($dashboard_to_date); ?>
            <?php if ($selected_unit_name): ?>
                &nbsp;|&nbsp; القسم: <?php echo htmlspecialchars($selected_unit_name); ?>
            <?php endif; ?>
            &nbsp;|&nbsp;
            أعده: <?php echo htmlspecialchars($_SESSION['full_name']); ?>
        </td>
    </tr>
    <tr><td colspan="4" style="height:8px; border:none;"></td></tr>
</table>

<!-- ===== KPI CARDS STATS ===== -->
<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 $total_reports; ?></td>
        <td>-</td>
    </tr>
    <tr class="even-row">
        <td class="text-right">التقارير المعلقة</td>
        <td><?php echo $pending_reports; ?></td>
        <td><?php echo $pending_reports > 0 ? 'تحتاج متابعة' : '-'; ?></td>
    </tr>
    <tr class="odd-row">
        <td class="text-right">التقارير المكتملة</td>
        <td><?php echo $completed_reports; ?></td>
        <td><?php echo $total_reports > 0 ? round(($completed_reports / $total_reports) * 100, 1) . '%' : '-'; ?></td>
    </tr>
    <tr class="even-row">
        <td class="text-right">إجمالي المرضى</td>
        <td><?php echo $total_patients; ?></td>
        <td>-</td>
    </tr>
    <tr class="odd-row">
        <td class="text-right">إجمالي التدخلات</td>
        <td><?php echo $total_interventions; ?></td>
        <td>-</td>
    </tr>
    <tr><td colspan="3" style="height:8px; border:none;"></td></tr>
</table>

<!-- ===== INTERVENTIONS DISTRIBUTION ===== -->
<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>1</td>
        <td class="text-right">توافق بين الأدوية</td>
        <td><?php echo $interventions['drug_compatibility'] ?? 0; ?></td>
    </tr>
    <tr class="even-row">
        <td>2</td>
        <td class="text-right">برنامج ترشيد استهلاك المضادات الحيوية</td>
        <td><?php echo $interventions['antibiotic_program'] ?? 0; ?></td>
    </tr>
    <tr class="odd-row">
        <td>3</td>
        <td class="text-right">الأدوية عالية الخطورة</td>
        <td><?php echo $interventions['high_risk_drugs'] ?? 0; ?></td>
    </tr>
    <tr class="even-row">
        <td>4</td>
        <td class="text-right">الأدوية المتشابهة في النطق</td>
        <td><?php echo $interventions['strict_priority'] ?? 0; ?></td>
    </tr>
    <tr class="odd-row">
        <td>5</td>
        <td class="text-right">توثيق الأخطاء الدوائية</td>
        <td><?php echo $interventions['error_documentation'] ?? 0; ?></td>
    </tr>
    <tr class="even-row">
        <td>6</td>
        <td class="text-right">تعريف الأدوية</td>
        <td><?php echo $interventions['drug_definition'] ?? 0; ?></td>
    </tr>
    <tr class="total-row">
        <td colspan="2">الإجمالي</td>
        <td><?php echo $total_interventions; ?></td>
    </tr>
    <tr><td colspan="3" style="height:8px; border:none;"></td></tr>
</table>

<!-- ===== REPORTS PER UNIT ===== -->
<?php if (count($reports_per_unit) > 0): ?>
<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>
    <?php
    $total_reports_units = 0;
    foreach ($reports_per_unit as $i => $unit):
        $total_reports_units += $unit['count'];
        $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 $unit['count']; ?></td>
    </tr>
    <?php endforeach; ?>
    <tr class="total-row">
        <td colspan="2">الإجمالي</td>
        <td><?php echo $total_reports_units; ?></td>
    </tr>
    <tr><td colspan="3" style="height:8px; border:none;"></td></tr>
</table>
<?php endif; ?>

<!-- ===== REPORTS OVER TIME ===== -->
<?php if (count($reports_over_time) > 0): ?>
<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>
    <?php
    $total_reports_time = 0;
    foreach ($reports_over_time as $i => $entry):
        $total_reports_time += $entry['count'];
        $cls = $i % 2 == 0 ? 'odd-row' : 'even-row';
    ?>
    <tr class="<?php echo $cls; ?>">
        <td><?php echo $i + 1; ?></td>
        <td><?php echo date('Y-m-d', strtotime($entry['date'])); ?></td>
        <td><?php echo $entry['count']; ?></td>
    </tr>
    <?php endforeach; ?>
    <tr class="total-row">
        <td colspan="2">الإجمالي</td>
        <td><?php echo $total_reports_time; ?></td>
    </tr>
    <tr><td colspan="3" style="height:8px; border:none;"></td></tr>
</table>
<?php endif; ?>

<!-- ===== PATIENT DETAILS ===== -->
<?php if (count($patient_details) > 0): ?>
<table>
    <tr><td colspan="11" 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>
        <th class="col-hdr">توثيق الأخطاء</th>
        <th class="col-hdr">تعريف الأدوية</th>
        <th class="col-hdr">الصيدلي</th>
    </tr>
    <?php foreach ($patient_details as $i => $patient): 
        $cls = $i % 2 == 0 ? 'odd-row' : 'even-row';
    ?>
    <tr class="<?php echo $cls; ?>">
        <td><?php echo $i + 1; ?></td>
        <td><?php echo htmlspecialchars($patient['tracking_date']); ?></td>
        <td class="text-right"><?php echo htmlspecialchars($patient['patient_name']); ?></td>
        <td><?php echo htmlspecialchars($patient['medical_number']); ?></td>
        <td><?php echo $patient['drug_compatibility'] ?: 0; ?></td>
        <td><?php echo $patient['antibiotic_program'] ?: 0; ?></td>
        <td><?php echo $patient['high_risk_drugs'] ?: 0; ?></td>
        <td><?php echo $patient['strict_priority'] ?: 0; ?></td>
        <td><?php echo $patient['error_documentation'] ?: 0; ?></td>
        <td><?php echo $patient['drug_definition'] ?: 0; ?></td>
        <td><?php echo htmlspecialchars($patient['pharmacist_name']); ?></td>
    </tr>
    <?php endforeach; ?>
    <tr><td colspan="11" style="height:8px; border:none;"></td></tr>
</table>
<?php endif; ?>

<!-- ===== 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>