/**
 * Test database setup - resets schema for each test run
 */

import { drizzle } from "drizzle-orm/node-postgres";
import { sql } from "drizzle-orm";
import { execSync } from "child_process";
import pg from "pg";
import { trails, walks, completions, drafts, ingestionCursor, accounts } from "@sidetrail/db";

const { Pool } = pg;

const TEST_DATABASE_URL =
  process.env.TEST_DATABASE_URL || "postgresql://localhost:5432/sidetrail_test";

let pool: pg.Pool | null = null;
let db: ReturnType<typeof drizzle> | null = null;

export async function initTestDb(): Promise<void> {
  if (pool) return;

  // Safety check: only run against test databases
  if (!TEST_DATABASE_URL.includes("_test") && !TEST_DATABASE_URL.includes("test_")) {
    throw new Error(
      `Refusing to run tests against database URL that doesn't contain "_test" or "test_": ${TEST_DATABASE_URL}`,
    );
  }

  pool = new Pool({ connectionString: TEST_DATABASE_URL });
  db = drizzle(pool);

  // Drop all tables
  await db.execute(sql`
    DROP TABLE IF EXISTS trails CASCADE;
    DROP TABLE IF EXISTS walks CASCADE;
    DROP TABLE IF EXISTS completions CASCADE;
    DROP TABLE IF EXISTS drafts CASCADE;
    DROP TABLE IF EXISTS accounts CASCADE;
    DROP TABLE IF EXISTS ingestion_cursor CASCADE;
    DROP TABLE IF EXISTS auth_state CASCADE;
    DROP TABLE IF EXISTS auth_session CASCADE;
  `);

  // Recreate from Drizzle schema
  execSync(`npx drizzle-kit push --force`, {
    env: { ...process.env, DATABASE_URL: TEST_DATABASE_URL },
    stdio: "ignore",
  });

  // Seed cursor
  await db.execute(sql`INSERT INTO ingestion_cursor (id, cursor_us) VALUES (1, 0)`);
}

export function getTestDb(): ReturnType<typeof drizzle> {
  if (!db) {
    throw new Error("Test database not initialized. Call initTestDb() first.");
  }
  return db;
}

export function getTestPool(): pg.Pool {
  if (!pool) {
    throw new Error("Test database not initialized. Call initTestDb() first.");
  }
  return pool;
}

export async function clearTestDb(): Promise<void> {
  if (!db) return;

  await db.delete(completions);
  await db.delete(walks);
  await db.delete(trails);
  await db.delete(drafts);
  await db.delete(accounts);

  await db.execute(sql`UPDATE ingestion_cursor SET cursor_us = 0, updated_at = NOW() WHERE id = 1`);
}

export async function closeTestDb(): Promise<void> {
  if (pool) {
    await pool.end();
    pool = null;
    db = null;
  }
}

export { trails, walks, completions, drafts, ingestionCursor, accounts };
