/* =============================================================================
README: run genomics_cancer_v3_test.sql 1st to create temp tables to test     */
USE `genomics`;
/* ========================================================================== */

INSERT INTO lab_sections(section_name, has_test_sign_out)
  /* new v3.1.2 lab-section */
    VALUES ('Consent update', 'yes'), /* all rest of cols default vals */
  /* new v3.2 lab-sections */
    ('Specimens - saliva', 'yes'),
    ('DNA - saliva', 'yes'),
    ('Specimens - bone marrow', 'yes'),
    ('DNA - bone marrow', 'yes');

  /* map lab_section_sample_type lab-section => sample_type */
SELECT @sample_id := id FROM specimens WHERE sample_code = 'GEN'; /* only have 1 */
INSERT INTO lab_section_sample_type(lab_section_id, sample_type_id)
    SELECT
        (SELECT id FROM lab_sections WHERE section_name = 'Specimens - saliva'),
        (@sample_id);
INSERT INTO lab_section_sample_type(lab_section_id, sample_type_id)
    SELECT
        (SELECT id FROM lab_sections WHERE section_name = 'DNA - saliva'),
        (@sample_id);
INSERT INTO lab_section_sample_type(lab_section_id, sample_type_id)
    SELECT
        (SELECT id FROM lab_sections WHERE section_name = 'Specimens - bone marrow'),
        (@sample_id);
INSERT INTO lab_section_sample_type(lab_section_id, sample_type_id)
    SELECT
        (SELECT id FROM lab_sections WHERE section_name = 'DNA - bone marrow'),
        (@sample_id);

/* set lab-tests inactive */
UPDATE lab_tests SET is_active = 'no' WHERE test_name LIKE 'consent_q%';  /* consent_q1 .. consent_q14 */
UPDATE lab_tests SET is_active = 'no' WHERE test_name IN (
    'edta1_qc_type','edta1_qc_result','tumour_qc_result', 'tumour_qc_type',
    'number_of_biopsies','biopsy_gauge','tumour_qc_date'
);

/* change lab-section parent of lab-tests to 'Specimens - FFPE' */
SELECT @section_id := id FROM lab_sections WHERE section_name = 'Specimens - FFPE';
UPDATE lab_tests SET lab_section_id = @section_id
    WHERE test_name IN ('tumour_sample_type','number_of_sections','section_thickness','number_of_blocks' );
/* change lab-section parent of lab-tests to 'Specimens - tumour' */
SELECT @section_id := id FROM lab_sections WHERE section_name = 'Specimens - tumour';
UPDATE lab_tests SET lab_section_id = @section_id WHERE test_name = 'pre_invasive_elements';

/* new lab-tests */
SELECT @consent_id := id FROM lab_sections WHERE section_name = 'Consent';
INSERT INTO lab_tests (test_name, field_label, lab_section_id, test_type, has_results) VALUES
    /* Consent section */
    ('health_related_additional', 'Health related additional', @consent_id, 'test', 'yes'),
    ('reproductive_additional',   'Reproductive additional',   @consent_id, 'test', 'yes');

SELECT @consent_update_id := id FROM lab_sections WHERE section_name = 'Consent update';
INSERT INTO lab_tests (test_name, field_label, lab_section_id, test_type, has_results) VALUES
    ('consent_date_update',     'Consent date',              @consent_update_id, 'test', 'yes'),
    ('consent_form_update',     'Consent form version',      @consent_update_id, 'test', 'yes'),
    ('consent_given_update',    'Consent given',             @consent_update_id, 'test', 'yes'),
    ('info_sheet_update',       'Info sheet version',        @consent_update_id, 'test', 'yes'),
    ('update_health_related',   'Health related additional', @consent_update_id, 'test', 'yes'),
    ('update_reproductive_add', 'Reproductive additional',   @consent_update_id, 'test', 'yes');

