CREATE TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT_ELEMENT]
(
    [CdaSectionImmVacCertElementID] uniqueidentifier NOT NULL,
    [EhrRusJopjk8kmtj] int NULL,
    [VaccineType] varchar(255) NULL,
    [IntroductionDate] datetime NOT NULL,
    [EhrRusEjqdjjqaqj] int NULL,
    [AccountingDate] datetime NULL,
    [IntroductionResult] varchar(255) NULL,
    [EhrRusS0JLHGTLBJ] int NULL,
    [Comment] varchar(255) NULL,
    [CdaSectionImmVacCert] uniqueidentifier NULL,
    [EhrRusD4U765T5TH] int NULL,
    [IntroductionPlace] varchar(255) NULL,
    [Revaccination] bit NOT NULL,
    [RevaccinationSequenceNumber] int NULL,
    [PreparationSerial] varchar(255) NULL,
    [EhrRusPokazanijaKPrimenenijuIblp] int NULL,
    [ExpirationDate] datetime NULL,
    [Performer] int NULL,
    [PlannedDate] datetime NULL,
    [VaccineReconstitution] varchar(255) NULL
)
ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT_ELEMENT] ADD CONSTRAINT [PK_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT]
PRIMARY KEY CLUSTERED ([CdaSectionImmVacCertElementID]) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT_ELEMENT] ADD CONSTRAINT [FK_T_EHR_RUS_JOPJK8KMTJ_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT_EhrRusJopjk8kmtj]
FOREIGN KEY([EhrRusJopjk8kmtj]) REFERENCES [EHR].[T_EHR_RUS_JOPJK8KMTJ] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT_ELEMENT] ADD CONSTRAINT [FK_T_EHR_RUS_EJQDJJQAQJ_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT_EhrRusEjqdjjqaqj]
FOREIGN KEY([EhrRusEjqdjjqaqj]) REFERENCES [EHR].[T_EHR_RUS_EJQDJJQAQJ] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT_ELEMENT] ADD CONSTRAINT [FK_T_EHR_RUS_S0JLHGTLBJ_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT_EhrRusS0JLHGTLBJ]
FOREIGN KEY([EhrRusS0JLHGTLBJ]) REFERENCES [EHR].[T_EHR_RUS_S0JLHGTLBJ] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT_ELEMENT] ADD CONSTRAINT [FK_T_CDA_SECTION_IMM_VAC_CERT_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT_CdaSectionImmVacCert]
FOREIGN KEY([CdaSectionImmVacCert]) REFERENCES [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT] ([CdaSectionImmVacCertID])
GO

ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT_ELEMENT] ADD CONSTRAINT [FK_T_EHR_RUS_D4U765T5TH_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT_EhrRusD4U765T5TH]
FOREIGN KEY([EhrRusD4U765T5TH]) REFERENCES [EHR].[T_EHR_RUS_D4U765T5TH] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT_ELEMENT] ADD CONSTRAINT [FK_T_EHR_RUS_POKAZANIJA_K_PRIMENENIJU_IBLP_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT_EhrRusPokazanijaKPrimenenijuIblp]
FOREIGN KEY([EhrRusPokazanijaKPrimenenijuIblp]) REFERENCES [EHR].[T_EHR_RUS_Pokazanija_k_primeneniju_IBLP] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT_ELEMENT] ADD CONSTRAINT [FK_T_POST_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT_Performer]
FOREIGN KEY([Performer]) REFERENCES [AKUZ].[T_POST] ([ResourceID])
GO

insert into akuz.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT -- новая таблица для переноса старой отдельной вакцинации в коллекцию
(
	CdaSectionImmVacCertElementID,
	EhrRusJopjk8kmtj, 
	IntroductionDate, 
	EhrRusEjqdjjqaqj, 
	AccountingDate, 
	IntroductionResult, 
	EhrRusS0JLHGTLBJ, 
	Comment, 
	Revaccination,
	CdaSectionImmVacCert
)
select
	newid(), 
	cert.EhrRusJopjk8kmtj, 
	cert.IntroductionDate, 
	cert.EhrRusEjqdjjqaqj, 
	cert.AccountingDate, 
	cert.IntroductionResult, 
	cert.EhrRusS0JLHGTLBJ, 
	cert.Comment, 
	0,
	cert.CdaSectionImmVacCertID
