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

INSERT INTO CountryMaster(id, name) VALUES (1, 'India');

set foreign_key_checks=0;
ALTER TABLE `CountryStateMaster` ADD COLUMN `country_id` TINYINT UNSIGNED NOT NULL  AFTER `id` , 
  ADD CONSTRAINT `CountryStateMaster_ibfk_1`
  FOREIGN KEY (`country_id` )
  REFERENCES `CountryMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, ADD INDEX `country_id` (`country_id` ASC) ;
set foreign_key_checks=1;

UPDATE CountryStateMaster SET country_id = 1;

CREATE TABLE CountryBankMaster (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  country_id TINYINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (country_id)
    REFERENCES CountryMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX countrybank_name_idx ON CountryBankMaster(name);

INSERT INTO CountryBankMaster(id, country_id, name) VALUES
(1, 1, 'Allahabad Bank'),
(2, 1, 'Andhra Bank'),
(3, 1, 'Axis Bank'),
(4, 1, 'Bank of Baroda'),
(5, 1, 'Bank of India'),
(6, 1, 'Bank of Maharashtra'),
(7, 1, 'Bank of Rajasthan'),
(8, 1, 'Canara Bank'),
(9, 1, 'Catholic Syrian Bank'),
(10, 1, 'Central Bank of India'),
(11, 1, 'City Union Bank'),
(12, 1, 'Corporation Bank'),
(13, 1, 'Dena Bank'),
(14, 1, 'Dhanlaxmi Bank'),
(15, 1, 'Federal Bank'),
(16, 1, 'HDFC Bank'),
(17, 1, 'ICICI Bank'),
(18, 1, 'IDBI Bank'),
(19, 1, 'Indian Bank'),
(20, 1, 'Indian Overseas Bank'),
(21, 1, 'IndusInd Bank'),
(22, 1, 'ING Vysya Bank'),
(23, 1, "Jammu & Kashmir Bank"),
(24, 1, 'Karnataka Bank'),
(25, 1, 'Karur Vysya Bank'),
(26, 1, 'Kotak Mahindra Bank'),
(27, 1, 'Lakshmi Vilas Bank'),
(28, 1, 'Nainital Bank'),
(29, 1, 'Oriental Bank of Commerce'),
(30, 1, 'Punjab and Sind Bank'),
(31, 1, 'Punjab National Bank'),
(32, 1, 'Saraswat Bank'),
(33, 1, 'South Indian Bank'),
(34, 1, "State Bank of Bikaner & Jaipur"),
(35, 1, 'State Bank of Hyderabad'),
(36, 1, 'State Bank of India'),
(37, 1, 'State Bank of Mysore'),
(38, 1, 'State Bank of Patiala'),
(39, 1, 'State Bank of Travancore'),
(40, 1, 'Syndicate Bank'),
(41, 1, 'Tamilnad Mercantile Bank'),
(42, 1, 'Uco Bank'),
(43, 1, 'Union Bank of India'),
(44, 1, 'United Bank of India'),
(45, 1, 'Vijaya Bank'),
(46, 1, 'Yes Bank');


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

INSERT INTO PaymentModeMaster(id, name) VALUES
(1, 'Cash'),
(2, 'Cheque'),
(3, 'ECS'),
(4, 'Wire Transfer');

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

INSERT INTO PaymentStatusMaster(id, name) VALUES
(1, 'Complete'),
(2, 'Under process'),
(3, 'Cancellation under process'),
(4, 'Cancelled');

SET foreign_key_checks=0;
ALTER TABLE `LoanPayment` 
ADD COLUMN `mode_id` TINYINT UNSIGNED NOT NULL AFTER `user_id` , 
  ADD CONSTRAINT `LoanPayment_ibfk_5`
  FOREIGN KEY (`mode_id` )
  REFERENCES `PaymentModeMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, ADD INDEX `mode_id` (`mode_id` ASC),
ADD COLUMN `status` TINYINT UNSIGNED NOT NULL AFTER `paid_by_id` , 
  ADD CONSTRAINT `LoanPayment_ibfk_6`
  FOREIGN KEY (`status` )
  REFERENCES `PaymentStatusMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, ADD INDEX `status` (`status` ASC) ;
UPDATE LoanPayment SET mode_id=1, status=1;
SET foreign_key_checks=1;

UPDATE DocumentTypeMaster SET id = 5 where name = 'Others';
INSERT INTO DocumentTypeMaster(id, name) VALUES (3, 'Cheque'), (4, 'ECS Mandate');

CREATE TABLE ChequeLoanPayment (
  payment_id INTEGER UNSIGNED NOT NULL,
  cheque_no VARCHAR(20) NOT NULL,
  cheque_date DATE NOT NULL,
  bank_id SMALLINT UNSIGNED NOT NULL,
  branch_name VARCHAR(100) NOT NULL,
  document_id INTEGER UNSIGNED DEFAULT NULL,
  notation VARCHAR(300) DEFAULT NULL,
  PRIMARY KEY (payment_id),
  FOREIGN KEY (payment_id)
    REFERENCES LoanPayment (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (bank_id)
    REFERENCES CountryBankMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (document_id)
    REFERENCES CustomerDocument (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;

SET foreign_key_checks=0;
ALTER TABLE `InterOfficePayment`
ADD COLUMN `mode_id` TINYINT UNSIGNED NOT NULL  AFTER `amount` , 
  ADD CONSTRAINT `InterOfficePayment_ibfk_5`
  FOREIGN KEY (`mode_id` )
  REFERENCES `PaymentModeMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, ADD INDEX `mode_id` (`mode_id` ASC) ;
UPDATE InterOfficePayment SET mode_id=1;
SET foreign_key_checks=1;

INSERT INTO GLTypeMaster(id, gl_code, gl_category_id, gl_class_id, name, system_defined) VALUES
(28, 'GL107', 1, 1, 'Cheque', 1),
(29, 'GL108', 1, 1, 'ECS', 1),
(30, 'GL204', 1, 2, 'Cheque', 1),
(31, 'GL205', 1, 2, 'ECS', 1),
(32, 'GL319', 1, 3, 'Cheque', 1),
(33, 'GL320', 1, 3, 'ECS', 1),
(34, 'GL109', 1, 1, 'Wire Transfer', 1);


INSERT INTO TransactionGLTypeMap(id, tx_type_id, debit_gl_type_id, credit_gl_type_id, is_default) VALUES
(44, 1, 14, 32, 0),
(45, 2, 32, 14, 0),
(46, 3, 32, 14, 0),
(47, 7, 32, 15, 0),
(48, 8, 32, 16, 0),
(49, 9, 32, 17, 0),
(50, 11, 32, 19, 0),
(51, 13, 32, 21, 0),
(52, 15, 32, 10, 0),
(53, 15, 30, 7, 0),
(54, 17, 32, 12, 0),
(55, 19, 32, 23, 0),
(56, 20, 32, 25, 0),
(57, 22, 32, 4, 0),
(58, 1, 14, 33, 0),
(59, 2, 33, 14, 0),
(60, 3, 33, 14, 0),
(61, 7, 33, 15, 0),
(62, 8, 33, 16, 0),
(63, 9, 33, 17, 0),
(64, 11, 33, 19, 0),
(65, 13, 33, 21, 0),
(66, 15, 33, 10, 0),
(67, 15, 31, 7, 0),
(68, 17, 33, 12, 0),
(69, 19, 33, 23, 0),
(70, 20, 33, 25, 0),
(71, 22, 33, 4, 0);