SELECT @dna_blood_id := id FROM lab_sections WHERE section_name = 'DNA - blood';
INSERT INTO lab_tests (test_name, field_label, lab_section_id, test_type, has_results) VALUES
    ('qubit_blood',      'Qubit',      @dna_blood_id, 'test', 'yes'),
    ('blood_summary_qc', 'QC Summary', @dna_blood_id, 'test', 'yes');

SELECT @dna_tumour_id := id FROM lab_sections WHERE section_name = 'DNA - tumour';
INSERT INTO lab_tests (test_name, field_label, lab_section_id, test_type, has_results) VALUES
    ('cellularity',                 'Cellularity',              @dna_tumour_id, 'test', 'yes'),
    ('cellularity_datetime',        'Cellularity datetime',     @dna_tumour_id, 'test', 'yes'),
    ('percent_necrosis',            '% Necrosis',               @dna_tumour_id, 'test', 'yes'),
    ('percent_necrosis_datetime',   '% Necrosis datetime',      @dna_tumour_id, 'test', 'yes'),
    ('tumour_content',              'Tumour content',           @dna_tumour_id, 'test', 'yes'),
    ('tumour_content_datetime',     'Tumour content datetime',  @dna_tumour_id, 'test', 'yes'),
    ('qubit_tumour',                'Qubit',                    @dna_tumour_id, 'test', 'yes'),
    ('qubit_tumour_datetime',       'Qubit datetime',           @dna_tumour_id, 'test', 'yes'),
    ('tumour_summary_qc',           'QC Summary',               @dna_tumour_id, 'test', 'yes');

SELECT @saliva_id := id FROM lab_sections WHERE section_name = 'Specimens - saliva';
INSERT INTO lab_tests (test_name, field_label, lab_section_id, test_type, has_results) VALUES
    ('saliva_sample_taken',         'Sample taken',        @saliva_id, 'test', 'yes'),
    ('saliva_handling_protocol',    'Handling protocol',   @saliva_id, 'test', 'yes'),
    ('oragene_tube_1',              'Oragene tube #1',     @saliva_id, 'test', 'yes'),
    ('oragene_tube_2',              'Oragene tube #2',     @saliva_id, 'test', 'yes');

SELECT @bm_id := id FROM lab_sections WHERE section_name = 'Specimens - bone marrow';
INSERT INTO lab_tests (test_name, field_label, lab_section_id, test_type, has_results) VALUES
    ('bm_sample_taken',             'Sample taken',          @bm_id, 'test', 'yes'),
    ('bm_clinic_sample_type',       'Clinic sample type',    @bm_id, 'test', 'yes'),
    ('bm_unsent_sample_reason',     'Unsent sample reason',  @bm_id, 'test', 'yes'),
    ('bm_tumour_size',              'Trephine length (mm)',  @bm_id, 'test', 'yes'),
    ('bm_sample_id',                'Sample ID',             @bm_id, 'test', 'yes'),
    ('bm_lab_identifier',           'Lab number',            @bm_id, 'test', 'yes');

SELECT @dna_saliva_id := id FROM lab_sections WHERE section_name = 'DNA - saliva';
INSERT INTO lab_tests (test_name, field_label, lab_section_id, test_type, has_results) VALUES
    ('saliva_dna_lab_number',       'DNA lab number',   @dna_saliva_id, 'test', 'yes'),
    ('saliva_dna_fluidx',           'Fluidx vial ID',   @dna_saliva_id, 'test', 'yes'),
    ('saliva_dna_fluidx_rack_id',   'Rack ID',          @dna_saliva_id, 'test', 'yes'),
    ('saliva_dna_fluidx_vol',       'Fluidx vol',       @dna_saliva_id, 'test', 'yes'),
    ('saliva_dna_fluidx_well',      'Fluidx well pos',  @dna_saliva_id, 'test', 'yes'),
    ('saliva_dna_qc_datetime',      'QC datetime',      @dna_saliva_id, 'test', 'yes'),
    ('saliva_dna_summary_qc',       'QC summary',       @dna_saliva_id, 'test', 'yes'),
    ('saliva_dna_qubit',            'Qubit',            @dna_saliva_id, 'test', 'yes'),
    ('saliva_dna_volume_stored',    'Volume stored',    @dna_saliva_id, 'test', 'yes');

