-- notes
-- bad debts reinstate, _client_employment_data, _income_pa_data, _client_address_data, nationality is assumed to not exist for older systems


truncate table vouchers;
truncate table repayments;
truncate table disbursements;
truncate table applications;
truncate table borrowers;
truncate table blacklist_reasons;
delete from ledgers where borrower_id != '0';
delete from ledgers where book_id != '0' and name NOT IN('Cash Book', 'Bank Book', 'Bad Debt Book');

CREATE TABLE IF NOT EXISTS `_bad_debts_data` (
  `#` int(3) DEFAULT NULL,
  `LOAN NUMBER` varchar(9) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `BAD DEBT REMARKS` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `BAD DEBT DATE` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE  `_client_data` ADD INDEX (  `NRIC` );
ALTER TABLE  `_loan_data` ADD INDEX (  `LOAN NUMBER` );
ALTER TABLE  `_paid_repayment_data` ADD INDEX (  `LOAN NUMBER` );

ALTER TABLE  `_bad_debts_data` 
CHANGE  `LOAN NUMBER`  `LOAN NUMBER` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `BAD DEBT REMARKS`  `BAD DEBT REMARKS` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `BAD DEBT DATE`  `BAD DEBT DATE` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE `_loan_data` 
CHANGE `LOAN NUMBER` `LOAN NUMBER` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `BORROWER` `BORROWER` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `SURETY` `SURETY` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `PAYMENT MODE` `PAYMENT MODE` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `VOUCHER NO.` `VOUCHER NO.` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `TERM` `TERM` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `PACKAGE` `PACKAGE` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `PURPOSE OF LOAN` `PURPOSE OF LOAN` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `LOAN REMARK` `LOAN REMARK` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE `AGENT CODE` `AGENT CODE` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_loan_data` CHANGE  `LOAN DATE`  `LOAN DATE` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

ALTER TABLE  `_paid_repayment_data` 
CHANGE  `LOAN NUMBER`  `LOAN NUMBER` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `REPAYMENT DUE DATE`  `REPAYMENT DUE DATE` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `PAYMENT MODE`  `PAYMENT MODE` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `REPAYMENT REMARKS`  `REPAYMENT REMARKS` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_unpaid_repayment_data` 
CHANGE  `LOAN NUMBER`  `LOAN NUMBER` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `PAYMENT MODE`  `PAYMENT MODE` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `REPAYMENT REMARKS`  `REPAYMENT REMARKS` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

-- remove commas from decimals
update _loan_data SET `LOAN AMOUNT ($)` = REPLACE(`LOAN AMOUNT ($)`, ',', '');
update _loan_data SET `TERM` = REPLACE(`TERM`, ',', '');
update _loan_data SET `INTEREST (%)` = REPLACE(`INTEREST (%)`, ',', '');
update _loan_data SET `ACCEPTANCE FEE ($)` = REPLACE(`ACCEPTANCE FEE ($)`, ',', '');
update _loan_data SET `DEFFERED PAYMENT FEE ($)` = REPLACE(`DEFFERED PAYMENT FEE ($)`, ',', '');
update _loan_data SET `LATE REPAYMENT FEE ($)` = REPLACE(`LATE REPAYMENT FEE ($)`, ',', '');
update _loan_data SET `LATE INTEREST RATE P.A. (%)` = REPLACE(`LATE INTEREST RATE P.A. (%)`, ',', '');
update _loan_data SET `LATE FEE ($)` = REPLACE(`LATE FEE ($)`, ',', '');
update _loan_data SET `VARIATION FEE ($)` = REPLACE(`VARIATION FEE ($)`, ',', '');

update _client_data SET `Income p.A` = REPLACE(`Income p.A`, ',', '');

update `_paid_repayment_data` SET `TOTAL AMOUNT PAID` = REPLACE(`TOTAL AMOUNT PAID`, ',', '');
update `_paid_repayment_data` SET `PRINCIPAL` = REPLACE(`PRINCIPAL`, ',', '');
update `_paid_repayment_data` SET `INTEREST` = REPLACE(`INTEREST`, ',', '');
update `_paid_repayment_data` SET `OTHER PERMIT` = REPLACE(`OTHER PERMIT`, ',', '');

update `_unpaid_repayment_data` SET `TOTAL AMOUNT PAID` = REPLACE(`TOTAL AMOUNT PAID`, ',', '');
update `_unpaid_repayment_data` SET `PRINCIPAL` = REPLACE(`PRINCIPAL`, ',', '');
update `_unpaid_repayment_data` SET `INTEREST` = REPLACE(`INTEREST`, ',', '');
update `_unpaid_repayment_data` SET `OTHER PERMIT` = REPLACE(`OTHER PERMIT`, ',', '');


ALTER TABLE `_loan_data` 
CHANGE `LOAN AMOUNT ($)` `LOAN AMOUNT` DECIMAL(10,2) NULL DEFAULT NULL, 
CHANGE `INTEREST (%)` `INTEREST` DECIMAL(10,2) NULL DEFAULT NULL, 
CHANGE `ACCEPTANCE FEE ($)` `ACCEPTANCE FEE` DECIMAL(10,2) NULL DEFAULT NULL, 
CHANGE `LATE REPAYMENT FEE ($)` `LATE REPAYMENT FEE` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
CHANGE `LATE INTEREST RATE P.A. (%)` `LATE INTEREST RATE P.A.` DECIMAL(10,2) NULL DEFAULT NULL, 
CHANGE `LATE FEE ($)` `LATE FEE` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
CHANGE `VARIATION FEE ($)` `VARIATION FEE` DECIMAL(10,2) NULL DEFAULT NULL;

delete from _loan_data WHERE `LOAN AMOUNT` = 0 OR `BORROWER` = '';
delete urd.* from _unpaid_repayment_data urd LEFT JOIN _loan_data sld ON urd.`LOAN NUMBER` = sld.`LOAN NUMBER` WHERE sld.`LOAN NUMBER` IS NULL;
delete prd.* from _paid_repayment_data prd LEFT JOIN _loan_data sld ON prd.`LOAN NUMBER` = sld.`LOAN NUMBER` WHERE sld.`LOAN NUMBER` IS NULL;

UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Jan ', '/01/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Feb ', '/02/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Mar ', '/03/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Apr ', '/04/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' May ', '/05/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Jun ', '/06/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Jul ', '/07/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Aug ', '/08/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Sep ', '/09/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Oct ', '/10/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Nov ', '/11/');
UPDATE _client_data SET `Date of Birth` = REPLACE(`Date of Birth`, ' Dec ', '/12/');

