-- notes
-- repayment/PS files are generally large and importing them in PMA strips long decimals. It sets decimals to stuff like 7,4
-- be careful with interest frequency. there are 2 interest rates - fRateOfInterest and mInterestPerYear

LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_loan_transfer.csv' INTO TABLE _loan_transfer FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'dio/_rep_payment.csv' INTO TABLE _rep_payment FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'dio/_mlcb_payment.csv' INTO TABLE _mlcb_payment FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'dio/_loan_disbursement_edited.csv' INTO TABLE _loan_disbursement FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_loan_transfer_static.csv' INTO TABLE _loan_transfer_static FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_loan_other_fees.csv' INTO TABLE _loan_other_fees FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_rep_payment_pay_method.csv' INTO TABLE _rep_payment_pay_method FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_reg_customer_individual.csv' INTO TABLE _reg_customer_individual FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_loan_disbursement_pay_method.csv' INTO TABLE _loan_disbursement_pay_method FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_mlcb_loan.csv' INTO TABLE _mlcb_loan FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_mlcb_installment.csv' INTO TABLE _mlcb_installment FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_mlcb_applicant.csv' INTO TABLE _mlcb_applicant FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_rep_other_fees.csv' INTO TABLE _rep_other_fees FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
-- -- LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_collector_remark.csv' INTO TABLE _sys_collector_remark FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
-- LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_loan_disbursement_select.csv' INTO TABLE _loan_disbursement_select FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_forms.csv' INTO TABLE _sys_forms FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_general_code.csv' INTO TABLE _sys_general_code FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_agent.csv' INTO TABLE _sys_agent FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

-- LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_moneylender.csv' INTO TABLE _sys_money_lender FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
-- LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_agent_commision.csv' INTO TABLE _sys_agent_commision FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_agent_payment_method.csv' INTO TABLE _sys_agent_payment_method FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_moneylender_link.csv' INTO TABLE _sys_moneylender_link FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_sms.csv' INTO TABLE _sys_sms FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_reg_customer_remark.csv' INTO TABLE _reg_customer_remark FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_agent_remark_log.csv' INTO TABLE _sys_agent_remark_log FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_kiv_log.csv' INTO TABLE _sys_kiv_log FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_collectors.csv' INTO TABLE _sys_collectors FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';



-- -- LOAD DATA LOCAL INFILE 'Downloads/ss-extract-new/_sys_sms_template.csv' INTO TABLE _sys_sms_template FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

delete from _loan_transfer WHERE strLoanRefNbr = 'strLoanRefNbr';
delete from _rep_payment WHERE strReceiptRefNbr = 'strReceiptRefNbr';
delete from _mlcb_payment WHERE ApiKey = 'ApiKey';
delete from _loan_disbursement WHERE strLoanRefNbr = 'strLoanRefNbr';
delete from _loan_transfer_static WHERE strLoanRefNbr = 'strLoanRefNbr';
delete from _sys_agent_remark_log WHERE nTransferSid = 'nTransferSid';
delete from _loan_other_fees WHERE strFeeName = 'strFeeName';

delete from _rep_payment_pay_method WHERE strReceiptRefNbr = 'strReceiptRefNbr';
delete from _sys_kiv_log WHERE nTransferSid = 'nTransferSid';
delete from _reg_customer_individual WHERE strICNbr = 'strICNbr';
delete from _reg_customer_remark WHERE strRemark = 'strRemark';
delete from _loan_disbursement_pay_method WHERE strLoanRefNbr = 'strLoanRefNbr';
delete from _mlcb_loan WHERE ApiKey = 'ApiKey';
delete from _mlcb_installment WHERE LonAppNo = 'LonAppNo';

delete from _mlcb_applicant WHERE LonAppNo = 'LonAppNo';
delete from _rep_other_fees WHERE strFeeName = 'strFeeName';
delete from _sys_collector_remark WHERE strLoanRef = 'strLoanRef';
delete from _loan_disbursement_select WHERE strLoanRefNbr LIKE '%strLoanRefNbr%';
delete from _sys_forms WHERE strMoneyLenderId = 'strMoneyLenderId' ;
delete from _sys_general_code WHERE strName = 'strName';
delete from _sys_agent WHERE strAgentId LIKE '%strAgentId%';
delete from _sys_collectors WHERE strAgentID = 'strAgentID';

