/* === CAPS re-upload (.xlsm / .xlsx / .csv) ===
   Spec §13: contracts enter as a DATE/STATUS projection only. The workbook's
   dollar columns (Transaction Max, Converted USD, Paid, Remaining) are used
   transiently IN THE BROWSER to compute a single 0..1 percent-remaining ratio,
   then discarded — no dollar amount is ever stored or transmitted.
   - Sheet: "Active Agreements" (header row auto-detected)
   - Key: CAPS contract number (falls back to vendor-name match for pre-CAPS rows)
   - Updates facts only; manual follow-up notes are never touched
   - Deletes nothing: the sheet only lists ACTIVE agreements, so tracked
     contracts missing from the upload are kept (expiry dates age them out)
   Parsing uses SheetJS, lazy-loaded only when this modal opens. Admin only. */

const XLSX_CDN = "https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js";
let __xlsxLoading = null;
function loadXlsx() {
  if (window.XLSX) return Promise.resolve();
  if (!__xlsxLoading) {
    __xlsxLoading = new Promise((res, rej) => {
      const s = document.createElement("script");
      s.src = XLSX_CDN;
      s.onload = res;
      s.onerror = () => { __xlsxLoading = null; rej(new Error("Couldn't load the spreadsheet parser — check the network and retry.")); };
      document.head.appendChild(s);
    });
  }
  return __xlsxLoading;
}

function capsDate(v) {
  if (v == null || v === "") return null;
  const d = v instanceof Date ? v : new Date(v);
  if (isNaN(d)) return null;
  return d.toLocaleDateString("en-US", { month: "short", day: "numeric", year: "numeric" });
}

function buildCapsPlan(workbook) {
  const wsName = workbook.SheetNames.find((n) => n.toLowerCase().includes("active") && !n.toLowerCase().includes("summary"));
  if (!wsName) throw new Error('No "Active Agreements" sheet found — is this the CAPS AAR workbook?');
  const rows = window.XLSX.utils.sheet_to_json(workbook.Sheets[wsName], { header: 1, raw: true });

  // Header row: contains both a vendor-name column and Begin Dt.
  let hi = -1, header = null;
  for (let i = 0; i < Math.min(rows.length, 15); i++) {
    const cells = (rows[i] || []).map((c) => String(c == null ? "" : c).trim());
    if (cells.includes("Begin Dt") && (cells.includes("Name") || cells.includes("Vendor"))) { hi = i; header = cells; break; }
  }
  if (hi === -1) throw new Error('Couldn\'t find the header row (expected "Name" / "Begin Dt" columns).');
  const col = (name) => header.indexOf(name);

  const existingByCaps = {}, existingByVendor = {};
  for (const c of window.CONTRACTS) {
    if (c.capsId) existingByCaps[String(c.capsId)] = c;
    existingByVendor[(c.vendor || "").toLowerCase()] = c;
  }

  const FACT_KEYS = ["capsId", "vendor", "type", "capsStatus", "begin", "expire", "pctRemaining"];
  const creates = [], updates = [], unchanged = [];
  const seen = new Set();

  for (let i = hi + 1; i < rows.length; i++) {
    const r = rows[i] || [];
    const get = (name) => { const idx = col(name); return idx === -1 ? null : r[idx]; };
    const name = String(get("Name") || "").trim();
    if (!name) continue;
    const capsId = String(get("Contract") || "").trim() || null;

    // Dollar columns — used only here, only for the ratio, then dropped.
    const max = Number(get("Converted Max. Amt to USD")) || Number(get("Transaction Max Amt")) || 0;
    const remaining = Number(get("Amount Remaining"));
    const pct = max > 0 && isFinite(remaining) ? Math.max(0, Math.min(1, Math.round((remaining / max) * 1000) / 1000)) : null;

    const facts = {
      capsId,
      vendor: name,
      // Grant-vs-contract lives in "Origin" (GRT/TCO) in the 2026 AAR format;
      // the "Type" column there is the procurement doc type (AP/PO).
      type: String(get("Origin") || get("Type") || "").trim() || null,
      capsStatus: String(get("Status") || "").trim() || null,
      begin: capsDate(get("Begin Dt")),
      expire: capsDate(get("Expire Dt")),
      pctRemaining: pct,
    };

    const existing = (capsId && existingByCaps[capsId]) || existingByVendor[name.toLowerCase()];
    if (existing) {
      seen.add(existing.id);
      const patch = {}, prev = {};
      for (const k of FACT_KEYS) {
        const nv = facts[k], ov = existing[k] === undefined ? null : existing[k];
        if (nv !== null && nv !== ov) { patch[k] = nv; prev[k] = ov; }
      }
      if (Object.keys(patch).length) updates.push({ id: existing.id, vendor: existing.vendor, patch, prev });
      else unchanged.push(existing.id);
    } else {
      const id = "CT-" + (capsId || name.replace(/[^A-Za-z0-9]/g, "").slice(0, 12));
      seen.add(id);
      creates.push({ id, data: { id, ...facts, followup: null } });
    }
  }

  if (!creates.length && !updates.length && !unchanged.length) {
    throw new Error("No agreement rows found under the header — empty sheet?");
  }
  const missing = window.CONTRACTS.filter((c) => !seen.has(c.id)).map((c) => c.vendor);
  return { creates, updates, unchangedCount: unchanged.length, missing };
}

