-- RateRight Growth Engine - Critical Fixes Migration
-- Consolidates all schema changes from critical fixes audit
-- Run this in Supabase SQL Editor

-- ============================================
-- 1. SMS TEMPLATES - Add description column
-- ============================================
ALTER TABLE sms_templates ADD COLUMN IF NOT EXISTS description TEXT;

-- ============================================
-- 2. SMS TEMPLATES - Request Contact Template
-- ============================================
INSERT INTO sms_templates (name, slug, content, description, is_active)
VALUES (
  'Request Contact',
  'request_contact',
  'Hey {{first_name}}, who does the hiring at your company? If you text me their name and number I''ll keep it between us 👍',
  'Request hiring manager contact info from workers',
  true
) ON CONFLICT (slug) DO UPDATE SET
  content = EXCLUDED.content,
  description = EXCLUDED.description,
  is_active = true;

-- ============================================
-- 3. LEADS - Contact Request Tracking
-- ============================================
ALTER TABLE leads ADD COLUMN IF NOT EXISTS contact_request_pending BOOLEAN DEFAULT false;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS contact_request_sent_at TIMESTAMPTZ;

-- Index for finding pending contact requests
CREATE INDEX IF NOT EXISTS idx_leads_contact_request_pending
  ON leads(contact_request_pending)
  WHERE contact_request_pending = true;

-- ============================================
-- 4. LEADS - Referral Tracking (for contractor extraction)
-- ============================================
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;

-- Indexes for 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;

-- ============================================
-- 5. VERIFY CHANGES
-- ============================================
SELECT 'sms_templates columns:' as check_type, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'sms_templates'
AND column_name = 'description'

UNION ALL

SELECT 'leads columns:' as check_type, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'leads'
AND column_name IN ('contact_request_pending', 'contact_request_sent_at', 'referred_by_lead_id', 'referral_context', 'is_hot_referral')

ORDER BY check_type, column_name;
