Merge task t-rWbMpxaBk: Metrics view in Admin dashboard

1 files changed, 62 insertions, 1 deletions

diff --git a/Biz/PodcastItLater/Core.py b/Biz/PodcastItLater/Core.py
index 0bb7251..2f05db3 100644
--- a/Biz/PodcastItLater/Core.py
+++ b/Biz/PodcastItLater/Core.py
@@ -391,7 +391,7 @@ class Database:  # noqa: PLR0904
             cursor.execute(
                 """
                 SELECT id, title, audio_url, duration, created_at,
-                       content_length, author, original_url, user_id
+                       content_length, author, original_url, user_id, is_public
                 FROM episodes
                 WHERE id = ?
             """,
@@ -1176,6 +1176,10 @@ class Database:  # noqa: PLR0904
             - 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
+            - total_users: Total number of users
+            - active_subscriptions: Number of active subscriptions
+            - submissions_24h: Submissions in last 24 hours
+            - submissions_7d: Submissions in last 7 days
         """
         with Database.get_connection() as conn:
             cursor = conn.cursor()
@@ -1245,6 +1249,29 @@ class Database:  # noqa: PLR0904
             )
             most_added = [dict(row) for row in cursor.fetchall()]
 
+            # Get user metrics
+            cursor.execute("SELECT COUNT(*) as count FROM users")
+            total_users = cursor.fetchone()["count"]
+
+            cursor.execute(
+                "SELECT COUNT(*) as count FROM users "
+                "WHERE subscription_status = 'active'",
+            )
+            active_subscriptions = cursor.fetchone()["count"]
+
+            # Get recent submission metrics
+            cursor.execute(
+                "SELECT COUNT(*) as count FROM queue "
+                "WHERE created_at >= datetime('now', '-1 day')",
+            )
+            submissions_24h = cursor.fetchone()["count"]
+
+            cursor.execute(
+                "SELECT COUNT(*) as count FROM queue "
+                "WHERE created_at >= datetime('now', '-7 days')",
+            )
+            submissions_7d = cursor.fetchone()["count"]
+
             return {
                 "total_episodes": total_episodes,
                 "total_plays": total_plays,
@@ -1253,6 +1280,10 @@ class Database:  # noqa: PLR0904
                 "most_played": most_played,
                 "most_downloaded": most_downloaded,
                 "most_added": most_added,
+                "total_users": total_users,
+                "active_subscriptions": active_subscriptions,
+                "submissions_24h": submissions_24h,
+                "submissions_7d": submissions_7d,
             }
 
     @staticmethod
@@ -1553,6 +1584,36 @@ class TestDatabase(Test.TestCase):
         # Test completed successfully - migration worked
         self.assertIsNotNone(conn)
 
+    def test_get_metrics_summary_extended(self) -> None:
+        """Verify extended metrics summary."""
+        # Create some data
+        user_id, _ = Database.create_user("test@example.com")
+        Database.create_episode(
+            "Test Article",
+            "url",
+            100,
+            1000,
+            user_id,
+        )
+
+        # Create a queue item
+        Database.add_to_queue(
+            "https://example.com",
+            "test@example.com",
+            user_id,
+        )
+
+        metrics = Database.get_metrics_summary()
+
+        self.assertIn("total_users", metrics)
+        self.assertIn("active_subscriptions", metrics)
+        self.assertIn("submissions_24h", metrics)
+        self.assertIn("submissions_7d", metrics)
+
+        self.assertEqual(metrics["total_users"], 1)
+        self.assertEqual(metrics["submissions_24h"], 1)
+        self.assertEqual(metrics["submissions_7d"], 1)
+
 
 class TestUserManagement(Test.TestCase):
     """Test user management functionality."""