ALTER TABLE `inventory_item_stock` ADD `unit_cost` DECIMAL(15,2) NOT NULL AFTER `max_stock`, ADD `cost` DECIMAL(15,2) NOT NULL AFTER `unit_cost`;
UPDATE inventory_item_stock SET inventory_item_stock.unit_cost = (SELECT billing_item_pricelists.price FROM billing_item_pricelists WHERE billing_item_pricelists.item_id = inventory_item_stock.item_id AND billing_item_pricelists.pricelist = 'COST');
UPDATE inventory_item_stock SET cost = unit_cost * stock;

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`;

UPDATE clinical_visits JOIN clinical_patients SET clinical_visits.type = 'Revisit' WHERE DATEDIFF(clinical_visits.created_datetime, clinical_patients.created_datetime) > 0

ALTER TABLE `billing_item_pricelists` CHANGE `account` `debit_account` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, CHANGE `account_id` `debit_account_id` INT NOT NULL;
ALTER TABLE `billing_item_pricelists` ADD `credit_account` VARCHAR(250) NOT NULL AFTER `debit_account_id`, ADD `credit_account_id` INT NOT NULL AFTER `credit_account`;
ALTER TABLE `billing_item_pricelists` ADD INDEX(`credit_account`);
ALTER TABLE `billing_item_pricelists` ADD INDEX(`credit_account_id`);
ALTER TABLE `billing_item_pricelists` ADD INDEX(`pricemode`);

ALTER TABLE `accounting_accounts` ADD `parent` VARCHAR(250) NOT NULL AFTER `subtype`, ADD `parent_id` INT NOT NULL AFTER `parent`, ADD `level` INT NOT NULL AFTER `parent_id`;
ALTER TABLE `accounting_accounts` ADD INDEX(`parent`);
ALTER TABLE `accounting_accounts` ADD INDEX(`parent_id`);
ALTER TABLE `accounting_accounts` ADD INDEX(`level`);
ALTER TABLE `accounting_accounts` ADD `ledger` INT NOT NULL AFTER `currency`, ADD `deposit` INT NOT NULL AFTER `ledger`, ADD `withdraw` INT NOT NULL AFTER `deposit`, ADD `extra` INT NOT NULL AFTER `withdraw`;
ALTER TABLE `accounting_accounts` ADD INDEX(`ledger`);
ALTER TABLE `accounting_accounts` ADD INDEX(`deposit`);
ALTER TABLE `accounting_accounts` ADD INDEX(`withdraw`);
ALTER TABLE `accounting_accounts` ADD INDEX(`channel`);

DELETE FROM `accounting_accounts` WHERE `accounting_accounts`.`id` = 4;
DELETE FROM `accounting_accounts` WHERE `accounting_accounts`.`id` = 5;
DELETE FROM `accounting_accounts` WHERE `accounting_accounts`.`id` = 6;
DELETE FROM `accounting_accounts` WHERE `accounting_accounts`.`id` = 7;
DELETE FROM `accounting_accounts` WHERE `accounting_accounts`.`id` = 8;
DELETE FROM `accounting_accounts` WHERE `accounting_accounts`.`id` = 9;
DELETE FROM `accounting_accounts` WHERE `accounting_accounts`.`id` = 10;
DELETE FROM `accounting_accounts` WHERE `accounting_accounts`.`id` = 11;
DELETE FROM `accounting_accounts` WHERE `accounting_accounts`.`id` = 12;

-- UPDATE `accounting_accounts` SET `id` = '4' WHERE `accounting_accounts`.`id` = 1;
-- UPDATE `accounting_accounts` SET `id` = '1' WHERE `accounting_accounts`.`id` = 2;
-- UPDATE `accounting_accounts` SET `id` = '2' WHERE `accounting_accounts`.`id` = 13;
-- UPDATE `accounting_accounts` SET `id` = '3' WHERE `accounting_accounts`.`id` = 15;
-- UPDATE `accounting_accounts` SET `id` = '5' WHERE `accounting_accounts`.`id` = 14;
-- UPDATE `accounting_accounts` SET `id` = '6' WHERE `accounting_accounts`.`id` = 16;

UPDATE `accounting_accounts` SET `name` = 'GEPG Account' WHERE `accounting_accounts`.`code` = 'GEPG'; 
UPDATE `accounting_accounts` SET `name` = 'NMB Account' WHERE `accounting_accounts`.`code` = 'NMB';
UPDATE `accounting_accounts` SET `name` = 'CRDB Account' WHERE `accounting_accounts`.`code` = 'CRDB';
UPDATE `accounting_accounts` SET `name` = 'NHIF Account' WHERE `accounting_accounts`.`code` = 'NHIF';
UPDATE `accounting_accounts` SET `name` = 'CASH Account' WHERE `accounting_accounts`.`code` = 'CASH';

UPDATE `billing_item_pricelists` SET `debit_account` = '', `debit_account_id` = 0, `credit_account` = '', `credit_account_id` = 0;
ALTER TABLE `billing_item_pricelists` ADD `opd_debit_account_id` INT NOT NULL AFTER `debit_account_id`, ADD `ipd_debit_account_id` INT NOT NULL AFTER `opd_debit_account_id`;
ALTER TABLE `billing_item_pricelists` ADD `opd_credit_account_id` INT NOT NULL AFTER `credit_account_id`, ADD `ipd_credit_account_id` INT NOT NULL AFTER `opd_credit_account_id`;
ALTER TABLE `billing_item_pricelists` ADD INDEX(`opd_debit_account_id`);
ALTER TABLE `billing_item_pricelists` ADD INDEX(`ipd_debit_account_id`);
ALTER TABLE `billing_item_pricelists` ADD INDEX(`opd_credit_account_id`);
ALTER TABLE `billing_item_pricelists` ADD INDEX(`ipd_credit_account_id`);