// Typed query wrapper for the vendored Hasna storage kit.
//
// A thin, typed surface over a `pg.Pool` (or any compatible executor). It fixes
// the drift found in the audit where knowledge's async adapter dropped the
// single-row `get()` helper, leaving callers to hand-roll `rows[0] ?? null`
// inconsistently. Every kit consumer gets the same small, safe vocabulary:
//
//   query   -> full result (rows + rowCount)
//   many    -> T[]                     (all rows)
//   get     -> T | null                (first row or null) <- restored
//   one     -> T                       (exactly one row, else throws)
//   execute -> void                    (DDL / writes where rows are ignored)
//
// PURE REMOTE (Amendment A1): this wrapper reads and writes the same cloud
// Postgres. There is no cache, no local mirror, and no merge — a `get()` hits
// the database every time.

import type { Pool, PoolClient, QueryResultRow } from "pg";

export interface QueryResult<T extends QueryResultRow> {
  rows: T[];
  rowCount: number;
}

/**
 * Minimal executor contract. `pg.Pool` and `pg.PoolClient` both satisfy the
 * `query` method; the wrapper builds the rest on top so tests can substitute a
 * lightweight shim without pulling in a live Postgres.
 */
export interface PgExecutor {
  query<T extends QueryResultRow>(
    sql: string,
    params?: readonly unknown[],
  ): Promise<{ rows: T[]; rowCount: number | null }>;
}

export interface TypedQueryClient {
  query<T extends QueryResultRow>(sql: string, params?: readonly unknown[]): Promise<QueryResult<T>>;
  many<T extends QueryResultRow>(sql: string, params?: readonly unknown[]): Promise<T[]>;
  /** First row or `null`. Restored here after knowledge dropped it. */
  get<T extends QueryResultRow>(sql: string, params?: readonly unknown[]): Promise<T | null>;
  /** Exactly one row; throws if zero or more than one row is returned. */
  one<T extends QueryResultRow>(sql: string, params?: readonly unknown[]): Promise<T>;
  execute(sql: string, params?: readonly unknown[]): Promise<void>;
}

/** Wrap any `PgExecutor` (a Pool, a PoolClient, or a test shim) with the typed vocabulary. */
export function wrapExecutor(executor: PgExecutor): TypedQueryClient {
  return {
    async query<T extends QueryResultRow>(sql: string, params?: readonly unknown[]): Promise<QueryResult<T>> {
      const result = await executor.query<T>(sql, params);
      return { rows: result.rows, rowCount: result.rowCount ?? result.rows.length };
    },
    async many<T extends QueryResultRow>(sql: string, params?: readonly unknown[]): Promise<T[]> {
      const result = await executor.query<T>(sql, params);
      return result.rows;
    },
    async get<T extends QueryResultRow>(sql: string, params?: readonly unknown[]): Promise<T | null> {
      const result = await executor.query<T>(sql, params);
      return result.rows[0] ?? null;
    },
    async one<T extends QueryResultRow>(sql: string, params?: readonly unknown[]): Promise<T> {
      const result = await executor.query<T>(sql, params);
      if (result.rows.length !== 1) {
        throw new Error(`Expected exactly one row, got ${result.rows.length}.`);
      }
      return result.rows[0] as T;
    },
    async execute(sql: string, params?: readonly unknown[]): Promise<void> {
      await executor.query(sql, params);
    },
  };
}

export interface PoolQueryClient extends TypedQueryClient {
  readonly pool: Pool;
  /** Run a callback inside a `BEGIN`/`COMMIT` transaction on a dedicated client. */
  transaction<T>(fn: (client: TypedQueryClient) => Promise<T>): Promise<T>;
  close(): Promise<void>;
}

/** Build a `PoolQueryClient` around a live `pg.Pool`. */
export function createQueryClient(pool: Pool): PoolQueryClient {
  const base = wrapExecutor(pool);
  return {
    ...base,
    pool,
    async transaction<T>(fn: (client: TypedQueryClient) => Promise<T>): Promise<T> {
      const client: PoolClient = await pool.connect();
      try {
        await client.query("BEGIN");
        const result = await fn(wrapExecutor(client));
        await client.query("COMMIT");
        return result;
      } catch (error) {
        try {
          await client.query("ROLLBACK");
        } catch {
          // ignore rollback failure; surface the original error
        }
        throw error;
      } finally {
        client.release();
      }
    },
    async close(): Promise<void> {
      await pool.end();
    },
  };
}