/************************************************************
 * Code formatted by SoftTree SQL Assistant © v11.0.35
 * Time: 6/16/2025 7:36:59 PM
 ************************************************************/

USE plib_namecheck;
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

IF DB_NAME() <> 'plib_namecheck'
BEGIN
    RAISERROR('Wrong database.', 16, 10);
    RETURN;
END;
GO

IF NOT EXISTS (
       SELECT 1
       FROM   sys.objects
       WHERE  [object_id] = OBJECT_ID(N'[dbo].[gsp_BackupAndSwitchModel]')
              AND [type] IN (N'P', N'PC')
   )
BEGIN
    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[gsp_BackupAndSwitchModel] AS';
END;
GO

/*
The gsp_BackupAndSwitchModel procedure automates the process of updating a model's underlying table while preserving its previous state.
It optionally creates a backup of the old table, registers the new or provided table with the model, activates the new model version, and updates model parameters to reference the backup if needed.
This ensures a safe switch-over to a new data structure for a model, with the ability to trace or restore the prior table if necessary.
The procedure performs input validation and error handling for missing or incorrect parameters.
*/
ALTER PROCEDURE [dbo].[gsp_BackupAndSwitchModel](
    @ModelPath      VARCHAR(MAX),
    @table_name     SYSNAME = NULL,
    @debug          BIT = 0
)
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT,
	    QUOTED_IDENTIFIER,
	    ANSI_NULLS,
	    ANSI_PADDING,
	    ANSI_WARNINGS,
	    ARITHABORT,
	    CONCAT_NULL_YIELDS_NULL ON;
	SET NUMERIC_ROUNDABORT OFF;
	
	BEGIN TRY
		DECLARE @context_db_name SYSNAME = (
		            SELECT DB_NAME() context_db_name
		        );
		DECLARE @ErrMsg VARCHAR(MAX);
		
		DECLARE @back_up_table       SYSNAME,
		        @ModelName           VARCHAR(MAX),
		        @ModelImplID         INT,
		        @ModelImplID_old     INT;
		
		-- If @table_name is not provided (NULL), retrieve it from v_plib_models based on the model path.
		-- If @table_name is provided, do not overwrite it; just get the ModelImplID for the given path.
		IF @table_name IS NULL
		BEGIN    
			SELECT @ModelImplID = ModelImplID,
		           @table_name = cpm.ScriptTable
		    FROM   dbo.v_plib_models AS cpm
		    WHERE  cpm.model_path = @ModelPath

			IF @ModelImplID IS NULL
			BEGIN
				RAISERROR(
					'Model ''%s'' not found. For new models, you must specify the table as a parameter.',
					16,
					1,
					@ModelPath
				);
				RETURN;
			END	

			IF @table_name IS NULL
			BEGIN
				RAISERROR(
					'Model ''%s'' not found. For new models, you must specify the table as a parameter, or another error has occurred.',
					16,
					1,
					@ModelPath
				);
				RETURN;
			END
		END
		ELSE
		    SELECT @ModelImplID = ModelImplID
		    FROM   dbo.v_plib_models AS cpm
		    WHERE  cpm.model_path = @ModelPath
		
		IF LEN(@table_name) = 0
		BEGIN
		    RAISERROR (
		        'No table found for the specified model path (''%s''). Try calling the procedure with the table name explicitly. Example: EXEC dbo.gsp_BackupAndSwitchModel @ModelPath = ''%s'', @table_name = ''Your_table_name''.',
		        16,
		        1,
		        @ModelPath,
		        @ModelPath
		    );
		    RETURN;
		END
		
		IF @table_name IS NOT NULL
		   AND OBJECT_ID(@table_name) IS NULL
		BEGIN
		    RAISERROR(
		        'Specified table ''%s'' not found in %s database. Check the table name and try again.',
		        16,
		        1,
		        @table_name,
		        @context_db_name
		    );
		    RETURN;
		END
		
		IF @debug = 1
		    SELECT @ModelPath [@ModelPath],
		           @table_name [@table_name]
		
		-- If the model already exists (@ModelImplID IS NOT NULL), create a backup of the current table and link it to the model.
		-- If the model does not exist, a backup is not needed.
		IF @ModelImplID IS NOT NULL
		BEGIN
		    SET @ModelImplID_old = @ModelImplID;
		    
		    EXEC staging_namecheck.dbo.gsp_Create_backup_from_table
		         @table_name = @table_name,
		         @back_up_table = @back_up_table OUTPUT,
		         @context_db_name = @context_db_name,
		         @debug = @debug
		    
		    SELECT @back_up_table = dbo.gsf_get_full_table_name(@back_up_table, 4)
		    EXEC dbo.plib_set_table @ModelImplID,
		         @back_up_table,
		         @debug;
		END
		
		EXEC dbo.plib_new_version @ModelPath,
		     @ModelImplID OUTPUT,
		     @debug;
		
		IF @debug = 1
		    SELECT @ModelImplID [@ModelImplID]
		
		EXEC dbo.plib_set_table @ModelImplID,
		     @table_name,
		     @debug;
		
		DECLARE @ModelID INT;
		
		SELECT @ModelID = ModelID
		FROM   dbo.ModelImpl mi
		WHERE  mi.ModelImplID = @ModelImplID
		
		IF @debug = 1
		    SELECT @ModelID [@ModelID]
		
		EXEC dbo.plib_set_active @ModelID,
		     @ModelImplID,
		     @debug
		
		-- Replace the original table name with the created backup in RuleParams of the old model version.
		-- If there was no previous model (@ModelImplID_old IS NULL), nothing will be updated.
		UPDATE dbo.ModelImpl
		SET    RuleParams      = REPLACE(
		           CAST(RuleParams AS VARCHAR(MAX)),
		           @table_name,
		           dbo.gsf_get_full_table_name(@back_up_table, 4)
		       )
		WHERE  ModelImplID     = @ModelImplID_old;
	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;
		
		SELECT @ErrorMessage = ERROR_MESSAGE(),
		       @ErrorSeverity     = ERROR_SEVERITY(),
		       @ErrorState        = ERROR_STATE();
		
		IF XACT_STATE() <> 0
		    ROLLBACK TRAN;
		
		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH;
