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

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('HTTP/1.1 403 Forbidden');
    exit('غير مصرح لك بالوصول');
}

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

// Log export activity
logActivity($conn, $_SESSION['user_id'], 'تصدير تقرير Excel', 'antibiotic_reports');

// Get data
$stmt = $conn->prepare("
    SELECT 
        ar.id,
        ar.report_date,
        ar.problem_authority,
        ar.intervention_choice,
        ar.status,
        ar.created_at,
        p.patient_name,
        p.mrn,
        p.gender,
        u.unit_name,
        COALESCE(a.antibiotic_name, ar.other_medication_name, 'غير محدد') as antibiotic_name,
        a.category as antibiotic_category,
        ar.drug_type,
        ar.other_medication_name,
        usr.full_name as pharmacist_name,
        GROUP_CONCAT(pd.problem_type SEPARATOR ', ') as problem_types
    FROM antibiotic_reports ar
    JOIN patients p ON ar.patient_id = p.id
    JOIN units u ON ar.unit_id = u.id
    LEFT JOIN antibiotics a ON ar.antibiotic_id = a.id
    JOIN users usr ON ar.reporting_pharmacist_id = usr.id
    LEFT JOIN problem_definitions pd ON ar.id = pd.report_id
    GROUP BY ar.id
    ORDER BY ar.created_at DESC
");
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Calculate statistics
$total_reports = count($data);
$completed_reports = count(array_filter($data, function($r) { return $r['status'] == 'completed'; }));
$pending_reports = count(array_filter($data, function($r) { return $r['status'] == 'pending'; }));
$male_patients = count(array_filter($data, function($r) { return $r['gender'] == 'male'; }));
$female_patients = count(array_filter($data, function($r) { return $r['gender'] == 'female'; }));

// Get unique counts
$unique_patients = count(array_unique(array_column($data, 'mrn')));
$unique_antibiotics = count(array_unique(array_column($data, 'antibiotic_name')));
$unique_units = count(array_unique(array_column($data, 'unit_name')));

// Get date range
$dates = array_column($data, 'report_date');
$min_date = !empty($dates) ? min($dates) : date('Y-m-d');
$max_date = !empty($dates) ? max($dates) : date('Y-m-d');

// Create Excel file with HTML table (Excel can open HTML tables)
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="antibiotic_reports_' . date('Y-m-d_H-i-s') . '.xls"');
header('Cache-Control: max-age=0');
header('Pragma: public');
?>
<!DOCTYPE html>
<html dir="rtl">
<head>
    <meta charset="UTF-8">
    <style>
        body {
            font-family: 'Arial', 'Tahoma', sans-serif;
            direction: rtl;
            margin: 0;
            padding: 20px;
        }
        
        .header-section {
            margin-bottom: 25px;
            padding: 20px;
            background: linear-gradient(135deg, #0d6efd 0%, #0a58ca 100%);
            color: white;
            text-align: center;
            border-radius: 8px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }
        
        .header-section h1 {
            margin: 0 0 10px 0;
            font-size: 20pt;
            font-weight: bold;
            text-shadow: 2px 2px 4px rgba(0,0,0,0.2);
        }
        
        .header-section p {
            margin: 5px 0;
            font-size: 11pt;
            opacity: 0.95;
        }
        
        .statistics-section {
            margin-bottom: 25px;
            border: 3px solid #0d6efd;
            border-radius: 8px;
            overflow: hidden;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }
        
        .statistics-section h2 {
            background: linear-gradient(135deg, #0d6efd 0%, #0a58ca 100%);
            color: white;
            padding: 12px;
            margin: 0;
            font-size: 15pt;
            text-align: center;
            font-weight: bold;
        }
        
        .stats-table {
            width: 100%;
            border-collapse: collapse;
        }
        
        .stats-table td {
            padding: 12px 15px;
            border: 1px solid #dee2e6;
            font-size: 11pt;
        }
        
        .stats-table .stat-label {
            background-color: #e7f1ff;
            font-weight: bold;
            width: 40%;
            color: #0a58ca;
        }
        
        .stats-table .stat-value {
            background-color: #fff;
            text-align: center;
            font-weight: bold;
            color: #0d6efd;
            font-size: 13pt;
        }
        
        .data-table {
            border-collapse: collapse;
            width: 100%;
            direction: rtl;
            margin-top: 25px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }
        
        .data-table th {
            background: linear-gradient(135deg, #0d6efd 0%, #0a58ca 100%);
            color: white;
            font-weight: bold;
            padding: 14px 10px;
            text-align: center;
            border: 2px solid #0a58ca;
            font-size: 11pt;
            white-space: nowrap;
            vertical-align: middle;
        }
        
        .data-table td {
            padding: 12px 10px;
            text-align: center;
            border: 1px solid #adb5bd;
            font-size: 10pt;
            vertical-align: middle;
        }
        
        .data-table tr:nth-child(even) {
            background-color: #f8f9fa;
        }
        
        .data-table tr:hover {
            background-color: #e7f1ff;
        }
        
        .status-completed {
            background-color: #d1e7dd;
            color: #0f5132;
            font-weight: bold;
            padding: 6px 12px;
            border-radius: 4px;
            border: 1px solid #a3cfbb;
        }
        
        .status-pending {
            background-color: #fff3cd;
            color: #664d03;
            font-weight: bold;
            padding: 6px 12px;
            border-radius: 4px;
            border: 1px solid #ffecb5;
        }
        
        .gender-male {
            color: #0d6efd;
            font-weight: bold;
            background-color: #cfe2ff;
            padding: 4px 8px;
            border-radius: 3px;
        }
        
        .gender-female {
            color: #d63384;
            font-weight: bold;
            background-color: #f7d6e6;
            padding: 4px 8px;
            border-radius: 3px;
        }
        
        .footer-section {
            margin-top: 25px;
            padding: 18px;
            background-color: #f8f9fa;
            border: 2px solid #dee2e6;
            border-radius: 8px;
            text-align: center;
            font-size: 10pt;
            color: #495057;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }
        
        .footer-section p {
            margin: 5px 0;
        }
    </style>
</head>
<body>
    <!-- Header Section -->
    <table>
        <tr><td colspan="14" style="background-color:#1a3a5c; color:#ffffff; font-size:16pt; font-weight:bold; text-align:center; padding:12px;">تقرير المضادات الحيوية الشامل</td></tr>
        <tr><td colspan="14" style="background-color:#1a3a5c; color:#ffffff; font-size:11pt; text-align:center; padding:6px;">مستشفى مدينة نصر التخصصي &nbsp;|&nbsp; Nasr City Specialized Hospital</td></tr>
        <tr><td colspan="14" style="background-color:#2e5c8a; color:#ffffff; font-size:10pt; text-align:center; padding:5px;">تاريخ التصدير: <?php echo date('Y-m-d H:i A'); ?> &nbsp;|&nbsp; أعده: <?php echo htmlspecialchars($_SESSION['full_name']); ?></td></tr>
        <tr><td colspan="14" style="height:8px; border:none;"></td></tr>
    </table>

    <!-- Statistics Section -->
    <table>
        <tr><td colspan="4" style="background-color:#D9E1F2; color:#1F3864; font-weight:bold; font-size:12pt; text-align:right; padding:8px 12px; border:2px solid #4472C4;">📊 الإحصائيات العامة</td></tr>
        <tr>
            <th style="background-color:#4472C4; color:#fff; padding:8px;">البيان</th>
            <th style="background-color:#4472C4; color:#fff; padding:8px;">القيمة</th>
            <th style="background-color:#4472C4; color:#fff; padding:8px;">البيان</th>
            <th style="background-color:#4472C4; color:#fff; padding:8px;">القيمة</th>
        </tr>
        <tr style="background-color:#f2f7ff;">
            <td style="text-align:right;">إجمالي التقارير</td><td><?php echo $total_reports; ?></td>
            <td style="text-align:right;">التقارير المكتملة</td><td><?php echo $completed_reports; ?></td>
        </tr>
        <tr style="background-color:#fff;">
            <td style="text-align:right;">التقارير المعلقة</td><td><?php echo $pending_reports; ?></td>
            <td style="text-align:right;">عدد المرضى الفريد</td><td><?php echo $unique_patients; ?></td>
        </tr>
        <tr style="background-color:#f2f7ff;">
            <td style="text-align:right;">مرضى ذكور</td><td><?php echo $male_patients; ?></td>
            <td style="text-align:right;">مرضى إناث</td><td><?php echo $female_patients; ?></td>
        </tr>
        <tr style="background-color:#fff;">
            <td style="text-align:right;">المضادات الحيوية المستخدمة</td><td><?php echo $unique_antibiotics; ?></td>
            <td style="text-align:right;">عدد الأقسام</td><td><?php echo $unique_units; ?></td>
        </tr>
        <tr style="background-color:#f2f7ff;">
            <td style="text-align:right;">أقدم تقرير</td><td><?php echo $min_date; ?></td>
            <td style="text-align:right;">أحدث تقرير</td><td><?php echo $max_date; ?></td>
        </tr>
        <tr style="background-color:#fff;">
            <td style="text-align:right;">معدل الإكمال</td>
            <td><?php echo $total_reports > 0 ? round(($completed_reports/$total_reports)*100,1) : 0; ?>%</td>
            <td style="text-align:right;">الفترة الزمنية (أيام)</td>
            <td><?php echo !empty($dates) ? round((strtotime($max_date)-strtotime($min_date))/(60*60*24)) : 0; ?></td>
        </tr>
        <tr><td colspan="4" style="height:8px; border:none;"></td></tr>
    </table>

    <!-- Data Table -->
    <table>
        <tr><td colspan="14" style="background-color:#D9E1F2; color:#1F3864; font-weight:bold; font-size:12pt; text-align:right; padding:8px 12px; border:2px solid #4472C4;">📋 بيانات التقارير التفصيلية</td></tr>
        <thead>
            <tr>
                <th style="width: 70px; min-width: 70px;">رقم التقرير</th>
                <th style="width: 110px; min-width: 110px;">التاريخ</th>
                <th style="width: 180px; min-width: 180px;">اسم المريض</th>
                <th style="width: 90px; min-width: 90px;">الجنس</th>
                <th style="width: 110px; min-width: 110px;">رقم الملف</th>
                <th style="width: 140px; min-width: 140px;">القسم</th>
                <th style="width: 180px; min-width: 180px;">المضاد الحيوي</th>
                <th style="width: 140px; min-width: 140px;">فئة المضاد</th>
                <th style="width: 140px; min-width: 140px;">سلطة المشكلة</th>
                <th style="width: 220px; min-width: 220px;">نوع المشكلة</th>
                <th style="width: 220px; min-width: 220px;">التدخل</th>
                <th style="width: 110px; min-width: 110px;">الحالة</th>
                <th style="width: 150px; min-width: 150px;">الصيدلي</th>
                <th style="width: 160px; min-width: 160px;">تاريخ الإنشاء</th>
            </tr>
        </thead>
        <tbody>
            <?php if (empty($data)): ?>
                <tr>
                    <td colspan="14" style="text-align: center; padding: 30px; color: #999;">لا توجد بيانات</td>
                </tr>
            <?php else: ?>
                <?php foreach ($data as $row): ?>
                <tr>
                    <td><?php echo htmlspecialchars($row['id']); ?></td>
                    <td><?php echo htmlspecialchars($row['report_date']); ?></td>
                    <td style="text-align: right;"><?php echo htmlspecialchars($row['patient_name']); ?></td>
                    <td class="<?php echo $row['gender'] == 'male' ? 'gender-male' : 'gender-female'; ?>">
                        <?php echo $row['gender'] == 'male' ? 'ذكر' : 'أنثى'; ?>
                    </td>
                    <td><?php echo htmlspecialchars($row['mrn']); ?></td>
                    <td><?php echo htmlspecialchars($row['unit_name']); ?></td>
                    <td style="text-align: right;"><?php echo htmlspecialchars($row['antibiotic_name']); ?></td>
                    <td><?php echo htmlspecialchars($row['antibiotic_category']); ?></td>
                    <td><?php echo htmlspecialchars(translateProblemAuthority($row['problem_authority'])); ?></td>
                    <td style="text-align: right;"><?php echo htmlspecialchars($row['problem_types'] ?? '-'); ?></td>
                    <td style="text-align: right;"><?php 
                        // Translate intervention choices from JSON to Arabic
                        $interventions = json_decode($row['intervention_choice'], true);
                        if (is_array($interventions)) {
                            $translations = [
                                'drug_discontinuation' => 'إيقاف الدواء',
                                'monitoring' => 'المراقبة',
                                'changing_another_agent' => 'تغيير إلى دواء آخر',
                                'changing_administration_route' => 'تغيير طريقة الإعطاء',
                                'addition_new_drug' => 'إضافة دواء جديد',
                                'change_medication' => 'تغيير الدواء',
                                'patient_own_medication' => 'دواء المريض الخاص',
                                'illegible_abbreviations' => 'اختصارات غير واضحة',
                                'medication_dispensing' => 'صرف الدواء',
                                'illegible_hand_writing' => 'خط يد غير واضح'
                            ];
                            $translated = array_map(function($item) use ($translations) {
                                return $translations[$item] ?? $item;
                            }, $interventions);
                            echo htmlspecialchars(implode(', ', $translated));
                        } else {
                            echo htmlspecialchars($row['intervention_choice'] ?? '-');
                        }
                    ?></td>
                    <td>
                        <span class="<?php echo $row['status'] == 'completed' ? 'status-completed' : 'status-pending'; ?>">
                            <?php echo $row['status'] == 'completed' ? 'مكتمل' : 'معلق'; ?>
                        </span>
                    </td>
                    <td style="text-align: right;"><?php echo htmlspecialchars($row['pharmacist_name']); ?></td>
                    <td><?php echo htmlspecialchars($row['created_at']); ?></td>
                </tr>
                <?php endforeach; ?>
            <?php endif; ?>
        </tbody>
    </table>

    <!-- Footer Section -->
    <table>
        <tr><td colspan="14" style="height:8px; border:none;"></td></tr>
        <tr>
            <td colspan="14" style="background-color:#f2f2f2; text-align:center; padding:10px; font-size:9pt; color:#555; border:1px solid #ccc;">
                مستشفى مدينة نصر التخصصي &nbsp;|&nbsp; نظام الصيدلة الإكلينيكية المتكامل (ICS) &nbsp;|&nbsp;
                تم التصدير بواسطة: <?php echo htmlspecialchars($_SESSION['full_name']); ?> &nbsp;|&nbsp;
                التاريخ: <?php echo date('Y-m-d H:i A'); ?>
            </td>
        </tr>
    </table>
</body>
</html>
