spb/wp-spboucher-ai

1import "server-only";2import { sql } from "@/db";3export async function adminPaperList(q?: string, status?: string) {4  return sql`SELECT p.id, p.identifier, p.slug, p.title, p.status, p.publication_status, p.year, p.featured, p.updated_at::text, p.view_count::int, p.download_count::int, s.code AS series_code, v.label AS version_label, (v.file_id IS NOT NULL) AS has_pdf,5    (SELECT string_agg(a.display_name, ', ' ORDER BY pa.position) FROM paper_authors pa JOIN authors a ON a.id = pa.author_id WHERE pa.paper_id = p.id) AS authors6    FROM papers p JOIN series s ON s.id = p.series_id LEFT JOIN paper_versions v ON v.id = p.current_version_id7    WHERE (${q ?? null}::text IS NULL OR p.title ILIKE '%' || ${q ?? ""} || '%' OR p.identifier ILIKE '%' || ${q ?? ""} || '%') AND (${status ?? null}::text IS NULL OR p.status = ${status ?? ""}) ORDER BY s.sort, p.number DESC`;8}9export async function adminPaper(id: string) {10  const [p] = await sql`SELECT p.*, s.code AS series_code FROM papers p JOIN series s ON s.id = p.series_id WHERE p.id = ${id} OR p.slug = ${id}`; if (!p) return null;11  const [authors, topics, methods, datasets, versions, relations, redirects] = await Promise.all([12    sql`SELECT a.id, a.display_name, pa.position FROM paper_authors pa JOIN authors a ON a.id = pa.author_id WHERE pa.paper_id = ${p.id} ORDER BY pa.position`,13    sql`SELECT topic_id AS id, assigned_by FROM paper_topics WHERE paper_id = ${p.id}`,14    sql`SELECT method_id AS id, assigned_by FROM paper_methods WHERE paper_id = ${p.id}`,15    sql`SELECT dataset_id AS id, assigned_by FROM paper_datasets WHERE paper_id = ${p.id}`,16    sql`SELECT v.*, f.integrity_ok, f.text_status, f.bytes AS file_bytes FROM paper_versions v LEFT JOIN files f ON f.id = v.file_id WHERE v.paper_id = ${p.id} ORDER BY v.version_no DESC`,17    sql`SELECT r.id, r.kind, r.weight, r.explanation, r.inferred, q.identifier, q.title, q.slug FROM paper_relations r JOIN papers q ON q.id = r.to_paper_id WHERE r.from_paper_id = ${p.id} ORDER BY r.kind, r.weight DESC`,18    sql`SELECT * FROM redirects WHERE to_path = ${"/papers/" + p.slug} ORDER BY from_path`,19  ]);20  return { ...(p as Record<string, any>), authors, topics, methods, datasets, versions, relations, redirects } as Record<string, any> & { authors: Record<string, any>[]; topics: Record<string, any>[]; methods: Record<string, any>[]; datasets: Record<string, any>[]; versions: Record<string, any>[]; relations: Record<string, any>[]; redirects: Record<string, any>[] };21}22export async function taxonomy() {23  const [series, authors, topics, methods, datasets] = await Promise.all([sql`SELECT * FROM series ORDER BY sort`, sql`SELECT * FROM authors ORDER BY sort, family_name`, sql`SELECT t.*, (SELECT count(*)::int FROM paper_topics pt WHERE pt.topic_id = t.id) AS n FROM topics t ORDER BY kind, sort`, sql`SELECT m.*, (SELECT count(*)::int FROM paper_methods pm WHERE pm.method_id = m.id) AS n FROM methods m ORDER BY category, sort`, sql`SELECT d.*, (SELECT count(*)::int FROM paper_datasets pd WHERE pd.dataset_id = d.id) AS n FROM datasets d ORDER BY sort`]);24  return { series, authors, topics, methods, datasets };25}26export async function dashboard() {27  const [[counts], queue, jobs, [backup], audit, searches, views, ai] = await Promise.all([28    sql`SELECT count(*) FILTER (WHERE status = 'published')::int AS published, count(*) FILTER (WHERE status = 'draft')::int AS draft, count(*) FILTER (WHERE status = 'reserved')::int AS reserved, count(*) FILTER (WHERE status = 'withdrawn')::int AS withdrawn, count(*)::int AS total FROM papers`,29    sql`SELECT status, count(*)::int AS n FROM ingest_candidates GROUP BY status`,30    sql`SELECT status, count(*)::int AS n FROM jobs WHERE created_at > now() - interval '7 days' GROUP BY status`,31    sql`SELECT * FROM backups ORDER BY created_at DESC LIMIT 1`,32    sql`SELECT * FROM audit_log ORDER BY at DESC LIMIT 12`,33    sql`SELECT query, count(*)::int AS n, avg(results)::int AS avg_results FROM search_log WHERE at > now() - interval '30 days' GROUP BY query ORDER BY n DESC LIMIT 10`,34    sql`SELECT day::text, sum(views)::int AS views, sum(downloads)::int AS downloads FROM paper_stats_daily WHERE day > current_date - 30 GROUP BY day ORDER BY day`,35    sql`SELECT count(*) FILTER (WHERE embedding IS NOT NULL)::int AS embedded, count(*)::int AS total, (SELECT count(*)::int FROM paper_chunks WHERE embedding IS NOT NULL) AS chunks_embedded, (SELECT count(*)::int FROM paper_chunks) AS chunks FROM papers WHERE status = 'published'`,36  ]);37  return { counts, queue, jobs, backup: backup ?? null, audit, searches, views, ai: ai[0] };38}39