create table payment_due_report like projects_loan_installer_super_latest.payment_due_report;

create table `options` LIKE projects_loan_installer_super_latest.`options`;
create table db_upgrades LIKE projects_loan_installer_super_latest.db_upgrades;
create table vt_allowed_ips LIKE projects_loan_installer_super_latest.vt_allowed_ips;
create table vt_api_keys LIKE projects_loan_installer_super_latest.vt_api_keys;
create table vt_audit_log LIKE projects_loan_installer_super_latest.vt_audit_log;
create table vt_classes LIKE projects_loan_installer_super_latest.vt_classes;
create table vt_cronlogs LIKE projects_loan_installer_super_latest.vt_cronlogs;
create table vt_dashboards LIKE projects_loan_installer_super_latest.vt_dashboards;
create table vt_dashboard_perms LIKE projects_loan_installer_super_latest.vt_dashboard_perms;
create table vt_dashlets LIKE projects_loan_installer_super_latest.vt_dashlets;
create table vt_designations LIKE projects_loan_installer_super_latest.vt_designations;
create table vt_email_profiles LIKE projects_loan_installer_super_latest.vt_email_profiles;
create table vt_email_templates LIKE projects_loan_installer_super_latest.vt_email_templates;
create table vt_import_maps LIKE projects_loan_installer_super_latest.vt_import_maps;
create table vt_labels LIKE projects_loan_installer_super_latest.vt_labels;
create table vt_languages LIKE projects_loan_installer_super_latest.vt_languages;
create table vt_message_read_status LIKE projects_loan_installer_super_latest.vt_message_read_status;
create table vt_methods LIKE projects_loan_installer_super_latest.vt_methods;
create table vt_permissions LIKE projects_loan_installer_super_latest.vt_permissions;
create table vt_roles LIKE projects_loan_installer_super_latest.vt_roles;
create table vt_sequences LIKE projects_loan_installer_super_latest.vt_sequences;
create table vt_sessions LIKE projects_loan_installer_super_latest.vt_sessions;
create table vt_sf_tokens LIKE projects_loan_installer_super_latest.vt_sf_tokens;
create table vt_sharing_rules LIKE projects_loan_installer_super_latest.vt_sharing_rules;
create table vt_sms_log LIKE projects_loan_installer_super_latest.vt_sms_log;
create table vt_smtp_servers LIKE projects_loan_installer_super_latest.vt_smtp_servers;
create table vt_translations LIKE projects_loan_installer_super_latest.vt_translations;
create table vt_users LIKE projects_loan_installer_super_latest.vt_users;
create table vt_user_roles LIKE projects_loan_installer_super_latest.vt_user_roles;

INSERT INTO `options` SELECT * FROM projects_loan_installer_super_latest.`options`;
INSERT INTO vt_allowed_ips SELECT * FROM projects_loan_installer_super_latest.vt_allowed_ips;
INSERT INTO vt_api_keys SELECT * FROM projects_loan_installer_super_latest.vt_api_keys;
INSERT INTO vt_audit_log SELECT * FROM projects_loan_installer_super_latest.vt_audit_log;
INSERT INTO vt_classes SELECT * FROM projects_loan_installer_super_latest.vt_classes;
INSERT INTO vt_cronlogs SELECT * FROM projects_loan_installer_super_latest.vt_cronlogs;
INSERT INTO vt_dashboards SELECT * FROM projects_loan_installer_super_latest.vt_dashboards;
INSERT INTO vt_dashboard_perms SELECT * FROM projects_loan_installer_super_latest.vt_dashboard_perms;
INSERT INTO vt_dashlets SELECT * FROM projects_loan_installer_super_latest.vt_dashlets;
INSERT INTO vt_designations SELECT * FROM projects_loan_installer_super_latest.vt_designations;
INSERT INTO vt_email_profiles SELECT * FROM projects_loan_installer_super_latest.vt_email_profiles;
INSERT INTO vt_email_templates SELECT * FROM projects_loan_installer_super_latest.vt_email_templates;
INSERT INTO vt_import_maps SELECT * FROM projects_loan_installer_super_latest.vt_import_maps;
INSERT INTO vt_labels SELECT * FROM projects_loan_installer_super_latest.vt_labels;
INSERT INTO vt_languages SELECT * FROM projects_loan_installer_super_latest.vt_languages;
INSERT INTO vt_message_read_status SELECT * FROM projects_loan_installer_super_latest.vt_message_read_status;
INSERT INTO vt_methods SELECT * FROM projects_loan_installer_super_latest.vt_methods;
INSERT INTO vt_permissions SELECT * FROM projects_loan_installer_super_latest.vt_permissions;
INSERT INTO vt_roles SELECT * FROM projects_loan_installer_super_latest.vt_roles;
INSERT INTO vt_sequences SELECT * FROM projects_loan_installer_super_latest.vt_sequences;
INSERT INTO vt_sessions SELECT * FROM projects_loan_installer_super_latest.vt_sessions;
INSERT INTO vt_sf_tokens SELECT * FROM projects_loan_installer_super_latest.vt_sf_tokens;
INSERT INTO vt_sharing_rules SELECT * FROM projects_loan_installer_super_latest.vt_sharing_rules;
INSERT INTO vt_sms_log SELECT * FROM projects_loan_installer_super_latest.vt_sms_log;
INSERT INTO vt_smtp_servers SELECT * FROM projects_loan_installer_super_latest.vt_smtp_servers;
INSERT INTO vt_translations SELECT * FROM projects_loan_installer_super_latest.vt_translations;
INSERT INTO vt_users SELECT * FROM projects_loan_installer_super_latest.vt_users;
INSERT INTO vt_user_roles SELECT * FROM projects_loan_installer_super_latest.vt_user_roles;

-- ------------------------------------
-- Create a new database for migration
-- ------------------------------------
-- CREATE DATABASE `projects_loan` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- ---------------------------------
-- Creating `countries`
-- ---------------------------------

CREATE TABLE IF NOT EXISTS `countries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=231 ;

--
-- Dumping data for table `countries`
--

INSERT INTO `countries` (`id`, `name`) VALUES
(1, 'Singapore Citizen'),
(2, 'Singapore Permanent Resident'),
(3, 'Afghan'),
(4, 'Albanian'),
(5, 'Algerian'),
(6, 'American'),
(7, 'American Samoa'),
(8, 'Andorran'),
(9, 'Angolan'),
(10, 'Antigua'),
(11, 'Argentinian'),
(12, 'Armenian'),
(13, 'Australian'),
(14, 'Austrian'),
(15, 'Azerbaijani'),
(16, 'Bahamas'),
(17, 'Bahraini'),
(18, 'Bangladeshi'),
(19, 'Barbados'),
(20, 'Belarussian'),
(21, 'Belgian'),
(22, 'Belize'),
(23, 'Benin'),
(24, 'Bhutan'),
(25, 'Bolivian'),
(26, 'Bosnian'),
(27, 'Br Dep Ter Citizen'),
(28, 'Br Nat. Overseas'),
(29, 'Br Overseas Cit.'),
(30, 'Br Protected Pers.'),
(31, 'Brazilian'),
(32, 'British'),
(33, 'British Subject'),
(34, 'Bruneian'),
(35, 'Bulgarian'),
(36, 'Burkina Faso'),
(37, 'Burundi'),
(38, 'Cambodian'),
(39, 'Cameroon'),
(40, 'Canadian'),
(41, 'Cape Verde'),
(42, 'Chadian'),
(43, 'Chilean'),
(44, 'Chinese'),
(45, 'Colombian'),
(46, 'Comoros'),
(47, 'Congo'),
(48, 'Cook Islands'),
(49, 'Costa Rican'),
(50, 'Croatian'),
(51, 'C tral African Rep'),
(52, 'Cuban'),
(53, 'Cypriot'),
(54, 'Czech'),
(55, 'Czechoslovak'),
(56, 'Danish'),
(57, 'Djibouti'),
(58, 'Dominica'),
(59, 'Dominican Republic'),
(60, 'Dutch'),
(61, 'East Timorese'),
(62, 'Ecuadorian'),
(63, 'Egyptian'),
(64, 'Equatorial Guinea'),
(65, 'Eritrean'),
(66, 'Estonian'),
(67, 'Ethiopian'),
(68, 'Fijian'),
(69, 'Filipino'),
(70, 'Finnish'),
(71, 'French'),
(72, 'French Guiana'),
(73, 'French Polynesia'),
(74, 'Gabon'),
(75, 'Gambian'),
(76, 'Georgian'),
(77, 'German'),
(78, 'German,East'),
(79, 'German,West'),
(80, 'Ghanaian'),
(81, 'Greek'),
(82, 'Grenadian'),
(83, 'Guadeloupe'),
(84, 'Guam'),
(85, 'Guatemala'),
(86, 'Guinea'),
(87, 'Guinea-Bissau'),
(88, 'Guyana'),
(89, 'Haitian'),
(90, 'Honduran'),
(91, 'Hong Kong'),
(92, 'Hungarian'),
(93, 'Iceland'),
(94, 'Indian'),
(95, 'Indonesian'),
(96, 'Iranian'),
(97, 'Iraqi'),
(98, 'Irish'),
(99, 'Israeli'),
(100, 'Italian'),
(101, 'Ivory Coast'),
(102, 'Jamaican'),
(103, 'Japanese'),
(104, 'Jordanian'),
(105, 'Kampuchean'),
(106, 'Kazakh'),
(107, 'Kenyan'),
(108, 'Kiribati'),
(109, 'Korea,North'),
(110, 'Korea,South'),
(111, 'Kuwaiti'),
(112, 'Kyrghis'),
(113, 'Kyrgyzstan'),
(114, 'Laotian'),
(115, 'Latvian'),
(116, 'Lebanese'),
(117, 'Lesotho'),
(118, 'Liberian'),
(119, 'Libyan'),
(120, 'Liechtensteiner'),
(121, 'Lithuanian'),
(122, 'Luxembourg'),
(123, 'Macao'),
(124, 'Macedonia'),
(125, 'Madagascar'),
(126, 'Malawi'),
(127, 'Malaysian'),
(128, 'Maldivian'),
(129, 'Mali'),
(130, 'Maltese'),
(131, 'Marshallese'),
(132, 'Martinique'),
(133, 'Mauritanean'),
(134, 'Mauritian'),
(135, 'Mexican'),
(136, 'Micronesian'),
(137, 'Moldavian'),
(138, 'Monaco'),
(139, 'Mongolian'),
(140, 'Moroccan'),
(141, 'Mozambique'),
(142, 'Myanmar'),
(143, 'Namibia'),
(144, 'Nauruan'),
(145, 'Nepalese'),
(146, 'Netherlands'),
(147, 'Netherlands Antil.'),
(148, 'New Caledonia'),
(149, 'New Zealander'),
(150, 'Nicaraguan'),
(151, 'Niger'),
(152, 'Nigerian'),
(153, 'Niue Island'),
(154, 'Norwegian'),
(155, 'Omani'),
(156, 'Pacific Is Trust T'),
(157, 'Pakistani'),
(158, 'Palauan'),
(159, 'Palestinian'),
(160, 'Panamanian'),
(161, 'Papua New Guinea'),
(162, 'Paraguay'),
(163, 'Peruvian'),
(164, 'Pitcairn'),
(165, 'Polish'),
(166, 'Portuguese'),
(167, 'Puerto Rican'),
(168, 'Qatar'),
(169, 'Reunion'),
(170, 'Romanian'),
(171, 'Russian'),
(172, 'Rwandan'),
(173, 'Salvadoran'),
(174, 'Samoan'),
(175, 'Sao Tome & Princi.'),
(176, 'Saudi Arabian'),
(177, 'Senegalese'),
(178, 'Seychelles'),
(179, 'Sierra Leone'),
(180, 'Slovak'),
(181, 'Slovenian'),
(182, 'Solomon Islands'),
(183, 'Somali'),
(184, 'South African'),
(185, 'South Yemen'),
(186, '(Soviet) Russian'),
(187, 'Spanish'),
(188, 'Sri Lankan'),
(189, 'St Kitts & Nevis'),
(190, 'St. Lucia'),
(191, 'St. Vincent'),
(192, 'Stateless'),
(193, 'Sudanese'),
(194, 'Suriname'),
(195, 'Swazi'),
(196, 'Swedish'),
(197, 'Swiss'),
(198, 'Syrian'),
(199, 'Tadzhik'),
(200, 'Taiwanese'),
(201, 'Tajikistani'),
(202, 'Tanzanian'),
(203, 'Thai'),
(204, 'Timorese'),
(205, 'Togo'),
(206, 'Tokelau Islands'),
(207, 'Tonga'),
(208, 'Trinidad & Tobago'),
(209, 'Tunisia'),
(210, 'Turk'),
(211, 'Turkmen'),
(212, 'Tuvalu'),
(213, 'Ugandan'),
(214, 'Ukrainian'),
(215, 'United Arab Em.'),
(216, 'Unknown'),
(217, 'Upper Volta'),
(218, 'Uruguay'),
(219, 'Uzbek'),
(220, 'Vanuatu'),
(221, 'Venezuelan'),
(222, 'Vietnamese'),
(223, 'Wallis And Futuna'),
(224, 'Western Sahara'),
(225, 'Yemen Arab Rep'),
(226, 'Yemeni'),
(227, 'Yugoslav'),
(228, 'Zairean'),
(229, 'Zambian'),
(230, 'Zimbabwean');


-- ---------------------------------
-- Creating `identification_types`
-- ---------------------------------

CREATE TABLE IF NOT EXISTS `identification_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;

