-- ============================================
-- RATERIGHT GROWTH ENGINE - CONSOLIDATED MIGRATION
-- Run this in Supabase SQL Editor
-- Creates ALL tables needed for the platform
-- Safe to run multiple times (uses IF NOT EXISTS)
-- ============================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================
-- SECTION 1: CORE CRM TABLES
-- ============================================

-- Leads table (main CRM)
CREATE TABLE IF NOT EXISTS leads (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(255),
  phone VARCHAR(20),
  company VARCHAR(255),
  type VARCHAR(20) DEFAULT 'worker', -- 'worker' or 'contractor'
  source VARCHAR(50) DEFAULT 'manual',
  status VARCHAR(50) DEFAULT 'new',
  score INTEGER DEFAULT 0,
  assigned_to UUID,
  last_contact_at TIMESTAMPTZ,
  last_contact_type VARCHAR(50),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ,

  -- Platform integration fields
  platform_user_id UUID,
  platform_last_synced_at TIMESTAMPTZ,

  -- AI fields
  ai_conversion_probability DECIMAL(5,2),
  ai_predicted_objections JSONB DEFAULT '[]',
  ai_recommended_action TEXT,
  ai_recommended_timing TEXT,
  ai_similar_wins JSONB DEFAULT '[]',
  ai_intel_brief JSONB,
  ai_last_analyzed_at TIMESTAMPTZ,

  -- Referral fields
  referred_by_lead_id UUID,
  referral_context TEXT,
  is_hot_referral BOOLEAN DEFAULT false,

  -- Contact request tracking
  contact_request_pending BOOLEAN DEFAULT false,
  contact_request_sent_at TIMESTAMPTZ,

  -- Platform lifecycle fields (Activation)
  platform_stage VARCHAR(50),
  platform_data JSONB,
  platform_health_score INTEGER DEFAULT 0,
  stage_changed_at TIMESTAMPTZ
);

-- Lead indexes
CREATE INDEX IF NOT EXISTS idx_leads_phone ON leads(phone);
CREATE INDEX IF NOT EXISTS idx_leads_email ON leads(email);
CREATE INDEX IF NOT EXISTS idx_leads_status ON leads(status);
CREATE INDEX IF NOT EXISTS idx_leads_score ON leads(score DESC);
CREATE INDEX IF NOT EXISTS idx_leads_deleted_at ON leads(deleted_at);
CREATE INDEX IF NOT EXISTS idx_leads_type ON leads(type);
CREATE INDEX IF NOT EXISTS idx_leads_platform_user_id ON leads(platform_user_id);
CREATE INDEX IF NOT EXISTS idx_leads_ai_probability ON leads(ai_conversion_probability DESC);
CREATE INDEX IF NOT EXISTS idx_leads_referred_by ON leads(referred_by_lead_id);
CREATE INDEX IF NOT EXISTS idx_leads_hot_referral ON leads(is_hot_referral) WHERE is_hot_referral = true;
CREATE INDEX IF NOT EXISTS idx_leads_contact_request_pending ON leads(contact_request_pending) WHERE contact_request_pending = true;
CREATE INDEX IF NOT EXISTS idx_leads_platform_stage ON leads(platform_stage);
CREATE INDEX IF NOT EXISTS idx_leads_health_score ON leads(platform_health_score DESC);

