CREATE OR REPLACE PROCEDURE internal.ptemporalstatus(IN par_tablename VARCHAR, IN par_enable INTEGER DEFAULT null, 
    IN par_enableifstatus NUMERIC DEFAULT null, IN par_silent NUMERIC DEFAULT 0, INOUT par_statuschanged NUMERIC DEFAULT null, 
    INOUT return_code int DEFAULT 0)
AS 
$BODY$
DECLARE
    var_Msg VARCHAR(2000);
    var_Sql VARCHAR(4000);
    var_TableCount INTEGER;
    var_CurrentStateNullCount INTEGER;
    var_CurrentState INTEGER;
    var_SameState NUMERIC(1, 0);
BEGIN
    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON;
    */
    CREATE TEMPORARY TABLE tables$ptemporalstatus
    (schemaname VARCHAR(200),
        tablename VARCHAR(200),
        schematablename VARCHAR(200),
        currentstate INTEGER);
    INSERT INTO tables$ptemporalstatus (schemaname, tablename, schematablename, currentstate)
    SELECT
        t.SchemaName, t.TableName, t.SchemaName || '.' + t.TableName, s.temporal_type
        FROM (SELECT
            CASE
                WHEN d.dotindex = 0 THEN 'dbo'
                ELSE SUBSTR(t.value, 1, d.dotindex - 1)
            END AS schemaname,
            CASE
                WHEN d.dotindex = 0 THEN t.value
                ELSE SUBSTR(t.value, d.dotindex + 1, 200)
            END AS tablename
            FROM regexp_split_to_table(par_TableName, '[,]')
                AS t
            CROSS JOIN LATERAL (SELECT
                STRPOS(t.value, '.') AS dotindex) AS d) AS t
        LEFT OUTER JOIN aws_sqlserver_ext.SYS_TABLES AS s
            ON object_id = aws_sqlserver_ext.object_id(t.schemaname || '.' || t.tablename, 'u');
    SELECT
        COUNT(*), SUM(CASE
            WHEN currentstate IS NULL THEN 1
            ELSE 0
        END),
        CASE
            WHEN COUNT(DISTINCT currentstate) = 1 THEN 1
            ELSE 0
        END
        INTO var_TableCount, var_CurrentStateNullCount, var_SameState
        FROM tables$ptemporalstatus;
    SELECT
        currentstate
        INTO var_CurrentState
        FROM tables$ptemporalstatus
        LIMIT 1;

    IF var_CurrentStateNullCount > 0 THEN
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 16, 0, '%s table(s) not found.', par_TableName USING ERRCODE = '50000';
        return_code := 1;
        RETURN;
    END IF;

    IF var_SameState = 0 THEN
        -- RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 16, 0, '%s table(s) not in same state.', par_TableName USING ERRCODE = '50000';
        return_code := 1;
        RETURN;
    END IF;

    IF par_StatusChanged = 0 THEN
        return_code := 0;
        DROP TABLE IF EXISTS tables$ptemporalstatus;
        RETURN;
    END IF;

    IF par_EnableIfStatus IS NOT NULL THEN
        IF NOT ((par_EnableIfStatus = 1 AND var_CurrentState = 2) OR (par_EnableIfStatus = 0 AND var_CurrentState = 0)) THEN
            par_StatusChanged := 0;
            return_code := 0;
            RETURN;
        END IF;
    END IF;

    IF par_Enable IS NULL THEN
        var_Msg :=
        CASE
            WHEN var_CurrentState = 0 THEN 'Non Temporal Table'
            WHEN var_CurrentState = 1 THEN 'History Table'
            WHEN var_CurrentState = 2 THEN 'System Versioned Temporal Table'
        END;
    ELSE
        IF par_Enable = 0 THEN
            IF var_CurrentState = 0 THEN
                var_Msg := par_TableName || ': Already Non Temporal Table';
            ELSE
                SELECT
                    string_agg(REPLACE('
                ALTER TABLE {SCHEMATABLE} SET (SYSTEM_VERSIONING = OFF);
                ALTER TABLE {SCHEMATABLE} DROP PERIOD FOR SYSTEM_TIME;
            ', '{SCHEMATABLE}', schematablename), ';
            ')
                    INTO var_Sql
                    FROM tables$ptemporalstatus;
                /*
                [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
                EXEC sp_executesql @stmt=@Sql;
                */
                var_Msg := par_TableName || ': Made Non Temporal Table';
            END IF;
        ELSE
            IF par_Enable = 1 THEN
                IF var_CurrentState = 2 THEN
                    var_Msg := par_TableName || ': Already System Versioned Temporal Table';
                ELSE
                    SELECT
                        string_agg(REPLACE('
                ALTER TABLE {SCHEMATABLE} ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime); 
                ALTER TABLE {SCHEMATABLE} SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {SCHEMATABLE}History));
            ', '{SCHEMATABLE}', schematablename), ';
            ')
                        INTO var_Sql
                        FROM tables$ptemporalstatus;
                    /*
                    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
                    EXEC sp_executesql @stmt=@Sql;
                    */
                    var_Msg := par_TableName || ': Made System Versioned Temporal Table';
                END IF;
            ELSE
                IF par_Enable = 2 THEN
                    SELECT
                        string_agg(REPLACE(REPLACE('
                ALTER TABLE {SCHEMATABLE}
                    ADD SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START CONSTRAINT [DF_{TABLE}_SysStartTime] DEFAULT CONVERT(datetime2(0), ''0001-01-01''), 
                        SysEndTime   datetime2(0) GENERATED ALWAYS AS ROW END   CONSTRAINT [DF_{TABLE}_SysEndTime]   DEFAULT CONVERT(datetime2(0), ''9999-12-31 23:59:59''), 
                        PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime); 
                ALTER TABLE {SCHEMATABLE} SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {SCHEMATABLE}History));
            ', '{SCHEMATABLE}', schematablename), '{TABLE}', tablename), ';
            ')
                        INTO var_Sql
                        FROM tables$ptemporalstatus;
                    /*
                    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
                    EXEC sp_executesql @stmt=@Sql;
                    */
                    var_Msg := par_TableName || ': Created Systm Versioned Temporal Table';
                END IF;
            END IF;
        END IF;
    END IF;

    IF par_Silent = 0 THEN
        -- RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, var_Msg USING ERRCODE = '50000';
    END IF;
    par_StatusChanged := 1;
    return_code := 1;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql;