import kpiDefinitions, { KPI_DEFINITIONS_SHA256 } from "./kpi-definitions.generated.js";
import { applyAccountingInventoryPolicy } from "./accounting-contract.mjs";
import {
  applyMappingFieldOverrides,
  approveMappingState,
  canExportResult,
  createMappingState,
  editMappingState,
  ingestPreparedWorkbooks,
  mappingRowsToConfig,
  recordCalculationState,
} from "./logic.mjs";

export { KPI_DEFINITIONS_SHA256 };

export const EXPORT_SHEET_NAMES = Object.freeze([
  "Source Inventory",
  "Data Dictionary",
  "Normalized Data",
  "Calculations",
  "Summary",
  "Dashboard",
  "Exceptions",
]);

const MAX_FILE_BYTES = 8 * 1024 * 1024;
const MAX_FILES = 3;
const MAX_SHEETS = 12;
const MAX_ROWS_PER_SHEET = 5000;
const MAX_ZIP_ENTRIES = 256;
const MAX_ZIP_UNCOMPRESSED_BYTES = 32 * 1024 * 1024;
const MAX_ZIP_COMPRESSION_RATIO = 100;
const ALLOWED_EXTENSIONS = new Set([".csv", ".xlsx"]);
const ALLOWED_MIME_TYPES = new Set([
  "",
  "text/csv",
  "application/csv",
  "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
]);
const CANONICAL_FIELDS = [
  "record_type", "lot_id", "invoice_id", "payment_id", "payment_invoice_id", "product", "buyer_segment",
  "carat", "purchase_cost", "import_cost", "cutting_cost", "certificate_cost", "logistics_cost",
  "expected_processing_cost", "invoice_date", "due_date", "sales_amount", "payment_date", "payment_amount",
  "required_documents", "completed_documents",
];
const FIELD_ALIASES = Object.freeze({
  lot_id: ["lot_id", "lot_code"],
  purchase_cost: ["purchase_cost", "purchase_thb"],
  sales_amount: ["sales_amount", "invoice_amount", "sales_thb"],
  payment_invoice_id: ["payment_invoice_id", "invoice_id_paid"],
});
const SHEET_ROLES = Object.freeze({
  "prepared lots": "lots",
  "cost lines": "costs",
  "document register": "documents",
  invoices: "invoices",
  payments: "payments",
  mapping: "mapping",
  metrics: "definitions",
  "data dictionary": "dictionary",
  controls: "controls",
});
const ROLE_HEADER_FIELDS = Object.freeze({
  lots: ["lot_id", "purchase_cost"],
  costs: ["cost_line_id", "lot_id", "amount_thb"],
  documents: ["document_id", "lot_id", "status"],
  invoices: ["invoice_id", "sales_amount", "due_date"],
  payments: ["payment_id", "payment_invoice_id", "payment_amount"],
  mapping: ["canonical_field", "source_field", "status"],
  definitions: ["metric", "definition"],
  dictionary: ["field", "type"],
  controls: ["control", "value"],
});

const state = {
  files: [],
  sources: [],
  headers: [],
  preparedFiles: [],
  mapping: createMappingState({}, "1.0"),
  mappingConfig: null,
  result: null,
};

export function canCalculate(mapping) {
  return mapping?.status === "Approved" && typeof mapping.version === "string" && mapping.version.length > 0;
}

export function validateUploadFile(file) {
  const name = String(file?.name ?? "");
  const extension = name.includes(".") ? name.slice(name.lastIndexOf(".")).toLowerCase() : "";
  if (!ALLOWED_EXTENSIONS.has(extension) || !ALLOWED_MIME_TYPES.has(String(file?.type ?? ""))) return "UNSUPPORTED_FILE_TYPE";
  if (!Number.isFinite(file?.size) || file.size < 1 || file.size > MAX_FILE_BYTES) return "FILE_TOO_LARGE";
  return null;
}

export function validateSelectedFileCount(fileCount) {
  return Number.isInteger(fileCount) && fileCount >= 1 && fileCount <= MAX_FILES ? null : "TOO_MANY_FILES";
}

export function guardWorkbookShape(sheetNames, rowCounts) {
  if (!Array.isArray(sheetNames) || sheetNames.length < 1 || sheetNames.length > MAX_SHEETS) return "TOO_MANY_SHEETS";
  if (!Array.isArray(rowCounts) || rowCounts.some((count) => !Number.isInteger(count) || count < 0 || count > MAX_ROWS_PER_SHEET)) return "TOO_MANY_ROWS";
  return null;
}

function archiveBytes(input) {
  if (input instanceof ArrayBuffer) return new Uint8Array(input);
  if (ArrayBuffer.isView(input)) return new Uint8Array(input.buffer, input.byteOffset, input.byteLength);
  return null;
}

function decodeZipName(bytes, offset, length) {
  return new TextDecoder("utf-8", { fatal: false }).decode(bytes.subarray(offset, offset + length));
}

