--удаление дубля карт
select tc.ToothCardID, 0 as 'nodelete', ac.Patient,
(select max(st.ActualDate) 
							from AKUZ.T_STATE_OF_TOOTH st 
								join AKUZ.T_TOOTH t on t.ToothID = st.Tooth
												where t.ToothCard = tc.ToothCardID) as 'stDate'
into #ToothCard
from [AKUZ].[T_TOOTH_CARD] tc
	join AKUZ.T_AMBULANCE_CARD ac on ac.AmbulanceCardID = tc.AmbulanceCard
where exists(select * 
				from AKUZ.T_TOOTH_CARD tc2 
					join AKUZ.T_AMBULANCE_CARD ac2 on ac2.AmbulanceCardID = tc2.AmbulanceCard		
				where tc2.ToothCardID <> tc.ToothCardID and ac.Patient = ac2.Patient)

update tc
set tc.nodelete = 1
from #ToothCard tc
where not exists(select * from #ToothCard tc1 where tc1.Patient = tc.Patient and tc1.ToothCardID <> tc.ToothCardID and tc1.stDate >= tc.stDate)

update tc
set tc.nodelete = 1
from #ToothCard tc
where tc.nodelete = 0 
and not exists(select * from #ToothCard tc1 where tc1.Patient = tc.Patient and tc1.ToothCardID > tc.ToothCardID and tc1.nodelete = tc.nodelete)
and not exists(select * from #ToothCard tc1 where tc1.Patient = tc.Patient and tc1.nodelete = 1)

delete from #ToothCard where nodelete = 1

delete st 
from AKUZ.T_STATE_OF_TOOTH st
join AKUZ.T_TOOTH t on st.Tooth =t.ToothID
join #ToothCard tc on tc.ToothCardID = t.ToothCard

delete t 
from AKUZ.T_TOOTH t
join #ToothCard tc on tc.ToothCardID = t.ToothCard

delete som 
from AKUZ.T_STATE_OF_MOUTH som
join #ToothCard tc on tc.ToothCardID = som.ToothCard

delete t 
from AKUZ.T_TOOTH_CARD t 
join #ToothCard tc on tc.ToothCardID = t.ToothCardID

drop table #ToothCard

--смена парента карты на пациента
ALTER TABLE [AKUZ].[T_TOOTH_CARD] ADD [Patient] uniqueidentifier NULL
GO
UPDATE tc
 SET tc.[Patient] = ac.Patient
from [AKUZ].[T_TOOTH_CARD] tc
join AKUZ.T_AMBULANCE_CARD ac on ac.AmbulanceCardID = tc.AmbulanceCard
GO
ALTER TABLE [AKUZ].[T_TOOTH_CARD] ALTER COLUMN [Patient] uniqueidentifier NOT NULL
GO

ALTER TABLE [AKUZ].[T_TOOTH_CARD] ADD CONSTRAINT [FK_T_PATIENT_T_TOOTH_CARD_Patient]
FOREIGN KEY([Patient]) REFERENCES [AKUZ].[T_PATIENT] ([PatientID])
GO

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

/*** ВНИМАНИЕ! СОЗДАЕТСЯ УНИКАЛЬНЫЙ ИНДЕКС, ПОЛЕ НЕ МОЖЕТ БЫТЬ NULLABLE ***/
CREATE UNIQUE NONCLUSTERED INDEX [UQ_T_TOOTH_CARD_Patient] ON [AKUZ].[T_TOOTH_CARD] ([Patient] ASC) ON [PRIMARY]
GO

/****** Object:  Index [UQ_T_TOOTH_CARD_AmbulanceCard]    Script Date: 06.10.2023 15:59:20 ******/
DROP INDEX [UQ_T_TOOTH_CARD_AmbulanceCard] ON [AKUZ].[T_TOOTH_CARD]
GO
ALTER TABLE [AKUZ].[T_TOOTH_CARD] DROP CONSTRAINT [FK_T_AMBULANCE_CARD_T_TOOTH_CARD_AMBULANCECARD]
GO
ALTER TABLE [AKUZ].[T_TOOTH_CARD] DROP COLUMN AmbulanceCard
GO


--[T_STATE_OF_TOOTH]
ALTER TABLE [AKUZ].[T_STATE_OF_TOOTH] ADD [MedicalExamination] uniqueidentifier NULL
GO

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

-- обьединение состояний зуба в 1 позицию за 1 день
delete st
from AKUZ.T_STATE_OF_TOOTH st
where st.TypeOfState = 0
and exists(select * from AKUZ.T_STATE_OF_TOOTH st1 where st1.TypeOfState = 1 and st1.Tooth = st.Tooth and st1.ActualDate = st.ActualDate)

ALTER TABLE [AKUZ].[T_STATE_OF_TOOTH] DROP COLUMN TypeOfState
GO


ALTER TABLE [AKUZ].[T_STATE_OF_TOOTH] ADD [ToothCard] uniqueidentifier NULL
GO
UPDATE st
SET [ToothCard] = t.ToothCard
from [AKUZ].[T_STATE_OF_TOOTH] st
join AKUZ.T_TOOTH t on t.ToothId = st.Tooth
GO
ALTER TABLE [AKUZ].[T_STATE_OF_TOOTH] ALTER COLUMN [ToothCard] uniqueidentifier NOT NULL
GO

ALTER TABLE [AKUZ].[T_STATE_OF_TOOTH] ADD CONSTRAINT [FK_T_TOOTH_CARD_T_STATE_OF_TOOTH_ToothCard]
FOREIGN KEY([ToothCard]) REFERENCES [AKUZ].[T_TOOTH_CARD] ([ToothCardID])
GO
