import { isModuleDatabaseError, ModuleDatabaseError } from "@ekklesia/module-database";
import { moduleDrizzle } from "@ekklesia/module-database/drizzle";
import { describe, expect, it } from "vitest";
import { imageAssets } from "../src/schema/tables.js";
import { createSignageStore } from "../src/server/store/drizzle.js";
import {
  type NewStoredImage,
  type NewStoredSelection,
  type NewStoredSlot,
  type SignageStore,
  SignageStoreConflict,
} from "../src/server/store/types.js";
import { type AbiResponder, createFakeAbiExecutor } from "./support/abi-executor.js";

/**
 * Signage's production store over the platform's Module Database ABI v1 (platform ADR 0050).
 *
 * The executor double holds the store to the ABI's own rules (`./support/abi-executor.ts`), so what
 * these prove is what Signage hands Core and how it reads Core's answers: SQL text, ABI parameters,
 * positional rows of raw text forms, and callback-scoped transactions. The decisions above the store
 * are proven against the in-memory store elsewhere.
 */

const GROUP = "00000000-0000-4000-8000-0000000000a1";
const CHANNEL = "00000000-0000-4000-8000-0000000000b1";
const FALLBACK_CHANNEL = "00000000-0000-4000-8000-0000000000b2";
const SELECTION = "00000000-0000-4000-8000-0000000000c1";
const TIMELINE = "00000000-0000-4000-8000-0000000000d1";
const SOURCE = "00000000-0000-4000-8000-0000000000e1";
const SLOT_1 = "00000000-0000-4000-8000-0000000000f1";
const SLOT_2 = "00000000-0000-4000-8000-0000000000f2";
const IMAGE = "00000000-0000-4000-8000-000000000101";

const INSTANT = new Date("2026-10-08T12:34:56.789Z");
/** How Core sends that instant: PostgreSQL's ISO output in UTC (ADR 0050's decoding table). */
const INSTANT_TEXT = "2026-10-08 12:34:56.789+00";

function image(): NewStoredImage {
  return {
    contentSha256: "a".repeat(64),
    displayName: "Welcome",
    encodedHeight: 1080,
    encodedWidth: 1920,
    expiresAt: INSTANT,
    hasAlpha: false,
    height: 1080,
    id: IMAGE,
    orientation: null,
    originalByteSize: 5_000_000_000,
    originalContentType: "image/png",
    originalFilename: "welcome.png",
    originalObjectKey: `originals/${IMAGE}`,
    renditions: [
      {
        byteSize: 1234,
        contentSha256: "b".repeat(64),
        contentType: "image/webp",
        height: 1080,
        kind: "display",
        objectKey: `renditions/${IMAGE}/display`,
        width: 1920,
      },
    ],
    status: "ready",
    width: 1920,
  };
}

function selection(): NewStoredSelection {
  return {
    availableUntil: null,
    deadlineAt: new Date(INSTANT.getTime() + 7_000),
    durationMs: 7_000,
    groupId: GROUP,
    id: SELECTION,
    itemId: IMAGE,
    itemRevision: "1",
    presentation: { tone: "warm" },
    providerId: "signage.native-images",
    rendererContributionId: null,
    rendererContributionVersion: null,
    selectionScope: null,
    sequence: 9_007_199_254_740_000,
    slotId: SLOT_1,
    stale: false,
    startedAt: INSTANT,
    timelineId: TIMELINE,
    variants: [
      { audienceKey: "default", isDefault: true, payload: { title: "Hello" } },
      { audienceKey: "online", isDefault: false, payload: "just text" },
    ],
    withdrawn: false,
  };
}

function slot(id: string): NewStoredSlot {
  return {
    durationOverrideMs: null,
    eligibleFrom: null,
    eligibleUntil: INSTANT,
    enabled: true,
    id,
    itemId: IMAGE,
    kind: "pinned-item",
    presentation: {
      backgroundColor: null,
      backgroundDarkened: null,
      backgroundMode: null,
      blurPixels: null,
      foregroundShadow: null,
    },
    providerId: "signage.native-images",
    sourceId: null,
    timelineId: TIMELINE,
  };
}

/** Answers a select from a table with the given rows, and every other statement with none. */
function rowsFrom(tables: Readonly<Record<string, AbiResponder>>): AbiResponder {
  return (statement) => tables[statement.summary]?.(statement);
}

