Repository Public
1import { sql } from "./db";2import { cosine, embed, sharedTerms } from "./embedding";34/**5 * The only read path used by public pages and public API routes.6 * It selects public film metadata and delivery assets — never prompts, drafts or costs.7 */8export type Locale = "en" | "fr";9export const media = (sha: string | null | undefined, ext: string) => (sha ? `/media/${sha}.${ext}` : null);1011interface FilmDb {12 id: string; slug: string; title_en: string; title_fr: string; synopsis_en: string; synopsis_fr: string; takeaway_en: string; takeaway_fr: string;13 language: string; kind: "fiction" | "factual" | "experimental"; duration: number; width: number; height: number; master_sha: string; master_bytes: number; low_sha: string | null;14 poster_sha: string; thumb_sha: string | null; captions: { en?: string | null; fr?: string | null }; provenance: Record<string, unknown>;15 sources: { claim: string; sources: { title: string; url: string }[] }[]; featured: boolean; published_at: Date; quality_score: number | null; views: number; likes: number;16 episode: number | null; embedding: number[]; series_slug: string | null; series_title_en: string | null; series_title_fr: string | null; series_accent: string | null;17 tags: { slug: string; kind: string; en: string; fr: string }[] | null;18}19export interface Film {20 id: string; slug: string; title: string; titleAlt: string; synopsis: string; takeaway: string; language: string; kind: FilmDb["kind"]; duration: number;21 width: number; height: number; video: string; videoLow: string | null; poster: string; thumb: string; captions: { en: string | null; fr: string | null };22 series: { slug: string; title: string; accent: string } | null; episode: number | null; tags: { slug: string; kind: string; label: string }[];23 publishedAt: string; featured: boolean; qualityScore: number | null; views: number; likes: number; bytes: number;24}25export interface FilmDetail extends Film { provenance: Record<string, unknown>; sources: FilmDb["sources"] }2627const SELECT = sql`28 SELECT f.*, s.slug AS series_slug, s.title_en AS series_title_en, s.title_fr AS series_title_fr, s.accent AS series_accent,29 (SELECT json_agg(json_build_object('slug', t.slug, 'kind', t.kind, 'en', t.label_en, 'fr', t.label_fr) ORDER BY t.kind, t.slug)30 FROM film_tags ft JOIN tags t ON t.id = ft.tag_id WHERE ft.film_id = f.id) AS tags31 FROM films f LEFT JOIN series s ON s.id = f.series_id`;3233function shape(r: FilmDb, l: Locale): Film {34 const fr = l === "fr";35 return {36 id: r.id, slug: r.slug, title: fr ? r.title_fr : r.title_en, titleAlt: fr ? r.title_en : r.title_fr, synopsis: fr ? r.synopsis_fr : r.synopsis_en,37 takeaway: fr ? r.takeaway_fr : r.takeaway_en, language: r.language, kind: r.kind, duration: Number(r.duration), width: r.width, height: r.height,38 video: media(r.master_sha, "mp4")!, videoLow: media(r.low_sha, "mp4"), poster: media(r.poster_sha, "jpg")!, thumb: media(r.thumb_sha ?? r.poster_sha, "jpg")!,39 captions: { en: media(r.captions?.en, "vtt"), fr: media(r.captions?.fr, "vtt") },40 series: r.series_slug ? { slug: r.series_slug, title: (fr ? r.series_title_fr : r.series_title_en)!, accent: r.series_accent! } : null,41 episode: r.episode, tags: (r.tags ?? []).map((t) => ({ slug: t.slug, kind: t.kind, label: fr ? t.fr : t.en })),42 publishedAt: r.published_at.toISOString(), featured: r.featured, qualityScore: r.quality_score, views: r.views, likes: r.likes, bytes: Number(r.master_bytes),43 };44}4546export interface FilmFilter { series?: string; tag?: string; language?: string; kind?: string; maxDuration?: number; sort?: "latest" | "top"; cursor?: string | null; limit?: number }47const encodeCursor = (d: Date, id: string) => Buffer.from(`${d.toISOString()}|${id}`).toString("base64url");48function decodeCursor(c: string | null | undefined): { at: Date; id: string } | null {49 if (!c) return null;50 try {51 const [at, id] = Buffer.from(c, "base64url").toString().split("|");52 const d = new Date(at);53 return Number.isNaN(d.getTime()) || !/^[0-9a-f-]{36}$/.test(id) ? null : { at: d, id };54 } catch { return null; }55}5657export async function listFilms(l: Locale, f: FilmFilter = {}): Promise<{ films: Film[]; nextCursor: string | null }> {58 const limit = Math.min(Math.max(f.limit ?? 12, 1), 48);59 const cur = f.sort === "top" ? null : decodeCursor(f.cursor);60 const rows = await sql<FilmDb[]>`61 ${SELECT}62 WHERE f.status = 'published'63 ${f.series ? sql`AND s.slug = ${f.series}` : sql``}64 ${f.tag ? sql`AND EXISTS (SELECT 1 FROM film_tags ft JOIN tags t ON t.id = ft.tag_id WHERE ft.film_id = f.id AND t.slug = ${f.tag})` : sql``}65 ${f.language ? sql`AND f.language = ${f.language}` : sql``}66 ${f.kind ? sql`AND f.kind = ${f.kind}` : sql``}67 ${f.maxDuration ? sql`AND f.duration <= ${f.maxDuration}` : sql``}68 ${cur ? sql`AND (f.published_at, f.id) < (${cur.at}, ${cur.id})` : sql``}69 ORDER BY ${f.sort === "top" ? sql`f.quality_score DESC NULLS LAST, f.likes DESC,` : sql``} f.published_at DESC, f.id DESC70 LIMIT ${limit + 1}`;71 const page = rows.slice(0, limit);72 const last = page.at(-1);73 return { films: page.map((r) => shape(r, l)), nextCursor: rows.length > limit && last && f.sort !== "top" ? encodeCursor(last.published_at, last.id) : null };74}7576export async function getFilm(l: Locale, slug: string): Promise<(FilmDetail & { _embedding: number[] }) | null> {77 const [r] = await sql<FilmDb[]>`${SELECT} WHERE f.slug = ${slug} AND f.status = 'published'`;78 if (!r) return null;79 // The public provenance block never includes prompts: it is built at publication from the manifest only.80 return { ...shape(r, l), provenance: r.provenance, sources: r.sources ?? [], _embedding: r.embedding };81}82export async function filmsByIds(l: Locale, ids: string[]): Promise<Film[]> {83 const clean = ids.filter((x) => /^[0-9a-f-]{36}$/.test(x)).slice(0, 60);84 if (!clean.length) return [];85 const rows = await sql<FilmDb[]>`${SELECT} WHERE f.status = 'published' AND f.id = ANY(${clean})`;86 return rows.map((r) => shape(r, l));87}8889export interface Related { film: Film; reason: string; score: number }90/** Explainable recommendations: lexical similarity + shared series/tags; no engagement signal. */91export async function relatedFilms(l: Locale, film: FilmDetail & { _embedding: number[] }, n = 4): Promise<Related[]> {92 const rows = await sql<FilmDb[]>`${SELECT} WHERE f.status = 'published' AND f.id <> ${film.id}`;93 const fr = l === "fr";94 return rows.map((r) => {95 const other = shape(r, l);96 const sim = cosine(film._embedding, r.embedding ?? []);97 const sameSeries = !!film.series && film.series.slug === other.series?.slug;98 const shared = other.tags.filter((t) => film.tags.some((x) => x.slug === t.slug));99 const terms = sharedTerms(`${film.title} ${film.synopsis}`, `${other.title} ${other.synopsis}`, 3);100 const score = sim + (sameSeries ? 0.25 : 0) + Math.min(shared.length, 4) * 0.06;101 const reason = sameSeries ? (fr ? `Même série : ${other.series!.title}` : `Same series: ${other.series!.title}`)102 : shared.length ? `${fr ? "Thèmes communs" : "Shared tags"}: ${shared.slice(0, 3).map((t) => t.label).join(", ")}`103 : terms.length ? `${fr ? "Vocabulaire proche" : "Similar wording"}: ${terms.join(", ")}`104 : fr ? "Également à l'affiche" : "Also showing";105 return { film: other, reason, score };106 }).sort((a, b) => b.score - a.score).slice(0, n);107}108109export interface SearchHit { film: Film; signals: string[]; score: number }110/** Hybrid search: full-text rank + trigram similarity + lexical-embedding cosine. Each hit says which signal matched. */111export async function searchFilms(l: Locale, q: string, limit = 24): Promise<SearchHit[]> {112 const query = q.trim().slice(0, 120);113 if (query.length < 2) return [];114 const rows = await sql<(FilmDb & { rank: number; trgm: number })[]>`115 SELECT x.*, ts_rank(x.search, plainto_tsquery('simple', ${query})) AS rank,116 GREATEST(similarity(x.title_en, ${query}), similarity(x.title_fr, ${query})) AS trgm117 FROM (${SELECT} WHERE f.status = 'published') x`;118 const qv = embed(query);119 const fr = l === "fr";120 return rows.map((r) => {121 const cos = cosine(qv, r.embedding ?? []);122 const signals: string[] = [];123 if (r.rank > 0) signals.push(fr ? "texte" : "text match");124 if (r.trgm > 0.3) signals.push(fr ? "titre" : "title");125 if (cos > 0.08) signals.push(fr ? "proximité lexicale" : "lexical similarity");126 const tag = (r.tags ?? []).find((t) => [t.en, t.fr, t.slug].some((x) => x.toLowerCase() === query.toLowerCase()));127 if (tag) signals.push(fr ? "étiquette" : "tag");128 return { film: shape(r, l), signals, score: Number(r.rank) * 2 + Number(r.trgm) + cos * 1.5 + (tag ? 1 : 0) };129 }).filter((h) => h.signals.length).sort((a, b) => b.score - a.score).slice(0, limit);130}131132export interface SeriesInfo { slug: string; title: string; description: string; kind: string; accent: string; count: number; cover: string | null }133export async function listSeries(l: Locale): Promise<SeriesInfo[]> {134 const rows = await sql<{ slug: string; title_en: string; title_fr: string; description_en: string; description_fr: string; kind: string; accent: string; count: number; cover: string | null }[]>`135 SELECT s.slug, s.title_en, s.title_fr, s.description_en, s.description_fr, s.kind, s.accent,136 (SELECT COUNT(*)::int FROM films f WHERE f.series_id = s.id AND f.status = 'published') AS count,137 (SELECT COALESCE(f.thumb_sha, f.poster_sha) FROM films f WHERE f.series_id = s.id AND f.status = 'published' ORDER BY f.published_at DESC LIMIT 1) AS cover138 FROM series s ORDER BY s.sort`;139 return rows.map((r) => ({ slug: r.slug, title: l === "fr" ? r.title_fr : r.title_en, description: l === "fr" ? r.description_fr : r.description_en, kind: r.kind, accent: r.accent, count: r.count, cover: media(r.cover, "jpg") }));140}141export async function listCollections(l: Locale): Promise<{ slug: string; title: string; description: string; films: Film[] }[]> {142 const cols = await sql<{ id: string; slug: string; title_en: string; title_fr: string; description_en: string; description_fr: string }[]>`SELECT * FROM collections ORDER BY sort`;143 const out = [];144 for (const c of cols) {145 const rows = await sql<FilmDb[]>`${SELECT} JOIN collection_films cf ON cf.film_id = f.id WHERE cf.collection_id = ${c.id} AND f.status = 'published' ORDER BY cf.sort`;146 if (rows.length) out.push({ slug: c.slug, title: l === "fr" ? c.title_fr : c.title_en, description: l === "fr" ? c.description_fr : c.description_en, films: rows.map((r) => shape(r, l)) });147 }148 return out;149}150export async function listTags(l: Locale): Promise<{ slug: string; kind: string; label: string; count: number }[]> {151 const rows = await sql<{ slug: string; kind: string; label_en: string; label_fr: string; count: number }[]>`152 SELECT t.slug, t.kind, t.label_en, t.label_fr, COUNT(f.id)::int AS count153 FROM tags t JOIN film_tags ft ON ft.tag_id = t.id JOIN films f ON f.id = ft.film_id AND f.status = 'published'154 GROUP BY t.id ORDER BY t.kind, count DESC, t.slug`;155 return rows.map((r) => ({ slug: r.slug, kind: r.kind, label: l === "fr" ? r.label_fr : r.label_en, count: r.count }));156}157export async function catalogStats() {158 const [r] = await sql<{ films: number; seconds: number; shots: number }[]>`159 SELECT COUNT(*)::int AS films, COALESCE(SUM(duration), 0)::float8 AS seconds,160 COALESCE(SUM(jsonb_array_length(provenance->'manifest'->'shots')), 0)::int AS shots161 FROM films WHERE status = 'published'`;162 return r;163}164export async function heroFilm(l: Locale): Promise<Film | null> {165 const [r] = await sql<FilmDb[]>`${SELECT} WHERE f.status = 'published' ORDER BY f.featured DESC, f.published_at DESC LIMIT 1`;166 return r ? shape(r, l) : null;167}168