-- -----------------------------------------------------
-- Table StationeryIssueToMaster
-- -----------------------------------------------------

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


INSERT INTO StationeryIssueToMaster(id, name) VALUES
(1, "Village/Area"),
(2, "Center"),
(3, "Officer"),
(4, "Loan Customer");

-- -----------------------------------------------------
-- Table StationeryRuleMaster
-- -----------------------------------------------------

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


INSERT INTO StationeryRuleMaster(id, name) VALUES
(1, "Village Feasibility"),
(2, "Group Formation and Training"),
(3, "Mutual Agreement of Members"),
(4, "Loan Account Opening"),
(5, "Loan Disbursement"),
(6, "Loan Center Collection"),
(7, "Loan Utilization"),
(8, "Loan Account Payment");

-- -----------------------------------------------------
-- Table StationeryType
-- -----------------------------------------------------

CREATE TABLE StationeryType (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  rule_id TINYINT UNSIGNED NOT NULL,
  issue_to_id TINYINT UNSIGNED NOT NULL,
  is_active TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  FOREIGN KEY (rule_id)
    REFERENCES StationeryRuleMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (issue_to_id)
    REFERENCES StationeryIssueToMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX stationerytype_name_idx ON StationeryType(name);

INSERT INTO StationeryType(id, name, rule_id, issue_to_id) VALUES
(1, "Receipt Book", 8, 3);

ALTER TABLE ReceiptBook
	DROP FOREIGN KEY ReceiptBook_ibfk_3,
	DROP INDEX receiptbook_abbr_idx,
	DROP INDEX receiptbook_start_end_idx;

ALTER TABLE ReceiptBookStatusMaster 
	DROP INDEX receiptbookstatus_name_idx;
RENAME TABLE ReceiptBookStatusMaster TO StationeryStatusMaster;
CREATE UNIQUE INDEX stationerystatus_name_idx ON StationeryStatusMaster(name);

UPDATE StationeryStatusMaster SET id=7 WHERE id=4;
UPDATE StationeryStatusMaster SET id=6 WHERE id=3;
UPDATE StationeryStatusMaster SET id=4 WHERE id=2;
INSERT INTO StationeryStatusMaster (id, name) VALUES (2, 'Issued to village/area');
INSERT INTO StationeryStatusMaster (id, name) VALUES (3, 'Issued to center');
INSERT INTO StationeryStatusMaster (id, name) VALUES (5, 'Issued to loan customer');

ALTER TABLE AccountPaymentReceipt
	DROP FOREIGN KEY AccountPaymentReceipt_ibfk_2,
	DROP INDEX accountpaymentreceipt_book_no_idx;

RENAME TABLE ReceiptBook TO Stationery;
ALTER TABLE Stationery 
	CHANGE COLUMN abbr batch_no VARCHAR(50) NOT NULL, 
	CHANGE COLUMN start_receipt_no start_serial_no VARCHAR(10) NOT NULL,
  	CHANGE COLUMN end_receipt_no end_serial_no VARCHAR(10) NOT NULL,
  	CHANGE COLUMN num_receipts batch_count SMALLINT UNSIGNED DEFAULT NULL,
	CHANGE COLUMN officer_issue_date issue_date DATE DEFAULT NULL,
	ADD COLUMN type_id SMALLINT UNSIGNED NOT NULL AFTER id,
	ADD COLUMN town_id MEDIUMINT UNSIGNED DEFAULT NULL AFTER office_issue_date,
	ADD COLUMN center_id MEDIUMINT UNSIGNED DEFAULT NULL AFTER town_id;
	
UPDATE Stationery SET type_id=1;
UPDATE Stationery SET status=7 WHERE status=4;
UPDATE Stationery SET status=6 WHERE status=3;
UPDATE Stationery SET status=4 WHERE status=2;
ALTER TABLE Stationery 
	ADD FOREIGN KEY (type_id) REFERENCES StationeryType (id) ON DELETE NO ACTION ON UPDATE CASCADE,
	ADD FOREIGN KEY (center_id) REFERENCES LoanCenter (id) ON DELETE NO ACTION ON UPDATE CASCADE,
	ADD FOREIGN KEY (status) REFERENCES StationeryStatusMaster (id) ON DELETE NO ACTION ON UPDATE CASCADE;
CREATE UNIQUE INDEX stationery_batch_no_idx ON Stationery(type_id, batch_no);
CREATE UNIQUE INDEX stationery_start_end_idx ON Stationery(type_id, start_serial_no, end_serial_no);

RENAME TABLE AccountPaymentReceipt TO AccountPaymentStationery;
ALTER TABLE AccountPaymentStationery
	CHANGE COLUMN receipt_book_id stationery_id smallint(5) unsigned NOT NULL, 
	CHANGE COLUMN receipt_no serial_no varchar(10) NOT NULL,
	DROP PRIMARY KEY,
	ADD PRIMARY KEY(payment_id,stationery_id),
	ADD FOREIGN KEY (stationery_id) REFERENCES Stationery (id) ON DELETE NO ACTION ON UPDATE CASCADE;
CREATE UNIQUE INDEX accountpaymentstationery_serial_no_idx ON AccountPaymentStationery(stationery_id, serial_no);


-- -----------------------------------------------------
-- Table LoanAccountStationery
-- -----------------------------------------------------

CREATE TABLE LoanAccountStationery (
  account_id MEDIUMINT UNSIGNED NOT NULL,
  stationery_id SMALLINT UNSIGNED NOT NULL,
  serial_no VARCHAR(10) NOT NULL,
  PRIMARY KEY (account_id,stationery_id),
  FOREIGN KEY (account_id)
    REFERENCES LoanAccount (account_id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (stationery_id)
    REFERENCES Stationery (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX loanaccountstationery_serial_no_idx ON LoanAccountStationery(stationery_id, serial_no);	

	

 




