-- March 2019 changes for HRMN  - James Doughty
-- Add field to patient_treatment table:

ALTER TABLE patient_treatment
ADD COLUMN `tx_cycles` SMALLINT(6) NULL DEFAULT NULL AFTER `end_date`;


-- =====================================================================================================
-- Add Scans parameter and data ---- **** DO ONLY ONCE *******
-- =====================================================================================================

-- -- 1. DONE ON JAMES PC - Add new parameter "scans" ------------------------------------------
INSERT INTO parameters (param_name, field_type, is_active)
VALUES ('scans', 'menu', 'yes');

-- -- 2. DONE ON JAMES PC - Insert required menu items -----------------------------------------
INSERT INTO menu_items (item_value)
VALUES 
	('CT'),
	('PET'),
	('MRI Only'),
	('CT and PET');

-- 3. DONE ON JAMES PC - update parameter_menu_item to associate values with menu items -------
SELECT @param_id := id  FROM parameters WHERE param_name = 'scans';
INSERT INTO parameter_menu_item (param_id, item_id)
	SELECT @param_id AS param_id, mi.item_id 
	FROM menu_items mi
	WHERE mi.item_value IN ('CT', 
							'PET', 
							'MRI Only', 
							'CT and PET',
							'none',
							'unknown');

-- 4. DONE ON JAMES PC - associate 'scans' parameter with lymphoid category --------------------
INSERT INTO category_parameter (category_id, parameter_id)
VALUES
	( (SELECT id FROM categories WHERE category = 'lymphoid'),
	  (SELECT id FROM parameters WHERE param_name = 'scans') );