ALTER TABLE  `_client_data` 
CHANGE  `NRIC`  `NRIC` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `NAME`  `NAME` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Date of Birth`  `Date of Birth` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Address`  `Address` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_client_data` CHANGE  `Income p.A`  `Income p.A` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_loan_data` CHANGE  `AGENT CODE`  `EMPLOYEE ID` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

INSERT INTO agents(name) SELECT `EMPLOYEE ID` FROM _loan_data WHERE `EMPLOYEE ID` != '' GROUP BY `EMPLOYEE ID`;
INSERT INTO ledgers(name, ledger_type_id) SELECT `EMPLOYEE ID`, '3' FROM _loan_data WHERE `EMPLOYEE ID` != '' GROUP BY `EMPLOYEE ID`;
UPDATE agents set ledger_id = (SELECT id FROM ledgers where ledger_type_id = '3' and name = agents.name) where ledger_id = '0';
INSERT INTO vt_users(username, password, parent_id, is_enabled, theme, first_name) SELECT `EMPLOYEE ID`, MD5(`EMPLOYEE ID`), '1', '1', 'blue2', `EMPLOYEE ID` FROM _loan_data WHERE `EMPLOYEE ID` != '' GROUP BY `EMPLOYEE ID`;
UPDATE agents set vt_user_id = (SELECT id FROM vt_users where first_name = agents.name) where vt_user_id = '0';
INSERT INTO vt_user_roles(user_id, role_id) SELECT vu.id, (SELECT id FROM vt_roles WHERE name = 'agent') FROM _loan_data ld LEFT JOIN vt_users vu ON ld.`EMPLOYEE ID` = vu.username WHERE `EMPLOYEE ID` != '' GROUP BY `EMPLOYEE ID`;

ALTER TABLE  `_client_data` CHANGE  `Address`  `Main Address` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `Phone Number`  `Main Phone Number` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

