<?php
header('Content-Type: application/json');
require_once __DIR__ . '../../../db/config.php';

//
// ## Accounts Receivable Aging Report
//
// Purpose: Show outstanding customer balances grouped by age.
//

$pdo = db();

$as_of_date = $_GET['as_of_date'] ?? date('Y-m-d');

try {
    $sql = <<<SQL
    SELECT
        s.subledger_id,
        s.name as student_name,
        i.id as invoice_id,
        i.invoice_date,
        i.due_date,
        DATEDIFF(:as_of_date, i.due_date) as days_overdue,
        (i.total_amount - COALESCE(p.paid_amount, 0)) as outstanding_balance
    FROM invoices i
    JOIN subledgers s ON i.student_id = s.subledger_id
    LEFT JOIN (
        SELECT invoice_id, SUM(amount) as paid_amount
        FROM payment_lines
        GROUP BY invoice_id
    ) p ON i.id = p.invoice_id
    WHERE (i.total_amount - COALESCE(p.paid_amount, 0)) > 0.01
      AND i.invoice_date <= :as_of_date
    ORDER BY s.name, i.invoice_date;
    SQL;

    $stmt = $pdo->prepare($sql);
    $stmt->execute([':as_of_date' => $as_of_date]);
    $details = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Bucket the outstanding balances
    $buckets = [
        'current' => 0,
        '1-30' => 0,
        '31-60' => 0,
        '61-90' => 0,
        '91+' => 0
    ];
    $total_outstanding = 0;

    foreach ($details as $detail) {
        $total_outstanding += $detail['outstanding_balance'];
        if ($detail['days_overdue'] <= 0) {
            $buckets['current'] += $detail['outstanding_balance'];
        } elseif ($detail['days_overdue'] <= 30) {
            $buckets['1-30'] += $detail['outstanding_balance'];
        } elseif ($detail['days_overdue'] <= 60) {
            $buckets['31-60'] += $detail['outstanding_balance'];
        } elseif ($detail['days_overdue'] <= 90) {
            $buckets['61-90'] += $detail['outstanding_balance'];
        } else {
            $buckets['91+'] += $detail['outstanding_balance'];
        }
    }

    echo json_encode([
        'success' => true,
        'report_name' => 'Accounts Receivable Aging',
        'generated_at' => date('c'),
        'filters' => ['as_of_date' => $as_of_date],
        'summary' => [
            'total_outstanding' => $total_outstanding,
            'buckets' => $buckets
        ],
        'details' => $details
    ]);

} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => 'Database error: ' . $e->getMessage()
    ]);
}