<?php

// app/Filament/Resources/JobResource/Widgets/JobStats.php
namespace App\Filament\Resources\JobResource\Widgets;

use Filament\Widgets\StatsOverviewWidget as BaseWidget;
use Filament\Widgets\StatsOverviewWidget\Stat;
use App\Models\Job;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class JobStats extends BaseWidget
{
    protected function getStats(): array
    {
        // Total unpaid cents
        if (Schema::hasColumn('jobs', 'remaining_amount_cents')) {
            $unpaidCents = (int) Job::query()->where('remaining_amount_cents', '>', 0)->sum('remaining_amount_cents');
        } else {
            $expr = '(COALESCE(charge_amount_cents,0) - COALESCE((SELECT COALESCE(SUM(amount_cents),0) FROM payments WHERE payments.job_id = jobs.id AND status IN ("succeeded","captured")),0))';
            $unpaidCents = (int) Job::query()->whereRaw("$expr > 0")->selectRaw("SUM($expr) as agg")->value('agg');
        }

        $jobsUnpaid = Job::query()->when(
            Schema::hasColumn('jobs', 'remaining_amount_cents'),
            fn ($q) => $q->where('remaining_amount_cents', '>', 0),
            fn ($q) => $q->whereRaw('(COALESCE(charge_amount_cents,0) - COALESCE((SELECT COALESCE(SUM(amount_cents),0) FROM payments WHERE payments.job_id = jobs.id AND status IN ("succeeded","captured")),0)) > 0')
        )->count();

        $paidTodayCents = (int) DB::table('payments')
            ->whereIn('status', ['succeeded','captured'])
            ->whereDate('created_at', today())
            ->sum('amount_cents');

        return [
            Stat::make('Total unpaid', 'NZ$ ' . number_format($unpaidCents / 100, 2)),
            Stat::make('Jobs unpaid', number_format($jobsUnpaid)),
            Stat::make('Paid today', 'NZ$ ' . number_format($paidTodayCents / 100, 2)),
        ];
    }
}
