emdash-cms--emdash/packages/core/tests/integration/database/media-usage-content-repair.test.ts

import { sql } from "kysely";
import { ulid } from "ulidx";
import { afterEach, beforeEach, expect, it, vi } from "vitest";

import { MediaUsageRepository } from "../../../src/database/repositories/media-usage.js";
import { validateIdentifier } from "../../../src/database/validate.js";
import {
	CONTENT_MEDIA_USAGE_ADAPTER_ID,
	CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
} from "../../../src/media/usage/content-refresh.js";
import {
	repairContentMediaUsageAll,
	repairContentMediaUsageCollection,
	scanContentMediaUsageCollection,
} from "../../../src/media/usage/content-repair.js";
import {
	buildContentMediaUsageSourceKey,
	type MediaUsageContentSourceVariant,
} from "../../../src/media/usage/source-key.js";
import { SchemaRegistry } from "../../../src/schema/registry.js";
import {
	describeEachDialect,
	setupForDialect,
	teardownForDialect,
	type DialectTestContext,
} from "../../utils/test-db.js";

describeEachDialect("content media usage repair", (dialect) => {
	let ctx: DialectTestContext;
	let registry: SchemaRegistry;
	let usageRepo: MediaUsageRepository;

	beforeEach(async () => {
		ctx = await setupForDialect(dialect);
		registry = new SchemaRegistry(ctx.db);
		usageRepo = new MediaUsageRepository(ctx.db);

		await registry.createCollection({ slug: "posts", label: "Posts" });
		await registry.createField("posts", { slug: "title", label: "Title", type: "string" });
		await registry.createField("posts", { slug: "hero", label: "Hero", type: "image" });
	});

	afterEach(async () => {
		await teardownForDialect(ctx);
	});

	it("scans collection content IDs in deterministic order", async () => {
		const later = await insertPost(ctx, {
			id: "post_z",
			slug: "later",
			status: "published",
			data: { title: "Later" },
		});
		const earlier = await insertPost(ctx, {
			id: "post_a",
			slug: "earlier",
			status: "published",
			data: { title: "Earlier" },
		});

		const scan = await scanContentMediaUsageCollection(ctx.db, "posts");

		expect(scan).toEqual({ collectionSlug: "posts", contentIds: [earlier.id, later.id] });
	});

	it("repairs collection sources and finalizes complete coverage", async () => {
		const first = await insertPost(ctx, {
			slug: "first-post",
			status: "published",
			data: {
				title: "First Post",
				hero: { id: "media-first", provider: "local", mimeType: "image/webp" },
			},
		});
		const second = await insertPost(ctx, {
			slug: "second-post",
			status: "published",
			data: {
				title: "Second Post",
				hero: { id: "media-second", provider: "local", mimeType: "image/webp" },
			},
		});

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				scope: {
					adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
					scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
					scopeKey: "posts",
				},
				status: "complete",
				indexedSourceCount: 2,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
				lastErrorCode: null,
				completedAt: expect.any(String),
			}),
		);
		expect(await usageRepo.findSource(sourceKey(first.id, "columns"))).toEqual(
			expect.objectContaining({ contentTitle: "First Post", sourceCompleteness: "complete" }),
		);
		expect(await usageRepo.findSource(sourceKey(second.id, "columns"))).toEqual(
			expect.objectContaining({ contentTitle: "Second Post", sourceCompleteness: "complete" }),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-first")).toHaveLength(1);
		expect(await usageRepo.findCurrentUsageByMediaId("media-second")).toHaveLength(1);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "posts",
			}),
		).toEqual(
			expect.objectContaining({
				status: "complete",
				cursor: null,
				indexedSourceCount: 2,
				failedSourceCount: 0,
				lastErrorCode: null,
			}),
		);
	});

	it("repairs empty collection scopes as complete", async () => {
		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "complete",
				indexedSourceCount: 0,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
				lastErrorCode: null,
			}),
		);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "posts",
			}),
		).toEqual(expect.objectContaining({ status: "complete", indexedSourceCount: 0 }));
	});

	it("repairs stale no-media content rows as complete coverage", async () => {
		const item = await insertPost(ctx, {
			slug: "no-media-post",
			status: "published",
			data: { title: "No Media Post" },
		});
		await usageRepo.upsertIndexStatus({
			adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
			scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
			scopeKey: "posts",
			status: "stale",
			lastErrorCode: "CONTENT_USAGE_STALE",
		});

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "complete",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				lastErrorCode: null,
			}),
		);
		expect(await usageRepo.findSource(sourceKey(item.id, "columns"))).toEqual(
			expect.objectContaining({ sourceCompleteness: "complete", lastErrorCode: null }),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-missing")).toEqual([]);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "posts",
			}),
		).toEqual(
			expect.objectContaining({
				status: "complete",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				lastErrorCode: null,
			}),
		);
	});

	it("does not mark empty collections complete when field discovery fails", async () => {
		await registry.createField("posts", { slug: "sections", label: "Sections", type: "repeater" });
		await ctx.db
			.updateTable("_emdash_fields")
			.set({ validation: "{" })
			.where("slug", "=", "sections")
			.execute();

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "failed",
				lastErrorCode: "INVALID_REPEATER_VALIDATION",
				indexedSourceCount: 0,
			}),
		);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "posts",
			}),
		).toEqual(
			expect.objectContaining({
				status: "failed",
				lastErrorCode: "INVALID_REPEATER_VALIDATION",
			}),
		);
	});

	it("treats repeater fields with non-array subfields as unsupported during repair", async () => {
		await registry.createField("posts", { slug: "sections", label: "Sections", type: "repeater" });
		await ctx.db
			.updateTable("_emdash_fields")
			.set({ validation: JSON.stringify({ subFields: "not-an-array" }) })
			.where("slug", "=", "sections")
			.execute();
		const item = await insertPost(ctx, {
			slug: "unsupported-repeater-post",
			status: "published",
			data: { title: "Unsupported Repeater Post" },
		});
		await sql`
			UPDATE ${sql.ref("ec_posts")}
			SET sections = ${JSON.stringify([{ image: { id: "media-repeater", provider: "local" } }])}
			WHERE id = ${item.id}
		`.execute(ctx.db);

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "complete",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				lastErrorCode: null,
			}),
		);
		expect(await usageRepo.findSource(sourceKey(item.id, "columns"))).toEqual(
			expect.objectContaining({ sourceCompleteness: "complete" }),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-repeater")).toEqual([]);
	});

	it("indexes valid columns when draft source repair fails", async () => {
		const item = await insertPost(ctx, {
			id: "post_failed_source",
			slug: "failed-source-post",
			status: "published",
			data: {
				title: "Failed Source Post",
				hero: { id: "media-unindexed", provider: "local", mimeType: "image/webp" },
			},
		});
		await ctx.db
			.insertInto("revisions")
			.values({
				id: "mismatched_revision",
				collection: "pages",
				entry_id: item.id,
				data: JSON.stringify({ hero: { id: "media-draft", provider: "local" } }),
			})
			.execute();
		await sql`
			UPDATE ${sql.ref("ec_posts")}
			SET draft_revision_id = ${"mismatched_revision"}
			WHERE id = ${item.id}
		`.execute(ctx.db);

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "partial",
				indexedSourceCount: 1,
				failedSourceCount: 1,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
				lastErrorCode: "DRAFT_REVISION_MISMATCH",
			}),
		);
		expect(await usageRepo.findSource(sourceKey(item.id, "columns"))).toEqual(
			expect.objectContaining({ sourceCompleteness: "complete" }),
		);
		expect(await usageRepo.findSource(sourceKey(item.id, "draft_overlay"))).toEqual(
			expect.objectContaining({
				sourceCompleteness: "failed",
				lastErrorCode: "DRAFT_REVISION_MISMATCH",
			}),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-unindexed")).toHaveLength(1);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "posts",
			}),
		).toEqual(
			expect.objectContaining({
				status: "partial",
				indexedSourceCount: 1,
				failedSourceCount: 1,
				lastErrorCode: "DRAFT_REVISION_MISMATCH",
			}),
		);
	});

	it("indexes valid columns when draft revision is missing", async () => {
		const item = await insertPost(ctx, {
			id: "post_missing_draft_revision",
			slug: "missing-draft-revision-post",
			status: "published",
			data: {
				title: "Missing Draft Revision Post",
				hero: { id: "media-missing-draft", provider: "local", mimeType: "image/webp" },
			},
		});
		await setMissingDraftRevision(ctx, item.id, "missing_revision");

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "partial",
				indexedSourceCount: 1,
				failedSourceCount: 1,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
				lastErrorCode: "DRAFT_REVISION_NOT_FOUND",
			}),
		);
		expect(await usageRepo.findSource(sourceKey(item.id, "columns"))).toEqual(
			expect.objectContaining({ sourceCompleteness: "complete" }),
		);
		expect(await usageRepo.findSource(sourceKey(item.id, "draft_overlay"))).toEqual(
			expect.objectContaining({
				sourceCompleteness: "failed",
				lastErrorCode: "DRAFT_REVISION_NOT_FOUND",
			}),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-missing-draft")).toHaveLength(1);
	});

	it("keeps draft failures without trusted source progress failed", async () => {
		const item = await insertPost(ctx, {
			id: "post_conflict",
			slug: "failed-conflicted-source-post",
			status: "published",
			data: {
				title: "Failed Conflicted Source Post",
				hero: { id: "media-repair", provider: "local", mimeType: "image/webp" },
			},
		});
		await ctx.db
			.insertInto("revisions")
			.values({
				id: "mismatched_revision",
				collection: "pages",
				entry_id: item.id,
				data: JSON.stringify({ hero: { id: "media-draft", provider: "local" } }),
			})
			.execute();
		await sql`
			UPDATE ${sql.ref("ec_posts")}
			SET draft_revision_id = ${"mismatched_revision"}
			WHERE id = ${item.id}
		`.execute(ctx.db);
		await installFresherColumnsSourceDuringRepairTrigger(ctx);

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "failed",
				indexedSourceCount: 0,
				failedSourceCount: 1,
				skippedSourceCount: 1,
				deletedSourceCount: 0,
				lastErrorCode: "DRAFT_REVISION_MISMATCH",
			}),
		);
		expect(await usageRepo.findSource(sourceKey(item.id, "columns"))).toEqual(
			expect.objectContaining({
				currentGeneration: "trigger_columns_generation",
				contentTitle: "Runtime Fresh Columns",
				sourceFingerprint: "runtime-fresher-columns",
			}),
		);
		expect(await usageRepo.findSource(sourceKey(item.id, "draft_overlay"))).toEqual(
			expect.objectContaining({
				sourceCompleteness: "failed",
				lastErrorCode: "DRAFT_REVISION_MISMATCH",
			}),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-fresher-columns")).toHaveLength(1);
		expect(await usageRepo.findCurrentUsageByMediaId("media-repair")).toHaveLength(0);
	});

	it("scans deleted content rows during repair", async () => {
		const deletedAt = "2026-01-01T00:00:00.000Z";
		const item = await insertPost(ctx, {
			slug: "trash-post",
			status: "published",
			deletedAt,
			data: {
				title: "Trash Post",
				hero: { id: "media-trash", provider: "local", mimeType: "image/webp" },
			},
		});

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result.status).toBe("complete");
		expect(result.indexedSourceCount).toBe(1);
		expect(await usageRepo.findSource(sourceKey(item.id, "columns"))).toEqual(
			expect.objectContaining({ contentDeletedAt: deletedAt }),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-trash")).toHaveLength(1);
	});

	it("does not finalize complete when content IDs change during repair", async () => {
		await insertPost(ctx, {
			id: "post_existing",
			slug: "existing-post",
			status: "published",
			data: {
				title: "Existing Post",
				hero: { id: "media-first", provider: "local", mimeType: "image/webp" },
			},
		});
		await installConcurrentPostInsertTrigger(ctx);

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "partial",
				indexedSourceCount: 1,
				skippedSourceCount: 1,
				lastErrorCode: "CONTENT_USAGE_REPAIR_CONFLICT",
			}),
		);
		expect(await usageRepo.findSource(sourceKey("post_concurrent", "columns"))).toBeNull();
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "posts",
			}),
		).toEqual(
			expect.objectContaining({
				status: "partial",
				lastErrorCode: "CONTENT_USAGE_REPAIR_CONFLICT",
			}),
		);
	});

	it("treats content deleted during repair as a skipped conflict", async () => {
		await insertPost(ctx, {
			id: "post_a",
			slug: "first-post",
			status: "published",
			data: {
				title: "First Post",
				hero: { id: "media-first", provider: "local", mimeType: "image/webp" },
			},
		});
		await insertPost(ctx, {
			id: "post_deleted",
			slug: "deleted-during-repair",
			status: "published",
			data: {
				title: "Deleted During Repair",
				hero: { id: "media-deleted", provider: "local", mimeType: "image/webp" },
			},
		});
		await installConcurrentPostDeleteTrigger(ctx);

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "partial",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				skippedSourceCount: 1,
				lastErrorCode: "CONTENT_USAGE_REPAIR_CONFLICT",
			}),
		);
		expect(await usageRepo.findSource(sourceKey("post_a", "columns"))).not.toBeNull();
		expect(await usageRepo.findSource(sourceKey("post_deleted", "columns"))).toBeNull();
		expect(await usageRepo.findCurrentUsageByMediaId("media-first")).toHaveLength(1);
		expect(await usageRepo.findCurrentUsageByMediaId("media-deleted")).toEqual([]);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "posts",
			}),
		).toEqual(
			expect.objectContaining({
				status: "partial",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				lastErrorCode: "CONTENT_USAGE_REPAIR_CONFLICT",
			}),
		);
	});

	it("reports skipped-only repair source conflicts as partial", async () => {
		await insertPost(ctx, {
			id: "post_conflict",
			slug: "conflicted-post",
			status: "published",
			data: {
				title: "Conflicted Post",
				hero: { id: "media-repair", provider: "local", mimeType: "image/webp" },
			},
		});
		await installFresherColumnsSourceDuringRepairTrigger(ctx);

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "partial",
				indexedSourceCount: 0,
				failedSourceCount: 0,
				skippedSourceCount: 1,
				deletedSourceCount: 0,
				lastErrorCode: "CONTENT_USAGE_REPAIR_CONFLICT",
			}),
		);
		expect(await usageRepo.findSource(sourceKey("post_conflict", "columns"))).toEqual(
			expect.objectContaining({
				currentGeneration: "trigger_columns_generation",
				contentTitle: "Runtime Fresh Columns",
				sourceFingerprint: "runtime-fresher-columns",
			}),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-fresher-columns")).toHaveLength(1);
		expect(await usageRepo.findCurrentUsageByMediaId("media-repair")).toHaveLength(0);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "posts",
			}),
		).toEqual(
			expect.objectContaining({
				status: "partial",
				indexedSourceCount: 0,
				failedSourceCount: 0,
				lastErrorCode: "CONTENT_USAGE_REPAIR_CONFLICT",
			}),
		);
	});

	it("does not delete stale observed draft sources that become fresher during repair", async () => {
		const item = await insertPost(ctx, {
			id: "post_draft_conflict",
			slug: "draft-conflict-post",
			status: "published",
			data: {
				title: "Draft Conflict Post",
				hero: { id: "media-columns", provider: "local", mimeType: "image/webp" },
			},
		});
		await usageRepo.replaceSource(contentSource(item.id, "draft_overlay"), [
			occurrence("hero", "media-stale-draft"),
		]);
		await installFresherDraftSourceDuringRepairTrigger(ctx);

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "partial",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				skippedSourceCount: 1,
				deletedSourceCount: 0,
				lastErrorCode: "CONTENT_USAGE_REPAIR_CONFLICT",
			}),
		);
		expect(await usageRepo.findSource(sourceKey(item.id, "draft_overlay"))).toEqual(
			expect.objectContaining({
				currentGeneration: "trigger_draft_generation",
				contentTitle: "Runtime Fresh Draft",
				sourceFingerprint: "runtime-fresher-draft",
			}),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-columns")).toHaveLength(1);
		expect(await usageRepo.findCurrentUsageByMediaId("media-fresher-draft")).toHaveLength(1);
		expect(await usageRepo.findCurrentUsageByMediaId("media-stale-draft")).toHaveLength(0);
	});

	it("returns the winning stale error when final status CAS loses", async () => {
		await insertPost(ctx, {
			slug: "stale-status-post",
			status: "published",
			data: {
				title: "Stale Status Post",
				hero: { id: "media-stale-status", provider: "local", mimeType: "image/webp" },
			},
		});
		await installStaleStatusTrigger(ctx);

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "stale",
				lastErrorCode: "CONTENT_USAGE_STALE",
				completedAt: null,
			}),
		);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "posts",
			}),
		).toEqual(expect.objectContaining({ status: "stale", lastErrorCode: "CONTENT_USAGE_STALE" }));
	});

	it("deletes observed draft and orphan sources that repair proves absent", async () => {
		const item = await insertPost(ctx, {
			slug: "columns-only-post",
			status: "published",
			data: {
				title: "Columns Only",
				hero: { id: "media-live", provider: "local", mimeType: "image/webp" },
			},
		});
		await usageRepo.replaceSource(contentSource(item.id, "draft_overlay"), [
			occurrence("hero", "media-stale-draft"),
		]);
		await usageRepo.replaceSource(contentSource("missing-post", "columns"), [
			occurrence("hero", "media-orphan"),
		]);

		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "posts" });

		expect(result).toEqual(
			expect.objectContaining({
				status: "complete",
				indexedSourceCount: 1,
				deletedSourceCount: 2,
			}),
		);
		expect(await usageRepo.findSource(sourceKey(item.id, "columns"))).not.toBeNull();
		expect(await usageRepo.findSource(sourceKey(item.id, "draft_overlay"))).toBeNull();
		expect(await usageRepo.findSource(sourceKey("missing-post", "columns"))).toBeNull();
		expect(await usageRepo.findCurrentUsageByMediaId("media-stale-draft")).toEqual([]);
		expect(await usageRepo.findCurrentUsageByMediaId("media-orphan")).toEqual([]);
	});

	it("returns failed without writing status for missing collections", async () => {
		const result = await repairContentMediaUsageCollection(ctx.db, { collectionSlug: "missing" });

		expect(result).toEqual(
			expect.objectContaining({
				scope: {
					adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
					scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
					scopeKey: "missing",
				},
				status: "failed",
				lastErrorCode: "COLLECTION_NOT_FOUND",
				indexedSourceCount: 0,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
			}),
		);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "missing",
			}),
		).toBeNull();
	});
});

