create table items (
  code varchar(255),
  integration_ref varchar(255),
  classification varchar(255),
  description varchar(255),
  uom varchar(255),
  itemtag varchar(255),
  usage_type varchar(255),
  original_price numeric(16,6),
  suite numeric(16,6),
  private numeric(16,6),
  semi_private numeric(16,6),
  ward numeric(16,6),
  opd numeric(16,6),
  charity numeric(16,6),
  mdrptag varchar(255),
  accounting numeric,
  admitting numeric,
  cathlab numeric,
  rehab numeric,
  ccu numeric,
  csr numeric,
  charityob numeric,
  ctscan numeric,
  cvor numeric,
  cvrr numeric,
  dr numeric,
  dental numeric,
  rehabmed numeric,
  diet numeric,
  endo numeric,
  echo numeric,
  eeg numeric,
  er numeric,
  eye numeric,
  hearing numeric,
  heart numeric,
  satheart numeric,
  hema numeric,
  hemo numeric,
  histo numeric,
  icu numeric,
  lab numeric,
  satlab numeric,
  mammo numeric,
  nicu numeric,
  newborn numeric,
  nucmed numeric,
  operating numeric,
  picu numeric,
  pmc numeric,
  pacu numeric,
  pulmo numeric,
  radio numeric,
  satxray numeric,
  diabetes numeric,
  radonco numeric,
  tele numeric,
  ultra numeric,
  uro numeric,
  wound numeric,
  charity_reader numeric,
  semi_private_reader numeric,
  private_reader numeric,
  suite_reader numeric,
  ward_reader numeric,
  opd_reader numeric
);


alter table items add ref_service_id bigint;
insert into ref_service (id, active, classification) values (0, true, '');

create or replace function pop_ref_service(classification text, description text, uom text, merxcode text, sapcode text, itemtag text, usage_type text, mdrptag text)
returns void as $$
declare
max_id bigint;
begin
select max(id) into max_id from ref_service;
insert into ref_service (id, classification, created_by, created_date, updated_date, active, barcode, code, description, brand_name, uom, item_type, usage_type, integration_ref, doh)
values (case when max_id is null then 0 else max_id + 1 end, CASE WHEN classification = 'CONSIGNED' THEN 'MISCELLANEOUS' ELSE classification END, 'IMPORT_SCRIPT', current_date, current_date, true, '1', merxcode, description, case when classification = 'DRUG' then description else null end, uom, case when classification = 'SERVICE_EQUIPMENT' AND usage_type = 'PER_USE' then 'EQUIPMENT' when classification = 'SERVICE_EQUIPMENT' AND (usage_type is null OR usage_type = '') then 'SERVICE' else null end, usage_type, sapcode, case when mdrptag = 'TRUE' then true else null end);
raise notice 'Inserted: %', merxcode;
select max(id) into max_id from ref_service;
update items set ref_service_id = max_id where code = merxcode;
IF itemtag is not null AND itemtag != '' AND itemtag != '-' THEN
  insert into ref_service_tag (ref_service_id, ref_item_tag) values (max_id, itemtag);
END IF;
end;
$$ language plpgsql;

select pop_ref_service(classification, description, uom, code, integration_ref, itemtag, usage_type, mdrptag) from items;

create or replace function pop_ref_price(
item_id bigint,
cost_p numeric,
suite_p numeric,
private_p numeric,
semi_private_p numeric,
ward_p numeric,
charity_rate_p numeric,
opd_p numeric,
charity_reader numeric,
semi_private_reader numeric,
private_reader numeric,
suite_reader numeric,
ward_reader numeric,
opd_reader numeric,
item_class text
) returns void as $$
declare
max_id bigint;
begin