SELECT @dna_bm_id := id FROM lab_sections WHERE section_name = 'DNA - bone marrow';
INSERT INTO lab_tests (test_name, field_label, lab_section_id, test_type, has_results) VALUES
    ('bm_dna_lab_number',       'DNA lab number',   @dna_bm_id, 'test', 'yes'),
    ('bm_dna_fluidx',           'Fluidx vial ID',   @dna_bm_id, 'test', 'yes'),
    ('bm_dna_fluidx_rack_id',   'Rack ID',          @dna_bm_id, 'test', 'yes'),
    ('bm_dna_fluidx_vol',       'Fluidx vol',       @dna_bm_id, 'test', 'yes'),
    ('bm_dna_fluidx_rack_well', 'Fluidx well pos',  @dna_bm_id, 'test', 'yes'),
    ('bm_dna_qc_datetime',      'QC datetime',      @dna_bm_id, 'test', 'yes'),
    ('bm_dna_summary_qc',       'QC summary',       @dna_bm_id, 'test', 'yes'),
    ('bm_dna_qubit',            'Qubit',            @dna_bm_id, 'test', 'yes'),
    ('bm_dna_volume_stored',    'Volume stored',    @dna_bm_id, 'test', 'yes');

/* new result data types */
INSERT INTO lab_test_result_data_types(description) VALUES
    ('info_sheet_update'),
    ('yes_no_irrelevant'),
    ('consent_update'),
    ('tumour_content'),
    ('cellularity'),
    ('pass_fail');

/* result data types for new lab-tests */
  /* yes_no options */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'yes_no';
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'health_related_additional'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'consent_given_update'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'update_health_related'),
        (@ltrdt_id);
  /* pass_fail options */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'pass_fail';
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'blood_summary_qc'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'tumour_summary_qc'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_dna_summary_qc'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_dna_summary_qc'),
        (@ltrdt_id);
  /* free_text options */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'free_text';
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'qubit_blood'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'qubit_tumour'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_dna_qubit'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'percent_necrosis'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_dna_lab_number'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_dna_fluidx'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_dna_fluidx_rack_id'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_dna_fluidx_rack_well'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_dna_fluidx_vol'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_dna_volume_stored'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_tumour_size'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_sample_id'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_lab_identifier'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'oragene_tube_1'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'oragene_tube_2'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_dna_lab_number'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_dna_volume_stored'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_dna_fluidx'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_dna_fluidx_rack_id'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_dna_fluidx_well'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_dna_fluidx_vol'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_dna_qubit'),
        (@ltrdt_id);
  /* yes_no_irrelevant options */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'yes_no_irrelevant';
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'reproductive_additional'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'update_reproductive_add'),
        (@ltrdt_id);
  /* datetime options */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'datetime';
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'percent_necrosis_datetime'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'cellularity_datetime'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'tumour_content_datetime'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'qubit_tumour_datetime'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_dna_qc_datetime'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_sample_taken'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_sample_taken'),
        (@ltrdt_id);
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_dna_qc_datetime'),
        (@ltrdt_id);
  /* date options */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'date';
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'consent_date_update'),
        (@ltrdt_id);
  /* other options */
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'consent_form_update'),
        (SELECT id FROM lab_test_result_data_types WHERE description = 'consent_update');
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'info_sheet_update'),
        (SELECT id FROM lab_test_result_data_types WHERE description = 'info_sheet_update');
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'tumour_content'),
        (SELECT id FROM lab_test_result_data_types WHERE description = 'tumour_content');
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'cellularity'),
        (SELECT id FROM lab_test_result_data_types WHERE description = 'cellularity');
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_clinic_sample_type'),
        (SELECT id FROM lab_test_result_data_types WHERE description = 'clinic_sample_type');
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'bm_unsent_sample_reason'),
        (SELECT id FROM lab_test_result_data_types WHERE description = 'unsent_sample_reason');
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'saliva_handling_protocol'),
        (SELECT id FROM lab_test_result_data_types WHERE description = 'handling_protocol');

  /* disable existing C1, C2 & R1 - R8 values for info_sheet & consent_form - do this BEFORE next block */
