path: root/web/ui/src/lib/server/db.ts

import Database from 'better-sqlite3';
import { mkdirSync } from 'node:fs';
import { dirname, resolve } from 'node:path';
import { randomUUID } from 'node:crypto';
import { env } from '$env/dynamic/private';

import type { PlanId } from '$lib/plans';

const DB_PATH = resolve(env.DATABASE_PATH || './data/dashboard.db');

// Make sure the data/ directory exists before better-sqlite3 tries to open.
mkdirSync(dirname(DB_PATH), { recursive: true });

export const db = new Database(DB_PATH);
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

// -------- schema --------

db.exec(`
  CREATE TABLE IF NOT EXISTS accounts (
    id            TEXT PRIMARY KEY,
    email         TEXT UNIQUE,
    pending_email TEXT,
    plan          TEXT NOT NULL DEFAULT 'free',
    created_at    INTEGER NOT NULL
  );

  CREATE TABLE IF NOT EXISTS api_keys (
    id            TEXT PRIMARY KEY,
    account_id    TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    key_hash      TEXT NOT NULL UNIQUE,
    key_prefix    TEXT NOT NULL,
    name          TEXT NOT NULL,
    scopes        TEXT NOT NULL DEFAULT '["*"]',
    active        INTEGER NOT NULL DEFAULT 1,
    created_at    INTEGER NOT NULL,
    last_used_at  INTEGER
  );
  CREATE INDEX IF NOT EXISTS idx_api_keys_account ON api_keys(account_id);

  CREATE TABLE IF NOT EXISTS magic_links (
    id          TEXT PRIMARY KEY,
    account_id  TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    token       TEXT NOT NULL UNIQUE,
    expires_at  INTEGER NOT NULL,
    used        INTEGER NOT NULL DEFAULT 0,
    created_at  INTEGER NOT NULL
  );
  CREATE INDEX IF NOT EXISTS idx_magic_links_token ON magic_links(token);

  CREATE TABLE IF NOT EXISTS usage_events (
    id          TEXT PRIMARY KEY,
    api_key_id  TEXT NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    dataset     TEXT NOT NULL,
    timestamp   INTEGER NOT NULL
  );
  CREATE INDEX IF NOT EXISTS idx_usage_key       ON usage_events(api_key_id);
  CREATE INDEX IF NOT EXISTS idx_usage_timestamp ON usage_events(timestamp);
  CREATE INDEX IF NOT EXISTS idx_usage_dataset   ON usage_events(dataset);
`);

// Idempotent migration: pre-existing dashboard.db files won't have
// pending_email yet. SQLite has no IF NOT EXISTS for ALTER, so check
// PRAGMA table_info first.
{
  const cols = db
    .prepare(`PRAGMA table_info(accounts)`)
    .all() as Array<{ name: string }>;
  if (!cols.some((c) => c.name === 'pending_email')) {
    db.exec(`ALTER TABLE accounts ADD COLUMN pending_email TEXT`);
  }
}

// -------- types --------

export interface Account {
  id: string;
  email: string | null;
  /** Email the user is mid-verifying. Cleared on completion or cancel. */
  pending_email: string | null;
  plan: PlanId;
  created_at: number;
}

export interface ApiKeyRow {
  id: string;
  account_id: string;
  key_hash: string;
  key_prefix: string;
  name: string;
  scopes: string;             // JSON string on disk
  active: number;             // 0 / 1
  created_at: number;
  last_used_at: number | null;
}

export interface ApiKey {
  id: string;
  account_id: string;
  key_prefix: string;
  name: string;
  scopes: string[];           // decoded
  active: boolean;
  created_at: number;
  last_used_at: number | null;
}

export function rowToKey(row: ApiKeyRow): ApiKey {
  return {
    id: row.id,
    account_id: row.account_id,
    key_prefix: row.key_prefix,
    name: row.name,
    scopes: JSON.parse(row.scopes),
    active: row.active === 1,
    created_at: row.created_at,
    last_used_at: row.last_used_at
  };
}

// -------- helpers --------

export function now(): number {
  return Date.now();
}

export function newId(): string {
  return randomUUID();
}

// -------- prepared statements --------