export function preflightXlsxArchive(input, overrides = {}) {
  const bytes = archiveBytes(input);
  if (!bytes || bytes.byteLength < 22) return "ZIP_INVALID";
  const limits = {
    maxEntries: overrides.maxEntries ?? MAX_ZIP_ENTRIES,
    maxUncompressedBytes: overrides.maxUncompressedBytes ?? MAX_ZIP_UNCOMPRESSED_BYTES,
    maxCompressionRatio: overrides.maxCompressionRatio ?? MAX_ZIP_COMPRESSION_RATIO,
  };
  const view = new DataView(bytes.buffer, bytes.byteOffset, bytes.byteLength);
  const lowerBound = Math.max(0, bytes.byteLength - 22 - 65535);
  let eocdOffset = -1;
  for (let offset = bytes.byteLength - 22; offset >= lowerBound; offset -= 1) {
    if (view.getUint32(offset, true) === 0x06054b50) {
      eocdOffset = offset;
      break;
    }
  }
  if (eocdOffset < 0) return "ZIP_INVALID";
  const diskNumber = view.getUint16(eocdOffset + 4, true);
  const centralDisk = view.getUint16(eocdOffset + 6, true);
  const diskEntries = view.getUint16(eocdOffset + 8, true);
  const totalEntries = view.getUint16(eocdOffset + 10, true);
  const centralSize = view.getUint32(eocdOffset + 12, true);
  const centralOffset = view.getUint32(eocdOffset + 16, true);
  const archiveCommentLength = view.getUint16(eocdOffset + 20, true);
  if (eocdOffset + 22 + archiveCommentLength !== bytes.byteLength) return "ZIP_INVALID";
  if (diskNumber !== 0 || centralDisk !== 0 || diskEntries !== totalEntries) return "ZIP_MULTI_DISK";
  if (totalEntries === 0xffff || centralSize === 0xffffffff || centralOffset === 0xffffffff) return "ZIP64_UNSUPPORTED";
  if (totalEntries < 1 || totalEntries > limits.maxEntries) return "ZIP_ENTRY_LIMIT";
  if (centralOffset + centralSize > eocdOffset) return "ZIP_INVALID";

  let cursor = centralOffset;
  let totalCompressed = 0;
  let totalUncompressed = 0;
  const seenNames = new Set();
  for (let index = 0; index < totalEntries; index += 1) {
    if (cursor + 46 > bytes.byteLength || view.getUint32(cursor, true) !== 0x02014b50) return "ZIP_INVALID";
    const flags = view.getUint16(cursor + 8, true);
    const method = view.getUint16(cursor + 10, true);
    const compressedSize = view.getUint32(cursor + 20, true);
    const uncompressedSize = view.getUint32(cursor + 24, true);
    const nameLength = view.getUint16(cursor + 28, true);
    const extraLength = view.getUint16(cursor + 30, true);
    const commentLength = view.getUint16(cursor + 32, true);
    const localHeaderOffset = view.getUint32(cursor + 42, true);
    const next = cursor + 46 + nameLength + extraLength + commentLength;
    if (next > bytes.byteLength || nameLength < 1) return "ZIP_INVALID";
    if ((flags & 0x0001) !== 0) return "ZIP_ENCRYPTED";
    if (method !== 0 && method !== 8) return "ZIP_COMPRESSION_UNSUPPORTED";
    if (compressedSize === 0xffffffff || uncompressedSize === 0xffffffff || localHeaderOffset === 0xffffffff) return "ZIP64_UNSUPPORTED";
    const name = decodeZipName(bytes, cursor + 46, nameLength).replaceAll("\\", "/");
    if (name.startsWith("/") || name.split("/").includes("..") || name.includes("\0")) return "ZIP_ENTRY_NAME";
    if (seenNames.has(name)) return "ZIP_DUPLICATE_ENTRY";
    seenNames.add(name);
    if (localHeaderOffset + 30 > centralOffset || view.getUint32(localHeaderOffset, true) !== 0x04034b50) return "ZIP_INVALID";
    const localNameLength = view.getUint16(localHeaderOffset + 26, true);
    const localExtraLength = view.getUint16(localHeaderOffset + 28, true);
    const dataOffset = localHeaderOffset + 30 + localNameLength + localExtraLength;
    if (dataOffset + compressedSize > centralOffset) return "ZIP_INVALID";
    totalCompressed += compressedSize;
    totalUncompressed += uncompressedSize;
    if (totalUncompressed > limits.maxUncompressedBytes) return "ZIP_UNCOMPRESSED_LIMIT";
    if (uncompressedSize / Math.max(1, compressedSize) > limits.maxCompressionRatio) return "ZIP_COMPRESSION_RATIO";
    cursor = next;
  }
  if (cursor !== centralOffset + centralSize) return "ZIP_INVALID";
  if (totalUncompressed / Math.max(1, totalCompressed) > limits.maxCompressionRatio) return "ZIP_COMPRESSION_RATIO";
  return null;
}

export function neutralizeSpreadsheetCell(value) {
  if (typeof value !== "string") return value;
  return /^\s*[=+\-@]/.test(value) ? `'${value}` : value;
}

