<?php

namespace App\Http\Controllers;

use App\Models\User;
use Illuminate\Http\Request;
use Inertia\Inertia;
use Spatie\Permission\Models\Role;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Carbon\Carbon;

class DashboardController extends Controller
{
    public function index()
    {
        // General Stats
        $totalUsers = User::count();
        $activeUsers = User::where('status', 'active')->count();
        $totalRoles = Role::count();
        $recentUsers = User::with('roles')->latest()->take(5)->get();

        $driver = DB::connection()->getDriverName();

        // Chart Data: User Growth (Last 6 Months)
        if ($driver === 'sqlite') {
            $monthFormat = "strftime('%Y-%m', created_at)";
        } elseif ($driver === 'pgsql') {
            $monthFormat = "to_char(created_at, 'YYYY-MM')";
        } else {
            $monthFormat = "DATE_FORMAT(created_at, '%Y-%m')";
        }
        
        $userGrowth = User::select(
            DB::raw('count(id) as total'),
            DB::raw("$monthFormat as month")
        )
            ->groupBy('month')
            ->orderBy('month', 'asc')
            ->take(6)
            ->get()
            ->map(function ($item) {
                return [
                    'label' => Carbon::parse($item->month . '-01')->format('M Y'),
                    'value' => $item->total,
                ];
            });
 
        // Chart Data: Activity Logs (Last 7 Days)
        if ($driver === 'sqlite') {
            $dateFormat = "strftime('%Y-%m-%d', created_at)";
        } elseif ($driver === 'pgsql') {
            $dateFormat = "to_char(created_at, 'YYYY-MM-DD')";
        } else {
            $dateFormat = "DATE(created_at)";
        }

        $activityStats = [];
        if (Schema::hasTable('activity_log')) {
            $activityStats = DB::table('activity_log')
                ->select(
                    DB::raw('count(id) as total'),
                    DB::raw("$dateFormat as date")
                )
                ->where('created_at', '>=', Carbon::now()->subDays(7))
                ->groupBy('date')
                ->orderBy('date', 'asc')
                ->get()
                ->map(function ($item) {
                    return [
                        'label' => Carbon::parse($item->date)->format('D, M d'),
                        'value' => $item->total,
                    ];
                });
        }

        return Inertia::render('Dashboard', [
            'stats' => [
                'totalUsers' => $totalUsers,
                'activeUsers' => $activeUsers,
                'totalRoles' => $totalRoles,
                'recentUsers' => $recentUsers,
                'charts' => [
                    'userGrowth' => $userGrowth,
                    'activityStats' => $activityStats,
                ]
            ]
        ]);
    }
}