-- Prompts Hub Migration
-- Creates tables for prompt templates and bulk send tracking

-- ===========================================
-- TABLE: prompt_templates
-- Stores reusable SMS/call script templates
-- ===========================================

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

  -- Basic info
  name VARCHAR(200) NOT NULL,
  category VARCHAR(50) NOT NULL,  -- 'cold_call', 'follow_up', 'closing', 'reengagement', 'onboarding', 'objection', 'custom'
  description TEXT,

  -- Template content
  template TEXT NOT NULL,  -- The actual message with {variables}
  variables TEXT[] DEFAULT '{}',  -- ['firstName', 'company', 'lastContact', etc.]

  -- Targeting
  target_type VARCHAR(20) DEFAULT 'all',  -- 'worker', 'contractor', 'all'
  target_stage VARCHAR(50),  -- Optional: 'signed_up', 'profile_complete', etc.

  -- Tone/style
  tone VARCHAR(30) DEFAULT 'friendly',  -- 'friendly', 'professional', 'urgent', 'casual'

  -- AI generation metadata
  ai_generated BOOLEAN DEFAULT false,
  ai_prompt TEXT,  -- The prompt used to generate this template

  -- Usage tracking
  times_used INTEGER DEFAULT 0,
  last_used_at TIMESTAMPTZ,

  -- Success metrics (updated by tracking)
  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,

  -- Calculated metrics (can be computed or stored)
  delivery_rate DECIMAL(5,2) DEFAULT 0,  -- delivery_count / send_count * 100
  reply_rate DECIMAL(5,2) DEFAULT 0,     -- reply_count / delivery_count * 100
  conversion_rate DECIMAL(5,2) DEFAULT 0, -- conversion_count / send_count * 100

  -- Best performance tracking
  best_day VARCHAR(10),  -- 'monday', 'tuesday', etc.
  best_hour INTEGER,     -- 0-23

  -- Status
  active BOOLEAN DEFAULT true,
  is_default BOOLEAN DEFAULT false,  -- System default templates
  created_by UUID,  -- User who created it

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

-- Indexes for prompt_templates
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);
CREATE INDEX IF NOT EXISTS idx_prompt_templates_times_used ON prompt_templates(times_used DESC);

-- ===========================================
-- TABLE: bulk_send_history
-- Tracks bulk SMS send operations
-- ===========================================

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

  -- Template used
  prompt_template_id UUID REFERENCES prompt_templates(id) ON DELETE SET NULL,
  template_snapshot TEXT NOT NULL,  -- Snapshot of template at time of send

  -- Send details
  recipient_count INTEGER NOT NULL,
  recipient_filter JSONB,  -- Filter criteria used: {type, stage, lastContact, etc.}
  recipient_ids UUID[],  -- Array of lead IDs

  -- Scheduling
  scheduled_at TIMESTAMPTZ,  -- NULL = sent immediately
  sent_at TIMESTAMPTZ,

  -- Status
  status VARCHAR(30) DEFAULT 'pending',  -- 'pending', 'sending', 'completed', 'failed', 'partial'

  -- Results (updated as messages are processed)
  sent_count INTEGER DEFAULT 0,
  delivered_count INTEGER DEFAULT 0,
  failed_count INTEGER DEFAULT 0,
  reply_count INTEGER DEFAULT 0,

  -- Cost tracking
  estimated_cost DECIMAL(10,2),
  actual_cost DECIMAL(10,2),

  -- Error tracking
  errors JSONB,  -- [{leadId, error, timestamp}]

  -- Metadata
  created_by UUID,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

-- Indexes for bulk_send_history
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);

-- ===========================================
-- TABLE: prompt_send_log
-- Individual message tracking for metrics
-- ===========================================

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

  -- References
  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,  -- Reference to sms_messages if applicable

  -- Message details
  personalized_message TEXT NOT NULL,

  -- Delivery tracking
  sent_at TIMESTAMPTZ DEFAULT NOW(),
  delivered_at TIMESTAMPTZ,
  delivery_status VARCHAR(30) DEFAULT 'sent',  -- 'sent', 'delivered', 'failed', 'undelivered'

  -- Response tracking
  replied_at TIMESTAMPTZ,
  reply_sentiment VARCHAR(20),  -- 'positive', 'negative', 'neutral'

  -- Conversion tracking
  converted_at TIMESTAMPTZ,
  conversion_type VARCHAR(50),  -- 'call_booked', 'signed_up', 'job_posted', etc.

  -- Time context for analysis
  day_of_week VARCHAR(10),
  hour_of_day INTEGER,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for prompt_send_log
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);
CREATE INDEX IF NOT EXISTS idx_prompt_send_log_sent ON prompt_send_log(sent_at DESC);

-- ===========================================
-- TRIGGER: Update updated_at on prompt_templates
-- ===========================================

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

-- ===========================================
-- TRIGGER: Update updated_at on bulk_send_history
-- ===========================================

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

DROP TRIGGER IF EXISTS bulk_send_history_updated_at ON bulk_send_history;
CREATE TRIGGER bulk_send_history_updated_at
  BEFORE UPDATE ON bulk_send_history
  FOR EACH ROW
  EXECUTE FUNCTION update_bulk_send_history_updated_at();

-- ===========================================
-- FUNCTION: Update template metrics
-- Called after tracking a send/reply/conversion
-- ===========================================

CREATE OR REPLACE FUNCTION update_prompt_template_metrics(template_id UUID)
RETURNS void AS $$
DECLARE
  stats RECORD;
