-- Rep Performance Schema
-- Tables for storing weekly rep performance snapshots and AI call scores

-- ============================================
-- Rep Weekly Snapshots
-- Stores weekly performance data per rep for trend analysis
-- ============================================

CREATE TABLE IF NOT EXISTS rep_weekly_snapshots (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  display_name TEXT,

  -- Period
  week_start DATE NOT NULL,
  week_end DATE NOT NULL,

  -- Activity metrics
  total_calls INTEGER DEFAULT 0,
  total_sms INTEGER DEFAULT 0,
  inbound_sms INTEGER DEFAULT 0,
  outbound_sms INTEGER DEFAULT 0,

  -- Call metrics
  avg_call_duration_seconds INTEGER DEFAULT 0,
  total_call_duration_seconds INTEGER DEFAULT 0,
  calls_with_duration INTEGER DEFAULT 0,

  -- Outcome metrics
  conversions INTEGER DEFAULT 0,
  conversion_rate NUMERIC(5,2) DEFAULT 0,

  -- Quality metrics (from AI scoring)
  avg_call_score NUMERIC(3,1) DEFAULT 0,
  calls_scored INTEGER DEFAULT 0,

  -- Talk ratio (from transcript analysis)
  avg_talk_ratio NUMERIC(5,2) DEFAULT 0,  -- Rep talk percentage

  -- Objection handling
  objections_faced INTEGER DEFAULT 0,
  objections_overcome INTEGER DEFAULT 0,

  -- Best performance windows
  best_hour INTEGER,  -- Hour of day with most conversions
  best_day TEXT,      -- Day of week with most conversions

  -- Comparison to previous week
  calls_change_pct NUMERIC(5,2),
  conversion_rate_change NUMERIC(5,2),

  -- Strengths and weaknesses (AI analyzed)
  strengths JSONB DEFAULT '[]'::jsonb,
  weaknesses JSONB DEFAULT '[]'::jsonb,
  coaching_tips JSONB DEFAULT '[]'::jsonb,

  -- Top performing calls (for examples)
  top_calls JSONB DEFAULT '[]'::jsonb,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for fast lookups
CREATE INDEX IF NOT EXISTS idx_rep_weekly_user_week
ON rep_weekly_snapshots(user_id, week_start DESC);

CREATE UNIQUE INDEX IF NOT EXISTS idx_rep_weekly_unique
ON rep_weekly_snapshots(user_id, week_start);

COMMENT ON TABLE rep_weekly_snapshots IS 'Weekly performance snapshots per rep for trend analysis';


-- ============================================
-- Call Quality Scores
-- AI-scored calls with detailed breakdown
-- ============================================

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

  -- Overall score (1-10)
  overall_score NUMERIC(3,1) NOT NULL,

  -- Component scores
  opening_score NUMERIC(3,1),      -- How well they opened
  rapport_score NUMERIC(3,1),      -- Building rapport
  discovery_score NUMERIC(3,1),    -- Asking questions
  objection_score NUMERIC(3,1),    -- Handling objections
  closing_score NUMERIC(3,1),      -- Closing technique

  -- Call metrics
  call_duration_seconds INTEGER,
  talk_ratio NUMERIC(5,2),         -- Rep talk percentage (0-100)

  -- Analysis
  strengths JSONB DEFAULT '[]'::jsonb,
  improvements JSONB DEFAULT '[]'::jsonb,
  coaching_note TEXT,

  -- Outcome
  call_outcome TEXT,
  led_to_conversion BOOLEAN DEFAULT FALSE,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_call_scores_user
ON call_quality_scores(user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_call_scores_comm
ON call_quality_scores(communication_id);

COMMENT ON TABLE call_quality_scores IS 'AI-scored call quality for rep coaching';


-- ============================================
-- RLS Policies
-- ============================================

ALTER TABLE rep_weekly_snapshots ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Authenticated users can read rep snapshots" ON rep_weekly_snapshots;
CREATE POLICY "Authenticated users can read rep snapshots" ON rep_weekly_snapshots
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "Service can insert rep snapshots" ON rep_weekly_snapshots;
CREATE POLICY "Service can insert rep snapshots" ON rep_weekly_snapshots
  FOR INSERT WITH CHECK (true);

DROP POLICY IF EXISTS "Service can update rep snapshots" ON rep_weekly_snapshots;
CREATE POLICY "Service can update rep snapshots" ON rep_weekly_snapshots
  FOR UPDATE USING (true);


ALTER TABLE call_quality_scores ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Authenticated users can read call scores" ON call_quality_scores;
CREATE POLICY "Authenticated users can read call scores" ON call_quality_scores
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "Service can insert call scores" ON call_quality_scores;
CREATE POLICY "Service can insert call scores" ON call_quality_scores
  FOR INSERT WITH CHECK (true);


-- ============================================
-- Verification
-- ============================================

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name IN ('rep_weekly_snapshots', 'call_quality_scores');
