Estimate and quantity exports (CSV and XLSX)

const q4 = (v) => (Number.isFinite(v) ? roundTo(v, 4) : null);
const dollars = (cents) => roundTo(cents / 100, 2);
const rate = (r4) => r4 / 10000;

/** The estimate as CSV text (values, not formulas). */
export function estimateCsv(state, est = baseEstimate(state), columns = []) {
  const lines = [csvLine([...estimateHead(), ...columns.map((c) => c.name)])];
  for (const r of est.rows) {
    const name = r.kind === 'component' ? '  ' + r.name : r.name;
    const type = r.kind === 'component' ? t('x.component') : r.kind === 'subcontract' ? t('x.sub') : TYPE_LABELS[r.type];
    if (r.error) lines.push(csvLine([name, type, r.unit, null, r.waste / 100, null, rate(r.mat), rate(r.lab), null, null, null, rowNote(r), ...colCells(columns, r, state)]));
    else lines.push(csvLine([name, type, r.unit, q4(r.qty), r.waste / 100, q4(r.qtyW), rate(r.mat), rate(r.lab), dollars(r.matExt), dollars(r.labExt), dollars(r.total), rowNote(r), ...colCells(columns, r, state)]));
  }
  const alts = alternates(state);
  lines.push(csvLine([alts.length ? t('export.alt.base') : t('x.total'), null, null, null, null, null, null, null, dollars(est.matTotal), dollars(est.labTotal), dollars(est.total), est.incomplete ? t('x.incomplete') : '']));
  if (alts.length) {                                       // T17: each alternate and what it does to the base bid
    lines.push('', csvLine([t('export.alt.h'), t('export.alt.kind'), null, null, null, null, null, null, null, null, t('export.alt.amount'), null]));
    for (const a of alts) lines.push(csvLine([a.area.name, t('alt.' + a.alt), null, null, null, null, null, null, dollars(a.est.matTotal * Math.sign(a.amount || 1)), dollars(a.est.labTotal * Math.sign(a.amount || 1)), dollars(a.amount), a.est.incomplete ? t('x.incomplete') : '']));
  }
  if (bidUsed(state.bid)) {                                 // T19: the bid summary (markups on the base bid)
    const b = bidSummary(est, state.bid, alts);
    lines.push('', csvLine([t('bid.h')]));
    for (const l of b.lines) lines.push(csvLine([l.label, l.pct ? (l.pct / 100) + '%' : null, null, null, null, null, null, null, null, null, dollars(l.cents), null]));
    for (const a of b.alts) lines.push(csvLine([t('bid.pdf.alt', { name: a.name, kind: t('alt.' + a.alt).toLowerCase() }), null, null, null, null, null, null, null, null, null, dollars(a.withMarkup), t('bid.alt.note')]));
  }
  return lines.join('\r\n') + '\r\n';
}

/** Per-sheet quantities as CSV. sheetLabel(key) names a sheet. */
export function sheetsCsv(state, sheetLabel) {
  const lines = [csvLine(['sheet', 'item', 'qty', 'unit', 'marks'].map((k) => t('x.head.' + k)))];
  for (const r of sheetBreakdown(state)) lines.push(csvLine([sheetLabel(r.sheet), r.name, q4(r.qty), r.unit, r.marks]));
  return lines.join('\r\n') + '\r\n';
}

/**
 * The material list: every assembly part (component row) and every item or condition row that carries a material rate,
 * added up by name and unit across the takeoff. → [{ name, unit, qty, qtyW, matExt (cents), csi, items: [item names] }],
 * sorted by CSI number (none last), then name. Rows whose formula failed are left out (the estimate says it is incomplete).
 */
export function materialList(state, est = baseEstimate(state)) {
  const items = new Map(state.items.map((i) => [i.id, i]));
  const by = new Map();
  for (const r of est.rows) {
    if (r.error || r.awarded || r.kind === 'subcontract' || !(r.qty > 0)) continue;     // an awarded package's material is the sub's
    if (r.kind !== 'component' && !(r.mat > 0)) continue;
    const it = items.get(r.itemId);
    const name = String(r.name).trim();
    const k = name.toLowerCase() + '\u0000' + r.unit;
    const g = by.get(k) || { name, unit: r.unit, qty: 0, qtyW: 0, matExt: 0, csi: it?.csi || '', items: [] };
    g.qty += r.qty; g.qtyW += r.qtyW; g.matExt += r.matExt;
    if (!g.csi && it?.csi) g.csi = it.csi;
    if (it && !g.items.includes(it.name)) g.items.push(it.name);
    by.set(k, g);
  }
  return [...by.values()].sort((a, b) => (a.csi || '99').localeCompare(b.csi || '99') || a.name.localeCompare(b.name));
}

