import fs from "fs";
import os from "os";
import path from "path";
import { afterEach, beforeEach, describe, expect, it } from "vitest";
import {
  DEFAULT_MAX_TRACKED_WORK_ORDERS,
  DEFAULT_TRACKED_WORK_ORDER_TTL_DAYS,
  LaborWorkOrderGapFillTracker,
  buildBulkSyncWorkOrderIdSet,
  computeWorkOrderGapFillWriteScope,
  findLaborWorkOrderIdsMissingOps,
  findMissingWorkOrderOpKeys,
  findOpenLaborWorkOrderIdsMissingOps,
  isTerminalWorkOrderForGapFill,
  isWorkOrderCoveredByBulkSync,
  mmPartIdentityKey,
  planWorkOrderGapFillFetch,
  toSyncedWorkOrderOperationRows,
  updateTrackedAfterWorkOrderGapFillFetch,
  type TrackedLaborReferencedWorkOrder,
} from "./labor-work-order-gap-fill.js";
import { normalizeLaborTicketWorkOrderOpKey } from "./labor-import-from-erp.js";
import {
  MMSendPart,
  MMSendPartOperation,
  MMSendWorkOrder,
  MMSendWorkOrderOperation,
  type MMSendWorkOrderBatch,
} from "../services/mm-api-service/types/send-types.js";
import Database from "better-sqlite3";
import {
  resetRuntimeStateDbForTests,
  setRuntimeStateJson,
} from "./local-data-store/sdk-runtime-state-db.js";

const bulkOp = (workOrderId: string, seq: string, sub = "0") => ({
  work_order_id: workOrderId,
  sequence_number: seq,
  sub,
});

const ticket = (
  workOrderId: string,
  seq: string,
  state: "OPEN" | "CLOSED" | string = "OPEN",
  sub = "0"
) => ({ workOrderId, sub, sequenceNumber: seq, state });

const part = (partNumber: string, method = "Standard") =>
  new MMSendPart(partNumber, "A", method);
const partOp = (partNumber: string, seq: string, method = "Standard") =>
  new MMSendPartOperation(partNumber, "A", method, seq, "R1", 0, 0, "", 1);
const workOrder = (
  workOrderId: string,
  partNumber: string,
  opts: { status?: string; closedDate?: string | null; method?: string } = {}
) =>
  new MMSendWorkOrder(
    workOrderId, "0", "0", "0",
    opts.status ?? "RELEASED",
    null, "", null, null,
    opts.closedDate ?? null,
    1, partNumber, "A", opts.method ?? "Standard"
  );
const op = (workOrderId: string, seq: string, sub = "0") =>
  new MMSendWorkOrderOperation(
    workOrderId, "0", "0", sub, seq, "R1",
    0, 0, 0, null, null, null, 0, 0, 0, 0, "Production", 1, "RELEASED"
  );

const seqs = (ops: ReadonlyArray<{ sequenceNumber?: unknown; sequence_number?: unknown }>) =>
  ops.map((o) => String(o.sequenceNumber ?? o.sequence_number));

describe("findMissingWorkOrderOpKeys", () => {
  const key = (wo: string, seq: string, sub = "0") =>
    normalizeLaborTicketWorkOrderOpKey(wo, sub, seq);

  it("returns only the operations with no match in the bulk batch, not the whole work order", () => {
    const missing = findMissingWorkOrderOpKeys(
      [ticket("J1", "10", "CLOSED"), ticket("J1", "20", "OPEN")],
      [bulkOp("J1", "10")]
    );
    expect([...missing]).toEqual([key("J1", "20")]);
  });

  it("counts CLOSED tickets too, unlike the work-order-id variant", () => {
    const missing = findMissingWorkOrderOpKeys([ticket("J1", "10", "CLOSED")], []);
    expect(missing.has(key("J1", "10"))).toBe(true);
  });

  it("keys a CLOSED and an OPEN ticket for the same operation identically", () => {
    const missing = findMissingWorkOrderOpKeys(
      [ticket("J1", "10", "CLOSED"), ticket("j1", "10", "OPEN")],
      []
    );
    expect(missing.size).toBe(1);
  });

  it("treats a sub mismatch as missing, so the key includes sub", () => {
    expect(findMissingWorkOrderOpKeys([ticket("J1", "10", "OPEN", "1")], [bulkOp("J1", "10", "0")]).size).toBe(1);
  });

  it("skips tickets with a blank work order id and tolerates nullish input", () => {
    expect(findMissingWorkOrderOpKeys([ticket("  ", "10"), { workOrderId: null }], []).size).toBe(0);
    expect(findMissingWorkOrderOpKeys(undefined as never, undefined as never).size).toBe(0);
  });

  it("skips tickets with a blank sequence number (the filter rejects them as missingFields)", () => {
    expect(findMissingWorkOrderOpKeys([ticket("J1", " "), { workOrderId: "J1", sub: "0" }], []).size).toBe(0);
    expect(findOpenLaborWorkOrderIdsMissingOps([ticket("J1", "")], [])).toEqual([]);
  });

  it("agrees with the filter on a whitespace-only sub", () => {
    expect(findMissingWorkOrderOpKeys([ticket("J1", "30", "OPEN", "  ")], [bulkOp("J1", "30", "0")]).size).toBe(0);
    expect(findOpenLaborWorkOrderIdsMissingOps([ticket("J1", "30", "OPEN", "  ")], [bulkOp("J1", "30", "0")])).toEqual([]);
  });
});

