import fs from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { DatabaseSync } from "node:sqlite";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
export const APP_ROOT = path.resolve(__dirname, "..");
export const DATA_ROOT = path.resolve(process.env.SSB_ADMIN_DATA_ROOT || APP_ROOT);
const DEFAULT_SEEDED_DB_PATH = path.join(APP_ROOT, "data", "anime-fighter-pro-organizer.sqlite");
const SEEDED_DB_PATH = path.resolve(process.env.SSB_SEED_DB_PATH || DEFAULT_SEEDED_DB_PATH);
export const DB_PATH = path.join(DATA_ROOT, "data", "anime-fighter-pro-organizer.sqlite");

fs.mkdirSync(path.dirname(DB_PATH), { recursive: true });
if (!fs.existsSync(DB_PATH) && DB_PATH !== SEEDED_DB_PATH && fs.existsSync(SEEDED_DB_PATH)) {
  fs.copyFileSync(SEEDED_DB_PATH, DB_PATH);
}

const db = new DatabaseSync(DB_PATH);
db.exec("PRAGMA foreign_keys = ON;");

function hasColumn(tableName, columnName) {
  return db.prepare(`PRAGMA table_info(${tableName})`).all().some((row) => row.name === columnName);
}

function ensureColumn(tableName, columnName, definition) {
  if (!hasColumn(tableName, columnName)) {
    db.exec(`ALTER TABLE ${tableName} ADD COLUMN ${columnName} ${definition}`);
  }
}

db.exec(`
CREATE TABLE IF NOT EXISTS characters (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  slug TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  roster_group TEXT NOT NULL DEFAULT '',
  archetype TEXT NOT NULL DEFAULT '',
  franchise TEXT NOT NULL DEFAULT '',
  tags TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'concept',
  description TEXT NOT NULL DEFAULT '',
  mechanics_notes TEXT NOT NULL DEFAULT '',
  moveset_notes TEXT NOT NULL DEFAULT '',
  unlock_notes TEXT NOT NULL DEFAULT '',
  is_public INTEGER NOT NULL DEFAULT 1,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS stages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  slug TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  category TEXT NOT NULL DEFAULT '',
  legal_status TEXT NOT NULL DEFAULT '',
  tags TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL DEFAULT '',
  size_notes TEXT NOT NULL DEFAULT '',
  gimmick_notes TEXT NOT NULL DEFAULT '',
  music_notes TEXT NOT NULL DEFAULT '',
  is_public INTEGER NOT NULL DEFAULT 1,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS mechanics (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  slug TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  subsystem TEXT NOT NULL DEFAULT '',
  tuning_value TEXT NOT NULL DEFAULT '',
  target_file TEXT NOT NULL DEFAULT '',
  summary TEXT NOT NULL DEFAULT '',
  notes TEXT NOT NULL DEFAULT '',
  is_public INTEGER NOT NULL DEFAULT 1,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS assets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  entity_type TEXT NOT NULL,
  entity_slug TEXT NOT NULL,
  slot TEXT NOT NULL,
  label TEXT NOT NULL DEFAULT '',
  original_filename TEXT NOT NULL,
  stored_filename TEXT NOT NULL,
  relative_upload_path TEXT NOT NULL,
  mime_type TEXT NOT NULL DEFAULT '',
  source_type TEXT NOT NULL DEFAULT 'admin',
  is_public INTEGER NOT NULL DEFAULT 0,
  is_approved INTEGER NOT NULL DEFAULT 0,
  notes TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS submissions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  submitter_name TEXT NOT NULL DEFAULT '',
  submitter_contact TEXT NOT NULL DEFAULT '',
  entity_type TEXT NOT NULL DEFAULT 'character',
  entity_slug TEXT NOT NULL DEFAULT '',
  slot TEXT NOT NULL DEFAULT '',
  title TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  link_url TEXT NOT NULL DEFAULT '',
  file_relative_path TEXT NOT NULL DEFAULT '',
  file_original_name TEXT NOT NULL DEFAULT '',
  file_mime_type TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'pending',
  admin_notes TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS apply_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  summary_json TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS import_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  source_type TEXT NOT NULL DEFAULT 'discord_import',
  import_slug TEXT NOT NULL DEFAULT '',
  title TEXT NOT NULL DEFAULT '',
  source_guild_id TEXT NOT NULL DEFAULT '',
  source_channel_id TEXT NOT NULL DEFAULT '',
  source_url TEXT NOT NULL DEFAULT '',
  summary_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
`);

