-- Website Signup Real-Time Pipeline Migration
-- Adds fields for tracking website signups and speed-to-lead metrics

-- ===========================================
-- PART 1: Leads table extensions
-- ===========================================

-- Track signup source and timing
ALTER TABLE leads ADD COLUMN IF NOT EXISTS signup_source VARCHAR(50);
ALTER TABLE leads ADD COLUMN IF NOT EXISTS signup_at TIMESTAMPTZ;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS signup_message TEXT;

-- Track first contact timing (for speed-to-lead)
ALTER TABLE leads ADD COLUMN IF NOT EXISTS first_contact_at TIMESTAMPTZ;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS first_contact_channel VARCHAR(20);

-- Re-engagement tracking
ALTER TABLE leads ADD COLUMN IF NOT EXISTS is_re_engaged BOOLEAN DEFAULT false;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS re_engaged_at TIMESTAMPTZ;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS re_engaged_insight TEXT;

-- Index for fast website signup queries
CREATE INDEX IF NOT EXISTS idx_leads_signup_at ON leads(signup_at DESC) WHERE signup_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_leads_source ON leads(source) WHERE source = 'website';

-- ===========================================
-- PART 2: Speed-to-lead metrics table
-- ===========================================

CREATE TABLE IF NOT EXISTS speed_to_lead (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,

  -- Key timestamps
  signup_at TIMESTAMPTZ NOT NULL,
  first_contact_at TIMESTAMPTZ,
  first_response_at TIMESTAMPTZ,
  converted_at TIMESTAMPTZ,

  -- Calculated metrics (in minutes for easy querying)
  minutes_to_contact INTEGER,
  minutes_to_response INTEGER,
  minutes_to_conversion INTEGER,

  -- Contact details
  contact_channel VARCHAR(20), -- 'call', 'sms', 'email'
  contact_attempts INTEGER DEFAULT 0,
  contacted_by UUID, -- user who made first contact

  -- Outcome tracking
  outcome VARCHAR(50), -- 'converted', 'lost', 'pending'

  -- Metadata
  signup_source VARCHAR(50), -- 'homepage', 'pricing_page', etc.
  utm_source VARCHAR(100),
  utm_campaign VARCHAR(100),

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for speed-to-lead analytics
CREATE INDEX IF NOT EXISTS idx_stl_signup_at ON speed_to_lead(signup_at DESC);
CREATE INDEX IF NOT EXISTS idx_stl_lead_id ON speed_to_lead(lead_id);
CREATE INDEX IF NOT EXISTS idx_stl_outcome ON speed_to_lead(outcome);

-- ===========================================
-- PART 3: Comments for documentation
-- ===========================================

COMMENT ON COLUMN leads.signup_source IS 'Where on the website they signed up (homepage, pricing, etc)';
COMMENT ON COLUMN leads.signup_at IS 'When they submitted the website form';
COMMENT ON COLUMN leads.signup_message IS 'Message they included in the form';
COMMENT ON COLUMN leads.first_contact_at IS 'When we first reached out after signup';
COMMENT ON COLUMN leads.first_contact_channel IS 'How we first contacted them (call/sms)';
COMMENT ON COLUMN leads.is_re_engaged IS 'True if this lead signed up again after previous contact';
COMMENT ON COLUMN leads.re_engaged_at IS 'When they re-engaged (signed up again)';
COMMENT ON COLUMN leads.re_engaged_insight IS 'AI-generated insight about why they came back';

COMMENT ON TABLE speed_to_lead IS 'Tracks time from website signup to first contact and conversion';
