DB ESM - HW_PROJECT

-- #1. Create Schema
DROP SCHEMA IF EXISTS `platform`;
CREATE SCHEMA `platform`;
USE `platform`;

-- #2. Create Tables
-- #2.1 Table: Users
CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(16) NOT NULL,
  `age` int,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password` VARCHAR(32) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  CONSTRAINT CHK_Person CHECK (`age` >= 18));

-- #2.2 Table: Roles
CREATE TABLE `roles`(
  `id` INT NOT NULL AUTO_INCREMENT,
  `role` VARCHAR(16) NOT NULL,

  PRIMARY KEY (`id`));

-- #2.3 Table: User-Roles Many to Many Relationship
CREATE TABLE `user_roles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NULL,
  `role_id` INT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  CONSTRAINT `user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `user_role`
    FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`));

-- #2.4 Table: Videos
CREATE TABLE `videos` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `title` VARCHAR(45) NOT NULL,
  `description` VARCHAR(1023) NULL,
  `video_url` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  CONSTRAINT `video_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`));

-- #2.5 Table: Comments (for videos)
CREATE TABLE `comments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `video_id` INT NOT NULL,
  `content` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  CONSTRAINT `comment_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `comment_video`
    FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`));

-- #2.6 Table: Video Likes
CREATE TABLE `video_likes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `video_id` INT NULL,
  `user_id` INT NULL,
  `is_up` BOOLEAN NULL DEFAULT FALSE,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  CONSTRAINT `uv_video_like`
    FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`),
  CONSTRAINT `uv_user_like`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`));


-- #2.7 Table: Comment Likes
CREATE TABLE `comment_likes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `comment_id` INT NULL,
  `user_id` INT NULL,
  `is_up` BOOLEAN NULL DEFAULT FALSE,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  CONSTRAINT `uc_comment_like`
    FOREIGN KEY (`comment_id`) REFERENCES `comments` (`id`),
  CONSTRAINT `uc_user_like`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`));

-- #2.8 Table: Categories (for videos)
CREATE TABLE `categories` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `category` VARCHAR(45) NOT NULL,

  PRIMARY KEY (`id`));

-- #2.9 Table: Video-Categories Many to Many Relationship
CREATE TABLE `video_category` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `video_id` INT NULL,
  `category_id` INT NULL,

  PRIMARY KEY (`id`),
  CONSTRAINT `fk_vc_video`
    FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`),
  CONSTRAINT `fk_vc_category`
    FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`));

-- #3 (Optional) Insert some data into tables
-- #3.1 Users
Insert into `users` (`username`, `age`, `email`, `password`)
Values ('Senshi', 23, 'saba.pochkhua@gmail.com', '3668D391BECFEC1C0EC3B388CB82DCA6');

Insert into `users` (`username`, `age`, `email`, `password`)
Values ('pewdiepie', 27, 'pdp@gmail.com', 'C83E986814118CB34E3BDF0BBD12AB55');

Insert into `users` (`username`, `age`, `email`, `password`)
Values ('Eminem', 33, 'em@gmail.com', '26B637ED41273425BE243E8D42E5B461');

Insert IGNORE into `users` (`username`, `age`, `email`, `password`)
Values ('someone', 23, 'saba.pochkhua@gmail.com', '3668D391BECFEC1C0EC3B388CB82DCA6'); -- error: duplicate entry on mail

Insert IGNORE into `users` (`username`, `age`, `email`, `password`)
Values ('killer_boy', 13, 'best_boy_EU@gmail.com', '7745D9B1899F8C7316ECF065D8FC2469'); -- error: check constraint on age

-- #3.2 roles
Insert into `roles` (`role`)
Values ('admin');

Insert into `roles` (`role`)
Values ('super_user');

Insert into `roles` (`role`)
Values ('user');

-- #3.3 user_roles
Insert into `user_roles` (`user_id`, `role_id`)
Values ((SELECT `id` from `users` where `username` = 'Senshi'), (SELECT `id` from `roles` where `role` = 'admin'));

Insert into `user_roles` (`user_id`, `role_id`)
Values ((SELECT `id` from `users` where `username` = 'pewdiepie'), (SELECT `id` from `roles` where `role` = 'user'));

Insert IGNORE into `user_roles` (`user_id`, `role_id`)
Values (1000, (SELECT `id` from `roles` where `role` = 'user')); -- error: fk constraint on user id

Insert IGNORE into `user_roles` (`user_id`, `role_id`)
Values ((SELECT `id` from `users` where `username` = 'Senshi'), 1000); -- error: fk constraint on role id

-- #3.4 Videos
Insert into `videos` (`user_id`, `title`, `description`, `video_url`)
values ((SELECT `id` from `users` where `username` = 'pewdiepie'), 'bitch lasagna', 'Track made by Party In Backyard ► https://www.youtube.com/channel/blablabla', '/videos/1.mp4');

Insert into `videos` (`user_id`, `title`, `description`, `video_url`)
values ((SELECT `id` from `users` where `username` = 'pewdiepie'), 'bitch lasagna *UPDATED*', 'GREATES MUSIC IN DA WORLD', '/videos/2.mp4');

Insert IGNORE into `videos` (`user_id`, `title`, `description`, `video_url`)
values (1000, 'Something', 'Something Desc', '/videos/3.mp4'); -- error: fk constraint on user id

