Repository Public
1import { sql, j } from "../lib/db";2import { embed } from "../lib/embedding";3import { slugify } from "../lib/hash";4import { getAsset } from "../lib/storage";5import { SERIES, TAGS } from "../lib/taxonomy";6import { CreativeBrief } from "../lib/contracts";7import { currentScript, getProject, latestFilmVersion } from "./stages";89export async function recordApproval(a: { projectId: string; userId: string | null; decision: "approve" | "reject" | "override_qa" | "approve_regeneration" | "auto"; note: string }) {10 const fv = await latestFilmVersion(a.projectId);11 await sql`INSERT INTO approvals (project_id, film_version_id, user_id, decision, note) VALUES (${a.projectId}, ${fv?.id ?? null}, ${a.userId}, ${a.decision}, ${a.note})`;12}1314/** Human gate. A failed QA can only be approved through an explicit, recorded override with a reason. */15export async function approveProject(projectId: string, userId: string, o: { override?: boolean; note?: string } = {}) {16 const p = await getProject(projectId);17 if (p.status === "qa_passed") {18 await recordApproval({ projectId, userId, decision: "approve", note: o.note ?? "" });19 } else if (p.status === "qa_failed") {20 if (!o.override) throw new Error("QA failed: publication needs an explicit override");21 if (!o.note || o.note.trim().length < 10) throw new Error("An override needs a written reason (10 characters or more)");22 await recordApproval({ projectId, userId, decision: "override_qa", note: o.note.trim() });23 } else throw new Error(`A project in status "${p.status}" cannot be approved: QA must run first`);24 await sql`UPDATE projects SET status = 'approved', updated_at = now() WHERE id = ${projectId}`;25}2627export async function rejectProject(projectId: string, userId: string, note: string) {28 await recordApproval({ projectId, userId, decision: "reject", note });29 await sql`UPDATE projects SET status = 'qa_failed', updated_at = now() WHERE id = ${projectId}`;30}3132export async function publishProject(projectId: string, userId: string | null, o: { scheduleFor?: Date | null; featured?: boolean } = {}) {33 const p = await getProject(projectId);34 const [approval] = await sql<{ decision: string; at: Date; film_version_id: string | null }[]>`35 SELECT decision, at, film_version_id FROM approvals WHERE project_id = ${projectId} ORDER BY at DESC LIMIT 1`;36 const fv = await latestFilmVersion(projectId);37 const sv = await currentScript(projectId);38 if (!fv || !sv) throw new Error("Nothing to publish: the film has not been assembled");39 if (!approval || !["approve", "override_qa", "auto"].includes(approval.decision) || approval.film_version_id !== fv.id) {40 throw new Error("This film version has not been approved");41 }42 if (!["approved", "published", "qa_passed"].includes(p.status)) throw new Error(`A project in status "${p.status}" cannot be published`);43 const [review] = await sql<{ score: number | null }[]>`SELECT score FROM quality_reviews WHERE film_version_id = ${fv.id} ORDER BY created_at DESC LIMIT 1`;44 const master = (await getAsset(fv.master_asset_id))!, low = await getAsset(fv.low_asset_id), poster = (await getAsset(fv.poster_asset_id))!;45 const thumb = await getAsset(fv.manifest._assets.thumb), capEn = await getAsset(fv.manifest._assets.captions.en), capFr = await getAsset(fv.manifest._assets.captions.fr);46 const s = sv.script;47 const brief = CreativeBrief.parse(p.brief);48 const series = SERIES.find((x) => x.slug === brief.seriesSlug);49 const kind = series?.kind ?? "fiction";50 const sources = (sv.research?.claims ?? []).filter((c) => c.verdict === "supported").map((c) => ({ claim: c.claim, sources: c.sources }));51 const { _assets, _timeline, ...manifest } = fv.manifest;52 void _assets; void _timeline;53 const provenance = {54 ...manifest.provenance,55 humanApproval: approval.decision === "auto" ? `automatic (policy) on ${approval.at.toISOString().slice(0, 10)}` : `approved by a human editor on ${approval.at.toISOString().slice(0, 10)}${approval.decision === "override_qa" ? " (QA override, reason recorded)" : ""}`,56 manifest,57 };58 const slugs = [...new Set(Object.values(s.tags).flat().map((x) => slugify(x)))].filter((x) => TAGS.some((t) => t.slug === x));59 const text = [s.titleEn, s.titleFr, s.synopsisEn, s.synopsisFr, s.takeawayEn, s.takeawayFr, series?.titleEn ?? "", slugs.join(" "), s.beats.map((b) => `${b.narration} ${b.narrationAlt}`).join(" ")].join(" ");60 const scheduled = o.scheduleFor && o.scheduleFor.getTime() > Date.now() ? o.scheduleFor : null;6162 const filmId = await sql.begin(async (tx) => {63 const [existing] = await tx<{ id: string; slug: string }[]>`SELECT id, slug FROM films WHERE project_id = ${projectId}`;64 let slug = existing?.slug;65 if (!slug) {66 const base = slugify(s.titleEn);67 const taken = (await tx<{ slug: string }[]>`SELECT slug FROM films WHERE slug LIKE ${base + "%"}`).map((r) => r.slug);68 slug = base;69 for (let n = 2; taken.includes(slug); n++) slug = `${base}-${n}`;70 }71 const [seriesRow] = series ? await tx<{ id: string }[]>`SELECT id FROM series WHERE slug = ${series.slug}` : [];72 const [{ ep }] = seriesRow ? await tx<{ ep: number }[]>`SELECT COALESCE(MAX(episode), 0) + 1 AS ep FROM films WHERE series_id = ${seriesRow.id}` : [{ ep: 1 }];73 const [film] = await tx<{ id: string }[]>`74 INSERT INTO films (slug, project_id, film_version_id, series_id, episode, title_en, title_fr, synopsis_en, synopsis_fr, takeaway_en, takeaway_fr, language, kind,75 duration, width, height, master_sha, master_bytes, low_sha, poster_sha, thumb_sha, captions, provenance, sources, status, featured, published_at, scheduled_for, quality_score, embedding, search)76 VALUES (${slug}, ${projectId}, ${fv.id}, ${seriesRow?.id ?? null}, ${seriesRow ? ep : null}, ${s.titleEn}, ${s.titleFr}, ${s.synopsisEn}, ${s.synopsisFr}, ${s.takeawayEn}, ${s.takeawayFr}, ${p.language}, ${kind},77 ${fv.duration}, ${master.width ?? 720}, ${master.height ?? 1280}, ${master.sha256}, ${master.bytes}, ${low?.sha256 ?? null}, ${poster.sha256}, ${thumb?.sha256 ?? null},78 ${j({ en: capEn?.sha256 ?? null, fr: capFr?.sha256 ?? null })}, ${j(provenance)}, ${j(sources)}, ${scheduled ? "scheduled" : "published"}, ${o.featured ?? false},79 ${scheduled ? null : new Date()}, ${scheduled}, ${review?.score ?? null}, ${embed(text)}, to_tsvector('simple', ${text}))80 ON CONFLICT (project_id) DO UPDATE SET film_version_id = EXCLUDED.film_version_id, title_en = EXCLUDED.title_en, title_fr = EXCLUDED.title_fr,81 synopsis_en = EXCLUDED.synopsis_en, synopsis_fr = EXCLUDED.synopsis_fr, takeaway_en = EXCLUDED.takeaway_en, takeaway_fr = EXCLUDED.takeaway_fr,82 duration = EXCLUDED.duration, master_sha = EXCLUDED.master_sha, master_bytes = EXCLUDED.master_bytes, low_sha = EXCLUDED.low_sha, poster_sha = EXCLUDED.poster_sha,83 thumb_sha = EXCLUDED.thumb_sha, captions = EXCLUDED.captions, provenance = EXCLUDED.provenance, sources = EXCLUDED.sources, status = EXCLUDED.status,84 published_at = COALESCE(films.published_at, EXCLUDED.published_at), scheduled_for = EXCLUDED.scheduled_for, quality_score = EXCLUDED.quality_score,85 embedding = EXCLUDED.embedding, search = EXCLUDED.search86 RETURNING id`;87 await tx`DELETE FROM film_tags WHERE film_id = ${film.id}`;88 if (slugs.length) await tx`INSERT INTO film_tags (film_id, tag_id) SELECT ${film.id}, id FROM tags WHERE slug = ANY(${slugs}) ON CONFLICT DO NOTHING`;89 const ids = [master.id, low?.id, poster.id, thumb?.id, capEn?.id, capFr?.id].filter((x): x is string => !!x);90 await tx`UPDATE assets SET public = true WHERE id = ANY(${ids})`;91 await tx`INSERT INTO publications (film_id, action, user_id) VALUES (${film.id}, ${scheduled ? "schedule" : "publish"}, ${userId})`;92 await tx`INSERT INTO collection_films (collection_id, film_id, sort) SELECT c.id, ${film.id}, (SELECT COUNT(*) FROM collection_films x WHERE x.collection_id = c.id) FROM collections c WHERE c.slug = 'first-light' AND (SELECT COUNT(*) FROM collection_films x WHERE x.collection_id = c.id) < 8 ON CONFLICT DO NOTHING`;93 await tx`UPDATE projects SET status = 'published', updated_at = now() WHERE id = ${projectId}`;94 return film.id;95 });96 return filmId;97}9899/** Takedown: the film leaves every public surface and its media stops being served. */100export async function setFilmStatus(filmId: string, status: "unpublished" | "hold" | "published", userId: string | null, note = "") {101 await sql.begin(async (tx) => {102 const [f] = await tx<{ master_sha: string; low_sha: string | null; poster_sha: string; thumb_sha: string | null; captions: Record<string, string | null> }[]>`103 UPDATE films SET status = ${status}, published_at = CASE WHEN ${status} = 'published' THEN COALESCE(published_at, now()) ELSE published_at END WHERE id = ${filmId}104 RETURNING master_sha, low_sha, poster_sha, thumb_sha, captions`;105 if (!f) throw new Error("Film not found");106 const shas = [f.master_sha, f.low_sha, f.poster_sha, f.thumb_sha, ...Object.values(f.captions)].filter((x): x is string => !!x);107 await tx`UPDATE assets SET public = ${status === "published"} WHERE sha256 = ANY(${shas})`;108 await tx`INSERT INTO publications (film_id, action, user_id, note) VALUES (${filmId}, ${status === "published" ? "publish" : status === "hold" ? "hold" : "unpublish"}, ${userId}, ${note})`;109 });110}111112/** Scheduled releases whose time has come. */113export async function releaseScheduled() {114 const due = await sql<{ id: string }[]>`UPDATE films SET status = 'published', published_at = now() WHERE status = 'scheduled' AND scheduled_for <= now() RETURNING id`;115 return due.length;116}117