import { Pool } from "pg";
import { vi } from "vitest";

export function createPool() {
  return new Pool({ connectionString: process.env.DATABASE_URL });
}

// Replace the app's Prisma-based queryRaw with a direct pg call so that
// tests don't depend on Prisma's singleton picking up the right DATABASE_URL.
// Must be called BEFORE dynamically importing any module that uses @/lib/db.
// Uses vi.doMock (not vi.mock) so it is NOT hoisted and CAN close over `p`.
export function mockDbWithPool(p: Pool) {
  vi.resetModules(); // clear module cache so fresh imports pick up the mock
  vi.doMock("@/lib/db", () => ({
    queryRaw: async (sql: string, params: unknown[] = []) => {
      const result = await p.query(sql, params);
      return result.rows;
    },
    // Mirrors the real module: a mock that omits an export makes it `undefined`
    // at the call site, so any route using queryRow fails with a confusing
    // "not a function" rather than a query error.
    queryRow: async (sql: string, params: unknown[] = []) => {
      const result = await p.query(sql, params);
      return result.rows[0] ?? null;
    },
    prisma: p,
  }));
}

/**
 * Refuse to truncate anything that is not the test database.
 *
 * `DATABASE_URL` in `.env.test` names the Postgres container by IP, and
 * container IPs move on recreation: 172.22.0.47 stopped being
 * `postgres-personal` and became `postgres-pantry`, so the suite spent a while
 * pointing its TRUNCATE at another app's database. It only failed safe because
 * the credentials happened not to match — had they matched, this would have
 * wiped pantry-app.
 *
 * Checked once per process, before the first truncate.
 */
let targetVerified = false;
async function assertTestDatabase(pool: Pool) {
  if (targetVerified) return;
  const { rows } = await pool.query<{ db: string }>(`SELECT current_database() AS db`);
  const db = rows[0]?.db;
  if (db !== "personal_test") {
    throw new Error(
      `Refusing to truncate: connected to "${db}", expected "personal_test". ` +
      `Check DATABASE_URL in .env.test — the Postgres container IP may have changed ` +
      `(docker inspect postgres-personal --format '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}').`
    );
  }
  targetVerified = true;
}

/** Wipe all data tables and restart sequences between tests. */
export async function resetDB(pool: Pool) {
  await assertTestDatabase(pool);
  await pool.query(`
    TRUNCATE
      split_payments,
      transaction_splits,
      transaction_tags,
      transaction_overrides,
      rule_apply_runs,
      rules,
      budgets,
      account_owner_mappings,
      transactions,
      statements,
      tags,
      trips,
      participants
    RESTART IDENTITY CASCADE
  `);
}

/** Seed two participants and return their IDs. */
export async function seedParticipants(pool: Pool, names: [string, string] = ["Alice", "Bob"]) {
  const r1 = await pool.query(
    `INSERT INTO participants (name) VALUES ($1) RETURNING id`,
    [names[0]]
  );
  const r2 = await pool.query(
    `INSERT INTO participants (name) VALUES ($1) RETURNING id`,
    [names[1]]
  );
  return { ownerId: r1.rows[0].id as number, otherId: r2.rows[0].id as number };
}

/** Insert a manual transaction (no statement) and return its id. */
export async function insertTransaction(
  pool: Pool,
  ownerId: number,
  overrides: {
    description?: string;
    amount?: number;
    category?: string;
    transaction_type?: string;
    transaction_date?: string;
    merchant_normalized?: string;
  } = {}
): Promise<number> {
  const r = await pool.query(
    `INSERT INTO transactions
       (owner_id, statement_id, transaction_date, description, amount, transaction_type, category, row_index)
     VALUES ($1, NULL, $2, $3, $4, $5, $6, 0) RETURNING id`,
    [
      ownerId,
      // Post-cutover by default: a split on an older transaction never counts
      // towards a balance (ACTIVE_OBLIGATION), so a pre-cutover default would
      // make every balance fixture silently read zero.
      overrides.transaction_date ?? "2026-06-15",
      overrides.description ?? "Test transaction",
      overrides.amount ?? 100,
      overrides.transaction_type ?? "debit",
      overrides.category ?? "other",
    ]
  );
  return r.rows[0].id as number;
}
