-- AI Call Scoring Migration
-- GPT-powered call scoring with dimension breakdown
-- Handles partial existing table

-- Add ALL potentially missing columns using ADD COLUMN IF NOT EXISTS
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS communication_id UUID;
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS lead_id UUID;
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS overall_score DECIMAL(3,1);
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS grade VARCHAR(2);
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS rapport_score DECIMAL(3,1);
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS discovery_score DECIMAL(3,1);
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS value_prop_score DECIMAL(3,1);
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS objection_score DECIMAL(3,1);
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS close_score DECIMAL(3,1);
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS professionalism_score DECIMAL(3,1);
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS dimension_notes JSONB DEFAULT '{}';
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS highlights TEXT[] DEFAULT '{}';
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS improvements TEXT[] DEFAULT '{}';
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS coaching_tip TEXT;
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS transcript_length INTEGER;
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS call_duration_seconds INTEGER;
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS had_objection BOOLEAN DEFAULT FALSE;
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS outcome VARCHAR(50);
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS scored_at TIMESTAMPTZ DEFAULT NOW();
ALTER TABLE call_scores ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW();

-- Create indexes (safe to run multiple times)
CREATE INDEX IF NOT EXISTS idx_call_scores_user ON call_scores(user_id);
CREATE INDEX IF NOT EXISTS idx_call_scores_lead ON call_scores(lead_id);
CREATE INDEX IF NOT EXISTS idx_call_scores_date ON call_scores(scored_at DESC);
CREATE INDEX IF NOT EXISTS idx_call_scores_grade ON call_scores(grade);
CREATE INDEX IF NOT EXISTS idx_call_scores_overall ON call_scores(overall_score DESC);

-- View for score summaries by user
CREATE OR REPLACE VIEW user_call_score_summary AS
SELECT
  user_id,
  COUNT(*) as total_calls,
  AVG(overall_score)::DECIMAL(3,1) as avg_score,
  AVG(rapport_score)::DECIMAL(3,1) as avg_rapport,
  AVG(discovery_score)::DECIMAL(3,1) as avg_discovery,
  AVG(value_prop_score)::DECIMAL(3,1) as avg_value_prop,
  AVG(objection_score)::DECIMAL(3,1) as avg_objection,
  AVG(close_score)::DECIMAL(3,1) as avg_close,
  AVG(professionalism_score)::DECIMAL(3,1) as avg_professionalism,
  COUNT(CASE WHEN grade IN ('A+', 'A') THEN 1 END) as excellent_calls,
  COUNT(CASE WHEN grade IN ('D', 'F') THEN 1 END) as poor_calls
FROM call_scores
WHERE scored_at > NOW() - INTERVAL '30 days'
GROUP BY user_id;

-- RLS Policies
ALTER TABLE call_scores ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view call scores" ON call_scores;
CREATE POLICY "Users can view call scores"
  ON call_scores FOR SELECT
  TO authenticated
  USING (true);

DROP POLICY IF EXISTS "Users can insert call scores" ON call_scores;
CREATE POLICY "Users can insert call scores"
  ON call_scores FOR INSERT
  TO authenticated
  WITH CHECK (true);

DROP POLICY IF EXISTS "Users can update call scores" ON call_scores;
CREATE POLICY "Users can update call scores"
  ON call_scores FOR UPDATE
  TO authenticated
  USING (true);