describeEachDialect("all-content media usage repair", (dialect) => {
	let ctx: DialectTestContext;
	let registry: SchemaRegistry;
	let usageRepo: MediaUsageRepository;

	beforeEach(async () => {
		ctx = await setupForDialect(dialect);
		registry = new SchemaRegistry(ctx.db);
		usageRepo = new MediaUsageRepository(ctx.db);
	});

	afterEach(async () => {
		await teardownForDialect(ctx);
	});

	it("repairs all collections in deterministic slug order", async () => {
		await createMediaCollection(registry, "zines", "Zines");
		await createMediaCollection(registry, "articles", "Articles");
		const zine = await insertContentItem(ctx, "zines", {
			slug: "launch-zine",
			status: "published",
			data: {
				title: "Launch Zine",
				hero: { id: "media-zine", provider: "local", mimeType: "image/webp" },
			},
		});
		const article = await insertContentItem(ctx, "articles", {
			slug: "launch-article",
			status: "published",
			data: {
				title: "Launch Article",
				hero: { id: "media-article", provider: "local", mimeType: "image/webp" },
			},
		});

		const result = await repairContentMediaUsageAll(ctx.db);

		expect(result).toEqual(
			expect.objectContaining({
				status: "complete",
				indexedSourceCount: 2,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
			}),
		);
		expect(result.collections.map((collection) => collection.scope.scopeKey)).toEqual([
			"articles",
			"zines",
		]);
		expect(result.collections).toEqual([
			expect.objectContaining({
				scope: expect.objectContaining({
					adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
					scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
					scopeKey: "articles",
				}),
				status: "complete",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
				lastErrorCode: null,
			}),
			expect.objectContaining({
				scope: expect.objectContaining({
					adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
					scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
					scopeKey: "zines",
				}),
				status: "complete",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
				lastErrorCode: null,
			}),
		]);
		expect(
			await usageRepo.findSource(collectionSourceKey("articles", article.id, "columns")),
		).toEqual(
			expect.objectContaining({ contentTitle: "Launch Article", sourceCompleteness: "complete" }),
		);
		expect(await usageRepo.findSource(collectionSourceKey("zines", zine.id, "columns"))).toEqual(
			expect.objectContaining({ contentTitle: "Launch Zine", sourceCompleteness: "complete" }),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-article")).toHaveLength(1);
		expect(await usageRepo.findCurrentUsageByMediaId("media-zine")).toHaveLength(1);
		await expectCollectionStatus(usageRepo, "articles", {
			status: "complete",
			indexedSourceCount: 1,
			failedSourceCount: 0,
			lastErrorCode: null,
		});
		await expectCollectionStatus(usageRepo, "zines", {
			status: "complete",
			indexedSourceCount: 1,
			failedSourceCount: 0,
			lastErrorCode: null,
		});
	});

	it("aggregates complete and failed collection results as partial", async () => {
		await createMediaCollection(registry, "broken", "Broken");
		await createMediaCollection(registry, "zines", "Zines");
		await createInvalidRepeaterField(ctx, registry, "broken");
		const zine = await insertContentItem(ctx, "zines", {
			slug: "launch-zine",
			status: "published",
			data: {
				title: "Launch Zine",
				hero: { id: "media-zine", provider: "local", mimeType: "image/webp" },
			},
		});

		const result = await repairContentMediaUsageAll(ctx.db);

		expect(result).toEqual(
			expect.objectContaining({
				status: "partial",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
			}),
		);
		expect(result.collections.map((collection) => collection.scope.scopeKey)).toEqual([
			"broken",
			"zines",
		]);
		expect(result.collections).toEqual([
			expect.objectContaining({
				scope: expect.objectContaining({
					scopeKey: "broken",
				}),
				status: "failed",
				indexedSourceCount: 0,
				failedSourceCount: 0,
				lastErrorCode: "INVALID_REPEATER_VALIDATION",
			}),
			expect.objectContaining({
				scope: expect.objectContaining({
					scopeKey: "zines",
				}),
				status: "complete",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				lastErrorCode: null,
			}),
		]);
		expect(await usageRepo.findSource(collectionSourceKey("zines", zine.id, "columns"))).toEqual(
			expect.objectContaining({ contentTitle: "Launch Zine", sourceCompleteness: "complete" }),
		);
		expect(await usageRepo.findCurrentUsageByMediaId("media-zine")).toHaveLength(1);
		await expectCollectionStatus(usageRepo, "broken", {
			status: "failed",
			indexedSourceCount: 0,
			failedSourceCount: 0,
			lastErrorCode: "INVALID_REPEATER_VALIDATION",
		});
		await expectCollectionStatus(usageRepo, "zines", {
			status: "complete",
			indexedSourceCount: 1,
			failedSourceCount: 0,
			lastErrorCode: null,
		});
	});

	it("gives stale collection results aggregate precedence", async () => {
		await createMediaCollection(registry, "broken", "Broken");
		await createMediaCollection(registry, "posts", "Posts");
		await createMediaCollection(registry, "zines", "Zines");
		await createInvalidRepeaterField(ctx, registry, "broken");
		await insertContentItem(ctx, "posts", {
			slug: "stale-status-post",
			status: "published",
			data: {
				title: "Stale Status Post",
				hero: { id: "media-stale-status", provider: "local", mimeType: "image/webp" },
			},
		});
		const zine = await insertContentItem(ctx, "zines", {
			slug: "launch-zine",
			status: "published",
			data: {
				title: "Launch Zine",
				hero: { id: "media-zine", provider: "local", mimeType: "image/webp" },
			},
		});
		await setMismatchedDraftRevision(ctx, "zines", zine.id);
		await installStaleStatusTrigger(ctx);

		const result = await repairContentMediaUsageAll(ctx.db);

		expect(result).toEqual(
			expect.objectContaining({
				status: "stale",
				indexedSourceCount: 2,
				failedSourceCount: 1,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
			}),
		);
		expect(result.collections).toEqual([
			expect.objectContaining({
				scope: expect.objectContaining({ scopeKey: "broken" }),
				status: "failed",
				indexedSourceCount: 0,
				lastErrorCode: "INVALID_REPEATER_VALIDATION",
			}),
			expect.objectContaining({
				scope: expect.objectContaining({ scopeKey: "posts" }),
				status: "stale",
				indexedSourceCount: 1,
				lastErrorCode: "CONTENT_USAGE_STALE",
				completedAt: null,
			}),
			expect.objectContaining({
				scope: expect.objectContaining({ scopeKey: "zines" }),
				status: "partial",
				indexedSourceCount: 1,
				failedSourceCount: 1,
				lastErrorCode: "DRAFT_REVISION_MISMATCH",
			}),
		]);
	});

	it("aggregates only failed collection results as failed", async () => {
		await createMediaCollection(registry, "broken", "Broken");
		await createInvalidRepeaterField(ctx, registry, "broken");

		const result = await repairContentMediaUsageAll(ctx.db);

		expect(result).toEqual(
			expect.objectContaining({
				status: "failed",
				indexedSourceCount: 0,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
			}),
		);
		expect(result.collections).toEqual([
			expect.objectContaining({
				scope: expect.objectContaining({ scopeKey: "broken" }),
				status: "failed",
				lastErrorCode: "INVALID_REPEATER_VALIDATION",
			}),
		]);
	});

	it("repairs empty sites as complete without creating status rows", async () => {
		const result = await repairContentMediaUsageAll(ctx.db);

		expect(result).toEqual({
			status: "complete",
			collections: [],
			indexedSourceCount: 0,
			failedSourceCount: 0,
			skippedSourceCount: 0,
			deletedSourceCount: 0,
		});
		expect(
			await ctx.db.selectFrom("_emdash_media_usage_index_status").select("adapter_id").execute(),
		).toHaveLength(0);
	});

	it("fails when final collection reconciliation cannot verify current collections", async () => {
		await createMediaCollection(registry, "articles", "Articles");
		const article = await insertContentItem(ctx, "articles", {
			slug: "launch-article",
			status: "published",
			data: {
				title: "Launch Article",
				hero: { id: "media-article", provider: "local", mimeType: "image/webp" },
			},
		});
		const selectFrom = ctx.db.selectFrom.bind(ctx.db) as typeof ctx.db.selectFrom;
		let collectionSelects = 0;
		const selectFromSpy = vi.spyOn(ctx.db, "selectFrom").mockImplementation(((table: string) => {
			if (table === "_emdash_collections") {
				collectionSelects++;
				if (collectionSelects > 4) {
					throw new Error("collection reconciliation unavailable");
				}
			}
			return selectFrom(table as never);
		}) as typeof ctx.db.selectFrom);
		const consoleError = vi.spyOn(console, "error").mockImplementation(() => {});

		try {
			await expect(repairContentMediaUsageAll(ctx.db)).rejects.toThrow(
				"collection reconciliation unavailable",
			);
			expect(consoleError).toHaveBeenCalledWith(
				"[media-usage] Failed to reconcile all-content repair collections:",
				expect.any(Error),
			);
		} finally {
			selectFromSpy.mockRestore();
			consoleError.mockRestore();
		}

		expect(collectionSelects).toBe(6);
		expect(
			await usageRepo.findSource(collectionSourceKey("articles", article.id, "columns")),
		).toEqual(expect.objectContaining({ contentTitle: "Launch Article" }));
		await expectCollectionStatus(usageRepo, "articles", {
			status: "complete",
			indexedSourceCount: 1,
			failedSourceCount: 0,
			lastErrorCode: null,
		});
	});

	it("excludes listed collections deleted before their repair result", async () => {
		await createMediaCollection(registry, "articles", "Articles");
		await createMediaCollection(registry, "doomed", "Doomed");
		const article = await insertContentItem(ctx, "articles", {
			slug: "launch-article",
			status: "published",
			data: {
				title: "Launch Article",
				hero: { id: "media-article", provider: "local", mimeType: "image/webp" },
			},
		});
		const doomed = await insertContentItem(ctx, "doomed", {
			slug: "doomed-entry",
			status: "published",
			data: {
				title: "Doomed Entry",
				hero: { id: "media-doomed", provider: "local", mimeType: "image/webp" },
			},
		});
		await installDoomedCollectionDeleteTrigger(ctx);

		const result = await repairContentMediaUsageAll(ctx.db);

		expect(result).toEqual(
			expect.objectContaining({
				status: "complete",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
			}),
		);
		expect(result.collections).toEqual([
			expect.objectContaining({
				scope: expect.objectContaining({ scopeKey: "articles" }),
				status: "complete",
				indexedSourceCount: 1,
			}),
		]);
		expect(
			await usageRepo.findSource(collectionSourceKey("articles", article.id, "columns")),
		).toEqual(expect.objectContaining({ contentTitle: "Launch Article" }));
		expect(
			await usageRepo.findSource(collectionSourceKey("doomed", doomed.id, "columns")),
		).toBeNull();
		expect(await usageRepo.findCurrentUsageByMediaId("media-doomed")).toEqual([]);
		expect(
			await ctx.db
				.selectFrom("_emdash_collections")
				.select("id")
				.where("slug", "=", "doomed")
				.executeTakeFirst(),
		).toBeUndefined();
	});

	it("excludes listed collections deleted after indexing from aggregate counts", async () => {
		await createMediaCollection(registry, "articles", "Articles");
		await createMediaCollection(registry, "vanishing", "Vanishing");
		const article = await insertContentItem(ctx, "articles", {
			slug: "launch-article",
			status: "published",
			data: {
				title: "Launch Article",
				hero: { id: "media-article", provider: "local", mimeType: "image/webp" },
			},
		});
		const vanishing = await insertContentItem(ctx, "vanishing", {
			slug: "vanishing-entry",
			status: "published",
			data: {
				title: "Vanishing Entry",
				hero: { id: "media-vanishing", provider: "local", mimeType: "image/webp" },
			},
		});
		await installVanishingCollectionDeleteTrigger(ctx);

		const result = await repairContentMediaUsageAll(ctx.db);

		expect(result).toEqual(
			expect.objectContaining({
				status: "complete",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
			}),
		);
		expect(result.collections).toEqual([
			expect.objectContaining({
				scope: expect.objectContaining({ scopeKey: "articles" }),
				status: "complete",
				indexedSourceCount: 1,
			}),
		]);
		expect(
			await usageRepo.findSource(collectionSourceKey("articles", article.id, "columns")),
		).toEqual(expect.objectContaining({ contentTitle: "Launch Article" }));
		expect(
			await usageRepo.findSource(collectionSourceKey("vanishing", vanishing.id, "columns")),
		).toEqual(expect.objectContaining({ contentTitle: "Vanishing Entry" }));
		expect(await usageRepo.findCurrentUsageByMediaId("media-vanishing")).toHaveLength(1);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "vanishing",
			}),
		).toBeNull();
		expect(
			await ctx.db
				.selectFrom("_emdash_collections")
				.select("id")
				.where("slug", "=", "vanishing")
				.executeTakeFirst(),
		).toBeUndefined();
	});

	it("excludes listed collections recreated with the same slug and a new ID", async () => {
		await createMediaCollection(registry, "articles", "Articles");
		await createMediaCollection(registry, "recreated", "Recreated");
		const originalRecreatedId = await getCollectionId(ctx, "recreated");
		const article = await insertContentItem(ctx, "articles", {
			slug: "launch-article",
			status: "published",
			data: {
				title: "Launch Article",
				hero: { id: "media-article", provider: "local", mimeType: "image/webp" },
			},
		});
		const recreated = await insertContentItem(ctx, "recreated", {
			slug: "recreated-entry",
			status: "published",
			data: {
				title: "Recreated Entry",
				hero: { id: "media-recreated", provider: "local", mimeType: "image/webp" },
			},
		});
		await installRecreatedCollectionRecreateTrigger(ctx);

		const result = await repairContentMediaUsageAll(ctx.db);

		expect(result).toEqual(
			expect.objectContaining({
				status: "complete",
				indexedSourceCount: 1,
				failedSourceCount: 0,
				skippedSourceCount: 0,
				deletedSourceCount: 0,
			}),
		);
		expect(result.collections).toEqual([
			expect.objectContaining({
				scope: expect.objectContaining({ scopeKey: "articles" }),
				status: "complete",
				indexedSourceCount: 1,
			}),
		]);
		expect(await getCollectionId(ctx, "recreated")).not.toBe(originalRecreatedId);
		expect(
			await usageRepo.findSource(collectionSourceKey("articles", article.id, "columns")),
		).toEqual(expect.objectContaining({ contentTitle: "Launch Article" }));
		expect(
			await usageRepo.findSource(collectionSourceKey("recreated", recreated.id, "columns")),
		).toEqual(expect.objectContaining({ contentTitle: "Recreated Entry" }));
		expect(await usageRepo.findCurrentUsageByMediaId("media-recreated")).toHaveLength(1);
		expect(
			await usageRepo.findIndexStatus({
				adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
				scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
				scopeKey: "recreated",
			}),
		).toBeNull();
	});
});

