-- -----------------------------------------------------
-- Table RelationMaster
-- -----------------------------------------------------
DROP TABLE IF EXISTS RelationMaster;

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


--
-- Master table values for RelationMaster
--
LOCK TABLES RelationMaster WRITE;
DELETE FROM RelationMaster;
INSERT INTO RelationMaster(id, name) VALUES
(1, 'Self'),
(2, 'Husband'),
(3, 'Wife'),
(4, 'Father'),
(5, 'Mother'),
(6, 'Son'),
(7, 'Daughter'),
(8, 'Brother'),
(9, 'Sister'),
(10, 'Father-in-law'),
(11, 'Mother-in-law'),
(12, 'Son-in-law'),
(13, 'Daughter-in-law'),
(14, 'Brother-in-law'),
(15, 'Sister-in-law');
UNLOCK TABLES;


-- -----------------------------------------------------
-- Table CustomerFleet
-- -----------------------------------------------------
DROP TABLE IF EXISTS CustomerFleet;

CREATE TABLE CustomerFleet (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id MEDIUMINT UNSIGNED NOT NULL,
  make_id TINYINT UNSIGNED NOT NULL,
  model VARCHAR(30) NOT NULL,
  registration_no VARCHAR(30) NOT NULL,
  manufacturing_year YEAR(4) NOT NULL,
  registered_owner_relation_id TINYINT UNSIGNED NOT NULL,
  registered_owner_name VARCHAR(100) NOT NULL,
  is_financed TINYINT(1) NOT NULL DEFAULT 0,
  financier_name VARCHAR(200) DEFAULT NULL,
  outstanding_amount DECIMAL(9,2) DEFAULT NULL,
  tenure_id TINYINT UNSIGNED DEFAULT NULL,
  balance_tenure SMALLINT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (customer_id)
    REFERENCES Customer (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (make_id)
    REFERENCES VehicleMakeMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (registered_owner_relation_id)
    REFERENCES RelationMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (tenure_id)
    REFERENCES TenureMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX customerfleet_reg_no_idx ON CustomerFleet(registration_no);


-- -----------------------------------------------------
-- Table LoanCreditNoteTypeMaster
-- -----------------------------------------------------
DROP TABLE IF EXISTS LoanCreditNoteTypeMaster;

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


--
-- Master table values for LoanCreditNoteTypeMaster
--
LOCK TABLES LoanCreditNoteTypeMaster WRITE;
DELETE FROM LoanCreditNoteTypeMaster;
INSERT INTO LoanCreditNoteTypeMaster(id, name) VALUES
(1, 'Deviation'),
(2, 'Mitigant'),
(3, 'Condition');
UNLOCK TABLES;


-- -----------------------------------------------------
-- Table LoanCreditNote
-- -----------------------------------------------------
DROP TABLE IF EXISTS LoanCreditNote;

CREATE TABLE LoanCreditNote (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_id MEDIUMINT UNSIGNED NOT NULL,
  type_id TINYINT UNSIGNED NOT NULL,
  note VARCHAR(500) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (account_id)
    REFERENCES LoanAccount (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (type_id)
    REFERENCES LoanCreditNoteTypeMaster (id) 
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX loancreditnote_acct_idx ON LoanCreditNote(account_id);


-- -----------------------------------------------------
-- Table LoanCreditRemarkTypeMaster
-- -----------------------------------------------------
DROP TABLE IF EXISTS LoanCreditRemarkTypeMaster;

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


--
-- Master table values for LoanCreditRemarkTypeMaster
--
LOCK TABLES LoanCreditRemarkTypeMaster WRITE;
DELETE FROM LoanCreditRemarkTypeMaster;
INSERT INTO LoanCreditRemarkTypeMaster(id, name) VALUES
(1, 'Customer Profile'),
(2, 'Purpose'),
(3, 'Stability/Ownership'),
(4, 'Fleet'),
(5, 'CIBIL'),
(6, 'Credit History'),
(7, 'Field Investigation'),
(8, 'Banking'),
(9, 'Proposal'),
(10, 'Request for approval');
UNLOCK TABLES;


-- -----------------------------------------------------
-- Table LoanCreditRemark
-- -----------------------------------------------------
DROP TABLE IF EXISTS LoanCreditRemark;

CREATE TABLE LoanCreditRemark (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_id MEDIUMINT UNSIGNED NOT NULL,
  type_id TINYINT UNSIGNED NOT NULL,
  remark VARCHAR(5000) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (account_id)
    REFERENCES LoanAccount (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (type_id)
    REFERENCES LoanCreditRemarkTypeMaster (id) 
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX loancreditremark_acct_idx ON LoanCreditRemark(account_id);