describe("findOpenLaborWorkOrderIdsMissingOps", () => {
  it("returns ids for OPEN tickets with no matching operation, deduped", () => {
    expect(
      findOpenLaborWorkOrderIdsMissingOps(
        [ticket("J1", "10"), ticket("J1", "20"), ticket("J2", "10")],
        []
      )
    ).toEqual(["J1", "J2"]);
  });

  it("ignores CLOSED tickets even when their operation is missing", () => {
    expect(findOpenLaborWorkOrderIdsMissingOps([ticket("J1", "10", "CLOSED")], [])).toEqual([]);
  });

  it("does not flag tickets whose operation is in the batch", () => {
    expect(findOpenLaborWorkOrderIdsMissingOps([ticket("J1", "10")], [bulkOp("J1", "10")])).toEqual([]);
  });

  it("matches case-insensitively on the work order id and compares state case-insensitively", () => {
    expect(findOpenLaborWorkOrderIdsMissingOps([ticket("j1", "10")], [bulkOp("J1", "10")])).toEqual([]);
    expect(findOpenLaborWorkOrderIdsMissingOps([ticket("J1", "10", " open ")], [])).toEqual(["J1"]);
  });

  it("treats a sequence or sub mismatch as missing", () => {
    expect(findOpenLaborWorkOrderIdsMissingOps([ticket("J1", "20")], [bulkOp("J1", "10")])).toEqual(["J1"]);
    expect(findOpenLaborWorkOrderIdsMissingOps([ticket("J1", "10", "OPEN", "1")], [bulkOp("J1", "10")])).toEqual(["J1"]);
  });

  it("returns ids trimmed with case preserved, and skips blank ids", () => {
    expect(findOpenLaborWorkOrderIdsMissingOps([ticket(" Ab-1 ", "10"), ticket("", "10")], [])).toEqual(["Ab-1"]);
  });
});

describe("findLaborWorkOrderIdsMissingOps", () => {
  it("counts tickets of every state, otherwise like the OPEN-only variant", () => {
    const tickets = [ticket("J1", "10", "CLOSED"), ticket("J2", "10", "OPEN"), ticket("J3", "10", "CLOSED")];
    expect(findLaborWorkOrderIdsMissingOps(tickets, [bulkOp("J3", "10")])).toEqual(["J1", "J2"]);
    expect(findOpenLaborWorkOrderIdsMissingOps(tickets, [bulkOp("J3", "10")])).toEqual(["J2"]);
  });

  it("applies the same field and dedupe rules", () => {
    expect(findLaborWorkOrderIdsMissingOps([ticket(" J1 ", "10", "CLOSED"), ticket("J1", "", "CLOSED"), ticket("J1", "20", "CLOSED")], [])).toEqual(["J1"]);
  });
});

describe("lot / split-aware gap detection", () => {
  const awareBulk = [{ work_order_id: "J1", lot: "1", split: "0", sub: "0", sequence_number: "10" }];
  const lotTicket = (lot: string, state = "OPEN") => ({ workOrderId: "J1", lot, split: "0", sub: "0", sequenceNumber: "10", state });

  it("treats another lot of a bulk-synced operation as missing when the bulk rows opt in", () => {
    expect(findOpenLaborWorkOrderIdsMissingOps([lotTicket("2")], awareBulk)).toEqual(["J1"]);
    expect(findOpenLaborWorkOrderIdsMissingOps([lotTicket("1")], awareBulk)).toEqual([]);
    expect([...findMissingWorkOrderOpKeys([lotTicket("2")], awareBulk)]).toEqual(["J1:2:0:0:10"]);
  });

  it("ignores lot / split when the bulk rows do not opt in", () => {
    expect(findOpenLaborWorkOrderIdsMissingOps([lotTicket("2")], [bulkOp("J1", "10")])).toEqual([]);
  });
});

describe("bulk coverage set", () => {
  it("is exact on trimmed ids, case preserved (coverage is asymmetric)", () => {
    const set = buildBulkSyncWorkOrderIdSet(["J1 ", null, "", 42]);
    expect([...set]).toEqual(["J1", "42"]);
    expect(isWorkOrderCoveredByBulkSync(set, " J1")).toBe(true);
    expect(isWorkOrderCoveredByBulkSync(set, "j1")).toBe(false);
    expect(isWorkOrderCoveredByBulkSync(set, 42)).toBe(true);
  });
});

describe("isTerminalWorkOrderForGapFill", () => {
  it("is terminal on a completed / cancelled / deleted status in any casing", () => {
    for (const status of ["COMPLETED", "completed", "Cancelled", "CANCELED", "DELETED"]) {
      expect(isTerminalWorkOrderForGapFill(workOrder("J", "P", { status }))).toBe(true);
    }
  });

  it("is terminal on a non-blank closed date regardless of status", () => {
    expect(isTerminalWorkOrderForGapFill(workOrder("J", "P", { closedDate: "2026-01-01T00:00:00.000Z" }))).toBe(true);
  });

  it("is not terminal for open statuses or a blank closed date", () => {
    for (const status of ["RELEASED", "ACTIVE", "NOT RELEASED", "PENDING", ""]) {
      expect(isTerminalWorkOrderForGapFill(workOrder("J", "P", { status }))).toBe(false);
    }
    expect(isTerminalWorkOrderForGapFill({ status: "RELEASED", closedDate: "  " })).toBe(false);
  });
});

