<?php

namespace App\Services;

use App\Models\Sale;
use App\Models\Product;
use App\Models\ProductStock;
use App\Models\Expense;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class DashboardService
{
    private function cacheKey(string $name): string
    {
        $orgId    = app()->bound('current_organization_id') ? app('current_organization_id') : 0;
        $branchId = app()->bound('current_branch_id')       ? app('current_branch_id')       : 0;
        return "dashboard:{$orgId}:{$branchId}:{$name}";
    }

    public static function clearCache(int $orgId, int $branchId): void
    {
        $prefix = "dashboard:{$orgId}:{$branchId}:";
        foreach (['stats', 'period_comparison', 'category_performance',
                  'low_stock', 'out_of_stock', 'expiring_products',
                  'top_products_10', 'recent_transactions_10'] as $suffix) {
            Cache::forget($prefix . $suffix);
        }
    }

    public function getStats()
    {
        return Cache::remember($this->cacheKey('stats'), 120, function () {
            $branchId = app()->bound('current_branch_id') ? app('current_branch_id') : null;
            $orgId    = app()->bound('current_organization_id') ? app('current_organization_id') : null;

            if ($branchId) {
                $lowStockCount   = Product::withoutGlobalScope('branch')
                    ->where('track_stock', true)->where('low_stock_threshold', '>', 0)->active()
                    ->whereHas('stocks', fn ($q) => $q
                        ->where('branch_id', $branchId)->where('quantity', '>', 0)
                        ->whereRaw('quantity <= (SELECT low_stock_threshold FROM products WHERE id = product_stocks.product_id)'))
                    ->count();
                $outOfStockCount = Product::withoutGlobalScope('branch')
                    ->where('track_stock', true)->active()
                    ->whereHas('stocks', fn ($q) => $q->where('branch_id', $branchId)->where('quantity', '<=', 0))
                    ->count();
                // Exclude stocks belonging to soft-deleted products.
                $totalInventoryQty = ProductStock::where('branch_id', $branchId)
                    ->whereHas('product')
                    ->sum('quantity');
            } else {
                $lowStockCount   = Product::where('track_stock', true)->where('low_stock_threshold', '>', 0)->where('stock_quantity', '>', 0)->whereRaw('stock_quantity <= low_stock_threshold')->active()->count();
                $outOfStockCount = Product::where('track_stock', true)->where('stock_quantity', '<=', 0)->active()->count();
                // Sum products.stock_quantity — always the maintained aggregate,
                // org-scoped via BelongsToOrganization global scope.
                $totalInventoryQty = Product::active()->sum('stock_quantity');
            }

            return [
                'today_sales' => Sale::today()->active()->sum('amount_paid'),
                'today_transactions' => Sale::today()->active()->count(),
                'total_revenue' => Sale::active()->sum('amount_paid'),
                'low_stock_count'          => $lowStockCount,
                'out_of_stock_count'       => $outOfStockCount,
                'total_inventory_quantity' => (int) $totalInventoryQty,
                'expiring_soon_count'  => Product::active()->whereNotNull('expiry_date')->whereDate('expiry_date', '>=', now()->toDateString())->whereDate('expiry_date', '<=', now()->addDays(7)->toDateString())->count(),
                'expired_count'        => Product::active()->whereNotNull('expiry_date')->whereDate('expiry_date', '<', now()->toDateString())->count(),
                'total_products'       => Product::active()->count(),
                'total_customers' => \App\Models\Customer::active()->count(),
                'total_outstanding'    => Sale::hasOutstanding()->sum('outstanding_balance'),
                'partial_sales_count'  => Sale::partial()->count(),
                'monthly_expenditure'  => Expense::thisMonth()->sum('amount'),
            ];
        });
    }

    public function getSalesChart($startDate, $endDate)
    {
        $key = $this->cacheKey('sales_chart_' . md5("{$startDate}|{$endDate}"));
        return Cache::remember($key, 300, function () use ($startDate, $endDate) {
            $sales = Sale::whereBetween('sale_date', [$startDate, $endDate])
                ->active()
                ->selectRaw('DATE(sale_date) as date, SUM(amount_paid) as total, COUNT(*) as count')
                ->groupBy('date')
                ->orderBy('date')
                ->get();

            return [
                'dates' => $sales->pluck('date'),
                'totals' => $sales->pluck('total'),
                'counts' => $sales->pluck('count'),
            ];
        });
    }

    public function getRevenueVsProfit($days = 30)
    {
        return Cache::remember($this->cacheKey("revenue_profit_{$days}"), 300, function () use ($days) {
            $startDate = Carbon::now()->subDays($days)->startOfDay();
            $orgId    = app()->bound('current_organization_id') ? app('current_organization_id') : null;
            $branchId = app()->bound('current_branch_id')       ? app('current_branch_id')       : null;

            // Pure SQL aggregation — never loads Sale/Product models into PHP memory
            $rows = DB::table('sales')
                ->join('sale_items', 'sales.id', '=', 'sale_items.sale_id')
                ->join('products', 'sale_items.product_id', '=', 'products.id')
                ->where('sales.sale_date', '>=', $startDate)
                ->whereIn('sales.status', ['completed', 'partial'])
                ->when($orgId,    fn ($q) => $q->where('sales.organization_id', $orgId))
                ->when($branchId, fn ($q) => $q->where('sales.branch_id', $branchId))
                ->selectRaw('DATE(sales.sale_date) as date')
                ->selectRaw('SUM(sale_items.subtotal) as revenue')
                ->selectRaw('SUM(sale_items.quantity * COALESCE(products.cost_price, 0)) as cost')
                ->groupBy('date')
                ->orderBy('date')
                ->get();

            return [
                'dates'   => $rows->pluck('date'),
                'revenue' => $rows->map(fn ($r) => round((float) $r->revenue, 2))->values(),
                'cost'    => $rows->map(fn ($r) => round((float) $r->cost, 2))->values(),
                'profit'  => $rows->map(fn ($r) => round((float) $r->revenue - (float) $r->cost, 2))->values(),
            ];
        });
    }

    public function getPeriodComparison()
    {
        return Cache::remember($this->cacheKey('period_comparison'), 300, function () {
            $thisMonthStart = Carbon::now()->startOfMonth();
            $lastMonthStart = Carbon::now()->subMonth()->startOfMonth();
            $lastMonthEnd = Carbon::now()->subMonth()->endOfMonth();

            $thisMonth = Sale::where('sale_date', '>=', $thisMonthStart)
                ->active()
                ->selectRaw('SUM(amount_paid) as revenue, SUM(amount_paid - discount) as net_revenue, COUNT(*) as transactions')
                ->first();

            $lastMonth = Sale::whereBetween('sale_date', [$lastMonthStart, $lastMonthEnd])
                ->active()
                ->selectRaw('SUM(amount_paid) as revenue, SUM(amount_paid - discount) as net_revenue, COUNT(*) as transactions')
                ->first();

            $revenueChange = $lastMonth->revenue > 0
                ? (($thisMonth->revenue - $lastMonth->revenue) / $lastMonth->revenue) * 100
                : 0;

            $transactionsChange = $lastMonth->transactions > 0
                ? (($thisMonth->transactions - $lastMonth->transactions) / $lastMonth->transactions) * 100
                : 0;

            return [
                'this_month' => [
                    'revenue' => round($thisMonth->revenue ?? 0, 2),
                    'transactions' => $thisMonth->transactions ?? 0,
                ],
                'last_month' => [
                    'revenue' => round($lastMonth->revenue ?? 0, 2),
                    'transactions' => $lastMonth->transactions ?? 0,
                ],
                'changes' => [
                    'revenue_percentage' => round($revenueChange, 1),
                    'transactions_percentage' => round($transactionsChange, 1),
                ],
            ];
        });
    }

    public function getTopProducts($limit = 10)
    {
        return Cache::remember($this->cacheKey("top_products_{$limit}"), 300, function () use ($limit) {
            $orgId    = app()->bound('current_organization_id') ? app('current_organization_id') : null;
            $branchId = app()->bound('current_branch_id')       ? app('current_branch_id')       : null;

            return DB::table('products')
                ->join('sale_items', 'products.id', '=', 'sale_items.product_id')
                ->join('sales', function ($join) {
                    $join->on('sale_items.sale_id', '=', 'sales.id')
                         ->whereIn('sales.status', ['completed', 'partial']);
                })
                ->select(
                    'products.id',
                    'products.name',
                    'products.sku',
                    'products.selling_price',
                    'products.stock_quantity',
                    DB::raw('SUM(sale_items.quantity) as total_sold'),
                    DB::raw('SUM(sale_items.subtotal) as total_revenue')
                )
                ->when($orgId, fn ($q) => $q->where('products.organization_id', $orgId))
                ->when($branchId, fn ($q) => $q->where('sales.branch_id', $branchId))
                ->whereNull('products.deleted_at')
                ->groupBy('products.id', 'products.name', 'products.sku', 'products.selling_price', 'products.stock_quantity')
                ->orderByDesc('total_sold')
                ->limit($limit)
                ->get();
        });
    }

    /**
     * Combined summary: returns all dashboard data in one call.
     * The frontend can hit a single endpoint instead of 7 parallel requests,
     * reducing middleware overhead (auth + tenant + branch checks) by ~6x.
     */
    public function getSummary(): array
    {
        return [
            'stats'               => $this->getStats(),
            'recent_transactions' => $this->getRecentTransactions(5),
            'low_stock'           => $this->getLowStockProducts(),
            'out_of_stock'        => $this->getOutOfStockProducts(),
            'expiry_alerts'       => $this->getExpiringProducts(),
            'period_comparison'   => $this->getPeriodComparison(),
            'revenue_vs_profit'   => $this->getRevenueVsProfit(30),
            'top_products'        => $this->getTopProducts(10),
            'category_performance'=> $this->getCategoryPerformance(),
        ];
    }

    public function getCategoryPerformance()
    {
        return Cache::remember($this->cacheKey('category_performance'), 300, function () {
            // Raw DB::table() bypasses Eloquent global scopes, so we explicitly
            // filter by the current tenant's organization_id and active branch.
            $orgId    = app()->bound('current_organization_id') ? app('current_organization_id') : null;
            $branchId = app()->bound('current_branch_id')       ? app('current_branch_id')       : null;

            return DB::table('categories')
                ->join('products', 'categories.id', '=', 'products.category_id')
                ->join('sale_items', 'products.id', '=', 'sale_items.product_id')
                // Count items from completed and partial sales
                ->join('sales', function ($join) {
                    $join->on('sale_items.sale_id', '=', 'sales.id')
                         ->whereIn('sales.status', ['completed', 'partial']);
                })
                ->select(
                    'categories.name',
                    DB::raw('SUM(sale_items.subtotal) as revenue'),
                    DB::raw('SUM(sale_items.quantity) as units_sold')
                )
                ->when($orgId, fn ($q) => $q
                    ->where('categories.organization_id', $orgId)
                    ->where('products.organization_id', $orgId)
                    ->where('sales.organization_id', $orgId)
                )
                ->when($branchId, fn ($q) => $q->where('sales.branch_id', $branchId))
                ->groupBy('categories.id', 'categories.name')
                ->orderByDesc('revenue')
                ->limit(10)
                ->get();
        });
    }

    public function getRecentTransactions($limit = 10)
    {
        return Cache::remember($this->cacheKey("recent_transactions_{$limit}"), 60, function () use ($limit) {
            return Sale::with(['customer', 'user', 'items'])
                ->latest()
                ->limit($limit)
                ->get();
        });
    }

    public function getLowStockProducts()
    {
        return Cache::remember($this->cacheKey('low_stock'), 300, function () {
            $branchId = app()->bound('current_branch_id') ? app('current_branch_id') : null;

            if ($branchId) {
                $products = Product::withoutGlobalScope('branch')
                    ->with('category')
                    ->where('track_stock', true)
                    ->where('low_stock_threshold', '>', 0)
                    ->whereHas('stocks', function ($q) use ($branchId) {
                        $q->where('branch_id', $branchId)
                          ->where('quantity', '>', 0)
                          ->whereRaw('quantity <= (SELECT low_stock_threshold FROM products WHERE id = product_stocks.product_id)');
                    })
                    ->active()
                    ->orderBy('stock_quantity')
                    ->get(['id', 'name', 'sku', 'unit', 'stock_quantity', 'low_stock_threshold', 'category_id']);

                // Replace aggregate stock_quantity with branch-specific quantity for accurate display.
                $branchStocks = ProductStock::where('branch_id', $branchId)
                    ->whereIn('product_id', $products->pluck('id'))
                    ->pluck('quantity', 'product_id');
                $products->each(fn ($p) => $p->stock_quantity = $branchStocks->get($p->id, 0));

                return $products;
            }

            return Product::with('category')
                ->where('track_stock', true)
                ->where('low_stock_threshold', '>', 0)
                ->where('stock_quantity', '>', 0)
                ->whereRaw('stock_quantity <= low_stock_threshold')
                ->active()
                ->orderBy('stock_quantity')
                ->get(['id', 'name', 'sku', 'unit', 'stock_quantity', 'low_stock_threshold', 'category_id']);
        });
    }

    public function getOutOfStockProducts()
    {
        return Cache::remember($this->cacheKey('out_of_stock'), 300, function () {
            $branchId = app()->bound('current_branch_id') ? app('current_branch_id') : null;

            if ($branchId) {
                $products = Product::withoutGlobalScope('branch')
                    ->with('category')
                    ->where('track_stock', true)
                    ->whereHas('stocks', function ($q) use ($branchId) {
                        $q->where('branch_id', $branchId)->where('quantity', '<=', 0);
                    })
                    ->active()
                    ->orderBy('name')
                    ->get(['id', 'name', 'sku', 'unit', 'stock_quantity', 'category_id']);

                // Show branch-specific stock_quantity (0 for out-of-stock items).
                $branchStocks = ProductStock::where('branch_id', $branchId)
                    ->whereIn('product_id', $products->pluck('id'))
                    ->pluck('quantity', 'product_id');
                $products->each(fn ($p) => $p->stock_quantity = $branchStocks->get($p->id, 0));

                return $products;
            }

            return Product::with('category')
                ->where('track_stock', true)
                ->where('stock_quantity', '<=', 0)
                ->active()
                ->orderBy('name')
                ->get(['id', 'name', 'sku', 'unit', 'stock_quantity', 'category_id']);
        });
    }

    public function getExpiringProducts()
    {
        return Cache::remember($this->cacheKey('expiring_products'), 300, function () {
            $today   = now()->toDateString();
            $in7days = now()->addDays(7)->toDateString();

            $expired = Product::with('category')
                ->active()
                ->whereNotNull('expiry_date')
                ->whereDate('expiry_date', '<', $today)
                ->orderBy('expiry_date')
                ->get(['id', 'name', 'sku', 'unit', 'stock_quantity', 'expiry_date', 'category_id']);

            $expiringSoon = Product::with('category')
                ->active()
                ->whereNotNull('expiry_date')
                ->whereDate('expiry_date', '>=', $today)
                ->whereDate('expiry_date', '<=', $in7days)
                ->orderBy('expiry_date')
                ->get(['id', 'name', 'sku', 'unit', 'stock_quantity', 'expiry_date', 'category_id']);

            return [
                'expired'       => $expired,
                'expiring_soon' => $expiringSoon,
            ];
        });
    }
}
