<?php

namespace App\Filament\Widgets;

use App\Filament\Resources\ProjectCharterResource;
use App\Models\ExchangeRequest;
use App\Models\ProjectCharter;
use BezhanSalleh\FilamentShield\Traits\HasWidgetShield;
use Filament\Tables\Columns\TextColumn;
use Filament\Tables\Table;
use Filament\Widgets\Concerns\InteractsWithPageFilters;
use Filament\Widgets\TableWidget as BaseWidget;

class ProjectFinancialStatusTable extends BaseWidget
{
    use HasWidgetShield;
    use InteractsWithPageFilters;

    protected static ?string $heading = null;

    protected static ?int $sort = 8;

    protected static bool $isLazy = false;

    protected static ?string $pollingInterval = null;

    public function getHeading(): string
    {
        return __('pages.projects_dashboard').' - '.__('widgets.project_financial_status');
    }

    protected function getTableHeading(): string
    {
        return __('pages.projects_dashboard').' - '.__('widgets.project_financial_status');
    }

    public function table(Table $table): Table
    {
        return $table
            ->query($this->getQuery())
            ->columns([
                TextColumn::make('name')
                    ->label(__('project_charter.name'))
                    ->searchable()
                    ->sortable()
                    ->url(fn (ProjectCharter $record): string => ProjectCharterResource::getUrl('view', ['record' => $record]))
                    ->color('primary')
                    ->weight('medium'),
                TextColumn::make('estimated_cost')
                    ->label(__('widgets.estimated_cost'))
                    ->formatStateUsing(fn ($state) => $state === null ? '-' : number_format($state, 2))
                    ->sortable(),
                TextColumn::make('total_cost')
                    ->label(__('widgets.total_cost'))
                    ->formatStateUsing(fn ($state) => $state === null ? '-' : number_format($state, 2))
                    ->sortable(),
                TextColumn::make('spent_amount')
                    ->label(__('widgets.spent'))
                    ->getStateUsing(fn ($record) => $record->total_approved_exchange_requests)
                    ->formatStateUsing(fn ($state) => number_format($state, 2))
                    ->sortable(),
                TextColumn::make('remaining_amount')
                    ->label(__('widgets.remaining'))
                    ->getStateUsing(function ($record) {
                        $estimated = $record->estimated_cost ?? 0;
                        $spent = $record->total_approved_exchange_requests ?? 0;
                        $remaining = $estimated - $spent;

                        return $remaining < 0 ? 0 : $remaining;
                    })
                    ->formatStateUsing(fn ($state) => number_format($state, 2)),
                TextColumn::make('approved_exchange_requests_count')
                    ->label(__('widgets.approved_exchange_requests'))
                    ->getStateUsing(fn ($record) => $this->countExchangeRequests($record, true))
                    ->badge()
                    ->color('success'),
                TextColumn::make('pending_exchange_requests_count')
                    ->label(__('widgets.pending_exchange_requests'))
                    ->getStateUsing(fn ($record) => $this->countExchangeRequests($record, false))
                    ->badge()
                    ->color('warning'),
            ])
            ->defaultSort('estimated_cost', 'desc')
            ->paginated([10, 25, 50])
            ->defaultPaginationPageOption(10)
            ->striped();
    }

    protected function getQuery()
    {
        $query = ProjectCharter::query()
            ->with([
                'deliverables.completionReports.exchangeRequest',
                'initiativeProject.initiative.performanceWallet.performanceCard.department',
            ]);

        // Apply year filter
        if ($selectedYear = $this->filters['year'] ?? null) {
            $query->where(function ($subQuery) use ($selectedYear) {
                $subQuery->whereYear('start_date', $selectedYear)
                    ->orWhereYear('expected_end_date', $selectedYear);
            });
        }

        if ($departmentId = $this->filters['department_id'] ?? null) {
            $query->whereHas('initiativeProject.initiative.performanceWallet.performanceCard', function ($q) use ($departmentId) {
                $q->where('department_id', $departmentId);
            });
        }
        if ($walletId = $this->filters['performance_wallet_id'] ?? null) {
            $query->whereHas('initiativeProject.initiative', function ($q) use ($walletId) {
                $q->where('performance_wallet_id', $walletId);
            });
        }
        if ($initiativeId = $this->filters['initiative_id'] ?? null) {
            $query->whereHas('initiativeProject', function ($q) use ($initiativeId) {
                $q->where('initiative_id', $initiativeId);
            });
        }

        return $query;
    }

    protected function countExchangeRequests(ProjectCharter $charter, bool $approved): int
    {
        $all = $charter->deliverables
            ->flatMap(fn ($d) => $d->completionReports)
            ->filter(fn ($cr) => $cr->exchangeRequest !== null)
            ->map(fn ($cr) => $cr->exchangeRequest);

        if ($approved) {
            return $all->where('status', ExchangeRequest::STATUS_APPROVED)->count();
        }

        return $all->filter(fn ($er) => ! in_array($er->status, [ExchangeRequest::STATUS_APPROVED, ExchangeRequest::STATUS_REJECTED]))->count();
    }
}
