<?php
require_once '../../config/config.php';
header('Content-Type: application/json');

try {
    // DataTables parameters
    $draw = isset($_GET['draw']) ? (int)$_GET['draw'] : 1;
    $start = isset($_GET['start']) ? (int)$_GET['start'] : 0;
    $length = isset($_GET['length']) ? (int)$_GET['length'] : 10;
    $searchValue = isset($_GET['search']['value']) ? trim($_GET['search']['value']) : '';
    $orderColumn = isset($_GET['order'][0]['column']) ? (int)$_GET['order'][0]['column'] : 0;
    $orderDir = isset($_GET['order'][0]['dir']) ? $_GET['order'][0]['dir'] : 'asc';
    
    // Filter parameters
    $examId = isset($_GET['exam_id']) ? (int)$_GET['exam_id'] : 0;
    $courseId = isset($_GET['course_id']) ? (int)$_GET['course_id'] : 0;
    $dateFrom = isset($_GET['date_from']) ? trim($_GET['date_from']) : '';
    $dateTo = isset($_GET['date_to']) ? trim($_GET['date_to']) : '';
    $session = isset($_GET['session']) ? trim($_GET['session']) : '';
    $examStatus = isset($_GET['exam_status']) ? trim($_GET['exam_status']) : '';
    $academicYear = isset($_GET['academic_year']) ? trim($_GET['academic_year']) : '';
    $examTypeId = isset($_GET['exam_type_id']) ? (int)$_GET['exam_type_id'] : 0;

    $where = [];
    $params = [];
    if ($examId > 0) { $where[] = 'e.exam_id = ?'; $params[] = $examId; }
    if ($courseId > 0) { $where[] = 's.course_id = ?'; $params[] = $courseId; }
    if ($dateFrom !== '') { $where[] = 'et.exam_date >= ?'; $params[] = $dateFrom; }
    if ($dateTo !== '') { $where[] = 'et.exam_date <= ?'; $params[] = $dateTo; }
    if ($session !== '') { $where[] = 'et.session = ?'; $params[] = $session; }
    if ($examStatus !== '') { $where[] = 'e.status = ?'; $params[] = $examStatus; }
    if ($academicYear !== '') { 
        // Handle both formats: "2000-2001" and "2000-2001 Even/Odd"
        if (strpos($academicYear, ' ') !== false) {
            // Format: "2000-2001 Even" - extract base year
            $baseYear = explode(' ', $academicYear)[0];
            $where[] = '(e.academic_year = ? OR e.academic_year = ?)'; 
            $params[] = $academicYear;
            $params[] = $baseYear;
        } else {
            // Format: "2000-2001" - match exact or with term
            $where[] = '(e.academic_year = ? OR e.academic_year LIKE ?)'; 
            $params[] = $academicYear;
            $params[] = $academicYear . ' %';
        }
    }
    if ($examTypeId > 0) { $where[] = 'e.exam_type_id = ?'; $params[] = $examTypeId; }
    
    // Add search condition
    if ($searchValue !== '') {
        $where[] = '(e.exam_name LIKE ? OR s.subject_code LIKE ? OR s.subject_name LIKE ? OR c.course_name LIKE ?)';
        $searchParam = '%' . $searchValue . '%';
        $params[] = $searchParam;
        $params[] = $searchParam;
        $params[] = $searchParam;
        $params[] = $searchParam;
    }
    
    $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
    
    // Debug logging (remove in production)
    error_log("Exam Timetable API Query - Academic Year: '$academicYear', Course ID: $courseId");
    error_log("Where SQL: $whereSql");
    error_log("Params: " . json_encode($params));

    // Define order columns
    $orderColumns = [
        'et.timetable_id',
        'e.exam_name',
        's.subject_code',
        'et.exam_date',
        'et.start_time',
        'et.session'
    ];
    $orderBy = isset($orderColumns[$orderColumn]) ? $orderColumns[$orderColumn] : 'et.timetable_id';
    $orderDir = strtoupper($orderDir) === 'DESC' ? 'DESC' : 'ASC';

    // Get total count
    $countSql = "SELECT COUNT(*) 
                 FROM exam_timetable et
                 JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
                 JOIN exams e ON es.exam_id = e.exam_id
                 JOIN exam_types ety ON ety.exam_type_id = e.exam_type_id
                 JOIN subjects s ON es.subject_id = s.subject_id
                 JOIN courses c ON s.course_id = c.course_id
                 $whereSql";
    $countStmt = $pdo->prepare($countSql);
    $countStmt->execute($params);
    $recordsTotal = $countStmt->fetchColumn();

    // Get filtered count (same as total for this case)
    $recordsFiltered = $recordsTotal;

    // Main query with pagination
    $sql = "SELECT et.timetable_id, et.exam_date, et.start_time, et.end_time, et.session,
                   e.exam_id, e.exam_name, e.status AS exam_status, ety.type_name AS exam_type,
                   es.exam_subject_id,
                   s.subject_id, s.subject_code, s.subject_name, s.course_id, s.type AS subject_type,
                   c.course_name
            FROM exam_timetable et
            JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
            JOIN exams e ON es.exam_id = e.exam_id
            JOIN exam_types ety ON ety.exam_type_id = e.exam_type_id
            JOIN subjects s ON es.subject_id = s.subject_id
            JOIN courses c ON s.course_id = c.course_id
            $whereSql
            ORDER BY $orderBy $orderDir
            LIMIT $length OFFSET $start";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Return DataTables format
    echo json_encode([
        'draw' => $draw,
        'recordsTotal' => $recordsTotal,
        'recordsFiltered' => $recordsFiltered,
        'data' => $data
    ]);
} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode(['status'=>'error','message'=>$e->getMessage()]);
}
?>