select max(id) into max_id from ref_price;
insert into ref_price (id, created_by, created_date, updated_date, active, price, billing_category_id, effective_dt, original_price, version, ref_service_id, readers_fee)
values
(max_id + 1, 'IMPORT_SCRIPT', current_date, current_date, true, opd_p, 20, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE opd_p END, 1, item_id, null),
(max_id + 2, 'IMPORT_SCRIPT', current_date, current_date, true, opd_p, 19, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE opd_p END, 1, item_id, null),
(max_id + 3, 'IMPORT_SCRIPT', current_date, current_date, true, ward_p, 1, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE ward_p END, 1, item_id, ward_reader),
(max_id + 4, 'IMPORT_SCRIPT', current_date, current_date, true, ward_p, 2, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE ward_p END, 1, item_id, ward_reader),
(max_id + 5, 'IMPORT_SCRIPT', current_date, current_date, true, suite_p, 3, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE suite_p END, 1, item_id, suite_reader),
(max_id + 6, 'IMPORT_SCRIPT', current_date, current_date, true, suite_p, 4, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE suite_p END, 1, item_id, suite_reader),
(max_id + 7, 'IMPORT_SCRIPT', current_date, current_date, true, private_p, 5, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE private_p END, 1, item_id, private_reader),
(max_id + 8, 'IMPORT_SCRIPT', current_date, current_date, true, private_p, 6, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE private_p END, 1, item_id, private_reader),
(max_id + 9, 'IMPORT_SCRIPT', current_date, current_date, true, semi_private_p, 7, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE semi_private_p END, 1, item_id, semi_private_reader),
(max_id + 10, 'IMPORT_SCRIPT', current_date, current_date, true, semi_private_p, 8, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE semi_private_p END, 1, item_id, semi_private_reader),
(max_id + 11, 'IMPORT_SCRIPT', current_date, current_date, true, charity_rate_p, 9, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE charity_rate_p END, 1, item_id, charity_reader),
(max_id + 12, 'IMPORT_SCRIPT', current_date, current_date, true, charity_rate_p, 10, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE charity_rate_p END, 1, item_id, charity_reader),
(max_id + 13, 'IMPORT_SCRIPT', current_date, current_date, true, opd_p, 11, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE opd_p END, 1, item_id, opd_reader),
(max_id + 14, 'IMPORT_SCRIPT', current_date, current_date, true, opd_p, 12, current_date, CASE WHEN item_class = 'DRUG' or item_class = 'NON_DRUG' or item_class = 'CONSIGNED' THEN
cost_p ELSE opd_p END, 1, item_id, opd_reader);
end;
$$ language plpgsql;

insert into ref_price (id, active) values (0, true);
select pop_ref_price(ref_service_id, original_price, suite, private, semi_private, ward, charity, opd, charity_reader, semi_private_reader, private_reader, suite_reader, ward_reader, opd_reader, classification) from items;

create or replace function pop_ref_service_inventory_item(ref_service_i numeric, department numeric) returns void as $$
declare
    max_id numeric;
    count numeric;
    loc_code text;
    item_code text;
begin
select count(*) into count from ref_service_item where department_id = department AND ref_service_id = ref_service_i;
IF count = 0 THEN
    select max(id) into max_id from ref_service_item;
    select code into loc_code from ref_department where id = department;
    select code into item_code from ref_service where id = ref_service_i;
    insert into ref_service_item (id, created_date, created_by, updated_date, updated_by, active, department_id, ref_service_id) values (max_id + 1, current_date, 'IMPORT_SCRIPT', current_date, 'IMPORT_SCRIPT', true, department, ref_service_i);
    raise notice 'Inserted itemcode:% department code:%', item_code, loc_code;
END IF;
end;
$$ language plpgsql;


insert into ref_service_item (id,active) values (0, true);
select pop_ref_service_inventory_item(ref_service_id, 47) from items where accounting > 0; 6
select pop_ref_service_inventory_item(ref_service_id, 58) from items where admitting > 0; 45
select pop_ref_service_inventory_item(ref_service_id, 72) from items where cathlab > 0; 1467
select pop_ref_service_inventory_item(ref_service_id, 50) from items where rehab > 0; 71
select pop_ref_service_inventory_item(ref_service_id, 15) from items where ccu > 0; 33
select pop_ref_service_inventory_item(ref_service_id, 32) from items where csr > 0; 236

select pop_ref_service_inventory_item(ref_service_id, 105) from items where charityob > 0; 2
select pop_ref_service_inventory_item(ref_service_id, 73) from items where ctscan > 0; 291
select pop_ref_service_inventory_item(ref_service_id, 21) from items where cvor > 0; 1130
select pop_ref_service_inventory_item(ref_service_id, 22) from items where cvrr > 0; 180
select pop_ref_service_inventory_item(ref_service_id, 23) from items where dr > 0; 261
select pop_ref_service_inventory_item(ref_service_id, 1253) from items where dental > 0; 4

select pop_ref_service_inventory_item(ref_service_id, 68) from items where rehabmed > 0; 4
select pop_ref_service_inventory_item(ref_service_id, 53) from items where diet > 0; 52
select pop_ref_service_inventory_item(ref_service_id, 74) from items where endo > 0; 249
select pop_ref_service_inventory_item(ref_service_id, 13) from items where echo > 0; 57
select pop_ref_service_inventory_item(ref_service_id, 54) from items where eeg > 0; 45

select code, description from ref_department where id in (68,53,74,13,54);

