/** Deterministic content → database import. Idempotent: records are upserted by slug; relations are replaced; a revision is written only when the content hash changes. */
import { readFileSync, readdirSync, existsSync } from "node:fs";
import { join } from "node:path";
import { createHash } from "node:crypto";
import { eq, sql as dsql, inArray } from "drizzle-orm";
import { db, schema } from "@/db";
import { toolRecordSchema, collectionSchema, ownerRelevanceSchema, type ToolRecord } from "./schema";
import { normalizeUrl } from "@/lib/url";
import { qualityScore } from "@/lib/quality";
import { audit } from "@/lib/audit";
import { humanize } from "@/lib/text";

export type ImportOptions = { root: string; publish?: boolean; actor?: string; only?: string[]; dryRun?: boolean; /** rewrite rows even when the content hash is unchanged (no revision written) */ force?: boolean };
export type ImportReport = { categories: number; vendors: number; tools: { created: string[]; updated: string[]; unchanged: string[]; published: string[]; skipped: { slug: string; reason: string }[] }; collections: number; ownerNotes: number; projects: number };

const readJson = (p: string) => JSON.parse(readFileSync(p, "utf8"));
const listJson = (d: string) => (existsSync(d) ? readdirSync(d).filter((f) => f.endsWith(".json")).sort().map((f) => join(d, f)) : []);
export const contentHash = (r: ToolRecord) => createHash("sha256").update(JSON.stringify(r)).digest("hex");

export async function importCategories(root: string) {
  const cats = readJson(join(root, "categories.json")) as { slug: string; name: string; description: string; children?: any[] }[];
  let n = 0;
  for (const [i, c] of cats.entries()) {
    const [parent] = await db.insert(schema.categories).values({ slug: c.slug, name: c.name, description: c.description, sortOrder: i })
      .onConflictDoUpdate({ target: schema.categories.slug, set: { name: c.name, description: c.description, sortOrder: i, updatedAt: new Date() } }).returning({ id: schema.categories.id });
    n++;
    for (const [j, ch] of (c.children ?? []).entries()) {
      await db.insert(schema.categories).values({ slug: ch.slug, name: ch.name, description: ch.description, parentId: parent.id, sortOrder: j })
        .onConflictDoUpdate({ target: schema.categories.slug, set: { name: ch.name, description: ch.description, parentId: parent.id, sortOrder: j, updatedAt: new Date() } });
      n++;
    }
  }
  return n;
}

async function upsertVendor(v: ToolRecord["vendor"]) {
  const slug = v.slug ?? v.name.toLowerCase().normalize("NFKD").replace(/[̀-ͯ]/g, "").replace(/[^a-z0-9]+/g, "-").replace(/^-|-$/g, "");
  const [row] = await db.insert(schema.vendors).values({ slug, name: v.name, websiteUrl: v.website ?? null })
    .onConflictDoUpdate({ target: schema.vendors.slug, set: { name: v.name, websiteUrl: v.website ?? null, updatedAt: new Date() } }).returning({ id: schema.vendors.id });
  return row.id;
}
async function upsertTag(slug: string, kind: "tag" | "use_case") {
  const [row] = await db.insert(schema.tags).values({ slug, kind, name: humanize(slug) }).onConflictDoUpdate({ target: [schema.tags.slug, schema.tags.kind], set: { name: humanize(slug) } }).returning({ id: schema.tags.id });
  return row.id;
}
async function upsertSource(s: ToolRecord["sources"][number]) {
  const url = normalizeUrl(s.url);
  const [row] = await db.insert(schema.sources).values({ url, sourceType: s.type, title: s.title ?? null }).onConflictDoUpdate({ target: schema.sources.url, set: { sourceType: s.type, title: s.title ?? undefined, updatedAt: new Date() } }).returning({ id: schema.sources.id });
  return row.id;
}

