import { Pool } from "pg";

// Raw PG Pool (MyBatis SqlSession 대응)
const globalForPool = globalThis as unknown as {
  pool: Pool | undefined;
};

export const pool =
  globalForPool.pool ??
  new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 10000,
  });

if (process.env.NODE_ENV !== "production") globalForPool.pool = pool;

// Helper: execute raw query and return rows as-is
// SQL에서 AS "FIELD_NAME" 형태로 alias를 지정하면 그대로 유지됨
// PostgreSQL은 따옴표 없는 alias를 소문자로 반환하므로, 대문자가 필요하면 SQL에서 "ALIAS" 사용
export async function queryRows<T = Record<string, unknown>>(
  sql: string,
  params: unknown[] = []
): Promise<T[]> {
  const client = await pool.connect();
  try {
    const result = await client.query(sql, params);
    return result.rows as T[];
  } finally {
    client.release();
  }
}

// Helper: execute raw query and return single row
export async function queryOne<T = Record<string, unknown>>(
  sql: string,
  params: unknown[] = []
): Promise<T | null> {
  const rows = await queryRows<T>(sql, params);
  return rows[0] ?? null;
}

// Helper: execute insert/update/delete
export async function execute(
  sql: string,
  params: unknown[] = []
): Promise<number> {
  const client = await pool.connect();
  try {
    const result = await client.query(sql, params);
    return result.rowCount ?? 0;
  } finally {
    client.release();
  }
}
