// Fresh module per test pour reset dbPromise + sqlite state.
type Db = {
  execAsync: jest.Mock;
  runAsync: jest.Mock;
  getAllAsync: jest.Mock;
  getFirstAsync: jest.Mock;
  withTransactionAsync: jest.Mock;
};

function setupSqliteMock(): Db {
  const db: Db = {
    execAsync: jest.fn().mockResolvedValue(undefined),
    runAsync: jest.fn().mockResolvedValue(undefined),
    getAllAsync: jest.fn().mockResolvedValue([]),
    getFirstAsync: jest.fn().mockResolvedValue(null),
    withTransactionAsync: jest.fn(async (fn: () => Promise<void>) => {
      await fn();
    }),
  };

  jest.resetModules();
  jest.doMock("expo-sqlite", () => ({
    openDatabaseAsync: jest.fn().mockResolvedValue(db),
  }));

  return db;
}

function loadModule() {
  return require("../../src/core/localDatabase");
}

describe("localDatabase", () => {
  let db: Db;
  let mod: ReturnType<typeof loadModule>;

  beforeEach(() => {
    db = setupSqliteMock();
    mod = loadModule();
  });

  describe("initializeLocalDatabase + ensureStepsSchema", () => {
    it("cree les tables et ajoute les colonnes manquantes", async () => {
      db.getAllAsync.mockResolvedValue([]); // pragma table_info vide → tous ALTER declenches
      await mod.initializeLocalDatabase();
      expect(db.execAsync).toHaveBeenCalled();
      // Beaucoup d'ALTER TABLE (13 colonnes)
      expect(db.runAsync.mock.calls.length).toBeGreaterThanOrEqual(13);
    });

    it("skip les ALTER quand colonnes existent deja", async () => {
      db.getAllAsync.mockResolvedValue(
        [
          "step_id",
          "order_index",
          "type",
          "label",
          "required",
          "status",
          "value_json",
          "unit",
          "constraints_json",
          "options_json",
          "metadata_json",
          "files_json",
          "is_visible",
          "version",
          "dirty",
        ].map((name) => ({ name }))
      );
      await mod.initializeLocalDatabase();
      expect(db.runAsync).not.toHaveBeenCalled();
    });
  });

  describe("clearLocalDatabase", () => {
    it("exec DELETE sur toutes les tables", async () => {
      await mod.clearLocalDatabase();
      expect(db.execAsync).toHaveBeenCalledWith(expect.stringContaining("DELETE FROM missions"));
    });
  });

  describe("saveOrUpdateStep", () => {
    const step = {
      id: "s1",
      intervention_id: 1,
      order_index: 0,
      type: "text",
      label: "L",
      required: true,
      status: "done" as const,
      value: "v",
      unit: "m",
      constraints: { max_length: 10 },
      options: ["a"],
      metadata: { phase: "P" },
      files: [],
      version: 2,
      updated_at: "2026-01-01T10:00:00.000Z",
    };

    it("stringifie et insere avec dirty=0 par defaut", async () => {
      await mod.saveOrUpdateStep(step);
      const args = db.runAsync.mock.calls[0][1];
      expect(args[0]).toBe(1); // intervention_id
      expect(args[1]).toBe("s1");
      expect(args[5]).toBe(1); // required=1
      expect(args[7]).toBe('"v"'); // value
      expect(args[13]).toBeNull(); // is_visible absent -> null
      expect(args[14]).toBe(2); // version
      expect(args[15]).toBe(0); // dirty=0
    });

    it("dirty=1 quand parametre true", async () => {
      await mod.saveOrUpdateStep(step, true);
      const args = db.runAsync.mock.calls[0][1];
      expect(args[15]).toBe(1);
    });

    it("is_visible mappe le booleen serveur en 1/0", async () => {
      await mod.saveOrUpdateStep({ ...step, is_visible: false });
      expect(db.runAsync.mock.calls[0][1][13]).toBe(0);
      db.runAsync.mockClear();
      await mod.saveOrUpdateStep({ ...step, is_visible: true });
      expect(db.runAsync.mock.calls[0][1][13]).toBe(1);
    });

    it("defauts quand fields optionnels absents", async () => {
      const s = { ...step, unit: undefined, constraints: undefined, options: undefined, metadata: undefined, files: undefined, value: null };
      await mod.saveOrUpdateStep(s);
      const args = db.runAsync.mock.calls[0][1];
      expect(args[7]).toBe("null");
      expect(args[8]).toBeNull();
      expect(args[9]).toBe("{}");
      expect(args[10]).toBe("[]");
      expect(args[11]).toBe("{}");
      expect(args[12]).toBe("[]");
    });

    it("safeIso fallback epoch pour updated_at invalide", async () => {
      const s = { ...step, updated_at: "" };
      await mod.saveOrUpdateStep(s);
      const args = db.runAsync.mock.calls[0][1];
      expect(args[16]).toBe("1970-01-01T00:00:00.000Z");
    });

    it("safeIso preserve dates valides", async () => {
      const s = { ...step, updated_at: "2026-05-01T00:00:00.000Z" };
      await mod.saveOrUpdateStep(s);
      const args = db.runAsync.mock.calls[0][1];
      expect(args[16]).toBe("2026-05-01T00:00:00.000Z");
    });
  });

  describe("applyServerStepsDelta", () => {
    const makeStep = (over: Record<string, unknown> = {}) => ({
      id: "s1",
      intervention_id: 1,
      order_index: 0,
      type: "text",
      label: "",
      required: false,
      status: "todo",
      value: "server",
      version: 2,
      updated_at: "2026-01-02T00:00:00.000Z",
      ...over,
    });

    it("step non existant localement -> applique", async () => {
      db.getFirstAsync.mockResolvedValue(null);
      const n = await mod.applyServerStepsDelta([makeStep()]);
      expect(n).toBe(1);
    });

    it("server version > local -> applique sans conflict si non-dirty", async () => {
      db.getFirstAsync.mockResolvedValue({
        value_json: '"old"',
        version: 1,
        updated_at: "2026-01-01T00:00:00.000Z",
        dirty: 0,
      });
      const n = await mod.applyServerStepsDelta([makeStep()]);
      expect(n).toBe(1);
      // pas d'insert dans step_conflicts
      const conflictInsert = db.runAsync.mock.calls.find((c) =>
        String(c[0]).includes("step_conflicts")
      );
      expect(conflictInsert).toBeUndefined();
    });

    it("server version > local + dirty=1 -> applique + conflict", async () => {
      db.getFirstAsync.mockResolvedValue({
        value_json: '"local"',
        version: 1,
        updated_at: "2026-01-01T00:00:00.000Z",
        dirty: 1,
      });
      const n = await mod.applyServerStepsDelta([makeStep()]);
      expect(n).toBe(1);
      const conflictInsert = db.runAsync.mock.calls.find((c) =>
        String(c[0]).includes("step_conflicts")
      );
      expect(conflictInsert).toBeDefined();
    });

    it("versions egales + server updated_at plus recent -> applique", async () => {
      db.getFirstAsync.mockResolvedValue({
        value_json: null,
        version: 2,
        updated_at: "2026-01-01T00:00:00.000Z",
        dirty: 0,
      });
      const n = await mod.applyServerStepsDelta([makeStep()]);
      expect(n).toBe(1);
    });

    it("versions egales + server updated_at plus ancien -> skip", async () => {
      db.getFirstAsync.mockResolvedValue({
        value_json: null,
        version: 2,
        updated_at: "2026-12-31T00:00:00.000Z",
        dirty: 0,
      });
      const n = await mod.applyServerStepsDelta([makeStep()]);
      expect(n).toBe(0);
    });

    it("server version < local -> skip", async () => {
      db.getFirstAsync.mockResolvedValue({
        value_json: null,
        version: 5,
        updated_at: "2026-01-01T00:00:00.000Z",
        dirty: 0,
      });
      const n = await mod.applyServerStepsDelta([makeStep({ version: 1 })]);
      expect(n).toBe(0);
    });

    it("gere step.version=0 / value undefined (?? fallback) en conflit", async () => {
      db.getFirstAsync.mockResolvedValue({
        value_json: '"local"',
        version: 1,
        updated_at: "2026-01-01T00:00:00.000Z",
        dirty: 1,
      });
      const n = await mod.applyServerStepsDelta([
        makeStep({ version: 0, value: undefined }),
      ]);
      // serverVersion=0 et localVersion=1: server < local -> skip
      expect(n).toBe(0);
    });

    it("server version > local + dirty=1 + value undefined (??)", async () => {
      db.getFirstAsync.mockResolvedValue({
        value_json: '"local"',
        version: 1,
        updated_at: "2026-01-01T00:00:00.000Z",
        dirty: 1,
      });
      const n = await mod.applyServerStepsDelta([
        makeStep({ version: 3, value: undefined }),
      ]);
      expect(n).toBe(1);
      const conflictInsert = db.runAsync.mock.calls.find((c) =>
        String(c[0]).includes("step_conflicts")
      );
      expect(conflictInsert?.[1][3]).toBe("null");
    });

    it("gere version local invalide (NaN fallback 0)", async () => {
      db.getFirstAsync.mockResolvedValue({
        value_json: null,
        version: null,
        updated_at: "2026-01-01T00:00:00.000Z",
        dirty: null,
      });
      const n = await mod.applyServerStepsDelta([makeStep()]);
      expect(n).toBe(1);
    });
  });

  describe("saveInterventionsSnapshot", () => {
    it("delete + insert chaque item + update local_meta", async () => {
      await mod.saveInterventionsSnapshot([{ id: 1 }, { id: 2 }]);
      const calls = db.runAsync.mock.calls;
      expect(calls[0][0]).toContain("DELETE FROM missions");
      expect(calls.some((c) => String(c[0]).includes("INSERT INTO missions"))).toBe(true);
      expect(calls.some((c) => (c[1] as unknown[])?.[0] === "missions_snapshot_count")).toBe(true);
    });
  });

  describe("mergeInterventionsSnapshot", () => {
    it("upsert chaque item (INSERT OR REPLACE) sans DELETE global", async () => {
      await mod.mergeInterventionsSnapshot([{ id: 1 }, { id: 2 }], null);
      const calls = db.runAsync.mock.calls;
      // Aucun DELETE FROM missions global (merge non destructif, keepIds=null).
      expect(calls.every((c) => !String(c[0]).includes("DELETE FROM missions WHERE"))).toBe(true);
      expect(calls.some((c) => String(c[0]).includes("INSERT OR REPLACE INTO missions"))).toBe(true);
      expect(calls.some((c) => (c[1] as unknown[])?.[0] === "missions_snapshot_count")).toBe(true);
    });

    it("purge les ids absents de keepIds (DELETE ... NOT IN)", async () => {
      await mod.mergeInterventionsSnapshot([{ id: 1 }], [1, 2, 3]);
      const calls = db.runAsync.mock.calls;
      const del = calls.find((c) => String(c[0]).includes("DELETE FROM missions WHERE id NOT IN"));
      expect(del).toBeDefined();
      expect(del?.[1]).toEqual([1, 2, 3]);
    });

    it("keepIds=[] vide entierement la table", async () => {
      await mod.mergeInterventionsSnapshot([], []);
      const calls = db.runAsync.mock.calls;
      expect(calls.some((c) => String(c[0]).trim() === "DELETE FROM missions")).toBe(true);
    });
  });

  describe("getInterventionsSnapshot", () => {
    it("parse chaque row, ignore les JSON corrompus", async () => {
      db.getAllAsync.mockResolvedValue([
        { id: 1, payload: '{"id":1}', updated_at: "u" },
        { id: 2, payload: "{bad", updated_at: "u" },
      ]);
      db.getFirstAsync.mockResolvedValue({ value: "2026-01-01" });
      const snap = await mod.getInterventionsSnapshot();
      expect(snap.items).toHaveLength(1);
      expect(snap.count).toBe(1);
      expect(snap.updated_at).toBe("2026-01-01");
    });

    it("updated_at vide quand meta absent", async () => {
      db.getAllAsync.mockResolvedValue([]);
      db.getFirstAsync.mockResolvedValue(null);
      const snap = await mod.getInterventionsSnapshot();
      expect(snap.updated_at).toBe("");
    });
  });

  describe("saveInterventionDetail", () => {
    it("insert/update avec ON CONFLICT", async () => {
      await mod.saveInterventionDetail({ id: 5 });
      expect(db.runAsync.mock.calls[0][0]).toContain("interventions");
    });
  });

  describe("getInterventionDetailById", () => {
    it("retourne null si rien", async () => {
      db.getFirstAsync.mockResolvedValue(null);
      expect(await mod.getInterventionDetailById(5)).toBeNull();
    });

    it("retourne item parse", async () => {
      db.getFirstAsync.mockResolvedValue({ payload: '{"id":5}', updated_at: "2026-01-01" });
      const res = await mod.getInterventionDetailById(5);
      expect(res?.item).toEqual({ id: 5 });
      expect(res?.updated_at).toBe("2026-01-01");
    });

    it("retourne null si JSON corrompu", async () => {
      db.getFirstAsync.mockResolvedValue({ payload: "{bad", updated_at: "x" });
      expect(await mod.getInterventionDetailById(5)).toBeNull();
    });
  });

  describe("enqueueSyncAction", () => {
    it("insert dans sync_queue", async () => {
      await mod.enqueueSyncAction({
        endpoint: "/x",
        method: "POST",
        payload: { a: 1 },
        entityType: "e",
        entityId: "1",
      });
      const args = db.runAsync.mock.calls[0];
      expect(args[0]).toContain("sync_queue");
      expect(args[1][0]).toBe("/x");
      expect(args[1][1]).toBe("POST");
      expect(args[1][2]).toBe('{"a":1}');
    });

    // M5 — coalescence : un save_step deja en file pour le meme step est REMPLACE
    // (derniere valeur gagne), pas empile.
    it("coalesce un save_step deja pending pour le meme step (UPDATE, pas INSERT)", async () => {
      db.getFirstAsync.mockResolvedValue({
        id: 7,
        payload: JSON.stringify({ action: "save_step", value: "ancienne" }),
      });

      await mod.enqueueSyncAction({
        endpoint: "/mobile/intervention_workflow.php",
        method: "POST",
        payload: { action: "save_step", value: "nouvelle" },
        entityType: "step",
        entityId: "258:abc",
      });

      const update = db.runAsync.mock.calls.find((c: unknown[]) =>
        String(c[0]).includes("UPDATE sync_queue")
      );
      expect(update).toBeDefined();
      // Le payload est remplace ET le budget de retries remis a zero (status
      // pending, attempts 0, last_error NULL) — #5.
      expect(String(update[0])).toContain("status = 'pending'");
      expect(String(update[0])).toContain("attempts = 0");
      expect(String(update[0])).toContain("last_error = NULL");
      expect(update[1]).toEqual([
        JSON.stringify({ action: "save_step", value: "nouvelle" }),
        expect.any(String),
        7,
      ]);
      // Pas d'INSERT (on a remplace)
      const insert = db.runAsync.mock.calls.find((c: unknown[]) =>
        String(c[0]).includes("INSERT INTO sync_queue")
      );
      expect(insert).toBeUndefined();
    });

    it("n'coalesce PAS deux actions differentes sur la meme entite (INSERT)", async () => {
      db.getFirstAsync.mockResolvedValue({
        id: 7,
        payload: JSON.stringify({ action: "intervention_start" }),
      });

      await mod.enqueueSyncAction({
        endpoint: "/mobile/intervention_workflow.php",
        method: "POST",
        payload: { action: "complete_workflow", version: 3 },
        entityType: "intervention",
        entityId: "258",
      });

      const insert = db.runAsync.mock.calls.find((c: unknown[]) =>
        String(c[0]).includes("INSERT INTO sync_queue")
      );
      expect(insert).toBeDefined();
    });

    it("payload multipart (sans action) n'est jamais coalesce (INSERT direct)", async () => {
      await mod.enqueueSyncAction({
        endpoint: "/up",
        method: "POST",
        payload: { __multipart: true, upload_files: [] },
        entityType: "step",
        entityId: "258:photo",
      });
      // getFirstAsync ne doit meme pas etre consulte (pas d'action)
      expect(db.getFirstAsync).not.toHaveBeenCalled();
      const insert = db.runAsync.mock.calls.find((c: unknown[]) =>
        String(c[0]).includes("INSERT INTO sync_queue")
      );
      expect(insert).toBeDefined();
    });
  });

  describe("getPendingQueueItems", () => {
    it("mappe les rows, skip JSON corrompu, limit par defaut 50", async () => {
      db.getAllAsync.mockResolvedValue([
        {
          id: 1,
          endpoint: "/x",
          method: "POST",
          payload: '{"a":1}',
          entity_type: "e",
          entity_id: "1",
          created_at: "c",
          attempts: 0,
        },
        {
          id: 2,
          endpoint: "/y",
          method: "POST",
          payload: "{bad",
          entity_type: "e",
          entity_id: "2",
          created_at: "c",
          attempts: 0,
        },
      ]);
      const items = await mod.getPendingQueueItems();
      expect(items).toHaveLength(1);
      expect(items[0].payload).toEqual({ a: 1 });
      expect(db.getAllAsync.mock.calls[0][1]).toEqual([50]);
    });

    it("accepte une limit custom", async () => {
      db.getAllAsync.mockResolvedValue([]);
      await mod.getPendingQueueItems(10);
      expect(db.getAllAsync.mock.calls[0][1]).toEqual([10]);
    });
  });

  describe("markQueueItemDone / markQueueItemFailed / markQueueItemDead", () => {
    it("done -> DELETE", async () => {
      await mod.markQueueItemDone(1);
      expect(db.runAsync).toHaveBeenCalledWith(expect.stringContaining("DELETE"), [1]);
    });

    it("failed -> UPDATE avec message tronque a 500 + bascule dead au cap", async () => {
      const longMsg = "x".repeat(600);
      await mod.markQueueItemFailed(1, longMsg);
      const sql = db.runAsync.mock.calls[0][0];
      const args = db.runAsync.mock.calls[0][1];
      expect(args[0].length).toBe(500);
      // attempts++ et passage 'dead' une fois MAX_SYNC_ATTEMPTS atteint.
      expect(sql).toContain("attempts = attempts + 1");
      expect(sql).toContain("'dead'");
      expect(args).toContain(mod.MAX_SYNC_ATTEMPTS);
    });

    it("dead -> UPDATE status='dead' immediat, message tronque a 500", async () => {
      const longMsg = "y".repeat(600);
      await mod.markQueueItemDead(7, longMsg);
      const sql = db.runAsync.mock.calls[0][0];
      const args = db.runAsync.mock.calls[0][1];
      expect(sql).toContain("status = 'dead'");
      expect(args[0].length).toBe(500);
      expect(args[args.length - 1]).toBe(7);
    });
  });

  describe("countDeadQueueItems", () => {
    it("retourne le COUNT des items status='dead'", async () => {
      db.getFirstAsync.mockResolvedValue({ c: 3 });
      const n = await mod.countDeadQueueItems();
      expect(n).toBe(3);
      expect(db.getFirstAsync.mock.calls[0][0]).toContain("status = 'dead'");
    });

    it("retourne 0 si pas de row", async () => {
      db.getFirstAsync.mockResolvedValue(null);
      expect(await mod.countDeadQueueItems()).toBe(0);
    });
  });

  describe("getLastSyncTimestamp / setLastSyncTimestamp", () => {
    it("get: retourne null si absent", async () => {
      db.getFirstAsync.mockResolvedValue(null);
      expect(await mod.getLastSyncTimestamp()).toBeNull();
    });

    it("get: retourne value", async () => {
      db.getFirstAsync.mockResolvedValue({ value: "2026-01-01" });
      expect(await mod.getLastSyncTimestamp()).toBe("2026-01-01");
    });

    it("get: null si row.value falsy", async () => {
      db.getFirstAsync.mockResolvedValue({ value: "" });
      expect(await mod.getLastSyncTimestamp()).toBeNull();
    });

    it("set: upsert dans local_meta", async () => {
      await mod.setLastSyncTimestamp("2026-01-01");
      expect(db.runAsync.mock.calls[0][1][0]).toBe("last_sync_at");
    });
  });

  describe("saveWorkflowDraft / loadWorkflowDraft / clearWorkflowDraft", () => {
    const draft = {
      inputValues: { s: "v" },
      commentValues: {},
      currentPhaseIdx: 0,
      sigTechData: "",
      sigClientData: "",
      sigClientPresent: false,
      showSignatureCard: false,
      updatedAt: "2026-01-01",
    };

    it("save -> upsert", async () => {
      await mod.saveWorkflowDraft(5, draft);
      expect(db.runAsync.mock.calls[0][1][0]).toBe("workflow_draft_5");
    });

    it("load: null si absent", async () => {
      db.getFirstAsync.mockResolvedValue(null);
      expect(await mod.loadWorkflowDraft(5)).toBeNull();
    });

    it("load: parse et retourne draft", async () => {
      db.getFirstAsync.mockResolvedValue({ value: JSON.stringify(draft) });
      expect(await mod.loadWorkflowDraft(5)).toEqual(draft);
    });

    it("load: null si JSON corrompu", async () => {
      db.getFirstAsync.mockResolvedValue({ value: "{bad" });
      expect(await mod.loadWorkflowDraft(5)).toBeNull();
    });

    it("load: null si row.value falsy", async () => {
      db.getFirstAsync.mockResolvedValue({ value: "" });
      expect(await mod.loadWorkflowDraft(5)).toBeNull();
    });

    it("clear -> DELETE", async () => {
      await mod.clearWorkflowDraft(5);
      expect(db.runAsync.mock.calls[0][1]).toEqual(["workflow_draft_5"]);
    });
  });

  describe("getStepsByInterventionId", () => {
    const base = {
      step_id: "s1",
      order_index: 0,
      type: "text",
      label: "L",
      required: 1,
      status: "done",
      value_json: '"v"',
      unit: "m",
      constraints_json: '{"max_length":5}',
      options_json: '["a"]',
      metadata_json: '{"phase":"P"}',
      files_json: "[]",
      version: 1,
      updated_at: "2026-01-01",
      dirty: 0,
    };

    it("mappe les rows en MobileStep, toutes branches JSON OK", async () => {
      db.getAllAsync.mockResolvedValue([base]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps).toHaveLength(1);
      expect(steps[0].id).toBe("s1");
      expect(steps[0].required).toBe(true);
      expect(steps[0].value).toBe("v");
      expect(steps[0].constraints).toEqual({ max_length: 5 });
      expect(steps[0].options).toEqual(["a"]);
      expect(steps[0].metadata).toEqual({ phase: "P" });
      expect(steps[0].files).toEqual([]);
    });

    it("JSON corrompu value_json -> null", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, value_json: "{bad" }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].value).toBeNull();
    });

    it("value_json null -> null", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, value_json: null }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].value).toBeNull();
    });

    it("constraints_json corrompu -> {}", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, constraints_json: "{bad" }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].constraints).toEqual({});
    });

    it("constraints_json null -> undefined", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, constraints_json: null }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].constraints).toBeUndefined();
    });

    it("options_json corrompu -> []", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, options_json: "{bad" }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].options).toEqual([]);
    });

    it("options_json null -> undefined", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, options_json: null }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].options).toBeUndefined();
    });

    it("metadata_json corrompu -> {}", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, metadata_json: "{bad" }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].metadata).toEqual({});
    });

    it("metadata_json null -> undefined", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, metadata_json: null }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].metadata).toBeUndefined();
    });

    it("files_json corrompu -> []", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, files_json: "{bad" }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].files).toEqual([]);
    });

    it("files_json null -> []", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, files_json: null }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].files).toEqual([]);
    });

    it("unit null -> undefined", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, unit: null }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].unit).toBeUndefined();
    });

    it("required=0 -> false", async () => {
      db.getAllAsync.mockResolvedValue([{ ...base, required: 0 }]);
      const steps = await mod.getStepsByInterventionId(1);
      expect(steps[0].required).toBe(false);
    });
  });

  describe("updateStepValueLocally", () => {
    it("update avec dirty=1", async () => {
      await mod.updateStepValueLocally(1, "s1", { status: "done", value: "v", version: 3 });
      const args = db.runAsync.mock.calls[0][1];
      expect(args[0]).toBe("done");
      expect(args[1]).toBe('"v"');
      expect(args[2]).toBe(3);
    });

    it("value undefined -> null stringifie", async () => {
      await mod.updateStepValueLocally(1, "s1", { status: "done", version: 2 });
      const args = db.runAsync.mock.calls[0][1];
      expect(args[1]).toBe("null");
    });
  });

  describe("getDb — cache module", () => {
    it("reutilise la meme promise sur appels consecutifs", async () => {
      const SQLite = require("expo-sqlite");
      await mod.setLastSyncTimestamp("x");
      await mod.setLastSyncTimestamp("y");
      expect(SQLite.openDatabaseAsync).toHaveBeenCalledTimes(1);
    });
  });
});