import { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("labor_ticket_sync_ledger", (table) => {
    table.increments("id");

    // Connector/system identifier (e.g. "dynamics-nav", "syteline", etc.)
    table.string("system").notNullable().index();
    table.string("laborTicketRef").notNullable().index();

    // Most recent "business signature" we attempted for this ref (excludes updatedAt/laborTicketId).
    table.string("signatureHash").notNullable().index();
    table.text("signature").notNullable();

    // Metadata for debugging/diagnostics only.
    table.datetime("mmUpdatedAt", { useTz: true, precision: 3 }).nullable().index();
    table.datetime("mmComparableTs", { useTz: true, precision: 3 }).nullable().index();

    table.string("lastOutcome").notNullable().index(); // "success" | "failure"
    table.text("lastErrorSummary").nullable();
    table.datetime("lastAttemptedAt", { useTz: true, precision: 3 }).notNullable();

    table.unique(["system", "laborTicketRef"]);
    table.timestamps(true, true);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTable("labor_ticket_sync_ledger");
}