import "./_env";
import { readFileSync, existsSync, statSync } from "node:fs";
import { join } from "node:path";
import { sql } from "../src/db";
import { makeIdentifier, parseVersionLabel } from "../src/lib/papers/identifiers";
import { sha256File, storeFile, ensureStorage } from "../src/lib/pdf/process";
import { lexvecFromMetadata } from "../src/lib/jobs/handlers";
import { enqueue } from "../src/lib/jobs/queue";
import { STORAGE_DIR } from "../src/lib/env";

/**
 * Imports the verified catalog (content/seed/catalog.json — written from the manuscripts on 2026-11-01 for www.spboucher.ai)
 * and the editorial taxonomy (content/seed/taxonomy.json). Idempotent: papers are upserted by slug; relations/tags are replaced.
 * Manuscripts are looked up in $STORAGE_DIR/inbox/<slug>_uqo.pdf (or --pdf-dir). Status = published only with --publish, because the
 * records were already public on www.spboucher.ai; otherwise papers are imported as drafts for review in /admin.
 */
const args = process.argv.slice(2); const publish = args.includes("--publish");
const pdfDir = args.find((a) => a.startsWith("--pdf-dir="))?.slice(10) ?? join(STORAGE_DIR, "inbox");
const root = join(import.meta.dirname, "..");
const catalog = JSON.parse(readFileSync(join(root, "content/seed/catalog.json"), "utf8")) as { papers: any[]; details: any[] };
const tx = JSON.parse(readFileSync(join(root, "content/seed/taxonomy.json"), "utf8")) as any;
const MONTH: Record<string, number> = { january: 1, february: 2, march: 3, april: 4, may: 5, june: 6, july: 7, august: 8, september: 9, october: 10, november: 11, december: 12 };
function monthDate(s?: string): string | null { if (!s) return null; const m = /^(\w+)\s+(\d{4})$/.exec(s.trim()); if (!m) return null; const mo = MONTH[m[1].toLowerCase()]; return mo ? `${m[2]}-${String(mo).padStart(2, "0")}-01` : null; }

await ensureStorage();
const log = (m: string) => console.log(m);
// Series
const seriesId: Record<string, number> = {};
for (const s of tx.series) { const [r] = await sql`INSERT INTO series (slug, code, name_en, name_fr, description_en, description_fr, sort) VALUES (${s.slug}, ${s.code}, ${s.name_en}, ${s.name_fr}, ${s.description_en}, ${s.description_fr}, ${s.sort}) ON CONFLICT (slug) DO UPDATE SET name_en = EXCLUDED.name_en, name_fr = EXCLUDED.name_fr, description_en = EXCLUDED.description_en, description_fr = EXCLUDED.description_fr, sort = EXCLUDED.sort RETURNING id`; seriesId[s.code] = r.id; }
// Authors
const authorId: Record<string, number> = {}; const authorByName: Record<string, number> = {};
for (const a of tx.authors) { const [r] = await sql`INSERT INTO authors (slug, given_name, family_name, display_name, is_primary, email, website, affiliation_en, affiliation_fr, department_en, department_fr, bio_en, bio_fr, sort) VALUES (${a.slug}, ${a.given_name}, ${a.family_name}, ${a.display_name}, ${a.is_primary}, ${a.email ?? null}, ${a.website ?? null}, ${a.affiliation_en ?? null}, ${a.affiliation_fr ?? null}, ${a.department_en ?? null}, ${a.department_fr ?? null}, ${a.bio_en ?? null}, ${a.bio_fr ?? null}, ${a.sort}) ON CONFLICT (slug) DO UPDATE SET display_name = EXCLUDED.display_name, is_primary = EXCLUDED.is_primary, email = EXCLUDED.email, website = EXCLUDED.website, affiliation_en = EXCLUDED.affiliation_en, affiliation_fr = EXCLUDED.affiliation_fr, department_en = EXCLUDED.department_en, department_fr = EXCLUDED.department_fr, bio_en = EXCLUDED.bio_en, bio_fr = EXCLUDED.bio_fr RETURNING id`; authorId[a.slug] = r.id; authorByName[a.display_name] = r.id; }
// Topics (domains + topics)
const topicId: Record<string, number> = {};
let sort = 0;
for (const d of tx.domains) { const [r] = await sql`INSERT INTO topics (slug, name_en, name_fr, description_en, description_fr, kind, accent, sort) VALUES (${d.slug}, ${d.name_en}, ${d.name_fr}, ${d.description_en ?? null}, ${d.description_fr ?? null}, 'domain', ${d.accent ?? null}, ${sort++}) ON CONFLICT (slug) DO UPDATE SET name_en = EXCLUDED.name_en, name_fr = EXCLUDED.name_fr, description_en = EXCLUDED.description_en, description_fr = EXCLUDED.description_fr, kind = 'domain', accent = EXCLUDED.accent RETURNING id`; topicId[d.slug] = r.id; }
for (const t of tx.topics) { const [r] = await sql`INSERT INTO topics (slug, name_en, name_fr, kind, sort) VALUES (${t.slug}, ${t.name_en}, ${t.name_fr}, 'topic', ${sort++}) ON CONFLICT (slug) DO UPDATE SET name_en = EXCLUDED.name_en, name_fr = EXCLUDED.name_fr RETURNING id`; topicId[t.slug] = r.id; }
const methodId: Record<string, number> = {};
for (const m of tx.methods) { const [r] = await sql`INSERT INTO methods (slug, name_en, name_fr, category, sort) VALUES (${m.slug}, ${m.name_en}, ${m.name_fr}, ${m.category}, ${sort++}) ON CONFLICT (slug) DO UPDATE SET name_en = EXCLUDED.name_en, name_fr = EXCLUDED.name_fr, category = EXCLUDED.category RETURNING id`; methodId[m.slug] = r.id; }
const datasetId: Record<string, number> = {};
for (const d of tx.datasets) { const [r] = await sql`INSERT INTO datasets (slug, name_en, name_fr, provider, coverage, frequency, access_conditions, license, url, is_public, sort) VALUES (${d.slug}, ${d.name_en}, ${d.name_fr}, ${d.provider ?? null}, ${d.coverage ?? null}, ${d.frequency ?? null}, ${d.access_conditions ?? null}, ${d.license ?? null}, ${d.url ?? null}, ${d.is_public ?? false}, ${sort++}) ON CONFLICT (slug) DO UPDATE SET name_en = EXCLUDED.name_en, name_fr = EXCLUDED.name_fr, provider = EXCLUDED.provider, coverage = EXCLUDED.coverage, frequency = EXCLUDED.frequency, access_conditions = EXCLUDED.access_conditions, url = EXCLUDED.url, is_public = EXCLUDED.is_public RETURNING id`; datasetId[d.slug] = r.id; }
log(`taxonomy: ${tx.domains.length} domains, ${tx.topics.length} topics, ${tx.methods.length} methods, ${tx.datasets.length} datasets`);

