<?php

function getSOA($con, $student_id)
{
    // 1. GET LATEST ENROLLMENT
    $stmt = $con->prepare("
        SELECT *
        FROM enroll_student
        WHERE student_id = ?
        ORDER BY enroll_id DESC
        LIMIT 1
    ");
    $stmt->bind_param("i", $student_id);
    $stmt->execute();
    $enroll = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if (!$enroll) {
        return [
            'monthly_payments' => [],
            'total_paid' => 0,
            'remaining_balance' => 0
        ];
    }

    $enroll_id = $enroll['enroll_id'];

    // 2. GET SOA FEES
    $stmt = $con->prepare("
        SELECT *
        FROM soa
        WHERE enroll_id = ?
        ORDER BY soa_date DESC
        LIMIT 1
    ");
    $stmt->bind_param("i", $enroll_id);
    $stmt->execute();
    $fee = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if (!$fee) {
        return [
            'monthly_payments' => [],
            'total_paid' => 0,
            'remaining_balance' => 0
        ];
    }

    // 3. GET VERIFIED PAYMENTS
    $stmt = $con->prepare("
        SELECT spay_amount
        FROM student_payment
        WHERE student_id = ?
        AND spay_status IN ('Verified','Underpaid')
        ORDER BY created_at ASC
    ");
    $stmt->bind_param("i", $student_id);
    $stmt->execute();
    $res = $stmt->get_result();

    $payments = [];
    while ($row = $res->fetch_assoc()) {
        $payments[] = (float)$row['spay_amount'];
    }
    $stmt->close();

    $totalPaid = array_sum($payments);
    $remaining = $totalPaid;

    // 4. BUILD SOA BREAKDOWN
    $monthly_payments = [];

    // DOWNPAYMENT
    $down_due = (float)$fee['down_payment'];
    $down_paid = min($down_due, $remaining);
    $remaining -= $down_paid;

    $monthly_payments[] = [
        'month' => 'Downpayment',
        'amount_due' => $down_due,
        'amount_paid' => $down_paid,
        'balance' => $down_due - $down_paid,
        'status' =>
            $down_paid >= $down_due ? 'Paid' :
            ($down_paid > 0 ? 'Underpaid' : 'Unpaid')
    ];

    // MONTHLY PAYMENTS (1–4)
    for ($i = 1; $i <= 4; $i++) {

        $due = (float)$fee['monthly_fee'];
        $paid = min($due, $remaining);
        $remaining -= $paid;

        $monthly_payments[] = [
            'month' => $i,
            'amount_due' => $due,
            'amount_paid' => $paid,
            'balance' => $due - $paid,
            'status' =>
                $paid >= $due ? 'Paid' :
                ($paid > 0 ? 'Underpaid' : 'Unpaid')
        ];
    }

    // 5. COMPUTE TOTAL BALANCE
    $total_due = (float)$fee['down_payment'] + ((float)$fee['monthly_fee'] * 4);
    $remaining_balance = max(0, $total_due - $totalPaid);

    return [
        'monthly_payments' => $monthly_payments,
        'total_paid' => $totalPaid,
        'remaining_balance' => $remaining_balance
    ];
}