/* new mlpa 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 @mlpa_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
	( @mlpa_section_id, 1 ),
	( @mlpa_section_id, 2 ),
	( @mlpa_section_id, 3 );

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

	('mlpa_quantification',	    'MLPA quantification',		@molecular_section_id, 'test', 'no');

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

/* set data type = 'mlpa' for all mlpa 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 = @mlpa_section_id;

/* link 'mlpa myeloma' to Molecular 'mlpa quantification': */
SELECT @mlpa_myeloma_id := id FROM lab_tests WHERE test_name = 'mlpa_myeloma';
SELECT @mlpa_quant_id := id FROM lab_tests WHERE test_name = 'mlpa_quantification';

INSERT INTO linked_lab_test( parent_test_id, linked_test_id )
VALUES (@mlpa_myeloma_id, @mlpa_quant_id);

/* register all 3 sample types for MLPA quantification: */
INSERT INTO lab_test_sample_type( lab_test_id, sample_type_id )
VALUES
    (@mlpa_quant_id, 1),
    (@mlpa_quant_id, 2),
    (@mlpa_quant_id, 3);

/* lab section status options */
SELECT @setup_id := id FROM lab_test_status_options WHERE description = 'setup';
SELECT @pcr_id := id FROM lab_test_status_options WHERE description = 'PCR';
SELECT @analyser_id := id FROM lab_test_status_options WHERE description = 'Analyser';
SELECT @prim_rep_id := id FROM lab_test_status_options WHERE description = 'Primary report';
INSERT INTO lab_section_status_option( lab_section_id, status_option_id, position)
VALUES
    (@mlpa_section_id, @setup_id,    1),
    (@mlpa_section_id, @pcr_id,      2),
    (@mlpa_section_id, @analyser_id, 3),
    (@mlpa_section_id, @prim_rep_id, 4);