/**
 * Generator: zet een content-model om in D1-SQL die nieuwe emdash-collecties aanmaakt
 * op een AL-geseede database, door de exacte tabelstructuur van emdash te spiegelen
 * (overgenomen uit de echte `ec_paginas`-DDL). Per collectie:
 *   - rij in _emdash_collections
 *   - CREATE TABLE ec_<slug> (systeemkolommen + veldkolommen)
 *   - rij in _emdash_fields per veld
 *   - content-rijen in ec_<slug>
 *
 * Bewust GEEN "search"/FTS (geen searchable velden) → geen FTS-tabellen nodig, dus
 * de gegenereerde SQL blijft veilig en compleet. Run: `node scripts/gen-cms-sql.mjs`.
 */
import { ulid } from "ulidx";
import { writeFileSync, mkdirSync } from "node:fs";
import { pathToFileURL } from "node:url";
import { resolve } from "node:path";

const TYPE_COL = {
  string: "TEXT", text: "TEXT", select: "TEXT", datetime: "TEXT", slug: "TEXT",
  image: "TEXT", file: "TEXT", reference: "TEXT",
  number: "REAL", integer: "INTEGER", boolean: "INTEGER",
  portableText: "JSON", json: "JSON", multiSelect: "JSON", repeater: "JSON",
};

// Systeemkolommen exact zoals emdash ze in ec_paginas aanmaakt.
const SYSTEM_COLS =
  `"id" text primary key, "slug" text, "status" text default 'draft', ` +
  `"author_id" text, "primary_byline_id" text, ` +
  `"created_at" text default (datetime('now')), "updated_at" text default (datetime('now')), ` +
  `"published_at" text, "scheduled_at" text, "deleted_at" text, "version" integer default 1, ` +
  `"live_revision_id" text references "revisions" ("id"), ` +
  `"draft_revision_id" text references "revisions" ("id"), ` +
  `"locale" text default 'en' not null, "translation_group" text`;

const q = (s) => "'" + String(s).replace(/'/g, "''") + "'";
function val(type, v) {
  if (v === null || v === undefined) return "NULL";
  const col = TYPE_COL[type] || "TEXT";
  if (col === "JSON") return q(JSON.stringify(v));
  if (col === "INTEGER") return type === "boolean" ? (v ? "1" : "0") : String(parseInt(v, 10));
  if (col === "REAL") return String(Number(v));
  if ((type === "image" || type === "file") && typeof v === "object") return q(JSON.stringify(v));
  return q(v);
}

export function generate(model) {
  const out = ["-- Auto-gegenereerd door scripts/gen-cms-sql.mjs — NIET met de hand bewerken."];
  for (const c of model) {
    const collId = ulid();
    const supports = JSON.stringify(c.supports ?? ["drafts", "revisions"]);
    out.push(`\n-- ===== collectie: ${c.slug} (${c.label}) =====`);
    out.push(
      `INSERT INTO _emdash_collections (id, slug, label, label_singular, supports, source, has_seo) ` +
      `VALUES (${q(collId)}, ${q(c.slug)}, ${q(c.label)}, ${q(c.labelSingular ?? c.label)}, ${q(supports)}, 'seed', 0);`
    );
    const fieldCols = c.fields
      .map((f) => {
        const col = TYPE_COL[f.type] || "TEXT";
        const nn = f.required && col === "TEXT" ? " NOT NULL DEFAULT ''" : "";
        return `"${f.slug}" ${col}${nn}`;
      })
      .join(", ");
    out.push(
      `CREATE TABLE "ec_${c.slug}" (${SYSTEM_COLS}, ${fieldCols}, ` +
      `constraint "ec_${c.slug}_slug_locale_unique" unique ("slug", "locale"));`
    );
    c.fields.forEach((f, i) => {
      const col = TYPE_COL[f.type] || "TEXT";
      // Repeater-velden krijgen hun rij-definitie (subFields) mee in de validation-kolom.
      const validation = f.subFields
        ? JSON.stringify({ subFields: f.subFields, minItems: f.minItems ?? 0, maxItems: f.maxItems ?? 20 })
        : null;
      const cols = "id, collection_id, slug, label, type, column_type, required, sort_order, searchable, translatable" + (validation ? ", validation" : "");
      const vals =
        `${q(ulid())}, ${q(collId)}, ${q(f.slug)}, ${q(f.label)}, ${q(f.type)}, ${q(col)}, ${f.required ? 1 : 0}, ${i}, 0, 1` +
        (validation ? `, ${q(validation)}` : "");
      out.push(`INSERT INTO _emdash_fields (${cols}) VALUES (${vals});`);
    });
    for (const entry of c.content ?? []) {
      const cols = [`"id"`, `"slug"`, `"status"`, `"locale"`];
      const vals = [q(ulid()), q(entry.slug), q(entry.status ?? "published"), q("en")];
      for (const f of c.fields) {
        if (entry.data && f.slug in entry.data) {
          cols.push(`"${f.slug}"`);
          vals.push(val(f.type, entry.data[f.slug]));
        }
      }
      out.push(`INSERT INTO "ec_${c.slug}" (${cols.join(", ")}) VALUES (${vals.join(", ")});`);
    }
  }
  return out.join("\n") + "\n";
}

// ---- Run: genereer per wave een .sql-bestand ----
// Gebruik: node scripts/gen-cms-sql.mjs <model-bestand> <output.sql>
// Alleen uitvoeren wanneer dit script direct wordt aangeroepen (niet bij import van `generate`).
const isMain = import.meta.url === pathToFileURL(process.argv[1] ?? "").href;
if (isMain) {
  const [, , modelPath, outName] = process.argv;
  if (!modelPath || !outName) {
    console.error("usage: node scripts/gen-cms-sql.mjs <model.mjs> <out.sql>");
    process.exit(1);
  }
  const mod = await import(pathToFileURL(resolve(modelPath)).href);
  const model = Object.values(mod).find((v) => Array.isArray(v));
  mkdirSync("migrations", { recursive: true });
  writeFileSync(`migrations/${outName}`, generate(model), "utf8");
  console.log("geschreven: migrations/" + outName);
}