export function toToolRow(r: ToolRecord, vendorId: string | null, hash: string) {
  const n = (u?: string) => (u ? normalizeUrl(u) : null);
  return {
    slug: r.slug, name: r.name, aliases: r.aliases, vendorId, homepageUrl: normalizeUrl(r.homepage_url), docsUrl: n(r.docs_url), repoUrl: n(r.repo_url), pricingUrl: n(r.pricing_url), licenseUrl: n(r.license_url), privacyUrl: n(r.privacy_url), securityUrl: n(r.security_url),
    logoUrl: n(r.logo_url), logoAttribution: r.logo_attribution ?? null, productType: r.product_type, productStatus: r.product_status, summary: r.summary, description: r.description,
    capabilities: r.capabilities, workflows: r.workflows, advantages: r.advantages, limitations: r.limitations, prerequisites: r.prerequisites, audiences: r.audiences, comparisonNotes: r.comparison_notes ?? null,
    operatingSystems: r.operating_systems, executionModes: r.execution_modes, interfaces: r.interfaces, deploymentModels: r.deployment_models, integrations: r.integrations, dataFormats: r.data_formats, languages: r.languages,
    sourceModel: r.source_model, licenseSpdx: r.license_spdx ?? null, pricingModel: r.pricing_model, pricingCheckedAt: r.pricing_checked_at ?? null, privacyPosture: r.privacy_posture ?? null, dataResidency: r.data_residency ?? null, selfHostable: r.self_hostable,
    confidence: r.confidence, uncertaintyNote: r.uncertainty_note ?? null, discoveryMethod: r.discovery_method, depth: r.depth, visibility: r.visibility, featured: r.featured, author: r.author,
    reviewedAt: r.reviewed_at ?? null, reviewedBy: r.reviewed_by ?? null, qualityScore: qualityScore(r), contentHash: hash,
    embeddingText: [r.name, r.summary, r.description, r.capabilities.join("; "), r.tags.join(" ")].join("\n"),
    keywords: [...r.tags, ...r.use_cases, ...r.categories.map((c) => c.slug), r.vendor.name, r.product_type, ...r.integrations].map((k) => k.replace(/-/g, " ")).join(" · "),
    updatedAt: new Date(),
  };
}

