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

if (!isset($_SESSION['user_id'])) {
    die('Unauthorized');
}

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

// Get parameters
if (isset($_GET['start_date']) && isset($_GET['end_date'])) {
    $start_date = $_GET['start_date'];
    $end_date = $_GET['end_date'];
} else {
    // الافتراضي
    $month = $_GET['month'] ?? date('Y-m');
    list($year, $month_num) = explode('-', $month);
    $month_name = date('F', mktime(0, 0, 0, $month_num, 1));
    
    // الشهر يبدأ من 21 الشهر السابق وينتهي 20 الشهر الحالي
    $prev_month_date = date('Y-m', strtotime("$year-$month_num-01 -1 month"));
    list($prev_year, $prev_month) = explode('-', $prev_month_date);
    
    $start_date = "$prev_year-$prev_month-21";
    $end_date = "$year-$month_num-20";
}

$start_dt = new DateTime($start_date);
$end_dt = new DateTime($end_date);

// حساب عدد الأيام الفعلي
$interval = $start_dt->diff($end_dt);
$total_days = $interval->days + 1; // +1 لأننا نحسب اليوم الأخير

// اسم الشهر للعنوان
$month_name = $start_dt->format('M') . ' - ' . $end_dt->format('M Y');

// Fetch data
try {
    $schedule_stmt = $conn->prepare("
        SELECT ps.*, p.name as pharmacist_name, p.code as pharmacist_code
        FROM pharmacist_schedule ps
        JOIN pharmacists p ON ps.pharmacist_id = p.id
        WHERE ps.schedule_date >= ? AND ps.schedule_date <= ?
        ORDER BY ps.team, p.name, ps.schedule_date
    ");
    $schedule_stmt->execute([$start_date, $end_date]);
    $schedules = $schedule_stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die('Error: ' . $e->getMessage());
}

// Set headers for Excel download
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="Clinical_Pharmacists_Schedule_' . $month . '.xls"');
header('Pragma: no-cache');
header('Expires: 0');

// Output BOM for UTF-8
echo "\xEF\xBB\xBF";
?>
<!DOCTYPE html>
<html dir="rtl">
<head>
    <meta charset="UTF-8">
    <style>
        table {
            border-collapse: collapse;
            width: 100%;
            direction: ltr;
        }
        th, td {
            border: 1px solid #000;
            padding: 8px;
            text-align: center;
            font-size: 11pt;
        }
        th {
            background-color: #4472C4;
            color: #ffffff;
            font-weight: bold;
        }
        .team-header {
            background-color: #D9E1F2;
            color: #1F3864;
            font-weight: bold;
            text-align: center;
        }
        tr:nth-child(odd) td { background-color: #ffffff; }
        tr:nth-child(even) td { background-color: #f2f7ff; }
        .shift-morning {
            background-color: #fff3cd !important;
        }
        .shift-evening {
            background-color: #d1ecf1 !important;
        }
        .shift-night {
            background-color: #f8d7da !important;
        }
        .shift-off {
            background-color: #d4edda !important;
        }
    </style>
</head>
<body>
    <!-- Header Table -->
    <table>
        <tr><td colspan="<?php echo $total_days + 10; ?>" style="background-color:#1a3a5c; color:#ffffff; font-size:16pt; font-weight:bold; text-align:center; padding:12px;">Clinical Pharmacists Schedule - <?php echo $month_name; ?></td></tr>
        <tr><td colspan="<?php echo $total_days + 10; ?>" style="height:8px; border:none;"></td></tr>
    </table>
    
    <table>
        <thead>
            <tr>
                <th rowspan="4">Name</th>
                <th rowspan="4">Code</th>
                <th colspan="<?php echo $total_days; ?>">Days</th>
                <th colspan="5">Statistics</th>
                <th colspan="3">Hours</th>
            </tr>
            <tr>
                <?php 
                $start_dt = new DateTime($start_date);
                for ($d = 1; $d <= $total_days; $d++): 
                    $day_name = $start_dt->format('l');
                ?>
                    <th><?php echo $day_name; ?></th>
                <?php 
                    $start_dt->modify('+1 day');
                endfor; 
                ?>
                <th>L</th><th>N</th><th>A</th><th>E</th><th>C</th>
                <th>Official vacation</th><th>Total hours</th><th>Over time</th>
            </tr>
            <tr>
                <?php 
                $start_dt = new DateTime($start_date);
                for ($d = 1; $d <= $total_days; $d++): 
                    $day_number = $start_dt->format('j');
                ?>
                    <th><?php echo $day_number; ?></th>
                <?php 
                    $start_dt->modify('+1 day');
                endfor; 
                ?>
                <th></th><th></th><th></th><th></th><th></th>
                <th></th><th></th><th></th>
            </tr>
            <tr>
                <?php 
                $start_dt = new DateTime($start_date);
                for ($d = 1; $d <= $total_days; $d++): 
                    $date_formatted = $start_dt->format('d-M');
                ?>
                    <th><?php echo $date_formatted; ?></th>
                <?php 
                    $start_dt->modify('+1 day');
                endfor; 
                ?>
                <th></th><th></th><th></th><th></th><th></th>
                <th></th><th></th><th></th>
            </tr>
        </thead>
        <tbody>
            <?php
            $teams = ['OR Team', 'Ward Team', 'ICU Team', 'NICU Team'];
            
            foreach ($teams as $team):
                $total_cols = $total_days + 10;
                echo "<tr><td colspan='$total_cols' class='team-header'>$team</td></tr>";
                
                // Filter schedules for this team
                $team_schedules = array_filter($schedules, function($s) use ($team) {
                    return $s['team'] == $team;
                });
                
                // Group by pharmacist
                $pharmacist_data = [];
                foreach ($team_schedules as $schedule) {
                    $pharmacist_id = $schedule['pharmacist_id'];
                    if (!isset($pharmacist_data[$pharmacist_id])) {
                        $pharmacist_data[$pharmacist_id] = [
                            'name' => $schedule['pharmacist_name'],
                            'code' => $schedule['pharmacist_code'],
                            'days' => []
                        ];
                    }
                    
                    // حساب رقم اليوم من 1 إلى total_days
                    $schedule_date = new DateTime($schedule['schedule_date']);
                    $start_dt_calc = new DateTime($start_date);
                    $diff = $start_dt_calc->diff($schedule_date);
                    $day_number = $diff->days + 1;
                    
                    if ($day_number >= 1 && $day_number <= $total_days) {
                        $pharmacist_data[$pharmacist_id]['days'][$day_number] = $schedule['shift_time'];
                    }
                }
                
                if (empty($pharmacist_data)) {
                    $total_cols = $total_days + 10;
                    echo "<tr><td colspan='$total_cols'>No data</td></tr>";
                } else {
                    foreach ($pharmacist_data as $data):
                        // حساب الإحصائيات
                        $stats = [
                            'L' => 0, 'N' => 0, 'A' => 0, 'E' => 0, 'C' => 0
                        ];
                        foreach ($data['days'] as $shift) {
                            if (isset($stats[$shift])) {
                                $stats[$shift]++;
                            }
                        }
                        
                        // حساب الساعات
                        $hours_12 = ($stats['L'] + $stats['N']) * 12;
                        $hours_8 = ($stats['A'] + $stats['E']) * 8;
                        $hours_7 = $stats['C'] * 7;
                        $total_hours = $hours_12 + $hours_8 + $hours_7;
                        $official_vacation = 0;
                        $overtime = $total_hours - 160;
            ?>
            <tr>
                <td><?php echo htmlspecialchars($data['name']); ?></td>
                <td><?php echo htmlspecialchars($data['code']); ?></td>
                <?php for ($day = 1; $day <= $total_days; $day++): 
                    $shift = $data['days'][$day] ?? '';
                    $class = '';
                    if ($shift == 'M') $class = 'shift-morning';
                    elseif ($shift == 'E') $class = 'shift-evening';
                    elseif ($shift == 'N') $class = 'shift-night';
                    elseif ($shift == 'O') $class = 'shift-off';
                ?>
                    <td class="<?php echo $class; ?>"><?php echo $shift; ?></td>
                <?php endfor; ?>
                <td><?php echo $stats['L']; ?></td>
                <td><?php echo $stats['N']; ?></td>
                <td><?php echo $stats['A']; ?></td>
                <td><?php echo $stats['E']; ?></td>
                <td><?php echo $stats['C']; ?></td>
                <td><?php echo $official_vacation; ?></td>
                <td><?php echo $total_hours; ?></td>
                <td><?php echo $overtime; ?></td>
            </tr>
            <?php
                    endforeach;
                }
            endforeach;
            ?>
        </tbody>
    </table>
    
    <br>
    <p><strong>Legend:</strong></p>
    <p>L = Long Day (8am-8pm) - 12 hours | N = Night (8pm-8am) - 12 hours</p>
    <p>A = Morning (8am-4pm) - 8 hours | E = Evening (4pm-12am) - 8 hours</p>
    <p>C = Coverage (8am-3pm) - 7 hours</p>
    
    <br><br>
    <p><strong>Signature:</strong> _______________________</p>
</body>
</html>