interface TestPostInput {
	id?: string;
	slug: string;
	status: string;
	deletedAt?: string | null;
	data: Record<string, unknown>;
}

interface TestPost {
	id: string;
}

async function createMediaCollection(
	registry: SchemaRegistry,
	slug: string,
	label: string,
): Promise<void> {
	await registry.createCollection({ slug, label });
	await registry.createField(slug, { slug: "title", label: "Title", type: "string" });
	await registry.createField(slug, { slug: "hero", label: "Hero", type: "image" });
}

async function getCollectionId(ctx: DialectTestContext, collectionSlug: string): Promise<string> {
	const row = await ctx.db
		.selectFrom("_emdash_collections")
		.select("id")
		.where("slug", "=", collectionSlug)
		.executeTakeFirst();
	if (!row) throw new Error(`Collection ${collectionSlug} was not found`);
	return row.id;
}

async function createInvalidRepeaterField(
	ctx: DialectTestContext,
	registry: SchemaRegistry,
	collectionSlug: string,
): Promise<void> {
	await registry.createField(collectionSlug, {
		slug: "sections",
		label: "Sections",
		type: "repeater",
	});
	await ctx.db
		.updateTable("_emdash_fields")
		.set({ validation: "{" })
		.where("slug", "=", "sections")
		.where(
			"collection_id",
			"=",
			ctx.db.selectFrom("_emdash_collections").select("id").where("slug", "=", collectionSlug),
		)
		.execute();
}

