-- Request Contact Feature Migration
-- Adds SMS template and tracking for contact requests from workers

-- Insert SMS template for requesting site manager contact
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;

-- Add contact_request_pending flag to track pending requests
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;
