import Database from "better-sqlite3";
import path from "path";

let db: Database.Database | null = null;

export function getDb(): Database.Database {
  if (!db) {
    const dbPath = process.env.DB_PATH ?? path.resolve("todo.db");
    db = new Database(dbPath);
    db.pragma("journal_mode = WAL");
    db.pragma("foreign_keys = ON");
    initSchema(db);
  }
  return db;
}

function initSchema(db: Database.Database) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id         TEXT PRIMARY KEY,
      username   TEXT UNIQUE NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS credentials (
      id         TEXT PRIMARY KEY,
      user_id    TEXT NOT NULL REFERENCES users(id),
      public_key TEXT NOT NULL,
      counter    INTEGER NOT NULL DEFAULT 0,
      transports TEXT
    );

    CREATE TABLE IF NOT EXISTS todos (
      id         TEXT PRIMARY KEY,
      user_id    TEXT NOT NULL REFERENCES users(id),
      title      TEXT NOT NULL,
      done       INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_todos_user ON todos(user_id, created_at DESC);
  `);
}

/* ── Users ── */

export interface User {
  id: string;
  username: string;
  created_at: string;
}

export function getUserById(id: string): User | null {
  return getDb().prepare("SELECT * FROM users WHERE id = ?").get(id) as User | null;
}

export function getUserByUsername(username: string): User | null {
  return getDb().prepare("SELECT * FROM users WHERE username = ?").get(username) as User | null;
}

export function upsertUser(id: string, username: string): User {
  getDb()
    .prepare("INSERT OR IGNORE INTO users (id, username) VALUES (?, ?)")
    .run(id, username);
  return getUserById(id) ?? getUserByUsername(username)!;
}

/* ── Credentials ── */

export interface Credential {
  id: string;
  user_id: string;
  public_key: string;
  counter: number;
  transports: string | null;
}

export function hasAnyCredential(): boolean {
  return (getDb().prepare("SELECT 1 FROM credentials LIMIT 1").get() as unknown) != null;
}

export function listCredentials(): Credential[] {
  return getDb().prepare("SELECT * FROM credentials").all() as Credential[];
}

export function getCredentialById(id: string): Credential | null {
  return getDb().prepare("SELECT * FROM credentials WHERE id = ?").get(id) as Credential | null;
}

export function saveCredential(input: {
  id: string;
  userId: string;
  publicKey: Uint8Array;
  counter: number;
  transports?: string[];
}): void {
  getDb()
    .prepare(
      "INSERT INTO credentials (id, user_id, public_key, counter, transports) VALUES (?, ?, ?, ?, ?)"
    )
    .run(
      input.id,
      input.userId,
      Buffer.from(input.publicKey).toString("base64url"),
      input.counter,
      input.transports ? JSON.stringify(input.transports) : null
    );
}

export function updateCredentialCounter(id: string, counter: number): void {
  getDb().prepare("UPDATE credentials SET counter = ? WHERE id = ?").run(counter, id);
}

/* ── Todos ── */

export interface Todo {
  id: string;
  user_id: string;
  title: string;
  done: number;
  created_at: string;
}

export function listTodos(userId: string, filter?: string): Todo[] {
  if (filter === "active") {
    return getDb()
      .prepare("SELECT * FROM todos WHERE user_id = ? AND done = 0 ORDER BY created_at DESC")
      .all(userId) as Todo[];
  }
  if (filter === "done") {
    return getDb()
      .prepare("SELECT * FROM todos WHERE user_id = ? AND done = 1 ORDER BY created_at DESC")
      .all(userId) as Todo[];
  }
  return getDb()
    .prepare("SELECT * FROM todos WHERE user_id = ? ORDER BY created_at DESC")
    .all(userId) as Todo[];
}

export function createTodo(userId: string, title: string): void {
  getDb()
    .prepare("INSERT INTO todos (id, user_id, title) VALUES (?, ?, ?)")
    .run(crypto.randomUUID(), userId, title);
}

export function toggleTodo(id: string, userId: string, done: boolean): void {
  getDb()
    .prepare("UPDATE todos SET done = ? WHERE id = ? AND user_id = ?")
    .run(done ? 1 : 0, id, userId);
}

export function deleteTodo(id: string, userId: string): void {
  getDb().prepare("DELETE FROM todos WHERE id = ? AND user_id = ?").run(id, userId);
}

export function countActiveTodos(userId: string): number {
  const row = getDb()
    .prepare("SELECT COUNT(*) as n FROM todos WHERE user_id = ? AND done = 0")
    .get(userId) as { n: number };
  return row.n;
}