const characterColumns = [
  ["approval_status", "TEXT NOT NULL DEFAULT 'unapproved'"],
  ["review_status", "TEXT NOT NULL DEFAULT 'approved'"],
  ["release_bucket", "TEXT NOT NULL DEFAULT 'unassigned'"],
  ["source_type", "TEXT NOT NULL DEFAULT 'manual'"],
  ["source_guild_id", "TEXT NOT NULL DEFAULT ''"],
  ["source_channel_id", "TEXT NOT NULL DEFAULT ''"],
  ["source_thread_id", "TEXT NOT NULL DEFAULT ''"],
  ["source_message_id", "TEXT NOT NULL DEFAULT ''"],
  ["source_url", "TEXT NOT NULL DEFAULT ''"],
  ["import_run_id", "INTEGER NOT NULL DEFAULT 0"],
];
const assetColumns = [
  ["review_status", "TEXT NOT NULL DEFAULT 'approved'"],
  ["quality_bucket", "TEXT NOT NULL DEFAULT ''"],
  ["quality_score", "INTEGER NOT NULL DEFAULT 0"],
  ["is_primary", "INTEGER NOT NULL DEFAULT 0"],
  ["source_guild_id", "TEXT NOT NULL DEFAULT ''"],
  ["source_channel_id", "TEXT NOT NULL DEFAULT ''"],
  ["source_thread_id", "TEXT NOT NULL DEFAULT ''"],
  ["source_message_id", "TEXT NOT NULL DEFAULT ''"],
  ["source_attachment_id", "TEXT NOT NULL DEFAULT ''"],
  ["source_url", "TEXT NOT NULL DEFAULT ''"],
  ["import_run_id", "INTEGER NOT NULL DEFAULT 0"],
];

for (const [name, definition] of characterColumns) {
  ensureColumn("characters", name, definition);
}
for (const [name, definition] of assetColumns) {
  ensureColumn("assets", name, definition);
}

db.exec(`
CREATE UNIQUE INDEX IF NOT EXISTS idx_characters_source_thread_unique
ON characters(source_thread_id)
WHERE source_thread_id <> '';

CREATE UNIQUE INDEX IF NOT EXISTS idx_assets_source_attachment_unique
ON assets(source_attachment_id)
WHERE source_attachment_id <> '';

CREATE INDEX IF NOT EXISTS idx_characters_source_type
ON characters(source_type, review_status, sort_order);

CREATE INDEX IF NOT EXISTS idx_assets_source_type
ON assets(source_type, review_status, entity_slug, quality_bucket);

CREATE TABLE IF NOT EXISTS moveset_entries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  character_slug TEXT NOT NULL,
  section_key TEXT NOT NULL,
  section_label TEXT NOT NULL DEFAULT '',
  section_type TEXT NOT NULL DEFAULT 'attack',
  notes TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(character_slug, section_key)
);

CREATE TABLE IF NOT EXISTS moveset_media (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  character_slug TEXT NOT NULL,
  section_key TEXT NOT NULL,
  label TEXT NOT NULL DEFAULT '',
  original_filename TEXT NOT NULL,
  stored_filename TEXT NOT NULL,
  relative_upload_path TEXT NOT NULL,
  mime_type TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_moveset_entries_character
ON moveset_entries(character_slug, section_key);

CREATE INDEX IF NOT EXISTS idx_moveset_media_character
ON moveset_media(character_slug, section_key, id DESC);
`);

const slugify = (value) => {
  return String(value ?? "")
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "")
    .slice(0, 80);
};

const text = (value) => String(value ?? "").trim();
const asBoolInt = (value) => (value ? 1 : 0);
const asInt = (value, fallback = 0) => {
  const parsed = Number.parseInt(String(value ?? ""), 10);
  return Number.isFinite(parsed) ? parsed : fallback;
};

const selectAllCharacters = db.prepare(`
  SELECT * FROM characters
  ORDER BY sort_order ASC, display_name COLLATE NOCASE ASC, id ASC
`);
const selectAllStages = db.prepare(`
  SELECT * FROM stages
  ORDER BY sort_order ASC, display_name COLLATE NOCASE ASC, id ASC
`);
const selectAllMechanics = db.prepare(`
  SELECT * FROM mechanics
  ORDER BY sort_order ASC, subsystem COLLATE NOCASE ASC, display_name COLLATE NOCASE ASC, id ASC
`);
const selectAllAssets = db.prepare(`
  SELECT * FROM assets
  ORDER BY id DESC
`);
const selectAllSubmissions = db.prepare(`
  SELECT * FROM submissions
  ORDER BY
    CASE status
      WHEN 'pending' THEN 0
      WHEN 'needs_review' THEN 1
      WHEN 'approved' THEN 2
      ELSE 3
    END ASC,
    id DESC
`);
const selectApplyRuns = db.prepare(`
  SELECT * FROM apply_runs
  ORDER BY id DESC
  LIMIT ?
`);
const selectImportRuns = db.prepare(`
  SELECT * FROM import_runs
  ORDER BY id DESC
  LIMIT ?
`);

