import { Pool } from "pg";

type QueryValue = string | number | boolean | Date | null | Buffer | QueryValue[] | Record<string, unknown>;

// eslint-disable-next-line @typescript-eslint/no-explicit-any
type QueryResult<Row = Record<string, any>> = {
  rows: Row[];
  rowCount: number | null;
};

type QueryClient = {
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  query<Row = Record<string, any>>(
    text: string,
    values?: QueryValue[],
  ): Promise<QueryResult<Row>>;
  release(): void;
};

declare global {
  var mindavioPgPool: Pool | undefined;
}

function getConnectionString() {
  const connectionString = process.env.DATABASE_URL ?? process.env.POSTGRES_URL;

  if (!connectionString) {
    throw new Error("Missing DATABASE_URL. Add a PostgreSQL connection string to .env.local.");
  }

  return connectionString;
}

function getPool() {
  if (!globalThis.mindavioPgPool) {
    globalThis.mindavioPgPool = new Pool({
      connectionString: getConnectionString(),
      max: 10,
      ssl: process.env.POSTGRES_SSL === "true" ? { rejectUnauthorized: false } : undefined,
    });
  }

  return globalThis.mindavioPgPool;
}

function templateToQuery(strings: TemplateStringsArray, values: QueryValue[]) {
  const text = strings.reduce((query, part, index) => {
    const placeholder = index < values.length ? `$${index + 1}` : "";
    return `${query}${part}${placeholder}`;
  }, "");

  return { text, values };
}

// eslint-disable-next-line @typescript-eslint/no-explicit-any
export async function query<Row = Record<string, any>>(
  text: string,
  values: QueryValue[] = [],
): Promise<QueryResult<Row>> {
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  const result = await (getPool() as any).query(text, values);
  return { rows: result.rows as Row[], rowCount: result.rowCount };
}

// eslint-disable-next-line @typescript-eslint/no-explicit-any
export async function sql<Row = Record<string, any>>(
  strings: TemplateStringsArray,
  ...values: QueryValue[]
) {
  const { text, values: queryValues } = templateToQuery(strings, values);
  return query<Row>(text, queryValues);
}

export async function transaction<Result>(
  run: (client: QueryClient) => Promise<Result>,
): Promise<Result> {
  const client = (await getPool().connect()) as QueryClient;

  try {
    await client.query("begin");
    const result = await run(client);
    await client.query("commit");
    return result;
  } catch (error) {
    await client.query("rollback");
    throw error;
  } finally {
    client.release();
  }
}