-- This script backfills XP for all existing study sessions and updates user
-- profiles. It's designed to be run once.

-- =============================================================================
-- Part 1: Create missing xp_events for all completed study sessions. This is
-- safe to run multiple times, as it will only insert records that don't exist.
-- =============================================================================
INSERT INTO xp_events (did, session_rkey, xp_gained)
SELECT
    ss.did,
    ss.rkey,
    (ss.duration / 60) * 10                     -- xp_gained (10 XP per minute)
FROM
    study_sessions ss
LEFT JOIN
    xp_events x ON ss.did = x.did AND ss.rkey = x.session_rkey
WHERE
    x.id IS NULL AND ss.duration >= 60;


-- =============================================================================
-- Part 2: Reset and Recalculate all user XP and Levels. WARNING: This will
-- reset all users to Level 1 and then recalculate their total XP from scratch
-- based on ALL their xp_events.
-- =============================================================================

-- Step 2.1: Reset all profiles to a clean slate.
UPDATE profiles
SET
    xp = 0,
    level = 0;

-- Step 2.2: Create a temporary table to hold the total XP for each user.
-- This makes the final update statement cleaner and more compatible across
-- SQLite versions.
CREATE TEMP TABLE user_total_xp AS
SELECT
    did,
    SUM(xp_gained) as total_xp
FROM
    xp_events
GROUP BY
    did;

-- Step 2.3: Update the profiles table with the new total XP from the temp
-- table.
UPDATE profiles
SET
    xp = (
        SELECT total_xp
        FROM user_total_xp
        WHERE user_total_xp.did = profiles.did
    )
WHERE
    EXISTS (
        SELECT 1
        FROM user_total_xp
        WHERE user_total_xp.did = profiles.did
    );

-- Clean up the temporary table.
DROP TABLE user_total_xp;