const getCharacterStatement = db.prepare(`SELECT * FROM characters WHERE slug = ?`);
const getCharacterBySourceThreadStatement = db.prepare(`SELECT * FROM characters WHERE source_thread_id = ?`);
const getStageStatement = db.prepare(`SELECT * FROM stages WHERE slug = ?`);
const getMechanicStatement = db.prepare(`SELECT * FROM mechanics WHERE slug = ?`);
const getAssetStatement = db.prepare(`SELECT * FROM assets WHERE id = ?`);
const getAssetBySourceAttachmentStatement = db.prepare(`SELECT * FROM assets WHERE source_attachment_id = ?`);
const getSubmissionStatement = db.prepare(`SELECT * FROM submissions WHERE id = ?`);
const getMovesetMediaStatement = db.prepare(`SELECT * FROM moveset_media WHERE id = ?`);

const countStatement = (tableName) => db.prepare(`SELECT COUNT(*) AS count FROM ${tableName}`);

const upsertCharacterStatement = db.prepare(`
  INSERT INTO characters (
    slug, display_name, roster_group, archetype, franchise, tags, status,
    description, mechanics_notes, moveset_notes, unlock_notes, is_public, sort_order,
    approval_status,
    release_bucket,
    review_status, source_type, source_guild_id, source_channel_id, source_thread_id,
    source_message_id, source_url, import_run_id, updated_at
  ) VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP
  )
  ON CONFLICT(slug) DO UPDATE SET
    display_name = excluded.display_name,
    roster_group = excluded.roster_group,
    archetype = excluded.archetype,
    franchise = excluded.franchise,
    tags = excluded.tags,
    status = excluded.status,
    description = excluded.description,
    mechanics_notes = excluded.mechanics_notes,
    moveset_notes = excluded.moveset_notes,
    unlock_notes = excluded.unlock_notes,
    is_public = excluded.is_public,
    sort_order = excluded.sort_order,
    approval_status = excluded.approval_status,
    release_bucket = excluded.release_bucket,
    review_status = excluded.review_status,
    source_type = excluded.source_type,
    source_guild_id = excluded.source_guild_id,
    source_channel_id = excluded.source_channel_id,
    source_thread_id = excluded.source_thread_id,
    source_message_id = excluded.source_message_id,
    source_url = excluded.source_url,
    import_run_id = excluded.import_run_id,
    updated_at = CURRENT_TIMESTAMP
`);

const upsertStageStatement = db.prepare(`
  INSERT INTO stages (
    slug, display_name, category, legal_status, tags, description, size_notes,
    gimmick_notes, music_notes, is_public, sort_order, updated_at
  ) VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP
  )
  ON CONFLICT(slug) DO UPDATE SET
    display_name = excluded.display_name,
    category = excluded.category,
    legal_status = excluded.legal_status,
    tags = excluded.tags,
    description = excluded.description,
    size_notes = excluded.size_notes,
    gimmick_notes = excluded.gimmick_notes,
    music_notes = excluded.music_notes,
    is_public = excluded.is_public,
    sort_order = excluded.sort_order,
    updated_at = CURRENT_TIMESTAMP
`);

const upsertMechanicStatement = db.prepare(`
  INSERT INTO mechanics (
    slug, display_name, subsystem, tuning_value, target_file, summary, notes, is_public, sort_order,
    updated_at
  ) VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP
  )
  ON CONFLICT(slug) DO UPDATE SET
    display_name = excluded.display_name,
    subsystem = excluded.subsystem,
    tuning_value = excluded.tuning_value,
    target_file = excluded.target_file,
    summary = excluded.summary,
    notes = excluded.notes,
    is_public = excluded.is_public,
    sort_order = excluded.sort_order,
    updated_at = CURRENT_TIMESTAMP
`);

const insertAssetStatement = db.prepare(`
  INSERT INTO assets (
    entity_type, entity_slug, slot, label, original_filename, stored_filename,
    relative_upload_path, mime_type, source_type, is_public, is_approved, notes,
    review_status, quality_bucket, quality_score, is_primary,
    source_guild_id, source_channel_id, source_thread_id, source_message_id,
    source_attachment_id, source_url, import_run_id, updated_at
  ) VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP
  )
`);

const updateAssetStatement = db.prepare(`
  UPDATE assets SET
    entity_type = ?,
    entity_slug = ?,
    slot = ?,
    label = ?,
    relative_upload_path = ?,
    mime_type = ?,
    is_public = ?,
    is_approved = ?,
    notes = ?,
    review_status = ?,
    quality_bucket = ?,
    quality_score = ?,
    is_primary = ?,
    source_type = ?,
    source_guild_id = ?,
    source_channel_id = ?,
    source_thread_id = ?,
    source_message_id = ?,
    source_attachment_id = ?,
    source_url = ?,
    import_run_id = ?,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = ?
`);

const insertSubmissionStatement = db.prepare(`
  INSERT INTO submissions (
    submitter_name, submitter_contact, entity_type, entity_slug, slot, title, description,
    link_url, file_relative_path, file_original_name, file_mime_type, status, admin_notes, updated_at
  ) VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP
  )
`);

const updateSubmissionStatement = db.prepare(`
  UPDATE submissions SET
    status = ?,
    admin_notes = ?,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = ?
`);

