--CREATING SCHEMA
CREATE scheme libro;
SET search_path TO libro,public;

--CREATING TABLES
CREATE TABLE book (
bno INTEGER PRIMARY KEY,
title VARCHAR(20) CHECK(title <> '                    ') NOT NULL,
author VARCHAR(20) CHECK(author <> '                    ') NOT NULL,
category CHAR(10) CHECK(category IN('Science', 'Lifestyle', 'Arts', 'Leisure')) NOT NULL,
price DECIMAL(6,2) Default 0.0, CHECK(price>=0.0),
sales INTEGER NOT NULL);

CREATE TABLE customer (
cno INTEGER PRIMARY KEY,
name VARCHAR(20) CHECK(name<> '                    '),
address VARCHAR(30) NOT NULL,
balance DECIMAL(8,2) CHECK(balance >= 0.0) DEFAULT 0);

CREATE TABLE bookOrder (
cno INTEGER NOT NULL,
bno INTEGER NOT NULL,
orderTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
qty INTEGER NOT NULL,
CONSTRAINT bookOrderfk1 FOREIGN KEY(cno) REFERENCES customer
    ON DELETE RESTRICT,
CONSTRAINT bookOrderfk2 FOREIGN KEY(bno) REFERENCES book
    ON DELETE RESTRICT
);

--INSERTING TEST DATA

INSERT INTO book(bno, title, author, category, price, sales) VALUES(1, 'To Kill A bird', 'Harper Lee', 'Arts', 100.00, 10);
INSERT INTO book(bno, title, author, category, price, sales) VALUES(2, 'Ulysses', 'James Joyce', 'Arts', 50.00, 50);
INSERT INTO book(bno, title, author, category, price, sales) VALUES(3, 'The Great Gatsby', 'F.Scott Fitzgerald', 'Arts', 500.00, 2);
INSERT INTO book(bno, title, author, category, price, sales) VALUES(4, 'Moby Dick', 'Herman Melville', 'Arts', 80.00, 7);
INSERT INTO book(bno, title, author, category, price, sales) VALUES(5, 'The story of TSM', 'Andy Dinh', 'Leisure', 250.00, 6);

INSERT INTO customer(cno, name, address, balance) VALUES(1, 'Bjergsen', 'TSM House', 100);
INSERT INTO customer(cno, name, address, balance) VALUES(2, 'Josh', 'Joshs House', 500);
INSERT INTO customer(cno, name, address, balance) VALUES(3, 'Thresh', 'Shadow Isles', 2000);
INSERT INTO customer(cno, name, address, balance) VALUES(4, 'Doublelift', 'Liquid Training Facility', 500);

INSERT INTO bookOrder(cno, bno, qty) VALUES(1, 3, 2);

SELECT * FROM book;
SELECT * FROM customer;
SELECT * FROM bookOrder;

--TRIGGERS AND FUNCTIONS
CREATE OR REPLACE FUNCTION updateBalanceOnBookOrder()



--TRANSACTION EXERCISES

--A:
INSERT INTO book(bno, title, author, category, price, sales) VALUES(6, 'How Ionia beat Noxus', 'Karma', 'Lifestyle', 5.00, 4);

--B:
DELETE * FROM book WHERE bno = 4;

--C:
INSERT INTO customer(cno, name, address, balance) VALUES(5, 'DRAVEN', 'Noxus', 9000);

--D:
DELETE * from customer WHERE cno = 4;

--E:
INSERT INTO bookOrder(cno, bno, qty) VALUES(5, 6, 1);
CREATE OR REPLACE FUNCTION update_sales()


CREATE OR REPLACE FUNCTION update_balance()