DROP TABLE `currency_setting`;
DROP TABLE `closed_currency`;
DROP TABLE `closed_currency_repurchase`;
DROP TABLE `country_currency`;
DROP TABLE `manual_closed_currency`;
DROP TABLE `manual_transactions_reconcile`;
DROP TABLE `manual_transactions_return`;

ALTER TABLE `invoice_master`  DROP INDEX `sic_id`,  DROP INDEX `sic_group_id`;
ALTER TABLE `invoice_master`  DROP COLUMN `nace_id`,  DROP COLUMN `nace_group_id`,DROP COLUMN `country_id`;

ALTER TABLE `invoice_rejected`  DROP INDEX `sic_id`,  DROP INDEX `sic_group_id`;
ALTER TABLE `invoice_rejected`  DROP COLUMN `nace_id`,  DROP COLUMN `nace_group_id`,  DROP COLUMN `country_id`;

ALTER TABLE `invoice_repurchase`  DROP INDEX `sic_id`,  DROP INDEX  `sic_group_id`;
ALTER TABLE `invoice_repurchase`  DROP COLUMN `nace_id`,  DROP COLUMN `nace_group_id`,  DROP COLUMN `country_id`;

ALTER TABLE `invoice_amendments`  DROP INDEX `sic_id`,  DROP INDEX `sic_group_id`;
ALTER TABLE `invoice_amendments`  DROP COLUMN `nace_id`,  DROP COLUMN `nace_group_id`,  DROP COLUMN `country_id`;


ALTER TABLE `invoice_amendments`  DROP COLUMN `base_face_value`;
ALTER TABLE `invoice_master`  DROP COLUMN `base_face_value`;
ALTER TABLE `invoice_rejected`  DROP COLUMN `base_face_value`;
ALTER TABLE `invoice_repurchase`  DROP COLUMN `base_face_value`;

ALTER TABLE `invoice_master`  DROP COLUMN `actual_face_value`;
ALTER TABLE `invoice_rejected`  DROP COLUMN `actual_face_value`;
ALTER TABLE `invoice_repurchase`  DROP COLUMN `actual_face_value`;
ALTER TABLE `invoice_amendments`  DROP COLUMN `actual_face_value`;

ALTER TABLE `invoice_master`  DROP COLUMN `currency_rate`;
ALTER TABLE `invoice_amendments`  DROP COLUMN `currency_rate`;
ALTER TABLE `invoice_rejected`  DROP COLUMN `currency_rate`;
ALTER TABLE `invoice_repurchase`  DROP COLUMN `currency_rate`;

DROP TABLE `invoice_reserve`;

ALTER TABLE `invoice_master`  CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `root_invoice_id`,  CHANGE COLUMN `user_id` `user_id` SMALLINT(20) UNSIGNED NOT NULL AFTER `debtor_id`;
ALTER TABLE `invoice_repurchase`  CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `root_invoice_id`,  CHANGE COLUMN `user_id` `user_id` SMALLINT(20) UNSIGNED NOT NULL AFTER `debtor_id`;
ALTER TABLE `invoice_rejected`  CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `root_invoice_id`,  CHANGE COLUMN `user_id` `user_id` SMALLINT(20) UNSIGNED NOT NULL AFTER `debtor_id`;
ALTER TABLE `invoice_amendments`  CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `root_invoice_id`,  CHANGE COLUMN `user_id` `user_id` SMALLINT(20) UNSIGNED NOT NULL AFTER `debtor_id`;

ALTER TABLE `invoice_master`  CHANGE COLUMN `invoice_id` `invoice_id` MEDIUMINT(20) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `root_invoice_id` `root_invoice_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `invoice_no`;
ALTER TABLE `invoice_rejected`  CHANGE COLUMN `invoice_id` `invoice_id` MEDIUMINT(20) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `reject_id` `reject_id` MEDIUMINT(20) UNSIGNED NOT NULL AFTER `invoice_id`,  CHANGE COLUMN `root_invoice_id` `root_invoice_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `invoice_no`;
ALTER TABLE `invoice_repurchase`  CHANGE COLUMN `invoice_repurchase_id` `invoice_repurchase_id` MEDIUMINT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `invoice_id` `invoice_id` MEDIUMINT(20) UNSIGNED NOT NULL AFTER `invoice_repurchase_id`,  CHANGE COLUMN `root_invoice_id` `root_invoice_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `invoice_no`;
ALTER TABLE `invoice_amendments`  CHANGE COLUMN `amendment_id` `amendment_id` MEDIUMINT(20) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `invoice_id` `invoice_id` MEDIUMINT(20) UNSIGNED NOT NULL AFTER `amendment_id`,  CHANGE COLUMN `root_invoice_id` `root_invoice_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `invoice_no`;