const updateCharacterReviewStatement = db.prepare(`
  UPDATE characters SET
    display_name = ?,
    roster_group = ?,
    description = ?,
    moveset_notes = ?,
    unlock_notes = ?,
    tags = ?,
    is_public = ?,
    approval_status = ?,
    review_status = ?,
    release_bucket = ?,
    updated_at = CURRENT_TIMESTAMP
  WHERE slug = ?
`);

const selectAllMovesetEntries = db.prepare(`
  SELECT * FROM moveset_entries
  ORDER BY character_slug COLLATE NOCASE ASC, section_key COLLATE NOCASE ASC, id ASC
`);
const selectAllMovesetMedia = db.prepare(`
  SELECT * FROM moveset_media
  ORDER BY character_slug COLLATE NOCASE ASC, section_key COLLATE NOCASE ASC, id DESC
`);
const upsertMovesetEntryStatement = db.prepare(`
  INSERT INTO moveset_entries (
    character_slug, section_key, section_label, section_type, notes, updated_at
  ) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
  ON CONFLICT(character_slug, section_key) DO UPDATE SET
    section_label = excluded.section_label,
    section_type = excluded.section_type,
    notes = excluded.notes,
    updated_at = CURRENT_TIMESTAMP
`);
const getMovesetEntryStatement = db.prepare(`
  SELECT * FROM moveset_entries
  WHERE character_slug = ? AND section_key = ?
`);
const insertMovesetMediaStatement = db.prepare(`
  INSERT INTO moveset_media (
    character_slug, section_key, label, original_filename, stored_filename,
    relative_upload_path, mime_type, updated_at
  ) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
`);
const deleteMovesetMediaStatement = db.prepare(`DELETE FROM moveset_media WHERE id = ?`);

const insertApplyRunStatement = db.prepare(`
  INSERT INTO apply_runs (summary_json) VALUES (?)
`);

const insertImportRunStatement = db.prepare(`
  INSERT INTO import_runs (
    source_type, import_slug, title, source_guild_id, source_channel_id, source_url, summary_json, updated_at
  ) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
`);
const updateImportRunStatement = db.prepare(`
  UPDATE import_runs SET
    source_type = ?,
    import_slug = ?,
    title = ?,
    source_guild_id = ?,
    source_channel_id = ?,
    source_url = ?,
    summary_json = ?,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = ?
`);

const deleteCharacterStatement = db.prepare(`DELETE FROM characters WHERE slug = ?`);
const deleteStageStatement = db.prepare(`DELETE FROM stages WHERE slug = ?`);
const deleteMechanicStatement = db.prepare(`DELETE FROM mechanics WHERE slug = ?`);
const deleteAssetStatement = db.prepare(`DELETE FROM assets WHERE id = ?`);
const deleteSubmissionStatement = db.prepare(`DELETE FROM submissions WHERE id = ?`);
const deleteCharacterAssetsStatement = db.prepare(`DELETE FROM assets WHERE entity_type = 'character' AND entity_slug = ?`);
const deleteCharacterMovesetEntriesStatement = db.prepare(`DELETE FROM moveset_entries WHERE character_slug = ?`);
const deleteCharacterMovesetMediaStatement = db.prepare(`DELETE FROM moveset_media WHERE character_slug = ?`);
const deleteCharacterSubmissionsStatement = db.prepare(`DELETE FROM submissions WHERE entity_type = 'character' AND entity_slug = ?`);

function normalizeCharacter(payload) {
  const slug = slugify(payload.slug || payload.display_name);
  if (!slug) {
    throw new Error("Character slug is required.");
  }
  const sourceType = text(payload.source_type || "manual") || "manual";
  const sourceThreadId = text(payload.source_thread_id);
  return {
    slug,
    display_name: text(payload.display_name || slug),
    roster_group: text(payload.roster_group),
    archetype: text(payload.archetype),
    franchise: text(payload.franchise),
    tags: text(payload.tags),
    status: text(payload.status || (sourceThreadId ? "discord-import" : "concept")),
    description: text(payload.description),
    mechanics_notes: text(payload.mechanics_notes),
    moveset_notes: text(payload.moveset_notes),
    unlock_notes: text(payload.unlock_notes),
    is_public: asBoolInt(payload.is_public),
    sort_order: asInt(payload.sort_order),
    approval_status: text(payload.approval_status || "unapproved"),
    release_bucket: text(payload.release_bucket || "unassigned"),
    review_status: text(payload.review_status || (sourceType === "discord_import" ? "pending_review" : "approved")),
    source_type: sourceType,
    source_guild_id: text(payload.source_guild_id),
    source_channel_id: text(payload.source_channel_id),
    source_thread_id: sourceThreadId,
    source_message_id: text(payload.source_message_id),
    source_url: text(payload.source_url),
    import_run_id: asInt(payload.import_run_id),
  };
}

