-- ================================================
-- GAMIFICATION BATTLEGROUND - DATABASE MIGRATION
-- ================================================
-- Created: Jan 17, 2026
-- Feature: Priority #7 - Sales Team Battleground

-- 1. HALL OF FAME - Records that stand forever
CREATE TABLE IF NOT EXISTS hall_of_fame (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  record_type VARCHAR(50) NOT NULL,  -- 'most_calls_day', 'biggest_deal', etc.
  user_id UUID NOT NULL,
  user_name VARCHAR(100) NOT NULL,
  value NUMERIC NOT NULL,            -- The record value
  details JSONB,                      -- Extra context (lead name, etc.)
  achieved_at TIMESTAMPTZ DEFAULT NOW(),
  previous_holder_id UUID,
  previous_value NUMERIC,
  UNIQUE(record_type)                -- Only one record per type
);

CREATE INDEX IF NOT EXISTS idx_hall_of_fame_type ON hall_of_fame(record_type);

-- 2. WEEKLY BADGES - Who owned the week
CREATE TABLE IF NOT EXISTS weekly_badges (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  badge_type VARCHAR(50) NOT NULL,   -- 'closer', 'dialer', 'speed_demon', etc.
  week_start DATE NOT NULL,
  week_end DATE NOT NULL,
  value NUMERIC,                      -- Stat that earned it
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(badge_type, week_start)     -- One badge per type per week
);

CREATE INDEX IF NOT EXISTS idx_weekly_badges_user ON weekly_badges(user_id);
CREATE INDEX IF NOT EXISTS idx_weekly_badges_week ON weekly_badges(week_start DESC);

-- 3. DAILY CHALLENGES - Individual + Team
CREATE TABLE IF NOT EXISTS daily_challenges (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  date DATE NOT NULL DEFAULT CURRENT_DATE,
  challenge_type VARCHAR(20) NOT NULL,  -- 'individual' or 'team'
  title VARCHAR(100) NOT NULL,
  description TEXT,
  target_value INTEGER NOT NULL,
  current_value INTEGER DEFAULT 0,
  bonus_xp INTEGER DEFAULT 50,
  completed BOOLEAN DEFAULT FALSE,
  completed_at TIMESTAMPTZ,
  user_id UUID,                         -- NULL for team challenges
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(date, challenge_type, user_id)
);

CREATE INDEX IF NOT EXISTS idx_daily_challenges_date ON daily_challenges(date DESC);

-- 4. TEAM PULSE FEED - Live activity stream
CREATE TABLE IF NOT EXISTS team_pulse (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type VARCHAR(50) NOT NULL,    -- 'conversion', 'record_broken', 'overtake', etc.
  user_id UUID NOT NULL,
  user_name VARCHAR(100) NOT NULL,
  title VARCHAR(200) NOT NULL,
  subtitle TEXT,
  emoji VARCHAR(10),
  link_type VARCHAR(50),              -- 'lead', 'conversation', 'leaderboard'
  link_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_team_pulse_created ON team_pulse(created_at DESC);

-- 5. LEADERBOARD SNAPSHOTS - For position tracking
CREATE TABLE IF NOT EXISTS leaderboard_snapshots (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  period VARCHAR(20) NOT NULL,        -- 'today', 'week', 'month', 'all'
  snapshot_date DATE NOT NULL,
  position INTEGER NOT NULL,
  calls INTEGER DEFAULT 0,
  conversions INTEGER DEFAULT 0,
  xp INTEGER DEFAULT 0,
  streak INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, period, snapshot_date)
);

CREATE INDEX IF NOT EXISTS idx_lb_snapshots_date ON leaderboard_snapshots(snapshot_date DESC, period);

-- 6. STREAK MILESTONES - Track streak achievements
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS streak_tier VARCHAR(20) DEFAULT 'none';
-- Tiers: 'none', 'bronze' (3), 'silver' (7), 'gold' (14), 'diamond' (30)

-- 7. USER PROFILE EXTENSIONS
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS display_name VARCHAR(100);
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS avatar_url TEXT;
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS current_badges JSONB DEFAULT '[]';
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS status VARCHAR(50) DEFAULT 'grinding';
-- Statuses: 'grinding', 'on_fire', 'crushing_it', 'in_the_zone'

-- Enable realtime on team_pulse for live updates
-- NOTE: Run this separately if it fails (may need admin)
-- ALTER PUBLICATION supabase_realtime ADD TABLE team_pulse;
