
/*{
    "Version": "1.0",
    "Type": "Mitigation",
    "Author": "sbadenkal",
    "AppVersion": "1.0",
    "Expiry": "05/03/2024",
    "DataOutput": "NoCustomerData",
    "ImpactSeverity": "CssSafe",
    "Notes": "Please do not modify this script without the prior approval of Authorization team. ",
    "Description": "Script to disable SysDeletedObjectSysRowVersion2 config based on status of SysDeletedObjectSysRowVersion while upgrading to PU63+",
    "VersionHistory" : [
         {"Version": "1.0", "Author": "sbadenkal", "Change": "Base version"}
    ]
}*/

--	IF SysDeletedObjectSysRowVersion Config is disabled
--		- If SysDeletedObjectSysRowVersion2 Config entry doesn't exist
--			+ Create new Configkeyidtable entry
--			+ Disable it by creating new entry in SYCONFIG for SysDeletedObjectSysRowVersion2
--			+ @Result = 3
--		- Else
--			+ NO-OP, @Result = 2
--	ELSE
--		+ NO-OP, @Result = 1



DECLARE @ConfigKeyIdTableExists BIT = CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'CONFIGKEYIDTABLE') AND EXISTS (SELECT TOP 1 1 FROM CONFIGKEYIDTABLE) THEN 1 ELSE 0 END
DECLARE @SysConfigTableExists BIT = CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'SYSCONFIG') AND EXISTS (SELECT TOP 1 1 FROM SYSCONFIG) THEN 1 ELSE 0 END
DECLARE @EnumIdTableExists BIT = CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'ENUMIDTABLE') THEN 1 ELSE 0 END
DECLARE @EnumValueTableExists BIT = CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'ENUMVALUETABLE') THEN 1 ELSE 0 END

DECLARE @OldSysRowVersionConfigName NVARCHAR(255) = 'SysDeletedObjectSysRowVersion';
DECLARE @NewSysRowVersionConfigName NVARCHAR(255) = 'SysDeletedObjectSysRowVersion2';

DECLARE @OldSysRowVersionConfigExists BIT =  CASE WHEN EXISTS(SELECT TOP 1 1 FROM CONFIGKEYIDTABLE WHERE NAME = @OldSysRowVersionConfigName) THEN 1 ELSE 0 END

DECLARE @OldSysRowVersionConfigID INT;
DECLARE @NewSysRowVersionConfigID INT;
DECLARE @OldSysRowVersionConfigEnabled BIT;
DECLARE @NewSysRowVersionConfigEnabled BIT;
DECLARE @ConfigTypeEnumValue INT = NULL;

DECLARE @SysConfigRowCNtAfter INT;

-- (-1) : Exception happened
-- 0  : Did not do any insert into CONFIGKEYIDTABLE table
-- 1  : Inserted into CONFIGKEYIDTABLE table
DECLARE @InsertRecordConfigKeyIdTable INT = 0;

-- (-1) : Exception happened
-- 0  : Did not do any insert into SYSCONFIG table
-- 1  : Inserted into SYSCONFIG table
DECLARE @InsertedRecordSysConfigTable INT = 0;

-- (-1) : Exception happened
-- 0  : No-OP, as ConfigKeyIdTable table or SysConfig table or OldSysRowVersionConfig entry doesn't exist
-- 1  : NO-OP, as OldSysRowVersionConfig was already enabled
-- 2  : NO-OP, as NewSysRowVersionConfig entry was already present in CONFIGKEYIDTABLE
-- 3  : NewSysRowVersionConfig entry is successfully created & disabled
DECLARE @Result INT = -1;