function times(count: number, summary: string): string[] {
  return Array.from({ length: count }, () => summary);
}

describe("Signage's production store over the Module Database ABI v1", () => {
  it("reaches PostgreSQL only as SQL text, ABI parameters, and positional rows", async () => {
    const fake = createFakeAbiExecutor();

    await createSignageStore(fake.executor).listTimelines();
    await createSignageStore(fake.executor).findViewerByKey("kiosk-1");

    expect(fake.events).toEqual(["select signage_timelines", "select signage_viewers"]);
    for (const statement of fake.statements) {
      expect(statement.depth).toBe(0);
      expect(statement.rowMode).toBe("array");
      expect(statement.text).not.toContain("kiosk-1");
    }
    expect(fake.statements[1]?.values).toEqual(["kiosk-1"]);
  });

  it("encodes instants as ISO text and JSON as text, never as objects Core would refuse", async () => {
    const fake = createFakeAbiExecutor();

    await createSignageStore(fake.executor).insertSelection(selection());

    const [selectionInsert, variantsInsert] = fake.statements;
    expect(selectionInsert?.values).toContain("2026-10-08T12:34:56.789Z");
    expect(selectionInsert?.values).toContain("2026-10-08T12:35:03.789Z");
    expect(selectionInsert?.values).toContain('{"tone":"warm"}');
    expect(selectionInsert?.values).toContain(9_007_199_254_740_000);
    expect(variantsInsert?.values).toContain('{"title":"Hello"}');
    expect(variantsInsert?.values).toContain('"just text"');
  });

  it("decodes the ABI's raw int8, timestamptz, and jsonb text into the values Signage reads", async () => {
    const fake = createFakeAbiExecutor(
      rowsFrom({
        "select signage_channel_groups": () => [
          {
            created_at: INSTANT_TEXT,
            id: GROUP,
            name: "Sanctuary",
            revision: 3,
            updated_at: INSTANT_TEXT,
          },
        ],
        "select signage_channels": () => [
          { group_id: GROUP, id: CHANNEL, is_default: true, key: "default", name: "Default" },
        ],
        "select signage_image_assets": () => [
          {
            content_sha256: "a".repeat(64),
            created_at: "2026-10-08 12:00:00.123456+00",
            display_name: "Welcome",
            encoded_height: 1080,
            encoded_width: 1920,
            expires_at: null,
            has_alpha: false,
            height: 1080,
            id: IMAGE,
            orientation: 6,
            original_byte_size: "5000000000",
            original_content_type: "image/png",
            original_filename: "welcome.png",
            original_object_key: `originals/${IMAGE}`,
            status: "ready",
            width: 1920,
          },
        ],
        "select signage_image_renditions": () => [
          {
            asset_id: IMAGE,
            byte_size: "1234",
            content_sha256: "b".repeat(64),
            content_type: "image/webp",
            height: 1080,
            kind: "display",
            object_key: `renditions/${IMAGE}/display`,
            width: 1920,
          },
        ],
        "select signage_playback_states": () => [
          {
            active_timeline_id: TIMELINE,
            blackout: false,
            current_selection_id: SELECTION,
            current_slot_id: SLOT_1,
            group_id: GROUP,
            next_sequence: "42",
            paused: false,
            paused_remaining_ms: null,
            pending_requested_at: null,
            pending_timeline_id: null,
            revision: 17,
            scheduled_activation_at: "2026-10-09 07:30:00-05",
            scheduled_timeline_id: TIMELINE,
            slot_deadline_at: "2026-10-08 12:35:03.789+00",
            slot_started_at: INSTANT_TEXT,
            static_asset_id: null,
            updated_at: INSTANT_TEXT,
          },
        ],
        "select signage_selection_variants": () => [
          {
            audience_key: "default",
            id: "00000000-0000-4000-8000-000000000201",
            is_default: true,
            payload: '{"title":"Hello","lines":[1,2]}',
            selection_id: SELECTION,
          },
          {
            audience_key: "online",
            id: "00000000-0000-4000-8000-000000000202",
            is_default: false,
            payload: '"123"',
            selection_id: SELECTION,
          },
        ],
        "select signage_selections": () => [
          {
            available_until: null,
            deadline_at: null,
            duration_ms: 7000,
            group_id: GROUP,
            id: SELECTION,
            item_id: IMAGE,
            item_revision: "1",
            presentation: '{"tone":"warm"}',
            provider_id: "signage.native-images",
            renderer_contribution_id: null,
            renderer_contribution_version: null,
            resolved_at: INSTANT_TEXT,
            revision: 2,
            selection_scope: null,
            sequence: "9007199254740000",
            slot_id: SLOT_1,
            stale: false,
            started_at: INSTANT_TEXT,
            timeline_id: TIMELINE,
            withdrawn: false,
          },
        ],
        "update signage_playback_states": () => [{ next_sequence: "43" }],
      }),
    );
    const store = createSignageStore(fake.executor);

    const group = await store.findChannelGroup(GROUP);
    expect(group?.playback).toMatchObject({
      nextSequence: 42,
      revision: 17,
      scheduledActivationAt: new Date("2026-10-09T12:30:00.000Z"),
      slotDeadlineAt: new Date("2026-10-08T12:35:03.789Z"),
      slotStartedAt: INSTANT,
      updatedAt: INSTANT,
    });
    expect(group?.playback.slotStartedAt).toBeInstanceOf(Date);
    expect(group?.channels).toEqual([
      { groupId: GROUP, id: CHANNEL, isDefault: true, key: "default", name: "Default" },
    ]);

    const found = await store.findSelection(SELECTION);
    expect(found).toMatchObject({
      presentation: { tone: "warm" },
      resolvedAt: INSTANT,
      sequence: 9_007_199_254_740_000,
      startedAt: INSTANT,
    });
    expect(found?.variants).toEqual([
      { audienceKey: "default", isDefault: true, payload: { lines: [1, 2], title: "Hello" } },
      // A JSON string stays a string, even one that looks like a number.
      { audienceKey: "online", isDefault: false, payload: "123" },
    ]);

    // `returning` rows decode the same way: the sequence this call owns is one below the new value.
    await expect(store.takeSelectionSequence(GROUP)).resolves.toBe(42);

    const [listed] = await store.listImages();
    expect(listed).toMatchObject({
      createdAt: new Date("2026-10-08T12:00:00.123Z"),
      expiresAt: null,
      orientation: 6,
      originalByteSize: 5_000_000_000,
    });
    expect(listed?.renditions).toEqual([expect.objectContaining({ byteSize: 1234 })]);
  });
});

