USE master
IF EXISTS( SELECT * FROM sys.DATABASES WHERE name = 'projeto')
    DROP DATABASE projeto;

GO
CREATE DATABASE projeto
ON PRIMARY
    (NAME = projeto_dat,
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\projetodat.mdf',
     SIZE = 10,
     MAXSIZE = 50,
     FILEGROWTH = 5)
LOG ON
    (NAME = projeto_log,
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\projetolog.ldf',
     SIZE = 5,
     MAXSIZE = 25,
     FILEGROWTH = 5);
GO

USE projeto;
GO

CREATE TABLE [USER](
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE NOT NULL,
password_hash VARBINARY(128) NOT NULL,
birthdate DATE NOT NULL,
profile_image_id INT,
bio VARCHAR(500),
TYPE VARCHAR(50) CHECK (TYPE IN('User','Moderator','Admin')) NOT NULL,
STATUS VARCHAR(50) CHECK (STATUS IN('Normal', 'Observation', 'Banned')) NOT NULL
);


CREATE TABLE Project(
id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
category_name VARCHAR(50) NOT NULL,
title VARCHAR(50) NOT NULL,
description VARCHAR(500),
gallery_id INT NOT NULL,
DATE DATETIME NOT NULL,
STATUS VARCHAR(50) CHECK(STATUS IN('Approved', 'Not approved', 'Approving')) NOT NULL
);


CREATE TABLE Gallery(
id INT IDENTITY(1,1) PRIMARY KEY,
description VARCHAR(50)
);

CREATE TABLE [Image](
id INT IDENTITY(1,1) PRIMARY KEY,
content VARCHAR(MAX) NOT NULL
);

CREATE TABLE Category(
name VARCHAR(50) PRIMARY KEY,
parent_category_name VARCHAR(50),
);

CREATE TABLE Comment(
id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
project_id INT NOT NULL,
content VARCHAR(300) NOT NULL,
parent_comment_id INT,
DATE DATETIME NOT NULL,
STATUS VARCHAR(50) CHECK (STATUS IN ('Visivel', 'Invisivel'))
);


 /**

 AO ALTERAR O VALOR DE COMMENT.STATUS
 CONTAR O TOTAL DE COMENTARIOS DE USER X QUE SEJAM INVISIVEL
 SE >= 10
 ALTERAR O VALOR DE USER.STATUS
 <-- aqui ter trigger para inserir no StatusChange
 **/

CREATE TABLE [Login](
id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
device VARCHAR(50) NOT NULL,
DATE DATETIME NOT NULL
);

CREATE TABLE [Notification](
id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
text VARCHAR(100) NOT NULL,
DATE DATETIME NOT NULL
);

CREATE TABLE Download(
id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
gallery_id INT NOT NULL,
DATE DATETIME NOT NULL
);

CREATE TABLE Advertisement(
id INT IDENTITY(1,1) PRIMARY KEY,
company VARCHAR(50) NOT NULL,
image_id INT NOT NULL,
totalGained INT,
link VARCHAR(200) NOT NULL
);


CREATE TABLE [VIEW](
id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL UNIQUE,
project_id INT NOT NULL,
DATE DATETIME NOT NULL
);


CREATE TABLE StatusChange(
id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL UNIQUE,
moderator_id INT NOT NULL,
new_status VARCHAR(50) CHECK (new_status IN('Normal', 'Observation', 'Banned')) NOT NULL,
DATE DATETIME NOT NULL
);

CREATE TABLE UserFollowCategory(
id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
category_name VARCHAR(50) NOT NULL,
DATE DATETIME NOT NULL,
);

CREATE TABLE UserFollowUser(
id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
followed_user_id INT NOT NULL,
DATE DATETIME NOT NULL
);

CREATE TABLE UserRateProject(
id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
project_id INT NOT NULL,
VALUE INT CHECK(VALUE IN (0,1,2,3,4,5,6,7,8,9,10)) NOT NULL
);

CREATE TABLE GalleryImage(
id INT IDENTITY(1,1) PRIMARY KEY,
gallery_id INT NOT NULL,
image_id INT NOT NULL
);

CREATE TABLE ProjectAdvertisement(
id INT IDENTITY(1,1) PRIMARY KEY,
project_id INT NOT NULL,
advertisement_id INT NOT NULL
);


ALTER TABLE [USER]
ADD FOREIGN KEY (profile_image_id) REFERENCES [Image](id);

