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

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

// Set headers for Excel download
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="departments_comparison_' . date('Y-m-d_H-i-s') . '.xls"');
header('Cache-Control: max-age=0');
header('Pragma: public');

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

// Get monthly data for trends
$monthly_data = [];
$stmt = $conn->query("
    SELECT 
        DATE_FORMAT(ar.report_date, '%Y-%m') as month,
        u.unit_name,
        u.unit_code,
        COUNT(ar.id) as reports_count,
        SUM(CASE WHEN ar.status = 'completed' THEN 1 ELSE 0 END) as completed_count
    FROM antibiotic_reports ar
    JOIN units u ON ar.unit_id = u.id
    WHERE ar.report_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
    GROUP BY DATE_FORMAT(ar.report_date, '%Y-%m'), u.id
    ORDER BY month DESC, u.unit_name
");
$monthly_results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Organize monthly data by department
foreach ($monthly_results as $row) {
    $month = $row['month'];
    $unit_name = $row['unit_name'];
    
    if (!isset($monthly_data[$month])) {
        $monthly_data[$month] = [];
    }
    
    $monthly_data[$month][$unit_name] = [
        'reports' => $row['reports_count'],
        'completed' => $row['completed_count'],
        'rate' => $row['reports_count'] > 0 ? round(($row['completed_count'] / $row['reports_count']) * 100, 1) : 0
    ];
}

// Get top antibiotics by department
$antibiotics_by_dept = [];
foreach ($departments as $dept_name => $dept_info) {
    list($unit_name, $unit_code) = $dept_info;
    
    $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 5
    ");
    $stmt->execute([$unit_name, $unit_code]);
    $antibiotics_by_dept[$dept_name] = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
?>
<!DOCTYPE html>
<html dir="rtl" lang="ar">
<head>
    <meta charset="UTF-8">
    <style>
        body {
            font-family: 'Arial', 'Tahoma', sans-serif;
            direction: rtl;
            margin: 0;
            padding: 15px;
            font-size: 10pt;
        }
        
        .header {
            margin-bottom: 25px;
            padding: 20px;
            background: linear-gradient(135deg, #8e44ad 0%, #9b59b6 100%);
            color: white;
            text-align: center;
            border-radius: 8px;
        }
        
        .header h1 {
            margin: 0 0 10px 0;
            font-size: 18pt;
            font-weight: bold;
        }
        
        .section {
            margin-bottom: 30px;
            page-break-inside: avoid;
        }
        
        .section-title {
            background: linear-gradient(135deg, #3498db 0%, #2980b9 100%);
            color: white;
            padding: 12px;
            margin-bottom: 15px;
            text-align: center;
            border-radius: 6px;
            font-size: 14pt;
            font-weight: bold;
        }
        
        .comparison-table {
            border-collapse: collapse;
            width: 100%;
            margin-bottom: 20px;
        }
        
        .comparison-table th {
            background: linear-gradient(135deg, #27ae60 0%, #229954 100%);
            color: white;
            font-weight: bold;
            padding: 10px 8px;
            text-align: center;
            border: 1px solid #229954;
            font-size: 9pt;
            white-space: nowrap;
        }
        
        .comparison-table td {
            padding: 8px 6px;
            text-align: center;
            border: 1px solid #bdc3c7;
            font-size: 9pt;
        }
        
        .comparison-table tr:nth-child(odd) td {
            background-color: #ffffff;
        }
        
        .comparison-table tr:nth-child(even) td {
            background-color: #f2f7ff;
        }
        
        .dept-name {
            text-align: right !important;
            font-weight: bold;
            background-color: #ecf0f1 !important;
            color: #2c3e50;
        }
        
        .rate-excellent { background-color: #d1e7dd; color: #0f5132; font-weight: bold; }
        .rate-good { background-color: #cff4fc; color: #055160; font-weight: bold; }
        .rate-average { background-color: #fff3cd; color: #664d03; font-weight: bold; }
        .rate-poor { background-color: #f8d7da; color: #721c24; font-weight: bold; }
        
        .chart-section {
            margin: 20px 0;
            padding: 15px;
            background-color: #f8f9fa;
            border-radius: 6px;
            border: 1px solid #dee2e6;
        }
        
        .chart-title {
            font-weight: bold;
            margin-bottom: 10px;
            color: #495057;
            font-size: 12pt;
        }
        
        .bar-chart {
            margin: 10px 0;
        }
        
        .bar-item {
            margin: 5px 0;
            display: flex;
            align-items: center;
        }
        
        .bar-label {
            width: 120px;
            font-size: 9pt;
            text-align: right;
        }
        
        .bar-visual {
            flex: 1;
            height: 20px;
            background-color: #e9ecef;
            border-radius: 10px;
            overflow: hidden;
            margin: 0 10px;
        }
        
        .bar-fill {
            height: 100%;
            border-radius: 10px;
            transition: width 0.3s ease;
        }
        
        .bar-value {
            width: 50px;
            text-align: center;
            font-size: 9pt;
            font-weight: bold;
        }
        
        .monthly-table {
            border-collapse: collapse;
            width: 100%;
            font-size: 8pt;
        }
        
        .monthly-table th {
            background-color: #4472C4;
            color: #ffffff;
            padding: 8px 4px;
            text-align: center;
            border: 1px solid #2e5c8a;
            font-size: 8pt;
        }
        
        .monthly-table td {
            padding: 6px 4px;
            text-align: center;
            border: 1px solid #dee2e6;
            font-size: 8pt;
        }
        
        .monthly-table tr:nth-child(odd) td { background-color: #ffffff; }
        .monthly-table tr:nth-child(even) td { background-color: #f2f7ff; }
        
        .antibiotics-grid {
            display: grid;
            grid-template-columns: repeat(2, 1fr);
            gap: 15px;
            margin-top: 15px;
        }
        
        .antibiotic-card {
            border: 1px solid #dee2e6;
            border-radius: 6px;
            padding: 10px;
            background-color: #fff;
        }
        
        .antibiotic-card h4 {
            margin: 0 0 10px 0;
            color: #495057;
            font-size: 11pt;
            text-align: center;
            background-color: #e9ecef;
            padding: 8px;
            border-radius: 4px;
        }
        
        .antibiotic-list {
            list-style: none;
            padding: 0;
            margin: 0;
        }
        
        .antibiotic-list li {
            padding: 4px 0;
            border-bottom: 1px solid #f8f9fa;
            font-size: 9pt;
            display: flex;
            justify-content: space-between;
        }
        
        .antibiotic-list li:last-child {
            border-bottom: none;
        }
    </style>
</head>
<body>

<!-- Header Table -->
<table>
    <tr><td colspan="8" style="background-color:#1a3a5c; color:#ffffff; font-size:16pt; font-weight:bold; text-align:center; padding:12px;">تقرير مقارنة الأقسام التفصيلي</td></tr>
    <tr><td colspan="8" style="background-color:#2e5c8a; color:#ffffff; font-size:10pt; text-align:center; padding:5px;">مستشفى مدينة نصر التخصصي - Nasr City Specialized Hospital | تحليل شامل ومقارنة أداء الأقسام | تاريخ التصدير: <?php echo date('Y-m-d H:i:s'); ?></td></tr>
    <tr><td colspan="8" style="height:8px; border:none;"></td></tr>
</table>

<!-- Overall Comparison -->
<div class="section">
    <table class="comparison-table">
        <tr><td colspan="8" 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>اسم القسم</th>
                <th>كود القسم</th>
                <th>إجمالي التقارير</th>
                <th>المكتملة</th>
                <th>المعلقة</th>
                <th>المرضى</th>
                <th>معدل الإكمال</th>
                <th>التقييم</th>
            </tr>
        </thead>
        <tbody>
            <?php
            $dept_comparison = [];
            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
                    FROM antibiotic_reports ar
                    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 = $stats['total_reports'] ?? 0;
                $completed = $stats['completed_reports'] ?? 0;
                $pending = $stats['pending_reports'] ?? 0;
                $patients = $stats['unique_patients'] ?? 0;
                $rate = $total > 0 ? round(($completed / $total) * 100, 1) : 0;
                
                $rate_class = 'rate-poor';
                $rating = 'يحتاج تحسين';
                if ($rate >= 90) {
                    $rate_class = 'rate-excellent';
                    $rating = 'ممتاز';
                } elseif ($rate >= 70) {
                    $rate_class = 'rate-good';
                    $rating = 'جيد';
                } elseif ($rate >= 50) {
                    $rate_class = 'rate-average';
                    $rating = 'متوسط';
                }
                
                $dept_comparison[$dept_name] = [
                    'code' => $unit_code,
                    'total' => $total,
                    'completed' => $completed,
                    'rate' => $rate
                ];
            ?>
            <tr>
                <td class="dept-name"><?php echo $dept_name; ?></td>
                <td><?php echo $unit_code; ?></td>
                <td><?php echo $total; ?></td>
                <td><?php echo $completed; ?></td>
                <td><?php echo $pending; ?></td>
                <td><?php echo $patients; ?></td>
                <td class="<?php echo $rate_class; ?>"><?php echo $rate; ?>%</td>
                <td><?php echo $rating; ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

<!-- Performance Chart - replaced with table for Excel compatibility -->
<div class="section">
    <table class="comparison-table">
        <tr><td colspan="3" 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>القسم</th>
            <th>معدل الإكمال</th>
            <th>التقييم</th>
        </tr>
        <?php
        uasort($dept_comparison, function($a, $b) {
            return $b['rate'] <=> $a['rate'];
        });
        $max_rate = max(array_column($dept_comparison, 'rate'));
        if ($max_rate == 0) $max_rate = 100;
        foreach ($dept_comparison as $dept_name => $data):
            $color = '#dc3545';
            if ($data['rate'] >= 90) $color = '#28a745';
            elseif ($data['rate'] >= 70) $color = '#17a2b8';
            elseif ($data['rate'] >= 50) $color = '#ffc107';
        ?>
        <tr>
            <td class="dept-name"><?php echo $dept_name; ?></td>
            <td><?php echo $data['rate']; ?>%</td>
            <td style="color:<?php echo $color; ?>; font-weight:bold;"><?php
                if ($data['rate'] >= 90) echo 'ممتاز';
                elseif ($data['rate'] >= 70) echo 'جيد';
                elseif ($data['rate'] >= 50) echo 'متوسط';
                else echo 'يحتاج تحسين';
            ?></td>
        </tr>
        <?php endforeach; ?>
    </table>
</div>

<!-- Monthly Trends -->
<?php if (!empty($monthly_data)): ?>
<div class="section">
    <table class="monthly-table">
        <tr><td colspan="<?php echo count($departments) + 1; ?>" style="background-color:#D9E1F2; color:#1F3864; font-weight:bold; font-size:12pt; text-align:right; padding:8px 12px; border:2px solid #4472C4;">الاتجاهات الشهرية (آخر 6 أشهر)</td></tr>
        <thead>
            <tr>
                <th>الشهر</th>
                <?php foreach ($departments as $dept_name => $dept_info): ?>
                <th><?php echo $dept_name; ?></th>
                <?php endforeach; ?>
            </tr>
        </thead>
        <tbody>
            <?php foreach (array_keys($monthly_data) as $month): ?>
            <tr>
                <td><strong><?php echo $month; ?></strong></td>
                <?php foreach ($departments as $dept_name => $dept_info): 
                    list($unit_name, $unit_code) = $dept_info;
                    $data = $monthly_data[$month][$unit_name] ?? ['reports' => 0, 'completed' => 0, 'rate' => 0];
                ?>
                <td>
                    <?php echo $data['reports']; ?> تقرير<br>
                    <small>(<?php echo $data['rate']; ?>% مكتمل)</small>
                </td>
                <?php endforeach; ?>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>
<?php endif; ?>

<!-- Top Antibiotics by Department -->
<div class="section">
    <table class="comparison-table">
        <tr><td colspan="3" 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>القسم</th>
            <th>المضاد الحيوي</th>
            <th>عدد الاستخدامات</th>
        </tr>
        <?php foreach ($antibiotics_by_dept as $dept_name => $antibiotics): ?>
        <?php if (!empty($antibiotics)): ?>
        <?php foreach ($antibiotics as $antibiotic): ?>
        <tr>
            <td class="dept-name"><?php echo $dept_name; ?></td>
            <td><?php echo htmlspecialchars($antibiotic['antibiotic_name']); ?></td>
            <td><?php echo $antibiotic['usage_count']; ?></td>
        </tr>
        <?php endforeach; ?>
        <?php else: ?>
        <tr>
            <td class="dept-name"><?php echo $dept_name; ?></td>
            <td colspan="2" style="text-align:center; color:#6c757d; font-style:italic;">لا توجد بيانات</td>
        </tr>
        <?php endif; ?>
        <?php endforeach; ?>
    </table>
</div>

<!-- Summary and Recommendations -->
<div class="section">
    <table class="comparison-table">
        <tr><td colspan="3" style="background-color:#D9E1F2; color:#1F3864; font-weight:bold; font-size:12pt; text-align:right; padding:8px 12px; border:2px solid #4472C4;">الملخص والتوصيات</td></tr>
        <?php
        $total_all_reports = array_sum(array_column($dept_comparison, 'total'));
        $total_all_completed = array_sum(array_column($dept_comparison, 'completed'));
        $overall_rate = $total_all_reports > 0 ? round(($total_all_completed / $total_all_reports) * 100, 1) : 0;
        $best_dept = array_keys($dept_comparison, max($dept_comparison))[0];
        $worst_dept = array_keys($dept_comparison, min($dept_comparison))[0];
        ?>
        
        <table class="comparison-table">
            <tr>
                <th>المؤشر</th>
                <th>القيمة</th>
                <th>التوصية</th>
            </tr>
            <tr>
                <td class="dept-name">المعدل العام للإكمال</td>
                <td><strong><?php echo $overall_rate; ?>%</strong></td>
                <td><?php echo $overall_rate >= 80 ? 'أداء جيد، حافظ عليه' : 'يحتاج تحسين'; ?></td>
            </tr>
            <tr>
                <td class="dept-name">أفضل قسم أداءً</td>
                <td><strong><?php echo $best_dept; ?></strong></td>
                <td>استخدم كنموذج للأقسام الأخرى</td>
            </tr>
            <tr>
                <td class="dept-name">القسم الذي يحتاج تحسين</td>
                <td><strong><?php echo $worst_dept; ?></strong></td>
                <td>يحتاج متابعة وتدريب إضافي</td>
            </tr>
            <tr>
                <td class="dept-name">إجمالي التقارير</td>
                <td><strong><?php echo $total_all_reports; ?></strong></td>
                <td>حجم عمل جيد</td>
            </tr>
        </table>
        
        <table class="comparison-table" style="margin-top:10px;">
            <tr><td colspan="3" style="background-color:#fff3cd; color:#856404; font-weight:bold; padding:8px 12px; border:1px solid #ffeaa7;">توصيات للتحسين: تنظيم دورات تدريبية للأقسام ذات الأداء المنخفض | مشاركة أفضل الممارسات من الأقسام عالية الأداء | تحسين نظام المتابعة والتذكير للتقارير المعلقة | إجراء مراجعة دورية شهرية لأداء كل قسم</td></tr>
        </table>
    </div>

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