select pop_ref_service_inventory_item(ref_service_id, 39) from items where er > 0; 493
select pop_ref_service_inventory_item(ref_service_id, 51) from items where eye > 0; 113
select pop_ref_service_inventory_item(ref_service_id, 55) from items where hearing > 0; 1

select code, description from ref_department where id in (39, 51, 55);

select pop_ref_service_inventory_item(ref_service_id, 56) from items where heart > 0; 17
select pop_ref_service_inventory_item(ref_service_id, 56) from items where satheart > 0;

select code, description from ref_department where id in (56);

select pop_ref_service_inventory_item(ref_service_id, 25) from items where hema > 0;  211
select pop_ref_service_inventory_item(ref_service_id, 26) from items where hemo > 0; 139

select code, description from ref_department where id in (25,26);

select pop_ref_service_inventory_item(ref_service_id, 1152) from items where histo > 0; 26
select pop_ref_service_inventory_item(ref_service_id, 27) from items where icu > 0; 25

select code, description from ref_department where id in (1152, 27);

select pop_ref_service_inventory_item(ref_service_id, 59) from items where lab > 0; 390
select pop_ref_service_inventory_item(ref_service_id, 59) from items where satlab > 0;

select code, description from ref_department where id in (59);

select pop_ref_service_inventory_item(ref_service_id, 1252) from items where mammo > 0; 18
select pop_ref_service_inventory_item(ref_service_id, 29) from items where nicu > 0; 24
select pop_ref_service_inventory_item(ref_service_id, 31) from items where newborn > 0; 106
select pop_ref_service_inventory_item(ref_service_id, 62) from items where nucmed > 0; 207
select pop_ref_service_inventory_item(ref_service_id, 35) from items where operating > 0; 2370
select pop_ref_service_inventory_item(ref_service_id, 37) from items where picu > 0; 195

select code, description from ref_department where id in (1252, 29, 31, 62, 35, 37);

select pop_ref_service_inventory_item(ref_service_id, 63) from items where pmc > 0; 2441
select pop_ref_service_inventory_item(ref_service_id, 36) from items where pacu > 0; 194
select pop_ref_service_inventory_item(ref_service_id, 76) from items where pulmo > 0; 259
select pop_ref_service_inventory_item(ref_service_id, 71) from items where radio > 0; 137
select pop_ref_service_inventory_item(ref_service_id, 71) from items where satxray > 0;

select code, description from ref_department where id in (63, 36, 76, 71);

select pop_ref_service_inventory_item(ref_service_id, 52) from items where diabetes > 0; 18
select pop_ref_service_inventory_item(ref_service_id, 60) from items where radonco > 0; 27
select pop_ref_service_inventory_item(ref_service_id, 20) from items where tele > 0;  197
select pop_ref_service_inventory_item(ref_service_id, 70) from items where ultra > 0; 247
select pop_ref_service_inventory_item(ref_service_id, 75) from items where uro > 0; 151
select pop_ref_service_inventory_item(ref_service_id, 86) from items where wound > 0; 96

select code, description from ref_department where id in (52, 60, 20, 70, 75, 86);

create or replace function pop_ref_inventory_item(service_item_id numeric) returns void as $$
begin
    insert into ref_inventory_item (id, on_hand_qty) values (service_item_id, 10000);
end;
$$ language plpgsql;


create table pkg_items(
    code text,
    package_loc text,
    description text,
    classification text,
    cost_price numeric,
    ward numeric,
    suite numeric,
    private numeric,
    semi_private numeric,
    opd numeric,
    charity numeric
);

create or replace function pop_ref_service_pkg_order_set(
item_code text,
pkg_loc text,
description text,
classification text,
cost_p numeric,
ward_p numeric,
suite_p numeric,
private_p numeric,
semi_private_p numeric,
opd_p numeric,
charity_p numeric
) returns void as $$
declare
    item_id bigint;
    dep_id bigint;
    max_price_id numeric;
begin

perform pop_ref_service(classification, description, null, item_code, null, null, null, null);
select id into item_id from ref_service where code = item_code;
select max(id) into max_price_id from ref_price;