UPDATE lab_test_result_options SET is_active = 'no' WHERE `value` RLIKE '^[CR][1-8]'; /* or use BINARY */

/* new lab-test result menu options */
  /* for new data type 'consent_form' */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'consent_form';
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
    (@ltrdt_id, 'c1_v2_2'),
    (@ltrdt_id, 'c2_v2_5'),
    (@ltrdt_id, 'c5_v1_1'),
    (@ltrdt_id, 'c1_v2_3'),
    (@ltrdt_id, 'c2_v2_6'),
    (@ltrdt_id, 'c5_v1_2'),
    (@ltrdt_id, 'c6_v1_0'),
    (@ltrdt_id, 'c8_v1_0'),
    (@ltrdt_id, 'ge_opt_out'),
    (@ltrdt_id, 'cohort_int'),
    (@ltrdt_id, 'cohort_int_and_res'),
    (@ltrdt_id, 'cohort_res'),
    (@ltrdt_id, 'r1_r2_cf_v2_2'),
    (@ltrdt_id, 'r1_cf_v2_3'),
    (@ltrdt_id, 'r5_cf_v2_1'),
    (@ltrdt_id, 'r5_cf_v2_2'),
    (@ltrdt_id, 'r6_cf_v2_1'),
    (@ltrdt_id, 'r6_cf_v2_2'),
    (@ltrdt_id, 'r7_cf_v2_4'),
    (@ltrdt_id, 'r7_cf_v2_5'),
    (@ltrdt_id, 'r8_cf_v2_1'),
    (@ltrdt_id, 'r8_cf_v2_2'),
    (@ltrdt_id, 'r1e_cf_v1_0');
  /* for new data type 'info_sheet' */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'info_sheet';
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
    (@ltrdt_id, 'c1_pis_v2_3'),
    (@ltrdt_id, 'c2_pis_v2_6'),
    (@ltrdt_id, 'c5_pis_v1_1'),
    (@ltrdt_id, 'c1_pis_v2_4'),
    (@ltrdt_id, 'c2_pis_v2_7'),
    (@ltrdt_id, 'c5_pis_v1_2'),
    (@ltrdt_id, 'c6_pis_v1_0'),
    (@ltrdt_id, 'c8_pis_v1_0'),
    (@ltrdt_id, 'ge_opt_out'),
    (@ltrdt_id, 'cohort_int'),
    (@ltrdt_id, 'cohort_int_and_res'),
    (@ltrdt_id, 'cohort_res'),
    (@ltrdt_id, 'r1_pis_v2_2'),
    (@ltrdt_id, 'r1_pis_v2_3'),
    (@ltrdt_id, 'r2_pis_v2_2'),
    (@ltrdt_id, 'r5_pis_v2_3'),
    (@ltrdt_id, 'r5_pis_v2_4'),
    (@ltrdt_id, 'r6_pis_v2_3'),
    (@ltrdt_id, 'r6_pis_v2_4'),
    (@ltrdt_id, 'r7_pis_v2_5'),
    (@ltrdt_id, 'r7_pis_v2_6'),
    (@ltrdt_id, 'r8_pis_v2_3'),
    (@ltrdt_id, 'r8_pis_v2_4'),
    (@ltrdt_id, 'r1e_pis_v1_0');
  /* for new data type 'consent_update' */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'consent_update';
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
    (@ltrdt_id, 'r1_r2_cf_v2_2'),
    (@ltrdt_id, 'r1_cf_v2_3'),
    (@ltrdt_id, 'r5_cf_v2_1'),
    (@ltrdt_id, 'r5_cf_v2_2'),
    (@ltrdt_id, 'r6_cf_v2_1'),
    (@ltrdt_id, 'r6_cf_v2_2'),
    (@ltrdt_id, 'r7_cf_v2_4'),
    (@ltrdt_id, 'r7_cf_v2_5'),
    (@ltrdt_id, 'r8_cf_v2_1'),
    (@ltrdt_id, 'r8_cf_v2_2'),
    (@ltrdt_id, 'r1e_cf_v1_0'),
    (@ltrdt_id, '7a_cf_v1_1'),
    (@ltrdt_id, '7b_cf_v1_1'),
    (@ltrdt_id, '7a_cf_v1_0'),
    (@ltrdt_id, '7b_cf_v1_0'),
    (@ltrdt_id, 'c1_cf_v2_2'),
    (@ltrdt_id, 'c2_cf_v2_5'),
    (@ltrdt_id, 'c5_cf_v1_1'),
    (@ltrdt_id, 'c1_cf_v2_3'),
    (@ltrdt_id, 'c2_cf_v2_6'),
    (@ltrdt_id, 'c5_cf_v1_2'),
    (@ltrdt_id, 'c6_cf_v1_0'),
    (@ltrdt_id, 'c8_cf_v1_0'),
    (@ltrdt_id, 'ge_opt_out'),
    (@ltrdt_id, 'cohort_int'),
    (@ltrdt_id, 'cohort_int_and_res'),
    (@ltrdt_id, 'cohort_res');
  /* for new data type 'info_sheet_update' */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'info_sheet_update';
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
   (@ltrdt_id, 'r1_pis_v2_2'),
   (@ltrdt_id, 'r1_pis_v2_3'),
   (@ltrdt_id, 'r2_pis_v2_2'),
   (@ltrdt_id, 'r5_pis_v2_3'),
   (@ltrdt_id, 'r5_pis_v2_4'),
   (@ltrdt_id, 'r6_pis_v2_3'),
   (@ltrdt_id, 'r6_pis_v2_4'),
   (@ltrdt_id, 'r7_pis_v2_5'),
   (@ltrdt_id, 'r7_pis_v2_6'),
   (@ltrdt_id, 'r8_pis_v2_3'),
   (@ltrdt_id, 'r8_pis_v2_4'),
   (@ltrdt_id, 'r1e_pis_v1_0'),
   (@ltrdt_id, '7a_pis_v1_0'),
   (@ltrdt_id, '7b_pis_v1_0'),
   (@ltrdt_id, 'c1_pis_v2_3'),
   (@ltrdt_id, 'c2_pis_v2_6'),
   (@ltrdt_id, 'c1_pis_v2_4'),
   (@ltrdt_id, 'c2_pis_v2_7'),
   (@ltrdt_id, 'c5_pis_v1_1'),
   (@ltrdt_id, 'c5_pis_v1_2'),
   (@ltrdt_id, 'c6_pis_v1_0'),
   (@ltrdt_id, 'c8_pis_v1_0'),
   (@ltrdt_id, 'ge_opt_out'),
   (@ltrdt_id, 'cohort_int'),
   (@ltrdt_id, 'cohort_int_and_res'),
   (@ltrdt_id, 'cohort_res');

  /* for new data type 'tumour_content' */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'tumour_content';
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
    (@ltrdt_id, 'low'),
    (@ltrdt_id, 'medium'),
    (@ltrdt_id, 'high');
  /* for new data type 'cellularity' */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'cellularity';
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
    (@ltrdt_id, 'verylow'),
    (@ltrdt_id, 'low'),
    (@ltrdt_id, 'medium'),
    (@ltrdt_id, 'high'),
    (@ltrdt_id, 'veryhigh');
  /* for new data type 'pass_fail' */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'pass_fail';
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
    (@ltrdt_id, 'pass'),
    (@ltrdt_id, 'fail');
  /* for new data type 'yes_no_irrelevant' */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'yes_no_irrelevant';
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
    (@ltrdt_id, 'yes'),
    (@ltrdt_id, 'no'),
    (@ltrdt_id, 'not_relevant');
  /* additional unsent_sample_reason options */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'unsent_sample_reason';
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
    (@ltrdt_id, 'FFPE not optimally fixed'),
    (@ltrdt_id, 'FFPE not optimally processed'),
    (@ltrdt_id, 'High necrosis (over 20%)');

  /* update some additional tissue_source options */
