import Database from 'better-sqlite3';
import { resolve } from 'node:path';

// TEMPORARY dev wiring. The API reads directly from the SvelteKit UI's
// SQLite file so "create a key in the UI → use it in the API" works with
// zero infra. This whole module gets replaced when the API moves to
// @tidyindex/core + Drizzle + Neon (or self-hosted Postgres via Hyperdrive).
const DB_PATH = resolve(
  process.env.DATABASE_PATH ?? '../ui/data/dashboard.db'
);

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

export interface ApiKeyLookup {
  keyId: string;
  name: string;
  scopes: string[];
  account: {
    id: string;
    email: string | null;
    plan: string;
  };
}

const lookupStmt = db.prepare<
  [string],
  {
    key_id: string;
    key_name: string;
    key_scopes: string;
    account_id: string;
    account_email: string | null;
    account_plan: string;
  }
>(`
  SELECT
    k.id         AS key_id,
    k.name       AS key_name,
    k.scopes     AS key_scopes,
    a.id         AS account_id,
    a.email      AS account_email,
    a.plan       AS account_plan
  FROM api_keys k
  JOIN accounts a ON a.id = k.account_id
  WHERE k.key_hash = ? AND k.active = 1
`);

const touchStmt = db.prepare<[number, string]>(
  `UPDATE api_keys SET last_used_at = ? WHERE id = ?`
);

export function lookupApiKey(hash: string): ApiKeyLookup | null {
  const row = lookupStmt.get(hash);
  if (!row) return null;

  // Update last_used_at so the UI shows the key as active. Cheap,
  // synchronous, runs on the hot path — fine for dev; in prod this
  // becomes a buffered write on the AccountMeter Durable Object.
  touchStmt.run(Date.now(), row.key_id);

  return {
    keyId: row.key_id,
    name: row.key_name,
    scopes: JSON.parse(row.key_scopes) as string[],
    account: {
      id: row.account_id,
      email: row.account_email,
      plan: row.account_plan
    }
  };
}