-- None of the loans have sureties
-- all loans assigned to admin
-- all loans treated as cash



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', 'Goodwill Book');


RENAME TABLE  `Loan` TO `_Loan`;
RENAME TABLE  `Receipt` TO `_Receipt`;

ALTER TABLE  `_Loan` ADD INDEX (  `LOAN NUMBER` );
ALTER TABLE  `_Loan` ADD INDEX (  `strICNbr` );
ALTER TABLE  `_Receipt` ADD INDEX (  `RECEIPT NUMBER` );
ALTER TABLE  `_Receipt` ADD INDEX (  `LOAN NUMBER` );

ALTER TABLE  `_Loan` CHANGE  `strICNbr`  `strICNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE `_Receipt` 
CHANGE `PRINCIPLE` `PRINCIPLE` DECIMAL(12,2) NULL DEFAULT NULL, 
CHANGE `INTEREST` `INTEREST` DECIMAL(12,2) NULL DEFAULT NULL, 
CHANGE `LATE FEE` `LATE FEE` DECIMAL(12,2) NULL DEFAULT NULL, 
CHANGE `LATE INTEREST` `LATE INTEREST` DECIMAL(12,2) NULL DEFAULT NULL, 
CHANGE `ADMIN FEE` `ADMIN FEE` DECIMAL(12,2) NULL DEFAULT NULL;
-- CHANGE `Total Pay` `Total Pay` DECIMAL(12,2) NULL DEFAULT NULL;


ALTER TABLE borrowers DROP INDEX borrower_type_id;

INSERT INTO borrowers(uid, name) SELECT trim(`strICNbr`), strName FROM _Loan;

update borrowers set borrower_type_id = '1';
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(account_number, date, borrower_id, amount, vt_user_id) 
SELECT `LOAN NUMBER`, DATE_ADD('1900-01-01', INTERVAL `Loan Date`-2 DAY) as loan_date, (SELECT id FROM borrowers WHERE uid = trim(`strICNbr`) ORDER BY id LIMIT 1) as borrower_id, `LOAN AMOUNT`, '1' FROM _Loan;

ALTER TABLE  `_Loan` CHANGE  `TERM`  `TERM` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

INSERT INTO disbursements(date, application_id, account_number, amount, book_amount, interest_rate, installment_count, payment_frequency_id, payment_type_id, book_id, acceptance_fee, late_fee) 
SELECT 
	date, 
	id, 
	account_number, 
	amount, 
	amount, 
	(SELECT `INTEREST` FROM _Loan WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0) as interest_rate, 
	(SELECT `INSTALLMENT` FROM _Loan WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0) as installment_count, 
	(SELECT tf.id FROM _Loan sbd LEFT JOIN time_frequencies tf ON sbd.`Term` = tf.name WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0) as payment_frequency_id, 
	'1', 
	(SELECT id FROM books WHERE name = 'Cash Book') as book_id, 
	(SELECT `ADMIN FEE` FROM _Loan WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0) as acceptance_fee, 
	(SELECT `LATE FEE` FROM _Loan WHERE `LOAN NUMBER` = ap.account_number AND `LOAN AMOUNT` != 0) as late_fee 
FROM applications ap;

update disbursements d SET interest_frequency_id = (SELECT tf.id FROM _Loan sbd LEFT JOIN time_frequencies tf ON sbd.`Term` = tf.name WHERE `LOAN NUMBER` = d.account_number AND `LOAN AMOUNT` != 0);

update disbursements SET interest_calculation_method_id = '4' WHERE date >= '2015-10-01';
update disbursements SET interest_calculation_method_id = '1' WHERE date < '2015-10-01';

update disbursements SET acceptance_fee_percent = ROUND(acceptance_fee/amount*100, 2);
update disbursements SET late_fee_percent = ROUND(late_fee/amount*100, 2);

update disbursements d SET imported_LonAppNo = (SELECT `APPNO` FROM _Loan l WHERE `LOAN NUMBER` = d.account_number AND `LOAN AMOUNT` != 0);

-- update disbursements d left join _Loan 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';


-- SET FOREIGN_KEY_CHECKS=0; 

INSERT INTO repayments(application_id, disbursement_id, account_number, date, principal, interest, late_fee, late_interest, acceptance_fee, remarks, book_id, receipt_number) 
SELECT ap.id, d.id, d.account_number, DATE_ADD('1900-01-01', INTERVAL `payment date`-2 DAY) as date, prd.Principle, prd.Interest, prd.`LATE FEE`, prd.`LATE INTEREST`, prd.`ADMIN FEE`, prd.`strPaidBy`, (SELECT id FROM books WHERE name = 'Cash Book') as book_id, prd.`Receipt Number` 
FROM _Receipt prd LEFT JOIN applications ap ON prd.`LOAN NUMBER` = ap.account_number LEFT JOIN disbursements d ON ap.id = d.application_id;

update repayments set permit_fee = legal_fee+contract_variation_fee+cheque_dishonoured_fee+termination_fee+late_fee+acceptance_fee;
update repayments set payment_type_id = (SELECT id FROM payment_types WHERE name = 'Cash');

update repayments set amount = principal + interest+late_fee+late_interest+acceptance_fee;


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

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

-- INSERT INTO disbursement_contact_details(disbursement_id, code, address1)
-- SELECT d.id, 'b1', Address FROM disbursements d LEFT JOIN _Loan l ON l.`LOAN NUMBER` = d.account_number;

update disbursements d SET borrower_annual_income = (SELECT `Annual Income` FROM _Loan l WHERE l.`LOAN NUMBER` = d.account_number);