describe("Signage's transactions over the ABI", () => {
  const existingSlots = () => [
    { id: SLOT_1, position: 0 },
    { id: SLOT_2, position: 1 },
  ];
  const flows: readonly {
    readonly name: string;
    readonly respond?: AbiResponder;
    readonly run: (store: SignageStore) => Promise<unknown>;
    readonly expected: readonly string[];
  }[] = [
    {
      expected: ["insert signage_image_assets", "insert signage_image_renditions"],
      name: "insertImage",
      run: (store) => store.insertImage(image()),
    },
    {
      expected: ["insert signage_channels", "update signage_channel_groups"],
      name: "addChannel",
      run: (store) =>
        store.addChannel({
          groupId: GROUP,
          id: CHANNEL,
          isDefault: false,
          key: "online",
          name: "Online",
        }),
    },
    {
      expected: [
        "insert signage_channel_groups",
        "insert signage_channels",
        "insert signage_playback_states",
      ],
      name: "createChannelGroup",
      run: (store) =>
        store.createChannelGroup({
          channelId: CHANNEL,
          channelName: "Default",
          groupId: GROUP,
          name: "Sanctuary",
        }),
    },
    {
      expected: [
        "select signage_channels",
        "update signage_viewers",
        "delete signage_channel_groups",
      ],
      name: "deleteChannelGroup",
      respond: rowsFrom({ "select signage_channels": () => [{ id: CHANNEL }] }),
      run: (store) => store.deleteChannelGroup(GROUP, FALLBACK_CHANNEL),
    },
    {
      expected: ["insert signage_selections", "insert signage_selection_variants"],
      name: "insertSelection",
      run: (store) => store.insertSelection(selection()),
    },
    {
      expected: [
        "select signage_channels",
        "delete signage_channels",
        "update signage_channel_groups",
      ],
      name: "removeChannel",
      respond: rowsFrom({
        "select signage_channels": () => [
          { group_id: GROUP, id: CHANNEL, is_default: false, key: "online", name: "Online" },
        ],
      }),
      run: (store) => store.removeChannel(CHANNEL),
    },
    {
      expected: [
        "update signage_selections",
        "delete signage_selection_variants",
        "insert signage_selection_variants",
      ],
      name: "updateSelection",
      run: (store) =>
        store.updateSelection(SELECTION, {
          bumpRevision: true,
          stale: true,
          variants: [{ audienceKey: "default", isDefault: true, payload: { title: "Again" } }],
        }),
    },
    {
      expected: [
        "select signage_timeline_slots",
        "insert signage_timeline_slots",
        ...times(6, "update signage_timeline_slots"),
        "update signage_timelines",
      ],
      name: "addSlot",
      respond: rowsFrom({ "select signage_timeline_slots": existingSlots }),
      run: (store) => store.addSlot({ ...slot("00000000-0000-4000-8000-0000000000f3") }, 1),
    },
    {
      expected: [
        "insert signage_timelines",
        "insert signage_provider_sources",
        "insert signage_timeline_slots",
      ],
      name: "createTimeline",
      run: (store) =>
        store.createTimeline({
          slots: [slot(SLOT_1)],
          sources: [
            {
              id: SOURCE,
              label: "Announcements",
              providerId: "announcements",
              selectionScope: "scope-1",
              sourceId: "all",
              timelineId: TIMELINE,
            },
          ],
          timeline: {
            description: null,
            eligibleFrom: INSTANT,
            eligibleUntil: null,
            id: TIMELINE,
            name: "Sunday",
          },
        }),
    },
    {
      expected: [
        "select signage_timeline_slots",
        "delete signage_timeline_slots",
        "select signage_timeline_slots",
        ...times(2, "update signage_timeline_slots"),
        "update signage_timelines",
      ],
      name: "removeSlot",
      respond: rowsFrom({
        "select signage_timeline_slots": (statement) =>
          statement.text.includes('"position"')
            ? [{ id: SLOT_2, position: 1 }]
            : [{ timeline_id: TIMELINE }],
      }),
      run: (store) => store.removeSlot(SLOT_1),
    },
    {
      expected: [
        "select signage_provider_sources",
        "delete signage_provider_sources",
        "select signage_timeline_slots",
        ...times(4, "update signage_timeline_slots"),
        "update signage_timelines",
      ],
      name: "removeSource",
      respond: rowsFrom({
        "select signage_provider_sources": () => [{ timeline_id: TIMELINE }],
        "select signage_timeline_slots": existingSlots,
      }),
      run: (store) => store.removeSource(SOURCE),
    },
    {
      expected: [
        "select signage_timeline_slots",
        ...times(4, "update signage_timeline_slots"),
        "update signage_timelines",
      ],
      name: "reorderSlots",
      respond: rowsFrom({ "select signage_timeline_slots": existingSlots }),
      run: (store) => store.reorderSlots(TIMELINE, [SLOT_2, SLOT_1]),
    },
    {
      expected: [
        "select signage_timeline_slots",
        "update signage_timeline_slots",
        "update signage_timelines",
      ],
      name: "updateSlot",
      respond: rowsFrom({ "select signage_timeline_slots": () => [{ timeline_id: TIMELINE }] }),
      run: (store) => store.updateSlot(SLOT_1, { enabled: false, eligibleUntil: INSTANT }),
    },
  ];

  it("covers every transaction the store opens", () => {
    // Thirteen `database.transaction` calls in src/server/store, none of them nested.
    expect(flows.map((flow) => flow.name)).toHaveLength(13);
  });

  for (const flow of flows) {
    it(`${flow.name} runs as one ABI transaction and commits`, async () => {
      const fake = createFakeAbiExecutor(flow.respond);

      await flow.run(createSignageStore(fake.executor));

      expect(fake.events).toEqual(["BEGIN", ...flow.expected, "COMMIT"]);
      // Every statement ran inside the transaction's own scope, and none opened a savepoint.
      expect(fake.statements.every((statement) => statement.depth === 1)).toBe(true);
    });
  }

  it("rolls back a refused default-channel removal and reports the conflict", async () => {
    const fake = createFakeAbiExecutor(
      rowsFrom({
        "select signage_channels": () => [
          { group_id: GROUP, id: CHANNEL, is_default: true, key: "default", name: "Default" },
        ],
      }),
    );

    await expect(createSignageStore(fake.executor).removeChannel(CHANNEL)).rejects.toMatchObject({
      guarantee: "channel.default",
      name: "SignageStoreConflict",
    });
    expect(fake.events).toEqual(["BEGIN", "select signage_channels", "ROLLBACK"]);
  });

  it("rolls back a slot order that is not exactly the timeline's slots", async () => {
    const fake = createFakeAbiExecutor(
      rowsFrom({ "select signage_timeline_slots": existingSlots }),
    );

    await expect(
      createSignageStore(fake.executor).reorderSlots(TIMELINE, [SLOT_2]),
    ).rejects.toMatchObject({ guarantee: "timeline.slotOrder" });
    expect(fake.events).toEqual(["BEGIN", "select signage_timeline_slots", "ROLLBACK"]);
  });

  it("rolls back the whole transaction when a later statement fails, and keeps that error", async () => {
    const fake = createFakeAbiExecutor((statement) => {
      if (statement.summary === "insert signage_selection_variants") {
        throw new ModuleDatabaseError("MODULE_DATABASE_QUERY_FAILED", { sqlState: "23503" });
      }
      return [];
    });

    const failure = await createSignageStore(fake.executor)
      .insertSelection(selection())
      .then(
        () => undefined,
        (error: unknown) => error,
      );

    expect(fake.events).toEqual([
      "BEGIN",
      "insert signage_selections",
      "insert signage_selection_variants",
      "ROLLBACK",
    ]);
    expect(failure).not.toBeInstanceOf(SignageStoreConflict);
    expect((failure as Error).cause).toSatisfy(isModuleDatabaseError);
  });
});

