// SQLite via better-sqlite3. One Database instance per renderer-tracked
// handle; handles are keyed by the normalised db name (Tauri's plugin-sql
// uses `sqlite:<name>` — we strip the prefix). Sync API is fine here
// because the main process has its own event loop; better-sqlite3's
// prepare/run/all are blocking but fast for typical chat-cache queries
// (<1ms per op for the current workload).
//
// Binding param style: Tauri's plugin-sql used $1, $2... positionals with
// bindings as an array. SQLite parses `$NAME` as a NAMED parameter
// (NAME = `1`, `2`, …), not as positional, so better-sqlite3 wants the
// bindings as `{ '1': v1, '2': v2 }` not `[v1, v2]`. We accept the old
// array-shape from callers and convert to the named-object on the way in.

import { app, ipcMain } from 'electron';
import Database from 'better-sqlite3';
import fs from 'node:fs';
import path from 'node:path';

import {
  CHANNELS,
  type SqlExecuteArgs,
  type SqlExecuteResult,
  type SqlLoadArgs,
  type SqlSelectArgs,
  type SqlSelectResult,
} from '../ipc-types';

interface Handle {
  db: Database.Database;
  filePath: string;
}

const handles = new Map<string, Handle>();

function stripPrefix(name: string): string {
  return name.startsWith('sqlite:') ? name.slice('sqlite:'.length) : name;
}

function requireHandle(h: string): Handle {
  const entry = handles.get(h);
  if (!entry) throw new Error(`sql: unknown handle ${h}`);
  return entry;
}

// Convert a positional bindings array `[v1, v2]` to the named-params object
// `{ '1': v1, '2': v2 }` that better-sqlite3 needs when the SQL uses
// `$1`/`$2` named placeholders. Returns the original array (spread later)
// when it's empty.
function bindParams(bindings: unknown[] | undefined): Record<string, unknown> | [] {
  const arr = bindings ?? [];
  if (arr.length === 0) return [];
  const obj: Record<string, unknown> = {};
  for (let i = 0; i < arr.length; i++) {
    obj[String(i + 1)] = arr[i];
  }
  return obj;
}

export function register(): void {
  ipcMain.handle(CHANNELS.SQL_LOAD, async (_evt, args: SqlLoadArgs): Promise<string> => {
    const rawName = stripPrefix(args.name);
    const fileName = rawName.endsWith('.db') ? rawName : rawName + '.db';
    const filePath = path.join(app.getPath('userData'), fileName);
    fs.mkdirSync(path.dirname(filePath), { recursive: true });
    const existing = handles.get(rawName);
    if (existing) return rawName;
    const db = new Database(filePath);
    db.pragma('journal_mode = WAL');
    handles.set(rawName, { db, filePath });
    return rawName;
  });

  ipcMain.handle(
    CHANNELS.SQL_EXECUTE,
    async (_evt, args: SqlExecuteArgs): Promise<SqlExecuteResult> => {
      const entry = requireHandle(args.handle);
      const stmt = entry.db.prepare(args.query);
      const params = bindParams(args.bindings);
      const info = Array.isArray(params) ? stmt.run() : stmt.run(params);
      return {
        rowsAffected: info.changes,
        lastInsertId:
          typeof info.lastInsertRowid === 'bigint'
            ? Number(info.lastInsertRowid)
            : (info.lastInsertRowid ?? null),
      };
    },
  );

  ipcMain.handle(
    CHANNELS.SQL_SELECT,
    async (_evt, args: SqlSelectArgs): Promise<SqlSelectResult> => {
      const entry = requireHandle(args.handle);
      const stmt = entry.db.prepare(args.query);
      const params = bindParams(args.bindings);
      const rows = (Array.isArray(params) ? stmt.all() : stmt.all(params)) as Record<
        string,
        unknown
      >[];
      return rows;
    },
  );

  ipcMain.handle(CHANNELS.SQL_CLOSE, async (_evt, handle: string): Promise<void> => {
    const entry = handles.get(handle);
    if (!entry) return;
    try {
      entry.db.close();
    } catch (err: unknown) {
      console.warn('[sql] close failed', err);
    }
    handles.delete(handle);
  });
}