export async function importTools(opts: ImportOptions): Promise<ImportReport["tools"]> {
  const actor = opts.actor ?? "content-import";
  const files = [...listJson(join(opts.root, "tools")), ...listJson(join(opts.root, "private"))];
  const report: ImportReport["tools"] = { created: [], updated: [], unchanged: [], published: [], skipped: [] };
  const parsed: ToolRecord[] = [];
  for (const f of files) {
    const p = toolRecordSchema.safeParse(readJson(f));
    if (!p.success) { report.skipped.push({ slug: f, reason: p.error.issues.map((i) => `${i.path.join(".")}: ${i.message}`).join("; ") }); continue; }
    if (opts.only && !opts.only.includes(p.data.slug)) continue;
    parsed.push(p.data);
  }
  if (opts.dryRun) { for (const r of parsed) report.updated.push(r.slug); return report; }
  const catIds = new Map((await db.select({ id: schema.categories.id, slug: schema.categories.slug }).from(schema.categories)).map((c) => [c.slug, c.id]));
  const idBySlug = new Map<string, string>();
  // pass 1: tools + direct relations
  for (const r of parsed) {
    const hash = contentHash(r);
    const vendorId = await upsertVendor(r.vendor);
    const existing = await db.select({ id: schema.tools.id, contentHash: schema.tools.contentHash, editorialStatus: schema.tools.editorialStatus, publishedAt: schema.tools.publishedAt }).from(schema.tools).where(eq(schema.tools.slug, r.slug)).then((x) => x[0]);
    const row = toToolRow(r, vendorId, hash);
    let id: string;
    if (!existing) {
      const [ins] = await db.insert(schema.tools).values({ ...row, editorialStatus: r.editorial_status }).returning({ id: schema.tools.id });
      id = ins.id; report.created.push(r.slug);
      await db.insert(schema.toolRevisions).values({ toolId: id, actor, reason: "import:create", snapshot: r });
      await audit({ name: actor }, "tool.create", { type: "tool", id }, { slug: r.slug });
    } else {
      id = existing.id;
      if (existing.contentHash === hash && !opts.force) report.unchanged.push(r.slug);
      else if (existing.contentHash === hash) { await db.update(schema.tools).set({ ...row, editorialStatus: existing.editorialStatus, updatedAt: dsql`updated_at` }).where(eq(schema.tools.id, id)); report.unchanged.push(r.slug); }
      else {
        // Never demote a published record back to reviewed via import; keep the DB status unless the file explicitly deprecates it.
        const keepStatus = existing.editorialStatus === "published" && r.editorial_status !== "deprecated" ? "published" : r.editorial_status;
        await db.update(schema.tools).set({ ...row, editorialStatus: keepStatus }).where(eq(schema.tools.id, id));
        report.updated.push(r.slug);
        await db.insert(schema.toolRevisions).values({ toolId: id, actor, reason: "import:update", snapshot: r });
        await audit({ name: actor }, "tool.update", { type: "tool", id }, { slug: r.slug });
      }
    }
    idBySlug.set(r.slug, id);
    // categories
    await db.delete(schema.toolCategories).where(eq(schema.toolCategories.toolId, id));
    for (const c of r.categories) { const cid = catIds.get(c.slug); if (!cid) { report.skipped.push({ slug: r.slug, reason: `unknown category ${c.slug}` }); continue; } await db.insert(schema.toolCategories).values({ toolId: id, categoryId: cid, isPrimary: c.primary, label: c.label ?? null }).onConflictDoNothing(); }
    // tags / use cases
    await db.delete(schema.toolTags).where(eq(schema.toolTags.toolId, id));
    for (const t of r.tags) await db.insert(schema.toolTags).values({ toolId: id, tagId: await upsertTag(t, "tag") }).onConflictDoNothing();
    for (const t of r.use_cases) await db.insert(schema.toolTags).values({ toolId: id, tagId: await upsertTag(t, "use_case") }).onConflictDoNothing();
    // sources
    await db.delete(schema.toolSources).where(eq(schema.toolSources.toolId, id));
    for (const s of r.sources) await db.insert(schema.toolSources).values({ toolId: id, sourceId: await upsertSource(s), supports: s.supports, checkedAt: s.checked_at, note: s.note ?? null }).onConflictDoNothing();
  }
  // pass 2: alternatives (need all ids)
  const allIds = new Map((await db.select({ id: schema.tools.id, slug: schema.tools.slug }).from(schema.tools)).map((t) => [t.slug, t.id]));
  for (const r of parsed) {
    const id = idBySlug.get(r.slug)!;
    await db.delete(schema.toolAlternatives).where(eq(schema.toolAlternatives.toolId, id));
    for (const a of r.alternatives) { const aid = allIds.get(a); if (aid && aid !== id) await db.insert(schema.toolAlternatives).values({ toolId: id, alternativeId: aid }).onConflictDoNothing(); }
  }
  // publish gate: reviewed + public + validated → published
  if (opts.publish) {
    const slugs = parsed.filter((r) => r.visibility === "public" && r.editorial_status === "reviewed").map((r) => r.slug);
    if (slugs.length) {
      const rows = await db.update(schema.tools).set({ editorialStatus: "published", publishedAt: dsql`coalesce(published_at, now())`, reviewedAt: dsql`coalesce(reviewed_at, current_date)`, reviewedBy: dsql`coalesce(reviewed_by, ${actor})` })
        .where(dsql`${schema.tools.slug} IN ${slugs} AND ${schema.tools.editorialStatus} = 'reviewed'`).returning({ slug: schema.tools.slug, id: schema.tools.id });
      for (const t of rows) { report.published.push(t.slug); await audit({ name: actor }, "tool.publish", { type: "tool", id: t.id }, { slug: t.slug, via: "import --publish" }); }
    }
  }
  return report;
}