BEGIN TRY
    BEGIN TRANSACTION T1

	IF @ConfigKeyIdTableExists = 1 AND @SysConfigTableExists = 1 AND @OldSysRowVersionConfigExists = 1
		BEGIN

			-- get OldSysRowVersionConfig ID
			SELECT @OldSysRowVersionConfigID = ID FROM CONFIGKEYIDTABLE WHERE NAME = @OldSysRowVersionConfigName

			-- get OldSysRowVersionConfig status
			SET @OldSysRowVersionConfigEnabled = (CASE WHEN EXISTS(SELECT 1 FROM SYSCONFIG S WHERE S.ID = @OldSysRowVersionConfigID) THEN 0 ELSE 1 END)

			-- since OldSysRowVersionConfig was disabled, disable the NewSysRowVersionConfig as well
			IF @OldSysRowVersionConfigEnabled = 0
				BEGIN
					IF @EnumIdTableExists = 1 AND @EnumValueTableExists = 1
						-- get correct ConfigType enum value for ConfigurationKey Enum
						SELECT @ConfigTypeEnumValue = ENUMVALUE FROM ENUMVALUETABLE WHERE ENUMID = (SELECT ID FROM ENUMIDTABLE where NAME = 'ConfigType');

					IF @ConfigTypeEnumValue IS NULL
						SET @ConfigTypeEnumValue = 6; -- Default ConfigurationKey Enum value

					-- check if CONFIGKEYIDTABLE entry exists for NewSysRowVersionConfig
					IF NOT EXISTS(SELECT 1 FROM CONFIGKEYIDTABLE WHERE NAME = @NewSysRowVersionConfigName)
						BEGIN
							
							-- create new CONFIGKEYIDTABLE entry at the end for NewSysRowVersionConfig as it does not exist
							INSERT INTO CONFIGKEYIDTABLE (ID, NAME, RECVERSION, RECID)
								SELECT ISNULL(MAX(IDA.ID), 0xFFFF)+1, @NewSysRowVersionConfigName, 0, ISNULL(MAX(IDA.ID), 0xFFFF)+1
								FROM CONFIGKEYIDTABLE IDA
								WHERE IDA.ID >= 0xFFFF

							IF @@ROWCOUNT <> 1
								THROW 51000, 'Incorrect rows inserted to CONFIGKEYIDTABLE!', 1;
							ELSE
								SET @InsertRecordConfigKeyIdTable = 1;

							-- get newly created NewSysRowVersionConfig ID
							SELECT @NewSysRowVersionConfigID = ID FROM CONFIGKEYIDTABLE WHERE NAME = @NewSysRowVersionConfigName

							-- get NewSysRowVersionConfig status
							SET @NewSysRowVersionConfigEnabled = (CASE WHEN EXISTS(SELECT 1 FROM SYSCONFIG S WHERE S.ID = @NewSysRowVersionConfigID) THEN 0 ELSE 1 END)

							-- Disable the newly created NewSysRowVersionConfig
							-- insert an entry into SYSCONFIG to disable NewSysRowVersionConfig
							INSERT INTO SYSCONFIG(CONFIGTYPE, ID)
								SELECT @ConfigTypeEnumValue, @NewSysRowVersionConfigID
								WHERE NOT EXISTS (
									SELECT 'x' FROM SYSCONFIG WHERE ID = @NewSysRowVersionConfigID
								)

							-- verify that only 1 record is inserted
							IF @@ROWCOUNT <> 1
									THROW 51001, 'Incorrect rows inserted to SYSCONFIG table!', 1;
							ELSE
								BEGIN

									-- verify that only 1 expected record is inserted
									SELECT @SysConfigRowCNtAfter = COUNT(*) FROM SYSCONFIG SC WHERE SC.ID = @NewSysRowVersionConfigID;
									IF @SysConfigRowCNtAfter = 1
										SET @InsertedRecordSysConfigTable = 1;
									ELSE
										THROW 51002, 'Incorrect number of rows inserted to SYSCONFIG table!', 1;
								END

							-- get NewSysRowVersionConfig status after disabling
							SET @NewSysRowVersionConfigEnabled = (CASE WHEN EXISTS(SELECT 1 FROM SYSCONFIG S WHERE S.ID = @NewSysRowVersionConfigID) THEN 0 ELSE 1 END)

							-- NewSysRowVersionConfig entry is successfully created & disabled
							SET @Result = 3;
						END
					ELSE
						-- NO-OP, as NewSysRowVersionConfig entry was already present in CONFIGKEYIDTABLE
						SET @Result = 2;
				END
			ELSE
				-- NO-OP, as OldSysRowVersionConfig was already enabled
				SET @Result = 1;
		  END
	ELSE
		-- No-OP, as ConfigKeyIdTable table or SysConfig table or OldSysRowVersionConfig entry doesn't exist
		SET @Result = 0;
	COMMIT TRANSACTION T1
    SELECT @Result AS Result, @OldSysRowVersionConfigEnabled AS OldSysRowVersionConfigEnabled, @NewSysRowVersionConfigEnabled AS NewSysRowVersionConfigEnabled, @InsertRecordConfigKeyIdTable AS InsertRecordConfigKeyIdTable, @InsertedRecordSysConfigTable AS InsertedRecordSysConfigTable, @SysConfigRowCNtAfter AS SysConfigRowCNtAfter, 0 AS Error, '' AS ErrorMessage;
END TRY
BEGIN CATCH
    SELECT -1 AS Result, @OldSysRowVersionConfigEnabled AS OldSysRowVersionConfigEnabled, @NewSysRowVersionConfigEnabled AS NewSysRowVersionConfigEnabled, @InsertRecordConfigKeyIdTable AS InsertRecordConfigKeyIdTable, @InsertedRecordSysConfigTable AS InsertedRecordSysConfigTable, @SysConfigRowCNtAfter AS SysConfigRowCNtAfter, ERROR_NUMBER() as Error, ERROR_MESSAGE() as ErrorMessage
    ROLLBACK TRANSACTION T1
END CATCH