ALTER TABLE `invoice_master`  CHANGE COLUMN `revolving` `revolving` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `document_value`;
ALTER TABLE `invoice_amendments`  CHANGE COLUMN `revolving` `revolving` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `document_value`;
ALTER TABLE `invoice_rejected`  CHANGE COLUMN `revolving` `revolving` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `document_value`;
ALTER TABLE `invoice_repurchase`  CHANGE COLUMN `revolving` `revolving` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `document_value`;

ALTER TABLE `invoice_master`  CHANGE COLUMN `invoice_status` `invoice_status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '1' COMMENT '0-Inactive:1-Published:2-frozen:3-Closed' AFTER `debx`,  CHANGE COLUMN `invoice_authorise` `invoice_authorise` TINYINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0-New:1-Contacted:2-Confirmed:3-Authorised' AFTER `revolving_status`,  CHANGE COLUMN `revolving_draft` `revolving_draft` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `instalment_draft`,  CHANGE COLUMN `posting_interface` `posting_interface` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `response_mail_status`,  CHANGE COLUMN `manual_transaction_id` `manual_transaction_id` MEDIUMINT(20) UNSIGNED NOT NULL AFTER `posting_interface`;
ALTER TABLE `invoice_amendments`  CHANGE COLUMN `invoice_status` `invoice_status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '1' COMMENT '0-Inactive:1-Published:2-frozen:3-Closed' AFTER `debx`,  CHANGE COLUMN `invoice_authorise` `invoice_authorise` TINYINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0-New:1-Contacted:2-Confirmed:3-Authorised' AFTER `revolving_status`,  CHANGE COLUMN `revolving_draft` `revolving_draft` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `instalment_draft`,  CHANGE COLUMN `posting_interface` `posting_interface` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `response_mail_status`,  CHANGE COLUMN `manual_transaction_id` `manual_transaction_id` MEDIUMINT(20) NOT NULL AFTER `posting_interface`;
ALTER TABLE `invoice_repurchase`  CHANGE COLUMN `invoice_status` `invoice_status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '1' COMMENT '0-Inactive:1-Published:2-frozen:3-Closed' AFTER `debx`,  CHANGE COLUMN `invoice_authorise` `invoice_authorise` TINYINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0-New:1-Contacted:2-Confirmed:3-Authorised' AFTER `revolving_status`,  CHANGE COLUMN `revolving_draft` `revolving_draft` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `instalment_draft`,  CHANGE COLUMN `posting_interface` `posting_interface` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `response_mail_status`,  CHANGE COLUMN `manual_transaction_id` `manual_transaction_id` MEDIUMINT(20) UNSIGNED NOT NULL AFTER `posting_interface`;
ALTER TABLE `invoice_rejected`  CHANGE COLUMN `posting_interface` `posting_interface` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `invoice_document_path`;


ALTER TABLE `invoice_master`  CHANGE COLUMN `drp_id` `drp_id` MEDIUMINT(20) UNSIGNED NOT NULL DEFAULT '0' AFTER `reserve`;


ALTER TABLE `invoice_master`  CHANGE COLUMN `noa_status` `noa_status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `etr_link`;
ALTER TABLE `invoice_repurchase`  CHANGE COLUMN `noa_status` `noa_status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `etr_link`;
ALTER TABLE `invoice_rejected`  CHANGE COLUMN `noa_status` `noa_status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `etr_link`;
ALTER TABLE `invoice_amendments`  CHANGE COLUMN `noa_status` `noa_status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `etr_link`;

ALTER TABLE `invoice_master`  CHANGE COLUMN `debx` `debx` TINYINT(5) UNSIGNED NOT NULL DEFAULT '2' COMMENT '1-Active:2-Deactive' AFTER `buy_max_thirty_day_reserve`;
ALTER TABLE `invoice_rejected`  CHANGE COLUMN `debx` `debx` TINYINT(5) UNSIGNED NOT NULL DEFAULT '2' COMMENT '1-Active:2-Deactive' AFTER `buy_max_thirty_day_reserve`;
ALTER TABLE `invoice_amendments`  CHANGE COLUMN `debx` `debx` TINYINT(5) UNSIGNED NOT NULL DEFAULT '2' COMMENT '1-Active:2-Deactive' AFTER `buy_max_thirty_day_reserve`;
ALTER TABLE `invoice_repurchase`  CHANGE COLUMN `debx` `debx` TINYINT(5) UNSIGNED NOT NULL DEFAULT '2' COMMENT '1-Active:2-Deactive' AFTER `buy_max_thirty_day_reserve`;


ALTER TABLE `invoice_master`  CHANGE COLUMN `modified_by` `modified_by` SMALLINT(20) UNSIGNED NOT NULL AFTER `modified_at`;

CREATE TABLE `currencies` (
    `currency_id` TINYINT UNSIGNED NULL AUTO_INCREMENT,
    `currency_code` VARCHAR(3) NULL DEFAULT '0',
    `currency_symbol` VARCHAR(3) NULL DEFAULT '0',
    `currency_rate` DECIMAL(5,4) UNSIGNED NULL DEFAULT '0',
    `is_active` TINYINT UNSIGNED NULL DEFAULT '0',
    PRIMARY KEY (`currency_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT;
