--
-- This program is part of the OpenLMIS logistics management information system platform software.
-- Copyright © 2013 VillageReach
--
-- This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more details.
-- You should have received a copy of the GNU Affero General Public License along with this program.  If not, see http://www.gnu.org/licenses.  For additional information contact info@OpenLMIS.org.
--

INSERT INTO referencedata.programs (id, code, name, description, active, shownonfullsupplytab, periodsskippable, enabledatephysicalstockcountcompleted)
  VALUES
    ('19ef7927-2597-4f75-b594-a0807d71f14a','ilshosp','Redesigned ILS','ILS Hospital','true', 'true','false', 'false');
--
-- This program is part of the OpenLMIS logistics management information system platform software.
-- Copyright © 2013 VillageReach
--
-- This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more details.
-- You should have received a copy of the GNU Affero General Public License along with this program.  If not, see http://www.gnu.org/licenses.  For additional information contact info@OpenLMIS.org.
--
INSERT INTO referencedata.geographic_levels
(id, code, name, levelNumber) VALUES
('48cfc451-99a8-44c1-b3d0-1d2d41e0cb1d', 'country', 'Country', 1),
('29222ebc-27e1-411d-a914-c3b4f79c3b4a', 'state', 'State', 2),
('4de24837-626e-436e-b347-6e5c919c8c39', 'province', 'Province', 3),
('5552712b-498a-4b85-a465-26b096ea04d1', 'district', 'District', 4);

INSERT INTO referencedata.geographic_zones
(id, code, name, levelId, parentId) values
('8a4f8d4d-5dd0-4108-810d-0f7da9059a8a', 'Root', 'Root', (SELECT id from referencedata.geographic_levels WHERE code = 'country'), NULL);

INSERT INTO referencedata.geographic_zones
(id, code, name, levelId, parentId) values
('8555d166-65a9-431e-82af-f5a0dc30a819', 'Mozambique', 'Mozambique', (SELECT id from referencedata.geographic_levels WHERE code = 'country'), NULL);


INSERT INTO referencedata.geographic_zones
(id, code, name, levelId, parentId) values
('960927bc-a015-498b-bd33-4fef4648ad5b', 'Arusha', 'Arusha',(SELECT id from referencedata.geographic_levels WHERE code = 'state'), (SELECT id from referencedata.geographic_zones WHERE code = 'Root'));


INSERT INTO referencedata.geographic_zones
(id, code, name, levelId, parentId) values
('3405b837-9a7a-46c8-9c4b-48d7784755d5', 'Dodoma', 'Dodoma',(SELECT id from referencedata.geographic_levels WHERE code = 'province'), (SELECT id from referencedata.geographic_zones WHERE code = 'Arusha'));

INSERT INTO referencedata.geographic_zones
(id, code, name, levelId, parentId) values
('cc125c1f-e335-4685-99f3-e85a889e17dd', 'Ngorongoro', 'Ngorongoro', (SELECT id from referencedata.geographic_levels WHERE code = 'district'), (SELECT id from referencedata.geographic_zones WHERE code = 'Dodoma'));






INSERT INTO referencedata.facility_operators
(id, code,      description,      displayOrder) VALUES
('5d2ccad6-9078-4996-a28c-5903a6c05bd8', 'MoH',     'MoH',     1),
('4da7a275-df6b-43b6-8d4f-883749ac7e7d', 'NGO',     'NGO',     2),
('9f1992b6-3855-465b-81fe-0b76cad138f2', 'FBO',     'FBO',     3),
('c5e44385-ae41-485f-9312-376bcec2ad2e', 'Private', 'Private', 4);



INSERT INTO referencedata.facility_types (id, code, name, description, displayOrder, active) VALUES
    ('e4a9973b-b249-4a8b-a3d4-8223b9123383', 'disp','Dispensary','Dispensary',13,'true'),
    ('a0677dcd-3e3f-4ee5-a8fa-a7bf2c77f224', 'heac','Health Centre','Health Centre',14,'true'),
    ('cf9fc00e-4e53-43ad-9faf-c362e30fb15e', 'ddho','District Designated Hospital','District Designated Hospital',10,'true');

--Insert Facilities Section
INSERT INTO referencedata.facilities (id, code, name, description, geographiczoneid,
  typeid, operatedbyid,
  active, golivedate, godowndate, comment, enabled)
            VALUES
    ('47cb7b3f-caf0-4164-bf09-46ed5192d3e2', 'MZ520495','Kibirizi','Dispensary',(select id from referencedata.geographic_zones where code ='Ngorongoro'),
    (select id from referencedata.facility_types where code ='disp'),(select id from referencedata.facility_operators where code='MoH'),
    'true','2013-01-01 00:00:00',NULL,NULL,'true'),
    ('14617116-12d5-4617-aad0-004c8a2cb83e', 'MZ510054','Katoro Bukoba DC','Health Centre',(select id from referencedata.geographic_zones where code ='Ngorongoro'),
    (select id from referencedata.facility_types where code ='heac'),(select id from referencedata.facility_operators where code='MoH'),
    'true','2013-01-01 00:00:00',NULL,NULL,'true');




--Insert Programs Supported

INSERT INTO referencedata.supported_programs (facilityId, programId, startDate, active) VALUES
  ((SELECT
      id
    from referencedata.facilities
    WHERE code = 'MZ520495'),
     (SELECT
      id
    from referencedata.programs
    WHERE code = 'ilshosp'), '11/11/12', TRUE);

  INSERT INTO referencedata.supported_programs (facilityId, programId, startDate, active) VALUES
  ((SELECT
      id
    from referencedata.facilities
    WHERE code = 'MZ510054'),
     (SELECT
      id
    from referencedata.programs
    WHERE code = 'ilshosp'), '11/11/12', TRUE);


