import { applyAccountingInventoryPolicy } from "./accounting-contract.mjs";

const REQUIRED_MAPPING_FIELDS = Object.freeze({
  lot: ["lot_id", "purchase_cost"],
  invoice: ["invoice_id", "sales_amount", "due_date"],
  payment: ["payment_id", "payment_invoice_id", "payment_amount"],
});

const PIPELINE_REQUIRED_MAPPING_FIELDS = Object.freeze({
  lot: ["lot_id", "product", "carat", "purchase_cost", "expected_processing_cost"],
  invoice: ["invoice_id", "lot_id", "invoice_date", "due_date", "sales_amount"],
  payment: ["payment_id", "payment_invoice_id", "payment_date", "payment_amount"],
});

const NUMERIC_FIELDS = Object.freeze([
  "purchase_cost",
  "import_cost",
  "cutting_cost",
  "certificate_cost",
  "logistics_cost",
  "expected_processing_cost",
  "sales_amount",
  "payment_amount",
  "carat",
  "required_documents",
  "completed_documents",
]);

const DATE_FIELDS = Object.freeze(["received_date", "invoice_date", "due_date", "payment_date"]);
const COST_FIELDS = Object.freeze(["purchase_cost", "import_cost", "cutting_cost", "certificate_cost", "logistics_cost"]);
const COST_TYPE_FIELDS = Object.freeze({
  purchase: "purchase_cost",
  import: "import_cost",
  cutting: "cutting_cost",
  certificate: "certificate_cost",
  logistics: "logistics_cost",
});
const EXCEPTION_ORDER = Object.freeze({
  DUPLICATE_SOURCE_KEY: 10,
  INVALID_DATE: 20,
  MISSING_MAPPING: 30,
  UNMATCHED_PAYMENT: 40,
  ZERO_CARAT: 50,
});

function round2(value) {
  return Math.round((value + Number.EPSILON) * 100) / 100;
}

function isIsoDate(value) {
  if (typeof value !== "string" || !/^\d{4}-\d{2}-\d{2}$/.test(value)) return false;
  const parsed = new Date(`${value}T00:00:00Z`);
  return !Number.isNaN(parsed.getTime()) && parsed.toISOString().slice(0, 10) === value;
}

function isoDateValue(value) {
  if (value instanceof Date && !Number.isNaN(value.getTime())) return value.toISOString().slice(0, 10);
  return String(value ?? "").trim();
}

export function coerceAccountingField(field, value) {
  if (NUMERIC_FIELDS.includes(field)) {
    if (typeof value === "number") return Number.isFinite(value) ? value : Number.NaN;
    const text = String(value ?? "").trim();
    if (!text || /^[=+@]/.test(text)) return Number.NaN;
    const validEnglishNumber = /^-?(?:(?:0|[1-9]\d*)(?:\.\d+)?|(?:[1-9]\d{0,2}(?:,\d{3})+)(?:\.\d+)?|\.\d+)$/.test(text);
    if (!validEnglishNumber) return Number.NaN;
    const parsed = Number(text.replaceAll(",", ""));
    return Number.isFinite(parsed) ? parsed : Number.NaN;
  }
  if (DATE_FIELDS.includes(field)) return isoDateValue(value);
  return String(value ?? "");
}

function asNumber(value) {
  if (value === "" || value === null || value === undefined) return 0;
  if (typeof value === "number") return Number.isFinite(value) ? value : Number.NaN;
  return coerceAccountingField("purchase_cost", value);
}

function numericOrZero(value) {
  const number = asNumber(value);
  return Number.isFinite(number) ? number : 0;
}

function rowType(row) {
  if (["lot", "invoice", "payment"].includes(row.record_type)) return row.record_type;
  if (row.payment_id || row.payment_invoice_id) return "payment";
  if (row.invoice_id) return "invoice";
  return "lot";
}

function mappingFields(mapping = {}) {
  return mapping.fields && typeof mapping.fields === "object" ? mapping.fields : mapping;
}

function mappingForRow(mapping, row) {
  const scoped = mapping.files?.[row.source_file]
    ?? mapping.sheets?.[row.source_sheet]
    ?? mapping;
  return mappingFields(scoped);
}

function mappedValue(row, mapping, field) {
  const sourceField = mapping[field];
  if (typeof sourceField === "string" && sourceField.length > 0) return row[sourceField];
  return row[field];
}

function requiredMappingsPresent(mapping, recordType) {
  return REQUIRED_MAPPING_FIELDS[recordType].every((field) => {
    const sourceField = mapping[field];
    return typeof sourceField === "string" && sourceField.trim().length > 0;
  });
}

function businessKey(row, recordType) {
  if (row.business_key !== undefined) return String(row.business_key ?? "").trim();
  const field = recordType === "invoice" ? "invoice_id" : recordType === "payment" ? "payment_id" : "lot_id";
  return String(row[field] ?? "").trim();
}

function exceptionFor(code, row, message, index = 0) {
  const trace = buildSourceTrace(row);
  const sourceKey = trace.source_key || `input-${index + 1}`;
  return {
    id: `${code}:${sourceKey}`,
    code,
    severity: "BLOCKED",
    message,
    ...trace,
  };
}

