-- Migration: Atomic XP Increment Function
-- Date: 2026-01-22
-- Description: Fixes race condition in XP updates by using FOR UPDATE row locking
-- Severity: HIGH - Data corruption under concurrent requests

-- Atomic XP increment function
-- Handles: XP addition, level calculation, streak updates
CREATE OR REPLACE FUNCTION add_user_xp(
  p_user_id UUID,
  p_xp_amount INTEGER,
  p_action TEXT DEFAULT NULL,
  p_reason TEXT DEFAULT NULL
)
RETURNS JSON AS $$
DECLARE
  v_result JSON;
  v_old_xp INTEGER;
  v_new_xp INTEGER;
  v_old_level INTEGER;
  v_new_level INTEGER;
  v_level_up BOOLEAN := FALSE;
  v_streak INTEGER;
  v_streak_bonus INTEGER := 0;
  v_last_activity DATE;
  v_today DATE := CURRENT_DATE;
BEGIN
  -- Lock the row for update (prevents race condition)
  SELECT total_xp, level, current_streak, last_activity_date
  INTO v_old_xp, v_old_level, v_streak, v_last_activity
  FROM user_xp
  WHERE user_id = p_user_id
  FOR UPDATE;

  IF NOT FOUND THEN
    -- Create new record
    INSERT INTO user_xp (user_id, total_xp, level, current_streak, longest_streak, last_activity_date)
    VALUES (p_user_id, p_xp_amount, 1, 1, 1, v_today)
    RETURNING json_build_object(
      'total_xp', total_xp,
      'level', level,
      'level_up', false,
      'streak', current_streak,
      'streak_bonus', 0
    ) INTO v_result;

    RETURN v_result;
  END IF;

  -- Calculate streak bonus
  IF v_last_activity IS NOT NULL THEN
    IF v_today - v_last_activity = 1 THEN
      -- Consecutive day
      v_streak := v_streak + 1;
      v_streak_bonus := LEAST(v_streak * 5, 100); -- Cap at 100
    ELSIF v_today - v_last_activity > 1 THEN
      -- Streak broken
      v_streak := 1;
    END IF;
    -- Same day: keep streak as is
  ELSE
    v_streak := 1;
  END IF;

  -- Calculate new XP and level
  v_new_xp := v_old_xp + p_xp_amount + v_streak_bonus;
  v_new_level := GREATEST(1, FLOOR(SQRT(v_new_xp / 100.0))::INTEGER);
  v_level_up := v_new_level > v_old_level;

  -- Atomic update
  UPDATE user_xp
  SET
    total_xp = v_new_xp,
    level = v_new_level,
    current_streak = v_streak,
    longest_streak = GREATEST(longest_streak, v_streak),
    last_activity_date = v_today
  WHERE user_id = p_user_id;

  -- Return result
  v_result := json_build_object(
    'total_xp', v_new_xp,
    'old_xp', v_old_xp,
    'xp_gained', p_xp_amount + v_streak_bonus,
    'level', v_new_level,
    'old_level', v_old_level,
    'level_up', v_level_up,
    'streak', v_streak,
    'streak_bonus', v_streak_bonus
  );

  RETURN v_result;
END;
$$ LANGUAGE plpgsql;
