-- Migration 032: Fix session_sequences drift (CF-816)
-- Problem: Retro-imported sessions with explicit session_number bypass the trigger,
--          leaving session_sequences.next_number behind the actual MAX(session_number).
--          Next auto-assigned number then collides with the unique index.
-- Fix: Make get_next_session_number() self-healing by always checking actual max.

-- Step 1: Replace the function with a self-healing version
CREATE OR REPLACE FUNCTION get_next_session_number(p_project TEXT)
RETURNS INTEGER AS $$
DECLARE
    v_seq_number INTEGER;
    v_max_number INTEGER;
    v_number INTEGER;
BEGIN
    -- Insert project if doesn't exist
    INSERT INTO projects (key, name) VALUES (p_project, p_project)
    ON CONFLICT (key) DO NOTHING;

    -- Insert sequence if doesn't exist
    INSERT INTO session_sequences (project, next_number)
    VALUES (p_project, 1)
    ON CONFLICT (project) DO NOTHING;

    -- Get the actual max session_number for this project (handles external inserts)
    SELECT COALESCE(MAX(session_number), 0) INTO v_max_number
    FROM sessions
    WHERE project = p_project;

    -- Sync sequence forward if it fell behind (self-healing)
    UPDATE session_sequences
    SET next_number = GREATEST(next_number, v_max_number + 1),
        last_updated = NOW()
    WHERE project = p_project;

    -- Now atomically increment and return
    UPDATE session_sequences
    SET next_number = next_number + 1,
        last_updated = NOW()
    WHERE project = p_project
    RETURNING next_number - 1 INTO v_number;

    RETURN v_number;
END;
$$ LANGUAGE plpgsql;

-- Step 2: Sync all existing sequences to match actual data
UPDATE session_sequences sq
SET next_number = GREATEST(sq.next_number, sub.actual_max + 1),
    last_updated = NOW()
FROM (
    SELECT project, COALESCE(MAX(session_number), 0) AS actual_max
    FROM sessions
    WHERE project IS NOT NULL
    GROUP BY project
) sub
WHERE sq.project = sub.project
  AND sq.next_number <= sub.actual_max;