server/src/db/client.ts

/**
 * Turso/libSQL database client wrapper.
 *
 * Provides a thin wrapper on top of `@libsql/client` that mirrors the
 * `better-sqlite3` API shapes used throughout the codebase, but async.
 *
 * Key patterns:
 *   db.prepare(sql).run(...args)     → await db.run(sql, args)
 *   db.prepare(sql).get(...args)     → await db.get(sql, args)
 *   db.prepare(sql).all(...args)     → await db.all(sql, args)
 *   db.exec(multiSql)               → await db.execMulti(multiSql)
 *   db.transaction(() => { ... })()  → await db.batch([...])
 *   db.pragma(...)                   → await db.run('PRAGMA ...')
 */

import fs from 'fs';
import path from 'path';
import { createClient, type Client, type InStatement, type ResultSet, type Row } from '@libsql/client';

export interface TursoDb {
  client: Client;
  /** Execute a single statement, return full result set. */
  execute(sql: string, args?: any[]): Promise<ResultSet>;
  /** Execute a single statement (like .prepare(sql).run(args)). Returns { changes, lastInsertRowid }. */
  run(sql: string, args?: any[]): Promise<{ changes: number; lastInsertRowid: number }>;
  /** Execute a single statement and return the first row (like .prepare(sql).get(args)). */
  get<T = any>(sql: string, args?: any[]): Promise<T | undefined>;
  /** Execute a single statement and return all rows (like .prepare(sql).all(args)). */
  all<T = any>(sql: string, args?: any[]): Promise<T[]>;
  /** Execute multi-statement SQL (split by semicolons). */
  execMulti(sql: string): Promise<void>;
  /** Execute a batch of statements transactionally (replaces db.transaction). */
  batch(statements: InStatement[]): Promise<ResultSet[]>;
}

let db: TursoDb | null = null;

function createTursoDb(client: Client): TursoDb {
  return {
    client,

    async execute(sql: string, args: any[] = []): Promise<ResultSet> {
      return client.execute({ sql, args });
    },

    async run(sql: string, args: any[] = []) {
      const result = await client.execute({ sql, args });
      return {
        changes: result.rowsAffected,
        lastInsertRowid: result.lastInsertRowid !== undefined ? Number(result.lastInsertRowid) : 0,
      };
    },

    async get<T = any>(sql: string, args: any[] = []): Promise<T | undefined> {
      const result = await client.execute({ sql, args });
      return (result.rows[0] as T) ?? undefined;
    },

    async all<T = any>(sql: string, args: any[] = []): Promise<T[]> {
      const result = await client.execute({ sql, args });
      return result.rows as T[];
    },

    async execMulti(sql: string): Promise<void> {
      const statements = sql
        .split(';')
        .map(s => s.trim())
        .filter(s => s.length > 0);
      for (const stmt of statements) {
        await client.execute(stmt);
      }
    },

    async batch(statements: InStatement[]): Promise<ResultSet[]> {
      if (statements.length === 0) return [];
      return client.batch(statements, 'write');
    },
  };
}

/**
 * Initialize the Turso client. Call once at startup.
 * Uses TURSO_DATABASE_URL + TURSO_AUTH_TOKEN env vars.
 * Falls back to a local file if TURSO_DATABASE_URL is not set (dev mode).
 */
export function initClient(url?: string): TursoDb {
  const dbUrl = url ?? process.env.TURSO_DATABASE_URL ?? 'file:./data/freeapi.db';

  if (process.env.NODE_ENV === 'production' && !process.env.TURSO_DATABASE_URL && !url) {
    console.warn('\n[WARNING] TURSO_DATABASE_URL is not set in production. Falling back to local SQLite database. Any changes will be lost when the container restarts!\n');
  }

  if (dbUrl.startsWith('file:')) {
    const filePath = dbUrl.slice(5);
    const dir = path.dirname(filePath);
    if (!fs.existsSync(dir)) {
      fs.mkdirSync(dir, { recursive: true });
    }
  }

  const authToken = process.env.TURSO_AUTH_TOKEN;

  const client = createClient({
    url: dbUrl,
    authToken: authToken || undefined,
  });

  db = createTursoDb(client);
  return db;
}

/** Get the initialized database wrapper. Throws if not yet created. */
export function getDb(): TursoDb {
  if (!db) {
    throw new Error('Database not initialized. Call initClient() first.');
  }
  return db;
}

// Re-export types
export type { Client, ResultSet, InStatement, Row };