feat: adapt genre code for n:m relation via imdb_genre

- genre table: (id, name) with unique name constraint
- imdb_genre table: (id, imdb_id, genre_id) junction table
- Upsert genres via INSERT ... ON DUPLICATE KEY UPDATE
- Link via imdb_genre using LAST_INSERT_ID
- Check missing genres via LEFT JOIN imdb_genre

1 files changed, 44 insertions, 6 deletions

diff --git a/schema/schema.sql b/schema/schema.sql
index b643cc7..9b1e5ab 100644
--- a/schema/schema.sql
+++ b/schema/schema.sql
@@ -5,19 +5,21 @@ 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,
-  `links_id` int(11) NOT NULL,
   `imdb_id` varchar(255) NOT NULL,
   `wiki_article` varchar(255) DEFAULT NULL,
   `synopsis` text DEFAULT NULL,
@@ -31,11 +33,25 @@ CREATE TABLE `imdb` (
   `average_rating` decimal(3,1) unsigned DEFAULT NULL,
   `num_votes` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
-  KEY `links_id` (`links_id`),
-  CONSTRAINT `imdb_ibfk_1` FOREIGN KEY (`links_id`) REFERENCES `links` (`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(),
@@ -52,6 +68,20 @@ CREATE TABLE `links` (
 ) 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,
@@ -62,18 +92,24 @@ CREATE TABLE `max_item` (
   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`)
+  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`)
+  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
@@ -81,6 +117,7 @@ INSERT INTO `profession` (`id`, `name`) VALUES
 (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,
@@ -98,13 +135,14 @@ CREATE TABLE `story` (
 ) 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`),
-  KEY `imdb_id` (`imdb_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`),