<?php
require("../../includes/config.php");

// Silence errors for clean JSON output, but keep logs available
ini_set('display_errors', 0);
ob_start(); 
header('Content-Type: application/json');

try {
    // Sanitize inputs
    $month  = $_GET['month'] ?? date('Y-m');
    $search = isset($_GET['search']) ? trim($_GET['search']) : '';
    $page   = (int)($_GET['page'] ?? 1);
    $limit  = 10; // Increased from 3 for better UX
    $offset = ($page - 1) * $limit;

    // Base Query - Ensure your DB columns match these exactly
    $query_str = " FROM fee_assign fa
                  JOIN students s ON fa.student_id = s.id
                  JOIN fee_types ft ON fa.fee_type_id = ft.id
                  WHERE fa.payment_status = 'unpaid' AND fa.due_month = :month";

    // Append search condition for Live Search
    if (!empty($search)) {
        $query_str .= " AND (s.student_name LIKE :search OR s.roll_no LIKE :search)";
    }

    // 1. Get Total Count for Pagination
    $count_stmt = $pdo->prepare("SELECT COUNT(*) $query_str");
    $count_stmt->bindValue(':month', $month);
    if (!empty($search)) {
        $count_stmt->bindValue(':search', "%$search%");
    }
    $count_stmt->execute();
    $total_rows = (int)$count_stmt->fetchColumn();

    // 2. Get Data Results
    $data_sql = "SELECT s.student_name, s.roll_no, s.parent_phone, ft.fee_title, fa.amount, fa.due_date " . 
                $query_str . " LIMIT :limit OFFSET :offset";
    
    $data_stmt = $pdo->prepare($data_sql);
    $data_stmt->bindValue(':month', $month);
    if (!empty($search)) {
        $data_stmt->bindValue(':search', "%$search%");
    }
    
    // Explicitly bind as Integers to prevent SQL syntax errors in LIMIT/OFFSET
    $data_stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
    $data_stmt->bindValue(':offset', (int)$offset, PDO::PARAM_INT);
    
    $data_stmt->execute();
    $results = $data_stmt->fetchAll(PDO::FETCH_ASSOC);

    // Wipe any accidental PHP warnings before sending JSON
    if (ob_get_length()) ob_end_clean();

    echo json_encode([
        'success' => true,
        'data' => $results,
        'total_pages' => (int)ceil($total_rows / $limit),
        'current_page' => $page,
        'total_records' => $total_rows
    ]);

} catch (Exception $e) {
    if (ob_get_length()) ob_end_clean();
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => $e->getMessage()
    ]);
}
?>