/* post insert BHODS data */
INSERT INTO bristol.request_specimen_detail
        (request_id, gross_description, specimen_quality)
    SELECT t1.request_id, t2.detail, t1.specimen_quality
    FROM bristol.request_report t1 LEFT JOIN bristol.request_gross_description t2
        on t1.request_id = t2.request_id ORDER BY t1.request_id;
INSERT INTO bristol.request_report_detail (request_id, clinical_details, comment,
        status, diagnosis_id, created_at, updated_at)
	SELECT request_id, clinical_details, comment, status, diagnosis_id, created_at,
        updated_at FROM bristol.request_report ORDER BY request_id;
RENAME TABLE bristol.`request_report` TO `_request_report`;
RENAME TABLE bristol.`request_gross_description` TO `_request_gross_description`;

UPDATE bristol.request_report_detail SET `comment` = NULL, updated_at = updated_at
    WHERE `comment` IN ('-', '.');
UPDATE bristol.request_report_detail SET clinical_details = NULL, updated_at = updated_at
    WHERE clinical_details IN ('-', '.', ',');


/* upload_data_files user functions: */
INSERT INTO bristol.user_functions (function_name, function_detail)
	 VALUES ('upload_data_files', 'upload and delete data files');
SELECT @function_id := id FROM bristol.user_functions
    WHERE function_name = 'upload_data_files';
INSERT INTO bristol.user_group_function (group_id, function_id)
    SELECT id, @function_id FROM bristol.user_groups WHERE group_name IN
        ('bms', 'cs', 'admin');
INSERT INTO bristol.user_permission (user_id, function_id)
	SELECT DISTINCT up.user_id, @function_id
	FROM bristol.user_permission up
		JOIN bristol.users u on up.user_id = u.id
		JOIN bristol.user_groups ug on u.group_id = ug.id
	WHERE ug.group_name in ('bms', 'cs', 'admin');

UPDATE bristol.user_functions SET function_detail = 'email report'
    WHERE function_name = 'email_report';
UPDATE bristol.users SET user_location_id = 26 /* BHODS */
    WHERE user_location_id IN (2,3); /* Blackpool & Boston */
DELETE FROM bristol.user_locations WHERE location_name NOT IN (
    'Torbay',
    'BHODS', 'HMDS', /* make these central labs in config */
    'North Bristol',
    'Musgrove Park Taunton',
    'Royal Devon and Exeter',
    'Royal United Hospital Bath',
    'University Hospital Bristol'
);
UPDATE `bristol`.`user_locations` SET `region_code`='RH8'
    WHERE location_name = 'Royal Devon and Exeter';
UPDATE `bristol`.`user_locations` SET `region_code`='RBA'
    WHERE location_name = 'Musgrove Park Taunton';
UPDATE `bristol`.`user_locations` SET `region_code`='RA7'
    WHERE location_name = 'University Hospital Bristol';
UPDATE `bristol`.`user_locations` SET `region_code`='RVJ'
    WHERE location_name = 'North Bristol';
UPDATE `bristol`.`user_locations` SET `region_code`='RA9'
    WHERE location_name = 'Torbay';
UPDATE `bristol`.`user_locations` SET `region_code`='RD1'
    WHERE location_name = 'Royal United Hospital Bath';

INSERT INTO bristol.`screen_category` (`id`, `name`, `is_active`) VALUES
	(1, 'fresh tissue samples', 'yes'),
	(2, 'fixed tissue samples', 'yes'),
	(3, 'acute leukaemias', 'yes'),
	(4, 'chronic myeloid disorders', 'yes'),
	(5, 'mature lymphoid disorders', 'yes'),
	(6, 'clinical trial samples', 'yes'),
	(7, 'miscellaneous', 'yes');
INSERT INTO bristol.screens_new(id,description,category_id,active)
	SELECT id,description, IF(sample_type = 'fresh', 1, 2) as category_id, active
    FROM bristol.screens;
RENAME TABLE bristol.`screens` TO `_screens`;
RENAME TABLE bristol.`screens_new` TO `screens`;

INSERT INTO bristol.request_diagnosis_history_new
        (id,request_id,diagnosis_id,option_id,user_id,time)
	SELECT id,request_id,diagnosis_id, IF(reason = 'error', 1, 2), user_id, time
	FROM bristol.request_diagnosis_history;
RENAME TABLE bristol.`request_diagnosis_history` TO `_request_diagnosis_history`;
RENAME TABLE bristol.`request_diagnosis_history_new` TO `request_diagnosis_history`;

