<?php

namespace App\Filament\Widgets;

use App\Models\AiUsageLog;
use App\Models\TravelAgent; // Make sure to import your TravelAgent model
use Filament\Tables;
use Filament\Tables\Table;
use Filament\Widgets\TableWidget as BaseWidget;
use Illuminate\Support\Facades\DB;

class TopAiUsers extends BaseWidget
{
    protected static ?string $heading = 'Top Travel Agents by AI Usage';
    
    protected int | string | array $columnSpan = 'full';
    protected static ?int $sort = 7;

    public function table(Table $table): Table
    {
        return $table
            ->query(
                // Use a subquery or join approach
                AiUsageLog::query()
                    ->join('travel_agents', 'ai_usage_logs.travel_agent_id', '=', 'travel_agents.id')
                    ->select(
                        'travel_agents.id',
                        'travel_agents.name as travel_agent_name',
                        DB::raw('COUNT(ai_usage_logs.id) as request_count'),
                        DB::raw('SUM(ai_usage_logs.word_count) as total_words')
                    )
                    ->groupBy('travel_agents.id', 'travel_agents.name')
                    ->orderBy('total_words', 'desc')
            )
            ->columns([
                Tables\Columns\TextColumn::make('travel_agent_name')
                    ->label('Travel Agent')
                    ->searchable()
                    ->sortable(),
                    
                Tables\Columns\TextColumn::make('request_count')
                    ->label('AI Requests')
                    ->sortable()
                    ->alignCenter()
                    ->numeric(),
                    
                Tables\Columns\TextColumn::make('total_words')
                    ->label('Words Generated')
                    ->sortable()
                    ->alignCenter()
                    ->formatStateUsing(fn ($state) => number_format($state))
                    ->numeric(),
                    
                Tables\Columns\TextColumn::make('avg_words_per_request')
                    ->label('Avg Words/Request')
                    ->getStateUsing(fn ($record) => $record->request_count > 0 ? number_format($record->total_words / $record->request_count) : 0)
                    ->alignCenter()
                    ->numeric(),
            ])
            ->paginated(false);
    }
}