<?php

namespace App\Services;

use App\Models\Purchase;
use App\Models\Sale;
use App\Models\Yarn;
use App\Models\Account;
use App\Models\PurchaseItem;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class DashboardService
{
    public function getSummary(): array
    {
        $today = Carbon::today();
        $startOfMonth = Carbon::now()->startOfMonth();
        $startOfLastMonth = Carbon::now()->subMonth()->startOfMonth();
        $endOfLastMonth = Carbon::now()->subMonth()->endOfMonth();

        $totalReceivable = (float) Sale::sum('grand_total') - (float) Sale::sum('paid_amount');
        $totalPayable = (float) Purchase::sum('grand_total') - (float) Purchase::sum('paid_amount');

        return [
            'today_purchase'      => (float) Purchase::whereDate('purchase_date', $today)->sum('grand_total'),
            'today_sales'         => (float) Sale::whereDate('sales_date', $today)->sum('grand_total'),
            'current_stock'        => $this->getTotalStock(),
            'stock_value'          => $this->getTotalStockValue(),
            'today_profit'        => $this->getTodayProfit(),
            'accounts_receivable'  => $totalReceivable,
            'accounts_payable'    => $totalPayable,
            'cash_balance'        => $this->getCashBalance(),
            'last_month_purchase'  => (float) Purchase::whereBetween('purchase_date', [$startOfLastMonth, $endOfLastMonth])->sum('grand_total'),
            'last_month_sales'    => (float) Sale::whereBetween('sales_date', [$startOfLastMonth, $endOfLastMonth])->sum('grand_total'),
            'this_month_purchase'  => (float) Purchase::whereBetween('purchase_date', [$startOfMonth, Carbon::now()])->sum('grand_total'),
            'this_month_sales'    => (float) Sale::whereBetween('sales_date', [$startOfMonth, Carbon::now()])->sum('grand_total'),
        ];
    }

    public function getTotalStock(): float
    {
        $in  = (float) DB::table('stock_ledgers')->sum('in_qty');
        $out = (float) DB::table('stock_ledgers')->sum('out_qty');
        return $in - $out;
    }

    public function getTotalStockValue(): float
    {
        $total = 0;
        foreach (Yarn::where('is_active', true)->get() as $yarn) {
            $total += (float) $yarn->stock_value;
        }
        return $total;
    }

    public function getTodayProfit(): float
    {
        $today = Carbon::today()->toDateString();
        $salesRevenue = (float) Sale::whereDate('sales_date', $today)->sum('grand_total');

        $cost = 0;
        $qtyByYarn = DB::table('sale_items')
            ->join('sales', 'sales.id', '=', 'sale_items.sale_id')
            ->whereDate('sales.sales_date', $today)
            ->groupBy('sale_items.yarn_id')
            ->select('sale_items.yarn_id', DB::raw('SUM(sale_items.quantity) as total_qty'))
            ->get();

        foreach ($qtyByYarn as $row) {
            $avgRate = (float) PurchaseItem::where('yarn_id', $row->yarn_id)->avg('rate');
            $cost += $row->total_qty * $avgRate;
        }

        return $salesRevenue - $cost;
    }

    public function getCashBalance(): float
    {
        $cash = Account::where('type', 'cash')->first();
        return $cash ? (float) $cash->balance : 0;
    }

    public function getBankBalance(): float
    {
        $total = 0;
        foreach (Account::where('type', 'bank')->get() as $a) {
            $total += (float) $a->balance;
        }
        return $total;
    }

    public function getMonthlyChartData(): array
    {
        $months = [];
        $purchaseData = [];
        $salesData = [];

        for ($i = 11; $i >= 0; $i--) {
            $date = Carbon::now()->subMonths($i);
            $months[] = $date->format('M');
            $purchaseData[] = (float) Purchase::whereYear('purchase_date', $date->year)->whereMonth('purchase_date', $date->month)->sum('grand_total');
            $salesData[] = (float) Sale::whereYear('sales_date', $date->year)->whereMonth('sales_date', $date->month)->sum('grand_total');
        }

        return ['categories' => $months, 'purchase' => $purchaseData, 'sales' => $salesData];
    }

    public function getStockMovement(): array
    {
        $startOfMonth = Carbon::now()->startOfMonth()->toDateString();
        $today = Carbon::today()->toDateString();

        return [
            'inflow'   => (float) DB::table('stock_ledgers')->where('type', 'purchase')->whereBetween('transaction_date', [$startOfMonth, $today])->sum('in_qty'),
            'outflow'  => (float) DB::table('stock_ledgers')->where('type', 'sale')->whereBetween('transaction_date', [$startOfMonth, $today])->sum('out_qty'),
            'reserved' => 0,
        ];
    }

    public function getRecentPurchases($limit = 5)
    {
        return Purchase::with(['party', 'items.yarn'])->latest('purchase_date')->limit($limit)->get();
    }

    public function getRecentSales($limit = 5)
    {
        return Sale::with(['party', 'items.yarn'])->latest('sales_date')->limit($limit)->get();
    }

    public function getProfitAnalysis(): array
    {
        $months = [];
        $profitData = [];

        for ($i = 6; $i >= 0; $i--) {
            $date = Carbon::now()->subMonths($i);
            $months[] = $date->format('M');
            $revenue = (float) Sale::whereYear('sales_date', $date->year)->whereMonth('sales_date', $date->month)->sum('grand_total');
            $cost = (float) Purchase::whereYear('purchase_date', $date->year)->whereMonth('purchase_date', $date->month)->sum('grand_total');
            $profitData[] = $revenue - $cost;
        }

        return ['categories' => $months, 'profit' => $profitData];
    }

    public function getAiInsights(): array
    {
        return [
            'low_stock'    => $this->getLowStockItems(),
            'top_selling'  => $this->getTopSellingYarns(3),
            'slow_moving'  => $this->getSlowMovingYarns(3),
            'month_change'  => $this->getMonthOverMonthChange(),
        ];
    }

    private function getLowStockItems($limit = 5)
    {
        $items = [];
        foreach (Yarn::where('is_active', true)->get() as $yarn) {
            if ($yarn->stock_status !== 'in_stock') {
                $items[] = $yarn;
            }
            if (count($items) >= $limit) break;
        }
        return collect($items);
    }

    public function getTopSellingYarns($limit = 3)
    {
        return DB::table('sale_items')
            ->join('yarns', 'yarns.id', '=', 'sale_items.yarn_id')
            ->groupBy('yarns.id', 'yarns.name')
            ->selectRaw('yarns.id, yarns.name, SUM(sale_items.quantity) as total_qty, SUM(sale_items.amount) as total_sales')
            ->orderByDesc('total_sales')
            ->limit($limit)
            ->get();
    }

    public function getSlowMovingYarns($limit = 3)
    {
        return DB::table('yarns')
            ->leftJoin('sale_items', 'sale_items.yarn_id', '=', 'yarns.id')
            ->where('yarns.is_active', true)
            ->groupBy('yarns.id', 'yarns.name')
            ->selectRaw('yarns.id, yarns.name, COALESCE(SUM(sale_items.quantity), 0) as total_qty')
            ->orderBy('total_qty', 'asc')
            ->limit($limit)
            ->get();
    }

    public function getMonthOverMonthChange(): array
    {
        $current = (float) Sale::whereMonth('sales_date', date('m'))->whereYear('sales_date', date('Y'))->sum('grand_total');
        $prevMonth = date('m') == 1 ? 12 : date('m') - 1;
        $prevYear  = date('m') == 1 ? date('Y') - 1 : date('Y');
        $previous = (float) Sale::whereMonth('sales_date', $prevMonth)->whereYear('sales_date', $prevYear)->sum('grand_total');

        $change = $previous > 0 ? (($current - $previous) / $previous) * 100 : 0;
        return ['percent' => round(abs($change), 1), 'direction' => $change >= 0 ? 'up' : 'down'];
    }
}
