-- DEDUP (status demotion, NO delete) before enforcing one-active-sub-per-org.
-- For each reference_id with >1 access-granting row (status IN active/trialing/
-- past_due), keep ONE canonical row and demote the others to 'canceled'.
-- Canonical = stripe_subscription_id IS NOT NULL first, then period_start DESC
-- NULLS LAST, then id ASC. Set-based + idempotent: no-op when there are no dups.
UPDATE "subscription" AS s
SET "status" = 'canceled'
FROM (
  SELECT
    "id",
    row_number() OVER (
      PARTITION BY "reference_id"
      ORDER BY
        ("stripe_subscription_id" IS NOT NULL) DESC,
        "period_start" DESC NULLS LAST,
        "id" ASC
    ) AS rn
  FROM "subscription"
  WHERE "status" IN ('active', 'trialing', 'past_due')
) AS ranked
WHERE s."id" = ranked."id"
  AND ranked.rn > 1;
--> statement-breakpoint
CREATE UNIQUE INDEX "subscription_one_active_per_org_uidx" ON "subscription" USING btree ("reference_id") WHERE status IN ('active', 'trialing', 'past_due');