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

try {
    $exam_id  = isset($_GET['exam_id']) && $_GET['exam_id'] !== '' ? (int)$_GET['exam_id'] : null;
    $staff_id = isset($_GET['staff_id']) && $_GET['staff_id'] !== '' ? (int)$_GET['staff_id'] : null;
    $status   = isset($_GET['status']) && $_GET['status'] !== '' ? trim($_GET['status']) : null;

    $where=[]; $bind=[];
    if ($exam_id)  { $where[]='qps.exam_id = :exam_id';  $bind[':exam_id']=$exam_id; }
    if ($staff_id) { $where[]='qps.staff_id = :staff_id'; $bind[':staff_id']=$staff_id; }
    if ($status) {
        if (strcasecmp($status, 'Pending') === 0) {
            $where[] = 'qss_latest.status IS NULL';
        } else {
            $where[] = 'qss_latest.status = :status';
            $bind[':status'] = $status;
        }
    }
    $whereSql = $where ? ('WHERE '.implode(' AND ', $where)) : '';

    $sql = "SELECT 
              qps.id,
              qps.exam_id,
              e.exam_name,
              qps.staff_id,
              CONCAT(s.first_name,' ',s.last_name) AS staff_name,
              qps.start_date,
              qps.end_date,
              qps.pattern_id,
              qp.pattern_name,
              qps.exam_subject_id,
              subj_es.subject_code AS subject_code,
              qps.template_id,
              t.template_name,
              qss_latest.status AS submission_status
            FROM qp_setter_assignments qps
            JOIN exams e ON qps.exam_id = e.exam_id
            JOIN staff s ON qps.staff_id = s.staff_id
            LEFT JOIN question_patterns qp ON qps.pattern_id = qp.pattern_id
            LEFT JOIN exam_subjects es ON qps.exam_subject_id = es.exam_subject_id
            LEFT JOIN subjects subj_es ON es.subject_id = subj_es.subject_id
            LEFT JOIN question_pattern_templates t ON qps.template_id = t.template_id
            LEFT JOIN qp_setter_submissions qss_latest ON qss_latest.id = (
                SELECT qss2.id FROM qp_setter_submissions qss2
                WHERE qss2.assignment_id = qps.id
                ORDER BY qss2.updated_at DESC, qss2.id DESC
                LIMIT 1
            )
            $whereSql
            ORDER BY qps.id DESC";
    $st = $pdo->prepare($sql);
    foreach($bind as $k=>$v){ $st->bindValue($k,$v); }
    $st->execute();
    echo json_encode(['status'=>'success','data'=>$st->fetchAll(PDO::FETCH_ASSOC)]);
} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode(['status'=>'error','message'=>$e->getMessage()]);
}
?>