export function buildSourceTrace(row = {}) {
  return {
    source_file: row.source_file ?? "",
    source_sheet: row.source_sheet ?? "",
    source_row: row.source_row ?? null,
    source_key: row.source_key ?? "",
  };
}

export function normalizeBatch(sources, approvedMapping) {
  if (!Array.isArray(sources) || sources.length < 1 || sources.some((source) => !Array.isArray(source))) {
    throw new TypeError("sources must contain one or more row arrays");
  }

  const rows = sources.flat();
  const result = {
    inputCount: rows.length,
    included: [],
    duplicate: [],
    invalid: [],
    excluded: [],
    exceptions: [],
  };
  const mappingApproved = typeof approvedMapping?.status === "string"
    && approvedMapping.status.toLowerCase() === "approved";
  const seenSourceKeys = new Set();
  const seenBusinessKeys = new Set();

  rows.forEach((sourceRow, index) => {
    const original = sourceRow && typeof sourceRow === "object" && !Array.isArray(sourceRow) ? sourceRow : {};
    const scopedMapping = mappingForRow(approvedMapping ?? {}, original);
    const recordType = rowType(original);
    const normalized = { ...original, record_type: recordType };

    for (const field of Object.keys(scopedMapping)) {
      if (["status", "version", "files", "sheets"].includes(field)) continue;
      if (typeof scopedMapping[field] !== "string") continue;
      const value = mappedValue(original, scopedMapping, field);
      normalized[field] = [...NUMERIC_FIELDS, ...DATE_FIELDS].includes(field)
        ? coerceAccountingField(field, value)
        : value;
    }
    normalized.business_key = businessKey(normalized, recordType);

    if (!mappingApproved) {
      result.excluded.push(normalized);
      result.exceptions.push(exceptionFor("MAPPING_NOT_APPROVED", normalized, "Approve the prepared mapping before calculation.", index));
      return;
    }
    if (!requiredMappingsPresent(scopedMapping, recordType)) {
      result.excluded.push(normalized);
      result.exceptions.push(exceptionFor("MISSING_MAPPING", normalized, `Complete the ${recordType} field mapping before calculation.`, index));
      return;
    }
    if (normalized.include === false || String(normalized.disposition ?? "").toLowerCase() === "excluded") {
      result.excluded.push(normalized);
      return;
    }

    const trace = buildSourceTrace(normalized);
    const invalidTrace = !trace.source_file
      || !trace.source_sheet
      || !Number.isInteger(trace.source_row)
      || trace.source_row < 1
      || !trace.source_key;
    const invalidNumber = NUMERIC_FIELDS.some((field) => normalized[field] !== undefined
      && (!Number.isFinite(asNumber(normalized[field])) || asNumber(normalized[field]) < 0));
    const invalidDate = DATE_FIELDS.some((field) => normalized[field] !== undefined
      && normalized[field] !== ""
      && !isIsoDate(normalized[field]));
    if (!normalized.business_key || invalidTrace || invalidNumber || invalidDate) {
      result.invalid.push(normalized);
      const code = !normalized.business_key ? "MISSING_BUSINESS_KEY"
        : invalidTrace ? "INVALID_SOURCE_TRACE"
          : invalidNumber ? "INVALID_NUMBER"
            : "INVALID_DATE";
      result.exceptions.push(exceptionFor(code, normalized, "Correct the invalid source row before calculation.", index));
      return;
    }

    if (seenSourceKeys.has(trace.source_key)) {
      result.duplicate.push(normalized);
      result.exceptions.push(exceptionFor("DUPLICATE_SOURCE_KEY", normalized, "The first row in stable source order is retained.", index));
      return;
    }
    const stableBusinessKey = `${recordType}:${normalized.business_key}`;
    if (seenBusinessKeys.has(stableBusinessKey)) {
      result.duplicate.push(normalized);
      result.exceptions.push(exceptionFor("DUPLICATE_BUSINESS_KEY", normalized, "The first row in stable source order is retained.", index));
      seenSourceKeys.add(trace.source_key);
      return;
    }

    seenSourceKeys.add(trace.source_key);
    seenBusinessKeys.add(stableBusinessKey);
    result.included.push(normalized);
  });

  return result;
}

function addByKey(target, key, value) {
  const label = String(key || "Unspecified");
  target[label] = round2((target[label] ?? 0) + value);
}

function agingBucket(daysPastDue) {
  if (daysPastDue <= 0) return "Current";
  if (daysPastDue <= 30) return "1-30";
  if (daysPastDue <= 60) return "31-60";
  if (daysPastDue <= 90) return "61-90";
  return "90+";
}

function asOfDay(value) {
  const date = value instanceof Date ? new Date(value.getTime()) : new Date(value);
  if (Number.isNaN(date.getTime())) throw new TypeError("asOfDate must be a valid date");
  date.setUTCHours(0, 0, 0, 0);
  return date;
}