let created = 0, updated = 0, withPdf = 0;
for (const p of catalog.papers) {
  const d = catalog.details.find((x: any) => x.slug === p.slug) ?? {};
  const code = p.kind === "firc" ? "FIRC" : "WP"; const idp = makeIdentifier(code, p.number);
  const released = monthDate(p.date); const ver = parseVersionLabel(p.version ?? "1") ?? { versionNo: 1, label: "v1" };
  const ssrnId = p.ssrn ? (/abstract=(\d+)/.exec(p.ssrn)?.[1] ?? null) : null;
  const artifacts: unknown[] = []; if (p.repo) artifacts.push({ kind: "repository", label: "Source repository (git.spboucher.ai)", url: p.repo });
  const links: unknown[] = []; if (p.ssrn) links.push({ kind: "ssrn", label: "SSRN", url: p.ssrn });
  const status = publish ? "published" : "draft";
  const notice = p.status ?? null;
  const [existing] = await sql`SELECT id, status FROM papers WHERE slug = ${idp.slug}`;
  const row = {
    series_id: seriesId[code], number: p.number, identifier: idp.identifier, slug: idp.slug, internal_code: p.repo ? p.repo.split("/").pop() : null,
    title: p.title, subtitle: p.subtitle ?? null, abstract: sql.json(d.abstract ?? [p.description]), research_question: null, notice,
    hero_headline: d.hero?.headline ?? null, hero_subheadline: d.hero?.subheadline ?? null,
    contributions: sql.json(d.contributions ?? []), methodology: sql.json(d.methodology ?? []), data_sections: sql.json(d.data ?? []), findings: sql.json(d.findings ?? []), reproducibility: sql.json(d.reproducibility ?? []),
    artifacts: sql.json(artifacts), keywords: d.keywords ?? [], jel: d.jel ? String(d.jel).split(/,\s*/).filter(Boolean) : [],
    ssrn_id: ssrnId, external_links: sql.json(links), year: p.year ?? null,
    first_released_on: released, first_released_precision: "month", last_revised_on: released, last_revised_precision: "month",
    primary_domain_id: topicId[tx.domain_of[p.slug]] ?? null, featured: tx.featured.includes(p.slug), featured_rank: tx.featured.includes(p.slug) ? tx.featured.indexOf(p.slug) + 1 : null,
  };
  let paperId: string;
  if (existing) { await sql`UPDATE papers SET ${sql(row as never)} WHERE id = ${existing.id}`; paperId = existing.id; updated++; }
  else { const [r] = await sql`INSERT INTO papers ${sql({ ...row, status, published_at: status === "published" ? new Date() : null } as never)} RETURNING id`; paperId = r.id; created++; }
  // Authors (order from the catalog)
  await sql`DELETE FROM paper_authors WHERE paper_id = ${paperId}`;
  let pos = 1; for (const name of p.authors ?? ["Simon-Pierre Boucher"]) { const aid = authorByName[name]; if (!aid) throw new Error(`unknown author ${name}`); await sql`INSERT INTO paper_authors (paper_id, author_id, position) VALUES (${paperId}, ${aid}, ${pos++})`; }
  // Topics: primary domain + secondary domains/topics + topic map
  const tset = new Set<number>(); tset.add(topicId[tx.domain_of[p.slug]]);
  for (const s of tx.secondary_domains[p.slug] ?? []) tset.add(topicId[s]);
  for (const [ts, arr] of Object.entries(tx.topics_of as Record<string, string[]>)) if (arr.includes(p.slug)) tset.add(topicId[ts]);
  await sql`DELETE FROM paper_topics WHERE paper_id = ${paperId} AND assigned_by = 'editor'`;
  for (const tid of tset) await sql`INSERT INTO paper_topics (paper_id, topic_id, assigned_by) VALUES (${paperId}, ${tid}, 'editor') ON CONFLICT DO NOTHING`;
  await sql`DELETE FROM paper_methods WHERE paper_id = ${paperId} AND assigned_by = 'editor'`;
  for (const [ms, arr] of Object.entries(tx.methods_of as Record<string, string[]>)) if (arr.includes(p.slug)) await sql`INSERT INTO paper_methods (paper_id, method_id, assigned_by) VALUES (${paperId}, ${methodId[ms]}, 'editor') ON CONFLICT DO NOTHING`;
  await sql`DELETE FROM paper_datasets WHERE paper_id = ${paperId} AND assigned_by = 'editor'`;
  for (const [ds, arr] of Object.entries(tx.datasets_of as Record<string, string[]>)) if (arr.includes(p.slug)) await sql`INSERT INTO paper_datasets (paper_id, dataset_id, assigned_by) VALUES (${paperId}, ${datasetId[ds]}, 'editor') ON CONFLICT DO NOTHING`;
  // Manuscript → file + version 1 (label = manuscript's own version number)
  const pdfPath = join(pdfDir, `${p.slug}_uqo.pdf`);
  if (existsSync(pdfPath)) {
    const sha = await sha256File(pdfPath); const st = statSync(pdfPath);
    let [f] = await sql`SELECT id FROM files WHERE sha256 = ${sha}`;
    if (!f) { const key = await storeFile(pdfPath, sha); [f] = await sql`INSERT INTO files (kind, storage_key, original_name, mime, bytes, sha256) VALUES ('manuscript', ${key}, ${`${p.slug}_uqo.pdf`}, 'application/pdf', ${st.size}, ${sha}) RETURNING id`; }
    const [v] = await sql`INSERT INTO paper_versions (paper_id, version_no, label, released_on, released_precision, description, description_fr, file_id, pages, sha256, bytes, is_current)
      VALUES (${paperId}, ${1}, ${ver.label}, ${released}, 'month', ${"Current manuscript as published on www.spboucher.ai (revised edition after the 2026-10-17 scientific review for papers 1–17)."}, ${"Manuscrit courant tel que publié sur www.spboucher.ai (édition révisée après la révision scientifique du 2026-10-17 pour les cahiers 1–17)."}, ${f.id}, ${p.pageCount ?? null}, ${sha}, ${st.size}, true)
      ON CONFLICT (paper_id, version_no) DO UPDATE SET label = EXCLUDED.label, file_id = EXCLUDED.file_id, pages = EXCLUDED.pages, sha256 = EXCLUDED.sha256, bytes = EXCLUDED.bytes, released_on = EXCLUDED.released_on, is_current = true RETURNING id`;
    await sql`UPDATE papers SET current_version_id = ${v.id} WHERE id = ${paperId}`;
    await enqueue("process_pdf", { fileId: f.id, paperId, versionId: v.id }, { dedupe: true });
    withPdf++;
  } else log(`! no manuscript for ${p.slug} (${pdfPath})`);
  await lexvecFromMetadata(paperId);
}
// Reserved identifiers (never public)
for (const r of tx.reserved) {
  const idp = makeIdentifier(r.code, r.number);
  await sql`INSERT INTO papers (series_id, number, identifier, slug, internal_code, title, status, notice) VALUES (${seriesId[r.code]}, ${r.number}, ${idp.identifier}, ${idp.slug}, ${r.internal_code}, ${r.title}, 'reserved', ${r.note})
    ON CONFLICT (slug) DO UPDATE SET title = EXCLUDED.title, internal_code = EXCLUDED.internal_code, notice = EXCLUDED.notice`;
}
await sql`INSERT INTO audit_log (actor_label, action, entity_type, entity_id, after) VALUES ('seed-import', ${publish ? "papers.seed_published" : "papers.seed_draft"}, 'catalog', 'content/seed/catalog.json', ${sql.json({ created, updated, withPdf, reserved: tx.reserved.length, note: publish ? "Records were already public on www.spboucher.ai; imported as published with owner approval given at deployment." : "Imported as drafts for review." })})`;
await enqueue("relations", {}, { dedupe: true });
log(`papers: ${created} created, ${updated} updated, ${withPdf} with manuscript, ${tx.reserved.length} reserved identifiers`);
await sql.end();