// Real-DB tests (DB_TESTS=1) for the sec audit A2 seats backstop:
//   - `syncOrgSeatsLimit` writes the right `organization.seats_limit` for
//     every point the resolved seats ceiling can change (plan, trial,
//     override, unlimited).
//   - the `BEFORE INSERT ON member` trigger (drizzle/0060) actually closes
//     the TOCTOU: N concurrent inserts racing the LAST seat → exactly 1
//     lands, same "N racers" pattern as quotas.test.ts / mcp/quota.test.ts.
// checkInvitationSeatBlock/the app-level checks are NOT re-tested here (see
// seat-guard.test.ts) — this file proves the DB-level backstop specifically.
import { beforeEach, describe, expect, test } from "bun:test";
import { count, eq } from "drizzle-orm";

const RUN_DB = process.env.DB_TESTS === "1";

describe.skipIf(!RUN_DB)("seats-limit (postgres)", () => {
  const ORG = "seats-race-org";
  const USER_PREFIX = "seats-race-user-";

  let db: typeof import("@/lib/db/client").db;
  let authSchema: typeof import("@/lib/db/auth-schema");
  let adminSchema: typeof import("@/lib/db/admin-schema");
  let seatsLimit: typeof import("./seats-limit");

  async function insertUser(id: string): Promise<void> {
    await db.insert(authSchema.user).values({
      id,
      name: id,
      email: `${id}@test.dev`,
      emailVerified: true,
      createdAt: new Date(),
      updatedAt: new Date(),
    });
  }

  async function insertMember(id: string, userId: string): Promise<void> {
    await db.insert(authSchema.member).values({
      id,
      organizationId: ORG,
      userId,
      role: "member",
      createdAt: new Date(),
    });
  }

  beforeEach(async () => {
    ({ db } = await import("@/lib/db/client"));
    authSchema = await import("@/lib/db/auth-schema");
    adminSchema = await import("@/lib/db/admin-schema");
    seatsLimit = await import("./seats-limit");
    const { assertTestDb } = await import("@/lib/db/assert-test-db");
    await assertTestDb();

    // Clean slate: organization delete cascades to member + org_limit_overrides
    // (both FK organization.id ON DELETE CASCADE), but NOT subscription
    // (referenceId is a plain text column, not an FK) or user — clear those
    // explicitly.
    await db
      .delete(authSchema.subscription)
      .where(eq(authSchema.subscription.referenceId, ORG));
    await db
      .delete(authSchema.organization)
      .where(eq(authSchema.organization.id, ORG));
    const staleUsers = await db
      .select({ id: authSchema.user.id })
      .from(authSchema.user);
    for (const u of staleUsers) {
      if (u.id.startsWith(USER_PREFIX)) {
        await db.delete(authSchema.user).where(eq(authSchema.user.id, u.id));
      }
    }
    await db.insert(authSchema.organization).values({
      id: ORG,
      name: "Seats Race Org",
      slug: `seats-race-org-${Date.now()}`,
      createdAt: new Date(),
    });
  });

  describe("syncOrgSeatsLimit", () => {
    test("no plan, no trial, no override → floors to 1 (never blocks the founding owner)", async () => {
      await seatsLimit.syncOrgSeatsLimit(ORG);
      const [org] = await db
        .select({ seatsLimit: authSchema.organization.seatsLimit })
        .from(authSchema.organization)
        .where(eq(authSchema.organization.id, ORG));
      expect(org.seatsLimit).toBe(1);
    });

    test("active trial (no subscription) → Pro seat count (3), not halved", async () => {
      await db
        .update(authSchema.organization)
        .set({ trialEndsAt: new Date(Date.now() + 86_400_000) })
        .where(eq(authSchema.organization.id, ORG));
      await seatsLimit.syncOrgSeatsLimit(ORG);
      const [org] = await db
        .select({ seatsLimit: authSchema.organization.seatsLimit })
        .from(authSchema.organization)
        .where(eq(authSchema.organization.id, ORG));
      expect(org.seatsLimit).toBe(3);
    });

    test("active agency subscription → 10 seats", async () => {
      await db.insert(authSchema.subscription).values({
        id: "seats-race-sub-1",
        plan: "agency",
        referenceId: ORG,
        status: "active",
      });
      await seatsLimit.syncOrgSeatsLimit(ORG);
      const [org] = await db
        .select({ seatsLimit: authSchema.organization.seatsLimit })
        .from(authSchema.organization)
        .where(eq(authSchema.organization.id, ORG));
      expect(org.seatsLimit).toBe(10);
    });

    test("expired subscription + active trial falls back to trial (Pro=3)", async () => {
      await db
        .update(authSchema.organization)
        .set({ trialEndsAt: new Date(Date.now() + 86_400_000) })
        .where(eq(authSchema.organization.id, ORG));
      await db.insert(authSchema.subscription).values({
        id: "seats-race-sub-2",
        plan: "solo",
        referenceId: ORG,
        status: "canceled",
      });
      await seatsLimit.syncOrgSeatsLimit(ORG);
      const [org] = await db
        .select({ seatsLimit: authSchema.organization.seatsLimit })
        .from(authSchema.organization)
        .where(eq(authSchema.organization.id, ORG));
      expect(org.seatsLimit).toBe(3);
    });

    test("a non-expired seats override wins outright over the plan", async () => {
      await db.insert(authSchema.subscription).values({
        id: "seats-race-sub-3",
        plan: "solo",
        referenceId: ORG,
        status: "active",
      });
      await db.insert(adminSchema.orgLimitOverrides).values({
        organizationId: ORG,
        seats: 42,
      });
      await seatsLimit.syncOrgSeatsLimit(ORG);
      const [org] = await db
        .select({ seatsLimit: authSchema.organization.seatsLimit })
        .from(authSchema.organization)
        .where(eq(authSchema.organization.id, ORG));
      expect(org.seatsLimit).toBe(42);
    });

    test("an expired override is ignored (falls back to plan)", async () => {
      await db.insert(authSchema.subscription).values({
        id: "seats-race-sub-4",
        plan: "starter",
        referenceId: ORG,
        status: "active",
      });
      await db.insert(adminSchema.orgLimitOverrides).values({
        organizationId: ORG,
        seats: 99,
        expiresAt: new Date(Date.now() - 86_400_000),
      });
      await seatsLimit.syncOrgSeatsLimit(ORG);
      const [org] = await db
        .select({ seatsLimit: authSchema.organization.seatsLimit })
        .from(authSchema.organization)
        .where(eq(authSchema.organization.id, ORG));
      expect(org.seatsLimit).toBe(1); // starter plan seats = 1
    });

    test("seats: -1 override → NULL (unlimited)", async () => {
      await db.insert(adminSchema.orgLimitOverrides).values({
        organizationId: ORG,
        seats: -1,
      });
      await seatsLimit.syncOrgSeatsLimit(ORG);
      const [org] = await db
        .select({ seatsLimit: authSchema.organization.seatsLimit })
        .from(authSchema.organization)
        .where(eq(authSchema.organization.id, ORG));
      expect(org.seatsLimit).toBeNull();
    });
  });

  describe("member insert trigger (postgres, concurrency)", () => {
    test("seats_limit NULL (unlimited) never blocks a member insert", async () => {
      const RACERS = 6;
      // seats_limit stays NULL (default) — no sync call.
      for (let i = 0; i < RACERS; i++) {
        await insertUser(`${USER_PREFIX}unlimited-racer-${i}`);
      }
      const results = await Promise.allSettled(
        Array.from({ length: RACERS }, (_, i) =>
          insertMember(
            `seats-race-member-unl-${i}`,
            `${USER_PREFIX}unlimited-racer-${i}`,
          ),
        ),
      );
      const fulfilled = results.filter((r) => r.status === "fulfilled");
      expect(fulfilled.length).toBe(RACERS);
    });

    test("concurrency: K inserts racing the LAST seat → exactly 1 lands", async () => {
      const LIMIT = 5;
      const RACERS = 12;

      await seatsLimit.syncOrgSeatsLimit(ORG); // floors to 1 first
      await db
        .update(authSchema.organization)
        .set({ seatsLimit: LIMIT })
        .where(eq(authSchema.organization.id, ORG));

      // Pre-fill LIMIT - 1 members — the racers fight over the ONE free seat.
      for (let i = 0; i < LIMIT - 1; i++) {
        const userId = `${USER_PREFIX}existing-${i}`;
        await insertUser(userId);
        await insertMember(`seats-race-member-existing-${i}`, userId);
      }

      for (let i = 0; i < RACERS; i++) {
        await insertUser(`${USER_PREFIX}racer-${i}`);
      }

      const results = await Promise.allSettled(
        Array.from({ length: RACERS }, (_, i) =>
          insertMember(
            `seats-race-member-racer-${i}`,
            `${USER_PREFIX}racer-${i}`,
          ),
        ),
      );

      const fulfilled = results.filter((r) => r.status === "fulfilled");
      const rejected = results.filter((r) => r.status === "rejected");
      expect(fulfilled.length).toBe(1);
      expect(rejected.length).toBe(RACERS - 1);
      for (const r of rejected) {
        // drizzle wraps the raw postgres.js error in a DrizzleQueryError —
        // the trigger's RAISE EXCEPTION (ERRCODE check_violation = 23514,
        // HINT SEAT_LIMIT_REACHED) surfaces on `.cause`.
        const reason = (r as PromiseRejectedResult).reason as {
          cause?: { code?: string; hint?: string; message?: string };
        };
        expect(reason.cause?.code).toBe("23514");
        expect(reason.cause?.hint).toBe("SEAT_LIMIT_REACHED");
      }

      const [row] = await db
        .select({ n: count() })
        .from(authSchema.member)
        .where(eq(authSchema.member.organizationId, ORG));
      expect(row?.n ?? 0).toBe(LIMIT); // exactly LIMIT members, not LIMIT - 1 + RACERS
    });
  });
});