-- Full-Text Search for leads table
-- Enables fast text search across first_name, last_name, email, company
-- Uses PostgreSQL tsvector with GIN index for optimal performance

-- Add generated text search column
ALTER TABLE leads ADD COLUMN IF NOT EXISTS search_text tsvector GENERATED ALWAYS AS (
  to_tsvector('english',
    coalesce(first_name, '') || ' ' ||
    coalesce(last_name, '') || ' ' ||
    coalesce(email, '') || ' ' ||
    coalesce(company, '') || ' ' ||
    coalesce(phone, '')
  )
) STORED;

-- Create GIN index for fast full-text search
CREATE INDEX IF NOT EXISTS idx_leads_search_text ON leads USING GIN(search_text);

-- Usage in queries:
-- SELECT * FROM leads WHERE search_text @@ to_tsquery('english', 'john & smith')
-- Or for partial matching: WHERE search_text @@ to_tsquery('english', 'joh:*')
