-- 0076_affiliate_tiers.sql
-- Add tier columns to referral_settings (% of sale, 1–10)
-- Add resolved_pct to credit_ledger for audit trail

ALTER TABLE referral_settings
  ADD COLUMN IF NOT EXISTS tier1_pct       SMALLINT NOT NULL DEFAULT 3
    CHECK (tier1_pct >= 1 AND tier1_pct <= 10),
  ADD COLUMN IF NOT EXISTS tier2_pct       SMALLINT NOT NULL DEFAULT 4
    CHECK (tier2_pct >= 1 AND tier2_pct <= 10),
  ADD COLUMN IF NOT EXISTS tier3_pct       SMALLINT NOT NULL DEFAULT 5
    CHECK (tier3_pct >= 1 AND tier3_pct <= 10),
  ADD COLUMN IF NOT EXISTS tier2_min_sales INTEGER  NOT NULL DEFAULT 20,
  ADD COLUMN IF NOT EXISTS tier3_min_sales INTEGER  NOT NULL DEFAULT 50,
  ADD COLUMN IF NOT EXISTS referral_pct    SMALLINT NOT NULL DEFAULT 2
    CHECK (referral_pct >= 1 AND referral_pct <= 10);

ALTER TABLE credit_ledger
  ADD COLUMN IF NOT EXISTS resolved_pct SMALLINT;
-- NULL for non-earn entries; filled for affiliate_commission and referral_reward rows.
