SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `genre`;
CREATE TABLE `genre` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `imdb_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `imdb_id` (`imdb_id`),
  CONSTRAINT `genre_ibfk_1` FOREIGN KEY (`imdb_id`) REFERENCES `imdb` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `imdb`;
CREATE TABLE `imdb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `imdb_id` varchar(255) NOT NULL,
  `wiki_article` varchar(255) DEFAULT NULL,
  `synopsis` text DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `poster_url` text DEFAULT NULL,
  `title_type` varchar(255) DEFAULT NULL,
  `primary_title` varchar(255) DEFAULT NULL,
  `original_title` varchar(255) DEFAULT NULL,
  `start_year` int(11) DEFAULT NULL,
  `runtime_minutes` int(11) DEFAULT NULL,
  `average_rating` decimal(3,1) unsigned DEFAULT NULL,
  `num_votes` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `imdb_id` (`imdb_id`),
  UNIQUE KEY `wiki_article` (`wiki_article`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `imdb_genre`;
CREATE TABLE `imdb_genre` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `imdb_id` int(11) NOT NULL,
  `genre_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `imdb_id` (`imdb_id`),
  KEY `genre_id` (`genre_id`),
  CONSTRAINT `imdb_genre_ibfk_1` FOREIGN KEY (`imdb_id`) REFERENCES `imdb` (`id`),
  CONSTRAINT `imdb_genre_ibfk_2` FOREIGN KEY (`genre_id`) REFERENCES `genre` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `links`;
CREATE TABLE `links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `story_id` int(11) NOT NULL,
  `url` varchar(1000) DEFAULT NULL,
  `field` int(11) NOT NULL,
  `host` varchar(255) DEFAULT NULL,
  `param` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `story_id` (`story_id`),
  CONSTRAINT `links_ibfk_1` FOREIGN KEY (`story_id`) REFERENCES `story` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=407448 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `links_imdb`;
CREATE TABLE `links_imdb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `links` int(11) NOT NULL,
  `imdb` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `links` (`links`),
  KEY `imdb` (`imdb`),
  CONSTRAINT `links_imdb_ibfk_1` FOREIGN KEY (`links`) REFERENCES `links` (`id`),
  CONSTRAINT `links_imdb_ibfk_2` FOREIGN KEY (`imdb`) REFERENCES `imdb` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `max_item`;
CREATE TABLE `max_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `max_story_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `max_story_id` (`max_story_id`),
  CONSTRAINT `max_item_ibfk_1` FOREIGN KEY (`max_story_id`) REFERENCES `story` (`story_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `max_item` (`id`, `max_story_id`, `created_at`, `updated_at`) VALUES
(1,	42989962,	'2025-02-09 11:30:02',	'2025-02-09 11:30:01');

DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `profession`;
CREATE TABLE `profession` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `profession` (`id`, `name`) VALUES
(1,	'actor'),
(2,	'director'),
(3,	'screenwriter');

DROP TABLE IF EXISTS `story`;
CREATE TABLE `story` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `story_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `type` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `text` text DEFAULT NULL,
  `score` int(11) NOT NULL,
  `descendants` int(11) NOT NULL,
  `time` int(11) NOT NULL,
  `poster` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `story_id` (`story_id`)
) ENGINE=InnoDB AUTO_INCREMENT=366974 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `who`;
CREATE TABLE `who` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `imdb_id` int(11) NOT NULL,
  `people_id` int(11) NOT NULL,
  `profession_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `imdb_id` (`imdb_id`,`people_id`,`profession_id`),
  KEY `people_id` (`people_id`),
  KEY `profession_id` (`profession_id`),
  CONSTRAINT `who_ibfk_1` FOREIGN KEY (`imdb_id`) REFERENCES `imdb` (`id`),
  CONSTRAINT `who_ibfk_2` FOREIGN KEY (`people_id`) REFERENCES `people` (`id`),
  CONSTRAINT `who_ibfk_3` FOREIGN KEY (`profession_id`) REFERENCES `profession` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;