ALTER TYPE "LedgerEntryType" ADD VALUE IF NOT EXISTS 'BONUS_RELEASE';

CREATE TYPE "VipRewardKind" AS ENUM ('LEVEL_UP', 'DAILY_CASHBACK', 'WEEKLY_CASHBACK', 'MONTHLY_CASHBACK');

ALTER TABLE "UserBonus"
  ADD COLUMN "remainingAmount" DECIMAL(18,6) NOT NULL DEFAULT 0,
  ADD COLUMN "completedAt" TIMESTAMP(3);

UPDATE "UserBonus"
SET "remainingAmount" = "bonusAmount"
WHERE "status" IN ('OFFERED', 'ACTIVE') AND "remainingAmount" = 0;

CREATE TABLE "VipRewardGrant" (
  "id" UUID NOT NULL,
  "userId" UUID NOT NULL,
  "kind" "VipRewardKind" NOT NULL,
  "tierKey" TEXT,
  "periodKey" TEXT NOT NULL,
  "amount" DECIMAL(18,6) NOT NULL,
  "wagerRequired" DECIMAL(18,6) NOT NULL,
  "userBonusId" UUID,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

CREATE UNIQUE INDEX "VipRewardGrant_userId_kind_periodKey_key" ON "VipRewardGrant"("userId", "kind", "periodKey");
CREATE UNIQUE INDEX "VipRewardGrant_userBonusId_key" ON "VipRewardGrant"("userBonusId");
CREATE INDEX "VipRewardGrant_kind_periodKey_idx" ON "VipRewardGrant"("kind", "periodKey");

ALTER TABLE "VipRewardGrant"
  ALTER COLUMN "id" SET DEFAULT gen_random_uuid();

ALTER TABLE "VipRewardGrant"
  ADD CONSTRAINT "VipRewardGrant_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT "VipRewardGrant_userBonusId_fkey" FOREIGN KEY ("userBonusId") REFERENCES "UserBonus"("id") ON DELETE SET NULL ON UPDATE CASCADE;

INSERT INTO "VipTier" ("id", "key", "name", "minVolume", "cashbackBps", "rakebackBps", "perks", "sortOrder", "createdAt")
VALUES
  (gen_random_uuid(), 'wood', 'Bois', 0, 10, 5, '{"support":"standard","levelBonusAmount":0}', 1, CURRENT_TIMESTAMP),
  (gen_random_uuid(), 'bronze', 'Bronze', 500, 25, 10, '{"support":"standard","levelBonusAmount":5}', 2, CURRENT_TIMESTAMP),
  (gen_random_uuid(), 'silver', 'Argent', 5000, 40, 20, '{"support":"standard","levelBonusAmount":60}', 3, CURRENT_TIMESTAMP),
  (gen_random_uuid(), 'gold', 'Or', 50000, 75, 35, '{"support":"priority","levelBonusAmount":250}', 4, CURRENT_TIMESTAMP),
  (gen_random_uuid(), 'platinum', 'Platine', 150000, 100, 50, '{"support":"priority","levelBonusAmount":500}', 5, CURRENT_TIMESTAMP),
  (gen_random_uuid(), 'jade', 'Jade', 300000, 125, 65, '{"support":"priority","levelBonusAmount":1000}', 6, CURRENT_TIMESTAMP),
  (gen_random_uuid(), 'sapphire', 'Saphir', 1000000, 150, 80, '{"support":"priority","manager":true,"levelBonusAmount":2500}', 7, CURRENT_TIMESTAMP),
  (gen_random_uuid(), 'black', 'Rubis', 5000000, 200, 110, '{"support":"priority","manager":true,"levelBonusAmount":10000}', 8, CURRENT_TIMESTAMP)
ON CONFLICT ("key") DO UPDATE SET
  "name" = EXCLUDED."name",
  "minVolume" = EXCLUDED."minVolume",
  "cashbackBps" = EXCLUDED."cashbackBps",
  "rakebackBps" = EXCLUDED."rakebackBps",
  "perks" = EXCLUDED."perks",
  "sortOrder" = EXCLUDED."sortOrder";