-- 1. Create the 'games' table CREATE TABLE games ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), variant TEXT NOT NULL, starting_fen TEXT NOT NULL, current_fen TEXT NOT NULL, white_id TEXT, black_id TEXT, status TEXT NOT NULL DEFAULT 'LOBBY', result JSONB, move_count INTEGER DEFAULT 0, created_at TIMESTAMPTZ DEFAULT now(), last_move_at TIMESTAMPTZ DEFAULT now() );
-- 2. Create the 'moves' table CREATE TABLE moves ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, game_id UUID NOT NULL REFERENCES games(id) ON DELETE CASCADE, move_index INTEGER NOT NULL, from_square TEXT NOT NULL, to_square TEXT NOT NULL, promotion TEXT, san TEXT NOT NULL, fen_after TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT now(), -- Ensure each move index is unique per game UNIQUE(game_id, move_index) );
-- 3. Enable Realtime for the 'moves' table -- This allows the app to listen for new moves instantly alter publication supabase_realtime add table moves;