-- Migration: Add processed_game ledger for full update_season_stats() idempotency
-- Date: 2026-03-18
-- Issue: #105
-- Purpose: Replace the last_game FK check in update_season_stats() with an
--          atomic INSERT into processed_game. This prevents out-of-order
--          re-delivery (game G re-delivered after G+1 was already processed)
--          from bypassing the guard and double-counting stats.

BEGIN;

CREATE TABLE IF NOT EXISTS processed_game (
    game_id INTEGER PRIMARY KEY REFERENCES stratgame(id) ON DELETE CASCADE,
    processed_at TIMESTAMP NOT NULL DEFAULT NOW()
);

COMMIT;

-- ============================================
-- VERIFICATION QUERIES
-- ============================================
-- \d processed_game

-- ============================================
-- ROLLBACK (if needed)
-- ============================================
-- DROP TABLE IF EXISTS processed_game;