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

2 files changed, 73 insertions, 15 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`),
diff --git a/src/imdbdata.go b/src/imdbdata.go
index c4216a7..cea914e 100644
--- a/src/imdbdata.go
+++ b/src/imdbdata.go
@@ -263,15 +263,25 @@ func (a *App) applyImdbUpdates(ratings map[string]ratingEntry, basics map[string
 	}
 	defer bStmt.Close()
 
-	genreStmt, err := tx.Prepare(`
-		INSERT INTO genre (imdb_id, name)
-		SELECT i.id, ? FROM imdb i WHERE i.imdb_id = ?
+	genreUpsert, err := tx.Prepare(`
+		INSERT INTO genre (name) VALUES (?)
+		ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)
 	`)
 	if err != nil {
 		tx.Rollback()
-		return fmt.Errorf("prepare genre insert: %w", err)
+		return fmt.Errorf("prepare genre upsert: %w", err)
 	}
-	defer genreStmt.Close()
+	defer genreUpsert.Close()
+
+	genreLink, err := tx.Prepare(`
+		INSERT INTO imdb_genre (imdb_id, genre_id)
+		VALUES ((SELECT i.id FROM imdb i WHERE i.imdb_id = ?), ?)
+	`)
+	if err != nil {
+		tx.Rollback()
+		return fmt.Errorf("prepare genre link: %w", err)
+	}
+	defer genreLink.Close()
 
 	rCount, bCount, gCount := 0, 0, 0
 
@@ -295,9 +305,19 @@ func (a *App) applyImdbUpdates(ratings map[string]ratingEntry, basics map[string
 		bCount++
 
 		for _, g := range b.Genres {
-			if _, err := genreStmt.Exec(g, id); err != nil {
+			res, err := genreUpsert.Exec(g)
+			if err != nil {
+				tx.Rollback()
+				return fmt.Errorf("upsert genre %s: %w", g, err)
+			}
+			genreID, err := res.LastInsertId()
+			if err != nil {
+				tx.Rollback()
+				return fmt.Errorf("get genre id: %w", err)
+			}
+			if _, err := genreLink.Exec(id, genreID); err != nil {
 				tx.Rollback()
-				return fmt.Errorf("insert genre %s for %s: %w", g, id, err)
+				return fmt.Errorf("link genre %s for %s: %w", g, id, err)
 			}
 			gCount++
 		}
@@ -340,8 +360,8 @@ func (a *App) fetchAndUpdateImdbData() error {
 	}
 	if err := a.DB.QueryRow(`
 		SELECT COUNT(*) FROM imdb i
-		LEFT JOIN genre g ON g.imdb_id = i.id
-		WHERE g.id IS NULL
+		LEFT JOIN imdb_genre ig ON ig.imdb_id = i.id
+		WHERE ig.id IS NULL
 	`).Scan(&missingGenres); err != nil {
 		return fmt.Errorf("count genres: %w", err)
 	}