-- Sec audit A2 backstop (ELEVA-112 S4) — see auth-schema.ts's `seatsLimit` doc.
-- `checkInvitationSeatBlock`/the invite-member hook (auth.ts) are
-- read-then-decide with no lock, and the actual member insert is 100% owned
-- by Better Auth outside any transaction we control — an app-level advisory
-- lock (the P8 import fix, quotas.ts `lockOrgForQuota`) can't wrap it. Only a
-- DB trigger sees the count-then-write as one atomic unit.
ALTER TABLE "organization" ADD COLUMN "seats_limit" integer;
--> statement-breakpoint

-- Backfill (same rationale as 0054's mcp_monthly_usage_counter): every
-- existing org gets its seats_limit resolved from the SAME inputs
-- `resolveFreshSeatsLimit` (src/lib/billing/seats-limit.ts) uses at runtime —
-- current subscription (prefer an access-granting status, else the most
-- recent), else the app-level trial, else expired (0) — with the plan→seats
-- table from plans.ts hardcoded here (solo/starter=1, pro=3, agency=10; a
-- trial always resolves to the Pro seat count, matching resolveQuotaLimits —
-- ONLY keywords/mcpMonthly are halved on trial, never seats), then a
-- non-expired seats override wins outright. Floored to 1 (unless truly
-- unlimited, -1 override → NULL) so this one-time backfill can never lock an
-- org out of its own founding owner — see resolveFreshSeatsLimit's doc for
-- why the floor exists. Any drift after this point is closed by the explicit
-- sync call-sites (org-bootstrap.ts, subscription-sync.ts, billing-actions.ts,
-- org-actions.ts), not by this migration.
WITH latest_sub AS (
  SELECT DISTINCT ON (o.id)
    o.id AS org_id,
    s.status,
    s.plan
  FROM "organization" o
  LEFT JOIN "subscription" s ON s.reference_id = o.id
  ORDER BY
    o.id,
    (CASE WHEN s.status IN ('active', 'trialing', 'past_due') THEN 0 ELSE 1 END),
    s.period_start DESC NULLS LAST
),
resolved AS (
  SELECT
    ls.org_id,
    CASE
      WHEN ls.status IN ('active', 'trialing', 'past_due') THEN
        CASE ls.plan
          WHEN 'solo' THEN 1
          WHEN 'starter' THEN 1
          WHEN 'pro' THEN 3
          WHEN 'agency' THEN 10
          ELSE 0
        END
      WHEN o.trial_ends_at IS NOT NULL AND o.trial_ends_at > now() THEN 3
      ELSE 0
    END AS plan_seats,
    ov.seats AS override_seats,
    ov.expires_at AS override_expires_at
  FROM latest_sub ls
  JOIN "organization" o ON o.id = ls.org_id
  LEFT JOIN "org_limit_overrides" ov ON ov.organization_id = ls.org_id
)
UPDATE "organization" o
SET seats_limit = CASE
  WHEN r.override_seats IS NOT NULL
       AND (r.override_expires_at IS NULL OR r.override_expires_at > now())
    THEN CASE WHEN r.override_seats = -1 THEN NULL ELSE GREATEST(r.override_seats, 1) END
  ELSE GREATEST(r.plan_seats, 1)
END
FROM resolved r
WHERE o.id = r.org_id;
--> statement-breakpoint

-- organization_enforce_seats_limit(): BEFORE INSERT ON member trigger. Locks
-- the organization row FIRST (FOR UPDATE) — a concurrent transaction
-- inserting another member for the SAME org blocks here until this one
-- commits or rolls back, so no two transactions can ever read the same
-- pre-insert count and both pass. This row lock (not an advisory lock) is
-- deliberate here, unlike quotas.ts's `lockOrgForQuota`: this trigger fires
-- INSIDE the insert's own transaction (Better Auth's / any caller's), so it
-- has no "unrelated concurrent write" contention problem to avoid — quite the
-- opposite, locking the exact row whose count we're about to depend on is the
-- whole point.
CREATE OR REPLACE FUNCTION organization_enforce_seats_limit()
RETURNS trigger
LANGUAGE plpgsql
SET search_path = public, pg_catalog
AS $$
DECLARE
  seats_limit_val integer;
  member_count integer;
BEGIN
  SELECT seats_limit INTO seats_limit_val
  FROM "organization"
  WHERE id = NEW.organization_id
  FOR UPDATE;

  IF seats_limit_val IS NULL THEN
    RETURN NEW; -- unlimited (no plan resolved yet, or a -1 override)
  END IF;

  SELECT count(*) INTO member_count
  FROM "member"
  WHERE organization_id = NEW.organization_id;

  IF member_count >= seats_limit_val THEN
    RAISE EXCEPTION 'seat limit reached for organization % (limit %, current %)',
      NEW.organization_id, seats_limit_val, member_count
      USING ERRCODE = 'check_violation',
            HINT = 'SEAT_LIMIT_REACHED';
  END IF;

  RETURN NEW;
END;
$$;
--> statement-breakpoint

DROP TRIGGER IF EXISTS member_enforce_seats_limit ON "member";
--> statement-breakpoint

CREATE TRIGGER member_enforce_seats_limit
BEFORE INSERT ON "member"
FOR EACH ROW
EXECUTE FUNCTION organization_enforce_seats_limit();