const Database = require("better-sqlite3");
const db = new Database("./data/worst-scan.db");

// Crear tablas de autores/series (misma migracion que src/lib/db.ts)
try {
  db.exec(`
    CREATE TABLE IF NOT EXISTS authors (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      slug TEXT UNIQUE NOT NULL,
      created_at TEXT DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS series (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      slug TEXT UNIQUE NOT NULL,
      description TEXT,
      cover_url TEXT,
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_authors_slug ON authors(slug);
    CREATE INDEX IF NOT EXISTS idx_series_slug ON series(slug);
    ALTER TABLE posts ADD COLUMN series_id INTEGER REFERENCES series(id);
  `);
  try { db.exec("CREATE INDEX IF NOT EXISTS idx_posts_series ON posts(series_id)"); } catch {}
} catch (e) { console.log("migracion:", e.message); }

// helper slugify (mismo que src/lib/slug.ts)
function slugify(...parts) {
  return parts
    .join("-")
    .toLowerCase()
    .normalize("NFD")
    .replace(/[\u0300-\u036f]/g, "")
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "")
    .slice(0, 80) || "sin-titulo";
}

// 1) Poblar autores desde los artist de posts publicados
const artists = db.prepare("SELECT DISTINCT artist FROM posts WHERE published=1 AND artist IS NOT NULL AND artist != '' AND artist != 'sin artista' AND artist != 'dummy'").all();
const upsertAuthor = db.prepare("INSERT OR IGNORE INTO authors(name, slug) VALUES (?, ?)");
let n = 0;
for (const { artist } of artists) {
  const r = upsertAuthor.run(artist, slugify(artist));
  if (r.changes > 0) n++;
}
console.log("Autores insertados:", n);

// 2) Verificar
const total = db.prepare("SELECT COUNT(*) c FROM authors").get();
console.log("Total autores en tabla:", total.c);

// 3) Verificar series (deberian ser 0 por ahora)
const series = db.prepare("SELECT COUNT(*) c FROM series").get();
console.log("Total series:", series.c);

db.close();