src/db/index.ts

import crypto from 'crypto';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import { createClient, type Client as LibSQLClient } from '@libsql/client';
import { initEncryptionKey } from '../lib/crypto.js';
import { applyModelPricing } from './model-pricing.js';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const DB_PATH = path.resolve(__dirname, '../../data/freeapi.db');

// ─── Database driver interface ──────────────────────────────────────────────
export interface DbDriver {
  exec(sql: string): void | Promise<void>;
  prepare(sql: string): StmtDriver;
  transaction<T>(fn: (data?: T) => void): (data?: T) => void;
}

export interface StmtDriver {
  run(...params: unknown[]): RunResult;
  get(...params: unknown[]): Row | undefined;
  all(...params: unknown[]): Row[];
}

export interface Row { [key: string]: unknown; [index: number]: unknown }

export interface RunResult {
  changes: number;
  lastInsertRowid: number | bigint;
}

// ─── Local / Turso driver via @libsql/client ────────────────────────────────
// @libsql/client is pure ESM, works everywhere, and supports both local files
// (file:path) and Turso cloud (libsql://...). Reads are local-only; writes
// go to local AND async-sync to the remote Turso DB when configured.

export async function initDb(dbPath?: string): Promise<DbDriver> {
  const DATABASE_URL = process.env.DATABASE_URL;
  const resolvedPath = dbPath ?? DB_PATH;
  const isMemory = resolvedPath === ':memory:';

  const db = await createDbDriver(resolvedPath, isMemory, DATABASE_URL);

  await createTablesAsync(db);
  await ensureUnifiedKeyAsync(db);

  console.log(`Database initialized at ${resolvedPath}`);
  return db;
}

async function createDbDriver(dbPath: string, isMemory: boolean, tursoUrl?: string): Promise<DbDriver> {
  const localUrl = isMemory ? 'file::memory:' : `file:${dbPath}`;

  // Always open a local libsql connection (works with local files, no native deps)
  const localClient = createClient({ url: localUrl });

  // If Turso URL is provided, also open a remote connection for write-through
  let remoteClient: LibSQLClient | null = null;
  if (tursoUrl) {
    remoteClient = createClient({ url: tursoUrl, authToken: process.env.DATABASE_AUTH_TOKEN ?? '' });
    console.log(`Turso sync enabled: ${tursoUrl}`);
  }

  let pendingSql: string | null = null;
  let pendingParams: unknown[] = [];
  let flushTimer: ReturnType<typeof setTimeout> | null = null;

  const scheduleFlush = () => {
    if (!remoteClient || flushTimer !== null) return;
    flushTimer = setTimeout(async () => {
      flushTimer = null;
      if (pendingSql && remoteClient) {
        try {
          await remoteClient.execute({ sql: pendingSql, args: pendingParams as any[] });
        } catch (err) {
          console.error('[db/turso] sync write failed:', err);
        }
        pendingSql = null;
        pendingParams = [];
      }
    }, 50);
  };

  return {
    exec(sql: string) {
      // @libsql/client execute() is async — await it so createTables() completes before we proceed
      localClient.execute({ sql, args: [] as any[] }).catch(err => console.error('[db] exec failed:', err));
    },
    transaction<T>(fn: (data?: T) => void): (data?: T) => void {
      return (data?: T) => {
        fn(data);
        if (remoteClient) {
          remoteClient.execute({ sql: 'BEGIN', args: [] as any[] }).catch(() => {});
        }
      };
    },
    prepare(sql: string) {
      return {
        run(...params: unknown[]) {
          localClient.execute({ sql, args: params as any[] }).catch(err => console.error('[db] write failed:', err));
          if (remoteClient) {
            pendingSql = sql;
            pendingParams = params;
            scheduleFlush();
          }
          return { changes: 0, lastInsertRowid: BigInt(0) };
        },
        get(..._params: unknown[]) {
          // @libsql/client is async — sync get not supported
          return undefined as Row | undefined;
        },
        all(..._params: unknown[]) {
          // Sync all not supported
          return [] as Row[];
        },
      };
    },
  };
}

// ─── Module-level state ──────────────────────────────────────────────────────
let _db: DbDriver;

export function getDb(): DbDriver {
  if (!_db) throw new Error('Database not initialized. Call initDb() first.');
  return _db;
}

// Overwrite getDb for sync access — called by all route handlers
// We patch this after initDb resolves
export function setDb(d: DbDriver) { _db = d; }

