-- Sequence v2 Enhancements
-- Stage-based sequences + randomized timing + auto-enrollment

-- Add stage and lead_type to sequences for matching
ALTER TABLE sequences
ADD COLUMN IF NOT EXISTS stage VARCHAR(50),
ADD COLUMN IF NOT EXISTS lead_type VARCHAR(20);

-- Add randomization columns to steps
ALTER TABLE sequence_steps
ADD COLUMN IF NOT EXISTS delay_days_min INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS delay_days_max INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS send_window VARCHAR(20) DEFAULT 'any';

-- send_window values: 'morning' (6-7:30am), 'lunch' (12-1pm), 'knockoff' (3-6pm), 'any' (8am-6pm)

-- Backfill existing steps: convert delay_hours to delay_days_min/max
UPDATE sequence_steps
SET delay_days_min = COALESCE(FLOOR(delay_hours / 24), 0),
    delay_days_max = COALESCE(FLOOR(delay_hours / 24), 0)
WHERE delay_days_min = 0 AND delay_hours > 0;

-- Add opt-out flag to leads (prevents auto-enrollment)
ALTER TABLE leads
ADD COLUMN IF NOT EXISTS sequence_opted_out BOOLEAN DEFAULT FALSE;

-- Add enrollment_source to track how enrollment happened
ALTER TABLE sequence_enrollments
ADD COLUMN IF NOT EXISTS enrollment_source VARCHAR(50) DEFAULT 'manual';

-- enrollment_source values: 'manual', 'no_answer', 'stage_change', 'import'

-- Index for finding sequence by stage + lead type
CREATE INDEX IF NOT EXISTS idx_sequences_stage_type
ON sequences(stage, lead_type) WHERE is_active = TRUE;

-- Index for finding sequences by lead type
CREATE INDEX IF NOT EXISTS idx_sequences_lead_type
ON sequences(lead_type) WHERE is_active = TRUE;

-- Index for opted-out leads
CREATE INDEX IF NOT EXISTS idx_leads_sequence_opted_out
ON leads(sequence_opted_out) WHERE sequence_opted_out = TRUE;