--
-- Dumping data for table `identification_types`
--

INSERT INTO `identification_types` (`id`, `name`) VALUES
(1, 'Singapore NRIC No.'),
(2, 'Singapore PR No.'),
(3, 'Foreign Identification No.'),
(4, 'Passport No.'),
(5, 'Other'), 
(6, 'VISIT PASS');

-- ---------------------------------
-- Creating `payer_types`
-- ---------------------------------

CREATE TABLE IF NOT EXISTS `payer_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;

--
-- Dumping data for table `payer_types`
--

INSERT INTO `payer_types` (`id`, `name`) VALUES
(1, 'Borrower'),
(2, 'Surety'),
(3, 'Other');


-- --------------------------
-- Importing LoanApplication
-- --------------------------

-- Rename table
RENAME TABLE LoanApplication to applications;
ALTER TABLE  `applications` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- LoanApplication = APPLICATIONS: Change field names, types, etc
ALTER TABLE `applications` 
CHANGE `LoanAppRegId` `id` INT NOT NULL, 
CHANGE `AppNo` `account_number` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `AppDate` `date` VARCHAR(16) NOT NULL, 
CHANGE `LenderId` `LenderId` INT NOT NULL, 
CHANGE `BorrowerId` `borrower_id` INT NOT NULL, 
CHANGE `LoanAmount` `amount` DECIMAL(14,2) NOT NULL ,
CHANGE `SuretyId` `surety_id` INT NOT NULL, 
CHANGE `LoanTypeId` `loan_purpose_id` INT NOT NULL, 
CHANGE `LoanType` `loan_type_id` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `SecureType` `SecureType` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `SecureValue` `SecureValue` INT NOT NULL, 
CHANGE `SecureInfo` `SecureInfo` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `Remarks` `Remarks` VARCHAR(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `Approved` `loan_status_id` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `ApprovedDate` `loan_status_date` VARCHAR(16) NOT NULL;

-- Get back the dates from excel 1900-01-01 format
-- Only if the data is imported from excel. If CSV then follow the next SQL please.
UPDATE applications SET date = DATE_ADD('1900-01-01', INTERVAL date-2 DAY), loan_status_date = DATE_ADD('1900-01-01', INTERVAL loan_status_date-2 DAY);

UPDATE applications SET loan_status_id = '1' WHERE loan_status_id = 'TRUE';

-- Alter dates
/*UPDATE applications SET 
date = concat(right(trim(date), 4), '-', substring(trim(date), 4, 2), '-', left(trim(date), 2)),
loan_status_date = concat(right(trim(loan_status_date), 4), '-', substring(trim(loan_status_date), 4, 2), '-', left(trim(loan_status_date), 2));
*/
-- Set loan type
UPDATE applications SET loan_type_id = '2' WHERE loan_type_id = 'U';
UPDATE applications SET loan_type_id = '1' WHERE loan_type_id = 'S';

-- Set dates as date type instead of varchar
ALTER TABLE  `applications` CHANGE  `date`  `date` DATE NOT NULL ,
CHANGE  `loan_status_date`  `loan_status_date` DATE NOT NULL;

-- Add new surety ID
ALTER TABLE  `applications` ADD  `new_surety_id` INT NOT NULL AFTER  `surety_id`;

-- Set Indices
ALTER TABLE  `applications` ADD PRIMARY KEY (`id`);
ALTER TABLE  `applications` ADD INDEX (  `account_number` );
ALTER TABLE  `applications` ADD INDEX (  `borrower_id` );
ALTER TABLE  `applications` CHANGE  `id`  `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE  `applications` ADD INDEX (  `new_surety_id` );

-- Add agent ID
ALTER TABLE  `applications` ADD  `agent_id` INT NOT NULL;



ALTER TABLE  `applications` ADD  `vt_user_id` SMALLINT( 5 ) NOT NULL ,
ADD INDEX (  `vt_user_id` );

-- Make vt_users UNSIGNED
ALTER TABLE  `applications` CHANGE  `vt_user_id`  `vt_user_id` SMALLINT( 5 ) UNSIGNED NOT NULL;

-- Only if the migration is from the desktop application
UPDATE `applications` SET `vt_user_id` = '1';


-- ---------------------------------
-- Importing Borrower Individuals
-- ---------------------------------

-- Rename table
RENAME TABLE `BorrowerInd` to borrower_individuals;
ALTER TABLE  `borrower_individuals` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `borrower_individuals` 
CHANGE `BorrowerId` `BorrowerId` INT(4) NOT NULL, 
CHANGE `BType` `BType` VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `FirstName` `FirstName` VARCHAR(69) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `MiddleName` `MiddleName` VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `Lastname` `Lastname` VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `Nationality` `Nationality` VARCHAR(28) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `NRICNo` `NRICNo` VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `CompName` `CompName` VARCHAR(33) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `PaySlip` `PaySlip` VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `NRIC` `NRIC` VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `Designation` `Designation` VARCHAR(19) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `IDDescription` `IDDescription` VARCHAR(19) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `SCitizen` `SCitizen` VARCHAR(19) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `Citizen` `Citizen` VARCHAR(19) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

UPDATE borrower_individuals SET Nationality = 'Singapore Permanent Resident' WHERE Nationality = 'PR';
UPDATE borrower_individuals SET Nationality = 'Singapore Citizen' WHERE Nationality = 'SINGAPOREAN';

-- ---------------------------------
-- Importing Credit Information
-- ---------------------------------

-- Rename table
RENAME TABLE `CreditInformation` to credit_information;
ALTER TABLE  `credit_information` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- ---------------------------
-- Creating `blacklist_reasons`
-- ---------------------------

CREATE TABLE IF NOT EXISTS `blacklist_reasons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `blacklist_reasons`
--

INSERT INTO `blacklist_reasons` (`id`, `name`) VALUES
(1, 'Doesnt pay on time.'),
(2, 'Wrong information provided');

-- --------------------------
-- Importing BorrowerMaster
-- --------------------------

-- Rename table
RENAME TABLE BorrowerMaster to borrowers;
ALTER TABLE  `borrowers` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Change field names, types, etc
ALTER TABLE  `borrowers` CHANGE  `BorrowerId`  `id` INT NOT NULL ,
CHANGE  `BorrowerName`  `name` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `BType`  `borrower_type_id` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `BlackList`  `blacklisted` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;


ALTER TABLE borrowers 
ADD `company` varchar(32) NOT NULL, 
ADD `designation` varchar(32) NOT NULL, 
ADD `blacklist_reason_id` int(11) NOT NULL,
ADD `country_id` int(11) NOT NULL,
ADD `uid` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `annual_income` decimal(14,2) NOT NULL,
ADD `dob` date NOT NULL,
ADD `identification_type_id` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `address1` text COLLATE utf8_unicode_ci NOT NULL,
ADD `address2` text COLLATE utf8_unicode_ci NOT NULL,
ADD `city` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `state` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `country` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `pincode` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `work_phone` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `home_phone` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `hand_phone` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `email` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `alternate_email` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `blk` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `level` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `unit` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `street` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
ADD `building` varchar(128) COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  `borrowers` ADD  `sms_phone` VARCHAR( 128 ) NOT NULL AFTER  `hand_phone`;

ALTER TABLE `borrowers` ADD  `ledger_id` INT NOT NULL AFTER `id`;

-- --------------------------
-- Creating borrower_types
-- --------------------------
CREATE TABLE IF NOT EXISTS `borrower_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

