<?php
declare(strict_types=1);

require_once __DIR__ . '/../app/init.php';
app_require_login();

$requestedCompanyId = (int) ($_GET['company_id'] ?? 0);
$companyId = app_effective_company_id($requestedCompanyId > 0 ? $requestedCompanyId : null, true);
if ($companyId !== null && $companyId > 0) {
    app_set_context_company_id($companyId);
}
$company = $companyId ? app_company_by_id((int) $companyId) : null;
$isArabic = app_ui_language() === 'ar';
$txt = static fn (string $en, string $ar): string => $isArabic ? $ar : $en;
$fromDate = app_sanitize_date((string) ($_GET['from_date'] ?? date('Y-01-01')), date('Y-01-01'));
$toDate = app_sanitize_date((string) ($_GET['to_date'] ?? date('Y-m-d')), date('Y-m-d'));
if ($fromDate > $toDate) {
    [$fromDate, $toDate] = [$toDate, $fromDate];
}
$activeReport = app_normalize_whitespace((string) ($_GET['report'] ?? 'overview'));
if (!in_array($activeReport, ['overview', 'statement', 'receivables', 'collections', 'revenue', 'aging'], true)) {
    $activeReport = 'overview';
}
$activeReportPrintLabel = [
    'overview' => $txt('Student Accounts Report', 'تقرير حسابات الطلاب'),
    'statement' => $txt('Student Statement', 'كشف حساب طالب'),
    'receivables' => $txt('Student Receivables', 'مديونيات الطلاب'),
    'collections' => $txt('Student Collections', 'تحصيلات الطلاب'),
    'revenue' => $txt('Revenue Analysis', 'تحليل الإيرادات'),
    'aging' => $txt('Receivables Aging', 'أعمار المديونية'),
][$activeReport] ?? $txt('Student Accounts Report', 'تقرير حسابات الطلاب');
$studentFilter = app_normalize_whitespace((string) ($_GET['student_number'] ?? ''));
$guardianFilter = app_normalize_whitespace((string) ($_GET['guardian_contact_code'] ?? ''));
$companyQuery = $companyId ? ('company_id=' . urlencode((string) $companyId)) : '';
$pdo = app_pdo();

$studentOptions = $companyId !== null ? app_module_lookup_options('student_number', (int) $companyId) : [];
$guardianOptions = $companyId !== null ? app_module_lookup_options('customer_contact_code', (int) $companyId) : [];

$fetchRows = static function (string $sql, array $params = []) use ($pdo): array {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll() ?: [];
};
$fetchNumber = static function (string $sql, array $params = []) use ($pdo): float {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return app_number($stmt->fetchColumn());
};

$baseParams = ['company_id' => (int) $companyId, 'from_date' => $fromDate, 'to_date' => $toDate];
$studentSql = $studentFilter !== '' ? ' AND x.student_number = :student_number' : '';
$guardianSql = $guardianFilter !== '' ? ' AND x.guardian_contact_code = :guardian_contact_code' : '';
$studentParams = $studentFilter !== '' ? ['student_number' => $studentFilter] : [];
$guardianParams = $guardianFilter !== '' ? ['guardian_contact_code' => $guardianFilter] : [];
$filteredParams = array_merge($baseParams, $studentParams, $guardianParams);

$totalStudents = $fetchNumber('SELECT COUNT(*) FROM students WHERE company_id = :company_id', ['company_id' => (int) $companyId]);
$totalInvoices = $fetchNumber('SELECT COUNT(*) FROM student_invoices WHERE company_id = :company_id', ['company_id' => (int) $companyId]);
$totalBilled = $fetchNumber(
    'SELECT COALESCE(SUM(grand_total), 0) FROM student_invoices x WHERE x.company_id = :company_id AND x.invoice_date BETWEEN :from_date AND :to_date' . $studentSql . $guardianSql,
    $filteredParams
);
$totalCollected = $fetchNumber(
    'SELECT COALESCE(SUM(amount), 0) FROM student_receipts x WHERE x.company_id = :company_id AND x.receipt_date BETWEEN :from_date AND :to_date' . $studentSql . $guardianSql,
    $filteredParams
);
$totalRefunded = $fetchNumber(
    'SELECT COALESCE(SUM(amount), 0) FROM student_refunds x WHERE x.company_id = :company_id AND x.voucher_date BETWEEN :from_date AND :to_date' . $studentSql . $guardianSql,
    $filteredParams
);
$totalOutstanding = $fetchNumber(
    'SELECT COALESCE(SUM(GREATEST(grand_total - paid_amount, 0)), 0) FROM student_invoices x WHERE x.company_id = :company_id AND x.invoice_date <= :to_date' . $studentSql . $guardianSql,
    array_merge(['company_id' => (int) $companyId, 'to_date' => $toDate], $studentParams, $guardianParams)
);

