-- RateRight Growth Engine - Contractor Referral Migration
-- Adds referral tracking for contractor leads extracted from worker calls
-- Run this in Supabase SQL Editor

-- Add referral tracking fields to leads table
ALTER TABLE leads ADD COLUMN IF NOT EXISTS referred_by_lead_id UUID REFERENCES leads(id);
ALTER TABLE leads ADD COLUMN IF NOT EXISTS referral_context TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS is_hot_referral BOOLEAN DEFAULT false;

-- Index for efficient referral lookups
CREATE INDEX IF NOT EXISTS idx_leads_referred_by ON leads(referred_by_lead_id);
CREATE INDEX IF NOT EXISTS idx_leads_hot_referral ON leads(is_hot_referral) WHERE is_hot_referral = true;

-- Verify columns were added
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'leads'
AND column_name IN ('referred_by_lead_id', 'referral_context', 'is_hot_referral');
