INSERT INTO `FeeCategoryMaster` (`id`, `name`) VALUES (3, 'Loan Payments');
INSERT INTO `FeeChargeTimeMaster` (`id`, `fee_category_id`, `name`) VALUES (5, 3, 'On Cheque Bounce');

ALTER TABLE `LoanAccountAssociate` ADD COLUMN `date_of_birth` DATE DEFAULT NULL  AFTER `legal_name` ;

-- Noting the deal source for the loan account

-- -----------------------------------------------------
-- Table DealSourceMaster
-- -----------------------------------------------------

CREATE TABLE DealSourceMaster (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX dealsource_name_idx ON DealSourceMaster(name);

--
-- Master table values for DealSourceMaster
--
DELETE FROM DealSourceMaster;
INSERT INTO DealSourceMaster(id, name) VALUES
(1, 'Direct'),
(2, 'DMA'),
(3, 'Sale Purchase Agent');

ALTER TABLE `LoanAccount` ADD COLUMN `deal_source_id` TINYINT UNSIGNED DEFAULT NULL AFTER `closing_date` , 
  ADD CONSTRAINT `LoanAccount_ibfk_6`
  FOREIGN KEY (`deal_source_id` )
  REFERENCES `DealSourceMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, ADD INDEX `loanaccount_deal_source_id_idx` (`deal_source_id` ASC) ;


-- Specifying if a PDC is for installment or security
 
-- -----------------------------------------------------
-- Table ChequeTypeMaster
-- -----------------------------------------------------

CREATE TABLE ChequeTypeMaster (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX chequetype_name_idx ON ChequeTypeMaster(name);

--
-- Master table values for ChequeTypeMaster
--
DELETE FROM ChequeTypeMaster;
INSERT INTO ChequeTypeMaster(id, name) VALUES
(1, 'Installment'),
(2, 'Security');


ALTER TABLE `LoanAccountCheque` ADD COLUMN `type_id` TINYINT UNSIGNED NOT NULL  AFTER `cheque_id` , 
  ADD CONSTRAINT `LoanAccountCheque_ibfk_3`
  FOREIGN KEY (`type_id` )
  REFERENCES `ChequeTypeMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, ADD INDEX `loanaccountcheque_type_id_idx` (`type_id` ASC) ;

UPDATE LoanAccountCheque SET type_id=(SELECT id FROM ChequeTypeMaster WHERE name = 'Installment');

-- -----------------------------------------------------
-- Table VehicleViability
-- -----------------------------------------------------

CREATE TABLE VehicleViability (
  collateral_id SMALLINT UNSIGNED NOT NULL,
  route VARCHAR(100) NOT NULL,
  distance SMALLINT UNSIGNED NOT NULL,
  num_trips SMALLINT UNSIGNED NOT NULL,
  avg_load DECIMAL(6,2) NOT NULL,
  rate DECIMAL(6,2) NOT NULL,
  fuel_average DECIMAL(6,2) NOT NULL,
  fuel_cost DECIMAL(8,2) NOT NULL,
  num_tyes TINYINT UNSIGNED NOT NULL,
  tyre_cost DECIMAL(8,2) NOT NULL,
  tyre_life MEDIUMINT UNSIGNED NOT NULL,
  driver_salary DECIMAL(9,2) NOT NULL,
  cleaner_salary DECIMAL(9,2) NOT NULL,
  permit_cost DECIMAL(9,2) NOT NULL,
  taxes DECIMAL(9,2) NOT NULL,
  maintenance_cost DECIMAL(9,2) NOT NULL,
  miscellaneous_cost DECIMAL(9,2) NOT NULL,
  proposed_installment_amount MEDIUMINT UNSIGNED NOT NULL,
  num_vehicles TINYINT UNSIGNED NOT NULL,
  business_cash_flow MEDIUMINT UNSIGNED NOT NULL,
  total_installment_amount MEDIUMINT UNSIGNED NOT NULL,
  PRIMARY KEY (collateral_id),
  FOREIGN KEY (collateral_id)
    REFERENCES LoanAccountCollateral (id) 
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;


-- Adding a couple of fields to LoanProduct and dropping one

ALTER TABLE `LoanProduct` DROP COLUMN `cheque_bounce_charge`,
ADD COLUMN approval_validity_period SMALLINT UNSIGNED NOT NULL AFTER penalty_rate_type_id,
ADD COLUMN approval_validity_period_unit_id TINYINT UNSIGNED NOT NULL AFTER approval_validity_period,
ADD CONSTRAINT `LoanProduct_ibfk_12` FOREIGN KEY (approval_validity_period_unit_id) REFERENCES TenureMaster (id) ON DELETE NO ACTION ON UPDATE CASCADE ;

UPDATE LoanProduct SET approval_validity_period=1, approval_validity_period_unit_id=(SELECT id from TenureMaster WHERE name='Months');



-- -----------------------------------------------------
-- Table LoanProductPaymentMode
-- -----------------------------------------------------

CREATE TABLE LoanProductPaymentMode (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id SMALLINT UNSIGNED NOT NULL,
  payment_mode_id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (product_id)
    REFERENCES LoanProduct (product_id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (payment_mode_id)
    REFERENCES PaymentModeMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX loanproductpaymentmode_prod_mode_idx ON LoanProductPaymentMode(product_id, payment_mode_id);

INSERT INTO LoanProductPaymentMode(product_id) SELECT product_id FROM LoanProduct;
UPDATE LoanProductPaymentMode SET payment_mode_id=(SELECT id FROM PaymentModeMaster WHERE name = 'Cash');
