ALTER TABLE  `_client_data_list` 
CHANGE  `Language Spoken`  `Language Spoken` VARCHAR( 7 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `App Date`  `App Date` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Loan Acc No.`  `Loan Acc No.` VARCHAR( 17 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Date Borrowed`  `Date Borrowed` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Borrower Name`  `Borrower Name` VARCHAR( 34 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `NRIC`  `NRIC` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Address`  `Address` VARCHAR( 47 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `DOB`  `DOB` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_client_data_list` 
CHANGE  `Company`  `Company` VARCHAR( 43 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Company Address`  `Company Address` VARCHAR( 70 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Occupation`  `Occupation` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Work Tel No`  `Work Tel No` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Surity Name`  `Surity Name` VARCHAR( 46 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Surity NRIC`  `Surity NRIC` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Surity Address`  `Surity Address` VARCHAR( 45 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Surity DOB`  `Surity DOB` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_client_data_list` 
CHANGE  `Surity Company`  `Surity Company` VARCHAR( 55 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Surity Company Address`  `Surity Company Address` VARCHAR( 53 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Surity Occupation`  `Surity Occupation` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Surity Salary`  `Surity Salary` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;




ALTER TABLE  `borrowers` DROP INDEX  `uid`;

insert into borrowers(name, uid, address1, annual_income, hand_phone, home_phone, work_phone, company, designation, dob) 
SELECT `Borrower Name`, NRIC, Address, Salary, `Hp No`, `Tel No`, `Work Tel No`, Company, Occupation, DATE_ADD('1900-01-01', INTERVAL DOB-2 DAY) FROM _client_data_list;


insert into borrowers(name, uid, address1, annual_income, hand_phone, home_phone, work_phone, company, designation, dob) 
SELECT `Surity Name`, `Surity NRIC`, `Surity Address`, `Surity Salary`, `Surity Hp No`, `Surity Tel No`, `Surity Work Tel No`, `Surity Company`, `Surity Occupation`, DATE_ADD('1900-01-01', INTERVAL `Surity DOB`-2 DAY) FROM _client_data_list WHERE `Surity Name` IS NOT NULL;



insert into applications(date, account_number, borrower_id, new_surety_id, amount, vt_user_id) 
SELECT DATE_ADD('1900-01-01', INTERVAL `App Date`-2 DAY), `Loan Acc No.`, (SELECT id FROM borrowers WHERE uid = `NRIC` ORDER BY id DESC LIMIT 1), (SELECT id FROM borrowers WHERE uid = `Surity NRIC` ORDER BY id DESC LIMIT 1), `Loan Amt`, '1' FROM _client_data_list;

insert into disbursements(application_id, date, account_number, amount, interest_rate, interest_frequency_id, interest_calculation_method_id, installment_count, payment_frequency_id, book_id, book_amount, payment_type_id, borrower_annual_income, status)
SELECT (SELECT id FROM applications WHERE account_number = `Loan Acc No.`), DATE_ADD('1900-01-01', INTERVAL `Date Borrowed`-2 DAY), `Loan Acc No.`, `Loan Amt`, `Monthly Interest Rate`, (SELECT id FROM time_frequencies WHERE name = 'monthly'), '1', `Term`, (SELECT id FROM time_frequencies WHERE name = 'monthly'), (SELECT id FROM books WHERE name = 'Cash Book'), `Loan Amt`, (SELECT id FROM payment_types WHERE name = 'Cash'), Salary, '0' FROM _client_data_list;


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

update borrowers set borrower_type_id = '1' where borrower_type_id = '';

update disbursements set first_payment_date = DATE_ADD(date, interval 1 month);

update applications set loan_type_id = '1' where loan_type_id = '';

ALTER TABLE  `borrowers` ADD UNIQUE (`borrower_type_id` , `uid`);

-- Notes:
-- 1. first payment date = 1 month after loan date
-- TERM = number of installments
-- All loans are unsecured
-- All borrowers are individuals
-- Late interest rates have not been migrated since our system has the same frequency for I and LI where as AK's excel export has monthly I rate but Daily LI rate.