export function calculateAccountingKpis(rows, asOfDate) {
  if (!Array.isArray(rows)) throw new TypeError("rows must be an array");
  const asOf = asOfDay(asOfDate);
  const totals = {
    purchase_cost: 0,
    import_cost: 0,
    cutting_cost: 0,
    certificate_cost: 0,
    logistics_cost: 0,
    expected_processing_cost: 0,
    sales_amount: 0,
    carat: 0,
    required_documents: 0,
    completed_documents: 0,
  };
  const trace = [];
  const traceKeys = new Set();
  const exceptions = [];
  const salesBySegment = {};
  const revenueByLot = {};
  const costByLot = {};
  const revenueByProduct = {};
  const costByProduct = {};
  const activeInvoices = new Map();
  const knownInvoiceIds = new Set();
  const payments = [];
  const paymentsByInvoice = new Map();
  const lotComponents = new Map();
  let hasDocumentControls = false;
  let lotCount = 0;

  rows.forEach((row, index) => {
    const recordType = rowType(row);
    const rowTrace = buildSourceTrace(row);
    const traceIdentity = rowTrace.source_key || JSON.stringify(rowTrace);
    if (!traceKeys.has(traceIdentity)) {
      traceKeys.add(traceIdentity);
      trace.push(rowTrace);
    }

    if (recordType === "lot") {
      lotCount += 1;
      const components = Object.fromEntries(COST_FIELDS.map((field) => [field, numericOrZero(row[field])]));
      const rowCost = COST_FIELDS.reduce((sum, field) => sum + components[field], 0);
      for (const field of [...COST_FIELDS, "expected_processing_cost", "carat"]) totals[field] += numericOrZero(row[field]);
      if (row.required_documents !== undefined || row.completed_documents !== undefined) {
        hasDocumentControls = true;
        totals.required_documents += numericOrZero(row.required_documents);
        totals.completed_documents += numericOrZero(row.completed_documents);
      }
      addByKey(costByLot, row.lot_id, rowCost);
      addByKey(costByProduct, row.product, rowCost);
      lotComponents.set(String(row.lot_id ?? ""), { row, components, rowCost });
      if (numericOrZero(row.carat) <= 0) {
        exceptions.push(exceptionFor("ZERO_CARAT", row, "Landed cost per carat cannot be calculated for a zero-carat lot.", index));
      }
      return;
    }

    if (recordType === "invoice") {
      const invoiceId = String(row.invoice_id ?? "");
      knownInvoiceIds.add(invoiceId);
      const invalidDate = !isIsoDate(row.due_date) || (row.invoice_date !== undefined && row.invoice_date !== "" && !isIsoDate(row.invoice_date));
      if (invalidDate) {
        exceptions.push(exceptionFor("INVALID_DATE", row, "Use valid ISO invoice and due dates.", index));
        return;
      }
      const invoiceDate = row.invoice_date ? new Date(`${row.invoice_date}T00:00:00Z`) : null;
      if (invoiceDate && invoiceDate.getTime() > asOf.getTime()) {
        exceptions.push(exceptionFor("FUTURE_INVOICE", row, "Exclude invoices dated after the selected as-of date.", index));
        return;
      }
      if (!activeInvoices.has(invoiceId)) activeInvoices.set(invoiceId, row);
      const sales = numericOrZero(row.sales_amount);
      totals.sales_amount += sales;
      addByKey(salesBySegment, row.buyer_segment, sales);
      addByKey(revenueByLot, row.lot_id, sales);
      addByKey(revenueByProduct, row.product, sales);
      return;
    }

    if (recordType === "payment") payments.push({ row, index });
  });

  for (const { row, index } of payments) {
    const invoiceId = String(row.payment_invoice_id ?? "");
    if (!knownInvoiceIds.has(invoiceId)) {
      exceptions.push(exceptionFor("UNMATCHED_PAYMENT", row, "Match the payment to an included invoice before AR calculation.", index));
      continue;
    }
    if (!isIsoDate(row.payment_date)) {
      exceptions.push(exceptionFor("INVALID_DATE", row, "Use a valid ISO payment date.", index));
      continue;
    }
    if (!activeInvoices.has(invoiceId)) continue;
    const paymentDate = new Date(`${row.payment_date}T00:00:00Z`);
    if (paymentDate.getTime() <= asOf.getTime()) {
      paymentsByInvoice.set(invoiceId, round2((paymentsByInvoice.get(invoiceId) ?? 0) + numericOrZero(row.payment_amount)));
    }
  }

  const arAging = { Current: 0, "1-30": 0, "31-60": 0, "61-90": 0, "90+": 0 };
  const arRows = [];
  for (const invoice of activeInvoices.values()) {
    const sales = numericOrZero(invoice.sales_amount);
    const appliedPayments = paymentsByInvoice.get(invoice.invoice_id) ?? 0;
    const openBalance = round2(Math.max(0, sales - appliedPayments));
    const dueDate = new Date(`${invoice.due_date}T00:00:00Z`);
    const daysPastDue = Math.floor((asOf.getTime() - dueDate.getTime()) / 86400000);
    const bucket = agingBucket(daysPastDue);
    arAging[bucket] = round2(arAging[bucket] + openBalance);
    arRows.push({
      invoice_id: invoice.invoice_id,
      lot_id: invoice.lot_id ?? "",
      product: invoice.product ?? "",
      buyer_segment: invoice.buyer_segment ?? "",
      invoice_date: invoice.invoice_date ?? "",
      due_date: invoice.due_date,
      sales_amount: sales,
      applied_payments: appliedPayments,
      open_balance: openBalance,
      days_past_due: daysPastDue,
      aging_bucket: bucket,
      ...buildSourceTrace(invoice),
    });
  }

  const landedCost = round2(COST_FIELDS.reduce((sum, field) => sum + totals[field], 0));
  const actualProcessingCost = round2(totals.cutting_cost + totals.certificate_cost);
  const totalCarat = round2(totals.carat);
  const documentCompleteness = hasDocumentControls && totals.required_documents > 0
    ? round2(totals.completed_documents / totals.required_documents)
    : null;
  if (hasDocumentControls && totals.required_documents === 0) {
    const row = rows.find((item) => item.required_documents !== undefined) ?? {};
    exceptions.push(exceptionFor("ZERO_DOCUMENT_DENOMINATOR", row, "Document completeness needs at least one required document."));
  }

  const grossMarginByLot = {};
  for (const key of new Set([...Object.keys(revenueByLot), ...Object.keys(costByLot)])) {
    grossMarginByLot[key] = round2((revenueByLot[key] ?? 0) - (costByLot[key] ?? 0));
  }
  const grossMarginByProduct = {};
  for (const key of new Set([...Object.keys(revenueByProduct), ...Object.keys(costByProduct)])) {
    grossMarginByProduct[key] = round2((revenueByProduct[key] ?? 0) - (costByProduct[key] ?? 0));
  }

  const lotMargins = [...lotComponents.entries()].map(([lotId, { row, components, rowCost }]) => {
    const actual = round2(components.cutting_cost + components.certificate_cost);
    const expected = round2(numericOrZero(row.expected_processing_cost));
    const revenue = round2(revenueByLot[lotId] ?? 0);
    return {
      lot_id: lotId,
      product: row.product ?? "",
      ...components,
      landed_cost: round2(rowCost),
      carat: numericOrZero(row.carat),
      actual_processing_cost: actual,
      expected_processing_cost: expected,
      processing_cost_variance: round2(actual - expected),
      revenue,
      gross_margin: round2(revenue - rowCost),
      ...buildSourceTrace(row),
    };
  });
  const productMargins = [...new Set([...Object.keys(revenueByProduct), ...Object.keys(costByProduct)])].map((product) => ({
    product,
    revenue: round2(revenueByProduct[product] ?? 0),
    landed_cost: round2(costByProduct[product] ?? 0),
    gross_margin: round2((revenueByProduct[product] ?? 0) - (costByProduct[product] ?? 0)),
  }));

  return {
    purchase_cost: round2(totals.purchase_cost),
    import_cost: round2(totals.import_cost),
    cutting_cost: round2(totals.cutting_cost),
    certificate_cost: round2(totals.certificate_cost),
    logistics_cost: round2(totals.logistics_cost),
    landed_cost: landedCost,
    lot_count: lotCount,
    total_carat: totalCarat,
    landed_cost_per_lot: lotCount ? round2(landedCost / lotCount) : null,
    landed_cost_per_carat: totalCarat > 0 ? round2(landedCost / totalCarat) : null,
    actual_processing_cost: actualProcessingCost,
    expected_processing_cost: round2(totals.expected_processing_cost),
    processing_cost_variance: round2(actualProcessingCost - totals.expected_processing_cost),
    sales_amount: round2(totals.sales_amount),
    gross_margin: round2(totals.sales_amount - landedCost),
    sales_by_buyer_segment: salesBySegment,
    gross_margin_by_lot: grossMarginByLot,
    gross_margin_by_product: grossMarginByProduct,
    ar_aging: arAging,
    document_completeness: documentCompleteness,
    lot_margins: lotMargins,
    product_margins: productMargins,
    ar_rows: arRows,
    trace,
    exceptions,
  };
}