async function setMismatchedDraftRevision(
	ctx: DialectTestContext,
	collectionSlug: string,
	contentId: string,
): Promise<void> {
	validateIdentifier(collectionSlug, "test collection slug");
	const revisionId = `mismatched_revision_${collectionSlug}`;
	await ctx.db
		.insertInto("revisions")
		.values({
			id: revisionId,
			collection: "mismatched_collection",
			entry_id: contentId,
			data: JSON.stringify({ hero: { id: "media-draft", provider: "local" } }),
		})
		.execute();
	await sql`
		UPDATE ${sql.ref(`ec_${collectionSlug}`)}
		SET draft_revision_id = ${revisionId}
		WHERE id = ${contentId}
	`.execute(ctx.db);
}

async function insertPost(ctx: DialectTestContext, input: TestPostInput): Promise<TestPost> {
	return insertContentItem(ctx, "posts", input);
}

async function insertContentItem(
	ctx: DialectTestContext,
	collectionSlug: string,
	input: TestPostInput,
): Promise<TestPost> {
	validateIdentifier(collectionSlug, "test collection slug");
	const id = input.id ?? ulid();
	const now = new Date().toISOString();
	const tableName = `ec_${collectionSlug}`;
	await sql`
		INSERT INTO ${sql.ref(tableName)} (
			id,
			slug,
			status,
			created_at,
			updated_at,
			deleted_at,
			version,
			locale,
			translation_group,
			title,
			hero
		) VALUES (
			${id},
			${input.slug},
			${input.status},
			${now},
			${now},
			${input.deletedAt ?? null},
			${1},
			${"en"},
			${id},
			${serializeFieldValue(input.data.title)},
			${serializeFieldValue(input.data.hero)}
		)
	`.execute(ctx.db);

	return { id };
}

