import { type Client, createClient } from "@tursodatabase/serverless/compat";
import { load } from "@std/dotenv";

await load({ export: true });

type Db = Pick<Client, "execute" | "executeMultiple" | "transaction">;

let client: Db | undefined;

async function getClient(): Promise<Db> {
  client ??= await createDb();
  return client;
}

async function createDb(): Promise<Db> {
  const url = Deno.env.get("TURSO_DATABASE_URL") ?? "file:./profectus.db";
  const authToken = Deno.env.get("TURSO_AUTH_TOKEN");
  if (url.startsWith("file:")) {
    const { createClient } = await import("@libsql/client");
    return createClient({ url });
  }
  return createClient({ url, authToken });
}

let initialized = false;

export async function initDb(): Promise<void> {
  if (initialized) return;
  const db = await getClient();
  await db.executeMultiple(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      email TEXT NOT NULL UNIQUE COLLATE NOCASE,
      name TEXT,
      password_hash TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS sessions (
      token TEXT PRIMARY KEY,
      user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      expires_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS heroes (
      user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
      name TEXT NOT NULL,
      class TEXT NOT NULL CHECK (class IN ('maiden', 'witch', 'assassin')),
      level INTEGER NOT NULL DEFAULT 1,
      anima INTEGER NOT NULL DEFAULT 40,
      progression TEXT NOT NULL DEFAULT '{}',
      deck TEXT NOT NULL DEFAULT '[]'
    );
    CREATE TABLE IF NOT EXISTS card_collection (
      user_id INTEGER NOT NULL REFERENCES heroes(user_id) ON DELETE CASCADE,
      card_id TEXT NOT NULL,
      quantity INTEGER NOT NULL DEFAULT 0 CHECK (quantity >= 0),
      PRIMARY KEY (user_id, card_id)
    );
  `);
  const heroColumns = await db.execute("PRAGMA table_info(heroes)");
  if (!heroColumns.rows.some((row) => String(row.name) === "progression")) {
    await db.execute(
      "ALTER TABLE heroes ADD COLUMN progression TEXT NOT NULL DEFAULT '{}'",
    );
  }
  const adminEmail = Deno.env.get("ADMIN_EMAIL")?.toLowerCase() ??
    "silen.locatelli@gmx.ch";
  await db.execute({
    sql: "UPDATE users SET role = 'admin' WHERE email = ?",
    args: [adminEmail],
  });
  initialized = true;
}

export async function query(sql: string, args?: (string | number | null)[]) {
  return (await getClient()).execute({ sql, args });
}

export async function transaction() {
  return await (await getClient()).transaction("write");
}