feat: add /genres page listing all genres with movie counts

- Add CinemaController::genres() and ::genre($name) methods
- /genres shows all genres with movie counts (like /popular/topics)
- /genres/{genre} shows movies filtered by that genre with pagination
- Genre badges on cinema_show page now link to /genres/{genre}
- Add Genres link to navbar next to Cinema

1 files changed, 65 insertions, 0 deletions

diff --git a/app/Http/Controllers/CinemaController.php b/app/Http/Controllers/CinemaController.php
index c433eea..a526830 100644
--- a/app/Http/Controllers/CinemaController.php
+++ b/app/Http/Controllers/CinemaController.php
@@ -80,6 +80,71 @@ class CinemaController extends Controller
         ]);
     }
 
+    public function genres()
+    {
+        $genres = DB::connection('cinema')
+            ->table('genre')
+            ->select('genre.name', DB::raw('COUNT(imdb_genre.imdb_id) as count'))
+            ->join('imdb_genre', 'imdb_genre.genre_id', '=', 'genre.id')
+            ->join('imdb', 'imdb.id', '=', 'imdb_genre.imdb_id')
+            ->whereNotNull('imdb.primary_title')
+            ->whereNotNull('imdb.wiki_article')
+            ->where('imdb.title_type', 'movie')
+            ->groupBy('genre.name')
+            ->orderBy('count', 'desc')
+            ->get();
+
+        return view('genres', ['genres' => $genres]);
+    }
+
+    public function genre($name)
+    {
+        $genre = DB::connection('cinema')
+            ->table('genre')
+            ->where('name', $name)
+            ->first();
+
+        if (! $genre) {
+            abort(404);
+        }
+
+        $movies = DB::connection('cinema')
+            ->table('imdb')
+            ->select(
+                'imdb.id',
+                'imdb.imdb_id',
+                'imdb.primary_title',
+                'imdb.original_title',
+                'imdb.start_year',
+                'imdb.average_rating',
+                'imdb.num_votes',
+                'imdb.synopsis',
+                'imdb.poster_url',
+                'imdb.title_type',
+                'imdb.runtime_minutes'
+            )
+            ->join('imdb_genre', 'imdb_genre.imdb_id', '=', 'imdb.id')
+            ->join('genre', 'genre.id', '=', 'imdb_genre.genre_id')
+            ->where('genre.name', $name)
+            ->whereNotNull('imdb.primary_title')
+            ->whereNotNull('imdb.wiki_article')
+            ->where('imdb.title_type', 'movie')
+            ->orderBy('imdb.average_rating', 'desc')
+            ->simplePaginate(12);
+
+        $count = DB::connection('cinema')
+            ->table('imdb')
+            ->join('imdb_genre', 'imdb_genre.imdb_id', '=', 'imdb.id')
+            ->join('genre', 'genre.id', '=', 'imdb_genre.genre_id')
+            ->where('genre.name', $name)
+            ->whereNotNull('imdb.primary_title')
+            ->whereNotNull('imdb.wiki_article')
+            ->where('imdb.title_type', 'movie')
+            ->count();
+
+        return view('genre_movies', ['movies' => $movies, 'count' => $count, 'genre_name' => $name]);
+    }
+
     public function search(Request $request)
     {
         $query = $request->input('q', '');