UPDATE settings_categories SET name = 'Referral from another country' WHERE code = 'International';
UPDATE settings_categories SET code = 'Domestic', name = 'Referral from another facility in Tanzania' WHERE code = 'Referral' AND type = 'ReferralType';
UPDATE settings_categories SET code = 'International', name = 'Referral to another country' WHERE code = 'InternationalReferral';
UPDATE settings_categories SET code = 'Domestic', name = 'Referral to another facility in Tanzania' WHERE code = 'DomesticReferral';
UPDATE settings_categories SET code = 'Domestic' WHERE code = 'DomesticReferral';

ALTER TABLE `clinical_visits` ADD `referral_in_code` VARCHAR(250) NOT NULL AFTER `referral_in_id`, ADD `referral_in_location` VARCHAR(250) NOT NULL AFTER `referral_in_code`, ADD `checkin_diagnoses` VARCHAR(250) NOT NULL AFTER `checkin_notes`, ADD `referral_in_notes` TEXT NOT NULL AFTER `referral_in_location`;
ALTER TABLE `clinical_visits` ADD `referral_out_code` VARCHAR(250) NOT NULL AFTER `referral_out_id`, ADD `referral_out_location` VARCHAR(250) NOT NULL AFTER `referral_out_code`, ADD `checkout_diagnoses` VARCHAR(250) NOT NULL AFTER `checkout_notes`, ADD `referral_out_notes` TEXT NOT NULL AFTER `referral_out_location`;
ALTER TABLE `clinical_visits` CHANGE `referral_in` `referral_in_type` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
ALTER TABLE `clinical_visits` ADD `referral_in` INT NOT NULL AFTER `care_type`, ADD INDEX `referral` (`referral_in`);

ALTER TABLE `clinical_visits` CHANGE `referral_out` `referral_out_type` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
ALTER TABLE `clinical_visits` ADD `checkout_template` TEXT NOT NULL AFTER `checkout_type`;
ALTER TABLE `clinical_visits` DROP `extra`;
ALTER TABLE `clinical_visits` ADD `code` INT NOT NULL AFTER `referral_in_notes`, ADD `name` VARCHAR(250) NOT NULL AFTER `code`, ADD `birthdate` DATE NOT NULL AFTER `name`, ADD `sex` VARCHAR(250) NOT NULL AFTER `birthdate`, ADD `phone` VARCHAR(250) NOT NULL AFTER `sex`;
ALTER TABLE `clinical_visits` CHANGE `residence` `residence` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL after `phone`;
ALTER TABLE `clinical_visits` ADD `referral_out` INT NOT NULL AFTER `checkout_datetime`, ADD INDEX `referral2` (`referral_out`);

UPDATE `clinical_visits` SET referral_in = 1, referral_out_type = 'Domestic' WHERE referral_in_type = 'Referral';
UPDATE `clinical_visits` SET referral_out = 1 WHERE (referral_out_type = 'Referral' OR referral_out_type = 'Domestic');

ALTER TABLE `clinical_visit_locations` ADD `referral_in_code` VARCHAR(250) NOT NULL AFTER `referral_in_id`, ADD `referral_in_location` VARCHAR(250) NOT NULL AFTER `referral_in_code`, ADD `checkin_diagnoses` VARCHAR(250) NOT NULL AFTER `checkin_notes`, ADD `referral_in_notes` TEXT NOT NULL AFTER `referral_in_location`;
ALTER TABLE `clinical_visit_locations` ADD `referral_out_code` VARCHAR(250) NOT NULL AFTER `referral_out_id`, ADD `referral_out_location` VARCHAR(250) NOT NULL AFTER `referral_out_code`, ADD `checkout_diagnoses` VARCHAR(250) NOT NULL AFTER `checkout_notes`, ADD `referral_out_notes` TEXT NOT NULL AFTER `referral_out_location`;
ALTER TABLE `clinical_visit_locations` CHANGE `referral_in` `referral_in_type` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
ALTER TABLE `clinical_visit_locations` ADD `referral_in` INT NOT NULL AFTER `care_type`, ADD INDEX `referral` (`referral_in`);

ALTER TABLE `clinical_visit_locations` CHANGE `referral_out` `referral_out_type` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
ALTER TABLE `clinical_visit_locations` ADD `referral_out` INT NOT NULL AFTER `checkout_datetime`, ADD INDEX `referral2` (`referral_out`);

ALTER TABLE `clinical_visit_locations` CHANGE `disposition` `checkout_template` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL after `checkout_type`;