/* ===== admin-lab-sheet.jsx — "Proovid (Excel)", RP's own lab sheet ==========

   WHAT THIS IS. A reproduction of

       T:\Geoloogia\Abimaterjalid\01 Välitöö-labor\Infotöötlus\
       GLxxxxx Labor_töödeldud.xlsx

   filled with the samples the crew entered in the field. Triin, 23.09: "tegin
   justkui väljas sisestamine koos proovide infoga ja nüüd peaksin siis selle
   info saama excelisse, et proovidega siis edasi tegeleda." Until now the only
   way out was the AGS4 text file, which is not something you work in.

   So the sheet is the office's own sheet, not a new invention: same columns in
   the same order, same header row on row 2, same freeze at E3, same autofilter,
   same yellow "the lab fills this" block, same IL/IC colour bands with the
   legend across the top. What we know goes in — borehole, sample number,
   interval, and the lab columns where results have already been imported — and
   what the lab owes is left blank, with the derived columns already carrying
   their formulas so the sheet computes the moment values are typed in.

   TWO DELIBERATE DIFFERENCES from the template, both explained where they
   happen: the IL and IC formulas are made relative (the template divides every
   row by row 3's WP and IP), and a sample with no lab result still gets a row.

   The mixed bold in the header IS reproduced, inconsistencies and all - that is
   what "format the same" means, and it is not this file's place to tidy the
   office's own template. */

/* Column numbers, once, because a sheet this wide is unreadable as literals.
   E..V are the sieve set in the template's order - the three "<0,00x" columns
   are curve points too, % finer than that size. */
const LS_SIEVES = [0.063, 0.125, 0.2, 0.63, 1, 2, 4, 6.3, 8, 12.5, 16, 20, 31.5, 45, 63];
const LS_FINES = ["<0,002", "<0,006", "<0,02"];
const LS_C = {
  labNr: 1, bh: 2, sampNr: 3, interval: 4,
  sieve0: 5,                       /* E, first sieve */
  k: 23, u: 24, wl: 25, wp: 26, ip: 27, wn: 28,
  kruusata: 29, f063: 30, liiv: 31, kruus: 32,
  grupp: 33, iso: 34, iso2018: 35, il: 36, ic: 37,
};

/* The IL bands, and the legend that sits over them on row 1. Same five colours
   the template uses, in the same order. */
const LS_BANDS = [
  { label: "väga pehme", fill: "#FF0000" },
  { label: "pehme", fill: "#DEEBF7" },
  { label: "sitke", fill: "#9DC3E6" },
  { label: "poolkõva", fill: "#00B0F0" },
  { label: "kõva", fill: "#00B050" },
];

function lsNum(v) {
  if (v === null || v === undefined || v === "") return null;
  const n = Number(v);
  return isFinite(n) ? n : null;
}

/* "2.50-3.00", the interval as the sheet writes it. */
function lsInterval(top, base) {
  const t = lsNum(top), b = lsNum(base);
  if (t === null && b === null) return "";
  const f = (x) => (x === null ? "?" : x.toFixed(2));
  return f(t) + "-" + f(b);
}

/* A lab result for this sample, or null.

   Matched on the borehole and the depth, not on the lab's own numbering: the
   labs number boreholes with bare integers ("1", "3") and their sample numbers
   restart per protocol, so neither identifies a sample on its own. 1 cm of
   tolerance absorbs the rounding between our two decimals and theirs. */
function lsResultFor(results, boreholeId, top, base) {
  const t = lsNum(top), b = lsNum(base);
  for (let i = 0; i < results.length; i++) {
    const r = results[i];
    if (r.borehole_id !== boreholeId) continue;
    const rt = lsNum(r.depth_top), rb = lsNum(r.depth_base);
    if (t !== null && rt !== null && Math.abs(t - rt) > 0.011) continue;
    if (b !== null && rb !== null && Math.abs(b - rb) > 0.011) continue;
    return r;
  }
  return null;
}

/* The grading curve as a lookup from sieve size to % passing. */
function lsCurve(r) {
  const out = {};
  ((r && r.grading) || []).forEach((p) => {
    const mm = lsNum(p.mm), pass = lsNum(p.passing);
    if (mm !== null && pass !== null) out[mm] = pass;
  });
  return out;
}