$receivableRows = $fetchRows(
    'SELECT x.student_invoice_number, x.invoice_date, x.student_number, COALESCE(s.student_name, x.student_number) AS student_name,
            x.guardian_contact_code, COALESCE(c.contact_name, x.guardian_contact_code) AS guardian_name,
            x.stage_code, x.grade_code, x.revenue_type_code, x.grand_total, x.paid_amount,
            GREATEST(x.grand_total - x.paid_amount, 0) AS balance, x.status
     FROM student_invoices x
     LEFT JOIN students s ON s.company_id = x.company_id AND s.student_number = x.student_number
     LEFT JOIN contacts c ON c.company_id = x.company_id AND c.contact_code = x.guardian_contact_code
     WHERE x.company_id = :company_id AND x.invoice_date <= :to_date' . $studentSql . $guardianSql . '
     ORDER BY balance DESC, x.invoice_date ASC, x.student_invoice_number ASC',
    array_merge(['company_id' => (int) $companyId, 'to_date' => $toDate], $studentParams, $guardianParams)
);

$collectionRows = $fetchRows(
    'SELECT x.receipt_number, x.receipt_date, x.student_number, COALESCE(s.student_name, x.student_number) AS student_name,
            x.guardian_contact_code, COALESCE(c.contact_name, x.guardian_contact_code) AS guardian_name,
            x.amount, x.payment_method, x.payment_account_name, x.reference
     FROM student_receipts x
     LEFT JOIN students s ON s.company_id = x.company_id AND s.student_number = x.student_number
     LEFT JOIN contacts c ON c.company_id = x.company_id AND c.contact_code = x.guardian_contact_code
     WHERE x.company_id = :company_id AND x.receipt_date BETWEEN :from_date AND :to_date' . $studentSql . $guardianSql . '
     ORDER BY x.receipt_date DESC, x.receipt_number DESC',
    $filteredParams
);

$revenueRows = $fetchRows(
    'SELECT x.revenue_type_code, COALESCE(rt.revenue_type_name, x.revenue_type_code) AS revenue_type_name,
            x.stage_code, x.grade_code, COUNT(*) AS invoices_count,
            COALESCE(SUM(x.subtotal), 0) AS subtotal, COALESCE(SUM(x.discount_total), 0) AS discount_total,
            COALESCE(SUM(x.tax_total), 0) AS tax_total, COALESCE(SUM(x.grand_total), 0) AS grand_total,
            COALESCE(SUM(x.paid_amount), 0) AS paid_amount
     FROM student_invoices x
     LEFT JOIN student_revenue_types rt ON rt.company_id = x.company_id AND rt.revenue_type_code = x.revenue_type_code
     WHERE x.company_id = :company_id AND x.invoice_date BETWEEN :from_date AND :to_date' . $studentSql . $guardianSql . '
     GROUP BY x.revenue_type_code, rt.revenue_type_name, x.stage_code, x.grade_code
     ORDER BY x.stage_code ASC, x.grade_code ASC, x.revenue_type_code ASC',
    $filteredParams
);

$agingRows = $fetchRows(
    'SELECT x.student_number, COALESCE(s.student_name, x.student_number) AS student_name,
            x.guardian_contact_code, COALESCE(c.contact_name, x.guardian_contact_code) AS guardian_name,
            SUM(CASE WHEN DATEDIFF(:aging_date, x.invoice_date) BETWEEN 0 AND 30 THEN GREATEST(x.grand_total - x.paid_amount, 0) ELSE 0 END) AS bucket_0_30,
            SUM(CASE WHEN DATEDIFF(:aging_date_31, x.invoice_date) BETWEEN 31 AND 60 THEN GREATEST(x.grand_total - x.paid_amount, 0) ELSE 0 END) AS bucket_31_60,
            SUM(CASE WHEN DATEDIFF(:aging_date_61, x.invoice_date) BETWEEN 61 AND 90 THEN GREATEST(x.grand_total - x.paid_amount, 0) ELSE 0 END) AS bucket_61_90,
            SUM(CASE WHEN DATEDIFF(:aging_date_90, x.invoice_date) > 90 THEN GREATEST(x.grand_total - x.paid_amount, 0) ELSE 0 END) AS bucket_over_90,
            SUM(GREATEST(x.grand_total - x.paid_amount, 0)) AS total_balance
     FROM student_invoices x
     LEFT JOIN students s ON s.company_id = x.company_id AND s.student_number = x.student_number
     LEFT JOIN contacts c ON c.company_id = x.company_id AND c.contact_code = x.guardian_contact_code
     WHERE x.company_id = :company_id AND x.invoice_date <= :to_date' . $studentSql . $guardianSql . '
     GROUP BY x.student_number, s.student_name, x.guardian_contact_code, c.contact_name
     HAVING total_balance > 0.01
     ORDER BY total_balance DESC, x.student_number ASC',
    array_merge(['company_id' => (int) $companyId, 'to_date' => $toDate, 'aging_date' => $toDate, 'aging_date_31' => $toDate, 'aging_date_61' => $toDate, 'aging_date_90' => $toDate], $studentParams, $guardianParams)
);

