task: sync database

4 files changed, 247 insertions, 103 deletions

diff --git a/Biz/PodcastItLater/Admin.py b/Biz/PodcastItLater/Admin.py
index 10a8e58..f1dc3ab 100644
--- a/Biz/PodcastItLater/Admin.py
+++ b/Biz/PodcastItLater/Admin.py
@@ -157,6 +157,59 @@ class MetricsDashboard(Component[AnyChildren, MetricsAttrs]):
         return UI.PageLayout(
             html.div(
                 html.h2(
+                    html.i(classes=["bi", "bi-people", "me-2"]),
+                    "Growth & Usage",
+                    classes=["mb-4"],
+                ),
+                # Growth & Usage cards
+                html.div(
+                    html.div(
+                        html.div(
+                            MetricCard(
+                                title="Total Users",
+                                value=metrics.get("total_users", 0),
+                                icon="bi-people",
+                            ),
+                            classes=["card", "shadow-sm"],
+                        ),
+                        classes=["col-md-3"],
+                    ),
+                    html.div(
+                        html.div(
+                            MetricCard(
+                                title="Active Subs",
+                                value=metrics.get("active_subscriptions", 0),
+                                icon="bi-credit-card",
+                            ),
+                            classes=["card", "shadow-sm"],
+                        ),
+                        classes=["col-md-3"],
+                    ),
+                    html.div(
+                        html.div(
+                            MetricCard(
+                                title="Submissions (24h)",
+                                value=metrics.get("submissions_24h", 0),
+                                icon="bi-activity",
+                            ),
+                            classes=["card", "shadow-sm"],
+                        ),
+                        classes=["col-md-3"],
+                    ),
+                    html.div(
+                        html.div(
+                            MetricCard(
+                                title="Submissions (7d)",
+                                value=metrics.get("submissions_7d", 0),
+                                icon="bi-calendar-week",
+                            ),
+                            classes=["card", "shadow-sm"],
+                        ),
+                        classes=["col-md-3"],
+                    ),
+                    classes=["row", "g-3", "mb-5"],
+                ),
+                html.h2(
                     html.i(classes=["bi", "bi-graph-up", "me-2"]),
                     "Episode Metrics",
                     classes=["mb-4"],
diff --git a/Biz/PodcastItLater/Core.py b/Biz/PodcastItLater/Core.py
index 8d31956..4c09a23 100644
--- a/Biz/PodcastItLater/Core.py
+++ b/Biz/PodcastItLater/Core.py
@@ -1100,6 +1100,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()
@@ -1169,6 +1173,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,
@@ -1177,6 +1204,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
@@ -1477,6 +1508,37 @@ 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."""
diff --git a/Biz/PodcastItLater/UI.py b/Biz/PodcastItLater/UI.py
index 27f5fff..a83ad32 100644
--- a/Biz/PodcastItLater/UI.py
+++ b/Biz/PodcastItLater/UI.py
@@ -422,130 +422,128 @@ class PricingPage(Component[AnyChildren, PricingPageAttrs]):
         current_tier = user.get("plan_tier", "free") if user else "free"
 
         return PageLayout(
-            user=user,
-            current_page="pricing",
-            page_title="Pricing - PodcastItLater",
-            error=None,
-            meta_tags=[],
-            children=[
+            html.div(
+                html.h2("Simple Pricing", classes=["text-center", "mb-5"]),
                 html.div(
-                    html.h2("Simple Pricing", classes=["text-center", "mb-5"]),
+                    # Free Tier
+                    html.div(
+                        html.div(
+                            html.div(
+                                html.h3("Free", classes=["card-title"]),
+                                html.h4(
+                                    "$0",
+                                    classes=[
+                                        "card-subtitle",
+                                        "mb-3",
+                                        "text-muted",
+                                    ],
+                                ),
+                                html.p(
+                                    "10 articles total",
+                                    classes=["card-text"],
+                                ),
+                                html.ul(
+                                    html.li("Convert 10 articles"),
+                                    html.li("Basic features"),
+                                    classes=["list-unstyled", "mb-4"],
+                                ),
+                                html.button(
+                                    "Current Plan",
+                                    classes=[
+                                        "btn",
+                                        "btn-outline-primary",
+                                        "w-100",
+                                    ],
+                                    disabled=True,
+                                )
+                                if current_tier == "free"
+                                else html.div(),
+                                classes=["card-body"],
+                            ),
+                            classes=["card", "mb-4", "shadow-sm", "h-100"],
+                        ),
+                        classes=["col-md-6"],
+                    ),
+                    # Paid Tier
                     html.div(
-                        # Free Tier
                         html.div(
                             html.div(
-                                html.div(
-                                    html.h3("Free", classes=["card-title"]),
-                                    html.h4(
-                                        "$0",
+                                html.h3(
+                                    "Unlimited",
+                                    classes=["card-title"],
+                                ),
+                                html.h4(
+                                    "$12/mo",
+                                    classes=[
+                                        "card-subtitle",
+                                        "mb-3",
+                                        "text-muted",
+                                    ],
+                                ),
+                                html.p(
+                                    "Unlimited articles",
+                                    classes=["card-text"],
+                                ),
+                                html.ul(
+                                    html.li("Unlimited conversions"),
+                                    html.li("Priority processing"),
+                                    html.li("Support independent software"),
+                                    classes=["list-unstyled", "mb-4"],
+                                ),
+                                html.form(
+                                    html.button(
+                                        "Upgrade Now",
+                                        type="submit",
                                         classes=[
-                                            "card-subtitle",
-                                            "mb-3",
-                                            "text-muted",
+                                            "btn",
+                                            "btn-primary",
+                                            "w-100",
                                         ],
                                     ),
-                                    html.p(
-                                        "10 articles total",
-                                        classes=["card-text"],
-                                    ),
-                                    html.ul(
-                                        html.li("Convert 10 articles"),
-                                        html.li("Basic features"),
-                                        classes=["list-unstyled", "mb-4"],
-                                    ),
+                                    action="/upgrade",
+                                    method="post",
+                                )
+                                if user and current_tier == "free"
+                                else (
                                     html.button(
                                         "Current Plan",
                                         classes=[
                                             "btn",
-                                            "btn-outline-primary",
+                                            "btn-success",
                                             "w-100",
                                         ],
                                         disabled=True,
                                     )
-                                    if current_tier == "free"
-                                    else html.div(),
-                                    classes=["card-body"],
-                                ),
-                                classes=["card", "mb-4", "shadow-sm", "h-100"],
-                            ),
-                            classes=["col-md-6"],
-                        ),
-                        # Paid Tier
-                        html.div(
-                            html.div(
-                                html.div(
-                                    html.h3(
-                                        "Unlimited",
-                                        classes=["card-title"],
-                                    ),
-                                    html.h4(
-                                        "$12/mo",
+                                    if current_tier == "paid"
+                                    else html.a(
+                                        "Login to Upgrade",
+                                        href="/",
                                         classes=[
-                                            "card-subtitle",
-                                            "mb-3",
-                                            "text-muted",
+                                            "btn",
+                                            "btn-primary",
+                                            "w-100",
                                         ],
-                                    ),
-                                    html.p(
-                                        "Unlimited articles",
-                                        classes=["card-text"],
-                                    ),
-                                    html.ul(
-                                        html.li("Unlimited conversions"),
-                                        html.li("Priority processing"),
-                                        html.li("Support independent software"),
-                                        classes=["list-unstyled", "mb-4"],
-                                    ),
-                                    html.form(
-                                        html.button(
-                                            "Upgrade Now",
-                                            type="submit",
-                                            classes=[
-                                                "btn",
-                                                "btn-primary",
-                                                "w-100",
-                                            ],
-                                        ),
-                                        action="/upgrade",
-                                        method="POST",
                                     )
-                                    if user and current_tier == "free"
-                                    else (
-                                        html.button(
-                                            "Current Plan",
-                                            classes=[
-                                                "btn",
-                                                "btn-success",
-                                                "w-100",
-                                            ],
-                                            disabled=True,
-                                        )
-                                        if user and current_tier == "paid"
-                                        else html.a(
-                                            "Login to Upgrade",
-                                            href="/",
-                                            classes=[
-                                                "btn",
-                                                "btn-primary",
-                                                "w-100",
-                                            ],
-                                        )
-                                    ),
-                                    classes=["card-body"],
                                 ),
-                                classes=[
-                                    "card",
-                                    "mb-4",
-                                    "shadow-sm",
-                                    "border-primary",
-                                    "h-100",
-                                ],
+                                classes=["card-body"],
                             ),
-                            classes=["col-md-6"],
+                            classes=[
+                                "card",
+                                "mb-4",
+                                "shadow-sm",
+                                "border-primary",
+                                "h-100",
+                            ],
                         ),
-                        classes=["row"],
+                        classes=["col-md-6"],
                     ),
-                    classes=["container", "py-3"],
+                    classes=["row"],
                 ),
-            ],
+                classes=["container", "py-3"],
+            ),
+            user=user,
+            current_page="pricing",
+            page_title="Pricing - PodcastItLater",
+            error=None,
+            meta_tags=[],
         )
diff --git a/Biz/PodcastItLater/Web.py b/Biz/PodcastItLater/Web.py
index 7e8e969..368db98 100644
--- a/Biz/PodcastItLater/Web.py
+++ b/Biz/PodcastItLater/Web.py
@@ -2398,6 +2398,37 @@ class TestMetricsDashboard(BaseWebTest):
         self.assertIn("Episode Metrics", response.text)
         self.assertIn("Total Episodes", response.text)
         self.assertIn("Total Plays", response.text)
+
+    def test_growth_metrics_display(self) -> None:
+        """Verify growth and usage metrics are displayed."""
+        # Create an active subscriber
+        user2_id, _ = Core.Database.create_user("active@example.com")
+        Core.Database.update_user_subscription(
+            user2_id,
+            subscription_id="sub_test",
+            status="active",
+            period_start=datetime.now(timezone.utc),
+            period_end=datetime.now(timezone.utc),
+            tier="paid",
+            cancel_at_period_end=False,
+        )
+        
+        # Create a queue item
+        Core.Database.add_to_queue(
+            "https://example.com/new",
+            "active@example.com",
+            user2_id,
+        )
+
+        # Get metrics page
+        response = self.client.get("/admin/metrics")
+
+        self.assertEqual(response.status_code, 200)
+        self.assertIn("Growth & Usage", response.text)
+        self.assertIn("Total Users", response.text)
+        self.assertIn("Active Subs", response.text)
+        self.assertIn("Submissions (24h)", response.text)
+
         self.assertIn("Total Downloads", response.text)
         self.assertIn("Total Adds", response.text)