<?php

namespace App\Filament\Widgets;

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

class MarketingAttributionWidget extends BaseWidget
{
    protected static ?int $sort = 3;

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

    protected static ?string $heading = 'UTM Marketing & Acquisition Attribution';

    public function table(Table $table): Table
    {
        return $table
            ->query(
                TrafficSession::query()
                    ->fromSub(function ($query) {
                        $query->from('traffic_sessions')
                            ->select('utm_source as id', 'utm_source', 'utm_medium', 'utm_campaign', DB::raw('count(id) as sessions_count'))
                            ->whereNotNull('utm_source')
                            ->groupBy('utm_source', 'utm_medium', 'utm_campaign');
                    }, 'traffic_sessions')
                    ->orderBy('sessions_count', 'desc')
            )
            ->paginated(false)
            ->columns([
                Tables\Columns\TextColumn::make('utm_source')
                    ->label('Source')
                    ->badge()
                    ->color('primary')
                    ->searchable(),
                Tables\Columns\TextColumn::make('utm_medium')
                    ->label('Medium')
                    ->placeholder('N/A')
                    ->searchable(),
                Tables\Columns\TextColumn::make('utm_campaign')
                    ->label('Campaign')
                    ->placeholder('N/A')
                    ->searchable(),
                Tables\Columns\TextColumn::make('sessions_count')
                    ->label('Visits')
                    ->numeric()
                    ->alignCenter(),
                Tables\Columns\TextColumn::make('add_to_carts')
                    ->label('Add to Carts')
                    ->getStateUsing(function ($record) {
                        return \App\Models\AnalyticsFunnelEvent::where('event_type', 'add_to_cart')
                            ->whereIn('session_id', function ($query) use ($record) {
                                $query->select('id')
                                    ->from('traffic_sessions')
                                    ->where('utm_source', $record->utm_source)
                                    ->where('utm_medium', $record->utm_medium)
                                    ->where('utm_campaign', $record->utm_campaign);
                            })
                            ->distinct('session_id')
                            ->count('session_id');
                    })
                    ->numeric()
                    ->alignCenter(),
                Tables\Columns\TextColumn::make('conversions')
                    ->label('Purchases')
                    ->getStateUsing(function ($record) {
                        return \App\Models\AnalyticsFunnelEvent::where('event_type', 'purchase')
                            ->whereIn('session_id', function ($query) use ($record) {
                                $query->select('id')
                                    ->from('traffic_sessions')
                                    ->where('utm_source', $record->utm_source)
                                    ->where('utm_medium', $record->utm_medium)
                                    ->where('utm_campaign', $record->utm_campaign);
                            })
                            ->distinct('session_id')
                            ->count('session_id');
                    })
                    ->numeric()
                    ->alignCenter(),
                Tables\Columns\TextColumn::make('conversion_rate')
                    ->label('Conversion Rate')
                    ->getStateUsing(function ($record) {
                        $sessions = $record->sessions_count;
                        $conversions = \App\Models\AnalyticsFunnelEvent::where('event_type', 'purchase')
                            ->whereIn('session_id', function ($query) use ($record) {
                                $query->select('id')
                                    ->from('traffic_sessions')
                                    ->where('utm_source', $record->utm_source)
                                    ->where('utm_medium', $record->utm_medium)
                                    ->where('utm_campaign', $record->utm_campaign);
                            })
                            ->distinct('session_id')
                            ->count('session_id');
                        return $sessions > 0 ? number_format(($conversions / $sessions) * 100, 1) . '%' : '0.0%';
                    })
                    ->badge()
                    ->color('success')
                    ->alignCenter(),
            ])
            ->filters([
                Tables\Filters\SelectFilter::make('utm_source')
                    ->label('Filter by Source')
                    ->options([
                        'google' => 'Google',
                        'facebook' => 'Facebook',
                        'instagram' => 'Instagram',
                        'newsletter' => 'Newsletter',
                    ]),
            ]);
    }
}