insert into ref_price (id, created_by, created_date, updated_date, active, price, billing_category_id, effective_dt, original_price, version, ref_service_id, readers_fee)
values
(max_price_id + 1, 'IMPORT_SCRIPT', current_date, current_date, true, CASE WHEN classification = 'ORDER_SET' THEN cost_p ELSE opd_p END, 20, current_date, CASE WHEN classification = 'ORDER_SET' THEN cost_p ELSE opd_p END, 1, item_id, null),
(max_price_id + 2, 'IMPORT_SCRIPT', current_date, current_date, true, CASE WHEN classification = 'ORDER_SET' THEN cost_p ELSE opd_p END, 19, current_date, CASE WHEN classification = 'ORDER_SET' THEN cost_p ELSE opd_p END, 1, item_id, null),
(max_price_id + 3, 'IMPORT_SCRIPT', current_date, current_date, true, ward_p, 1, current_date, ward_p, 1, item_id, null),
(max_price_id + 4, 'IMPORT_SCRIPT', current_date, current_date, true, ward_p, 2, current_date, ward_p, 1, item_id, null),
(max_price_id + 5, 'IMPORT_SCRIPT', current_date, current_date, true, suite_p, 3, current_date, suite_p, 1, item_id, null),
(max_price_id + 6, 'IMPORT_SCRIPT', current_date, current_date, true, suite_p, 4, current_date, suite_p, 1, item_id, null),
(max_price_id + 7, 'IMPORT_SCRIPT', current_date, current_date, true, private_p, 5, current_date, private_p, 1, item_id, null),
(max_price_id + 8, 'IMPORT_SCRIPT', current_date, current_date, true, private_p, 6, current_date, private_p, 1, item_id, null),
(max_price_id + 9, 'IMPORT_SCRIPT', current_date, current_date, true, semi_private_p, 7, current_date, semi_private_p, 1, item_id, null),
(max_price_id + 10, 'IMPORT_SCRIPT', current_date, current_date, true, semi_private_p, 8, current_date, semi_private_p, 1, item_id, null),
(max_price_id + 11, 'IMPORT_SCRIPT', current_date, current_date, true, charity_p, 9, current_date, charity_p, 1, item_id, null),
(max_price_id + 12, 'IMPORT_SCRIPT', current_date, current_date, true, charity_p, 10, current_date, charity_p, 1, item_id, null),
(max_price_id + 13, 'IMPORT_SCRIPT', current_date, current_date, true, opd_p, 11, current_date, opd_p, 1, item_id, null),
(max_price_id + 14, 'IMPORT_SCRIPT', current_date, current_date, true, opd_p, 12, current_date, opd_p, 1, item_id, null);

select id into dep_id from ref_department where code = pkg_loc;
IF dep_id is not null THEN
    perform pop_ref_service_inventory_item(item_id, dep_id);
END IF;
end;
$$ language plpgsql;

select pop_ref_service_pkg_order_set(code, package_loc, description, classification, cost_price, ward, suite ,private, semi_private, opd, charity) from pkg_items;


// data for pkg_ref_item_backup in cgh db

create table pkg_ref_item_back (
package_code text,
package_loc_code text,
package_loc_desc text,
package_classification text,
package_description text,
package_item_code text,
package_item_description text,
package_item_quantity numeric,
package_item_sap_code text,
department_code text,
department_description text
);

create or replace function pop_pkg_ref_item(
pkg_ref_code text,
pkg_ref_class text,
pkg_item_code text,
pkg_item_loc text,
pkg_item_qty numeric
) returns void as $$
declare
    max_id numeric;
    pkg_id numeric;
    pkg_item_id numeric;
    pkg_item_loc_id numeric;
    pkg_item_service_id numeric;
begin
select id into pkg_id from ref_service where code = pkg_ref_code;
select id into pkg_item_id from ref_service where code = pkg_item_code;
select id into pkg_item_loc_id from ref_department where code = pkg_item_loc;
select id into pkg_item_service_id from ref_service_item where department_id = pkg_item_loc_id and ref_service_id = pkg_item_id;
IF pkg_item_service_id is not null and pkg_id is not null THEN
    select max(id) into max_id from pkg_ref_item;
    insert into pkg_ref_item (id, created_by, created_date, updated_by, updated_date, quantity, pkg_ref_service_id, service_item_id, classification)
        values (max_id + 1, 'IMPORT_SCRIPT', current_date, 'IMPORT_SCRIPT', current_date, pkg_item_qty, pkg_id, pkg_item_service_id, pkg_ref_class);
END IF;
end;
$$ language plpgsql;

insert into pkg_ref_item (id) values (0);
select pop_pkg_ref_item(package_code, package_classification, package_item_code, department_code, package_item_quantity) from pkg_ref_item_back;

create or replace function clean_package_order_set(r_id numeric, rs_id numeric) returns void as $$
declare
    count numeric;
begin
select count(*) into count from package_order_set where id = rs_id;
IF count != 1 THEN
    delete from package_order_set where row_id = r_id;
END IF;
end;
$$ language plpgsql;