delete from _sys_money_lender WHERE strLicenceNbr = 'strLicenceNbr';
delete from _sys_agent_commision WHERE strAgentID = 'strAgentID';
delete from _sys_agent_payment_method WHERE strAgentId = 'strAgentId';
delete from _sys_moneylender_link WHERE strConStringName = 'strConStringName' ;
-- delete from _sys_sms WHERE strName = 'strName';



-- ---------------------------------------------------------------------------------------------------------------------------------------------------------------


ALTER TABLE `_loan_disbursement` ADD PRIMARY KEY(`nSid`);
ALTER TABLE `_loan_disbursement` ADD INDEX(`strLoanRefNbr`);
ALTER TABLE `_loan_disbursement_pay_method` ADD PRIMARY KEY(`nSid`);
ALTER TABLE `_loan_disbursement_pay_method` ADD INDEX(`strLoanRefNbr`);
ALTER TABLE `_loan_other_fees` ADD PRIMARY KEY(`nSid`);
ALTER TABLE `_loan_transfer` ADD PRIMARY KEY(`nSid`);
ALTER TABLE `_loan_transfer` ADD INDEX(`strLoanRefNbr`);
ALTER TABLE `_loan_transfer_static` ADD PRIMARY KEY(`nSid`);
ALTER TABLE `_loan_transfer_static` ADD INDEX(`strLoanRefNbr`);
ALTER TABLE `_rep_payment` ADD PRIMARY KEY(`nSid`);
ALTER TABLE `_rep_payment` ADD INDEX(`strReceiptRefNbr`);
ALTER TABLE `_rep_payment` ADD INDEX(`strLoanRefNbr`);
ALTER TABLE `_rep_payment_pay_method` ADD PRIMARY KEY(`nSid`);
ALTER TABLE `_rep_payment_pay_method` ADD INDEX(`strReceiptRefNbr`);
ALTER TABLE `_reg_customer_individual` ADD PRIMARY KEY(`nSid`);
ALTER TABLE `_reg_customer_remark` ADD INDEX(`strCustNRIC`);
ALTER TABLE `_loan_other_fees` ADD INDEX(`strLoanRefNbr`);

ALTER TABLE  `_rep_other_fees` ADD INDEX (  `strFeeName` );


ALTER TABLE borrowers DROP INDEX borrower_type_id;





-- ---------
-- Borrowers
-- ---------


ALTER TABLE  `_reg_customer_individual` CHANGE  `strICDescription`  `strICDescription` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;
ALTER TABLE  `_reg_customer_individual` CHANGE  `strICDescription`  `strICDescription` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

-- update _reg_customer_individual SET strICDescription = '' WHERE strICDescription = 'Singapore NRIC No';
-- update _reg_customer_individual SET strICDescription = '' WHERE strICDescription = 'Others';
-- update _reg_customer_individual SET strICDescription = '' WHERE strICDescription = 'Foreign Identification Number';
-- update _reg_customer_individual SET strICDescription = '' WHERE strICDescription = 'Singapore PR No';
update _reg_customer_individual SET strICDescription = 'Others' WHERE strICDescription = 'Foreign Person';

ALTER TABLE  `_reg_customer_individual` CHANGE  `strMAPostalCode`  `strMAPostalCode` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ;
ALTER TABLE  `_reg_customer_individual` CHANGE  `strICNbr`  `strICNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ;
-- ALTER TABLE  `_reg_customer_corporate` CHANGE  `strMAPostalCode`  `strMAPostalCode` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ;
-- ALTER TABLE  `_reg_customer_corporate` CHANGE  `strNRICNbr`  `strNRICNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ;

INSERT INTO borrowers(id, name, borrower_type_id, country_id, uid, annual_income, dob, identification_type_id, address1, address2, city, state, country, pincode, work_phone, home_phone, hand_phone, sms_phone, email, alternate_email, blk, level, unit, street, building, company, designation, sms_subscription, image, remarks) SELECT nSid, strName, '', '', strICNbr, mAnnualIncome, dtDateOfBirth, '', strMailingAddress, strBusinessAddress, '', '', '', '', strBusinessPhone, strHomePhone, strHandPhone, '', strEmail, strAlternateEmail, strBlockNo, '', strUnitNo, strStreetName, strBuildingName, strEmployer, strDesignation, '', '', '' FROM _reg_customer_individual;

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