END;
GO


USE CCR;
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

IF DB_NAME() <> 'CCR'
BEGIN
    RAISERROR('Wrong database.', 16, 10);
    RETURN;
END;
GO

IF NOT EXISTS (
       SELECT 1
       FROM   sys.objects
       WHERE  [object_id] = OBJECT_ID(N'[dbo].[gsp_BackupAndSwitchModel]')
              AND [type] IN (N'P', N'PC')
   )
BEGIN
    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[gsp_BackupAndSwitchModel] AS';
END;
GO

/*
The gsp_BackupAndSwitchModel procedure automates the process of updating a model's underlying table while preserving its previous state.
It optionally creates a backup of the old table, registers the new or provided table with the model, activates the new model version, and updates model parameters to reference the backup if needed.
This ensures a safe switch-over to a new data structure for a model, with the ability to trace or restore the prior table if necessary.
The procedure performs input validation and error handling for missing or incorrect parameters.
*/
ALTER PROCEDURE [dbo].[gsp_BackupAndSwitchModel](
    @ModelPath      VARCHAR(MAX),
    @table_name     SYSNAME = NULL,
    @debug          BIT = 0
)
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT,
	    QUOTED_IDENTIFIER,
	    ANSI_NULLS,
	    ANSI_PADDING,
	    ANSI_WARNINGS,
	    ARITHABORT,
	    CONCAT_NULL_YIELDS_NULL ON;
	SET NUMERIC_ROUNDABORT OFF;
	
	BEGIN TRY
		DECLARE @context_db_name SYSNAME = (
		            SELECT DB_NAME() context_db_name
		        );
		DECLARE @ErrMsg VARCHAR(MAX);
		
		DECLARE @back_up_table       SYSNAME,
		        @ModelName           VARCHAR(MAX),
		        @ModelImplID         INT,
		        @ModelImplID_old     INT;
		
		-- If @table_name is not provided (NULL), retrieve it from v_plib_models based on the model path.
		-- If @table_name is provided, do not overwrite it; just get the ModelImplID for the given path.
		IF @table_name IS NULL
		BEGIN
			SELECT @ModelImplID = ModelImplID,
		           @table_name = cpm.ScriptTable
		    FROM   dbo.v_ccr_models AS cpm
		    WHERE  cpm.model_path = @ModelPath

			IF @ModelImplID IS NULL
			BEGIN
				RAISERROR(
					'Model ''%s'' not found. For new models, you must specify the table as a parameter.',
					16,
					1,
					@ModelPath
				);
				RETURN;
			END	

			IF @table_name IS NULL
			BEGIN
				RAISERROR(
					'Model ''%s'' not found. For new models, you must specify the table as a parameter, or another error has occurred.',
					16,
					1,
					@ModelPath
				);
				RETURN;
			END

		END
		ELSE
		    SELECT @ModelImplID = ModelImplID
		    FROM   dbo.v_ccr_models AS cpm
		    WHERE  cpm.model_path = @ModelPath
		
		IF LEN(@table_name) = 0
		BEGIN
		    RAISERROR (
		        'No table found for the specified model path (''%s''). Try calling the procedure with the table name explicitly. Example: EXEC dbo.gsp_BackupAndSwitchModel @ModelPath = ''%s'', @table_name = ''Your_table_name''.',
		        16,
		        1,
		        @ModelPath,
		        @ModelPath
		    );
		    RETURN;
		END
		
		IF @table_name IS NOT NULL
		   AND OBJECT_ID(@table_name) IS NULL
		BEGIN
		    RAISERROR(
		        'Specified table ''%s'' not found in %s database. Check the table name and try again.',
		        16,
		        1,
		        @table_name,
		        @context_db_name
		    );
		    RETURN;
		END
		
		IF @debug = 1
		    SELECT @ModelPath [@ModelPath],
		           @table_name [@table_name]
		
		-- If the model already exists (@ModelImplID IS NOT NULL), create a backup of the current table and link it to the model.
		-- If the model does not exist, a backup is not needed.
		IF @ModelImplID IS NOT NULL
		BEGIN
		    SET @ModelImplID_old = @ModelImplID;
		    
		    EXEC staging_namecheck.dbo.gsp_Create_backup_from_table
		         @table_name = @table_name,
		         @back_up_table = @back_up_table OUTPUT,
		         @context_db_name = @context_db_name,
		         @debug = @debug
		    SELECT @back_up_table = dbo.gsf_get_full_table_name(@back_up_table, 4)
			EXEC dbo.plib_set_table @ModelImplID,
		         @back_up_table,
		         @debug;
		END
		
		EXEC dbo.plib_new_version @ModelPath,
		     @ModelImplID OUTPUT,
		     @debug;
		
		IF @debug = 1
		    SELECT @ModelImplID [@ModelImplID]
		
		EXEC dbo.plib_set_table @ModelImplID,
		     @table_name,
		     @debug;
		
		DECLARE @ModelID INT;
		
		SELECT @ModelID = ModelID
		FROM   dbo.ModelImpl mi
		WHERE  mi.ModelImplID = @ModelImplID
		
		IF @debug = 1
		    SELECT @ModelID [@ModelID]
		
		EXEC dbo.plib_set_active @ModelID,
		     @ModelImplID,
		     @debug
		
		-- Replace the original table name with the created backup in RuleParams of the old model version.
		-- If there was no previous model (@ModelImplID_old IS NULL), nothing will be updated.
		UPDATE dbo.ModelImpl
		SET    RuleParams      = REPLACE(
		           CAST(RuleParams AS VARCHAR(MAX)),
		           @table_name,
		           dbo.gsf_get_full_table_name(@back_up_table, 4)
		       )
		WHERE  ModelImplID     = @ModelImplID_old;
	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;
		
		SELECT @ErrorMessage = ERROR_MESSAGE(),
		       @ErrorSeverity     = ERROR_SEVERITY(),
		       @ErrorState        = ERROR_STATE();
		
		IF XACT_STATE() <> 0
		    ROLLBACK TRAN;
		
		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH;
END;
GO