-- Tables

-- Alters

ALTER TABLE AKUZ.T_ONK_MOVEMENT_CARD DROP CONSTRAINT FK_T_ONK_REGISTRY_CARD_T_ONK_MOVEMENT_CARD_OnkRegistryCard;

CREATE TEMPORARY TABLE tBaseCards (RecordDate timestamp, OnkRegistryCardID uuid, OnkRegistryID uuid);

-- Находим записи в регистре без рег карты но с рег картами в МК
INSERT INTO tBaseCards
(
    RecordDate,
    OnkRegistryCardID,
    OnkRegistryID
)
select rcMK.RecordDate, rcMK.OnkRegistryCardID, r.OnkRegistryID 
from AKUZ.T_ONK_REGISTRY r
    join AKUZ.T_ONK_MOVEMENT_CARD mc on mc.OnkRegistry = r.OnkRegistryID
    join AKUZ.T_ONK_REGISTRY_CARD rcMK on rcMK.OnkRegistryCardID = mc.OnkRegistryCard
where not exists
    (
        select * from AKUZ.T_ONK_REGISTRY_CARD rrc
            join AKUZ.T_ONK_REGISTRY rr on rr.OnkRegistryID = rrc.OnkRegistry
        where rr.OnkRegistryID = r.OnkRegistryID
    );

CREATE TEMPORARY TABLE tBaseCardsGrouped (OnkRegistryID uuid, OnkRegistryCardID uuid, RecordDate timestamp);

-- Группируем выбирая последнюю по дате рег карту из МК
INSERT INTO tBaseCardsGrouped
(
    OnkRegistryID,
    OnkRegistryCardID,
    RecordDate
)
SELECT t.OnkRegistryID, max(t.OnkRegistryCardID) as OnkRegistryCardID, max(t.RecordDate) as RecordDate
FROM tBaseCards t 
    JOIN (
            SELECT tgrup.OnkRegistryID, max(tgrup.RecordDate) as MaxRecordDate 
            FROM tBaseCards tgrup GROUP BY tgrup.OnkRegistryID
        ) as t1
        ON t.OnkRegistryID = t1.OnkRegistryID 
            AND t.RecordDate = t1.MaxRecordDate
GROUP BY t.OnkRegistryID;

-- Записям в регистре без рег карты проставляем последнюю рег карту по дате из МК
update AKUZ.T_ONK_REGISTRY_CARD rc
    set AKUZ.T_ONK_REGISTRY_CARD.OnkRegistry = rcg.OnkRegistryID -- select *
from tBaseCardsGrouped rcg 
	where rc.OnkRegistryCardID = rcg.OnkRegistryCardID;

CREATE TEMPORARY TABLE tRegCards (OnkRegistryCardID uuid, OnkRegistryCardID uuid, Actual_OnkRegistryCardID uuid
    , DeregistrationDate timestamp
    , DeathDate timestamp
    , InvalidGroup integer
    , DispDeregistration integer
    , DeathMKB varchar(255)
    , DeathDiag varchar(255)
    , AutopsyInfo integer);

-- находим старые в рег карты прикреплёные исключительно к МК
INSERT INTO tRegCards
(
    OnkRegistryCardID, OnkRegistryCardID, Actual_OnkRegistryCardID
    , DeregistrationDate
    , DeathDate 
    , InvalidGroup 
    , DispDeregistration 
    , DeathMKB
    , DeathDiag
    , AutopsyInfo
)
select rc.OnkRegistryCardID, rcOrig.OnkRegistryCardID as Actual_OnkRegistryCardID
    , rc.DeregistrationDate
    , rc.DeathDate
    , rc.InvalidGroup
    , rc.DispDeregistration
    , rc.DeathMKB
    , rc.DeathDiag
    , rc.AutopsyInfo
from AKUZ.T_ONK_REGISTRY_CARD rc
    join AKUZ.T_ONK_MOVEMENT_CARD mc on mc.OnkRegistryCard = rc.OnkRegistryCardID
    join AKUZ.T_ONK_REGISTRY r on r.OnkRegistryID = mc.OnkRegistry
    join AKUZ.T_ONK_REGISTRY_CARD rcOrig on rcOrig.OnkRegistry = r.OnkRegistryID
where not exists    
    (   
        select * from AKUZ.T_ONK_REGISTRY_CARD rrc
            join AKUZ.T_ONK_REGISTRY rr on rr.OnkRegistryID = rrc.OnkRegistry
        where rc.OnkRegistryCardID = rrc.OnkRegistryCardID
    );

-- Проставляем данные из старых рег карт в актуальные рег карты
update AKUZ.T_ONK_DIAGNOSIS d
    set AKUZ.T_ONK_DIAGNOSIS.OnkRegistryCard = r.Actual_OnkRegistryCardID -- select *
from tRegCards r on r.OnkRegistryCardID = d.OnkRegistryCard;

update AKUZ.T_ONK_TREATMENT t
    set AKUZ.T_ONK_TREATMENT.OnkRegistryCard = r.Actual_OnkRegistryCardID -- select *
from tRegCards r on r.OnkRegistryCardID = t.OnkRegistryCard;

update AKUZ.T_ONK_REGISTRY_CARD_TALON rct
    set AKUZ.T_ONK_REGISTRY_CARD_TALON.OnkRegistryCard = r.Actual_OnkRegistryCardID -- select *
from tRegCards r on r.OnkRegistryCardID = rct.OnkRegistryCard;

update AKUZ.T_ONK_PATIENT_YEAR_CONDITION pyc
    set AKUZ.T_ONK_PATIENT_YEAR_CONDITION.OnkRegistryCard = r.Actual_OnkRegistryCardID -- select *