function normalizeStage(payload) {
  const slug = slugify(payload.slug || payload.display_name);
  if (!slug) {
    throw new Error("Stage slug is required.");
  }
  return {
    slug,
    display_name: text(payload.display_name || slug),
    category: text(payload.category),
    legal_status: text(payload.legal_status),
    tags: text(payload.tags),
    description: text(payload.description),
    size_notes: text(payload.size_notes),
    gimmick_notes: text(payload.gimmick_notes),
    music_notes: text(payload.music_notes),
    is_public: asBoolInt(payload.is_public),
    sort_order: asInt(payload.sort_order),
  };
}

function normalizeMechanic(payload) {
  const slug = slugify(payload.slug || payload.display_name);
  if (!slug) {
    throw new Error("Mechanic slug is required.");
  }
  return {
    slug,
    display_name: text(payload.display_name || slug),
    subsystem: text(payload.subsystem),
    tuning_value: text(payload.tuning_value),
    target_file: text(payload.target_file),
    summary: text(payload.summary),
    notes: text(payload.notes),
    is_public: asBoolInt(payload.is_public),
    sort_order: asInt(payload.sort_order),
  };
}

function normalizeAsset(payload) {
  return {
    entity_type: text(payload.entity_type),
    entity_slug: slugify(payload.entity_slug),
    slot: text(payload.slot),
    label: text(payload.label),
    original_filename: text(payload.original_filename),
    stored_filename: text(payload.stored_filename),
    relative_upload_path: text(payload.relative_upload_path),
    mime_type: text(payload.mime_type),
    source_type: text(payload.source_type || "admin"),
    is_public: asBoolInt(payload.is_public),
    is_approved: asBoolInt(payload.is_approved),
    notes: text(payload.notes),
    review_status: text(payload.review_status || (payload.source_type === "discord_import" ? "pending_review" : "approved")),
    quality_bucket: text(payload.quality_bucket),
    quality_score: asInt(payload.quality_score),
    is_primary: asBoolInt(payload.is_primary),
    source_guild_id: text(payload.source_guild_id),
    source_channel_id: text(payload.source_channel_id),
    source_thread_id: text(payload.source_thread_id),
    source_message_id: text(payload.source_message_id),
    source_attachment_id: text(payload.source_attachment_id),
    source_url: text(payload.source_url),
    import_run_id: asInt(payload.import_run_id),
  };
}

function rowCounts() {
  return {
    characters: countStatement("characters").get().count,
    stages: countStatement("stages").get().count,
    mechanics: countStatement("mechanics").get().count,
    assets: countStatement("assets").get().count,
    submissions: countStatement("submissions").get().count,
    import_runs: countStatement("import_runs").get().count,
    imported_characters: db.prepare(`SELECT COUNT(*) AS count FROM characters WHERE source_type = 'discord_import'`).get().count,
    imported_assets: db.prepare(`SELECT COUNT(*) AS count FROM assets WHERE source_type = 'discord_import'`).get().count,
  };
}

function seedDefaults() {
  if (rowCounts().characters === 0) {
    upsertCharacter({
      slug: "orange-pill",
      display_name: "Orange Pill",
      roster_group: "Prototype",
      archetype: "Burst Brawler",
      franchise: "Anime Fighter Pro",
      tags: "starter,placeholder",
      status: "playable-prototype",
      description: "Current orange debug fighter used as a placeholder roster entry.",
      moveset_notes: "Uses the current prototype move set.",
      mechanics_notes: "Baseline tuning reference for the engine.",
      is_public: 1,
      sort_order: 1,
      approval_status: "unapproved",
      release_bucket: "OG",
      review_status: "approved",
      source_type: "manual",
    });
    upsertCharacter({
      slug: "blue-pill",
      display_name: "Blue Pill",
      roster_group: "Prototype",
      archetype: "Control Fighter",
      franchise: "Anime Fighter Pro",
      tags: "starter,placeholder",
      status: "playable-prototype",
      description: "Current blue debug fighter used as a placeholder roster entry.",
      moveset_notes: "Uses the current prototype move set.",
      mechanics_notes: "Baseline tuning reference for the engine.",
      is_public: 1,
      sort_order: 2,
      approval_status: "unapproved",
      release_bucket: "OG",
      review_status: "approved",
      source_type: "manual",
    });
  }

  if (rowCounts().stages === 0) {
    upsertStage({
      slug: "battlefield-prototype",
      display_name: "Battlefield Prototype",
      category: "Starter",
      legal_status: "testing",
      tags: "battlefield,three-platform",
      description: "Current three-platform stage prototype used for engine iteration.",
      size_notes: "Scaled-up battlefield style shell.",
      gimmick_notes: "No hazards. Soft platforms.",
      music_notes: "Menu tracks only right now.",
      is_public: 1,
      sort_order: 1,
    });
  }

  if (rowCounts().mechanics === 0) {
    upsertMechanic({
      slug: "damage-launch-scaling",
      display_name: "Damage Launch Scaling",
      subsystem: "Combat",
      tuning_value: "percent-driven",
      target_file: "scripts/fighter/fighter_25d.gd",
      summary: "Launch grows with damage and hit angle.",
      notes: "Keep this aligned with Smash-style knockback iteration.",
      is_public: 1,
      sort_order: 1,
    });
    upsertMechanic({
      slug: "ledge-snap-priority",
      display_name: "Ledge Snap Priority",
      subsystem: "Recovery",
      tuning_value: "last-grab-wins",
      target_file: "scripts/fighter/fighter_25d.gd",
      summary: "Only one fighter can hold ledge at a time.",
      notes: "Late grab ejects current holder downward.",
      is_public: 1,
      sort_order: 2,
    });
    upsertMechanic({
      slug: "triple-jump-window",
      display_name: "Triple Jump Window",
      subsystem: "Movement",
      tuning_value: "global",
      target_file: "scripts/fighter/fighter_25d.gd",
      summary: "Third jump available from ground or air, then aerial options are exhausted.",
      notes: "Tied into air-dodge exhaustion.",
      is_public: 1,
      sort_order: 3,
    });
  }
}