ALTER TABLE Project
ADD FOREIGN KEY (user_id) REFERENCES [USER](id);

ALTER TABLE Project
ADD FOREIGN KEY (category_name) REFERENCES Category(name);

ALTER TABLE Project
ADD FOREIGN KEY (gallery_id) REFERENCES Gallery(id);

ALTER TABLE Category
ADD FOREIGN KEY (parent_category_name) REFERENCES Category(name);

ALTER TABLE Comment
ADD FOREIGN KEY (user_id) REFERENCES [USER](id);

ALTER TABLE Comment
ADD FOREIGN KEY (project_id) REFERENCES Project(id);

ALTER TABLE Comment
ADD FOREIGN KEY (parent_comment_id) REFERENCES Comment(id);

ALTER TABLE [Login]
ADD FOREIGN KEY (user_id) REFERENCES [USER](id);

ALTER TABLE [Notification]
ADD FOREIGN KEY (user_id) REFERENCES [USER](id);

ALTER TABLE Download
ADD FOREIGN KEY (user_id) REFERENCES [USER](id);

ALTER TABLE Download
ADD FOREIGN KEY (gallery_id) REFERENCES Gallery(id);

ALTER TABLE Advertisement
ADD FOREIGN KEY (image_id) REFERENCES [Image](id);

ALTER TABLE [VIEW]
ADD FOREIGN KEY (user_id) REFERENCES [USER](id);

ALTER TABLE [VIEW]
ADD FOREIGN KEY (project_id) REFERENCES Project(id);

ALTER TABLE StatusChange
ADD FOREIGN KEY (user_id) REFERENCES [USER](id);

ALTER TABLE StatusChange
ADD FOREIGN KEY (moderator_id) REFERENCES [USER](id);

ALTER TABLE UserFollowCategory
ADD FOREIGN KEY (user_id) REFERENCES [USER](id);

ALTER TABLE UserFollowCategory
ADD FOREIGN KEY (category_name) REFERENCES Category(name);

ALTER TABLE UserFollowUser
ADD FOREIGN KEY (user_id) REFERENCES [USER](id);

ALTER TABLE UserFollowUser
ADD FOREIGN KEY (followed_user_id) REFERENCES [USER](id);


ALTER TABLE UserRateProject
ADD FOREIGN KEY (user_id) REFERENCES [USER](id);

ALTER TABLE UserRateProject
ADD FOREIGN KEY (project_id) REFERENCES Project(id);

ALTER TABLE GalleryImage
ADD FOREIGN KEY (gallery_id) REFERENCES Gallery(id);

ALTER TABLE GalleryImage
ADD FOREIGN KEY (image_id) REFERENCES [Image](id);

ALTER TABLE ProjectAdvertisement
ADD FOREIGN KEY (project_id) REFERENCES Project(id);

ALTER TABLE ProjectAdvertisement
ADD FOREIGN KEY (advertisement_id) REFERENCES Advertisement(id);


-- trigger password hash

IF OBJECT_ID('trgPasswordHash') IS NOT NULL
    DROP TRIGGER trgPasswordHash;

GO
CREATE TRIGGER trgPasswordHash ON [USER]
FOR INSERT
AS
BEGIN
    UPDATE [USER]
    SET password_hash = HASHBYTES('SHA1', password_hash);
END
GO

--teste password hash
/*


INSERT INTO [User](name, email, password_hash, birthdate, type, status)
            VALUES('Tiago Santos', 'tiago.afsantos@hotmail.com', CAST('passwordteste' AS VARBINARY(128)), '19981113', 'User', 'Normal');


SELECT * FROM [User]

*/

--trigger notification
-- cada vez que um project e upload ir ver a tabela user follow user para ver quem segue o gajo que deu upload e mandar notificaçao a esses id's

IF OBJECT_ID('trgNotificationOnUserUpload') IS NOT NULL
    DROP TRIGGER trgNotificationOnUserUpload;

