registerPage('expiry', async (content, params = {}) => {
  document.getElementById('topbar-title').textContent = 'Expiry Report';

  let clients = [];
  let allRows = [];
  let grouped = {};


  function daysBadge(d) {
    const n = parseInt(d);
    if (isNaN(n)) return `<span class="badge badge-gray">Unknown</span>`;
    const abs = Math.abs(n);
    const months = Math.round(abs / 30.44);
    const label = abs < 30 ? `${abs}d` : `${months}mo`;
    if (n < 0) return `<span class="badge badge-danger">Expired ${label} ago</span>`;
    if (n === 0) return `<span class="badge badge-danger">Expires today</span>`;
    if (n <= 30) return `<span class="badge badge-danger">${label} left</span>`;
    if (n <= 60) return `<span class="badge badge-warning">${label} left</span>`;
    if (n <= 180) return `<span class="badge badge-info">${label} left</span>`;
    return `<span class="badge badge-success">${label} left</span>`;
  }

  function renderGroups(grp) {
    const container = document.getElementById('expiry-table');
    if (!container) return;
    if (!Object.keys(grp).length) { container.innerHTML = emptyHTML('No records match your filters'); return; }

    container.innerHTML = Object.entries(grp).map(([client, rows], ci) => `
      <div class="card" style="margin-bottom:10px" id="expgrp-${ci}">
        <div onclick="toggleExpGrp(${ci})"
             style="display:flex;align-items:center;gap:10px;padding:12px 18px;cursor:pointer;user-select:none">
          <svg id="exp-arrow-${ci}" style="transition:transform .2s;flex-shrink:0"
               width="14" height="14" fill="none" viewBox="0 0 24 24" stroke="currentColor">
            <path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M19 9l-7 7-7-7"/>
          </svg>
          <span style="font-size:13px;font-weight:500;flex:1">${client}</span>
          <span class="badge badge-gray">${rows.length} records</span>
          ${rows.filter(r => parseInt(r.days_left) < 0).length
        ? `<span class="badge badge-danger">${rows.filter(r => parseInt(r.days_left) < 0).length} expired</span>` : ''}
          ${rows.filter(r => { const n = parseInt(r.days_left); return n >= 0 && n <= 30; }).length
        ? `<span class="badge badge-warning">${rows.filter(r => { const n = parseInt(r.days_left); return n >= 0 && n <= 30; }).length} critical</span>` : ''}
        </div>
        <div id="exp-body-${ci}" style="border-top:1px solid var(--border)">
          <div class="table-wrap">
            <table style="font-size:12px">
              <thead><tr>
                <th>Type</th><th>Name</th><th>Surname</th><th>ID/Passport</th>
                <th>Assessment / Test</th><th>Done Date</th><th>Expiry Date</th><th>Status</th>
              </tr></thead>
              <tbody>${rows.map(r => `<tr>
                <td><span class="badge ${r.rec_type === 'assessment' ? 'badge-info' : 'badge-orange'}" style="font-size:10px">${r.rec_type === 'assessment' ? 'Assessment' : 'Test'}</span></td>
                <td class="fw-500">${r.client_employees_name}</td>
                <td>${r.surname}</td>
                <td class="mono">${r.i_doc_passport || '—'}</td>
                <td>${r.name}</td>
                <td>${(r.done_date || '').slice(0, 10)}</td>
                <td>${r.expiry_date || '—'}</td>
                <td>${daysBadge(r.days_left)}</td>
              </tr>`).join('')}
              </tbody>
            </table>
          </div>
        </div>
      </div>`).join('');
  }

  window.toggleExpGrp = (ci) => {
    const body = document.getElementById(`exp-body-${ci}`);
    const arrow = document.getElementById(`exp-arrow-${ci}`);
    if (!body) return;
    const open = body.style.display !== 'none';
    body.style.display = open ? 'none' : '';
    if (arrow) arrow.style.transform = open ? 'rotate(-90deg)' : '';
  };

  async function load() {
    const clientId = document.getElementById('expiry-client').value || '';
    const search = document.getElementById('expiry-search').value || '';
    const days = document.getElementById('expiry-days').value || '90';
    document.getElementById('expiry-table').innerHTML = loadingHTML();
    const r = await api('expiry.php', { action: 'list', clients_id: clientId, search, days_warn: days });
    if (!r.success) { document.getElementById('expiry-table').innerHTML = emptyHTML('Error: ' + r.error); return; }
    allRows = r.rows;
    grouped = r.grouped;
    document.getElementById('expiry-count').textContent = r.total + ' records';
    renderGroups(grouped);
  }

  content.innerHTML = `
    <div class="card" style="margin-bottom:16px">
      <div class="card-body" style="padding:14px 18px">
        <div style="display:flex;gap:10px;flex-wrap:wrap;align-items:center">
          <div class="search-input-wrap" style="flex:1;min-width:200px">
            <svg width="16" height="16" fill="none" viewBox="0 0 24 24" stroke="currentColor"><circle cx="11" cy="11" r="8"/><path d="m21 21-4.35-4.35" stroke-linecap="round"/></svg>
            <input type="text" id="expiry-search" placeholder="Search name, surname, ID, assessment…">
          </div>
          <select id="expiry-client" style="min-width:180px"><option value="">All Clients</option></select>
          <select id="expiry-days" style="min-width:160px">
            <option value="30">Expiring in 30 days</option>
            <option value="60">Expiring in 60 days</option>
            <option value="90" selected>Expiring in 90 days</option>
            <option value="180">Expiring in 180 days</option>
            <option value="365">Expiring in 1 year</option>
            <option value="9999">All (inc. expired)</option>
          </select>
          <span id="expiry-count" class="text-muted text-sm"></span>
          <button class="btn btn-secondary" onclick="expiryExcelDl()">⬇ Excel</button>
          <button class="btn btn-secondary" onclick="expiryPDFDl()">⬇ PDF</button>
        </div>
      </div>
    </div>
    <div id="expiry-table">${loadingHTML()}</div>`;

  // Load clients for filter
  const cr = await api('clients.php', { action: 'list' });
  if (cr.success) {
    const sel = document.getElementById('expiry-client');
    cr.clients.forEach(c => sel.innerHTML += `<option value="${c.record_id}">${c.clients_name}</option>`);
  }

  let debounce;
  document.getElementById('expiry-search').addEventListener('input', () => { clearTimeout(debounce); debounce = setTimeout(load, 350); });
  document.getElementById('expiry-client').addEventListener('change', load);
  document.getElementById('expiry-days').addEventListener('change', load);
  load();

  // ── Excel export ─────────────────────────────────────────────────────────
  window.expiryExcelDl = () => {
    if (!allRows.length) { toast('No data to export', 'error'); return; }
    const headers = ['Type', 'Name', 'Surname', 'ID/Passport', 'Client', 'Assessment/Test', 'Done Date', 'Expiry Date', 'Days Left'];
    const rows = allRows.map(r => [
      r.rec_type, r.client_employees_name, r.surname, r.i_doc_passport || '',
      r.clients_name || '', r.name, (r.done_date || '').slice(0, 10),
      r.expiry_date || '', r.days_left
    ]);
    const wb = XLSX.utils.book_new();
    const ws = XLSX.utils.aoa_to_sheet([headers, ...rows]);
    XLSX.utils.book_append_sheet(wb, ws, 'Expiry');
    XLSX.writeFile(wb, 'expiry_report_' + new Date().toISOString().slice(0, 10) + '.xlsx');
  };

  // ── PDF export ────────────────────────────────────────────────────────────
  window.expiryPDFDl = () => {
    if (!Object.keys(grouped).length) { toast('No data to export', 'error'); return; }


    function dayLabel(d) {
      const n = parseInt(d);
      if (isNaN(n)) return '';
      const abs = Math.abs(n);
      const months = Math.round(abs / 30.44);
      const label = abs < 30 ? `${abs}d` : `${months}mo`;
      if (n < 0) return `<span style="color:#dc2626;font-weight:600">Expired ${label} ago</span>`;
      if (n <= 30) return `<span style="color:#dc2626;font-weight:600">${label} left</span>`;
      if (n <= 60) return `<span style="color:#d97706;font-weight:600">${label} left</span>`;
      return `<span style="color:#0284c7">${label} left</span>`;
    }

    const date = new Date().toLocaleDateString('en-ZA', { day: 'numeric', month: 'short', year: 'numeric' });
    const groupBlocks = Object.entries(grouped).map(([client, rows]) => `
      <h3 style="margin:16px 0 6px;font-size:12px;text-transform:uppercase;color:#64748b;letter-spacing:.06em">${client} (${rows.length})</h3>
      <table style="width:100%;border-collapse:collapse;font-size:10px;margin-bottom:8px">
        <thead><tr style="background:#0d1b2a;color:#fff">
          <th style="padding:5px 7px">Type</th><th style="padding:5px 7px">Name</th>
          <th style="padding:5px 7px">ID</th><th style="padding:5px 7px">Assessment/Test</th>
          <th style="padding:5px 7px">Done</th><th style="padding:5px 7px">Expires</th>
          <th style="padding:5px 7px">Status</th>
        </tr></thead>
        <tbody>${rows.map((r, i) => `<tr style="${i % 2 ? 'background:#f8fafc' : ''}">
          <td style="padding:4px 7px;border-bottom:1px solid #e2e8f0">${r.rec_type === 'assessment' ? 'Assessment' : 'Test'}</td>
          <td style="padding:4px 7px;border-bottom:1px solid #e2e8f0">${r.client_employees_name} ${r.surname}</td>
          <td style="padding:4px 7px;border-bottom:1px solid #e2e8f0;font-family:monospace">${r.i_doc_passport || ''}</td>
          <td style="padding:4px 7px;border-bottom:1px solid #e2e8f0">${r.name}</td>
          <td style="padding:4px 7px;border-bottom:1px solid #e2e8f0">${(r.done_date || '').slice(0, 10)}</td>
          <td style="padding:4px 7px;border-bottom:1px solid #e2e8f0">${r.expiry_date || ''}</td>
          <td style="padding:4px 7px;border-bottom:1px solid #e2e8f0">${dayLabel(r.days_left)}</td>
        </tr>`).join('')}</tbody>
      </table>`).join('');

    const html = `<!DOCTYPE html><html><head><meta charset="UTF-8"><title>Expiry Report</title>
    <style>*{box-sizing:border-box;margin:0;padding:0}body{font-family:Arial,sans-serif;font-size:11px;color:#1a2332;padding:20px}
    .hdr{display:flex;align-items:center;justify-content:space-between;margin-bottom:16px;padding-bottom:10px;border-bottom:3px solid #e85d04}
    .brand{display:flex;align-items:center;gap:10px}.logo{width:36px;height:36px;background:#e85d04;border-radius:6px;color:#fff;font-weight:700;font-size:13px;display:flex;align-items:center;justify-content:center}
    .bname{font-size:16px;font-weight:700}.bsub{font-size:10px;color:#64748b}
    .meta{text-align:right;font-size:10px;color:#64748b}.meta b{color:#1a2332}
    .foot{margin-top:14px;display:flex;justify-content:space-between;color:#94a3b8;font-size:9px;border-top:1px solid #e2e8f0;padding-top:6px}
    @media print{@page{margin:1cm}body{padding:0}}</style>
    </head><body>
    <div class="hdr">
      <div class="brand"><div class="logo">SS</div>
        <div><div class="bname">SafeSure</div><div class="bsub">Expiry Report</div></div>
      </div>
      <div class="meta"><div>Date: <b>${date}</b></div><div>Total: <b>${allRows.length} records</b></div></div>
    </div>
    ${groupBlocks}
    <div class="foot"><span>SafeSure Competency Assessment System</span><span>Confidential — Expiry Report ${date}</span></div>
    <script>window.onload=function(){window.print();}<\/script>
    </body></html>`;
    const w = window.open('', '_blank'); w.document.write(html); w.document.close();
  };
});