IF NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE NAME = 'CD_ANTERIOR' AND OBJECT_ID = OBJECT_ID('GLB_TP_RECEITA'))
BEGIN
    ALTER TABLE GLB_TP_RECEITA
	ADD CD_ANTERIOR VARCHAR(50) NULL;

END
GO

INSERT INTO dbo.GLB_TP_RECEITA (CD_EMP, CD_TP_RECEITA, DS_TP_RECEITA, DT_ULT_ALT, CD_USU, DT_CAD, CD_ANTERIOR)
	SELECT
		1,
		ISNULL((SELECT MAX(CD_TP_RECEITA) FROM GLB_TP_RECEITA WHERE CD_EMP = 1),0) + ROW_NUMBER() OVER(ORDER BY CD_UNIVER ASC) AS CD_TP_RECEITA,
		T.DS_RECEITA,
		GETDATE(),
		1,
		GETDATE(),
		T.CD_RECEITA_TIPO
	FROM dbo.TLV_RECEITA_TIPO AS T
	WHERE NOT EXISTS (
			  SELECT 1
			  FROM dbo.GLB_TP_RECEITA AS G
			  WHERE G.CD_EMP = 1
				AND G.DS_TP_RECEITA = T.DS_RECEITA
		  )

IF NOT EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME = 'EST_PROD_PRINC_ATIVOS_BKP_61765')
BEGIN
	SELECT *
	INTO EST_PROD_PRINC_ATIVOS_BKP_61765
	FROM EST_PROD_PRINC_ATIVOS
END

DECLARE @NomeFK NVARCHAR(500);

SELECT @NomeFK = fk.name
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.tables t ON t.object_id = fkc.parent_object_id
JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = fkc.parent_column_id
WHERE t.name = 'EST_PROD_PRINC_ATIVOS' 
  AND c.name = 'CD_RECEITA_TIPO';

IF @NomeFK IS NOT NULL
BEGIN
    EXEC('ALTER TABLE EST_PROD_PRINC_ATIVOS DROP CONSTRAINT ' + @NomeFK);
END


UPDATE EPA
SET EPA.CD_RECEITA_TIPO = GTR.CD_TP_RECEITA
FROM EST_PROD_PRINC_ATIVOS EPA
JOIN TLV_RECEITA_TIPO TRT 
     ON TRT.CD_RECEITA_TIPO = EPA.CD_RECEITA_TIPO
JOIN GLB_TP_RECEITA GTR 
     ON GTR.DS_TP_RECEITA = TRT.DS_RECEITA;


DECLARE @PKName NVARCHAR(200);
DECLARE @HasCT BIT = 0;

SELECT @PKName = kc.name
FROM sys.key_constraints kc
WHERE kc.parent_object_id = OBJECT_ID('GLB_TP_RECEITA')
  AND kc.type = 'PK';

SELECT @HasCT = 1
FROM sys.change_tracking_tables ct
WHERE ct.object_id = OBJECT_ID('GLB_TP_RECEITA');

IF @HasCT = 1
BEGIN
    ALTER TABLE GLB_TP_RECEITA
    DISABLE CHANGE_TRACKING;
END

IF @PKName IS NOT NULL
BEGIN
    EXEC('ALTER TABLE GLB_TP_RECEITA DROP CONSTRAINT ' + @PKName);
END

IF NOT EXISTS (
    SELECT 1 
    FROM sys.key_constraints kc
    WHERE kc.parent_object_id = OBJECT_ID('GLB_TP_RECEITA')
      AND kc.type = 'PK'
)
BEGIN
    ALTER TABLE GLB_TP_RECEITA
    ADD CONSTRAINT PK_GLB_TP_RECEITA PRIMARY KEY (CD_TP_RECEITA);
END

IF @HasCT = 1
BEGIN
    ALTER TABLE GLB_TP_RECEITA
    ENABLE CHANGE_TRACKING;
END
GO


ALTER TABLE EST_PROD_PRINC_ATIVOS
ADD CONSTRAINT FK_EST_PROD_PRINC_ATIVOS_GLB_TP_RECEITA
FOREIGN KEY (CD_RECEITA_TIPO)
REFERENCES GLB_TP_RECEITA ( CD_TP_RECEITA);
