src/lib/db/review-identity.integration.test.ts

Linked repository file from this build.

import { expect, test } from "bun:test";
import { eq, inArray } from "drizzle-orm";
import { catalogQuerySchema } from "@/features/catalog/query";
import { reviewIdentity, reviewKey } from "@/features/reviews/review-key";
import type { Actor, ActorRef } from "@/features/reviews/types";
import { createSeed } from "@/programs/snap-negative/seed";
import { flagReview, queryCatalog } from "./catalog-store";
import { getDb } from "./index";
import { queryEvents, queryInbox, touchReview } from "./inbox-store";
import { queryReviewVersions, reviewStore } from "./review-store";
import { inboxEvents, reviewIndex, reviewSnapshots, reviewTouches } from "./schema";
import { querySearchPage, searchSchema } from "./search-store";

const integration = process.env.QC_DB_INTEGRATION === "1" ? test : test.skip;
integration("same review number stays separate across fiscal years", async () => {
  const id = `ffy-identity-${crypto.randomUUID()}`;
  const actor: Actor = { name: "Examiner", staffId: `${id}-e`, program: id, role: "examiner" };
  const writer: ActorRef = { name: "Importer", role: "admin" };
  const reviews = ["2025-09", "2026-09"].map((sampleMonth) => ({
    ...createSeed(),
    id: crypto.randomUUID(),
    reviewNumber: id,
    sampleMonth,
    program: id,
    examiner: actor.staffId!,
    supervisor: "",
    clientName: sampleMonth,
  }));
  const keys = reviews.map(reviewKey);
  const db = getDb();
  try {
    for (const review of reviews) {
      await reviewStore.append({
        review,
        actor: writer,
        version: 0,
        requestId: `${reviewKey(review)}-0`,
        savedAt: "2026-09-01T12:00:00Z",
      });
    }
    expect((await reviewStore.load(keys[0]!))?.review.sampleMonth).toBe("2025-09");
    expect((await reviewStore.load(keys[1]!))?.review.sampleMonth).toBe("2026-09");
    expect(await reviewStore.load(id)).toBeUndefined();
    await touchReview(actor, id);
    await expect(flagReview(actor, id, true)).rejects.toThrow();
    expect((await queryReviewVersions(actor)).map((row) => row.reviewKey).sort()).toEqual(
      [...keys].sort(),
    );
    expect((await reviewStore.list()).filter((row) => row.review.program === id)).toHaveLength(2);

    // Only the selected year's read state and flag change.
    await touchReview(actor, keys[0]!);
    await flagReview(actor, keys[0]!, true);
    const catalog = await queryCatalog(actor, catalogQuerySchema.parse({}));
    expect(catalog.total).toBe(2);
    expect(catalog.reviews.find((row) => row.reviewKey === keys[0])).toMatchObject({
      unread: false,
      flagged: true,
    });
    expect(catalog.reviews.find((row) => row.reviewKey === keys[1])).toMatchObject({
      unread: true,
      flagged: false,
    });
    expect((await queryInbox(actor)).lastTouched.map((row) => row.reviewKey)).toEqual([keys[0]!]);
    const search = await querySearchPage(actor, searchSchema.parse({ q: id, quick: "false" }));
    expect(search.total).toBe(2);
    expect(search.reviews.map((row) => [row.reviewKey, row.clientName]).sort()).toEqual(
      keys.map((key, i) => [key, reviews[i]!.clientName]).sort(),
    );

    await reviewStore.append({
      review: { ...reviews[0]!, appointment: "2026-09-10T10:00" },
      actor: writer,
      version: 1,
      requestId: `${keys[0]}-1`,
      savedAt: "2026-09-02T12:00:00Z",
    });
    expect((await reviewStore.load(keys[0]!))?.version).toBe(1);
    expect((await reviewStore.load(keys[1]!))?.version).toBe(0);
    const news = await queryEvents(actor);
    expect(news.unreadCount).toBe(2);
    expect(news.events.find((event) => event.kind === "appointment_changed")?.reviewKey).toBe(
      keys[0],
    );
    expect(news.events.find((event) => event.kind === "distributed")?.reviewKey).toBe(keys[1]);

    await db.delete(reviewIndex).where(eq(reviewIndex.reviewKey, keys[0]!));
    expect(await reviewStore.load(id)).toBeUndefined();
    expect((await reviewStore.findBusiness(reviewIdentity(reviews[1]!)))?.review.id).toBe(keys[1]);
  } finally {
    for (const table of [inboxEvents, reviewTouches, reviewIndex, reviewSnapshots])
      await db.delete(table).where(inArray(table.reviewKey, keys));
  }
});