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

if (!isset($_SESSION['user_id']) || ($_SESSION['user_role'] != 'admin' && $_SESSION['user_role'] != 'teacher')) {
    http_response_code(403);
    echo 'Forbidden';
    exit();
}

$exam_id = isset($_GET['exam_id']) ? (int)$_GET['exam_id'] : 0;
if (!$exam_id) { die('Invalid exam'); }

// Fetch exam
$stmt = $pdo->prepare("SELECT id, name, exam_date FROM exams WHERE id = ?");
$stmt->execute([$exam_id]);
$exam = $stmt->fetch();
if (!$exam) { die('Exam not found'); }

// Fetch duties (optional date filter)
$dateFilter = isset($_GET['date']) && $_GET['date'] !== '' ? $_GET['date'] : null;
$stmt = $pdo->prepare("SELECT r.room_name, t.first_name, t.last_name, t.employee_id, d.duty_role, d.duty_date, d.start_time, d.end_time, d.remarks
                       FROM exam_room_duties d
                       JOIN exam_rooms r ON d.room_id = r.id
                       JOIN teachers t ON d.teacher_id = t.id
                       WHERE d.exam_id = :exam_id AND (:date IS NULL OR d.duty_date = :date2)
                       ORDER BY r.room_name, d.room_id, d.order_index, d.id");

// Routine subjects by date (BN/EN)
$sub_stmt = $pdo->prepare("SELECT es.exam_date, s.name AS subject_bn, COALESCE(NULLIF(s.description,''), s.code) AS subject_en
                           FROM exam_subjects es
                           JOIN subjects s ON es.subject_id = s.id
                           WHERE es.exam_id = ? AND es.exam_date IS NOT NULL
                           ORDER BY es.exam_date, s.name");
$sub_stmt->execute([$exam_id]);
$date_subjects_rows = $sub_stmt->fetchAll();
$routine = [];
foreach ($date_subjects_rows as $r) { $routine[$r['exam_date']][] = [$r['subject_bn'], $r['subject_en']]; }
$stmt->bindValue(':exam_id', $exam_id, PDO::PARAM_INT);
$stmt->bindValue(':date', $dateFilter, $dateFilter ? PDO::PARAM_STR : PDO::PARAM_NULL);
$stmt->bindValue(':date2', $dateFilter, $dateFilter ? PDO::PARAM_STR : PDO::PARAM_NULL);
$stmt->execute();
$rows = $stmt->fetchAll();

$filename = 'room_duties_exam_' . $exam_id . '_' . date('Ymd_His') . '.csv';
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=' . $filename);
$output = fopen('php://output', 'w');

// UTF-8 BOM for Excel
fwrite($output, "\xEF\xBB\xBF");

// Header row with routine dates and subjects by date (BN/EN)
if (!empty($routine)) {
    fputcsv($output, ['Exam', $exam['name']]);
    fputcsv($output, ['Routine Dates & Subjects (BN / EN)']);
    foreach ($routine as $d => $subs) {
        $pairs = array_map(function($p){ return $p[0] . ' (' . $p[1] . ')'; }, $subs);
        fputcsv($output, [$d . ': ' . implode(', ', $pairs)]);
    }
    fputcsv($output, []);
} else {
    fputcsv($output, ['Exam', $exam['name']]);
    fputcsv($output, []);
}

fputcsv($output, ['Room', 'Teacher (BN)', 'Teacher (EN)', 'Employee ID', 'Role', 'Date', 'Start', 'End', 'Remarks']);

$roleMap = ['invigilator' => 'Main', 'assistant' => 'Assistant', 'reserve' => 'Reserve'];
foreach ($rows as $r) {
    $teacherNameBn = trim(($r['first_name'] ?? '') . ' ' . ($r['last_name'] ?? ''));
    $teacherNameEn = $r['employee_id'] ? $r['employee_id'] : '';
    fputcsv($output, [
        $r['room_name'],
        $teacherNameBn,
        $teacherNameEn,
        $r['employee_id'],
        $roleMap[$r['duty_role']] ?? $r['duty_role'],
        $r['duty_date'],
        $r['start_time'] ? substr($r['start_time'],0,5) : '',
        $r['end_time'] ? substr($r['end_time'],0,5) : '',
        $r['remarks']
    ]);
}

fclose($output);
exit;