// src/db.mjs
import { fileURLToPath } from 'node:url';
import pg from 'pg';
import { bootWalk, createFailoverPool, migrationsFrom } from '@turnzero/database';

const MIGRATIONS = fileURLToPath(new URL('../migrations/', import.meta.url));
const HEALTH_READ_MS = 2_000;

// The live pool, which only the process entry opens: the package's failover
// pool over a pg pool. Its maximum is the plan's connection_limit, which the
// platform injects and the turnzero command's provision writes; one
// connection where the setting is absent.
export function openLivePool() {
  const url = process.env.APP_DATABASE_URL;
  if (!url) throw new Error('APP_DATABASE_URL is not set');
  const limit = process.env.APP_DATABASE_CONNECTION_LIMIT ?? '';
  const max = /^[1-9][0-9]*$/.test(limit) ? Number(limit) : 1;
  return createFailoverPool({
    // settings is { max, connectionTimeoutMillis }: the maximum and the five-second connect limit.
    connect: (settings) => new pg.Pool({ connectionString: url, ...settings }),
    max,
    onConnectionLoss: (error) => console.warn('database session ended:', error.message),
  });
}

// The migrations through the package's boot walk: a lost connection retried
// every second for up to 120 seconds, and any other failure ends the start.
export function migrate(pool) {
  return bootWalk(pool, migrationsFrom(MIGRATIONS), {
    onRetry: (error, attempt) => {
      if (attempt === 1) console.warn('database unreachable at start; retrying:', error.message);
    },
  });
}

// The data layer over any pool with the pg.Pool shape: the live one, or a test double's.
export function createDataLayer(pool) {
  return {
    // One read over the connection, bounded so a probe never waits on a lost database.
    async reachable() {
      let timer;
      const late = new Promise((resolve) => { timer = setTimeout(resolve, HEALTH_READ_MS, 'late'); });
      try {
        return (await Promise.race([pool.query('SELECT 1'), late])) !== 'late';
      } catch {
        return false;
      } finally {
        clearTimeout(timer);
      }
    },
    async addNote(title) {
      const { rows } = await pool.query('INSERT INTO "notes" (title) VALUES ($1) RETURNING id, title', [title]);
      return rows[0];
    },
    async listNotes() {
      const { rows } = await pool.query('SELECT id, title FROM "notes" ORDER BY id');
      return rows;
    },
  };
}