-- apps/web/src/server/db/migrations/0073_wallet_affiliate_tables.sql
-- Apply via Neon MCP run_sql against project rough-brook-49654702.

-- wallet_balances: add pending/matured split
ALTER TABLE wallet_balances
  ADD COLUMN IF NOT EXISTS pending_agorot BIGINT NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS matured_agorot BIGINT NOT NULL DEFAULT 0;

-- Backfill: existing balance_agorot all goes to matured (legacy earns have no hold).
UPDATE wallet_balances SET matured_agorot = balance_agorot, pending_agorot = 0;

-- purchases: ref_attribution_id
ALTER TABLE purchases
  ADD COLUMN IF NOT EXISTS ref_attribution_id UUID REFERENCES referrals(id);

CREATE INDEX IF NOT EXISTS purchases_ref_attribution_idx
  ON purchases(ref_attribution_id)
  WHERE ref_attribution_id IS NOT NULL;

-- affiliate_payouts
DO $$ BEGIN
  CREATE TYPE affiliate_payout_status AS ENUM (
    'requested','approved','processing','paid','failed','cancelled'
  );
  EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

CREATE TABLE IF NOT EXISTS affiliate_payouts (
  id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id              UUID NOT NULL REFERENCES users(id),
  enrollment_id        UUID NOT NULL REFERENCES affiliate_enrollments(id),
  amount_agorot        BIGINT NOT NULL CHECK (amount_agorot > 0),
  status               affiliate_payout_status NOT NULL,
  requested_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  approved_at          TIMESTAMPTZ,
  approved_by_user_id  UUID REFERENCES users(id),
  processing_at        TIMESTAMPTZ,
  paid_at              TIMESTAMPTZ,
  failed_at            TIMESTAMPTZ,
  failure_reason       TEXT,
  stripe_transfer_id   TEXT UNIQUE,
  stripe_payout_id     TEXT UNIQUE,
  idempotency_key      TEXT NOT NULL UNIQUE,
  ledger_entry_id      UUID UNIQUE REFERENCES credit_ledger(id)
);

CREATE INDEX IF NOT EXISTS affiliate_payouts_user_idx
  ON affiliate_payouts(user_id, requested_at DESC);

CREATE INDEX IF NOT EXISTS affiliate_payouts_status_idx
  ON affiliate_payouts(status);

-- affiliate_admin_actions
CREATE TABLE IF NOT EXISTS affiliate_admin_actions (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ts              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  admin_user_id   UUID NOT NULL REFERENCES users(id),
  target_user_id  UUID REFERENCES users(id),
  action          TEXT NOT NULL,
  payload         JSONB NOT NULL,
  reason          TEXT
);

CREATE INDEX IF NOT EXISTS affiliate_admin_actions_target_idx
  ON affiliate_admin_actions(target_user_id, ts DESC);

-- referral_link_stats_daily
CREATE TABLE IF NOT EXISTS referral_link_stats_daily (
  link_id              UUID NOT NULL REFERENCES referral_links(id),
  day                  DATE NOT NULL,
  clicks               INTEGER NOT NULL DEFAULT 0,
  clicks_suspicious    INTEGER NOT NULL DEFAULT 0,
  signups              INTEGER NOT NULL DEFAULT 0,
  purchases            INTEGER NOT NULL DEFAULT 0,
  commission_agorot    BIGINT NOT NULL DEFAULT 0,
  reward_agorot        BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (link_id, day)
);

CREATE INDEX IF NOT EXISTS referral_link_stats_daily_day_idx
  ON referral_link_stats_daily(day);