function stableValue(value) {
  if (Array.isArray(value)) return value.map(stableValue);
  if (!value || typeof value !== "object") return value;
  return Object.fromEntries(Object.keys(value).sort().map((key) => [key, stableValue(value[key])]));
}

function stableJson(value) {
  return JSON.stringify(stableValue(value));
}

function hashText(value) {
  let hash = 2166136261;
  for (let index = 0; index < value.length; index += 1) {
    hash ^= value.charCodeAt(index);
    hash = Math.imul(hash, 16777619);
  }
  return `map-${(hash >>> 0).toString(16).padStart(8, "0")}`;
}

function mappingStatePayload(state) {
  return {
    fields: state.fields ?? {},
    sheets: state.sheets ?? {},
    files: state.files ?? {},
  };
}

function nextVersion(version) {
  const parts = String(version || "1.0").split(".");
  const last = Number(parts.at(-1));
  if (!Number.isInteger(last) || last < 0) return `${version || "1.0"}.1`;
  parts[parts.length - 1] = String(last + 1);
  return parts.join(".");
}

export function createMappingState(fields = {}, version = "1.0") {
  const state = {
    status: "Pending",
    version: String(version),
    fields: { ...fields },
    sheets: {},
    files: {},
    result: null,
    calculated_signature: null,
    approved_signature: null,
  };
  return { ...state, signature: hashText(stableJson(mappingStatePayload(state))) };
}

