spb/wp-spboucher-ai
Public
1import "server-only";2import { sqlPublic as sql } from "@/db";3import type { ListParams, PaperFull, PaperSummary } from "./types";4import { parseIdentifier } from "./identifiers";56const SUMMARY_SELECT = sql`7 p.id, p.identifier, p.slug, p.number, s.code AS series_code, s.name_en AS series_name_en, s.name_fr AS series_name_fr,8 p.title, p.subtitle, p.title_fr, p.abstract, p.year, p.status, p.publication_status, p.notice, p.language,9 p.first_released_on::text, p.first_released_precision, p.last_revised_on::text, p.last_revised_precision,10 v.label AS version_label, v.version_no, v.pages, (v.file_id IS NOT NULL AND NOT v.withdrawn) AS has_pdf, v.file_id, v.sha256,11 coalesce((SELECT json_agg(json_build_object('slug', a.slug, 'display_name', a.display_name, 'given_name', a.given_name, 'family_name', a.family_name) ORDER BY pa.position) FROM paper_authors pa JOIN authors a ON a.id = pa.author_id WHERE pa.paper_id = p.id), '[]'::json) AS authors,12 (SELECT json_build_object('slug', t.slug, 'name_en', t.name_en, 'name_fr', t.name_fr, 'accent', t.accent) FROM topics t WHERE t.id = p.primary_domain_id) AS domain,13 p.keywords, p.ssrn_id, p.doi, p.view_count::int, p.download_count::int, p.featured, p.featured_rank, p.published_at::text, p.updated_at::text,14 (SELECT count(*)::int FROM public_paper_versions pv WHERE pv.paper_id = p.id) AS versions_count`;15const FROM = sql`FROM public_papers p JOIN series s ON s.id = p.series_id LEFT JOIN public_paper_versions v ON v.id = p.current_version_id`;1617export async function listPapers(params: ListParams): Promise<{ items: PaperSummary[]; total: number; page: number; perPage: number }> {18 const perPage = Math.min(100, Math.max(1, params.perPage ?? 20)); const page = Math.max(1, params.page ?? 1);19 const conds = [sql`p.status = 'published'`];20 if (params.featured) conds.push(sql`p.featured`);21 if (params.year?.length) conds.push(sql`p.year = ANY(${params.year})`);22 if (params.series?.length) conds.push(sql`s.code = ANY(${params.series.map((x) => x.toUpperCase())})`);23 if (params.status?.length) conds.push(sql`p.status = ANY(${params.status})`);24 if (params.pubStatus?.length) conds.push(sql`p.publication_status = ANY(${params.pubStatus})`);25 if (params.lang?.length) conds.push(sql`p.language = ANY(${params.lang})`);26 if (params.pdf) conds.push(sql`v.file_id IS NOT NULL AND NOT v.withdrawn`);27 if (params.domain?.length) conds.push(sql`EXISTS (SELECT 1 FROM paper_topics pt JOIN topics t ON t.id = pt.topic_id WHERE pt.paper_id = p.id AND t.kind = 'domain' AND t.slug = ANY(${params.domain}))`);28 if (params.topic?.length) conds.push(sql`EXISTS (SELECT 1 FROM paper_topics pt JOIN topics t ON t.id = pt.topic_id WHERE pt.paper_id = p.id AND t.slug = ANY(${params.topic}))`);29 if (params.author?.length) conds.push(sql`EXISTS (SELECT 1 FROM paper_authors pa JOIN authors a ON a.id = pa.author_id WHERE pa.paper_id = p.id AND a.slug = ANY(${params.author}))`);30 if (params.method?.length) conds.push(sql`EXISTS (SELECT 1 FROM paper_methods pm JOIN methods m ON m.id = pm.method_id WHERE pm.paper_id = p.id AND m.slug = ANY(${params.method}))`);31 if (params.dataset?.length) conds.push(sql`EXISTS (SELECT 1 FROM paper_datasets pd JOIN datasets d ON d.id = pd.dataset_id WHERE pd.paper_id = p.id AND d.slug = ANY(${params.dataset}))`);32 if (params.keyword?.length) conds.push(sql`p.keywords && ${params.keyword}::text[]`);33 const q = params.q?.trim();34 if (q) conds.push(sql`(p.fts @@ websearch_to_tsquery('english', f_unaccent(${q})) OR p.search_blob % f_unaccent(lower(${q})) OR word_similarity(f_unaccent(lower(${q})), p.search_blob) > 0.45 OR p.identifier ILIKE ${"%" + q.replace(/\s+/g, "") + "%"} OR EXISTS (SELECT 1 FROM paper_authors pa JOIN authors a ON a.id = pa.author_id WHERE pa.paper_id = p.id AND f_unaccent(lower(a.display_name)) LIKE '%' || f_unaccent(lower(${q})) || '%'))`);35 const where = conds.reduce((acc, c) => sql`${acc} AND ${c}`);36 const order = {37 recent: sql`p.first_released_on DESC NULLS LAST, p.number DESC`, revised: sql`p.last_revised_on DESC NULLS LAST, p.number DESC`, number: sql`s.sort, p.number DESC`,38 title: sql`p.title ASC`, year: sql`p.year DESC NULLS LAST, p.number DESC`, views: sql`p.view_count DESC, p.number DESC`, downloads: sql`p.download_count DESC, p.number DESC`,39 }[params.sort ?? "recent"];40 const rank = q ? sql`ts_rank_cd(p.fts, websearch_to_tsquery('english', f_unaccent(${q}))) + similarity(f_unaccent(lower(${q})), p.search_blob) DESC,` : sql``;41 const [{ total }] = await sql`SELECT count(*)::int AS total ${FROM} WHERE ${where}`;42 const items = await sql`SELECT ${SUMMARY_SELECT} ${FROM} WHERE ${where} ORDER BY ${rank} ${order} LIMIT ${perPage} OFFSET ${(page - 1) * perPage}`;43 return { items: items as unknown as PaperSummary[], total, page, perPage };44}4546export async function getPaper(idOrSlug: string): Promise<PaperFull | null> {47 const parsed = parseIdentifier(idOrSlug); const slug = parsed?.slug ?? idOrSlug.toLowerCase();48 const rows = await sql`SELECT ${SUMMARY_SELECT}, p.abstract_fr, p.structured_abstract, p.research_question, p.research_question_fr, p.hero_headline, p.hero_subheadline, p.contributions, p.methodology, p.data_sections, p.findings, p.reproducibility, p.artifacts, p.external_links, p.jel, p.journal_ref, p.internal_code, p.embedding_model,49 coalesce((SELECT json_agg(json_build_object('slug', t.slug, 'name_en', t.name_en, 'name_fr', t.name_fr, 'kind', t.kind, 'assigned_by', pt.assigned_by) ORDER BY t.kind, t.sort) FROM paper_topics pt JOIN topics t ON t.id = pt.topic_id WHERE pt.paper_id = p.id), '[]'::json) AS topics,50 coalesce((SELECT json_agg(json_build_object('slug', m.slug, 'name_en', m.name_en, 'name_fr', m.name_fr, 'assigned_by', pm.assigned_by, 'note', pm.note) ORDER BY m.sort) FROM paper_methods pm JOIN methods m ON m.id = pm.method_id WHERE pm.paper_id = p.id), '[]'::json) AS methods,51 coalesce((SELECT json_agg(json_build_object('slug', d.slug, 'name_en', d.name_en, 'name_fr', d.name_fr, 'provider', d.provider, 'assigned_by', pd.assigned_by, 'role', pd.role) ORDER BY d.sort) FROM paper_datasets pd JOIN datasets d ON d.id = pd.dataset_id WHERE pd.paper_id = p.id), '[]'::json) AS datasets,52 coalesce((SELECT json_agg(json_build_object('id', pv.id, 'version_no', pv.version_no, 'label', pv.label, 'released_on', pv.released_on::text, 'released_precision', pv.released_precision, 'description', pv.description, 'description_fr', pv.description_fr, 'file_id', CASE WHEN pv.withdrawn THEN NULL ELSE pv.file_id END, 'pages', pv.pages, 'sha256', pv.sha256, 'bytes', pv.bytes, 'is_current', pv.is_current, 'withdrawn', pv.withdrawn, 'withdrawn_reason', pv.withdrawn_reason) ORDER BY pv.version_no DESC) FROM public_paper_versions pv WHERE pv.paper_id = p.id), '[]'::json) AS versions,53 coalesce((SELECT json_agg(x) FROM (SELECT q.slug, q.identifier, q.title, q.year, array_agg(r.kind ORDER BY r.kind) AS kinds, array_agg(r.explanation ORDER BY r.kind) AS explanations, array_agg(r.explanation_fr ORDER BY r.kind) AS explanations_fr, bool_and(r.inferred) AS inferred, sum(r.weight)::real AS weight54 FROM public_paper_relations r JOIN public_papers q ON q.id = r.to_paper_id AND q.status = 'published' WHERE r.from_paper_id = p.id GROUP BY q.slug, q.identifier, q.title, q.year, q.number ORDER BY sum(r.weight) DESC, q.number DESC LIMIT 8) x), '[]'::json) AS related55 ${FROM} WHERE p.slug = ${slug} LIMIT 1`;56 return (rows[0] as unknown as PaperFull) ?? null;57}5859export async function facets() {60 const [years, domains, topics, authors, methods, datasets, series, keywords] = await Promise.all([61 sql`SELECT year, count(*)::int AS n FROM public_papers WHERE status = 'published' AND year IS NOT NULL GROUP BY year ORDER BY year DESC`,62 sql`SELECT t.slug, t.name_en, t.name_fr, t.accent, count(pt.paper_id)::int AS n FROM topics t LEFT JOIN paper_topics pt ON pt.topic_id = t.id LEFT JOIN public_papers p ON p.id = pt.paper_id AND p.status = 'published' WHERE t.kind = 'domain' AND t.is_active GROUP BY t.id ORDER BY t.sort`,63 sql`SELECT t.slug, t.name_en, t.name_fr, count(p.id)::int AS n FROM topics t JOIN paper_topics pt ON pt.topic_id = t.id JOIN public_papers p ON p.id = pt.paper_id AND p.status = 'published' WHERE t.kind = 'topic' GROUP BY t.id HAVING count(p.id) > 0 ORDER BY t.name_en`,64 sql`SELECT a.slug, a.display_name AS name, count(p.id)::int AS n FROM authors a JOIN paper_authors pa ON pa.author_id = a.id JOIN public_papers p ON p.id = pa.paper_id AND p.status = 'published' GROUP BY a.id ORDER BY a.sort`,65 sql`SELECT m.slug, m.name_en, m.name_fr, count(p.id)::int AS n FROM methods m JOIN paper_methods pm ON pm.method_id = m.id JOIN public_papers p ON p.id = pm.paper_id AND p.status = 'published' GROUP BY m.id HAVING count(p.id) > 0 ORDER BY m.name_en`,66 sql`SELECT d.slug, d.name_en, d.name_fr, count(p.id)::int AS n FROM datasets d JOIN paper_datasets pd ON pd.dataset_id = d.id JOIN public_papers p ON p.id = pd.paper_id AND p.status = 'published' GROUP BY d.id HAVING count(p.id) > 0 ORDER BY d.name_en`,67 sql`SELECT s.code AS slug, s.name_en, s.name_fr, count(p.id)::int AS n FROM series s LEFT JOIN public_papers p ON p.series_id = s.id AND p.status = 'published' GROUP BY s.id ORDER BY s.sort`,68 sql`SELECT k AS keyword, count(*)::int AS n FROM public_papers p, unnest(p.keywords) k WHERE p.status = 'published' GROUP BY k HAVING count(*) >= 2 ORDER BY n DESC, k LIMIT 60`,69 ]);70 type N = { slug: string; name_en: string; name_fr: string; n: number; accent?: string | null };71 return { years: years as unknown as { year: number; n: number }[], domains: domains as unknown as N[], topics: topics as unknown as N[], authors: authors as unknown as { slug: string; name: string; n: number }[], methods: methods as unknown as N[], datasets: datasets as unknown as N[], series: series as unknown as N[], keywords: keywords as unknown as { keyword: string; n: number }[] };72}7374export async function stats() {75 const [r] = await sql`SELECT76 (SELECT count(*)::int FROM public_papers WHERE status = 'published') AS papers,77 (SELECT count(*)::int FROM public_papers p JOIN public_paper_versions v ON v.id = p.current_version_id WHERE p.status = 'published' AND v.file_id IS NOT NULL AND NOT v.withdrawn) AS manuscripts,78 (SELECT count(DISTINCT t.id)::int FROM topics t JOIN paper_topics pt ON pt.topic_id = t.id JOIN public_papers p ON p.id = pt.paper_id AND p.status = 'published' WHERE t.kind = 'topic') AS topics,79 (SELECT count(DISTINCT t.id)::int FROM topics t JOIN paper_topics pt ON pt.topic_id = t.id JOIN public_papers p ON p.id = pt.paper_id AND p.status = 'published' WHERE t.kind = 'domain') AS domains,80 (SELECT count(DISTINCT a.id)::int FROM authors a JOIN paper_authors pa ON pa.author_id = a.id JOIN public_papers p ON p.id = pa.paper_id AND p.status = 'published') AS authors,81 (SELECT count(DISTINCT d.id)::int FROM datasets d JOIN paper_datasets pd ON pd.dataset_id = d.id JOIN public_papers p ON p.id = pd.paper_id AND p.status = 'published') AS datasets,82 (SELECT count(DISTINCT m.id)::int FROM methods m JOIN paper_methods pm ON pm.method_id = m.id JOIN public_papers p ON p.id = pm.paper_id AND p.status = 'published') AS methods,83 (SELECT max(greatest(first_released_on, last_revised_on))::text FROM public_papers WHERE status = 'published') AS latest,84 (SELECT max(first_released_precision) FROM public_papers WHERE status = 'published') AS latest_precision,85 (SELECT coalesce(sum(v.pages), 0)::int FROM public_papers p JOIN public_paper_versions v ON v.id = p.current_version_id WHERE p.status = 'published') AS pages,86 (SELECT count(*)::int FROM public_papers WHERE status = 'published' AND ssrn_id IS NOT NULL) AS ssrn,87 (SELECT count(*)::int FROM public_paper_versions v JOIN public_papers p ON p.id = v.paper_id WHERE p.status = 'published') AS versions`;88 return r as unknown as { papers: number; manuscripts: number; topics: number; domains: number; authors: number; datasets: number; methods: number; latest: string | null; latest_precision: "day" | "month" | "year"; pages: number; ssrn: number; versions: number };89}9091export async function domainsWithCounts() {92 return sql`SELECT t.slug, t.name_en, t.name_fr, t.description_en, t.description_fr, t.accent, count(pt.paper_id)::int AS n FROM topics t LEFT JOIN paper_topics pt ON pt.topic_id = t.id LEFT JOIN public_papers p ON p.id = pt.paper_id AND p.status = 'published' WHERE t.kind = 'domain' AND t.is_active GROUP BY t.id ORDER BY t.sort`;93}94export async function topicsWithCounts() {95 return sql`SELECT t.slug, t.name_en, t.name_fr, t.description_en, t.description_fr, t.kind, t.accent, count(p.id)::int AS n FROM topics t LEFT JOIN paper_topics pt ON pt.topic_id = t.id LEFT JOIN public_papers p ON p.id = pt.paper_id AND p.status = 'published' WHERE t.is_active GROUP BY t.id ORDER BY t.kind, t.sort`;96}97export async function getTopic(slug: string) { const [t] = await sql`SELECT slug, name_en, name_fr, description_en, description_fr, kind, accent FROM topics WHERE slug = ${slug} AND is_active`; return t ?? null; }98export async function methodsWithCounts() {99 return sql`SELECT m.slug, m.name_en, m.name_fr, m.description_en, m.description_fr, m.category, count(p.id)::int AS n, coalesce(json_agg(json_build_object('slug', p.slug, 'identifier', p.identifier, 'title', p.title, 'year', p.year) ORDER BY p.number DESC) FILTER (WHERE p.id IS NOT NULL), '[]'::json) AS papers100 FROM methods m LEFT JOIN paper_methods pm ON pm.method_id = m.id LEFT JOIN public_papers p ON p.id = pm.paper_id AND p.status = 'published' GROUP BY m.id ORDER BY m.category, m.name_en`;101}102export async function getMethod(slug: string) { const [m] = await sql`SELECT slug, name_en, name_fr, description_en, description_fr, category FROM methods WHERE slug = ${slug}`; return m ?? null; }103export async function datasetsWithCounts() {104 return sql`SELECT d.slug, d.name_en, d.name_fr, d.provider, d.coverage, d.frequency, d.access_conditions, d.license, d.url, d.description_en, d.description_fr, d.is_public, count(p.id)::int AS n, coalesce(json_agg(json_build_object('slug', p.slug, 'identifier', p.identifier, 'title', p.title, 'year', p.year) ORDER BY p.number DESC) FILTER (WHERE p.id IS NOT NULL), '[]'::json) AS papers105 FROM datasets d LEFT JOIN paper_datasets pd ON pd.dataset_id = d.id LEFT JOIN public_papers p ON p.id = pd.paper_id AND p.status = 'published' GROUP BY d.id ORDER BY n DESC, d.name_en`;106}107export async function getDataset(slug: string) { const [d] = await sql`SELECT * FROM datasets WHERE slug = ${slug}`; return d ?? null; }108export async function authorsWithCounts() {109 return sql`SELECT a.*, count(p.id)::int AS n FROM authors a LEFT JOIN paper_authors pa ON pa.author_id = a.id LEFT JOIN public_papers p ON p.id = pa.paper_id AND p.status = 'published' GROUP BY a.id ORDER BY a.sort, a.family_name`;110}111export async function getAuthor(slug: string) { const [a] = await sql`SELECT * FROM authors WHERE slug = ${slug}`; return a ?? null; }112export async function seriesWithCounts() {113 return sql`SELECT s.*, count(p.id)::int AS n, min(p.number)::int AS min_no, max(p.number)::int AS max_no FROM series s LEFT JOIN public_papers p ON p.series_id = s.id AND p.status = 'published' WHERE s.is_active GROUP BY s.id ORDER BY s.sort`;114}115export async function timeline() {116 const rows = await sql`SELECT p.slug, p.identifier, p.title, p.year, p.first_released_on::text AS released, p.first_released_precision AS rp, v.released_on::text AS vdate, v.version_no, v.label FROM public_papers p JOIN public_paper_versions v ON v.paper_id = p.id WHERE p.status = 'published' ORDER BY coalesce(v.released_on, p.first_released_on) DESC, p.number DESC`;117 return rows as unknown as { slug: string; identifier: string; title: string; year: number | null; released: string | null; rp: string; vdate: string | null; version_no: number; label: string }[];118}119export async function graph(kinds?: string[]) {120 const nodes = await sql`SELECT p.slug, p.identifier, p.title, p.year, t.slug AS domain, t.accent, p.view_count::int AS views FROM public_papers p LEFT JOIN topics t ON t.id = p.primary_domain_id WHERE p.status = 'published' ORDER BY p.number`;121 const edges = kinds?.length122 ? await sql`SELECT a.slug AS source, b.slug AS target, r.kind, r.weight, r.inferred, r.explanation, r.explanation_fr FROM public_paper_relations r JOIN public_papers a ON a.id = r.from_paper_id JOIN public_papers b ON b.id = r.to_paper_id WHERE a.slug < b.slug AND r.kind = ANY(${kinds})`123 : await sql`SELECT a.slug AS source, b.slug AS target, r.kind, r.weight, r.inferred, r.explanation, r.explanation_fr FROM public_paper_relations r JOIN public_papers a ON a.id = r.from_paper_id JOIN public_papers b ON b.id = r.to_paper_id WHERE a.slug < b.slug`;124 return { nodes, edges };125}126export async function siteSetting<T = unknown>(key: string): Promise<T | null> { const [r] = await sql`SELECT value FROM public_settings WHERE key = ${key}`; return (r?.value as T) ?? null; }127export async function findRedirect(path: string) { const [r] = await sql`SELECT to_path, status FROM redirects WHERE from_path = ${path}`; return r ?? null; }128export async function publicFile(fileId: string) { const [f] = await sql`SELECT f.*, v.paper_id, v.id AS version_id, p.slug, p.identifier, v.label FROM public_files f JOIN public_paper_versions v ON v.file_id = f.id JOIN public_papers p ON p.id = v.paper_id WHERE f.id = ${fileId} AND NOT v.withdrawn LIMIT 1`; return f ?? null; }129export async function previewFor(slug: string) { const [r] = await sql`SELECT f.sha256 FROM public_papers p JOIN public_paper_versions v ON v.id = p.current_version_id JOIN public_files f ON f.id = v.file_id WHERE p.slug = ${slug}`; return r?.sha256 ?? null; }130