<?php
require_once '../../config/config.php';
header('Content-Type: application/json');
try {
    $limit = isset($_GET['length']) ? (int)$_GET['length'] : 10;
    $offset = isset($_GET['start']) ? (int)$_GET['start'] : 0;
    $orderColumnIndex = isset($_GET['order'][0]['column']) ? (int)$_GET['order'][0]['column'] : 0;
    $orderDir = isset($_GET['order'][0]['dir']) && in_array(strtolower($_GET['order'][0]['dir']), ['asc','desc']) ? $_GET['order'][0]['dir'] : 'asc';
    $searchValue = isset($_GET['search']['value']) ? trim($_GET['search']['value']) : '';

    $filterAy = isset($_GET['academic_year']) ? trim($_GET['academic_year']) : '';
    $filterAyId = isset($_GET['academic_year_id']) ? (int)$_GET['academic_year_id'] : 0;
    $filterExamId = isset($_GET['exam_id']) ? (int)$_GET['exam_id'] : 0;
    $filterCourseId = isset($_GET['course_id']) ? (int)$_GET['course_id'] : 0;
    $filterBatchId = isset($_GET['batch_id']) ? (int)$_GET['batch_id'] : 0;
    $filterExamType = isset($_GET['exam_type']) ? trim($_GET['exam_type']) : '';
    $filterTermType = isset($_GET['term_type']) ? trim($_GET['term_type']) : '';
    $filterSemester = isset($_GET['semester']) ? (int)$_GET['semester'] : 0;
    $filterSession = isset($_GET['session']) ? trim($_GET['session']) : '';
    $filterExamDate = isset($_GET['exam_date']) ? trim($_GET['exam_date']) : '';
    $filterExamDateFrom = isset($_GET['exam_date_from']) ? trim($_GET['exam_date_from']) : '';
    $filterExamDateTo = isset($_GET['exam_date_to']) ? trim($_GET['exam_date_to']) : '';

    // Use the full academic year string for filtering (consistent with exam_timetable API)
    // Don't strip the term part - use it as-is for exact matching

    $columns = ['exam_subject_id','exam_name','subject_code','subject_name','max_marks','pass_marks','exam_date','session'];
    $orderBy = isset($columns[$orderColumnIndex]) ? $columns[$orderColumnIndex] : 'exam_subject_id';

    $whereParts = [];
    $bindings = [];
    if ($searchValue !== '') { $whereParts[] = '(e.exam_name LIKE :q OR s.subject_code LIKE :q OR s.subject_name LIKE :q)'; $bindings[':q'] = "%$searchValue%"; }
    if ($filterAyId > 0) { $whereParts[] = 'e.academic_year_id = :ayid'; $bindings[':ayid'] = $filterAyId; }
    elseif ($filterAy !== '') { $whereParts[] = 'e.academic_year = :ay'; $bindings[':ay'] = $filterAy; }
    if ($filterExamId > 0) { $whereParts[] = 'es.exam_id = :eid'; $bindings[':eid'] = $filterExamId; }
    if ($filterCourseId > 0) { $whereParts[] = 's.course_id = :cid'; $bindings[':cid'] = $filterCourseId; }
    if ($filterBatchId > 0) { 
        $whereParts[] = 'EXISTS (SELECT 1 FROM student_enrollments se WHERE se.batch_id = :bid AND se.course_id = s.course_id)'; 
        $bindings[':bid'] = $filterBatchId; 
    }
    if ($filterSemester > 0) { $whereParts[] = 'e.semester = :sem'; $bindings[':sem'] = $filterSemester; }
    if ($filterSession !== '') { $whereParts[] = 'COALESCE(et.session, es.session) = :sess'; $bindings[':sess'] = $filterSession; }
    if ($filterExamDate !== '') { $whereParts[] = 'COALESCE(et.exam_date, es.exam_date) = :edate'; $bindings[':edate'] = $filterExamDate; }
    if ($filterExamDateFrom !== '') { $whereParts[] = 'COALESCE(et.exam_date, es.exam_date) >= :edatefrom'; $bindings[':edatefrom'] = $filterExamDateFrom; }
    if ($filterExamDateTo !== '') { $whereParts[] = 'COALESCE(et.exam_date, es.exam_date) <= :edateto'; $bindings[':edateto'] = $filterExamDateTo; }
    $joinExamType = '';
    if ($filterExamType !== '') { $joinExamType = ' JOIN exam_types t ON t.exam_type_id = e.exam_type_id '; $whereParts[] = 't.type_name = :etype'; $bindings[':etype'] = $filterExamType; }
    if ($filterTermType !== '') { $whereParts[] = 'e.academic_year LIKE :termType'; $bindings[':termType'] = "%$filterTermType%"; }
    $where = $whereParts ? ('WHERE '.implode(' AND ', $whereParts)) : '';

    $sql = "SELECT es.exam_subject_id, es.max_marks, es.pass_marks, 
                   COALESCE(et.exam_date, es.exam_date) as exam_date, 
                   COALESCE(et.session, es.session) as session,
                   e.exam_name, c.course_name, c.course_code, s.subject_code, s.subject_name
            FROM exam_subjects es
            JOIN exams e ON e.exam_id=es.exam_id
            $joinExamType
            JOIN subjects s ON s.subject_id=es.subject_id
            JOIN courses c ON c.course_id = s.course_id
            LEFT JOIN exam_timetable et ON et.exam_subject_id = es.exam_subject_id
            $where ORDER BY $orderBy $orderDir LIMIT :limit OFFSET :offset";
    $stmt=$pdo->prepare($sql);
    foreach($bindings as $k=>$v){ $stmt->bindValue($k,$v,is_int($v)?PDO::PARAM_INT:PDO::PARAM_STR);} $stmt->bindValue(':limit',$limit,PDO::PARAM_INT); $stmt->bindValue(':offset',$offset,PDO::PARAM_INT); $stmt->execute();
    $data=$stmt->fetchAll(PDO::FETCH_ASSOC);

    $total=(int)$pdo->query('SELECT COUNT(*) FROM exam_subjects')->fetchColumn();
    if ($where){ $stmtF=$pdo->prepare("SELECT COUNT(*) FROM exam_subjects es JOIN exams e ON e.exam_id=es.exam_id $joinExamType JOIN subjects s ON s.subject_id=es.subject_id JOIN courses c ON c.course_id = s.course_id LEFT JOIN exam_timetable et ON et.exam_subject_id = es.exam_subject_id $where"); foreach($bindings as $k=>$v){ $stmtF->bindValue($k,$v,is_int($v)?PDO::PARAM_INT:PDO::PARAM_STR);} $stmtF->execute(); $filtered=(int)$stmtF->fetchColumn(); } else { $filtered=$total; }

    echo json_encode(['draw'=>(int)($_GET['draw']??0),'recordsTotal'=>$total,'recordsFiltered'=>$filtered,'data'=>$data]);
} catch (Throwable $e){ http_response_code(500); echo json_encode(['error'=>'Server error: '.$e->getMessage()]); }
?>