-- ============================================
-- Call List Smart Prioritization Migration
-- Created: 2026-01-16
-- Purpose: Add fields for AI-powered call list ranking
-- ============================================

-- Add priority scoring fields to leads table
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_list_score INTEGER DEFAULT 0;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_list_reason TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_list_reason_type TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_list_warning TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS call_list_updated_at TIMESTAMPTZ;

-- Add last inbound tracking for urgency calculations
ALTER TABLE leads ADD COLUMN IF NOT EXISTS last_inbound_at TIMESTAMPTZ;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS last_inbound_type TEXT;

-- Index for fast call list sorting
CREATE INDEX IF NOT EXISTS idx_leads_call_list_score ON leads(call_list_score DESC)
WHERE deleted_at IS NULL AND status NOT IN ('converted', 'opted_out', 'archived');

-- Index for finding stale hot leads quickly
CREATE INDEX IF NOT EXISTS idx_leads_hot_stale ON leads(last_contact_at)
WHERE score >= 70 AND deleted_at IS NULL AND status NOT IN ('converted', 'opted_out', 'archived');

-- Index for inbound recency lookups
CREATE INDEX IF NOT EXISTS idx_leads_last_inbound ON leads(last_inbound_at DESC)
WHERE last_inbound_at IS NOT NULL;

-- ============================================
-- Comments explaining each field
-- ============================================
COMMENT ON COLUMN leads.call_list_score IS 'Computed priority score 0-1000 for call list sorting';
COMMENT ON COLUMN leads.call_list_reason IS 'Human-readable explanation: "Replied 12 mins ago - strike now!"';
COMMENT ON COLUMN leads.call_list_reason_type IS 'Category: urgency, intent, timing, pattern, score';
COMMENT ON COLUMN leads.call_list_warning IS 'Warning flag: going_cold, callback_overdue, stale_48hrs';
COMMENT ON COLUMN leads.call_list_updated_at IS 'When priority was last computed';
COMMENT ON COLUMN leads.last_inbound_at IS 'Timestamp of most recent inbound SMS or call';
COMMENT ON COLUMN leads.last_inbound_type IS 'Type of last inbound: sms_inbound, call_inbound';

-- ============================================
-- Verification query (run after migration)
-- ============================================
-- SELECT column_name, data_type, column_default
-- FROM information_schema.columns
-- WHERE table_name = 'leads'
-- AND column_name LIKE 'call_list%' OR column_name LIKE 'last_inbound%';