const stmts = {
  accountById: db.prepare<string, Account>(
    `SELECT id, email, pending_email, plan, created_at FROM accounts WHERE id = ?`
  ),
  accountByEmail: db.prepare<string, Account>(
    `SELECT id, email, pending_email, plan, created_at FROM accounts WHERE email = ?`
  ),
  insertAccount: db.prepare(
    `INSERT INTO accounts (id, email, plan, created_at) VALUES (?, ?, ?, ?)`
  ),
  updateAccountEmail: db.prepare(
    `UPDATE accounts SET email = ? WHERE id = ?`
  ),
  setPendingEmail: db.prepare(
    `UPDATE accounts SET pending_email = ? WHERE id = ?`
  ),
  clearPendingEmail: db.prepare(
    `UPDATE accounts SET pending_email = NULL WHERE id = ?`
  ),
  promotePendingEmail: db.prepare(
    `UPDATE accounts SET email = pending_email, pending_email = NULL WHERE id = ?`
  ),
  updateAccountPlan: db.prepare(
    `UPDATE accounts SET plan = ? WHERE id = ?`
  ),
  deleteAccount: db.prepare(
    `DELETE FROM accounts WHERE id = ?`
  ),
  reassignKeys: db.prepare(
    `UPDATE api_keys SET account_id = ? WHERE account_id = ?`
  ),
  reassignUsage: db.prepare(
    `UPDATE usage_events SET api_key_id = api_key_id WHERE api_key_id IN
      (SELECT id FROM api_keys WHERE account_id = ?)`
  ),
  keysForAccount: db.prepare<string, ApiKeyRow>(
    `SELECT * FROM api_keys WHERE account_id = ? ORDER BY created_at DESC`
  ),
  keyById: db.prepare<[string, string], ApiKeyRow>(
    `SELECT * FROM api_keys WHERE id = ? AND account_id = ?`
  ),
  insertKey: db.prepare(
    `INSERT INTO api_keys
      (id, account_id, key_hash, key_prefix, name, scopes, active, created_at)
     VALUES (?, ?, ?, ?, ?, ?, 1, ?)`
  ),
  revokeKey: db.prepare(
    `UPDATE api_keys SET active = 0 WHERE id = ? AND account_id = ?`
  ),
  countActiveKeys: db.prepare<string, { c: number }>(
    `SELECT COUNT(*) AS c FROM api_keys WHERE account_id = ? AND active = 1`
  ),
  insertMagicLink: db.prepare(
    `INSERT INTO magic_links (id, account_id, token, expires_at, used, created_at)
     VALUES (?, ?, ?, ?, 0, ?)`
  ),
  magicLinkByToken: db.prepare<
    string,
    { id: string; account_id: string; expires_at: number; used: number }
  >(
    `SELECT id, account_id, expires_at, used FROM magic_links WHERE token = ?`
  ),
  markMagicLinkUsed: db.prepare(
    `UPDATE magic_links SET used = 1 WHERE id = ?`
  ),
  insertUsageEvent: db.prepare(
    `INSERT INTO usage_events (id, api_key_id, dataset, timestamp)
     VALUES (?, ?, ?, ?)`
  ),
  usageCountSince: db.prepare<[string, number], { c: number }>(
    `SELECT COUNT(*) AS c FROM usage_events e
      JOIN api_keys k ON k.id = e.api_key_id
     WHERE k.account_id = ? AND e.timestamp >= ?`
  ),
  usageByDataset: db.prepare<
    [string, number],
    { dataset: string; c: number }
  >(
    `SELECT e.dataset AS dataset, COUNT(*) AS c
       FROM usage_events e
       JOIN api_keys k ON k.id = e.api_key_id
      WHERE k.account_id = ? AND e.timestamp >= ?
      GROUP BY e.dataset
      ORDER BY c DESC`
  ),
  usageByKey: db.prepare<
    [number, string],
    { key_id: string; name: string; c: number }
  >(
    `SELECT k.id AS key_id, k.name AS name, COUNT(e.id) AS c
       FROM api_keys k
       LEFT JOIN usage_events e
         ON e.api_key_id = k.id AND e.timestamp >= ?
      WHERE k.account_id = ?
      GROUP BY k.id
      ORDER BY c DESC`
  )
};

export const queries = stmts;