-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateForeignKeys]') and xtype = N'P')
drop procedure [dbo].sp_UpdateForeignKeys
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Guzel Minnekaeva
-- Create date: 2023-09-26
-- Description:	Удаление и создание внешних ключей относящихся к сущности @NameEhrOrTree
-- =============================================
CREATE PROCEDURE [dbo].sp_UpdateForeignKeys
	@NameEhrOrTree varchar(100)
AS
BEGIN
	SET NOCOUNT ON;

	declare @infoFK table 
	(
		schemaNameTable varchar(200) NULL,
		nameTable varchar(200) NULL,
		fkSchemaName varchar(200) NULL,
		fkName varchar(200) NULL,
		attributeName varchar(200) NULL,
		type_ varchar(200) NULL
	)
	
	Insert Into @infoFK
	    SELECT schemaNameTable = OBJECT_SCHEMA_NAME(fk.parent_object_id), 
		nameTable = OBJECT_NAME(fk.parent_object_id), 
		fkSchemaName = OBJECT_SCHEMA_NAME(fk.object_id), 
		fkName = OBJECT_NAME(fk.object_id), 
		attributeName = c_parent.name, 
		type_ = SUBSTRING(ea.SystemType, 5, 100) FROM sys.foreign_keys fk 
	INNER JOIN sys.foreign_key_columns fkc
	    ON fkc.constraint_object_id = fk.object_id
	INNER JOIN sys.columns c_parent
	    ON fkc.parent_column_id = c_parent.column_id  
	    AND c_parent.object_id = fk.parent_object_id 
	LEFT JOIN VCLib.T_ENTITIES_ATTRIBUTES ea
		ON ea.MappingTable = OBJECT_NAME(fk.parent_object_id) And ea.MappingName = c_parent.name
	WHERE fk.referenced_object_id = OBJECT_ID(@NameEhrOrTree) 
	




	declare @tab table 
	(
		sqlText varchar(max) NULL,
		fk varchar(200) NULL
	)
	
    DECLARE @schemaNameTable varchar(200) 
	DECLARE @nameTable varchar(200) 
	DECLARE @fkSchemaName varchar(200)
	DECLARE @fkName varchar(200) 
	DECLARE @attributeName varchar(200)
	DECLARE @type_ varchar(200)

	DECLARE @sqlDrop varchar(max)
   
   DECLARE my_cur CURSOR FOR 
     SELECT schemaNameTable, nameTable,	fkSchemaName, fkName, attributeName, type_  
     FROM @infoFK
   
   OPEN my_cur
   FETCH NEXT FROM my_cur INTO @schemaNameTable, @nameTable, @fkSchemaName, @fkName, @attributeName, @type_
   	  
   WHILE @@FETCH_STATUS = 0
   BEGIN
		SET @sqlDrop = N'IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' + @fkSchemaName + '.' + @fkName + ''') AND parent_object_id = OBJECT_ID(N''' + @schemaNameTable + '.' + @nameTable + '''))
                                        ALTER TABLE ' + @schemaNameTable + '.' + @nameTable + ' DROP CONSTRAINT [' + @fkName + ']
                                        '
		-- для EHR_RUS_
   		IF @attributeName = 'EhrRusID'
			-- удаление внешних ключей
			Insert Into @tab VALUES(@sqlDrop, @fkName)
		-- для сущностей, где используются EHR_RUS_
		ELSE
		BEGIN
			DECLARE @entName VARCHAR(30) 
			IF @type_ IS NOT NULL 
				SET @entName = @type_
			ELSE
				-- нет в xml (в метадате), есть в бд ; если нашлось, значит есть записи в EHR.T_EHR_RUS 
				BEGIN
					DECLARE @sqlExpression nvarchar(500)
					SET @sqlExpression = N'SELECT top 1 @entNameOUT = e.EntName FROM VCLib.T_ENTITIES e JOIN EHR.T_EHR_RUS er on e.EntityID = er.type_ JOIN ' + @schemaNameTable + '.' + @nameTable + ' entity on entity.' + @attributeName + ' = er.EhrRusID'
					DECLARE @ParmDefinition nvarchar(500); 
					SET @ParmDefinition = N'@entNameOUT nvarchar(30) OUTPUT';  
					EXEC sp_executesql @sqlExpression, @ParmDefinition, @entNameOUT = @entName OUTPUT;
				END						

			-- удаление и создание внешних ключей
			IF @entName IS NOT NULL 
				Insert Into @tab VALUES(@sqlDrop + N'
											ALTER TABLE ' + @schemaNameTable + '.' + @nameTable + '  WITH CHECK ADD  CONSTRAINT [FK_T_' + @entName + '_' + @nameTable + '_' + @attributeName + '] FOREIGN KEY([' + @attributeName + '])
			                                REFERENCES [EHR].[T_' + @entName + '] ([EhrRusID])', @fkName)
			-- нет в xml (в метадате), есть в бд ; нет записи в EHR.T_EHR_RUS 
			ELSE
				-- удаление внешних ключей
				Insert Into @tab VALUES(@sqlDrop, @fkName)		
		END

        FETCH NEXT FROM my_cur INTO @schemaNameTable, @nameTable, @fkSchemaName, @fkName, @attributeName, @type_
   END
   
   CLOSE my_cur
   DEALLOCATE my_cur




   DECLARE @sqlExpressions varchar(max)
   
   DECLARE cur CURSOR FOR 
     SELECT sqlText 
     FROM @tab
   
   OPEN cur
   FETCH NEXT FROM cur INTO @sqlExpressions
   WHILE @@FETCH_STATUS = 0
   BEGIN 
        exec( @sqlExpressions)

        FETCH NEXT FROM cur INTO @sqlExpressions
   END
   
   CLOSE cur
   DEALLOCATE cur

END
GO

                            