DROP DATABASE IF EXISTS youtube_db;
CREATE DATABASE youtube_db;
USE youtube_db;

CREATE TABLE User (
	id INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    username varchar(255) NOT NULL UNIQUE KEY,
    views_num int NOT NULL DEFAULT 0,

	PRIMARY KEY(id)
);

CREATE TABLE Channel (
	id INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(100) NOT NULL,
    user_id int,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES User(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE Video (
	id INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(50) NOT NULL,
    channel_id int NOT NULL,
    views INTEGER DEFAULT 0,

    PRIMARY KEY(id),
    FOREIGN KEY(channel_id) REFERENCES Channel(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TRIGGER VideosInsert
AFTER INSERT
ON Video
FOR EACH ROW
	UPDATE user
    SET views_num = ( select sum(views) from Video left join Channel on Video.channel_id = Channel.id)
    WHERE New.id = id;


CREATE TRIGGER VideosUpdate
AFTER UPDATE
ON Video
FOR EACH ROW
	UPDATE user
    SET views_num = ( select sum(views) from Video left join Channel on Video.channel_id = Channel.id)
    WHERE New.id = id;


CREATE TRIGGER VideosDelete
AFTER Delete
ON Video
FOR EACH ROW
	UPDATE user
    SET views_num = 0
    WHERE OLD.id = id;

INSERT INTO User(name, username) VALUES ('Pesho Programista', 'pe60');
INSERT INTO User(name, username) VALUES ('Gosho', 'go60');
INSERT INTO User(name, username) VALUES ('Tosho', 'to60');

INSERT INTO Channel(name, user_id) VALUES ('Channel1', 1);
INSERT INTO Channel(name, user_id) VALUES ('Channel2', 2);
INSERT INTO Channel(name, user_id) VALUES ('Channel3', 3);

INSERT INTO Video(name, channel_id) VALUES ('Video1', 1);
INSERT INTO Video(name, channel_id, views) VALUES ('Video2', 2, 2);
INSERT INTO Video(name, channel_id) VALUES ('Video0', 3);

Select v.name, v.views, u.name, ch.name from Video v
LEFT JOIN Channel ch ON ch.id = v.channel_id
LEFT JOIN  User u ON u.id = ch.user_id;

Select ch.name from Channel ch
Inner join Video v on v.channel_id = ch.id
group by ch.name;

Select u.name, u.username, u.views_num from User u
Inner join Channel ch on ch.user_id = u.id
Inner join Video v on v.channel_id
group by u.username;