<?php
/**
 * Outstanding fee balances.
 *
 * The previous inline queries joined fee_structures and payments in one pass:
 *
 *     JOIN fee_structures fs ON fs.class_name = s.class AND fs.academic_year = ?
 *     LEFT JOIN payments p   ON p.student_id  = s.id    AND p.fee_structure_id = fs.id
 *     ... SUM(fs.amount) - SUM(p.amount_paid)
 *
 * A class has one fee_structure row per fee_type (9 in the demo school). When a
 * student pays one of those fees in two instalments, the join duplicates that
 * structure row, so SUM(fs.amount) counts the fee twice. The student's balance
 * is inflated by the full fee amount for every extra payment row — and that
 * inflated figure is what goes out in the reminder SMS.
 *
 * Both sides are pre-aggregated here so each fee is billed once and each
 * payment counted once, whatever the row counts are.
 */

if (!defined('DB_HOST')) {
    require_once __DIR__ . '/../config.php';
}

if (!function_exists('rbcOutstandingBalances')) {
    /**
     * Students owing more than $min_balance for the given academic year.
     *
     * @param mysqli      $conn
     * @param string      $academic_year
     * @param string|null $class_name  Already resolved via resolveClassName(); null = all classes.
     * @param float       $min_balance Exclusive lower bound.
     *
     * @return array<int, array{id:int, first_name:string, last_name:string, class:string,
     *                          father_phone:?string, mother_phone:?string,
     *                          billed:float, paid:float, balance:float}>
     */
    function rbcOutstandingBalances(
        mysqli  $conn,
        string  $academic_year,
        ?string $class_name  = null,
        float   $min_balance = 0.01
    ): array {
        $sql = "
            SELECT s.id, s.first_name, s.last_name, s.class,
                   s.father_phone, s.mother_phone,
                   COALESCE(billed.total, 0)                              AS billed,
                   COALESCE(paid.total, 0)                                AS paid,
                   COALESCE(billed.total, 0) - COALESCE(paid.total, 0)    AS balance
            FROM students s
            -- One row per class: the total billed for that class this year.
            JOIN (
                SELECT class_name, SUM(amount) AS total
                FROM fee_structures
                WHERE academic_year = ?
                GROUP BY class_name
            ) billed ON billed.class_name = s.class
            -- One row per student: everything they have paid against this
            -- year's structures, however many instalments that took.
            LEFT JOIN (
                SELECT p.student_id, SUM(p.amount_paid) AS total
                FROM payments p
                JOIN fee_structures fs ON fs.id = p.fee_structure_id
                WHERE fs.academic_year = ?
                GROUP BY p.student_id
            ) paid ON paid.student_id = s.id
            WHERE s.status = 'active'";

        $types = 'ss';
        $binds = [$academic_year, $academic_year];

        if ($class_name !== null && $class_name !== '') {
            $sql    .= " AND s.class = ?";
            $types  .= 's';
            $binds[] = $class_name;
        }

        $sql   .= " HAVING balance > ? ORDER BY balance DESC";
        $types .= 'd';
        $binds[] = $min_balance;

        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            error_log('rbcOutstandingBalances: prepare failed - ' . $conn->error);
            return [];
        }
        $stmt->bind_param($types, ...$binds);
        $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        foreach ($rows as &$r) {
            $r['id']      = (int) $r['id'];
            $r['billed']  = (float) $r['billed'];
            $r['paid']    = (float) $r['paid'];
            $r['balance'] = (float) $r['balance'];
        }
        unset($r);

        return $rows;
    }
}

if (!function_exists('rbcStudentFeeBreakdown')) {
    /**
     * Every fee_structures row for one student's class + academic year, with
     * paid/remaining computed per item.
     *
     * The one shared source for what used to be ~4 near-identical inline
     * queries (parent-portal fee page, its AJAX refresh, the canteen fee-desk
     * lookup, the admin per-student payment summary) — each had its own copy
     * of this JOIN and had started to drift: different sort order, and a
     * separate regex-based "is this feeding?" check that could disagree with
     * the one the canteen module itself trusts. is_feeding here is read
     * straight off billing_frequency (see database/migrations/bulk_fee_payment.sql),
     * so there is exactly one definition of "is this a feeding fee" left.
     *
     * Returns every fee item, outstanding or not — callers filter on
     * `remaining` themselves, since some need "everything" (a fee statement)
     * and some need "outstanding only" (a payment desk).
     *
     * @return array<int, array{id:int, fee_type:string, term:?string,
     *   billing_frequency:string, amount:float, due_date:?string,
     *   paid:float, remaining:float, is_feeding:bool}>
     */
    function rbcStudentFeeBreakdown(
        mysqli $conn,
        int    $student_id,
        string $class_name,
        string $academic_year
    ): array {
        $stmt = $conn->prepare(
            "SELECT fs.id, fs.fee_type, fs.term, fs.billing_frequency, fs.amount, fs.due_date,
                    COALESCE(SUM(p.amount_paid), 0)                     AS paid,
                    fs.amount - COALESCE(SUM(p.amount_paid), 0)         AS remaining
             FROM fee_structures fs
             LEFT JOIN payments p
                    ON p.fee_structure_id = fs.id
                   AND p.student_id = ?
             WHERE fs.class_name    = ?
               AND fs.academic_year = ?
             GROUP BY fs.id, fs.fee_type, fs.term, fs.billing_frequency, fs.amount, fs.due_date
             ORDER BY
                 CASE WHEN fs.billing_frequency = 'recurring_weekly' THEN 0 ELSE 1 END,
                 fs.due_date ASC,
                 fs.fee_type ASC"
        );
        if (!$stmt) {
            error_log('rbcStudentFeeBreakdown: prepare failed - ' . $conn->error);
            return [];
        }
        $stmt->bind_param('iss', $student_id, $class_name, $academic_year);
        $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        foreach ($rows as &$r) {
            $r['id']         = (int) $r['id'];
            $r['amount']     = (float) $r['amount'];
            $r['paid']       = (float) $r['paid'];
            $r['remaining']  = (float) $r['remaining'];
            $r['is_feeding'] = $r['billing_frequency'] === 'recurring_weekly';
        }
        unset($r);

        return $rows;
    }
}

