<?php
ob_start();
error_reporting(0);
ini_set('display_errors', '0');
ini_set('display_startup_errors', '0');
require_once '../../config/config.php';
header('Content-Type: application/json');

try {
    $draw   = isset($_GET['draw']) ? (int)$_GET['draw'] : 1;
    $start  = isset($_GET['start']) ? (int)$_GET['start'] : 0;
    $length = isset($_GET['length']) ? (int)$_GET['length'] : 10;
    $search = isset($_GET['search']['value']) ? trim($_GET['search']['value']) : '';

    $academicYear = isset($_GET['academic_year']) ? trim($_GET['academic_year']) : '';
    $courseId     = isset($_GET['course_id']) && $_GET['course_id'] !== '' ? (int)$_GET['course_id'] : 0;
    $examDate     = isset($_GET['exam_date']) ? trim($_GET['exam_date']) : '';
    $batchId      = isset($_GET['batch_id']) && $_GET['batch_id'] !== '' ? (int)$_GET['batch_id'] : 0;
    $session      = isset($_GET['session']) ? trim($_GET['session']) : '';

    // Build WHERE with filters (based on registrations + optional timetable for date/session)
    $where = [];
    $params = [];

    if ($academicYear !== '') { $where[] = 'e.academic_year = ?'; $params[] = $academicYear; }
    if ($courseId > 0)       { $where[] = 's.course_id = ?'; $params[] = $courseId; }
    if ($examDate !== '')    { $where[] = 'et.exam_date = ?'; $params[] = $examDate; }
    if ($session !== '')     { $where[] = 'et.session = ?'; $params[] = $session; }
    if ($batchId > 0)        { $where[] = 'se.batch_id = ?'; $params[] = $batchId; }

    // Search on student name or roll #
    $searchSql = '';
    if ($search !== '') {
        $searchSql = ' AND (st.university_rollno LIKE ? OR st.first_name LIKE ? OR st.last_name LIKE ? OR CONCAT(st.first_name, " ", st.last_name) LIKE ?)';
        $like = '%'.$search.'%';
    }

    $whereSql = '';
    if (!empty($where)) { $whereSql = 'WHERE ' . implode(' AND ', $where); }

    // Count total distinct students (without filters) for DataTables total
    $totalSql = 'SELECT COUNT(DISTINCT st.student_id) FROM students st
                 JOIN student_registrations sr ON st.student_id = sr.student_id
                 JOIN exam_subjects es ON sr.exam_subject_id = es.exam_subject_id
                 JOIN exams e ON es.exam_id = e.exam_id
                 JOIN subjects s ON es.subject_id = s.subject_id
                 LEFT JOIN exam_timetable et ON et.exam_subject_id = es.exam_subject_id
                 LEFT JOIN student_enrollments se ON se.student_id = st.student_id';
    $total = (int)$pdo->query($totalSql)->fetchColumn();

    // Count filtered
    $countSql = 'SELECT COUNT(*) FROM (
                    SELECT DISTINCT st.student_id
                    FROM students st
                    JOIN student_registrations sr ON st.student_id = sr.student_id
                    JOIN exam_subjects es ON sr.exam_subject_id = es.exam_subject_id
                    JOIN exams e ON es.exam_id = e.exam_id
                    JOIN subjects s ON es.subject_id = s.subject_id
                    LEFT JOIN exam_timetable et ON et.exam_subject_id = es.exam_subject_id
                    LEFT JOIN student_enrollments se ON se.student_id = st.student_id
                    ' . $whereSql . ($search !== '' ? $searchSql : '') . '
                ) x';
    $countStmt = $pdo->prepare($countSql);
    $bindIdx = 1;
    foreach ($params as $p) { $countStmt->bindValue($bindIdx++, $p); }
    if ($search !== '') { $countStmt->bindValue($bindIdx++, $like); $countStmt->bindValue($bindIdx++, $like); $countStmt->bindValue($bindIdx++, $like); $countStmt->bindValue($bindIdx++, $like); }
    $countStmt->execute();
    $filtered = (int)$countStmt->fetchColumn();

    // Fetch paginated rows with exams count
    $dataSql = 'SELECT st.student_id,
                       st.university_rollno AS roll_number,
                       CONCAT(st.first_name, " ", st.last_name) AS student_name,
                       c.course_name,
                       COUNT(DISTINCT es.exam_subject_id) AS exams_count
                FROM students st
                JOIN student_registrations sr ON st.student_id = sr.student_id
                JOIN exam_subjects es ON sr.exam_subject_id = es.exam_subject_id
                JOIN exams e ON es.exam_id = e.exam_id
                JOIN subjects s ON es.subject_id = s.subject_id
                JOIN courses c ON s.course_id = c.course_id
                LEFT JOIN exam_timetable et ON et.exam_subject_id = es.exam_subject_id
                LEFT JOIN student_enrollments se ON se.student_id = st.student_id
                ' . $whereSql . ($search !== '' ? $searchSql : '') . '
                GROUP BY st.student_id, st.university_rollno, st.first_name, st.last_name, c.course_name
                ORDER BY st.university_rollno ASC
                LIMIT ? OFFSET ?';
    $stmt = $pdo->prepare($dataSql);
    $bindIdx = 1;
    foreach ($params as $p) { $stmt->bindValue($bindIdx++, $p); }
    if ($search !== '') { $stmt->bindValue($bindIdx++, $like); $stmt->bindValue($bindIdx++, $like); $stmt->bindValue($bindIdx++, $like); $stmt->bindValue($bindIdx++, $like); }
    $stmt->bindValue($bindIdx++, $length, PDO::PARAM_INT);
    $stmt->bindValue($bindIdx++, $start, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Clean any accidental output before JSON
    while (ob_get_level() > 0) { ob_end_clean(); }
    echo json_encode([
        'draw' => $draw,
        'recordsTotal' => $total,
        'recordsFiltered' => $filtered,
        'data' => $rows
    ]);
    exit;
} catch (Throwable $e) {
    http_response_code(500);
    while (ob_get_level() > 0) { ob_end_clean(); }
    echo json_encode(['draw'=>isset($_GET['draw'])?(int)$_GET['draw']:1,'recordsTotal'=>0,'recordsFiltered'=>0,'data'=>[],'error'=>$e->getMessage()]);
    exit;
}
?>