export function editMappingState(state, scope, field, sourceField) {
  if (!state || typeof state !== "object") throw new TypeError("state must be a mapping state");
  const next = {
    ...state,
    fields: { ...(state.fields ?? {}) },
    sheets: Object.fromEntries(Object.entries(state.sheets ?? {}).map(([key, value]) => [key, { ...value }])),
    files: Object.fromEntries(Object.entries(state.files ?? {}).map(([key, value]) => [key, { ...value }])),
    status: "Pending",
    version: nextVersion(state.version),
    result: null,
    calculated_signature: null,
    approved_signature: null,
  };
  const scopeName = String(scope ?? "").trim();
  const target = scopeName ? (next.sheets[scopeName] ??= {}) : next.fields;
  if (sourceField === undefined || sourceField === null || String(sourceField).trim() === "") delete target[field];
  else target[field] = String(sourceField);
  next.signature = hashText(stableJson(mappingStatePayload(next)));
  return next;
}

export function approveMappingState(state) {
  if (!state || typeof state !== "object") throw new TypeError("state must be a mapping state");
  return { ...state, status: "Approved", approved_signature: state.signature };
}

export function recordCalculationState(state, result) {
  if (state?.status !== "Approved" || state.approved_signature !== state.signature) {
    throw new Error("MAPPING_NOT_APPROVED");
  }
  return { ...state, result, calculated_signature: state.signature };
}

export function canExportResult(state) {
  return state?.status === "Approved"
    && state.result !== null
    && state.result !== undefined
    && state.approved_signature === state.signature
    && state.calculated_signature === state.signature;
}

function mappingEntry(row, index) {
  const sourceRow = Number.isInteger(row.source_row) ? row.source_row : index + 5;
  const sourceFile = String(row.source_file ?? "Accounting-KPI-Definitions.xlsx");
  const sourceSheet = String(row.source_sheet ?? "Mapping");
  return {
    ...row,
    mapping_id: String(row.mapping_id ?? `MAP-${String(index + 1).padStart(3, "0")}`),
    canonical_field: String(row.canonical_field ?? "").trim(),
    source_field: String(row.source_field ?? "").trim(),
    status: String(row.status ?? "Pending"),
    version: String(row.version ?? "1.0"),
    source_file: sourceFile,
    source_sheet: sourceSheet,
    source_row: sourceRow,
    source_key: String(row.source_key ?? `${sourceFile}|${sourceSheet}|${sourceRow}`),
  };
}

export function mappingRowsToConfig(rows) {
  if (!Array.isArray(rows)) throw new TypeError("mapping rows must be an array");
  const entries = rows.map((row, index) => {
    if (Array.isArray(row)) {
      const [canonical_field, source_field, status, owner = "", version = "1.0", approved_by = ""] = row;
      return mappingEntry({ canonical_field, source_field, status, owner, version, approved_by }, index);
    }
    return mappingEntry(row && typeof row === "object" ? row : {}, index);
  });
  const fields = {};
  const byRole = {};
  for (const entry of entries) {
    if (!entry.canonical_field || entry.status.toLowerCase() !== "approved" || !entry.source_field) continue;
    const role = String(entry.record_type ?? entry.target_role ?? "").toLowerCase();
    if (role) (byRole[role] ??= {})[entry.canonical_field] = entry.source_field;
    else fields[entry.canonical_field] = entry.source_field;
  }
  const required = new Set(Object.values(PIPELINE_REQUIRED_MAPPING_FIELDS).flat());
  const missingRequired = [...required].filter((field) => !fields[field]
    && !Object.values(byRole).some((roleFields) => roleFields[field]));
  const version = entries.find((entry) => entry.version)?.version ?? "1.0";
  const missing = entries.filter((entry) => entry.status.toLowerCase() !== "approved" || !entry.source_field);
  const config = {
    ...fields,
    status: missingRequired.length ? "Pending" : "Approved",
    version,
    fields,
    byRole,
    entries,
    missing,
    missingRequired,
  };
  config.signature = hashText(stableJson({ fields, byRole, entries: entries.map(({ canonical_field, source_field, status }) => ({ canonical_field, source_field, status })) }));
  return config;
}

export function applyMappingFieldOverrides(config, overrides) {
  if (!config || !Array.isArray(config.entries)) throw new TypeError("config must contain mapping entries");
  if (!overrides || typeof overrides !== "object" || Array.isArray(overrides)) throw new TypeError("overrides must be a mapping object");
  const seen = new Set();
  const entries = config.entries.map((entry) => {
    const canonicalField = String(entry.canonical_field ?? "").trim();
    seen.add(canonicalField);
    if (Object.hasOwn(overrides, canonicalField)) {
      const sourceField = String(overrides[canonicalField] ?? "").trim();
      return { ...entry, source_field: sourceField, status: sourceField ? "Approved" : "Pending" };
    }
    if (String(entry.status ?? "").toLowerCase() === "approved") {
      return { ...entry, source_field: "", status: "Pending" };
    }
    return { ...entry };
  });
  for (const [canonicalField, value] of Object.entries(overrides)) {
    if (seen.has(canonicalField)) continue;
    const sourceField = String(value ?? "").trim();
    entries.push(mappingEntry({ canonical_field: canonicalField, source_field: sourceField, status: sourceField ? "Approved" : "Pending", version: config.version }, entries.length));
  }
  return mappingRowsToConfig(entries);
}

