import { env } from "@/lib/runtime-env";
import type { GeneratorFont } from "@/lib/generator-font-types";
import { ensureSchema } from "./ensure-schema";

type FontRow = {
  id: number; slug: string; name: string; css_family: string; source_type: "builtin" | "uploaded";
  storage_key: string | null; original_file_name: string | null; content_type: string | null;
  sign_enabled: number; neon_enabled: number; handwritten: number; enabled: number;
  factor_bp: number; position: number;
};

const defaults = [
  ["grotesk", "Grotesk", "var(--font-syne), Arial, sans-serif", 1, 0, 0, 10000, 10],
  ["serif", "Élégante", "Georgia, serif", 1, 0, 0, 9500, 20],
  ["rounded", "Arrondie", "'Arial Rounded MT Bold', Arial, sans-serif", 1, 0, 0, 10400, 30],
  ["condensed", "Condensée", "Impact, Haettenschweiler, sans-serif", 1, 0, 0, 7800, 40],
  ["montserrat", "Montserrat", "var(--font-montserrat), Arial, sans-serif", 1, 0, 0, 9800, 50],
  ["bebas", "Bebas Neue", "var(--font-bebas), Impact, sans-serif", 1, 0, 0, 6600, 60],
  ["oswald", "Oswald", "var(--font-oswald), Arial, sans-serif", 1, 0, 0, 7200, 70],
  ["playfair", "Playfair Display", "var(--font-playfair), Georgia, serif", 1, 0, 0, 9400, 80],
  ["pacifico", "Pacifico", "'Pacifico', cursive", 1, 1, 1, 11800, 90],
  ["dancing", "Dancing", "'Dancing Script', cursive", 0, 1, 1, 10800, 100],
  ["allura", "Allura", "'Allura', cursive", 0, 1, 1, 10200, 110],
  ["sacramento", "Sacramento", "'Sacramento', cursive", 0, 1, 1, 9800, 120],
  ["satisfy", "Satisfy", "'Satisfy', cursive", 0, 1, 1, 10800, 130],
] as const;

let seeded: Promise<void> | null = null;

export async function ensureGeneratorFonts() {
  await ensureSchema();
  seeded ??= env.DB.batch(defaults.map(([slug, name, family, sign, neon, handwritten, factor, position]) =>
    env.DB.prepare(`INSERT OR IGNORE INTO generator_fonts
      (slug, name, css_family, source_type, sign_enabled, neon_enabled, handwritten, enabled,
       factor_bp, position, created_at, updated_at)
      VALUES (?, ?, ?, 'builtin', ?, ?, ?, 1, ?, ?, ?, ?)`)
      .bind(slug, name, family, sign, neon, handwritten, factor, position, Date.now(), Date.now()),
  )).then(() => undefined);
  await seeded;
}

export async function listGeneratorFonts(options: { generator?: "sign" | "neon"; includeDisabled?: boolean } = {}) {
  await ensureGeneratorFonts();
  const clauses: string[] = [];
  if (!options.includeDisabled) clauses.push("enabled = 1");
  if (options.generator === "sign") clauses.push("sign_enabled = 1");
  if (options.generator === "neon") clauses.push("neon_enabled = 1");
  const where = clauses.length ? ` WHERE ${clauses.join(" AND ")}` : "";
  const rows = await env.DB.prepare(`SELECT * FROM generator_fonts${where} ORDER BY position ASC, name ASC`).all<FontRow>();
  return rows.results.map(toPublicFont);
}

export async function findGeneratorFont(slug: string, generator?: "sign" | "neon", includeDisabled = false) {
  await ensureGeneratorFonts();
  const row = await env.DB.prepare(`SELECT * FROM generator_fonts WHERE slug = ?${includeDisabled ? "" : " AND enabled = 1"}${generator === "sign" ? " AND sign_enabled = 1" : generator === "neon" ? " AND neon_enabled = 1" : ""}`)
    .bind(slug).first<FontRow>();
  return row ? toPublicFont(row) : null;
}

export async function fontStorageKey(slug: string) {
  await ensureGeneratorFonts();
  const row = await env.DB.prepare("SELECT storage_key, content_type FROM generator_fonts WHERE slug = ?")
    .bind(slug).first<{ storage_key: string | null; content_type: string | null }>();
  return row?.storage_key ? { key: row.storage_key, contentType: row.content_type ?? "application/octet-stream" } : null;
}

function toPublicFont(row: FontRow): GeneratorFont {
  return {
    id: Number(row.id), slug: row.slug, name: row.name, cssFamily: row.css_family,
    sourceType: row.source_type, fileUrl: row.storage_key ? `/api/font-assets?slug=${encodeURIComponent(row.slug)}` : "",
    originalFileName: row.original_file_name ?? "", contentType: row.content_type ?? "",
    signEnabled: Boolean(row.sign_enabled), neonEnabled: Boolean(row.neon_enabled),
    handwritten: Boolean(row.handwritten), enabled: Boolean(row.enabled), factorBp: Number(row.factor_bp),
    position: Number(row.position),
  };
}