describe("planWorkOrderGapFillFetch", () => {
  const now = new Date("2026-09-21T12:00:00.000Z");
  const daysAgo = (d: number) => new Date(now.getTime() - d * 86400000).toISOString();
  const tracked = (id: string, d: number): TrackedLaborReferencedWorkOrder => ({
    workOrderId: id,
    lastLaborSeenAt: daysAgo(d),
  });

  it("unions open-labor ids with carried tracked ids, deduped case-insensitively", () => {
    const plan = planWorkOrderGapFillFetch({
      tracked: [tracked("J2", 1), tracked("j1", 2)],
      laborWorkOrderIds: ["J1", "J3"],
      now,
    });
    expect(plan.fetchWorkOrderIds).toEqual(["J1", "J3", "J2"]);
    expect(plan.carriedTracked.map((t) => t.workOrderId)).toEqual(["J2", "j1"]);
    expect(plan.dropped).toEqual({ expired: 0 });
  });

  it("drops tracked entries past the TTL (default 30 days) or with unparseable timestamps", () => {
    const plan = planWorkOrderGapFillFetch({
      tracked: [
        tracked("OLD", DEFAULT_TRACKED_WORK_ORDER_TTL_DAYS + 1),
        { workOrderId: "BAD", lastLaborSeenAt: "not-a-date" },
        tracked("OK", DEFAULT_TRACKED_WORK_ORDER_TTL_DAYS - 1),
      ],
      laborWorkOrderIds: [],
      now,
    });
    expect(plan.fetchWorkOrderIds).toEqual(["OK"]);
    expect(plan.dropped.expired).toBe(2);
  });

  it("honors a custom TTL", () => {
    const plan = planWorkOrderGapFillFetch({
      tracked: [tracked("J1", 3)],
      laborWorkOrderIds: [],
      now,
      trackedTtlDays: 2,
    });
    expect(plan.fetchWorkOrderIds).toEqual([]);
    expect(plan.dropped.expired).toBe(1);
  });

  it("keeps fetching tracked entries whatever the bulk rows name: only the fetched rows show coverage", () => {
    // The bulk window may hold one lot of J1 while a gap-filled sibling lot is outside it.
    const plan = planWorkOrderGapFillFetch({
      tracked: [tracked("J1", 1)],
      laborWorkOrderIds: [],
      now,
    });
    expect(plan.fetchWorkOrderIds).toEqual(["J1"]);
  });
});

describe("updateTrackedAfterWorkOrderGapFillFetch", () => {
  const now = new Date("2026-09-21T12:00:00.000Z");
  const earlier = "2026-09-20T12:00:00.000Z";
  const carried = (id: string): TrackedLaborReferencedWorkOrder => ({
    workOrderId: id,
    lastLaborSeenAt: earlier,
  });

  it("adds open-labor ids with a fresh timestamp and refreshes already-tracked ones", () => {
    const next = updateTrackedAfterWorkOrderGapFillFetch({
      carriedTracked: [carried("J1")],
      laborWorkOrderIds: ["j1", "J2"],
      terminalWorkOrderIds: [],
      now,
    });
    expect(next.tracked).toEqual([
      { workOrderId: "j1", lastLaborSeenAt: now.toISOString() },
      { workOrderId: "J2", lastLaborSeenAt: now.toISOString() },
    ]);
    expect(next.terminal).toEqual([]);
  });

  it("closes out tracked work orders that came back terminal and hands them back", () => {
    const next = updateTrackedAfterWorkOrderGapFillFetch({
      carriedTracked: [carried("J1"), carried("J2")],
      laborWorkOrderIds: [],
      terminalWorkOrderIds: [" j1 "],
      now,
    });
    expect(next.tracked.map((t) => t.workOrderId)).toEqual(["J2"]);
    expect(next.terminal).toEqual([carried("J1")]);
    expect(next.dropped).toEqual({ coveredByBulkSync: 0, terminal: 1, evicted: 0 });
  });

  it("does not track a work order that is already terminal when first seen", () => {
    const next = updateTrackedAfterWorkOrderGapFillFetch({
      carriedTracked: [],
      laborWorkOrderIds: ["J1"],
      terminalWorkOrderIds: ["J1"],
      now,
    });
    expect(next.tracked).toEqual([]);
    expect(next.terminal.map((t) => t.workOrderId)).toEqual(["J1"]);
  });

  it("ignores terminal ids that were never tracked", () => {
    const next = updateTrackedAfterWorkOrderGapFillFetch({
      carriedTracked: [carried("J1")],
      laborWorkOrderIds: [],
      terminalWorkOrderIds: ["J9", "", null],
      now,
    });
    expect(next.tracked).toEqual([carried("J1")]);
    expect(next.dropped.terminal).toBe(0);
  });

  it("drops work orders the bulk sync covers row for row, case-insensitively, before the terminal pass", () => {
    const next = updateTrackedAfterWorkOrderGapFillFetch({
      carriedTracked: [carried("J1"), carried("J2"), carried("J3")],
      laborWorkOrderIds: ["J2"],
      // J3 is both covered and terminal: nothing is left to close out, so no pending entry.
      terminalWorkOrderIds: ["J3"],
      bulkCoveredWorkOrderIds: ["j1", "J2", "J3"],
      now,
    });
    expect(next.tracked).toEqual([]);
    expect(next.terminal).toEqual([]);
    expect(next.dropped).toEqual({ coveredByBulkSync: 3, terminal: 0, evicted: 0 });
  });

  it("evicts the oldest entries beyond the cap", () => {
    const carriedTracked = Array.from({ length: 5 }, (_, i) => ({
      workOrderId: `J${i}`,
      lastLaborSeenAt: new Date(now.getTime() - (i + 1) * 60000).toISOString(),
    }));
    const next = updateTrackedAfterWorkOrderGapFillFetch({
      carriedTracked,
      laborWorkOrderIds: ["NEW"],
      terminalWorkOrderIds: [],
      now,
      maxTrackedWorkOrders: 3,
    });
    expect(next.tracked.map((t) => t.workOrderId)).toEqual(["NEW", "J0", "J1"]);
    expect(next.dropped.evicted).toBe(3);
  });

  it("keeps the default cap at 1000", () => {
    expect(DEFAULT_MAX_TRACKED_WORK_ORDERS).toBe(1000);
  });
});