function preparedSheetRole(name) {
  const normalized = String(name ?? "").trim().toLowerCase();
  if (["prepared lots", "lots"].includes(normalized)) return "lot";
  if (normalized === "cost lines") return "cost";
  if (normalized === "document register") return "document";
  if (normalized === "invoices") return "invoice";
  if (normalized === "payments") return "payment";
  return null;
}

const EXPLICIT_PREPARED_SHEET_ROLES = Object.freeze({
  lots: "lot",
  lot: "lot",
  costs: "cost",
  cost: "cost",
  documents: "document",
  document: "document",
  invoices: "invoice",
  invoice: "invoice",
  payments: "payment",
  payment: "payment",
  mapping: null,
  definitions: null,
  dictionary: null,
  controls: null,
});

export function resolvePreparedSheetRole(explicitRole, name) {
  if (explicitRole !== undefined && explicitRole !== null && String(explicitRole).trim() !== "") {
    const normalized = String(explicitRole).trim().toLowerCase();
    if (!Object.hasOwn(EXPLICIT_PREPARED_SHEET_ROLES, normalized)) throw new Error(`INVALID_SHEET_ROLE:${normalized}`);
    return EXPLICIT_PREPARED_SHEET_ROLES[normalized];
  }
  return preparedSheetRole(name);
}

function sheetsForFile(file, fileIndex) {
  const fileName = String(file?.name ?? file?.source_file ?? `Prepared-${fileIndex + 1}.xlsx`);
  if (Array.isArray(file?.sheets)) {
    return file.sheets.map((sheet, sheetIndex) => ({
      fileName,
      name: String(sheet?.name ?? sheet?.source_sheet ?? `Sheet ${sheetIndex + 1}`),
      rows: Array.isArray(sheet?.rows) ? sheet.rows : [],
      headerRow: Number.isInteger(sheet?.headerRow) ? sheet.headerRow : 4,
      role: sheet?.role,
    }));
  }
  if (file?.sheets && typeof file.sheets === "object") {
    return Object.entries(file.sheets).map(([name, rows]) => ({
      fileName,
      name,
      rows: Array.isArray(rows) ? rows : rows?.rows ?? [],
      headerRow: rows?.headerRow ?? 4,
      role: rows?.role,
    }));
  }
  if (Array.isArray(file?.groups)) {
    return file.groups.map((rows, sheetIndex) => ({
      fileName,
      name: String(rows?.[0]?.source_sheet ?? `Sheet ${sheetIndex + 1}`),
      rows,
      headerRow: 4,
    }));
  }
  return [];
}

function preparedRows(files) {
  const sheets = files.flatMap(sheetsForFile);
  const inventory = applyAccountingInventoryPolicy(sheets.map((sheet) => {
    const role = resolvePreparedSheetRole(sheet.role, sheet.name);
    return {
      source_file: sheet.fileName,
      source_sheet: sheet.name,
      rows: sheet.rows.length,
      role: sheet.role ?? role ?? "configuration",
      status: role ? "Prepared" : "Configuration / not transactional",
    };
  }));
  const rows = sheets.flatMap((sheet) => {
    const role = resolvePreparedSheetRole(sheet.role, sheet.name);
    if (!role) return [];
    return sheet.rows.map((source, index) => {
      const row = source && typeof source === "object" && !Array.isArray(source) ? source : {};
      const sourceRow = Number.isInteger(row.source_row) ? row.source_row : sheet.headerRow + index + 1;
      const sourceFile = String(row.source_file ?? sheet.fileName);
      const sourceSheet = String(row.source_sheet ?? sheet.name);
      return {
        ...row,
        source_file: sourceFile,
        source_sheet: sourceSheet,
        source_row: sourceRow,
        source_key: String(row.source_key ?? `${sourceFile}|${sourceSheet}|${sourceRow}`),
        __role: role,
      };
    });
  });
  return { inventory, rows };
}

function roleMapping(config, role) {
  return { ...(config?.fields ?? mappingFields(config)), ...(config?.byRole?.[role] ?? {}) };
}

function mappedPreparedRow(source, role, config) {
  const fields = roleMapping(config, role);
  const mapped = { ...source, record_type: role };
  for (const [canonical, sourceField] of Object.entries(fields)) {
    if (typeof sourceField !== "string" || !sourceField || source[sourceField] === undefined) continue;
    mapped[canonical] = coerceAccountingField(canonical, source[sourceField]);
  }
  mapped.business_key = businessKey(mapped, role);
  delete mapped.__role;
  return mapped;
}