SELECT @ltrdt_id := id FROM lab_test_result_data_types WHERE description = 'tissue_source';
UPDATE lab_test_result_options SET `value` = 'Endoscopic biopsy'
    WHERE `value` = 'endoscopic biopsy';
UPDATE lab_test_result_options SET `value` = 'Non-guided biopsy'
    WHERE `value` = 'non-guided biopsy';
UPDATE lab_test_result_options SET `value` = 'Stereotactically guided biopsy'
    WHERE `value` = 'stereotactically guided biopsy';
UPDATE lab_test_result_options SET `value` = 'Surgical resection'
    WHERE `value` = 'surgical resection';
  /* additional tissue_source options */
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
    (@ltrdt_id, 'Endoscopic USS-guided biopsy'),
    (@ltrdt_id, 'Endoscopic USS-guided FNA'),
    (@ltrdt_id, 'Laparoscopic biopsy'),
    (@ltrdt_id, 'Laparoscopic excision');
  /* set all tissue_source lab_test_result_options active; should be 11 total */
UPDATE lab_test_result_options SET is_active = 'yes' WHERE data_type_id = @ltrdt_id;

/*
    lab_test_result_data_types:
    13 = sample_type (not used)
    19 = clinic_sample_type (new name for sample_type)
    20 = tumour_sample_type (has 2 options, sections & blocks)

    need to move all lab_test_result_options where data_type_id = 13 to 19, except
        'DNA FFPE Tumour' & 'DNA FF Tumour' which are already in both sections
*/
SELECT @st_id := id FROM lab_test_result_data_types WHERE description = 'sample_type';
SELECT @cst_id := id FROM lab_test_result_data_types WHERE description = 'clinic_sample_type';
 /* need a double-select to avoid error: "can't specify target table
    `lab_test_result_options` for update in FROM clause" */