update borrowers set identification_type_id = (SELECT it.id FROM _reg_customer_individual rci LEFT JOIN identification_types it ON rci.strICDescription = it.name ORDER BY nSid DESC LIMIT 1) WHERE identification_type_id = '';

UPDATE borrowers SET borrower_type_id = '1';
update borrowers set pincode = (SELECT strMAPostalCode FROM _reg_customer_individual WHERE uid = strICNbr ORDER BY nSid DESC LIMIT 1) WHERE borrower_type_id = '1' AND pincode = '';

ALTER TABLE  `_reg_customer_individual` CHANGE  `strNationality`  `strNationality` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
UPDATE borrowers SET country_id = (SELECT c.id FROM _reg_customer_individual bi LEFT JOIN countries c ON bi.strNationality = c.name WHERE bi.nSid = borrowers.id AND c.name != '');
UPDATE borrowers SET country_id = (SELECT id FROM countries  WHERE name = 'Unknown') WHERE country_id = '0';



-- corporate borrowers - UID might clash with individual borrowers because IDs are coming from external DB only.
ALTER TABLE  `_reg_customer_corporate` CHANGE  `strCountry`  `strCountry` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ;

INSERT INTO borrowers(id, name, borrower_type_id, country_id, uid, annual_income, dob, identification_type_id, address1, address2, city, state, country, pincode, work_phone, home_phone, hand_phone, sms_phone, email, alternate_email, blk, level, unit, street, building) SELECT nSid, strCorporateName, '2', (SELECT id from countries WHERE name = strCountry), strNRICNbr, '', '', '', strMailingAddress, strBusinessAddress, '', '', '', strPostalCodeS, strBusinessPhone, strHomePhone, strHandPhone, '', strEmail, '', strBlockNo, '', strUnitNo, strStreetName, strBuildingName FROM _reg_customer_corporate;

UPDATE borrowers SET country_id = (SELECT id FROM countries  WHERE name = 'Unknown') WHERE country_id = '0';

INSERT INTO borrowers_corporate(company_name, incorporation_date, contact_person, designation, uid, incorporation_number) SELECT strCorporateName, dtIncorporationDT, strContactPerson, strDesignation, strNRICNbr, strPlaceNbr FROM _reg_customer_corporate;

update borrowers_corporate bc SET borrower_id = (SELECT id FROM borrowers WHERE name = bc.company_name AND uid = bc.uid);

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

-- update borrowers set pincode = (SELECT strMAPostalCode FROM _reg_customer_corporate WHERE uid = strNRICNbr ORDER BY nSid DESC LIMIT 1) WHERE borrower_type_id = '2';

-- -------
-- Agents
-- -------

