<?php

namespace App\Services\Dashboard;

use App\Models\InventoryBalance;
use App\Models\Order;
use App\Models\OrderItem;
use App\Models\Product;
use App\Models\StockTransfer;
use App\Services\ProfitService;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;

class StoreDashboardService
{
    public function __construct(protected ProfitService $profitService)
    {
    }

    /**
     * @return array<string, mixed>
     */
    public function build(?int $locationId): array
    {
        $hasLocationColumn = Schema::hasColumn('orders', 'fulfillment_location_id');
        $orders = fn (): Builder => $this->scopedOrders($locationId, $hasLocationColumn);
        $paidOrders = fn (): Builder => $this->scopedOrders($locationId, $hasLocationColumn)->paid();

        $todaySales = (clone $paidOrders())->whereDate('created_at', today())->sum('total_payable');
        $todayOrders = (clone $orders())->whereDate('created_at', today())->count();

        $monthSales = (clone $paidOrders())
            ->whereMonth('created_at', now()->month)
            ->whereYear('created_at', now()->year)
            ->sum('total_payable');

        $monthOrders = (clone $orders())
            ->whereMonth('created_at', now()->month)
            ->whereYear('created_at', now()->year)
            ->count();

        $lastMonthSales = (clone $paidOrders())
            ->whereMonth('created_at', now()->subMonth()->month)
            ->whereYear('created_at', now()->subMonth()->year)
            ->sum('total_payable');

        $salesGrowth = $lastMonthSales > 0
            ? (($monthSales - $lastMonthSales) / $lastMonthSales) * 100
            : 0;

        $totalRevenue = (clone $paidOrders())->sum('total_payable');
        $totalOrders = (clone $orders())->count();
        $avgOrderValue = (clone $paidOrders())->avg('total_payable') ?? 0;
        $pendingOrders = (clone $orders())->where('status', 'placed')->count();

        $todayPosOrders = (clone $orders())->whereDate('created_at', today())->where('source', 'pos')->count();
        $todayOnlineOrders = (clone $orders())->whereDate('created_at', today())
            ->where(function ($q) {
                $q->whereNull('source')->orWhere('source', '!=', 'pos');
            })
            ->count();

        [$lowStockProducts, $lowStockItems] = $this->lowStock($locationId);

        $incomingTransfers = 0;
        if ($locationId && Schema::hasTable('stock_transfers')) {
            $incomingTransfers = StockTransfer::query()
                ->where('to_location_id', $locationId)
                ->whereIn('status', [
                    StockTransfer::STATUS_APPROVED,
                    StockTransfer::STATUS_IN_TRANSIT,
                ])
                ->count();
        }

        $ordersByStatus = (clone $orders())
            ->select('status', DB::raw('count(*) as count'))
            ->groupBy('status')
            ->pluck('count', 'status');

        $paymentStats = (clone $orders())
            ->select('payment_status', DB::raw('count(*) as count'))
            ->groupBy('payment_status')
            ->pluck('count', 'payment_status');

        $revenueData = (clone $paidOrders())
            ->where('created_at', '>=', now()->subDays(7))
            ->select(DB::raw('DATE(created_at) as date'), DB::raw('SUM(total_payable) as revenue'))
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        $monthlyRevenueData = (clone $paidOrders())
            ->where('created_at', '>=', now()->subDays(30))
            ->select(DB::raw('DATE(created_at) as date'), DB::raw('SUM(total_payable) as revenue'))
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        $topProducts = OrderItem::query()
            ->select('product_id', DB::raw('SUM(qty) as total_sold'), DB::raw('SUM(line_total) as total_revenue'))
            ->whereHas('order', function ($query) use ($locationId, $hasLocationColumn) {
                $query->where('created_at', '>=', now()->subDays(30))
                    ->where('payment_status', 'paid');
                if ($locationId && $hasLocationColumn) {
                    $query->where('fulfillment_location_id', $locationId);
                }
            })
            ->with('product:id,name,image_path')
            ->groupBy('product_id')
            ->orderByDesc('total_sold')
            ->take(5)
            ->get();

        $profitFilters = array_filter([
            'payment_status' => 'paid',
            'location_id' => $locationId,
        ]);

        $todayProfitData = $this->profitService->getProfitReport(array_merge($profitFilters, [
            'start_date' => today()->format('Y-m-d'),
            'end_date' => today()->format('Y-m-d'),
        ]));

        $monthProfitData = $this->profitService->getProfitReport(array_merge($profitFilters, [
            'start_date' => now()->startOfMonth()->format('Y-m-d'),
            'end_date' => now()->format('Y-m-d'),
        ]));

        $totalProfitData = $this->profitService->getProfitReport($profitFilters);

        $topProfitableProducts = $this->profitService->getTopProfitableProducts(5, array_merge($profitFilters, [
            'start_date' => now()->subDays(30)->format('Y-m-d'),
            'end_date' => now()->format('Y-m-d'),
        ]));

        [$purchasePriceCoverage, $productsMissingPurchasePrice] = $this->purchasePriceCoverage();

        return [
            'todaySales' => $todaySales,
            'todayOrders' => $todayOrders,
            'monthSales' => $monthSales,
            'monthOrders' => $monthOrders,
            'salesGrowth' => $salesGrowth,
            'totalRevenue' => $totalRevenue,
            'totalOrders' => $totalOrders,
            'avgOrderValue' => $avgOrderValue,
            'pendingOrders' => $pendingOrders,
            'todayPosOrders' => $todayPosOrders,
            'todayOnlineOrders' => $todayOnlineOrders,
            'incomingTransfers' => $incomingTransfers,
            'lowStockProducts' => $lowStockProducts,
            'lowStockItems' => $lowStockItems,
            'ordersByStatus' => $ordersByStatus,
            'paymentStats' => $paymentStats,
            'revenueData' => $revenueData,
            'monthlyRevenueData' => $monthlyRevenueData,
            'topProducts' => $topProducts,
            'todayProfitData' => $todayProfitData,
            'monthProfitData' => $monthProfitData,
            'totalProfitData' => $totalProfitData,
            'topProfitableProducts' => $topProfitableProducts,
            'purchasePriceCoverage' => $purchasePriceCoverage,
            'productsMissingPurchasePrice' => $productsMissingPurchasePrice,
            'profitTrendData' => $this->profitTrend($locationId, $hasLocationColumn),
        ];
    }

