From ca22ffbb3eebef8d9f8b851b7b9b60a4f73a484f Mon Sep 17 00:00:00 2001
From: Ben Sima <ben@bsima.me>
Date: Thu, 4 Sep 2025 09:34:30 -0400
Subject: Add Metadata Extraction for Podcast Queue

This commit adds support for extracting title and author metadata from URLs when
adding articles to the podcast queue. It includes database schema changes,
migration logic, and UI updates to display the extracted metadata.
---
 Biz/PodcastItLater/Core.py | 50 +++++++++++++++++++++++++++++++++++++---------
 1 file changed, 41 insertions(+), 9 deletions(-)

(limited to 'Biz/PodcastItLater/Core.py')

diff --git a/Biz/PodcastItLater/Core.py b/Biz/PodcastItLater/Core.py
index 6c04db8..86676b5 100644
--- a/Biz/PodcastItLater/Core.py
+++ b/Biz/PodcastItLater/Core.py
@@ -82,7 +82,9 @@ class Database:  # noqa: PLR0904
                     status TEXT DEFAULT 'pending',
                     retry_count INTEGER DEFAULT 0,
                     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-                    error_message TEXT
+                    error_message TEXT,
+                    title TEXT,
+                    author TEXT
                 )
             """)
 
@@ -117,14 +119,19 @@ class Database:  # noqa: PLR0904
         # Run migration to add user support
         Database.migrate_to_multi_user(db_path)
 
+        # Run migration to add metadata fields
+        Database.migrate_add_metadata_fields(db_path)
+
     @staticmethod
-    def add_to_queue(
+    def add_to_queue(  # noqa: PLR0913, PLR0917
         url: str,
         email: str,
         user_id: int,
         db_path: str | None = None,
+        title: str | None = None,
+        author: str | None = None,
     ) -> int:
-        """Insert new job into queue, return job ID.
+        """Insert new job into queue with metadata, return job ID.
 
         Raises:
             ValueError: If job ID cannot be retrieved after insert.
@@ -134,8 +141,9 @@ class Database:  # noqa: PLR0904
         with Database.get_connection(db_path) as conn:
             cursor = conn.cursor()
             cursor.execute(
-                "INSERT INTO queue (url, email, user_id) VALUES (?, ?, ?)",
-                (url, email, user_id),
+                "INSERT INTO queue (url, email, user_id, title, author) "
+                "VALUES (?, ?, ?, ?, ?)",
+                (url, email, user_id, title, author),
             )
             conn.commit()
             job_id = cursor.lastrowid
@@ -284,7 +292,8 @@ class Database:  # noqa: PLR0904
         with Database.get_connection(db_path) as conn:
             cursor = conn.cursor()
             cursor.execute("""
-                SELECT id, url, email, status, created_at, error_message
+                SELECT id, url, email, status, created_at, error_message,
+                       title, author
                 FROM queue
                 WHERE status IN ('pending', 'processing', 'error')
                 ORDER BY created_at DESC
@@ -382,7 +391,7 @@ class Database:  # noqa: PLR0904
                 cursor.execute(
                     """
                     SELECT id, url, email, status, retry_count, created_at,
-                           error_message
+                           error_message, title, author
                     FROM queue
                     WHERE user_id = ?
                     ORDER BY created_at DESC
@@ -392,7 +401,7 @@ class Database:  # noqa: PLR0904
             else:
                 cursor.execute("""
                     SELECT id, url, email, status, retry_count, created_at,
-                           error_message
+                           error_message, title, author
                     FROM queue
                     ORDER BY created_at DESC
                 """)
@@ -489,6 +498,28 @@ class Database:  # noqa: PLR0904
             conn.commit()
             logger.info("Database migrated to support multiple users")
 
+    @staticmethod
+    def migrate_add_metadata_fields(db_path: str | None = None) -> None:
+        """Add title and author fields to queue table."""
+        if db_path is None:
+            db_path = Database.get_default_db_path()
+        with Database.get_connection(db_path) as conn:
+            cursor = conn.cursor()
+
+            # Check if columns already exist
+            cursor.execute("PRAGMA table_info(queue)")
+            queue_info = cursor.fetchall()
+            queue_columns = [col[1] for col in queue_info]
+
+            if "title" not in queue_columns:
+                cursor.execute("ALTER TABLE queue ADD COLUMN title TEXT")
+
+            if "author" not in queue_columns:
+                cursor.execute("ALTER TABLE queue ADD COLUMN author TEXT")
+
+            conn.commit()
+            logger.info("Database migrated to support metadata fields")
+
     @staticmethod
     def create_user(email: str, db_path: str | None = None) -> tuple[int, str]:
         """Create a new user and return (user_id, token).
@@ -583,7 +614,8 @@ class Database:  # noqa: PLR0904
             cursor = conn.cursor()
             cursor.execute(
                 """
-                SELECT id, url, email, status, created_at, error_message
+                SELECT id, url, email, status, created_at, error_message,
+                       title, author
                 FROM queue
                 WHERE user_id = ? AND
                       status IN ('pending', 'processing', 'error')
-- 
cgit v1.2.3