
set search_path to hisinone;

begin work;
drop table if exists trans_orgunit;

create table trans_orgunit(lid integer, uniquename_alt varchar(255),
uniquename_neu varchar(255));
insert into trans_orgunit(uniquename_alt,uniquename_neu)
values  
('06','2600000'),--FB EMW
('05','2500000'),--FB INFO
('07','2700000'),--FB BWP
('03','2300000'),--FB AFG
('04','2400000'),--FB DES
('02','2200000'),--FB WI
('01','2100000') --FB LOEL
;


--\COPY orgunit_load  FROM ./orgunit.csv WITH DELIMITER ',' NULL '' CSV HEADER 

--orgunit FB auf KLR Schlüssel umstellen:
update orgunit set uniquename=(select T.uniquename_neu
from trans_orgunit T
where T.uniquename_alt=orgunit.uniquename)
where uniquename in (select T.uniquename_alt
from trans_orgunit T)
;
drop table trans_orgunit;

--kopie für lookups:
select * into temp tmp_orgunit_load 
from orgunit_load;

--orgunits die schon existieren werden nicht eingefügt:
delete from orgunit_load where uniquename  in (select uniquename
from orgunit
where uniquename is not null);
 

--reset obj_guid
update orgunit_load set obj_guid=md5(random()::text || clock_timestamp()::text)::uuid
;

--lids erzeugen:
insert into orgunitobj(obj_guid,lock_version)
select O.obj_guid,0
from orgunit_load O
where O.uniquename is not null
and O.uniquename not in (select O2.uniquename
from orgunit O2
where O2.uniquename is not null);

alter table orgunit_load 
add column lid_neu integer,
add column parent_lid_neu integer,
add column parent_uniquename varchar(255);
 
update orgunit_load set lid_neu=(select OO.id
from orgunitobj OO
where OO.obj_guid=orgunit_load.obj_guid);


 update orgunit_load set parent_uniquename=(select T.uniquename
 from tmp_orgunit_load T
 where T.lid_alt=orgunit_load.parent_lid_alt);

-- update orgunit_load set parent_uniquename=(select T.uniquename
-- from tmp_orgunit_load T
-- where T.lid=orgunit_load.parent_lid);

--kopie für lookups:
select * into temp tmp_orgunit_load2 
from orgunit_load;

--nun die parent_lid_neu setzen:
update orgunit_load set parent_lid_neu=(select T.lid_neu
from tmp_orgunit_load2 T
where T.uniquename=orgunit_load.parent_uniquename);

drop table tmp_orgunit_load;
drop table tmp_orgunit_load2;

--Parents von DSen ermitteln, die schon in orgunit drin sind:
update orgunit_load set parent_lid_neu=(select min(O.lid)
from orgunit O where O.uniquename=orgunit_load.parent_uniquename)
where parent_lid_neu is null;


--nicht zugeorndete werden unter den Top Knoten gehängt:
update orgunit_load set parent_lid_neu=(select min(O.lid)
from orgunit O where O.parent_lid is null)
where parent_lid_neu is null;

--nun einfügen:
insert into orgunit(lid,
       k_orgunittype_id,
       valid_from,
       valid_to,
       astat,
       companynumber,
       description,
       shorttext,
       defaulttext,
       longtext,
       sortorder,
       k_language_id,
       parent_lid,
       uniquename,
       unit_template_id,
       k_astat_orgunit_category_id,
       k_visibility_id,
       external_scope_name,
       obj_guid,
       lock_version)
       select lid_neu as lid,
       k_orgunittype_id,
       valid_from,
       valid_to,
       astat,
       companynumber,
       description,
       shorttext,
       defaulttext,
       longtext,
       sortorder,
       k_language_id,
       parent_lid_neu,
       uniquename,
       unit_template_id,
       k_astat_orgunit_category_id,
       k_visibility_id,
       external_scope_name,
       md5(random()::text || clock_timestamp()::text)::uuid as obj_guid,
       0 as lock_version
       from orgunit_load
       where lid_neu is not null;
       
--drop table orgunit_load;


commit;

