-- Platform Activation Command Center Migration
-- Adds lifecycle tracking for worker/contractor activation funnels

-- ===========================================
-- PART 1: Add platform lifecycle fields to leads
-- ===========================================

-- Platform stage tracking
ALTER TABLE leads ADD COLUMN IF NOT EXISTS platform_stage VARCHAR(50);
ALTER TABLE leads ADD COLUMN IF NOT EXISTS platform_data JSONB;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS platform_health_score INTEGER DEFAULT 0;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS stage_changed_at TIMESTAMPTZ;

-- Indexes for stage queries
CREATE INDEX IF NOT EXISTS idx_leads_platform_stage ON leads(platform_stage);
CREATE INDEX IF NOT EXISTS idx_leads_health_score ON leads(platform_health_score DESC);

-- ===========================================
-- PART 2: Stage transitions audit table
-- ===========================================

CREATE TABLE IF NOT EXISTS stage_transitions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  from_stage VARCHAR(50),
  to_stage VARCHAR(50),
  triggered_by VARCHAR(50), -- 'platform_sync', 'manual', 'ai_detection'
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_stage_transitions_lead ON stage_transitions(lead_id);
CREATE INDEX IF NOT EXISTS idx_stage_transitions_date ON stage_transitions(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_stage_transitions_to_stage ON stage_transitions(to_stage);

-- ===========================================
-- PART 3: Activation metrics (daily rollups)
-- ===========================================

CREATE TABLE IF NOT EXISTS activation_metrics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  date DATE NOT NULL,
  lead_type VARCHAR(20), -- 'worker', 'contractor'
  stage VARCHAR(50),
  count_at_stage INTEGER DEFAULT 0,
  count_activated INTEGER DEFAULT 0, -- moved to next stage
  count_stuck INTEGER DEFAULT 0, -- 7+ days no progress
  avg_days_in_stage DECIMAL(5,2),
  revenue_at_risk DECIMAL(12,2),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(date, lead_type, stage)
);

CREATE INDEX IF NOT EXISTS idx_activation_metrics_date ON activation_metrics(date DESC);
CREATE INDEX IF NOT EXISTS idx_activation_metrics_stage ON activation_metrics(stage);

-- ===========================================
-- PART 4: Comments for documentation
-- ===========================================

COMMENT ON COLUMN leads.platform_stage IS 'Current lifecycle stage: not_signed_up, signed_up, profile_complete, job_ready/stripe_ready, applied/job_posted, working/hired, proven/active';
COMMENT ON COLUMN leads.platform_data IS 'Full platform API data (35 fields) stored as JSONB';
COMMENT ON COLUMN leads.platform_health_score IS 'Health score 0-100 based on recency, activity, payment status';
COMMENT ON COLUMN leads.stage_changed_at IS 'When the platform_stage last changed';

COMMENT ON TABLE stage_transitions IS 'Audit trail of all stage changes for funnel analysis';
COMMENT ON TABLE activation_metrics IS 'Daily rollup of activation funnel metrics';