GO
CREATE TRIGGER trgNotificationOnUserUpload ON Project
AFTER INSERT
AS
BEGIN

    DECLARE @uploaderId INT;

    SELECT @uploaderId = (SELECT user_id FROM Project WHERE id = (SELECT MAX(id) FROM Project));

    DECLARE @cursorFollow AS CURSOR;

    SET @cursorFollow = CURSOR FOR
                        (SELECT user_id FROM UserFollowUser WHERE followed_user_id = @uploaderId);

    DECLARE @userFollows INT;

    OPEN @cursorFollow;

    FETCH NEXT FROM @cursorFollow INTO @userFollows;

    WHILE @@FETCH_STATUS = 0

        BEGIN

            INSERT INTO [Notification] (user_id, text, DATE)
                                VALUES (@userFollows, 'Um utilizador que voce segue carregou um novo projeto!', GETDATE());

        FETCH NEXT FROM @cursorFollow INTO @userFollows;

        END

    CLOSE @cursorFollow;

    DEALLOCATE @cursorFollow;

END
GO


IF OBJECT_ID('trgNotificationOnCategoryUpload') IS NOT NULL
    DROP TRIGGER trgNotificationOnCategoryUpload;

GO
CREATE TRIGGER trgNotificationOnCategoryUpload ON Project
AFTER INSERT
AS
BEGIN

    DECLARE @categoryName VARCHAR(50);
    DECLARE @uploaderId INT;

    SELECT @categoryName = (SELECT category_name FROM Project WHERE id = (SELECT MAX(id) FROM Project));
    SELECT @uploaderId = (SELECT user_id FROM Project WHERE id = (SELECT MAX(id) FROM Project));

    DECLARE @cursorFollow AS CURSOR;

    SET @cursorFollow = CURSOR FOR
                        (SELECT user_id FROM UserFollowCategory WHERE category_name = @categoryName);

    DECLARE @userFollows INT;

    OPEN @cursorFollow;

    FETCH NEXT FROM @cursorFollow INTO @userFollows;

    WHILE @@FETCH_STATUS = 0

        BEGIN

            IF (@userFollows != @uploaderId)
                INSERT INTO [Notification] (user_id, text, DATE)
                                    VALUES (@userFollows, 'Uma categoria que voce segue (' + @categoryName + ') tem um novo projeto!', GETDATE());

        FETCH NEXT FROM @cursorFollow INTO @userFollows;

        END

    CLOSE @cursorFollow;

    DEALLOCATE @cursorFollow;

END
GO

--testes do trigger
/*
INSERT INTO [User](name, email, password_hash, birthdate, type, status)
            VALUES('Tiago Santos', 'tiago.afsantos@hotmail.com', CAST('passwordteste' AS VARBINARY(128)), '19981113', 'User', 'Normal')

INSERT INTO [User](name, email, password_hash, birthdate, type, status)
            VALUES('Tiago 1234', 'tiago.1234@hotmail.com', CAST('passwordteste' AS VARBINARY(128)), '19981113', 'User', 'Normal');

INSERT INTO Category(name) values ('Digital Art');

INSERT INTO UserFollowCategory(user_id, category_name, date) VALUES (1, 'Digital Art', GETDATE());

INSERT INTO Gallery(description) values (null);

INSERT INTO Project(user_id, category_name, title, description, gallery_id, date, status)
             VALUES(2, 'Digital Art', 'Arte', 'Descricao', 1, GETDATE(), 'Approved');

SELECT * FROM [Notification];
*/


IF OBJECT_ID('trgCommentNotification') IS NOT NULL
    DROP TRIGGER trgCommentNotification;

GO
CREATE TRIGGER trgCommentNotification ON Comment
AFTER INSERT
AS
BEGIN

    IF (SELECT parent_comment_id FROM Comment WHERE id = (SELECT MAX(id) FROM Comment)) IS NOT NULL
    BEGIN

        DECLARE @replyUsername VARCHAR(50);
        DECLARE @replyUserId INT;
        DECLARE @originalUserId INT;

        SELECT @replyUserId = (SELECT user_id FROM Comment WHERE id = (SELECT MAX(id) FROM Comment));
        SELECT @replyUsername = (SELECT name FROM [USER] WHERE id = @replyUserId);
        SELECT @originalUserId = (SELECT user_id FROM Comment WHERE id = ( ( SELECT parent_comment_id FROM Comment WHERE id = (SELECT MAX(id) FROM Comment))));


        INSERT INTO [Notification] (user_id, text, DATE)
        VALUES (@replyUserId, '' + @replyUsername + ' respondeu ao seu comentario', GETDATE());

    END

END
GO