function invalidCanonicalCode(row, role) {
  const trace = buildSourceTrace(row);
  if (!trace.source_file || !trace.source_sheet || !Number.isInteger(trace.source_row) || trace.source_row < 1 || !trace.source_key) return "INVALID_SOURCE_TRACE";
  if (!businessKey(row, role)) return "MISSING_BUSINESS_KEY";
  const numericFields = role === "lot"
    ? ["purchase_cost", "import_cost", "cutting_cost", "certificate_cost", "logistics_cost", "expected_processing_cost", "carat"]
    : role === "invoice" ? ["sales_amount"] : ["payment_amount"];
  if (numericFields.some((field) => row[field] !== undefined && (!Number.isFinite(row[field]) || row[field] < 0))) return "INVALID_NUMBER";
  const dates = role === "invoice" ? ["invoice_date", "due_date"] : role === "payment" ? ["payment_date"] : [];
  if (dates.some((field) => !isIsoDate(row[field]))) return "INVALID_DATE";
  return null;
}

function addSupportData(lots, costRows, documentRows, exceptions) {
  const costByLot = new Map();
  const costTraceByLot = new Map();
  const seenCostKeys = new Set();
  costRows.forEach((row, index) => {
    if (seenCostKeys.has(row.source_key)) {
      exceptions.push(exceptionFor("DUPLICATE_SOURCE_KEY", row, "The first row in stable source order is retained.", index));
      return;
    }
    seenCostKeys.add(row.source_key);
    const field = COST_TYPE_FIELDS[String(row.cost_type ?? "").trim().toLowerCase()];
    const amount = coerceAccountingField("purchase_cost", row.amount_thb);
    if (!field || !Number.isFinite(amount) || amount < 0) return;
    const lotId = String(row.lot_id ?? "");
    const values = costByLot.get(lotId) ?? {};
    values[field] = round2((values[field] ?? 0) + amount);
    costByLot.set(lotId, values);
    (costTraceByLot.get(lotId) ?? costTraceByLot.set(lotId, []).get(lotId)).push(buildSourceTrace(row));
  });

  const documentsByLot = new Map();
  documentRows.forEach((row) => {
    const lotId = String(row.lot_id ?? "");
    const summary = documentsByLot.get(lotId) ?? { required: 0, completed: 0, trace: [] };
    summary.required += 1;
    if (String(row.status ?? "").trim().toLowerCase() === "complete") summary.completed += 1;
    summary.trace.push(buildSourceTrace(row));
    documentsByLot.set(lotId, summary);
  });

  return lots.map((lot) => {
    const costs = costByLot.get(String(lot.lot_id ?? ""));
    const documents = documentsByLot.get(String(lot.lot_id ?? ""));
    return {
      ...lot,
      ...(costs ?? {}),
      ...(documents ? { required_documents: documents.required, completed_documents: documents.completed } : {}),
      support_trace: {
        costs: costTraceByLot.get(String(lot.lot_id ?? "")) ?? [],
        documents: documents?.trace ?? [],
      },
    };
  });
}

function missingMappingException(entry, index) {
  return exceptionFor("MISSING_MAPPING", entry, `Approve a source field for ${entry.canonical_field || "the required field"} before calculation.`, index);
}

function sortAndDedupeExceptions(exceptions) {
  const seen = new Set();
  return exceptions.filter((exception) => {
    const identity = `${exception.code}|${exception.source_key}|${exception.source_row}`;
    if (seen.has(identity)) return false;
    seen.add(identity);
    return true;
  }).sort((left, right) => {
    const priority = (EXCEPTION_ORDER[left.code] ?? 100) - (EXCEPTION_ORDER[right.code] ?? 100);
    return priority || String(left.source_key).localeCompare(String(right.source_key)) || Number(left.source_row ?? 0) - Number(right.source_row ?? 0);
  });
}

