<?php

namespace App\Filament\Widgets;

use App\Models\Job;
use App\Models\Payment;
use Filament\Tables;
use Filament\Tables\Table;
use Filament\Widgets\TableWidget as BaseWidget;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Carbon;

class UnpaidJobsTable extends BaseWidget
{
    protected static ?string $heading = 'Unpaid Jobs';
    protected int|string|array $columnSpan = 'full';

    public function table(Table $table): Table
    {
        return $table
            ->query($this->getQuery())
            ->defaultSort('start_at', 'asc')
            ->columns([
                // 🔴 Red flag if job starts within 4 days
                Tables\Columns\IconColumn::make('urgent')
                    ->label('')
                    ->icon('heroicon-m-flag')
                    ->color('danger')
                    ->tooltip('Starts within 4 days')
                    ->state(function (Job $record) {
                        if (empty($record->start_at)) return false;
                        $start = $record->start_at instanceof \DateTimeInterface
                            ? $record->start_at
                            : Carbon::parse($record->start_at);
                        return now()->lte($start) && $start->lte(now()->addDays(4));
                    }),

                // ✅ External Reference (clickable to job page)
                Tables\Columns\TextColumn::make('external_reference')
                    ->label('External Ref')
                    ->url(fn (Job $r) => url("/admin/jobs/{$r->id}"))
                    ->openUrlInNewTab()
                    ->sortable()
                    ->searchable(),

                // Pickup time
                Tables\Columns\TextColumn::make('start_at')
                    ->label('Pickup')
                    ->dateTime('D d M, H:i')
                    ->sortable(),

                // ✅ Outstanding Amount
                Tables\Columns\TextColumn::make('due_cents')
                    ->label('Amount Due')
                    ->formatStateUsing(fn ($state) => 'NZD ' . number_format(((int) $state) / 100, 2))
                    ->color('danger')
                    ->weight('bold')
                    ->sortable(),
            ])
            ->emptyStateHeading('No unpaid jobs 🎉')
            ->emptyStateDescription('All balances are settled.');
    }

    /**
     * due_cents = charge_amount_cents - paid_cents
     * paid_cents = SUM(payments.amount_cents) where status in (succeeded, paid, captured)
     */
    protected function getQuery(): Builder
    {
        $paidSub = Payment::query()
            ->selectRaw('job_id, COALESCE(SUM(amount_cents), 0) AS paid_cents')
            ->whereIn('status', ['succeeded', 'paid', 'captured'])
            ->groupBy('job_id');

        return Job::query()
            ->leftJoinSub($paidSub, 'p', 'p.job_id', '=', 'jobs.id')
            ->select([
                'jobs.id',
                'jobs.external_reference',
                'jobs.start_at',
                'jobs.charge_amount_cents',
            ])
            ->selectRaw('GREATEST(COALESCE(jobs.charge_amount_cents,0) - COALESCE(p.paid_cents,0), 0) AS due_cents')
            ->havingRaw('due_cents > 0')
            ->orderBy('jobs.start_at', 'asc');
    }
}