async function expectCollectionStatus(
	usageRepo: MediaUsageRepository,
	scopeKey: string,
	expected: {
		status: string;
		indexedSourceCount: number;
		failedSourceCount: number;
		lastErrorCode: string | null;
	},
): Promise<void> {
	expect(
		await usageRepo.findIndexStatus({
			adapterId: CONTENT_MEDIA_USAGE_ADAPTER_ID,
			scopeType: CONTENT_MEDIA_USAGE_COLLECTION_SCOPE,
			scopeKey,
		}),
	).toEqual(expect.objectContaining({ ...expected, cursor: null }));
}

function contentSource(
	contentId: string,
	variant: MediaUsageContentSourceVariant,
): Parameters<MediaUsageRepository["replaceSource"]>[0] {
	return {
		sourceKey: sourceKey(contentId, variant),
		sourceType: "content",
		collectionSlug: "posts",
		contentId,
		sourceVariant: variant,
		locale: "en",
		translationGroup: `tg-${contentId}`,
		contentSlug: contentId,
		contentTitle: contentId,
		contentStatus: "published",
		contentScheduledAt: null,
		contentDeletedAt: null,
		revisionId: null,
	};
}

async function setMissingDraftRevision(
	ctx: DialectTestContext,
	contentId: string,
	revisionId: string,
): Promise<void> {
	if (ctx.dialect === "postgres") {
		await sql`
			ALTER TABLE ${sql.ref("ec_posts")}
			DROP CONSTRAINT IF EXISTS ec_posts_draft_revision_id_fkey
		`.execute(ctx.db);
	} else {
		await sql`PRAGMA foreign_keys = OFF`.execute(ctx.db);
	}

	await sql`
		UPDATE ${sql.ref("ec_posts")}
		SET draft_revision_id = ${revisionId}
		WHERE id = ${contentId}
	`.execute(ctx.db);
}

