<?= $this->extend('layout/management_layout') ?>
<?= $this->section('content') ?>

<div class="container-fluid mt-4">
  <h2 class="text-center mt-4 mb-3">Detailed Financial Report</h2>

  <style>
    /* Keep table headers visible while scrolling the page */
    #invoicesTable thead th {
      position: sticky;
      top: 0;
      z-index: 2;
      /* above table body cells */
      background-color: #f8f9fa;
      /* Bootstrap light header bg */
    }
  </style>

  <!-- Top toolbar: put all filters on one line -->
  <form id="financialFilterForm" method="get" class="d-flex align-items-end gap-3 flex-nowrap justify-content-center mb-3 w-100 overflow-auto" style="white-space: nowrap;">
    <div class="d-flex align-items-center gap-2 flex-nowrap">
      <label class="form-label mb-0 me-2">School year</label>
      <select name="school_year" class="form-select form-select-sm rounded-pill px-3" style="min-width: 180px;">
        <?php foreach (($schoolYears ?? []) as $sy): ?>
          <option value="<?= esc($sy) ?>" <?= (string)($selectedYear ?? '') === (string)$sy ? 'selected' : '' ?>><?= esc($sy) ?></option>
        <?php endforeach; ?>
      </select>
    </div>
    <div class="d-flex align-items-end gap-2 flex-nowrap">
      <div class="d-flex flex-column">
        <label class="form-label">Date From:</label>
        <input type="date" name="date_from" class="form-control" value="<?= esc($_GET['date_from'] ?? ($dateFrom ?? '')) ?>">
      </div>
      <div class="d-flex flex-column">
        <label class="form-label">Date To:</label>
        <input type="date" name="date_to" class="form-control" value="<?= esc($_GET['date_to'] ?? ($dateTo ?? '')) ?>">
      </div>
      <button type="submit" class="btn btn-secondary ms-2">Filter</button>
    </div>
  </form>

  <!-- Loading & error placeholders -->
  <div id="reportLoading" class="text-center my-4 d-none">
    <div class="spinner-border text-primary" role="status"></div>
    <div class="mt-2">Loading report…</div>
    <div class="text-muted small" id="reportLastRef"></div>
  </div>
  <div id="reportError" class="alert alert-danger d-none">
    Failed to load report. Please try again.
    <a href="#" id="reportRetry" class="alert-link ms-2">Retry</a>
  </div>
  <div class="d-flex gap-2 mb-3 justify-content-end flex-nowrap w-100 overflow-auto" style="white-space: nowrap;">
    <a id="downloadCsvLink" href="<?= base_url('payment/download_csv') ?>" class="btn btn-success">Download CSV</a>
    <button class="btn btn-primary" onclick="window.print()">Print Report</button>
    <a id="summaryReportLink" href="<?= base_url('financial-report/financialReportSummary') ?>" class="btn btn-info">Display Summary Report</a>
  </div>
  <?php if (isset($eventFeesTotal)): ?>
    <div class="alert alert-info mb-3">
      <strong>Event fees total:</strong> $<?= number_format((float)$eventFeesTotal, 2) ?>
    </div>
  <?php endif; ?>
  <div class="table-responsive">
    <table id="invoicesTable" class="table table-bordered table-striped align-middle w-100">
      <thead>
        <tr>
          <th>Invoice #</th>
          <th>Parent</th>
          <th>School Year</th>
          <th>Total</th>
          <th>Paid</th>
          <th>Cash</th>
          <th>Credit</th>
          <th>Check</th>
          <th>Balance</th>
          <th>Refund</th>
          <th>Discount</th>
          <th>Status</th>
        </tr>
      </thead>
      <tbody>
        <?php if (!empty($invoices ?? [])): ?>
          <?php
          $breakdowns = $paymentBreakdown ?? [];
          foreach ($invoices as $inv):
            $payMatches = array_filter($payments ?? [], fn($p) => isset($p['invoice_id']) && (int)$p['invoice_id'] === (int)$inv['id']);
            $pay        = $payMatches ? reset($payMatches) : null;
            $paid       = (float)($pay['paid_amount'] ?? 0);
            $bd         = $breakdowns[(int)$inv['id']] ?? [];
            $cash       = (float)($bd['cash']   ?? 0);
            $cred       = (float)($bd['credit'] ?? 0);
            $chk        = (float)($bd['check']  ?? 0);
            $refMatches = array_filter($refunds ?? [], fn($r) => isset($r['invoice_id']) && (int)$r['invoice_id'] === (int)$inv['id']);
            $ref        = $refMatches ? reset($refMatches) : null;
            $refunded   = (float)($ref['total_refunded'] ?? 0);
            $discMatches = array_filter($discounts ?? [], fn($d) => isset($d['invoice_id']) && (int)$d['invoice_id'] === (int)$inv['id']);
            $disc        = $discMatches ? reset($discMatches) : null;
            $discounted  = (float)($disc['discount_amount'] ?? 0);
            $totalAmount = (float)($inv['total_amount'] ?? 0);
            // Recompute balance for display: total - discount - paid - refunded
            $balanceCalc = (float)$totalAmount - (float)$discounted - (float)$paid - (float)$refunded;
            if ($balanceCalc < 0) $balanceCalc = 0.0;
            $balance     = $balanceCalc;
            if ($balanceCalc <= 0.00001) {
              $status = 'Paid';
              $statusClass = 'bg-success';
            } elseif ($paid > 0.00001) {
              $status = 'Partially Paid';
              $statusClass = 'bg-warning text-dark';
            } else {
              $status = 'Unpaid';
              $statusClass = 'bg-danger';
            }
          ?>
            <tr>
              <td><?= esc($inv['invoice_number']) ?></td>
              <td>
                <?php $pid = (int)($inv['parent_id'] ?? 0); ?>
                <?php if ($pid): ?>
                  <a href="<?= site_url('family') ?>?guardian_id=<?= (int)$pid ?>" class="text-decoration-none" data-family-guardian-id="<?= $pid ?>">
                    <?= esc($inv['parent_name']) ?>
                  </a>
                <?php else: ?>
                  <?= esc($inv['parent_name']) ?>
                <?php endif; ?>
              </td>
              <td><?= esc($inv['school_year']) ?></td>
              <td data-order="<?= $totalAmount ?>">$<?= number_format($totalAmount, 2) ?></td>
              <td data-order="<?= $paid ?>">$<?= number_format($paid, 2) ?></td>
              <td data-order="<?= $cash ?>">$<?= number_format($cash, 2) ?></td>
              <td data-order="<?= $cred ?>">$<?= number_format($cred, 2) ?></td>
              <td data-order="<?= $chk ?>">$<?= number_format($chk, 2) ?></td>
              <td data-order="<?= $balance ?>">$<?= number_format($balance, 2) ?></td>
              <td data-order="<?= $refunded ?>">$<?= number_format($refunded, 2) ?></td>
              <td data-order="<?= $discounted ?>">$<?= number_format($discounted, 2) ?></td>
              <td data-order="<?= esc($status) ?>"><span class="badge <?= esc($statusClass) ?>"><?= esc($status) ?></span></td>
            </tr>
          <?php endforeach; ?>
        <?php endif; ?>
      </tbody>
    </table>
  </div>

  <h3 class="mt-5">Payments by Method (Filtered)</h3>
  <div class="table-responsive">
    <table id="summaryTable" class="table table-bordered align-middle w-100">
      <thead>
        <tr>
          <th>Cash</th>
          <th>Credit</th>
          <th>Check</th>
          <th>Grand Total (All Payments)</th>
        </tr>
      </thead>
      <tbody>
        <?php
        $pt         = $paymentTotals ?? [];
        $totalCash   = (float)($pt['total_cash']   ?? 0);
        $totalCredit = (float)($pt['total_credit'] ?? 0);
        $totalCheck  = (float)($pt['total_check']  ?? 0);
        $grandTotal  = (float)($pt['total_all']    ?? ($totalCash + $totalCredit + $totalCheck));
        ?>
        <tr>
          <td class="sum-cash" data-order="<?= $totalCash ?>">$<?= number_format($totalCash, 2) ?></td>
          <td class="sum-credit" data-order="<?= $totalCredit ?>">$<?= number_format($totalCredit, 2) ?></td>
          <td class="sum-check" data-order="<?= $totalCheck ?>">$<?= number_format($totalCheck, 2) ?></td>
          <td class="sum-all fw-bold" data-order="<?= $grandTotal ?>">$<?= number_format($grandTotal, 2) ?></td>
        </tr>
      </tbody>
    </table>
  </div>

  <h3 class="mt-5">Expenses Summary</h3>
  <div class="table-responsive">
    <table id="expensesTable" class="table table-bordered align-middle w-100">
      <thead>
        <tr>
          <th>Category</th>
          <th>Total Amount</th>
        </tr>
      </thead>
      <tbody>
        <?php if (!empty($expenses ?? [])): foreach ($expenses as $exp): $expAmt = (float)($exp['total_amount'] ?? 0); ?>
            <tr>
              <td><?= esc($exp['category']) ?></td>
              <td data-order="<?= $expAmt ?>">$<?= number_format($expAmt, 2) ?></td>
            </tr>
        <?php endforeach;
        endif; ?>
      </tbody>
    </table>
  </div>

  <h3 class="mt-5">Reimbursements Summary</h3>
  <div class="table-responsive">
    <table id="reimbTable" class="table table-bordered align-middle w-100">
      <thead>
        <tr>
          <th>Status</th>
          <th>Total Amount</th>
        </tr>
      </thead>
      <tbody>
        <?php if (!empty($reimbursements ?? [])): foreach ($reimbursements as $r): $rAmt = (float)($r['total_amount'] ?? 0); ?>
            <tr>
              <td><?= esc($r['status']) ?></td>
              <td data-order="<?= $rAmt ?>">$<?= number_format($rAmt, 2) ?></td>
            </tr>
        <?php endforeach;
        endif; ?>
      </tbody>
    </table>
  </div>


