/* new MPLA lab-section deployment */

/* new lab-section */
INSERT INTO lab_sections( section_name, has_result_summary, has_section_notes, has_test_sign_out, 
	has_results_import, has_foreign_id, has_labels, auto_expand )
VALUES( 'Multiplex Ligation-dependent Probe Amplification', 'yes', 'no', 'no', 'no', 'no', 'no', 'no' );

/* get new lab-section id: */
SELECT @mpla_section_id := id FROM lab_sections WHERE section_name LIKE 'Multiplex Ligation%';

/* set default lab_section_sample_types (all 3): */
INSERT INTO lab_section_sample_type( lab_section_id, sample_type_id )
VALUES 
	( @mpla_section_id, 1 ), 
	( @mpla_section_id, 2 ), 
	( @mpla_section_id, 3 );

/* new data type entry 'mpla': */
INSERT INTO lab_test_result_data_types( description ) VALUES ( 'mlpa' );
/* get new lab_test_result_data_types id: */
SELECT @data_type_id := id FROM lab_test_result_data_types WHERE description = 'mlpa';

/* new result options for MPLA tests: */
INSERT INTO lab_test_result_options( data_type_id, value )
VALUES 
	( @data_type_id, 'Gain' ),
	( @data_type_id, 'Loss' ),
	( @data_type_id, 'Suspicious' );

/* for new Molecular section lab-test 'MLPA quantification': */
SELECT @molecular_section_id := id FROM lab_sections WHERE section_name = 'Molecular';

/* new MPLA section lab-tests: */
INSERT INTO lab_tests
	(test_name,field_label,lab_section_id,test_type,has_results)
VALUES
	('mpla_myeloma', 			'MLPA myeloma', 			@mpla_section_id, 'panel', 'no'),
    ('1p12_fam46c',    			'1p12 (FAM46C)',   			@mpla_section_id, 'test', 'yes'),
    ('1p21_1_col11a1', 			'1p21.1 (COL11A1)', 		@mpla_section_id, 'test', 'yes'),
	('1p21_3_dpyd',				'1p21.3 (DPYD)',			@mpla_section_id, 'test', 'yes'),
	('1p31_3_rpe65',			'1p31.3 (RPE65)',			@mpla_section_id, 'test', 'yes'),
	('1p31_3_lepr_5',			'1p31.3 (LEPR-5)',			@mpla_section_id, 'test', 'yes'),
	('1p32_1_dab1',				'1p32.1 (DAB1)',			@mpla_section_id, 'test', 'yes'),
	('1p32_2_ppap2b',			'1p32.2 (PPAP2B)',			@mpla_section_id, 'test', 'yes'),
	('1p32_3_cdkn2c',			'1p32.3 (CDKN2C)',			@mpla_section_id, 'test', 'yes'),
	('1p32_3_faf1',				'1p32.3 (FAF1)',			@mpla_section_id, 'test', 'yes'),
	('1q21_3_cks1b1',			'1q21.3 (CKS1B1)',			@mpla_section_id, 'test', 'yes'),
	('1q23_3_nuf2',				'1q23.3 (NUF2)',			@mpla_section_id, 'test', 'yes'),
	('1q23_3_pbx1',				'1q23.3 (PBX1)',			@mpla_section_id, 'test', 'yes'),
	('1q23_3_rp11_541J2',		'1q23.3 (RP11-541J2)',		@mpla_section_id, 'test', 'yes'),
	('1q23_3_rp11_480n10',		'1q23.3 (RP11-480N10)',		@mpla_section_id, 'test', 'yes'),
	('5q31_3_pcdha1',			'5q31.3 (PCDHA1)',			@mpla_section_id, 'test', 'yes'),
	('5q31_3_pcdhac1',			'5q31.3 (PCDHAC1)',			@mpla_section_id, 'test', 'yes'),
	('5q31_3_pcdhb2',			'5q31.3 (PCDHB2)',			@mpla_section_id, 'test', 'yes'),
	('5q31_3_pcdhb10',			'5q31.3 (PCDHB10)',			@mpla_section_id, 'test', 'yes'),
	('5q31_3_slc25a2',			'5q31.3 (SLC25A2)',			@mpla_section_id, 'test', 'yes'),
	('5q31_3_pcdhga11',			'5q31.3 (PCDHGA11)',		@mpla_section_id, 'test', 'yes'),
	('9p24_1_jak2',				'9p24.1 (JAK2)',			@mpla_section_id, 'test', 'yes'),
	('9q34_3_col5a1',			'9q34.3 (COL5A1)',			@mpla_section_id, 'test', 'yes'),
	('12p13_31_cd27',			'12p13.31 (CD27)',			@mpla_section_id, 'test', 'yes'),
	('12p13_31_vamp1',			'12p13.31 (VAMP1)',			@mpla_section_id, 'test', 'yes'),
	('12p13_31_ncapd2',			'12p13.31 (NCAPD2)',		@mpla_section_id, 'test', 'yes'),
	('12p13_31_chd4',			'12p13.31 (CHD4)',			@mpla_section_id, 'test', 'yes'),
	('13q14_2_rb1',				'13q14.2 (RB1)',			@mpla_section_id, 'test', 'yes'),
	('13q14_2_dleu2_intr_1',	'13q14.2 (DLEU2-intr 1)',	@mpla_section_id, 'test', 'yes'),
	('13q22_1_dis3',			'13q22.1 (DIS3)',			@mpla_section_id, 'test', 'yes'),
	('14q32_32_traf3',			'14q32.32 (TRAF3)',			@mpla_section_id, 'test', 'yes'),
	('15q12_gabrb3',			'15q12 (GABRB3)',			@mpla_section_id, 'test', 'yes'),
	('15q26_3_igf1r',			'15q26.3 (IGF1R)',			@mpla_section_id, 'test', 'yes'),
	('16q12_1_cyld',			'16q12.1 (CYLD)',			@mpla_section_id, 'test', 'yes'),
	('16q23_1_wwox',			'16q23.1 (WWOX)',			@mpla_section_id, 'test', 'yes'),
	('17p13_1_tp53',			'17p13.1 (TP53)',			@mpla_section_id, 'test', 'yes'),
    
	('mpla_quantification',	    'MLPA quantification',		@molecular_section_id, 'test', 'no');

/* register all 3 sample types for all MPLA lab-tests: */
INSERT INTO lab_test_sample_type( lab_test_id, sample_type_id )
	SELECT id, 1 FROM lab_tests WHERE lab_section_id = @mpla_section_id;
INSERT INTO lab_test_sample_type( lab_test_id, sample_type_id )
	SELECT id, 2 FROM lab_tests WHERE lab_section_id = @mpla_section_id;
INSERT INTO lab_test_sample_type( lab_test_id, sample_type_id )
	SELECT id, 3 FROM lab_tests WHERE lab_section_id = @mpla_section_id;

/* set data type = 'mpla' for all MPLA lab-tests: */
INSERT INTO lab_test_data_type( lab_test_id, data_type_id )
	SELECT id, @data_type_id FROM lab_tests WHERE lab_section_id = @mpla_section_id;

/* link 'MPLA myeloma' to Molecular 'MPLA quaification': */
SELECT @parent_test_id := id FROM lab_tests WHERE test_name = 'mpla_myeloma';
SELECT @linked_test_id := id FROM lab_tests WHERE test_name = 'mpla_quantification';

INSERT INTO linked_lab_test(  parent_test_id,  linked_test_id )
VALUES (@parent_test_id, @linked_test_id )