CREATE OR REPLACE PROCEDURE change_game_id(
   source_game_id INT,
   target_game_id INT
)
LANGUAGE plpgsql    
AS $$
BEGIN
    -- clean out the target
    DELETE FROM game_choice WHERE game_id = target_game_id;
    DELETE FROM game_assignment WHERE game_id = target_game_id;
    DELETE FROM game WHERE id = target_game_id;

    -- clone soure game to target
    INSERT INTO game (id, late_finish, player_max, player_min, room_id, slot_id, players_contact_gm, slot_preference, teen_friendly, "year", author_id, "full", game_contact_email, genre, gm_names, message, description, player_preference, late_start, name, "type", returning_players, setting, short_name, slot_conflicts, char_instructions, estimated_length) SELECT target_game_id, late_finish, player_max, player_min, room_id, slot_id, players_contact_gm, slot_preference, teen_friendly, "year", author_id, "full", game_contact_email, genre, gm_names, message, description, player_preference, late_start, name, "type", returning_players, setting, short_name, slot_conflicts, char_instructions, estimated_length FROM game WHERE id = source_game_id;
     -- update dependencies
    UPDATE game_choice SET game_id = target_game_id WHERE game_id = source_game_id;
    UPDATE game_assignment SET game_id = target_game_id WHERE game_id = source_game_id;
     -- remove cloned game
    DELETE FROM game WHERE id = source_game_id;

    COMMIT;
END;$$


CREATE OR REPLACE PROCEDURE swap_game_ids(
   game_id_a INT,
   game_id_b INT
)
LANGUAGE plpgsql    
AS $$
BEGIN
	-- swap 'em through id = -1 as a buffer
    CALL change_game_id(game_id_a, -1);
    CALL change_game_id(game_id_b, game_id_a);
    CALL change_game_id(-1, game_id_b);

    COMMIT;
END;$$


-- any game
SELECT * FROM game WHERE id IN (144,604);
SELECT game_id, COUNT(*) AS "game_choice" FROM game_choice WHERE game_id IN (144,604) GROUP BY game_id;
SELECT game_id, COUNT(*) AS "game_assignment" FROM game_assignment WHERE game_id IN (144,604) GROUP BY game_id;

CALL swap_game_ids( 144, 604);

SELECT * FROM game WHERE id IN (144,604);
SELECT game_id, COUNT(*) AS "game_choice" FROM game_choice WHERE game_id IN (144,604) GROUP BY game_id;
SELECT game_id, COUNT(*) AS "game_assignment" FROM game_assignment WHERE game_id IN (144,604) GROUP BY game_id;

-- no game in slot x
CALL swap_game_ids( 596, 1);
CALL swap_game_ids( 597, 2);
CALL swap_game_ids( 598, 3);
CALL swap_game_ids( 599, 4);
CALL swap_game_ids( 600, 5);
CALL swap_game_ids( 601, 6);
CALL swap_game_ids( 602, 7);
CALL swap_game_ids( 603, 8);

DROP PROCEDURE IF EXISTS swap_game_ids;

DROP PROCEDURE IF EXISTS change_game_id;