<?php
// Server-side DataTables provider for qp_setter_submissions list
header('Content-Type: application/json');
require_once __DIR__ . '/../../config/config.php';

try {
    // DataTables params
    $draw   = (int)($_GET['draw'] ?? 1);
    $start  = (int)($_GET['start'] ?? 0);
    $length = (int)($_GET['length'] ?? 10);
    if ($length <= 0 || $length > 100) { $length = 10; }
    
    // Filter params
    $statusFilter = trim($_GET['status_filter'] ?? '');
    $subjectFilter = trim($_GET['subject_filter'] ?? '');
    $examFilter = trim($_GET['exam_filter'] ?? '');
    $staffFilter = trim($_GET['staff_filter'] ?? '');
    $dateRange = trim($_GET['date_range'] ?? '');
    $dateFilter = trim($_GET['date_filter'] ?? '');

    // Base query joining needed info with WHERE conditions
    $whereConditions = [];
    $params = [];
    
    if ($statusFilter) {
        $whereConditions[] = "qss.status = ?";
        $params[] = $statusFilter;
    }
    
    if ($subjectFilter) {
        $whereConditions[] = "sub.subject_code = ?";
        $params[] = $subjectFilter;
    }
    
    if ($examFilter) {
        $whereConditions[] = "qps.exam_id = ?";
        $params[] = $examFilter;
    }
    
    if ($staffFilter) {
        $whereConditions[] = "qss.staff_id = ?";
        $params[] = $staffFilter;
    }
    
    if ($dateRange) {
        $dateParts = explode(' - ', $dateRange);
        if (count($dateParts) === 2) {
            $whereConditions[] = "DATE(qss.created_at) BETWEEN ? AND ?";
            $params[] = $dateParts[0];
            $params[] = $dateParts[1];
        }
    }
    
    if ($dateFilter) {
        $whereConditions[] = "DATE(qss.created_at) = ?";
        $params[] = $dateFilter;
    }
    
    $whereClause = $whereConditions ? ' WHERE ' . implode(' AND ', $whereConditions) : '';
    
    $baseSql = "
        FROM qp_setter_submissions qss
        JOIN qp_setter_assignments qps ON qss.assignment_id = qps.id
        LEFT JOIN staff s ON qss.staff_id = s.staff_id
        LEFT JOIN exam_subjects es ON qps.exam_subject_id = es.exam_subject_id
        LEFT JOIN subjects sub ON es.subject_id = sub.subject_id
        $whereClause
    ";

    // Count total with filters
    $countStmt = $pdo->prepare("SELECT COUNT(*) $baseSql");
    $countStmt->execute($params);
    $total = (int)$countStmt->fetchColumn();

    // Ordering (optional; we keep stable desc by id)
    $orderSql = " ORDER BY qss.id DESC ";

    // Fetch page with filters
    $stmt = $pdo->prepare("SELECT qss.id, qss.assignment_id, qss.staff_id, qss.status, qss.created_at, qss.updated_at,
                                   s.first_name, s.last_name, s.staff_code,
                                   sub.subject_code, sub.subject_name
                            $baseSql
                            $orderSql
                            LIMIT ?, ?");
    
    // Bind filter params first
    foreach ($params as $index => $value) {
        $stmt->bindValue($index + 1, $value);
    }
    
    // Bind LIMIT parameters
    $stmt->bindValue(count($params) + 1, $start, PDO::PARAM_INT);
    $stmt->bindValue(count($params) + 2, $length, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $data = array_map(function($r){
        return [
            'id' => (int)$r['id'],
            'assignment_id' => (int)$r['assignment_id'],
            'staff_id' => (int)$r['staff_id'],
            'staff_name' => trim(($r['first_name'] ?? '') . ' ' . ($r['last_name'] ?? '')),
            'staff_code' => (string)($r['staff_code'] ?? ''),
            'subject_code' => (string)($r['subject_code'] ?? ''),
            'subject_name' => (string)($r['subject_name'] ?? ''),
            'status' => (string)($r['status'] ?? '')
        ];
    }, $rows ?: []);

    echo json_encode([
        'draw' => $draw,
        'recordsTotal' => $total,
        'recordsFiltered' => $total,
        'data' => $data
    ]);
} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode(['error' => true, 'message' => 'Server error']);
}
?>