/** UTILIZADORES **/
INSERT INTO [USER](name, email, password_hash, birthdate, TYPE, STATUS)
            VALUES('Tiago Santos', 'tiago.afsantos@hotmail.com', CAST('passwordteste' AS VARBINARY(128)), '19981113', 'User', 'Normal')

INSERT INTO [USER](name, email, password_hash, birthdate, TYPE, STATUS)
            VALUES('Ruben Amendoeira', 'ruben.amendoeira@gmail.com', CAST('passwordteste2' AS VARBINARY(128)), '19961206', 'User', 'Normal');

INSERT INTO [USER](name, email, password_hash, birthdate, TYPE, STATUS)
            VALUES('Hugo Ferreira', 'hugoferreira@gmail.com', CAST('benfica123' AS VARBINARY(128)), '11980216', 'User', 'Normal');

INSERT INTO [USER](name, email, password_hash, birthdate, TYPE, STATUS)
            VALUES('Tiago Neto', 'tiagoneto@gmail.com', CAST('pokemon1998' AS VARBINARY(128)), '11980121', 'User', 'Normal');

INSERT INTO [USER](name, email, password_hash, birthdate, TYPE, STATUS)
            VALUES('Tomás Santos', 'tomas.santos24@gmail.com', CAST('password123' AS VARBINARY(128)), '11961103', 'User', 'Normal');

INSERT INTO [USER](name, email, password_hash, birthdate, TYPE, STATUS)
            VALUES('João Almeida', 'joaoalmeida@gmail.com', CAST('password001' AS VARBINARY(128)), '11951123', 'Moderator', 'Normal');

INSERT INTO [USER](name, email, password_hash, birthdate, TYPE, STATUS)
            VALUES('Pedro Nunes', 'nunes1121@gmail.com', CAST('helloworld' AS VARBINARY(128)), '11950114', 'Moderator', 'Normal');

INSERT INTO [USER](name, email, password_hash, birthdate, TYPE, STATUS)
            VALUES('Diogo Silva', 'silvadiogo@gmail.com', CAST('helloworld12' AS VARBINARY(128)), '11960704', 'Admin', 'Normal');


/** CATEGORIAS **/
INSERT INTO Category(name) VALUES ('Digital Art');
INSERT INTO Category(name) VALUES ('Photography');

INSERT INTO Category(name, parent_category_name) VALUES ('3D Art', 'Digital Art');
INSERT INTO Category(name, parent_category_name) VALUES ('Animation', 'Digital Art')
INSERT INTO Category(name, parent_category_name) VALUES ('Pixel Art', 'Digital Art');
INSERT INTO Category(name, parent_category_name) VALUES ('Typography', 'Digital Art');

INSERT INTO Category(name, parent_category_name) VALUES ('Landscapes', 'Photography');
INSERT INTO Category(name, parent_category_name) VALUES ('Portraits', 'Photography');
INSERT INTO Category(name, parent_category_name) VALUES ('Photomanipulation', 'Photography');

/** GALERIAS **/
INSERT INTO Gallery(description) VALUES (NULL);
INSERT INTO Gallery(description) VALUES (NULL);
INSERT INTO Gallery(description) VALUES (NULL);
INSERT INTO Gallery(description) VALUES (NULL);

/** IMAGENS **/
INSERT INTO Image(content) VALUES('');
INSERT INTO Image(content) VALUES('');
INSERT INTO Image(content) VALUES('');
INSERT INTO Image(content) VALUES('');
INSERT INTO Image(content) VALUES('');
INSERT INTO Image(content) VALUES('');
INSERT INTO Image(content) VALUES('');
INSERT INTO Image(content) VALUES('');
INSERT INTO Image(content) VALUES('');
INSERT INTO Image(content) VALUES('');

/** INSERIR IMAGENS NUMA GALERIA **/
INSERT INTO GalleryImage(gallery_id, image_id) VALUES(1, 1);
INSERT INTO GalleryImage(gallery_id, image_id) VALUES(1, 2);
INSERT INTO GalleryImage(gallery_id, image_id) VALUES(1, 3);
INSERT INTO GalleryImage(gallery_id, image_id) VALUES(2, 4);
INSERT INTO GalleryImage(gallery_id, image_id) VALUES(2, 5);
INSERT INTO GalleryImage(gallery_id, image_id) VALUES(3, 6);
INSERT INTO GalleryImage(gallery_id, image_id) VALUES(3, 7);
INSERT INTO GalleryImage(gallery_id, image_id) VALUES(3, 8);
INSERT INTO GalleryImage(gallery_id, image_id) VALUES(3, 9);
INSERT INTO GalleryImage(gallery_id, image_id) VALUES(4, 10);

