<?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']) : '';

    $columns = ['session_id','session_name','subject_name','pattern_name','academic_year','exam_type','session_date','status','created_by_name'];
    $orderBy = isset($columns[$orderColumnIndex]) ? $columns[$orderColumnIndex] : 'session_id';

    $where = '';
    $bindings = [];
    if ($searchValue !== '') {
        $where = 'WHERE mes.session_name LIKE :q OR s.subject_name LIKE :q OR qp.pattern_name LIKE :q OR mes.academic_year LIKE :q';
        $bindings[':q'] = "%$searchValue%";
    }

    $sql = "SELECT mes.session_id, mes.session_name, mes.academic_year, mes.session_date, mes.status,
                   s.subject_name, qp.pattern_name, qp.total_marks,
                   et.type_name as exam_type,
                   CONCAT(st.first_name, ' ', st.last_name) as created_by_name,
                   COUNT(DISTINCT mea.assignment_id) as total_assignments,
                   COUNT(DISTINCT sme.entry_id) as total_entries,
                   mes.created_at, mes.updated_at
            FROM mark_entry_sessions mes
            LEFT JOIN exam_subjects es ON mes.exam_subject_id = es.exam_subject_id
            LEFT JOIN subjects s ON es.subject_id = s.subject_id
            LEFT JOIN question_patterns qp ON mes.pattern_id = qp.pattern_id
            LEFT JOIN exam_types et ON mes.exam_type_id = et.exam_type_id
            LEFT JOIN staff st ON mes.created_by = st.staff_id
            LEFT JOIN mark_entry_assignments mea ON mes.session_id = mea.session_id
            LEFT JOIN student_marks_entry sme ON mes.session_id = sme.session_id
            $where
            GROUP BY mes.session_id
            ORDER BY $orderBy $orderDir
            LIMIT $limit OFFSET $offset";

    $stmt = $pdo->prepare($sql);
    foreach ($bindings as $key => $value) {
        $stmt->bindValue($key, $value);
    }
    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Get total count
    $countSql = "SELECT COUNT(DISTINCT mes.session_id) as total
                 FROM mark_entry_sessions mes
                 LEFT JOIN exam_subjects es ON mes.exam_subject_id = es.exam_subject_id
                 LEFT JOIN subjects s ON es.subject_id = s.subject_id
                 LEFT JOIN question_patterns qp ON mes.pattern_id = qp.pattern_id
                 LEFT JOIN exam_types et ON mes.exam_type_id = et.exam_type_id
                 LEFT JOIN staff st ON mes.created_by = st.staff_id
                 $where";

    $countStmt = $pdo->prepare($countSql);
    foreach ($bindings as $key => $value) {
        $countStmt->bindValue($key, $value);
    }
    $countStmt->execute();
    $totalRecords = $countStmt->fetch(PDO::FETCH_ASSOC)['total'];

    echo json_encode([
        'draw' => intval($_GET['draw'] ?? 1),
        'recordsTotal' => $totalRecords,
        'recordsFiltered' => $totalRecords,
        'data' => $data
    ]);

} catch (Exception $e) {
    echo json_encode([
        'draw' => intval($_GET['draw'] ?? 1),
        'recordsTotal' => 0,
        'recordsFiltered' => 0,
        'data' => [],
        'error' => $e->getMessage()
    ]);
}
?>