describe("computeWorkOrderGapFillWriteScope", () => {
  const scope = (
    fullBatch: MMSendWorkOrderBatch,
    tickets: ReturnType<typeof ticket>[],
    bulkOps: ReturnType<typeof bulkOp>[],
    terminalWorkOrderIds: string[] = []
  ) =>
    computeWorkOrderGapFillWriteScope({
      fullBatch,
      bulkSyncedWorkOrderOperations: bulkOps,
      missingOpKeys: findMissingWorkOrderOpKeys(tickets, bulkOps),
      terminalWorkOrderIds,
    });

  const batchJ1 = (): MMSendWorkOrderBatch => ({
    parts: [part("P1")],
    partOperations: [partOp("P1", "30"), partOp("P1", "40")],
    workOrders: [workOrder("J1", "P1")],
    workOrderOperations: [op("J1", "30"), op("J1", "40")],
  });

  it("returns every op of the fetched work orders for the key set, but writes only the missing one", () => {
    // The operator clocked out since last cycle: the CLOSED ticket must still pass the
    // labor filter, and its op must be written rather than assumed present.
    const result = scope(batchJ1(), [ticket("J1", "30", "CLOSED")], []);
    expect(seqs(result.keySetWorkOrderOps)).toEqual(["30", "40"]);
    expect(seqs(result.batchToWrite.workOrderOperations)).toEqual(["30"]);
  });

  it("writes the full entity set for a work order the bulk sync is not covering", () => {
    const result = scope(batchJ1(), [ticket("J1", "30")], []);
    expect(result.batchToWrite.workOrders.map((w) => w.workOrderId)).toEqual(["J1"]);
    expect(result.batchToWrite.parts.map((p) => p.partNumber)).toEqual(["P1"]);
    expect(seqs(result.batchToWrite.partOperations)).toEqual(["30", "40"]);
    expect(result.uncoveredWorkOrderIds).toEqual(["J1"]);
    expect(result.closingWorkOrderIds).toEqual([]);
  });

  it("writes only the missing operation for a work order the bulk sync covered this cycle", () => {
    const full: MMSendWorkOrderBatch = {
      parts: [part("P2")],
      partOperations: [partOp("P2", "10"), partOp("P2", "20")],
      workOrders: [workOrder("J2", "P2", { status: "COMPLETED" })],
      workOrderOperations: [op("J2", "10"), op("J2", "20")],
    };
    // Terminal but covered: the bulk writer owns it, so no close-out here.
    const result = scope(full, [ticket("J2", "20", "OPEN"), ticket("J2", "10", "CLOSED")], [bulkOp("J2", "10")], ["J2"]);
    expect(seqs(result.batchToWrite.workOrderOperations)).toEqual(["20"]);
    expect(result.batchToWrite.workOrders).toEqual([]);
    expect(result.batchToWrite.parts).toEqual([]);
    // Op 20's part operation comes along: nothing in the bulk rows owns P2/20.
    expect(seqs(result.batchToWrite.partOperations)).toEqual(["20"]);
    expect(result.closingWorkOrderIds).toEqual([]);
    expect(seqs(result.keySetWorkOrderOps)).toEqual(["10", "20"]);
    // Every row is covered, but op 20 is written this cycle: the watch-list entry must stay
    // to retry that write if it fails after the ticket closes.
    expect(result.bulkCoveredWorkOrderIds).toEqual([]);
  });

  it("writes the full op set and the work order when an uncovered work order closes out", () => {
    const full = batchJ1();
    full.workOrders = [workOrder("J1", "P1", { status: "COMPLETED", closedDate: "2026-09-20T00:00:00.000Z" })];
    const result = scope(full, [], [], ["J1"]);
    expect(seqs(result.batchToWrite.workOrderOperations)).toEqual(["30", "40"]);
    expect(result.batchToWrite.workOrders[0].status).toBe("COMPLETED");
    expect(result.closingWorkOrderIds).toEqual(["J1"]);
  });

  it("selects parts by the full part identity including method", () => {
    const full: MMSendWorkOrderBatch = {
      parts: [part("P1", "Standard"), part("P1", "Alt"), part("P9")],
      partOperations: [partOp("P1", "30", "Standard"), partOp("P1", "30", "Alt"), partOp("P9", "10")],
      workOrders: [workOrder("J1", "P1", { method: "Alt" })],
      workOrderOperations: [op("J1", "30")],
    };
    const result = scope(full, [ticket("J1", "30")], []);
    expect(result.batchToWrite.parts.map((p) => p.method)).toEqual(["Alt"]);
    expect(result.batchToWrite.partOperations.map((p) => p.method)).toEqual(["Alt"]);
  });

  it("writes only the work order rows the written operations belong to, not idle sibling lots", () => {
    const wo2 = new MMSendWorkOrder("J1", "1", "0", "0", "RELEASED", null, "", null, null, null, 1, "P1", "A", "Standard");
    const full = batchJ1();
    full.workOrders.push(wo2);
    const result = scope(full, [ticket("J1", "30")], []);
    expect(result.batchToWrite.workOrders.map((w) => w.lot)).toEqual(["0"]);
  });

  it("writes every row of a closing id, idle sibling lots included", () => {
    const wo2 = new MMSendWorkOrder("J1", "1", "0", "0", "COMPLETED", null, "", null, null, null, 1, "P1", "A", "Standard");
    const full = batchJ1();
    full.workOrders = [workOrder("J1", "P1", { status: "COMPLETED" }), wo2];
    const result = scope(full, [], [], ["J1"]);
    expect(result.batchToWrite.workOrders.map((w) => w.lot).sort()).toEqual(["0", "1"]);
  });

  describe("bulk coverage of a work order id spelled two ways", () => {
    // The watch list keys ids case-insensitively, so coverage reported for one spelling
    // removes the entry shared by every spelling.
    const row = (id: string, lot: string) =>
      new MMSendWorkOrder(id, lot, "0", "0", "COMPLETED", null, "", null, null, "2026-09-20T00:00:00.000Z", 1, "P1", "A", "Standard");
    const full = (): MMSendWorkOrderBatch => ({
      parts: [part("P1")],
      partOperations: [],
      workOrders: [row("abc", "1"), row("ABC", "2")],
      workOrderOperations: [],
    });
    const bulkRow = (id: string, lot: string) => ({ work_order_id: id, lot, split: "0", sub: "0", sequence_number: "10" });
    const run = (bulk: ReturnType<typeof bulkRow>[]) =>
      computeWorkOrderGapFillWriteScope({
        fullBatch: full(),
        bulkSyncedWorkOrderOperations: bulk,
        missingOpKeys: new Set(),
        terminalWorkOrderIds: ["abc", "ABC"],
      });

    it("is not reported while another spelling still has an uncovered row", () => {
      const result = run([bulkRow("abc", "1")]);
      expect(result.bulkCoveredWorkOrderIds).toEqual([]);
      // The uncovered spelling still closes out its own row.
      expect(result.closingWorkOrderIds).toEqual(["ABC"]);
      expect(result.batchToWrite.workOrders.map((w) => `${w.workOrderId}/${w.lot}`)).toEqual(["ABC/2"]);
    });

    it("is reported in every spelling once all of them are covered", () => {
      const result = run([bulkRow("abc", "1"), bulkRow("ABC", "2")]);
      expect(result.bulkCoveredWorkOrderIds).toEqual(["abc", "ABC"]);
      expect(result.closingWorkOrderIds).toEqual([]);
      expect(result.batchToWrite.workOrders).toEqual([]);
    });
  });

  describe("a terminal id with one lot inside the bulk window", () => {
    const lotRow = (lot: string, status: string) =>
      new MMSendWorkOrder("J1", lot, "0", "0", status, null, "", null, null, null, 1, "P1", "A", "Standard");
    const lotOp = (lot: string, seq: string) =>
      new MMSendWorkOrderOperation("J1", lot, "0", "0", seq, "R1", 0, 0, 0, null, null, null, 0, 0, 0, 0, "Production", 1, "RELEASED");
    const full = (): MMSendWorkOrderBatch => ({
      parts: [part("P1")],
      partOperations: [partOp("P1", "30"), partOp("P1", "40")],
      workOrders: [lotRow("1", "COMPLETED"), lotRow("2", "COMPLETED")],
      workOrderOperations: [lotOp("1", "30"), lotOp("2", "30"), lotOp("2", "40")],
    });
    const bulkLot1 = [{ work_order_id: "J1", lot: "1", split: "0", sub: "0", sequence_number: "30" }];

    it("closes out the uncovered lot and leaves the covered one to the bulk sync", () => {
      const result = computeWorkOrderGapFillWriteScope({
        fullBatch: full(),
        bulkSyncedWorkOrderOperations: bulkLot1,
        missingOpKeys: new Set(),
        terminalWorkOrderIds: ["J1"],
      });
      expect(result.closingWorkOrderIds).toEqual(["J1"]);
      expect(result.batchToWrite.workOrders.map((w) => w.lot)).toEqual(["2"]);
      expect(result.batchToWrite.workOrderOperations.map((o) => `${o.lot}/${o.sequenceNumber}`)).toEqual(["2/30", "2/40"]);
      expect(result.bulkCoveredWorkOrderIds).toEqual([]);
    });

    it("reports an id as bulk-covered only when every fetched row is covered", () => {
      const both = [...bulkLot1, { work_order_id: "J1", lot: "2", split: "0", sub: "0", sequence_number: "30" }];
      const result = computeWorkOrderGapFillWriteScope({
        fullBatch: full(),
        bulkSyncedWorkOrderOperations: both,
        missingOpKeys: new Set(),
        terminalWorkOrderIds: ["J1"],
      });
      expect(result.bulkCoveredWorkOrderIds).toEqual(["J1"]);
      expect(result.closingWorkOrderIds).toEqual([]);
      expect(result.batchToWrite.workOrders).toEqual([]);
      expect(result.batchToWrite.workOrderOperations).toEqual([]);
    });
  });

  it("writes the work order of a closing id even when the batch carries no operations for it", () => {
    // Otherwise the id leaves the watch list after the write and its terminal state never
    // reaches MM: it is outside the bulk window, so nothing fetches it again.
    const full: MMSendWorkOrderBatch = {
      parts: [part("P1"), part("P2")],
      partOperations: [partOp("P2", "10")],
      workOrders: [workOrder("J1", "P1", { status: "COMPLETED" }), workOrder("J2", "P2")],
      workOrderOperations: [op("J2", "10")],
    };
    const result = scope(full, [ticket("J2", "10")], [], ["J1"]);
    expect(result.batchToWrite.workOrders.map((w) => w.workOrderId).sort()).toEqual(["J1", "J2"]);
    expect(result.batchToWrite.parts.map((p) => p.partNumber).sort()).toEqual(["P1", "P2"]);
    expect(result.uncoveredWorkOrderIds.sort()).toEqual(["J1", "J2"]);
    expect(result.closingWorkOrderIds).toEqual(["J1"]);
  });

  describe("a part shared with a covered fetched work order", () => {
    // J1 aged out of the window, J2 for the same part is inside it. The bulk sync wrote P1
    // and P1's part operations for J2's sequences this cycle.
    const shared = (): MMSendWorkOrderBatch => ({
      parts: [part("P1")],
      partOperations: [partOp("P1", "10"), partOp("P1", "30"), partOp("P1", "40")],
      workOrders: [workOrder("J1", "P1"), workOrder("J2", "P1")],
      workOrderOperations: [op("J1", "30"), op("J1", "40"), op("J2", "10"), op("J2", "30")],
    });
    const bulk = [bulkOp("J2", "10"), bulkOp("J2", "30")];

    it("leaves the part row and the bulk-written part operations to the bulk sync", () => {
      const result = scope(shared(), [ticket("J1", "30")], bulk);
      expect(seqs(result.batchToWrite.workOrderOperations)).toEqual(["30"]);
      expect(result.batchToWrite.workOrders.map((w) => w.workOrderId)).toEqual(["J1"]);
      expect(result.batchToWrite.parts).toEqual([]);
      // P1/30 exists via J2's bulk write, so J1/30's FK is already satisfied.
      expect(result.batchToWrite.partOperations).toEqual([]);
      expect(result.sharedPartIdentities).toEqual(["P1|A|Standard"]);
    });

    it("still writes a part operation only the uncovered work order needs", () => {
      const result = scope(shared(), [ticket("J1", "40")], bulk);
      expect(seqs(result.batchToWrite.partOperations)).toEqual(["40"]);
      expect(result.batchToWrite.parts).toEqual([]);
    });

    it("writes the full part set when no covered fetched work order shares the part", () => {
      const full = shared();
      full.workOrders[1] = workOrder("J2", "P2");
      const result = scope(full, [ticket("J1", "30")], bulk);
      expect(result.batchToWrite.parts.map((p) => p.partNumber)).toEqual(["P1"]);
      expect(seqs(result.batchToWrite.partOperations)).toEqual(["10", "30", "40"]);
      expect(result.sharedPartIdentities).toEqual([]);
    });

    it("does not treat a part as shared through an uncovered sibling", () => {
      // Two uncovered work orders for one part: no competing writer, full set once.
      const result = scope(shared(), [ticket("J1", "30"), ticket("J2", "10")], []);
      expect(result.batchToWrite.parts.map((p) => p.partNumber)).toEqual(["P1"]);
      expect(seqs(result.batchToWrite.partOperations)).toEqual(["10", "30", "40"]);
    });
  });

  describe("bulk rows carrying part identity", () => {
    // J1 aged out; J9, for the same part, is in the window and was NOT fetched. Only the
    // bulk rows' part fields make that sharing visible.
    const bulkWithPart = (id: string, seq: string) => ({
      work_order_id: id, sub: "0", sequence_number: seq,
      part_number: "P1", part_revision: "A", method: "Standard",
    });

    it("leaves a part the bulk sync wrote through an un-fetched work order to the bulk sync", () => {
      const result = scope(batchJ1(), [ticket("J1", "30")], [bulkWithPart("J9", "30"), bulkWithPart("J9", "40")]);
      expect(result.batchToWrite.workOrders.map((w) => w.workOrderId)).toEqual(["J1"]);
      expect(result.batchToWrite.parts).toEqual([]);
      // Both of P1's part operations were written through J9.
      expect(result.batchToWrite.partOperations).toEqual([]);
      expect(result.sharedPartIdentities).toEqual(["P1|A|Standard"]);
    });

    it("still writes the part operation the un-fetched work order did not carry", () => {
      const result = scope(batchJ1(), [ticket("J1", "30")], [bulkWithPart("J9", "40")]);
      expect(seqs(result.batchToWrite.partOperations)).toEqual(["30"]);
      expect(result.batchToWrite.parts).toEqual([]);
    });

    it("does not confuse a different part on the bulk rows with a shared one", () => {
      const other = { ...bulkWithPart("J9", "30"), part_number: "P2" };
      const result = scope(batchJ1(), [ticket("J1", "30")], [other]);
      expect(result.batchToWrite.parts.map((p) => p.partNumber)).toEqual(["P1"]);
      expect(seqs(result.batchToWrite.partOperations)).toEqual(["30", "40"]);
    });
  });

  describe("a missing operation on a covered row", () => {
    // J2 is covered (bulk wrote op 10) but its op 20 was dropped by the bulk mapping,
    // possibly together with part operation P2/20.
    const full = (): MMSendWorkOrderBatch => ({
      parts: [part("P2")],
      partOperations: [partOp("P2", "10"), partOp("P2", "20")],
      workOrders: [workOrder("J2", "P2")],
      workOrderOperations: [op("J2", "10"), op("J2", "20")],
    });

    it("brings its part operation along when no bulk row owns it", () => {
      const result = scope(full(), [ticket("J2", "20")], [bulkOp("J2", "10")]);
      expect(seqs(result.batchToWrite.workOrderOperations)).toEqual(["20"]);
      expect(seqs(result.batchToWrite.partOperations)).toEqual(["20"]);
      expect(result.batchToWrite.workOrders).toEqual([]);
      expect(result.batchToWrite.parts).toEqual([]);
    });

    it("leaves the part operation alone when a bulk row for the same part wrote that sequence", () => {
      const bulk = [
        bulkOp("J2", "10"),
        { work_order_id: "J9", sub: "0", sequence_number: "20", part_number: "P2", part_revision: "A", method: "Standard" },
      ];
      const result = scope(full(), [ticket("J2", "20")], bulk);
      expect(seqs(result.batchToWrite.workOrderOperations)).toEqual(["20"]);
      expect(result.batchToWrite.partOperations).toEqual([]);
    });
  });

  describe("coverage by MM work order identity", () => {
    const woRow = (id: string, lot: string, sub: string, partNumber = "P1") =>
      new MMSendWorkOrder(id, lot, "0", sub, "RELEASED", null, "", null, null, null, 1, partNumber, "A", "Standard");
    const opRow = (id: string, lot: string, sub: string, seq: string) =>
      new MMSendWorkOrderOperation(id, lot, "0", sub, seq, "R1", 0, 0, 0, null, null, null, 0, 0, 0, 0, "Production", 1, "RELEASED");

    it("writes the parents of a sub-assembly row the bulk sync did not touch, even though the id is covered", () => {
      // Epicor: sub is the assembly. Bulk wrote J1 assembly 0; labor is on assembly 1.
      const full: MMSendWorkOrderBatch = {
        parts: [part("P1")],
        partOperations: [partOp("P1", "30")],
        workOrders: [woRow("J1", "0", "0"), woRow("J1", "0", "1")],
        workOrderOperations: [opRow("J1", "0", "0", "30"), opRow("J1", "0", "1", "30")],
      };
      const result = scope(full, [ticket("J1", "30", "OPEN", "1")], [bulkOp("J1", "30", "0")]);
      expect(result.batchToWrite.workOrderOperations.map((o) => o.sub)).toEqual(["1"]);
      expect(result.batchToWrite.workOrders.map((w) => w.sub)).toEqual(["1"]);
      // P1 is shared with the covered assembly-0 row; P1/30 was written by the bulk sync.
      expect(result.batchToWrite.parts).toEqual([]);
      expect(result.batchToWrite.partOperations).toEqual([]);
      expect(result.uncoveredWorkOrderIds).toEqual(["J1"]);
    });

    it("uses lot / split when the bulk rows carry them", () => {
      const full: MMSendWorkOrderBatch = {
        parts: [part("P1")],
        partOperations: [partOp("P1", "30"), partOp("P1", "40")],
        workOrders: [woRow("J1", "1", "0"), woRow("J1", "2", "0")],
        workOrderOperations: [opRow("J1", "1", "0", "30"), opRow("J1", "2", "0", "40")],
      };
      const bulk = [{ work_order_id: "J1", lot: "1", split: "0", sub: "0", sequence_number: "30" }];
      // Lot-aware bulk rows mean lot-aware keys: the ticket names its lot.
      const lotTicket = { workOrderId: "J1", lot: "2", split: "0", sub: "0", sequenceNumber: "40", state: "OPEN" };
      const result = scope(full, [lotTicket], bulk);
      expect(result.batchToWrite.workOrders.map((w) => w.lot)).toEqual(["2"]);
      expect(seqs(result.batchToWrite.workOrderOperations)).toEqual(["40"]);
      expect(seqs(result.batchToWrite.partOperations)).toEqual(["40"]);
    });

    it("treats every lot / split as covered when the bulk rows carry no lot / split", () => {
      const full: MMSendWorkOrderBatch = {
        parts: [part("P1")],
        partOperations: [partOp("P1", "30"), partOp("P1", "40")],
        workOrders: [woRow("J1", "1", "0"), woRow("J1", "2", "0")],
        workOrderOperations: [opRow("J1", "1", "0", "30"), opRow("J1", "2", "0", "40")],
      };
      const result = scope(full, [ticket("J1", "40")], [bulkOp("J1", "30")]);
      expect(seqs(result.batchToWrite.workOrderOperations)).toEqual(["40"]);
      expect(result.batchToWrite.workOrders).toEqual([]);
      expect(result.batchToWrite.parts).toEqual([]);
    });

    it("matches an empty and a zero sub as the same row, like the labor key", () => {
      const full: MMSendWorkOrderBatch = {
        parts: [part("P1")],
        partOperations: [partOp("P1", "30")],
        workOrders: [woRow("J1", "0", "")],
        workOrderOperations: [opRow("J1", "0", "", "30"), opRow("J1", "0", "", "40")],
      };
      const result = scope(full, [ticket("J1", "40")], [bulkOp("J1", "30", "0")]);
      expect(result.batchToWrite.workOrders).toEqual([]);
      expect(seqs(result.batchToWrite.workOrderOperations)).toEqual(["40"]);
    });
  });

  it("writes nothing when the bulk batch already carries every referenced op", () => {
    const result = scope(batchJ1(), [ticket("J1", "30")], [bulkOp("J1", "30")]);
    expect(result.batchToWrite.workOrderOperations).toEqual([]);
    expect(result.batchToWrite.workOrders).toEqual([]);
    expect(seqs(result.keySetWorkOrderOps)).toEqual(["30", "40"]);
  });

  it("tolerates a batch with missing arrays", () => {
    const result = computeWorkOrderGapFillWriteScope({
      fullBatch: {} as MMSendWorkOrderBatch,
      bulkSyncedWorkOrderOperations: [],
      missingOpKeys: new Set(),
      terminalWorkOrderIds: [],
    });
    expect(result.batchToWrite).toEqual({ parts: [], partOperations: [], workOrders: [], workOrderOperations: [] });
    expect(result.keySetWorkOrderOps).toEqual([]);
  });
});

