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) stDate
INTO TEMP TABLE ToothCard
from AKUZ.T_TOOTH_CARD tc
    join AKUZ.T_AMBULANCE_CARD ac on ac.AmbulanceCardID = tc.AmbulanceCard
where 
  exists(select 1
         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 ToothCard tc
set nodelete = 1
where not exists(select 1
                 from ToothCard tc1 where tc1.Patient = tc.Patient and tc1.ToothCardID <> tc.ToothCardID and tc1.stDate >= tc.stDate);

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

delete
from ToothCard where nodelete = 1;

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

delete
from AKUZ.T_TOOTH t
  using ToothCard tc
where tc.ToothCardID = t.ToothCard;

delete
from AKUZ.T_STATE_OF_MOUTH som
  using #ToothCard tc
where tc.ToothCardID = som.ToothCard;

delete
from AKUZ.T_TOOTH_CARD t
  using ToothCard tc
where tc.ToothCardID = t.ToothCardID;

drop table ToothCard;


ALTER TABLE AKUZ.T_TOOTH_CARD ADD "patient" uuid NULL;

UPDATE AKUZ.T_TOOTH_CARD tc
SET Patient = ac.Patient
from AKUZ.T_AMBULANCE_CARD ac 
where ac.AmbulanceCardID = tc.AmbulanceCard;

ALTER TABLE AKUZ.T_TOOTH_CARD ALTER COLUMN "patient" SET NOT NULL;

ALTER TABLE AKUZ.T_TOOTH_CARD ADD CONSTRAINT FK_T_PATIENT_T_TOOTH_CARD_Patient
FOREIGN KEY("patient") REFERENCES AKUZ.T_PATIENT (PatientID);

do $$ begin
IF EXISTS(select * from pg_indexes where schemaname ilike 'AKUZ' and tablename ilike 'T_TOOTH_CARD' and indexname ilike 'UQ_T_TOOTH_CARD_Patient')
THEN
drop index AKUZ.UQ_T_TOOTH_CARD_Patient;
END IF;
end; $$;
ALTER TABLE AKUZ.T_TOOTH_CARD DROP CONSTRAINT if exists UQ_T_TOOTH_CARD_Patient;
ALTER TABLE AKUZ.T_TOOTH_CARD DROP COLUMN if exists Patient_UQ;

CREATE UNIQUE INDEX UQ_T_TOOTH_CARD_Patient ON AKUZ.T_TOOTH_CARD(Patient);

DROP INDEX UQ_T_TOOTH_CARD_AmbulanceCard ON AKUZ.T_TOOTH_CARD;
ALTER TABLE AKUZ.T_TOOTH_CARD DROP CONSTRAINT FK_T_AMBULANCE_CARD_T_TOOTH_CARD_AMBULANCECARD;
ALTER TABLE AKUZ.T_TOOTH_CARD DROP COLUMN AmbulanceCard;


ALTER TABLE AKUZ.T_STATE_OF_TOOTH ADD "medicalexamination" uuid NULL;

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);

-- обьединение состояний зуба в 1 позицию за 1 день
delete 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;


ALTER TABLE AKUZ.T_STATE_OF_TOOTH ADD "toothcard" uuid NULL;

UPDATE AKUZ.T_STATE_OF_TOOTH st
SET ToothCard = t.ToothCard
from AKUZ.T_TOOTH t 
where t.ToothId = st.Tooth;

ALTER TABLE AKUZ.T_STATE_OF_TOOTH ALTER COLUMN "toothcard" SET NOT NULL;

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);
