import knex, { Knex } from "knex";
import config from "../../knexfile.js";
import { createHash } from "crypto";

const TABLE = "labor_ticket_sync_ledger";

export type LaborTicketLedgerOutcome = "success" | "failure";

export type LaborTicketSyncLedgerRow = {
  id: number;
  system: string;
  laborTicketRef: string;
  signatureHash: string;
  signature: string;
  mmUpdatedAt: string | null;
  mmComparableTs: string | null;
  lastOutcome: LaborTicketLedgerOutcome;
  lastErrorSummary: string | null;
  lastAttemptedAt: string;
  created_at?: string;
  updated_at?: string;
};

export class LaborTicketSyncLedgerManager {
  private readonly db: Knex;

  constructor() {
    this.db = knex(config.local);
  }

  /**
   * IMPORTANT: SQLite ledger keys must be stable strings.
   *
   * In practice, MM payloads can drift between `number` and `string` representations across cycles
   * (and across different API surfaces / serializers). If we treat those as different keys, we will
   * fail to retrieve previously-written ledger rows and reprocess tickets unnecessarily.
   *
   * This helper coerces the key to a non-empty string (or throws) to guarantee consistent
   * store/retrieve behavior.
   */
  private static coerceKeyToString(value: unknown, keyName: string): string {
    if (value === null || value === undefined) {
      throw new Error(`Ledger key "${keyName}" is ${String(value)}`);
    }
    const s = String(value);
    if (s.length === 0) {
      throw new Error(`Ledger key "${keyName}" is empty`);
    }
    return s;
  }

  static hashSignature(signature: string): string {
    return createHash("sha256").update(signature).digest("hex");
  }

  async getLatestByRef(options: {
    system: unknown;
    laborTicketRef: unknown;
  }): Promise<LaborTicketSyncLedgerRow | null> {
    const system = LaborTicketSyncLedgerManager.coerceKeyToString(
      options.system,
      "system"
    );
    const laborTicketRef = LaborTicketSyncLedgerManager.coerceKeyToString(
      options.laborTicketRef,
      "laborTicketRef"
    );

    const row = await this.db<LaborTicketSyncLedgerRow>(TABLE)
      .where({ system, laborTicketRef })
      .first();

    return row ?? null;
  }

  async upsertAttempt(options: {
    system: unknown;
    laborTicketRef: unknown;
    signature: string;
    outcome: LaborTicketLedgerOutcome;
    mmUpdatedAt: string | null;
    mmComparableTs: string | null;
    errorSummary: string | null;
    attemptedAtIso: string;
  }): Promise<void> {
    const {
      signature,
      outcome,
      mmUpdatedAt,
      mmComparableTs,
      errorSummary,
      attemptedAtIso,
    } = options;

    const system = LaborTicketSyncLedgerManager.coerceKeyToString(
      options.system,
      "system"
    );
    const laborTicketRef = LaborTicketSyncLedgerManager.coerceKeyToString(
      options.laborTicketRef,
      "laborTicketRef"
    );

    const signatureHash = LaborTicketSyncLedgerManager.hashSignature(signature);

    // SQLite upsert via "insert then update if missing".
    const updated = await this.db(TABLE)
      .where({ system, laborTicketRef })
      .update({
        signatureHash,
        signature,
        mmUpdatedAt,
        mmComparableTs,
        lastOutcome: outcome,
        lastErrorSummary: errorSummary,
        lastAttemptedAt: attemptedAtIso,
      });

    if (updated < 1) {
      await this.db(TABLE).insert({
        system,
        laborTicketRef,
        signatureHash,
        signature,
        mmUpdatedAt,
        mmComparableTs,
        lastOutcome: outcome,
        lastErrorSummary: errorSummary,
        lastAttemptedAt: attemptedAtIso,
      });
    }
  }

  async destroy(): Promise<void> {
    await this.db.destroy();
  }
}