database

import Database from "better-sqlite3";

export interface QueryResult {
  columns: string[];
  rows: unknown[][];
  rowCount: number;
  truncated: boolean;
}

export interface TableInfo {
  name: string;
  columns: Array<{ name: string; type: string; notnull: boolean; pk: boolean }>;
}

export function sqliteQuery(dbPath: string, sql: string, maxRows: number): QueryResult {
  const db = new Database(dbPath, { readonly: true });
  try {
    const stmt = db.prepare(sql);
    const rows = stmt.all() as Record<string, unknown>[];
    const columns = rows.length > 0 ? Object.keys(rows[0]) : [];
    const truncated = rows.length > maxRows;
    return {
      columns,
      rows: rows.slice(0, maxRows).map(r => columns.map(c => r[c])),
      rowCount: rows.length,
      truncated,
    };
  } finally {
    db.close();
  }
}

export function sqliteWriteQuery(dbPath: string, sql: string): { changes: number; lastInsertRowid: number | bigint } {
  const db = new Database(dbPath);
  try {
    const result = db.prepare(sql).run();
    return { changes: result.changes, lastInsertRowid: result.lastInsertRowid };
  } finally {
    db.close();
  }
}

export function sqliteTables(dbPath: string): string[] {
  const db = new Database(dbPath, { readonly: true });
  try {
    const rows = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
    ).all() as Array<{ name: string }>;
    return rows.map(r => r.name);
  } finally {
    db.close();
  }
}

export function sqliteSchema(dbPath: string, tableName: string): TableInfo {
  const db = new Database(dbPath, { readonly: true });
  try {
    const cols = db.prepare(`PRAGMA table_info(${tableName})`).all() as Array<{
      name: string; type: string; notnull: number; pk: number;
    }>;
    return {
      name: tableName,
      columns: cols.map(c => ({ name: c.name, type: c.type, notnull: c.notnull === 1, pk: c.pk === 1 })),
    };
  } finally {
    db.close();
  }
}