-- Communications table
CREATE TABLE IF NOT EXISTS communications (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  type VARCHAR(50) NOT NULL, -- 'sms', 'call', 'email'
  direction VARCHAR(20) NOT NULL, -- 'inbound', 'outbound'
  phone VARCHAR(20),
  content TEXT,
  external_id VARCHAR(100),
  delivery_status VARCHAR(50),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_communications_lead_id ON communications(lead_id);
CREATE INDEX IF NOT EXISTS idx_communications_external_id ON communications(external_id);
CREATE INDEX IF NOT EXISTS idx_communications_type ON communications(type);
CREATE INDEX IF NOT EXISTS idx_communications_created_at ON communications(created_at DESC);

-- Lead notes
CREATE TABLE IF NOT EXISTS lead_notes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_by VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_lead_notes_lead_id ON lead_notes(lead_id);

-- Platform activity
CREATE TABLE IF NOT EXISTS platform_activity (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  activity_type VARCHAR(50) NOT NULL,
  description TEXT,
  activity_at TIMESTAMPTZ NOT NULL,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_platform_activity_lead_id ON platform_activity(lead_id);

-- Callbacks
CREATE TABLE IF NOT EXISTS callbacks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  phone VARCHAR(20),
  reason TEXT,
  priority VARCHAR(20) DEFAULT 'medium',
  status VARCHAR(20) DEFAULT 'pending',
  source_type VARCHAR(50),
  source_id UUID,
  notes TEXT,
  scheduled_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  handled_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_callbacks_lead_id ON callbacks(lead_id);
CREATE INDEX IF NOT EXISTS idx_callbacks_status ON callbacks(status);
CREATE INDEX IF NOT EXISTS idx_callbacks_scheduled_at ON callbacks(scheduled_at);

-- Conversions
CREATE TABLE IF NOT EXISTS conversions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  type VARCHAR(50) NOT NULL,
  value DECIMAL(10,2),
  notes TEXT,
  converted_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_conversions_lead_id ON conversions(lead_id);

-- Daily metrics
CREATE TABLE IF NOT EXISTS daily_metrics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  date DATE NOT NULL UNIQUE,
  calls_made INTEGER DEFAULT 0,
  sms_sent INTEGER DEFAULT 0,
  sms_received INTEGER DEFAULT 0,
  new_leads INTEGER DEFAULT 0,
  conversions INTEGER DEFAULT 0,
  hot_leads INTEGER DEFAULT 0,
  pending_callbacks INTEGER DEFAULT 0,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Team members
CREATE TABLE IF NOT EXISTS team_members (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  role VARCHAR(50),
  phone VARCHAR(20),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================
-- SECTION 2: MESSAGING & AUTOMATION TABLES
-- ============================================

-- SMS templates
CREATE TABLE IF NOT EXISTS sms_templates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  slug VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  description TEXT,
  variables JSONB DEFAULT '[]',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Sequences
CREATE TABLE IF NOT EXISTS sequences (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL,
  description TEXT,
  trigger_type VARCHAR(50),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Sequence steps
CREATE TABLE IF NOT EXISTS sequence_steps (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  sequence_id UUID REFERENCES sequences(id) ON DELETE CASCADE,
  step_order INTEGER NOT NULL,
  action_type VARCHAR(50) NOT NULL,
  delay_hours INTEGER DEFAULT 0,
  template_id UUID REFERENCES sms_templates(id),
  config JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Sequence enrollments
CREATE TABLE IF NOT EXISTS sequence_enrollments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  sequence_id UUID REFERENCES sequences(id) ON DELETE CASCADE,
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  current_step INTEGER DEFAULT 0,
  status VARCHAR(20) DEFAULT 'active',
  enrolled_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  paused_at TIMESTAMPTZ
);

-- ============================================
-- SECTION 3: AI & INTELLIGENCE TABLES
-- ============================================

-- Call recordings
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_url TEXT,
  audio_duration_seconds INTEGER,
  transcript TEXT,
  transcript_segments JSONB DEFAULT '[]',
  transcription_model VARCHAR(50),
  ai_summary TEXT,
  ai_key_points JSONB DEFAULT '[]',
  ai_sentiment VARCHAR(20),
  ai_sentiment_score DECIMAL(3,2),
  ai_detected_objections JSONB DEFAULT '[]',
  ai_next_steps TEXT,
  ai_coaching_notes TEXT,
  call_direction VARCHAR(20),
  caller_phone VARCHAR(20),
  recording_source VARCHAR(50),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  processed_at TIMESTAMPTZ
);

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 DESC);

-- AI insights
CREATE TABLE IF NOT EXISTS ai_insights (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  insight_type VARCHAR(50) NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  data JSONB NOT NULL,
  recommendations JSONB DEFAULT '[]',
  confidence_score DECIMAL(3,2),
  sample_size INTEGER,
  is_active BOOLEAN DEFAULT true,
  acknowledged_at TIMESTAMPTZ,
  acknowledged_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

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);

-- Conversion factors
CREATE TABLE IF NOT EXISTS conversion_factors (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  factor_name VARCHAR(100) NOT NULL,
  factor_type VARCHAR(50) NOT NULL,
  correlation_score DECIMAL(4,3),
  impact_weight DECIMAL(4,3),
  sample_size INTEGER,
  confidence_interval JSONB,
  description TEXT,
  positive_example TEXT,
  negative_example TEXT,
  last_calculated_at TIMESTAMPTZ,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_conversion_factors_type ON conversion_factors(factor_type);

-- AI message drafts
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,
  user_prompt TEXT NOT NULL,
  context_used JSONB,
  draft_message TEXT NOT NULL,
  message_type VARCHAR(50),
  tone VARCHAR(50),
  was_sent BOOLEAN DEFAULT false,
  was_edited BOOLEAN DEFAULT false,
  final_message TEXT,
  sent_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ai_message_drafts_lead_id ON ai_message_drafts(lead_id);

-- Voice commands
CREATE TABLE IF NOT EXISTS voice_commands (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  audio_url TEXT,
  transcript TEXT NOT NULL,
  intent VARCHAR(50),
  parsed_data JSONB,
  action_taken TEXT,
  affected_lead_id UUID REFERENCES leads(id),
  success BOOLEAN,
  error_message TEXT,
  user_email VARCHAR(255),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_voice_commands_created_at ON voice_commands(created_at DESC);

-- Copilot sessions
CREATE TABLE IF NOT EXISTS copilot_sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  started_at TIMESTAMPTZ DEFAULT NOW(),
  ended_at TIMESTAMPTZ,
  duration_seconds INTEGER,
  full_transcript TEXT,
  transcript_segments JSONB DEFAULT '[]',
  suggestions_shown JSONB DEFAULT '[]',
  objections_detected JSONB DEFAULT '[]',
  call_outcome VARCHAR(50),
  ai_summary JSONB DEFAULT NULL,
  user_rating INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_copilot_sessions_lead_id ON copilot_sessions(lead_id);

-- Company intel
CREATE TABLE IF NOT EXISTS company_intel (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_name TEXT NOT NULL,
  company_name_normalized TEXT,
  summary TEXT,
  size VARCHAR(50),
  industry VARCHAR(100),
  location TEXT,
  recent_projects JSONB,
  hiring_signals JSONB,
  ai_sales_angle TEXT,
  user_notes TEXT,
  manually_updated BOOLEAN DEFAULT false,
  updated_by VARCHAR(255),
  notes_updated_at TIMESTAMPTZ,
  website_url TEXT,
  abn VARCHAR(20),
  linkedin_url TEXT,
  facebook_url TEXT,
  searched_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_company_intel_name ON company_intel(company_name_normalized);
CREATE INDEX IF NOT EXISTS idx_company_intel_user_notes ON company_intel(manually_updated) WHERE manually_updated = true;

-- Lead intel
CREATE TABLE IF NOT EXISTS lead_intel (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  linkedin_url TEXT,
  job_title TEXT,
  years_at_company INTEGER,
  previous_companies JSONB,
  summary TEXT,
  ai_talking_points TEXT,
  user_notes TEXT,
  manually_updated BOOLEAN DEFAULT false,
  updated_by VARCHAR(255),
  notes_updated_at TIMESTAMPTZ,
  searched_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_lead_intel_lead ON lead_intel(lead_id);
CREATE INDEX IF NOT EXISTS idx_lead_intel_user_notes ON lead_intel(manually_updated) WHERE manually_updated = true;

-- Ideas
CREATE TABLE IF NOT EXISTS ideas (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  content TEXT NOT NULL,
  category VARCHAR(50) NOT NULL,
  user_email VARCHAR(255),
  lead_id UUID REFERENCES leads(id),
  ai_detected_category BOOLEAN DEFAULT false,
  tags JSONB DEFAULT '[]',
  status VARCHAR(50) DEFAULT 'captured',
  priority VARCHAR(20),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  actioned_at TIMESTAMPTZ,
  notes TEXT
);

CREATE INDEX IF NOT EXISTS idx_ideas_category ON ideas(category);
CREATE INDEX IF NOT EXISTS idx_ideas_status ON ideas(status);
CREATE INDEX IF NOT EXISTS idx_ideas_user ON ideas(user_email);
CREATE INDEX IF NOT EXISTS idx_ideas_created ON ideas(created_at DESC);

-- ============================================
-- SECTION 4: LEARNING SYSTEM TABLES
-- ============================================

-- Conversion patterns
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_name TEXT,
  lead_company TEXT,
  lead_trade VARCHAR(100),
  lead_source VARCHAR(100),
  lead_score_at_conversion INTEGER,
  days_to_convert INTEGER,
  total_touchpoints INTEGER,
  calls_made INTEGER,
  sms_sent INTEGER,
  sms_received INTEGER,
  winning_channel VARCHAR(50),
  first_contact_type VARCHAR(50),
  final_contact_type VARCHAR(50),
  total_call_duration_seconds INTEGER,
  avg_call_duration_seconds INTEGER,
  calls_to_connect INTEGER,
  objections_raised JSONB DEFAULT '[]',
  objection_count INTEGER DEFAULT 0,
  objections_overcome INTEGER DEFAULT 0,
  intel_briefs_viewed INTEGER DEFAULT 0,
  copilot_sessions INTEGER DEFAULT 0,
  ai_suggestions_shown INTEGER DEFAULT 0,
  ai_suggestions_helpful INTEGER DEFAULT 0,
  best_contact_time VARCHAR(50),
  best_contact_day VARCHAR(20),
  conversion_hour INTEGER,
  conversion_day_of_week INTEGER,
  winning_messages JSONB DEFAULT '[]',
  effective_talking_points JSONB DEFAULT '[]',
  communication_timeline JSONB DEFAULT '[]',
  copilot_suggestions_used JSONB DEFAULT '[]',
  conversion_type VARCHAR(50),
  conversion_value DECIMAL(10,2),
  converted_by VARCHAR(255),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

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 DESC);

-- Objection responses (learning)
CREATE TABLE IF NOT EXISTS objection_responses (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  objection_type VARCHAR(50) NOT NULL,
  objection_text TEXT,
  objection_keywords JSONB DEFAULT '[]',
  response_text TEXT NOT NULL,
  key_point TEXT,
  follow_up_question TEXT,
  times_shown INTEGER DEFAULT 0,
  times_helpful INTEGER DEFAULT 0,
  times_not_helpful INTEGER DEFAULT 0,
  led_to_conversion INTEGER DEFAULT 0,
  effectiveness_score DECIMAL(4,3) DEFAULT 0,
  lead_trade VARCHAR(100),
  lead_source VARCHAR(100),
  source VARCHAR(50) DEFAULT 'ai',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

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);

-- Suggestion feedback
CREATE TABLE IF NOT EXISTS suggestion_feedback (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  copilot_session_id UUID REFERENCES copilot_sessions(id),
  lead_id UUID REFERENCES leads(id),
  objection_detected TEXT,
  objection_type VARCHAR(50),
  suggestion_response TEXT,
  suggestion_key_point TEXT,
  was_helpful BOOLEAN,
  feedback_note TEXT,
  user_email VARCHAR(255),
  call_outcome VARCHAR(50),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

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);

-- ============================================
-- SECTION 5: GAMIFICATION TABLES (Phase 6-9)
-- ============================================

-- User XP and levels
CREATE TABLE IF NOT EXISTS user_xp (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL,
  total_xp INTEGER DEFAULT 0,
  level INTEGER DEFAULT 1,
  current_streak INTEGER DEFAULT 0,
  longest_streak INTEGER DEFAULT 0,
  last_activity_date DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id)
);

CREATE INDEX IF NOT EXISTS idx_user_xp_user ON user_xp(user_id);
CREATE INDEX IF NOT EXISTS idx_user_xp_level ON user_xp(level DESC);
CREATE INDEX IF NOT EXISTS idx_user_xp_total ON user_xp(total_xp DESC);

-- User achievements
CREATE TABLE IF NOT EXISTS user_achievements (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL,
  achievement_id VARCHAR(50) NOT NULL,
  unlocked_at TIMESTAMPTZ DEFAULT NOW(),
  metadata JSONB DEFAULT '{}',
  UNIQUE(user_id, achievement_id)
);

CREATE INDEX IF NOT EXISTS idx_user_achievements_user ON user_achievements(user_id);
CREATE INDEX IF NOT EXISTS idx_user_achievements_id ON user_achievements(achievement_id);

-- XP history
CREATE TABLE IF NOT EXISTS xp_history (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL,
  amount INTEGER NOT NULL,
  reason VARCHAR(100) NOT NULL,
  related_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_xp_history_user ON xp_history(user_id);
CREATE INDEX IF NOT EXISTS idx_xp_history_date ON xp_history(created_at DESC);

-- Call scores (coaching)
CREATE TABLE IF NOT EXISTS call_scores (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  call_id UUID NOT NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  user_id UUID,
  scores JSONB NOT NULL DEFAULT '{}',
  overall_score INTEGER DEFAULT 0,
  ai_feedback TEXT,
  transcript_summary TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_call_scores_call ON call_scores(call_id);
CREATE INDEX IF NOT EXISTS idx_call_scores_lead ON call_scores(lead_id);
CREATE INDEX IF NOT EXISTS idx_call_scores_user ON call_scores(user_id);

-- Winning patterns (analytics)
CREATE TABLE IF NOT EXISTS winning_patterns (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  pattern_type VARCHAR(50) NOT NULL,
  pattern_name TEXT,
  description TEXT NOT NULL,
  recommendation TEXT,
  data JSONB DEFAULT '{}',
  criteria JSONB,
  example_leads JSONB DEFAULT '[]',
  confidence DECIMAL(5,2) DEFAULT 0,
  sample_count INTEGER DEFAULT 0,
  sample_size INTEGER,
  conversion_rate DECIMAL(4,3),
  baseline_rate DECIMAL(4,3),
  lift_percentage DECIMAL(5,2),
  is_active BOOLEAN DEFAULT true,
  confidence_score DECIMAL(3,2),
  period_start DATE,
  period_end DATE,
  calculated_at TIMESTAMPTZ,
  discovered_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

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

-- AI Alerts
CREATE TABLE IF NOT EXISTS ai_alerts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID,
  alert_type VARCHAR(50) NOT NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  message TEXT,
  data JSONB DEFAULT '{}',
  priority VARCHAR(20) DEFAULT 'medium',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  read_at TIMESTAMPTZ,
  dismissed_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_ai_alerts_user ON ai_alerts(user_id);
CREATE INDEX IF NOT EXISTS idx_ai_alerts_lead ON ai_alerts(lead_id);
CREATE INDEX IF NOT EXISTS idx_ai_alerts_type ON ai_alerts(alert_type);
CREATE INDEX IF NOT EXISTS idx_ai_alerts_pending ON ai_alerts(user_id) WHERE dismissed_at IS NULL AND read_at IS NULL;

-- Weekly summaries
CREATE TABLE IF NOT EXISTS weekly_summaries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL,
  week_start DATE NOT NULL,
  summary JSONB NOT NULL DEFAULT '{}',
  insights TEXT[],
  recommendations TEXT[],
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, week_start)
);

CREATE INDEX IF NOT EXISTS idx_weekly_summaries_user ON weekly_summaries(user_id);
CREATE INDEX IF NOT EXISTS idx_weekly_summaries_week ON weekly_summaries(week_start DESC);

-- ============================================
-- SECTION 6: ACTIVATION SYSTEM TABLES
-- ============================================

-- Stage transitions (audit trail)
CREATE TABLE IF NOT EXISTS stage_transitions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  from_stage VARCHAR(50),
  to_stage VARCHAR(50),
  triggered_by VARCHAR(50), -- 'platform_sync', 'manual', 'ai_detection'
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_stage_transitions_lead ON stage_transitions(lead_id);
CREATE INDEX IF NOT EXISTS idx_stage_transitions_date ON stage_transitions(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_stage_transitions_to_stage ON stage_transitions(to_stage);

-- Activation metrics (daily rollups)
CREATE TABLE IF NOT EXISTS activation_metrics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  date DATE NOT NULL,
  lead_type VARCHAR(20), -- 'worker', 'contractor'
  stage VARCHAR(50),
  count_at_stage INTEGER DEFAULT 0,
  count_activated INTEGER DEFAULT 0, -- moved to next stage
  count_stuck INTEGER DEFAULT 0, -- 7+ days no progress
  avg_days_in_stage DECIMAL(5,2),
  revenue_at_risk DECIMAL(12,2),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(date, lead_type, stage)
);

CREATE INDEX IF NOT EXISTS idx_activation_metrics_date ON activation_metrics(date DESC);
CREATE INDEX IF NOT EXISTS idx_activation_metrics_stage ON activation_metrics(stage);

-- ============================================
-- SECTION 7: SCRIPT SYSTEM TABLES
-- ============================================

-- Scripts
CREATE TABLE IF NOT EXISTS scripts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(200) NOT NULL,
  description TEXT,
  category VARCHAR(50), -- 'cold_call', 'follow_up', 'closing', 'reactivation'
  target_type VARCHAR(20), -- 'worker', 'contractor', 'both'
  target_stage VARCHAR(50),
  opening TEXT,
  discovery_questions JSONB DEFAULT '[]',
  value_props JSONB DEFAULT '[]',
  closing_techniques JSONB DEFAULT '[]',
  is_active BOOLEAN DEFAULT true,
  times_used INTEGER DEFAULT 0,
  success_count INTEGER DEFAULT 0,
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_scripts_category ON scripts(category);
CREATE INDEX IF NOT EXISTS idx_scripts_target ON scripts(target_type, target_stage);
CREATE INDEX IF NOT EXISTS idx_scripts_active ON scripts(is_active) WHERE is_active = true;

-- Objections library
CREATE TABLE IF NOT EXISTS objections (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  objection TEXT NOT NULL,
  category VARCHAR(50), -- 'price', 'timing', 'trust', 'competition', 'authority', 'need'
  frequency INTEGER DEFAULT 0,
  responses JSONB DEFAULT '[]',
  tags TEXT[],
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_objections_category ON objections(category);

-- Script sessions
CREATE TABLE IF NOT EXISTS script_sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  script_id UUID REFERENCES scripts(id) ON DELETE SET NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,
  started_at TIMESTAMPTZ DEFAULT NOW(),
  ended_at TIMESTAMPTZ,
  duration_seconds INTEGER,
  outcome VARCHAR(50),
  objections_encountered UUID[],
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_script_sessions_script ON script_sessions(script_id);
CREATE INDEX IF NOT EXISTS idx_script_sessions_lead ON script_sessions(lead_id);
CREATE INDEX IF NOT EXISTS idx_script_sessions_outcome ON script_sessions(outcome);

-- Script-objection links
CREATE TABLE IF NOT EXISTS script_objections (
  script_id UUID REFERENCES scripts(id) ON DELETE CASCADE,
  objection_id UUID REFERENCES objections(id) ON DELETE CASCADE,
  frequency INTEGER DEFAULT 0,
  PRIMARY KEY (script_id, objection_id)
);

-- ============================================
-- SECTION 8: HELPER FUNCTIONS & TRIGGERS
-- ============================================

-- Level calculation function
CREATE OR REPLACE FUNCTION calculate_level(xp INTEGER)
RETURNS INTEGER AS $$
BEGIN
  -- Level formula: Level = floor(sqrt(xp / 100)) + 1
  -- Level 1: 0-99 XP
  -- Level 2: 100-399 XP
  -- Level 3: 400-899 XP
  -- Level 4: 900-1599 XP
  RETURN GREATEST(1, FLOOR(SQRT(xp::FLOAT / 100)) + 1);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Auto-update level trigger
CREATE OR REPLACE FUNCTION update_user_level()
RETURNS TRIGGER AS $$
BEGIN
  NEW.level := calculate_level(NEW.total_xp);
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_user_level ON user_xp;
CREATE TRIGGER trigger_update_user_level
  BEFORE UPDATE OF total_xp ON user_xp
  FOR EACH ROW
  EXECUTE FUNCTION update_user_level();

-- ============================================
-- SECTION 9: DEFAULT DATA
-- ============================================

-- Request contact SMS template
INSERT INTO sms_templates (name, slug, content, description, is_active)
VALUES (
  'Request Contact',
  'request_contact',
  'Hey {{first_name}}, who does the hiring at your company? If you text me their name and number I''ll keep it between us',
  'Request hiring manager contact info from workers',
  true
) ON CONFLICT (slug) DO NOTHING;

-- ============================================
-- VERIFICATION QUERY
-- ============================================

-- Run this to verify all tables were created:
-- SELECT table_name FROM information_schema.tables
-- WHERE table_schema = 'public'
-- ORDER BY table_name;