ALTER TABLE  `_sys_agent` CHANGE  `strAgentId`  `strAgentId` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strAgentName`  `strAgentName` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

DELETE FROM agents WHERE name = 'Company(default)';
INSERT INTO agents(name) SELECT LOWER(REPLACE(strAgentName, ' ', '')) FROM _sys_agent;
INSERT INTO vt_users(username, password, role_id, parent_id, language, theme, first_name, is_enabled) SELECT LOWER(REPLACE(name, ' ', '')), md5(LOWER(REPLACE(name, ' ', ''))), (SELECT id FROM vt_roles WHERE name='Agent'), '1', 'en', 'blue1', name, '1' FROM agents WHERE name != 'admin';

update agents SET vt_user_id = (SELECT id FROM vt_users WHERE username = name);

INSERT INTO vt_user_roles(user_id, role_id) SELECT vt_user_id, (SELECT id FROM vt_roles WHERE name = 'Agent') FROM agents;





-- ------------
-- Applications
-- ------------

ALTER TABLE  `_loan_disbursement` 
-- CHANGE  `nSid`  `nSid` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strLoanRefNbr`  `strLoanRefNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strMoneylenderId`  `strMoneylenderId` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strLenderName`  `strLenderName` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `mLoanAmount`  `mLoanAmount` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `mOtherPermittedFee`  `mOtherPermittedFee` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_loan_disbursement` CHANGE  `fRateOfInterest`  `fRateOfInterest` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `mPrinciplePerTerm`  `mPrinciplePerTerm` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `mInterestPerYear`  `mInterestPerYear` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strRepayPlan`  `strRepayPlan` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strLoanPurpose`  `strLoanPurpose` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `bLoanType`  `bLoanType` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_loan_disbursement` CHANGE  `strLoanTypeRemarks`  `strLoanTypeRemarks` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strTypeOfSecurity`  `strTypeOfSecurity` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strMarketValue`  `strMarketValue` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strTermType`  `strTermType` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `nInstallment`  `nInstallment` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_loan_disbursement` CHANGE  `dtLoanDate`  `dtLoanDate` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `dtFirstRepaymentDT`  `dtFirstRepaymentDT` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `dtCalculateFromDT`  `dtCalculateFromDT` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `bCompleteInd`  `bCompleteInd` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strRemarks`  `strRemarks` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strCreatedBy`  `strCreatedBy` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_loan_disbursement` CHANGE  `dtCreatedDT`  `dtCreatedDT` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `dtUpdatedBy`  `dtUpdatedBy` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `dtUpdatedDT`  `dtUpdatedDT` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strAgentID`  `strAgentID` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strBorrowerType`  `strBorrowerType` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strSuretyType`  `strSuretyType` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_loan_disbursement` CHANGE  `dtNextPaidDT`  `dtNextPaidDT` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strWriteOffReason`  `strWriteOffReason` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `dtCompleted`  `dtCompleted` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `bAdvance`  `bAdvance` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strKIV`  `strKIV` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strNewLoanType`  `strNewLoanType` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_loan_disbursement` CHANGE  `strLateFeeCalculation`  `strLateFeeCalculation` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strLateIntCalculation`  `strLateIntCalculation` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strLoanStatus`  `strLoanStatus` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strCollectionRemark`  `strCollectionRemark` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;


INSERT INTO applications(account_number, date, borrower_id, amount, loan_purpose_id, vt_user_id, new_surety_id, loan_type_id) 
SELECT strLoanRefNbr, dtLoanDate, nBorrowerSid, mLoanAmount, (SELECT id FROM loan_purposes WHERE name = strLoanPurpose) as loan_purpose, (SELECT vt_user_id FROM agents LEFT JOIN _sys_agent ON LOWER(REPLACE(_sys_agent.strAgentName, ' ', '')) = agents.name WHERE _sys_agent.strAgentId = _loan_disbursement.strAgentID LIMIT 1) as vt_user, nSuretySid, '2' FROM _loan_disbursement;


-- -------------
-- Disbursements
-- -------------

INSERT INTO disbursements(application_id, date, account_number, amount, interest_rate, late_interest_rate, interest_frequency_id, interest_calculation_method_id, installment_count, payment_frequency_id, late_fee, first_payment_date) 
SELECT (SELECT id FROM applications WHERE account_number = strLoanRefNbr), dtLoanDate, strLoanRefNbr, mLoanAmount, fRateOfInterest, fRateOfLaInterest, '5', CASE strRepayPlan WHEN 'Fixed Instalment Plan' THEN '1' WHEN 'Interest Open Plan' THEN '2' WHEN 'Interest Repayment Plan' THEN '3' END, nInstallment, (SELECT id FROM time_frequencies WHERE name = strTermType), mLateFee, dtFirstRepaymentDT FROM _loan_disbursement;

update disbursements d SET remarks = (SELECT strCollectionRemark FROM _loan_disbursement WHERE strLoanRefNbr = d.account_number);
update disbursements d SET borrower_annual_income = (SELECT mAnnualIncome FROM _loan_disbursement WHERE strLoanRefNbr = d.account_number);

-- set int freq = pay freq only if its same for the entire company
-- why was this coded?
-- update disbursements d SET interest_frequency_id = payment_frequency_id WHERE d.date <= '2014-12-22';
update disbursements d SET interest_frequency_id = payment_frequency_id;
	

ALTER TABLE  `_loan_other_fees` 
CHANGE  `strFeeName`  `strFeeName` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strLoanRefNbr`  `strLoanRefNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

update `_loan_other_fees` SET `strLoanRefNbr` = TRIM(`strLoanRefNbr`);
update `_loan_other_fees` SET `strLoanRefNbr` = REPLACE(`strLoanRefNbr`, ' ', '');
update `_loan_other_fees` SET `strLoanRefNbr` = REPLACE(`strLoanRefNbr`, '\r\n', '');
update `_loan_other_fees` SET `strLoanRefNbr` = REPLACE(`strLoanRefNbr`, '\n\r', '');
update `_loan_other_fees` SET `strLoanRefNbr` = REPLACE(`strLoanRefNbr`, '\n', '');
update `_loan_other_fees` SET `strLoanRefNbr` = REPLACE(`strLoanRefNbr`, '\r', '');

-- HACK: newline issue came up at the end of field in goldstar migration, couldn't solve it with trim/space replace, so finally int casted it.
-- update _loan_other_fees SET strLoanRefNbr = strLoanRefNbr*1 where strLoanRefNbr NOT regexp "[a-z]";


-- ALTER TABLE  `_loan_other_fees` ADD INDEX (  `strLoanRefNbr` );
update disbursements d SET acceptance_fee = (SELECT mValue FROM _loan_other_fees WHERE strLoanRefNbr = d.account_number AND strFeeName = 'ACCEPTANCE0' AND mValue > 0 LIMIT 1);
update disbursements d SET cheque_dishonour_fee = (SELECT mValue FROM _loan_other_fees WHERE strLoanRefNbr = d.account_number AND strFeeName = 'CHEQUE GIRO UNSUCCESS FEE' AND mValue > 0 LIMIT 1);
-- update disbursements d SET acceptance_fee = (SELECT mValue FROM _loan_other_fees WHERE strLoanRefNbr = d.account_number AND strFeeName = 'NON ELECTRONIC FUND TRANSFER CHARGES');
update disbursements d SET contract_variation_fee = (SELECT mValue FROM _loan_other_fees WHERE strLoanRefNbr = d.account_number AND strFeeName = 'TERM OF CONTRACT VARIATION' AND mValue > 0 LIMIT 1);
update disbursements d SET termination_fee = (SELECT mValue FROM _loan_other_fees WHERE strLoanRefNbr = d.account_number AND strFeeName = 'TERMINATION FEE' AND mValue > 0 LIMIT 1);

-- SELECT ROUND(acceptance_fee/amount*100, 2) FROM disbursements
update disbursements set acceptance_fee_percent = ROUND(acceptance_fee/amount*100, 2);


update disbursements set status = (SELECT bCompleteInd FROM _loan_disbursement WHERE account_number = strLoanRefNbr);


-- -----
-- Books
-- -----

INSERT INTO books(name, Bank) SELECT trim(strPaymentMethod), '1' FROM _rep_payment_pay_method GROUP BY strPaymentMethod;
INSERT IGNORE INTO books(name, Bank) SELECT trim(strPaymentMethod), '1' FROM _loan_disbursement_pay_method GROUP BY strPaymentMethod;
UPDATE books SET type = 'cash' WHERE name LIKE '%cash%';
UPDATE books SET type = 'bank' WHERE name LIKE '%bank%';
INSERT INTO ledgers(name, ledger_type_id, book_id) select name, '4', id FROM books b where name != 'Cash Book' AND name != 'Bank Book' AND (SELECT id FROM ledgers WHERE book_id = b.id) IS NULL;


-- ---------------------
-- Disbursement Payments
-- ---------------------

ALTER TABLE  `_loan_disbursement_pay_method` CHANGE  `strLoanRefNbr`  `strLoanRefNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strPaymentMethod`  `strPaymentMethod` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strBank`  `strBank` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strAccountNbr`  `strAccountNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_loan_disbursement_pay_method` ADD INDEX (  `strLoanRefNbr` );

update disbursements set book_id = (select books.id FROM _loan_disbursement_pay_method LEFT JOIN books ON trim(strPaymentMethod) = books.name WHERE strLoanRefNbr = account_number LIMIT 1), book_amount = (select mAmount FROM _loan_disbursement_pay_method WHERE strLoanRefNbr = account_number LIMIT 1);





-- ----------
-- Repayments
-- ----------

update `_rep_payment` SET `strReceiptRefNbr` = TRIM(`strReceiptRefNbr`);
update `_rep_payment` SET `strReceiptRefNbr` = REPLACE(`strReceiptRefNbr`, ' ', '');
update `_rep_payment` SET `strReceiptRefNbr` = REPLACE(`strReceiptRefNbr`, '\r\n', '');
update `_rep_payment` SET `strReceiptRefNbr` = REPLACE(`strReceiptRefNbr`, '\n\r', '');
update `_rep_payment` SET `strReceiptRefNbr` = REPLACE(`strReceiptRefNbr`, '\n', '');
update `_rep_payment` SET `strReceiptRefNbr` = REPLACE(`strReceiptRefNbr`, '\r', '');


ALTER TABLE  `_rep_payment` CHANGE  `strReceiptRefNbr`  `strReceiptRefNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `strLoanRefNbr`  `strLoanRefNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `dtPaymentDate`  `dtPaymentDate` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  `_rep_payment` ADD INDEX (  `strLoanRefNbr` );

insert into repayments(id, date, application_id, disbursement_id, receipt_number, account_number, principal, interest, late_fee, late_interest, amount)
select nSid, dtPaymentDate, (SELECT id FROM applications WHERE account_number = strLoanRefNbr), (SELECT id FROM disbursements WHERE account_number = strLoanRefNbr), strReceiptRefNbr, strLoanRefNbr, mPrinciple, mInterest, mLateFee, mLateInterestFee, mTotalPaid FROM _rep_payment WHERE (SELECT id FROM applications WHERE account_number = TRIM(strLoanRefNbr)) IS NOT NULL;

ALTER TABLE  `_rep_other_fees` CHANGE  `strFeeName`  `strFeeName` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strReceiptRefNbr`  `strReceiptRefNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_rep_other_fees` ADD INDEX (  `strReceiptRefNbr` );

