<?php

namespace App\Modules\Pilkades\Http\Controllers\AdminDesa;

use App\Http\Controllers\Controller;
use App\Modules\Pilkades\Models\VisitorLog;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;

class StatisticController extends Controller
{
    public function index(): View
    {
        $desaId = Auth::user()->desa_id;
        $driver = DB::connection()->getDriverName();
        $monthExpression = $driver === 'sqlite'
            ? "strftime('%Y-%m', visited_at)"
            : "DATE_FORMAT(visited_at, '%Y-%m')";

        $dailyVisitors = VisitorLog::query()
            ->selectRaw('DATE(visited_at) as date, COUNT(*) as total')
            ->where('desa_id', $desaId)
            ->where('visited_at', '>=', now()->subDays(14))
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        $monthlyVisitors = VisitorLog::query()
            ->selectRaw("{$monthExpression} as month, COUNT(*) as total")
            ->where('desa_id', $desaId)
            ->where('visited_at', '>=', now()->subMonths(6))
            ->groupBy('month')
            ->orderBy('month')
            ->get();

        $topKota = VisitorLog::query()
            ->selectRaw('kota, COUNT(*) as total')
            ->where('desa_id', $desaId)
            ->whereNotNull('kota')
            ->groupBy('kota')
            ->orderByDesc('total')
            ->limit(10)
            ->get();

        return view('pilkades::admin-desa.statistic.index', compact('dailyVisitors', 'monthlyVisitors', 'topKota'));
    }
}
