ALTER TABLE "licenses" ADD COLUMN "user_id" UUID;

ALTER TABLE "licenses"
ADD CONSTRAINT "licenses_user_id_fkey"
FOREIGN KEY ("user_id") REFERENCES "users"("id")
ON DELETE RESTRICT ON UPDATE CASCADE;

CREATE INDEX "licenses_user_id_idx" ON "licenses"("user_id");

UPDATE "licenses" AS license
SET "user_id" = candidate."user_id"
FROM (
    SELECT subscription."user_id", subscription."plugin"
    FROM "subscriptions" AS subscription
    WHERE subscription."status" = 'active'
    GROUP BY subscription."user_id", subscription."plugin"
    HAVING COUNT(*) = 1
) AS candidate
WHERE license."user_id" IS NULL
  AND license."plugin" = candidate."plugin"
  AND (
      SELECT COUNT(*)
      FROM (
          SELECT DISTINCT matching."user_id"
          FROM "subscriptions" AS matching
          WHERE matching."plugin" = license."plugin"
            AND matching."status" = 'active'
      ) AS owners
  ) = 1;
