/* ============================================================================
   SYSTEM_INFO – TABELLEN-SKRIPT
    Voraussetzung: Schema [_meta] existiert (siehe [SCHEMA_META].sql)
   ============================================================================ */

USE [DD_IIM];
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRAN;

    IF OBJECT_ID(N'_meta.TBDD_CFG_SYSTEM_INFO', N'U') IS NULL
    BEGIN
        CREATE TABLE [_meta].[TBDD_CFG_SYSTEM_INFO]
        (
            [PK_CFG_SYSTEM_INFO_ID] BIGINT IDENTITY(1,1) NOT NULL,
            [SYSTEM_VERSION]         NVARCHAR(50) NOT NULL,
            [COMMENT]                NVARCHAR(4000) NULL,
            [CREATED_WHO]            BIGINT        NOT NULL,
            [CREATED_WHEN]           DATETIME2(0)  NOT NULL,
            [CHANGED_WHO]            BIGINT        NULL,
            [CHANGED_WHEN]           DATETIME2(0)  NULL
        );
    END;

    IF NOT EXISTS
    (
        SELECT 1
        FROM sys.key_constraints kc
        JOIN sys.tables t  ON t.object_id = kc.parent_object_id
        JOIN sys.schemas s ON s.schema_id = t.schema_id
        WHERE s.name = N'_meta'
          AND t.name = N'TBDD_CFG_SYSTEM_INFO'
          AND kc.type = N'PK'
    )
    BEGIN
        ALTER TABLE [_meta].[TBDD_CFG_SYSTEM_INFO]
            ADD CONSTRAINT [PK_CFG_SYSTEM_INFO_ID]
            PRIMARY KEY CLUSTERED ([PK_CFG_SYSTEM_INFO_ID]);
    END;

    IF NOT EXISTS (SELECT 1 FROM [_meta].[TBDD_CFG_SYSTEM_INFO] WITH (HOLDLOCK, UPDLOCK))
    BEGIN
        INSERT INTO [_meta].[TBDD_CFG_SYSTEM_INFO]
        (
            [SYSTEM_VERSION],
            [COMMENT],
            [CREATED_WHO],
            [CREATED_WHEN]
        )
        VALUES
        (
            N'1.0.0.0',
            N'Initiale Anlage',
            1,
            GETDATE()
        );
    END;

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRAN;

    DECLARE @msg NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @num INT = ERROR_NUMBER();
    DECLARE @sev INT = ERROR_SEVERITY();
    DECLARE @stt INT = ERROR_STATE();
    RAISERROR(N'Fehler %d: %s', @sev, @stt, @num, @msg);
END CATCH;
GO