-- Achievement System Migration
-- Adds gamification with achievements, XP, levels, and streaks

-- ============================================
-- PART 1: User XP and Levels
-- ============================================

CREATE TABLE IF NOT EXISTS user_xp (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL,
  total_xp INTEGER DEFAULT 0,
  level INTEGER DEFAULT 1,
  current_streak INTEGER DEFAULT 0,
  longest_streak INTEGER DEFAULT 0,
  last_activity_date DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id)
);

CREATE INDEX IF NOT EXISTS idx_user_xp_user ON user_xp(user_id);
CREATE INDEX IF NOT EXISTS idx_user_xp_level ON user_xp(level DESC);
CREATE INDEX IF NOT EXISTS idx_user_xp_total ON user_xp(total_xp DESC);

-- ============================================
-- PART 2: Achievements
-- ============================================

CREATE TABLE IF NOT EXISTS user_achievements (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL,
  achievement_id VARCHAR(50) NOT NULL,
  unlocked_at TIMESTAMPTZ DEFAULT NOW(),
  metadata JSONB DEFAULT '{}',
  UNIQUE(user_id, achievement_id)
);

CREATE INDEX IF NOT EXISTS idx_user_achievements_user ON user_achievements(user_id);
CREATE INDEX IF NOT EXISTS idx_user_achievements_id ON user_achievements(achievement_id);

-- ============================================
-- PART 3: XP History (for tracking earnings)
-- ============================================

CREATE TABLE IF NOT EXISTS xp_history (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL,
  amount INTEGER NOT NULL,
  reason VARCHAR(100) NOT NULL,
  related_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_xp_history_user ON xp_history(user_id);
CREATE INDEX IF NOT EXISTS idx_xp_history_date ON xp_history(created_at DESC);

-- ============================================
-- PART 4: Call Scores (for coaching phase)
-- ============================================

CREATE TABLE IF NOT EXISTS call_scores (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  call_id UUID NOT NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  user_id UUID,
  scores JSONB NOT NULL DEFAULT '{}',
  overall_score INTEGER DEFAULT 0,
  ai_feedback TEXT,
  transcript_summary TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_call_scores_call ON call_scores(call_id);
CREATE INDEX IF NOT EXISTS idx_call_scores_lead ON call_scores(lead_id);
CREATE INDEX IF NOT EXISTS idx_call_scores_user ON call_scores(user_id);

-- ============================================
-- PART 5: Winning Patterns (for analytics phase)
-- ============================================

CREATE TABLE IF NOT EXISTS winning_patterns (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  pattern_type VARCHAR(50) NOT NULL,
  description TEXT NOT NULL,
  data JSONB DEFAULT '{}',
  confidence DECIMAL(5,2) DEFAULT 0,
  sample_count INTEGER DEFAULT 0,
  discovered_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_winning_patterns_type ON winning_patterns(pattern_type);
CREATE INDEX IF NOT EXISTS idx_winning_patterns_confidence ON winning_patterns(confidence DESC);

-- ============================================
-- PART 6: AI Alerts (for AI layer phase)
-- ============================================

CREATE TABLE IF NOT EXISTS ai_alerts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID,
  alert_type VARCHAR(50) NOT NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  message TEXT,
  data JSONB DEFAULT '{}',
  priority VARCHAR(20) DEFAULT 'medium',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  read_at TIMESTAMPTZ,
  dismissed_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_ai_alerts_user ON ai_alerts(user_id);
CREATE INDEX IF NOT EXISTS idx_ai_alerts_lead ON ai_alerts(lead_id);
CREATE INDEX IF NOT EXISTS idx_ai_alerts_type ON ai_alerts(alert_type);
CREATE INDEX IF NOT EXISTS idx_ai_alerts_pending ON ai_alerts(user_id) WHERE dismissed_at IS NULL AND read_at IS NULL;

-- ============================================
-- PART 7: Weekly Summaries
-- ============================================

CREATE TABLE IF NOT EXISTS weekly_summaries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL,
  week_start DATE NOT NULL,
  summary JSONB NOT NULL DEFAULT '{}',
  insights TEXT[],
  recommendations TEXT[],
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, week_start)
);

CREATE INDEX IF NOT EXISTS idx_weekly_summaries_user ON weekly_summaries(user_id);
CREATE INDEX IF NOT EXISTS idx_weekly_summaries_week ON weekly_summaries(week_start DESC);

-- ============================================
-- PART 8: Helper function for level calculation
-- ============================================

CREATE OR REPLACE FUNCTION calculate_level(xp INTEGER)
RETURNS INTEGER AS $$
BEGIN
  -- Level formula: Level = floor(sqrt(xp / 100)) + 1
  -- Level 1: 0-99 XP
  -- Level 2: 100-399 XP
  -- Level 3: 400-899 XP
  -- Level 4: 900-1599 XP
  -- etc.
  RETURN GREATEST(1, FLOOR(SQRT(xp::FLOAT / 100)) + 1);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- ============================================
-- PART 9: Trigger to auto-update level
-- ============================================

CREATE OR REPLACE FUNCTION update_user_level()
RETURNS TRIGGER AS $$
BEGIN
  NEW.level := calculate_level(NEW.total_xp);
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_user_level ON user_xp;
CREATE TRIGGER trigger_update_user_level
  BEFORE UPDATE OF total_xp ON user_xp
  FOR EACH ROW
  EXECUTE FUNCTION update_user_level();
