DROP TABLE user_rate_publication;

DROP TABLE publication_has_genre;

DROP TABLE comments;

DROP TABLE publications;

DROP TABLE users;

DROP TABLE genres;


CREATE TABLE users
  (
     id       INT PRIMARY KEY,
     username VARCHAR2(20) UNIQUE NOT NULL,
     password_hash VARCHAR2(20) NOT NULL CHECK (LENGTH(password_hash) > 5)
  );

CREATE TABLE publications
  (
     id         INT PRIMARY KEY,
     title      VARCHAR2(100) NOT NULL,
     content    VARCHAR2(6398) NOT NULL,
     created_at DATE DEFAULT CURRENT_DATE,
     n_rating   INT DEFAULT 0 NOT NULL CHECK (n_rating >= 0),
     sum_rating INT DEFAULT 0 NOT NULL CHECK (sum_rating >= 0),
     user_id    INT,
     FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  );

CREATE TABLE genres
  (
     id   INT PRIMARY KEY,
     name VARCHAR2(20) NOT NULL
  );

CREATE TABLE comments
  (
     id             INT PRIMARY KEY,
     content        VARCHAR2(200) NOT NULL CHECK (content != ''),
     created_at     DATE DEFAULT CURRENT_DATE,
     user_id        INT,
     publication_id INT,
     FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
     FOREIGN KEY (publication_id) REFERENCES publications(id) ON DELETE CASCADE
  );

CREATE TABLE publication_has_genre
  (
     publication_id INT,
     genre_id    INT,
     PRIMARY KEY (publication_id, genre_id),
     FOREIGN KEY (publication_id) REFERENCES publications(id) ON DELETE CASCADE,
     FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE CASCADE
  );

CREATE TABLE user_rate_publication
  (
     user_id        INT,
     publication_id INT,
     rating         INT CHECK (rating BETWEEN 1 AND 5),
     PRIMARY KEY (user_id, publication_id),
     FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
     FOREIGN KEY (publication_id) REFERENCES publications(id) ON DELETE CASCADE
  );

--Insert data

INSERT INTO genres VALUES (1, 'drama');
INSERT INTO genres VALUES (2, 'action');
INSERT INTO genres VALUES (3, 'adventure');
INSERT INTO genres VALUES (4, 'comedy');
INSERT INTO genres VALUES (5, 'crime');
INSERT INTO genres VALUES (6, 'fantasy');
INSERT INTO genres VALUES (7, 'horror');
INSERT INTO genres VALUES (8, 'romance');
INSERT INTO genres VALUES (9, 'science fiction');
INSERT INTO genres VALUES (10, 'historical');

--Register users

INSERT INTO users VALUES (1, 'ann black', 'annpass');
INSERT INTO users VALUES (2, 'dan white', 'danpass');
INSERT INTO users VALUES (3, 'ben grey', 'benpass');
INSERT INTO users VALUES (4, 'rachel green', 'rachelpass');

--Create index for publications
CREATE INDEX idx_title ON publications (title);

--Create index for publication-genre relationship
CREATE INDEX idx_genre ON publication_has_genre (genre_id);

--login (unsuccessful)

SELECT * FROM users WHERE users.id = 3 AND users.password_hash = 'Bpass';

--login (successful)

SELECT * FROM users WHERE users.id = 3 AND users.password_hash = 'benpass';

--edit username

UPDATE users SET users.username = 'Benjamin Grey' WHERE users.id = 3 AND users.password_hash = 'benpass';

--edit password

UPDATE users SET users.password_hash = 'NewSTrongPassword113' WHERE users.id = 3 AND users.password_hash = 'benpass';

--adding publications

SET TRANSACTION NAME 'publish_11';
    INSERT INTO publications (id, title, content, user_id) VALUES (11, 'My Novel', 'This is my firs novel', 1);
    INSERT INTO publication_has_genre VALUES (11, 1);
    INSERT INTO publication_has_genre VALUES (11, 8);
    INSERT INTO publication_has_genre VALUES (11, 10);
COMMIT;

SET TRANSACTION NAME 'publish_21';
    INSERT INTO publications (id, title, content, user_id) VALUES (21, 'New detective by Mr White', 'Best detecrive you will ever read', 2);
    INSERT INTO publication_has_genre VALUES (21, 5);
    INSERT INTO publication_has_genre VALUES (21, 2);
COMMIT;

SET TRANSACTION NAME 'publish_22';
    INSERT INTO publications (id, title, content, user_id) VALUES (22, 'New detective by Mr White: The Chilling Sequel', 'Detective Spectre faces The Horror', 2);
    INSERT INTO publication_has_genre VALUES (22, 5);
    INSERT INTO publication_has_genre VALUES (22, 7);
COMMIT;

