let sharedPgClientPromise = null;

export function resolveGooseSessionPgUrl(env = process.env) {
  const explicit = String(env.GOOSE_SESSION_DB_URL ?? '').trim();
  if (explicit) return explicit;
  const host = env.GOOSE_SESSION_PG_HOST ?? '127.0.0.1';
  const port = env.GOOSE_SESSION_PG_PORT ?? '5432';
  const database = env.GOOSE_SESSION_PG_DATABASE ?? 'memind_sessions';
  const user = env.GOOSE_SESSION_PG_USER ?? 'john';
  const password = env.GOOSE_SESSION_PG_PASSWORD ?? '';
  if (password) {
    return `postgresql://${encodeURIComponent(user)}:${encodeURIComponent(password)}@${host}:${port}/${database}`;
  }
  return `postgresql://${user}@${host}:${port}/${database}`;
}

export function isGooseSessionPgConfigured(env = process.env) {
  if (String(env.GOOSE_SESSION_DB_URL ?? '').trim()) return true;
  return String(env.GOOSE_SESSION_PG_DISABLE ?? '') !== '1';
}

async function getSharedPgClient(env = process.env) {
  if (!isGooseSessionPgConfigured(env)) return null;
  if (!sharedPgClientPromise) {
    sharedPgClientPromise = import('pg')
      .then(async ({ default: pg }) => {
        const client = new pg.Client({ connectionString: resolveGooseSessionPgUrl(env) });
        await client.connect();
        return client;
      })
      .catch((err) => {
        sharedPgClientPromise = null;
        throw err;
      });
  }
  return sharedPgClientPromise;
}

export async function fetchGooseSessionAccumulatedCostUsd(sessionId, env = process.env) {
  const normalizedSessionId = String(sessionId ?? '').trim();
  if (!normalizedSessionId || !isGooseSessionPgConfigured(env)) return null;
  try {
    const client = await getSharedPgClient(env);
    const result = await client.query(
      `SELECT accumulated_cost FROM sessions WHERE id = $1 LIMIT 1`,
      [normalizedSessionId],
    );
    const raw = result.rows[0]?.accumulated_cost;
    if (raw == null) return null;
    const value = Number(raw);
    return Number.isFinite(value) && value >= 0 ? value : null;
  } catch {
    return null;
  }
}

export function createGooseSessionCostReader(env = process.env) {
  return (sessionId) => fetchGooseSessionAccumulatedCostUsd(sessionId, env);
}

export async function closeGooseSessionPgClient() {
  if (!sharedPgClientPromise) return;
  try {
    const client = await sharedPgClientPromise;
    await client.end();
  } catch {
    // ignore shutdown errors in tests/scripts
  } finally {
    sharedPgClientPromise = null;
  }
}