update repayments r SET acceptance_fee = (SELECT SUM(mValue) FROM _rep_other_fees WHERE strReceiptRefNbr LIKE r.receipt_number AND (strFeeName = 'ACCEPTANCE0' OR strFeeName = 'ACCEPTANCE FEE')); -- slow query maybe if 1. double index on strReceiptRefNbr 2. using like/=
update repayments r SET termination_fee = (SELECT SUM(mValue) FROM _rep_other_fees WHERE strReceiptRefNbr = r.receipt_number AND (strFeeName = 'TERMINATION FEE'));
update repayments r SET contract_variation_fee = (SELECT SUM(mValue) FROM _rep_other_fees WHERE strReceiptRefNbr = r.receipt_number AND (strFeeName = 'VARYING FEE'));
-- update repayments r SET interest = (SELECT mValue FROM _rep_other_fees WHERE strReceiptRefNbr = r.receipt_number AND (strFeeName = 'interest'));
-- update repayments r SET late_interest = (SELECT SUM(mValue) FROM _rep_other_fees WHERE strReceiptRefNbr = r.receipt_number AND (strFeeName = 'late interest'));
-- update repayments r SET late_fee = (SELECT SUM(mValue) FROM _rep_other_fees WHERE strReceiptRefNbr = r.receipt_number AND (strFeeName = 'late fee'));
-- 'DUE DATE ADJUSTMENT'


