#====================================
USE `genomics`; # or change to `test`
#====================================

/* new lab-section */
INSERT INTO lab_sections(section_name, has_test_sign_out)
    VALUES ('Consent update', 'yes'); /* all rest of cols default vals */

/* 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');

/* 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);
  /* 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 = 'percent_necrosis'),
        (@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);
  /* other options */
INSERT INTO lab_test_data_type(lab_test_id, data_type_id)
    SELECT
        (SELECT id FROM lab_tests WHERE test_name = 'consent_date_update'),
        (SELECT 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_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');

/* new lab-test result menu options */
  /* for existing data type 'info_sheet' (values R1 - R8 already exist )*/
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'),
    (@ltrdt_id, 'c2_pis'),
    (@ltrdt_id, 'c3_pis'),
    (@ltrdt_id, 'c4_pis'),
    (@ltrdt_id, 'c5_pis');
  /* 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'),
    (@ltrdt_id, 'R3/R4'),
    (@ltrdt_id, 'R5'),
    (@ltrdt_id, 'R6'),
    (@ltrdt_id, 'R7'),
    (@ltrdt_id, 'R8'),
    (@ltrdt_id, '7a_cf'),
    (@ltrdt_id, '7b_cf'),
    (@ltrdt_id, 'c1_cf'),
    (@ltrdt_id, 'c2_cf'),
    (@ltrdt_id, 'c5_cf');
  /* 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'),
    (@ltrdt_id, 'R2'),
    (@ltrdt_id, 'R3'),
    (@ltrdt_id, 'R4'),
    (@ltrdt_id, 'R5'),
    (@ltrdt_id, 'R6'),
    (@ltrdt_id, 'R7'),
    (@ltrdt_id, 'R8'),
    (@ltrdt_id, '7a_pis'),
    (@ltrdt_id, '7b_pis'),
    (@ltrdt_id, 'c1_pis'),
    (@ltrdt_id, 'c2_pis'),
    (@ltrdt_id, 'c3_pis'),
    (@ltrdt_id, 'c4_pis'),
    (@ltrdt_id, 'c5_pis');
  /* 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');

/* inactivate test result menu options */
UPDATE lab_test_result_options SET is_active = 'no' WHERE `value` IN ('C1','C2');

/* update lab-section names */
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) DNA - blood' WHERE section_name = 'DNA - blood';
UPDATE lab_sections SET section_name = '5) Specimens - tumour' WHERE section_name = 'Specimens - tumour';
UPDATE lab_sections SET section_name = '5.1) Specimens - frozen' WHERE section_name = 'Specimens - frozen';
UPDATE lab_sections SET section_name = '5.2) Specimens - FFPE' WHERE section_name = 'Specimens - FFPE';
UPDATE lab_sections SET section_name = '6) Transfer to genetics' WHERE section_name = 'Transfer to genetics';
UPDATE lab_sections SET section_name = '7) DNA - tumour' WHERE section_name = 'DNA - tumour';
UPDATE lab_sections SET section_name = '8) Dispatch' WHERE section_name = 'Dispatch';

/* run genomics_v3_screen_lab_test.pl */