--
-- This program is part of the OpenLMIS logistics management information system platform software.
-- Copyright © 2013 VillageReach
--
-- This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more details.
-- You should have received a copy of the GNU Affero General Public License along with this program.  If not, see http://www.gnu.org/licenses.  For additional information contact info@OpenLMIS.org.
--
insert into referencedata.orderable_display_categories
(id, code, displayname , displayOrder) values
   ('0704c1ec-c12e-4ff2-aaf6-5063828d4937','arv','Antiretrovirals',1),
    ('209fd868-2b0e-499b-b718-45be0399e797','anb','Antibiotics',4),
    ('03a9b3ad-71ac-44a2-9bbf-2d249d35bdea','anh','Antihelminthics',5),
    ('d59760c5-858e-4faa-be62-b0c7c73ceda4','anf','Antifungals',6),
    ('65933653-693c-4bd1-8217-04f4b94bc4fa','anm','Antimalarials',7),
    ('6a11b690-b8e1-4364-88d5-a6362e5f7670','bro','Bronchodilators',8),
    ('b811a180-946e-4518-95a9-c28afd587a56','cns','Central Nervous system drugs',9),
    ('3c88196d-d46e-4672-8fcf-ad9cb1b76d4a','con','Contraceptives',10),
    ('6a6fb7e1-37bf-486b-8e85-2cca6450d6bb','sup','Supplements',11),
    ('01c1bcf7-9343-40bb-8407-f0da05bac7b1','git','GIT drugs',12),
    ('93c9c5a9-e698-43fa-84b6-f320c1c2e363','opp','Ophthalmic preparation',13),
    ('85caf109-9e19-492f-91d8-3345c9d6b80a','ans','Antiseptics/Disinfectants',14),
    ('4c485800-0749-4968-aee3-341e9dc97a4f','anp','Antispasmodics',15),
    ('352bbfa1-1027-4b62-a2a8-28fc5dc042f3','anw','Anti warts',16),
    ('2bcb476b-a31d-4d5d-9027-6130abb9e3cf','oxy','Oxytocics',17),
    ('21c681be-52fa-4175-a5d9-13db4d3da018','pma','PMTCT Option A',18),
    ('0161db1a-82a6-41aa-83d9-5548d1b1282f','pmb','PMTCT Option B+',19),
    ('a3a6450e-79ba-4584-be9e-faf21bd1d6ea','ivf','Intravenous Fluids',20),
    ('f067b924-66c6-4239-b8f0-c54b400cec99','msu','Medical supplies and utensils',21),
    ('dd632936-845e-4f4c-8f49-638fadc81c5f','lab','Laboratory utensils/reagents',22),
    ('c45bf570-c204-4765-83b5-0e08ce913a56','mgt','Management tools',23),
    ('ced6315c-d92d-4883-b059-e314b99379ac','anl','Antileprosy',24),
    ('d320d5ce-9ad9-4264-aae7-69a7ebb6097d','ute','Uterotonics',26),
    ('fb1935fe-37e7-4107-893b-c10972274ed2','oid','Opportunistic Infections Drugs',27),
    ('2cc822ac-88d5-4d92-bb04-51995c348b90','chem','Chemistry',28),
    ('eac9c96c-699a-4e5e-bb50-c1ad02629d04','oth','Other Methods',29),
    ('74a092aa-fc27-4599-b0f8-8c615b09fce5','ser','Serology',30),
    ('ba724aec-c694-4222-8a65-354fd8a0cdba','bac','Bacteriology (Stains and Bacterial ID agents)',31),
    ('d55bc88d-157b-46f5-951a-91d0ffbeb4b0','acc','Accessories and other Supplies',32),
    ('3c988979-1242-48b1-969b-944d976ad06f','hae','Haematology',33),
    ('5480e7ce-7cd6-4422-ab19-9d5f3d11804a','cdf','CD-4 Testing Facs Count',34),
    ('521d249f-dbc4-452f-9ecd-e949c879d379','axy','AXYSM',35),
    ('ccf8234a-7129-4726-a877-fa82e2a1833e','vil','Viral Load',36),
    ('60769202-2592-4ca6-917c-9265a1aa8e62','pcr','PCR Test',37),
    ('dd4b01c4-9144-4ec5-9a60-f4b17d0570dc','bga','Blood and glucose analyser',38),
    ('9143508a-10b4-48ca-a87b-9055316e2f2a','chu','Reagents Strip for Urine - Chemistry',39),
    ('4087e164-3545-4b65-9126-a3e15a9e9adb','his','Histopathology',40),
    ('0d880b88-41ae-4dd0-9623-e365b4b07257','bfi','Bacterial and Fungal Isolation and Identification',41),
    ('5c1989cf-97d5-4c74-9f35-d151689f3745','bsi','Bacterial Stains and Identification Agent',42),
    ('a8e6a05c-3099-4a8c-b4dd-f489acf263ef','pry','Parasitology',43),
    ('7f23310b-6b99-4a7d-99a1-225df6bb69cb','cgi','Consumables and General Items',44),
    ('e5469f37-d3b6-45a6-8bb9-b516307c44e0','hep','Haematology Pentra 8',46),
    ('468a1cb9-01bf-451a-a5cd-c3bfde9e3ec7','fas','Facs Callibur',47),
    ('6cf111b1-b812-4cce-a924-b7cdd415f44a','btf','Blood Transfusion',48),
    ('67ecb679-330b-4bb7-ab78-db8f60631870','ant','Anti - Inflamatory',49),
    ('4760f7fb-3eb3-45c0-bc3c-3c09c3462300','antang','Anti-Angina Medicines',31),
    ('4f7a3882-a858-46dc-a74b-1b628b2b361f','gen','General Purposes, Laboratory and Disinfectants Items',51),
    ('3813a5dd-4690-4af1-86b5-7068baff87dd','hospef','Hospital Equipment And Furniture ',52),
    ('21af9196-367b-48f7-a372-5cfd679367d4','hosplub','Hospital Linen, Uniforms & Beddings Materials ',53),
    ('78215733-4aab-4dd9-8274-0f1afe0ade2a','hhw','Hospital Hollow Wares ',54),
    ('fcd972ff-70cc-424c-b2a3-508167198b34','patmgmnt','Patient Management Devices And Other Tools ',55),
    ('8fbd19bc-433e-46b4-9404-774b55f18b76','psychoa','Psychotherapeutic Agents',56),
    ('4bbbc077-1b2e-4568-ac68-00130a15fe57','reud','Reusable Devices (Hospital Instruments) ',57),
    ('360e7db5-db21-4c00-a9a4-c6c27b80da24','atb','Anti-TB',25),
    ('dc092c11-5549-4514-b8a4-38420e847e59','antac','Anti - Acid Medicines',58),
    ('b4fd2fa7-d262-4bb0-9757-4dc910b8e6e4','antdb','Anti - Diabetic Medicines',59),
    ('d5f4f9fe-5bb4-46e5-b767-7dd3738b5cd4','anhc','Anti - Hypertensive and Cardiac Glycoside',60),
    ('b962e5f4-97ba-47ce-8528-49ce8ce0870f','antp','Antipruritics',61),
    ('d2286ab0-91f6-4e69-936e-ae84d25a136b','cat','Catheters &Tubes',62),
    ('5d3ec5ff-62d2-41d3-969a-aac8c73fd514','DiureticMed','Diuretic Medicines',63),
    ('d100e6ba-ede9-4aa2-8681-1ddfa91b9e7a','MicroSerol','Microbiology and Serology Items',64),
    ('cc81fafc-6680-4586-b64b-f7c599dc18f7','Patmgmntdevice','Patient Management Devices And Other Tools',65),
    ('8066166a-f284-40e6-97c7-2b8934db67ee','psychothagent','Psychotherapeutic Agents',66),
    ('fabb2e40-f61d-4f62-96c8-9a97f6f2b102','steroid','Steroidal Anti-Inflammatory Medicines',67),
    ('91fc0368-3052-4f98-96d6-4a36783e35e1','surgicalInst','Surgical Instruments & Medical Kits/Sets',68),
    ('d7114982-df56-4167-ba7e-9abd21a9574c','syrin','Syringes, Needles, Cannulas &  Administration Sets',69),
    ('dfdcbbc6-5c8c-413d-a72e-a6e7d47ea681','vit','Vitamins & Minerals',70),
    ('fb324808-48b0-4100-8dd1-7e245a4a5cee','wasteCollect','Waste Collection Bags',71),
    ('7053ebe3-ab6f-4b86-ada3-7f0fb9818802','dress','Dressing Material ',72),
    ('72260fdb-c5eb-4894-bb63-0cb138ef1fc9','ana','Anti-allergies and Medicines used Anaphylaxis and Shock',3),
    ('953c07e6-5230-456c-8193-0700ab164e62','cough','Cough Medicine',72),
    ('b5c4dd5b-642a-43ca-a940-00ef67891aee','NSAIDS','Anti-pyretics NSAIDS',73),
    ('ccbe34ba-c9a5-443c-9f28-72065bd6a67b','anschisto','Anti-Schistosomal',74),
    ('f7d22767-f4fa-4094-bc04-d2ec8b43dc7f','screenm','Screen Master',75),
    ('7373b9f0-a682-4703-98fa-2f1fd6a760f0','anlg','Analgesics',0),
    ('e5e7f5b2-785f-47dd-97e5-ce889f94e2d6','antidote','Antidote',76),
    ('8c04054e-2805-41cd-ad04-4bc92467fc8a','hemamicro60','Hematology Micro 60',78),
    ('08c3669d-480a-474a-bba0-5e7f96151113','amoe','Amoebicides',1),
    ('c1b3ce64-0c67-41bf-8481-d72f5c0843ae','lane','Local Anesthesia',2),
    ('782af745-f878-42c9-888e-44e64028d1f4','gane','General Anesthesia',3),
    ('7c5d4bd9-0407-4861-95b5-9da7e0a1ad24','asth','Anti-Asthmatic and Cough Medicine',6),
    ('4fc373c8-29bf-4f31-86e0-d307a4be392a','Anti-Co','Anti-Coagulant & Antagonists',79),
    ('90a8e876-4f1f-4b65-901d-af45ec36cc1a','Anti-Ep','Anti-Epileptics/Anticonvasants',80),
    ('466f60f4-5f62-48a1-9a28-a93da6924bea','Anti-Ha','Anti-Haemorrhoids',81),
    ('2f7ca10a-f7aa-4851-a53e-933c408c5324','Anti-Py','Anti-Pyretics And Nsaids',82),
    ('a63bcad0-aa5a-475a-b4c1-a0ec2fa85030','Gloves','Gloves & Other Protective Gears',83),
    ('5defe9f6-bf47-4707-8c33-17ab09ad1f41','Ironde','Iron Deficiency Anaemias Medicines',84),
    ('957eda00-5b8a-4941-a797-f324980b1dc5','Liq','Liquid, Correcting Electrolytes and Acid/Base Disturbances',85),
    ('4caa657a-9b5e-47ea-ae92-2f44ea190de5','Med-Diar','Medicines used in Diarrhoea',86),
    ('cf9d4b5f-158d-48f6-addd-aaaf8ee8230a','Muscle-Relax','Muscle Relaxants , Cholineserase Inhibitors and Anticholinergic',87),
    ('4b0309e5-b443-4f2a-b05a-846d23d87e0c','Narcoti','Narcotic & Antagonists',88),
    ('4e0946b1-a626-499d-be18-acd4304d1f30','Ophtham','Ophthamological Preparations',89),
    ('480ed068-b510-4769-b0a5-bd1bf7b0ca4b','SeraIm','Sera And Immunoglobulins',90),
    ('f3a5492d-3cf2-458c-9041-6fd9168b49b9','X-RayC','X-Ray Chemicals And Reagents',91),
    ('a0e3724f-1d19-40ec-a147-826d31bdf0a9','X-RayF','X-Ray Films',92),
    ('c5906b14-0074-4b79-86cd-fb207b1de953','ane','Anaesthetics',1),
    ('34f57249-3079-4a3a-a877-8316478fae08','hmind','Hematology Reagents for Mindray Machines BC 3200',99),
    ('876accb8-5efe-4097-8797-fe771b6d5a0b','hsys','Haematology Reagents for Sysmex (KX21, KX 1000i/PoCH)',110),
    ('d3e10233-f01e-4dfa-a3ef-6aa8da4916f9','gds','GIT drugs and Suppliments',122),
    ('76f33c27-55a3-4460-a2c4-a4daf1a4450b','ecfvm','Electrolystes corection  Fluid, Vaccines and Immunoglobins',123),
    ('41f8cba5-4eae-433e-9eb3-620c6fb3fdb9','adv','Antiretriviral Drugs -ARVs',124),
    ('51dcafbd-f6de-4bcc-a7d8-09945ce80d4e','aaam','Anti-Allergies and anaphylaxis mediecine',114),
    ('33b93768-0004-480b-ba63-cdd267cdc377','ast','Asthma',115),
    ('4ad7b754-960a-43a0-83f6-e7e89177e687','anhs','Antihelminthes',116),
    ('0ca21034-dcec-46aa-9602-7b12b2e1a7cb','anfs','Anti-Fungals',117),
    ('42fd00a8-c9af-40df-8dac-554891d08f83','antd','Anti-Diabetics',118),
    ('1e59ccf4-0e1c-4989-a989-676a5879478a','ahcg','Anti - Hypertensive and Cardiac Glycoside.',119),
    ('e62d5b1c-4c98-48bc-b89d-522fdc05788c','amm','Anti-Malarial medicine',120),
    ('0bf945f3-b9a4-4166-b42a-1bfd3570ad18','cnsm','Central Nervous System Medicines',121),
    ('8c7044f7-59d0-461f-9615-800c9834da5d','agmr','Anesthetics General and Muscle relaxants',112),
    ('8b76727e-cb09-4d52-a7c7-eeaab5e80306','anta','Antibiotics and Amoebicides',113),
    ('1c844e05-8ee0-4ff8-9fd6-5f8247e2f955','laa','Local Anaesthetics and analgesics',111),
    ('5450a6b9-58cf-43a5-aa99-1b15ff3b9eec','nar','Narcotics',126),
    ('7ec73257-11d8-4d35-b1a7-c856ad3d92b3','fpsmp','Family Planning and Safe Motherhood product',127),
    ('60198ac9-bd24-4526-9fc7-712d1265fe4d','ansd','Antiseptics/Disinfectants.',128),
    ('12be084f-67bf-4cc6-bd68-62bea8a0bcf1','Ophthamp','Ophthalmic Preparation.',129),
    ('9e752ffa-2dc2-4871-9068-8a178d125c85','msus','Medical supplies and utensils.',130),
    ('de08a4c1-8a35-42c7-8a40-12e8128c1975','nmv','Nutritions (Minerals and Vitamins)',131),
    ('6d0800c9-28dd-4f15-b311-acb232979576','sergy','Serology.',132),
    ('48a2e880-c266-4909-82d6-6b19ecd76166','hbtr','Hematology and Blood transfusion',133),
    ('3c1fc4a6-0d4b-4edd-9017-d95ed2e0cd71','chemy','Chemistry.',134),
    ('127f6a00-4194-40c7-9a8f-69a966a76a53','icd','Immunophenotyping/CD4',135),
    ('56722ec3-8533-4180-b236-51de7358bb5e','cosu','Consumables',136),
    ('cba540f5-c161-43e1-b587-b985771aa273','lmt','LMIS and Management Tools',137),
    ('d35faf64-aa54-4e88-8b85-68c5e84e96c4','tbli','TB&LEPROSY ITEMS',138),
    ('90dd8a41-2b8c-4e8c-b9ad-9ea5625c0213','ctfc','CD-4 Testing Facs Count.',201),
    ('6237320a-f948-4c09-99ad-56584edea531','vrl','Viral Load.',202),
    ('d953ba2e-6d5b-453d-b731-137efc0c898c','htlgy','Haematology.',203),
    ('6ea32c73-181a-4346-8014-460196ac5dcb','htlgym','Haematology Micro 60.',204),
    ('a6a601c9-09a1-463b-9f96-ba30d52f52e8','htlgyrfs','Haematology Reagents for Sysmex (KX21, KX 1000i/PoCH).',205),
    ('6260c728-bac8-4765-80d2-228ebf07bef7','htlgyrfmm','Haematology Reagents for Mindray Machines BC 3200.',206),
    ('933183ff-6379-4b28-a2c3-b505199a12dd','bctrg','Bacteriology (Stains and Bacterial ID agents).',207),
    ('65a1c0ce-1395-40d6-bc69-e1e026491ca5','lur','Laboratory utensils/reagents.',208),
    ('bff1c85f-94f1-4d35-b0ab-3751c86758dd','rsfuc','Reagents Strip for urine - Chemistry.',209),
    ('00ff6a3f-6dfe-486e-95e5-f7d28734e07f','cgs','Consumables and General items.',211),
    ('adbec852-070b-471f-8cdb-18cc8f3cedfe','scc','Specimen Collection/Consumables',45),
    ('484a0323-0f29-4b16-bf66-35c78ffa6777','sccnew','Specimen Collection/Consumables.',210),
    ('ea26f5d0-c35a-4c85-a809-22189e64b906','hmp','Haematology Pentra 80.',212),
    ('cddbb94a-4859-48ad-ae4c-ff256c5c7563','dmn','Dawa za Magonjwa Nyemelezi',125);