/* ---------------------------------------------------------------------------
   THE EXPORT
--------------------------------------------------------------------------- */
async function exportLabSheet(pid, opts) {
  const P = (window.PROJECTS || []).find((x) => x.id === pid);
  if (!P) throw new Error("projekti ei leitud");
  const code = (P.code || "") + (P.part ? "-" + P.part : "");

  const holes = (window.BOREHOLES || {})[pid] || [];
  const logs = window.SAMPLE_LOG || {};

  /* every sample the crew entered, in borehole then depth order */
  const rows = [];
  holes.forEach((h) => {
    const log = logs[h.key];
    if (!log || !log.samp || !log.samp.length) return;
    log.samp.forEach((s, i) => {
      rows.push({
        bh: h.id || h.name || "",
        /* _uuid, not id: `id` is the display name (PA1) and lab_results
           references the row uuid */
        boreholeId: h._uuid || null,
        ref: s.ref || String(i + 1),
        top: s.top, base: s.base, type: s.type || "",
      });
    });
  });
  /* borehole order first, then depth within a hole. sortBoreholeIds decides the
     borehole rule once for the whole set - PA before KP, by number where the
     numbering runs on through the types (Triin, 23.09). */
  const bhCmp = window.bhSorter
    ? window.bhSorter(rows.map((r) => r.bh))
    : (x, y) => String(x).localeCompare(String(y));
  rows.sort((a, b) => bhCmp(a.bh, b.bh) || ((lsNum(a.top) || 0) - (lsNum(b.top) || 0)));

  if (!rows.length) throw new Error("sellel projektil pole ühtegi proovi");

  /* GL26047_Proovid.xlsx (Christopher, 23.09). NOT _Labor_töödeldud: that
     name belongs to the file the LAB has filled in, and this one deliberately
     leaves their half empty. Two stages of the same sheet under one name in the
     project folder is how the wrong one gets sent.

     ASKED FOR BEFORE THE WORK, not after: showSaveFilePicker needs the user
     gesture to still be warm, and building the sheet first spends it. */
  const fileName = (code || "projekt").replace(/[^\w\u00c0-\u017e-]+/g, "_") + "_Proovid.xlsx";
  const dest = RPStore.pickDest ? await RPStore.pickDest(fileName, "lab") : null;

  /* the lab is not in loadAll, so it is fetched here - most projects have none
     yet, and an empty result set simply leaves those columns blank */
  let results = [];
  try {
    const labData = await RPStore.loadLabForProject(pid);
    results = (labData && labData.results) || [];
  } catch (e) {
    results = [];                   /* a sheet of field samples is still useful */
  }

  const wb = XlsxLite();

  /* ---- styles, matching the template cell for cell ---- */
  const HDR = { bold: true, size: 12, border: "grid", alignH: "center", alignV: "center" };
  const st = {
    hdrYellowSm: wb.style(Object.assign({}, HDR, { bold: true, size: 11, fill: "#FFFF00" })),
    hdrYellow: wb.style(Object.assign({}, HDR, { fill: "#FFFF00" })),
    hdrYellowWrap: wb.style(Object.assign({}, HDR, { fill: "#FFFF00", wrap: true })),
    /* W..AC are NOT bold and NOT filled in the office's file */
    hdrPlain: wb.style({ size: 11, border: "grid", alignH: "center", alignV: "center" }),
    hdrBlue: wb.style(Object.assign({}, HDR, { fill: "#DEEBF7" })),
    hdrBlueWrap: wb.style(Object.assign({}, HDR, { fill: "#DEEBF7", wrap: true })),
    hdrPlainWrap: wb.style({ size: 11, alignH: "center", alignV: "center", wrap: true }),
    hdrBare: wb.style({ size: 11 }),

    text: wb.style({ border: "grid", alignH: "center", alignV: "center", numFmt: "@" }),
    cell: wb.style({ border: "grid", alignH: "center", alignV: "center" }),
    cellWrap: wb.style({ size: 12, border: "grid", alignH: "center", alignV: "center", wrap: true }),
    n1: wb.style({ border: "grid", alignH: "center", alignV: "center", numFmt: "0.0" }),
    n0: wb.style({ border: "grid", alignH: "center", alignV: "center", numFmt: "0" }),
    n1b: wb.style({ border: "grid", alignH: "center", alignV: "center", numFmt: "0.0", fill: "#DEEBF7" }),
    n0b: wb.style({ border: "grid", alignH: "center", alignV: "center", numFmt: "0", fill: "#DEEBF7" }),
    n2bare: wb.style({ numFmt: "0.00" }),
    bare: wb.style({}),
  };

  const sheetRows = [];

  /* ---- row 1: the IL legend ---- */
  sheetRows.push({
    r: 1,
    cells: LS_BANDS.map((b, i) => ({
      c: LS_C.il + i, v: b.label, s: wb.style({ size: 11, fill: b.fill }),
    })),
  });

  /* ---- row 2: the header ---- */
  const hdr = [];
  hdr.push({ c: LS_C.labNr, v: "LAB NR", s: st.hdrYellowSm });
  hdr.push({ c: LS_C.bh, v: "Puuraugu", s: st.hdrYellowWrap });
  hdr.push({ c: LS_C.sampNr, v: "Proovi nr", s: st.hdrYellowWrap });
  hdr.push({ c: LS_C.interval, v: "Proovi võtmise intervall [m]", s: st.hdrYellowWrap });
  LS_SIEVES.forEach((mm, i) => hdr.push({ c: LS_C.sieve0 + i, n: mm, s: st.hdrYellow }));
  LS_FINES.forEach((t, i) => hdr.push({ c: LS_C.sieve0 + LS_SIEVES.length + i, v: t, s: st.hdrYellow }));
  [["k", LS_C.k], ["u", LS_C.u], ["WL", LS_C.wl], ["WP", LS_C.wp],
   ["IP", LS_C.ip], ["Wn", LS_C.wn], ["kruusata", LS_C.kruusata]]
    .forEach(([t, c]) => hdr.push({ c: c, v: t, s: st.hdrPlain }));
  hdr.push({ c: LS_C.f063, v: "<0.063", s: st.hdrBlue });
  hdr.push({ c: LS_C.liiv, v: "liiv", s: st.hdrBlue });
  hdr.push({ c: LS_C.kruus, v: "kruus", s: st.hdrBlue });
  hdr.push({ c: LS_C.grupp, v: "Pinnasegrupp", s: st.hdrBlueWrap });
  hdr.push({ c: LS_C.iso, v: "ISO", s: st.hdrBlue });
  hdr.push({ c: LS_C.iso2018, v: "SAVI/MÖLL\nISO2018", s: st.hdrPlainWrap });
  hdr.push({ c: LS_C.il, v: "IL", s: st.hdrBare });
  hdr.push({ c: LS_C.ic, v: "IC", s: st.hdrBare });
  sheetRows.push({ r: 2, h: 47.25, cells: hdr });

  /* ---- the samples ---- */
  const A = window.XlsxColA;
  const COL = {};
  Object.keys(LS_C).forEach((k) => { COL[k] = A(LS_C[k]); });
  const colSieve2 = A(LS_C.sieve0 + LS_SIEVES.indexOf(2));   /* the 2 mm sieve */
  const col063 = A(LS_C.sieve0);                              /* 0.063 mm */

  let withLab = 0;
  rows.forEach((s, i) => {
    const r = 3 + i;
    const lab = lsResultFor(results, s.boreholeId, s.top, s.base);
    if (lab) withLab++;
    const curve = lsCurve(lab);
    const cells = [];

    cells.push({ c: LS_C.labNr, v: (lab && lab.lab_nr) || "", s: st.text });
    cells.push({ c: LS_C.bh, v: s.bh, s: st.cellWrap });
    cells.push({ c: LS_C.sampNr, v: String(s.ref), s: st.cellWrap });
    cells.push({ c: LS_C.interval, v: lsInterval(s.top, s.base), s: st.cellWrap });

    LS_SIEVES.forEach((mm, j) => {
      const v = curve[mm];
      cells.push({ c: LS_C.sieve0 + j, n: v == null ? "" : v, s: st.n1 });
    });
    /* the three fines columns are curve points keyed by their own size */
    [0.002, 0.006, 0.02].forEach((mm, j) => {
      const v = curve[mm];
      cells.push({ c: LS_C.sieve0 + LS_SIEVES.length + j, n: v == null ? "" : v, s: st.n1 });
    });

    const put = (c, v, style) => cells.push({ c: c, n: v == null ? "" : v, s: style || st.cell });
    put(LS_C.k, lab ? lsNum(lab.permeability) : null);
    put(LS_C.u, lab ? lsNum(lab.organic_pct) : null);
    put(LS_C.wl, lab ? lsNum(lab.w_liquid) : null);
    put(LS_C.wp, lab ? lsNum(lab.w_plastic) : null);
    put(LS_C.ip, lab ? lsNum(lab.plasticity_ix) : null);
    put(LS_C.wn, lab ? lsNum(lab.w_natural) : null);

    /* THE DERIVED COLUMNS STAY FORMULAS, as in the template. A baked number
       would stop recomputing the moment anyone corrects a sieve value, which
       is exactly what this sheet is for. */
    /* GUARDED, and the template is not. Its four stub rows showed #DIV/0! and
       a kruus of 100 with nothing typed in, which nobody noticed on four rows.
       Triin gets one row per sample and the lab columns start empty, so
       unguarded these would read "100% kruusa" and #DIV/0! down the whole
       sheet. Blank until the sieves are filled; identical once they are. */
    const ifSieve = (expr) => 'IF(' + colSieve2 + r + '="","",' + expr + ")";
    cells.push({ c: LS_C.kruusata, f: 'IFERROR(100/(100-' + COL.kruus + r + ")*" + COL.liiv + r + ',"")', s: st.n1 });
    cells.push({ c: LS_C.f063, f: 'IF(' + col063 + r + '="","",' + col063 + r + ")", s: st.n1b });
    cells.push({ c: LS_C.liiv, f: ifSieve(colSieve2 + r + "-" + COL.f063 + r), s: st.n1b });
    cells.push({ c: LS_C.kruus, f: ifSieve("100-" + colSieve2 + r), s: st.n1b });

    cells.push({ c: LS_C.grupp, v: (lab && lab.soil_group) || "", s: st.n0b });
    cells.push({ c: LS_C.iso, v: (lab && lab.iso_class) || "", s: st.n0b });

    /* EVS-EN ISO 14688-2 Joonis 1, the A-line, written exactly as the office
       writes it - the same test iso-naming.js implements for the app. */
    cells.push({
      c: LS_C.iso2018,
      f: 'IF(' + COL.wl + r + '<1,"-",IF(' + COL.wl + r + '>25.38,IF(' + COL.ip + r +
         '>0.73*(' + COL.wl + r + '-20),IF(' + COL.ip + r + '<7,"ClL-SiL","Cl"),"Si"),IF(' +
         COL.ip + r + '>7,"Cl",IF(' + COL.ip + r + '<4,"Si","ClL-SiL"))))',
      s: st.bare,
    });

    /* IL AND IC ARE RELATIVE HERE, AND THE TEMPLATE'S ARE NOT. The office file
       has =(AB3-$Z$3)/$AA$3 on every row, so rows 4 downwards divide by row
       THREE's WP and IP and read wrong. Reproducing that faithfully would mean
       shipping wrong numbers, so these reference their own row. */
    cells.push({ c: LS_C.il, f: 'IFERROR((' + COL.wn + r + "-" + COL.wp + r + ")/" + COL.ip + r + ',"")', s: st.n2bare });
    cells.push({ c: LS_C.ic, f: 'IFERROR((' + COL.wl + r + "-" + COL.wn + r + ")/" + COL.ip + r + ',"")', s: st.n2bare });

    sheetRows.push({ r: r, h: 15.75, cells: cells });
  });

  const last = 2 + rows.length;
  const ilRef = COL.il + "3:" + COL.il + last;
  const icRef = COL.ic + "3:" + COL.ic + last;

  const cols = [];
  cols[LS_C.bh - 1] = { w: 11.43 };
  cols[LS_C.interval - 1] = { w: 15.14 };
  cols[LS_C.iso - 1] = { w: 35.14 };
  cols[LS_C.iso2018 - 1] = { w: 12.43 };

  wb.sheet("Sheet1", {
    cols: cols,
    rows: sheetRows,
    freeze: "E3",
    autoFilter: "A2:" + A(LS_C.ic + 3) + last,
    /* the five bands, in the template's order and with its thresholds */
    condFormats: [
      { ref: ilRef, rules: [
        { op: "greaterThan", formulas: ["0.75"], fill: "#FF0000" },
        { op: "between", formulas: ["0.5", "0.75"], fill: "#DEEBF7" },
        { op: "between", formulas: ["0.25", "0.5"], fill: "#9DC3E6" },
        { op: "between", formulas: ["0", "0.25"], fill: "#00B0F0" },
        { op: "lessThan", formulas: ["0"], fill: "#00B050" },
      ] },
      { ref: icRef, rules: [
        { op: "lessThan", formulas: ["0.25"], fill: "#FF0000" },
        { op: "between", formulas: ["0.25", "0.5"], fill: "#DEEBF7" },
        { op: "between", formulas: ["0.5", "0.75"], fill: "#9DC3E6" },
        { op: "between", formulas: ["0.75", "1"], fill: "#00B0F0" },
        { op: "greaterThan", formulas: ["1"], fill: "#00B050" },
      ] },
    ],
  });

  if (RPStore.saveTo && dest) await RPStore.saveTo(dest, fileName, wb.blob());
  else RPStore.download(fileName, wb.blob());

  return { samples: rows.length, withLab: withLab, holes: new Set(rows.map((x) => x.bh)).size };
}

Object.assign(window, { exportLabSheet });