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

<?php

declare(strict_types=1);

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Invoice;
use App\Models\PaymentTransaction;
use App\Models\Service;
use App\Models\User;
use Illuminate\Support\Facades\Cache;
use Inertia\Inertia;
use Inertia\Response;
use Laravel\Cashier\Subscription;

class DashboardController extends Controller
{
    public function index(): Response
    {
        $stats = Cache::remember('admin.dashboard.stats', 300, function () {
            $totalCustomers = User::role('customer')->count();

            $newCustomersThisMonth = User::role('customer')
                ->where('created_at', '>=', now()->startOfMonth())
                ->count();

            // MRR: sum of plan prices normalized to monthly
            $mrr = $this->calculateMrr();

            // Previous month MRR for month-over-month change
            $previousMrr = $this->calculatePreviousMonthMrr();
            $mrrChangePercent = $previousMrr > 0
                ? round((($mrr - $previousMrr) / $previousMrr) * 100, 1)
                : null;

            $arr = $mrr * 12;

            $activeServices = Service::query()
                ->where('status', 'active')
                ->count();

            // Service breakdown by type
            $serviceBreakdown = Service::query()
                ->where('status', 'active')
                ->selectRaw('service_type, COUNT(*) as count')
                ->groupBy('service_type')
                ->pluck('count', 'service_type')
                ->toArray();

            // Transaction revenue and estimated processor fees
            $transactionStats = PaymentTransaction::query()
                ->where('status', 'succeeded')
                ->selectRaw("
                    COALESCE(SUM(amount), 0) as total,
                    COALESCE(SUM(CASE
                        WHEN gateway = 'stripe' THEN (amount * 0.029) + 0.30
                        WHEN gateway = 'paypal' THEN (amount * 0.0349) + 0.49
                        ELSE 0
                    END), 0) as fees
                ")
                ->first();

            $totalTransactionRevenue = (float) $transactionStats->total;
            $estimatedFees = round((float) $transactionStats->fees, 2);
            $netRevenue = round($totalTransactionRevenue - $estimatedFees, 2);

            // Revenue this month
            $revenueThisMonth = (float) Invoice::query()
                ->where('status', 'paid')
                ->where('paid_at', '>=', now()->startOfMonth())
                ->sum('total');

            // Overdue invoices
            $overdueCount = Invoice::query()
                ->where('status', 'overdue')
                ->count();

            $overdueAmount = (float) Invoice::query()
                ->where('status', 'overdue')
                ->sum('total');

            // Churn rate (current month)
            $currentChurnRate = $this->calculateCurrentChurnRate();
            $churnHealthStatus = match (true) {
                $currentChurnRate < 3.0 => 'healthy',
                $currentChurnRate <= 7.0 => 'watch',
                default => 'high',
            };

            // Monthly Revenue Trend (last 12 months)
            $revenueByMonth = PaymentTransaction::query()
                ->where('status', 'succeeded')
                ->where('created_at', '>=', now()->subMonths(12))
                ->selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount) as total, COUNT(*) as transactions, ROUND(AVG(amount), 2) as avg_amount")
                ->groupBy('month')
                ->orderBy('month')
                ->get()
                ->map(fn ($row) => [
                    'month' => $row->month,
                    'total' => (float) $row->total,
                    'transactions' => (int) $row->transactions,
                    'avg_amount' => (float) $row->avg_amount,
                ]);

            return compact(
                'totalCustomers',
                'newCustomersThisMonth',
                'mrr',
                'mrrChangePercent',
                'arr',
                'activeServices',
                'serviceBreakdown',
                'totalTransactionRevenue',
                'estimatedFees',
                'netRevenue',
                'revenueThisMonth',
                'overdueCount',
                'overdueAmount',
                'currentChurnRate',
                'churnHealthStatus',
                'revenueByMonth',
            );
        });

        return Inertia::render('Admin/Dashboard', $stats);
    }

    private function calculateMrr(): float
    {
        return (float) (Subscription::query()
            ->where('subscriptions.stripe_status', 'active')
            ->whereNotNull('subscriptions.plan_id')
            ->join('plan_prices', function ($join): void {
                $join->on('subscriptions.plan_id', '=', 'plan_prices.plan_id')
                    ->on('subscriptions.billing_cycle', '=', 'plan_prices.billing_cycle');
            })
            ->selectRaw('SUM(CASE subscriptions.billing_cycle
                WHEN "monthly" THEN plan_prices.price
                WHEN "quarterly" THEN plan_prices.price / 3
                WHEN "semi_annual" THEN plan_prices.price / 6
                WHEN "annual" THEN plan_prices.price / 12
                ELSE plan_prices.price
            END) as mrr')
            ->value('mrr') ?? 0);
    }

    private function calculatePreviousMonthMrr(): float
    {
        $lastMonthEnd = now()->subMonth()->endOfMonth();

        return (float) (Subscription::query()
            ->where('subscriptions.stripe_status', 'active')
            ->whereNotNull('subscriptions.plan_id')
            ->where('subscriptions.created_at', '<', $lastMonthEnd)
            ->where(function ($query) use ($lastMonthEnd): void {
                $query->whereNull('subscriptions.cancelled_at')
                    ->orWhere('subscriptions.cancelled_at', '>', $lastMonthEnd);
            })
            ->join('plan_prices', function ($join): void {
                $join->on('subscriptions.plan_id', '=', 'plan_prices.plan_id')
                    ->on('subscriptions.billing_cycle', '=', 'plan_prices.billing_cycle');
            })
            ->selectRaw('SUM(CASE subscriptions.billing_cycle
                WHEN "monthly" THEN plan_prices.price
                WHEN "quarterly" THEN plan_prices.price / 3
                WHEN "semi_annual" THEN plan_prices.price / 6
                WHEN "annual" THEN plan_prices.price / 12
                ELSE plan_prices.price
            END) as mrr')
            ->value('mrr') ?? 0);
    }

    private function calculateCurrentChurnRate(): float
    {
        $monthStart = now()->startOfMonth();
        $monthEnd = now()->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();

        return $totalAtStart > 0 ? round(($cancelled / $totalAtStart) * 100, 1) : 0;
    }
}