$openingInvoices = $fetchNumber(
    'SELECT COALESCE(SUM(grand_total), 0) FROM student_invoices x WHERE x.company_id = :company_id AND x.invoice_date < :from_date' . $studentSql . $guardianSql,
    array_merge(['company_id' => (int) $companyId, 'from_date' => $fromDate], $studentParams, $guardianParams)
);
$openingReceipts = $fetchNumber(
    'SELECT COALESCE(SUM(amount), 0) FROM student_receipts x WHERE x.company_id = :company_id AND x.receipt_date < :from_date' . $studentSql . $guardianSql,
    array_merge(['company_id' => (int) $companyId, 'from_date' => $fromDate], $studentParams, $guardianParams)
);
$openingRefunds = $fetchNumber(
    'SELECT COALESCE(SUM(amount), 0) FROM student_refunds x WHERE x.company_id = :company_id AND x.voucher_date < :from_date' . $studentSql . $guardianSql,
    array_merge(['company_id' => (int) $companyId, 'from_date' => $fromDate], $studentParams, $guardianParams)
);
$openingBalance = $openingInvoices + $openingRefunds - $openingReceipts;

$statementInvoiceRows = $fetchRows(
    'SELECT x.invoice_date AS movement_date, x.student_invoice_number AS document_no, "Invoice" AS movement_type,
            x.student_number, COALESCE(s.student_name, x.student_number) AS student_name,
            x.guardian_contact_code, COALESCE(c.contact_name, x.guardian_contact_code) AS guardian_name,
            x.revenue_type_code AS description, x.grand_total AS debit, 0 AS credit
     FROM student_invoices x
     LEFT JOIN students s ON s.company_id = x.company_id AND s.student_number = x.student_number
     LEFT JOIN contacts c ON c.company_id = x.company_id AND c.contact_code = x.guardian_contact_code
     WHERE x.company_id = :company_id AND x.invoice_date BETWEEN :from_date AND :to_date' . $studentSql . $guardianSql,
    $filteredParams
);
$statementReceiptRows = $fetchRows(
    'SELECT x.receipt_date AS movement_date, x.receipt_number AS document_no, "Receipt" AS movement_type,
            x.student_number, COALESCE(s.student_name, x.student_number) AS student_name,
            x.guardian_contact_code, COALESCE(c.contact_name, x.guardian_contact_code) AS guardian_name,
            x.payment_method AS description, 0 AS debit, x.amount AS credit
     FROM student_receipts x
     LEFT JOIN students s ON s.company_id = x.company_id AND s.student_number = x.student_number
     LEFT JOIN contacts c ON c.company_id = x.company_id AND c.contact_code = x.guardian_contact_code
     WHERE x.company_id = :company_id AND x.receipt_date BETWEEN :from_date AND :to_date' . $studentSql . $guardianSql,
    $filteredParams
);
$statementRefundRows = $fetchRows(
    'SELECT x.voucher_date AS movement_date, x.voucher_number AS document_no, "Refund" AS movement_type,
            x.student_number, COALESCE(s.student_name, x.student_number) AS student_name,
            x.guardian_contact_code, COALESCE(c.contact_name, x.guardian_contact_code) AS guardian_name,
            x.reason AS description, x.amount AS debit, 0 AS credit
     FROM student_refunds x
     LEFT JOIN students s ON s.company_id = x.company_id AND s.student_number = x.student_number
     LEFT JOIN contacts c ON c.company_id = x.company_id AND c.contact_code = x.guardian_contact_code
     WHERE x.company_id = :company_id AND x.voucher_date BETWEEN :from_date AND :to_date' . $studentSql . $guardianSql,
    $filteredParams
);
$statementRows = array_merge($statementInvoiceRows, $statementReceiptRows, $statementRefundRows);
usort($statementRows, static function (array $a, array $b): int {
    return [(string) ($a['movement_date'] ?? ''), (string) ($a['document_no'] ?? '')]
        <=> [(string) ($b['movement_date'] ?? ''), (string) ($b['document_no'] ?? '')];
});