-- Dumping data for table `borrower_types`
INSERT INTO `borrower_types` (`id`, `name`) VALUES
(1, 'Individual'),
(2, 'Corporate');

-- Update field values in borrower table
UPDATE borrowers SET borrower_type_id = '1' WHERE borrower_type_id='I';
UPDATE borrowers SET borrower_type_id = '2' WHERE borrower_type_id='C';

-- Change field types in borrower table
ALTER TABLE  `borrowers` 
CHANGE  `borrower_type_id`  `borrower_type_id` TINYINT( 1 ) NOT NULL ,
CHANGE  `blacklisted`  `blacklisted` TINYINT( 1 ) NOT NULL;

-- Add indices
ALTER TABLE  `borrowers` ADD PRIMARY KEY (  `id` );
ALTER TABLE  `borrowers` CHANGE  `id`  `id` INT( 11 ) NOT NULL AUTO_INCREMENT;


-- Update some borrower information by taking info from borrower individual table
update `borrowers` b set country_id = (select c.id from borrower_individuals bi LEFT JOIN countries c ON c.name=bi.Nationality where name = bi.Nationality and bi.BorrowerId=b.id);
update `borrowers` set uid = (select NRICNo from borrower_individuals where BorrowerId=borrowers.id);
update `borrowers` set company = (select CompName from borrower_individuals where BorrowerId=borrowers.id);
update `borrowers` set designation = (select Designation from borrower_individuals where BorrowerId=borrowers.id);
update `borrowers` set identification_type_id = (select IDDescription from borrower_individuals where BorrowerId=borrowers.id );
update `borrowers` set identification_type_id = 'Singapore NRIC No.' where identification_type_id = 'Singapore NRIC No';
update `borrowers` set identification_type_id = 'Singapore PR No.' where identification_type_id = 'Singapore PR No';
update `borrowers` set identification_type_id = 'Foreign Identification No.' where identification_type_id = 'Foreign Identification Number';
update `borrowers` set identification_type_id = (select id from identification_types where name = borrowers.identification_type_id);

-- Update some borrower information by taking info from credit information table
update `borrowers` set annual_income=(select AnnIncome from credit_information WHERE BorrowerId=borrowers.id);

ALTER TABLE CorresInformation CHANGE DOB DOB varchar(128) not null;

-- Reformat date in CorresInformation table
-- UPDATE CorresInformation SET DOB = concat(right(trim(DOB), 4), '-', substring(trim(DOB), 4, 2), '-', left(trim(DOB), 2));

-- If corresinformation comes from xls
UPDATE CorresInformation SET DOB = DATE_ADD('1900-01-01', INTERVAL DOB-2 DAY);

-- Update some borrower information by taking info from CorresInformation table
update `borrowers` set 
address1 = (select Address1 from CorresInformation where BorrowerId=borrowers.id), 
address2 = (select Address2 from CorresInformation where BorrowerId=borrowers.id), 
city = (select City from CorresInformation where BorrowerId=borrowers.id), 
state = (select State from CorresInformation where BorrowerId=borrowers.id), 
country = (select Country from CorresInformation where BorrowerId=borrowers.id), 
pincode = (select PinCode from CorresInformation where BorrowerId=borrowers.id), 
work_phone = (select WorkPhone from CorresInformation where BorrowerId=borrowers.id), 
home_phone = (select HomePhone from CorresInformation where BorrowerId=borrowers.id), 
hand_phone = (select HandPhone from CorresInformation where BorrowerId=borrowers.id), 
email = (select Email from CorresInformation where BorrowerId=borrowers.id), 
alternate_email = (select AltEmail from CorresInformation where BorrowerId=borrowers.id), 
dob = (select DOB from CorresInformation where BorrowerId=borrowers.id);

UPDATE borrowers SET sms_phone = hand_phone WHERE char_length(hand_phone) = 8;



-- -----------------------------
-- Importing Corporate Borrowers
-- -----------------------------

