Enhance admin analytics with ARR, revenue trends, churn, and overdue invoices

Add Annual Recurring Revenue stat, 12-month revenue trend with progress bars,
customer growth chart, 6-month churn rate tracker with status indicators, and
overdue invoices table with days-overdue coloring.

Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>

website/app/Http/Controllers/Admin/DashboardController.php

@@ -6,6 +6,7 @@ namespace App\Http\Controllers\Admin;
 
 use App\Http\Controllers\Controller;
 use App\Models\Invoice;
+use App\Models\PaymentTransaction;
 use App\Models\Plan;
 use App\Models\Service;
 use App\Models\User;
@@ -118,6 +119,61 @@ class DashboardController extends Controller
             ->where('paid_at', '>=', now()->startOfMonth())
             ->sum('total');
 
+        // ARR (Annual Recurring Revenue)
+        $arr = (float) $mrr * 12;
+
+        // Monthly Revenue Trend (last 12 months)
+        $revenueByMonth = PaymentTransaction::query()
+            ->where('status', 'completed')
+            ->where('created_at', '>=', now()->subMonths(12))
+            ->selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount) as total")
+            ->groupBy('month')
+            ->orderBy('month')
+            ->get()
+            ->map(fn ($row) => ['month' => $row->month, 'total' => (float) $row->total]);
+
+        // Customer Growth (last 12 months - new signups per month)
+        $customerGrowth = User::role('customer')
+            ->where('created_at', '>=', now()->subMonths(12))
+            ->selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, COUNT(*) as count")
+            ->groupBy('month')
+            ->orderBy('month')
+            ->get()
+            ->map(fn ($row) => ['month' => $row->month, 'count' => (int) $row->count]);
+
+        // Churn Rate (subscriptions cancelled vs total in last 6 months)
+        $churnData = [];
+        for ($i = 5; $i >= 0; $i--) {
+            $monthStart = now()->subMonths($i)->startOfMonth();
+            $monthEnd = now()->subMonths($i)->endOfMonth();
+
+            $totalAtStart = Subscription::query()
+                ->where('created_at', '<', $monthStart)
+                ->where(function ($query) use ($monthStart): void {
+                    $query->whereNull('cancelled_at')
+                        ->orWhere('cancelled_at', '>', $monthStart);
+                })
+                ->count();
+
+            $cancelled = Subscription::query()
+                ->whereBetween('cancelled_at', [$monthStart, $monthEnd])
+                ->count();
+
+            $churnData[] = [
+                'month' => $monthStart->format('Y-m'),
+                'rate' => $totalAtStart > 0 ? round(($cancelled / $totalAtStart) * 100, 1) : 0,
+                'cancelled' => $cancelled,
+            ];
+        }
+
+        // Overdue Invoices (detailed list)
+        $overdueInvoices = Invoice::query()
+            ->where('status', 'overdue')
+            ->with('user:id,name,email')
+            ->latest('due_date')
+            ->take(10)
+            ->get(['id', 'user_id', 'number', 'total', 'due_date', 'status']);
+
         return Inertia::render('Admin/Dashboard', [
             'totalCustomers' => $totalCustomers,
             'mrr' => (float) $mrr,
@@ -133,6 +189,11 @@ class DashboardController extends Controller
             'revenueByServiceType' => $revenueByServiceType,
             'newCustomersThisMonth' => $newCustomersThisMonth,
             'revenueThisMonth' => (float) $revenueThisMonth,
+            'arr' => $arr,
+            'revenueByMonth' => $revenueByMonth,
+            'customerGrowth' => $customerGrowth,
+            'churnData' => $churnData,
+            'overdueInvoices' => $overdueInvoices,
         ]);
     }
 }

website/resources/ts/Pages/Admin/Dashboard.vue

@@ -1,4 +1,6 @@
 <script lang="ts" setup>
+import { computed } from 'vue'
+import { Link } from '@inertiajs/vue3'
 import AdminLayout from '@/Layouts/AdminLayout.vue'
 import StatCard from '@/Components/StatCard.vue'
 import { resolveInvoiceStatusColor, resolveSubscriptionStatusColor } from '@/utils/resolvers'
