CREATE TYPE "AffiliateCpaValidationMode" AS ENUM ('FIRST_DEPOSIT', 'CUMULATIVE_DEPOSIT');

ALTER TABLE "AffiliateLink"
  ADD COLUMN "rewardType" "AffiliateOfferType" NOT NULL DEFAULT 'HYBRID',
  ADD COLUMN "cpaAmount" DECIMAL(18,6),
  ADD COLUMN "revenueShareBps" INTEGER,
  ADD COLUMN "minCpaDepositAmount" DECIMAL(18,6) NOT NULL DEFAULT 0,
  ADD COLUMN "cpaValidationMode" "AffiliateCpaValidationMode" NOT NULL DEFAULT 'FIRST_DEPOSIT',
  ADD COLUMN "cpaWindowDays" INTEGER,
  ADD COLUMN "allowedCountries" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  ADD COLUMN "terms" TEXT;

ALTER TABLE "AffiliateReferral"
  ADD COLUMN "rewardEligible" BOOLEAN NOT NULL DEFAULT true,
  ADD COLUMN "rejectionReason" TEXT,
  ADD COLUMN "depositCount" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN "depositTotal" DECIMAL(18,6) NOT NULL DEFAULT 0,
  ADD COLUMN "firstDepositAt" TIMESTAMP(3),
  ADD COLUMN "firstDepositAmount" DECIMAL(18,6),
  ADD COLUMN "cpaQualifiedAt" TIMESTAMP(3);

UPDATE "AffiliateLink" AS link
SET
  "rewardType" = COALESCE(offer."type", link."rewardType"),
  "cpaAmount" = CASE
    WHEN offer."type" = 'CPA' THEN offer."cpaAmount"
    WHEN offer."type" = 'HYBRID' THEN offer."hybridCpaAmount"
    ELSE link."cpaAmount"
  END,
  "revenueShareBps" = CASE
    WHEN offer."type" = 'RS' THEN offer."revenueShareBps"
    WHEN offer."type" = 'HYBRID' THEN offer."hybridRevenueShareBps"
    ELSE link."revenueShareBps"
  END,
  "minCpaDepositAmount" = COALESCE(offer."minFtdAmount", link."minCpaDepositAmount"),
  "terms" = COALESCE(offer."terms", link."terms")
FROM "AffiliateOffer" AS offer
WHERE link."offerId" = offer."id";

UPDATE "AffiliateReferral"
SET
  "depositCount" = CASE WHEN "ftdAt" IS NOT NULL THEN 1 ELSE 0 END,
  "depositTotal" = COALESCE("ftdAmount", 0),
  "firstDepositAt" = "ftdAt",
  "firstDepositAmount" = "ftdAmount",
  "cpaQualifiedAt" = "qualifiedAt";

CREATE INDEX "AffiliateLink_accountId_status_idx" ON "AffiliateLink"("accountId", "status");
CREATE INDEX "AffiliateReferral_rewardEligible_registeredAt_idx" ON "AffiliateReferral"("rewardEligible", "registeredAt");
CREATE INDEX "AffiliateReferral_cpaQualifiedAt_idx" ON "AffiliateReferral"("cpaQualifiedAt");