CREATE TABLE IF NOT EXISTS `BorrowerCorp` (
  `BorrowerId` int(4) DEFAULT NULL,
  `BType` varchar(1) DEFAULT NULL,
  `CompName` varchar(40) DEFAULT NULL,
  `IncorpDate` int(5) DEFAULT NULL,
  `IncorpPlace` varchar(78) DEFAULT NULL,
  `IncorpNo` varchar(10) DEFAULT NULL,
  `ROC` varchar(1) DEFAULT NULL,
  `NRIC` varchar(1) DEFAULT NULL,
  `Nationality` varchar(17) DEFAULT NULL,
  `ContactPerson` varchar(28) DEFAULT NULL,
  `Designation` varchar(11) DEFAULT NULL,
  `NRICNO` varchar(9) DEFAULT NULL,
  `IDDescription` varchar(17) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


-- Rename table
RENAME TABLE BorrowerCorp to borrowers_corporate;
ALTER TABLE  `borrowers_corporate` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `borrowers_corporate` 
CHANGE `BorrowerId` `borrower_id` INT NOT NULL, 
CHANGE `BType` `BType` VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `CompName` `company_name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `IncorpDate` `incorporation_date` VARCHAR( 16 ) NOT NULL, 
CHANGE `IncorpPlace` `incorporation_address` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `IncorpNo` `incorporation_number` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `ROC` `ROC` VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `NRIC` `NRIC` VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `Nationality` `country_id` VARCHAR(17) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `ContactPerson` `contact_phone` INT(16) NOT NULL, 
CHANGE `Designation` `designation` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE `NRICNO` `uid` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE  `IDDescription`  `identification_type_id` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

-- Add primary key
ALTER TABLE  `borrowers_corporate` ADD  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

/*
--Change dates
-- UPDATE borrowers_corporate SET incorporation_date = concat(right(trim(incorporation_date), 4), '-', substring(trim(incorporation_date), 4, 2), '-', left(trim(incorporation_date), 2));
*/
-- Only if the data is imported from excel. If CSV then follow the next SQL please.
UPDATE borrowers_corporate SET incorporation_date = DATE_ADD('1900-01-01', INTERVAL incorporation_date-2 DAY);

ALTER TABLE  `borrowers_corporate` CHANGE  `incorporation_date`  `incorporation_date` DATE NOT NULL;

UPDATE `borrowers_corporate` SET country_id = 'Singapore Citizen' WHERE country_id = 'SINGAPOREAN';
UPDATE `borrowers_corporate` s SET country_id = (select c.id FROM countries c where name = s.country_id);

ALTER TABLE  `borrowers_corporate` CHANGE  `contact_phone`  `contact_person` VARCHAR( 512 ) NOT NULL;

-- Copy the Incorporation Number from borrowers corporate to the borrowers module
UPDATE borrowers SET uid = (SELECT incorporation_number FROM borrowers_corporate WHERE borrower_id = borrowers.id) WHERE borrower_type_id = '2' AND uid = '';

-- --------------------------
-- Importing SuretyMaster
-- --------------------------

-- Rename table
-- RENAME TABLE SuretyMaster to sureties;
-- ALTER TABLE  `sureties` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Change field names, types, etc
-- ALTER TABLE  `sureties` 
-- CHANGE  `SuretyId`  `id` INT NOT NULL ,
-- CHANGE  `SuretyName`  `name` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
-- CHANGE  `SType`  `borrower_type_id` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

-- Update field values in sureties table
-- UPDATE sureties SET borrower_type_id = '1' WHERE borrower_type_id='I';
-- UPDATE sureties SET borrower_type_id = '2' WHERE borrower_type_id='C';

-- Change field types in sureties table
-- ALTER TABLE  `sureties` CHANGE  `borrower_type_id`  `borrower_type_id` TINYINT( 1 ) NOT NULL;

-- Add Indices
-- ALTER TABLE  `sureties` ADD PRIMARY KEY (  `id` );

-- --------------------------
-- Creating loan_statuses
-- --------------------------

CREATE TABLE IF NOT EXISTS `loan_statuses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4;

--
-- Dumping data for table `loan_statuses`
--

INSERT INTO `loan_statuses` (`id`, `name`) VALUES
(1, 'Approved'),
(2, 'On Hold'),
(3, 'Rejected');

-- --------------------------
-- Creating `loan_types`
-- --------------------------

CREATE TABLE IF NOT EXISTS `loan_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `loan_types`
--

INSERT INTO `loan_types` (`id`, `name`) VALUES
(1, 'Secured'),
(2, 'Unsecured');

-- --------------------------
-- Importing LoanDisbursement(from CSV)
-- --------------------------

-- Rename table
-- RENAME TABLE `TABLE 25` to disbursements;
ALTER TABLE  `disbursements` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Change field names, types, etc
ALTER TABLE  `disbursements` 
CHANGE  `LoanDisRegId`  `id` INT NOT NULL ,
CHANGE  `VoucherNo`  `voucher_number` VARCHAR( 16 ) NOT NULL ,
CHANGE  `VoucherDate`  `date` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `LoanAppRegId`  `application_id` INT NOT NULL ,
CHANGE  `LoanAccountNo`  `account_number` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `LoanAmount`  `amount` DECIMAL(14,2) NOT NULL ,
CHANGE  `PaymentMode`  `PaymentMode` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PMDate`  `PMDate` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PMTransNo`  `transaction_number` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PMBankName`  `PMBankName` VARCHAR( 3 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE  `InterestRate`  `interest_rate` DECIMAL(14,2) NOT NULL ,
CHANGE  `InterestPeriod`  `interest_frequency_id` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `InterestFrom`  `InterestFrom` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `GracePeriod`  `GracePeriod` INT( 1 ) NOT NULL ,
CHANGE  `GraceType`  `GraceType` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `CalculationMethod`  `interest_calculation_method_id` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `NoPayment`  `installment_count` INT( 2 ) NOT NULL ,
CHANGE  `FreqencyNo`  `FreqencyNo` INT( 1 ) NOT NULL ,
CHANGE  `FreqencyType`  `payment_frequency_id` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `ModeOfPayment`  `ModeOfPayment` VARCHAR( 3 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE  `OverPayment`  `OverPayment` VARCHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `LatePaymentInterestRate`  `late_interest_rate` DECIMAL(14,2) NOT NULL ,
CHANGE  `PaymentStartDate`  `first_payment_date` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PaymentEndDate`  `last_payment_date` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `Remarks`  `Remarks` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `LenderId`  `LenderId` INT( 2 ) NOT NULL ,
CHANGE  `Approved`  `Approved` VARCHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `CloseLoan`  `status` VARCHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PayTypeID`  `payment_type_id` INT( 1 ) NOT NULL ,
CHANGE  `EMICal`  `EMICal` INT( 1 ) NOT NULL, 
CHANGE  `TypeID`  `book_id` INT NOT NULL ,
CHANGE  `Rest`  `Rest` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `MailAddress`  `MailAddress` VARCHAR( 82 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `BLanguage`  `language_code` VARCHAR( 7 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `TermsAndCondition`  `terms_and_conditions` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `Amount1`  `Amount1` DECIMAL(14,2) NOT NULL ,
CHANGE  `Amount2`  `Amount2` DECIMAL(14,2) NOT NULL ,
CHANGE  `TypeID1`  `TypeID1` INT NOT NULL ,
CHANGE  `PayTypeID1`  `PayTypeID1` INT NOT NULL ,
CHANGE  `VoucherNo1`  `VoucherNo1` VARCHAR( 4 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  `disbursements` 
CHANGE  `Amount1`  `book_amount` DECIMAL(14,2) NOT NULL ,
CHANGE  `Amount2`  `book_amount1` DECIMAL(14,2) NOT NULL ,
CHANGE  `TypeID1`  `book_id1` INT NOT NULL ,
CHANGE  `PayTypeID1`  `payment_type_id1` INT NOT NULL ,
CHANGE  `VoucherNo1`  `voucher_number1` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE  `disbursements` ADD  `second_payment_date` DATE NOT NULL AFTER  `first_payment_date`;

-- Add extra fields
ALTER TABLE  `disbursements` 
ADD agent_principal_share decimal(14,2) NOT NULL,
ADD agent_interest_share decimal(14,2) NOT NULL,
ADD agent_late_fee_share decimal(14,2) NOT NULL,
ADD agent_late_interest_share decimal(14,2) NOT NULL,
ADD acceptance_fee decimal(14,2) NOT NULL,
ADD late_repayment_fee decimal(14,2) NOT NULL,
ADD legal_fee decimal(14,2) NOT NULL,
ADD renewal_fee decimal(14,2) NOT NULL,
ADD contract_variation_fee decimal(14,2) NOT NULL,
ADD cheque_dishonour_fee decimal(14,2) NOT NULL,
ADD termination_fee decimal(14,2) NOT NULL,
ADD acceptance_fee_percent decimal(14,2) NOT NULL,
ADD renewal_fee_percent decimal(14,2) NOT NULL,
ADD late_fee decimal(14,2) NOT NULL,
ADD late_fee_percent decimal(14,2) NOT NULL,
ADD contract_variation_fee_percent decimal(14,2) NOT NULL,
ADD cheque_dishonour_fee_percent decimal(14,2) NOT NULL,
ADD termination_fee_percent decimal(14,2) NOT NULL,
ADD legal_fee_percent decimal(14,2) NOT NULL,
ADD CollectionFees_unknown decimal(14,2) NOT NULL,
ADD CollectionFeesP_unknown decimal(14,2) NOT NULL,
ADD late_repayment_fee_percent decimal(14,2) NOT NULL;

-- Alter dates
UPDATE disbursements SET 
date = concat(right(trim(date), 4), '-', substring(trim(date), 4, 2), '-', left(trim(date), 2)),
PMDate = concat(right(trim(PMDate), 4), '-', substring(trim(PMDate), 4, 2), '-', left(trim(PMDate), 2)), 
first_payment_date = concat(right(trim(first_payment_date), 4), '-', substring(trim(first_payment_date), 4, 2), '-', left(trim(first_payment_date), 2)), 
last_payment_date = concat(right(trim(last_payment_date), 4), '-', substring(trim(last_payment_date), 4, 2), '-', left(trim(last_payment_date), 2)), 
PMDate1 = concat(right(trim(PMDate1), 4), '-', substring(trim(PMDate1), 4, 2), '-', left(trim(PMDate1), 2));

-- Alter status
ALTER TABLE  `disbursements` CHANGE  `status`  `status` VARCHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT  '0 = active, 1 = closed';
UPDATE disbursements SET status = '1' WHERE status='TRUE';
UPDATE disbursements SET status = '0' WHERE status='FALSE';

-- Reset date field types from varchar to date
ALTER TABLE  `disbursements` 
CHANGE  `date`  `date` DATE NOT NULL ,
CHANGE  `PMDate`  `PMDate` DATE NOT NULL ,
CHANGE  `first_payment_date`  `first_payment_date` DATE NOT NULL ,
CHANGE  `last_payment_date`  `last_payment_date` DATE NOT NULL ,
CHANGE  `PMDate1`  `PMDate1` DATE NOT NULL;

-- Update Language Codes
UPDATE disbursements SET language_code = 'en' WHERE language_code = 'ENGLISH';
UPDATE disbursements SET language_code = 'en' WHERE language_code = '6';
UPDATE disbursements SET language_code = 'en' WHERE language_code = 'E';
UPDATE disbursements SET language_code = 'en' WHERE language_code = '';
UPDATE disbursements SET language_code = 'zh' WHERE language_code = 'CHINESE';
UPDATE disbursements SET language_code = 'zh' WHERE language_code = 'c';

-- Add Indices
ALTER TABLE  `disbursements` ADD PRIMARY KEY (  `id` );
ALTER TABLE  `disbursements` ADD INDEX (  `account_number` );
ALTER TABLE  `disbursements` CHANGE  `id`  `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE  `disbursements` ADD INDEX (  `application_id` );



-- --------------------------
-- Importing LoanRepayments(from CSV)
-- --------------------------

-- Rename table
-- RENAME TABLE `TABLE 26` to repayments;
ALTER TABLE  `repayments` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Change field names, types, etc
ALTER TABLE  `repayments` CHANGE  `LoanRepaymentId`  `id` INT NOT NULL ,
CHANGE  `LoanDisRegId`  `disbursement_id` INT NOT NULL ,
CHANGE  `LoanAccNo`  `account_number` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `VoucherDate`  `date` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `VoucherNo`  `voucher_number` INT NOT NULL ,
CHANGE  `PaidBy`  `payer_type_id` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `OtherPayName`  `OtherPayName` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `OtherPayAddress`  `OtherPayAddress` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `OtherNRIC`  `OtherNRIC` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `Amount`  `amount` DECIMAL(14,2) NOT NULL, 
CHANGE  `PaymentMode`  `PaymentMode` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PayDetDate`  `PayDetDate` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PayDetTransChqNo`  `transaction_number` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PayDetBank`  `PayDetBank` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `Remarks`  `Remarks` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `RegularPay`  `RegularPay` DECIMAL(14,2) NOT NULL ,
CHANGE  `PrincipalPay`  `paid_principal` DECIMAL(14,2) NOT NULL ,
CHANGE  `InterestPay`  `paid_interest` DECIMAL(14,2) NOT NULL ,
CHANGE  `PrincipalAdj`  `PrincipalAdj` INT( 1 ) NOT NULL ,
CHANGE  `InterestAdj`  `InterestAdj` INT( 1 ) NOT NULL, 
CHANGE  `AmountAdj`  `AmountAdj` INT NOT NULL ,
CHANGE  `PermitFees`  `permit_fee` DECIMAL(14,2) NOT NULL ,
CHANGE  `AFees`  `acceptance_fee` DECIMAL(14,2) NOT NULL ,
CHANGE  `RFees`  `renewal_fee` INT NOT NULL ,
CHANGE  `LateFees`  `late_fee` DECIMAL(14,2) NOT NULL ,
CHANGE  `VarFees`  `contract_variation_fee` DECIMAL(14,2) NOT NULL ,
CHANGE  `ChequeFees`  `cheque_dishonoured_fee` DECIMAL(14,2) NOT NULL ,
CHANGE  `TerFees`  `termination_fee` DECIMAL(14,2) NOT NULL ,
CHANGE  `LegalFees`  `legal_fee` INT NOT NULL ,
CHANGE  `Principal`  `principal` DECIMAL(14,2) NOT NULL, 
CHANGE  `Interest`  `interest` DECIMAL(14,2) NOT NULL ,
CHANGE  `LenderID`  `LenderID` INT NOT NULL ,
CHANGE  `RegularPrincipal`  `RegularPrincipal` DECIMAL(14,2) NOT NULL ,
CHANGE  `RegularInterest`  `RegularInterest` DECIMAL(14,2) NOT NULL ,
CHANGE  `AmountPrincipal`  `AmountPrincipal` INT NOT NULL ,
CHANGE  `AmountInterest`  `late_interest` DECIMAL(14,2) NOT NULL ,
CHANGE  `ReceiptNo`  `receipt_number` varchar(128) NOT NULL ,
CHANGE  `OutStanding`  `outstanding_principal` DECIMAL( 43, 13 ) NOT NULL ,
CHANGE  `NoofLateDays`  `NoofLateDays` INT NOT NULL ,
CHANGE  `NoofLatePayment`  `NoofLatePayment` INT NOT NULL, 
CHANGE  `CheqDishonour`  `CheqDishonour` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PayTypeID`  `payment_type_id` INT NOT NULL ,
CHANGE  `TypeID`  `book_id` INT NOT NULL ,
CHANGE  `CollectionFees`  `CollectionFees` INT( 1 ) NOT NULL;

ALTER TABLE  `repayments` CHANGE  `outstanding_principal`  `outstanding_principal` DECIMAL(14,2) NOT NULL;
ALTER TABLE  `repayments` CHANGE  `Remarks`  `remarks` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

-- Create new field called application ID

ALTER TABLE  `repayments` 
ADD  `application_id` INT NOT NULL AFTER  `disbursement_id` ,
ADD INDEX (  `application_id` );

-- SET application ID as per account_number

UPDATE repayments SET application_id = (SELECT application_id FROM disbursements WHERE disbursements.id = repayments.disbursement_id LIMIT 1);


-- Edit the Other Payer fields name and type

ALTER TABLE  `repayments` 
CHANGE  `OtherPayName`  `other_payer_name` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `OtherPayAddress`  `other_payer_address` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `OtherNRIC`  `other_payer_uid` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;


-- Add Indices
ALTER TABLE  `repayments` ADD PRIMARY KEY (  `id` );
ALTER TABLE  `repayments` ADD INDEX (  `account_number` );
ALTER TABLE  `repayments` ADD INDEX (  `disbursement_id` );
ALTER TABLE  `repayments` CHANGE  `id`  `id` INT( 11 ) NOT NULL AUTO_INCREMENT;

-- Alter dates
UPDATE repayments SET 
date = concat(right(trim(date), 4), '-', substring(trim(date), 4, 2), '-', left(trim(date), 2)),
PayDetDate = concat(right(trim(PayDetDate), 4), '-', substring(trim(PayDetDate), 4, 2), '-', left(trim(PayDetDate), 2));

-- Reset date field types from varchar to date
ALTER TABLE  `repayments` 
CHANGE  `date`  `date` DATE NOT NULL ,
CHANGE  `PayDetDate`  `PayDetDate` DATE NOT NULL;

UPDATE repayments SET payer_type_id = (SELECT id FROM payer_types WHERE name = repayments.payer_type_id);

-- --------------------------
-- Importing PaymentSchedule
-- --------------------------

-- Rename table
RENAME TABLE `PaymentSchedule` to payment_schedule;
ALTER TABLE  `payment_schedule` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Change field names, types, etc
ALTER TABLE  `payment_schedule` 
CHANGE  `LoanDisRegID`  `disbursement_id` INT NOT NULL ,
CHANGE  `PaymentDate`  `date` VARCHAR(16) NOT NULL ,
CHANGE  `Amount`  `amount` DECIMAL(14,2) NOT NULL ,
CHANGE  `Principal`  `principal` DECIMAL(14,2) NOT NULL ,
CHANGE  `Interest`  `interest` DECIMAL(14,2) NOT NULL;

-- Add id = P/K and a couple of other fields
ALTER TABLE  `payment_schedule` ADD  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE  `payment_schedule` 
ADD  `google_calendar_url` VARCHAR( 512 ) NOT NULL ,
ADD  `skip` TINYINT( 1 ) NOT NULL;

ALTER TABLE  `payment_schedule` ADD `application_id` INT NOT NULL AFTER `id`;
UPDATE payment_schedule SET application_id = (SELECT application_id FROM disbursements WHERE id = disbursement_id) WHERE application_id = '0';

-- Add index on disbursement_id
ALTER TABLE  `payment_schedule` ADD INDEX (  `disbursement_id` );

-- Get back the dates from excel 1900-01-01 format
UPDATE payment_schedule SET date = DATE_ADD('1900-01-01', INTERVAL date-2 DAY);

-- Get back dates if imported from CSV
-- UPDATE payment_schedule SET date = concat(right(trim(date), 4), '-', substring(trim(date), 4, 2), '-', left(trim(date), 2));


-- ---------------------------------
-- Importing Entries(Table 28) as Vouchers
-- ---------------------------------

-- Rename table
-- RENAME TABLE `TABLE 28` to vouchers;
ALTER TABLE  `vouchers` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Change field names, types, etc
ALTER TABLE  `vouchers` 
CHANGE  `EntryId`  `entry_id` INT NOT NULL ,
CHANGE  `VoucherDate`  `date` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `VoucherNo`  `voucher_number` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `TransType`  `transaction_type` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PayType`  `pay_type` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `RefNo`  `reference_number` INT NOT NULL ,
CHANGE  `PaymentType`  `PaymentType` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `Amount`  `amount` DECIMAL(14,2) NOT NULL ,
CHANGE  `Narration`  `narration` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PaymentMode`  `PaymentMode` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE  `PMDate`  `PMDate` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PMTransNo`  `PMTransNo` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PMBankName`  `PMBankName` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `LenderID`  `LenderID` INT NOT NULL ,
CHANGE  `BorrowerID`  `borrower_id` INT NOT NULL ,
CHANGE  `SuretyID`  `surety_id` INT NOT NULL ,
CHANGE  `PaidBy`  `payer_type_id` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `OtherPayName`  `OtherPayName` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `OtherPayAddress`  `OtherPayAddress` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `OtherNRIC`  `OtherNRIC` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
CHANGE  `LoanAccountNo`  `account_number` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `CheqDishonour`  `CheqDishonour` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `PayTypeID`  `payment_type_id` INT NOT NULL ,
CHANGE  `EntryMode`  `entry_mode` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `AccountId`  `ledger_id` INT NOT NULL ,
CHANGE  `TypeAccountId`  `book_ledger_id` INT NOT NULL ,
CHANGE  `ParentEntryID`  `ParentEntryID` INT( 10 ) NOT NULL ,
CHANGE  `TypeId`  `book_id` INT NOT NULL;

ALTER TABLE  `vouchers` CHANGE  `PMBankName`  `particulars` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `vouchers` CHANGE  `PMTransNo`  `transaction_number` VARCHAR( 14 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `vouchers` CHANGE  `ParentEntryID`  `parent_voucher_id` INT NOT NULL;

-- Add indices
ALTER TABLE  `vouchers` ADD  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

-- Change parent voucher ID for bank transfers to use the new ID instead of entry_id
UPDATE vouchers v SET parent_voucher_id = (
	SELECT id FROM (
		SELECT * FROM vouchers
	) as tmp 
	WHERE entry_id = v.parent_voucher_id)
WHERE entry_mode = 'T';

-- update apples
--    set price = (
--       select price from (
--          select * from apples
--       ) as x
--       where variety = 'gala')
--    where variety = 'fuji';


-- Alter dates
UPDATE vouchers SET 
date = concat(right(trim(date), 4), '-', substring(trim(date), 4, 2), '-', left(trim(date), 2)),
PMDate = concat(right(trim(PMDate), 4), '-', substring(trim(PMDate), 4, 2), '-', left(trim(PMDate), 2));

-- Reset date field types from varchar to date
ALTER TABLE  `vouchers` 
CHANGE  `date`  `date` DATE NOT NULL ,
CHANGE  `PMDate`  `PMDate` DATE NOT NULL;

-- ---------------------------------
-- Creating `loan_purposes`
-- ---------------------------------

CREATE TABLE IF NOT EXISTS `loan_purposes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `loan_purposes`
--

INSERT INTO `loan_purposes` (`id`, `name`) VALUES
(1, 'Business'),
(2, 'Education'),
(3, 'Medical'),
(4, 'Renovation'),
(5, 'Domestic'),
(6, 'Others'),
(7, 'Personal'),
(8, 'Housing'),
(9, 'Vehicle');


-- ---------------------------------
-- Importing Surety Individual
-- ---------------------------------

-- Rename table
RENAME TABLE `SuretyInd` to sureties;
ALTER TABLE  `sureties` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE  `sureties` 
CHANGE  `SuretyId`  `id` INT( 3 ) NOT NULL ,
CHANGE  `SType`  `borrower_type_id` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `FirstName`  `name` VARCHAR( 60 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `Nationality`  `country_id` VARCHAR( 28 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `NRICNo`  `uid` VARCHAR( 21 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `IDDescription`  `identification_type_id` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

update sureties set borrower_type_id = '1' where borrower_type_id='I';

-- Fix identification types typos
update `sureties` set identification_type_id = 'Singapore NRIC No.' where identification_type_id = 'Singapore NRIC No';
update `sureties` set identification_type_id = 'Singapore PR No.' where identification_type_id = 'Singapore PR No';
update `sureties` set identification_type_id = 'Foreign Identification No.' where identification_type_id = 'Foreign Identification Number';
update `sureties` set identification_type_id = 'Singapore NRIC No.' where identification_type_id = 'NRIC';

-- Fix nationality typos
UPDATE sureties SET country_id = 'Singapore Citizen' WHERE country_id = 'C';
UPDATE sureties SET country_id = 'Singapore Citizen' WHERE country_id = 'F';
UPDATE sureties SET country_id = 'Singapore Citizen' WHERE country_id = 'PR';
UPDATE sureties SET country_id = 'Singapore Citizen' WHERE country_id = 'SINGAPOREAN';

-- Set country_id from lookup table
update `sureties` s set country_id = (select c.id FROM countries c where name = s.country_id);

-- Set identification_type_id from lookup table
update `sureties` set identification_type_id = (select id from identification_types where name = sureties.identification_type_id);

-- 
-- A very important piece of SQL - for merging sureties with borrowers.
-- 
insert into `borrowers`(name, borrower_type_id, country_id, uid, identification_type_id, company, designation) select name, borrower_type_id, country_id, uid, identification_type_id, CompName, Designation from sureties on duplicate key update `borrowers`.name = sureties.name, borrowers.borrower_type_id = sureties.borrower_type_id;

-- 
-- Updating surety_ids in application table
-- 

update applications set new_surety_id = (select borrowers.id FROM sureties LEFT JOIN borrowers ON (sureties.name=borrowers.name AND sureties.uid = borrowers.uid) where sureties.id = applications.surety_id LIMIT 1);

-- ---------------------------------
-- Importing Ledgers
-- ---------------------------------

-- Rename table
RENAME TABLE `Account` to ledgers;
ALTER TABLE  `ledgers` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Change field name, types, etc
ALTER TABLE  `ledgers` CHANGE  `AccountId`  `id` INT NOT NULL ,
CHANGE  `AccountName`  `name` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `MajorHeadID`  `ledger_type_id` INT NOT NULL ,
CHANGE  `BorrowerId`  `borrower_id` INT NOT NULL ,
CHANGE  `TypeId`  `book_id` INT NOT NULL;

-- Define Indices
ALTER TABLE  `ledgers` ADD PRIMARY KEY (  `id` );
ALTER TABLE  `ledgers` CHANGE  `id`  `id` INT( 11 ) NOT NULL AUTO_INCREMENT;



-- 
-- Linking ledgers with borrowers again
-- 
update `borrowers` set ledger_id = (select id FROM ledgers where borrower_id=borrowers.id );

INSERT INTO  `ledgers` (`name` ,`ledger_type_id` ,`borrower_id`) SELECT name, '3', id FROM borrowers WHERE ledger_id = '0';
update `borrowers` set ledger_id = (select id FROM ledgers where borrower_id=borrowers.id ) WHERE ledger_id = '0';

-- Create a Bad Debt Ledger
INSERT INTO  `ledgers` (`id` ,`name` ,`ledger_type_id` ,`borrower_id` ,`book_id`) VALUES (NULL ,  'Bad Debt',  '3',  '',  '');



-- ---------------------------
-- Creating `time_frequencies`
-- ---------------------------

CREATE TABLE IF NOT EXISTS `time_frequencies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `ratio` int(11) NOT NULL,
  `tenure` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;

--
-- Dumping data for table `time_frequencies`
--

INSERT INTO `time_frequencies` (`id`, `name`, `ratio`, `tenure`) VALUES
(1, 'daily', 365, 1),
(2, 'weekly', 52, 7),
(3, 'bi-weekly', 26, 14),
(4, 'monthly', 12, 0),
(5, 'annually', 1, 0);

-- Updating disbursement time frequencies
UPDATE disbursements SET interest_frequency_id = '4' WHERE interest_frequency_id = 'M';
UPDATE disbursements SET interest_frequency_id = '2' WHERE interest_frequency_id = 'W';
UPDATE disbursements SET interest_frequency_id = '5' WHERE interest_frequency_id = 'Y';
UPDATE disbursements SET payment_frequency_id = '4' WHERE payment_frequency_id = 'M';
UPDATE disbursements SET payment_frequency_id = '2' WHERE payment_frequency_id = 'W';
UPDATE disbursements SET payment_frequency_id = '5' WHERE payment_frequency_id = 'Y';

-- ---------------------------------------
-- Creating `interest_calculation_methods`
-- ---------------------------------------

CREATE TABLE IF NOT EXISTS `interest_calculation_methods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `code` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `interest_calculation_methods`
--

INSERT INTO `interest_calculation_methods` (`id`, `name`, `code`) VALUES
(1, 'Simple Interest', 'S'),
(2, 'Rest', 'R');


-- ---------------------------------------
-- Creating `payment_types`
-- ---------------------------------------

CREATE TABLE IF NOT EXISTS `payment_types` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(12) CHARACTER SET utf8 NOT NULL,
  `code` varchar(1) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

--
-- Dumping data for table `payment_types`
--

INSERT INTO `payment_types` (`id`, `name`, `code`) VALUES
(1, 'Cash', 'C'),
(2, 'Cheque', 'Q'),
(3, 'Transfer', 'T'),
(4, 'Demand Draft', 'D'),
(5, 'Money Order', 'M'),
(6, 'Postal Order', 'P');


-- ----------------------------------
-- Table structure for table `agents`
-- ----------------------------------

CREATE TABLE IF NOT EXISTS `agents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `ledger_id` int(11) NOT NULL,
  `principal_share` decimal(14,2) NOT NULL,
  `interest_share` decimal(14,2) NOT NULL,
  `late_fee_share` decimal(14,2) NOT NULL,
  `late_interest_share` decimal(14,2) NOT NULL,
  `vt_user_id` int(11) NOT NULL,
  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;

-- Add indices
ALTER TABLE  `agents` ADD INDEX (  `ledger_id` );

--
-- Dumping data for table `agents`
--

INSERT INTO `agents` (`id`, `name`, `ledger_id`, `principal_share`, `interest_share`, `late_fee_share`, `late_interest_share`) VALUES
(1, 'Company(default)', 1, '0.00', '0.00', '0.00', '0.00');


-- ----------------------------------
-- Table structure for table `ledger_types`
-- ----------------------------------

CREATE TABLE IF NOT EXISTS `ledger_types` (
  `id` int(1) NOT NULL DEFAULT '0',
  `name` varchar(11) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `ledger_types`
--

INSERT INTO `ledger_types` (`id`, `name`) VALUES
(1, 'Asset'),
(2, 'Liabilities'),
(3, 'ProfitLoss'),
(4, 'CashBank');

-- ---------------------------------
-- Importing book_types as books
-- ---------------------------------

-- Rename table
RENAME TABLE `BookType` to books;
ALTER TABLE  `books` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Changing field names, types, etc.
ALTER TABLE  `books` 
CHANGE  `TypeId`  `id` INT( 2 ) NOT NULL ,
CHANGE  `TypeName`  `name` VARCHAR( 14 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `Bank`  `Bank` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `OpeningBalance`  `opening_balance` INT( 6 ) NOT NULL ,
CHANGE  `ODAmount`  `ODAmount` INT( 1 ) NOT NULL ,
CHANGE  `ClosingBalance`  `closing_balance` INT( 1 ) NOT NULL ,
CHANGE  `ModeType`  `ModeType` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `Prefix`  `Prefix` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `Suffix`  `Suffix` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
CHANGE  `StartNo`  `StartNo` INT( 1 ) NOT NULL ,
CHANGE  `Width`  `Width` INT( 1 ) NOT NULL ,
CHANGE  `MaxNo`  `MaxNo` INT( 4 ) NOT NULL ,
CHANGE  `LenderId`  `LenderId` INT( 2 ) NOT NULL;

-- Define Indices
ALTER TABLE  `books` ADD PRIMARY KEY (  `id` );
ALTER TABLE  `books` CHANGE  `id`  `id` INT( 2 ) NOT NULL AUTO_INCREMENT;

ALTER TABLE  `books` CHANGE  `name`  `name` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

-- ---------------------------------
-- Importing CoBorrower
-- ---------------------------------

CREATE TABLE IF NOT EXISTS `CoBorrower` (
  `LoanAppRegId` int(4) DEFAULT NULL,
  `BorrowerId` int(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Rename table
RENAME TABLE `CoBorrower` to co_borrowers;
ALTER TABLE  `co_borrowers` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Change field types, names, etc
ALTER TABLE  `co_borrowers` 
CHANGE  `LoanAppRegId`  `application_id` INT NOT NULL ,
CHANGE  `BorrowerId`  `borrower_id` INT NOT NULL;

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

-- ---------------------------------
-- Creating co_sureties
-- ---------------------------------
CREATE TABLE IF NOT EXISTS `co_sureties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `application_id` int(11) NOT NULL,
  `surety_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;


-- ---------------------------
-- Importing LoanDisPermitFees
-- ---------------------------

-- Rename table
RENAME TABLE `LoanDisPermitFees` to disbursement_fee;
ALTER TABLE  `disbursement_fee` ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Changing field name, types, etc
ALTER TABLE `disbursement_fee` CHANGE `LoanDisRegID` `disbursement_id` INT(4) NOT NULL, 
CHANGE `AFees` `acceptance_fee` DECIMAL(10, 2) NOT NULL, 
CHANGE `AFeesP` `acceptance_fee_percent` DECIMAL(10, 2) NOT NULL, 
CHANGE `RFees` `renewal_fee` DECIMAL(10, 2) NOT NULL, 
CHANGE `RFeesP` `renewal_fee_percent` DECIMAL(10, 2) NOT NULL, 
CHANGE `LateFees` `late_fee` DECIMAL(10, 2) NOT NULL, 
CHANGE `LateFeesP` `late_fee_percent` DECIMAL(10, 2) NOT NULL, 
CHANGE `VarFees` `contract_variation_fee` DECIMAL(10, 2) NOT NULL, 
CHANGE `VarFeesP` `contract_variation_fee_percent` DECIMAL(10, 2) NOT NULL, 
CHANGE `ChequeFees` `cheque_dishonour_fee` DECIMAL(10, 2) NOT NULL, 
CHANGE `ChequeFeesP` `cheque_dishonour_fee_percent` DECIMAL(10, 2) NOT NULL, 
CHANGE `TerFees` `termination_fee` DECIMAL(10, 2) NOT NULL, 
CHANGE `TerFeesP` `termination_fee_percent` DECIMAL(10, 2) NOT NULL, 
CHANGE `LegalFees` `legal_fee` DECIMAL(10, 2) NOT NULL, 
CHANGE `LegalFeesP` `legal_fee_percent` DECIMAL(10, 2) NOT NULL;

-- Import dibursement fee data to disbursement table

update disbursements set 
acceptance_fee = (select acceptance_fee from disbursement_fee where disbursement_id=id), 
late_repayment_fee = (select late_repayment_fee from disbursement_fee where disbursement_id=id), 
legal_fee = (select legal_fee from disbursement_fee where disbursement_id=id), 
renewal_fee = (select renewal_fee from disbursement_fee where disbursement_id=id), 
contract_variation_fee = (select contract_variation_fee from disbursement_fee where disbursement_id=id), 
cheque_dishonour_fee = (select cheque_dishonour_fee from disbursement_fee where disbursement_id=id), 
termination_fee = (select termination_fee from disbursement_fee where disbursement_id=id), 
acceptance_fee_percent = (select acceptance_fee_percent from disbursement_fee where disbursement_id=id), 
renewal_fee_percent = (select renewal_fee_percent from disbursement_fee where disbursement_id=id), 
late_fee = (select late_fee from disbursement_fee where disbursement_id=id), 
late_fee_percent = (select late_fee_percent from disbursement_fee where disbursement_id=id), 
contract_variation_fee_percent	 = (select contract_variation_fee_percent from disbursement_fee where disbursement_id=id), 
cheque_dishonour_fee_percent = (select cheque_dishonour_fee_percent from disbursement_fee where disbursement_id=id), 
termination_fee_percent = (select termination_fee_percent from disbursement_fee where disbursement_id=id), 
legal_fee_percent = (select legal_fee_percent from disbursement_fee where disbursement_id=id), 
CollectionFees_unknown = (select CollectionFees_unknown from disbursement_fee where disbursement_id=id), 
CollectionFeesP_unknown = (select CollectionFeesP_unknown from disbursement_fee where disbursement_id=id);


--
-- Table structure for table `loan_plans`
--

CREATE TABLE IF NOT EXISTS `loan_plans` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `interest_rate` decimal(14,2) DEFAULT NULL,
  `late_interest_rate` decimal(14,2) DEFAULT NULL,
  `interest_frequency_id` int(11) NOT NULL,
  `interest_calculation_method_id` int(11) NOT NULL,
  `acceptance_fee_percent` decimal(14,2) DEFAULT NULL,
  `late_repayment_fee_percent` decimal(14,2) DEFAULT NULL,
  `legal_fee_percent` decimal(14,2) DEFAULT NULL,
  `renewal_fee_percent` decimal(14,2) DEFAULT NULL,
  `contract_variation_fee_percent` decimal(14,2) DEFAULT NULL,
  `cheque_dishonour_fee_percent` decimal(14,2) DEFAULT NULL,
  `termination_fee_percent` decimal(14,2) DEFAULT NULL,
  `late_fee_percent` decimal(14,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `loan_plans`
--

INSERT INTO `loan_plans` (`id`, `name`, `interest_rate`, `late_interest_rate`, `interest_frequency_id`, `interest_calculation_method_id`, `acceptance_fee_percent`, `late_repayment_fee_percent`, `legal_fee_percent`, `renewal_fee_percent`, `contract_variation_fee_percent`, `cheque_dishonour_fee_percent`, `termination_fee_percent`, `late_fee_percent`) VALUES
(1, '10% Annual Flat', '10.00', '10.00', 5, 1, '10.00', '10.00', '10.00', '10.00', '10.00', '10.00', '10.00', '10.00'),
(2, '20% Monthly flat', '20.00', '20.00', 4, 1, '20.00', '20.00', '20.00', '20.00', '20.00', '20.00', '20.00', '20.00');



-- ----------------------------------
-- Creating API Connection Module
-- ----------------------------------

CREATE TABLE api_connections (
	id INT(10) unsigned NOT NULL AUTO_INCREMENT,
	name VARCHAR(128) NOT NULL,
	url VARCHAR(512) NOT NULL, 
	secret_key VARCHAR(128) NOT NULL, 
  
	PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;



-- ----------------------------------
-- Creating Calls module for followup
-- ----------------------------------

CREATE TABLE IF NOT EXISTS `Followup` (
  `FollowupId` int(4) DEFAULT NULL,
  `LoanDisRegId` int(4) DEFAULT NULL,
  `CallDate` int(5) DEFAULT NULL,
  `NextCallDate` int(5) DEFAULT NULL,
  `Remarks` varchar(236) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE calls(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
	date date not null,
	next_call_date date not null,
	application_id int not null, 
	disbursement_id int not null, 
	remarks text not null, 
	
	PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE  `calls` ADD INDEX (  `application_id` );

INSERT INTO calls(date, next_call_date, application_id, remarks, disbursement_id) 
SELECT DATE_ADD('1900-01-01', INTERVAL CallDate-2 DAY), DATE_ADD('1900-01-01', INTERVAL NextCallDate-2 DAY), (SELECT application_id FROM disbursements WHERE id = LoanDisRegId), Remarks, LoanDisRegId FROM Followup;



CREATE TABLE demand_letters (
	id INT(10) unsigned NOT NULL AUTO_INCREMENT,
	date DATE NOT NULL, 
	application_id INT NOT NULL, 
	principal decimal(10, 2) NOT NULL, 
	interest decimal(10, 2) NOT NULL, 
	late_fee decimal(10, 2) NOT NULL, 
	late_interest decimal(10, 2) NOT NULL, 
	total decimal(10, 2) NOT NULL, 
	borrower_address varchar(128) not null, 
	pdf_url varchar(512) NOT NULL, 
	
  
	PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE action_codes (
	id INT(10) unsigned NOT NULL AUTO_INCREMENT,
	name VARCHAR(128) NOT NULL,
  
	PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO action_codes(name) VALUES('Test');

ALTER TABLE `calls` ADD `payment_schedule_id` INT NOT NULL ,
ADD `action_code_id` INT NOT NULL;





-- FOREIGN KEYS
ALTER TABLE  `applications` ADD FOREIGN KEY (  `borrower_id` ) REFERENCES  `borrowers` ( `id` ) ON DELETE RESTRICT ON UPDATE CASCADE ;

ALTER TABLE  `applications` ADD FOREIGN KEY (  `vt_user_id` ) REFERENCES  `vt_users` ( `id` ) ON DELETE RESTRICT ON UPDATE CASCADE ;

ALTER TABLE  `disbursements` ADD FOREIGN KEY (  `application_id` ) REFERENCES  `applications` ( `id` ) ON DELETE RESTRICT ON UPDATE CASCADE ;

ALTER TABLE  `repayments` ADD FOREIGN KEY (  `application_id` ) REFERENCES  `applications` ( `id` ) ON DELETE RESTRICT ON UPDATE CASCADE ;

ALTER TABLE  `payment_schedule` ADD FOREIGN KEY (  `disbursement_id` ) REFERENCES  `disbursements` ( `id` ) ON DELETE RESTRICT ON UPDATE CASCADE ;

ALTER TABLE  `disbursements` DROP FOREIGN KEY  `disbursements_ibfk_1` ;
ALTER TABLE  `disbursements` ADD FOREIGN KEY (  `application_id` ) REFERENCES  `applications` ( `id` ) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE  `payment_schedule` DROP FOREIGN KEY  `payment_schedule_ibfk_1` ;
ALTER TABLE  `payment_schedule` ADD FOREIGN KEY (  `disbursement_id` ) REFERENCES  `disbursements` ( `id` ) ON DELETE CASCADE ON UPDATE CASCADE ;



-- Apply a combo index - used for surety merging
-- ALTER TABLE  `borrowers` ADD UNIQUE (`name` ,`uid`);
-- ALTER TABLE  `borrowers` ADD UNIQUE (`uid`);

-- Apply unique key to UID for corporate borrowers
ALTER TABLE  `borrowers_corporate` ADD UNIQUE (`uid`);


-- Previous DB errors
-- UPDATE  `projects_loan`.`repayments` SET  `disbursement_id` =  '4961' WHERE  `repayments`.`id` =12729;
ALTER TABLE  `vouchers` CHANGE  `voucher_number`  `voucher_number` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;



-- Make account numbers unique
ALTER TABLE  `applications` ADD UNIQUE (`account_number`);

-- SET Cash Book as bank so that it appears in the disbursement FORM
UPDATE `books` SET `Bank` = '1' WHERE name ='Cash Book';




-- Receipt numbering issue in Quick Credit led to this piece of migration code
-- ALTER TABLE  `repayments` ADD  `reciept_number` INT NOT NULL AFTER  `id`;
-- UPDATE repayments SET receipt_number = id;


-- Cheque Dishonour
UPDATE vouchers SET CheqDishonour = '1' WHERE CheqDishonour = 'TRUE';
UPDATE vouchers SET CheqDishonour = '0' WHERE CheqDishonour = 'FALSE';
ALTER TABLE  `vouchers` CHANGE  `CheqDishonour`  `cheque_dishonour` tinyint(1) NOT NULL;

UPDATE repayments SET CheqDishonour = '1' WHERE CheqDishonour = 'TRUE';
UPDATE repayments SET CheqDishonour = '0' WHERE CheqDishonour = 'FALSE';
ALTER TABLE  `repayments` CHANGE  `CheqDishonour`  `cheque_dishonour` tinyint(1) NOT NULL;

UPDATE disbursements SET interest_calculation_method_id = '2' WHERE interest_calculation_method_id = 'R';
UPDATE disbursements SET interest_calculation_method_id = '1' WHERE interest_calculation_method_id = 'E';


ALTER TABLE `books` CHANGE `opening_balance` `opening_balance` DECIMAL(10 ,2) NOT NULL, CHANGE `ODAmount` `ODAmount` DECIMAL(10 ,2) NOT NULL, CHANGE `closing_balance` `closing_balance` DECIMAL(10 ,2) NOT NULL;

ALTER TABLE  `borrowers` ADD INDEX (  `ledger_id` );

ALTER TABLE  `vouchers` ADD INDEX (  `account_number` );
ALTER TABLE  `repayments` CHANGE  `account_number`  `account_number` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;





ALTER TABLE  `books` CHANGE  `name`  `name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `agents` ADD  `opening_balance` DECIMAL(14,2) NOT NULL AFTER  `name`;

ALTER TABLE  `applications` ADD  `mlas_submission` TINYINT( 1 ) NOT NULL;
ALTER TABLE  `applications` ADD  `dp_ref` varchar( 128 ) NOT NULL;

ALTER TABLE  `books` ADD  `agent_id` INT NOT NULL;

ALTER TABLE  `books` CHANGE  `Bank`  `Bank` VARCHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT  'enabled/disabled';
ALTER TABLE  `books` ADD  `type` VARCHAR( 4 ) NOT NULL AFTER  `Bank`;
UPDATE books set type ='cash' where name = 'cash book';
UPDATE books set type ='bank' where name = 'bank book';

UPDATE books set type ='bank' where name IN('ocbc book', 'dbs book', 'uob book', 'uob bank book', 'ocbc bank book', 'dbs bank book');

ALTER TABLE  `borrowers` ADD  `image` VARCHAR( 128 ) NOT NULL;



-- ALTER TABLE  `disbursements` ADD  `eir` DECIMAL(14,2) NOT NULL AFTER  `InterestFrom`;
-- ALTER TABLE  `disbursements` CHANGE  `EIR`  `eir` DECIMAL(14, 4) NOT NULL;
-- ALTER TABLE  `disbursements` ADD  `eir` DECIMAL(14, 4) NOT NULL;
ALTER TABLE  `disbursements` ADD  `irr` DECIMAL(14,2) NOT NULL AFTER  `eir`;

ALTER TABLE  `disbursements` CHANGE  `Remarks`  `Remarks` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;


-- Next of kin
ALTER TABLE  `borrowers` ADD  `nok_name` VARCHAR( 128 ) NOT NULL ,
ADD  `nok_contact` VARCHAR( 128 ) NOT NULL ,
ADD  `nok_relation` VARCHAR( 128 ) NOT NULL ,
ADD  `remarks` TEXT NOT NULL;


INSERT INTO  `interest_calculation_methods` (`id` , `name` , `code`) VALUES (NULL ,  'Rest Schedule',  'RS');
INSERT INTO  `interest_calculation_methods` (`id` ,`name` ,`code`) VALUES (NULL ,  'Compound Interest',  'CI');


ALTER TABLE  `borrowers` ADD  `sms_subscription` TINYINT( 1 ) NOT NULL;
UPDATE borrowers SET sms_subscription = '1';



CREATE TABLE IF NOT EXISTS sms_templates (
	id INT(10) unsigned NOT NULL AUTO_INCREMENT,
	name VARCHAR(128) NOT NULL,
	text text not null, 
  
	PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



INSERT INTO `sms_templates` (`id`, `name`, `text`) VALUES
(1, 'Payment Due', 'Hello,\r\n\r\nYou have a payment due of ${installment_amount} on {due_date} for loan account number {account_number}.\r\n\r\nThanks\r\n{company}'),
(2, 'Receipt', 'Hi,\r\n\r\nWe have received a repayment of ${amount} on {date} for account number {account_number}.\r\n\r\nThanks\r\n{company}'),
(3, 'Loan Closed', 'Hi,\r\n\r\nWe have received the final repayment for account number {account_number} on {date} and the loan is now closed.\r\n\r\nThanks\r\n{company}');



ALTER TABLE  `payment_schedule` ADD FOREIGN KEY (  `disbursement_id` ) REFERENCES  `disbursements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE  `demand_letters` ADD  `assigned_to` INT NOT NULL;






/*
ALTER TABLE  `loan_plans` CHANGE  `interest_rate`  `interest_rate` DECIMAL( 10, 4 ) NULL DEFAULT NULL ,
CHANGE  `late_interest_rate`  `late_interest_rate` DECIMAL( 10, 4 ) NULL DEFAULT NULL ,
CHANGE  `acceptance_fee_percent`  `acceptance_fee_percent` DECIMAL( 10, 4 ) NULL DEFAULT NULL ,
CHANGE  `late_repayment_fee_percent`  `late_repayment_fee_percent` DECIMAL( 10, 4 ) NULL DEFAULT NULL ,
CHANGE  `legal_fee_percent`  `legal_fee_percent` DECIMAL( 10, 4 ) NULL DEFAULT NULL ,
CHANGE  `renewal_fee_percent`  `renewal_fee_percent` DECIMAL( 10, 4 ) NULL DEFAULT NULL ,
CHANGE  `contract_variation_fee_percent`  `contract_variation_fee_percent` DECIMAL( 10, 4 ) NULL DEFAULT NULL ,
CHANGE  `cheque_dishonour_fee_percent`  `cheque_dishonour_fee_percent` DECIMAL( 10, 4 ) NULL DEFAULT NULL ,
CHANGE  `termination_fee_percent`  `termination_fee_percent` DECIMAL( 10, 4 ) NULL DEFAULT NULL ,
CHANGE  `late_fee_percent`  `late_fee_percent` DECIMAL( 10, 4 ) NULL DEFAULT NULL;
*/



ALTER TABLE `payment_schedule` ADD termination_fee decimal(10, 2) not null AFTER  `interest`;
ALTER TABLE `payment_schedule` ADD cheque_dishonour_fee decimal(10, 2) not null AFTER  `interest`;
ALTER TABLE `payment_schedule` ADD contract_variation_fee decimal(10, 2) not null AFTER  `interest`;
ALTER TABLE `payment_schedule` ADD renewal_fee decimal(10, 2) not null AFTER  `interest`;
ALTER TABLE `payment_schedule` ADD legal_fee decimal(10, 2) not null AFTER  `interest`;
ALTER TABLE `payment_schedule` ADD late_fee decimal(10, 2) not null AFTER  `interest`;
ALTER TABLE `payment_schedule` CHANGE  `skip`  `skip` TINYINT( 1 ) NOT NULL COMMENT  '1=skipped, 2=inactive(consolidated)';

ALTER TABLE  `vouchers` ADD INDEX (  `borrower_id` );

ALTER TABLE  `calls` ADD  `google_calendar_url` VARCHAR( 512 ) NOT NULL;




-- --------------
-- Lender details
-- --------------
delete from `options` where name IN('application_title', 'terms_and_conditions', 'lender_name', 'registration_number', 'license_number', 'address1', 'address2', 'city', 'state', 'country', 'postalcode', 'service_phone', 'hand_phone', 'other_phone', 'fax', 'email', 'blk', 'level', 'unit', 'contact_person', 'designation', 'opening_date');

INSERT INTO `options` (name, value) VALUES('application_title', (SELECT LenderName FROM LenderMaster ORDER BY LenderID DESC LIMIT 1)) ON DUPLICATE KEY UPDATE value = (SELECT LenderName FROM LenderMaster ORDER BY LenderID DESC LIMIT 1);
INSERT INTO `options` (name, value) VALUES('terms_and_conditions', (SELECT IFNULL(TermsAndCondition, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1)) ON DUPLICATE KEY UPDATE value = (SELECT IFNULL(TermsAndCondition, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1);

INSERT INTO `options` (name, value) VALUES('lender_name', (SELECT LenderName FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('registration_number', (SELECT CreditBureauNo FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('license_number', (SELECT LicenceNo FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('address1', (SELECT Address1 FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('address2', (SELECT IFNULL(Address2, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));

INSERT INTO `options` (name, value) VALUES('city', (SELECT IFNULL(City, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('state', (SELECT IFNULL(State, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('country', (SELECT IFNULL(Country, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('postalcode', (SELECT IFNULL(PinCode, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));

INSERT INTO `options` (name, value) VALUES('service_phone', (SELECT IFNULL(ServicePhone, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('hand_phone', (SELECT IFNULL(HandPhone, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('other_phone', (SELECT IFNULL(OtherPhone, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('fax', (SELECT IFNULL(Fax, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));

INSERT INTO `options` (name, value) VALUES('email', (SELECT IFNULL(EMailID, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('blk', (SELECT IFNULL(HouseNo, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('level', (SELECT IFNULL(LevelNo, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('unit', (SELECT IFNULL(UnitNo, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('contact_person', (SELECT IFNULL(ContactPerson, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));
INSERT INTO `options` (name, value) VALUES('designation', (SELECT IFNULL(Designation, '') FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));

INSERT INTO `options` (name, value) VALUES('opening_date', (SELECT DATE_ADD('1900-01-01', INTERVAL OpeningDate-2 DAY) FROM LenderMaster ORDER BY LenderID DESC LIMIT 1));





-- db upgrades
ALTER TABLE  `loan_plans` 
ADD  `amount` DECIMAL(14,2) NOT NULL ,
ADD  `installments` INT NOT NULL ,
ADD  `first_payment_date` VARCHAR( 16 ) NOT NULL ,
ADD  `book_id` INT NOT NULL ,
ADD  `payment_type_id` INT NOT NULL;

-- demand letter type for loan amount only
ALTER TABLE  `demand_letters` ADD  `type` VARCHAR( 16 ) NOT NULL;

-- Loan plan changes - payment frequency id
ALTER TABLE  `loan_plans` ADD  `payment_frequency_id` INT NOT NULL;
update loan_plans set payment_frequency_id = '4';

ALTER TABLE  `disbursements` ADD  `borrower_annual_income` decimal(14,2) NOT NULL;
update disbursements d LEFT JOIN applications ap ON d.application_id = ap.id set borrower_annual_income = (SELECT annual_income FROM borrowers WHERE id = ap.borrower_id) WHERE borrower_annual_income = 0;

-- hr_letters
CREATE TABLE `hr_letters` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date` date NOT NULL,
 `application_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- locking ledger edit
ALTER TABLE  `ledgers` ADD  `edit_disabled` TINYINT( 1 ) NOT NULL;
update ledgers set `edit_disabled` = '1' WHERE name IN('Interest', 'LateInterest', 'PermitFees', 'Bad Debt Expense', 'Goodwill Expense');






-- rejected borrowers
CREATE TABLE `borrowers_rejected` (
 `uid` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `address` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `contact` varchar(32) NOT NULL,
 `reason` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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



-- second mailing address
ALTER TABLE  `borrowers` ADD  `address1b` TEXT NOT NULL AFTER  `address1`;


CREATE TABLE IF NOT EXISTS `interview_requests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL,
  `uid` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `company` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `caveat_letters` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `application_id` int(11) NOT NULL,
  `property_address` text COLLATE utf8_unicode_ci NOT NULL,
  `second_person_name` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `second_person_uid` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

ALTER TABLE `disbursements` 
ADD  `account_number_prefix` VARCHAR( 64 ) NOT NULL AFTER  `account_number` ,
ADD  `account_number_suffix` VARCHAR( 64 ) NOT NULL AFTER  `account_number_prefix`;



ALTER TABLE  `disbursements` ADD UNIQUE ( `account_number` );