$runningBalance = $openingBalance;
foreach ($statementRows as $index => $row) {
    $runningBalance += app_number($row['debit'] ?? 0) - app_number($row['credit'] ?? 0);
    $statementRows[$index]['running_balance'] = $runningBalance;
}

if (app_normalize_whitespace((string) ($_GET['export'] ?? '')) === 'xlsx') {
    app_export_xlsx('student-accounts-report-' . date('Ymd-His') . '.xlsx', [
        'Summary' => [
            app_xlsx_row(['Student Accounts Report'], 'title'),
            ['Company', (string) ($company['name'] ?? '')],
            ['From', $fromDate],
            ['To', $toDate],
            ['Student', $studentFilter],
            ['Guardian', $guardianFilter],
            [],
            ['Metric', 'Value'],
            ['Students', $totalStudents],
            ['Invoices', $totalInvoices],
            ['Billed', $totalBilled],
            ['Collected', $totalCollected],
            ['Refunded', $totalRefunded],
            ['Outstanding', $totalOutstanding],
        ],
        'Statement' => array_merge([['Date', 'Document', 'Type', 'Student', 'Guardian', 'Description', 'Debit', 'Credit', 'Balance']], array_map(static fn (array $row): array => [(string) ($row['movement_date'] ?? ''), (string) ($row['document_no'] ?? ''), (string) ($row['movement_type'] ?? ''), (string) ($row['student_name'] ?? ''), (string) ($row['guardian_name'] ?? ''), (string) ($row['description'] ?? ''), app_number($row['debit'] ?? 0), app_number($row['credit'] ?? 0), app_number($row['running_balance'] ?? 0)], $statementRows)),
        'Receivables' => array_merge([['Invoice', 'Date', 'Student', 'Guardian', 'Stage', 'Grade', 'Revenue', 'Total', 'Paid', 'Balance', 'Status']], array_map(static fn (array $row): array => [(string) ($row['student_invoice_number'] ?? ''), (string) ($row['invoice_date'] ?? ''), (string) ($row['student_name'] ?? ''), (string) ($row['guardian_name'] ?? ''), (string) ($row['stage_code'] ?? ''), (string) ($row['grade_code'] ?? ''), (string) ($row['revenue_type_code'] ?? ''), app_number($row['grand_total'] ?? 0), app_number($row['paid_amount'] ?? 0), app_number($row['balance'] ?? 0), (string) ($row['status'] ?? '')], $receivableRows)),
        'Collections' => array_merge([['Receipt', 'Date', 'Student', 'Guardian', 'Amount', 'Method', 'Account', 'Reference']], array_map(static fn (array $row): array => [(string) ($row['receipt_number'] ?? ''), (string) ($row['receipt_date'] ?? ''), (string) ($row['student_name'] ?? ''), (string) ($row['guardian_name'] ?? ''), app_number($row['amount'] ?? 0), (string) ($row['payment_method'] ?? ''), (string) ($row['payment_account_name'] ?? ''), (string) ($row['reference'] ?? '')], $collectionRows)),
        'Revenue' => array_merge([['Revenue Type', 'Name', 'Stage', 'Grade', 'Invoices', 'Subtotal', 'Discount', 'VAT', 'Total', 'Paid']], array_map(static fn (array $row): array => [(string) ($row['revenue_type_code'] ?? ''), (string) ($row['revenue_type_name'] ?? ''), (string) ($row['stage_code'] ?? ''), (string) ($row['grade_code'] ?? ''), (int) ($row['invoices_count'] ?? 0), app_number($row['subtotal'] ?? 0), app_number($row['discount_total'] ?? 0), app_number($row['tax_total'] ?? 0), app_number($row['grand_total'] ?? 0), app_number($row['paid_amount'] ?? 0)], $revenueRows)),
        'Aging' => array_merge([['Student', 'Guardian', '0-30', '31-60', '61-90', 'Over 90', 'Total']], array_map(static fn (array $row): array => [(string) ($row['student_name'] ?? ''), (string) ($row['guardian_name'] ?? ''), app_number($row['bucket_0_30'] ?? 0), app_number($row['bucket_31_60'] ?? 0), app_number($row['bucket_61_90'] ?? 0), app_number($row['bucket_over_90'] ?? 0), app_number($row['total_balance'] ?? 0)], $agingRows)),
    ]);
}

