<?php

namespace Modules\SupplyChain\Services;

use App\Utils\ModuleUtil;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Modules\SupplyChain\Models\DemandForecast;

class DemandForecastService
{
    public function __construct(
        protected ModuleUtil $moduleUtil
    ) {
    }

    /**
     * Generate forecasts for all products with sales history.
     *
     * @return Collection<int, DemandForecast>
     */
    public function generateForBusiness(int $businessId, int $monthsAhead = 3): Collection
    {
        $months = (int) config('supplychain.forecast.moving_average_months', 3);
        $products = $this->getProductsWithSalesHistory($businessId, $months);

        $forecasts = collect();
        foreach ($products as $row) {
            for ($i = 1; $i <= $monthsAhead; $i++) {
                $forecastMonth = now()->addMonths($i)->startOfMonth();
                $forecasts->push($this->forecastProduct(
                    $businessId,
                    (int) $row->product_id,
                    $row->variation_id ? (int) $row->variation_id : null,
                    $forecastMonth,
                    $months
                ));
            }
        }

        return $forecasts;
    }

    public function forecastProduct(
        int $businessId,
        int $productId,
        ?int $variationId,
        Carbon $forecastMonth,
        ?int $movingAverageMonths = null
    ): DemandForecast {
        $months = $movingAverageMonths ?? (int) config('supplychain.forecast.moving_average_months', 3);
        $historyStart = $forecastMonth->copy()->subMonths($months)->startOfMonth();
        $historyEnd = $forecastMonth->copy()->subMonth()->endOfMonth();

        $salesQty = $this->getHistoricalSalesQty($businessId, $productId, $variationId, $historyStart, $historyEnd);
        $movingAvg = $months > 0 ? $salesQty / $months : 0;

        $crmQty = $this->getCrmPipelineQty($businessId, $productId, $forecastMonth);
        $crmWeight = (float) config('supplychain.forecast.crm_pipeline_weight', 0.25);
        $forecastQty = round($movingAvg + ($crmQty * $crmWeight), 4);

        $confidence = $this->calculateConfidence($salesQty, $crmQty, $months);

        return DemandForecast::updateOrCreate(
            [
                'business_id' => $businessId,
                'product_id' => $productId,
                'variation_id' => $variationId,
                'forecast_month' => $forecastMonth->toDateString(),
                'method' => 'moving_average',
            ],
            [
                'forecast_qty' => max(0, $forecastQty),
                'confidence' => $confidence,
                'source' => $crmQty > 0 ? 'sales_crm' : 'sales',
                'meta' => [
                    'moving_average_months' => $months,
                    'historical_sales_qty' => $salesQty,
                    'crm_pipeline_qty' => $crmQty,
                    'moving_average' => round($movingAvg, 4),
                ],
            ]
        );
    }

    /**
     * @return Collection<int, object>
     */
    protected function getProductsWithSalesHistory(int $businessId, int $months): Collection
    {
        $since = now()->subMonths($months)->startOfMonth();

        return DB::table('transaction_sell_lines')
            ->join('transactions', 'transactions.id', '=', 'transaction_sell_lines.transaction_id')
            ->where('transactions.business_id', $businessId)
            ->where('transactions.type', 'sell')
            ->where('transactions.status', 'final')
            ->where('transactions.transaction_date', '>=', $since)
            ->select('transaction_sell_lines.product_id', 'transaction_sell_lines.variation_id')
            ->groupBy('transaction_sell_lines.product_id', 'transaction_sell_lines.variation_id')
            ->get();
    }

    protected function getHistoricalSalesQty(
        int $businessId,
        int $productId,
        ?int $variationId,
        Carbon $start,
        Carbon $end
    ): float {
        $query = DB::table('transaction_sell_lines')
            ->join('transactions', 'transactions.id', '=', 'transaction_sell_lines.transaction_id')
            ->where('transactions.business_id', $businessId)
            ->where('transactions.type', 'sell')
            ->where('transactions.status', 'final')
            ->where('transaction_sell_lines.product_id', $productId)
            ->whereBetween('transactions.transaction_date', [$start, $end]);

        if ($variationId) {
            $query->where('transaction_sell_lines.variation_id', $variationId);
        }

        return (float) $query->sum('transaction_sell_lines.quantity');
    }

    protected function getCrmPipelineQty(int $businessId, int $productId, Carbon $forecastMonth): float
    {
        if (! $this->moduleUtil->isModuleInstalled('Crm')) {
            return 0.0;
        }

        $total = 0.0;

        if (Schema::hasTable('crm_company_requirements')) {
            $query = DB::table('crm_company_requirements')
                ->join('crm_companies', 'crm_companies.id', '=', 'crm_company_requirements.company_id')
                ->where('crm_companies.business_id', $businessId)
                ->where('crm_company_requirements.product_id', $productId);

            if (Schema::hasColumn('crm_company_requirements', 'status')) {
                $query->whereIn('crm_company_requirements.status', ['open', 'qualified', 'proposal']);
            }

            $total += (float) $query->sum(DB::raw('CAST(crm_company_requirements.quantity_estimate AS DECIMAL(22,4))'));
        }

        if (Schema::hasTable('crm_lead_products') && Schema::hasTable('crm_leads')) {
            $total += (float) DB::table('crm_lead_products')
                ->join('crm_leads', 'crm_leads.id', '=', 'crm_lead_products.lead_id')
                ->where('crm_leads.business_id', $businessId)
                ->where('crm_lead_products.product_id', $productId)
                ->whereNotIn('crm_leads.lead_status', ['converted', 'lost'])
                ->sum('crm_lead_products.quantity');
        }

        return $total;
    }

    protected function calculateConfidence(float $salesQty, float $crmQty, int $months): float
    {
        if ($salesQty <= 0 && $crmQty <= 0) {
            return 25.0;
        }

        $base = min(90.0, 40.0 + ($months * 10));
        if ($crmQty > 0) {
            $base = min(95.0, $base + 10);
        }
        if ($salesQty > 0) {
            $base = min(98.0, $base + 5);
        }

        return round($base, 2);
    }

    /**
     * Aggregate forecast qty by variation for MRP consumption.
     *
     * @return array<int, float> variation_id => qty
     */
    public function getForecastByVariation(int $businessId, ?Carbon $month = null): array
    {
        $month = ($month ?? now()->addMonth()->startOfMonth())->copy()->startOfMonth();

        $rows = DemandForecast::forBusiness($businessId)
            ->whereDate('forecast_month', $month->toDateString())
            ->whereNotNull('variation_id')
            ->get(['variation_id', 'forecast_qty']);

        $map = [];
        foreach ($rows as $row) {
            $map[(int) $row->variation_id] = ($map[(int) $row->variation_id] ?? 0) + (float) $row->forecast_qty;
        }

        return $map;
    }
}