<?php

declare(strict_types=1);

namespace App\Http\Controllers;

use App\Models\Outlet;
use App\Models\OutletIngredient;
use App\Models\OutletProduct;
use App\Models\Transaction;
use App\Models\TransactionItem;
use App\Models\Waste;
use App\Models\Table;
use App\Models\User;
use App\Models\Ingredient;
use App\Models\Product;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Inertia\Inertia;
use Inertia\Response;

class ReportController extends Controller
{
    /**
     * Display reports and analytics.
     */
    public function index(Request $request): Response
    {
        $data = $this->getReportData($request);
        return Inertia::render('CafePOS/Reports/Index', $data);
    }

    /**
     * Display print preview for reports.
     */
    public function print(Request $request): Response
    {
        $data = $this->getReportData($request);
        return Inertia::render('CafePOS/Reports/Print', $data);
    }

    /**
     * Fetch reports data query metrics.
     */
    protected function getReportData(Request $request): array
    {
        $outletId = $request->input('outlet_id') ? (int)$request->input('outlet_id') : null;
        
        $startDate = $request->input('start_date', date('Y-m-d', strtotime('-30 days')));
        $endDate = $request->input('end_date', date('Y-m-d'));

        // 1. Core Metrics
        $transactionsQuery = Transaction::where('status', 'success')
            ->whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate]);

        if ($outletId) {
            $transactionsQuery->where('outlet_id', $outletId);
        }

        $totalRevenue = (float)$transactionsQuery->sum('total_amount');
        $totalTransactions = $transactionsQuery->count();
        $averageTicket = $totalTransactions > 0 ? $totalRevenue / $totalTransactions : 0;

        // 2. Calculations: COGS / HPP (Food & Beverage Cost)
        $itemsQuery = TransactionItem::whereHas('transaction', function ($q) use ($startDate, $endDate, $outletId) {
                $q->where('status', 'success')
                  ->whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate]);
                if ($outletId) {
                    $q->where('outlet_id', $outletId);
                }
            })
            ->with(['product.recipes.ingredient', 'modifiers.modifier.ingredient']);

        $totalCOGS = 0.0;
        foreach ($itemsQuery->get() as $item) {
            $itemCost = 0.0;
            $qty = $item->qty;
            $variant = $item->variant;
            $product = $item->product;

            if ($product) {
                if ($product->hasRecipe()) {
                    $recipeItems = $product->recipes
                        ->filter(fn($r) => $r->variant === null || $r->variant === $variant);

                    foreach ($recipeItems as $recipe) {
                        $ing = $recipe->ingredient;
                        if ($ing && $ing->conversion_ratio > 0) {
                            $unitCost = (float)$ing->purchase_price / (float)$ing->conversion_ratio;
                            $itemCost += $unitCost * (float)$recipe->usage_qty;
                        }
                    }
                } else {
                    // Default retail cost is 40% of selling price
                    $itemCost += (float)$item->price * 0.40;
                }
            }

            // Modifier Costs
            foreach ($item->modifiers as $itemMod) {
                $mod = $itemMod->modifier;
                if ($mod && $mod->ingredient_id && $mod->usage_qty) {
                    $ing = $mod->ingredient;
                    if ($ing && $ing->conversion_ratio > 0) {
                        $unitCost = (float)$ing->purchase_price / (float)$ing->conversion_ratio;
                        $itemCost += $unitCost * (float)$mod->usage_qty;
                    }
                }
            }

            $totalCOGS += $itemCost * $qty;
        }

        // 3. Waste Calculations
        $wastesQuery = Waste::whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate])
            ->when($outletId, fn($q) => $q->where('outlet_id', $outletId))
            ->get();

        $totalWasteCost = 0.0;
        foreach ($wastesQuery as $waste) {
            $item = $waste->item;
            if ($waste->item_type === 'Ingredient') {
                if ($item && $item->conversion_ratio > 0) {
                    $unitCost = (float)$item->purchase_price / (float)$item->conversion_ratio;
                    $totalWasteCost += $unitCost * (float)$waste->qty;
                }
            } else {
                if ($item) {
                    $totalWasteCost += (float)$item->price * 0.40 * (float)$waste->qty;
                }
            }
        }

        // 4. Calculations: Low Stock Warnings
        $lowIngredientsQuery = OutletIngredient::with(['outlet', 'ingredient'])
            ->whereColumn('stock_qty', '<=', 'threshold_qty');
        
        if ($outletId) {
            $lowIngredientsQuery->where('outlet_id', $outletId);
        }
        $lowIngredients = $lowIngredientsQuery->get()->map(function ($item) {
            return [
                'type' => 'Ingredient',
                'name' => $item->ingredient->name,
                'outlet_name' => $item->outlet->name,
                'stock_qty' => (float)$item->stock_qty,
                'threshold_qty' => (float)$item->threshold_qty,
                'unit' => $item->ingredient->unit,
            ];
        });

        $lowProductsQuery = OutletProduct::with(['outlet', 'product'])
            ->whereColumn('stock_qty', '<=', 'threshold_qty');
        
        if ($outletId) {
            $lowProductsQuery->where('outlet_id', $outletId);
        }
        $lowProducts = $lowProductsQuery->get()->map(function ($item) {
            return [
                'type' => 'Retail Product',
                'name' => $item->product->name,
                'outlet_name' => $item->outlet->name,
                'stock_qty' => (float)$item->stock_qty,
                'threshold_qty' => (float)$item->threshold_qty,
                'unit' => 'pcs',
            ];
        });

        $lowStockAlerts = $lowIngredients->concat($lowProducts);

        // 5. Sales Trend (Daily Omzet)
        $salesTrendData = Transaction::where('status', 'success')
            ->whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate])
            ->when($outletId, fn($q) => $q->where('outlet_id', $outletId))
            ->select(
                DB::raw('DATE(created_at) as date'),
                DB::raw('SUM(total_amount) as total'),
                DB::raw('COUNT(id) as count')
            )
            ->groupBy(DB::raw('DATE(created_at)'))
            ->orderBy('date', 'asc')
            ->get()
            ->map(function ($item) {
                return [
                    'date' => $item->date,
                    'total' => (float)$item->total,
                    'count' => (int)$item->count,
                ];
            });

        // 6. Top Selling Products
        $topProducts = TransactionItem::whereHas('transaction', function ($q) use ($startDate, $endDate, $outletId) {
                $q->where('status', 'success')
                  ->whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate]);
                
                if ($outletId) {
                    $q->where('outlet_id', $outletId);
                }
            })
            ->select('product_id', DB::raw('SUM(qty) as total_qty'), DB::raw('SUM(subtotal) as total_sales'))
            ->groupBy('product_id')
            ->orderBy('total_qty', 'desc')
            ->limit(5)
            ->with('product')
            ->get()
            ->map(function ($item) {
                return [
                    'name' => $item->product ? $item->product->name : 'Unknown Product',
                    'category' => $item->product ? $item->product->category : 'Unknown',
                    'qty' => (int)$item->total_qty,
                    'sales' => (float)$item->total_sales,
                ];
            });

        // 7. NEW: Waiter Performance
        $waiterPerformance = Transaction::where('status', 'success')
            ->whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate])
            ->when($outletId, fn($q) => $q->where('outlet_id', $outletId))
            ->whereNotNull('waiter_id')
            ->select('waiter_id', DB::raw('COUNT(id) as transaction_count'), DB::raw('SUM(total_amount) as total_amount'))
            ->groupBy('waiter_id')
            ->orderBy('transaction_count', 'desc')
            ->with('waiter')
            ->get()
            ->map(fn($w) => [
                'name' => $w->waiter ? $w->waiter->name : 'Staff Waiter',
                'transactions' => (int)$w->transaction_count,
                'sales' => (float)$w->total_amount,
            ]);

        // 8. NEW: Table Occupancy / Turnover
        $tableTurnover = Transaction::where('status', 'success')
            ->whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate])
            ->when($outletId, fn($q) => $q->where('outlet_id', $outletId))
            ->whereNotNull('table_id')
            ->select('table_id', DB::raw('COUNT(id) as transaction_count'), DB::raw('SUM(total_amount) as total_amount'))
            ->groupBy('table_id')
            ->orderBy('transaction_count', 'desc')
            ->with('table')
            ->get()
            ->map(fn($t) => [
                'number' => $t->table ? $t->table->number : 'Unknown Table',
                'transactions' => (int)$t->transaction_count,
                'sales' => (float)$t->total_amount,
            ]);

        // 9. NEW: Hourly distribution (Peak Hours)
        $peakHours = Transaction::where('status', 'success')
            ->whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate])
            ->when($outletId, fn($q) => $q->where('outlet_id', $outletId))
            ->select(DB::raw('HOUR(created_at) as hour'), DB::raw('COUNT(id) as transaction_count'), DB::raw('SUM(total_amount) as total_amount'))
            ->groupBy(DB::raw('HOUR(created_at)'))
            ->orderBy('hour', 'asc')
            ->get()
            ->map(fn($h) => [
                'hour' => str_pad((string)$h->hour, 2, '0', STR_PAD_LEFT) . ':00',
                'transactions' => (int)$h->transaction_count,
                'sales' => (float)$h->total_amount,
            ]);

        $outlets = Outlet::select('id', 'name')->get();

        return [
            'metrics' => [
                'total_revenue' => $totalRevenue,
                'total_transactions' => $totalTransactions,
                'average_ticket' => $averageTicket,
                'low_stock_count' => $lowStockAlerts->count(),
                'total_cogs' => $totalCOGS,
                'food_cost_percentage' => $totalRevenue > 0 ? ($totalCOGS / $totalRevenue) * 100 : 0,
                'total_waste_cost' => $totalWasteCost,
            ],
            'lowStockAlerts' => $lowStockAlerts,
            'salesTrend' => $salesTrendData,
            'topProducts' => $topProducts,
            'waiterPerformance' => $waiterPerformance,
            'tableTurnover' => $tableTurnover,
            'peakHours' => $peakHours,
            'outlets' => $outlets,
            'filters' => [
                'outlet_id' => $outletId,
                'start_date' => $startDate,
                'end_date' => $endDate,
            ],
        ];
    }
}
