import { mkdirSync } from "node:fs";
import { dirname, join } from "node:path";
import createSqlite from "better-sqlite3";
import type Database from "better-sqlite3";
import { randomUUID } from "node:crypto";
import { hashPassword } from "@/lib/auth/password";
import { runMigrations } from "@/lib/db/migrate";

const SqliteDatabase =
  (createSqlite as unknown as { default?: typeof createSqlite }).default ?? createSqlite;

const ADMIN_EMAIL = "alexsey.websites.work@gmail.com";
const ADMIN_PASSWORD = "System101";

export type AppDatabase = Database.Database;

let db: AppDatabase | undefined;

function dbPath() {
  return process.env.DATABASE_PATH || join(process.cwd(), "data", "app.db");
}

function initSchema(database: AppDatabase) {
  database.exec(`
    PRAGMA foreign_keys = ON;

    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      email TEXT NOT NULL UNIQUE COLLATE NOCASE,
      password_hash TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS user_roles (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      role TEXT NOT NULL CHECK (role IN ('admin', 'user')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      UNIQUE (user_id, role)
    );

    CREATE TABLE IF NOT EXISTS categories (
      id TEXT PRIMARY KEY,
      slug TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL,
      description TEXT,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS games (
      id TEXT PRIMARY KEY,
      slug TEXT NOT NULL UNIQUE,
      title TEXT NOT NULL,
      description TEXT,
      instructions TEXT,
      thumb_url TEXT NOT NULL,
      embed_url TEXT NOT NULL,
      width INTEGER NOT NULL DEFAULT 800,
      height INTEGER NOT NULL DEFAULT 600,
      category_id TEXT REFERENCES categories(id) ON DELETE SET NULL,
      source_id TEXT UNIQUE,
      source TEXT,
      is_featured INTEGER NOT NULL DEFAULT 0,
      plays INTEGER NOT NULL DEFAULT 0,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE INDEX IF NOT EXISTS games_category_idx ON games(category_id);
    CREATE INDEX IF NOT EXISTS games_featured_idx ON games(is_featured) WHERE is_featured = 1;
  `);
}

function seedDefaults(database: AppDatabase) {
  const categoryCount = database.prepare("SELECT COUNT(*) AS c FROM categories").get() as { c: number };
  if (categoryCount.c === 0) {
    const insert = database.prepare(
      "INSERT INTO categories (id, slug, name, sort_order) VALUES (?, ?, ?, ?)",
    );
    const categories = [
      ["arcade", "Аркады", 10],
      ["racing", "Гонки", 20],
      ["shooter", "Стрелялки", 30],
      ["puzzle", "Головоломки", 40],
      ["action", "Экшен", 50],
      ["sports", "Спорт", 60],
      ["girls", "Для девочек", 70],
      ["io", "IO игры", 80],
      ["strategy", "Стратегии", 90],
      ["adventure", "Приключения", 100],
    ] as const;
    for (const [slug, name, sortOrder] of categories) {
      insert.run(randomUUID(), slug, name, sortOrder);
    }
  }

  const adminCount = database.prepare("SELECT COUNT(*) AS c FROM user_roles WHERE role = 'admin'").get() as {
    c: number;
  };
  if (adminCount.c === 0) {
    const existing = database
      .prepare("SELECT id FROM users WHERE email = ? COLLATE NOCASE")
      .get(ADMIN_EMAIL) as { id: string } | undefined;

    const userId = existing?.id ?? randomUUID();
    if (!existing) {
      database
        .prepare("INSERT INTO users (id, email, password_hash) VALUES (?, ?, ?)")
        .run(userId, ADMIN_EMAIL, hashPassword(ADMIN_PASSWORD));
    }
    database
      .prepare("INSERT OR IGNORE INTO user_roles (id, user_id, role) VALUES (?, ?, 'admin')")
      .run(randomUUID(), userId);
  }
}

export function getDb(): AppDatabase {
  if (db) return db;

  const path = dbPath();
  try {
    mkdirSync(dirname(path), { recursive: true });
    db = new SqliteDatabase(path);
    db.pragma("foreign_keys = ON");
    initSchema(db);
    runMigrations(db);
    seedDefaults(db);
    return db;
  } catch (error) {
    const hint =
      error instanceof Error && /bindings|better_sqlite3|MODULE_NOT_FOUND/i.test(error.message)
        ? " Запустите: apt install -y build-essential python3 && npm install && npm rebuild better-sqlite3"
        : "";
    throw new Error(`Не удалось открыть SQLite (${path}): ${error instanceof Error ? error.message : error}.${hint}`);
  }
}

export type DbUser = {
  id: string;
  email: string;
  password_hash: string;
  created_at: string;
};

export type DbCategory = {
  id: string;
  slug: string;
  name: string;
  description: string | null;
  sort_order: number;
  created_at: string;
  updated_at: string;
};

export type DbGame = {
  id: string;
  slug: string;
  title: string;
  description: string | null;
  instructions: string | null;
  thumb_url: string;
  embed_url: string;
  width: number;
  height: number;
  category_id: string | null;
  source_id: string | null;
  source: string | null;
  is_featured: number;
  plays: number;
  sort_order: number;
  created_at: string;
  updated_at: string;
  category_slug?: string | null;
  category_name?: string | null;
};

export function mapGameRow(row: DbGame) {
  return {
    id: row.id,
    slug: row.slug,
    title: row.title,
    description: row.description,
    instructions: row.instructions,
    thumb_url: row.thumb_url,
    embed_url: row.embed_url,
    width: row.width,
    height: row.height,
    category_id: row.category_id,
    source_id: row.source_id,
    source: row.source,
    is_featured: Boolean(row.is_featured),
    plays: row.plays,
    sort_order: row.sort_order,
    created_at: row.created_at,
    updated_at: row.updated_at,
    categories:
      row.category_slug && row.category_name
        ? { slug: row.category_slug, name: row.category_name }
        : null,
  };
}