/* configure specimen types for lab sections (just using Generic sample type): */
INSERT INTO `bristol`.`sample_types` (`specimen_type`) VALUES ('generic');
SELECT @sample_type_id := id FROM bristol.sample_types WHERE specimen_type = 'generic'; /* it's the only 1 */

INSERT INTO `bristol`.`lab_section_sample_type` (lab_section_id, sample_type_id)
	SELECT t2.id, @sample_type_id
	FROM `bristol`.`lab_sections` t2
	WHERE t2.section_name <> 'Additional Information';

INSERT INTO `bristol`.`lab_test_sample_type` (lab_test_id, sample_type_id)
	SELECT t1.id, @sample_type_id FROM bristol.lab_tests t1;

INSERT INTO `bristol`.`specimen_sample_type` (`specimen_id`, `sample_type_id`)
    SELECT id, @sample_type_id FROM bristol.specimens;

INSERT INTO bristol.lab_test_result_data_types (description) VALUES
	('decimal'),
	('integer'),
	('date'),
	('datetime');

/* switch all lab-test results off & delete all lab-test data-types: */
UPDATE bristol.lab_tests SET has_results = 'no';
DELETE FROM bristol.lab_test_data_type;
/* only want 'recieved' in Additional tests section to take a result: */
UPDATE bristol.lab_tests SET has_results = 'yes' WHERE test_name = 'received';
SELECT @lab_test_id := id FROM bristol.lab_tests WHERE test_name = 'received';
SELECT @data_type_id := id FROM bristol.lab_test_result_data_types
    WHERE description = 'date';
INSERT INTO bristol.lab_test_data_type(lab_test_id, data_type_id, is_active)
    VALUES(@lab_test_id, @data_type_id, 'yes');
/* don't need this if MO section not taking results:
SELECT @data_type_id := id FROM lab_test_result_data_types
    WHERE description = 'molecular text';
INSERT INTO lab_test_data_type(lab_test_id, data_type_id, is_active)
    SELECT lt.id, @data_type_id, 'yes'
    FROM lab_tests lt
        JOIN lab_sections ls on lt.lab_section_id = ls.id
    WHERE ls.section_name = 'Molecular oncology'
        and has_results = 'yes';
*/

/* specific lab tests: // no longer required, lab_tests src table updated
UPDATE bristol.lab_tests SET test_name='mo_additional_testing' WHERE test_name='MO - Additional testing';
UPDATE bristol.lab_tests SET test_name='col1a1_pdgfb', field_label='COL1A1/PDGFB [(t(17;22)]'
	WHERE test_name='COL1A1/PDGFB [(t(17;22';
UPDATE bristol.lab_tests SET test_name='igh_fgfr3' WHERE test_name='IGH@/FGFR3[t(4;14)]';
UPDATE bristol.lab_tests SET test_name='igh_maf' WHERE test_name='IGH@/MAF[t(14;16)]';
 */
/* generic lab tests: // no longer required, lab_tests src table updated
UPDATE bristol.lab_tests SET test_name = LCASE(test_name);
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,' ','_');
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,';','_');
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,'(','');
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,')','');
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,'-','_');
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,'/','_');
 */

DELETE FROM bristol.sessions; /* causes storable incompatibility */

/* remove temp tables: */
SET FOREIGN_KEY_CHECKS = 0;
/* // don't need if foreign_key_checks set to 0
ALTER TABLE `request_initial_screen`
	DROP FOREIGN KEY `request_initial_screen_ibfk_2`;
ALTER TABLE `request_initial_screen`
	ADD CONSTRAINT `request_initial_screen_ibfk_2` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`);
ALTER TABLE `screen_lab_test`
	DROP FOREIGN KEY `screen_lab_test_ibfk_1`;
ALTER TABLE `screen_lab_test`
	ADD CONSTRAINT `screen_lab_test_ibfk_1` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`);
ALTER TABLE `screen_lab_test_detail`
	DROP FOREIGN KEY `screen_lab_test_detail_ibfk_1`;
ALTER TABLE `screen_lab_test_detail`
	ADD CONSTRAINT `screen_lab_test_detail_ibfk_1` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`);
*/
DROP TABLE bristol._screens;
DROP TABLE bristol._request_diagnosis_history;
DROP TABLE bristol._request_gross_description;
DROP TABLE bristol._request_report;
SET FOREIGN_KEY_CHECKS = 1;