export function rowsToCsv(rows, columns) {
  const quote = (value) => {
    const safe = String(neutralizeSpreadsheetCell(value ?? ""));
    return /[",\r\n]/.test(safe) ? `"${safe.replaceAll('"', '""')}"` : safe;
  };
  return [columns, ...rows.map((row) => columns.map((column) => row[column]))]
    .map((row) => row.map(quote).join(","))
    .join("\r\n");
}

export function renderTable(table, rows, columns) {
  const doc = table.ownerDocument ?? globalThis.document;
  const head = doc.createElement("thead");
  const headRow = doc.createElement("tr");
  for (const column of columns) {
    const cell = doc.createElement("th");
    cell.textContent = column.replaceAll("_", " ");
    headRow.append(cell);
  }
  head.append(headRow);

  const body = doc.createElement("tbody");
  for (const row of rows) {
    const tableRow = doc.createElement("tr");
    for (const column of columns) {
      const cell = doc.createElement("td");
      cell.textContent = String(row[column] ?? "");
      tableRow.append(cell);
    }
    body.append(tableRow);
  }
  table.replaceChildren(head, body);
}

function csvRows(text) {
  const rows = [];
  let row = [];
  let value = "";
  let quoted = false;
  for (let index = 0; index < text.length; index += 1) {
    const character = text[index];
    if (character === '"') {
      if (quoted && text[index + 1] === '"') {
        value += '"';
        index += 1;
      } else {
        quoted = !quoted;
      }
    } else if (character === "," && !quoted) {
      row.push(value);
      value = "";
    } else if ((character === "\n" || character === "\r") && !quoted) {
      if (character === "\r" && text[index + 1] === "\n") index += 1;
      row.push(value);
      if (row.some((cell) => cell !== "")) rows.push(row);
      row = [];
      value = "";
    } else {
      value += character;
    }
  }
  row.push(value);
  if (row.some((cell) => cell !== "")) rows.push(row);
  if (quoted) throw new Error("CSV_QUOTE_NOT_CLOSED");
  return rows;
}

function cellValue(value) {
  if (value instanceof Date) return value.toISOString().slice(0, 10);
  if (value && typeof value === "object") {
    if ("result" in value) return value.result ?? "";
    if ("text" in value) return value.text;
    if (Array.isArray(value.richText)) return value.richText.map((part) => part.text).join("");
  }
  return value ?? "";
}

function inferredRole(headers) {
  if (headers.includes("cost_line_id") && headers.includes("amount_thb")) return "costs";
  if (headers.includes("document_id") && headers.includes("document_type")) return "documents";
  if (headers.includes("payment_id") || headers.includes("payment_invoice_id")) return "payments";
  if (headers.includes("invoice_id") && headers.includes("sales_amount")) return "invoices";
  if (headers.includes("canonical_field") && headers.includes("source_field")) return "mapping";
  if (headers.includes("metric") && headers.includes("definition")) return "definitions";
  if (headers.includes("field") && headers.includes("type")) return "dictionary";
  if (headers.includes("control") && headers.includes("value")) return "controls";
  if (headers.includes("lot_id") || headers.includes("lot_code")) return "lots";
  return null;
}

function roleForSheet(sheetName, headers = []) {
  return SHEET_ROLES[String(sheetName).trim().toLowerCase()] ?? inferredRole(headers);
}

function headerCandidate(values) {
  const headers = values.map((value) => String(cellValue(value) ?? "").trim());
  if (!headers.length || headers.every((header) => !header) || headers.some((header) => !header)) return null;
  return new Set(headers).size === headers.length ? headers : null;
}

export function recordsFromGrid(grid, fileName, sheetName, physicalRows = null) {
  if (!Array.isArray(grid) || !grid.length) throw new Error(`INVALID_HEADER:${sheetName}`);
  let headerIndex = -1;
  let headers = null;
  let role = SHEET_ROLES[String(sheetName).trim().toLowerCase()] ?? null;
  for (let index = 0; index < Math.min(grid.length, 25); index += 1) {
    const candidate = headerCandidate(grid[index]);
    if (!candidate) continue;
    const candidateRole = role ?? inferredRole(candidate);
    if (!candidateRole) continue;
    const required = ROLE_HEADER_FIELDS[candidateRole] ?? [];
    if (required.every((field) => candidate.includes(field) || FIELD_ALIASES[field]?.some((alias) => candidate.includes(alias)))) {
      headerIndex = index;
      headers = candidate;
      role = candidateRole;
      break;
    }
  }
  if (headerIndex < 0 || !headers || !role) throw new Error(`INVALID_HEADER:${sheetName}`);
  const rowNumbers = physicalRows ?? grid.map((_, index) => index + 1);
  const records = [];
  for (let index = headerIndex + 1; index < grid.length; index += 1) {
    const values = grid[index];
    if (!values.some((value) => cellValue(value) !== "")) continue;
    const record = Object.fromEntries(headers.map((header, column) => [header, cellValue(values[column])]));
    const sourceRow = rowNumbers[index];
    records.push({
      ...record,
      source_file: fileName,
      source_sheet: sheetName,
      source_row: sourceRow,
      source_key: String(record.source_key || `${fileName}|${sheetName}|${sourceRow}`),
    });
  }
  return { headers, records, role, headerRow: rowNumbers[headerIndex] };
}

export async function parseFile(file) {
  if (file.name.toLowerCase().endsWith(".csv")) {
    const grid = csvRows(await file.text());
    const shapeError = guardWorkbookShape(["CSV"], [Math.max(0, grid.length - 1)]);
    if (shapeError) throw new Error(shapeError);
    const parsed = recordsFromGrid(grid, file.name, "CSV");
    return {
      inventory: [{ source_file: file.name, source_sheet: "CSV", rows: parsed.records.length, role: parsed.role, status: "Prepared" }],
      preparedFile: { name: file.name, sheets: [{ name: "CSV", role: parsed.role, headerRow: parsed.headerRow, rows: parsed.records, headers: parsed.headers }] },
      headers: parsed.headers,
      mappingRows: parsed.role === "mapping" ? parsed.records : [],
    };
  }

  if (!globalThis.ExcelJS?.Workbook) throw new Error("EXCELJS_RUNTIME_MISSING");
  const archive = await file.arrayBuffer();
  const archiveError = preflightXlsxArchive(archive);
  if (archiveError) throw new Error(archiveError);
  const workbook = new globalThis.ExcelJS.Workbook();
  await workbook.xlsx.load(archive);
  const sheetNames = workbook.worksheets.map((sheet) => sheet.name);
  const rowCounts = workbook.worksheets.map((sheet) => sheet.rowCount);
  const shapeError = guardWorkbookShape(sheetNames, rowCounts);
  if (shapeError) throw new Error(shapeError);
  const inventory = [];
  const preparedSheets = [];
  const headers = new Set();
  const mappingRows = [];
  for (const sheet of workbook.worksheets) {
    if (String(sheet.name).trim().toLowerCase() === "source inventory") continue;
    const knownRole = roleForSheet(sheet.name);
    if (!knownRole) {
      inventory.push({ source_file: file.name, source_sheet: sheet.name, rows: Math.max(0, sheet.rowCount - 4), status: "Configuration / not transactional" });
      continue;
    }
    const grid = [];
    const physicalRows = [];
    for (let rowNumber = 1; rowNumber <= sheet.rowCount; rowNumber += 1) {
      grid.push(sheet.getRow(rowNumber).values.slice(1).map(cellValue));
      physicalRows.push(rowNumber);
    }
    if (!grid.length) continue;
    const parsed = recordsFromGrid(grid, file.name, sheet.name, physicalRows);
    parsed.headers.forEach((header) => headers.add(header));
    inventory.push({ source_file: file.name, source_sheet: sheet.name, rows: parsed.records.length, role: parsed.role, status: "Prepared" });
    preparedSheets.push({ name: sheet.name, role: parsed.role, headerRow: parsed.headerRow, rows: parsed.records, headers: parsed.headers });
    if (parsed.role === "mapping") mappingRows.push(...parsed.records);
  }
  return {
    inventory: applyAccountingInventoryPolicy(inventory),
    preparedFile: { name: file.name, sheets: preparedSheets },
    headers: [...headers],
    mappingRows,
  };
}

function preparedMapping(headers) {
  const headerSet = new Set(headers);
  const mapping = {};
  for (const field of CANONICAL_FIELDS) {
    const aliases = FIELD_ALIASES[field] ?? [field];
    const match = aliases.find((alias) => headerSet.has(alias));
    if (match) mapping[field] = match;
  }
  return mapping;
}

function renderInventory() {
  renderTable(document.querySelector("#source-inventory"), state.sources, ["source_file", "source_sheet", "rows", "status"]);
  const documentRows = state.sources.filter((row) => /document/i.test(row.source_sheet));
  renderTable(document.querySelector("#document-register"), documentRows, ["source_file", "source_sheet", "rows", "status"]);
}

export function mappingControlFields(entries = []) {
  if (!Array.isArray(entries) || entries.length === 0) return [...CANONICAL_FIELDS];
  return [...new Set(entries.map((entry) => String(entry?.canonical_field ?? "").trim()).filter(Boolean))];
}

export function renderMappingControls(container, mapping, headers, entries = [], onChange = () => {}) {
  container.replaceChildren();
  const doc = container.ownerDocument ?? globalThis.document;
  for (const field of mappingControlFields(entries)) {
    const label = doc.createElement("label");
    const text = doc.createElement("span");
    text.textContent = field;
    const select = doc.createElement("select");
    select.dataset.field = field;
    const empty = doc.createElement("option");
    empty.value = "";
    empty.textContent = "Not mapped";
    select.append(empty);
    for (const header of headers) {
      const option = doc.createElement("option");
      option.value = header;
      option.textContent = header;
      option.selected = (mapping.fields?.[field] ?? mapping[field]) === header;
      select.append(option);
    }
    select.addEventListener("change", () => onChange(field, select.value));
    label.append(text, select);
    container.append(label);
  }
}

function renderStateMappingControls() {
  renderMappingControls(
    document.querySelector("#mapping-controls"),
    state.mapping,
    state.headers,
    state.mappingConfig?.entries,
    (field, value) => {
      state.mapping = editMappingState(state.mapping, "", field, value);
      state.result = null;
      updateGate();
    },
  );
}

function updateGate(message = "Review prepared mappings, then approve.") {
  const approved = canCalculate(state.mapping);
  const badge = document.querySelector("#mapping-status");
  badge.textContent = state.mapping.status;
  badge.dataset.state = approved ? "approved" : "pending";
  document.querySelector("#calculate").disabled = !approved || state.sources.length === 0;
  document.querySelector("#export-xlsx").disabled = !canExportResult(state.mapping);
  document.querySelector("#export-csv").disabled = !canExportResult(state.mapping);
  document.querySelector("#status-message").textContent = message;
}

function renderKpis(kpis) {
  const metrics = [
    ["Landed cost", kpis.landed_cost, "THB"],
    ["Cost / carat", kpis.landed_cost_per_carat, "THB"],
    ["Gross margin", kpis.gross_margin, "THB"],
    ["Document completeness", kpis.document_completeness, "%"],
  ];
  const container = document.querySelector("#kpi-cards");
  container.replaceChildren();
  for (const [label, value, unit] of metrics) {
    const card = document.createElement("article");
    const name = document.createElement("span");
    name.textContent = label;
    const number = document.createElement("strong");
    number.textContent = value === null ? "Blocked" : unit === "%" ? `${(value * 100).toFixed(0)}%` : new Intl.NumberFormat("en-US", { maximumFractionDigits: 2 }).format(value);
    const suffix = document.createElement("small");
    suffix.textContent = unit === "%" ? "Required documents present" : unit;
    card.append(name, number, suffix);
    container.append(card);
  }
  const breakdown = Object.entries(kpis.sales_by_buyer_segment).map(([segment, amount]) => ({ segment, amount }));
  renderTable(document.querySelector("#breakdown"), breakdown, ["segment", "amount"]);
}

function renderExceptions(exceptions) {
  renderTable(document.querySelector("#exceptions"), exceptions, ["id", "code", "severity", "source_file", "source_sheet", "source_row", "message"]);
}

function addSheetRows(sheet, columns, rows) {
  sheet.columns = columns.map((key) => ({ header: key, key, width: Math.min(34, Math.max(12, key.length + 4)) }));
  for (const row of rows) {
    const safe = Object.fromEntries(columns.map((column) => [column, neutralizeSpreadsheetCell(row[column] ?? "")]));
    sheet.addRow(safe);
  }
  sheet.views = [{ state: "frozen", ySplit: 1 }];
  sheet.autoFilter = { from: { row: 1, column: 1 }, to: { row: 1, column: columns.length } };
  sheet.getRow(1).eachCell((cell) => {
    cell.font = { bold: true, color: { argb: "FFFFFFFF" } };
    cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFD81920" } };
  });
}

function normalizedColumns(rows) {
  const ordered = [...CANONICAL_FIELDS, "source_file", "source_sheet", "source_row", "source_key"];
  return ordered.filter((field) => rows.some((row) => row[field] !== undefined));
}

function total(values) {
  return Object.values(values ?? {}).reduce((sum, value) => sum + Number(value ?? 0), 0);
}

function lotCalculationRows(result) {
  if (Array.isArray(result.calculations?.lotMargins)) return result.calculations.lotMargins;
  const invoiceRevenue = new Map();
  for (const row of result.normalized.filter((item) => item.record_type === "invoice")) {
    invoiceRevenue.set(row.lot_id, (invoiceRevenue.get(row.lot_id) ?? 0) + Number(row.sales_amount ?? 0));
  }
  return result.normalized.filter((row) => row.record_type === "lot").map((row) => {
    const landedCost = ["purchase_cost", "import_cost", "cutting_cost", "certificate_cost", "logistics_cost"]
      .reduce((sum, field) => sum + Number(row[field] ?? 0), 0);
    const actualProcessing = Number(row.cutting_cost ?? 0) + Number(row.certificate_cost ?? 0);
    const revenue = invoiceRevenue.get(row.lot_id) ?? 0;
    return {
      ...row,
      landed_cost: landedCost,
      actual_processing_cost: actualProcessing,
      processing_cost_variance: actualProcessing - Number(row.expected_processing_cost ?? 0),
      revenue,
      gross_margin: revenue - landedCost,
      landed_cost_per_carat: Number(row.carat) > 0 ? landedCost / Number(row.carat) : null,
    };
  });
}

function summaryRows(result) {
  const { classification, controls, kpis } = result;
  const rows = [
    ["classification_input", classification.input, "rows", "Control", "Input physical canonical rows"],
    ["classification_included", classification.included, "rows", "Control", "Included normalized rows"],
    ["classification_duplicate", classification.duplicate, "rows", "Control", "Later stable-order duplicates"],
    ["classification_invalid", classification.invalid, "rows", "Control", "Invalid canonical rows"],
    ["classification_excluded", classification.excluded, "rows", "Control", "Explicitly excluded rows"],
    ["classification_difference", controls.classification_difference, "rows", "Check", "input - included - duplicate - invalid - excluded"],
    ["landed_cost", kpis.landed_cost, "THB", "KPI", "Cost-line-derived landed cost"],
    ["lot_count", kpis.lot_count, "lots", "KPI", "Included lot records"],
    ["landed_cost_per_lot", kpis.landed_cost_per_lot, "THB/lot", "KPI", "Landed cost / included lots"],
    ["actual_processing_cost", kpis.actual_processing_cost, "THB", "KPI", "Cutting + certificate"],
    ["expected_processing_cost", kpis.expected_processing_cost, "THB", "KPI", "Approved expected processing cost"],
    ["processing_cost_variance", kpis.processing_cost_variance, "THB", "KPI", "Actual - expected"],
    ["sales_amount", kpis.sales_amount, "THB", "KPI", "Invoices on or before as-of"],
    ["gross_margin", kpis.gross_margin, "THB", "KPI", "Sales - landed cost"],
    ["document_completeness", kpis.document_completeness, "ratio", "KPI", "Completed / required"],
    ...Object.entries(kpis.ar_aging).map(([bucket, value]) => [`ar_${bucket.toLowerCase().replaceAll(/[^a-z0-9]+/g, "_")}`, value, "THB", "AR aging", `Open balance · ${bucket}`]),
    ["open_ar", total(kpis.ar_aging), "THB", "KPI", "Sum of AR aging buckets"],
    ["exception_count", result.exceptions.length, "rows", "Control", "Traceable exception rows"],
    ...Object.entries(kpis.sales_by_buyer_segment).map(([segment, value]) => [`sales_segment:${segment}`, value, "THB", "Buyer segment", "Sales on or before as-of"]),
    ...Object.entries(kpis.gross_margin_by_product).map(([product, value]) => [`gross_margin_product:${product}`, value, "THB", "Product margin", "Revenue less lot cost by product"]),
  ];
  return rows.map(([metric, value, unit, classificationName, notes]) => ({
    metric,
    value,
    unit,
    classification: classificationName,
    status: metric === "classification_difference" ? (value === 0 ? "PASS" : "FAIL") : "REVIEW",
    notes,
  }));
}

export async function buildBrowserExportBuffer(ExcelJSRuntime, result, inventory = []) {
  if (!ExcelJSRuntime?.Workbook) throw new Error("EXCELJS_RUNTIME_MISSING");
  if (!result?.normalized || !result?.classification || !result?.controls || !result?.kpis) throw new Error("RESULT_NOT_CALCULATED");
  const workbook = new ExcelJSRuntime.Workbook();
  workbook.creator = "ZenityX Training";
  workbook.created = new Date("2026-08-09T00:00:00Z");
  workbook.modified = new Date("2026-08-09T00:00:00Z");
  const sheets = Object.fromEntries(EXPORT_SHEET_NAMES.map((name) => [name, workbook.addWorksheet(name)]));
  addSheetRows(sheets["Source Inventory"], ["source_file", "source_sheet", "rows", "role", "status"], applyAccountingInventoryPolicy(inventory));

  addSheetRows(sheets["Data Dictionary"], ["field", "type", "owner", "version", "definition"], kpiDefinitions.fields);
  const normalized = result.normalized;
  addSheetRows(sheets["Normalized Data"], normalizedColumns(normalized), normalized);

  const lotRows = lotCalculationRows(result);
  const arRows = result.calculations?.arRows ?? [];
  const productRows = result.calculations?.productMargins ?? [];
  const controlRows = [
    ["classification_input", result.classification.input],
    ["classification_included", result.classification.included],
    ["classification_duplicate", result.classification.duplicate],
    ["classification_invalid", result.classification.invalid],
    ["classification_excluded", result.classification.excluded],
    ["classification_difference", result.controls.classification_difference],
  ].map(([control_name, control_value]) => ({ calculation_type: "CLASSIFICATION", control_name, control_value, control_status: control_name === "classification_difference" ? (control_value === 0 ? "PASS" : "FAIL") : "CONTROL" }));
  const calculationColumns = [
    "calculation_type", "source_file", "source_sheet", "source_row", "source_key", "lot_id", "invoice_id", "product",
    "purchase_cost", "import_cost", "cutting_cost", "certificate_cost", "logistics_cost", "landed_cost",
    "expected_processing_cost", "actual_processing_cost", "processing_cost_variance", "revenue", "gross_margin", "carat",
    "landed_cost_per_carat", "due_date", "sales_amount", "applied_payments", "open_balance", "days_past_due", "aging_bucket",
    "control_name", "control_value", "control_status",
  ];
  const calculationRows = [
    ...lotRows.map((row) => ({ calculation_type: "LOT_MARGIN", ...row })),
    ...arRows.map((row) => ({ calculation_type: "AR_AGING", ...row })),
    ...productRows.map((row) => ({ calculation_type: "PRODUCT_MARGIN", ...row })),
    ...controlRows,
  ];
  addSheetRows(sheets.Calculations, calculationColumns, calculationRows);
  for (let index = 0; index < lotRows.length; index += 1) {
    const sheetRow = index + 2;
    const row = lotRows[index];
    sheets.Calculations.getCell(`N${sheetRow}`).value = { formula: `SUM(I${sheetRow}:M${sheetRow})`, result: row.landed_cost };
    sheets.Calculations.getCell(`P${sheetRow}`).value = { formula: `K${sheetRow}+L${sheetRow}`, result: row.actual_processing_cost };
    sheets.Calculations.getCell(`Q${sheetRow}`).value = { formula: `P${sheetRow}-O${sheetRow}`, result: row.processing_cost_variance };
    sheets.Calculations.getCell(`S${sheetRow}`).value = { formula: `R${sheetRow}-N${sheetRow}`, result: row.gross_margin };
    sheets.Calculations.getCell(`U${sheetRow}`).value = { formula: `IF(T${sheetRow}=0,"",N${sheetRow}/T${sheetRow})`, result: row.landed_cost_per_carat ?? "" };
  }

  const reportSummaryRows = summaryRows(result);
  addSheetRows(sheets.Summary, ["metric", "value", "unit", "classification", "status", "notes"], reportSummaryRows);
  sheets.Summary.getCell("B7").value = { formula: "B2-SUM(B3:B6)", result: result.controls.classification_difference };
  sheets.Summary.getCell("B10").value = { formula: 'IF(B9=0,"",B8/B9)', result: result.kpis.landed_cost_per_lot ?? "" };
  sheets.Summary.getCell("B13").value = { formula: "B11-B12", result: result.kpis.processing_cost_variance };
  sheets.Summary.getCell("B15").value = { formula: "B14-B8", result: result.kpis.gross_margin };
  sheets.Summary.getCell("B22").value = { formula: "SUM(B17:B21)", result: total(result.kpis.ar_aging) };

  const calculationLastRow = calculationRows.length + 1;
  const segmentRows = reportSummaryRows
    .map((row, index) => ({ row, sheetRow: index + 2 }))
    .filter(({ row }) => row.classification === "Buyer segment");
  const segmentRange = segmentRows.length
    ? `B${segmentRows[0].sheetRow}:B${segmentRows.at(-1).sheetRow}`
    : "B1:B1";
  const classification = result.classification;
  const componentTotal = classification.included + classification.duplicate + classification.invalid + classification.excluded;
  const arTotal = total(result.kpis.ar_aging);
  const arRowsTotal = arRows.reduce((sum, row) => sum + Number(row.open_balance ?? 0), 0);
  const lotMarginTotal = lotRows.reduce((sum, row) => sum + Number(row.gross_margin ?? 0), 0);
  const productMarginTotal = productRows.reduce((sum, row) => sum + Number(row.gross_margin ?? 0), 0);
  const segmentTotal = total(result.kpis.sales_by_buyer_segment);
  const controlSpecs = [
    ["classification", "B2", "SUM(B3:B6)", classification.input, componentTotal, 0, "Summary B2:B7"],
    ["AR buckets", "B22", `SUMIFS('Calculations'!$Y$2:$Y$${calculationLastRow},'Calculations'!$A$2:$A$${calculationLastRow},"AR_AGING")`, arTotal, arRowsTotal, 0.01, "Calculations AR_AGING"],
    ["lot margin", "B15", `SUMIFS('Calculations'!$S$2:$S$${calculationLastRow},'Calculations'!$A$2:$A$${calculationLastRow},"LOT_MARGIN")`, result.kpis.gross_margin, lotMarginTotal, 0.01, "Calculations LOT_MARGIN"],
    ["product margin", "B15", `SUMIFS('Calculations'!$S$2:$S$${calculationLastRow},'Calculations'!$A$2:$A$${calculationLastRow},"PRODUCT_MARGIN")`, result.kpis.gross_margin, productMarginTotal, 0.01, "Calculations PRODUCT_MARGIN"],
    ["landed cost / lot", "B10", 'IF(B9=0,0,B8/B9)', result.kpis.landed_cost_per_lot ?? 0, result.kpis.landed_cost_per_lot ?? 0, 0.01, "Summary B8:B10"],
    ["sales by segment", "B14", `SUM(${segmentRange})`, result.kpis.sales_amount, segmentTotal, 0.01, `Summary ${segmentRange}`],
    ["processing variance", "B13", "B11-B12", result.kpis.processing_cost_variance, result.kpis.processing_cost_variance, 0.01, "Summary B11:B13"],
  ];
  const controlHeaders = ["check", "actual", "expected", "difference", "tolerance", "status", "where_to_fix"];
  controlHeaders.forEach((header, index) => {
    const cell = sheets.Summary.getCell(1, index + 8);
    cell.value = header;
    cell.font = { bold: true, color: { argb: "FFFFFFFF" } };
    cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFD81920" } };
  });
  controlSpecs.forEach(([label, actualFormula, expectedFormula, actual, expected, tolerance, where], index) => {
    const row = index + 2;
    const difference = Math.round((Number(actual ?? 0) - Number(expected ?? 0)) * 100) / 100;
    sheets.Summary.getCell(`H${row}`).value = label;
    sheets.Summary.getCell(`I${row}`).value = { formula: actualFormula, result: actual };
    sheets.Summary.getCell(`J${row}`).value = { formula: expectedFormula, result: expected };
    sheets.Summary.getCell(`K${row}`).value = { formula: `I${row}-J${row}`, result: difference };
    sheets.Summary.getCell(`L${row}`).value = tolerance;
    sheets.Summary.getCell(`M${row}`).value = { formula: `IF(ABS(K${row})<=L${row},"PASS","FAIL")`, result: Math.abs(difference) <= tolerance ? "PASS" : "FAIL" };
    sheets.Summary.getCell(`N${row}`).value = where;
  });
  sheets.Summary.getColumn("H").width = 24;
  sheets.Summary.getColumn("N").width = 32;

  const dashboardRows = reportSummaryRows.filter((row) => row.classification === "KPI" || row.classification === "AR aging" || row.classification === "Product margin" || row.classification === "Buyer segment" || row.metric.startsWith("classification_"));
  addSheetRows(sheets.Dashboard, ["metric", "value", "unit", "classification", "status"], dashboardRows);
  for (let row = 2; row <= dashboardRows.length + 1; row += 1) {
    const summaryIndex = reportSummaryRows.findIndex((item) => item.metric === dashboardRows[row - 2].metric);
    sheets.Dashboard.getCell(`B${row}`).value = { formula: `'Summary'!B${summaryIndex + 2}`, result: dashboardRows[row - 2].value };
  }
  addSheetRows(sheets.Exceptions, ["id", "code", "severity", "classification", "source_file", "source_sheet", "source_row", "source_key", "expected_disposition", "message"], result.exceptions);

  return workbook.xlsx.writeBuffer();
}

async function exportWorkbook() {
  if (!canExportResult(state.mapping) || !globalThis.ExcelJS?.Workbook) return;
  const buffer = await buildBrowserExportBuffer(globalThis.ExcelJS, state.result, state.sources);
  downloadBlob(new Blob([buffer], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" }), "Accounting-Insights-Report.xlsx");
}

function downloadBlob(blob, name) {
  const url = URL.createObjectURL(blob);
  const link = document.createElement("a");
  link.href = url;
  link.download = name;
  link.click();
  URL.revokeObjectURL(url);
}

async function onFilesSelected(event) {
  const files = [...event.target.files];
  if (validateSelectedFileCount(files.length)) {
    updateGate("Choose one to three prepared CSV/XLSX files.");
    return;
  }
  const fileError = files.map(validateUploadFile).find(Boolean);
  if (fileError) {
    updateGate(`Upload blocked: ${fileError}`);
    return;
  }
  try {
    const parsed = [];
    for (const file of files) parsed.push(await parseFile(file));
    state.files = files;
    state.sources = applyAccountingInventoryPolicy(parsed.flatMap((item) => item.inventory));
    state.preparedFiles = parsed.map((item) => item.preparedFile);
    state.headers = [...new Set(parsed.flatMap((item) => item.headers))].sort();
    const declaredRows = parsed.flatMap((item) => item.mappingRows);
    const inferredMapping = preparedMapping(state.headers);
    const declaredMapping = declaredRows.length ? mappingRowsToConfig(declaredRows) : null;
    state.mappingConfig = declaredMapping;
    state.mapping = createMappingState({ ...inferredMapping, ...(declaredMapping?.fields ?? {}) }, declaredMapping?.version ?? "1.0");
    state.result = null;
    renderInventory();
    renderStateMappingControls();
    updateGate("Prepared mapping is Pending. Review every field and select Approve mapping.");
  } catch (error) {
    updateGate(`Upload blocked: ${error.message}`);
  }
}

function approveMapping() {
  state.mapping = approveMappingState(state.mapping);
  updateGate("Mapping Approved. Calculation is enabled; source files remain untouched.");
}

function calculate() {
  if (!canCalculate(state.mapping)) return;
  const mappingConfig = state.mappingConfig
    ? applyMappingFieldOverrides(state.mappingConfig, state.mapping.fields)
    : {
      status: "Approved",
      version: state.mapping.version,
      fields: state.mapping.fields,
      entries: Object.entries(state.mapping.fields).map(([canonical_field, source_field]) => ({ canonical_field, source_field, status: "Approved" })),
      missing: [],
      missingRequired: [],
    };
  const result = ingestPreparedWorkbooks(state.preparedFiles, mappingConfig, new Date("2026-08-31T00:00:00Z"));
  state.mapping = recordCalculationState(state.mapping, result);
  state.result = result;
  renderKpis(result.kpis);
  renderExceptions(result.exceptions);
  updateGate(`Calculated ${result.normalized.length} included rows; ${result.exceptions.length} exceptions require review.`);
}

function exportExceptionCsv() {
  if (!canExportResult(state.mapping) || !state.result) return;
  const columns = ["id", "code", "severity", "source_file", "source_sheet", "source_row", "source_key", "message"];
  downloadBlob(new Blob([rowsToCsv(state.result.exceptions, columns)], { type: "text/csv;charset=utf-8" }), "Accounting-Exceptions.csv");
}

function initialize() {
  renderTable(document.querySelector("#data-dictionary"), kpiDefinitions.fields, ["field", "type", "owner", "version", "definition"]);
  document.querySelector("#files").addEventListener("change", onFilesSelected);
  document.querySelector("#approve-mapping").addEventListener("click", approveMapping);
  document.querySelector("#calculate").addEventListener("click", calculate);
  document.querySelector("#export-xlsx").addEventListener("click", exportWorkbook);
  document.querySelector("#export-csv").addEventListener("click", exportExceptionCsv);
  updateGate();
}

if (typeof document !== "undefined") initialize();