from akuz.t_CDA_SECTION_IMM_VAC_CERT cert
GO

ALTER TABLE [AKUZ].[T_CDA_COMPLICATIONS_VAC_CERT] ADD [CdaSectionImmVacCertElement] uniqueidentifier NULL
GO

update compl
set compl.CdaSectionImmVacCertElement = element.CdaSectionImmVacCertElementID
from akuz.t_CDA_COMPLICATIONS_VAC_CERT compl
join akuz.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT element on element.CdaSectionImmVacCert = compl.CdaSectionImmVacCert
go

ALTER TABLE [AKUZ].[T_CDA_COMPLICATIONS_VAC_CERT] ALTER COLUMN [CdaSectionImmVacCertElement] uniqueidentifier NOT NULL
GO

ALTER TABLE [AKUZ].[T_CDA_COMPLICATIONS_VAC_CERT] ADD CONSTRAINT [FK_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT_T_CDA_COMPLICATIONS_VAC_CERT_CdaSectionImmVacCertElement]
FOREIGN KEY([CdaSectionImmVacCertElement]) REFERENCES [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT_ELEMENT] ([CdaSectionImmVacCertElementID])
GO

-- удаление колонок из таблицы CDA_SECTION_IMM_VAC_CERT, которые были перенесены в CDA_SECTION_IMM_VAC_CERT_ELEMENT
ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT] DROP CONSTRAINT [FK_T_EHR_RUS_JOPJK8KMTJ_T_CDA_SECTION_IMM_VAC_CERT_EhrRusJopjk8kmtj]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT] DROP CONSTRAINT [FK_T_EHR_RUS_EJQDJJQAQJ_T_CDA_SECTION_IMM_VAC_CERT_EhrRusEjqdjjqaqj]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_IMM_VAC_CERT] DROP CONSTRAINT [FK_T_EHR_RUS_S0JLHGTLBJ_T_CDA_SECTION_IMM_VAC_CERT_EhrRusS0JLHGTLBJ]
GO

exec dbo.sp_AlterDropColumn '[AKUZ].[T_CDA_SECTION_IMM_VAC_CERT]' , 'EhrRusJopjk8kmtj', 0
GO
exec dbo.sp_AlterDropColumn '[AKUZ].[T_CDA_SECTION_IMM_VAC_CERT]' , 'IntroductionDate', 0
GO
exec dbo.sp_AlterDropColumn '[AKUZ].[T_CDA_SECTION_IMM_VAC_CERT]' , 'EhrRusEjqdjjqaqj', 0
GO
exec dbo.sp_AlterDropColumn '[AKUZ].[T_CDA_SECTION_IMM_VAC_CERT]' , 'AccountingDate', 0
GO
exec dbo.sp_AlterDropColumn '[AKUZ].[T_CDA_SECTION_IMM_VAC_CERT]' , 'IntroductionResult', 0
GO
exec dbo.sp_AlterDropColumn '[AKUZ].[T_CDA_SECTION_IMM_VAC_CERT]' , 'EhrRusS0JLHGTLBJ', 0
GO
exec dbo.sp_AlterDropColumn '[AKUZ].[T_CDA_SECTION_IMM_VAC_CERT]' , 'Comment', 0
GO

-- удаление ссылки из таблицы CDA_COMPLICATIONS_VAC_CERT
ALTER TABLE [AKUZ].[T_CDA_COMPLICATIONS_VAC_CERT] DROP CONSTRAINT [FK_T_CDA_SECTION_IMM_VAC_CERT_T_CDA_COMPLICATIONS_VAC_CERT_CdaSectionImmVacCert]
GO
exec dbo.sp_AlterDropColumn '[AKUZ].[T_CDA_COMPLICATIONS_VAC_CERT]' , 'CdaSectionImmVacCert', 0
GO