CREATE SCHEMA librarydb;

CREATE TABLE librarydb.book (
	id INTEGER NOT NULL,
	name VARCHAR ( 50 ) NOT NULL,
	author VARCHAR ( 50 ),
  publisher VARCHAR (50),
  tags JSON,
  genre_ids INTEGER[],
  PRIMARY KEY (id)
);

CREATE TABLE librarydb.member (
	id INTEGER NOT NULL,
	name VARCHAR ( 50 ) NOT NULL,
  PRIMARY KEY (id)
);


CREATE TABLE librarydb.issue_history ( 
	book_id INTEGER NOT NULL, 
  member_id INTEGER NOT NULL,
  issue_date DATE,
  return_date DATE,
CONSTRAINT fk_book FOREIGN KEY(book_id) REFERENCES librarydb.book(id),
CONSTRAINT fk_member FOREIGN KEY(member_id) REFERENCES librarydb.member(id)
);

INSERT INTO librarydb.book (id, name, author) VALUES (1, 'Book 1', 'ABC');
INSERT INTO librarydb.book (id, name, author) VALUES (2, 'Book 2', 'PQR');
INSERT INTO librarydb.book (id, name, author) VALUES (3, 'Book 3', 'XYZ');

INSERT INTO librarydb.member(id, name) VALUES (1, 'Member 1');
INSERT INTO librarydb.member(id, name) VALUES (2, 'Member 2');

INSERT INTO librarydb.issue_history VALUES (1, 1, TO_DATE('2021-09-27','YYYY-MM-DD'), TO_DATE('2021-09-27','YYYY-MM-DD'));
INSERT INTO librarydb.issue_history VALUES (2, 2, TO_DATE('2021-09-27','YYYY-MM-DD'), NULL);


CREATE VIEW librarydb.book_in_circulation as 
  SELECT b.id, b.name, b.author, b.publisher, i.member_id, i.issue_date FROM 
  librarydb.book b 
  JOIN 
  librarydb.issue_history i 
  on b.id=i.book_id 
  where i.return_date is null;