export function listCharacters() {
  return selectAllCharacters.all();
}

export function listPublishableCharacters() {
  return db.prepare(`
    SELECT * FROM characters
    WHERE source_type <> 'discord_import' OR review_status = 'approved'
    ORDER BY sort_order ASC, display_name COLLATE NOCASE ASC, id ASC
  `).all();
}

export function listStages() {
  return selectAllStages.all();
}

export function listMechanics() {
  return selectAllMechanics.all();
}

export function listAssets() {
  return selectAllAssets.all();
}

export function listSubmissions() {
  return selectAllSubmissions.all();
}

export function listMovesetEntries() {
  return selectAllMovesetEntries.all();
}

export function listMovesetMedia() {
  return selectAllMovesetMedia.all();
}

export function listApplyRuns(limit = 10) {
  return selectApplyRuns.all(limit);
}

export function listImportRuns(limit = 10) {
  return selectImportRuns.all(limit);
}

export function upsertCharacter(payload) {
  const row = normalizeCharacter(payload);
  upsertCharacterStatement.run(
    row.slug,
    row.display_name,
    row.roster_group,
    row.archetype,
    row.franchise,
    row.tags,
    row.status,
    row.description,
    row.mechanics_notes,
    row.moveset_notes,
    row.unlock_notes,
    row.is_public,
    row.sort_order,
    row.approval_status,
    row.release_bucket,
    row.review_status,
    row.source_type,
    row.source_guild_id,
    row.source_channel_id,
    row.source_thread_id,
    row.source_message_id,
    row.source_url,
    row.import_run_id
  );
  return getCharacterStatement.get(row.slug);
}

export function upsertImportedCharacter(payload) {
  const existing = payload.source_thread_id ? getCharacterBySourceThreadStatement.get(payload.source_thread_id) : null;
  const merged = {
    ...payload,
    slug: existing?.slug || payload.slug,
    display_name: existing?.display_name || payload.display_name,
    roster_group: existing?.roster_group || payload.roster_group,
    archetype: existing?.archetype || payload.archetype,
    franchise: existing?.franchise || payload.franchise,
    tags: existing?.tags || payload.tags,
    status: existing?.status || payload.status || "discord-import",
    description: payload.description || existing?.description || "",
    mechanics_notes: existing?.mechanics_notes || payload.mechanics_notes || "",
    moveset_notes: payload.moveset_notes || existing?.moveset_notes || "",
    unlock_notes: payload.unlock_notes || existing?.unlock_notes || "",
    is_public: existing ? existing.is_public : payload.is_public ?? 0,
    sort_order: existing?.sort_order ?? payload.sort_order,
    approval_status: existing?.approval_status || payload.approval_status || "unapproved",
    review_status: existing?.review_status || payload.review_status || "pending_review",
    source_type: "discord_import",
  };
  return upsertCharacter(merged);
}

export function upsertStage(payload) {
  const row = normalizeStage(payload);
  upsertStageStatement.run(
    row.slug,
    row.display_name,
    row.category,
    row.legal_status,
    row.tags,
    row.description,
    row.size_notes,
    row.gimmick_notes,
    row.music_notes,
    row.is_public,
    row.sort_order
  );
  return getStageStatement.get(row.slug);
}

export function upsertMechanic(payload) {
  const row = normalizeMechanic(payload);
  upsertMechanicStatement.run(
    row.slug,
    row.display_name,
    row.subsystem,
    row.tuning_value,
    row.target_file,
    row.summary,
    row.notes,
    row.is_public,
    row.sort_order
  );
  return getMechanicStatement.get(row.slug);
}

export function createAsset(payload) {
  const row = normalizeAsset(payload);
  const result = insertAssetStatement.run(
    row.entity_type,
    row.entity_slug,
    row.slot,
    row.label,
    row.original_filename,
    row.stored_filename,
    row.relative_upload_path,
    row.mime_type,
    row.source_type,
    row.is_public,
    row.is_approved,
    row.notes,
    row.review_status,
    row.quality_bucket,
    row.quality_score,
    row.is_primary,
    row.source_guild_id,
    row.source_channel_id,
    row.source_thread_id,
    row.source_message_id,
    row.source_attachment_id,
    row.source_url,
    row.import_run_id
  );
  return getAssetStatement.get(result.lastInsertRowid);
}

