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

DO $$ BEGIN
  CREATE TYPE affiliate_enrollment_status AS ENUM ('active','suspended','revoked');
  EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
  CREATE TYPE affiliate_stripe_status AS ENUM ('none','pending','restricted','enabled','rejected');
  EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

CREATE TABLE IF NOT EXISTS affiliate_enrollments (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id                 UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE RESTRICT,
  status                  affiliate_enrollment_status NOT NULL,
  commission_pct          INTEGER,
  enrolled_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  suspended_at            TIMESTAMPTZ,
  suspended_reason        TEXT,
  notes                   TEXT,
  stripe_account_id       TEXT UNIQUE,
  stripe_status           affiliate_stripe_status NOT NULL DEFAULT 'none',
  stripe_payouts_enabled  BOOLEAN NOT NULL DEFAULT FALSE,
  stripe_updated_at       TIMESTAMPTZ,
  tos_accepted_at         TIMESTAMPTZ NOT NULL,
  tos_version             TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS affiliate_enrollments_status_idx
  ON affiliate_enrollments(status);

CREATE INDEX IF NOT EXISTS affiliate_enrollments_stripe_acct_idx
  ON affiliate_enrollments(stripe_account_id)
  WHERE stripe_account_id IS NOT NULL;