-- SELECT rf.*, ap.account_number FROM _rep_other_fees rf LEFT JOIN repayments r on r.receipt_number = rf.strReceiptRefNbr LEFT JOIN applications ap on r.application_id = ap.id WHERE strFeeName = 'OTHER PERMITTED'


-- Settings repayment book to same as disburesment book
-- update repayments set book_id = (select book_id from disbursements where id = disbursement_id);

ALTER TABLE  `_rep_payment_pay_method` 
CHANGE  `strReceiptRefNbr`  `strReceiptRefNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strPaymentMethod`  `strPaymentMethod` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strBank`  `strBank` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `strAccountNbr`  `strAccountNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_rep_payment_pay_method` ADD INDEX (  `strPaymentMethod` );
ALTER TABLE  `books` ADD INDEX (  `name` );
ALTER TABLE  `_rep_payment_pay_method` ADD INDEX (  `strReceiptRefNbr` );

ALTER TABLE  `_rep_payment` ADD INDEX (  `strReceiptRefNbr` ) ;

-- CM specific
-- update _rep_payment_pay_method SET strPaymentMethod = 'BANK-UOB2' WHERE strPaymentMethod = 'CASH-UOB2';

-- 3453 rows affected
update repayments set book_id = (select b.id from _rep_payment_pay_method rppm LEFT JOIN books b ON trim(rppm.strPaymentMethod) = b.name where rppm.strReceiptRefNbr = repayments.receipt_number LIMIT 1) WHERE (select b.id from _rep_payment_pay_method rppm LEFT JOIN books b ON trim(rppm.strPaymentMethod) = b.name where rppm.strReceiptRefNbr = repayments.receipt_number LIMIT 1) IS NOT NULL;

-- setting books in repayments when receipt_numbers in _rep_payment_pay_method is cut
-- update `repayments` SET book_id = (SELECT id FROM books WHERE name = 'CASH') WHERE `book_id` = 0 and receipt_number LIKE '%JV-CFI/20101124/096-%'
-- update `repayments` SET book_id = (SELECT id FROM books WHERE name = 'CASH') WHERE `book_id` = 0 and receipt_number LIKE '%JV-CFI/20110503/182-%'
-- update `repayments` SET book_id = (SELECT id FROM books WHERE name = 'CASH') WHERE `book_id` = 0 and receipt_number LIKE '%JV-CFI/20110521/226-%'
-- update `repayments` SET book_id = (SELECT id FROM books WHERE name = 'CASH') WHERE `book_id` = 0 and receipt_number LIKE '%JV-CFI/20110505/187-%'
-- update `repayments` SET book_id = (SELECT id FROM books WHERE name = 'Cash-UOB') WHERE `book_id` = 0 and receipt_number LIKE '%JV-CFI/20110602/244-%'


update repayments set permit_fee = legal_fee + acceptance_fee + contract_variation_fee + cheque_dishonoured_fee + termination_fee + renewal_fee + late_fee;


-- ----------------
-- Payment Schedule
-- ----------------

ALTER TABLE  `_loan_transfer_static` 
CHANGE  `strLoanRefNbr`  `strLoanRefNbr` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `dtExpectedPayDate`  `dtExpectedPayDate` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `dtSecondPaydate`  `dtSecondPaydate` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `dtLastUpdateDT`  `dtLastUpdateDT` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `dtCreatedDT`  `dtCreatedDT` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;


INSERT INTO payment_schedule(application_id, disbursement_id, date, amount, principal, interest) 
SELECT (SELECT id FROM applications WHERE account_number = strLoanRefNbr), (SELECT id FROM disbursements WHERE account_number = strLoanRefNbr),  dtExpectedPayDate, mPrinciple+mInterest, mPrinciple, mInterest FROM _loan_transfer_static WHERE (SELECT id FROM applications WHERE account_number = strLoanRefNbr) IS NOT NULL;





-- update applications set loan_type_id = '2' where loan_type_id = '';


INSERT INTO disbursement_contact_details(disbursement_id, code, address1, address2, pincode, work_phone, home_phone, hand_phone, sms_phone, email, company, designation)
SELECT d.id, 'b1', strMailingAddress, strBusinessAddress, strMAPostalCode, strBusinessPhone, strHomePhone, strHandPhone, '', strEmail, strEmployer, strDesignation 
FROM disbursements d 
LEFT JOIN disbursement_contact_details dcd ON dcd.disbursement_id = d.id AND dcd.code = 'b1' 
LEFT JOIN _loan_disbursement ld ON ld.strLoanRefNbr = d.account_number
LEFT JOIN _reg_customer_individual rci ON rci.nSid = ld.nBorrowerSid
WHERE dcd.pincode IS NULL AND rci.nSid IS NOT NULL;



ALTER TABLE `_mlcb_loan` CHANGE `LonAgrNo` `LonAgrNo` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
update disbursements d SET imported_LonAppNo = (SELECT LonAppNo FROM _mlcb_loan WHERE LonAgrNo = d.account_number ORDER BY dtCreated LIMIT 1) WHERE imported_LonAppNo = '';

-- use alternate excel file
ALTER TABLE  `_mlcb_excel_list` CHANGE  `LoanAgreementNo`  `LonAgrNo` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE  `_mlcb_excel_list` CHANGE  `LoanApplicationNo`  `LonAppNo` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;


-- use excel file
RENAME TABLE  `_mlcb_excel_list` TO `_mlcb_excel_list_old` ;

ALTER TABLE  `_mlcb_excel_list` 
CHANGE  `LonAgrNo`  `LonAgrNo` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
CHANGE  `LonAppNo`  `LonAppNo` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE  `_mlcb_excel_list` ADD INDEX (  `LonAgrNo` );
ALTER TABLE  `_mlcb_excel_list` ADD INDEX (  `LonAppNo` );

update disbursements d SET imported_LonAppNo = (SELECT LonAppNo FROM _mlcb_excel_list WHERE LonAgrNo = d.account_number ORDER BY LonAppNo LIMIT 1);



-- new repayment entries for CYS from excel file with checkboxes provided
-- ALTER TABLE  `_new_repayments` CHANGE  `Receipt no.`  `Receipt no.` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE  `Loan Reference No.`  `Loan Reference No.` VARCHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE  `Term`  `Term` VARCHAR( 3 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE  `Agent`  `Agent` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE  `Paid Date`  `Paid Date` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE  `Created Date`  `Created Date` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ;
-- ALTER TABLE  `_new_repayments` CHANGE  `Payment Mode`  `Payment Mode` VARCHAR( 9 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ;

-- not sure what to do with the code below, was unnecessary during fcms migration
-- insert into repayments(date, application_id, disbursement_id, receipt_number, account_number, principal, interest, late_fee, late_interest, amount, book_id, remarks) select str_to_date(`Paid Date`, '%e %b %y'), (SELECT id FROM applications WHERE account_number = `Loan Reference No.`), (SELECT id FROM disbursements WHERE account_number = `Loan Reference No.`), `Receipt no.`, `Loan Reference No.`, nr.Principal, nr.Interest, nr.`Late Fee`, nr.`Late Interest`, nr.`Total Paid`, (SELECT id FROM books WHERE name = nr.`Payment Mode`), 'nr' FROM _new_repayments nr LEFT JOIN disbursements d ON nr.`Loan Reference No.` = d.account_number WHERE d.id IS NOT NULL;

-- INSERT INTO vouchers(date, voucher_number, transaction_type, pay_type, reference_number, amount, borrower_id, account_number, payment_type_id, ledger_id, book_ledger_id, book_id) SELECT date, '', 'R', 'R', id, amount, (SELECT b.id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), account_number, payment_type_id, (SELECT b.ledger_id FROM applications ap LEFT JOIN borrowers b ON ap.borrower_id = b.id WHERE ap.id = r.application_id), (SELECT id FROM ledgers WHERE book_id = r.book_id), book_id FROM repayments r WHERE remarks = 'nr';




-- goodwill entries (JV receipts)
-- update repayments set book_id = (SELECT id FROM books WHERE name = 'Goodwill Book'), amount = principal, interest = 0, late_interest = 0, late_fee = 0, permit_fee = 0 WHERE receipt_number LIKE '%JV%' AND principal > 0;

-- goodwill expense entries
-- INSERT INTO vouchers(date, amount, narration, ledger_id, book_ledger_id, book_id, transaction_type, pay_type, entry_mode, voucher_number) SELECT date, principal, CONCAT('Goodwill Expense for Account ', (SELECT account_number FROM disbursements WHERE id = r.disbursement_id)) as bad_debt_narration, (SELECT id FROM ledgers WHERE name = 'Goodwill Expense') as ledger_id, (SELECT id FROM ledgers WHERE book_id = r.book_id) as book_ledger_id, book_id, 'P', 'O', 'P', voucher_number FROM repayments r WHERE book_id = (SELECT id FROM books WHERE name = 'Goodwill Book');

-- check goodwill repayments having loan status = active
-- select d.account_number FROM repayments r LEFT JOIN disbursements d ON r.disbursement_id = d.id WHERE r.book_id = (SELECT id FROM books WHERE name = 'Goodwill Book') AND d.status != '1'



-- deleting PS for reentry in CF/FCMS. 999.99 issue with decimals
-- delete ps.* from payment_schedule ps LEFT JOIN disbursements d ON ps.disbursement_id = d.id WHERE d.date <= '2014-12-22';