BEGIN
  SELECT
    COUNT(*) as send_count,
    COUNT(*) FILTER (WHERE delivery_status = 'delivered') as delivery_count,
    COUNT(*) FILTER (WHERE replied_at IS NOT NULL) as reply_count,
    COUNT(*) FILTER (WHERE reply_sentiment = 'positive') as positive_reply_count,
    COUNT(*) FILTER (WHERE converted_at IS NOT NULL) as conversion_count,
    MODE() WITHIN GROUP (ORDER BY day_of_week) FILTER (WHERE reply_sentiment = 'positive') as best_day,
    MODE() WITHIN GROUP (ORDER BY hour_of_day) FILTER (WHERE reply_sentiment = 'positive') as best_hour
  INTO stats
  FROM prompt_send_log
  WHERE prompt_template_id = template_id;

  UPDATE prompt_templates SET
    send_count = stats.send_count,
    delivery_count = stats.delivery_count,
    reply_count = stats.reply_count,
    positive_reply_count = stats.positive_reply_count,
    conversion_count = stats.conversion_count,
    delivery_rate = CASE WHEN stats.send_count > 0 THEN (stats.delivery_count::DECIMAL / stats.send_count * 100) ELSE 0 END,
    reply_rate = CASE WHEN stats.delivery_count > 0 THEN (stats.reply_count::DECIMAL / stats.delivery_count * 100) ELSE 0 END,
    conversion_rate = CASE WHEN stats.send_count > 0 THEN (stats.conversion_count::DECIMAL / stats.send_count * 100) ELSE 0 END,
    best_day = stats.best_day,
    best_hour = stats.best_hour,
    times_used = stats.send_count,
    last_used_at = NOW()
  WHERE id = template_id;
END;
$$ LANGUAGE plpgsql;

-- ===========================================
-- SEED: Default prompt templates
-- ===========================================

INSERT INTO prompt_templates (name, category, description, template, variables, target_type, tone, is_default) VALUES

-- Cold Call Scripts
('Initial Outreach - Worker', 'cold_call', 'First contact with potential workers',
'Hey {firstName}! Saw you signed up on RateRight. Quick question - what type of work are you most interested in? We have {openJobCount} jobs in your area right now.',
ARRAY['firstName', 'openJobCount'], 'worker', 'friendly', true),

('Initial Outreach - Contractor', 'cold_call', 'First contact with potential contractors',
'Hi {firstName}, thanks for signing up to RateRight! I noticed you''re in {industry}. Are you currently looking to hire skilled workers? We have pre-vetted candidates ready.',
ARRAY['firstName', 'industry'], 'contractor', 'professional', true),

-- Follow Up
('24hr Follow Up', 'follow_up', 'Follow up after no response',
'Hey {firstName}, just checking in! Did you get a chance to look at those opportunities I mentioned? Happy to jump on a quick call if easier.',
ARRAY['firstName'], 'all', 'friendly', true),

('Post-Call Follow Up', 'follow_up', 'Send after a call',
'Great chatting with you {firstName}! As discussed, here''s the next step: {nextStep}. Let me know if you have any questions!',
ARRAY['firstName', 'nextStep'], 'all', 'professional', true),

-- Reengagement
('Win Back - Worker', 'reengagement', 'Re-engage inactive workers',
'Hey {firstName}, it''s been a while! Just wanted to let you know we have {newJobCount} new jobs posted since we last spoke. Any interest in taking a look?',
ARRAY['firstName', 'newJobCount'], 'worker', 'friendly', true),

('Win Back - Contractor', 'reengagement', 'Re-engage inactive contractors',
'Hi {firstName}, hope all is well! I noticed you haven''t posted a job recently. Have your hiring needs changed, or can I help with anything?',
ARRAY['firstName'], 'contractor', 'professional', true),

-- Closing
('Urgency Close', 'closing', 'Create urgency to close',
'{firstName}, just wanted to give you a heads up - the {opportunity} we discussed is getting a lot of interest. Want me to lock it in for you before it''s gone?',
ARRAY['firstName', 'opportunity'], 'all', 'urgent', true),

('Soft Close', 'closing', 'Gentle push to close',
'Hey {firstName}, what would it take to get you started today? I''m here to help make this as easy as possible for you.',
ARRAY['firstName'], 'all', 'friendly', true),

-- Onboarding
('Welcome - Worker', 'onboarding', 'Welcome new worker signups',
'Welcome to RateRight {firstName}! I''m {agentName}, your dedicated rep. To get you matched with jobs ASAP, could you complete your profile? Takes 2 mins: {profileLink}',
ARRAY['firstName', 'agentName', 'profileLink'], 'worker', 'friendly', true),

('Welcome - Contractor', 'onboarding', 'Welcome new contractor signups',
'Welcome aboard {firstName}! I''m {agentName} from RateRight. Ready to find your next great hire? Let''s set up a quick call to understand your needs: {calendarLink}',
ARRAY['firstName', 'agentName', 'calendarLink'], 'contractor', 'professional', true),

-- Objection Handling
('Price Objection', 'objection', 'Handle pricing concerns',
'I totally understand {firstName}. Here''s the thing - our clients typically save {savingsAmount} by finding the right match faster. Would a quick ROI breakdown help?',
ARRAY['firstName', 'savingsAmount'], 'contractor', 'professional', true),

('Timing Objection', 'objection', 'Handle "not the right time"',
'No pressure at all {firstName}! When would be a better time to revisit this? I''ll set a reminder to check back then.',
ARRAY['firstName'], 'all', 'friendly', true)

ON CONFLICT DO NOTHING;
