<?php
header('Content-Type: application/json');
session_start();

require_once('../libs/classes/DbPathsArray.php');
require_once('../libs/classes/App_DB.php');

if (!isset($_SESSION['userid'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

$db = new App_DB(DBU::$dba_user);

$month = isset($_GET['month']) ? (int) $_GET['month'] : (int) date('m');
$year = isset($_GET['year']) ? (int) $_GET['year'] : (int) date('Y');
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$darja = isset($_GET['darja']) && !empty($_GET['darja']) ? (int) $_GET['darja'] : 0;

// Find students who have assignments but NO collection for the given month/year
$sql = "SELECT a.id, a.student_id, a.fee_type_id, a.amount, a.due_day,
               a.fee_month as assign_month, a.fee_year as assign_year,
               t.fee_name, r.stdName, r.fatherName, reg.registrationNo,
               d.darja as darjaName
        FROM student_fee_assignments a
        JOIN fee_types t ON a.fee_type_id = t.id
        JOIN registrationinfo r ON a.student_id = r.sno
        LEFT JOIN regnumbers reg ON r.sno = reg.regSno
        LEFT JOIN stddarjaat sd ON sd.stdSno = r.sno AND sd.isCurrent = 1
        LEFT JOIN darjaat d ON sd.darja = d.derjaCode
        WHERE a.is_active = 1
        AND a.is_paid = 0";
//GROUP BY a.id";

$params = [];

// Optional darja filter
if ($darja > 0) {
    $sql .= " AND sd.darja = ?";
    $params[] = $darja;
}

// Optional search filter
if ($search !== '') {
    $sql .= " AND (r.stdName LIKE ? OR r.fatherName LIKE ? OR reg.registrationNo LIKE ?)";
    $searchParam = '%' . $search . '%';
    $params[] = $searchParam;
    $params[] = $searchParam;
    $params[] = $searchParam;
}

$sql .= " ORDER BY r.stdName ASC";

$res = $db->getRecordSet($sql, $params);
echo json_encode(['success' => true, 'data' => $res, 'month' => $month, 'year' => $year]);
?>