create table temp_radiology_names (
  id INT not null auto_increment primary key,
  loinc_map varchar(10),
  name varchar(50),
  concept_id int
);


insert into temp_radiology_names
  (loinc_map, name)
values
("36550-2","Abdomen 1 view (XRay)"),
("37085-8","Abdomen AP (supine and lateraldecubitus) (XRay)"),
("26136-2","Acromioclavicular joints bilateral (XRay)"),
("26386-3","Ankle left 2 views (XRay)"),
("26387-1","Ankle right 2 views (XRay)"),
("36662-5","Calcaneus left 2 views (XRay)"),
("37718-4","Calcaneus right 2 views (XRay)"),
("36554-4","Chest 1 view (XRay)"),
("36643-5","Chest 2 views (XRay)"),
("43467-0","Chest 2 views and right oblique and left oblique (XRay)"),
("41792-3","Chest bilateral oblique (XRay)"),
("42009-1","Chest 2 views and apical (XRay)"),
("24640-5","Chest lordotic (XRay)"),
("36320-0","Chest 4 views (XRay)"),
("36646-8","Clavicle left 2 views (XRay)"),
("37679-8","Clavicle right 2 views (XRay)"),
("43539-6","Spine Cervical 2 or 3 views (XRay)"),
("36650-0","Elbow left 2 views (XRay)"),
("37681-4","Elbow right 2 views (XRay)"),
("36297-0","Facial bones 3 views (XRay)"),
("36654-2","Femur left 2 views (XRay)"),
("37690-5","Femur right 2 views (XRay)"),
("38846-2","Foot left 2 views (XRay)"),
("37697-0","Foot right 2 views (XRay)"),
("36660-9","Forearm left 2 views (XRay)"),
("37707-7","Forearm right 2 views (XRay)"),
("26389-7","Hand left 2 views (XRay)"),
("26390-5","Hand right 2 views (XRay)"),
("36664-1","Hip left 2 views (XRay)"),
("37721-8","Hip right 2 views (XRay)"),
("26392-1","Humerus left 2 views (XRay)"),
("26393-9","Humerus right 2 views (XRay)"),
("47373-6","Knee left 1 or 2 views (XRay) "),
("47375-1","Knee right 1 or 2 views (XRay)"),
("48469-1","Spine Lumbar 2 or 3 views (XRay)"),
("24828-6","Mandible panorex (XRay)"),
("37616-0","Pelvis 1 view (XRay)"),
("37066-8","Ribs left 2 views (XRay)"),
("37780-4","Ribs right 2 views (XRay)"),
("36668-2","Shoulder left 2 views (XRay)"),
("37793-7","Shoulder right 2 views (XRay)"),
("37854-7","Sinuses 3 views (XRay)"),
("37868-7","Skull 4 views (XRay)"),
("37875-2","Spine 1 view (XRay)"),
("24984-7","Spine Thoracic and Lumbar 2 views (XRay)"),
("36672-4","Lower leg left 2 views (XRay)"),
("37815-8","Lower leg right 2 views (XRay)"),
("37905-7","Spine Thoracic 2 views (XRay)"),
("38105-3","Kidney retrograde pyelogram (XRay)"),
("37483-5","Wrist left 2 views (XRay)"),
("37826-5","Wrist right 2 views (XRay)"),
("36424-0","Abdomen without contrast (CT)"),
("29252-4","Chest without contrast (CT)"),
("30592-0","Spine Cervical without contrast (CT)"),
("30799-1","Head without contrast (CT)"),
("30620-9","Spine Lumbar without contrast (CT)"),
("36452-1","Lower extremity left without contrast (CT)"),
("36454-7","Lower extremity right without contrast (CT)"),
("30802-3","Facial bones and Maxilla without contrast (CT)"),
("30616-7","Pelvis with and without contrast IV (CT)"),
("36169-1","Upper extremity left with contrast IV (CT)"),
("36170-9","Upper extremity right with contrast IV (CT)"),
("36457-0","Upper extremity left without contrast (CT)"),
("36458-8","Upper extremity right without contrast (CT)"),
("36449-7","Lower extremity bilateral without contrast (CT)"),
("36952-0","Abdomen and Pelvis without contrast (CT)"),
("24558-9","Abdomen (US)"),
("30704-1","Abdomen limited (US)"),
("26216-2","Breast right (US)"),
("26215-4","Breast left (US)"),
("24630-6","Chest (US)"),
("62494-0","US Guidance for percutaneous drainage of Cavity"),
("30703-3","US Guidance for aspiration of Pericardial space"),
("44164-2","Head and Neck (US)"),
("24559-7","Guided paracentensis (US)"),
("24869-0","Pelvis (US)"),
("38046-9","Pelvis limited (US)"),
("24531-6","Abdomen retroperitoneum (US)"),
("38047-7","Abdomen retroperitoneum limited (US)"),
("25002-7","Scrotum and Testicle (US)"),
("24677-7","Pelvis transvaginal (US)"),
("24693-4","Extremity (US)"),
("48692-8","Lower extremity vein left (US)"),
("48691-0","Lower extremity vein right (US)"),
("46364-6","Lower extremity vein bilateral (US)");