function occurrence(
	fieldSlug: string,
	mediaId: string,
): Parameters<MediaUsageRepository["replaceSource"]>[1][number] {
	return {
		fieldSlug,
		fieldPath: fieldSlug,
		occurrenceIndex: 0,
		referenceType: "image_field",
		mediaId,
		provider: "local",
		providerAssetId: mediaId,
		mediaKind: "image",
		mimeType: null,
	};
}

function sourceKey(contentId: string, sourceVariant: MediaUsageContentSourceVariant): string {
	return collectionSourceKey("posts", contentId, sourceVariant);
}

function collectionSourceKey(
	collectionSlug: string,
	contentId: string,
	sourceVariant: MediaUsageContentSourceVariant,
): string {
	return buildContentMediaUsageSourceKey({
		collectionSlug,
		contentId,
		sourceVariant,
	});
}

async function installConcurrentPostInsertTrigger(ctx: DialectTestContext): Promise<void> {
	if (ctx.dialect === "postgres") {
		await sql`
			CREATE FUNCTION media_usage_insert_concurrent_post()
			RETURNS trigger
			LANGUAGE plpgsql
			AS $$
			BEGIN
				IF NEW.media_id = 'media-first' THEN
					INSERT INTO ec_posts (
						id,
						slug,
						status,
						created_at,
						updated_at,
						version,
						locale,
						translation_group,
						title,
						hero
					) VALUES (
						'post_concurrent',
						'concurrent-post',
						'published',
						'2026-01-01T00:00:00.000Z',
						'2026-01-01T00:00:00.000Z',
						1,
						'en',
						'post_concurrent',
						'Concurrent Post',
						'{"id":"media-concurrent","provider":"local","mimeType":"image/webp"}'
					)
					ON CONFLICT (id) DO NOTHING;
				END IF;
				RETURN NEW;
			END;
			$$
		`.execute(ctx.db);
		await sql`
			CREATE TRIGGER media_usage_insert_concurrent_post
			AFTER INSERT ON _emdash_media_usage
			FOR EACH ROW
			EXECUTE FUNCTION media_usage_insert_concurrent_post()
		`.execute(ctx.db);
		return;
	}

	await sql`
		CREATE TRIGGER media_usage_insert_concurrent_post
		AFTER INSERT ON _emdash_media_usage
		WHEN NEW.media_id = 'media-first'
		BEGIN
			INSERT OR IGNORE INTO ec_posts (
				id,
				slug,
				status,
				created_at,
				updated_at,
				version,
				locale,
				translation_group,
				title,
				hero
			) VALUES (
				'post_concurrent',
				'concurrent-post',
				'published',
				'2026-01-01T00:00:00.000Z',
				'2026-01-01T00:00:00.000Z',
				1,
				'en',
				'post_concurrent',
				'Concurrent Post',
				'{"id":"media-concurrent","provider":"local","mimeType":"image/webp"}'
			);
		END
	`.execute(ctx.db);
}