    /**
     * @return array{0: int, 1: \Illuminate\Support\Collection}
     */
    protected function lowStock(?int $locationId): array
    {
        if ($locationId && Schema::hasTable('inventory_balances')) {
            $lowStockProducts = InventoryBalance::query()
                ->where('location_id', $locationId)
                ->whereRaw('(on_hand - reserved) <= ?', [10])
                ->count();

            $lowStockItems = InventoryBalance::query()
                ->with('product:id,name,image_path,is_active')
                ->where('location_id', $locationId)
                ->whereRaw('(on_hand - reserved) <= ?', [10])
                ->whereHas('product', fn ($q) => $q->where('is_active', true))
                ->orderByRaw('(on_hand - reserved) asc')
                ->take(5)
                ->get()
                ->map(function (InventoryBalance $balance) {
                    return (object) [
                        'id' => $balance->product_id,
                        'name' => $balance->product?->name,
                        'current_stock' => max(0, $balance->on_hand - $balance->reserved),
                        'image_path' => $balance->product?->image_path,
                    ];
                });

            return [$lowStockProducts, $lowStockItems];
        }

        $lowStockProducts = Product::where('current_stock', '<=', 10)->count();
        $lowStockItems = Product::where('current_stock', '<=', 10)
            ->where('is_active', true)
            ->select('id', 'name', 'current_stock', 'image_path')
            ->orderBy('current_stock')
            ->take(5)
            ->get();

        return [$lowStockProducts, $lowStockItems];
    }

    /**
     * @return array{0: float, 1: int}
     */
    protected function purchasePriceCoverage(): array
    {
        $activeSingleProductsCount = Product::active()
            ->whereNull('parent_id')
            ->where('product_type', 'single')
            ->count();

        $activeVariantChildrenCount = Product::active()
            ->whereNotNull('parent_id')
            ->count();

        $totalProductsCount = $activeSingleProductsCount + $activeVariantChildrenCount;

        $singleProductsWithPrice = Product::active()
            ->whereNull('parent_id')
            ->where('product_type', 'single')
            ->withPurchasePrice()
            ->count();

        $variantProductsWithPrice = Product::active()
            ->whereNotNull('parent_id')
            ->withPurchasePrice()
            ->count();

        $productsWithPurchasePrice = $singleProductsWithPrice + $variantProductsWithPrice;
        $purchasePriceCoverage = $totalProductsCount > 0
            ? round(($productsWithPurchasePrice / $totalProductsCount) * 100, 1)
            : 0;

        return [$purchasePriceCoverage, $totalProductsCount - $productsWithPurchasePrice];
    }

    /**
     * @return list<array{date: string, revenue: float, cogs: float, profit: float}>
     */
    protected function profitTrend(?int $locationId, bool $hasLocationColumn): array
    {
        try {
            $profitTrendQuery = OrderItem::query()
                ->whereHas('order', function ($query) use ($locationId, $hasLocationColumn) {
                    $query->where('created_at', '>=', now()->subDays(30))
                        ->where('payment_status', 'paid');
                    if ($locationId && $hasLocationColumn) {
                        $query->where('fulfillment_location_id', $locationId);
                    }
                })
                ->whereNotNull('purchase_price')
                ->select(
                    DB::raw('DATE(orders.created_at) as date'),
                    DB::raw('SUM(COALESCE((order_items.price * order_items.qty) - COALESCE(order_items.allocated_discount, 0), 0)) as revenue'),
                    DB::raw('SUM(COALESCE((order_items.purchase_price * order_items.qty) + COALESCE(order_items.allocated_extra_costs, 0), 0)) as cogs'),
                    DB::raw('SUM(COALESCE(((order_items.price * order_items.qty) - COALESCE(order_items.allocated_discount, 0)) - ((order_items.purchase_price * order_items.qty) + COALESCE(order_items.allocated_extra_costs, 0)), 0)) as profit')
                )
                ->join('orders', 'order_items.order_id', '=', 'orders.id');

            if ($locationId && $hasLocationColumn) {
                $profitTrendQuery->where('orders.fulfillment_location_id', $locationId);
            }

            return $profitTrendQuery
                ->groupBy('date')
                ->orderBy('date')
                ->get()
                ->map(fn ($item) => [
                    'date' => $item->date,
                    'revenue' => (float) $item->revenue,
                    'cogs' => (float) $item->cogs,
                    'profit' => (float) $item->profit,
                ])
                ->all();
        } catch (\Exception $e) {
            Log::error('Error generating profit trend data: '.$e->getMessage());

            return [];
        }
    }

    protected function scopedOrders(?int $locationId, bool $hasLocationColumn): Builder
    {
        $query = Order::query();

        if ($locationId && $hasLocationColumn) {
            $query->where('fulfillment_location_id', $locationId);
        }

        return $query;
    }
}