INSERT INTO borrowers(uid, name, address1, hand_phone, dob) SELECT trim(NRIC), REPLACE(REPLACE(TRIM(NAME), '\n', ''), '\t', ''), REPLACE(REPLACE(TRIM(`Main Address`), '\n', ''), '\t', ''), `Main Phone Number`, concat(right(trim(`Date of Birth`), 4), '-', substring(trim(`Date of Birth`), 4, 2), '-', left(trim(`Date of Birth`), 2)) FROM _client_data;

update borrowers set borrower_type_id = '1';

CREATE TABLE IF NOT EXISTS `_blacklisted_client_data` (
  `#` int(2) DEFAULT NULL,
  `BLACKLISTED NRIC` varchar(9) DEFAULT NULL,
  `BLACKLIST REMARKS` varchar(27) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE  `_blacklisted_client_data` 
CHANGE  `BLACKLISTED NRIC`  `BLACKLISTED NRIC` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `BLACKLIST REMARKS`  `BLACKLIST REMARKS` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

INSERT IGNORE INTO blacklist_reasons(id, name) SELECT null, `BLACKLIST REMARKS` FROM _blacklisted_client_data WHERE `BLACKLIST REMARKS` != '' AND `BLACKLIST REMARKS` IS NOT NULL;

update borrowers left join _blacklisted_client_data ON borrowers.uid = _blacklisted_client_data.`BLACKLISTED NRIC` LEFT JOIN blacklist_reasons ON blacklist_reasons.name = _blacklisted_client_data.`BLACKLIST REMARKS` SET borrowers.blacklisted = '1' , borrowers.blacklist_reason_id = blacklist_reasons.id WHERE _blacklisted_client_data.`BLACKLISTED NRIC` IS NOT NULL;

update _loan_data SET `PURPOSE OF LOAN` = 'Others' WHERE `PURPOSE OF LOAN` = 'OTHER';
update _loan_data SET `PURPOSE OF LOAN` = 'Domestic' WHERE `PURPOSE OF LOAN` = 'Home / Family Needs';
update _loan_data SET `PURPOSE OF LOAN` = 'Others' WHERE `PURPOSE OF LOAN` = 'Housing';


UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Jan ', '/01/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Feb ', '/02/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Mar ', '/03/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Apr ', '/04/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' May ', '/05/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Jun ', '/06/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Jul ', '/07/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Aug ', '/08/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Sep ', '/09/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Oct ', '/10/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Nov ', '/11/');
UPDATE _loan_data SET `LOAN DATE` = REPLACE(`LOAN DATE`, ' Dec ', '/12/');

update _loan_data SET PACKAGE = 'daily' WHERE PACKAGE = 'Day';

UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Jan ', '/01/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Feb ', '/02/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Mar ', '/03/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Apr ', '/04/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' May ', '/05/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Jun ', '/06/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Jul ', '/07/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Aug ', '/08/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Sep ', '/09/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Oct ', '/10/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Nov ', '/11/');
UPDATE _bad_debts_data SET `BAD DEBT DATE` = REPLACE(`BAD DEBT DATE`, ' Dec ', '/12/');

INSERT INTO applications(account_number, date, borrower_id, amount, loan_purpose_id, new_surety_id, vt_user_id) SELECT `LOAN NUMBER`, concat(right(trim(`LOAN DATE`), 4), '-', substring(trim(`LOAN DATE`), 4, 2), '-', left(trim(`LOAN DATE`), 2)) as loan_date, (SELECT id FROM borrowers WHERE uid = trim(`BORROWER`) ORDER BY id LIMIT 1) as borrower_id, `LOAN AMOUNT`, (SELECT id FROM loan_purposes WHERE name = `PURPOSE OF LOAN`) as loan_purpose_id, (SELECT id FROM borrowers WHERE uid = trim(`SURETY`) ORDER BY id LIMIT 1) as surety_id, '1' FROM _loan_data WHERE trim(`BORROWER`) != '' HAVING borrower_id != '';

update applications set vt_user_id = (SELECT id FROM _loan_data ld LEFT JOIN vt_users vu ON ld.`EMPLOYEE ID` = vu.username WHERE applications.account_number = ld.`LOAN NUMBER`) WHERE vt_user_id = '1';


update _loan_data SET PACKAGE = 'bi-weekly' WHERE PACKAGE = 'B/Weekly';
update _loan_data SET PACKAGE = 'monthly' WHERE PACKAGE = 'Monthly';
update _loan_data SET PACKAGE = 'weekly' WHERE PACKAGE = 'Weekly';

