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

import { env } from "../config/env.js";

export const db = new Pool({
  connectionString: env.DATABASE_URL,
  application_name: "tracker-api",
  max: env.DB_MAX_CONNECTIONS,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 30_000,
  statement_timeout: env.DB_STATEMENT_TIMEOUT_MS,
  query_timeout: env.DB_QUERY_TIMEOUT_MS,
  lock_timeout: env.DB_LOCK_TIMEOUT_MS,
  idle_in_transaction_session_timeout: env.DB_IDLE_TRANSACTION_TIMEOUT_MS,
});

const summarizeSql = (text: string) => text.replace(/\s+/g, " ").trim().slice(0, 240);

export const query = async <T extends QueryResultRow = QueryResultRow>(
  text: string,
  params: unknown[] = [],
): Promise<T[]> => {
  const startedAt = performance.now();

  try {
    const result = await db.query<T>(text, params);
    return result.rows;
  } finally {
    const durationMs = performance.now() - startedAt;
    if (durationMs >= env.DB_SLOW_QUERY_MS) {
      console.warn("slow database query", {
        durationMs: Math.round(durationMs),
        sql: summarizeSql(text),
      });
    }
  }
};

export const queryOne = async <T extends QueryResultRow = QueryResultRow>(
  text: string,
  params: unknown[] = [],
): Promise<T | null> => {
  const rows = await query<T>(text, params);
  return rows[0] ?? null;
};

export const withDatabaseAdvisoryLock = async <T>(
  lockName: string,
  work: () => Promise<T>,
): Promise<{ acquired: boolean; value?: T }> => {
  const client = await db.connect();
  let acquired = false;

  try {
    const lockResult = await client.query<{ acquired: boolean }>(
      "select pg_try_advisory_lock(hashtext('tracker-api'), hashtext($1)) as acquired",
      [lockName],
    );
    acquired = lockResult.rows[0]?.acquired === true;

    if (!acquired) {
      return { acquired: false };
    }

    return { acquired: true, value: await work() };
  } finally {
    if (acquired) {
      await client.query(
        "select pg_advisory_unlock(hashtext('tracker-api'), hashtext($1))",
        [lockName],
      ).catch((error) => {
        console.error("failed to release database advisory lock", { lockName, error });
      });
    }
    client.release();
  }
};

export const isDatabaseBusyError = (error: unknown) => {
  const code = typeof error === "object" && error !== null && "code" in error
    ? String(error.code)
    : null;

  return code === "57014" || code === "55P03";
};