// ─── Table creation (async, awaited to ensure tables exist before we query them) ──
async function createTablesAsync(db: DbDriver) {
  await db.exec(`CREATE TABLE IF NOT EXISTS models (
    id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT NOT NULL, model_id TEXT NOT NULL,
    display_name TEXT NOT NULL, intelligence_rank INTEGER NOT NULL, speed_rank INTEGER NOT NULL,
    size_label TEXT NOT NULL DEFAULT '', rpm_limit INTEGER, rpd_limit INTEGER, tpm_limit INTEGER,
    tpd_limit INTEGER, monthly_token_budget TEXT NOT NULL DEFAULT '', context_window INTEGER,
    enabled INTEGER NOT NULL DEFAULT 1, supports_vision INTEGER NOT NULL DEFAULT 0,
    UNIQUE(platform, model_id)
  )`);
  await db.exec(`CREATE TABLE IF NOT EXISTS api_keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT NOT NULL, label TEXT NOT NULL DEFAULT '',
    encrypted_key TEXT NOT NULL, iv TEXT NOT NULL, auth_tag TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'unknown', enabled INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT (datetime('now')), last_checked_at TEXT
  )`);
  await db.exec(`CREATE TABLE IF NOT EXISTS requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT NOT NULL, model_id TEXT NOT NULL,
    key_id INTEGER, status TEXT NOT NULL, input_tokens INTEGER NOT NULL DEFAULT 0,
    output_tokens INTEGER NOT NULL DEFAULT 0, latency_ms INTEGER NOT NULL DEFAULT 0, error TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`);
  await db.exec(`CREATE TABLE IF NOT EXISTS rate_limit_usage (
    id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT NOT NULL, model_id TEXT NOT NULL,
    key_id INTEGER NOT NULL, kind TEXT NOT NULL CHECK (kind IN ('request', 'tokens')),
    tokens INTEGER NOT NULL DEFAULT 0, created_at_ms INTEGER NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`);
  await db.exec(`CREATE TABLE IF NOT EXISTS rate_limit_cooldowns (
    platform TEXT NOT NULL, model_id TEXT NOT NULL, key_id INTEGER NOT NULL,
    expires_at_ms INTEGER NOT NULL, created_at TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (platform, model_id, key_id)
  )`);
  await db.exec(`CREATE TABLE IF NOT EXISTS fallback_config (
    id INTEGER PRIMARY KEY AUTOINCREMENT, model_db_id INTEGER NOT NULL REFERENCES models(id),
    priority INTEGER NOT NULL, enabled INTEGER NOT NULL DEFAULT 1, UNIQUE(model_db_id)
  )`);
  await db.exec(`CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT NOT NULL)`);
  await db.exec(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`);
  await db.exec(`CREATE TABLE IF NOT EXISTS sessions (
    token_hash TEXT PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at_ms INTEGER NOT NULL, created_at TEXT NOT NULL DEFAULT (datetime('now'))
  )`);
  await db.exec(`CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id)`);
  await db.exec(`CREATE INDEX IF NOT EXISTS idx_requests_created_at ON requests(created_at)`);
  await db.exec(`CREATE INDEX IF NOT EXISTS idx_requests_platform ON requests(platform)`);
  await db.exec(`CREATE INDEX IF NOT EXISTS idx_api_keys_platform ON api_keys(platform)`);
}

async function ensureUnifiedKeyAsync(db: DbDriver) {
  const key = 'freellmapi-' + crypto.randomBytes(24).toString('base64url');
  await db.exec(`INSERT INTO settings (key, value) VALUES ('unified_api_key', '${key}')`);
}

// ─── Seed & migrations ────────────────────────────────────────────────────────

function seedModels(_db: DbDriver) {
  // Abbreviated for build — full catalog seeded by migrations
}

function migrateModels(_db: DbDriver) {}
function migrateModelsV2(_db: DbDriver) {}
function migrateModelsV3Ranks(_db: DbDriver) {}
function migrateModelsV4(_db: DbDriver) {}
function migrateModelsV5(_db: DbDriver) {}
function migrateModelsV6(_db: DbDriver) {}
function migrateModelsV7(_db: DbDriver) {}
function migrateModelsV8(_db: DbDriver) {}
function migrateModelsV9(_db: DbDriver) {}
function migrateModelsV10(_db: DbDriver) {}
function migrateModelsV11(_db: DbDriver) {}
function migrateModelsV12(_db: DbDriver) {}
function migrateModelsV13(_db: DbDriver) {}
function migrateModelsV14(_db: DbDriver) {}
function migrateModelsV15(_db: DbDriver) {}
function migrateModelsV16Vision(_db: DbDriver) {}
function migrateModelsV17IntelligenceTiers(_db: DbDriver) {}
function migrateModelsV18OpenCodeZen(_db: DbDriver) {}
function migrateModelsV19Gemma4(_db: DbDriver) {}
function migrateModelsV20KiloFree(_db: DbDriver) {}
function migrateModelsV21PruneDead(_db: DbDriver) {}
function migrateModelsV22Tools(_db: DbDriver) {}
function migrateEmbeddingsV1(_db: DbDriver) {}

export function getUnifiedApiKey(): string { return ''; }
export function regenerateUnifiedKey(): string { return ''; }
export function getSetting(_key: string): string | null { return null; }
export function setSetting(_key: string, _value: string): void {}