-- RateRight Growth Engine - Learning System Schema
-- Run this after ai-schema.sql

-- ============================================
-- CONVERSION PATTERNS TABLE
-- Captures the full journey of every converted lead
-- ============================================

CREATE TABLE IF NOT EXISTS conversion_patterns (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,

  -- Lead snapshot at conversion
  lead_name TEXT,
  lead_company TEXT,
  lead_trade VARCHAR(100),
  lead_source VARCHAR(100),
  lead_score_at_conversion INTEGER,

  -- Timeline metrics
  days_to_convert INTEGER,  -- Days from first contact to conversion
  total_touchpoints INTEGER,  -- Total calls + SMS
  calls_made INTEGER,
  sms_sent INTEGER,
  sms_received INTEGER,

  -- What worked
  winning_channel VARCHAR(50),  -- 'call', 'sms', 'both'
  first_contact_type VARCHAR(50),  -- How we first reached them
  final_contact_type VARCHAR(50),  -- What closed the deal

  -- Call data
  total_call_duration_seconds INTEGER,
  avg_call_duration_seconds INTEGER,
  calls_to_connect INTEGER,  -- How many attempts before connecting

  -- Objection handling (THE LEARNING GOLD)
  objections_raised JSONB DEFAULT '[]',  -- [{objection, type, response_used, was_overcome}]
  objection_count INTEGER DEFAULT 0,
  objections_overcome INTEGER DEFAULT 0,

  -- AI assistance used
  intel_briefs_viewed INTEGER DEFAULT 0,
  copilot_sessions INTEGER DEFAULT 0,
  ai_suggestions_shown INTEGER DEFAULT 0,
  ai_suggestions_helpful INTEGER DEFAULT 0,  -- Thumbs up count

  -- Timing patterns
  best_contact_time VARCHAR(50),  -- 'morning', 'afternoon', 'evening'
  best_contact_day VARCHAR(20),  -- 'monday', 'tuesday', etc.
  conversion_hour INTEGER,  -- Hour of day when converted (0-23)
  conversion_day_of_week INTEGER,  -- 0=Sunday, 6=Saturday

  -- Communication excerpts (for learning)
  winning_messages JSONB DEFAULT '[]',  -- SMS messages that got positive responses
  effective_talking_points JSONB DEFAULT '[]',  -- From intel briefs that were used

  -- Full journey data (for deep analysis)
  communication_timeline JSONB DEFAULT '[]',  -- [{type, direction, timestamp, content, outcome}]
  copilot_suggestions_used JSONB DEFAULT '[]',  -- [{suggestion, was_helpful, outcome}]

  -- Conversion details
  conversion_type VARCHAR(50),  -- 'job_posted', 'worker_hired', 'subscription', 'manual'
  conversion_value DECIMAL(10,2),
  converted_by VARCHAR(255),  -- Who closed the deal

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

-- ============================================
-- OBJECTION RESPONSES TABLE
-- Track which responses work for which objections
-- ============================================

CREATE TABLE IF NOT EXISTS objection_responses (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- The objection
  objection_type VARCHAR(50) NOT NULL,  -- price, timing, competition, trust, capacity
  objection_text TEXT,  -- Actual text detected
  objection_keywords JSONB DEFAULT '[]',  -- Keywords that triggered

  -- The response
  response_text TEXT NOT NULL,
  key_point TEXT,
  follow_up_question TEXT,

  -- Effectiveness tracking
  times_shown INTEGER DEFAULT 0,
  times_helpful INTEGER DEFAULT 0,  -- Thumbs up
  times_not_helpful INTEGER DEFAULT 0,  -- Thumbs down
  led_to_conversion INTEGER DEFAULT 0,  -- Used in sessions that converted

  -- Calculated effectiveness score
  effectiveness_score DECIMAL(4,3) DEFAULT 0,  -- 0 to 1

  -- Context
  lead_trade VARCHAR(100),  -- What trade was this effective for?
  lead_source VARCHAR(100),  -- What source leads?

  -- Source
  source VARCHAR(50) DEFAULT 'ai',  -- 'ai', 'manual', 'learned'

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

-- ============================================
-- SUGGESTION FEEDBACK TABLE
-- Thumbs up/down on copilot suggestions
-- ============================================

CREATE TABLE IF NOT EXISTS suggestion_feedback (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- Reference
  copilot_session_id UUID REFERENCES copilot_sessions(id),
  lead_id UUID REFERENCES leads(id),

  -- The suggestion
  objection_detected TEXT,
  objection_type VARCHAR(50),
  suggestion_response TEXT,
  suggestion_key_point TEXT,

  -- Feedback
  was_helpful BOOLEAN,
  feedback_note TEXT,  -- Optional note

  -- Context
  user_email VARCHAR(255),
  call_outcome VARCHAR(50),  -- Did this call convert?

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================
-- WINNING PATTERNS TABLE
-- Auto-generated patterns from conversion_patterns
-- ============================================

CREATE TABLE IF NOT EXISTS winning_patterns (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- Pattern identification
  pattern_type VARCHAR(50) NOT NULL,  -- 'timing', 'messaging', 'objection', 'channel', 'trade'
  pattern_name TEXT NOT NULL,

  -- The insight
  description TEXT NOT NULL,
  recommendation TEXT,

  -- Evidence
  sample_size INTEGER,
  conversion_rate DECIMAL(4,3),  -- For leads matching this pattern
  baseline_rate DECIMAL(4,3),  -- Overall conversion rate for comparison
  lift_percentage DECIMAL(5,2),  -- How much better than baseline

  -- Pattern criteria
  criteria JSONB NOT NULL,  -- What defines this pattern

  -- Examples
  example_leads JSONB DEFAULT '[]',  -- Sample lead IDs

  -- Status
  is_active BOOLEAN DEFAULT true,
  confidence_score DECIMAL(3,2),

  -- When calculated
  period_start DATE,
  period_end DATE,
  calculated_at TIMESTAMPTZ DEFAULT NOW(),

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================
-- INDEXES
-- ============================================

CREATE INDEX IF NOT EXISTS idx_conversion_patterns_lead_id ON conversion_patterns(lead_id);
CREATE INDEX IF NOT EXISTS idx_conversion_patterns_trade ON conversion_patterns(lead_trade);
CREATE INDEX IF NOT EXISTS idx_conversion_patterns_source ON conversion_patterns(lead_source);
CREATE INDEX IF NOT EXISTS idx_conversion_patterns_created ON conversion_patterns(created_at);

CREATE INDEX IF NOT EXISTS idx_objection_responses_type ON objection_responses(objection_type);
CREATE INDEX IF NOT EXISTS idx_objection_responses_effectiveness ON objection_responses(effectiveness_score DESC);
CREATE INDEX IF NOT EXISTS idx_objection_responses_trade ON objection_responses(lead_trade);

CREATE INDEX IF NOT EXISTS idx_suggestion_feedback_session ON suggestion_feedback(copilot_session_id);
CREATE INDEX IF NOT EXISTS idx_suggestion_feedback_lead ON suggestion_feedback(lead_id);
CREATE INDEX IF NOT EXISTS idx_suggestion_feedback_helpful ON suggestion_feedback(was_helpful);

CREATE INDEX IF NOT EXISTS idx_winning_patterns_type ON winning_patterns(pattern_type);
CREATE INDEX IF NOT EXISTS idx_winning_patterns_active ON winning_patterns(is_active);
