-- Battleground V2: XP Duels + Manager Challenges
-- Created: Jan 19, 2026

-- ===========================================
-- XP DUELS - Head-to-head challenges
-- ===========================================

CREATE TABLE IF NOT EXISTS duels (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  challenger_id UUID NOT NULL,
  target_id UUID NOT NULL,
  duel_type VARCHAR(50) NOT NULL, -- 'calls_hour', 'calls_day', 'conversions_day', 'response_time_day'
  stake_amount INTEGER NOT NULL DEFAULT 100,
  challenger_odds DECIMAL(4,2) DEFAULT 1.00, -- e.g., 2.50 means challenger is 2.5:1 favorite
  status VARCHAR(20) DEFAULT 'pending', -- pending, accepted, active, completed, declined, expired
  challenger_score INTEGER DEFAULT 0,
  target_score INTEGER DEFAULT 0,
  winner_id UUID,
  xp_won INTEGER, -- Amount of XP transferred to winner
  starts_at TIMESTAMPTZ,
  ends_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

-- Indexes for duels
CREATE INDEX IF NOT EXISTS idx_duels_challenger ON duels(challenger_id);
CREATE INDEX IF NOT EXISTS idx_duels_target ON duels(target_id);
CREATE INDEX IF NOT EXISTS idx_duels_status ON duels(status) WHERE status IN ('pending', 'active');
CREATE INDEX IF NOT EXISTS idx_duels_active ON duels(ends_at) WHERE status = 'active';

-- ===========================================
-- MANAGER CHALLENGES - Custom team challenges
-- ===========================================

CREATE TABLE IF NOT EXISTS manager_challenges (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_by UUID NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  metric VARCHAR(50) NOT NULL, -- 'calls', 'conversions', 'response_time', 'talk_time'
  metric_goal VARCHAR(20) NOT NULL DEFAULT 'most', -- 'most', 'first', 'threshold'
  target_value INTEGER, -- For threshold-based challenges
  reward_type VARCHAR(50) NOT NULL DEFAULT 'xp_multiplier', -- 'xp_multiplier', 'real_prize', 'bragging_rights'
  reward_value INTEGER DEFAULT 2, -- XP multiplier or prize value
  reward_description TEXT, -- "Pizza party", "$50 bonus", etc.
  status VARCHAR(20) DEFAULT 'active', -- active, completed, cancelled
  winner_id UUID,
  starts_at TIMESTAMPTZ DEFAULT NOW(),
  ends_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for manager challenges
CREATE INDEX IF NOT EXISTS idx_manager_challenges_status ON manager_challenges(status) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS idx_manager_challenges_ends ON manager_challenges(ends_at) WHERE status = 'active';

-- ===========================================
-- CHALLENGE PARTICIPANTS - Who's competing
-- ===========================================

CREATE TABLE IF NOT EXISTS challenge_participants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  challenge_id UUID REFERENCES manager_challenges(id) ON DELETE CASCADE,
  user_id UUID NOT NULL,
  current_score INTEGER DEFAULT 0,
  joined_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(challenge_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_challenge_participants_challenge ON challenge_participants(challenge_id);
CREATE INDEX IF NOT EXISTS idx_challenge_participants_user ON challenge_participants(user_id);

-- ===========================================
-- DUEL HISTORY - For odds calculation
-- ===========================================

CREATE TABLE IF NOT EXISTS duel_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  duels_won INTEGER DEFAULT 0,
  duels_lost INTEGER DEFAULT 0,
  total_xp_won INTEGER DEFAULT 0,
  total_xp_lost INTEGER DEFAULT 0,
  current_duel_streak INTEGER DEFAULT 0,
  best_duel_streak INTEGER DEFAULT 0,
  last_duel_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_duel_history_user ON duel_history(user_id);

-- ===========================================
-- CELEBRATION EVENTS - Team-wide notifications
-- ===========================================

CREATE TABLE IF NOT EXISTS celebration_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type VARCHAR(50) NOT NULL, -- 'conversion', 'duel_win', 'streak_milestone', 'challenge_win'
  user_id UUID NOT NULL,
  title VARCHAR(200) NOT NULL,
  subtitle TEXT,
  xp_amount INTEGER,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Keep only recent celebrations (auto-cleanup via index)
CREATE INDEX IF NOT EXISTS idx_celebration_events_recent ON celebration_events(created_at DESC);

-- ===========================================
-- RLS POLICIES
-- ===========================================

ALTER TABLE duels ENABLE ROW LEVEL SECURITY;
ALTER TABLE manager_challenges ENABLE ROW LEVEL SECURITY;
ALTER TABLE challenge_participants ENABLE ROW LEVEL SECURITY;
ALTER TABLE duel_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE celebration_events ENABLE ROW LEVEL SECURITY;

-- Duels: Users can see all duels, create their own
CREATE POLICY "Users can view all duels" ON duels FOR SELECT TO authenticated USING (true);
CREATE POLICY "Users can create duels" ON duels FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "Users can update their duels" ON duels FOR UPDATE TO authenticated USING (true);

-- Manager challenges: All can view, managers can create
CREATE POLICY "Users can view challenges" ON manager_challenges FOR SELECT TO authenticated USING (true);
CREATE POLICY "Users can create challenges" ON manager_challenges FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "Users can update challenges" ON manager_challenges FOR UPDATE TO authenticated USING (true);

-- Participants: All can view and join
CREATE POLICY "Users can view participants" ON challenge_participants FOR SELECT TO authenticated USING (true);
CREATE POLICY "Users can join challenges" ON challenge_participants FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "Users can update own participation" ON challenge_participants FOR UPDATE TO authenticated USING (true);

-- Duel history: Users can see all
CREATE POLICY "Users can view duel history" ON duel_history FOR SELECT TO authenticated USING (true);
CREATE POLICY "System can manage duel history" ON duel_history FOR ALL TO authenticated USING (true);

-- Celebrations: All can view
CREATE POLICY "Users can view celebrations" ON celebration_events FOR SELECT TO authenticated USING (true);
CREATE POLICY "System can create celebrations" ON celebration_events FOR INSERT TO authenticated WITH CHECK (true);

-- ===========================================
-- HELPER FUNCTIONS
-- ===========================================

-- Function to calculate user's average metric over last 30 days
CREATE OR REPLACE FUNCTION get_user_metric_average(p_user_id UUID, p_metric VARCHAR)
RETURNS DECIMAL AS $$
DECLARE
  avg_value DECIMAL;
BEGIN
  IF p_metric = 'calls' OR p_metric = 'calls_day' OR p_metric = 'calls_hour' THEN
    SELECT COALESCE(AVG(daily_count), 0) INTO avg_value
    FROM (
      SELECT DATE(created_at) as day, COUNT(*) as daily_count
      FROM communications
      WHERE user_id = p_user_id
        AND type = 'call_outbound'
        AND created_at > NOW() - INTERVAL '30 days'
      GROUP BY DATE(created_at)
    ) daily;
  ELSIF p_metric = 'conversions' OR p_metric = 'conversions_day' THEN
    SELECT COALESCE(AVG(daily_count), 0) INTO avg_value
    FROM (
      SELECT DATE(created_at) as day, COUNT(*) as daily_count
      FROM leads
      WHERE assigned_to = p_user_id
        AND status = 'converted'
        AND created_at > NOW() - INTERVAL '30 days'
      GROUP BY DATE(created_at)
    ) daily;
  ELSE
    avg_value := 10; -- Default fallback
  END IF;

  RETURN COALESCE(avg_value, 1);
END;
$$ LANGUAGE plpgsql;

-- Function to expire old pending duels
CREATE OR REPLACE FUNCTION expire_pending_duels()
RETURNS void AS $$
BEGIN
  UPDATE duels
  SET status = 'expired'
  WHERE status = 'pending'
    AND created_at < NOW() - INTERVAL '24 hours';
END;
$$ LANGUAGE plpgsql;

-- Function to complete finished duels
CREATE OR REPLACE FUNCTION complete_finished_duels()
RETURNS void AS $$
BEGIN
  UPDATE duels
  SET status = 'completed',
      completed_at = NOW(),
      winner_id = CASE
        WHEN challenger_score > target_score THEN challenger_id
        WHEN target_score > challenger_score THEN target_id
        ELSE NULL -- Tie
      END,
      xp_won = CASE
        WHEN challenger_score > target_score THEN ROUND(stake_amount * challenger_odds)
        WHEN target_score > challenger_score THEN ROUND(stake_amount / NULLIF(challenger_odds, 0))
        ELSE 0
      END
  WHERE status = 'active'
    AND ends_at < NOW();
END;
$$ LANGUAGE plpgsql;
