Merge Ava deployment changes

1 files changed, 156 insertions, 0 deletions

diff --git a/Biz/PodcastItLater/Core.py b/Biz/PodcastItLater/Core.py
index 3a88f22..05ed153 100644
--- a/Biz/PodcastItLater/Core.py
+++ b/Biz/PodcastItLater/Core.py
@@ -202,6 +202,9 @@ class Database:  # noqa: PLR0904
         # Run migration to add public feed features
         Database.migrate_add_public_feed()
 
+        # Run migration to add feedback table
+        Database.migrate_add_feedback_table()
+
     @staticmethod
     def add_to_queue(
         url: str,
@@ -774,6 +777,42 @@ class Database:  # noqa: PLR0904
             logger.info("Database migrated for public feed feature")
 
     @staticmethod
+    def migrate_add_feedback_table() -> None:
+        """Add feedback table for collecting user research data."""
+        with Database.get_connection() as conn:
+            cursor = conn.cursor()
+
+            cursor.execute("""
+                CREATE TABLE IF NOT EXISTS feedback (
+                    id TEXT PRIMARY KEY,
+                    email TEXT,
+                    source TEXT,
+                    campaign_id TEXT,
+                    rating INTEGER,
+                    feedback_text TEXT,
+                    use_case TEXT,
+                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+                )
+            """)
+
+            # Create indexes for querying feedback
+            cursor.execute(
+                "CREATE INDEX IF NOT EXISTS idx_feedback_source "
+                "ON feedback(source)",
+            )
+            cursor.execute(
+                "CREATE INDEX IF NOT EXISTS idx_feedback_campaign "
+                "ON feedback(campaign_id)",
+            )
+            cursor.execute(
+                "CREATE INDEX IF NOT EXISTS idx_feedback_created "
+                "ON feedback(created_at)",
+            )
+
+            conn.commit()
+            logger.info("Created feedback table")
+
+    @staticmethod
     def migrate_add_default_titles() -> None:
         """Add default titles to queue items that have None titles."""
         with Database.get_connection() as conn:
@@ -1073,6 +1112,18 @@ class Database:  # noqa: PLR0904
             logger.info("Unmarked episode %s as public", episode_id)
 
     @staticmethod
+    def toggle_episode_public(episode_id: int) -> None:
+        """Toggle an episode's public status."""
+        with Database.get_connection() as conn:
+            cursor = conn.cursor()
+            cursor.execute(
+                "UPDATE episodes SET is_public = NOT is_public WHERE id = ?",
+                (episode_id,),
+            )
+            conn.commit()
+            logger.info("Toggled public status for episode %s", episode_id)
+
+    @staticmethod
     def get_public_episodes(limit: int = 50) -> list[dict[str, Any]]:
         """Get public episodes for public feed."""
         with Database.get_connection() as conn:
@@ -1189,6 +1240,111 @@ class Database:  # noqa: PLR0904
             return dict(row) if row is not None else None
 
     @staticmethod
+    def create_feedback(  # noqa: PLR0913, PLR0917
+        feedback_id: str,
+        email: str | None,
+        source: str | None,
+        campaign_id: str | None,
+        rating: int | None,
+        feedback_text: str | None,
+        use_case: str | None,
+    ) -> str:
+        """Create a new feedback entry.
+
+        Args:
+            feedback_id: Unique ID for the feedback
+            email: Optional email address
+            source: How they heard about PIL (outreach, organic, trial)
+            campaign_id: Optional link to outreach draft ID
+            rating: Optional 1-5 rating
+            feedback_text: Optional general feedback
+            use_case: What they want to use PIL for
+
+        Returns:
+            The feedback ID
+        """
+        with Database.get_connection() as conn:
+            cursor = conn.cursor()
+            cursor.execute(
+                """
+                INSERT INTO feedback
+                    (id, email, source, campaign_id, rating,
+                     feedback_text, use_case)
+                VALUES (?, ?, ?, ?, ?, ?, ?)
+                """,
+                (
+                    feedback_id,
+                    email,
+                    source,
+                    campaign_id,
+                    rating,
+                    feedback_text,
+                    use_case,
+                ),
+            )
+            conn.commit()
+            logger.info("Created feedback %s", feedback_id)
+            return feedback_id
+
+    @staticmethod
+    def get_feedback(
+        limit: int = 50,
+        since: str | None = None,
+    ) -> list[dict[str, Any]]:
+        """Get feedback entries.
+
+        Args:
+            limit: Maximum number of entries to return
+            since: Optional ISO date to filter by (get entries after this date)
+
+        Returns:
+            List of feedback entries
+        """
+        with Database.get_connection() as conn:
+            cursor = conn.cursor()
+            if since:
+                cursor.execute(
+                    """
+                    SELECT * FROM feedback
+                    WHERE created_at >= ?
+                    ORDER BY created_at DESC
+                    LIMIT ?
+                    """,
+                    (since, limit),
+                )
+            else:
+                cursor.execute(
+                    """
+                    SELECT * FROM feedback
+                    ORDER BY created_at DESC
+                    LIMIT ?
+                    """,
+                    (limit,),
+                )
+            rows = cursor.fetchall()
+            return [dict(row) for row in rows]
+
+    @staticmethod
+    def get_feedback_by_id(feedback_id: str) -> dict[str, Any] | None:
+        """Get a single feedback entry by ID."""
+        with Database.get_connection() as conn:
+            cursor = conn.cursor()
+            cursor.execute(
+                "SELECT * FROM feedback WHERE id = ?",
+                (feedback_id,),
+            )
+            row = cursor.fetchone()
+            return dict(row) if row is not None else None
+
+    @staticmethod
+    def get_feedback_count() -> int:
+        """Get total count of feedback entries."""
+        with Database.get_connection() as conn:
+            cursor = conn.cursor()
+            cursor.execute("SELECT COUNT(*) as count FROM feedback")
+            return int(cursor.fetchone()["count"])
+
+    @staticmethod
     def get_metrics_summary() -> dict[str, Any]:
         """Get aggregate metrics summary for admin dashboard.