LauckCycles/app/Http/Controllers/ReportController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Sale;
use App\Models\Expense;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class ReportController extends Controller
{
    public function index()
    {
        // 1. Totales Generales
        $totalIncome = Sale::sum('total');
        $totalOutcome = Expense::where('status', 'Pagado')->sum('total_amount');
        $balance = $totalIncome - $totalOutcome;

        // 2. Gastos por Categoría (Para Pie Chart)
        $expensesByCategory = Expense::where('status', 'Pagado')
            ->select('expense_category', DB::raw('SUM(total_amount) as total'))
            ->groupBy('expense_category')
            ->get();

        // 2.5 Ingresos por Categoría (Para Pie Chart)
        $rawEarningsByCategory = \App\Models\SaleDetail::join('products', 'sale_details.product_id', '=', 'products.id')
            ->select('products.type', DB::raw('SUM(sale_details.quantity * sale_details.price) as total_earnings'))
            ->groupBy('products.type')
            ->get();

        $categoryTranslations = [
            'bike' => 'Bicicletas',
            'clothing' => 'Indumentaria',
            'accessory' => 'Accesorios',
            'spare' => 'Repuestos',
            'children' => 'Niños',
            'rollers' => 'Rollers',
            'skate' => 'Skates',
            'other' => 'Otros'
        ];

        $earningsByCategory = $rawEarningsByCategory->map(function($item) use ($categoryTranslations) {
            $type = $item->type;
            return [
                'category' => $categoryTranslations[$type] ?? ucfirst($type),
                'total' => $item->total_earnings
            ];
        });

        // 3. Ingresos y Egresos por Mes (Últimos 6 meses)
        $months = [];
        $monthlyIncomeData = [];
        $monthlyOutcomeData = [];

        for ($i = 5; $i >= 0; $i--) {
            $date = now()->subMonths($i);
            $monthName = $date->translatedFormat('M Y'); // ej: may 2026
            
            $months[] = ucfirst($monthName);
            
            // Ingresos del mes
            $inc = Sale::whereYear('created_at', $date->year)
                       ->whereMonth('created_at', $date->month)
                       ->sum('total');
            $monthlyIncomeData[] = $inc;
            
            // Egresos del mes
            $out = Expense::where('status', 'Pagado')
                          ->whereYear('expense_date', $date->year)
                          ->whereMonth('expense_date', $date->month)
                          ->sum('total_amount');
            $monthlyOutcomeData[] = $out;
        }

        // 4. Top 5 Productos más vendidos
        $topProducts = \App\Models\SaleDetail::with('product')
            ->select('product_id', DB::raw('SUM(quantity) as total_qty'))
            ->groupBy('product_id')
            ->orderByDesc('total_qty')
            ->take(5)
            ->get();

        // 5. Top 5 Repuestos más usados/vendidos
        $topSpares = \App\Models\SaleDetail::with('product')
            ->whereHas('product', function ($query) {
                $query->where('type', 'spare');
            })
            ->select('product_id', DB::raw('SUM(quantity) as total_qty'))
            ->groupBy('product_id')
            ->orderByDesc('total_qty')
            ->take(5)
            ->get();

        // 6. Mejores Clientes (Top Spenders)
        $topClients = Sale::with('client')
            ->whereNotNull('client_id')
            ->select('client_id', DB::raw('SUM(total) as total_spent'))
            ->groupBy('client_id')
            ->orderByDesc('total_spent')
            ->take(5)
            ->get();

        return view('reports.index', compact(
            'totalIncome', 
            'totalOutcome', 
            'balance', 
            'expensesByCategory', 
            'earningsByCategory',
            'months', 
            'monthlyIncomeData', 
            'monthlyOutcomeData',
            'topProducts',
            'topSpares',
            'topClients'
        ));
    }
}