ALTER TABLE  `books` ADD UNIQUE (`name`);
INSERT IGNORE INTO books(name, Bank) SELECT `PAYMENT MODE`, '1' FROM _paid_repayment_data;
INSERT IGNORE INTO books(name, Bank) SELECT `PAYMENT MODE/ACCOUNT`, '1' FROM _debit_credit_transfer_data;
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 disbursements(date, application_id, account_number, amount, book_amount, interest_rate, interest_frequency_id, installment_count, payment_frequency_id, payment_type_id, Remarks, book_id) 
SELECT date, id, account_number, amount, amount, (SELECT INTEREST FROM _loan_data WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0), '5', (SELECT IF(TERM = 'OPEN', 0, TERM) FROM _loan_data WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0), (SELECT tf.id FROM _loan_data sbd LEFT JOIN time_frequencies tf ON sbd.`PACKAGE` = tf.name WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0), '1', (SELECT `LOAN REMARK` FROM _loan_data WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0), (SELECT books.id FROM _loan_data LEFT JOIN books ON books.name = _loan_data.`PAYMENT MODE` WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0) FROM applications ap;

update disbursements d left join _loan_data ld ON d.account_number = ld.`LOAN NUMBER` set d.late_interest_rate = ld.`LATE INTEREST RATE P.A.` where d.late_interest_rate = '0' and ld.`LOAN NUMBER` IS NOT NULL;

update disbursements SET status = '0';
update disbursements SET interest_calculation_method_id = (SELECT id FROM interest_calculation_methods WHERE name = 'Simple Interest') WHERE installment_count > 0;
update disbursements SET interest_calculation_method_id = (SELECT id FROM interest_calculation_methods WHERE name = 'Rest Schedule') WHERE installment_count = 0 AND (interest_calculation_method_id = '0' OR interest_calculation_method_id = '');

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 disbursements set installment_count = '12' where interest_calculation_method_id IN ('2', '3') AND payment_frequency_id = '4';
update disbursements set installment_count = '26' where interest_calculation_method_id IN ('2', '3') AND payment_frequency_id = '3';
update disbursements set installment_count = '52' where interest_calculation_method_id IN ('2', '3') AND payment_frequency_id = '2';
update disbursements set installment_count = '1' where interest_calculation_method_id IN ('2', '3') AND payment_frequency_id = '5';

UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jan ', '/01/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Feb ', '/02/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Mar ', '/03/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Apr ', '/04/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' May ', '/05/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jun ', '/06/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jul ', '/07/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Aug ', '/08/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Sep ', '/09/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Oct ', '/10/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Nov ', '/11/');
UPDATE _paid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Dec ', '/12/');


INSERT INTO repayments(application_id, disbursement_id, account_number, date, principal, interest, amount, remarks, book_id, permit_fee, receipt_number) SELECT ap.id as application_id, d.id as disbursement_id, d.account_number, concat(right(trim(prd.`REPAYMENT DUE DATE`), 4), '-', substring(trim(prd.`REPAYMENT DUE DATE`), 4, 2), '-', left(trim(prd.`REPAYMENT DUE DATE`), 2)) as date, prd.`PRINCIPAL`, prd.`INTEREST`, prd.`TOTAL AMOUNT PAID`, prd.`REPAYMENT REMARKS`, (SELECT id FROM books WHERE name = prd.`PAYMENT MODE`) as book_id, prd.`OTHER PERMIT`, prd.`RECEIPT NO.` FROM _paid_repayment_data prd LEFT JOIN applications ap ON prd.`LOAN NUMBER` = ap.account_number LEFT JOIN disbursements d ON ap.id = d.application_id WHERE ap.id IS NOT NULL AND d.id IS NOT NULL;


update repayments set late_fee = permit_fee;
update repayments set amount = late_fee + principal + interest WHERE amount != late_fee + principal + interest;

update repayments r set acceptance_fee = late_fee, late_fee = 0 WHERE date = (SELECT date FROM disbursements WHERE id = r.disbursement_id) AND late_fee > 0;
update disbursements d set acceptance_fee = (SELECT acceptance_fee FROM repayments WHERE disbursement_id = d.id AND date = d.date and acceptance_fee > 0) where (SELECT COUNT(id) FROM repayments WHERE acceptance_fee > 0 AND disbursement_id = d.id) = 1;



-- -----------------------------
-- Non loan related transactions
-- -----------------------------