INSERT INTO `currencies` (`currency_id`, `currency_code`, `currency_symbol`, `currency_rate`, `is_active`) VALUES (1, 'EUR', '€', 1.0000, 1);
INSERT INTO `currencies` (`currency_id`, `currency_code`, `currency_symbol`, `currency_rate`, `is_active`) VALUES (2, 'GBP', '£', 1.0000, 1);
INSERT INTO `currencies` (`currency_id`, `currency_code`, `currency_symbol`, `currency_rate`, `is_active`) VALUES (3, 'USD', '$', 1.0000, 1);

UPDATE currency_rates SET currency_id=3 WHERE currency_id=223;
UPDATE currency_rates SET currency_id=2 WHERE currency_id=222;

SELECT MAX(currency_date)
INTO @cur_date
FROM currency_rates;
UPDATE currencies
LEFT JOIN currency_rates ON currency_rates.currency_id = currencies.currency_id AND currency_rates.currency_date = @cur_date
SET currencies.currency_rate = currency_rates.currency_rate
WHERE currencies.currency_id IN (2,3);

ALTER TABLE `manual_transactions`  CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `transaction_type`,  CHANGE COLUMN `originator_id` `originator_id` SMALLINT(40) UNSIGNED NOT NULL AFTER `debtor_id`,  CHANGE COLUMN `currency_id` `currency_id` TINYINT(20) UNSIGNED NOT NULL AFTER `originator_id`;

ALTER TABLE `manual_transactions_rejected`  CHANGE COLUMN `trans_reject_id` `trans_reject_id` MEDIUMINT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `manual_transaction_id` `manual_transaction_id` MEDIUMINT(11) UNSIGNED NOT NULL AFTER `trans_reject_id`,  CHANGE COLUMN `transaction_type` `transaction_type` TINYINT(7) UNSIGNED NOT NULL COMMENT '' AFTER `manual_transaction_id`,  CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `transaction_type`,  CHANGE COLUMN `originator_id` `originator_id` SMALLINT(40) UNSIGNED NOT NULL AFTER `debtor_id`,  CHANGE COLUMN `currency_id` `currency_id` TINYINT(20) UNSIGNED NOT NULL AFTER `originator_id`;

ALTER TABLE `manual_transactions`  DROP COLUMN `fx_rate`,  DROP COLUMN `actual_fx_rate`;
ALTER TABLE `manual_transactions_rejected`  DROP COLUMN `fx_rate`,  DROP COLUMN `actual_fx_rate`;

ALTER TABLE `manual_transactions`  DROP COLUMN `parent_id`,  DROP COLUMN `prev_parent_id`;
ALTER TABLE `manual_transactions_rejected`  DROP COLUMN `parent_id`;

ALTER TABLE `manual_transactions`  DROP COLUMN `balance`,  DROP COLUMN `current_balance`;
ALTER TABLE `manual_transactions_rejected`  DROP COLUMN `balance`;