insert into referencedata.dispensables
(id,type) values
('bc51ce3e-322a-4d41-a9ec-aef2aee77d55','default'),
('5ccbecf3-3403-4331-8d34-f49030005472','default'),
('5667378b-160a-4dc4-aaf3-6ab21ffae8e3','default'),
('4c781e51-d6c4-45c6-b9be-ca8dbebcd889','default');

insert into referencedata.dispensable_attributes
(dispensableid,key,value) values
('bc51ce3e-322a-4d41-a9ec-aef2aee77d55','dispensingUnit','100 Tablets'),
('5ccbecf3-3403-4331-8d34-f49030005472','dispensingUnit','1000 Tabs'),
('5667378b-160a-4dc4-aaf3-6ab21ffae8e3','dispensingUnit','28 Tablets'),
('4c781e51-d6c4-45c6-b9be-ca8dbebcd889','dispensingUnit','30 Tablets');

--Insert products
insert into referencedata.orderables
(id, code, fullproductName, description, netcontent, packRoundingThreshold, roundToZero,dispensableid,versionid) values
('b477a8e4-056f-4506-ab57-c7ef5f6afd33','10010129AC','Zinc Sulphate','Zinc Sulphate',100,1,'true','bc51ce3e-322a-4d41-a9ec-aef2aee77d55',1),
('b327c607-5a87-415d-8c62-daff58def51a','10010031MD','Griseofulvin',NULL,1000,1,'true','5ccbecf3-3403-4331-8d34-f49030005472',1),
('72c581bd-93dd-43e2-b9e0-4b13002fdf97','10010222SC','LOSARTAN 50MG TABLETS','LOSARTAN 50MG TABLETS',28,1,'true','5667378b-160a-4dc4-aaf3-6ab21ffae8e3',1),
('3bf36a15-b276-4c14-ba58-b63ed02cd5d7','10010190SP','LORATIDINE 10MG TABLETS','LORATIDINE 10MG TABLETS',30,1,'true','4c781e51-d6c4-45c6-b9be-ca8dbebcd889',1),
('dcca56dc-dbeb-4454-a70c-f84afcbb12f6','10010190MD','Loratidine Tablet',NULL,30,1,'true','4c781e51-d6c4-45c6-b9be-ca8dbebcd889',1);



