<?php

namespace App\Filament\Widgets;

use Filament\Widgets\TableWidget as BaseWidget;
use Filament\Tables;
use Filament\Tables\Table;
use App\Models\OrderItem;
use Illuminate\Support\Facades\DB;

class ProductPerformanceWidget extends BaseWidget
{
    protected static ?int $sort = 2;

    protected int | string | array $columnSpan = 'full';

    protected static ?string $heading = 'Top Selling Products & Conversion';

    public function table(Table $table): Table
    {
        return $table
            ->query(
                \App\Models\Product::query()
                    ->join('order_items', 'products.id', '=', 'order_items.product_id')
                    ->select('products.id', 'products.id as product_id', 'products.name as product_name', DB::raw('SUM(order_items.quantity) as items_sold'), DB::raw('SUM(order_items.price * order_items.quantity) as revenue'))
                    ->groupBy('products.id', 'products.name')
                    ->orderBy('items_sold', 'desc')
            )
            ->paginated(false)
            ->columns([
                Tables\Columns\TextColumn::make('product_name')
                    ->label('Product')
                    ->searchable(),
                Tables\Columns\TextColumn::make('items_sold')
                    ->label('Items Sold')
                    ->numeric()
                    ->alignCenter(),
                Tables\Columns\TextColumn::make('revenue')
                    ->label('Revenue Generated')
                    ->money('BDT')
                    ->alignEnd(),
                Tables\Columns\TextColumn::make('views')
                    ->label('Storefront Views')
                    ->getStateUsing(fn ($record) => 
                        \App\Models\AnalyticsFunnelEvent::where('event_type', 'view_item')
                            ->where('product_id', $record->product_id)
                            ->count()
                    )
                    ->numeric()
                    ->alignCenter(),
                Tables\Columns\TextColumn::make('conversion_rate')
                    ->label('Detail-to-Purchase Rate')
                    ->getStateUsing(function ($record) {
                        $views = \App\Models\AnalyticsFunnelEvent::where('event_type', 'view_item')
                            ->where('product_id', $record->product_id)
                            ->count();
                        
                        $purchases = \App\Models\AnalyticsFunnelEvent::where('event_type', 'purchase')
                            ->where('product_id', $record->product_id)
                            ->count();
                            
                        return $views > 0 ? number_format(($purchases / $views) * 100, 1) . '%' : '0.0%';
                    })
                    ->badge()
                    ->color(fn ($state) => floatval($state) >= 5.0 ? 'success' : (floatval($state) >= 1.0 ? 'info' : 'gray'))
                    ->alignCenter(),
            ]);
    }
}
