delete from _loans WHERE `Business Model` = '' AND `Date of Loan` = '' AND `Name of Borrower` = '';
delete from _repayments WHERE `Date of Receipt` = '' AND `Loan Account No.` = '' AND `Total amount paid` = '0';
delete from _repayments WHERE `Date of Receipt` IS NULL AND `Loan Account No.` IS NULL AND `Total amount paid` <= 0;

ALTER TABLE `_loans` 
CHANGE `No.` `No.` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `Loan Account No` `Loan Account No` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `Date of Loan` `Date of Loan` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `Name of Borrower` `Name of Borrower` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `Identity Card Type` `Identity Card Type` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `Identity Card / Passport No of Borrower` `Identity Card / Passport No of Borrower` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `Nationality` `Nationality` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_loans` 
CHANGE  `Address  - Block Number`  `block` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Address  - Level Number`  `level` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Address  - Unit Number`  `unit` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Address  - Street Name`  `street` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Address  - Building Name`  `building` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Address  - Postal Code`  `postalcode` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Un-Formatted Address of Borrower`  `address1` VARCHAR( 1024 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_loans` CHANGE  `Payment Term`  `Payment Term` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_loans` 
CHANGE  `Purpose of Loan`  `Purpose of Loan` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Type of loan`  `Type of loan` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Mobile`  `Mobile` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Home`  `Home` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Office`  `Office` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_loans` ADD INDEX (  `Loan Account No` );

ALTER TABLE  `_repayments` 
CHANGE  `Date of Receipt`  `Date of Receipt` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `No`  `No` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Loan Account No.`  `Loan Account No.` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Mode of Payment`  `Mode of Payment` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Cheque No`  `Cheque No` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Total amount paid`  `Total amount paid` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_repayments` 
CHANGE  `Amount paid towards principal`  `Amount paid towards principal` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Amount paid towards interest including late interest`  `Amount paid towards interest including late interest` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Amount paid towards permitted fees including late fees`  `Amount paid towards permitted fees including late fees` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Admin Fee`  `Admin Fee` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Legal Fee`  `Legal Fee` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_repayments` 
CHANGE  `Overdue and Outstanding principal`  `Overdue and Outstanding principal` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Business Model`  `Business Model` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_repayments` CHANGE  `Official Receipt No.`  `Official Receipt No.` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;


update _loans set `Identity Card Type` = 'Singapore NRIC No.' where `Identity Card Type` = 'Singapore NRIC No';
update _loans set `Identity Card Type` = 'Singapore PR No.' where `Identity Card Type` = 'Singapore PR No';
update _loans set `Payment Term` = 'bi-weekly' where `Payment Term` = 'Fortnightly';
update _loans set `Payment Term` = 'daily' where `Payment Term` = 'Daily';
update _loans set `Payment Term` = 'weekly' where `Payment Term` = 'Weekly';
update _loans set `Payment Term` = 'monthly' where `Payment Term` = 'Monthly';

update _loans set `Identity Card Type` = '' where `Identity Card Type` IS NULL;
update _loans set block = '' where block IS NULL;
update _loans set level = '' where level IS NULL;
update _loans set unit = '' where unit IS NULL;
update _loans set street = '' where street IS NULL;
update _loans set building = '' where building IS NULL;
update _loans set postalcode = '' where postalcode IS NULL;
update _loans set Mobile = '' where Mobile IS NULL;
update _loans set Home = '' where Home IS NULL;
update _loans set Office = '' where Office IS NULL;
update _loans set `Name of Surety` = '' where `Name of Surety` IS NULL;
update _loans set `Identity Card / Passport No. of Borrower` = '' where `Identity Card / Passport No. of Borrower` IS NULL;
update _loans set `Contact No` = '' where `Contact No` IS NULL;

update _loans set `Type of loan` = 'Unsecured' where `Type of loan` = 'U';

