/* ============================================================================
    SYSTEM_INFO – PROZEDUREN
    Voraussetzung: Tabelle [_meta].[TBDD_CFG_SYSTEM_INFO] existiert
    ============================================================================ */

USE [DD_IIM];
GO

DROP PROCEDURE IF EXISTS [_meta].[PRDD_INSERT_SYSTEM_INFO];
DROP PROCEDURE IF EXISTS [_meta].[PRDD_UPDATE_SYSTEM_INFO];
GO

CREATE OR ALTER PROCEDURE [_meta].[PRDD_UPSERT_SYSTEM_INFO]
    @PK_CFG_SYSTEM_INFO_ID BIGINT = NULL,
    @SYSTEM_VERSION NVARCHAR(50),
    @COMMENT NVARCHAR(4000) = NULL,
    @CREATED_WHO BIGINT,
    @CREATED_WHEN DATETIME2(0),
    @CHANGED_WHO BIGINT = NULL,
    @CHANGED_WHEN DATETIME2(0) = NULL,
    @OUT_PK_CFG_SYSTEM_INFO_ID BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @identity_insert_enabled BIT = 0;

    BEGIN TRY
        EXEC sys.sp_set_session_context @key = N'_meta_allow_tbdd_system_info_dml', @value = 1;

        IF @PK_CFG_SYSTEM_INFO_ID IS NULL
        BEGIN
            EXEC sys.sp_set_session_context @key = N'_meta_tbdd_system_info_use_input_pk', @value = 0;

            INSERT INTO [_meta].[TBDD_CFG_SYSTEM_INFO]
            (
                [SYSTEM_VERSION],
                [COMMENT],
                [CREATED_WHO],
                [CREATED_WHEN],
                [CHANGED_WHO],
                [CHANGED_WHEN]
            )
            VALUES
            (
                @SYSTEM_VERSION,
                @COMMENT,
                @CREATED_WHO,
                @CREATED_WHEN,
                @CHANGED_WHO,
                @CHANGED_WHEN
            );

            SET @OUT_PK_CFG_SYSTEM_INFO_ID = CAST(@@IDENTITY AS BIGINT);
        END
        ELSE
        BEGIN
            UPDATE [_meta].[TBDD_CFG_SYSTEM_INFO]
               SET [SYSTEM_VERSION] = @SYSTEM_VERSION,
                   [COMMENT] = @COMMENT,
                   [CHANGED_WHO] = @CHANGED_WHO,
                   [CHANGED_WHEN] = @CHANGED_WHEN
             WHERE [PK_CFG_SYSTEM_INFO_ID] = @PK_CFG_SYSTEM_INFO_ID;

            IF @@ROWCOUNT = 0
            BEGIN
                SET @identity_insert_enabled = 1;
                EXEC sys.sp_set_session_context @key = N'_meta_tbdd_system_info_use_input_pk', @value = 1;
                SET IDENTITY_INSERT [_meta].[TBDD_CFG_SYSTEM_INFO] ON;

                INSERT INTO [_meta].[TBDD_CFG_SYSTEM_INFO]
                (
                    [PK_CFG_SYSTEM_INFO_ID],
                    [SYSTEM_VERSION],
                    [COMMENT],
                    [CREATED_WHO],
                    [CREATED_WHEN],
                    [CHANGED_WHO],
                    [CHANGED_WHEN]
                )
                VALUES
                (
                    @PK_CFG_SYSTEM_INFO_ID,
                    @SYSTEM_VERSION,
                    @COMMENT,
                    @CREATED_WHO,
                    @CREATED_WHEN,
                    @CHANGED_WHO,
                    @CHANGED_WHEN
                );

                SET IDENTITY_INSERT [_meta].[TBDD_CFG_SYSTEM_INFO] OFF;
                SET @identity_insert_enabled = 0;
                EXEC sys.sp_set_session_context @key = N'_meta_tbdd_system_info_use_input_pk', @value = 0;
            END;

            SET @OUT_PK_CFG_SYSTEM_INFO_ID = @PK_CFG_SYSTEM_INFO_ID;
        END;

        EXEC sys.sp_set_session_context @key = N'_meta_tbdd_system_info_use_input_pk', @value = NULL;
        EXEC sys.sp_set_session_context @key = N'_meta_allow_tbdd_system_info_dml', @value = NULL;
    END TRY
    BEGIN CATCH
        IF @identity_insert_enabled = 1
        BEGIN
            BEGIN TRY
                SET IDENTITY_INSERT [_meta].[TBDD_CFG_SYSTEM_INFO] OFF;
            END TRY
            BEGIN CATCH
            END CATCH
        END;

        EXEC sys.sp_set_session_context @key = N'_meta_tbdd_system_info_use_input_pk', @value = NULL;
        EXEC sys.sp_set_session_context @key = N'_meta_allow_tbdd_system_info_dml', @value = NULL;
        THROW;
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE [_meta].[PRDD_DELETE_SYSTEM_INFO]
    @PK_CFG_SYSTEM_INFO_ID BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        EXEC sys.sp_set_session_context @key = N'_meta_allow_tbdd_system_info_dml', @value = 1;

        DELETE FROM [_meta].[TBDD_CFG_SYSTEM_INFO]
         WHERE [PK_CFG_SYSTEM_INFO_ID] = @PK_CFG_SYSTEM_INFO_ID;

        EXEC sys.sp_set_session_context @key = N'_meta_allow_tbdd_system_info_dml', @value = NULL;
    END TRY
    BEGIN CATCH
        EXEC sys.sp_set_session_context @key = N'_meta_allow_tbdd_system_info_dml', @value = NULL;
        THROW;
    END CATCH
END;
GO