<?php

namespace App\Http\Controllers\Biller;

use App\Http\Controllers\Controller;
use App\Models\Order;
use App\Models\OrderItem;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ReportController extends Controller
{
    private function getDateFilterData(Request $request)
    {
        $filter = $request->get('date_filter', 'today');
        $startDate = Carbon::today();
        $endDate = Carbon::today()->endOfDay();

        if ($filter === 'yesterday') {
            $startDate = Carbon::yesterday();
            $endDate = Carbon::yesterday()->endOfDay();
        } elseif ($filter === 'last_7_days') {
            $startDate = Carbon::today()->subDays(6);
            $endDate = Carbon::today()->endOfDay();
        } elseif ($filter === 'this_month') {
            $startDate = Carbon::today()->startOfMonth();
            $endDate = Carbon::today()->endOfDay();
        } elseif ($filter === 'custom' && $request->has(['start_date', 'end_date'])) {
            $startDate = Carbon::parse($request->start_date)->startOfDay();
            $endDate = Carbon::parse($request->end_date)->endOfDay();
        }

        return compact('filter', 'startDate', 'endDate');
    }

    private function getBaseOrderQuery($branchId, $startDate, $endDate)
    {
        return Order::where('branch_id', $branchId)
            ->where('status', 'settled')
            ->whereBetween('created_at', [$startDate, $endDate]);
    }

    public function index(Request $request)
    {
        $branchId = auth()->user()->active_branch_id;
        $dateData = $this->getDateFilterData($request);
        $ordersQuery = $this->getBaseOrderQuery($branchId, $dateData['startDate'], $dateData['endDate']);

        $salesTotals = (clone $ordersQuery)->select(
            DB::raw('COUNT(id) as total_orders'),
            DB::raw('SUM(grand_total) as total_revenue'),
            DB::raw('SUM(subtotal) as total_subtotal'),
            DB::raw('SUM(gst_amount) as total_gst'),
            DB::raw('SUM(discount_amount) as total_discount'),
            DB::raw('SUM(CASE WHEN order_type = "dine_in" THEN grand_total ELSE 0 END) as dine_in_revenue'),
            DB::raw('SUM(CASE WHEN order_type = "takeaway" THEN grand_total ELSE 0 END) as takeaway_revenue'),
            DB::raw('SUM(CASE WHEN order_type = "delivery" THEN grand_total ELSE 0 END) as delivery_revenue')
        )->first();

        $salesSummary = [
            'total_revenue' => $salesTotals->total_revenue ?? 0,
            'total_subtotal' => $salesTotals->total_subtotal ?? 0,
            'total_gst' => $salesTotals->total_gst ?? 0,
            'total_discount' => $salesTotals->total_discount ?? 0,
            'total_orders' => $salesTotals->total_orders ?? 0,
            'by_type' => [
                'dine_in' => $salesTotals->dine_in_revenue ?? 0,
                'takeaway' => $salesTotals->takeaway_revenue ?? 0,
                'delivery' => $salesTotals->delivery_revenue ?? 0,
            ],
        ];

        $paymentMethodsTotals = (clone $ordersQuery)->select(
            DB::raw('SUM(CASE WHEN payment_method = "cash" THEN grand_total WHEN payment_method = "split" THEN split_cash ELSE 0 END) as cash_total'),
            DB::raw('SUM(CASE WHEN payment_method = "card" THEN grand_total WHEN payment_method = "split" THEN split_card ELSE 0 END) as card_total'),
            DB::raw('SUM(CASE WHEN payment_method = "upi" THEN grand_total WHEN payment_method = "split" THEN split_upi ELSE 0 END) as upi_total')
        )->first();

        $paymentMethods = [
            'cash' => $paymentMethodsTotals->cash_total ?? 0,
            'card' => $paymentMethodsTotals->card_total ?? 0,
            'upi' => $paymentMethodsTotals->upi_total ?? 0,
        ];

        $itemSales = OrderItem::join('orders', 'order_items.order_id', '=', 'orders.id')
            ->where('orders.branch_id', $branchId)
            ->where('orders.status', 'settled')
            ->whereBetween('orders.created_at', [$dateData['startDate'], $dateData['endDate']])
            ->select('order_items.name', DB::raw('SUM(order_items.quantity) as total_qty'), DB::raw('SUM(order_items.subtotal) as total_revenue'))
            ->groupBy('order_items.name')
            ->orderByDesc('total_qty')
            ->limit(20)
            ->get();

        $categorySales = OrderItem::join('items', 'order_items.item_id', '=', 'items.id')
            ->join('categories', 'items.category_id', '=', 'categories.id')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->where('orders.branch_id', $branchId)
            ->where('orders.status', 'settled')
            ->whereBetween('orders.created_at', [$dateData['startDate'], $dateData['endDate']])
            ->select('categories.name', DB::raw('SUM(order_items.quantity) as total_qty'), DB::raw('SUM(order_items.subtotal) as total_revenue'))
            ->groupBy('categories.name')
            ->orderByDesc('total_revenue')
            ->get();

        $waiterPerformance = Order::with('waiter')
            ->where('branch_id', $branchId)
            ->where('status', 'settled')
            ->whereNotNull('waiter_id')
            ->whereBetween('created_at', [$dateData['startDate'], $dateData['endDate']])
            ->select('waiter_id', DB::raw('COUNT(id) as total_orders'), DB::raw('SUM(grand_total) as total_revenue'))
            ->groupBy('waiter_id')
            ->get()
            ->map(function ($order) {
                return [
                    'name' => $order->waiter->name ?? 'Unknown',
                    'total_orders' => $order->total_orders,
                    'total_revenue' => $order->total_revenue,
                    'avg_order_value' => $order->total_orders > 0 ? $order->total_revenue / $order->total_orders : 0,
                ];
            })->sortByDesc('total_revenue')->values();

        $tableTurnaround = Order::where('branch_id', $branchId)
            ->where('status', 'settled')
            ->where('order_type', 'dine_in')
            ->whereBetween('created_at', [$dateData['startDate'], $dateData['endDate']])
            ->select(DB::raw('AVG(TIMESTAMPDIFF(MINUTE, created_at, updated_at)) as avg_turnaround_minutes'))
            ->value('avg_turnaround_minutes');

        return view('biller.reports.index', array_merge($dateData, compact(
            'salesSummary', 'paymentMethods', 'itemSales', 'categorySales', 'waiterPerformance', 'tableTurnaround'
        )));
    }

    public function sales(Request $request)
    {
        $branchId = auth()->user()->active_branch_id;
        $dateData = $this->getDateFilterData($request);
        $ordersQuery = $this->getBaseOrderQuery($branchId, $dateData['startDate'], $dateData['endDate']);

        $salesTotals = (clone $ordersQuery)->select(
            DB::raw('COUNT(id) as total_orders'),
            DB::raw('SUM(grand_total) as total_revenue'),
            DB::raw('SUM(subtotal) as total_subtotal'),
            DB::raw('SUM(gst_amount) as total_gst'),
            DB::raw('SUM(discount_amount) as total_discount'),
            DB::raw('SUM(CASE WHEN order_type = "dine_in" THEN grand_total ELSE 0 END) as dine_in_revenue'),
            DB::raw('SUM(CASE WHEN order_type = "takeaway" THEN grand_total ELSE 0 END) as takeaway_revenue'),
            DB::raw('SUM(CASE WHEN order_type = "delivery" THEN grand_total ELSE 0 END) as delivery_revenue')
        )->first();

        $salesSummary = [
            'total_revenue' => $salesTotals->total_revenue ?? 0,
            'total_subtotal' => $salesTotals->total_subtotal ?? 0,
            'total_gst' => $salesTotals->total_gst ?? 0,
            'total_discount' => $salesTotals->total_discount ?? 0,
            'total_orders' => $salesTotals->total_orders ?? 0,
            'by_type' => [
                'dine_in' => $salesTotals->dine_in_revenue ?? 0,
                'takeaway' => $salesTotals->takeaway_revenue ?? 0,
                'delivery' => $salesTotals->delivery_revenue ?? 0,
            ],
        ];

        $paymentMethodsTotals = (clone $ordersQuery)->select(
            DB::raw('SUM(CASE WHEN payment_method = "cash" THEN grand_total WHEN payment_method = "split" THEN split_cash ELSE 0 END) as cash_total'),
            DB::raw('SUM(CASE WHEN payment_method = "card" THEN grand_total WHEN payment_method = "split" THEN split_card ELSE 0 END) as card_total'),
            DB::raw('SUM(CASE WHEN payment_method = "upi" THEN grand_total WHEN payment_method = "split" THEN split_upi ELSE 0 END) as upi_total')
        )->first();

        $paymentMethods = [
            'cash' => $paymentMethodsTotals->cash_total ?? 0,
            'card' => $paymentMethodsTotals->card_total ?? 0,
            'upi' => $paymentMethodsTotals->upi_total ?? 0,
        ];

        return view('biller.reports.sales', array_merge($dateData, compact('salesSummary', 'paymentMethods')));
    }

    public function items(Request $request)
    {
        $branchId = auth()->user()->active_branch_id;
        $dateData = $this->getDateFilterData($request);

        // We paginate items here because there could be hundreds.
        $itemSales = OrderItem::join('orders', 'order_items.order_id', '=', 'orders.id')
            ->where('orders.branch_id', $branchId)
            ->where('orders.status', 'settled')
            ->whereBetween('orders.created_at', [$dateData['startDate'], $dateData['endDate']])
            ->select('order_items.name', DB::raw('SUM(order_items.quantity) as total_qty'), DB::raw('SUM(order_items.subtotal) as total_revenue'))
            ->groupBy('order_items.name')
            ->orderByDesc('total_qty')
            ->paginate(50); // Paginated instead of limit

        return view('biller.reports.items', array_merge($dateData, compact('itemSales')));
    }

    public function categories(Request $request)
    {
        $branchId = auth()->user()->active_branch_id;
        $dateData = $this->getDateFilterData($request);

        $categorySales = OrderItem::join('items', 'order_items.item_id', '=', 'items.id')
            ->join('categories', 'items.category_id', '=', 'categories.id')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->where('orders.branch_id', $branchId)
            ->where('orders.status', 'settled')
            ->whereBetween('orders.created_at', [$dateData['startDate'], $dateData['endDate']])
            ->select('categories.name', DB::raw('SUM(order_items.quantity) as total_qty'), DB::raw('SUM(order_items.subtotal) as total_revenue'))
            ->groupBy('categories.name')
            ->orderByDesc('total_revenue')
            ->get();

        return view('biller.reports.categories', array_merge($dateData, compact('categorySales')));
    }

    public function stewards(Request $request)
    {
        $branchId = auth()->user()->active_branch_id;
        $dateData = $this->getDateFilterData($request);

        $waiterPerformance = Order::with('waiter')
            ->where('branch_id', $branchId)
            ->where('status', 'settled')
            ->whereNotNull('waiter_id')
            ->whereBetween('created_at', [$dateData['startDate'], $dateData['endDate']])
            ->select('waiter_id', DB::raw('COUNT(id) as total_orders'), DB::raw('SUM(grand_total) as total_revenue'))
            ->groupBy('waiter_id')
            ->get()
            ->map(function ($order) {
                return [
                    'name' => $order->waiter->name ?? 'Unknown',
                    'total_orders' => $order->total_orders,
                    'total_revenue' => $order->total_revenue,
                    'avg_order_value' => $order->total_orders > 0 ? $order->total_revenue / $order->total_orders : 0,
                ];
            })->sortByDesc('total_revenue')->values();

        $tableTurnaround = Order::where('branch_id', $branchId)
            ->where('status', 'settled')
            ->where('order_type', 'dine_in')
            ->whereBetween('created_at', [$dateData['startDate'], $dateData['endDate']])
            ->select(DB::raw('AVG(TIMESTAMPDIFF(MINUTE, created_at, updated_at)) as avg_turnaround_minutes'))
            ->value('avg_turnaround_minutes');

        return view('biller.reports.stewards', array_merge($dateData, compact('waiterPerformance', 'tableTurnaround')));
    }
}