UPDATE lab_test_result_options t1 SET t1.data_type_id = @cst_id
    WHERE t1.data_type_id = @st_id AND t1.`value` NOT IN
        ( SELECT duplicate FROM ( SELECT t2.`value` as duplicate
            FROM lab_test_result_options t2 WHERE t2.data_type_id = @cst_id) as t3 );
  /* switch off lab_test_result_data_types 'sample_type' & its remaining lab_test_result_options */
UPDATE lab_test_result_data_types SET is_active = 'no' WHERE id = @st_id;
UPDATE lab_test_result_options SET is_active = 'no' WHERE data_type_id = @st_id;

  /* rename some existing clinic_sample_type options */
UPDATE lab_test_result_options SET `value` = 'Additional Research Material'
    WHERE `value` = 'Additional Tumour Material';
UPDATE lab_test_result_options SET `value` = 'FFPE Scrapings/Slides'
    WHERE `value` = 'Tumour Scrapings';
UPDATE lab_test_result_options SET `value` = 'EDTA Plasma (for ctDNA)'
    WHERE `value` = 'EDTA Plasma';
UPDATE lab_test_result_options SET `value` = 'DNA HaemOnc Blood'
    WHERE `value` = 'DNA Blood Tumour';
UPDATE lab_test_result_options SET `value` = 'Deparaffinised Lysed Cells'
    WHERE `value` = 'Lysed Tumour Cells';

  /* additional clinic_sample_type options */
INSERT INTO lab_test_result_options(data_type_id,`value`) VALUES
    (@cst_id, 'DNA Bone Marrow Aspirate'),
    (@cst_id, 'DNA BMA Sorted Cells'),
    (@cst_id, 'Tumour Tissue FFPE'),
    (@cst_id, 'Lysate FFPE'),
    (@cst_id, 'Lysate FF'),
    (@cst_id, 'Buffy Coat'),
    (@cst_id, 'Streck Plasma'); /* check 21 lab_test_result_options for data_type_id = 19 */

  /* update lab-section names */
