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

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

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

// Set headers for Excel download
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="system_reports_' . date('Y-m-d_H-i-s') . '.xls"');
header('Pragma: no-cache');
header('Expires: 0');

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

try {
    // Get all statistics
    $stmt = $conn->query("SELECT COUNT(*) as total FROM patients");
    $total_patients = $stmt->fetch()['total'];
    
    $stmt = $conn->query("SELECT COUNT(*) as total FROM antibiotic_reports");
    $total_reports = $stmt->fetch()['total'];
    
    $stmt = $conn->query("SELECT COUNT(*) as total FROM antibiotics");
    $total_antibiotics = $stmt->fetch()['total'];
    
    $stmt = $conn->query("SELECT COUNT(*) as total FROM antibiotics WHERE is_active = 1");
    $active_antibiotics = $stmt->fetch()['total'];
    
    $stmt = $conn->query("SELECT COUNT(*) as total FROM users WHERE is_active = 1");
    $total_users = $stmt->fetch()['total'];
    
    // Reports by department
    $stmt = $conn->query("
        SELECT u.unit_name as department, COUNT(ar.id) as count 
        FROM antibiotic_reports ar
        JOIN units u ON ar.unit_id = u.id
        GROUP BY u.id, u.unit_name
        ORDER BY count DESC
    ");
    $reports_by_department = $stmt->fetchAll();
    
    // Reports by status (replacing non-existent priority column)
    $stmt = $conn->query("
        SELECT 
            CASE 
                WHEN status = 'completed' THEN 'مكتمل'
                WHEN status = 'pending' THEN 'معلق'
                WHEN status = 'cancelled' THEN 'ملغي'
                ELSE 'غير محدد'
            END as priority_ar,
            status as priority,
            COUNT(*) as count,
            ROUND((COUNT(*) * 100.0 / (SELECT COUNT(*) FROM antibiotic_reports)), 1) as percentage
        FROM antibiotic_reports 
        GROUP BY status
    ");
    $reports_by_priority = $stmt->fetchAll();
    
    // Most active users
    $stmt = $conn->query("
        SELECT u.full_name, u.role, COUNT(al.id) as activity_count
        FROM users u
        LEFT JOIN activity_log al ON u.id = al.user_id
        WHERE u.is_active = 1
        GROUP BY u.id
        ORDER BY activity_count DESC
    ");
    $most_active_users = $stmt->fetchAll();
    
    // Antibiotic usage
    $stmt = $conn->query("
        SELECT 
            a.antibiotic_name,
            COUNT(ar.id) as usage_count
        FROM antibiotic_reports ar
        JOIN antibiotics a ON ar.antibiotic_id = a.id
        GROUP BY a.id, a.antibiotic_name
        ORDER BY usage_count DESC
    ");
    $antibiotic_usage = $stmt->fetchAll();

} catch (PDOException $e) {
    die("خطأ في قاعدة البيانات: " . $e->getMessage());
}

?>
<!DOCTYPE html>
<html dir="rtl" lang="ar">
<head>
    <meta charset="UTF-8">
    <title>تقرير إحصائيات النظام</title>
    <style>
        body { font-family: Arial, sans-serif; direction: rtl; margin: 0; padding: 0; }
        table { border-collapse: collapse; width: 100%; margin-bottom: 20px; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: right; }
        th { background-color: #4472C4; color: #ffffff; font-weight: bold; }
        .stats-table td { text-align: center; }
        .data-table tr:nth-child(odd) td { background-color: #ffffff; }
        .data-table tr:nth-child(even) td { background-color: #f2f7ff; }
    </style>
</head>
<body>
    <!-- Header Table -->
    <table>
        <tr><td colspan="3" style="background-color:#1a3a5c; color:#ffffff; font-size:16pt; font-weight:bold; text-align:center; padding:12px;">تقرير إحصائيات نظام الصيدلة الإكلينيكية المتكامل (ICS)</td></tr>
        <tr><td colspan="3" 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="3" style="height:8px; border:none;"></td></tr>
    </table>

    <!-- General Statistics -->
    <table class="stats-table">
        <tr><td colspan="2" 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>
        </tr>
        <tr>
            <td>إجمالي المرضى</td>
            <td><?php echo number_format($total_patients); ?></td>
        </tr>
        <tr>
            <td>إجمالي التقارير</td>
            <td><?php echo number_format($total_reports); ?></td>
        </tr>
        <tr>
            <td>إجمالي المضادات الحيوية</td>
            <td><?php echo number_format($total_antibiotics); ?></td>
        </tr>
        <tr>
            <td>المضادات الحيوية النشطة</td>
            <td><?php echo number_format($active_antibiotics); ?></td>
        </tr>
        <tr>
            <td>المستخدمين النشطين</td>
            <td><?php echo number_format($total_users); ?></td>
        </tr>
        <tr>
            <td>معدل نشاط المضادات الحيوية</td>
            <td><?php echo $total_antibiotics > 0 ? round(($active_antibiotics / $total_antibiotics) * 100, 1) : 0; ?>%</td>
        </tr>
    </table>

    <!-- Reports by Department -->
    <table>
        <tr><td colspan="2" 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>
        </tr>
        <?php foreach ($reports_by_department as $dept): ?>
        <tr>
            <td><?php echo htmlspecialchars($dept['department']); ?></td>
            <td><?php echo $dept['count']; ?></td>
        </tr>
        <?php endforeach; ?>
    </table>

    <!-- Reports by Status -->
    <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 ($reports_by_priority as $priority): ?>
        <tr>
            <td><?php echo $priority['priority_ar']; ?></td>
            <td><?php echo $priority['count']; ?></td>
            <td><?php echo $priority['percentage']; ?>%</td>
        </tr>
        <?php endforeach; ?>
    </table>

    <!-- Most Active Users -->
    <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 
        $role_ar = [
            'admin' => 'مدير',
            'pharmacist' => 'صيدلي',
            'doctor' => 'طبيب',
            'nurse' => 'ممرض',
            'secretary' => 'سكرتارية',
            'pharmacy_assistant' => 'مساعد صيدلي'
        ];
        foreach ($most_active_users as $user): 
        ?>
        <tr>
            <td><?php echo htmlspecialchars($user['full_name']); ?></td>
            <td><?php echo $role_ar[$user['role']] ?? $user['role']; ?></td>
            <td><?php echo $user['activity_count']; ?></td>
        </tr>
        <?php endforeach; ?>
    </table>

    <!-- Antibiotic Usage -->
    <table>
        <tr><td colspan="2" 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>
        </tr>
        <?php foreach ($antibiotic_usage as $antibiotic): ?>
        <tr>
            <td><?php echo htmlspecialchars($antibiotic['antibiotic_name']); ?></td>
            <td><?php echo $antibiotic['usage_count']; ?></td>
        </tr>
        <?php endforeach; ?>
    </table>

    <!-- 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'); ?></td></tr>
    </table>
</body>
</html>
