setup.sql

DROP DATABASE IF EXISTS `imdb`;
CREATE DATABASE `imdb` /*!40100 COLLATE 'utf8mb4_unicode_ci' */;
USE `imdb`;
CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` CHAR(50) NOT NULL,
	`username` CHAR(15) NOT NULL,
	`email` CHAR(255) NOT NULL,
	`password` CHAR(255) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE INDEX (`username`),
	UNIQUE INDEX (`email`)
);
CREATE TABLE `tokens` (
	`token` CHAR(255) NOT NULL,
	`user` INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`token`),
	FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE `movies` (
	`id` CHAR(15) NOT NULL,
	`title` CHAR(255) NOT NULL,
	`description` VARCHAR(1000) NOT NULL,
	`avatar` TEXT NOT NULL,
	`year` YEAR NOT NULL,
	`duration` CHAR(7) NOT NULL,
	`rate` FLOAT UNSIGNED NOT NULL,
	`rank` TINYINT(3) UNSIGNED NOT NULL,
	`directors` VARCHAR(1000) NOT NULL,
	`actors` VARCHAR(1000) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE INDEX (`rank`)
);
CREATE TABLE `thumbnails` (
	`url` VARCHAR(1000) NOT NULL,
	`movie` CHAR(15) NOT NULL,
	FOREIGN KEY (`movie`) REFERENCES `movies` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE `comments` (
	`author` INT(10) UNSIGNED NOT NULL,
	`movie` CHAR(15) NOT NULL,
	`time` DATETIME NOT NULL,
	`content` CHAR(255) NOT NULL,
	FOREIGN KEY (`author`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (`movie`) REFERENCES `movies` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE `rates` (
	`user` INT(10) UNSIGNED NOT NULL,
	`movie` CHAR(15) NOT NULL,
	`rate` FLOAT NOT NULL,
	FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (`movie`) REFERENCES `movies` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE `favorites` (
	`user` INT(10) UNSIGNED NOT NULL,
	`movie` CHAR(15) NOT NULL,
	PRIMARY KEY (`user`, `movie`),
	FOREIGN KEY (`movie`) REFERENCES `movies` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE `genres` (
	`genre` CHAR(15) NOT NULL,
	`movie` CHAR(15) NOT NULL,
	PRIMARY KEY (`genre`, `movie`),
	FOREIGN KEY (`movie`) REFERENCES `movies` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE `top_week` (
	`movie` CHAR(15) NOT NULL,
	`rank` TINYINT(3) UNSIGNED NOT NULL,
	PRIMARY KEY (`movie`),
	UNIQUE INDEX (`rank`),
	FOREIGN KEY (`movie`) REFERENCES `movies` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
);