-- -----------------------------------------------------
-- Table ReceiptBookStatusMaster
-- -----------------------------------------------------
DROP TABLE IF EXISTS ReceiptBookStatusMaster;

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

--
-- Master table values for ReceiptBookStatusMaster
--
LOCK TABLES ReceiptBookStatusMaster WRITE;
DELETE FROM ReceiptBookStatusMaster;
INSERT INTO ReceiptBookStatusMaster(id, name) VALUES
(1, 'Issued to branch'),
(2, 'Issued to officer'),
(3, 'In use'),
(4, 'Closed');
UNLOCK TABLES;

-- -----------------------------------------------------
-- Table ReceiptBook
-- -----------------------------------------------------
DROP TABLE IF EXISTS ReceiptBook;

CREATE TABLE ReceiptBook (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  abbr VARCHAR(50) NOT NULL,
  start_receipt_no VARCHAR(10) NOT NULL,
  end_receipt_no VARCHAR(10) NOT NULL,
  num_receipts SMALLINT UNSIGNED DEFAULT NULL,
  office_id SMALLINT UNSIGNED NOT NULL,
  office_issue_date DATE NOT NULL,
  officer_id SMALLINT UNSIGNED DEFAULT NULL,
  officer_issue_date DATE DEFAULT NULL,
  status TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (office_id)
    REFERENCES Office (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (officer_id)
    REFERENCES Employee (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (status)
    REFERENCES ReceiptBookStatusMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX receiptbook_abbr_idx ON ReceiptBook(abbr);
CREATE UNIQUE INDEX receiptbook_start_end_idx ON ReceiptBook(start_receipt_no, end_receipt_no);


-- -----------------------------------------------------
-- Table LoanPaymentReceipt
-- -----------------------------------------------------
DROP TABLE IF EXISTS LoanPaymentReceipt;

CREATE TABLE LoanPaymentReceipt (
  payment_id INTEGER UNSIGNED NOT NULL,
  receipt_book_id SMALLINT UNSIGNED NOT NULL,
  receipt_no VARCHAR(10) NOT NULL,
  PRIMARY KEY (payment_id),
  FOREIGN KEY (payment_id)
    REFERENCES LoanPayment (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (receipt_book_id)
    REFERENCES ReceiptBook (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX loanpaymentreceipt_book_no_idx ON LoanPaymentReceipt(receipt_book_id, receipt_no);




