/**
 * Live Postgres/MySQL integration tests. These are GATED on env vars so the
 * normal `bun test` run stays green without a database:
 *
 *   WRNEXUS_PG_URL=postgres://…   WRNEXUS_MYSQL_URL=mysql://…   bun test packages/db
 *
 * The `bun run test:db:live` script spins up both via docker-compose, sets the
 * env vars, runs this file, and tears the containers down.
 */
import { test, expect } from "bun:test";
import { v, table, createDb, createTableSql, paginate, type Dialect } from "../src/index.ts";
import { bunSql } from "../src/adapters/bunsql.ts";

const users = table<{ id: number; email: string; name: string; active: boolean }>("users", {
  id: v.id(),
  email: v.string().unique(),
  name: v.string(),
  active: v.boolean().default(true),
});

const targets: { dialect: Dialect; url: string }[] = [];
if (process.env.WRNEXUS_PG_URL)
  targets.push({ dialect: "postgres", url: process.env.WRNEXUS_PG_URL });
if (process.env.WRNEXUS_MYSQL_URL)
  targets.push({ dialect: "mysql", url: process.env.WRNEXUS_MYSQL_URL });

const ph = (dialect: Dialect, i: number) => (dialect === "postgres" ? `$${i}` : "?");

if (targets.length === 0) {
  test.skip("live PG/MySQL (set WRNEXUS_PG_URL / WRNEXUS_MYSQL_URL to run)", () => {});
} else {
  for (const { dialect, url } of targets) {
    test(`${dialect}: DDL + CRUD + transaction + pagination`, async () => {
      const db = createDb(bunSql(url, dialect));
      try {
        await db.exec("DROP TABLE IF EXISTS users");
        await db.exec(createTableSql(users, dialect));

        for (let i = 1; i <= 5; i++) {
          await db.exec(
            `INSERT INTO users (email, name, active) VALUES (${ph(dialect, 1)}, ${ph(dialect, 2)}, ${ph(dialect, 3)})`,
            [`u${i}@x.com`, `U${i}`, true],
          );
        }

        const count = await db.one<{ n: number | string }>("SELECT COUNT(*) AS n FROM users");
        expect(Number(count?.n)).toBe(5);

        // Transaction rollback leaves the table unchanged.
        await db
          .tx(async (t) => {
            await t.exec(
              `INSERT INTO users (email, name, active) VALUES (${ph(dialect, 1)}, ${ph(dialect, 2)}, ${ph(dialect, 3)})`,
              ["rollback@x.com", "R", true],
            );
            throw new Error("rollback");
          })
          .catch(() => {});
        const after = await db.one<{ n: number | string }>("SELECT COUNT(*) AS n FROM users");
        expect(Number(after?.n)).toBe(5);

        const page = await paginate(
          db,
          { sql: "SELECT * FROM users ORDER BY id", model: users },
          { page: 1, perPage: 2 },
        );
        expect(page.total).toBe(5);
        expect(page.totalPages).toBe(3);
        expect(page.items.length).toBe(2);

        await db.exec("DROP TABLE IF EXISTS users");
      } finally {
        await db.close();
      }
    });
  }
}