insert into borrowers(name, uid, identification_type_id, country_id, blk, level, unit, street, address1, building, pincode, annual_income, hand_phone, home_phone, work_phone) 
SELECT `Name of Borrower`, `Identity Card / Passport No of Borrower`, IFNULL((SELECT id FROM identification_types WHERE name = `Identity Card Type`), 0), IFNULL((SELECT id FROM countries WHERE name = `Nationality`), 0), l.block, l.level, l.unit, l.street, l.address1, l.building, l.postalcode, REPLACE(`Annual Income Amount`, ',', ''), Mobile, Home, Office
FROM _loans as l 
ON duplicate key update name = `Name of Borrower`, uid = `Identity Card / Passport No of Borrower`, identification_type_id = IFNULL((SELECT id FROM identification_types WHERE name = `Identity Card Type`), 0), country_id = IFNULL((SELECT id FROM countries WHERE name = `Nationality`), 0), blk = l.block, level = l.level, unit = l.unit, street = l.street, address1 = l.address1, building = l.building, pincode = l.postalcode, annual_income = REPLACE(`Annual Income Amount`, ',', ''), hand_phone = Mobile, home_phone = Home, work_phone = Office;

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

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 into applications(date, account_number, borrower_id, amount, loan_type_id, loan_purpose_id, vt_user_id) 
SELECT concat(right(trim(`Date of Loan`), 4), '-', substring(trim(`Date of Loan`), 4, 2), '-', left(trim(`Date of Loan`), 2)), `Loan Account No`, (SELECT id FROM borrowers WHERE uid = `Identity Card / Passport No of Borrower`), REPLACE(`Loan Amount`, ',', ''), (SELECT id FROM loan_types WHERE name = `Type of loan`), (SELECT id FROM loan_purposes WHERE name = `Purpose of Loan`), '1' FROM _loans;


insert into disbursements(date, application_id, 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 ap.date, ap.id, ap.account_number, ap.amount, REPLACE(`Rate of interest/annum`, '%', ''), '5', '1', `No. of Instalments`, (SELECT id FROM time_frequencies WHERE name = `Payment Term`), (SELECT id FROM books WHERE name = 'Cash Book'), ap.amount, (SELECT id FROM payment_types WHERE name = 'Cash'), REPLACE(`Annual Income Amount`, ',', ''), '0'
FROM applications ap LEFT JOIN _loans l ON ap.account_number = l.`Loan Account No`;

update disbursements set first_payment_date = date + interval 1 month where payment_frequency_id = (SELECT id FROM time_frequencies WHERE name = 'monthly');
update disbursements set first_payment_date = date + interval 1 week where payment_frequency_id = (SELECT id FROM time_frequencies WHERE name = 'weekly');
update disbursements set first_payment_date = date + interval 2 week where payment_frequency_id = (SELECT id FROM time_frequencies WHERE name = 'bi-weekly');
update disbursements set first_payment_date = date + interval 1 day where payment_frequency_id = (SELECT id FROM time_frequencies WHERE name = 'daily');


update _repayments  set `Mode of Payment` = replace(`Mode of Payment`, ' (A)', ' Book');
update _repayments  set `Mode of Payment` = replace(`Mode of Payment`, ' (B)', ' Book');
update _repayments  set `Mode of Payment` = replace(`Mode of Payment`, ' (C)', ' Book');
update _repayments  set `Mode of Payment` = replace(`Mode of Payment`, 'Interbank Transfer - ', '');
update _repayments  set `Mode of Payment` = replace(`Mode of Payment`, 'DBS', 'DBS Book');
update _repayments  set `Mode of Payment` = replace(`Mode of Payment`, 'POSB', 'POSB Book');
update _repayments  set `Mode of Payment` = replace(`Mode of Payment`, 'UOB', 'UOB Book');


-- select * FROM _repayments r LEFT JOIN disbursements d ON d.account_number = r.`Loan Account No.` WHERE d.id IS NULL;
insert into repayments(date, disbursement_id, application_id, account_number, amount, principal, interest, receipt_number, permit_fee, legal_fee, book_id)
select concat(right(trim(`Date of Receipt`), 4), '-', substring(trim(`Date of Receipt`), 4, 2), '-', left(trim(`Date of Receipt`), 2)) as date, (SELECT id FROM disbursements WHERE account_number = `Loan Account No.`) as disbursement_id, (SELECT id FROM applications WHERE account_number = `Loan Account No.`) as application_id, `Loan Account No.`, REPLACE(`Total amount paid`, ',', '') as amount, REPLACE(`Amount paid towards principal`, ',', '') as principal , REPLACE(`Amount paid towards interest including late interest`, ',', '') as interest, `Official Receipt No.`, REPLACE(`Amount paid towards permitted fees including late fees`, ',', '') as permit_fee, `Legal Fee`, (SELECT id FROM books WHERE name = `Mode of Payment`) as book_id FROM _repayments;




update repayments r left join (SELECT r1.id as pk, d.amount - SUM(r2.principal) as amount FROM disbursements d LEFT JOIN repayments as r1 ON d.id = r1.disbursement_id LEFT JOIN repayments as r2 ON r1.disbursement_id = r2.disbursement_id WHERE r2.date <= r1.date GROUP BY r1.id ORDER BY r1.id) as tx on r.id = tx.pk set r.outstanding_principal = tx.amount WHERE tx.amount != r.outstanding_principal;


update disbursements d set status = '1' WHERE d.status = '0' and d.amount = (SELECT SUM(principal) FROM repayments WHERE disbursement_id = d.id AND cheque_dishonour != '1');