describe("mmPartIdentityKey", () => {
  it("keys raw values with a Standard method fallback, as the flattened builder does", () => {
    expect(mmPartIdentityKey({ partNumber: "P1", partRevision: null, method: undefined })).toBe("P1||Standard");
    expect(mmPartIdentityKey({ partNumber: 7, partRevision: "A", method: "" })).toBe("7|A|Standard");
    expect(mmPartIdentityKey(part("P1", "Alt"))).toBe("P1|A|Alt");
    // Deliberately not normalized: whitespace and case are the connector's to get right.
    expect(mmPartIdentityKey({ partNumber: "P1 ", partRevision: "a" })).toBe("P1 |a|Standard");
  });
});

describe("toSyncedWorkOrderOperationRows", () => {
  it("maps send operations to the labor filter's row shape, carrying lot / split", () => {
    expect(toSyncedWorkOrderOperationRows([op("J1", "30", "1")])).toEqual([
      { work_order_id: "J1", lot: "0", split: "0", sub: "1", sequence_number: "30" },
    ]);
  });
});

describe("LaborWorkOrderGapFillTracker", () => {
  let sqlitePath: string;

  beforeEach(() => {
    sqlitePath = path.join(os.tmpdir(), `gap-fill-tracker-${process.pid}-${Date.now()}.sqlite3`);
    process.env.SQLITE_DB_PATH = sqlitePath;
    resetRuntimeStateDbForTests();
  });

  afterEach(() => {
    resetRuntimeStateDbForTests();
    delete process.env.SQLITE_DB_PATH;
    for (const suffix of ["", "-wal", "-shm"]) {
      try {
        fs.rmSync(`${sqlitePath}${suffix}`);
      } catch {
        // ignore
      }
    }
  });

  it("requires a connector type", () => {
    expect(() => new LaborWorkOrderGapFillTracker(" ")).toThrow(/connectorType/);
  });

  it("returns [] when nothing was saved", () => {
    expect(new LaborWorkOrderGapFillTracker("epicor").load()).toEqual([]);
  });

  it("round-trips a watch list, including an empty one", () => {
    const tracker = new LaborWorkOrderGapFillTracker("epicor");
    const entries = [{ workOrderId: "J1", lastLaborSeenAt: "2026-09-21T00:00:00.000Z" }];
    tracker.save(entries);
    expect(tracker.load()).toEqual(entries);
    tracker.save([]);
    expect(tracker.load()).toEqual([]);
  });

  it("namespaces by connector type", () => {
    new LaborWorkOrderGapFillTracker("a").save([{ workOrderId: "J1", lastLaborSeenAt: "2026-09-21T00:00:00.000Z" }]);
    expect(new LaborWorkOrderGapFillTracker("b").load()).toEqual([]);
  });

  it("throws when the store cannot be read, rather than returning a list a save would persist", () => {
    // A directory cannot be opened as a SQLite database.
    process.env.SQLITE_DB_PATH = os.tmpdir();
    expect(() => new LaborWorkOrderGapFillTracker("epicor").load()).toThrow();
  });

  it("starts empty when the stored content is not valid JSON, and the next save replaces it", () => {
    const tracker = new LaborWorkOrderGapFillTracker("epicor");
    tracker.save([{ workOrderId: "J1", lastLaborSeenAt: "2026-09-21T00:00:00.000Z" }]);
    const raw = new Database(sqlitePath);
    raw.prepare("UPDATE sdk_runtime_state SET value = ? WHERE key = ?").run("{not json", "laborWorkOrderGapFill:v1:epicor");
    raw.close();
    expect(tracker.load()).toEqual([]);
    const entries = [{ workOrderId: "J2", lastLaborSeenAt: "2026-09-22T00:00:00.000Z" }];
    tracker.save(entries);
    expect(tracker.load()).toEqual(entries);
  });

  it("starts empty when the stored content is not an array", () => {
    setRuntimeStateJson("laborWorkOrderGapFill:v1:epicor", { workOrderId: "J1" });
    expect(new LaborWorkOrderGapFillTracker("epicor").load()).toEqual([]);
  });

  it("drops malformed entries on load", () => {
    const tracker = new LaborWorkOrderGapFillTracker("epicor");
    tracker.save([
      { workOrderId: "J1", lastLaborSeenAt: "2026-09-21T00:00:00.000Z" },
      { workOrderId: 5 } as never,
      null as never,
    ]);
    expect(tracker.load().map((t) => t.workOrderId)).toEqual(["J1"]);
  });
});