-- ============================================
-- PERSONAL INTEL MIGRATION
-- Adds personal profile fields to lead_intel
-- ============================================

-- Family & Personal
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS family_status TEXT;
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS hobbies TEXT[];
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS interests TEXT[];
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS location_suburb TEXT;
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS personality_notes TEXT;

-- Conversation Helpers
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS conversation_starters TEXT[];
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS personal_news JSONB;

-- Social Profiles
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS social_profiles JSONB;
-- Expected format: { "facebook": "url", "instagram": "url", "twitter": "url" }

-- Add index for leads with personal intel
CREATE INDEX IF NOT EXISTS idx_lead_intel_has_personal
ON lead_intel(lead_id)
WHERE family_status IS NOT NULL OR hobbies IS NOT NULL;
