
CREATE OR REPLACE PROCEDURE tcg.public.USP_TCG_MTG_Deck_Save (
      a_comment TEXT
    , a_guid UUID
    , a_user_id INT
	, OUT o_success BOOLEAN
)
LANGUAGE plpgsql
AS $$
DECLARE
	v_bad_data_error_type_id INT;
	v_change_set_id INT;
    v_comment TEXT;
	-- v_error_message TEXT;
    v_guid UUID;
	v_time_start TIMESTAMP;
    v_user_id INT;
	v_warning_error_type_id INT;
BEGIN
    v_comment := TRIM(COALESCE(a_comment, ''));
    v_guid := a_guid;
    v_user_id := a_user_id;

	v_bad_data_error_type_id := (SELECT ERROR_TYPE.error_type_id FROM tcg.public.Error_Type ERROR_TYPE WHERE ERROR_TYPE.code = 'BAD_DATA' LIMIT 1);
	v_warning_error_type_id := (SELECT ERROR_TYPE.error_type_id FROM tcg.public.Error_Type ERROR_TYPE WHERE ERROR_TYPE.code = 'WARNING' LIMIT 1);
	v_time_start := CURRENT_TIMESTAMP;
	
    IF NOT EXISTS (SELECT * FROM tcg.public.TCG_User TCG_USER WHERE TCG_USER.user_id = v_user_id LIMIT 1) THEN
		RAISE EXCEPTION 'Access User ID required.';
    END IF;

	DROP TABLE IF EXISTS Temp_MTG_Deck_Save_Deck;
	DROP TABLE IF EXISTS Temp_MTG_Deck_Save_Commander_Bracket;
	DROP TABLE IF EXISTS Temp_MTG_Deck_Save_Error;
        
    CREATE TEMP TABLE Temp_MTG_Deck_Save_Deck (
          temp_id INT NOT NULL
        , deck_id INT
        , name TEXT
        , is_commander BOOLEAN
        , commander_bracket_id INT
        , active BOOLEAN
        , created_on TIMESTAMP
        , created_by_user_id INT
        , updated_last_on TIMESTAMP
        , updated_last_by_user_id INT

        , is_new BOOLEAN
        , error_name TEXT
        , is_duplicate BOOLEAN
    );

    CREATE TEMP TABLE Temp_MTG_Deck_Save_Commander_Bracket (
          commander_bracket_id INT NOT NULL
        , name TEXT
        , description TEXT
        , display_order INT
        , active BOOLEAN
        , created_on TIMESTAMP
        , created_by_user_id INT
        , updated_last_on TIMESTAMP
        , updated_last_by_user_id INT
        , change_set_id INT
    );

	CREATE TEMP TABLE Temp_MTG_Deck_Save_Error (
		  temp_id INT GENERATED ALWAYS AS IDENTITY
		, error_type_id INT
		, message TEXT
	);

	INSERT INTO Temp_MTG_Deck_Save_Commander_Bracket (
          commander_bracket_id
        , name
        , description
        , display_order
        , active
        , created_on
        , created_by_user_id
        , updated_last_on
        , updated_last_by_user_id
        , change_set_id
    )
	SELECT
		  COMMANDER_BRACKET.commander_bracket_id
        , COMMANDER_BRACKET.name
        , COMMANDER_BRACKET.description
        , COMMANDER_BRACKET.display_order
        , COMMANDER_BRACKET.active
        , COMMANDER_BRACKET.created_on
        , COMMANDER_BRACKET.created_by_user_id
        , COMMANDER_BRACKET.updated_last_on
        , COMMANDER_BRACKET.updated_last_by_user_id
        , COMMANDER_BRACKET.change_set_id
	FROM tcg.public.TCG_MTG_Deck_Commander_Bracket COMMANDER_BRACKET
	;

	WITH Temp_Deck AS (
		SELECT
			  DECK_T.temp_id
            , CASE 
                WHEN
                        COALESCE(DECK_T.deck_id, 0) < 1
                    OR  DECK.deck_id IS NULL
				THEN DECK.deck_id
				ELSE DECK_T.deck_id
			END AS deck_id
            , TRIM(COALESCE(DECK_T.name, '')) AS name
            , COALESCE(DECK_T.is_commander, TRUE) AS is_commander
            , DECK_T.commander_bracket_id
	        , COALESCE(DECK_T.active, TRUE) AS active
	        , COALESCE(DECK_T.created_on, v_time_start) AS created_on
	        , COALESCE(DECK_T.created_by_user_id, v_user_id) AS created_by_user_id
	        , COALESCE(DECK_T.updated_last_on, v_time_start) AS updated_last_on
	        , COALESCE(DECK_T.updated_last_by_user_id, v_user_id) AS updated_last_by_user_id
		FROM tcg.public.TCG_MTG_Deck_Temp DECK_T
        LEFT JOIN tcg.public.TCG_MTG_Deck DECK
            ON  DECK.deck_id = DECK_T.deck_id
            AND DECK.created_by_user_id = v_user_id
		WHERE DECK_T.guid = v_guid
	)
    INSERT INTO Temp_MTG_Deck_Save_Deck (
          temp_id
        , deck_id
        , name
        , is_commander
        , commander_bracket_id
        , active
        , created_on
        , created_by_user_id
        , updated_last_on
        , updated_last_by_user_id

		, is_new
		, error_name
    )
	SELECT
          T_DECK.temp_id
        , T_DECK.deck_id
        , T_DECK.name
        , T_DECK.is_commander
        , T_DECK.commander_bracket_id
        , T_DECK.active
        , T_DECK.created_on
        , T_DECK.created_by_user_id
        , T_DECK.updated_last_on
        , T_DECK.updated_last_by_user_id

		, CASE WHEN T_DECK.deck_id IS NULL THEN TRUE ELSE FALSE END AS is_new
		, CONCAT(
			  'Deck { id: '
			, CAST(T_DECK.deck_id AS VARCHAR)
			, ', temp id: '
			, CAST(T_DECK.temp_id AS VARCHAR)
			, ', name: '
			, T_DECK.name
			, ' }'
		) -- error_name
	FROM Temp_Deck T_DECK
    LEFT JOIN tcg.public.TCG_MTG_Deck DECK
        ON  DECK.deck_id = T_DECK.deck_id
        AND DECK.created_by_user_id = v_user_id
	;

	-- Missing fields
	WITH Deck_Missing_Field AS (
        SELECT
              T_DECK.temp_id
            , 'name' AS field
        FROM Temp_MTG_Deck_Save_Deck T_DECK
        WHERE T_DECK.name = ''
        UNION
        SELECT
              T_DECK.temp_id
            , 'commander_bracket_id' AS field
        FROM Temp_MTG_Deck_Save_Deck T_DECK
        WHERE
                T_DECK.commander_bracket_id IS NULL
            AND T_DECK.is_commander
    )
	INSERT INTO Temp_MTG_Deck_Save_Error (
		  error_type_id
		, message
	)
	SELECT
		  v_bad_data_error_type_id -- error_type_id
		, CONCAT(
			'Missing mandatory field: '
			, DECK_MISSING_FIELD.field
			, ' on '
			, T_DECK.error_name
			, '.'
		) -- message
	FROM Temp_MTG_Deck_Save_Deck T_DECK
	INNER JOIN Deck_Missing_Field DECK_MISSING_FIELD ON T_DECK.temp_id = DECK_MISSING_FIELD.temp_id
	;

	-- Failed join fields
	WITH Deck_Failed_Join_Field AS (
        SELECT
              T_DECK.temp_id
            , 'commander_bracket_id' AS field
        FROM Temp_MTG_Deck_Save_Deck T_DECK
        LEFT JOIN Temp_MTG_Deck_Save_Commander_Bracket T_COMMANDER_BRACKET
			ON 	T_DECK.commander_bracket_id = T_COMMANDER_BRACKET.commander_bracket_id
			AND T_COMMANDER_BRACKET.active
        WHERE
                T_COMMANDER_BRACKET.commander_bracket_id IS NULL
            AND T_DECK.commander_bracket_id IS NOT NULL
    )
	INSERT INTO Temp_MTG_Deck_Save_Error (
		  error_type_id
		, message
	)
	SELECT
		  v_bad_data_error_type_id -- error_type_id
		, CONCAT(
			'Failed relationship: '
			, DECK_FAILED_JOIN_FIELD.field
			, ' on '
			, T_DECK.error_name
			, '.'
		) -- message
	FROM Temp_MTG_Deck_Save_Deck T_DECK
	INNER JOIN Deck_Failed_Join_Field DECK_FAILED_JOIN_FIELD ON T_DECK.temp_id = DECK_FAILED_JOIN_FIELD.temp_id
	;

	-- Duplicate Deck
	WITH 
		  All_Deck AS (
			SELECT
				  T_DECK.temp_id
                , T_DECK.created_by_user_id
                , T_DECK.name
                , TRUE AS is_save_record
			FROM Temp_MTG_Deck_Save_Deck T_DECK
            UNION
			SELECT
				  NULL AS temp_id
                , DECK.created_by_user_id
                , DECK.name
                , FALSE AS is_save_record
			FROM tcg.public.TCG_MTG_Deck DECK
            LEFT JOIN Temp_MTG_Deck_Save_Deck T_DECK ON DECK.deck_id = T_DECK.deck_id
            WHERE T_DECK.deck_id IS NULL
		)
		, Deck_Name_Count AS (
			SELECT
				  ALL_DECK.created_by_user_id
                , ALL_DECK.name
                , COUNT(*) AS count_deck
			FROM All_Deck ALL_DECK
            GROUP BY
                  ALL_DECK.created_by_user_id
                , ALL_DECK.name
		)
		, Duplicate_Deck AS (
			SELECT
				  ALL_DECK.temp_id
                , ALL_DECK.name
                , ALL_DECK.created_by_user_id
				, CASE WHEN COALESCE(DECK_NAME_COUNT.count_deck, 0) <= 1 THEN TRUE ELSE FALSE END AS is_unique
			FROM All_Deck ALL_DECK
            INNER JOIN Deck_Name_Count DECK_NAME_COUNT
                ON  ALL_DECK.name = DECK_NAME_COUNT.name
                AND ALL_DECK.created_by_user_id = DECK_NAME_COUNT.created_by_user_id
		)
	UPDATE Temp_MTG_Deck_Save_Deck T_DECK
	SET is_duplicate = NOT DUPLICATE_DECK.is_unique
	FROM Duplicate_Deck DUPLICATE_DECK
	WHERE T_DECK.temp_id = DUPLICATE_DECK.temp_id
	;
	
	IF EXISTS (
		SELECT *
		FROM Temp_MTG_Deck_Save_Deck T_DECK
		WHERE T_DECK.is_duplicate
		LIMIT 1
	) THEN
		INSERT INTO Temp_MTG_Deck_Save_Error (
			  error_type_id
			, message
		)
		SELECT
			  v_bad_data_error_type_id -- error_type_id
			, CONCAT(
				  'Duplicate on '
				, T_DECK.error_name
			) -- message
		FROM Temp_MTG_Deck_Save_Deck T_DECK
		WHERE T_DECK.is_duplicate
		;
	END IF;

	-- Nothing to save
	IF NOT EXISTS (SELECT * FROM Temp_MTG_Deck_Save_Deck LIMIT 1) THEN
		INSERT INTO Temp_MTG_Deck_Save_Error (
			  error_type_id
			, message
		)
		VALUES (
			  v_warning_error_type_id
			, 'Nothing to save.'
		);
	END IF;

	/*
		INSERT INTO Temp_MTG_Deck_Save_Error (
			  error_type_id
			, message
		)
		SELECT
			  v_warning_error_type_id AS error_type_id
			, CONCAT(
				  'Decks: '
				, STRING_AGG(T_DECK.error_name, ', ')
			) AS message
		FROM Temp_MTG_Deck_Save_Deck T_DECK
		;
	*/

	-- Outputs
	-- Change Set
	INSERT INTO tcg.public.TCG_Change_Set (
		  comment
		, updated_last_by_user_id
	)
	VALUES (
		  v_comment
		, v_user_id
	)
	RETURNING change_set_id INTO v_change_set_id
	;
	
	IF NOT EXISTS (SELECT * FROM Temp_MTG_Deck_Save_Error T_ERROR WHERE T_ERROR.error_type_id <> v_warning_error_type_id LIMIT 1) THEN
		
		-- Deck
		INSERT INTO tcg.public.TCG_MTG_Deck (
              name
            , is_commander
            , commander_bracket_id
            , active
            , created_on
            , created_by_user_id
            , updated_last_on
            , updated_last_by_user_id
            , change_set_id
		)
		SELECT
			  T_DECK.name
            , T_DECK.is_commander
            , T_DECK.commander_bracket_id
		    , T_DECK.active
            , T_DECK.created_on
            , T_DECK.created_by_user_id
            , T_DECK.updated_last_on
		    , T_DECK.updated_last_by_user_id
            , v_change_set_id -- change_set_id
		FROM Temp_MTG_Deck_Save_Deck T_DECK
		WHERE
				T_DECK.is_new
			AND T_DECK.active
		;
	
		UPDATE tcg.public.TCG_MTG_Deck DECK
		SET
              name = T_DECK.name
            , is_commander = T_DECK.is_commander
            , commander_bracket_id = T_DECK.commander_bracket_id
		    , active = T_DECK.active
			, updated_last_on = v_time_start
			, updated_last_by_user_id = v_user_id
		    , change_set_id = v_change_set_id
		FROM Temp_MTG_Deck_Save_Deck T_DECK
		WHERE
				DECK.deck_id = T_DECK.deck_id
			AND NOT T_DECK.is_new
		;

	END IF;

	-- Error
	INSERT INTO tcg.public.Error (
		  guid
		, error_type_id
		, message
		, display_order
		, created_on
		, created_by_user_id
		, updated_last_on
		, updated_last_by_user_id
		, change_set_id
	)
	SELECT
		  v_guid
		, T_ERROR.error_type_id
		, T_ERROR.message
		, T_ERROR.temp_id
		, v_time_start
		, v_user_id
		, v_time_start
		, v_user_id
		, v_change_set_id
	FROM Temp_MTG_Deck_Save_Error T_ERROR
	LEFT JOIN tcg.public.Error_Type ERROR_TYPE ON T_ERROR.error_type_id = ERROR_TYPE.error_type_id
	ORDER BY T_ERROR.temp_id
	;

    COMMIT;
	
	o_success := NOT EXISTS (
		SELECT *
		FROM Temp_MTG_Deck_Save_Error T_ERROR
		WHERE T_ERROR.error_type_id <> v_warning_error_type_id
		LIMIT 1
	);
	
	DELETE FROM tcg.public.TCG_MTG_Deck_Temp DECK_T WHERE DECK_T.guid = v_guid;

	DROP TABLE IF EXISTS Temp_MTG_Deck_Save_Deck;
	DROP TABLE IF EXISTS Temp_MTG_Deck_Save_Commander_Bracket;
	DROP TABLE IF EXISTS Temp_MTG_Deck_Save_Error;
