-- Criar o banco de dados
CREATE DATABASE IF NOT EXISTS talkcine;

-- Usar o banco de dados
USE talkcine;

-- Tabela de usuários
CREATE TABLE IF NOT EXISTS users (
  userId INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  birthday VARCHAR(50) NOT NULL,
  profilePic VARCHAR(200),
  bio VARCHAR(500),
  password VARCHAR(255) NOT NULL,
  followers INT,
  following INT,
  lists INT
);

-- Tabela de filmes assistidos
CREATE TABLE IF NOT EXISTS watched_movies (
  watchedMovieId INT PRIMARY KEY AUTO_INCREMENT,
  movieName VARCHAR(255) NOT NULL,
  movieImage VARCHAR(200),
  username VARCHAR(255) NOT NULL,
  FOREIGN KEY (username) REFERENCES users(username)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- Tabela de avaliações de filmes
CREATE TABLE IF NOT EXISTS movie_reviews (
  reviewId INT PRIMARY KEY AUTO_INCREMENT,
  movieName VARCHAR(255) NOT NULL,
  categories VARCHAR(255),
  reviewText TEXT,
  rating DECIMAL(3, 1),
  moviePoster VARCHAR(200),
  username VARCHAR(255) NOT NULL,
  profilePic VARCHAR(200), -- Coluna adicionada para armazenar profilePic
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  likesCount INT DEFAULT 0,
  FOREIGN KEY (username) REFERENCES users(username)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- Tabela de curtidas em avaliações
CREATE TABLE IF NOT EXISTS likes (
  likeId INT PRIMARY KEY AUTO_INCREMENT,
  reviewId INT NOT NULL,
  username VARCHAR(255) NOT NULL,
  FOREIGN KEY (reviewId) REFERENCES movie_reviews(reviewId)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (username) REFERENCES users(username)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- Tabela de comentários em avaliações
CREATE TABLE IF NOT EXISTS comments (
  commentId INT PRIMARY KEY AUTO_INCREMENT,
  reviewId INT NOT NULL,
  username VARCHAR(255) NOT NULL,
  commentText TEXT NOT NULL,
  profilePic VARCHAR(200),
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (reviewId) REFERENCES movie_reviews(reviewId)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (username) REFERENCES users(username)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- Tabela de seguidores e seguindo
CREATE TABLE IF NOT EXISTS followers (
  followerId INT PRIMARY KEY AUTO_INCREMENT,
  followerUsername VARCHAR(255) NOT NULL,
  followingUsername VARCHAR(255) NOT NULL,
  FOREIGN KEY (followerUsername) REFERENCES users(username)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (followingUsername) REFERENCES users(username)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  UNIQUE (followerUsername, followingUsername)
);

-- Trigger para atualização em cascata da profilePic na tabela movie_reviews
DELIMITER //

CREATE TRIGGER update_movie_reviews_profilePic
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE movie_reviews
    SET profilePic = NEW.profilePic
    WHERE username = NEW.username;
END; //

DELIMITER ;

-- Trigger para atualização em cascata da profilePic na tabela comments
DELIMITER //

CREATE TRIGGER update_comments_profilePic
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE comments
    SET profilePic = NEW.profilePic
    WHERE username = NEW.username;
END; //

DELIMITER ;