--Insert program_products
insert into referencedata.program_orderables(id, programId, orderableid, fullSupply, priceperpack, active, displayOrder, orderabledisplaycategoryId, orderableversionid) values
('46a33d1d-d9a5-4ed6-9f38-00eae4079c78',(select id from referencedata.programs where code='ilshosp'),(select id from referencedata.orderables where code = '10010129AC'),'true',0.00,'true',455,(select id from referencedata.orderable_display_categories where code='cgi'),1),
('2ed21424-6e08-471a-a66f-1f36639846a6',(select id from referencedata.programs where code='ilshosp'),(select id from referencedata.orderables where code = '10010031MD'),'true',0.00,'true',0,(select id from referencedata.orderable_display_categories where code='cgi'),1),
('e264962a-45ec-4c7f-8f99-83c37dfe22b8',(select id from referencedata.programs where code='ilshosp'),(select id from referencedata.orderables where code = '10010222SC'),'true',6300.00,'true',560,(select id from referencedata.orderable_display_categories where code='cgi'),1),
('23102117-fecf-46b7-b6ad-540b666629b7',(select id from referencedata.programs where code='ilshosp'),(select id from referencedata.orderables where code = '10010190SP'),'true',2600.00,'true',9,(select id from referencedata.orderable_display_categories where code='cgi'),1),
('ac8a72d6-082e-4728-8d16-ae016993948a',(select id from referencedata.programs where code='ilshosp'),(select id from referencedata.orderables where code = '10010190MD'),'true',133300.00,'true',356,(select id from referencedata.orderable_display_categories where code='cgi'),1);





