-- ============================================
-- RATERIGHT GROWTH ENGINE - COMPLETE DATABASE SETUP
-- Run this in Supabase SQL Editor
-- Order matters! Run in sequence.
-- ============================================

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

-- ============================================
-- 1. CORE SCHEMA (schema.sql)
-- ============================================

-- Leads table
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),
  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
);

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

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

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

-- Callbacks
CREATE TABLE IF NOT EXISTS callbacks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id),
  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()
);

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

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

-- 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),
  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),
  lead_id UUID REFERENCES leads(id),
  current_step INTEGER DEFAULT 0,
  status VARCHAR(20) DEFAULT 'active',
  enrolled_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  paused_at TIMESTAMPTZ
);

-- Core 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);
CREATE INDEX IF NOT EXISTS idx_leads_deleted_at ON leads(deleted_at);
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_callbacks_lead_id ON callbacks(lead_id);
CREATE INDEX IF NOT EXISTS idx_callbacks_status ON callbacks(status);
CREATE INDEX IF NOT EXISTS idx_platform_activity_lead_id ON platform_activity(lead_id);

-- ============================================
-- 2. AI SCHEMA (ai-schema.sql)
-- ============================================

-- AI fields on 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
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
);

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

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

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

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

-- Copilot sessions
CREATE TABLE IF NOT EXISTS copilot_sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id),
  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()
);

-- Add ai_summary column if missing (for existing databases)
ALTER TABLE copilot_sessions ADD COLUMN IF NOT EXISTS ai_summary JSONB DEFAULT NULL;

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

-- ============================================
-- 3. IDEAS SCHEMA (ideas-schema.sql)
-- ============================================

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

-- ============================================
-- 4. LEARNING SCHEMA (learning-schema.sql)
-- ============================================

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

-- Objection responses
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()
);

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

-- Learning 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_winning_patterns_type ON winning_patterns(pattern_type);
CREATE INDEX IF NOT EXISTS idx_winning_patterns_active ON winning_patterns(is_active);

-- ============================================
-- 5. INTEL SCHEMA (intel-schema.sql)
-- ============================================

-- 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,
  searched_at TIMESTAMPTZ DEFAULT NOW()
);

-- 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,
  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_lead_intel_lead ON lead_intel(lead_id);

-- ============================================
-- 6. INTEL NOTES MIGRATION
-- ============================================

ALTER TABLE company_intel ADD COLUMN IF NOT EXISTS user_notes TEXT;
ALTER TABLE company_intel ADD COLUMN IF NOT EXISTS manually_updated BOOLEAN DEFAULT false;
ALTER TABLE company_intel ADD COLUMN IF NOT EXISTS updated_by VARCHAR(255);
ALTER TABLE company_intel ADD COLUMN IF NOT EXISTS notes_updated_at TIMESTAMP WITH TIME ZONE;

ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS user_notes TEXT;
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS manually_updated BOOLEAN DEFAULT false;
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS updated_by VARCHAR(255);
ALTER TABLE lead_intel ADD COLUMN IF NOT EXISTS notes_updated_at TIMESTAMP WITH TIME ZONE;

CREATE INDEX IF NOT EXISTS idx_company_intel_user_notes ON company_intel(manually_updated) WHERE manually_updated = true;
CREATE INDEX IF NOT EXISTS idx_lead_intel_user_notes ON lead_intel(manually_updated) WHERE manually_updated = true;

-- ============================================
-- 7. COMPANY LINKS MIGRATION
-- ============================================

ALTER TABLE company_intel ADD COLUMN IF NOT EXISTS website_url TEXT;
ALTER TABLE company_intel ADD COLUMN IF NOT EXISTS abn VARCHAR(20);
ALTER TABLE company_intel ADD COLUMN IF NOT EXISTS linkedin_url TEXT;
ALTER TABLE company_intel ADD COLUMN IF NOT EXISTS facebook_url TEXT;

-- ============================================
-- 8. CONTRACTOR REFERRAL MIGRATION
-- ============================================

ALTER TABLE leads ADD COLUMN IF NOT EXISTS referred_by_lead_id UUID REFERENCES leads(id);
ALTER TABLE leads ADD COLUMN IF NOT EXISTS referral_context TEXT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS is_hot_referral BOOLEAN DEFAULT false;

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;

-- ============================================
-- 9. REQUEST CONTACT MIGRATION
-- ============================================

-- SMS template for requesting contact info from workers
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 UPDATE SET
  content = EXCLUDED.content,
  description = EXCLUDED.description,
  is_active = true;