if (!function_exists('rbcCheckoutFeeContext')) {
    /**
     * Everything a payment UI needs for one student: their outstanding fee
     * items — filtered to specific ids, or to the current term, or left as
     * every outstanding item — plus the current term name and how many
     * weeks of feeding are left to sell in it. Shared by
     * ajax/get_outstanding_fee_items.php (bursar bulk-payment checklist)
     * and both online checkout pages (payment_checkout.php,
     * parent-portal/checkout.php), so there is one definition of "what does
     * this student still owe" instead of three.
     *
     * Amounts are always `remaining` (outstanding balance), never the
     * fee_structures sticker `amount` — a checkout page charging the full
     * sticker amount for an item that already has a partial cash payment
     * against it would overcharge for exactly the part already paid.
     *
     * @param array<int,int>|null $onlyFeeIds Restrict to these fee_structure ids, or null for no id filter.
     * @param string|null         $onlyTerm   Restrict to this term (e.g. current term for "pay this term"), or null for no term filter.
     *
     * @return array{current_term:string, max_weeks_covered:int, items:array<int,array{
     *   fee_structure_id:int, fee_type:string, term:?string, is_current_term:bool,
     *   is_feeding:bool, amount:float, paid:float, remaining:float, due_date:?string}>}
     */
    function rbcCheckoutFeeContext(
        mysqli  $conn,
        int     $student_id,
        string  $class_name,
        string  $academic_year,
        ?array  $onlyFeeIds = null,
        ?string $onlyTerm = null
    ): array {
        $currentTerm = function_exists('getCurrentTerm') ? getCurrentTerm() : '';

        $items = [];
        foreach (rbcStudentFeeBreakdown($conn, $student_id, $class_name, $academic_year) as $row) {
            if ($row['remaining'] <= 0.005) {
                continue;
            }
            if ($onlyFeeIds !== null && !in_array($row['id'], $onlyFeeIds, true)) {
                continue;
            }
            if ($onlyTerm !== null && $row['term'] !== $onlyTerm) {
                continue;
            }
            $items[] = [
                'fee_structure_id' => $row['id'],
                'fee_type'         => $row['fee_type'],
                'term'             => $row['term'],
                'is_current_term'  => $row['term'] === $currentTerm,
                'is_feeding'       => $row['is_feeding'],
                'amount'           => $row['amount'],
                'paid'             => $row['paid'],
                'remaining'        => $row['remaining'],
                'due_date'         => $row['due_date'],
            ];
        }

        // Weeks of feeding left to sell in the current term — falls back to
        // a fixed default when the school hasn't entered this term's dates
        // into school_terms yet.
        $maxWeeksCovered = 16;
        $termStmt = $conn->prepare(
            "SELECT start_date, end_date FROM school_terms WHERE academic_year = ? AND term = ? LIMIT 1"
        );
        $termStmt->bind_param('ss', $academic_year, $currentTerm);
        $termStmt->execute();
        $termRow = $termStmt->get_result()->fetch_assoc();
        $termStmt->close();

        if ($termRow && !empty($termRow['end_date'])) {
            $today = new DateTime('today');
            $start = new DateTime($termRow['start_date']);
            $end   = new DateTime($termRow['end_date']);
            $from  = $today > $start ? $today : $start;
            $maxWeeksCovered = $end > $from ? max(1, (int) ceil($from->diff($end)->days / 7)) : 1;
        }

        return [
            'current_term'      => $currentTerm,
            'max_weeks_covered' => $maxWeeksCovered,
            'items'             => $items,
        ];
    }
}

if (!function_exists('rbcAvailableCredit')) {
    /**
     * A student's total unspent balance in student_fee_credits (see
     * database/migrations/bulk_fee_payment_batches.sql). Credits are only
     * ever created by an explicit admin/bursar action — never auto-detected
     * from a withdrawal/transfer/fee-correction scenario — so this is purely
     * a sum of whatever has already been issued and not yet applied.
     *
     * Not wired into rbcStudentFeeBreakdown()/rbcOutstandingBalances() yet:
     * nothing can create a credit until the bulk-payment UI does, so every
     * caller would just get 0.0 today. Added now so that UI has a read side
     * to report into as soon as it exists, without a second migration.
     */
    function rbcAvailableCredit(mysqli $conn, int $student_id): float
    {
        $stmt = $conn->prepare(
            "SELECT COALESCE(SUM(amount), 0) AS total
             FROM student_fee_credits
             WHERE student_id = ? AND status = 'available'"
        );
        if (!$stmt) {
            error_log('rbcAvailableCredit: prepare failed - ' . $conn->error);
            return 0.0;
        }
        $stmt->bind_param('i', $student_id);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        return (float) ($row['total'] ?? 0.0);
    }
}