@@ -47,6 +49,36 @@ interface RevenueByServiceType {
   invoice_count: number
 }
 
+interface RevenueMonth {
+  month: string
+  total: number
+}
+
+interface CustomerGrowthMonth {
+  month: string
+  count: number
+}
+
+interface ChurnMonth {
+  month: string
+  rate: number
+  cancelled: number
+}
+
+interface OverdueInvoice {
+  id: number
+  user_id: number
+  number: string
+  total: string
+  due_date: string
+  status: string
+  user: {
+    id: number
+    name: string
+    email: string
+  } | null
+}
+
 interface Props {
   totalCustomers: number
   mrr: number
@@ -62,6 +94,11 @@ interface Props {
   revenueByServiceType: RevenueByServiceType[]
   newCustomersThisMonth: number
   revenueThisMonth: number
+  arr: number
+  revenueByMonth: RevenueMonth[]
+  customerGrowth: CustomerGrowthMonth[]
+  churnData: ChurnMonth[]
+  overdueInvoices: OverdueInvoice[]
 }
 
 defineOptions({ layout: AdminLayout })
@@ -78,6 +115,12 @@ function formatDate(dateStr: string): string {
   return date.toLocaleDateString('en-US', { month: 'short', day: '2-digit', year: 'numeric' })
 }
 
+function formatMonthLabel(monthStr: string): string {
+  const [year, month] = monthStr.split('-')
+  const date = new Date(parseInt(year), parseInt(month) - 1)
+  return date.toLocaleDateString('en-US', { month: 'short', year: '2-digit' })
+}
+
 function resolveServiceTypeColor(type: string): string {
   const map: Record<string, string> = {
     vps: 'primary',
@@ -107,6 +150,33 @@ function formatServiceType(type: string): string {
   }
   return map[type] ?? type
 }
+
+function resolveChurnColor(rate: number): string {
+  if (rate < 2) return 'success'
+  if (rate <= 5) return 'warning'
+  return 'error'
+}
+
+function daysOverdue(dueDateStr: string): number {
+  const due = new Date(dueDateStr)
+  const now = new Date()
+  const diff = Math.floor((now.getTime() - due.getTime()) / (1000 * 60 * 60 * 24))
+  return Math.max(0, diff)
+}
+
+const maxRevenue = computed<number>(() => {
+  if (props.revenueByMonth.length === 0) return 1
+  return Math.max(...props.revenueByMonth.map(r => r.total), 1)
+})
+
+const totalCustomerGrowth = computed<number>(() => {
+  return props.customerGrowth.reduce((sum, row) => sum + row.count, 0)
+})
+
+const maxCustomerGrowth = computed<number>(() => {
+  if (props.customerGrowth.length === 0) return 1
+  return Math.max(...props.customerGrowth.map(r => r.count), 1)
+})
 </script>
 
 <template>
@@ -140,12 +210,42 @@ function formatServiceType(type: string): string {
         />
       </VCol>
 
+      <VCol cols="12" sm="6" lg="3">
+        <StatCard
+          title="Annual Recurring Revenue"
+          :stats="formatCurrency(arr)"
+          icon="tabler-calendar-dollar"
+          color="info"
+        />
+      </VCol>
+
       <VCol cols="12" sm="6" lg="3">
         <StatCard
           title="Active Services"
           :stats="activeServices"
           icon="tabler-server"
-          color="info"
+          color="warning"
+        />
+      </VCol>
+    </VRow>
+
+    <!-- Row 1b: Secondary Metrics -->
+    <VRow class="mb-2">
+      <VCol cols="12" sm="6" lg="3">
+        <StatCard
+          title="Total Revenue"
+          :stats="formatCurrency(totalRevenue)"
+          icon="tabler-trending-up"
+          color="success"
+        />
+      </VCol>
+
+      <VCol cols="12" sm="6" lg="3">
+        <StatCard
+          title="Revenue This Month"
+          :stats="formatCurrency(revenueThisMonth)"
+          icon="tabler-report-money"
+          color="primary"
         />
       </VCol>
 
@@ -157,9 +257,254 @@ function formatServiceType(type: string): string {
           color="warning"
         />
       </VCol>
+
+      <VCol cols="12" sm="6" lg="3">
+        <StatCard
+          title="Overdue Accounts"
+          :stats="`${overdueCount} (${formatCurrency(overdueAmount)})`"
+          icon="tabler-alert-circle"
+          :color="overdueCount > 0 ? 'error' : 'success'"
+        />
+      </VCol>
     </VRow>
 
-    <!-- Row 2: Recent Subscriptions & Recent Invoices -->
+    <!-- Row 2: Revenue Trend (12 Months) -->
+    <VRow class="mb-2">
+      <VCol cols="12">
+        <VCard>
+          <VCardTitle class="d-flex align-center justify-space-between">
+            <div class="d-flex align-center gap-2">
+              <VIcon icon="tabler-chart-bar" size="22" />
+              <span>Revenue Trend (12 Months)</span>
+            </div>
+            <VChip size="small" color="success" variant="tonal">
+              Payment Transactions
+            </VChip>
+          </VCardTitle>
+
+          <VCardText v-if="revenueByMonth.length === 0" class="text-center py-8">
+            <VIcon icon="tabler-chart-bar-off" size="48" color="disabled" class="mb-2" />
+            <div class="text-medium-emphasis">No revenue data available for the last 12 months.</div>
+          </VCardText>
+
+          <VTable v-else density="comfortable" hover>
+            <thead>
+              <tr>
+                <th style="width: 120px;">Month</th>
+                <th style="width: 140px;" class="text-end">Revenue</th>
+                <th>Trend</th>
+              </tr>
+            </thead>
+            <tbody>
+              <tr v-for="row in revenueByMonth" :key="row.month">
+                <td class="text-body-2 font-weight-medium">
+                  {{ formatMonthLabel(row.month) }}
+                </td>
+                <td class="text-end text-body-2 font-weight-semibold">
+                  {{ formatCurrency(row.total) }}
+                </td>
+                <td>
+                  <VProgressLinear
+                    :model-value="(row.total / maxRevenue) * 100"
+                    color="success"
+                    height="20"
+                    rounded
+                    class="my-1"
+                  >
+                    <template #default>
+                      <span class="text-caption font-weight-medium">
+                        {{ Math.round((row.total / maxRevenue) * 100) }}%
+                      </span>
+                    </template>
+                  </VProgressLinear>
+                </td>
+              </tr>
+            </tbody>
+          </VTable>
+        </VCard>
+      </VCol>
+    </VRow>
+
+    <!-- Row 3: Customer Growth + Churn Rate side by side -->
+    <VRow class="mb-2">
+      <!-- Customer Growth -->
+      <VCol cols="12" lg="6">
+        <VCard>
+          <VCardTitle class="d-flex align-center justify-space-between">
+            <div class="d-flex align-center gap-2">
+              <VIcon icon="tabler-user-plus" size="22" />
+              <span>Customer Growth (12 Months)</span>
+            </div>
+            <VChip size="small" color="primary" variant="tonal">
+              {{ totalCustomerGrowth }} total
+            </VChip>
+          </VCardTitle>
+
+          <VCardText v-if="customerGrowth.length === 0" class="text-center py-8">
+            <VIcon icon="tabler-users-minus" size="48" color="disabled" class="mb-2" />
+            <div class="text-medium-emphasis">No customer signups in the last 12 months.</div>
+          </VCardText>
+
+          <VTable v-else density="comfortable" hover>
+            <thead>
+              <tr>
+                <th style="width: 120px;">Month</th>
+                <th style="width: 80px;" class="text-end">Signups</th>
+                <th>Growth</th>
+              </tr>
+            </thead>
+            <tbody>
+              <tr v-for="row in customerGrowth" :key="row.month">
+                <td class="text-body-2 font-weight-medium">
+                  {{ formatMonthLabel(row.month) }}
+                </td>
+                <td class="text-end text-body-2 font-weight-semibold">
+                  {{ row.count }}
+                </td>
+                <td>
+                  <VProgressLinear
+                    :model-value="(row.count / maxCustomerGrowth) * 100"
+                    color="primary"
+                    height="18"
+                    rounded
+                    class="my-1"
+                  />
+                </td>
+              </tr>
+            </tbody>
+          </VTable>
+        </VCard>
+      </VCol>
+
+      <!-- Churn Rate -->
+      <VCol cols="12" lg="6">
+        <VCard>
+          <VCardTitle class="d-flex align-center justify-space-between">
+            <div class="d-flex align-center gap-2">
+              <VIcon icon="tabler-user-minus" size="22" />
+              <span>Churn Rate (6 Months)</span>
+            </div>
+            <VChip size="small" color="warning" variant="tonal">
+              Monthly
+            </VChip>
+          </VCardTitle>
+
+          <VTable density="comfortable" hover>
+            <thead>
+              <tr>
+                <th>Month</th>
+                <th class="text-end">Cancelled</th>
+                <th class="text-end">Churn Rate</th>
+                <th>Status</th>
+              </tr>
+            </thead>
+            <tbody>
+              <tr v-for="row in churnData" :key="row.month">
+                <td class="text-body-2 font-weight-medium">
+                  {{ formatMonthLabel(row.month) }}
+                </td>
+                <td class="text-end text-body-2">
+                  {{ row.cancelled }}
+                </td>
+                <td class="text-end text-body-2 font-weight-semibold">
+                  {{ row.rate }}%
+                </td>
+                <td>
+                  <VChip
+                    :color="resolveChurnColor(row.rate)"
+                    size="small"
+                    variant="tonal"
+                  >
+                    <VIcon
+                      :icon="row.rate < 2 ? 'tabler-circle-check' : row.rate <= 5 ? 'tabler-alert-triangle' : 'tabler-alert-circle'"
+                      size="14"
+                      class="me-1"
+                    />
+                    {{ row.rate < 2 ? 'Healthy' : row.rate <= 5 ? 'Watch' : 'High' }}
+                  </VChip>
+                </td>
+              </tr>
+            </tbody>
+          </VTable>
+        </VCard>
+      </VCol>
+    </VRow>
+
+    <!-- Row 4: Overdue Invoices -->
+    <VRow class="mb-2">
+      <VCol cols="12">
+        <VCard>
+          <VCardTitle class="d-flex align-center justify-space-between">
+            <div class="d-flex align-center gap-2">
+              <VIcon icon="tabler-alert-circle" size="22" color="error" />
+              <span>Overdue Invoices</span>
+            </div>
+            <VChip v-if="overdueInvoices.length > 0" size="small" color="error" variant="tonal">
+              {{ overdueInvoices.length }} overdue
+            </VChip>
+          </VCardTitle>
+
+          <VCardText v-if="overdueInvoices.length === 0" class="text-center py-8">
+            <VAvatar color="success" variant="tonal" size="56" class="mb-3">
+              <VIcon icon="tabler-circle-check" size="28" />
+            </VAvatar>
+            <div class="text-body-1 font-weight-medium">No overdue invoices</div>
+            <div class="text-body-2 text-medium-emphasis">All invoices are current.</div>
+          </VCardText>
+
+          <VTable v-else density="comfortable" hover>
+            <thead>
+              <tr>
+                <th>Customer</th>
+                <th>Invoice #</th>
+                <th class="text-end">Amount</th>
+                <th>Due Date</th>
+                <th class="text-end">Days Overdue</th>
+              </tr>
+            </thead>
+            <tbody>
+              <tr v-for="invoice in overdueInvoices" :key="invoice.id">
+                <td>
+                  <Link
+                    v-if="invoice.user"
+                    :href="`/customers/${invoice.user.id}`"
+                    class="text-body-2 font-weight-medium text-primary text-decoration-none"
+                  >
+                    {{ invoice.user.name }}
+                  </Link>
+                  <span v-else class="text-body-2 text-medium-emphasis">Unknown</span>
+                </td>
+                <td>
+                  <Link
+                    :href="`/invoices/${invoice.id}`"
+                    class="text-body-2 font-weight-medium text-primary text-decoration-none"
+                  >
+                    {{ invoice.number }}
+                  </Link>
+                </td>
+                <td class="text-end text-body-2 font-weight-semibold text-error">
+                  {{ formatCurrency(invoice.total) }}
+                </td>
+                <td class="text-body-2">
+                  {{ formatDate(invoice.due_date) }}
+                </td>
+                <td class="text-end">
+                  <VChip
+                    :color="daysOverdue(invoice.due_date) > 30 ? 'error' : daysOverdue(invoice.due_date) > 14 ? 'warning' : 'info'"
+                    size="small"
+                    variant="tonal"
+                  >
+                    {{ daysOverdue(invoice.due_date) }} days
+                  </VChip>
+                </td>
+              </tr>
+            </tbody>
+          </VTable>
+        </VCard>
+      </VCol>
+    </VRow>
+
+    <!-- Row 5: Recent Subscriptions & Recent Invoices -->
     <VRow class="mb-2">
       <!-- Recent Subscriptions -->
       <VCol cols="12" lg="6">
@@ -287,7 +632,7 @@ function formatServiceType(type: string): string {
       </VCol>
     </VRow>
 
-    <!-- Row 3: Popular Plans & Quick Stats -->
+    <!-- Row 6: Popular Plans & Revenue by Type -->
     <VRow>
       <!-- Popular Plans -->
       <VCol cols="12" lg="6">
@@ -340,68 +685,8 @@ function formatServiceType(type: string): string {
         </VCard>
       </VCol>
 
-      <!-- Quick Stats & Revenue by Type -->
+      <!-- Revenue by Service Type -->
       <VCol cols="12" lg="6">
-        <!-- Quick Stats -->
-        <VCard class="mb-4">
-          <VCardTitle class="d-flex align-center gap-2">
-            <VIcon icon="tabler-chart-bar" size="22" />
-            <span>Quick Stats</span>
-          </VCardTitle>
-
-          <VCardText>
-            <VRow>
-              <VCol cols="6">
-                <div class="d-flex align-center gap-3 mb-4">
-                  <VAvatar color="success" variant="tonal" rounded size="40">
-                    <VIcon icon="tabler-trending-up" size="22" />
-                  </VAvatar>
-                  <div>
-                    <div class="text-caption text-medium-emphasis">Total Revenue</div>
-                    <div class="text-body-1 font-weight-semibold">{{ formatCurrency(totalRevenue) }}</div>
-                  </div>
-                </div>
-              </VCol>
-              <VCol cols="6">
-                <div class="d-flex align-center gap-3 mb-4">
-                  <VAvatar color="primary" variant="tonal" rounded size="40">
-                    <VIcon icon="tabler-user-plus" size="22" />
-                  </VAvatar>
-                  <div>
-                    <div class="text-caption text-medium-emphasis">New This Month</div>
-                    <div class="text-body-1 font-weight-semibold">{{ newCustomersThisMonth }} customers</div>
-                  </div>
-                </div>
-              </VCol>
-              <VCol cols="6">
-                <div class="d-flex align-center gap-3">
-                  <VAvatar color="info" variant="tonal" rounded size="40">
-                    <VIcon icon="tabler-report-money" size="22" />
-                  </VAvatar>
-                  <div>
-                    <div class="text-caption text-medium-emphasis">Revenue This Month</div>
-                    <div class="text-body-1 font-weight-semibold">{{ formatCurrency(revenueThisMonth) }}</div>
-                  </div>
-                </div>
-              </VCol>
-              <VCol cols="6">
-                <div class="d-flex align-center gap-3">
-                  <VAvatar :color="overdueCount > 0 ? 'error' : 'success'" variant="tonal" rounded size="40">
-                    <VIcon :icon="overdueCount > 0 ? 'tabler-alert-circle' : 'tabler-circle-check'" size="22" />
-                  </VAvatar>
-                  <div>
-                    <div class="text-caption text-medium-emphasis">Overdue Accounts</div>
-                    <div class="text-body-1 font-weight-semibold" :class="overdueCount > 0 ? 'text-error' : ''">
-                      {{ overdueCount }} ({{ formatCurrency(overdueAmount) }})
-                    </div>
-                  </div>
-                </div>
-              </VCol>
-            </VRow>
-          </VCardText>
-        </VCard>
-
-        <!-- Revenue by Service Type -->
         <VCard>
           <VCardTitle class="d-flex align-center gap-2">
             <VIcon icon="tabler-report-analytics" size="22" />