create table if not exists akuz.f_medical_examination_anamnesismorbi
(
    id    uuid not null
        constraint pk_f_medical_examination_anamnesismorbi primary key
        constraint fk_spread_akuz_medical_examination_anamnesismorbi
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_complaint
(
    id    uuid not null
        constraint pk_f_medical_examination_complaint primary key
        constraint fk_spread_akuz_medical_examination_complaint
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_objectivestatus
(
    id    uuid not null
        constraint pk_f_medical_examination_objectivestatus primary key
        constraint fk_spread_akuz_medical_examination_objectivestatus
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_recomendations
(
    id    uuid not null
        constraint pk_f_medical_examination_recomendations primary key
        constraint fk_spread_akuz_medical_examination_recomendations
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_epidemiological
(
    id    uuid not null
        constraint pk_f_medical_examination_epidemiological primary key
        constraint fk_spread_akuz_medical_examination_epidemiological
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_gynaecological
(
    id    uuid not null
        constraint pk_f_medical_examination_gynaecological primary key
        constraint fk_spread_akuz_medical_examination_gynaecological
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_drugallergy
(
    id    uuid not null
        constraint pk_f_medical_examination_drugallergy primary key
        constraint fk_spread_akuz_medical_examination_drugallergy
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_foodallergy
(
    id    uuid not null
        constraint pk_f_medical_examination_foodallergy primary key
        constraint fk_spread_akuz_medical_examination_foodallergy
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_patientanamnesis
(
    id    uuid not null
        constraint pk_f_medical_examination_patientanamnesis primary key
        constraint fk_spread_akuz_medical_examination_patientanamnesis
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_descr
(
    id    uuid not null
        constraint pk_f_medical_examination_descr primary key
        constraint fk_spread_akuz_medical_examination_descr
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_healing
(
    id    uuid not null
        constraint pk_f_medical_examination_healing primary key
        constraint fk_spread_akuz_medical_examination_healing
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_protocol
(
    id    uuid not null
        constraint pk_f_medical_examination_protocol primary key
        constraint fk_spread_akuz_medical_examination_protocol
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_statuslocale
(
    id    uuid not null
        constraint pk_f_medical_examination_statuslocale primary key
        constraint fk_spread_akuz_medical_examination_statuslocale
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_diagnosisjustification
(
    id    uuid not null
        constraint pk_f_medical_examination_diagnosisjustification primary key
        constraint fk_spread_akuz_medical_examination_diagnosisjustification
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_examination
(
    id    uuid not null
        constraint pk_f_medical_examination_examination primary key
        constraint fk_spread_akuz_medical_examination_examination
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_expertanamnesis
(
    id    uuid not null
        constraint pk_f_medical_examination_expertanamnesis primary key
        constraint fk_spread_akuz_medical_examination_expertanamnesis
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_transfusionanamnesis
(
    id    uuid not null
        constraint pk_f_medical_examination_transfusionanamnesis primary key
        constraint fk_spread_akuz_medical_examination_transfusionanamnesis
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_transferreddiseasestext
(
    id    uuid not null
        constraint pk_f_medical_examination_transferreddiseasestext primary key
        constraint fk_spread_akuz_medical_examination_transferreddiseasestext
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_medicationstaken
(
    id    uuid not null
        constraint pk_f_medical_examination_medicationstaken primary key
        constraint fk_spread_akuz_medical_examination_medicationstaken
            references akuz.t_medical_examination,
    value text
);

create table if not exists akuz.f_medical_examination_decision
(
    id    uuid not null
        constraint pk_f_medical_examination_decision primary key
        constraint fk_spread_akuz_medical_examination_decision
            references akuz.t_medical_examination,
    value text
);

insert into akuz.f_medical_examination_anamnesismorbi(id, value)
select m.medicalrecordid, m.anamnesismorbi
from akuz.t_medical_examination m
where m.anamnesismorbi is not null
  and not exists (select 1 from akuz.f_medical_examination_anamnesismorbi f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_complaint(id, value)
select m.medicalrecordid, m.complaint
from akuz.t_medical_examination m
where m.complaint is not null
  and not exists (select 1 from akuz.f_medical_examination_complaint f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_objectivestatus(id, value)
select m.medicalrecordid, m.objectivestatus
from akuz.t_medical_examination m
where m.objectivestatus is not null
  and not exists (select 1 from akuz.f_medical_examination_objectivestatus f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_recomendations(id, value)
select m.medicalrecordid, m.recomendations
from akuz.t_medical_examination m
where m.recomendations is not null
  and not exists (select 1 from akuz.f_medical_examination_recomendations f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_epidemiological(id, value)
select m.medicalrecordid, m.epidemiological
from akuz.t_medical_examination m
where m.epidemiological is not null
  and not exists (select 1 from akuz.f_medical_examination_epidemiological f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_gynaecological(id, value)
select m.medicalrecordid, m.gynaecological
from akuz.t_medical_examination m
where m.gynaecological is not null
  and not exists (select 1 from akuz.f_medical_examination_gynaecological f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_drugallergy(id, value)
select m.medicalrecordid, m.drugallergy
from akuz.t_medical_examination m
where m.drugallergy is not null
  and not exists (select 1 from akuz.f_medical_examination_drugallergy f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_foodallergy(id, value)
select m.medicalrecordid, m.foodallergy
from akuz.t_medical_examination m
where m.foodallergy is not null
  and not exists (select 1 from akuz.f_medical_examination_foodallergy f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_patientanamnesis(id, value)
select m.medicalrecordid, m.patientanamnesis
from akuz.t_medical_examination m
where m.patientanamnesis is not null
  and not exists (select 1 from akuz.f_medical_examination_patientanamnesis f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_descr(id, value)
select m.medicalrecordid, m.descr
from akuz.t_medical_examination m
where m.descr is not null
  and not exists (select 1 from akuz.f_medical_examination_descr f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_healing(id, value)
select m.medicalrecordid, m.healing
from akuz.t_medical_examination m
where m.healing is not null
  and not exists (select 1 from akuz.f_medical_examination_healing f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_protocol(id, value)
select m.medicalrecordid, m.protocol
from akuz.t_medical_examination m
where m.protocol is not null
  and not exists (select 1 from akuz.f_medical_examination_protocol f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_statuslocale(id, value)
select m.medicalrecordid, m.statuslocale
from akuz.t_medical_examination m
where m.statuslocale is not null
  and not exists (select 1 from akuz.f_medical_examination_statuslocale f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_diagnosisjustification(id, value)
select m.medicalrecordid, m.diagnosisjustification
from akuz.t_medical_examination m
where m.diagnosisjustification is not null
  and not exists (select 1 from akuz.f_medical_examination_diagnosisjustification f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_examination(id, value)
select m.medicalrecordid, m.examination
from akuz.t_medical_examination m
where m.examination is not null
  and not exists (select 1 from akuz.f_medical_examination_examination f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_expertanamnesis(id, value)
select m.medicalrecordid, m.expertanamnesis
from akuz.t_medical_examination m
where m.expertanamnesis is not null
  and not exists (select 1 from akuz.f_medical_examination_expertanamnesis f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_transfusionanamnesis(id, value)
select m.medicalrecordid, m.transfusionanamnesis
from akuz.t_medical_examination m
where m.transfusionanamnesis is not null
  and not exists (select 1 from akuz.f_medical_examination_transfusionanamnesis f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_transferreddiseasestext(id, value)
select m.medicalrecordid, m.transferreddiseasestext
from akuz.t_medical_examination m
where m.transferreddiseasestext is not null
  and not exists (select 1 from akuz.f_medical_examination_transferreddiseasestext f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_medicationstaken(id, value)
select m.medicalrecordid, m.medicationstaken
from akuz.t_medical_examination m
where m.medicationstaken is not null
  and not exists (select 1 from akuz.f_medical_examination_medicationstaken f where f.id = m.medicalrecordid);

insert into akuz.f_medical_examination_decision(id, value)
select m.medicalrecordid, m.decision
from akuz.t_medical_examination m
where m.decision is not null
  and not exists (select 1 from akuz.f_medical_examination_decision f where f.id = m.medicalrecordid);

call public.sp_alterdropcolumn('akuz.t_medical_examination', 'anamnesismorbi');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'complaint');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'objectivestatus');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'recomendations');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'epidemiological');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'gynaecological');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'drugallergy');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'foodallergy');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'patientanamnesis');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'descr');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'healing');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'protocol');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'statuslocale');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'diagnosisjustification');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'examination');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'expertanamnesis');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'transfusionanamnesis');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'transferreddiseasestext');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'medicationstaken');
call public.sp_alterdropcolumn('akuz.t_medical_examination', 'decision');

drop procedure if exists akuz.sp_syncmedicalexaminationspreadbatch(integer);