-- #3.5 Comments
Insert into `comments` (`user_id`, `video_id`, `content`)
values ((SELECT `id` from `users` where `username` = 'Senshi'), (SELECT `id` from `videos` where `title` = 'bitch lasagna'), 'Greates Music Video EVER!');

Insert into `comments` (`user_id`, `video_id`, `content`)
values ((SELECT `id` from `users` where `username` = 'Eminem'), (SELECT `id` from `videos` where `title` = 'bitch lasagna'), 'MY MUSIC IS BETTER!');

Insert IGNORE into `comments` (`user_id`, `video_id`, `content`)
values ((SELECT `id` from `users` where `username` = 'Eminem'), (SELECT `id` from `videos` where `title` = 'bitch lasagna'), null); -- error: content cannot be null

-- #3.6 Vide Likes
Insert into `video_likes` (`video_id`, `user_id`, `is_up`)
values ((SELECT `id` from `videos` where `title` = 'bitch lasagna'), (SELECT `id` from `users` where `username` = 'Senshi'), true);

Insert into `video_likes` (`video_id`, `user_id`, `is_up`)
values ((SELECT `id` from `videos` where `title` = 'bitch lasagna'), (SELECT `id` from `users` where `username` = 'pewdiepie'), true);

Insert into `video_likes` (`video_id`, `user_id`, `is_up`)
values ((SELECT `id` from `videos` where `title` = 'bitch lasagna'), (SELECT `id` from `users` where `username` = 'Eminem'), false);

-- #3.7 Comment Likes
Insert into `comment_likes` (`comment_id`, `user_id`, `is_up`)
values ((SELECT `id` from `comments` where `content` = 'Greates Music Video EVER!'), (SELECT `id` from `users` where `username` = 'pewdiepie'), true);

Insert into `comment_likes` (`comment_id`, `user_id`, `is_up`)
values ((SELECT `id` from `comments` where `content` = 'MY MUSIC IS BETTER!'), (SELECT `id` from `users` where `username` = 'pewdiepie'), false);

Insert into `comment_likes` (`comment_id`, `user_id`, `is_up`)
values ((SELECT `id` from `comments` where `content` = 'MY MUSIC IS BETTER!'), (SELECT `id` from `users` where `username` = 'Senshi'), false);

-- #3.8 Categories
Insert into `categories` (`category`)
values ('Music');

Insert into `categories` (`category`)
values ('Sport');

Insert into `categories` (`category`)
values ('Funny');

-- #3.9 Video Category Connection
Insert into `video_category` (`video_id`, `category_id`)
values ((SELECT `id` from `videos` where `title` = 'bitch lasagna'), (SELECT `id` from `categories` where `category` = 'Music'));

Insert into `video_category` (`video_id`, `category_id`)
values ((SELECT `id` from `videos` where `title` = 'bitch lasagna'), (SELECT `id` from `categories` where `category` = 'Funny'));


-- #4. Create Views
-- #4.1 View: User Information
CREATE OR REPLACE VIEW UserInfo AS
SELECT  `username`, `email`, `age`, COALESCE(videos.count, 0) as 'Total Uploaded Videos', COALESCE(comments.count, 0) as 'Total Comments Written', COALESCE(comment_likes.count, 0) as 'Total Comments Liked/Disliked' FROM users
LEFT OUTER JOIN (SELECT `user_id`, COUNT(*) as count FROM `videos` GROUP BY `user_id`) videos on users.id = videos.user_id
LEFT OUTER JOIN (SELECT `user_id`, COUNT(*) as count FROM `comments` GROUP BY `user_id`) comments on users.id = comments.user_id
LEFT OUTER JOIN (SELECT `user_id`, COUNT(*) as count FROM `comment_likes` GROUP BY `user_id`) comment_likes on users.id = comment_likes.user_id;

select * from UserInfo;

-- #4.2 View: Video Information
CREATE OR REPLACE VIEW VideoInfo AS
SELECT  `title`, `description`, `video_url`, COALESCE(video_all_comments.count, 0) as 'Total Comments on Video', COALESCE(video_all_likes.count, 0) as 'Total Likes', COALESCE(video_all_dislikes.count, 0) as 'Total Disikes'  FROM videos
LEFT OUTER JOIN (SELECT `video_id`, COUNT(*) as count FROM `comments` GROUP BY `video_id`) video_all_comments on videos.id = video_all_comments .video_id
LEFT OUTER JOIN (SELECT `video_id`, COUNT(*) as count FROM `video_likes` where is_up GROUP BY `video_id`) video_all_likes on videos.id = video_all_likes .video_id
LEFT OUTER JOIN (SELECT `video_id`, COUNT(*) as count FROM `video_likes` where !is_up GROUP BY `video_id`) video_all_dislikes on videos.id = video_all_dislikes.video_id;

select * from VideoInfo;

-- #4.3 View: Category Info
CREATE OR REPLACE VIEW CategoryInfo as
SELECT categories.id, categories.category, COALESCE(cat.count,0) as 'Video Count' FROM categories
LEFT OUTER JOIN (SELECT `category_id`, COUNT(*) as count FROM `video_category` GROUP BY `category_id`) cat on cat.category_id = categories.id;

SELECT * FROM CategoryInfo;