delimiter $$

CREATE PROCEDURE addLOINC()
    BEGIN
       DECLARE i INT DEFAULT 1;
   
          WHILE (i<=85) DO
            insert into concept
               (datatype_id,class_id,creator,date_created,uuid )
            values 
               (4,19,7,now(),uuid());

            update temp_radiology_names 
              set concept_id = (select max(concept_id) from concept)
              where id = i;

            insert into concept_name
                (concept_id,name,
                 locale,creator,date_created,uuid,
                 concept_name_type,locale_preferred)
            values
                ((select concept_id from temp_radiology_names where id = i),
                 (select name from temp_radiology_names where id = i),
                 'en', 7, now(), uuid(),
                 'FULLY_SPECIFIED', 1);

            -- Add reference term
            insert into concept_reference_term
                (concept_source_id,code,creator,date_created,uuid )
            values 
                (22,(select loinc_map from temp_radiology_names where id = i),
                 7,now(),uuid());  

            -- Add SAME-AS map for reference map
            insert into concept_reference_map
                (concept_id,
                 concept_reference_term_id,
                 concept_map_type_id,creator,date_created,uuid)
              values 
                ((select concept_id from temp_radiology_names where id = i),
                 (select max(concept_reference_term_id) from concept_reference_term),
                 1,7,now(),uuid()); 

            insert into concept_set
               (concept_set,concept_id,creator,date_created,uuid)
              values
               (7709,
                (select concept_id from temp_radiology_names where id = i),
                7,now(),uuid());

            SET i=i+1;
         END WHILE;

    END$$

delimiter ;

call addLOINC();

drop procedure addLOINC;

-- Generate concept indexing

delimiter $$

CREATE PROCEDURE addPIHMaps()
    BEGIN
       DECLARE i INT DEFAULT 1;
   
          WHILE (i<=85) DO

            -- Add reference term with PIH: concept_id
            insert into concept_reference_term
                (concept_source_id,code,creator,date_created,uuid )
            values 
                (5,(select concept_id from temp_radiology_names where id = i),
                 7,now(),uuid());  

            -- Add SAME-AS map for reference map
            insert into concept_reference_map
                (concept_id,
                 concept_reference_term_id,
                 concept_map_type_id,creator,date_created,uuid)
              values 
                ((select concept_id from temp_radiology_names where id = i),
                 (select max(concept_reference_term_id) from concept_reference_term),
                 1,7,now(),uuid()); 

            -- Add reference term with PIH: concept_name
            insert into concept_reference_term
                (concept_source_id,code,creator,date_created,uuid )
            values 
                (5,(select name from temp_radiology_names where id = i),
                 7,now(),uuid());  

            -- Add SAME-AS map for reference map
            insert into concept_reference_map
                (concept_id,
                 concept_reference_term_id,
                 concept_map_type_id,creator,date_created,uuid)
              values 
                ((select concept_id from temp_radiology_names where id = i),
                 (select max(concept_reference_term_id) from concept_reference_term),
                 1,7,now(),uuid()); 

            SET i=i+1;
         END WHILE;

    END$$

delimiter ;

call addPIHMaps();

drop procedure addPIHMaps;