async function installConcurrentPostDeleteTrigger(ctx: DialectTestContext): Promise<void> {
	if (ctx.dialect === "postgres") {
		await sql`
			CREATE FUNCTION media_usage_delete_concurrent_post()
			RETURNS trigger
			LANGUAGE plpgsql
			AS $$
			BEGIN
				IF NEW.media_id = 'media-first' THEN
					DELETE FROM ec_posts WHERE id = 'post_deleted';
				END IF;
				RETURN NEW;
			END;
			$$
		`.execute(ctx.db);
		await sql`
			CREATE TRIGGER media_usage_delete_concurrent_post
			AFTER INSERT ON _emdash_media_usage
			FOR EACH ROW
			EXECUTE FUNCTION media_usage_delete_concurrent_post()
		`.execute(ctx.db);
		return;
	}

	await sql`
		CREATE TRIGGER media_usage_delete_concurrent_post
		AFTER INSERT ON _emdash_media_usage
		WHEN NEW.media_id = 'media-first'
		BEGIN
			DELETE FROM ec_posts WHERE id = 'post_deleted';
		END
	`.execute(ctx.db);
}

async function installStaleStatusTrigger(ctx: DialectTestContext): Promise<void> {
	if (ctx.dialect === "postgres") {
		await sql`
			CREATE FUNCTION media_usage_mark_status_stale()
			RETURNS trigger
			LANGUAGE plpgsql
			AS $$
			BEGIN
				UPDATE _emdash_media_usage_index_status
				SET status = 'stale',
					last_error_code = 'CONTENT_USAGE_STALE',
					updated_at = '2026-01-01T00:00:01.000Z'
				WHERE adapter_id = 'content-media'
				AND scope_type = 'collection'
				AND scope_key = 'posts';
				RETURN NEW;
			END;
			$$
		`.execute(ctx.db);
		await sql`
			CREATE TRIGGER media_usage_mark_status_stale
			AFTER INSERT ON _emdash_media_usage
			FOR EACH ROW
			EXECUTE FUNCTION media_usage_mark_status_stale()
		`.execute(ctx.db);
		return;
	}

	await sql`
		CREATE TRIGGER media_usage_mark_status_stale
		AFTER INSERT ON _emdash_media_usage
		BEGIN
			UPDATE _emdash_media_usage_index_status
			SET status = 'stale',
				last_error_code = 'CONTENT_USAGE_STALE',
				updated_at = '2026-01-01T00:00:01.000Z'
			WHERE adapter_id = 'content-media'
			AND scope_type = 'collection'
			AND scope_key = 'posts';
		END
	`.execute(ctx.db);
}

async function installDoomedCollectionDeleteTrigger(ctx: DialectTestContext): Promise<void> {
	if (ctx.dialect === "postgres") {
		await sql`
			CREATE FUNCTION media_usage_delete_doomed_collection()
			RETURNS trigger
			LANGUAGE plpgsql
			AS $$
			BEGIN
				IF NEW.media_id = 'media-article' THEN
					DELETE FROM _emdash_collections WHERE slug = 'doomed';
				END IF;
				RETURN NEW;
			END;
			$$
		`.execute(ctx.db);
		await sql`
			CREATE TRIGGER media_usage_delete_doomed_collection
			AFTER INSERT ON _emdash_media_usage
			FOR EACH ROW
			EXECUTE FUNCTION media_usage_delete_doomed_collection()
		`.execute(ctx.db);
		return;
	}

	await sql`
		CREATE TRIGGER media_usage_delete_doomed_collection
		AFTER INSERT ON _emdash_media_usage
		WHEN NEW.media_id = 'media-article'
		BEGIN
			DELETE FROM _emdash_collections WHERE slug = 'doomed';
		END
	`.execute(ctx.db);
}

async function installVanishingCollectionDeleteTrigger(ctx: DialectTestContext): Promise<void> {
	if (ctx.dialect === "postgres") {
		await sql`
			CREATE FUNCTION media_usage_delete_vanishing_collection()
			RETURNS trigger
			LANGUAGE plpgsql
			AS $$
			BEGIN
				IF NEW.media_id = 'media-vanishing' THEN
					DELETE FROM _emdash_collections WHERE slug = 'vanishing';
				END IF;
				RETURN NEW;
			END;
			$$
		`.execute(ctx.db);
		await sql`
			CREATE TRIGGER media_usage_delete_vanishing_collection
			AFTER INSERT ON _emdash_media_usage
			FOR EACH ROW
			EXECUTE FUNCTION media_usage_delete_vanishing_collection()
		`.execute(ctx.db);
		return;
	}

	await sql`
		CREATE TRIGGER media_usage_delete_vanishing_collection
		AFTER INSERT ON _emdash_media_usage
		WHEN NEW.media_id = 'media-vanishing'
		BEGIN
			DELETE FROM _emdash_collections WHERE slug = 'vanishing';
		END
	`.execute(ctx.db);
}

async function installRecreatedCollectionRecreateTrigger(ctx: DialectTestContext): Promise<void> {
	if (ctx.dialect === "postgres") {
		await sql`
			CREATE FUNCTION media_usage_recreate_collection()
			RETURNS trigger
			LANGUAGE plpgsql
			AS $$
			BEGIN
				IF NEW.media_id = 'media-recreated' THEN
					DELETE FROM _emdash_collections WHERE slug = 'recreated';
					INSERT INTO _emdash_collections (
						id,
						slug,
						label,
						supports,
						source,
						has_seo,
						comments_enabled,
						url_pattern
					) VALUES (
						'collection_recreated_new',
						'recreated',
						'Recreated New',
						'["drafts","revisions"]',
						'manual',
						0,
						0,
						NULL
					);
				END IF;
				RETURN NEW;
			END;
			$$
		`.execute(ctx.db);
		await sql`
			CREATE TRIGGER media_usage_recreate_collection
			AFTER INSERT ON _emdash_media_usage
			FOR EACH ROW
			EXECUTE FUNCTION media_usage_recreate_collection()
		`.execute(ctx.db);
		return;
	}

	await sql`
		CREATE TRIGGER media_usage_recreate_collection
		AFTER INSERT ON _emdash_media_usage
		WHEN NEW.media_id = 'media-recreated'
		BEGIN
			DELETE FROM _emdash_collections WHERE slug = 'recreated';
			INSERT INTO _emdash_collections (
				id,
				slug,
				label,
				supports,
				source,
				has_seo,
				comments_enabled,
				url_pattern
			) VALUES (
				'collection_recreated_new',
				'recreated',
				'Recreated New',
				'["drafts","revisions"]',
				'manual',
				0,
				0,
				NULL
			);
		END
	`.execute(ctx.db);
}

