<?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', 'multi_sheet_departments');

// Define departments mapping
$departments = [
    'الدور الرابع' => ['4th floor', '4TH'],
    'الدور الخامس' => ['5th floor', '5TH'], 
    'الدور السادس' => ['6th Floor', '6TH'],
    'الحضانة' => ['NICU', 'NICU'],
    'رعاية اطفال' => ['NICU', 'NICU'],
    'رعاية حرجة' => ['4th ICU', '4ICU'],
    'رعاية مركزة' => ['CICU', 'CICU'],
    'رعاية قلب' => ['CCU', 'CCU']
];

// Create Excel content as HTML with multiple worksheets
$filename = 'departments_report_' . date('Y-m-d_H-i-s') . '.xls';

// Set headers for Excel download
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Cache-Control: max-age=0');
header('Pragma: public');

// Output UTF-8 BOM for proper Arabic display
echo "\xEF\xBB\xBF";

// Start Excel workbook with multiple worksheets
?>
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <!--[if gte mso 9]>
    <xml>
        <x:ExcelWorkbook>
            <x:ExcelWorksheets>
                <x:ExcelWorksheet>
                    <x:Name>الملخص العام</x:Name>
                    <x:WorksheetSource HRef="#summary"/>
                </x:ExcelWorksheet>
                <?php foreach ($departments as $dept_name => $dept_info): ?>
                <x:ExcelWorksheet>
                    <x:Name><?php echo htmlspecialchars($dept_name); ?></x:Name>
                    <x:WorksheetSource HRef="#<?php echo htmlspecialchars($dept_info[1]); ?>"/>
                </x:ExcelWorksheet>
                <?php endforeach; ?>
            </x:ExcelWorksheets>
        </x:ExcelWorkbook>
    </xml>
    <![endif]-->
    <style>
        body { font-family: Arial, sans-serif; direction: rtl; margin: 0; padding: 0; }
        .dept-header { background-color: #D9E1F2; color: #1F3864; padding: 10px; text-align: center; margin-bottom: 15px; }
        table { border-collapse: collapse; width: 100%; margin-bottom: 20px; }
        th { background-color: #4472C4; color: #ffffff; padding: 8px; border: 1px solid #2e5c8a; text-align: center; font-weight: bold; }
        td { padding: 6px; border: 1px solid #ccc; text-align: center; }
        .stats-table th { background-color: #4472C4; }
        .data-table th { background-color: #4472C4; }
        .summary-table th { background-color: #4472C4; }
        tr:nth-child(odd) td { background-color: #ffffff; }
        tr:nth-child(even) td { background-color: #f2f7ff; }
        .text-right { text-align: right; }
        .status-completed { background-color: #d1e7dd; color: #0f5132; font-weight: bold; }
        .status-pending { background-color: #fff3cd; color: #664d03; font-weight: bold; }
    </style>
</head>
<body>

<!-- Summary Sheet -->
<div id="summary">
    <!-- Header Table -->
    <table>
        <tr><td colspan="10" style="background-color:#1a3a5c; color:#ffffff; font-size:16pt; font-weight:bold; text-align:center; padding:12px;">الملخص العام - تقرير الأقسام</td></tr>
        <tr><td colspan="10" style="background-color:#2e5c8a; color:#ffffff; font-size:10pt; text-align:center; padding:5px;">مستشفى مدينة نصر التخصصي | تاريخ التقرير: <?php echo date('Y-m-d H:i:s'); ?> | المستخدم: <?php echo htmlspecialchars($_SESSION['full_name']); ?></td></tr>
        <tr><td colspan="10" style="height:8px; border:none;"></td></tr>
    </table>

    <table class="summary-table">
        <tr>
            <th>اسم القسم</th>
            <th>كود القسم</th>
            <th>إجمالي التقارير</th>
            <th>التقارير المكتملة</th>
            <th>التقارير المعلقة</th>
            <th>عدد المرضى الفريد</th>
            <th>مرضى ذكور</th>
            <th>مرضى إناث</th>
            <th>معدل الإكمال %</th>
            <th>آخر تقرير</th>
        </tr>
        <?php
        $grand_total_reports = 0;
        $grand_total_completed = 0;
        $grand_total_pending = 0;
        $grand_total_patients = 0;
        $grand_total_male = 0;
        $grand_total_female = 0;
        
        foreach ($departments as $dept_name => $dept_info):
            list($unit_name, $unit_code) = $dept_info;
            
            $stmt = $conn->prepare("
                SELECT 
                    COUNT(ar.id) as total_reports,
                    SUM(CASE WHEN ar.status = 'completed' THEN 1 ELSE 0 END) as completed_reports,
                    SUM(CASE WHEN ar.status = 'pending' THEN 1 ELSE 0 END) as pending_reports,
                    COUNT(DISTINCT ar.patient_id) as unique_patients,
                    SUM(CASE WHEN p.gender = 'male' THEN 1 ELSE 0 END) as male_patients,
                    SUM(CASE WHEN p.gender = 'female' THEN 1 ELSE 0 END) as female_patients,
                    MAX(ar.report_date) as latest_report
                FROM antibiotic_reports ar
                JOIN patients p ON ar.patient_id = p.id
                JOIN units u ON ar.unit_id = u.id
                WHERE u.unit_name = ? OR u.unit_code = ?
            ");
            $stmt->execute([$unit_name, $unit_code]);
            $stats = $stmt->fetch(PDO::FETCH_ASSOC);
            
            $total_reports = $stats['total_reports'] ?? 0;
            $completed_reports = $stats['completed_reports'] ?? 0;
            $pending_reports = $stats['pending_reports'] ?? 0;
            $unique_patients = $stats['unique_patients'] ?? 0;
            $male_patients = $stats['male_patients'] ?? 0;
            $female_patients = $stats['female_patients'] ?? 0;
            $latest_report = $stats['latest_report'] ?? '-';
            
            $completion_rate = $total_reports > 0 ? round(($completed_reports / $total_reports) * 100, 1) : 0;
            
            $grand_total_reports += $total_reports;
            $grand_total_completed += $completed_reports;
            $grand_total_pending += $pending_reports;
            $grand_total_patients += $unique_patients;
            $grand_total_male += $male_patients;
            $grand_total_female += $female_patients;
        ?>
        <tr>
            <td class="text-right"><strong><?php echo $dept_name; ?></strong></td>
            <td><?php echo $unit_code; ?></td>
            <td><?php echo $total_reports; ?></td>
            <td><?php echo $completed_reports; ?></td>
            <td><?php echo $pending_reports; ?></td>
            <td><?php echo $unique_patients; ?></td>
            <td><?php echo $male_patients; ?></td>
            <td><?php echo $female_patients; ?></td>
            <td><?php echo $completion_rate; ?>%</td>
            <td><?php echo $latest_report; ?></td>
        </tr>
        <?php endforeach; ?>
        <tr style="background-color: #fff3cd; font-weight: bold;">
            <td class="text-right">الإجمالي العام</td>
            <td>-</td>
            <td><?php echo $grand_total_reports; ?></td>
            <td><?php echo $grand_total_completed; ?></td>
            <td><?php echo $grand_total_pending; ?></td>
            <td><?php echo $grand_total_patients; ?></td>
            <td><?php echo $grand_total_male; ?></td>
            <td><?php echo $grand_total_female; ?></td>
            <td><?php echo $grand_total_reports > 0 ? round(($grand_total_completed / $grand_total_reports) * 100, 1) : 0; ?>%</td>
            <td>-</td>
        </tr>
    </table>
</div>

<?php
// Generate individual department sheets
foreach ($departments as $dept_name => $dept_info):
    list($unit_name, $unit_code) = $dept_info;
    
    // Get department 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,
            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
        WHERE u.unit_name = ? OR u.unit_code = ?
        GROUP BY ar.id
        ORDER BY ar.created_at DESC
    ");
    $stmt->execute([$unit_name, $unit_code]);
    $dept_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Calculate department statistics
    $dept_total_reports = count($dept_data);
    $dept_completed_reports = count(array_filter($dept_data, function($r) { return $r['status'] == 'completed'; }));
    $dept_pending_reports = count(array_filter($dept_data, function($r) { return $r['status'] == 'pending'; }));
    $dept_male_patients = count(array_filter($dept_data, function($r) { return $r['gender'] == 'male'; }));
    $dept_female_patients = count(array_filter($dept_data, function($r) { return $r['gender'] == 'female'; }));
    $dept_unique_patients = count(array_unique(array_column($dept_data, 'mrn')));
?>

<!-- Department Sheet -->
<div id="<?php echo htmlspecialchars($unit_code); ?>">
    <!-- Department Header Row -->
    <table>
        <tr><td colspan="13" style="background-color:#D9E1F2; color:#1F3864; font-weight:bold; font-size:14pt; text-align:center; padding:10px; border:2px solid #4472C4;"><?php echo $dept_name; ?> (<?php echo $unit_name; ?>)</td></tr>
    </table>

    <!-- Department Statistics -->
    <table class="stats-table">
        <tr>
            <th>إجمالي التقارير</th>
            <th>التقارير المكتملة</th>
            <th>التقارير المعلقة</th>
            <th>عدد المرضى الفريد</th>
            <th>مرضى ذكور</th>
            <th>مرضى إناث</th>
            <th>معدل الإكمال</th>
            <th>كود القسم</th>
        </tr>
        <tr>
            <td><?php echo $dept_total_reports; ?></td>
            <td><?php echo $dept_completed_reports; ?></td>
            <td><?php echo $dept_pending_reports; ?></td>
            <td><?php echo $dept_unique_patients; ?></td>
            <td><?php echo $dept_male_patients; ?></td>
            <td><?php echo $dept_female_patients; ?></td>
            <td><?php echo $dept_total_reports > 0 ? round(($dept_completed_reports / $dept_total_reports) * 100, 1) : 0; ?>%</td>
            <td><?php echo $unit_code; ?></td>
        </tr>
    </table>

    <!-- Department Data -->
    <?php if (!empty($dept_data)): ?>
    <table class="data-table">
        <tr><td colspan="13" style="background-color:#D9E1F2; color:#1F3864; font-weight:bold; font-size:11pt; text-align:right; padding:8px 12px; border:2px solid #4472C4;">بيانات التقارير التفصيلية</td></tr>
        <tr>
            <th>رقم التقرير</th>
            <th>التاريخ</th>
            <th>اسم المريض</th>
            <th>الجنس</th>
            <th>رقم الملف</th>
            <th>المضاد الحيوي</th>
            <th>فئة المضاد</th>
            <th>سلطة المشكلة</th>
            <th>نوع المشكلة</th>
            <th>التدخل</th>
            <th>الحالة</th>
            <th>الصيدلي</th>
            <th>تاريخ الإنشاء</th>
        </tr>
        <?php foreach ($dept_data as $row): ?>
        <tr>
            <td><?php echo htmlspecialchars($row['id']); ?></td>
            <td><?php echo htmlspecialchars($row['report_date']); ?></td>
            <td class="text-right"><?php echo htmlspecialchars($row['patient_name']); ?></td>
            <td><?php echo $row['gender'] == 'male' ? 'ذكر' : 'أنثى'; ?></td>
            <td><?php echo htmlspecialchars($row['mrn']); ?></td>
            <td class="text-right"><?php echo htmlspecialchars($row['antibiotic_name']); ?></td>
            <td><?php echo htmlspecialchars($row['antibiotic_category']); ?></td>
            <td><?php 
                $authorities = [
                    'physicians' => 'الأطباء',
                    'nurses' => 'الممرضات', 
                    'pharmacist' => 'الصيدلي',
                    'other' => 'أخرى'
                ];
                echo $authorities[$row['problem_authority']] ?? $row['problem_authority'];
            ?></td>
            <td class="text-right"><?php echo htmlspecialchars($row['problem_types'] ?? '-'); ?></td>
            <td class="text-right"><?php 
                $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 class="<?php echo $row['status'] == 'completed' ? 'status-completed' : 'status-pending'; ?>">
                <?php echo $row['status'] == 'completed' ? 'مكتمل' : 'معلق'; ?>
            </td>
            <td class="text-right"><?php echo htmlspecialchars($row['pharmacist_name']); ?></td>
            <td><?php echo htmlspecialchars($row['created_at']); ?></td>
        </tr>
        <?php endforeach; ?>
    </table>
    <?php else: ?>
    <p style="text-align: center; color: #666; font-style: italic; padding: 20px;">لا توجد بيانات متاحة لهذا القسم</p>
    <?php endif; ?>

    <?php
    // Get top antibiotics for this department
    $stmt = $conn->prepare("
        SELECT 
            a.antibiotic_name,
            COUNT(ar.id) as usage_count
        FROM antibiotic_reports ar
        JOIN antibiotics a ON ar.antibiotic_id = a.id
        JOIN units u ON ar.unit_id = u.id
        WHERE u.unit_name = ? OR u.unit_code = ?
        GROUP BY a.id, a.antibiotic_name
        ORDER BY usage_count DESC
        LIMIT 10
    ");
    $stmt->execute([$unit_name, $unit_code]);
    $top_antibiotics = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (!empty($top_antibiotics)):
    ?>
    <table class="stats-table">
        <tr><td colspan="3" style="background-color:#D9E1F2; color:#1F3864; font-weight:bold; font-size:11pt; text-align:right; padding:8px 12px; border:2px solid #4472C4;">أكثر المضادات الحيوية استخداماً في <?php echo $dept_name; ?></td></tr>
        <tr>
            <th>#</th>
            <th>اسم المضاد الحيوي</th>
            <th>عدد مرات الاستخدام</th>
        </tr>
        <?php foreach ($top_antibiotics as $index => $antibiotic): ?>
        <tr>
            <td><?php echo $index + 1; ?></td>
            <td class="text-right"><?php echo htmlspecialchars($antibiotic['antibiotic_name']); ?></td>
            <td><?php echo $antibiotic['usage_count']; ?></td>
        </tr>
        <?php endforeach; ?>
    </table>
    <?php endif; ?>
</div>

<?php endforeach; ?>

<!-- Footer Table -->
<table>
    <tr><td colspan="3" style="background-color:#f2f2f2; text-align:center; padding:10px; font-size:9pt; color:#555; border:1px solid #ccc;">مستشفى مدينة نصر التخصصي | نظام الصيدلة الإكلينيكية المتكامل (ICS) - تقرير الأقسام متعدد الشيتات | تم إنشاء التقرير في: <?php echo date('Y-m-d H:i:s'); ?> | بواسطة: <?php echo htmlspecialchars($_SESSION['full_name']); ?> | الأقسام المشمولة: الدور الرابع، الخامس، السادس، الحضانة، رعاية الأطفال، الرعاية الحرجة، الرعاية المركزة، رعاية القلب</td></tr>
</table>

</body>
</html>