ALTER TABLE `manual_transactions`  DROP COLUMN `paid_balance`,  DROP COLUMN `processed_amount`;
ALTER TABLE `manual_transactions_rejected`  CHANGE COLUMN `status` `status` TINYINT(10) UNSIGNED NOT NULL COMMENT '' AFTER `reconcile_ref`,  DROP COLUMN `paid_balance`,  DROP COLUMN `processed_amount`;

ALTER TABLE `manual_transactions`  CHANGE COLUMN `reconcile_payment` `reconcile_payment` MEDIUMINT(10) UNSIGNED NOT NULL AFTER `transref_list`,  CHANGE COLUMN `payment_return` `payment_return` MEDIUMINT(10) UNSIGNED NOT NULL AFTER `reconcile_ref`,  CHANGE COLUMN `false_payment` `false_payment` MEDIUMINT(10) UNSIGNED NOT NULL AFTER `payment_return`,  CHANGE COLUMN `amendment_id` `amendment_id` MEDIUMINT(10) UNSIGNED NOT NULL AFTER `false_payment`;
ALTER TABLE `manual_transactions_rejected`  CHANGE COLUMN `reconcile_payment` `reconcile_payment` MEDIUMINT(10) UNSIGNED NOT NULL AFTER `transref_list`,  CHANGE COLUMN `reconcile_ref` `reconcile_ref` MEDIUMINT(10) UNSIGNED NOT NULL AFTER `reconcile_payment`;

UPDATE manual_transactions SET debtor_id = debtors;
ALTER TABLE `manual_transactions`  DROP COLUMN `debtors`,  DROP INDEX `Index 3`,  ADD INDEX `Index 3` (`debtor_id`);
ALTER TABLE `manual_transactions`  DROP INDEX `transaction_type`,  ADD INDEX `transaction_type` (`transaction_type`, `originator_id`, `amount`, `specific_allocation`, `status`);

ALTER TABLE `country_mas`  DROP COLUMN `currency_code`,  DROP COLUMN `currency_symbol`,  DROP COLUMN `rate`,  DROP COLUMN `currency_rate`,DROP COLUMN `default_value`,DROP INDEX `country_value`,  DROP INDEX `country_name`;

ALTER TABLE `debtors_detail`  CHANGE COLUMN `currency_country` `currency_id` TINYINT(3) UNSIGNED NOT NULL AFTER `contact_address_id`;

ALTER TABLE `debtors_detail`  CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(20) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `parent_id` `parent_id` MEDIUMINT(40) UNSIGNED NOT NULL DEFAULT '0' AFTER `founded`,CHANGE COLUMN `created_by` `created_by` TINYINT(10) UNSIGNED NOT NULL AFTER `currency_id`,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `modified_at`,CHANGE COLUMN `nace_id` `nace_id` SMALLINT(20) UNSIGNED NOT NULL DEFAULT '261' AFTER `debtor_company_registration_number`,  CHANGE COLUMN `nace_group_id` `nace_group_id` TINYINT(10) UNSIGNED NOT NULL DEFAULT '16' AFTER `nace_id`,CHANGE COLUMN `published` `published` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0-Waiting:1-Approved:2-Rejected:3-Expired' AFTER `modified_by`,  DROP INDEX `debtor_reference_id`,  ADD INDEX `debtor_reference_id` (`debtor_reference_id`, `nace_id`, `nace_group_id`, `debtor_name`, `trade_name`, `average_payment_cycle`, `no_credit_note_issued`, `avg_invoice_predicted_annum`, `avg_revenue_predicted_annum`, `debtor_risk`, `debtor_limit`, `parent_id`);

