// ════════════════════════════════════════════════════════════════════════
// PropMystro · G4 · pm-d-exports.jsx  (mirror of pm-settings.jsx PMExportCentre)
// Export centre — pull the data out for the accountant, the bank, or your own
// records. Everything generates client-side in the browser from RLS-scoped
// reads. CSV cells are defanged against spreadsheet formula-injection.
// Gated by features.exports ('csv' on Professional, 'csv+api' on Portfolio);
// reads stay open even for read_only / canceled accounts. → window.PMExportCentre
// ════════════════════════════════════════════════════════════════════════
(function () {
  const { useState, useEffect, useCallback } = React;

  // ── download helpers ─────────────────────────────────────────────────
  function downloadCsv(rows, filename) {
    const esc = (v) => {
      let s = v == null ? '' : String(v);
      // SECURITY: defang CSV formula-injection. A cell beginning with = + - @
      // (or tab / CR) executes as a formula when opened in Excel / Sheets.
      // Prefix an apostrophe — but leave plain numbers (e.g. -500.00) intact.
      if (/^[=+\-@\t\r]/.test(s) && !/^-?\d*\.?\d+$/.test(s)) s = "'" + s;
      return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
    };
    blobDown(rows.map(r => r.map(esc).join(',')).join('\r\n'), 'text/csv;charset=utf-8', filename);
  }
  function downloadJson(obj, filename) {
    blobDown(JSON.stringify(obj, null, 2), 'application/json', filename);
  }
  function blobDown(content, mime, filename) {
    try {
      const url = URL.createObjectURL(new Blob([content], { type: mime }));
      const a = document.createElement('a');
      a.href = url; a.download = filename; document.body.appendChild(a); a.click();
      setTimeout(() => { document.body.removeChild(a); URL.revokeObjectURL(url); }, 100);
    } catch (e) { alert('Export failed: ' + e.message); }
  }

  const todayISO = () => new Date().toISOString().slice(0, 10);
  const daysU = (d) => d ? Math.ceil((new Date(d) - new Date()) / 86400000) : null;
  // current UK tax year bounds (6 Apr – 5 Apr)
  function taxYear() {
    const now = new Date(); const y = now.getFullYear();
    const start = new Date(y, 3, 6) <= now ? y : y - 1;
    return { label: start + '-' + String(start + 1).slice(2), start: start + '-04-06', end: (start + 1) + '-04-05' };
  }

  const CERT_COLS = [
    { v: 'gas_safety', s: 'Gas Safety' }, { v: 'eicr', s: 'EICR' }, { v: 'epc', s: 'EPC' },
    { v: 'deposit_protection', s: 'Deposit' }, { v: 'right_to_rent', s: 'Right to Rent' },
    { v: 'insurance', s: 'Insurance' }, { v: 'hmo_licence', s: 'HMO licence', hmoOnly: true },
  ];

  function Spin() { return <span className="spin dark" />; }

  function Locked({ openBilling }) {
    return <div className="card card-pad"><div className="locked">
      <div className="lock-ico">↓</div>
      <h3>Exports are a Professional feature</h3>
      <p>Pull the full ledger, compliance matrix, bank reconciliation and per-property finance out as Excel-ready CSV — plus a one-page portfolio PDF.</p>
      <button className="btn btn-primary btn-sm" style={{ width: 'auto' }} onClick={openBilling}>Upgrade to Professional</button>
      <div className="lock-foot">Your data stays exactly as it is — upgrading just unlocks the module.</div>
    </div></div>;
  }

  function ExportCentre({ sb, account, openBilling, toast }) {
    const entitled = !!(account.features && account.features.exports);
    const hasTax = account.features && account.features.tax === true;
    const [d, setD] = useState(null);

    const load = useCallback(async () => {
      if (!entitled) return;
      const [p, o, po, c, pay, exp, br, ty, tn, tax] = await Promise.all([
        sb.from('properties').select('id,address,postcode,borough,type,rent,primary_owner_id').order('address', { ascending: true }),
        sb.from('owners').select('id,name,email'),
        sb.from('property_owners').select('property_id,owner_id'),
        sb.from('certificates').select('property_id,type,expiry_date'),
        sb.from('payments').select('id,tenancy_id,property_id,date,amount,kind,method,ref,note'),
        sb.from('expenses').select('id,property_id,date,category,sa105_box,amount,vendor,description,source'),
        sb.from('bank_rows').select('id,date,description,amount,status,match_type,match_label'),
        sb.from('tenancies').select('id,property_id,lead_tenant_id,status,rent_pcm,start_date'),
        sb.from('tenants').select('id,full_name'),
        sb.from('tax_submissions').select('*'),
      ]);
      setD({
        props: p.data || [], owners: o.data || [], po: po.data || [], certs: c.data || [],
        payments: pay.data || [], expenses: exp.data || [], bankRows: br.data || [],
        tenancies: ty.data || [], tenants: tn.data || [], tax: tax.data || [],
      });
    }, [sb, entitled]);
    useEffect(() => { load(); }, [load]);

    if (!entitled) return <React.Fragment>
      <div className="pagehead"><h1>Export centre</h1><p>Pull the data out whenever you need it — for the accountant, the bank, or your own records.</p></div>
      <Locked openBilling={openBilling} />
    </React.Fragment>;
    if (d === null) return <div className="card card-pad"><Spin /></div>;

    const propById = Object.fromEntries(d.props.map(p => [p.id, p]));
    const ownerById = Object.fromEntries(d.owners.map(o => [o.id, o]));
    const tenById = Object.fromEntries(d.tenants.map(t => [t.id, t]));
    const tyById = Object.fromEntries(d.tenancies.map(t => [t.id, t]));
    const ownerName = (p) => p && p.primary_owner_id && ownerById[p.primary_owner_id] ? ownerById[p.primary_owner_id].name : '';

    // ── builders ─────────────────────────────────────────────────────
    const ledgerCsv = () => {
      const rows = [['type', 'date', 'property', 'owner', 'tenant_or_vendor', 'category_or_method', 'sa105_box', 'amount']];
      d.payments.forEach(pm => {
        const ty = tyById[pm.tenancy_id];
        const prop = propById[pm.property_id || (ty && ty.property_id)] || {};
        const lead = ty && tenById[ty.lead_tenant_id];
        rows.push([pm.kind || 'payment', pm.date, prop.address || '', ownerName(prop), lead ? lead.full_name : '', pm.method || '', '', Number(pm.amount) || 0]);
      });
      d.expenses.forEach(e => {
        const prop = propById[e.property_id] || {};
        rows.push(['expense', e.date, prop.address || '', ownerName(prop), e.vendor || '', e.category || '', e.sa105_box || '', -(Number(e.amount) || 0)]);
      });
      const head = rows.shift();
      rows.sort((a, b) => String(a[1]).localeCompare(String(b[1])));
      downloadCsv([head].concat(rows), 'ledger-' + todayISO() + '.csv');
    };

    const complianceCsv = () => {
      const rows = [['property', 'postcode', 'borough', 'owner'].concat(CERT_COLS.map(c => c.s))];
      const latest = (pid, type) => d.certs.filter(c => c.property_id === pid && c.type === type).sort((a, b) => new Date(b.expiry_date || 0) - new Date(a.expiry_date || 0))[0];
      d.props.forEach(p => {
        const row = [p.address, p.postcode, p.borough || '', ownerName(p)];
        CERT_COLS.forEach(col => {
          if (col.hmoOnly && p.type !== 'hmo') { row.push('n/a'); return; }
          const cert = latest(p.id, col.v);
          if (!cert) { row.push('MISSING'); return; }
          if (!cert.expiry_date) { row.push('on file'); return; }
          const dd = daysU(cert.expiry_date);
          row.push(dd < 0 ? 'OVERDUE ' + cert.expiry_date : 'exp ' + cert.expiry_date);
        });
        rows.push(row);
      });
      downloadCsv(rows, 'compliance-matrix-' + todayISO() + '.csv');
    };

    const bankCsv = () => {
      const rows = [['date', 'description', 'amount', 'status', 'matched_to']];
      d.bankRows.slice().sort((a, b) => String(b.date).localeCompare(String(a.date)))
        .forEach(r => rows.push([r.date, r.description, Number(r.amount) || 0, r.status || '', r.match_label || r.match_type || '']));
      downloadCsv(rows, 'bank-reconciliation-' + todayISO() + '.csv');
    };

    const taxJson = () => {
      downloadJson({
        format: 'propmystro_tax_submissions', generatedAt: new Date().toISOString(),
        account: account.name, submissions: d.tax,
      }, 'tax-submissions-' + todayISO() + '.json');
    };

    const propertyFinanceCsv = (propertyId, from, to) => {
      const prop = propById[propertyId];
      if (!prop) return;
      const inRange = (dt) => (!from || dt >= from) && (!to || dt <= to);
      const tenancyIds = d.tenancies.filter(t => t.property_id === prop.id).map(t => t.id);

      const rows = [];
      rows.push(['Property income & expenses']);
      rows.push(['Property', prop.address]);
      rows.push(['Postcode', prop.postcode || '']);
      rows.push(['Owner', ownerName(prop)]);
      rows.push(['Period', (from || 'all') + ' to ' + (to || 'all')]);
      rows.push(['Generated', todayISO()]);
      rows.push([]);

      const pays = d.payments.filter(pm => (tenancyIds.includes(pm.tenancy_id) || pm.property_id === prop.id) && inRange(pm.date)).sort((a, b) => String(a.date).localeCompare(String(b.date)));
      let incomeTotal = 0;
      rows.push(['INCOME']);
      rows.push(['Date', 'Tenant', 'Method', 'Reference', 'Amount (£)']);
      pays.forEach(pm => {
        incomeTotal += Number(pm.amount) || 0;
        const ty = tyById[pm.tenancy_id]; const lead = ty && tenById[ty.lead_tenant_id];
        rows.push([pm.date, lead ? lead.full_name : '', pm.method || '', pm.ref || pm.note || '', (Number(pm.amount) || 0).toFixed(2)]);
      });
      rows.push(['', '', '', 'Total income', incomeTotal.toFixed(2)]);
      rows.push([]);

      const exps = d.expenses.filter(e => e.property_id === prop.id && inRange(e.date)).sort((a, b) => String(a.date).localeCompare(String(b.date)));
      let expenseTotal = 0;
      rows.push(['EXPENSES']);
      rows.push(['Date', 'Category', 'SA105 box', 'Vendor', 'Description', 'Amount (£)']);
      exps.forEach(e => {
        expenseTotal += Number(e.amount) || 0;
        rows.push([e.date, e.category || '', e.sa105_box || '', e.vendor || '', e.description || '', (Number(e.amount) || 0).toFixed(2)]);
      });
      rows.push(['', '', '', '', 'Total expenses', expenseTotal.toFixed(2)]);
      rows.push([]);
      rows.push(['SUMMARY']);
      rows.push(['Total income', incomeTotal.toFixed(2)]);
      rows.push(['Total expenses', expenseTotal.toFixed(2)]);
      rows.push(['Net', (incomeTotal - expenseTotal).toFixed(2)]);

      const slug = (prop.address || 'property').replace(/[^a-z0-9]+/gi, '-').toLowerCase().slice(0, 40);
      downloadCsv(rows, 'finance-' + slug + '-' + (from || 'all') + '_' + (to || 'all') + '.csv');
    };

    const portfolioPdf = () => {
      const monthlyRent = d.props.reduce((s, p) => s + (Number(p.rent) || 0), 0);
      const latest = (pid, type) => d.certs.filter(c => c.property_id === pid && c.type === type).sort((a, b) => new Date(b.expiry_date || 0) - new Date(a.expiry_date || 0))[0];
      let flags = 0;
      const rowsHtml = d.props.map(p => {
        let crit = 0, warn = 0;
        CERT_COLS.forEach(col => {
          if (col.hmoOnly && p.type !== 'hmo') return;
          const cert = latest(p.id, col.v);
          const dd = cert && cert.expiry_date ? daysU(cert.expiry_date) : null;
          if (!cert || dd == null || dd < 30) crit++; else if (dd < 90) warn++;
        });
        flags += crit + warn;
        const status = crit ? '● Critical' : warn ? '● Warning' : '● Healthy';
        const esc = (s) => String(s == null ? '' : s).replace(/&/g, '&amp;').replace(/</g, '&lt;');
        return '<tr><td>' + esc(p.address) + '</td><td>' + esc(p.postcode) + '</td><td>' + esc(ownerName(p)) + '</td><td>' + esc((p.type || '').toUpperCase()) + '</td><td>£' + (Number(p.rent) || 0).toLocaleString() + '</td><td>' + status + '</td></tr>';
      }).join('');
      const html = '<!DOCTYPE html><html><head><meta charset="utf-8"><title>Portfolio summary</title>' +
        '<style>body{font-family:Helvetica,Arial,sans-serif;color:#1a1815;padding:40px;max-width:900px;margin:0 auto;}' +
        'h1{font-size:26px;margin:0 0 4px;}.sub{color:#8a7d70;font-size:13px;margin-bottom:24px;}' +
        '.kpis{display:flex;gap:20px;margin:20px 0 28px;}.kpi{flex:1;border:1px solid #ddd;border-radius:8px;padding:12px 14px;}' +
        '.kpi .l{font-size:10px;letter-spacing:.05em;color:#8a7d70;text-transform:uppercase;}.kpi .v{font-size:22px;font-weight:700;margin-top:4px;}' +
        'table{width:100%;border-collapse:collapse;font-size:12.5px;margin-top:8px;}th,td{text-align:left;padding:7px 8px;border-bottom:1px solid #e6e2d8;}' +
        'th{font-size:10px;letter-spacing:.05em;text-transform:uppercase;color:#8a7d70;}@media print{body{padding:0;}}</style></head><body>' +
        '<h1>Portfolio summary</h1><div class="sub">PropMystro · ' + account.name + ' · generated ' + todayISO() + '</div>' +
        '<div class="kpis">' +
        '<div class="kpi"><div class="l">Properties</div><div class="v">' + d.props.length + '</div></div>' +
        '<div class="kpi"><div class="l">Monthly rent</div><div class="v">£' + monthlyRent.toLocaleString() + '</div></div>' +
        '<div class="kpi"><div class="l">Active tenancies</div><div class="v">' + d.tenancies.filter(t => t.status === 'active').length + '</div></div>' +
        '<div class="kpi"><div class="l">Compliance flags</div><div class="v">' + flags + '</div></div>' +
        '</div>' +
        '<table><thead><tr><th>Property</th><th>Postcode</th><th>Owner</th><th>Type</th><th>Rent pcm</th><th>Compliance</th></tr></thead><tbody>' +
        rowsHtml + '</tbody></table>' +
        '<script>window.onload=function(){setTimeout(function(){window.print();},300);}<\/script></body></html>';
      const w = window.open('', '_blank');
      if (w) { w.document.write(html); w.document.close(); }
      else if (toast) toast('Allow pop-ups to generate the portfolio summary PDF.');
    };

    const ledgerRows = d.payments.length + d.expenses.length;
    const cards = [
      { title: 'Full ledger', fmt: 'CSV', n: ledgerRows + ' rows', desc: 'Every payment and expense — date, property, owner, SA105 box, amount.', action: ledgerCsv, ok: ledgerRows > 0 },
      { title: 'Compliance matrix', fmt: 'CSV', n: d.props.length + ' properties', desc: 'Every property × certificate type with its current status and expiry.', action: complianceCsv, ok: d.props.length > 0 },
      { title: 'Bank reconciliation', fmt: 'CSV', n: d.bankRows.length + ' lines', desc: 'All imported statement lines with their match status.', action: bankCsv, ok: d.bankRows.length > 0 },
      { title: 'Portfolio summary', fmt: 'PDF', n: '1 page', desc: 'Overview of all properties, rent and compliance — print or save as PDF.', action: portfolioPdf, ok: d.props.length > 0 },
      { title: 'Tax submissions', fmt: 'JSON', n: d.tax.length + ' records', desc: 'MTD quarterly + final declaration records, box-mapped, for your accountant.', action: taxJson, ok: hasTax && d.tax.length > 0, portfolioOnly: !hasTax },
    ];

    return <React.Fragment>
      <ExportStyles />
      <div className="pagehead"><h1>Export centre</h1><p>Pull the data out whenever you need it — for the accountant, the bank, or your own records. Everything generates locally in your browser.</p></div>

      <PropertyFinanceCard props={d.props} onExport={propertyFinanceCsv} />

      <div className="pmd-mono exp-group-l">STANDARD EXPORTS</div>
      <div className="exp-grid">
        {cards.map((c, i) => <div key={i} className="card exp-card">
          <div className="exp-card-head"><strong>{c.title}</strong><span className="role-tag">{c.fmt}</span></div>
          <p className="exp-card-d">{c.desc}</p>
          <div className="exp-card-foot">
            <span className="pmd-mono exp-card-n">{c.n}</span>
            {c.portfolioOnly
              ? <button className="btn btn-ghost btn-sm" onClick={openBilling}>Portfolio plan →</button>
              : <button className="btn btn-primary btn-sm" style={{ width: 'auto' }} disabled={!c.ok} onClick={c.action}>Download {c.fmt} ↓</button>}
          </div>
        </div>)}
      </div>

      <div className="exp-note pmd-mono">Cells are sanitised against spreadsheet formula-injection · exports reflect your owner-scope-free, account-wide data</div>
    </React.Fragment>;
  }

  // Per-property income & expenses over a custom range — the feature card.
  function PropertyFinanceCard({ props, onExport }) {
    const ty = taxYear();
    const [propertyId, setPropertyId] = useState(props[0] ? props[0].id : '');
    const [from, setFrom] = useState(ty.start);
    const [to, setTo] = useState(ty.end);
    return <div className="card exp-feature">
      <div className="card-head">
        <div><h3>Property income &amp; expenses</h3><div className="sub">One property, any date range — opens directly in Excel with income, expense and net summary sections.</div></div>
        <span className="role-tag">CSV · Excel</span>
      </div>
      <div className="card-pad exp-feature-body">
        <label className="exp-field"><span className="pmd-mono">PROPERTY</span>
          <select className="pm-input" value={propertyId} onChange={e => setPropertyId(e.target.value)}>
            {props.length === 0 && <option value="">No properties yet</option>}
            {props.map(p => <option key={p.id} value={p.id}>{p.address}</option>)}
          </select>
        </label>
        <label className="exp-field"><span className="pmd-mono">FROM</span>
          <input className="pm-input" type="date" value={from} onChange={e => setFrom(e.target.value)} />
        </label>
        <label className="exp-field"><span className="pmd-mono">TO</span>
          <input className="pm-input" type="date" value={to} onChange={e => setTo(e.target.value)} />
        </label>
        <div className="exp-feature-actions">
          <button className="btn btn-primary btn-sm" style={{ width: 'auto' }} disabled={!propertyId} onClick={() => onExport(propertyId, from, to)}>Download CSV ↓</button>
          <button className="linkbtn" onClick={() => { setFrom(ty.start); setTo(ty.end); }}>Tax year {ty.label}</button>
          <button className="linkbtn" onClick={() => { setFrom(''); setTo(''); }}>All dates</button>
        </div>
      </div>
    </div>;
  }

  function ExportStyles() {
    return <style>{`
      .exp-feature { border-color: var(--brand-deep); margin-bottom: 22px; }
      .exp-feature-body { display: flex; gap: 14px; align-items: flex-end; flex-wrap: wrap; }
      .exp-field { display: flex; flex-direction: column; gap: 4px; }
      .exp-field .pmd-mono { font-size: 9.5px; color: var(--ink-faint); letter-spacing: .06em; }
      .exp-feature-actions { display: flex; align-items: center; gap: 14px; flex-wrap: wrap; }
      .exp-group-l { font-size: 10px; letter-spacing: .08em; color: var(--ink-faint); margin: 0 0 10px 2px; }
      .exp-grid { display: grid; grid-template-columns: repeat(auto-fill, minmax(280px, 1fr)); gap: 14px; }
      .exp-grid > .card { margin-top: 0; }
      .exp-card { padding: 16px 18px; display: flex; flex-direction: column; gap: 8px; }
      .exp-card-head { display: flex; justify-content: space-between; align-items: center; gap: 10px; }
      .exp-card-head strong { font-size: 14.5px; }
      .exp-card-d { margin: 0; font-size: 12.5px; color: var(--ink-faint); line-height: 1.5; flex: 1; }
      .exp-card-foot { display: flex; justify-content: space-between; align-items: center; gap: 10px; }
      .exp-card-n { font-size: 10.5px; color: var(--ink-faint); }
      .exp-note { margin-top: 18px; font-size: 10.5px; color: var(--ink-faint); text-align: center; }
    `}</style>;
  }

  window.PMExportCentre = ExportCentre;
})();