/** The estimate by CSI division: [{ div ('03' | '' for items without a number), title, mat, lab, total (cents), items }]. */
export function byDivision(state, est = baseEstimate(state)) {
  const items = new Map(state.items.map((i) => [i.id, i]));
  const by = new Map();
  for (const r of est.rows) {
    if (r.error) continue;
    const it = items.get(r.itemId);
    const div = r.kind === 'subcontract' ? r.pkg : it?.csi ? it.csi.slice(0, 2) : '';
    const g = by.get(div) || { div, title: div ? divisionTitle(div) : t('csi.div.none'), mat: 0, lab: 0, total: 0, items: new Set() };
    g.mat += r.matExt; g.lab += r.labExt; g.total += r.total; if (r.itemId) g.items.add(r.itemId);
    by.set(div, g);
  }
  return [...by.values()].sort((a, b) => (a.div || '99').localeCompare(b.div || '99')).map((g) => ({ ...g, items: g.items.size }));
}

/**
 * Spot elevations for CivilCraft (0.5 TK.5): the count marks of one sheet that carry an elevation (`z`), as a point file
 * CivilCraft opens (points.js: a header naming Point, Northing, Easting, Elevation, Description). Northing and easting are
 * the sheet's own coordinates in feet (PDF user space × its scale: y up = north on an unrotated sheet), so distances and
 * volumes between the points are true; place them on site coordinates in CivilCraft. → { ok, csv, n } | { ok: false, why }
 */
export function spotElevationsCsv(state, sheet, sheetLabel = (k) => k) {
  const sc = state.sheets[sheet]?.scale;
  const marks = state.marks.filter((m) => m.sheet === sheet && m.kind === 'count' && typeof m.z === 'number');
  if (!marks.length) return { ok: false, why: t('spots.none') };
  if (!sc) return { ok: false, why: t('spots.noscale', { sheet: sheetLabel(sheet) }) };
  const k = sc.ftPerUnit;
  const items = new Map(state.items.map((i) => [i.id, i]));
  const lines = [csvLine(['Point', 'Northing', 'Easting', 'Elevation', 'Description'])];   // i18n-ignore: CivilCraft reads these column names
  marks.forEach((m, i) => lines.push(csvLine([i + 1, roundTo(m.pts[0].y * k, 4), roundTo(m.pts[0].x * k, 4), roundTo(m.z, 4), items.get(m.item)?.name || ''])));
  return { ok: true, csv: lines.join('\r\n') + '\r\n', n: marks.length };
}

