ALTER TABLE  `_mls` ADD PRIMARY KEY (  `S/No` );

ALTER TABLE  `_mls` CHANGE `Loan Account No`  `account_number` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Date of Loan`  `date_of_loan` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Name of Borrower`  `borrower` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Address  - Level Number`  `level` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Address  - Unit Number`  `unit` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Address  - Street Name`  `street` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Address  - Building Name`  `building` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Address  - Postal Code`  `pincode` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Un-Formatted Address of Borrower`  `address1` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Annual Income Amount`  `annual_income` DECIMAL( 10, 2 ) NOT NULL;
ALTER TABLE  `_mls` CHANGE `Loan Amount`  `loan_amount` DECIMAL( 10, 2 ) NOT NULL;
ALTER TABLE  `_mls` CHANGE `Effective interest rate/ annum`  `eir` DECIMAL( 22, 2 ) NOT NULL;
ALTER TABLE  `_mls` CHANGE `Identity Card Type`  `identification_type` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Address  - Block Number`  `block` VARCHAR( 512 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Identity Card / Passport No of Borrower`  `uid` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Type of loan`  `loan_type` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Purpose of Loan`  `purpose` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Purpose -  If Others`  `purpose_other` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `_mls` CHANGE `Nationality`  `nationality` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

-- checks
-- SELECT nationality, (SELECT id FROM countries WHERE name = `nationality`) as country_id FROM _mls WHERE nationality IS NOT NULL HAVING country_id IS NULL;
-- SELECT identification_type, (SELECT id FROM identification_types WHERE name = `identification_type`) as identification_type_id FROM _mls WHERE identification_type IS NOT NULL HAVING identification_type_id IS NULL;

update _mls SET identification_type = 'Singapore NRIC No.' WHERE identification_type = 'Singapore NRIC No';
update _mls SET identification_type = 'Other' WHERE identification_type = 'Others';
update _mls SET identification_type = 'Singapore PR No.' WHERE identification_type = 'Singapore PR No';

UPDATE _mls SET date_of_loan = DATE_ADD('1900-01-01', INTERVAL date_of_loan-2 DAY);

-- ALTER TABLE  `borrowers` DROP INDEX  `uid`;

insert into borrowers(name, uid, blk, level, unit, street, building, pincode, address1, annual_income, country_id, identification_type_id) SELECT m.borrower, m.uid, m.block, m.level, m.unit, m.street, m.building, m.pincode, m.address1, m.annual_income, (SELECT id FROM countries WHERE name = m.nationality), (SELECT id FROM identification_types WHERE name = m.identification_type) FROM _mls m ON DUPLICATE KEY UPDATE name = m.borrower, uid=m.uid, blk=m.block, level=m.level, unit = m.unit, street=m.street, building=m.building, pincode=m.pincode, address1=m.address1, annual_income=m.annual_income, country_id=(SELECT id FROM countries WHERE name = m.nationality), identification_type_id=(SELECT id FROM identification_types WHERE name = m.identification_type);

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 ledgers(name, ledger_type_id, borrower_id) VALUES('$name', '1', '$id')

insert into applications(date, account_number, borrower_id, amount, loan_purpose_id, loan_type_id, vt_user_id) 
SELECT  date_of_loan, account_number, (SELECT id FROM borrowers WHERE uid = m.uid), loan_amount, (SELECT id FROM loan_purposes WHERE name = m.purpose_other), (SELECT id FROM loan_types WHERE name = m.loan_type), '1' FROM _mls m;



insert into disbursements(date, account_number, application_id, amount, eir, borrower_annual_income)
SELECT m.date_of_loan, m.account_number, (SELECT id FROM applications WHERE account_number = m.account_number), m.loan_amount, m.eir, m.annual_income FROM _mls m;