zemailnator/app/Filament/Widgets/SubscriptionMetrics.php

<?php

namespace App\Filament\Widgets;

use App\Models\Subscription;
use Filament\Widgets\ChartWidget;
use Illuminate\Support\Facades\DB;

class SubscriptionMetrics extends ChartWidget
{
    protected static ?int $sort = 2;

    protected int|string|array $columnSpan = 'full';

    public function getHeading(): string
    {
        return 'Subscription Metrics';
    }

    protected function getData(): array
    {
        $period = $this->getPeriod();

        $subscriptionsByProvider = $this->getSubscriptionsByProvider($period);
        $subscriptionsByStatus = $this->getSubscriptionsByStatus();
        $monthlyTrend = $this->getMonthlySubscriptionTrend();

        return [
            'datasets' => [
                [
                    'label' => 'Subscriptions by Provider',
                    'data' => array_values($subscriptionsByProvider),
                    'backgroundColor' => [
                        'rgba(59, 130, 246, 0.8)',   // blue
                        'rgba(34, 197, 94, 0.8)',    // green
                        'rgba(168, 85, 247, 0.8)',   // purple
                        'rgba(251, 146, 60, 0.8)',   // orange
                        'rgba(107, 114, 128, 0.8)',  // gray
                        'rgba(236, 72, 153, 0.8)',   // pink
                    ],
                    'borderColor' => [
                        'rgba(59, 130, 246, 1)',
                        'rgba(34, 197, 94, 1)',
                        'rgba(168, 85, 247, 1)',
                        'rgba(251, 146, 60, 1)',
                        'rgba(107, 114, 128, 1)',
                        'rgba(236, 72, 153, 1)',
                    ],
                ],
            ],
            'labels' => array_keys($subscriptionsByProvider),
        ];
    }

    protected function getType(): string
    {
        return 'doughnut';
    }

    protected function getOptions(): array
    {
        return [
            'responsive' => true,
            'plugins' => [
                'legend' => [
                    'position' => 'bottom',
                ],
                'tooltip' => [
                    'callbacks' => [
                        'label' => 'function(context) {
                            const label = context.label || "";
                            const value = context.parsed || 0;
                            const total = context.dataset.data.reduce((a, b) => a + b, 0);
                            const percentage = ((value / total) * 100).toFixed(1);
                            return label + ": " + value + " (" + percentage + "%)";
                        }',
                    ],
                ],
            ],
        ];
    }

    private function getPeriod(): string
    {
        return 'last_30_days'; // Could be made configurable
    }

    private function getSubscriptionsByProvider(string $period): array
    {
        $query = Subscription::query();

        if ($period === 'last_30_days') {
            $query->where('created_at', '>=', now()->subDays(30));
        }

        return $query
            ->select('provider', DB::raw('count(*) as count'))
            ->groupBy('provider')
            ->orderBy('count', 'desc')
            ->pluck('count', 'provider')
            ->toArray();
    }

    private function getSubscriptionsByStatus(): array
    {
        return Subscription::query()
            ->select('status', DB::raw('count(*) as count'))
            ->groupBy('status')
            ->orderBy('count', 'desc')
            ->pluck('count', 'status')
            ->toArray();
    }

    private function getMonthlySubscriptionTrend(): array
    {
        $dateFormat = $this->getDateFormatExpression();

        return Subscription::query()
            ->select(
                DB::raw("{$dateFormat} as month"),
                DB::raw('count(*) as count')
            )
            ->groupBy('month')
            ->orderBy('month')
            ->pluck('count', 'month')
            ->toArray();
    }

    private function getDateFormatExpression(): string
    {
        $connection = DB::connection()->getDriverName();

        return match ($connection) {
            'sqlite' => "strftime('%Y-%m', subscriptions.created_at)",
            'mysql', 'mariadb' => "DATE_FORMAT(subscriptions.created_at, '%Y-%m')",
            'pgsql' => "TO_CHAR(subscriptions.created_at, 'YYYY-MM')",
            'sqlsrv' => "FORMAT(subscriptions.created_at, 'yyyy-MM')",
            default => "DATE_FORMAT(subscriptions.created_at, '%Y-%m')", // fallback to MySQL format
        };
    }
}