1 files changed, 109 insertions, 0 deletions
diff --git a/Biz/PodcastItLater/Core.py b/Biz/PodcastItLater/Core.py
index c625e11..40b50ea 100644
--- a/Biz/PodcastItLater/Core.py
+++ b/Biz/PodcastItLater/Core.py
@@ -1088,6 +1088,98 @@ class Database:  # noqa: PLR0904
             return dict(row) if row is not None else None
 
     @staticmethod
+    def get_metrics_summary() -> dict[str, Any]:
+        """Get aggregate metrics summary for admin dashboard.
+
+        Returns:
+            dict with keys:
+            - total_episodes: Total number of episodes
+            - total_plays: Total play events
+            - total_downloads: Total download events
+            - total_adds: Total add events
+            - most_played: List of top 10 most played episodes
+            - most_downloaded: List of top 10 most downloaded episodes
+            - most_added: List of top 10 most added episodes
+        """
+        with Database.get_connection() as conn:
+            cursor = conn.cursor()
+
+            # Get total episodes
+            cursor.execute("SELECT COUNT(*) as count FROM episodes")
+            total_episodes = cursor.fetchone()["count"]
+
+            # Get event counts
+            cursor.execute(
+                "SELECT COUNT(*) as count FROM episode_metrics "
+                "WHERE event_type = 'played'",
+            )
+            total_plays = cursor.fetchone()["count"]
+
+            cursor.execute(
+                "SELECT COUNT(*) as count FROM episode_metrics "
+                "WHERE event_type = 'downloaded'",
+            )
+            total_downloads = cursor.fetchone()["count"]
+
+            cursor.execute(
+                "SELECT COUNT(*) as count FROM episode_metrics "
+                "WHERE event_type = 'added'",
+            )
+            total_adds = cursor.fetchone()["count"]
+
+            # Get most played episodes
+            cursor.execute(
+                """
+                SELECT e.id, e.title, e.author, COUNT(*) as play_count
+                FROM episode_metrics em
+                JOIN episodes e ON em.episode_id = e.id
+                WHERE em.event_type = 'played'
+                GROUP BY em.episode_id
+                ORDER BY play_count DESC
+                LIMIT 10
+            """,
+            )
+            most_played = [dict(row) for row in cursor.fetchall()]
+
+            # Get most downloaded episodes
+            cursor.execute(
+                """
+                SELECT e.id, e.title, e.author, COUNT(*) as download_count
+                FROM episode_metrics em
+                JOIN episodes e ON em.episode_id = e.id
+                WHERE em.event_type = 'downloaded'
+                GROUP BY em.episode_id
+                ORDER BY download_count DESC
+                LIMIT 10
+            """,
+            )
+            most_downloaded = [dict(row) for row in cursor.fetchall()]
+
+            # Get most added episodes
+            cursor.execute(
+                """
+                SELECT e.id, e.title, e.author, COUNT(*) as add_count
+                FROM episode_metrics em
+                JOIN episodes e ON em.episode_id = e.id
+                WHERE em.event_type = 'added'
+                GROUP BY em.episode_id
+                ORDER BY add_count DESC
+                LIMIT 10
+            """,
+            )
+            most_added = [dict(row) for row in cursor.fetchall()]
+
+            return {
+                "total_episodes": total_episodes,
+                "total_plays": total_plays,
+                "total_downloads": total_downloads,
+                "total_adds": total_adds,
+                "most_played": most_played,
+                "most_downloaded": most_downloaded,
+                "most_added": most_added,
+            }
+
+    @staticmethod
     def track_episode_event(
         episode_id: int,
         event_type: str,
@@ -1130,6 +1222,23 @@ class Database:  # noqa: PLR0904
             return {row["event_type"]: row["count"] for row in rows}
 
     @staticmethod
+    def get_episode_metric_events(episode_id: int) -> list[dict[str, Any]]:
+        """Get raw metric events for an episode (for testing)."""
+        with Database.get_connection() as conn:
+            cursor = conn.cursor()
+            cursor.execute(
+                """
+                SELECT id, episode_id, user_id, event_type, created_at
+                FROM episode_metrics
+                WHERE episode_id = ?
+                ORDER BY created_at DESC
+            """,
+                (episode_id,),
+            )
+            rows = cursor.fetchall()
+            return [dict(row) for row in rows]
+
+    @staticmethod
     def set_user_stripe_customer(user_id: int, customer_id: str) -> None:
         """Link Stripe customer ID to user."""
         with Database.get_connection() as conn: