set search_path to hisinone;

begin work;

truncate table projectobj cascade;

insert into projectobj(obj_guid,lock_version)
select P.obj_guid,0
from project_load P
;
--provisorisch die Projekte auf laufend setztn, wenn unbekannt:
update project_load set k_project_status_id=(select S.id from k_project_status S
where S.hiskey_id=102)
where k_project_status_id not in (select S.id from k_project_status S)
;

truncate table project cascade;

insert into project(id,
       lid,
       uniquename,
       shorttext,
       defaulttext,
       longtext,
       k_language_id,
       k_project_type_id,
       k_project_status_id,
       startdate,
       enddate,
       reference_number,
       commentary,
       is_economical,
       k_funder_category_kdsf_id,
       k_visibility_id,
       --is_pseudonymized,
       --pseudonymized_title,
       --pseudonymized_research_partner,
       --k_project_objective_id,
       valid_from,
       valid_to,
       created_at,
       updated_at,
       obj_guid,
       lock_version
)
SELECT P.id,
O.id as lid,
       uniquename,
       shorttext,
       defaulttext,
       longtext,
       k_language_id,
       k_project_type_id,
       k_project_status_id,
       startdate,
       enddate,
       reference_number,
       commentary,
       is_economical,
       k_funder_category_kdsf_id,
       k_visibility_id,
       to_date('01.01.1900','DD.MM.YYYY') as valid_from,
       to_date('01.01.3000','DD.MM.YYYY') as valid_to,
       current_date,
       current_date,
       P.obj_guid,
       O.lock_version
FROM project_load P, projectobj O
where O.obj_guid=P.obj_guid;

select setval('project_id_seq', (select max(id) from project));

truncate table cost_unit_clearing cascade;
insert into cost_unit_clearing(
id,
       uniquename,
       defaulttext,
       longtext,
       shorttext,
       valid_from,
       valid_to,
       is_economical,
       k_language_id,
       k_project_type_id,
       obj_guid,
       lock_version)
       select 
id,
       uniquename,
       defaulttext,
       longtext,
       shorttext,
       valid_from,
       valid_to,
       is_economical,
       k_language_id,
       k_project_type_id,
       obj_guid,
       lock_version
from cost_unit_clearing_load;

select setval('cost_unit_clearing_id_seq', (select max(id) from cost_unit_clearing));

truncate table cost_unit cascade;
insert into cost_unit(id,
       project_lid,
       course_of_study_lid,
       cost_unit_study_id,
       cost_unit_clearing_id,
       parent_cost_unit_id,
--       external_cost_accounting_key_id,
       obj_guid,
       lock_version)
       select U.id,
P.lid,
null::integer as course_of_study_lid,
null::integer as cost_unit_study_id, --TODO
U.cost_unit_clearing_id,
U.parent_cost_unit_id,
--U.external_cost_accounting_key_id,
U.obj_guid,
U.lock_version
FROM cost_unit_load U left outer join project P
on (P.id=U.project_id)
where U.course_of_study_lid is null
and U.cost_unit_study_id is null
 ;
select setval('cost_unit_id_seq', (select max(id) from cost_unit));

truncate table cost_center_clearing cascade;
insert into cost_center_clearing(id,
       uniquename,
       shorttext,
       defaulttext,
       longtext,
       k_language_id,
       k_orgunittype_id,
       obj_guid,
       lock_version)
       select id,
       uniquename,
       shorttext,
       defaulttext,
       longtext,
       k_language_id,
       k_orgunittype_id,
       obj_guid,
       lock_version
FROM cost_center_clearing_load 
 ;
 
 select setval('cost_center_clearing_id_seq', (select max(id) from cost_center_clearing));

 --building neue Datensätze:
 
 update  building_load set uniquename='neuemitte' where id=71;

 
insert into building(campus_id,
       inventorynumber,
       uniquename,
       k_language_id,
       shorttext,
       defaulttext,
       longtext,
       longitude,
       latitude,
       rented,
       k_buildingquality_id,
       valid_from,
       valid_to,
       sortorder,
       obj_guid,
       lock_version)
       select L.campus_id,
L.inventorynumber,
L.uniquename,
L.k_language_id,
L.shorttext,
L.defaulttext,
L.longtext,
L.longitude,
L.latitude,
L.rented,
L.k_buildingquality_id,
L.valid_from,
L.valid_to,
L.sortorder,
md5(random()::text || clock_timestamp()::text)::uuid as obj_guid, 
L.lock_version
       from building_load L
       left outer join building B2
       on (B2.uniquename=L.uniquename)
       where L.uniquename is not null
       and B2.id is null;
       
 
truncate table cost_center cascade;


insert into cost_center(
 id,
       building_id,
       orgunit_lid,
       cost_center_clearing_id,
       parent_cost_center_id,
 --      external_cost_accounting_key_id,
       obj_guid,
       lock_version)
select L.id,
       L.building_id,
       O.lid_neu as orgunit_lid,
       L.cost_center_clearing_id,
       L.parent_cost_center_id,
--       external_cost_accounting_key_id,
       L.obj_guid,
       L.lock_version
       from cost_center_load L left outer join orgunit_load O
       on (L.orgunit_lid=O.lid_alt)
       
 ;
 select setval('cost_center_id_seq', (select max(id) from cost_center));

 
truncate table cost_unit_cost_center cascade;
insert into cost_unit_cost_center(
 id,
       cost_center_id,
       cost_unit_id,
       allocation_factor,
       is_primary_cost_center,
       obj_guid,
       lock_version)
       select 
 id,
       cost_center_id,
       cost_unit_id,
       allocation_factor,
       is_primary_cost_center,
       obj_guid,
       lock_version
from cost_unit_cost_center_load
where cost_unit_id in (select id from cost_unit);

select setval('cost_unit_cost_center_id_seq', (select max(id) from cost_unit_cost_center));


--financial_account


truncate table financial_account cascade;
insert into financial_account(
 id,
       uniquename,
       shorttext,
       defaulttext,
       longtext,
       parent_financial_account_id,
       automatic_account,
       locked_for_posting,
       valid_from,
       valid_to,
       k_financial_account_type_id,
       k_tax_tax_key,
       current_balance_positive,
       left_order,
       right_order,
       obj_guid,
       lock_version)
       select 
 id,
       uniquename,
       shorttext,
       defaulttext,
       longtext,
       parent_financial_account_id,
       automatic_account,
       locked_for_posting,
       valid_from,
       valid_to,
       k_financial_account_type_id,
       k_tax_tax_key,
       current_balance_positive,
       left_order,
       right_order,
       obj_guid,
       lock_version
from financial_account_load;

select setval('financial_account_id_seq', (select max(id) from financial_account));

--budget_source

update hisinone.budget_source_load set k_budget_source_type_id=4
where k_budget_source_type_id is null;

update hisinone.budget_source_load set k_status_id=20047;


truncate table budget_source cascade;
insert into budget_source(
 id,
       defaulttext,
       longtext,
       shorttext,
       uniquename,
       parent_budget_source_id,
       valid_from,
       valid_to,
 --      k_budget_source_type_id,
       purpose_of_funding,
       is_selectable,
       k_budget_source_category_id,
--       research_partner_id,
 --      project_management_research_partner_id,
       orgunit_lid,
 --      k_status_id,
       obj_guid,
       lock_version)
       select 
  id,
       defaulttext,
       longtext,
       shorttext,
       uniquename,
       parent_budget_source_id,
       valid_from,
       valid_to,
--       k_budget_source_type_id,
       purpose_of_funding,
       is_selectable,
       k_budget_source_category_id,
--       research_partner_id,
 --      project_management_research_partner_id,
       orgunit_lid,
 --      k_status_id,
       obj_guid,
       lock_version
from budget_source_load;

select setval('budget_source_id_seq', (select max(id) from budget_source));


--area_cost_type
truncate table area_cost_type cascade;
truncate table area_cost_typeobj cascade;

insert into area_cost_typeobj(id,obj_guid,lock_version)
select T.lid,T.obj_guid,T.lock_version
from area_cost_type_load T
;

insert into area_cost_type(
id,
       lid,
       uniquename,
       shorttext,
       defaulttext,
       longtext,
       k_language_id,
       imputed_rent_area_factor,
       valid_from,
       valid_to,
       obj_guid,
       lock_version)
       select 
 id,
       lid,
       uniquename,
       shorttext,
       defaulttext,
       longtext,
       k_language_id,
       imputed_rent_area_factor,
       valid_from,
       valid_to,
       obj_guid,
       lock_version
from area_cost_type_load;

select setval('area_cost_type_id_seq', (select max(id) from area_cost_type));
select setval('area_cost_typeobj_id_seq', (select max(id) from area_cost_typeobj));


--alternative_tree

truncate table alternative_tree_node cascade;
truncate table alternative_tree cascade;
insert into alternative_tree(
 id,
       tree_type,
       k_alternative_tree_type_id,
       tree_name,
       tree_description,
       original_alternative_tree_id,
       is_copy_of_original_hierarchy,
       obj_guid,
       lock_version)
       select 
 id,
       tree_type,
       k_alternative_tree_type_id,
       tree_name,
       tree_description,
       original_alternative_tree_id,
       is_copy_of_original_hierarchy,
       obj_guid,
       lock_version
from alternative_tree_load;

select setval('alternative_tree_id_seq', (select max(id) from alternative_tree));

insert into alternative_tree_node(
 id,
       tree_node_type,
       is_expanded,
       parent_id,
       alternative_tree_id,
       cost_center_id,
       financial_account_id,
       cost_unit_id,
       budget_source_id,
       obj_guid,
       lock_version)
       select 
id,
       tree_node_type,
       is_expanded,
       parent_id,
       alternative_tree_id,
       cost_center_id,
       financial_account_id,
       cost_unit_id,
       budget_source_id,
       obj_guid,
       lock_version
from alternative_tree_node_load
where cost_unit_id is null or cost_unit_id in (select U.id
from cost_unit_load U 
where U.course_of_study_lid is null
and U.cost_unit_study_id is null);

select setval('alternative_tree_node_id_seq', (select max(id) from alternative_tree_node));

--allocation_exclusion

truncate table allocation_exclusion cascade;
insert into allocation_exclusion(
id,
       allocation_mode_id,
       exclusion_value,
       relative_value,
       cost_type_pattern,
       budget_source_pattern,
       obj_guid,
       lock_version)
       select 
 id,
       allocation_mode_id,
       exclusion_value,
       relative_value,
       cost_type_pattern,
       budget_source_pattern,
       obj_guid,
       lock_version
from allocation_exclusion_load;

select setval('allocation_exclusion_id_seq', (select max(id) from allocation_exclusion));

--allocation 
truncate table allocation_key_element cascade;
--allocation_key_month ist leer
--allocation_key_topic_parameter ist leer
--allocation_scheme ist nicht relevant
--allocation_exclusion ist leer
truncate table allocation_step cascade;
truncate table allocation_rule cascade;
truncate table allocation_accounting_model_month cascade;
truncate table allocation_accounting_model cascade;
truncate table allocation_key_month cascade;
truncate table allocation_key_element cascade;
truncate table allocation_key cascade;
truncate table allocation_source_subset cascade;
truncate table allocation_mode cascade;

truncate table allocation_accounting_model cascade;
insert into allocation_accounting_model(
id,
       model_name,
       start_year,
       start_k_month_id,
       end_year,
       end_k_month_id,
       k_allocation_state_id,
       cost_center_alternative_tree_id,
       cost_type_alternative_tree_id,
       cost_unit_alternative_tree_id,
       budget_source_alternative_tree_id,
       note,
       obj_guid,
       lock_version)
       select 
 id,
       model_name,
       start_year,
       start_k_month_id,
       end_year,
       end_k_month_id,
       k_allocation_state_id,
       cost_center_alternative_tree_id,
       cost_type_alternative_tree_id,
       cost_unit_alternative_tree_id,
       budget_source_alternative_tree_id,
       note,
       obj_guid,
       lock_version
from allocation_accounting_model_load;

select setval('allocation_accounting_model_id_seq', (select max(id) from allocation_accounting_model));



insert into allocation_accounting_model_month(
id,
       allocation_year,
       allocation_accounting_model_id,
       k_month_id,
       k_allocation_state_id,
       is_frozen,
   --    last_modified_on,
       obj_guid,
       lock_version)
       select 
 id,
       allocation_year,
       allocation_accounting_model_id,
       k_month_id,
       k_allocation_state_id,
       0 as is_frozen,
     --  last_modified_on,
       obj_guid,
       lock_version
from allocation_accounting_model_month_load;

select setval('allocation_accounting_model_month_id_seq', (select max(id) from allocation_accounting_model_month));



insert into allocation_step(
id,
       step_name,
       allocation_accounting_model_id,
       allocation_order,
       k_allocation_method_id,
       k_allocation_type_id,
       cost_center_alternative_tree_id,
       cost_type_alternative_tree_id,
       cost_unit_alternative_tree_id,
       budget_source_alternative_tree_id,
       note,
       obj_guid,
       lock_version)
       select 
 id,
       step_name,
       allocation_accounting_model_id,
       allocation_order,
       k_allocation_method_id,
       k_allocation_type_id,
       cost_center_alternative_tree_id,
       cost_type_alternative_tree_id,
       cost_unit_alternative_tree_id,
       budget_source_alternative_tree_id,
       note,
       obj_guid,
       lock_version
from allocation_step_load;

select setval('allocation_step_id_seq', (select max(id) from allocation_step));



insert into allocation_mode(
id,
       mode_name,
       allocation_step_id,
       allocation_order,
       allocate_costs,
       allocate_proceeds,
       has_budget_source_forwarded,
       has_cost_types_forwarded,
       is_manually_created,
       note,
       has_costcenter_costunits_forwarded,
       obj_guid,
       lock_version)
       select 
 id,
       mode_name,
       allocation_step_id,
       allocation_order,
       allocate_costs,
       allocate_proceeds,
       has_budget_source_forwarded,
       has_cost_types_forwarded,
       is_manually_created,
       note,
       has_costcenter_costunits_forwarded,
       obj_guid,
       lock_version
from allocation_mode_load;

select setval('allocation_mode_id_seq', (select max(id) from allocation_mode));



insert into allocation_key(
id,
       uniquename,
       defaulttext,
       k_measure_id,
       source_key,
       automatic_creation,
       k_topic_area_id,
       original_allocation_key_id,
       obj_guid,
       lock_version)
       select 
 id,
       uniquename,
       defaulttext,
       k_measure_id,
       source_key,
       automatic_creation,
       k_topic_area_id,
       original_allocation_key_id,
       obj_guid,
       lock_version
from allocation_key_load;

select setval('allocation_key_id_seq', (select max(id) from allocation_key));

insert into allocation_key_element(
id,
       key_value,
       allocation_year,
       k_month_id,
       source_cost_center_id,
       source_cost_unit_id,
       target_cost_center_id,
       target_cost_unit_id,
       allocation_key_id,
       obj_guid,
       lock_version)
       select 
 id,
       key_value,
       allocation_year,
       k_month_id,
       source_cost_center_id,
       source_cost_unit_id,
       target_cost_center_id,
       target_cost_unit_id,
       allocation_key_id,
       obj_guid,
       lock_version
from allocation_key_element_load
where target_cost_unit_id is null or target_cost_unit_id in (select U.id
from cost_unit_load U 
where U.course_of_study_lid is null
and U.cost_unit_study_id is null);

select setval('allocation_key_element_id_seq', (select max(id) from allocation_key_element));



insert into allocation_rule(
id,
       allocation_mode_id,
       allocation_order,
       allocation_key_id,
       rule_value,
       relative_rule,
       cost_type_pattern,
       budget_source_pattern,
       financial_account_id,
       source_cost_center_id,
       target_cost_center_id,
       source_cost_unit_id,
       obj_guid,
       lock_version)
       select 
 id,
       allocation_mode_id,
       allocation_order,
       allocation_key_id,
       rule_value,
       relative_rule,
       cost_type_pattern,
       budget_source_pattern,
       financial_account_id,
       source_cost_center_id,
       target_cost_center_id,
       source_cost_unit_id,
       obj_guid,
       lock_version
from allocation_rule_load;

select setval('allocation_rule_id_seq', (select max(id) from allocation_rule));


commit;

