<?php

namespace App\Filament\Widgets;

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

class UserActivityWidget extends BaseWidget
{
    public function getHeading(): ?string
    {
        return 'User Behavior Summary';
    }
    protected static ?int $sort = 3;
    protected int | string | array $columnSpan = 'full';

    public function table(Table $table): Table
    {
        return $table
            ->query(
                SiteActivity::query()
                    ->select(
                        DB::raw("COALESCE(url, '') || '-' || COALESCE(target_type, '') || '-' || COALESCE(target_id, '') as id"),
                        'url',
                        DB::raw('count(*) as views'),
                        DB::raw('avg(duration_seconds) as avg_duration'),
                        'target_type',
                        'target_id'
                    )
                    ->groupBy('url', 'target_type', 'target_id')
                    ->orderBy('views', 'desc')
                    ->limit(10)
            )
            ->columns([
                Tables\Columns\TextColumn::make('url')
                    ->label('Page/Service')
                    ->formatStateUsing(function ($state, $record) {
                        if ($record->target_type === 'service') return "Service: " . ucfirst($record->target_id);
                        return $state;
                    })
                    ->searchable(),
                Tables\Columns\TextColumn::make('views')
                    ->label('Total Views')
                    ->sortable(),
                Tables\Columns\TextColumn::make('avg_duration')
                    ->label('Avg. Time Spent')
                    ->formatStateUsing(fn ($state) => round($state) . 's')
                    ->sortable(),
            ]);
    }
}
