-- Migration: 004_affiliate_tables.sql
-- Affiliate system: referral codes, conversions, payouts, wallets
-- Created: 2026-04-12

-- ============================================================
-- Table 1: referral_codes
-- ============================================================
CREATE TABLE IF NOT EXISTS referral_codes (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  owner_user_id UUID NOT NULL REFERENCES auth.users(id),
  affiliate_tier TEXT NOT NULL DEFAULT 'standard' CHECK (affiliate_tier IN ('standard', 'premium', 'founder')),
  commission_pct NUMERIC(5,2) NOT NULL DEFAULT 15.00,
  max_uses INTEGER,
  current_uses INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- Table 2: wallet_addresses (before payouts — FK dependency)
-- ============================================================
CREATE TABLE IF NOT EXISTS wallet_addresses (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  chain TEXT NOT NULL CHECK (chain IN ('ethereum', 'polygon', 'solana', 'bitcoin', 'base', 'arbitrum')),
  address TEXT NOT NULL,
  label TEXT,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- Table 3: referral_conversions
-- ============================================================
CREATE TABLE IF NOT EXISTS referral_conversions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  referral_code_id UUID NOT NULL REFERENCES referral_codes(id),
  referred_user_id UUID NOT NULL REFERENCES auth.users(id),
  referrer_user_id UUID NOT NULL REFERENCES auth.users(id),
  conversion_type TEXT NOT NULL CHECK (conversion_type IN ('signup', 'upgrade', 'renewal')),
  subscription_tier TEXT NOT NULL CHECK (subscription_tier IN ('analyst', 'desk')),
  revenue_amount NUMERIC(10,2) NOT NULL DEFAULT 0,
  commission_amount NUMERIC(10,2) NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'paid', 'disputed', 'refunded')),
  converted_at TIMESTAMPTZ DEFAULT NOW(),
  confirmed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- Table 4: affiliate_payouts
-- ============================================================
CREATE TABLE IF NOT EXISTS affiliate_payouts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  affiliate_user_id UUID NOT NULL REFERENCES auth.users(id),
  payout_amount NUMERIC(10,2) NOT NULL,
  payout_method TEXT NOT NULL CHECK (payout_method IN ('crypto', 'paypal', 'bank_transfer', 'stripe')),
  wallet_address_id UUID REFERENCES wallet_addresses(id),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
  transaction_hash TEXT,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  conversions_included INTEGER NOT NULL DEFAULT 0,
  requested_at TIMESTAMPTZ DEFAULT NOW(),
  processed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- Indexes
-- ============================================================
CREATE INDEX IF NOT EXISTS idx_referral_codes_owner ON referral_codes(owner_user_id);
CREATE INDEX IF NOT EXISTS idx_referral_codes_code ON referral_codes(code);
CREATE INDEX IF NOT EXISTS idx_referral_conversions_code ON referral_conversions(referral_code_id);
CREATE INDEX IF NOT EXISTS idx_referral_conversions_referrer ON referral_conversions(referrer_user_id);
CREATE INDEX IF NOT EXISTS idx_referral_conversions_referred ON referral_conversions(referred_user_id);
CREATE INDEX IF NOT EXISTS idx_referral_conversions_status ON referral_conversions(status);
CREATE INDEX IF NOT EXISTS idx_affiliate_payouts_user ON affiliate_payouts(affiliate_user_id);
CREATE INDEX IF NOT EXISTS idx_affiliate_payouts_status ON affiliate_payouts(status);
CREATE INDEX IF NOT EXISTS idx_wallet_addresses_user ON wallet_addresses(user_id);

-- ============================================================
-- Row Level Security
-- ============================================================
ALTER TABLE referral_codes ENABLE ROW LEVEL SECURITY;
ALTER TABLE referral_conversions ENABLE ROW LEVEL SECURITY;
ALTER TABLE affiliate_payouts ENABLE ROW LEVEL SECURITY;
ALTER TABLE wallet_addresses ENABLE ROW LEVEL SECURITY;

-- referral_codes: public read, service role full access
CREATE POLICY "referral_codes_public_read" ON referral_codes
  FOR SELECT USING (true);

CREATE POLICY "referral_codes_service_write" ON referral_codes
  FOR ALL USING (auth.role() = 'service_role');

-- referral_conversions: service role full access only
CREATE POLICY "referral_conversions_service_access" ON referral_conversions
  FOR ALL USING (auth.role() = 'service_role');

-- affiliate_payouts: service role full access only
CREATE POLICY "affiliate_payouts_service_access" ON affiliate_payouts
  FOR ALL USING (auth.role() = 'service_role');

-- wallet_addresses: service role full access only
CREATE POLICY "wallet_addresses_service_access" ON wallet_addresses
  FOR ALL USING (auth.role() = 'service_role');