import { mkdir } from 'node:fs/promises';
import { dirname, resolve } from 'node:path';
import { PGlite } from '@electric-sql/pglite';
import pg from 'pg';
import type { AppEnv } from '../config/env.js';
import type { Database, Queryable, QueryResult } from './types.js';

function normalizeResult<T>(result: { rows?: T[]; affectedRows?: number; rowCount?: number | null }): QueryResult<T> {
  const rows = result.rows ?? [];
  return { rows, rowCount: result.rowCount ?? result.affectedRows ?? rows.length };
}

function hasMultipleStatements(sql: string): boolean {
  return sql.split(';').filter((statement) => statement.trim().length > 0).length > 1;
}

export async function createDatabase(env: AppEnv): Promise<Database> {
  if (env.DATABASE_DRIVER === 'postgres') {
    const pool = new pg.Pool({
      connectionString: env.DATABASE_URL,
      max: env.NODE_ENV === 'test' ? 4 : 20,
      idleTimeoutMillis: 30_000,
      statement_timeout: 30_000,
      application_name: 'mandegar-delivery-system'
    });
    await pool.query('SELECT 1');
    return {
      kind: 'postgres',
      async query<T extends Record<string, unknown>>(sql: string, params: unknown[] = []) {
        return normalizeResult<T>(await pool.query(sql, params));
      },
      async transaction<T>(callback: (tx: Queryable) => Promise<T>) {
        const client = await pool.connect();
        try {
          await client.query('BEGIN');
          const value = await callback({
            async query<R extends Record<string, unknown>>(sql: string, params: unknown[] = []) {
              return normalizeResult<R>(await client.query(sql, params));
            }
          });
          await client.query('COMMIT');
          return value;
        } catch (error) {
          await client.query('ROLLBACK');
          throw error;
        } finally {
          client.release();
        }
      },
      async close() {
        await pool.end();
      }
    };
  }

  const target = env.PGLITE_DATA_DIR === ':memory:' ? 'memory://' : resolve(env.PGLITE_DATA_DIR);
  if (target !== 'memory://') await mkdir(dirname(target), { recursive: true });
  const pglite = new PGlite(target);
  await pglite.waitReady;
  return {
    kind: 'pglite',
    async query<T extends Record<string, unknown>>(sql: string, params: unknown[] = []) {
      if (params.length === 0 && hasMultipleStatements(sql)) {
        await pglite.exec(sql);
        return { rows: [], rowCount: 0 };
      }
      return normalizeResult<T>(await pglite.query<T>(sql, params));
    },
    async transaction<T>(callback: (tx: Queryable) => Promise<T>) {
      return pglite.transaction(async (inner) => callback({
        async query<R extends Record<string, unknown>>(sql: string, params: unknown[] = []) {
          if (params.length === 0 && hasMultipleStatements(sql)) {
            await inner.exec(sql);
            return { rows: [], rowCount: 0 };
          }
          return normalizeResult<R>(await inner.query<R>(sql, params));
        }
      }));
    },
    async close() {
      await pglite.close();
    }
  };
}
