<?php

namespace App\Filament\Widgets;

use App\Models\Booking;
use App\Models\User;
use Filament\Tables;
use Filament\Tables\Concerns\InteractsWithTable;
use Filament\Tables\Contracts\HasTable;
use Filament\Tables\Table;
use Filament\Widgets\Concerns\InteractsWithPageFilters;
use Filament\Widgets\TableWidget as BaseWidget;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

class TopUsersByBookingsTable extends BaseWidget implements HasTable
{
    use InteractsWithTable;
    use InteractsWithPageFilters;

    protected int | string | array $columnSpan = [
        'md' => '50%',
        'xl' => '50%',
    ];

    public function getHeading(): string
    {
        return __('Top Users by Bookings');
    }

    public function table(Table $table): Table
    {
        return $table
            ->query($this->getTableQuery())
            ->columns([
                Tables\Columns\TextColumn::make('full_name')
                    ->label(__('User Name'))
                    ->sortable(['first_name', 'last_name'])
                    ->searchable(['first_name', 'last_name']),
                Tables\Columns\TextColumn::make('email')
                    ->label(__('Email'))
                    ->sortable()
                    ->searchable(),
                Tables\Columns\TextColumn::make('booking_count')
                    ->label(__('Total Bookings'))
                    ->sortable(),
                Tables\Columns\TextColumn::make('total_spent')
                    ->label(__('Total Spent (SAR)'))
                    ->money('SAR')
                    ->sortable(),
            ])
            ->defaultSort('booking_count', 'desc')
            ->paginated([10]);
    }

    protected function getTableQuery(): Builder
    {
        $dateFrom = $this->filters['date_from'] ?? null;
        $dateUntil = $this->filters['date_until'] ?? null;

        $query = User::query()
            ->leftJoin('bookings', 'users.id', '=', 'bookings.user_id')
            ->select('users.id', 'users.first_name', 'users.last_name', 'users.email', DB::raw('COALESCE(COUNT(bookings.id), 0) as booking_count'), DB::raw('COALESCE(SUM(bookings.total_amount), 0) as total_spent'))
            ->groupBy('users.id', 'users.first_name', 'users.last_name', 'users.email');

        if ($dateFrom) {
            $query->whereDate('bookings.created_at', '>=', Carbon::parse($dateFrom));
        }

        if ($dateUntil) {
            $query->whereDate('bookings.created_at', '<=', Carbon::parse($dateUntil));
        }

        return $query->orderByDesc('booking_count')->limit(10);
    }
}
