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("microblog.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 posts (
      id         TEXT PRIMARY KEY,
      content    TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at DESC);

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

/* ── Posts ── */

export interface Post {
  id: string;
  content: string;
  created_at: string;
}

export interface CreatePostInput {
  id: string;
  content: string;
}

export function listPosts(): Post[] {
  return getDb().prepare("SELECT * FROM posts ORDER BY created_at DESC").all() as Post[];
}

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

export function createPost(input: CreatePostInput): Post {
  getDb().prepare("INSERT INTO posts (id, content) VALUES (?, ?)").run(input.id, input.content);
  return getPostById(input.id)!;
}

export function deletePost(id: string): void {
  getDb().prepare("DELETE FROM posts WHERE id = ?").run(id);
}

/* ── Credentials (passkey) ── */

export interface Credential {
  id: string;
  public_key: string;
  counter: number;
  transports: string | 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;
  publicKey: Uint8Array;
  counter: number;
  transports?: string[];
}): void {
  getDb()
    .prepare(
      "INSERT INTO credentials (id, public_key, counter, transports) VALUES (?, ?, ?, ?)"
    )
    .run(
      input.id,
      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);
}