-- Move plan ownership from individual shops to an admin-level subscription.
CREATE TABLE "admin_subscriptions" (
    "id" TEXT NOT NULL,
    "adminId" TEXT NOT NULL,
    "planId" TEXT NOT NULL,
    "planExpiresAt" TIMESTAMP(3),
    "scheduledPlanCode" TEXT,
    "scheduledPlanAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "admin_subscriptions_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "admin_subscriptions_adminId_key" ON "admin_subscriptions"("adminId");
CREATE INDEX "admin_subscriptions_planExpiresAt_idx" ON "admin_subscriptions"("planExpiresAt");
CREATE INDEX "admin_subscriptions_scheduledPlanAt_idx" ON "admin_subscriptions"("scheduledPlanAt");

ALTER TABLE "admin_subscriptions"
  ADD CONSTRAINT "admin_subscriptions_adminId_fkey"
  FOREIGN KEY ("adminId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "admin_subscriptions"
  ADD CONSTRAINT "admin_subscriptions_planId_fkey"
  FOREIGN KEY ("planId") REFERENCES "plans"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

WITH ranked_shop_plans AS (
  SELECT DISTINCT ON (s."adminId")
    s."adminId",
    s."planId",
    s."planExpiresAt",
    s."scheduledPlanCode",
    s."scheduledPlanAt",
    s."createdAt"
  FROM "shops" s
  JOIN "plans" p ON p."id" = s."planId"
  ORDER BY
    s."adminId",
    p."priceMonthly" DESC,
    s."planExpiresAt" DESC NULLS LAST,
    s."createdAt" ASC
)
INSERT INTO "admin_subscriptions" (
  "id",
  "adminId",
  "planId",
  "planExpiresAt",
  "scheduledPlanCode",
  "scheduledPlanAt",
  "createdAt",
  "updatedAt"
)
SELECT
  'adminsub_' || rsp."adminId",
  rsp."adminId",
  rsp."planId",
  rsp."planExpiresAt",
  rsp."scheduledPlanCode",
  rsp."scheduledPlanAt",
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
FROM ranked_shop_plans rsp;

DROP INDEX IF EXISTS "shops_planExpiresAt_idx";
DROP INDEX IF EXISTS "shops_scheduledPlanAt_idx";

ALTER TABLE "shops" DROP CONSTRAINT IF EXISTS "shops_planId_fkey";
ALTER TABLE "shops"
  DROP COLUMN IF EXISTS "planId",
  DROP COLUMN IF EXISTS "planExpiresAt",
  DROP COLUMN IF EXISTS "scheduledPlanCode",
  DROP COLUMN IF EXISTS "scheduledPlanAt";