<?php

namespace App\Filament\Widgets;

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

class CustomerBehaviorWidget extends BaseWidget
{
    protected static ?int $sort = 4;

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

    protected static ?string $heading = 'Customer Value & Cohorts (LTV)';

    public function table(Table $table): Table
    {
        return $table
            ->query(
                Order::query()
                    ->fromSub(function ($query) {
                        $query->from('orders')
                            ->select(DB::raw('COALESCE(customer_email, customer_name) as id'), 'customer_name', 'customer_email', DB::raw('COUNT(id) as total_orders'), DB::raw('SUM(grand_total) as ltv'), DB::raw('AVG(grand_total) as customer_aov'))
                            ->where('payment_status', 'paid')
                            ->groupBy('customer_name', 'customer_email');
                    }, 'orders')
                    ->orderBy('ltv', 'desc')
            )
            ->paginated(false)
            ->columns([
                Tables\Columns\TextColumn::make('customer_name')
                    ->label('Customer Name')
                    ->searchable(),
                Tables\Columns\TextColumn::make('customer_email')
                    ->label('Email Address')
                    ->placeholder('N/A'),
                Tables\Columns\TextColumn::make('total_orders')
                    ->label('Orders Placed')
                    ->numeric()
                    ->alignCenter(),
                Tables\Columns\TextColumn::make('customer_aov')
                    ->label('Customer AOV')
                    ->money('BDT')
                    ->alignEnd(),
                Tables\Columns\TextColumn::make('ltv')
                    ->label('Lifetime Value (LTV)')
                    ->money('BDT')
                    ->alignEnd()
                    ->badge()
                    ->color('success'),
            ]);
    }
}
