-- === PART 1/2: Change tx_cycles to float  ================================================
ALTER TABLE `patient_treatment`
	CHANGE COLUMN `tx_cycles` `tx_cycles` FLOAT NULL DEFAULT NULL AFTER `end_date`;
	
-- === PART 2/2: Split 'scans' field ========================================================

-- Add 'mri' to lymph section ------------------------------------
INSERT INTO category_parameter(category_id, parameter_id)
VALUES ( (SELECT id FROM categories WHERE category='lymphoid'),
			(SELECT id FROM parameters WHERE param_name='mri') 
		 );


-- Add 'pet' parameter -------------------------------------------
INSERT INTO parameters 
    (param_name, field_type, is_active)
VALUES
	('pet', 'menu', 'yes');
	
-- Associate 'pet' parameter with menu items Y, N and U ----------
SELECT @pet_id_for_menu := id FROM parameters WHERE param_name = 'pet';  -- get id for 'pet'
INSERT INTO parameter_menu_item
	(param_id, item_id)
VALUES	
	( @pet_id_for_menu, (SELECT item_id FROM menu_items WHERE item_value='Y')),
	( @pet_id_for_menu, (SELECT item_id FROM menu_items WHERE item_value='N')),
	( @pet_id_for_menu, (SELECT item_id FROM menu_items WHERE item_value='U'));

-- Add 'pet' to lymph section ------------------------------------
INSERT INTO category_parameter(category_id, parameter_id)
VALUES ( (SELECT id FROM categories WHERE category='lymphoid'),
			(SELECT id FROM parameters WHERE param_name='pet') 
		 );

-- Remove 'scans' (48) from lymph section as a field --------------
DELETE FROM category_parameter
WHERE 
	category_id=(SELECT id FROM categories WHERE category='lymphoid')
	AND
	parameter_id=(SELECT id FROM parameters where param_name='scans');