INSERT INTO ledgers(name, ledger_type_id) SELECT `LEDGER CODE`, '3' FROM  `_debit_credit_transfer_data` GROUP BY  `LEDGER CODE`;

ALTER TABLE  `_debit_credit_transfer_data` ADD  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

ALTER TABLE  `_debit_credit_transfer_data` 
CHANGE  `PAYMENT MODE/ACCOUNT`  `PAYMENT MODE/ACCOUNT` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE `VOUCHER NO.` `VOUCHER NO.` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL, 
CHANGE  `CHEQUE NO.`  `CHEQUE NO.` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `TRANSACTION`  `TRANSACTION` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `PAYER / PAYEE`  `PAYER / PAYEE` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `LEDGER CODE`  `LEDGER CODE` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `TRANSACTION REMARKS`  `TRANSACTION REMARKS` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_debit_credit_transfer_data` CHANGE  `TRANSACTION DATE`  `TRANSACTION DATE` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Jan ', '/01/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Feb ', '/02/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Mar ', '/03/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Apr ', '/04/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' May ', '/05/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Jun ', '/06/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Jul ', '/07/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Aug ', '/08/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Sep ', '/09/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Oct ', '/10/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Nov ', '/11/');
UPDATE _debit_credit_transfer_data SET `TRANSACTION DATE` = REPLACE(`TRANSACTION DATE`, ' Dec ', '/12/');

-- --------
-- payments
-- --------

INSERT INTO vouchers(date, transaction_type, pay_type, ledger_id, book_id, book_ledger_id, payment_type_id, amount, narration, entry_mode) 
SELECT 
	concat(right(trim(`TRANSACTION DATE`), 4), '-', substring(trim(`TRANSACTION DATE`), 4, 2), '-', left(trim(`TRANSACTION DATE`), 2)), 
	'P', 
	'O', 
	(SELECT id FROM ledgers WHERE name = TRIM(dctd.`LEDGER CODE`) ORDER BY id ASC LIMIT 1), 
	(SELECT id FROM books WHERE name = TRIM(dctd.`PAYMENT MODE/ACCOUNT`)), 
	(SELECT id FROM ledgers WHERE name = TRIM(dctd.`PAYMENT MODE/ACCOUNT`) AND book_id != '0'), 
	IF(`CHEQUE NO.` IS NULL, (SELECT id FROM payment_types WHERE name = 'Cash'), (SELECT id FROM payment_types WHERE name = 'Cheque')), 
	AMOUNT, 
	`TRANSACTION REMARKS`, 
	IF(TRANSACTION = 'Expenses', 'P', 'R') 
FROM _debit_credit_transfer_data dctd WHERE `TRANSACTION` = 'Expenses';

-- --------
-- receipts
-- --------

INSERT INTO vouchers(date, transaction_type, pay_type, ledger_id, book_id, book_ledger_id, payment_type_id, amount, narration, entry_mode) 
SELECT 
	concat(right(trim(`TRANSACTION DATE`), 4), '-', substring(trim(`TRANSACTION DATE`), 4, 2), '-', left(trim(`TRANSACTION DATE`), 2)), 
	'R', 
	'O', 
	(SELECT id FROM ledgers WHERE name = dctd.`LEDGER CODE` ORDER BY id ASC LIMIT 1), 
	(SELECT id FROM books WHERE name = dctd.`PAYMENT MODE/ACCOUNT`), 
	(SELECT id FROM ledgers WHERE name = dctd.`PAYMENT MODE/ACCOUNT` AND book_id != '0'), 
	IF(`CHEQUE NO.` IS NULL, (SELECT id FROM payment_types WHERE name = 'Cash'), (SELECT id FROM payment_types WHERE name = 'Cheque')), 
	AMOUNT*-1, 
	`TRANSACTION REMARKS`, 
	IF(TRANSACTION = 'Expenses', 'P', 'R') 
FROM _debit_credit_transfer_data dctd WHERE `TRANSACTION` = 'Deposit';


INSERT INTO vouchers(
	date, transaction_type, pay_type, entry_mode, book_ledger_id, ledger_id, book_id, amount, transaction_number, narration
) 
SELECT 
	concat(right(trim(dctd.`TRANSACTION DATE`), 4), '-', substring(trim(dctd.`TRANSACTION DATE`), 4, 2), '-', left(trim(dctd.`TRANSACTION DATE`), 2)) as date, 
	'P', 
	'O', 
	'T', 
	(SELECT id FROM ledgers WHERE name = dctd.`PAYMENT MODE/ACCOUNT` LIMIT 1) as `from`, 
	(SELECT id FROM ledgers WHERE name = dctd1.`PAYMENT MODE/ACCOUNT` LIMIT 1) as `to`, 
	(SELECT id FROM books WHERE name = dctd.`PAYMENT MODE/ACCOUNT` LIMIT 1) as `book_id`, 
	ABS(dctd.`AMOUNT`), 
	dctd.`CHEQUE NO.`, 
	dctd.`TRANSACTION REMARKS`
FROM _debit_credit_transfer_data dctd 
LEFT JOIN _debit_credit_transfer_data dctd1 ON 
	dctd.`TRANSACTION DATE` = dctd1.`TRANSACTION DATE` AND 
	dctd.`TRANSACTION` != dctd1.`TRANSACTION` AND 
	ABS(dctd.`AMOUNT`) = ABS(dctd1.`AMOUNT`) 
WHERE dctd.`TRANSACTION` LIKE '%transfer%' AND dctd.`TRANSACTION` = 'TransferOUT'
GROUP BY dctd.id;

UPDATE vouchers SET parent_voucher_id = id WHERE transaction_type = 'P' AND pay_type = 'O' AND entry_mode = 'T';


INSERT INTO vouchers(date, voucher_number, transaction_type, pay_type, amount, narration, entry_mode, ledger_id, book_ledger_id, parent_voucher_id, book_id) 
SELECT date, voucher_number, 'R', pay_type, amount, narration, entry_mode, book_ledger_id, ledger_id, parent_voucher_id, (SELECT book_id FROM ledgers WHERE id = vouchers.ledger_id) 
FROM vouchers WHERE transaction_type = 'P' AND pay_type = 'O' AND entry_mode = 'T';




-- os principal reset in 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;




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 vouchers SET book_ledger_id = (SELECT id FROM ledgers WHERE book_id = vouchers.book_id) where book_ledger_id = '0' AND book_id != '0'

update applications set loan_type_id = '2';


UPDATE disbursements d SET status = '1' WHERE amount = (SELECT SUM(principal) FROM repayments WHERE disbursement_id = d.id);

UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jan ', '/01/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Feb ', '/02/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Mar ', '/03/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Apr ', '/04/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' May ', '/05/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jun ', '/06/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Jul ', '/07/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Aug ', '/08/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Sep ', '/09/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Oct ', '/10/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Nov ', '/11/');
UPDATE _unpaid_repayment_data SET `REPAYMENT DUE DATE` = REPLACE(`REPAYMENT DUE DATE`, ' Dec ', '/12/');


insert into calls(date, next_call_date, application_id, remarks, disbursement_id, payment_schedule_id) 
SELECT '', concat(right(trim(`REPAYMENT DUE DATE`), 4), '-', substring(trim(`REPAYMENT DUE DATE`), 4, 2), '-', left(trim(`REPAYMENT DUE DATE`), 2)), (SELECT application_id FROM disbursements WHERE account_number = `LOAN NUMBER`), `REPAYMENT REMARKS`, (SELECT id FROM disbursements WHERE account_number = `LOAN NUMBER`), '' FROM _unpaid_repayment_data;


update borrowers b set country_id = '1' WHERE (SELECT `Nationality Status` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = "Singaporean" AND b.country_id = '0';


update borrowers b set identification_type_id = '5' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Business Pass' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '3' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'E-Pass' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '3' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Foreigner' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '3' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Identity Card' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '2' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Permanent Resident' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '5' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'S-Pass' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '1' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Singapore Citizen' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '1' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Singaporean' AND b.identification_type_id = '';
update borrowers b set identification_type_id = '3' WHERE (SELECT `Nationality` FROM _client_data WHERE NRIC = b.uid LIMIT 1) = 'Work Permit' AND b.identification_type_id = '';


update borrowers b set annual_income = (SELECT `Income p.A` FROM _client_data cd WHERE cd.NRIC = b.uid) where b.annual_income = '0';
update disbursements d LEFT JOIN applications ap ON d.application_id = ap.id set d.borrower_annual_income = (SELECT annual_income FROM borrowers where id = ap.borrower_id) where d.borrower_annual_income = '0';