export function ingestPreparedWorkbooks(preparedFiles, mappingConfig, asOfDate) {
  if (!Array.isArray(preparedFiles) || preparedFiles.length < 1) throw new TypeError("preparedFiles must contain one or more files");
  const config = mappingConfig?.entries ? mappingConfig : mappingRowsToConfig(mappingConfig ?? []);
  const { inventory, rows } = preparedRows(preparedFiles);
  const canonicalSources = rows.filter((row) => ["lot", "invoice", "payment"].includes(row.__role));
  const costRows = rows.filter((row) => row.__role === "cost");
  const documentRows = rows.filter((row) => row.__role === "document");
  const included = [];
  const duplicate = [];
  const invalid = [];
  const excluded = [];
  const exceptions = [];
  const seenSourceKeys = new Set();
  const seenBusinessKeys = new Set();
  const reportedMissing = new Set();
  const schemaBySheet = new Map();
  for (const row of canonicalSources) {
    const schemaKey = `${row.source_file}|${row.source_sheet}|${row.__role}`;
    const columns = schemaBySheet.get(schemaKey) ?? new Set();
    Object.keys(row).forEach((column) => columns.add(column));
    schemaBySheet.set(schemaKey, columns);
  }

  canonicalSources.forEach((source, index) => {
    const role = source.__role;
    const fields = roleMapping(config, role);
    const schemaKey = `${source.source_file}|${source.source_sheet}|${role}`;
    const availableColumns = schemaBySheet.get(schemaKey) ?? new Set();
    const missingRequired = PIPELINE_REQUIRED_MAPPING_FIELDS[role].filter((field) => {
      const sourceField = fields[field];
      return !sourceField || !availableColumns.has(sourceField);
    });
    if (missingRequired.length) {
      excluded.push(mappedPreparedRow(source, role, config));
      for (const field of missingRequired) {
        const missingIdentity = `${schemaKey}:${field}`;
        if (reportedMissing.has(missingIdentity)) continue;
        reportedMissing.add(missingIdentity);
        const configuredSourceField = fields[field];
        const entry = !configuredSourceField ? config.entries?.find((item) => item.canonical_field === field) : null;
        const trace = entry ?? {
          canonical_field: field,
          source_file: source.source_file,
          source_sheet: source.source_sheet,
          source_row: source.source_row,
          source_key: source.source_key,
        };
        exceptions.push(missingMappingException(trace, index));
      }
      return;
    }

    const row = mappedPreparedRow(source, role, config);
    const invalidCode = invalidCanonicalCode(row, role);
    if (invalidCode) {
      invalid.push(row);
      exceptions.push(exceptionFor(invalidCode, row, "Correct the invalid source row before calculation.", index));
      return;
    }
    if (seenSourceKeys.has(row.source_key)) {
      duplicate.push(row);
      exceptions.push(exceptionFor("DUPLICATE_SOURCE_KEY", row, "The first row in stable source order is retained.", index));
      return;
    }
    const stableBusinessKey = `${role}:${businessKey(row, role)}`;
    if (seenBusinessKeys.has(stableBusinessKey)) {
      duplicate.push(row);
      exceptions.push(exceptionFor("DUPLICATE_BUSINESS_KEY", row, "The first row in stable source order is retained.", index));
      seenSourceKeys.add(row.source_key);
      return;
    }
    seenSourceKeys.add(row.source_key);
    seenBusinessKeys.add(stableBusinessKey);
    included.push(row);
  });

  const validInvoiceIds = new Set(included.filter((row) => row.record_type === "invoice").map((row) => String(row.invoice_id)));
  for (let index = included.length - 1; index >= 0; index -= 1) {
    const row = included[index];
    if (row.record_type !== "payment" || validInvoiceIds.has(String(row.payment_invoice_id))) continue;
    included.splice(index, 1);
    invalid.unshift(row);
    exceptions.push(exceptionFor("UNMATCHED_PAYMENT", row, "Match the payment to an included invoice before AR calculation.", index));
  }

  const joinedLots = addSupportData(included.filter((row) => row.record_type === "lot"), costRows, documentRows, exceptions);
  const normalized = [
    ...joinedLots,
    ...included.filter((row) => row.record_type === "invoice"),
    ...included.filter((row) => row.record_type === "payment"),
  ];

  for (const [index, entry] of (config.missing ?? []).entries()) {
    const canonical = entry.canonical_field;
    const used = canonical === "legacy_fee"
      ? joinedLots.some((row) => String(row[entry.source_field] ?? "").trim() !== "")
      : (config.missingRequired ?? []).includes(canonical);
    if (used && !reportedMissing.has(`config:${canonical}`)) {
      reportedMissing.add(`config:${canonical}`);
      exceptions.push(missingMappingException(entry, index));
    }
  }

  const kpis = calculateAccountingKpis(normalized, asOfDate);
  const allExceptions = sortAndDedupeExceptions([...exceptions, ...kpis.exceptions]);
  const classification = {
    input: canonicalSources.length,
    included: normalized.length,
    duplicate: duplicate.length,
    invalid: invalid.length,
    excluded: excluded.length,
  };
  const classificationDifference = classification.input
    - classification.included
    - classification.duplicate
    - classification.invalid
    - classification.excluded;
  const arTotal = round2(Object.values(kpis.ar_aging).reduce((sum, value) => sum + value, 0));
  const arRowsTotal = round2(kpis.ar_rows.reduce((sum, row) => sum + row.open_balance, 0));
  const lotMarginTotal = round2(kpis.lot_margins.reduce((sum, row) => sum + row.gross_margin, 0));
  const controls = {
    classification_difference: classificationDifference,
    ar_reconciliation_difference: round2(arTotal - arRowsTotal),
    margin_reconciliation_difference: round2(kpis.gross_margin - lotMarginTotal),
  };
  controls.reconciliation_status = Object.values(controls).every((value) => typeof value !== "number" || value === 0) ? "PASS" : "REVIEW";

  return {
    inventory,
    normalized,
    classification,
    classifications: { included: normalized, duplicate, invalid, excluded },
    support: {
      costLines: costRows.map((row) => Object.fromEntries(Object.entries(row).filter(([field]) => field !== "__role"))),
      documentRegister: documentRows.map((row) => Object.fromEntries(Object.entries(row).filter(([field]) => field !== "__role"))),
    },
    calculations: {
      lotMargins: kpis.lot_margins,
      productMargins: kpis.product_margins,
      arRows: kpis.ar_rows,
    },
    controls,
    kpis,
    exceptions: allExceptions,
  };
}