from tRegCards r on r.OnkRegistryCardID = pyc.OnkRegistryCard;

update AKUZ.T_ONK_REGISTRY_CARD rcActual
    set AKUZ.T_ONK_REGISTRY_CARD.DeregistrationDate = ISNULL(rcActual.DeregistrationDate, rOld.DeregistrationDate)
    , AKUZ.T_ONK_REGISTRY_CARD.DeathDate = ISNULL(rcActual.DeathDate, rOld.DeathDate)
    , AKUZ.T_ONK_REGISTRY_CARD.InvalidGroup = ISNULL(rcActual.InvalidGroup, rOld.InvalidGroup)
    , AKUZ.T_ONK_REGISTRY_CARD.DispDeregistration = ISNULL(rcActual.DispDeregistration, rOld.DispDeregistration)
    , AKUZ.T_ONK_REGISTRY_CARD.DeathMKB = ISNULL(rcActual.DeathMKB, rOld.DeathMKB)
    , AKUZ.T_ONK_REGISTRY_CARD.DeathDiag = ISNULL(rcActual.DeathDiag, rOld.DeathDiag)
    , AKUZ.T_ONK_REGISTRY_CARD.AutopsyInfo = ISNULL(rcActual.AutopsyInfo, rOld.AutopsyInfo)
from tRegCards rOld on rcActual.OnkRegistryCardID = rOld.Actual_OnkRegistryCardID;
    
-- Времянки чистятся сами в PG

-- Удаляем ссылки на рег карты в МК
update AKUZ.T_ONK_MOVEMENT_CARD mc
    set AKUZ.T_ONK_MOVEMENT_CARD.OnkRegistryCard = null -- select * 
where mc.OnkRegistryCard is not null;

-- Удаляем чилды у не прикреплённых рег карт
delete -- select *
from AKUZ.T_ONK_DIAGNOSIS
where OnkRegistryCard in (
	select rc.OnkRegistryCardID 
	from AKUZ.T_ONK_REGISTRY_CARD rc		
	where rc.OnkRegistry is null
);

delete -- select *
from AKUZ.T_ONK_SURGICAL_OPERATION
where OnkTreatment in (
    select t.OnkTreatmentID 
    from AKUZ.T_ONK_TREATMENT t
        join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
    where rc.OnkRegistry is null
);

delete -- select *
from AKUZ.T_ONK_RADIATION_THERAPY
where OnkConditionTreatID in (
    select ct.OnkConditionTreatID 
    from AKUZ.T_ONK_CONDITION_TREAT ct 
        join AKUZ.T_ONK_TREATMENT t on t.OnkTreatmentID = ct.OnkTreatment
        join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
    where rc.OnkRegistry is null
);

delete -- select *
from AKUZ.T_ONK_HORMONIMMUNTHERAPIE
where OnkConditionTreatID in (
    select ct.OnkConditionTreatID 
    from AKUZ.T_ONK_CONDITION_TREAT ct 
        join AKUZ.T_ONK_TREATMENT t on t.OnkTreatmentID = ct.OnkTreatment
        join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
    where rc.OnkRegistry is null
);

delete -- select *
from AKUZ.T_ONK_CHEMOTHERAPY
where OnkConditionTreatID in (
    select ct.OnkConditionTreatID 
    from AKUZ.T_ONK_CONDITION_TREAT ct 
        join AKUZ.T_ONK_TREATMENT t on t.OnkTreatmentID = ct.OnkTreatment
        join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
    where rc.OnkRegistry is null
);

delete -- select *
from AKUZ.T_ONK_CONDITION_TREAT
where OnkTreatment in (
    select t.OnkTreatmentID 
    from AKUZ.T_ONK_TREATMENT t
        join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = t.OnkRegistryCard
    where rc.OnkRegistry is null
);

delete -- select *
from AKUZ.T_ONK_TREATMENT
where OnkRegistryCard in (
    select rc.OnkRegistryCardID 
    from AKUZ.T_ONK_REGISTRY_CARD rc
    where rc.OnkRegistry is null
);

delete -- select *
from AKUZ.T_ONK_REGISTRY_CARD_TALON
where OnkRegistryCard in (
    select rc.OnkRegistryCardID 
    from AKUZ.T_ONK_REGISTRY_CARD rc
    where rc.OnkRegistry is null
);

delete -- select *
from AKUZ.T_AMBULANCE_CARD_RECORD
where MedicalRecordID in (
    select rct.MedicalRecordID 
    from AKUZ.T_ONK_REGISTRY_CARD_TALON rct
        join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = rct.OnkRegistryCard
    where rc.OnkRegistry is null
);

delete -- select *
from AKUZ.T_MEDICAL_RECORD
where MedicalRecordID in (
    select rct.MedicalRecordID 
    from AKUZ.T_ONK_REGISTRY_CARD_TALON
        join AKUZ.T_ONK_REGISTRY_CARD rc on rc.OnkRegistryCardID = rct.OnkRegistryCard
    where rc.OnkRegistry is null
);

delete -- select *
from AKUZ.T_ONK_PATIENT_YEAR_CONDITION
where OnkRegistryCard in (
    select rc.OnkRegistryCardID 
    from AKUZ.T_ONK_REGISTRY_CARD rc
    where rc.OnkRegistry is null
);

-- Удаляем не прикреплённые рег карты
delete -- select * 
from AKUZ.T_ONK_REGISTRY_CARD
where OnkRegistry is null;

ALTER TABLE AKUZ.T_ONK_MOVEMENT_CARD DROP COLUMN "onkregistrycard";