-- Add contact request tracking fields
ALTER TABLE leads ADD COLUMN IF NOT EXISTS contact_request_pending BOOLEAN DEFAULT false;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS contact_request_sent_at TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS idx_leads_contact_request_pending
  ON leads(contact_request_pending)
  WHERE contact_request_pending = true;

-- ============================================
-- 10. PROMPTS HUB MIGRATION
-- ============================================

-- Prompt templates table
CREATE TABLE IF NOT EXISTS prompt_templates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(200) NOT NULL,
  category VARCHAR(50) NOT NULL,
  description TEXT,
  template TEXT NOT NULL,
  variables TEXT[] DEFAULT '{}',
  target_type VARCHAR(20) DEFAULT 'all',
  target_stage VARCHAR(50),
  tone VARCHAR(30) DEFAULT 'friendly',
  ai_generated BOOLEAN DEFAULT false,
  ai_prompt TEXT,
  times_used INTEGER DEFAULT 0,
  last_used_at TIMESTAMPTZ,
  send_count INTEGER DEFAULT 0,
  delivery_count INTEGER DEFAULT 0,
  reply_count INTEGER DEFAULT 0,
  positive_reply_count INTEGER DEFAULT 0,
  conversion_count INTEGER DEFAULT 0,
  delivery_rate DECIMAL(5,2) DEFAULT 0,
  reply_rate DECIMAL(5,2) DEFAULT 0,
  conversion_rate DECIMAL(5,2) DEFAULT 0,
  best_day VARCHAR(10),
  best_hour INTEGER,
  active BOOLEAN DEFAULT true,
  is_default BOOLEAN DEFAULT false,
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_prompt_templates_category ON prompt_templates(category);
CREATE INDEX IF NOT EXISTS idx_prompt_templates_target ON prompt_templates(target_type, target_stage);
CREATE INDEX IF NOT EXISTS idx_prompt_templates_active ON prompt_templates(active);
CREATE INDEX IF NOT EXISTS idx_prompt_templates_reply_rate ON prompt_templates(reply_rate DESC);

-- Bulk send history table
CREATE TABLE IF NOT EXISTS bulk_send_history (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  prompt_template_id UUID REFERENCES prompt_templates(id) ON DELETE SET NULL,
  template_snapshot TEXT NOT NULL,
  recipient_count INTEGER NOT NULL,
  recipient_filter JSONB,
  recipient_ids UUID[],
  scheduled_at TIMESTAMPTZ,
  sent_at TIMESTAMPTZ,
  status VARCHAR(30) DEFAULT 'pending',
  sent_count INTEGER DEFAULT 0,
  delivered_count INTEGER DEFAULT 0,
  failed_count INTEGER DEFAULT 0,
  reply_count INTEGER DEFAULT 0,
  estimated_cost DECIMAL(10,2),
  actual_cost DECIMAL(10,2),
  errors JSONB,
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_bulk_send_history_template ON bulk_send_history(prompt_template_id);
CREATE INDEX IF NOT EXISTS idx_bulk_send_history_status ON bulk_send_history(status);
CREATE INDEX IF NOT EXISTS idx_bulk_send_history_created ON bulk_send_history(created_at DESC);

-- Prompt send log for tracking
CREATE TABLE IF NOT EXISTS prompt_send_log (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  prompt_template_id UUID REFERENCES prompt_templates(id) ON DELETE SET NULL,
  bulk_send_id UUID REFERENCES bulk_send_history(id) ON DELETE SET NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  sms_message_id UUID,
  personalized_message TEXT NOT NULL,
  sent_at TIMESTAMPTZ DEFAULT NOW(),
  delivered_at TIMESTAMPTZ,
  delivery_status VARCHAR(30) DEFAULT 'sent',
  replied_at TIMESTAMPTZ,
  reply_sentiment VARCHAR(20),
  converted_at TIMESTAMPTZ,
  conversion_type VARCHAR(50),
  day_of_week VARCHAR(10),
  hour_of_day INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_prompt_send_log_template ON prompt_send_log(prompt_template_id);
CREATE INDEX IF NOT EXISTS idx_prompt_send_log_bulk ON prompt_send_log(bulk_send_id);
CREATE INDEX IF NOT EXISTS idx_prompt_send_log_lead ON prompt_send_log(lead_id);

-- Update triggers
CREATE OR REPLACE FUNCTION update_prompt_templates_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS prompt_templates_updated_at ON prompt_templates;
CREATE TRIGGER prompt_templates_updated_at
  BEFORE UPDATE ON prompt_templates
  FOR EACH ROW
  EXECUTE FUNCTION update_prompt_templates_updated_at();

-- ============================================
-- VERIFY ALL TABLES CREATED
-- ============================================

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;