SET TRANSACTION NAME 'publish_23';
    INSERT INTO publications (id, title, content, user_id) VALUES (23, 'New detective by Mr White: Grand Finale', 'Detective Spectre gets the girl', 2);
    INSERT INTO publication_has_genre VALUES (23, 5);
    INSERT INTO publication_has_genre VALUES (23, 8);
COMMIT;

--rate publication

SET TRANSACTION NAME 'rate_3_11';
    INSERT INTO user_rate_publication VALUES (3, 11, 2);
    UPDATE publications p SET p.n_rating = p.n_rating+1 WHERE p.id=11;
    UPDATE publications p SET p.sum_rating = p.sum_rating+(SELECT urp.rating
    FROM user_rate_publication urp WHERE urp.user_id=3 AND urp.publication_id=11) WHERE p.id=11;
COMMIT;

SET TRANSACTION NAME 'rate_3_21';
    INSERT INTO user_rate_publication VALUES (3, 21, 3);
    UPDATE publications p SET p.n_rating = p.n_rating+1 WHERE p.id=21;
    UPDATE publications p SET p.sum_rating = p.sum_rating+(SELECT urp.rating
    FROM user_rate_publication urp WHERE urp.user_id=3 AND urp.publication_id=21) WHERE p.id=21;
COMMIT;

SET TRANSACTION NAME 'rate_1_21';
    INSERT INTO user_rate_publication VALUES (1, 21, 4);
    UPDATE publications p SET p.n_rating = p.n_rating+1 WHERE p.id=21;
    UPDATE publications p SET p.sum_rating = p.sum_rating+(SELECT urp.rating
    FROM user_rate_publication urp WHERE urp.user_id=1 AND urp.publication_id=21) WHERE p.id=21;
COMMIT;

SET TRANSACTION NAME 'rate_4_21';
    INSERT INTO user_rate_publication VALUES (4, 21, 5);
    UPDATE publications p SET p.n_rating = p.n_rating+1 WHERE p.id=21;
    UPDATE publications p SET p.sum_rating = p.sum_rating+(SELECT urp.rating
    FROM user_rate_publication urp WHERE urp.user_id=4 AND urp.publication_id=21) WHERE p.id=21;
COMMIT;

SET TRANSACTION NAME 'rate_4_22';
    INSERT INTO user_rate_publication VALUES (4, 22, 5);
    UPDATE publications p SET p.n_rating = p.n_rating+1 WHERE p.id=22;
    UPDATE publications p SET p.sum_rating = p.sum_rating+(SELECT urp.rating
    FROM user_rate_publication urp WHERE urp.user_id=4 AND urp.publication_id=22) WHERE p.id=22;
COMMIT;

--unrate publication
SET TRANSACTION NAME 'rate_4_21';
    UPDATE publications p SET p.n_rating = p.n_rating-1 WHERE p.id=21;
    UPDATE publications p SET p.sum_rating = p.sum_rating-(SELECT urp.rating
    FROM user_rate_publication urp WHERE urp.user_id=4 AND urp.publication_id=21) WHERE p.id=21;
    DELETE FROM user_rate_publication urp WHERE urp.user_id=4 AND urp.publication_id=21;
COMMIT;

--create comments
INSERT INTO comments (id, content, user_id, publication_id) VALUES (1, 'Marvellous!', 4, 21);

--find all published crime stories

SELECT p.id, p.title, p.created_at, p.user_id, (CASE WHEN p.n_rating = 0 THEN 0 ELSE TRUNC(CAST(p.sum_rating AS FLOAT) / p.n_rating, 2) END) AS rating
FROM publications p INNER JOIN  publication_has_genre pc ON p.id = pc.publication_id
WHERE pc.genre_id = (SELECT genres.id FROM genres WHERE genres.name='crime');

--sort publications by rating

SELECT p.id, p.title, p.created_at, p.user_id, (CASE WHEN p.n_rating = 0 THEN 0 ELSE TRUNC(CAST(p.sum_rating AS FLOAT) / p.n_rating, 2) END) AS rating
FROM publications p ORDER BY rating DESC;

--delete user
SET TRANSACTION NAME 'delete_1';
    UPDATE publications p SET p.n_rating = p.n_rating-1 WHERE p.id IN (SELECT urp.publication_id
    FROM user_rate_publication urp WHERE urp.user_id=1);
    UPDATE publications p SET p.sum_rating =  (SELECT (p.sum_rating-urp.rating)
    FROM publications p INNER JOIN user_rate_publication urp ON p.id=urp.publication_id WHERE urp.user_id = 1) WHERE p.id IN (SELECT urp.publication_id
    FROM user_rate_publication urp WHERE urp.user_id=1);
    DELETE FROM users WHERE users.id=1;
COMMIT;

--find a work by title
SELECT p.id, p.title, p.created_at, p.user_id FROM publications p WHERE p.title = 'New detective by Mr White: Grand Finale';