
CREATE OR REPLACE FUNCTION tcg.public.FN_TCG_MTG_Game_Round_Player_Damage_Get_Many (
      a_access_user_id INT
	, a_get_all_game BOOLEAN
    , a_get_inactive_game BOOLEAN
	, a_game_ids TEXT
	, a_get_all_user BOOLEAN
	, a_get_inactive_user BOOLEAN
	, a_user_ids TEXT
    , a_require_all_id_filters_met BOOLEAN
    , a_require_any_id_filters_met BOOLEAN
)
RETURNS TABLE (
      damage_id INT
    , round_id INT
    , player_id INT
    , received_from_commander_player_id INT
    , life_gain INT
    , life_loss INT
    , commander_deaths INT
    , is_eliminated BOOLEAN
    , active BOOLEAN
    , created_on TIMESTAMP
    , created_by_user_id INT
	, updated_last_on TIMESTAMP
	, updated_last_by_user_id INT
    , change_set_id INT
    , game_created_on TIMESTAMP
    , round_display_order INT
    , player_display_order INT
)
LANGUAGE plpgsql
AS $$
DECLARE
	v_access_user_id INT;
	v_can_admin BOOLEAN;
    v_get_all_game BOOLEAN;
    v_get_inactive_game BOOLEAN;
	v_game_ids TEXT;
    v_get_all_user BOOLEAN;
    v_get_inactive_user BOOLEAN;
	v_user_ids TEXT;
    v_require_all_id_filters_met BOOLEAN;
    v_require_any_id_filters_met BOOLEAN;
BEGIN
	v_access_user_id := a_access_user_id;
	v_get_all_game := COALESCE(a_get_all_game, FALSE);
    v_get_inactive_game := COALESCE(a_get_inactive_game, FALSE);
	v_game_ids := TRIM(COALESCE(a_game_ids, ''));
	v_get_all_user := COALESCE(a_get_all_user, FALSE);
    v_get_inactive_user := COALESCE(a_get_inactive_user, FALSE);
	v_user_ids := TRIM(COALESCE(a_user_ids, ''));
    v_require_all_id_filters_met := COALESCE(a_require_all_id_filters_met, FALSE);
    v_require_any_id_filters_met := COALESCE(a_require_any_id_filters_met, FALSE);
	
    IF NOT EXISTS (SELECT * FROM tcg.public.TCG_User TCG_USER WHERE TCG_USER.user_id = v_access_user_id LIMIT 1) THEN
		RAISE EXCEPTION 'Access User ID required.';
    END IF;

	v_can_admin := COALESCE((SELECT TCG_USER.is_super_user FROM tcg.public.TCG_User TCG_USER WHERE TCG_USER.user_id = v_access_user_id LIMIT 1), FALSE);
	
	-- Outputs
    RETURN QUERY
	WITH Access_Player AS (
		SELECT
			  PLAYER.player_id
			, PLAYER.game_id
			, PLAYER.user_id
			, PLAYER.deck_id
			, PLAYER.active
		FROM tcg.public.FN_TCG_MTG_Game_Player_Get_Many (
			  a_access_user_id := v_access_user_id
			, a_get_all_game := v_get_all_game
		    , a_get_inactive_game := v_get_inactive_game
		    , a_game_ids := v_game_ids
		    , a_get_all_user := v_get_all_user
		    , a_get_inactive_user := v_get_inactive_user
		    , a_user_ids := v_user_ids
		    , a_require_all_id_filters_met := v_require_all_id_filters_met
		    , a_require_any_id_filters_met := v_require_any_id_filters_met
		) PLAYER
	)
	SELECT DISTINCT
          PLAYER_DAMAGE.damage_id
        , PLAYER_DAMAGE.round_id
        , PLAYER_DAMAGE.player_id
        , PLAYER_DAMAGE.received_from_commander_player_id
        , PLAYER_DAMAGE.life_gain
        , PLAYER_DAMAGE.life_loss
        , PLAYER_DAMAGE.commander_deaths
		, PLAYER_DAMAGE.is_eliminated
        , PLAYER_DAMAGE.active
        , PLAYER_DAMAGE.created_on
        , PLAYER_DAMAGE.created_by_user_id
        , PLAYER_DAMAGE.updated_last_on
        , PLAYER_DAMAGE.updated_last_by_user_id
        , PLAYER_DAMAGE.change_set_id
        , GAME.created_on AS game_created_on
        , GAME_ROUND.display_order AS round_display_order
        , PLAYER.display_order AS player_display_order
    FROM tcg.public.TCG_MTG_Game_Round_Player_Damage PLAYER_DAMAGE
	INNER JOIN Access_Player ACCESS_PLAYER ON PLAYER_DAMAGE.player_id = ACCESS_PLAYER.player_id
    INNER JOIN tcg.public.TCG_MTG_Game_Round GAME_ROUND ON PLAYER_DAMAGE.round_id = GAME_ROUND.round_id
    INNER JOIN tcg.public.TCG_MTG_Game GAME ON GAME_ROUND.game_id = GAME.game_id
	INNER JOIN tcg.public.TCG_MTG_Game_Player PLAYER ON PLAYER_DAMAGE.player_id = PLAYER.player_id
    WHERE
            (
                (
                    NOT v_require_all_id_filters_met
                AND NOT v_require_any_id_filters_met
            )
            OR  (
                    v_require_all_id_filters_met
                AND (
                        v_get_all_game
                    OR  GAME.game_id = ANY(string_to_array(v_game_ids, ',')::INT[])
                )
            )
            OR  (
                    NOT v_require_all_id_filters_met
                AND v_require_any_id_filters_met
                AND (
                        v_get_all_game
                    OR  GAME.game_id = ANY(string_to_array(v_game_ids, ',')::INT[])
                )
            )
        )
        AND (
                v_get_inactive_game
            OR  PLAYER_DAMAGE.active
        )
	ORDER BY
          GAME.created_on DESC
        , GAME_ROUND.display_order
        , PLAYER.display_order
		, PLAYER_DAMAGE.damage_id
    ;
END;
$$;


SELECT *
FROM tcg.public.FN_TCG_MTG_Game_Round_Player_Damage_Get_Many (
      a_access_user_id := 3
	, a_get_all_game := FALSE
    , a_get_inactive_game := FALSE
    , a_game_ids := '30'
	, a_get_all_user := TRUE
    , a_get_inactive_user := FALSE
    , a_user_ids := ''
    , a_require_all_id_filters_met := TRUE 
    , a_require_any_id_filters_met := FALSE 
)
;


SELECT *
FROM tcg.public.TCG_MTG_Game
;
SELECT *
FROM tcg.public.TCG_MTG_Game_Round_Player_Damage
;