--Insert facility_approved_products
insert into referencedata.facility_type_approved_products(id, facilityTypeId, programid, orderableid, maxperiodsOfStock) values
('430418a6-5b15-4ffa-a4ab-ad6d8369ed2e',(select id from referencedata.facility_types where code='disp'), (select id from referencedata.programs where code='ilshosp'),
 (select id from referencedata.orderables where  code='10010129AC'), 2),
('c5fb65b8-772c-4a2a-a45b-d76ac0bbec2f',(select id from referencedata.facility_types where code='disp'), (select id from referencedata.programs where code='ilshosp'),
(select id from referencedata.orderables where  code='10010031MD'), 2),
('d0712f5d-46bb-4dfe-a277-68cc8876d74c',(select id from referencedata.facility_types where code='disp'), (select id from referencedata.programs where code='ilshosp'),
(select id from referencedata.orderables where  code='10010222SC'), 2),
('1b0345c7-33ad-4d38-bc56-87b96fbcf7d1',(select id from referencedata.facility_types where code='disp'), (select id from referencedata.programs where code='ilshosp'),
(select id from referencedata.orderables where  code='10010190SP'), 2),
('f0a17ebf-744f-4226-a116-72eb94338883',(select id from referencedata.facility_types where code='heac'), (select id from referencedata.programs where code='ilshosp'),
(select id from referencedata.orderables where  code='10010129AC'), 2),
('7be9d936-3194-477d-870d-0db535d61142',(select id from referencedata.facility_types where code='heac'), (select id from referencedata.programs where code='ilshosp'),
(select id from referencedata.orderables where  code='10010031MD'), 2),
('37ecc6dc-e267-4edd-96fe-5351d1e91db6',(select id from referencedata.facility_types where code='heac'), (select id from referencedata.programs where code='ilshosp'),
(select id from referencedata.orderables where  code='10010222SC'), 2),
('c8843e80-9bd3-4845-991f-00e3c8e36d3a',(select id from referencedata.facility_types where code='heac'), (select id from referencedata.programs where code='ilshosp'),
(select id from referencedata.orderables where  code='10010190SP'), 2);







INSERT into referencedata.roles(id, name, description) VALUES
    ('edfa94dc-3217-4d5c-91fd-6d43c8691434', 'Create & Submit','Can only create R&R'),
    ('3c2f3bda-04e6-45f2-b043-c943d9be1986', 'Approve Only','Can only approve the R&R'),
    ('08f34610-6e0b-4376-83bd-a974a7b02ebd', 'Authorize only','Can only authorize R&R'),
    ('3c1e6ae3-96a1-4fae-a377-2179f8e8fd4f', 'Admin', 'Admin');

INSERT into referencedata.role_rights
(roleId, rightid) VALUES
  ((SELECT  id from referencedata.roles WHERE name = 'Create & Submit'), (select id from referencedata.rights where name = 'REQUISITION_VIEW')),
  ((SELECT id from referencedata.roles WHERE name = 'Approve Only'), (select id from referencedata.rights where name = 'REQUISITION_VIEW')),

  ((SELECT id from referencedata.roles WHERE name = 'Authorize only'), (select id from referencedata.rights where name = 'REQUISITION_VIEW')),
((SELECT id from referencedata.roles WHERE name = 'Admin'), (select id from referencedata.rights where name = 'FACILITIES_MANAGE')),
((SELECT id from referencedata.roles WHERE name = 'Admin'), (select id from referencedata.rights where name = 'USER_ROLES_MANAGE')),
((SELECT id from referencedata.roles WHERE name = 'Admin'), (select id from referencedata.rights where name = 'PROGRAMS_MANAGE')),
((SELECT id from referencedata.roles WHERE name = 'Admin'), (select id from referencedata.rights where name = 'PROCESSING_SCHEDULES_MANAGE')),
((SELECT id from referencedata.roles WHERE name = 'Admin'), (select id from referencedata.rights where name = 'REQUISITION_TEMPLATES_MANAGE')),
((SELECT id from referencedata.roles WHERE name = 'Admin'), (select id from referencedata.rights where name = 'USERS_MANAGE')),
((SELECT id from referencedata.roles WHERE name = 'Admin'), (select id from referencedata.rights where name = 'REPORTS_VIEW')),
((SELECT id from referencedata.roles WHERE name = 'Admin'), (select id from referencedata.rights where name = 'REPORT_TEMPLATES_EDIT')),
((SELECT id from referencedata.roles WHERE name = 'Admin'), (select id from referencedata.rights where name = 'SYSTEM_SETTINGS_MANAGE'))
;


--Insert into referencedata.user table

INSERT into referencedata.users
(id, userName, homefacilityId, firstName, lastName, email, verified, active) VALUES
  ('5806ca9e-24e0-4b40-9638-f551e6048e27', 'StoreInCharge',
   (SELECT
      id
    from referencedata.facilities
    WHERE code = 'MZ520495'), 'Hassan', 'Matoto', 'Fatima_Doe@openlmis.com', TRUE, TRUE),

  ('a3c2508d-f46c-42b7-ab21-7a0fdfa0071e', 'FacilityInCharge',
   (SELECT
      id
    from referencedata.facilities
    WHERE code = 'MZ520495'), 'Rachel', 'Stephen', 'Jane_Doe@openlmis.com', TRUE, TRUE),

  ('c86bfd2c-7a22-4911-a9bc-0921c34681de', 'lmu',
   (SELECT
      id
    from referencedata.facilities
    WHERE code = 'MZ520495'), 'Evance', 'Nkya', 'Frank_Doe@openlmis.com', TRUE, TRUE),
    ('81104549-a8e0-454d-86c8-dada103365e1', 'Admin123', (SELECT
      id
    from referencedata.facilities
    WHERE code = 'MZ520495'), 'John', 'Doe', 'John_Doe@openlmis.com', TRUE, TRUE);