export function upsertImportedAsset(payload) {
  const existing = payload.source_attachment_id ? getAssetBySourceAttachmentStatement.get(String(payload.source_attachment_id)) : null;
  const row = normalizeAsset({
    ...payload,
    source_type: "discord_import",
    entity_type: payload.entity_type || existing?.entity_type || "character",
    entity_slug: existing?.entity_slug || payload.entity_slug,
    slot: existing?.slot || payload.slot || "",
    label: existing?.label || payload.label || "",
    is_public: existing ? existing.is_public : payload.is_public ?? 0,
    is_approved: existing ? existing.is_approved : payload.is_approved ?? 0,
    review_status: existing?.review_status || payload.review_status || "pending_review",
    quality_bucket: existing?.quality_bucket || payload.quality_bucket,
    quality_score: payload.quality_score ?? existing?.quality_score ?? 0,
    is_primary: existing ? existing.is_primary : payload.is_primary ?? 0,
    notes: existing?.notes || payload.notes || "",
  });

  if (existing) {
    updateAssetStatement.run(
      row.entity_type,
      row.entity_slug,
      row.slot,
      row.label,
      row.relative_upload_path,
      row.mime_type,
      row.is_public,
      row.is_approved,
      row.notes,
      row.review_status,
      row.quality_bucket,
      row.quality_score,
      row.is_primary,
      row.source_type,
      row.source_guild_id,
      row.source_channel_id,
      row.source_thread_id,
      row.source_message_id,
      row.source_attachment_id,
      row.source_url,
      row.import_run_id,
      existing.id
    );
    return getAssetStatement.get(existing.id);
  }

  return createAsset({
    ...payload,
    ...row,
    original_filename: payload.original_filename,
    stored_filename: payload.stored_filename,
  });
}

export function updateAssetReview(id, payload) {
  const existing = getAssetStatement.get(id);
  if (!existing) {
    throw new Error("Asset not found.");
  }

  const merged = normalizeAsset({
    ...existing,
    ...payload,
    original_filename: existing.original_filename,
    stored_filename: existing.stored_filename,
    source_type: existing.source_type,
  });

  updateAssetStatement.run(
    merged.entity_type,
    merged.entity_slug,
    merged.slot,
    merged.label,
    merged.relative_upload_path,
    merged.mime_type,
    merged.is_public,
    merged.is_approved,
    merged.notes,
    merged.review_status,
    merged.quality_bucket,
    merged.quality_score,
    merged.is_primary,
    merged.source_type,
    merged.source_guild_id,
    merged.source_channel_id,
    merged.source_thread_id,
    merged.source_message_id,
    merged.source_attachment_id,
    merged.source_url,
    merged.import_run_id,
    id
  );
  return getAssetStatement.get(id);
}

export function updateCharacterReview(slug, payload) {
  const existing = getCharacterStatement.get(slug);
  if (!existing) {
    throw new Error("Character not found.");
  }
  updateCharacterReviewStatement.run(
    text(payload.display_name || existing.display_name),
    text(payload.roster_group || existing.roster_group),
    text(payload.description ?? existing.description),
    text(payload.moveset_notes ?? existing.moveset_notes),
    text(payload.unlock_notes ?? existing.unlock_notes),
    text(payload.tags ?? existing.tags),
    asBoolInt(payload.is_public ?? existing.is_public),
    text(payload.approval_status || existing.approval_status || "unapproved"),
    text(payload.review_status || existing.review_status),
    text(payload.release_bucket || existing.release_bucket || "unassigned"),
    slug
  );
  return getCharacterStatement.get(slug);
}

export function upsertMovesetEntry(payload) {
  const characterSlug = slugify(payload.character_slug);
  const sectionKey = text(payload.section_key).toLowerCase();
  if (!characterSlug) {
    throw new Error("Character slug is required.");
  }
  if (!sectionKey) {
    throw new Error("Section key is required.");
  }
  upsertMovesetEntryStatement.run(
    characterSlug,
    sectionKey,
    text(payload.section_label || sectionKey),
    text(payload.section_type || "attack"),
    text(payload.notes)
  );
  return getMovesetEntryStatement.get(characterSlug, sectionKey);
}

export function createMovesetMedia(payload) {
  const characterSlug = slugify(payload.character_slug);
  const sectionKey = text(payload.section_key).toLowerCase();
  if (!characterSlug) {
    throw new Error("Character slug is required.");
  }
  if (!sectionKey) {
    throw new Error("Section key is required.");
  }
  const result = insertMovesetMediaStatement.run(
    characterSlug,
    sectionKey,
    text(payload.label),
    text(payload.original_filename),
    text(payload.stored_filename),
    text(payload.relative_upload_path),
    text(payload.mime_type)
  );
  return getMovesetMediaStatement.get(result.lastInsertRowid);
}