async function installFresherColumnsSourceDuringRepairTrigger(
	ctx: DialectTestContext,
): Promise<void> {
	if (ctx.dialect === "postgres") {
		await sql`
			CREATE FUNCTION media_usage_insert_fresher_columns_source()
			RETURNS trigger
			LANGUAGE plpgsql
			AS $$
			BEGIN
				IF NEW.media_id = 'media-repair' THEN
					INSERT INTO _emdash_media_usage_sources (
						source_key,
						source_type,
						collection_slug,
						content_id,
						source_variant,
						locale,
						translation_group,
						content_slug,
						content_title,
						content_status,
						current_generation,
						schema_version,
						source_updated_at,
						source_version,
						source_fingerprint,
						source_completeness,
						last_attempted_at,
						indexed_at,
						created_at,
						updated_at
					) VALUES (
						'content:posts:post_conflict:columns',
						'content',
						'posts',
						'post_conflict',
						'columns',
						'en',
						'post_conflict',
						'runtime-fresh-columns',
						'Runtime Fresh Columns',
						'published',
						'trigger_columns_generation',
						1,
						'2099-01-01T00:00:01.000Z',
						2,
						'runtime-fresher-columns',
						'complete',
						'2099-01-01T00:00:01.000Z',
						'2099-01-01T00:00:01.000Z',
						'2099-01-01T00:00:01.000Z',
						'2099-01-01T00:00:01.000Z'
					)
					ON CONFLICT (source_key) DO NOTHING;

					INSERT INTO _emdash_media_usage (
						id,
						source_key,
						generation,
						field_slug,
						field_path,
						occurrence_index,
						reference_type,
						media_id,
						provider,
						provider_asset_id,
						media_kind,
						mime_type,
						created_at
					) VALUES (
						'usage_trigger_columns',
						'content:posts:post_conflict:columns',
						'trigger_columns_generation',
						'hero',
						'hero',
						0,
						'image_field',
						'media-fresher-columns',
						'local',
						'media-fresher-columns',
						'image',
						'image/webp',
						'2099-01-01T00:00:01.000Z'
					)
					ON CONFLICT (id) DO NOTHING;
				END IF;
				RETURN NEW;
			END;
			$$
		`.execute(ctx.db);
		await sql`
			CREATE TRIGGER media_usage_insert_fresher_columns_source
			AFTER INSERT ON _emdash_media_usage
			FOR EACH ROW
			EXECUTE FUNCTION media_usage_insert_fresher_columns_source()
		`.execute(ctx.db);
		return;
	}

	await sql`
		CREATE TRIGGER media_usage_insert_fresher_columns_source
		AFTER INSERT ON _emdash_media_usage
		WHEN NEW.media_id = 'media-repair'
		BEGIN
			INSERT OR IGNORE INTO _emdash_media_usage_sources (
				source_key,
				source_type,
				collection_slug,
				content_id,
				source_variant,
				locale,
				translation_group,
				content_slug,
				content_title,
				content_status,
				current_generation,
				schema_version,
				source_updated_at,
				source_version,
				source_fingerprint,
				source_completeness,
				last_attempted_at,
				indexed_at,
				created_at,
				updated_at
			) VALUES (
				'content:posts:post_conflict:columns',
				'content',
				'posts',
				'post_conflict',
				'columns',
				'en',
				'post_conflict',
				'runtime-fresh-columns',
				'Runtime Fresh Columns',
				'published',
				'trigger_columns_generation',
				1,
				'2099-01-01T00:00:01.000Z',
				2,
				'runtime-fresher-columns',
				'complete',
				'2099-01-01T00:00:01.000Z',
				'2099-01-01T00:00:01.000Z',
				'2099-01-01T00:00:01.000Z',
				'2099-01-01T00:00:01.000Z'
			);

			INSERT OR IGNORE INTO _emdash_media_usage (
				id,
				source_key,
				generation,
				field_slug,
				field_path,
				occurrence_index,
				reference_type,
				media_id,
				provider,
				provider_asset_id,
				media_kind,
				mime_type,
				created_at
			) VALUES (
				'usage_trigger_columns',
				'content:posts:post_conflict:columns',
				'trigger_columns_generation',
				'hero',
				'hero',
				0,
				'image_field',
				'media-fresher-columns',
				'local',
				'media-fresher-columns',
				'image',
				'image/webp',
				'2099-01-01T00:00:01.000Z'
			);
		END
	`.execute(ctx.db);
}

async function installFresherDraftSourceDuringRepairTrigger(
	ctx: DialectTestContext,
): Promise<void> {
	if (ctx.dialect === "postgres") {
		await sql`
			CREATE FUNCTION media_usage_update_fresher_draft_source()
			RETURNS trigger
			LANGUAGE plpgsql
			AS $$
			BEGIN
				IF NEW.media_id = 'media-columns' THEN
					INSERT INTO _emdash_media_usage (
						id,
						source_key,
						generation,
						field_slug,
						field_path,
						occurrence_index,
						reference_type,
						media_id,
						provider,
						provider_asset_id,
						media_kind,
						mime_type,
						created_at
					) VALUES (
						'usage_trigger_draft',
						'content:posts:post_draft_conflict:draft_overlay',
						'trigger_draft_generation',
						'hero',
						'hero',
						0,
						'image_field',
						'media-fresher-draft',
						'local',
						'media-fresher-draft',
						'image',
						'image/webp',
						'2099-01-01T00:00:01.000Z'
					)
					ON CONFLICT (id) DO NOTHING;

					UPDATE _emdash_media_usage_sources
					SET current_generation = 'trigger_draft_generation',
						source_updated_at = '2099-01-01T00:00:01.000Z',
						source_version = 2,
						source_fingerprint = 'runtime-fresher-draft',
						content_title = 'Runtime Fresh Draft',
						last_attempted_at = '2099-01-01T00:00:01.000Z',
						indexed_at = '2099-01-01T00:00:01.000Z',
						updated_at = '2099-01-01T00:00:01.000Z'
					WHERE source_key = 'content:posts:post_draft_conflict:draft_overlay';
				END IF;
				RETURN NEW;
			END;
			$$
		`.execute(ctx.db);
		await sql`
			CREATE TRIGGER media_usage_update_fresher_draft_source
			AFTER INSERT ON _emdash_media_usage
			FOR EACH ROW
			EXECUTE FUNCTION media_usage_update_fresher_draft_source()
		`.execute(ctx.db);
		return;
	}

	await sql`
		CREATE TRIGGER media_usage_update_fresher_draft_source
		AFTER INSERT ON _emdash_media_usage
		WHEN NEW.media_id = 'media-columns'
		BEGIN
			INSERT OR IGNORE INTO _emdash_media_usage (
				id,
				source_key,
				generation,
				field_slug,
				field_path,
				occurrence_index,
				reference_type,
				media_id,
				provider,
				provider_asset_id,
				media_kind,
				mime_type,
				created_at
			) VALUES (
				'usage_trigger_draft',
				'content:posts:post_draft_conflict:draft_overlay',
				'trigger_draft_generation',
				'hero',
				'hero',
				0,
				'image_field',
				'media-fresher-draft',
				'local',
				'media-fresher-draft',
				'image',
				'image/webp',
				'2099-01-01T00:00:01.000Z'
			);

			UPDATE _emdash_media_usage_sources
			SET current_generation = 'trigger_draft_generation',
				source_updated_at = '2099-01-01T00:00:01.000Z',
				source_version = 2,
				source_fingerprint = 'runtime-fresher-draft',
				content_title = 'Runtime Fresh Draft',
				last_attempted_at = '2099-01-01T00:00:01.000Z',
				indexed_at = '2099-01-01T00:00:01.000Z',
				updated_at = '2099-01-01T00:00:01.000Z'
			WHERE source_key = 'content:posts:post_draft_conflict:draft_overlay';
		END
	`.execute(ctx.db);
}

function serializeFieldValue(value: unknown): unknown {
	if (value === null || value === undefined) return null;
	if (typeof value === "object") return JSON.stringify(value);
	return value;
}