--Insert supervisory nodes


INSERT into referencedata.supervisory_nodes
(id, facilityId, name, code, parentId) VALUES
  ('7b2d5193-f2fa-45a1-83f0-524beaedbbc4', (SELECT id from referencedata.facilities WHERE code = 'MZ520495'), 'Mwanza Zone', 'MWAN-SNZ', NULL);




--Insert role assignments

--INSERT into referencedata.role_assignments
--(type, id, userId, roleId, programId, supervisoryNodeId) VALUES
--  ('supervision', '659c9b9f-c028-45b2-bf33-be77b7dc28e8', (SELECT ID from referencedata.USERS WHERE username = 'StoreInCharge'), (SELECT id from referencedata.roles WHERE name = 'Create & Submit'), (SELECT id from referencedata.programs WHERE code = 'ilshosp'), NULL),
--  ('supervision', 'b9e1c145-b633-43cd-ad0a-a4f4cc667777', (SELECT ID from referencedata.USERS WHERE username = 'lmu'), (SELECT id from referencedata.roles WHERE name = 'Approve Only'), (SELECT id from referencedata.programs WHERE code = 'ilshosp'), (select id from referencedata.supervisory_nodes where code='MWAN-SNZ')),
--  ('supervision', '2659d31c-7f83-4d85-bc71-1abc9009115e', (SELECT ID from referencedata.USERS WHERE username = 'FacilityInCharge'), (SELECT id from referencedata.roles WHERE name = 'Authorize only'), (SELECT id from referencedata.programs WHERE code = 'ilshosp'), NULL),
--  ('direct', 'aad366b2-59cf-4bac-9ca2-fd74f91fd13b', (SELECT id from referencedata.users WHERE userName = 'Admin123'), (SELECT id from referencedata.roles WHERE name = 'Admin'), (SELECT id from referencedata.programs WHERE code = 'ilshosp'), NULL);
--


INSERT into referencedata.role_assignments
(type, id, userId, roleId, programId, supervisoryNodeId) VALUES
  ('supervision', uuid_generate_v4(), (SELECT ID from referencedata.USERS WHERE username = 'StoreInCharge'), (SELECT id from referencedata.roles WHERE name = 'Storeroom Manager'), (SELECT id from referencedata.programs WHERE code = 'ilshosp'), (select id from referencedata.supervisory_nodes where code='MWAN-SNZ')),
  ('supervision', uuid_generate_v4(), (SELECT ID from referencedata.USERS WHERE username = 'lmu'), (SELECT id from referencedata.roles WHERE name = 'District Storeroom Manager'), (SELECT id from referencedata.programs WHERE code = 'ilshosp'), (select id from referencedata.supervisory_nodes where code='MWAN-SNZ')),
  ('supervision', uuid_generate_v4(), (SELECT ID from referencedata.USERS WHERE username = 'FacilityInCharge'), (SELECT id from referencedata.roles WHERE name = 'Store Manager'), (SELECT id from referencedata.programs WHERE code = 'ilshosp'), (select id from referencedata.supervisory_nodes where code='MWAN-SNZ')),
  ('direct', uuid_generate_v4(), (SELECT id from referencedata.users WHERE userName = 'Admin123'), (SELECT id from referencedata.roles WHERE name = 'Admin'), (SELECT id from referencedata.programs WHERE code = 'ilshosp'), (select id from referencedata.supervisory_nodes where code='MWAN-SNZ')),
  






INSERT INTO referencedata.processing_schedules (id, code, name, description) VALUES
 ('9faacf97-357c-4ad1-8b0c-c21bc9de6737', 'groupA','BiMonthly Group A','Reporting and Ordering Group A'),
 ('2d21c306-6309-4bdb-ab26-c57b0feb1f51', 'groupB','BiMonthly Group B','Reporting and Ordering Group B');