ALTER TABLE `categories`  CHANGE COLUMN `nace_id` `nace_id` SMALLINT(20) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(10) UNSIGNED NULL DEFAULT NULL AFTER `modified_at`,CHANGE COLUMN `nace_group_id` `nace_group_id` TINYINT(10) UNSIGNED NOT NULL AFTER `nace_group`;
ALTER TABLE `category_group`  CHANGE COLUMN `nace_group_id` `nace_group_id` TINYINT(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(10) UNSIGNED NULL DEFAULT NULL AFTER `modified_at`;

ALTER TABLE `organisation_currencies`  ADD COLUMN `currency_id` TINYINT UNSIGNED NULL DEFAULT '0' AFTER `currency_code`,  ADD INDEX `Index 2` (`user_id`, `currency_id`);
UPDATE organisation_currencies SET currency_id=3 WHERE currency_code="USD";
UPDATE organisation_currencies SET currency_id=2 WHERE currency_code="GBP";
UPDATE organisation_currencies SET currency_id=1 WHERE currency_code="EUR";

ALTER TABLE `bank_account`  CHANGE COLUMN `currency_id` `country_id` TINYINT(40) UNSIGNED NOT NULL,  ADD COLUMN `currency_id` TINYINT(40) UNSIGNED NOT NULL AFTER `country_id`;
ALTER TABLE `bank_account`  DROP COLUMN `currency`;
UPDATE bank_account SET bank_account.currency_id = 1 WHERE bank_account.country_id IN (104,241,44,14);
UPDATE bank_account SET bank_account.currency_id = 2 WHERE bank_account.country_id IN (222);
UPDATE bank_account SET bank_account.currency_id = 3 WHERE bank_account.country_id IN (223);

ALTER TABLE `bank_account`  DROP COLUMN `country_id`;
ALTER TABLE `bank_account`  CHANGE COLUMN `account_id` `account_id` TINYINT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `account_type` `account_type` TINYINT(2) UNSIGNED NOT NULL AFTER `names`;
ALTER TABLE `bank_account`  CHANGE COLUMN `address_id` `address_id` MEDIUMINT(40) UNSIGNED NOT NULL,  CHANGE COLUMN `status_type` `status_type` TINYINT(3) UNSIGNED NOT NULL COMMENT '1-Segregated Retail Investor Trust A/c,2-Segregated Clearing Trust A/c,3-Exchange Loss & Default Control A/c,4-Segregated Debtor Trust A/c,5-Exchange Intermediary & Agent A/c,6-Exchange Loss & Default Reserve A/c,7-Exchange Operations Deposit A/c' AFTER `address_id`,  CHANGE COLUMN `status` `status` TINYINT(2) UNSIGNED NOT NULL AFTER `status_type`,  CHANGE COLUMN `authorise` `authorise` TINYINT(2) UNSIGNED NOT NULL AFTER `status`,  CHANGE COLUMN `created_by` `created_by` TINYINT(20) UNSIGNED NOT NULL AFTER `modified_date`,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(20) UNSIGNED NOT NULL AFTER `created_by`;

UPDATE debtors_detail SET currency_id=1 WHERE currency_id IN (104,241,44,14);
UPDATE debtors_detail SET currency_id=2 WHERE currency_id=222;
UPDATE debtors_detail SET currency_id=3 WHERE currency_id=223;


ALTER TABLE `debtors_detail`  DROP INDEX `debtor_reference_id`,  ADD INDEX `debtor_reference_id` (`debtor_reference_id`, `nace_id`, `nace_group_id`, `debtor_name`, `trade_name`, `average_payment_cycle`, `no_credit_note_issued`, `avg_invoice_predicted_annum`, `avg_revenue_predicted_annum`, `debtor_risk`, `debtor_limit`, `parent_id`);
ALTER TABLE `debtors_detail`  ADD INDEX `currency_id` (`currency_id`);
ALTER TABLE `debtors_master`  ADD COLUMN `currency_id` TINYINT UNSIGNED NOT NULL AFTER `no_of_traders`,  ADD INDEX `currency_id` (`currency_id`);

UPDATE manual_transactions SET currency_id=1 WHERE currency_id IN (104,241,44,14);
UPDATE manual_transactions SET currency_id=2 WHERE currency_id=222;
UPDATE manual_transactions SET currency_id=3 WHERE currency_id=223;


ALTER TABLE `organisation_currency`  CHANGE COLUMN `country_id` `country_id` TINYINT(25) UNSIGNED NOT NULL AFTER `branch_id`,  ADD COLUMN `currency_id` TINYINT(25) UNSIGNED NOT NULL AFTER `country_id`;

ALTER TABLE `organisation_currency`  CHANGE COLUMN `created_at` `created_at` DATETIME NOT NULL AFTER `currency_bank_id`,  ADD COLUMN `is_main` TINYINT UNSIGNED NOT NULL DEFAULT '0' AFTER `created_at`;
ALTER TABLE `organisation_currency`  CHANGE COLUMN `org_currency_id` `org_currency_id` SMALLINT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST;

INSERT INTO organisation_currency (org_currency_id,organisation_id,branch_id,currency_id,country_id,bank_account_type,currency_bank_id,created_at,is_main)
SELECT NULL,organisation_details.organisation_id, 0,0, organisation_details.organisation_jurisdiction, 0,0,NOW(),1 
FROM organisation_details;
UPDATE organisation_currency SET currency_id=1 WHERE country_id IN (104,241,44,14);
UPDATE organisation_currency SET currency_id=2 WHERE country_id=222;
UPDATE organisation_currency SET currency_id=3 WHERE country_id=223;

ALTER TABLE `organisation_currency`  DROP COLUMN `country_id`,  DROP INDEX `organisation_id`,  ADD INDEX `organisation_id` (`organisation_id`, `branch_id`, `bank_account_type`, `currency_bank_id`, `currency_id`);

ALTER TABLE `organisation_currency`  CHANGE COLUMN `organisation_id` `organisation_id` SMALLINT(25) UNSIGNED NOT NULL AFTER `org_currency_id`;

ALTER TABLE `debtors_master`  DROP COLUMN `currency_id`,  DROP INDEX `currency_id`;

ALTER TABLE `risk_credit`
    CHANGE COLUMN `risk_credit_id` `risk_credit_id` SMALLINT(20) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    CHANGE COLUMN `user_id` `user_id` SMALLINT(20) UNSIGNED NOT NULL AFTER `risk_credit_id`,
    CHANGE COLUMN `organisation_id` `organisation_id` SMALLINT(20) UNSIGNED NOT NULL AFTER `user_id`,
    CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(20) UNSIGNED NOT NULL AFTER `organisation_id`,
    CHANGE COLUMN `provider1_credit_limit_modified_by` `provider1_credit_limit_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider1_credit_limit_modified_at`,
    CHANGE COLUMN `provider1_risk_score_modified_by` `provider1_risk_score_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider1_risk_score_modified_at`,
    CHANGE COLUMN `provider2_credit_limit_modified_by` `provider2_credit_limit_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider2_credit_limit_modified_at`,
    CHANGE COLUMN `provider2_risk_score_modified_by` `provider2_risk_score_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider2_risk_score_modified_at`,
    CHANGE COLUMN `provider3_credit_limit_modified_by` `provider3_credit_limit_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider3_credit_limit_modified_at`,
    CHANGE COLUMN `provider3_risk_score_modified_by` `provider3_risk_score_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider3_risk_score_modified_at`,
    CHANGE COLUMN `provider4_credit_limit_modified_by` `provider4_credit_limit_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider4_credit_limit_modified_at`,
    CHANGE COLUMN `provider4_risk_score_modified_by` `provider4_risk_score_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider4_risk_score_modified_at`,
    CHANGE COLUMN `provider5_credit_limit_modified_by` `provider5_credit_limit_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider5_credit_limit_modified_at`,
    CHANGE COLUMN `provider5_risk_score_modified_by` `provider5_risk_score_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider5_risk_score_modified_at`,
    CHANGE COLUMN `provider6_credit_limit_modified_by` `provider6_credit_limit_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider6_credit_limit_modified_at`,
    CHANGE COLUMN `provider6_risk_score_modified_by` `provider6_risk_score_modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `provider6_risk_score_modified_at`;
    
ALTER TABLE `risk_credit` DROP INDEX `user_id`, ADD INDEX `user_id` (`user_id`, `organisation_id`, `debtor_id`);
ALTER TABLE `risk_credit`  DROP INDEX `provider1_risk_score_modified_by`,  DROP INDEX `modified_by`,  DROP INDEX `provider2_credit_limit_modified_by`,  DROP INDEX `provider2_risk_score_modified_by`;
ALTER TABLE `risk_credit`  DROP INDEX `user_id`,  ADD INDEX `user_id` (`user_id`, `organisation_id`),  ADD INDEX `debtor_id` (`debtor_id`);

ALTER TABLE `debtor_relation`  CHANGE COLUMN `relation_id` `relation_id` MEDIUMINT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `trader_id` `trader_id` SMALLINT(40) UNSIGNED NOT NULL AFTER `relation_id`,  CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `trader_id`,  CHANGE COLUMN `organisation_id` `organisation_id` SMALLINT(40) UNSIGNED NOT NULL AFTER `debtor_id`,  CHANGE COLUMN `status` `status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER `child_id`,  CHANGE COLUMN `revolving_etr` `revolving_etr` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `invt_status`,  CHANGE COLUMN `ed_status` `ed_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '0-false,1-true' AFTER `revolving_etr`,  CHANGE COLUMN `bcc_status` `bcc_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '0-false,1-true' AFTER `ed_status`,  CHANGE COLUMN `changed_status` `changed_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '2-notified,6-rate' AFTER `noid_notified`,  CHANGE COLUMN `noid_status` `noid_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '1-Issued,2-Approved,3-Rejected' AFTER `changed_status`;
ALTER TABLE `debtor_relation`  ADD INDEX `debtor_id` (`debtor_id`);

ALTER TABLE `address_details`  CHANGE COLUMN `address_id` `address_id` INT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `address_country` `address_country` TINYINT(25) UNSIGNED NOT NULL AFTER `address_state`,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(20) UNSIGNED NOT NULL AFTER `modified_at`;
ALTER TABLE `debtor_bank_details`  CHANGE COLUMN `debtor_bank_id` `debtor_bank_id` MEDIUMINT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `master_debtor_id` `master_debtor_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `debtor_bank_id`,  CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `master_debtor_id`;
ALTER TABLE `debtor_settings`  CHANGE COLUMN `debtor_setting_id` `debtor_setting_id` MEDIUMINT(20) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `debtor_id` `debtor_id` MEDIUMINT(10) UNSIGNED NOT NULL AFTER `debtor_setting_id`,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(20) UNSIGNED NOT NULL AFTER `modified_at`;
ALTER TABLE `help_tips_settings`  CHANGE COLUMN `help_tip_id` `help_tip_id` SMALLINT(50) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `help_title_id` `help_title_id` TINYINT(50) UNSIGNED NOT NULL AFTER `help_tip_id`,  CHANGE COLUMN `ordering` `ordering` TINYINT(10) UNSIGNED NOT NULL AFTER `help_key`,  CHANGE COLUMN `status` `status` TINYINT(2) UNSIGNED NOT NULL COMMENT '0-Inactive,1-Active' AFTER `ordering`;
ALTER TABLE `help_title_settings`  CHANGE COLUMN `help_title_id` `help_title_id` TINYINT(50) UNSIGNED NOT NULL AUTO_INCREMENT FIRST;
ALTER TABLE `labels_settings`  CHANGE COLUMN `label_id` `label_id` SMALLINT(20) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `label_type` `label_type` TINYINT(10) UNSIGNED NOT NULL AFTER `label_id`,  CHANGE COLUMN `label_added_by` `label_added_by` TINYINT(10) UNSIGNED NOT NULL AFTER `label_added_on`,  CHANGE COLUMN `ordering` `ordering` TINYINT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `label_added_by`,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `modified_at`;
ALTER TABLE `label_type_settings`  CHANGE COLUMN `label_type_id` `label_type_id` TINYINT(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `label_type_status` `label_type_status` TINYINT(2) UNSIGNED NOT NULL COMMENT '1-active,2-inactive' AFTER `label_type`,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `label_type_status`;
ALTER TABLE `mail_management`  CHANGE COLUMN `mail_id` `mail_id` TINYINT(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `mail_type` `mail_type` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AFTER `mail_subject`,  CHANGE COLUMN `mail_status` `mail_status` TINYINT(3) UNSIGNED NOT NULL AFTER `mail_content_text`,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(10) UNSIGNED NOT NULL AFTER `modified_at`;
ALTER TABLE `manual_transactions_invoice`  CHANGE COLUMN `t_id` `t_id` INT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `transaction_id` `transaction_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `t_id`,  CHANGE COLUMN `invoice_id` `invoice_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `transaction_id`,  CHANGE COLUMN `allocation` `allocation` TINYINT(11) UNSIGNED NOT NULL COMMENT '1-Reconcile,0-Non Reconcile' AFTER `invoice_id`,  CHANGE COLUMN `ra_status` `ra_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '0 - not reconcile, 1 - reconcile' AFTER `reserve_amount`,  CHANGE COLUMN `trailing_status` `trailing_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '0-not with specific allocation,1-with specific allocation' AFTER `ra_status`;
ALTER TABLE `organisation_bank_account_details`  CHANGE COLUMN `account_id` `account_id` SMALLINT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `organisation_id` `organisation_id` MEDIUMINT(40) UNSIGNED NOT NULL AFTER `retailer_id`;
ALTER TABLE `organisation_details`  CHANGE COLUMN `organisation_id` `organisation_id` SMALLINT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `user_id` `user_id` SMALLINT(20) UNSIGNED NULL DEFAULT NULL AFTER `organisation_id`,  CHANGE COLUMN `nace_group_id` `nace_group_id` TINYINT(10) UNSIGNED NOT NULL AFTER `reserve`,  CHANGE COLUMN `nace_id` `nace_id` SMALLINT(20) UNSIGNED NOT NULL AFTER `nace_group_id`,  CHANGE COLUMN `business_entity_type` `business_entity_type` TINYINT(10) UNSIGNED NULL DEFAULT NULL AFTER `parent_company_name`,  CHANGE COLUMN `business_function_type` `business_function_type` TINYINT(10) UNSIGNED NULL DEFAULT NULL AFTER `business_entity_type`,  CHANGE COLUMN `active_debtors` `active_debtors` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '2' AFTER `organisation_limit`,  CHANGE COLUMN `submitter_legally_approved` `submitter_legally_approved` TINYINT(3) UNSIGNED NOT NULL COMMENT '1-Yes,2-No' AFTER `submitted_date`,  CHANGE COLUMN `application_status` `application_status` TINYINT(50) UNSIGNED NOT NULL COMMENT '0-Waiting for Review ,1-Approved,2-Rejected' AFTER `submitter_legally_approved`,  CHANGE COLUMN `organisation_status` `organisation_status` TINYINT(10) UNSIGNED NOT NULL COMMENT '1-Apply,2-Applied,3-Approve,4-Rate or Bank or Active,5-Lodged or Notified,6-Authorised,7-Suspended,8-Terminated' AFTER `application_step`,  CHANGE COLUMN `organisation_confirm` `organisation_confirm` TINYINT(2) UNSIGNED NOT NULL COMMENT '0-Off,1-On' AFTER `organisation_status`,  CHANGE COLUMN `org_interface` `org_interface` TINYINT(2) UNSIGNED NOT NULL COMMENT '0-User,1-Admin' AFTER `organisation_confirm`,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(20) UNSIGNED NOT NULL AFTER `modified_at`,  CHANGE COLUMN `created_by` `created_by` TINYINT(20) UNSIGNED NOT NULL AFTER `created_at`,  CHANGE COLUMN `number_of_directors` `number_of_directors` SMALLINT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER `created_by`;
ALTER TABLE `organisation_documents`  CHANGE COLUMN `organization_document_id` `organization_document_id` SMALLINT(20) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `organisation_id` `organisation_id` SMALLINT(20) UNSIGNED NOT NULL AFTER `organization_document_id`,  CHANGE COLUMN `modified_by` `modified_by` TINYINT(20) UNSIGNED NOT NULL AFTER `modified_at`;

ALTER TABLE `temp_registration`  ADD COLUMN `currency_id` TINYINT UNSIGNED NOT NULL AFTER `modified_at`;

ALTER TABLE `organisation_bank_details`  CHANGE COLUMN `organisation_bank_id` `organisation_bank_id` MEDIUMINT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,  CHANGE COLUMN `organisation_id` `organisation_id` SMALLINT(40) UNSIGNED NOT NULL AFTER `organisation_bank_id`,  CHANGE COLUMN `retailer_id` `retailer_id` SMALLINT(40) UNSIGNED NULL DEFAULT NULL AFTER `organisation_id`,  ADD COLUMN `currency_id` TINYINT UNSIGNED NOT NULL AFTER `contact_id`;

ALTER TABLE `address_details`  CHANGE COLUMN `address_id` `address_id` MEDIUMINT(40) UNSIGNED NOT NULL AUTO_INCREMENT FIRST;
ALTER TABLE `debtors_detail`  CHANGE COLUMN `address_id` `address_id` MEDIUMINT(11) UNSIGNED NULL DEFAULT NULL AFTER `filescv`,  CHANGE COLUMN `billing_address_id` `billing_address_id` MEDIUMINT(11) UNSIGNED NULL DEFAULT NULL AFTER `address_id`,  CHANGE COLUMN `contact_address_id` `contact_address_id` MEDIUMINT(11) UNSIGNED NULL DEFAULT NULL AFTER `billing_address_id`;