<?php

namespace App\Filament\Widgets;

use App\Models\Order;
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;
use Illuminate\Database\Eloquent\Builder;

class OrderProductStatsTable extends BaseWidget
{
    use HasWidgetShield, InteractsWithPageFilters;

    public static ?int $sort = 4;

    public function getHeading(): string
    {
        return __('pages.orders_dashboard').' - '.'Product Statistics';
    }

    public function getTableHeading(): string
    {
        return __('pages.orders_dashboard').' - '.__('widgets.order_product_statistics');
    }

    public function getTableRecordKey($record): string
    {
        // Use the type field as the unique key since we're selecting DISTINCT type
        return $record->type;
    }

    protected function getTableQuery(): Builder
    {
        // Create a custom query that returns unique product types
        // We'll use a base query and then transform the data
        return Order::query()
            ->selectRaw('DISTINCT type')
            ->filterByTypePermissions()
            ->whereIn('status', [
                Order::STATUS_PROJECT_MANAGER_APPROVED,
                Order::STATUS_CLASSIFICATION,
                Order::STATUS_AGREEMENT,
                Order::STATUS_CEO_APPROVED,
                Order::STATUS_LAUNCHED,
                Order::STATUS_ACCEPTED,
            ])
            ->whereHas('finance', function (Builder $financeQuery) {
                $financeQuery->whereNotNull('count')
                    ->whereNotNull('capacity');
            });
    }

    public function table(Table $table): Table
    {
        return $table
            ->query($this->getTableQuery())
            ->columns([
                TextColumn::make('type')
                    ->label(__('order.order_type'))
                    ->formatStateUsing(function ($state): string {
                        $orderTypes = Order::getOrderTypes();

                        return $orderTypes[$state] ?? $state;
                    })
                    ->sortable(),

                TextColumn::make('orders_count')
                    ->label(__('widgets.number_of_orders'))
                    ->getStateUsing(function ($record): int {
                        return $this->getOrdersCountForType($record->type);
                    })
                    ->numeric()
                    ->badge()
                    ->color('primary'),

                TextColumn::make('total_count')
                    ->label(__('widgets.total_product_count'))
                    ->getStateUsing(function ($record): int {
                        return $this->getTotalCountForType($record->type);
                    })
                    ->numeric()
                    ->badge()
                    ->color('success'),

                TextColumn::make('total_capacity')
                    ->label(__('widgets.total_product_capacity'))
                    ->getStateUsing(function ($record): string {
                        return $this->getTotalCapacityForType($record->type);
                    })
                    ->badge()
                    ->color('info'),
            ])
            ->defaultSort('type')
            ->heading(__('widgets.order_product_statistics'))
            ->description(__('widgets.order_product_statistics_description'))
            ->paginated(false);
    }

    private function getOrdersCountForType(string $type): int
    {
        $selectedYear = $this->filters['year'] ?? null;
        $query = Order::where('type', $type)
            ->filterByTypePermissions()
            ->whereIn('status', [
                Order::STATUS_PROJECT_MANAGER_APPROVED,
                Order::STATUS_CLASSIFICATION,
                Order::STATUS_AGREEMENT,
                Order::STATUS_CEO_APPROVED,
                Order::STATUS_LAUNCHED,
                Order::STATUS_ACCEPTED,
            ])
            ->whereHas('finance');

        if ($selectedYear) {
            $query->whereYear('created_at', $selectedYear);
        }

        return $query->count();
    }

    private function getTotalCountForType(string $type): int
    {
        $selectedYear = $this->filters['year'] ?? null;
        $query = Order::where('type', $type)
            ->filterByTypePermissions()
            ->whereIn('status', [
                Order::STATUS_PROJECT_MANAGER_APPROVED,
                Order::STATUS_CLASSIFICATION,
                Order::STATUS_AGREEMENT,
                Order::STATUS_CEO_APPROVED,
                Order::STATUS_LAUNCHED,
                Order::STATUS_ACCEPTED,
            ])
            ->whereHas('finance', function (Builder $financeQuery) {
                $financeQuery->whereNotNull('count');
            });

        if ($selectedYear) {
            $query->whereYear('created_at', $selectedYear);
        }

        return $query->with('finance')->get()->sum(function ($order) {
            return $order->finance->count ?? 0;
        });
    }

    private function getTotalCapacityForType(string $type): string
    {
        $selectedYear = $this->filters['year'] ?? null;
        $query = Order::where('type', $type)
            ->filterByTypePermissions()
            ->whereIn('status', [
                Order::STATUS_PROJECT_MANAGER_APPROVED,
                Order::STATUS_CLASSIFICATION,
                Order::STATUS_AGREEMENT,
                Order::STATUS_CEO_APPROVED,
                Order::STATUS_LAUNCHED,
                Order::STATUS_ACCEPTED,
            ])
            ->whereHas('finance', function (Builder $financeQuery) {
                $financeQuery->whereNotNull('capacity');
            });

        if ($selectedYear) {
            $query->whereYear('created_at', $selectedYear);
        }

        $orders = $query->with('finance')->get();

        // Sum up all capacities (extract numeric values)
        $totalCapacity = $orders->sum(function ($order) {
            $capacity = $order->finance->capacity ?? '';
            // Extract numeric value from capacity string
            preg_match('/[\d.]+/', $capacity, $matches);

            return ! empty($matches) ? (float) $matches[0] : 0;
        });

        // Get appropriate unit based on product type
        $unit = $this->getCapacityUnit($type);

        return number_format($totalCapacity, 2).' '.$unit;
    }

    private function getCapacityUnit(string $type): string
    {
        return match ($type) {
            Order::TYPE_WELL_DRILLING => __('widgets.meters'),
            Order::TYPE_HOME_FILTERS => __('widgets.liters_per_day'),
            Order::TYPE_WATER_BOTTLES, Order::TYPE_COOLERS => __('widgets.liters'),
            default => __('widgets.tons'),
        };
    }
}
