-- Pipeline Value Tracking Migration
-- Adds estimated_value to leads and pipeline metrics to daily_metrics

-- Add estimated_value field to leads table
-- This tracks the potential deal value before conversion
ALTER TABLE leads ADD COLUMN IF NOT EXISTS estimated_value DECIMAL(10,2) DEFAULT NULL;

-- Add pipeline metrics to daily_metrics table for historical tracking
ALTER TABLE daily_metrics ADD COLUMN IF NOT EXISTS total_pipeline_value DECIMAL(12,2) DEFAULT 0;
ALTER TABLE daily_metrics ADD COLUMN IF NOT EXISTS hot_pipeline_value DECIMAL(12,2) DEFAULT 0;
ALTER TABLE daily_metrics ADD COLUMN IF NOT EXISTS warm_pipeline_value DECIMAL(12,2) DEFAULT 0;
ALTER TABLE daily_metrics ADD COLUMN IF NOT EXISTS converted_value DECIMAL(12,2) DEFAULT 0;

-- Create index for efficient pipeline value queries
CREATE INDEX IF NOT EXISTS idx_leads_estimated_value ON leads(estimated_value) WHERE estimated_value IS NOT NULL AND deleted_at IS NULL;

-- Add comments for documentation
COMMENT ON COLUMN leads.estimated_value IS 'Estimated deal value for this lead (before conversion)';
COMMENT ON COLUMN daily_metrics.total_pipeline_value IS 'Sum of estimated_value for all active leads (warm+hot)';
COMMENT ON COLUMN daily_metrics.hot_pipeline_value IS 'Sum of estimated_value for hot leads (score >= 70)';
COMMENT ON COLUMN daily_metrics.warm_pipeline_value IS 'Sum of estimated_value for warm leads (score 50-69)';
COMMENT ON COLUMN daily_metrics.converted_value IS 'Sum of conversion values for the day';