END;
$$;



-- Call it with:
DO $$
DECLARE
	v_comment TEXT := 'MTG Deck Save.';
	v_guid UUID;
    v_user_id INT := 3;
    v_success BOOLEAN;
BEGIN
	v_guid := gen_random_uuid();

	INSERT INTO tcg.public.TCG_MTG_Deck_Temp (
		  guid
        , deck_id
        , name
        , is_commander
        , commander_bracket_id
        , active
	)
	VALUES
          (
              v_guid
            , 1 -- deck_id
            , 'Final Fantasy: Counter Blitz' -- name
            , TRUE -- is_commander
            , 2 -- commander_bracket_id
            , TRUE -- active
        )
        , (
              v_guid
            , 2 -- deck_id
            , 'Tarkir Dragonstorm: Jeskai Striker' -- name
            , TRUE -- is_commander
            , 2 -- commander_bracket_id
            , TRUE -- active
        )
        , (
              v_guid
            , 3 -- deck_id
            , 'The Lost Caverns of Ixalan: Blood Rites' -- name
            , TRUE -- is_commander
            , 2 -- commander_bracket_id
            , TRUE -- active
        )
    ;

    CALL tcg.public.USP_TCG_MTG_Deck_Save (
		  v_comment -- a_comment
		, v_guid -- a_guid
		, v_user_id -- a_user_id
		, v_success -- o_success
	);
    RAISE NOTICE 'Success: %', CASE WHEN v_success THEN 'TRUE' ELSE 'FALSE' END;
END;
$$;

SELECT *
-- DELETE
FROM tcg.public.Error ERROR
LEFT JOIN tcg.public.Error_Type ERROR_TYPE ON ERROR.error_type_id = ERROR_TYPE.error_type_id
;
SELECT *
FROM tcg.public.Error_Type ERROR_TYPE
;
SELECT *
FROM tcg.public.TCG_Change_Set CHANGE_SET
ORDER BY CHANGE_SET.change_set_id  DESC
;

SELECT *
-- DELETE
FROM tcg.public.TCG_MTG_Deck_Temp
;
SELECT *
-- DELETE
FROM tcg.public.TCG_MTG_Deck DECK
;
SELECT *
-- DELETE
FROM tcg.public.TCG_MTG_Deck_Audit DECK_AUDIT
;
-- 1 - Final Fantasy: Counter Blitz
-- 2 - Tarkir Dragonstorm: Jeskai Striker