</div>
<?= $this->endSection() ?>

<?= $this->section('scripts') ?>
<script>
  document.addEventListener('DOMContentLoaded', function() {
    // Fetch data from API and hydrate tables
    function fmt(n) {
      return '$' + Number(n || 0).toFixed(2);
    }

    function formatDateTime(value) {
      const date = value instanceof Date ? value : new Date(value);
      if (Number.isNaN(date.valueOf())) {
        return '';
      }
      const pad = (num) => String(num).padStart(2, '0');
      const datePart = `${pad(date.getMonth() + 1)}-${pad(date.getDate())}-${date.getFullYear()}`;
      const timePart = `${pad(date.getHours())}:${pad(date.getMinutes())}`;
      return `${datePart} ${timePart}`;
    }

    function qs(sel) {
      return document.querySelector(sel);
    }

    function qsa(sel) {
      return Array.from(document.querySelectorAll(sel));
    }

    // --- DataTables FixedHeader support (lazy-load assets if missing) ---
    function loadScript(src, id) {
      return new Promise((resolve, reject) => {
        if (id && document.getElementById(id)) return resolve();
        const s = document.createElement('script');
        if (id) s.id = id;
        s.src = src;
        s.onload = resolve;
        s.onerror = reject;
        document.head.appendChild(s);
      });
    }

    function loadCss(href, id) {
      return new Promise((resolve) => {
        if (id && document.getElementById(id)) return resolve();
        const l = document.createElement('link');
        if (id) l.id = id;
        l.rel = 'stylesheet';
        l.href = href;
        l.onload = resolve;
        document.head.appendChild(l);
      });
    }

    function ensureDtFixedHeaderAssets() {
      const tasks = [];
      const needJQ = !window.jQuery;
      if (needJQ) tasks.push(loadScript('https://cdn.jsdelivr.net/npm/jquery@3.7.1/dist/jquery.min.js', 'jq-core'));

      // Core DataTables + Bootstrap 5 integration
      if (!window.jQuery || !jQuery.fn || !jQuery.fn.DataTable) {
        tasks.push(loadScript('https://cdn.jsdelivr.net/npm/datatables.net@1.13.10/js/jquery.dataTables.min.js', 'dt-core'));
        tasks.push(loadScript('https://cdn.jsdelivr.net/npm/datatables.net-bs5@1.13.10/js/dataTables.bootstrap5.min.js', 'dt-bs5'));
        // base CSS if not already present (id optional)
        tasks.push(loadCss('https://cdn.jsdelivr.net/npm/datatables.net-bs5@1.13.10/css/dataTables.bootstrap5.min.css', 'dt-bs5-css'));
      }

      // FixedHeader extension (JS + Bootstrap 5 CSS)
      // Only load if plugin missing
      const needFH = !(window.jQuery && jQuery.fn && jQuery.fn.dataTable && jQuery.fn.dataTable.FixedHeader);
      if (needFH) {
        tasks.push(loadScript('https://cdn.jsdelivr.net/npm/datatables.net-fixedheader@3.4.0/js/dataTables.fixedHeader.min.js', 'dt-fixedheader'));
        tasks.push(loadCss('https://cdn.jsdelivr.net/npm/datatables.net-fixedheader-bs5@3.4.0/css/fixedHeader.bootstrap5.min.css', 'dt-fixedheader-css'));
      }

      return Promise.all(tasks).catch(() => {});
    }

    // Compute offset if a fixed/sticky top navbar exists to prevent overlap
  function getFixedHeaderOffset() {
    let total = 0;
    const stack = [];
    const header = document.querySelector('header.navbar.sticky-top, header.navbar.fixed-top');
    if (header) stack.push(header);
    const mgmt = document.getElementById('navbarManagement');
    if (mgmt && (mgmt.classList.contains('sticky-top') || mgmt.classList.contains('fixed-top'))) stack.push(mgmt);
    // Include any other sticky/fixed navbars at top (avoid duplicates)
    document.querySelectorAll('.navbar.sticky-top, .navbar.fixed-top').forEach(el => { if (!stack.includes(el)) stack.push(el); });
    stack.forEach(el => { const h = el.offsetHeight || el.getBoundingClientRect().height || 0; total += Math.max(0, Math.round(h)); });
    return total;
  }

    function buildParams() {
      const p = new URLSearchParams();
      const f = document.getElementById('financialFilterForm');
      if (f) {
        new FormData(f).forEach((v, k) => p.set(k, v));
      }
      return p.toString();
    }

    function updateActionLinks() {
      const params = buildParams();
      const q = params ? ('?' + params) : '';
      const csvBase = '<?= base_url('payment/download_csv') ?>';
      const sumBase = '<?= base_url('financial-report/financialReportSummary') ?>';
      const dl = document.getElementById('downloadCsvLink');
      const sr = document.getElementById('summaryReportLink');
      if (dl) dl.href = csvBase + q;
      if (sr) sr.href = sumBase + q;
    }

    let __reqSeq = 0;

    function hydrate() {
      const mySeq = ++__reqSeq;
      const baseUrl = '<?= site_url('payment/financial_report') ?>';
      const params = buildParams();
      const url = baseUrl + (params ? ('?' + params + '&format=json') : '?format=json');
      // Show loading
      const $loading = document.getElementById('reportLoading');
      const $error = document.getElementById('reportError');
      $error.classList.add('d-none');
      $loading.classList.remove('d-none');

      fetch(url, {
          headers: {
            'Accept': 'application/json'
          }
        })
        .then(r => r.json())
        .then(data => {
          if (mySeq !== __reqSeq) return; // stale response; ignore
          if (!data || data.ok !== true) throw new Error('bad response');
          // Destroy DataTables BEFORE changing DOM to avoid conflicts
          if (window.jQuery && jQuery.fn && jQuery.fn.DataTable) {
            ['#invoicesTable', '#summaryTable', '#expensesTable', '#reimbTable'].forEach(sel => {
              if (jQuery.fn.DataTable.isDataTable(sel)) {
                jQuery(sel).DataTable().clear().destroy();
              }
            });
          }

          const tbody = qs('#invoicesTable tbody');
          tbody.innerHTML = '';

          const paymentsMap = {};
          (data.payments || []).forEach(p => {
            paymentsMap[p.invoice_id] = Number(p.paid_amount || 0);
          });
          const refundsMap = {};
          (data.refunds || []).forEach(r => {
            refundsMap[r.invoice_id] = Number(r.total_refunded || 0);
          });
          const discountsMap = {};
          (data.discounts || []).forEach(d => {
            discountsMap[d.invoice_id] = Number(d.discount_amount || 0);
          });
          const breakdown = data.paymentBreakdown || {};

          (data.invoices || []).forEach(inv => {
            const paid = paymentsMap[inv.id] || 0;
            const bd = breakdown[inv.id] || {};
            const cash = Number(bd.cash || 0),
              credit = Number(bd.credit || 0),
              check = Number(bd.check || 0);
            const refunded = refundsMap[inv.id] || 0;
            const discounted = discountsMap[inv.id] || 0;
            const total = Number(inv.total_amount || 0);
            let balance = total - paid - discounted - refunded;
            if (!Number.isFinite(balance) || balance < 0) balance = 0;
            let status = 'Unpaid';
            let statusClass = 'bg-danger';
            if (balance <= 0.00001) {
              status = 'Paid';
              statusClass = 'bg-success';
            } else if (paid > 0.00001) {
              status = 'Partially Paid';
              statusClass = 'bg-warning text-dark';
            }
            const tr = document.createElement('tr');
            const parentHtml = (inv.parent_id && Number(inv.parent_id) > 0) ?
              `<a href="<?= site_url('family') ?>?guardian_id=${encodeURIComponent(inv.parent_id)}" class="text-decoration-none" data-family-guardian-id="${inv.parent_id}">${inv.parent_name||''}</a>` :
              (inv.parent_name || '');
            tr.innerHTML = `
            <td>${inv.invoice_number||''}</td>
            <td>${parentHtml}</td>
            <td>${inv.school_year||''}</td>
            <td data-order="${total}">${fmt(total)}</td>
            <td data-order="${paid}">${fmt(paid)}</td>
            <td data-order="${cash}">${fmt(cash)}</td>
            <td data-order="${credit}">${fmt(credit)}</td>
            <td data-order="${check}">${fmt(check)}</td>
            <td data-order="${balance}">${fmt(balance)}</td>
            <td data-order="${refunded}">${fmt(refunded)}</td>
            <td data-order="${discounted}">${fmt(discounted)}</td>
            <td data-order="${status}"><span class="badge ${statusClass}">${status}</span></td>`;
            tbody.appendChild(tr);
          });

          // Summary totals: rebuild row to be safe after DT destroy
          const pt = data.paymentTotals || {};
          const tCash = Number(pt.total_cash || 0);
          const tCred = Number(pt.total_credit || 0);
          const tChk = Number(pt.total_check || 0);
          const tAll = Number(pt.total_all || (tCash + tCred + tChk));
          const sBody = qs('#summaryTable tbody');
          if (sBody) {
            sBody.innerHTML = `<tr>
            <td class="sum-cash" data-order="${tCash}">${fmt(tCash)}</td>
            <td class="sum-credit" data-order="${tCred}">${fmt(tCred)}</td>
            <td class="sum-check" data-order="${tChk}">${fmt(tChk)}</td>
            <td class="sum-all fw-bold" data-order="${tAll}">${fmt(tAll)}</td>
          </tr>`;
          }

          // Expenses
          const expTbody = qs('#expensesTable tbody');
          expTbody.innerHTML = '';
          (data.expenses || []).forEach(exp => {
            const amt = Number(exp.total_amount || 0);
            const tr = document.createElement('tr');
            tr.innerHTML = `<td>${exp.category||''}</td><td data-order="${amt}">${fmt(amt)}</td>`;
            expTbody.appendChild(tr);
          });

          // Reimbursements
          const rTbody = qs('#reimbTable tbody');
          rTbody.innerHTML = '';
          (data.reimbursements || []).forEach(r => {
            const amt = Number(r.total_amount || 0);
            const tr = document.createElement('tr');
            tr.innerHTML = `<td>${r.status||''}</td><td data-order="${amt}">${fmt(amt)}</td>`;
            rTbody.appendChild(tr);
          });

          // Re-init with options (FixedHeader enabled)
          initDT('#invoicesTable', {
            order: [
              [2, 'asc'],
              [0, 'asc']
            ],
            columnDefs: [{
                targets: 2,
                visible: false
              } // hide School Year column
            ],
            fixedHeader: {
              header: true,
              headerOffset: getFixedHeaderOffset()
            }
          });
          initDT('#summaryTable', {
            paging: false,
            searching: false,
            info: false,
            ordering: true
          });
          initDT('#expensesTable', {
            order: [
              [1, 'desc']
            ]
          });
          initDT('#reimbTable', {
            order: [
              [1, 'desc']
            ]
          });

          // Update last refreshed timestamp
          const lastRef = document.getElementById('reportLastRef');
          if (lastRef) lastRef.textContent = 'Last refreshed: ' + formatDateTime(new Date());

          $loading.classList.add('d-none');
          $error.classList.add('d-none');
          // Keep action links synced with current filters
          updateActionLinks();
        })
        .catch(() => {
          if (mySeq !== __reqSeq) return; // stale error; ignore
          document.getElementById('reportLoading').classList.add('d-none');
          document.getElementById('reportError').classList.remove('d-none');
        });
    }

    // Helper to (re)initialize safely
    function initDT(selector, options) {
      if (!window.jQuery || !jQuery.fn || !jQuery.fn.DataTable) return;

      if (jQuery.fn.DataTable.isDataTable(selector)) {
        jQuery(selector).DataTable().clear().destroy();
      }
      return jQuery(selector).DataTable(Object.assign({
        // Defaults that prevent “stuck at 10/page 1”
        stateSave: false, // don't restore old 10/page
        paging: true,
        ordering: true,
        info: true,
        searching: true,
        pageLength: 100,
        lengthMenu: [
          [10, 25, 50, 100, -1],
          [10, 25, 50, 100, 'All']
        ],
        pagingType: 'full_numbers',
        dom: 'lfrtip'
      }, options || {}));
    }

    function initializeTablesAndHandlers() {
      // Be noisy if DT errors happen
      if (window.jQuery && jQuery.fn && jQuery.fn.dataTable) {
        jQuery.fn.dataTable.ext.errMode = 'console';
      }

      // Main invoices table with FixedHeader
      initDT('#invoicesTable', {
        order: [
          [2, 'asc'],
          [0, 'asc']
        ], // School Year (hidden), then Invoice #
        columnDefs: [{
            targets: 2,
            visible: false
          } // hide School Year column
        ],
        fixedHeader: {
          header: true,
          headerOffset: getFixedHeaderOffset()
        }
      });

      // Payments summary (1 row; keep ordering for convenience)
      initDT('#summaryTable', {
        paging: false,
        searching: false,
        info: false,
        ordering: true
      });

      // Expenses & Reimbursements
      initDT('#expensesTable', {
        order: [
          [1, 'desc']
        ]
      });
      initDT('#reimbTable', {
        order: [
          [1, 'desc']
        ]
      });

      // Initial hydrate and on form submit
      hydrate();
      updateActionLinks();
      const filterForm = document.getElementById('financialFilterForm');
      if (filterForm) {
        filterForm.addEventListener('submit', function(e) {
          e.preventDefault();
          hydrate();
          updateActionLinks();
        });
        filterForm.addEventListener('change', function() {
          updateActionLinks();
        });
      }

      // Auto hydrate on year change
      const yearSel = document.querySelector('#financialFilterForm select[name="school_year"]');
      if (yearSel) yearSel.addEventListener('change', function() {
        hydrate();
        updateActionLinks();
      });

      // Retry handler on error banner
      const retry = document.getElementById('reportRetry');
      if (retry) retry.addEventListener('click', function(ev) {
        ev.preventDefault();
        hydrate();
      });
    }

    // Ensure DT core + FixedHeader then init
    ensureDtFixedHeaderAssets().then(initializeTablesAndHandlers, initializeTablesAndHandlers);

    // OPTIONAL: one-time clear of any old saved state from previous versions
    // Uncomment once, reload, then comment it back.
    // if (window.localStorage) {
    //   Object.keys(localStorage)
    //     .filter(k => k.startsWith('DataTables_'))
    //     .forEach(k => localStorage.removeItem(k));
    // }
  });
</script>


<?= $this->endSection() ?>