<?php
require_once '../../config/config.php';
header('Content-Type: application/json');

try {
    // Log the request for debugging
    error_log("Hall Tickets API Request: " . json_encode($_GET));
    
    // Get the query parameters sent by DataTables
    $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']) : '';

    // Map column indices to actual database columns
    $columns = ['student_id', 'student_name', 'roll_number', 'course_name', 'exams_count'];
    $orderBy = isset($columns[$orderColumnIndex]) ? $columns[$orderColumnIndex] : 'student_id';

    // Filter parameters
    $academicYear = isset($_GET['academic_year']) ? trim($_GET['academic_year']) : '';
    $courseId = isset($_GET['course_id']) ? (int)$_GET['course_id'] : 0;
    $examDate = isset($_GET['exam_date']) ? trim($_GET['exam_date']) : '';
    $session = isset($_GET['session']) ? trim($_GET['session']) : '';
    
    error_log("Filter parameters: academic_year=$academicYear, course_id=$courseId, exam_date=$examDate, session=$session");

    // Build WHERE clause
    $where = [];
    $bindings = [];

    // Add filter conditions
    if ($academicYear !== '') {
        $where[] = 'e.academic_year = ?';
        $bindings[] = $academicYear;
    }

    if ($courseId > 0) {
        $where[] = 's.course_id = ?';
        $bindings[] = $courseId;
    }

    if ($examDate !== '') {
        $where[] = 'et.exam_date = ?';
        $bindings[] = $examDate;
    }

    if ($session !== '') {
        $where[] = 'et.session = ?';
        $bindings[] = $session;
    }

    // Add search condition
    if (!empty($searchValue)) {
        $where[] = '(st.first_name LIKE ? OR st.last_name LIKE ? OR st.university_rollno LIKE ? OR c.course_name LIKE ?)';
        $searchParam = "%$searchValue%";
        $bindings[] = $searchParam;
        $bindings[] = $searchParam;
        $bindings[] = $searchParam;
        $bindings[] = $searchParam;
    }

    $whereClause = $where ? 'WHERE ' . implode(' AND ', $where) : '';

    // Base SQL query to get grouped students with exam counts
    $sqlData = "SELECT 
                       st.student_id,
                       CONCAT(st.first_name, ' ', st.last_name) as student_name,
                       st.university_rollno as roll_number,
                       c.course_name,
                       COUNT(DISTINCT es.exam_subject_id) as exams_count
                FROM students st
                JOIN student_registrations sr ON st.student_id = sr.student_id
                JOIN student_seating ss ON sr.registration_id = ss.registration_id
                JOIN hall_allocations ha ON ss.allocation_id = ha.allocation_id
                JOIN exam_timetable et ON ha.timetable_id = et.timetable_id
                JOIN halls h ON ha.hall_id = h.hall_id
                JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
                JOIN subjects s ON es.subject_id = s.subject_id
                JOIN courses c ON s.course_id = c.course_id
                JOIN exams e ON es.exam_id = e.exam_id
                $whereClause 
                GROUP BY st.student_id, st.first_name, st.last_name, st.university_rollno, c.course_name
                ORDER BY $orderBy $orderDir 
                LIMIT ? OFFSET ?";

    $bindings[] = $limit;
    $bindings[] = $offset;

    // Prepare and execute the query
    $stmt = $pdo->prepare($sqlData);

    foreach ($bindings as $index => $value) {
        $stmt->bindValue($index + 1, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
    }

    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Total records count (without filtering)
    $sqlTotal = "SELECT COUNT(DISTINCT st.student_id) as total 
                 FROM students st
                 JOIN student_registrations sr ON st.student_id = sr.student_id
                 JOIN student_seating ss ON sr.registration_id = ss.registration_id
                 JOIN hall_allocations ha ON ss.allocation_id = ha.allocation_id
                 JOIN exam_timetable et ON ha.timetable_id = et.timetable_id
                 JOIN halls h ON ha.hall_id = h.hall_id
                 JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
                 JOIN subjects s ON es.subject_id = s.subject_id
                 JOIN courses c ON s.course_id = c.course_id
                 JOIN exams e ON es.exam_id = e.exam_id";
    $totalRecords = $pdo->query($sqlTotal)->fetchColumn();

    // Total filtered records count (with filtering)
    $sqlFiltered = "SELECT COUNT(DISTINCT st.student_id) as total 
                    FROM students st
                    JOIN student_registrations sr ON st.student_id = sr.student_id
                    JOIN student_seating ss ON sr.registration_id = ss.registration_id
                    JOIN hall_allocations ha ON ss.allocation_id = ha.allocation_id
                    JOIN exam_timetable et ON ha.timetable_id = et.timetable_id
                    JOIN halls h ON ha.hall_id = h.hall_id
                    JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
                    JOIN subjects s ON es.subject_id = s.subject_id
                    JOIN courses c ON s.course_id = c.course_id
                    JOIN exams e ON es.exam_id = e.exam_id
                    $whereClause";
    $stmtFiltered = $pdo->prepare($sqlFiltered);

    // Remove limit and offset from bindings for filtered count
    $filterBindings = array_slice($bindings, 0, -2);
    
    foreach ($filterBindings as $index => $value) {
        $stmtFiltered->bindValue($index + 1, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
    }

    $stmtFiltered->execute();
    $totalFilteredRecords = $stmtFiltered->fetchColumn();

    // Log the response for debugging
    $response = [
        "draw" => isset($_GET['draw']) ? (int)$_GET['draw'] : 0,
        "recordsTotal" => (int)$totalRecords,
        "recordsFiltered" => (int)$totalFilteredRecords,
        "data" => $data
    ];
    
    error_log("Hall Tickets API Response: " . json_encode($response));
    
    // Return DataTables response
    echo json_encode($response);
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(["error" => "Database error: " . $e->getMessage()]);
    error_log("Database error: " . $e->getMessage());
    exit;
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(["error" => "An unexpected error occurred: " . $e->getMessage()]);
    error_log("Error: " . $e->getMessage());
    exit;
}
?>