/* // Sort::Naturally can't handle decimals so intended sort order doesn't happen
UPDATE lab_sections SET section_name = '1) Approach'
    WHERE section_name = 'Approach';
UPDATE lab_sections SET section_name = '2) Consent'
    WHERE section_name = 'Consent';
UPDATE lab_sections SET section_name = '2.1) Consent update'
    WHERE section_name = 'Consent update';
UPDATE lab_sections SET section_name = '2.2) Consent withdrawal'
    WHERE section_name = 'Consent withdrawal';
UPDATE lab_sections SET section_name = '3) Specimens - blood'
    WHERE section_name = 'Specimens - blood';
UPDATE lab_sections SET section_name = '4) Specimens - bone marrow'
    WHERE section_name = 'Specimens - bone marrow';
UPDATE lab_sections SET section_name = '5) Specimens - saliva'
    WHERE section_name = 'Specimens - saliva';
UPDATE lab_sections SET section_name = '6) Specimens - tumour'
    WHERE section_name = 'Specimens - tumour';
UPDATE lab_sections SET section_name = '6.1) Specimens - frozen'
    WHERE section_name = 'Specimens - frozen';
UPDATE lab_sections SET section_name = '6.2) Specimens - FFPE'
    WHERE section_name = 'Specimens - FFPE';
UPDATE lab_sections SET section_name = '7) DNA - blood'
    WHERE section_name = 'DNA - blood';
UPDATE lab_sections SET section_name = '8) DNA - bone marrow'
    WHERE section_name = 'DNA - bone marrow';
UPDATE lab_sections SET section_name = '9) DNA - saliva'
    WHERE section_name = 'DNA - saliva';
UPDATE lab_sections SET section_name = '10) DNA - tumour'
    WHERE section_name = 'DNA - tumour';
UPDATE lab_sections SET section_name = '11) Transfer to genetics'
    WHERE section_name = 'Transfer to genetics';
UPDATE lab_sections SET section_name = '12) Dispatch'
    WHERE section_name = 'Dispatch';
*/

  /* new HaemOnc category: */
INSERT INTO screen_category (`name`) VALUES ('HaemOnc');
INSERT INTO screens (`description`, `category_id`, `active`) VALUES
    ('Acute Lymphoblastic Leukaemia', 3, 'yes'),
    ('Acute Myeloid Leukaemia', 3, 'yes'),
    ('Chronic Lymphocytic Leukaemia', 3, 'yes'),
    ('Burkitt Lymphoma', 3, 'yes'),
    ('Chronic Myeloid Leukaemia', 3, 'yes'),
    ('Diffuse Large B-cell Lymphoma', 3, 'yes'),
    ('High Grade Lymphoma NOS', 3, 'yes'),
    ('Mediastinal B-cell Lymphoma', 3, 'yes'),
    ('Multiple Myeloma', 3, 'yes'),
    ('Myelodysplastic Syndrome (high risk)', 3, 'yes'),
    ('Non-Hodgkins B-cell Lymphoma (low/moderate grade)', 3, 'yes'),
    ('Classical Hodgkins Lymphoma', 3, 'yes'),
    ('Nodular Lymphocyte Predominant Hodgkins Lymphoma', 3, 'yes'),
    ('T-cell Lymphoma', 3, 'yes'),
    /* 3 new cancers */
    ('Nasopharyngeal', 2, 'yes'),
    ('Sinonasal', 2, 'yes'),
    ('Oral or Oropharyngeal', 2, 'yes');

/* run genomics_v3_screen_lab_test.pl */
/* set genomics.xsd symlink to <current>.xsd */
/* set outstanding tests to complete for discontinued tests */
/* run scripts/xml_validate.pl */