export function deleteMovesetMedia(id) {
  deleteMovesetMediaStatement.run(id);
}

export function deleteCharacter(slug) {
  deleteCharacterMovesetEntriesStatement.run(slug);
  deleteCharacterMovesetMediaStatement.run(slug);
  deleteCharacterAssetsStatement.run(slug);
  deleteCharacterSubmissionsStatement.run(slug);
  deleteCharacterStatement.run(slug);
}

export function deleteStage(slug) {
  deleteStageStatement.run(slug);
}

export function deleteMechanic(slug) {
  deleteMechanicStatement.run(slug);
}

export function deleteAsset(id) {
  deleteAssetStatement.run(id);
}

export function deleteSubmission(id) {
  deleteSubmissionStatement.run(id);
}

export function createSubmission(payload) {
  const result = insertSubmissionStatement.run(
    text(payload.submitter_name),
    text(payload.submitter_contact),
    text(payload.entity_type || "character"),
    slugify(payload.entity_slug),
    text(payload.slot),
    text(payload.title),
    text(payload.description),
    text(payload.link_url),
    text(payload.file_relative_path),
    text(payload.file_original_name),
    text(payload.file_mime_type),
    text(payload.status || "pending"),
    text(payload.admin_notes)
  );
  return getSubmissionStatement.get(result.lastInsertRowid);
}

export function updateSubmission(id, payload) {
  updateSubmissionStatement.run(
    text(payload.status || "pending"),
    text(payload.admin_notes),
    id
  );
  return getSubmissionStatement.get(id);
}

export function createApplyRun(summary) {
  const result = insertApplyRunStatement.run(JSON.stringify(summary, null, 2));
  return result.lastInsertRowid;
}

export function createImportRun(payload) {
  const result = insertImportRunStatement.run(
    text(payload.source_type || "discord_import"),
    text(payload.import_slug),
    text(payload.title),
    text(payload.source_guild_id),
    text(payload.source_channel_id),
    text(payload.source_url),
    JSON.stringify(payload.summary || {}, null, 2)
  );
  return db.prepare(`SELECT * FROM import_runs WHERE id = ?`).get(result.lastInsertRowid);
}

export function updateImportRun(id, payload) {
  updateImportRunStatement.run(
    text(payload.source_type || "discord_import"),
    text(payload.import_slug),
    text(payload.title),
    text(payload.source_guild_id),
    text(payload.source_channel_id),
    text(payload.source_url),
    JSON.stringify(payload.summary || {}, null, 2),
    id
  );
  return db.prepare(`SELECT * FROM import_runs WHERE id = ?`).get(id);
}

export function getAdminSnapshot() {
  return {
    counts: rowCounts(),
    characters: listCharacters(),
    stages: listStages(),
    mechanics: listMechanics(),
    assets: listAssets(),
    submissions: listSubmissions(),
    apply_runs: listApplyRuns(12),
    import_runs: listImportRuns(12),
  };
}

export function getPublicCatalog() {
  const characters = db.prepare(`
    SELECT * FROM characters
    WHERE is_public = 1 AND (source_type <> 'discord_import' OR review_status = 'approved')
    ORDER BY sort_order ASC, display_name COLLATE NOCASE ASC, id ASC
  `).all();
  const stages = db.prepare(`
    SELECT * FROM stages
    WHERE is_public = 1
    ORDER BY sort_order ASC, display_name COLLATE NOCASE ASC, id ASC
  `).all();
  const mechanics = db.prepare(`
    SELECT * FROM mechanics
    WHERE is_public = 1
    ORDER BY sort_order ASC, subsystem COLLATE NOCASE ASC, display_name COLLATE NOCASE ASC, id ASC
  `).all();
  const assets = db.prepare(`
    SELECT * FROM assets
    WHERE is_public = 1 AND is_approved = 1 AND (source_type <> 'discord_import' OR review_status = 'approved')
    ORDER BY id DESC
  `).all();

  return { characters, stages, mechanics, assets };
}

export function getPublicAssetById(id) {
  return db.prepare(`
    SELECT * FROM assets
    WHERE id = ? AND is_public = 1 AND is_approved = 1 AND (source_type <> 'discord_import' OR review_status = 'approved')
  `).get(id);
}

export function getAssetById(id) {
  return getAssetStatement.get(id);
}

export function getSubmissionById(id) {
  return getSubmissionStatement.get(id);
}

export function getMovesetMediaById(id) {
  return getMovesetMediaStatement.get(id);
}

export function getApprovedAssets() {
  return db.prepare(`
    SELECT * FROM assets
    WHERE is_approved = 1
      AND (source_type <> 'discord_import' OR (review_status = 'approved' AND slot <> ''))
    ORDER BY id DESC
  `).all();
}

seedDefaults();