const D = ref(n, 3), E = ref(n, 4), F = ref(n, 5), G = ref(n, 6), H = ref(n, 7), I = ref(n, 8), J = ref(n, 9);
rows.push([
  name, type, r.unit,
  { v: r.qty, s: 'qty' },
  { v: r.waste / 10000, s: 'pct' },
  { v: r.qtyW, f: D + '*(1+' + E + ')', s: 'qty' },
  { v: rate(r.mat), s: 'rate' },
  { v: rate(r.lab), s: 'rate' },
  { v: dollars(r.matExt), f: 'ROUND(' + F + '*' + G + ',2)', s: 'money' },
  { v: dollars(r.labExt), f: r.work ? 'ROUND(' + F + '/' + r.crew.prod + '*' + r.crew.size + '*' + rate(r.crew.wage) + ',2)' : 'ROUND(' + F + '*' + H + ',2)', s: 'money' },   // T18: a crew's hours × wage
  { v: dollars(r.total), f: I + '+' + J, s: 'money' },

  const r = measureMark(m, sc?.ftPerUnit, it);
  const row = [sheetLabel(m.sheet), it.name, TYPE_LABELS[m.kind]];
  const unit = unitOf(it, m.kind);                      // a second condition's mark (T8) in that condition's unit
  if (!r) { markRow(addArea(m, [...row, null, null, t('x.noscale.sheet')])); continue; }
  if (m.kind === 'count') markRow(addArea(m, [...row, { v: 1, s: 'int' }, 'EA', typeof m.z === 'number' ? t('spots.detail', { z: roundTo(m.z, 4) }) : '']));
  else if (m.kind === 'linear') {
    const k = UNITS[unit].system === 'metric' ? 0.3048 : 1;
    const ded = new Set(r.deducts);
    const segLen = (i) => (r.slopes[i].k === 'flat' ? formatFtIn(r.segmentsFt[i]) : t('export.slope.seg', { len: formatFtIn(r.slopedFt[i]), kind: r.slopes[i].k === 'hip' ? t('slope.hip').toLowerCase() : t('slope.rake').toLowerCase(), pitch: formatPitch(r.slopes[i].p) }));
    const segs = r.segmentsFt.map((s, i) => (ded.has(i) ? t('export.deduct.seg', { len: segLen(i) }) : segLen(i))).join(', ');
    markRow(addArea(m, [...row, { v: r.lengthFt * k, s: 'qty' }, k === 1 ? 'LF' : 'm', t('x.segments', { n: r.segmentsFt.length, list: segs }) +
      (ded.size ? '; ' + t('export.deduct.total', { cut: formatFtIn(r.deductFt), gross: formatFtIn(r.grossFt) }) : '')]));
  } else {
    const k = UNITS[unit].perSqFt;
    const slopeNote = r.factor > 1 ? t('export.slope.area', { pitch: formatPitch(r.pitch), plan: roundTo(r.planSqft * k, 2) }) + '; ' : '';
    markRow(addArea(m, [...row, { v: r.areaSqft * k, s: 'qty' }, UNITS[unit].label, slopeNote + (m.holes.length ? t('x.gross', { gross: roundTo(r.grossSqft * k, 2), cut: roundTo(r.cutSqft * k, 2) }) + '; ' : '') + t('x.perimeter', { len: formatFtIn(r.perimeterFt) })]));
  }
}

/**
 * The markups block under the Estimate sheet's total (TK.4), with live formulas: each markup's base in column I, its percent
 * in E, its amount = ROUND(I × E, 2) in K; the cost and the bid add up column K. Cached values are bidSummary's to the cent.
 */
function markupRows(est, bid, totalRow, firstRow) {
  const b = normalBid(bid);
  const sum = bidSummary(est, b);
  const L = (key) => sum.lines.find((l) => l.key === key);
  const rows = [];
  const at = {};                                   // key → 1-based row of its amount (column K)
  let r = firstRow;
  const push = (cells, key) => { rows.push(cells); if (key) at[key] = r; r++; };
  push([{ v: t('bid.h'), s: 'bold' }]);
  const pctRow = (key, baseF, baseV) => push([L(key).label, null, null, null, { v: L(key).pct / 10000, s: 'pct' }, null, null, null,
    { v: dollars(baseV), f: baseF, s: 'money' }, null, { v: dollars(L(key).cents), f: 'ROUND(I' + r + '*E' + r + ',2)', s: 'money' }, t('bid.x.base')], key);
  const I = 'I' + totalRow, J = 'J' + totalRow;
  if (L('burden')) pctRow('burden', J, est.labTotal);
  push([{ v: L('cost').label, s: 'bold' }, null, null, null, null, null, null, null, null, null,
    { v: dollars(L('cost').cents), f: [...(est.subTotal > 0 ? ['K' + totalRow] : [I, J]), ...(at.burden ? ['K' + at.burden] : [])].join('+'), s: 'boldMoney' }, ''], 'cost');   // 0.7 B3: K holds the chosen sub bids too
  const above = ['cost'];
  const refs = () => above.filter((k) => at[k]).map((k) => 'K' + at[k]).join('+');
  const cents = () => above.reduce((n, k) => n + (L(k)?.cents || 0), 0);
  if (L('tax')) { pctRow('tax', I, est.matTotal); above.push('tax'); }
  for (const k of ['overhead', 'profit', 'bond']) if (L(k)) { pctRow(k, refs(), cents()); above.push(k); }
  push([{ v: L('total').label, s: 'bold' }, null, null, null, null, null, null, null, null, null, { v: dollars(L('total').cents), f: refs(), s: 'boldMoney' }, ''], 'total');
  return rows;
}

/**
 * The "By bid area" worksheet (T12): each bid area's estimate (formulas on that area's own totals), a subtotal per
 * area (SUM over its rows) and the sum of the subtotals. Marks in no bid area come last.
 */
export function areaRows(state) {
  const head = ['export.areas.area', 'export.areas.item', 'export.areas.unit', 'export.areas.qty', 'export.areas.qtyw', 'export.areas.mat', 'export.areas.lab', 'export.areas.total', 'export.areas.notes'];
  const rows = [head.map((k) => ({ v: t(k), s: 'head' }))];
  const subs = [];
  for (const { area, est } of areaEstimates(state)) {
    const name = area ? (area.alt ? t('export.alt.name', { name: area.name, kind: t('alt.' + area.alt).toLowerCase() }) : area.name) : t('export.areas.none');
    const first = rows.length + 1;                // 1-based first row of this area's block
    for (const r of est.rows) {
      const label = r.kind === 'component' ? { v: '    ' + r.name } : { v: r.name, s: 'bold' };
      if (r.error) { rows.push([name, label, r.unit, null, null, null, null, null, rowNote(r)]); continue; }
      rows.push([name, label, r.unit, { v: r.qty, s: 'qty' }, { v: r.qtyW, s: 'qty' }, { v: dollars(r.matExt), s: 'money' }, { v: dollars(r.labExt), s: 'money' }, { v: dollars(r.total), s: 'money' }, rowNote(r)]);
    }
    const last = rows.length;
    rows.push([{ v: t('export.areas.subtotal', { area: name }), s: 'bold' }, null, null, null, null,
      { v: dollars(est.matTotal), f: 'SUM(F' + first + ':F' + last + ')', s: 'boldMoney' },
      { v: dollars(est.labTotal), f: 'SUM(G' + first + ':G' + last + ')', s: 'boldMoney' },
      { v: dollars(est.total), f: 'SUM(H' + first + ':H' + last + ')', s: 'boldMoney' },
      est.incomplete ? t('export.areas.incomplete') : '']);
    subs.push({ row: rows.length, est });
    rows.push([]);
  }
  if (subs.length) {
    const sum = (col) => subs.map((x) => col + x.row).join('+');
    const tot = (k) => subs.reduce((n, x) => n + x.est[k], 0);
    rows.push([{ v: t('export.areas.all'), s: 'bold' }, null, null, null, null,
      { v: dollars(tot('matTotal')), f: sum('F'), s: 'boldMoney' }, { v: dollars(tot('labTotal')), f: sum('G'), s: 'boldMoney' }, { v: dollars(tot('total')), f: sum('H'), s: 'boldMoney' },
      t('export.areas.all.note')]);
  }
  return rows;
}

/**
 * The "Alternates" worksheet (T17): the base bid, then each alternate's items (its own quantities and costs) and what it
 * does to the base bid (+ an add, − a deduct), and the base bid with every add taken.
 */
export function alternateRows(state, base = baseEstimate(state), alts = alternates(state)) {
  const rows = [['export.alt.h', 'export.areas.item', 'export.areas.unit', 'export.areas.qty', 'export.alt.amount', 'export.areas.notes'].map((k) => ({ v: t(k), s: 'head' }))];
  rows.push([{ v: t('export.alt.base'), s: 'bold' }, null, null, null, { v: dollars(base.total), s: 'boldMoney' }, base.incomplete ? t('x.incomplete') : '']);
  rows.push([]);
  for (const a of alts) {
    const name = t('export.alt.name', { name: a.area.name, kind: t('alt.' + a.alt).toLowerCase() });
    for (const r of a.est.rows) {
      const label = r.kind === 'component' ? { v: '    ' + r.name } : { v: r.name, s: 'bold' };
      rows.push([name, label, r.unit, r.error ? null : { v: r.qty, s: 'qty' }, r.error ? null : { v: dollars(r.total), s: 'money' }, rowNote(r)]);
    }
    rows.push([{ v: name, s: 'bold' }, null, null, null, { v: dollars(a.amount), s: 'boldMoney' }, a.alt === 'add' ? t('export.alt.add.note') : t('export.alt.deduct.note')]);
    rows.push([]);
  }
  const adds = alts.filter((a) => a.alt === 'add').reduce((n, a) => n + a.amount, 0);
  rows.push([{ v: t('export.alt.withadds'), s: 'bold' }, null, null, null, { v: dollars(base.total + adds), s: 'boldMoney' }, '']);
  return rows;
}