export async function importCollections(root: string) {
  const ids = new Map((await db.select({ id: schema.tools.id, slug: schema.tools.slug }).from(schema.tools)).map((t) => [t.slug, t.id]));
  let n = 0;
  for (const [i, f] of listJson(join(root, "collections")).entries()) {
    const c = collectionSchema.parse(readJson(f));
    const [row] = await db.insert(schema.collections).values({ slug: c.slug, title: c.title, description: c.description, published: c.published, sortOrder: i })
      .onConflictDoUpdate({ target: schema.collections.slug, set: { title: c.title, description: c.description, published: c.published, sortOrder: i, updatedAt: new Date() } }).returning({ id: schema.collections.id });
    await db.delete(schema.collectionItems).where(eq(schema.collectionItems.collectionId, row.id));
    for (const [pos, it] of c.items.entries()) { const tid = ids.get(it.tool); if (tid) await db.insert(schema.collectionItems).values({ collectionId: row.id, toolId: tid, position: pos, blurb: it.blurb ?? null }); }
    n++;
  }
  return n;
}

export async function importOwner(root: string) {
  const ids = new Map((await db.select({ id: schema.tools.id, slug: schema.tools.slug }).from(schema.tools)).map((t) => [t.slug, t.id]));
  let notes = 0, projects = 0;
  const pf = join(root, "owner", "projects.json");
  if (existsSync(pf)) for (const p of readJson(pf) as { slug: string; name: string; description?: string }[]) {
    await db.insert(schema.projects).values({ slug: p.slug, name: p.name, description: p.description ?? null }).onConflictDoUpdate({ target: schema.projects.slug, set: { name: p.name, description: p.description ?? null, updatedAt: new Date() } }); projects++;
  }
  const projIds = new Map((await db.select({ id: schema.projects.id, slug: schema.projects.slug }).from(schema.projects)).map((p) => [p.slug, p.id]));
  for (const f of listJson(join(root, "owner")).filter((f) => !f.endsWith("projects.json"))) {
    for (const raw of readJson(f)) {
      const o = ownerRelevanceSchema.parse(raw); const tid = ids.get(o.tool); if (!tid) continue;
      await db.insert(schema.ownerNotes).values({ toolId: tid, useState: o.use_state, rating: o.rating ?? null, notes: o.notes ?? null, clusterCompat: o.cluster_compat, priority: o.priority, subscriptionStatus: o.subscription_status ?? null, customTags: o.custom_tags, reviewAt: o.review_at ?? null })
        .onConflictDoUpdate({ target: schema.ownerNotes.toolId, set: { useState: o.use_state, rating: o.rating ?? null, notes: o.notes ?? null, clusterCompat: o.cluster_compat, priority: o.priority, subscriptionStatus: o.subscription_status ?? null, customTags: o.custom_tags, reviewAt: o.review_at ?? null, updatedAt: new Date() } });
      await db.delete(schema.projectTools).where(eq(schema.projectTools.toolId, tid));
      for (const ps of o.projects) { const pid = projIds.get(ps); if (pid) await db.insert(schema.projectTools).values({ projectId: pid, toolId: tid }).onConflictDoNothing(); }
      notes++;
    }
  }
  return { notes, projects };
}

export async function importAll(opts: ImportOptions): Promise<ImportReport> {
  const categories = await importCategories(opts.root);
  const tools = await importTools(opts);
  const collections = opts.dryRun ? 0 : await importCollections(opts.root);
  const owner = opts.dryRun ? { notes: 0, projects: 0 } : await importOwner(opts.root);
  const vendors = (await db.select({ n: dsql<number>`count(*)::int` }).from(schema.vendors))[0].n;
  return { categories, vendors, tools, collections, ownerNotes: owner.notes, projects: owner.projects };
}