-- Tables

/***** Object:  Table [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION] *****/
CREATE TABLE [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION]
(
    [MedicalRecordID] uniqueidentifier NOT NULL,
    [MedicalExamination] uniqueidentifier NULL,
    [DispanserisationInit] uniqueidentifier null,
    [VisitLocation] int NULL,
    [RecordDate] datetime NULL,
    [Post] int NOT NULL,
    [Users] uniqueidentifier NULL,
    [type_] int NOT NULL
)
ON [PRIMARY]
GO

-- Alters

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

ALTER TABLE [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION] ADD CONSTRAINT [FK_T_MEDICAL_EXAMINATION_T_DISPANSERISATION_DYNAMIC_OBSERVATION_MedicalExamination]
FOREIGN KEY([MedicalExamination]) REFERENCES [AKUZ].[T_MEDICAL_EXAMINATION] ([MedicalRecordID])
GO

ALTER TABLE [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION] ADD CONSTRAINT [FK_T_DISPANSERISATION_INIT_T_DISPANSERISATION_DYNAMIC_OBSERVATION_DispanserisationInit]
FOREIGN KEY([DispanserisationInit]) REFERENCES [AKUZ].[T_DISPANSERISATION_INIT] ([MedicalRecordID])
GO

ALTER TABLE [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION] ADD CONSTRAINT [FK_T_POST_T_DISPANSERISATION_DYNAMIC_OBSERVATION_Post]
FOREIGN KEY([Post]) REFERENCES [AKUZ].[T_POST] ([ResourceID])
GO

ALTER TABLE [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION] ADD CONSTRAINT [FK_T_USERS_T_DISPANSERISATION_DYNAMIC_OBSERVATION_Users]
FOREIGN KEY([Users]) REFERENCES [AKUZ].[T_USERS] ([UserID])
GO

ALTER TABLE [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION] ADD CONSTRAINT [FK_T_ENTITIES_T_DISPANSERISATION_DYNAMIC_OBSERVATION_type_]
FOREIGN KEY([type_]) REFERENCES [VCLib].[T_ENTITIES] ([EntityID])
GO


INSERT INTO  [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION]
( [MedicalRecordID],
    [MedicalExamination],
    [DispanserisationInit],
    [VisitLocation],
    [RecordDate],
    [Post],
    [Users],
    [type_])
select mr.MedicalRecordID,
        dp.MedicalExamination,
        dp.DispanserisationInit,
        dp.VisitLocation,
        mr.RecordTime,
        mr.Post,
        mr.Users,
        mr.type_
from AKUZ.T_MEDICAL_RECORD mr
join AKUZ.T_DISPANSERISATION_PRESENCE dp on dp.MedicalRecordID = mr.MedicalRecordID

CREATE NONCLUSTERED INDEX [IX_DISPANSERISATION_DYNAMIC_OBSERVATION_MedicalExamination] ON [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION] ([MedicalExamination]) WITH (FILLFACTOR = 95)
CREATE NONCLUSTERED INDEX [IX_T_DISPANSERISATION_DYNAMIC_OBSERVATION_DispanserisationInit] ON [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION] ([DispanserisationInit] ASC)  WITH (FILLFACTOR = 95)

if exists(select* from sys.indexes i where i.[object_id] = object_id('AKUZ.T_DISPANSERISATION_PRESENCE', 'U') and i.name = 'IX_DISPANSERISATION_PRESENCE_MedicalExamination')
	DROP INDEX IX_DISPANSERISATION_PRESENCE_MedicalExamination ON AKUZ.T_DISPANSERISATION_PRESENCE;
GO
if exists(select* from sys.indexes i where i.[object_id] = object_id('AKUZ.T_DISPANSERISATION_PRESENCE', 'U') and i.name = 'IX_T_DISPANSERISATION_PRESENCE_DispanserisationInit')
	DROP INDEX IX_T_DISPANSERISATION_PRESENCE_DispanserisationInit ON AKUZ.T_DISPANSERISATION_PRESENCE;
GO

ALTER TABLE [AKUZ].[T_DISPANSERISATION_PRESENCE] DROP CONSTRAINT [FK_T_MEDICAL_EXAMINATION_T_DISPANSERISATION_PRESENCE_MEDICALEXAMINATION]
GO
ALTER TABLE [AKUZ].[T_DISPANSERISATION_PRESENCE] DROP COLUMN MedicalExamination
GO

ALTER TABLE [AKUZ].[T_DISPANSERISATION_PRESENCE] DROP CONSTRAINT [FK_T_DISPANSERISATION_INIT_T_DISPANSERISATION_PRESENCE_DISPANSERISATIONINIT]
GO
ALTER TABLE [AKUZ].[T_DISPANSERISATION_PRESENCE] DROP COLUMN DispanserisationInit
GO

ALTER TABLE [AKUZ].[T_DISPANSERISATION_PRESENCE] DROP COLUMN VisitLocation
GO

ALTER TABLE [AKUZ].[T_DISPANSERISATION_PRESENCE] DROP CONSTRAINT [FK_T_MEDICAL_RECORD_T_DISPANSERISATION_PRESENCE_MEDICALRECORDID]
GO

ALTER TABLE [AKUZ].[T_DISPANSERISATION_PRESENCE] ADD CONSTRAINT [FK_T_DISPANSERISATION_DYNAMIC_OBSERVATION_T_DISPANSERISATION_PRESENCE_MedicalRecordID]
FOREIGN KEY([MedicalRecordID]) REFERENCES [AKUZ].[T_DISPANSERISATION_DYNAMIC_OBSERVATION] ([MedicalRecordID])
GO

delete mr
from AKUZ.T_MEDICAL_RECORD mr
join AKUZ.T_DISPANSERISATION_PRESENCE dp on dp.MedicalRecordID = mr.MedicalRecordID