$exportUrl = 'student_reports.php?' . http_build_query([
    'company_id' => $companyId,
    'report' => $activeReport,
    'from_date' => $fromDate,
    'to_date' => $toDate,
    'student_number' => $studentFilter,
    'guardian_contact_code' => $guardianFilter,
    'export' => 'xlsx',
]);
?>
<!doctype html>
<html <?= app_html_attrs() ?>>
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title><?= app_escape((string) app_config('app_name')) ?> - <?= app_escape($txt('Student Reports', 'تقارير حسابات الطلاب')) ?></title>
  <link rel="icon" href="assets/favicon.svg" type="image/svg+xml">
  <link rel="shortcut icon" href="assets/favicon.svg" type="image/svg+xml">
  <link rel="stylesheet" href="assets/styles.css">
</head>
<body>
<div class="app-shell has-sidebar">
  <?= app_sidebar_nav_html('student_reports', null, $companyId) ?>
  <div class="workspace-page">
    <section class="workspace-hero workspace-hero--students screen-only">
      <div>
        <a class="workspace-back" href="student_accounts.php<?= $companyId ? '?company_id=' . (int) $companyId : '' ?>"><?= app_escape($txt('Back to Student Accounts', 'رجوع لحسابات الطلاب')) ?></a>
        <h1><?= app_escape($txt('Student Reports', 'تقارير حسابات الطلاب')) ?></h1>
        <p><?= app_escape($txt('Student statements, receivables, collections, revenue analysis, and aging with date filters and Excel export.', 'كشوف حساب الطلاب والمديونيات والتحصيلات وتحليل الإيرادات وأعمار المديونية مع فلترة بالتاريخ وتصدير Excel.')) ?></p>
      </div>
      <img src="<?= app_escape(app_logo_path_for_header($company)) ?>" alt="Company Logo">
    </section>

    <section class="erp-control-panel screen-only">
      <div class="erp-control-main">
        <div>
          <h1><?= app_escape($txt('Filters', 'الفلاتر')) ?></h1>
          <p><?= app_escape($txt('Choose report, period, student, or guardian.', 'اختر التقرير والفترة والطالب أو ولي الأمر.')) ?></p>
        </div>
        <form method="get" class="erp-search">
          <?php if ($companyId !== null): ?><input type="hidden" name="company_id" value="<?= (int) $companyId ?>"><?php endif; ?>
          <select name="report">
            <option value="overview" <?= $activeReport === 'overview' ? 'selected' : '' ?>><?= app_escape($txt('Overview', 'نظرة عامة')) ?></option>
            <option value="statement" <?= $activeReport === 'statement' ? 'selected' : '' ?>><?= app_escape($txt('Student Statement', 'كشف حساب طالب')) ?></option>
            <option value="receivables" <?= $activeReport === 'receivables' ? 'selected' : '' ?>><?= app_escape($txt('Receivables', 'المديونيات')) ?></option>
            <option value="collections" <?= $activeReport === 'collections' ? 'selected' : '' ?>><?= app_escape($txt('Collections', 'التحصيلات')) ?></option>
            <option value="revenue" <?= $activeReport === 'revenue' ? 'selected' : '' ?>><?= app_escape($txt('Revenue Analysis', 'تحليل الإيرادات')) ?></option>
            <option value="aging" <?= $activeReport === 'aging' ? 'selected' : '' ?>><?= app_escape($txt('Aging', 'أعمار المديونية')) ?></option>
          </select>
          <input type="date" name="from_date" value="<?= app_escape($fromDate) ?>">
          <input type="date" name="to_date" value="<?= app_escape($toDate) ?>">
          <select name="student_number">
            <option value=""><?= app_escape($txt('All Students', 'كل الطلاب')) ?></option>
            <?php foreach ($studentOptions as $code => $label): ?>
              <option value="<?= app_escape((string) $code) ?>" <?= $studentFilter === (string) $code ? 'selected' : '' ?>><?= app_escape((string) $label) ?></option>
            <?php endforeach; ?>
          </select>
          <select name="guardian_contact_code">
            <option value=""><?= app_escape($txt('All Guardians', 'كل أولياء الأمور')) ?></option>
            <?php foreach ($guardianOptions as $code => $label): ?>
              <option value="<?= app_escape((string) $code) ?>" <?= $guardianFilter === (string) $code ? 'selected' : '' ?>><?= app_escape((string) $label) ?></option>
            <?php endforeach; ?>
          </select>
          <button class="btn btn-primary" type="submit"><?= app_escape($txt('Search', 'بحث')) ?></button>
          <button class="btn btn-secondary" type="button" data-print-page data-print-target="#studentReportPrintArea"><?= app_escape($txt('Print', 'طباعة')) ?></button>
          <a class="btn btn-secondary" href="<?= app_escape($exportUrl) ?>">Export XLSX</a>
        </form>
      </div>
    </section>

    <main
      id="studentReportPrintArea"
      class="print-scope"
      data-print-scope
      data-default-print-target
      data-report-print
      data-print-title="<?= app_escape($activeReportPrintLabel) ?>"
      data-print-logo="<?= app_escape(app_logo_path_for_header($company)) ?>"
    >
      <section class="table-card report-cover-card">
        <div class="table-header">
          <div>
            <h3><?= app_escape($txt('Student Accounts Report', 'تقرير حسابات الطلاب')) ?></h3>
            <div class="hint"><?= app_escape((string) ($company['name'] ?? app_config('app_name'))) ?> | <?= app_escape($fromDate) ?> - <?= app_escape($toDate) ?></div>
          </div>
        </div>
        <section class="workspace-kpis">
          <div class="workspace-kpi"><span><?= app_escape($txt('Students', 'الطلاب')) ?></span><strong><?= app_escape(number_format($totalStudents)) ?></strong></div>
          <div class="workspace-kpi"><span><?= app_escape($txt('Billed', 'إجمالي الفواتير')) ?></span><strong><?= app_escape(app_money_format($totalBilled)) ?></strong></div>
          <div class="workspace-kpi"><span><?= app_escape($txt('Collected', 'المحصل')) ?></span><strong><?= app_escape(app_money_format($totalCollected)) ?></strong></div>
          <div class="workspace-kpi"><span><?= app_escape($txt('Outstanding', 'الرصيد المستحق')) ?></span><strong><?= app_escape(app_money_format($totalOutstanding)) ?></strong></div>
        </section>
      </section>

      <?php if ($activeReport === 'overview' || $activeReport === 'statement'): ?>
        <section class="table-card">
          <div class="table-header"><div><h3><?= app_escape($txt('Student Statement', 'كشف حساب الطالب')) ?></h3><div class="hint"><?= app_escape($txt('Opening balance is calculated before the from date.', 'رصيد أول المدة محسوب قبل تاريخ البداية.')) ?></div></div></div>
          <div class="table-wrap">
            <table class="data-table" style="min-width: 1100px;">
              <thead><tr><th><?= app_escape($txt('Date', 'التاريخ')) ?></th><th><?= app_escape($txt('Document', 'المستند')) ?></th><th><?= app_escape($txt('Type', 'النوع')) ?></th><th><?= app_escape($txt('Student', 'الطالب')) ?></th><th><?= app_escape($txt('Guardian', 'ولي الأمر')) ?></th><th><?= app_escape($txt('Description', 'البيان')) ?></th><th><?= app_escape($txt('Debit', 'مدين')) ?></th><th><?= app_escape($txt('Credit', 'دائن')) ?></th><th><?= app_escape($txt('Balance', 'الرصيد')) ?></th></tr></thead>
              <tbody>
                <tr><td><?= app_escape($fromDate) ?></td><td>Opening</td><td><?= app_escape($txt('Opening Balance', 'رصيد أول المدة')) ?></td><td colspan="3"></td><td><?= app_escape($openingBalance >= 0 ? app_money_format($openingBalance) : '0.00') ?></td><td><?= app_escape($openingBalance < 0 ? app_money_format(abs($openingBalance)) : '0.00') ?></td><td><?= app_escape(app_money_format($openingBalance)) ?></td></tr>
                <?php foreach ($statementRows as $row): ?>
                  <tr>
                    <td><?= app_escape(app_date_format((string) ($row['movement_date'] ?? ''))) ?></td>
                    <td><?= app_escape((string) ($row['document_no'] ?? '')) ?></td>
                    <td><?= app_escape((string) ($row['movement_type'] ?? '')) ?></td>
                    <td><?= app_escape((string) ($row['student_name'] ?? '')) ?></td>
                    <td><?= app_escape((string) ($row['guardian_name'] ?? '')) ?></td>
                    <td><?= app_escape((string) ($row['description'] ?? '')) ?></td>
                    <td><?= app_escape(app_money_format($row['debit'] ?? 0)) ?></td>
                    <td><?= app_escape(app_money_format($row['credit'] ?? 0)) ?></td>
                    <td><?= app_escape(app_money_format($row['running_balance'] ?? 0)) ?></td>
                  </tr>
                <?php endforeach; ?>
              </tbody>
            </table>
          </div>
        </section>
      <?php endif; ?>

      <?php if ($activeReport === 'overview' || $activeReport === 'receivables'): ?>
        <section class="table-card">
          <div class="table-header"><div><h3><?= app_escape($txt('Student Receivables', 'مديونيات الطلاب')) ?></h3><div class="hint"><?= app_escape($txt('Balances as of selected to date.', 'الأرصدة حتى تاريخ النهاية المختار.')) ?></div></div></div>
          <div class="table-wrap">
            <table class="data-table" style="min-width: 1150px;">
              <thead><tr><th><?= app_escape($txt('Invoice', 'الفاتورة')) ?></th><th><?= app_escape($txt('Date', 'التاريخ')) ?></th><th><?= app_escape($txt('Student', 'الطالب')) ?></th><th><?= app_escape($txt('Guardian', 'ولي الأمر')) ?></th><th><?= app_escape($txt('Stage', 'المرحلة')) ?></th><th><?= app_escape($txt('Grade', 'الصف')) ?></th><th><?= app_escape($txt('Revenue', 'الإيراد')) ?></th><th><?= app_escape($txt('Total', 'الإجمالي')) ?></th><th><?= app_escape($txt('Paid', 'المسدد')) ?></th><th><?= app_escape($txt('Balance', 'الرصيد')) ?></th><th><?= app_escape($txt('Status', 'الحالة')) ?></th></tr></thead>
              <tbody>
                <?php foreach ($receivableRows as $row): ?>
                  <tr><td><?= app_escape((string) ($row['student_invoice_number'] ?? '')) ?></td><td><?= app_escape(app_date_format((string) ($row['invoice_date'] ?? ''))) ?></td><td><?= app_escape((string) ($row['student_name'] ?? '')) ?></td><td><?= app_escape((string) ($row['guardian_name'] ?? '')) ?></td><td><?= app_escape((string) ($row['stage_code'] ?? '')) ?></td><td><?= app_escape((string) ($row['grade_code'] ?? '')) ?></td><td><?= app_escape((string) ($row['revenue_type_code'] ?? '')) ?></td><td><?= app_escape(app_money_format($row['grand_total'] ?? 0)) ?></td><td><?= app_escape(app_money_format($row['paid_amount'] ?? 0)) ?></td><td><?= app_escape(app_money_format($row['balance'] ?? 0)) ?></td><td><?= app_escape((string) ($row['status'] ?? '')) ?></td></tr>
                <?php endforeach; ?>
              </tbody>
            </table>
          </div>
        </section>
      <?php endif; ?>

      <?php if ($activeReport === 'overview' || $activeReport === 'collections'): ?>
        <section class="table-card">
          <div class="table-header"><div><h3><?= app_escape($txt('Student Collections', 'تحصيلات الطلاب')) ?></h3><div class="hint"><?= app_escape(app_money_format($totalCollected)) ?></div></div></div>
          <div class="table-wrap"><table class="data-table" style="min-width: 980px;"><thead><tr><th><?= app_escape($txt('Receipt', 'السند')) ?></th><th><?= app_escape($txt('Date', 'التاريخ')) ?></th><th><?= app_escape($txt('Student', 'الطالب')) ?></th><th><?= app_escape($txt('Guardian', 'ولي الأمر')) ?></th><th><?= app_escape($txt('Amount', 'المبلغ')) ?></th><th><?= app_escape($txt('Method', 'الطريقة')) ?></th><th><?= app_escape($txt('Account', 'الحساب')) ?></th><th><?= app_escape($txt('Reference', 'المرجع')) ?></th></tr></thead><tbody><?php foreach ($collectionRows as $row): ?><tr><td><?= app_escape((string) ($row['receipt_number'] ?? '')) ?></td><td><?= app_escape(app_date_format((string) ($row['receipt_date'] ?? ''))) ?></td><td><?= app_escape((string) ($row['student_name'] ?? '')) ?></td><td><?= app_escape((string) ($row['guardian_name'] ?? '')) ?></td><td><?= app_escape(app_money_format($row['amount'] ?? 0)) ?></td><td><?= app_escape((string) ($row['payment_method'] ?? '')) ?></td><td><?= app_escape((string) ($row['payment_account_name'] ?? '')) ?></td><td><?= app_escape((string) ($row['reference'] ?? '')) ?></td></tr><?php endforeach; ?></tbody></table></div>
        </section>
      <?php endif; ?>

      <?php if ($activeReport === 'overview' || $activeReport === 'revenue'): ?>
        <section class="table-card">
          <div class="table-header"><div><h3><?= app_escape($txt('Revenue Analysis', 'تحليل الإيرادات')) ?></h3><div class="hint"><?= app_escape(app_money_format($totalBilled)) ?></div></div></div>
          <div class="table-wrap"><table class="data-table" style="min-width: 1020px;"><thead><tr><th><?= app_escape($txt('Revenue Type', 'نوع الإيراد')) ?></th><th><?= app_escape($txt('Name', 'الاسم')) ?></th><th><?= app_escape($txt('Stage', 'المرحلة')) ?></th><th><?= app_escape($txt('Grade', 'الصف')) ?></th><th><?= app_escape($txt('Invoices', 'الفواتير')) ?></th><th><?= app_escape($txt('Subtotal', 'قبل الضريبة')) ?></th><th><?= app_escape($txt('Discount', 'الخصم')) ?></th><th><?= app_escape($txt('VAT', 'الضريبة')) ?></th><th><?= app_escape($txt('Total', 'الإجمالي')) ?></th><th><?= app_escape($txt('Paid', 'المسدد')) ?></th></tr></thead><tbody><?php foreach ($revenueRows as $row): ?><tr><td><?= app_escape((string) ($row['revenue_type_code'] ?? '')) ?></td><td><?= app_escape((string) ($row['revenue_type_name'] ?? '')) ?></td><td><?= app_escape((string) ($row['stage_code'] ?? '')) ?></td><td><?= app_escape((string) ($row['grade_code'] ?? '')) ?></td><td><?= app_escape(number_format((int) ($row['invoices_count'] ?? 0))) ?></td><td><?= app_escape(app_money_format($row['subtotal'] ?? 0)) ?></td><td><?= app_escape(app_money_format($row['discount_total'] ?? 0)) ?></td><td><?= app_escape(app_money_format($row['tax_total'] ?? 0)) ?></td><td><?= app_escape(app_money_format($row['grand_total'] ?? 0)) ?></td><td><?= app_escape(app_money_format($row['paid_amount'] ?? 0)) ?></td></tr><?php endforeach; ?></tbody></table></div>
        </section>
      <?php endif; ?>

      <?php if ($activeReport === 'overview' || $activeReport === 'aging'): ?>
        <section class="table-card">
          <div class="table-header"><div><h3><?= app_escape($txt('Receivables Aging', 'أعمار مديونية الطلاب')) ?></h3><div class="hint"><?= app_escape($toDate) ?></div></div></div>
          <div class="table-wrap"><table class="data-table"><thead><tr><th><?= app_escape($txt('Student', 'الطالب')) ?></th><th><?= app_escape($txt('Guardian', 'ولي الأمر')) ?></th><th>0-30</th><th>31-60</th><th>61-90</th><th>90+</th><th><?= app_escape($txt('Total', 'الإجمالي')) ?></th></tr></thead><tbody><?php foreach ($agingRows as $row): ?><tr><td><?= app_escape((string) ($row['student_name'] ?? '')) ?></td><td><?= app_escape((string) ($row['guardian_name'] ?? '')) ?></td><td><?= app_escape(app_money_format($row['bucket_0_30'] ?? 0)) ?></td><td><?= app_escape(app_money_format($row['bucket_31_60'] ?? 0)) ?></td><td><?= app_escape(app_money_format($row['bucket_61_90'] ?? 0)) ?></td><td><?= app_escape(app_money_format($row['bucket_over_90'] ?? 0)) ?></td><td><?= app_escape(app_money_format($row['total_balance'] ?? 0)) ?></td></tr><?php endforeach; ?></tbody></table></div>
        </section>
      <?php endif; ?>
    </main>
  </div>
</div>
<script src="assets/app.js"></script>
</body>
</html>