/** UTILIZADOR SEGUE UMA CATEGORIA **/
INSERT INTO UserFollowCategory(user_id, category_name, DATE) VALUES(1, 'Typography', GETDATE());
INSERT INTO UserFollowCategory(user_id, category_name, DATE) VALUES(2, 'Animation', GETDATE());

/** UTILIZADOR SEGUE OUTRO UTILIZADOR **/
INSERT INTO UserFollowUser(user_id, followed_user_id, DATE) VALUES(1, 2, GETDATE());
INSERT INTO UserFollowUser(user_id, followed_user_id, DATE) VALUES(2, 4, GETDATE());

/** PROJETOS **/
INSERT INTO Project(user_id, category_name, title, description, gallery_id, DATE, STATUS)
             VALUES(2, 'Landscapes', 'Serra da Estrela', 'Fotos do fim de semana.', 1, GETDATE(), 'Approved');
INSERT INTO Project(user_id, category_name, title, description, gallery_id, DATE, STATUS)
             VALUES(3, 'Typography', 'Branding IPS', 'Branding showcase para o IPS.', 2, GETDATE(), 'Approved');
INSERT INTO Project(user_id, category_name, title, description, gallery_id, DATE, STATUS)
             VALUES(3, 'Animation', 'Teste de animação de liquidos.', '', 3, GETDATE(), 'Approved');
INSERT INTO Project(user_id, category_name, title, description, gallery_id, DATE, STATUS)
             VALUES(4, 'Digital Art', 'Financial App UI', '', 4, GETDATE(), 'Approved');

/** VISUALIZAÇÕES **/
INSERT INTO [VIEW](user_id, project_id, DATE) VALUES(1, 1, GETDATE());
INSERT INTO [VIEW](user_id, project_id, DATE) VALUES(2, 1, GETDATE());
INSERT INTO [VIEW](user_id, project_id, DATE) VALUES(3, 2, GETDATE());
INSERT INTO [VIEW](user_id, project_id, DATE) VALUES(4, 3, GETDATE());
INSERT INTO [VIEW](user_id, project_id, DATE) VALUES(2, 4, GETDATE());

/** AVALIAÇÕES **/
INSERT INTO UserRateProject(user_id, project_id, VALUE) VALUES(1, 3, 7);
INSERT INTO UserRateProject(user_id, project_id, VALUE) VALUES(3, 2, 9);


/** COMENTÁRIOS **/
INSERT INTO Comment(user_id, project_id, content, DATE) VALUES (1, 1, 'teste', GETDATE());
INSERT INTO Comment(user_id, project_id, content, parent_comment_id, DATE) VALUES (2, 1, 'teste2', 4,  GETDATE());
INSERT INTO Comment(user_id, project_id, content, DATE) VALUES (3, 2, 'teste3',  GETDATE());

/** PUBLICIDADE **/
INSERT INTO Advertisement(company, image_id, link) VALUES('Coca Cola', 3, 'http://www.cocacola.com');
INSERT INTO Advertisement(company, image_id, link) VALUES('McDonalds', 6, 'http://www.mcdonalds.com/promotions');

/** CONECTAR PUBLICIDADES A PROJETOS **/
INSERT INTO ProjectAdvertisement(project_id, advertisement_id) VALUES(2, 1);
INSERT INTO ProjectAdvertisement(project_id, advertisement_id) VALUES(4, 2);

/** DOWNLOADS **/
INSERT INTO Download(user_id, gallery_id, DATE) VALUES(2, 1, GETDATE());
INSERT INTO Download(user_id, gallery_id, DATE) VALUES(3, 2, GETDATE());
INSERT INTO Download(user_id, gallery_id, DATE) VALUES(3, 4, GETDATE());

/** INICIOS DE SESSÃO **/
INSERT INTO Login(user_id, device, DATE) VALUES(3, 'Samsung-673D', GETDATE());
INSERT INTO Login(user_id, device, DATE) VALUES(1, 'Acer 524', GETDATE());
INSERT INTO Login(user_id, device, DATE) VALUES(2, 'iPhone X 8719', GETDATE());

SELECT * FROM [Notification];