SELECT @section_id := id FROM lab_sections WHERE section_name = 'Multiplex Ligation-dependent Probe Amplification';

INSERT INTO lab_tests( test_name, field_label, lab_section_id, test_type, has_results, is_active ) VALUES
	(LOWER('2p24_3_MYCN'), '2p24.3 (MYCN)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('2p16_1_REL'),  '2p16.1 (REL)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('6q21_AIM1'),  '6q21 (AIM1)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('6q21_SEC63'),  '6q21 (SEC63)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('6q23_3_TNFAIP3'),  '6q23.3 (TNFAIP3)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('6q25_1_LATS1'),  '6q25.1 (LATS1)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('6q25_3_IGF2R'),  '6q25.3 (IGF2R)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('6q26_PARK2'),  '6q26 (PARK2)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('8p21_3_TNFRSF10B'),  '8p21.3 (TNFRSF10B)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('8p21_3_TNFRSF10A'),  '8p21.3 (TNFRSF10A)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('8q24_11_EIF3H'),  '8q24.11 (EIF3H)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('8q24_21_MYC'),  '8q24.21 (MYC)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('9p21_3_CDKN2A'),  '9p21.3 (CDKN2A)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('9p21_3_CDKN2B'),  '9p21.3 (CDKN2B)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('10q23_31_PTEN'),  '10q23.31 (PTEN)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('11q22_3_ATM'),  '11q22.3 (ATM)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('11q22_3_RDX'),  '11q22.3 (RDX)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('11q23_1_PPP2R1B'),  '11q23.1 (PPP2R1B)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('11q23_3_CADM1'),  '11q23.3 (CADM1)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('12p13_32_CCND2'),  '12p13.32 (CCND2)', @section_id, 'test', 'yes', 'yes'),
#	(LOWER('12p13_31_CD27'),  '12p13.31 (CD27)', @section_id, 'test', 'yes', 'yes'), # already exists
	(LOWER('12p12_1_LRMP'),  '12p12.1 (LRMP)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('12p13_3_STAT6'),  '12p13.3 (STAT6)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('12q14_1_CDK4'),  '12q14.1 (CDK4)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('12q14_3_HMGA2'),  '12q14.3 (HMGA2)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('12q15_IFNG'),  '12q15 (IFNG)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('12q23_2_IGF1'),  '12q23.2 (IGF1)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('12q23_2_PAH'),  '12q23.2 (PAH)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('12q24_33_CHFR'),  '12q24.33 (CHFR)', @section_id, 'test', 'yes', 'yes'),
#	(LOWER('13q14_2_RB1'),  '13q14.2 (RB1)', @section_id, 'test', 'yes', 'yes'), # already exists
	(LOWER('13q14_2_FNDC3A'),  '13q14.2 (FNDC3A)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('13q14_2_DLEU2'),  '13q14.2 (DLEU2)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('13q14_2_KCNRG'),  '13q14.2 (KCNRG)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('13q14_2_MIR15A'),  '13q14.2 (MIR15A)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('13q14_2_DLEU1'),  '13q14.2 (DLEU1)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('13q14_3_DLEU7'),  '13q14.3 (DLEU7)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('13q14_3_ATP7B'),  '13q14.3 (ATP7B)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('14q32_33_AKT1'),  '14q32.33 (AKT1)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('14q32_33_MTA1'),  '14q32.33 (MTA1)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('14q32_33_KIAA0125'),  '14q32.33 (KIAA0125)', @section_id, 'test', 'yes', 'yes'),
#	(LOWER('17p13_1_TP53'),  '17p13.1 (TP53)', @section_id, 'test', 'yes', 'yes'), # already exists
	(LOWER('19p13_2_CDKN2D'),  '19p13.2 (CDKN2D)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('19p13_2_LDLR'),  '19p13.2 (LDLR)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('19q12_CCNE1'),  '19q12 (CCNE1)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('19q13_2_AKT2'),  '19q13.2 (AKT2)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('19q13_42_MIR498'),  '19q13.42 (MIR498)', @section_id, 'test', 'yes', 'yes'),
	(LOWER('SF3B1_K700E'),  'SF3B1 K700E', @section_id, 'test', 'yes', 'yes'),
	(LOWER('MYD88_L265P'),  'MYD88 L265P', @section_id, 'test', 'yes', 'yes'),
	(LOWER('NOTCH1_P2514_fs'),  'NOTCH1 P2514*fs', @section_id, 'test', 'yes', 'yes');

DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp(id INT);
INSERT INTO temp
	SELECT id FROM lab_tests WHERE test_name IN (
		'2p24_3_mycn',
		'2p16_1_rel',
		'6q21_aim1',
		'6q21_sec63',
		'6q23_3_tnfaip3',
		'6q25_1_lats1',
		'6q25_3_igf2r',
		'6q26_park2',
		'8p21_3_tnfrsf10b',
		'8p21_3_tnfrsf10a',
		'8q24_11_eif3h',
		'8q24_21_myc',
		'9p21_3_cdkn2a',
		'9p21_3_cdkn2b',
		'10q23_31_pten',
		'11q22_3_atm',
		'11q22_3_rdx',
		'11q23_1_ppp2r1b',
		'11q23_3_cadm1',
		'12p13_32_ccnd2',
		'12p12_1_lrmp',
		'12p13_3_stat6',
		'12q14_1_cdk4',
		'12q14_3_hmga2',
		'12q15_ifng',
		'12q23_2_igf1',
		'12q23_2_pah',
		'12q24_33_chfr',
		'13q14_2_fndc3a',
		'13q14_2_dleu2',
		'13q14_2_kcnrg',
		'13q14_2_mir15a',
		'13q14_2_dleu1',
		'13q14_3_dleu7',
		'13q14_3_atp7b',
		'14q32_33_akt1',
		'14q32_33_mta1',
		'14q32_33_kiaa0125',
		'19p13_2_cdkn2d',
		'19p13_2_ldlr',
		'19q12_ccne1',
		'19q13_2_akt2',
		'19q13_42_mir498'
) AND lab_section_id = @section_id;	

INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)  
	SELECT id, 1 FROM temp;
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)  
	SELECT id, 2 FROM temp;
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)  
	SELECT id, 3 FROM temp;

SELECT @type_id := id FROM lab_test_result_data_types WHERE description = 'mlpa';
INSERT INTO lab_test_data_type(lab_test_id, data_type_id, is_active) 
	SELECT id, @type_id, 'yes' FROM temp;
	
# new data type for last 3 lab-tests:
INSERT INTO lab_test_result_data_types(description,is_active) VALUES ('mlpa_alt', 'yes');
SELECT @type_id := id FROM lab_test_result_data_types WHERE description = 'mlpa_alt';

INSERT INTO lab_test_result_options(data_type_id,`value`,is_active) VALUES
	(@type_id, 'mutated', 'yes'),
	(@type_id, 'normal or wild-type', 'yes'),
	(@type_id, 'suspicious', 'yes');

DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp(id INT);
INSERT INTO temp
	SELECT id FROM lab_tests WHERE test_name IN (
		'sf3b1_k700e',
		'myd88_l265p',
		'notch1_p2514_fs'
	);
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)  
	SELECT id, 1 FROM temp;
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)  
	SELECT id, 2 FROM temp;
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)  
	SELECT id, 3 FROM temp;
INSERT INTO lab_test_data_type(lab_test_id, data_type_id, is_active) 
	SELECT id, @type_id, 'yes' FROM temp;