describe("a duplicate native image (SQLSTATE 23505)", () => {
  function failingAssetInsert(error: Error) {
    return createFakeAbiExecutor((statement) => {
      if (statement.summary === "insert signage_image_assets") {
        throw error;
      }
      return [];
    });
  }

  it("reaches Signage wrapped by Drizzle, with the Ekklesia error as its cause", async () => {
    // Why the old `error.code === "23505"` check never matched: Drizzle wraps whatever the driver,
    // here the ABI executor, throws.
    const fake = failingAssetInsert(
      new ModuleDatabaseError("MODULE_DATABASE_QUERY_FAILED", { sqlState: "23505" }),
    );
    const failure = await moduleDrizzle(fake.executor)
      .insert(imageAssets)
      .values({ ...image(), renditions: undefined } as never)
      .then(
        () => undefined,
        (error: unknown) => error as Error & { code?: unknown },
      );

    expect(failure?.name).not.toBe("ModuleDatabaseError");
    expect(failure?.code).toBeUndefined();
    expect(failure?.cause).toMatchObject({
      code: "MODULE_DATABASE_QUERY_FAILED",
      name: "ModuleDatabaseError",
      sqlState: "23505",
    });
  });

  it("is recognized through that wrapping and reported as Signage's content-hash conflict", async () => {
    const fake = failingAssetInsert(
      new ModuleDatabaseError("MODULE_DATABASE_QUERY_FAILED", { sqlState: "23505" }),
    );

    const failure = await createSignageStore(fake.executor)
      .insertImage(image())
      .then(
        () => undefined,
        (error: unknown) => error,
      );

    expect(failure).toBeInstanceOf(SignageStoreConflict);
    expect((failure as SignageStoreConflict).guarantee).toBe("image.contentSha256");
    expect(fake.events).toEqual(["BEGIN", "insert signage_image_assets", "ROLLBACK"]);
  });

  it.each([
    [
      "a foreign-key violation",
      new ModuleDatabaseError("MODULE_DATABASE_QUERY_FAILED", { sqlState: "23503" }),
    ],
    ["a failure with no SQLSTATE", new ModuleDatabaseError("MODULE_DATABASE_QUERY_FAILED")],
    ["a refused parameter", new ModuleDatabaseError("MODULE_DATABASE_PARAMETER_INVALID")],
    // A driver-shaped error is never what the ABI throws, and Signage no longer reads one.
    ["a raw pg-shaped error", Object.assign(new Error("duplicate key"), { code: "23505" })],
  ])("does not mistake %s for a duplicate", async (_name, error) => {
    const fake = failingAssetInsert(error);

    const failure = await createSignageStore(fake.executor)
      .insertImage(image())
      .then(
        () => undefined,
        (caught: unknown) => caught,
      );

    expect(failure).toBeInstanceOf(Error);
    expect(failure).not.toBeInstanceOf(SignageStoreConflict);
    expect((failure as Error).cause).toBe(error);
    expect(fake.events.at(-1)).toBe("ROLLBACK");
  });
});