function CapsUpload({ onClose }) {
  const me = window.Store.session();
  const isAdmin = me && me.role === "admin";
  const [plan, setPlan] = React.useState(null);
  const [err, setErr] = React.useState(null);
  const [busy, setBusy] = React.useState(false);
  const [done, setDone] = React.useState(false);

  React.useEffect(() => {
    const onKey = (e) => { if (e.key === "Escape") onClose(); };
    window.addEventListener("keydown", onKey);
    return () => window.removeEventListener("keydown", onKey);
  }, [onClose]);

  async function parseBuffer(buf) {
    await loadXlsx();
    const wb = window.XLSX.read(new Uint8Array(buf), { type: "array", cellDates: true });
    setPlan(buildCapsPlan(wb));
  }

  function readFile(file) {
    setErr(null); setPlan(null);
    const rd = new FileReader();
    rd.onload = () => parseBuffer(rd.result).catch((e) => setErr(String(e.message || e)));
    rd.readAsArrayBuffer(file);
  }

  async function loadSample() {
    setErr(null); setPlan(null);
    try {
      const res = await fetch("/Appendix%20D%20-%20CAPS%20contracts%20file%20(CAPS%20AAR%20-%20Retirement%20Savings%20(PGM15)%20-%202026%2007).xlsm");
      if (!res.ok) throw new Error("Sample workbook not found on this server.");
      await parseBuffer(await res.arrayBuffer());
    } catch (e) { setErr(String(e.message || e)); }
  }

  // Dev: ?upload=caps&sample=1 parses the repo workbook automatically (local mode).
  React.useEffect(() => {
    if (window.Store.mode === "local" && new URLSearchParams(location.search).get("sample") === "1") loadSample();
  }, []);

  async function apply() {
    setBusy(true); setErr(null);
    try {
      const events = [];
      for (const c of plan.creates) events.push({ entityType: "contract", entityId: c.id, action: "create", patch: c.data, note: "CAPS upload" });
      for (const u of plan.updates) events.push({ entityType: "contract", entityId: u.id, action: "update", patch: u.patch, prev: u.prev, note: "CAPS upload" });
      events.push({
        entityType: "meta", entityId: "app", action: "update",
        patch: {
          lastCapsSync: new Date().toISOString(),
          ASOF: new Date().toLocaleDateString("en-US", { month: "long", day: "numeric", year: "numeric" }),
        }, note: "CAPS upload",
      });
      await window.Store.commit(events);
      setDone(true);
    } catch (e) { setErr(String(e.message || e)); }
    finally { setBusy(false); }
  }

  return (
    <>
      <div className="scrim is-open" onClick={onClose}></div>
      <div style={{
        position: "fixed", top: "50%", left: "50%", transform: "translate(-50%,-50%)",
        width: 560, maxWidth: "94vw", maxHeight: "86vh", overflowY: "auto", zIndex: 50,
      }} className="card">
        <div className="card__head">
          <div>
            <h3>CAPS upload</h3>
            <div className="section-sub">Re-sync contracts from the CAPS AAR workbook (.xlsm) — or a CSV of the Active Agreements sheet. Dollar amounts are reduced to a % remaining in your browser and never stored.</div>
          </div>
          <button className="btn btn--ghost" onClick={onClose}>✕</button>
        </div>
        <div className="card__body">
          {!isAdmin ? (
            <div className="muted" style={{ fontSize: 13 }}>Contract data is shared — ask an admin (Andrew or John) to run uploads.</div>
          ) : done ? (
            <div>
              <div style={{ fontSize: 14, fontWeight: 600, color: "var(--green)" }}>✓ Sync applied</div>
              <div style={{ fontSize: 12, color: "var(--ink-2)", marginTop: 6 }}>
                {plan.creates.length} new · {plan.updates.length} updated — recorded in the edit log.
              </div>
              <button className="btn btn--primary" style={{ marginTop: 14 }} onClick={onClose}>Close</button>
            </div>
          ) : (
            <>
              <div className="row" style={{ gap: 10, flexWrap: "wrap" }}>
                <label className="btn btn--primary" style={{ cursor: "pointer" }}>
                  Choose workbook…
                  <input type="file" accept=".xlsm,.xlsx,.xls,.csv" style={{ display: "none" }}
                         onChange={(e) => e.target.files[0] && readFile(e.target.files[0])} />
                </label>
                {window.Store.mode === "local" ? (
                  <button className="btn" onClick={loadSample}>Load July workbook sample</button>
                ) : null}
              </div>

              {err ? (
                <div style={{ marginTop: 12, padding: "8px 12px", borderRadius: 4, background: "var(--red-bg)", color: "var(--red)", fontSize: 12 }}>{err}</div>
              ) : null}

              {plan ? (
                <div style={{ marginTop: 16 }}>
                  <div className="tab-summary" style={{ padding: "0 0 12px", marginBottom: 12 }}>
                    <SumStat label="New" value={plan.creates.length} accent={plan.creates.length ? "green" : null} />
                    <SumStat label="Updated" value={plan.updates.length} accent={plan.updates.length ? "yellow" : null} />
                    <SumStat label="Unchanged" value={plan.unchangedCount} muted />
                    <SumStat label="Not in upload" value={plan.missing.length} muted />
                  </div>

                  {plan.creates.length ? (
                    <div style={{ marginBottom: 12 }}>
                      <div className="eyebrow" style={{ marginBottom: 4 }}>New</div>
                      {plan.creates.map((c) => (
                        <div key={c.id} style={{ fontSize: 12, padding: "3px 0" }}>
                          <span style={{ fontWeight: 600 }}>{c.data.vendor}</span>
                          <span className="muted"> — {c.data.type || "?"} · {c.data.begin || "?"} → {c.data.expire || "?"}
                            {c.data.pctRemaining != null ? ` · ${Math.round(c.data.pctRemaining * 100)}% remaining` : ""}</span>
                        </div>
                      ))}
                    </div>
                  ) : null}

                  {plan.updates.length ? (
                    <div style={{ marginBottom: 12 }}>
                      <div className="eyebrow" style={{ marginBottom: 4 }}>Changes</div>
                      {plan.updates.map((u) => (
                        <div key={u.id} style={{ fontSize: 12, padding: "3px 0" }}>
                          <span style={{ fontWeight: 600 }}>{u.vendor}</span>
                          <span className="muted"> — {Object.keys(u.patch).map((k) =>
                            k === "capsId" ? "CAPS # set" :
                            k === "pctRemaining" ? `remaining: ${u.prev[k] != null ? Math.round(u.prev[k] * 100) + "%" : "—"} → ${Math.round(u.patch[k] * 100)}%` :
                            `${k}: ${u.prev[k] || "—"} → ${u.patch[k]}`
                          ).join(" · ")}</span>
                        </div>
                      ))}
                    </div>
                  ) : null}

                  {plan.missing.length ? (
                    <div style={{ marginBottom: 12 }}>
                      <div className="eyebrow" style={{ marginBottom: 4 }}>Tracked here but not in this upload (kept — active sheet only lists live agreements)</div>
                      <div style={{ fontSize: 12, color: "var(--ink-mute)" }}>{plan.missing.join(" · ")}</div>
                    </div>
                  ) : null}

                  <div className="row" style={{ marginTop: 8, gap: 8 }}>
                    <button className="btn btn--primary" disabled={busy || (!plan.creates.length && !plan.updates.length)} onClick={apply}>
                      {busy ? "Applying…" : `Apply sync (${plan.creates.length + plan.updates.length} changes)`}
                    </button>
                    <button className="btn btn--ghost" onClick={() => setPlan(null)}>Start over</button>
                  </div>
                </div>
              ) : (
                <div className="mono faint" style={{ fontSize: 11, marginTop: 14, lineHeight: 1.6 }}>
                  Reads the "Active Agreements" sheet (header auto-detected). Ingested: CAPS #, name, type,
                  status, begin/expire dates, % remaining. Everything else — including every dollar figure —
                  is discarded at parse.
                </div>
              )}
            </>
          )}
        </div>
      </div>
    </>
  );
}

Object.assign(window, { CapsUpload });