INSERT INTO referencedata.processing_periods
(id, name, description, startDate, endDate, processingscheduleId) VALUES
    ('70f5bef1-9755-4f15-8933-d1b02cf69e2d','Jan 2019',NULL,'2019-01-01T00:00:00.000+03:00','2019-01-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('297bf315-1dce-4fbc-9d6d-7a572d2b213c','Jan -  Feb 2019',NULL,'2019-01-01T00:00:00.000+03:00','2019-02-28T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('1bdc180f-d1f3-4e71-9150-4ce244d1c97b','March 2019',NULL,'2019-03-01T00:00:00.000+03:00','2019-03-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('ac867e52-e468-48da-afe7-08ef3836a979','March - April 2019',NULL,'2019-03-01T00:00:00.000+03:00','2019-04-30T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('a48d5a86-683f-423b-99a2-2451afcef1c9','May 2019',NULL,'2019-05-01T00:00:00.000+03:00','2019-05-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('dee28f2b-390e-4253-b571-df71b5565f32','May - June 2019',NULL,'2019-05-01T00:00:00.000+03:00','2019-06-30T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('e13960d3-7b5e-403e-8796-0cc1f58d791c','July 2019',NULL,'2019-07-01T00:00:00.000+03:00','2019-07-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('cdd91765-8471-4f14-a708-64a15123ee38','July - August 2019',NULL,'2019-07-01T00:00:00.000+03:00','2019-08-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('fbbb45e5-069b-4938-8d9b-eff7d659eea1','Sep 2019',NULL,'2019-09-01T00:00:00.000+03:00','2019-09-30T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('6d903e0c-16e0-422c-b6d8-ecc00067f3a0','Sep - Oct 2019',NULL,'2019-09-01T00:00:00.000+03:00','2019-10-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('d5d1a17d-f52e-45c8-b8ea-953611b5216b','Nov 2019',NULL,'2019-11-01T00:00:00.000+03:00','2019-11-30T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('e5b04c19-cd68-45a1-a695-c8c6950f9773','Nov - Dec 2019',NULL,'2019-11-01T00:00:00.000+03:00','2019-12-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupA')),
    ('fa3fc1a1-72eb-40e5-95ba-c56bbd7c2036','Dec - Jan 2019',NULL,'2018-12-01T00:00:00.000+03:00','2019-01-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('9cf0e924-fd69-47b2-8adc-7e6a0839e576','Feb 2019',NULL,'2019-02-01T00:00:00.000+03:00','2019-02-28T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('41c668fd-3a48-4472-8c20-bd1570494104','Feb - March 2019',NULL,'2019-02-01T00:00:00.000+03:00','2019-03-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('eff7fe10-1a2a-4092-81cd-07755f530475','April 2019',NULL,'2019-04-01T00:00:00.000+03:00','2019-04-30T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('948810b0-eb80-417a-9e6c-e14cf372ce5e','April - May 2019',NULL,'2019-04-01T00:00:00.000+03:00','2019-05-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('2ca220da-93fa-4036-a54f-314d03d81a68','June 2019',NULL,'2019-06-01T00:00:00.000+03:00','2019-06-30T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('2c3b5a06-1a13-4a72-9222-c3e87fbe3a58','June - July 2019',NULL,'2019-06-01T00:00:00.000+03:00','2019-07-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('a9f795a3-0e3b-45d8-b6c3-90d6c3ea88de','Aug 2019',NULL,'2019-08-01T00:00:00.000+03:00','2019-08-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('e72f8b82-fb3c-4117-af0f-98f2e8070be0','Aug - Sept 2019',NULL,'2019-08-01T00:00:00.000+03:00','2019-09-30T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('21e04a55-7a28-41df-b391-01892bf8414f','Oct 2019',NULL,'2019-10-01T00:00:00.000+03:00','2019-10-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('be13048b-d2cc-4a25-bd45-f44f5480acdc','Oct - Nov 2019',NULL,'2019-10-01T00:00:00.000+03:00','2019-11-30T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB')),
    ('10c9b4d8-3952-4d1d-9300-741b27cadef6','Dec 2019',NULL,'2019-12-01T00:00:00.000+03:00','2019-12-31T23:59:59.000+03:00',(SELECT id from referencedata.processing_schedules WHERE code = 'groupB'));







INSERT into referencedata.requisition_groups (id, code ,name,description,supervisoryNodeId )values
('3f1a2773-0a66-4501-9782-3a1d1cab04b3', 'DARE-RGILS15A','Morogoro MC - ILS - A','Morogoro MC Requisition Group A - ILS',(select id from referencedata.supervisory_nodes where code='MWAN-SNZ'));

INSERT into referencedata.requisition_group_members ( requisitionGroupId ,facilityId )values
((select id from referencedata. requisition_groups where code ='DARE-RGILS15A'),(select id from referencedata. facilities where code ='MZ520495')),
((select id from referencedata. requisition_groups where code ='DARE-RGILS15A'),(select id from referencedata. facilities where code ='MZ510054'));



insert into referencedata.requisition_group_program_schedules (id, requisitionGroupId , programId , processingscheduleId , directDelivery ) values
('61e63623-ac95-469f-b3ed-facae9d0cd08', (select id from referencedata.requisition_groups where code='DARE-RGILS15A'),(select id from referencedata.programs where code='ilshosp'),(select id from referencedata.processing_schedules where code='groupA'),TRUE);





insert into requisition.requisition_templates (id, name, archived, populatestockonhandfromstockcards) values
('a2ade107-dcac-4e34-ae53-1dcfc6f39d5d', 'ILS redesigned template', false, false);

insert into requisition.requisition_template_assignments (id, templateid, programid, facilityTypeId) values
('74b81822-5649-4834-8f2f-004d8e3ce4ee', (select id from requisition.requisition_templates where name='ILS redesigned template'), (select id from referencedata.programs where code='ilshosp'), (select id from referencedata.facility_types where code ='disp'));



insert into requisition.columns_maps
(requisitioncolumnid, key, name, requisitioncolumnoptionid, requisitiontemplateid, isdisplayed, source, displayOrder, label)
values
    ((select id from requisition.available_requisition_columns where name = 'skipped'),'skipped','skipped','488e6882-563d-4b69-b7eb-fd59e7772a41',(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,1,'Skip'),
    ((select id from requisition.available_requisition_columns where name = 'orderable.productCode'),'orderable.productCode','orderable.productCode',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',2,2,'Namba mpya ya MSD'),
    ((select id from requisition.available_requisition_columns where name = 'orderable.fullProductName'),'orderable.fullProductName','orderable.fullProductName',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',2,3,'Maelezo ya bidhaa'),
    ((select id from requisition.available_requisition_columns where name = 'orderable.dispensable.displayUnit'),'orderable.dispensable.displayUnit','orderable.dispensable.displayUnit',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',2,4,'Kipimo cha Ugavi (U)'),
    ((select id from requisition.available_requisition_columns where name = 'beginningBalance'),'beginningBalance','beginningBalance',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,5,'Kiasi cha kuanzia (A)'),
    ((select id from requisition.available_requisition_columns where name = 'totalReceivedQuantity'),'totalReceivedQuantity','totalReceivedQuantity',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,6,'Kiasi kilichopokelewa (B)'),
    ((select id from requisition.available_requisition_columns where name = 'totalLossesAndAdjustments'),'totalLossesAndAdjustments','totalLossesAndAdjustments',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,7,'Upotevu au Marekebisho (C)'),
    ((select id from requisition.available_requisition_columns where name = 'totalStockoutDays'),'totalStockoutDays','totalStockoutDays',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,8,'Siku Ambazo Dawa Haikuwepo Kituoni (X)'),
    ((select id from requisition.available_requisition_columns where name = 'stockOnHand'),'stockOnHand','stockOnHand',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,9,'Salio la mwisho (D)'),
    ((select id from requisition.available_requisition_columns where name = 'totalConsumedQuantity'),'totalConsumedQuantity','totalConsumedQuantity',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,10,'Matumizi halisi (E)'),
    ((select id from requisition.available_requisition_columns where name = 'adjustedConsumption'),'adjustedConsumption','adjustedConsumption',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',1,11,'Makadirio ya matumizi baada ya marekebisho [E* 60]/]60-X] (Z)'),
    ((select id from requisition.available_requisition_columns where name = 'maximumStockQuantity'),'maximumStockQuantity','maximumStockQuantity','ff2b350c-37f2-4801-b21e-27ca12c12b3c',(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',1,12,'Kiasi cha juu kinachohitajika [Z x 2] (Y)'),
    ((select id from requisition.available_requisition_columns where name = 'calculatedOrderQuantity'),'calculatedOrderQuantity','calculatedOrderQuantity',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',1,13,'Kiasi kinachohitajika kilichokokotolewa [Y - D] (F)'),
    ((select id from requisition.available_requisition_columns where name = 'requestedQuantity'),'requestedQuantity','requestedQuantity',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,14,'Kiasi halisi kinachohitajika (G)'),
    ((select id from requisition.available_requisition_columns where name = 'packsToShip'),'packsToShip','packsToShip','dcf41f06-3000-4af6-acf5-5de4fffc966f',(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',1,15,'Kiasi cha kuagizwa [K/U] (H)'),
    ((select id from requisition.available_requisition_columns where name = 'remarks'),'remarks','remarks',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,16,'Maelezo ya kiasi kilichoidhinishwa'),
    ((select id from requisition.available_requisition_columns where name = 'requestedQuantityExplanation'),'requestedQuantityExplanation','requestedQuantityExplanation',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,17,'Maelezo ya kiasi halisi kinachohitajika'),
    ((select id from requisition.available_requisition_columns where name = 'pricePerPack'),'pricePerPack','pricePerPack',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',2,18,'Bei (I)'),
    ((select id from requisition.available_requisition_columns where name = 'totalCost'),'totalCost','totalCost',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',1,19,'Gharama [H x I] (J)'),
    ((select id from requisition.available_requisition_columns where name = 'approvedQuantity'),'approvedQuantity','approvedQuantity',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'true',0,20,'Kiasi kilichoindinishwa (K)'),
    ((select id from requisition.available_requisition_columns where name = 'total'),'total','total',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'false',1,22,'Total'),
    ((select id from requisition.available_requisition_columns where name = 'averageConsumption'),'averageConsumption','averageConsumption',NULL,(select id from requisition.requisition_templates where name='ILS redesigned template'),'false',1,23,'Average Monthly Consumption(AMC)'),
    ((select id from requisition.available_requisition_columns where name = 'numberOfNewPatientsAdded'),'numberOfNewPatientsAdded','numberOfNewPatientsAdded',(select id from requisition.available_requisition_column_options where optionname = 'newPatientCount'),(select id from requisition.requisition_templates where name='ILS redesigned template'),'false',0,24,'Total number of new patients added to service on the program')
;

insert into stockmanagement.stock_card_line_item_reasons(id,name,description,reasoncategory,reasontype,isfreetextallowed) values
('e3fc3cf3-da18-44b0-a220-77c985202e06','Transfer In','Transfer In','TRANSFER','CREDIT',false),
('c1fc3cf3-da18-44b0-a220-77c985202e06','Transfer Out','Transfer Out','TRANSFER','DEBIT',true),
('279d55bd-42e3-438c-a63d-9c021b185dae','Facility return','Facility return','ADJUSTMENT','CREDIT',false),
('5b09202e-b8a7-4f77-9e0e-8156f8efc613','Damage','Damage','ADJUSTMENT','DEBIT',false),
('3e7940db-49a5-492f-a4eb-75acb981dd2b','Expired','Expired','ADJUSTMENT','DEBIT',false),
('09e1d05d-f736-4834-9346-c27196a99eae','Stolen','Stolen','ADJUSTMENT','DEBIT',false),
('b7e99f5b-af04-433d-9c30-d4f90c60c47b','Lost','Lost','ADJUSTMENT','DEBIT',true),
('30f6002d-a78d-44db-b2b9-46e76795a188','Passed open-vial time limit','Passed open-vial time limit','ADJUSTMENT','DEBIT',false),
('6f8d0431-6ec5-4280-9019-c6024b168b23','Cold chain failure','Cold chain failure','ADJUSTMENT','DEBIT',false);


insert into auth.auth_users (id,enabled,password,username) values
((select id from referencedata.users where username = 'StoreInCharge'),TRUE,'$2a$10$4IZfidcJzbR5Krvj87ZJdOZvuQoD/kvPAJe549rUNoP3N3uH0Lq2G','StoreInCharge'),
((select id from referencedata.users where username = 'FacilityInCharge'),TRUE,'$2a$10$4IZfidcJzbR5Krvj87ZJdOZvuQoD/kvPAJe549rUNoP3N3uH0Lq2G','FacilityInCharge'),
((select id from referencedata.users where username = 'lmu'),TRUE,'$2a$10$4IZfidcJzbR5Krvj87ZJdOZvuQoD/kvPAJe549rUNoP3N3uH0Lq2G','lmu'),
((select id from referencedata.users where username = 'Admin123'),TRUE,'$2a$10$4IZfidcJzbR5Krvj87ZJdOZvuQoD/kvPAJe549rUNoP3N3uH0Lq2G','Admin123');

insert into notification.user_contact_details (referenceDataUserId,phoneNumber,email,allowNotify,emailVerified) values
((select id from referencedata.users where username = 'StoreInCharge'),NULL,'Fatima_Doe@openlmis.com','false','true'),
((select id from referencedata.users where username = 'FacilityInCharge'),NULL,'Jane_Doe@openlmis.com','false','true'),
((select id from referencedata.users where username = 'lmu'),NULL,'Frank_Doe@openlmis.com','false','true'),
((select id from referencedata.users where username = 'Admin123'),NULL,'John_Doe@openlmis.com','false','true');




insert into  stockmanagement.valid_reason_assignments (id,programId,facilityTypeId,reasonId,hidden) VALUES
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='disp'),'b5c27da7-bdda-4790-925a-9484c5dfb594', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='heac'),'b5c27da7-bdda-4790-925a-9484c5dfb594', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='ddho'),'b5c27da7-bdda-4790-925a-9484c5dfb594', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='disp'),'313f2f5f-0c22-4626-8c49-3554ef763de3', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='heac'),'313f2f5f-0c22-4626-8c49-3554ef763de3', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='ddho'),'313f2f5f-0c22-4626-8c49-3554ef763de3', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='disp'),'84eb13c3-3e54-4687-8a5f-a9f20dcd0dac', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='heac'),'84eb13c3-3e54-4687-8a5f-a9f20dcd0dac', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='ddho'),'84eb13c3-3e54-4687-8a5f-a9f20dcd0dac', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='disp'),'f8bb41e2-ab43-4781-ae7a-7bf3b5116b82', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='heac'),'f8bb41e2-ab43-4781-ae7a-7bf3b5116b82', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='ddho'),'f8bb41e2-ab43-4781-ae7a-7bf3b5116b82', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='disp'),'9b4b653a-f319-4a1b-bb80-8d6b4dd6cc12', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='heac'),'9b4b653a-f319-4a1b-bb80-8d6b4dd6cc12', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='ddho'),'9b4b653a-f319-4a1b-bb80-8d6b4dd6cc12', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='disp'),'0676fdea-9ba8-4e6d-ae26-bb14f0dcfecd', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='heac'),'0676fdea-9ba8-4e6d-ae26-bb14f0dcfecd', true ),
(uuid_generate_v4(), (select id from referencedata.programs  where code='ilshosp'), (select id from referencedata.facility_types where code='ddho'),'0676fdea-9ba8-4e6d-ae26-bb14f0dcfecd', true );


