ALTER TABLE  `_borrowers` 
CHANGE  `Office/Branch`  `Office/Branch` VARCHAR( 14 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Client Account No.`  `Client Account No.` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Client`  `Client` VARCHAR( 28 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Client Status`  `Client Status` VARCHAR( 6 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Loan Officer`  `Loan Officer` VARCHAR( 15 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `External Id`  `External Id` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_borrowers` 
CHANGE  `Loan`  `Loan` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Status`  `Status` VARCHAR( 31 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Fund`  `Fund` VARCHAR( 6 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Loan Purpose`  `Loan Purpose` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Currency`  `Currency` VARCHAR( 3 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_borrowers` 
CHANGE  `Arrears Tolerance Amount`  `Arrears Tolerance Amount` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Interest Rate Frequency`  `Interest Rate Frequency` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Interest Method`  `Interest Method` VARCHAR( 4 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Interest Calculated in Period`  `Interest Calculated in Period` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Term Frequency Period`  `Term Frequency Period` VARCHAR( 4 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Repayment Frequency Period`  `Repayment Frequency Period` VARCHAR( 4 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Amortization`  `Amortization` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_borrowers` 
CHANGE  `Submitted`  `Submitted` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Approved`  `Approved` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Expected Disbursal`  `Expected Disbursal` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Expected First Repayment`  `Expected First Repayment` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Interest Calculated From`  `Interest Calculated From` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Disbursed`  `Disbursed` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_borrowers` CHANGE  `Expected Maturity`  `Expected Maturity` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Matured On`  `Matured On` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Closed`  `Closed` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Rejected`  `Rejected` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Rescheduled`  `Rescheduled` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Withdrawn`  `Withdrawn` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Written Off`  `Written Off` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_borrowers` CHANGE  `Client Account No.`  `Client Account No.` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

insert into borrowers(name) SELECT `Client` FROM _borrowers;

INSERT INTO ledgers(name, borrower_id, ledger_type_id) SELECT name, id, '1' FROM borrowers WHERE ledger_id = '0';
UPDATE borrowers SET ledger_id = (SELECT id FROM ledgers WHERE borrower_id = borrowers.id) WHERE ledger_id = '0';

INSERT IGNORE INTO books(name, Bank) SELECT `Fund`, '1' FROM _borrowers WHERE `Fund` != '';
insert into ledgers(name, ledger_type_id, book_id) select name, '4', id FROM books where name != 'Cash Book' AND name != 'Bank Book';

insert into applications(date, account_number, borrower_id, amount, vt_user_id) 
SELECT `Expected Disbursal`, `Client Account No.`, (SELECT id FROM borrowers WHERE name = b.Client ORDER BY id LIMIT 1), `Principal`, '1' FROM _borrowers b;


insert into disbursements(date, account_number, application_id, amount, book_id, installment_count, interest_rate, interest_frequency_id, interest_calculation_method_id, payment_frequency_id)
SELECT `Expected Disbursal`, `Client Account No.`, (SELECT id FROM applications WHERE account_number = b.`Client Account No.`), `Principal`, (SELECT id FROM books WHERE name = b.`Fund`), `Expected No. Repayments`, `Nominal Interest Rate Per Period`, (SELECT id FROM time_frequencies WHERE name = 'annually'), '1', CASE WHEN CONCAT(`Repayment Frequency`, `Repayment Frequency Period`) = '30Days' THEN '4' WHEN CONCAT(`Repayment Frequency`, `Repayment Frequency Period`) = '15Days' THEN '3' END as test FROM _borrowers b;