declare @_type int = (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'ONK_FIRST_PATIENT_DIAG_NOTIFICATION') 

INSERT INTO [AKUZ].[T_MEDICAL_RECORD]
           ([MedicalRecordID]
           ,[Post]
           ,[RecordDate]
           ,[RecordTime]
           ,[Users]
           ,[type_])
select 
           n.OnkFirstPatientDiagNotificationID -- (<MedicalRecordID, uniqueidentifier,>
           ,n.Post -- <Post, uniqueidentifier,>
           ,n.RecordDate -- <RecordDate, datetime,>
           ,n.RecordDate -- <RecordTime, datetime,>
           ,n.Users -- <Users, uniqueidentifier,>
           ,@_type -- <type_, int,>)
from [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] n

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] DROP CONSTRAINT [FK_T_POST_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION_Post]
ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] DROP CONSTRAINT [FK_T_USERS_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION_Users]
GO

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] DROP CONSTRAINT [PK_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION]
GO

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] ADD [MedicalRecordID] uniqueidentifier NULL
GO

update n
	set n.[MedicalRecordID] = OnkFirstPatientDiagNotificationID
from [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] n

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] ALTER COLUMN [MedicalRecordID] uniqueidentifier NOT NULL
GO

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] ADD CONSTRAINT [PK_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION]
PRIMARY KEY CLUSTERED ([MedicalRecordID]) ON [PRIMARY]
GO

INSERT INTO [AKUZ].[T_AMBULANCE_CARD_RECORD]
           ([AmbulanceCard]
           ,[MedicalRecordID]
           ,[AmbulanceEvent]
           ,[PostingDate]
           ,[Movement])
select
           (select top 1 ac.AmbulanceCardID from AKUZ.T_AMBULANCE_CARD ac
		   where ac.CardLpu = n.LpuFrom and ac.Patient = r.Patient) -- (<AmbulanceCard, uniqueidentifier,>
           ,n.OnkFirstPatientDiagNotificationID -- <MedicalRecordID, uniqueidentifier,>
           ,null -- <AmbulanceEvent, uniqueidentifier,>
           ,null -- <PostingDate, datetime,>
           ,null -- <Movement, uniqueidentifier,>)
from [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] n
	join AKUZ.T_ONK_MOVEMENT_CARD mc on mc.OnkMovementCardID = n.OnkMovementCard
	join AKUZ.T_ONK_REGISTRY r on r.OnkRegistryID = mc.OnkRegistry
GO

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] ADD CONSTRAINT [FK_T_AMBULANCE_CARD_RECORD_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION_MedicalRecordID]
FOREIGN KEY([MedicalRecordID]) REFERENCES [AKUZ].[T_AMBULANCE_CARD_RECORD] ([MedicalRecordID])
GO

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] DROP COLUMN RecordDate
GO 

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] DROP COLUMN Users
GO

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] DROP COLUMN Post
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION]') AND name = N'UQ_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION_OnkMovementCard' and is_unique_constraint <> 1)
DROP INDEX [UQ_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION_OnkMovementCard] ON [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION]
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION]') AND name = N'UQ_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION_OnkMovementCard' and is_unique_constraint = 1)
ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] DROP CONSTRAINT [UQ_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION_OnkMovementCard]
IF  EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION]') AND name = N'OnkMovementCard_UQ')
ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] DROP COLUMN [OnkMovementCard_UQ]
GO

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION]ADD [OnkMovementCard_UQ] AS (CASE WHEN [OnkMovementCard] IS NOT NULL THEN NULL ELSE [MedicalRecordID] END)
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION_OnkMovementCard] ON [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] ([OnkMovementCard] ASC, [OnkMovementCard_UQ] ASC) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION] DROP COLUMN OnkFirstPatientDiagNotificationID
GO
