-- RateRight Growth Engine - AI Features Schema
-- Run this in Supabase SQL Editor after the main schema

-- ============================================
-- AI FIELDS ON LEADS TABLE
-- ============================================

-- Add AI-powered fields to leads
ALTER TABLE leads ADD COLUMN IF NOT EXISTS ai_conversion_probability DECIMAL(5,2);
ALTER TABLE leads ADD COLUMN IF NOT EXISTS ai_predicted_objections JSONB DEFAULT '[]';
ALTER TABLE leads ADD COLUMN IF NOT EXISTS ai_recommended_action TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS ai_recommended_timing TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS ai_similar_wins JSONB DEFAULT '[]';
ALTER TABLE leads ADD COLUMN IF NOT EXISTS ai_intel_brief JSONB;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS ai_last_analyzed_at TIMESTAMPTZ;

-- ============================================
-- CALL RECORDINGS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS call_recordings (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  communication_id UUID REFERENCES communications(id),

  -- Audio data
  audio_url TEXT,
  audio_duration_seconds INTEGER,

  -- Transcription
  transcript TEXT,
  transcript_segments JSONB DEFAULT '[]',  -- [{start, end, text, speaker}]
  transcription_model VARCHAR(50),

  -- AI Analysis
  ai_summary TEXT,
  ai_key_points JSONB DEFAULT '[]',
  ai_sentiment VARCHAR(20),  -- positive, neutral, negative
  ai_sentiment_score DECIMAL(3,2),
  ai_detected_objections JSONB DEFAULT '[]',
  ai_next_steps TEXT,
  ai_coaching_notes TEXT,

  -- Metadata
  call_direction VARCHAR(20),  -- inbound, outbound
  caller_phone VARCHAR(20),
  recording_source VARCHAR(50),  -- browser, twilio, manual

  created_at TIMESTAMPTZ DEFAULT NOW(),
  processed_at TIMESTAMPTZ
);

-- ============================================
-- AI INSIGHTS TABLE (Weekly Patterns)
-- ============================================

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

  -- Time period
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  insight_type VARCHAR(50) NOT NULL,  -- weekly_summary, conversion_pattern, timing_analysis, objection_analysis

  -- The insight data
  title TEXT NOT NULL,
  description TEXT,
  data JSONB NOT NULL,

  -- Recommendations
  recommendations JSONB DEFAULT '[]',

  -- Metrics
  confidence_score DECIMAL(3,2),
  sample_size INTEGER,

  -- Status
  is_active BOOLEAN DEFAULT true,
  acknowledged_at TIMESTAMPTZ,
  acknowledged_by UUID,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================
-- CONVERSION FACTORS TABLE
-- ============================================

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

  -- Factor details
  factor_name VARCHAR(100) NOT NULL,
  factor_type VARCHAR(50) NOT NULL,  -- demographic, behavioral, engagement, timing

  -- Statistical data
  correlation_score DECIMAL(4,3),  -- -1 to 1
  impact_weight DECIMAL(4,3),
  sample_size INTEGER,
  confidence_interval JSONB,  -- {lower, upper}

  -- Description
  description TEXT,
  positive_example TEXT,
  negative_example TEXT,

  -- Tracking
  last_calculated_at TIMESTAMPTZ,
  is_active BOOLEAN DEFAULT true,

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

-- ============================================
-- AI MESSAGE DRAFTS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS ai_message_drafts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,

  -- Request
  user_prompt TEXT NOT NULL,
  context_used JSONB,

  -- Generated message
  draft_message TEXT NOT NULL,
  message_type VARCHAR(50),  -- sms, email
  tone VARCHAR(50),  -- friendly, professional, urgent

  -- Usage
  was_sent BOOLEAN DEFAULT false,
  was_edited BOOLEAN DEFAULT false,
  final_message TEXT,
  sent_at TIMESTAMPTZ,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================
-- VOICE COMMANDS LOG
-- ============================================

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

  -- Audio
  audio_url TEXT,
  transcript TEXT NOT NULL,

  -- Parsed intent
  intent VARCHAR(50),  -- update_lead, schedule_callback, draft_message, get_recommendation
  parsed_data JSONB,

  -- Execution
  action_taken TEXT,
  affected_lead_id UUID REFERENCES leads(id),
  success BOOLEAN,
  error_message TEXT,

  -- User
  user_email VARCHAR(255),

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================
-- LIVE COPILOT SESSIONS
-- ============================================

CREATE TABLE IF NOT EXISTS copilot_sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id),

  -- Session info
  started_at TIMESTAMPTZ DEFAULT NOW(),
  ended_at TIMESTAMPTZ,
  duration_seconds INTEGER,

  -- Transcript
  full_transcript TEXT,
  transcript_segments JSONB DEFAULT '[]',

  -- AI Assistance provided
  suggestions_shown JSONB DEFAULT '[]',  -- [{timestamp, trigger, suggestion, was_helpful}]
  objections_detected JSONB DEFAULT '[]',

  -- Outcome
  call_outcome VARCHAR(50),
  user_rating INTEGER,  -- 1-5

  created_at TIMESTAMPTZ DEFAULT NOW()
);

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

-- NOTE: winning_patterns table is defined in learning-schema.sql
CREATE INDEX IF NOT EXISTS idx_call_recordings_lead_id ON call_recordings(lead_id);
CREATE INDEX IF NOT EXISTS idx_call_recordings_created_at ON call_recordings(created_at);
CREATE INDEX IF NOT EXISTS idx_ai_insights_period ON ai_insights(period_start, period_end);
CREATE INDEX IF NOT EXISTS idx_ai_insights_type ON ai_insights(insight_type);
CREATE INDEX IF NOT EXISTS idx_conversion_factors_type ON conversion_factors(factor_type);
CREATE INDEX IF NOT EXISTS idx_ai_message_drafts_lead_id ON ai_message_drafts(lead_id);
CREATE INDEX IF NOT EXISTS idx_voice_commands_created_at ON voice_commands(created_at);
CREATE INDEX IF NOT EXISTS idx_copilot_sessions_lead_id ON copilot_sessions(lead_id);
CREATE INDEX IF NOT EXISTS idx_leads_ai_probability ON leads(ai_conversion_probability);

-- ============================================
-- SEED INITIAL CONVERSION FACTORS
-- ============================================

INSERT INTO conversion_factors (factor_name, factor_type, correlation_score, impact_weight, description) VALUES
  ('Response Speed', 'engagement', 0.72, 0.15, 'Leads who respond to first SMS within 1 hour convert at 3x rate'),
  ('Company Size', 'demographic', 0.45, 0.10, 'Businesses with 10-50 employees have highest conversion'),
  ('Inbound Interest', 'behavioral', 0.68, 0.20, 'Leads who initiate contact convert at 4x rate'),
  ('Multiple Touchpoints', 'engagement', 0.55, 0.12, 'Leads with 3+ interactions before call convert 2x better'),
  ('Morning Calls', 'timing', 0.35, 0.08, 'Calls between 9-11am have 40% higher connection rate'),
  ('Referral Source', 'demographic', 0.62, 0.15, 'Referred leads convert at 5x rate of cold leads'),
  ('Quick Follow-up', 'timing', 0.58, 0.12, 'Following up within 24 hours increases conversion 60%'),
  ('Positive Sentiment', 'behavioral', 0.48, 0.08, 'Leads with positive SMS sentiment convert 2x better')
ON CONFLICT DO NOTHING;
