-- Call Analytics Migration
-- Advanced call quality metrics: talk ratio, filler words, questions, sentiment

CREATE TABLE IF NOT EXISTS call_analytics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  communication_id UUID REFERENCES communications(id) ON DELETE CASCADE UNIQUE,
  lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,
  user_id UUID,

  -- Talk/Listen Ratio
  agent_talk_time_seconds INTEGER DEFAULT 0,
  caller_talk_time_seconds INTEGER DEFAULT 0,
  talk_listen_ratio DECIMAL(4,3), -- 0.000 to 1.000 (agent portion)

  -- Monologue Detection
  longest_agent_monologue_seconds INTEGER DEFAULT 0,
  monologue_count INTEGER DEFAULT 0, -- >60s without pause

  -- Filler Words
  filler_word_count INTEGER DEFAULT 0,
  filler_words_per_minute DECIMAL(4,2),
  filler_word_breakdown JSONB DEFAULT '{}', -- {"um": 5, "uh": 3, "like": 12}

  -- Questions
  agent_questions INTEGER DEFAULT 0,
  caller_questions INTEGER DEFAULT 0,
  open_questions INTEGER DEFAULT 0,
  closed_questions INTEGER DEFAULT 0,

  -- Sentiment
  sentiment_timeline JSONB DEFAULT '[]', -- [{time: 30, sentiment: "positive", score: 0.8}]
  sentiment_trend VARCHAR(20), -- 'improving', 'declining', 'stable'
  overall_sentiment_score DECIMAL(3,2), -- 0.00 to 1.00

  -- Pace
  agent_words_per_minute INTEGER,
  caller_words_per_minute INTEGER,
  total_word_count INTEGER DEFAULT 0,

  -- Quality Score
  call_quality_score INTEGER, -- 0-100
  quality_breakdown JSONB DEFAULT '{}', -- {talk_ratio: 80, filler: 70, questions: 90, ...}

  -- Coaching
  coaching_points JSONB DEFAULT '[]', -- [{area: "questions", message: "Ask more open questions"}]

  -- Raw data
  transcript_words JSONB, -- Full word-level timing data

  -- Metadata
  call_duration_seconds INTEGER,
  analyzed_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_call_analytics_lead ON call_analytics(lead_id);
CREATE INDEX IF NOT EXISTS idx_call_analytics_user ON call_analytics(user_id);
CREATE INDEX IF NOT EXISTS idx_call_analytics_quality ON call_analytics(call_quality_score);
CREATE INDEX IF NOT EXISTS idx_call_analytics_created ON call_analytics(created_at DESC);

-- Coaching alerts table - flags calls that need review
CREATE TABLE IF NOT EXISTS coaching_alerts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  call_analytics_id UUID REFERENCES call_analytics(id) ON DELETE CASCADE,
  communication_id UUID REFERENCES communications(id) ON DELETE CASCADE,
  user_id UUID,
  lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,

  alert_type VARCHAR(50) NOT NULL, -- 'low_quality', 'long_monologue', 'filler_words', 'few_questions'
  severity VARCHAR(20) DEFAULT 'medium', -- 'low', 'medium', 'high'
  message TEXT,
  metric_value DECIMAL(10,2),
  threshold_value DECIMAL(10,2),

  is_reviewed BOOLEAN DEFAULT FALSE,
  reviewed_at TIMESTAMPTZ,
  reviewed_by UUID,
  review_notes TEXT,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_coaching_alerts_user ON coaching_alerts(user_id);
CREATE INDEX IF NOT EXISTS idx_coaching_alerts_reviewed ON coaching_alerts(is_reviewed, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_coaching_alerts_type ON coaching_alerts(alert_type);

-- RLS Policies
ALTER TABLE call_analytics ENABLE ROW LEVEL SECURITY;
ALTER TABLE coaching_alerts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view call analytics"
  ON call_analytics FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can insert call analytics"
  ON call_analytics FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Users can update call analytics"
  ON call_analytics FOR UPDATE
  TO authenticated
  USING (true);

CREATE POLICY "Users can view coaching alerts"
  ON coaching_alerts FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can insert coaching alerts"
  ON coaching_alerts FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Users can update coaching alerts"
  ON coaching_alerts FOR UPDATE
  TO authenticated
  USING (true);

-- Helper view for team quality trends
CREATE OR REPLACE VIEW call_quality_trends AS
SELECT
  user_id,
  DATE_TRUNC('day', created_at) as day,
  COUNT(*) as calls_analyzed,
  AVG(call_quality_score) as avg_quality_score,
  AVG(talk_listen_ratio) as avg_talk_ratio,
  AVG(filler_word_count) as avg_filler_words,
  AVG(agent_questions) as avg_questions,
  SUM(CASE WHEN call_quality_score >= 70 THEN 1 ELSE 0 END) as good_calls,
  SUM(CASE WHEN call_quality_score < 50 THEN 1 ELSE 0 END) as poor_calls
FROM call_analytics
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY user_id, DATE_TRUNC('day', created_at)
ORDER BY day DESC;
