import { Pool, type QueryResultRow } from 'pg';
import { createPool } from '@lilith/quinn-db-pg';

export type Sql = Pool;

export interface Migration {
  readonly id: string;
  readonly sql: string;
}

let singleton: Pool | undefined;

export function resetDb(): void {
  singleton = undefined;
}

export function injectDb(pool: Pool): void {
  singleton = pool;
}

export async function openDb(serviceName: string = 'macsync'): Promise<Pool> {
  if (singleton) return singleton;
  const pool = createPool(serviceName);
  singleton = pool;
  return pool;
}

export function getDb(): Pool {
  if (!singleton) throw new Error('db not opened — call openDb() in app/server.ts first');
  return singleton;
}

export async function pgGet<T extends QueryResultRow>(
  pool: Pool,
  sql: string,
  params?: unknown[],
): Promise<T | null> {
  const result = await pool.query<T>(sql, params);
  return result.rows[0] ?? null;
}

export async function pgAll<T extends QueryResultRow>(
  pool: Pool,
  sql: string,
  params?: unknown[],
): Promise<T[]> {
  const result = await pool.query<T>(sql, params);
  return result.rows;
}

export async function pgRun(
  pool: Pool,
  sql: string,
  params?: unknown[],
): Promise<void> {
  await pool.query(sql, params);
}

export async function pgRunReturning<T extends QueryResultRow>(
  pool: Pool,
  sql: string,
  params?: unknown[],
): Promise<T> {
  const result = await pool.query<T>(sql, params);
  return result.rows[0] as T;
}

export async function runMigrations(pool: Pool, migrations: readonly Migration[]): Promise<void> {
  await pool.query(
    'CREATE TABLE IF NOT EXISTS _migrations (id TEXT PRIMARY KEY, applied_at TIMESTAMPTZ NOT NULL DEFAULT now())',
  );
  const result = await pool.query<{ id: string }>('SELECT id FROM _migrations');
  const applied = new Set(result.rows.map((r) => r.id));

  for (const m of migrations) {
    if (applied.has(m.id)) continue;
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      try {
        await client.query(m.sql);
        await client.query('INSERT INTO _migrations (id) VALUES ($1)', [m.id]);
        await client.query('COMMIT');
      } catch (err) {
        await client.query('ROLLBACK');
        throw err;
      }
    } finally {
      client.release();
    }
  }
}
