DROP DATABASE IF EXISTS Library;
CREATE DATABASE Library;


# Creating Company Schema
USE Library

DROP TABLE IF EXISTS BOOK;
CREATE TABLE BOOK (
  Isbn			CHAR(13) NOT NULL,
  Title			VARCHAR(200) CHARACTER SET utf8 NOT NULL,
  Cover			VARCHAR(100),
  CONSTRAINT pk_Book PRIMARY KEY (Isbn)
 # CONSTRAINT uk_dname UNIQUE (dname) 
);

DROP TABLE IF EXISTS AUTHORS;
CREATE TABLE AUTHORS(
	Name VARCHAR(50) CHARACTER SET utf8 NOT NULL,
	Author_id INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT pk_authors PRIMARY KEY (Author_id)
);

DROP TABLE IF EXISTS BOOK_AUTHORS;
CREATE TABLE BOOK_AUTHORS (
  Author_id INT(10) NOT NULL,
  Isbn CHAR(13) NOT NULL,
  CONSTRAINT fk_book_authors_authors FOREIGN KEY (Author_id) references AUTHORS(Author_id),
  CONSTRAINT fk_book_authors_book FOREIGN KEY (Isbn) references BOOK(Isbn)
);

DROP TABLE IF EXISTS BORROWER;
CREATE TABLE BORROWER (
  Card_id           CHAR(8) NOT NULL,
  SSN				CHAR(11) NOT NULL,
  Bname             VARCHAR(20) NOT NULL,
  Address           VARCHAR(50) NOT NULL,
  Phone			    CHAR(14),
  CONSTRAINT pk_borrower PRIMARY KEY (Card_id),
  CONSTRAINT uk_ssn UNIQUE (SSN)
);

# To accomodate auto increment card_id

DROP TABLE IF EXISTS BORROWER_SEQ;
CREATE TABLE BORROWER_SEQ
(
  Card_id INT NOT NULL AUTO_INCREMENT,
  CONSTRAINT pk_borrower_seq PRIMARY KEY (Card_id)
);

# https://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix

DELIMITER $$
DROP TRIGGER IF EXISTS TG_BORROWER_INSERT;
CREATE TRIGGER TG_BORROWER_INSERT
BEFORE INSERT ON BORROWER
FOR EACH ROW
BEGIN
  INSERT INTO BORROWER_SEQ(Card_id) VALUES (NULL);
  # SET NEW.id = CONCAT('ID', LPAD(LAST_INSERT_ID(), 6, '0'));
END$$
DELIMITER ;

DROP TABLE IF EXISTS BOOK_LOANS;
CREATE TABLE BOOK_LOANS (
  Loan_id    	INT(10) NOT NULL AUTO_INCREMENT,
  Isbn  		CHAR(13), 
  Card_id		CHAR(8),
  Date_out		DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  Due_date		DATETIME,
  Date_in		DATETIME,
  CONSTRAINT pk_book_loans PRIMARY KEY (Loan_id),
  CONSTRAINT fk_book_loans_borrower FOREIGN KEY (Card_id) references BORROWER(Card_id)
);

DROP TRIGGER IF EXISTS TG_BOOK_LOANS_INSERT;
CREATE TRIGGER TG_BOOK_LOANS_INSERT
BEFORE INSERT ON BOOK_LOANS
FOR EACH ROW
SET NEW.Due_date = DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL 14 DAY);

DROP TABLE IF EXISTS FINES;
CREATE TABLE FINES (
  Loan_id      INT(10) NOT NULL,
  Fine_amt     DECIMAL(10,2),
  Paid		   BIT(1) DEFAULT 0,
  CONSTRAINT fk_fines_book_loans FOREIGN KEY (Loan_id) references BOOK_LOANS(Loan_id)
);