<?php

namespace Modules\PurchaseRequisition\Entities\Services;

use DB;
use App\VariationLocationDetails;
use App\PurchaseLine;

class CalculationService
{
    /**
     * Calculate covered quantity for a product across ALL locations.
     * Formula: Covered QTY = (((Total Sold - Total Return) * Stock Cover Interval) / Selling Interval)
     *
     * @param int $productId
     * @param int $variationId
     * @param int $businessId
     * @param array $settings
     * @return float
     */
    public function calculateCoveredQuantityAllLocations($productId, $variationId, $businessId, $settings)
    {
        $sellingInterval = $settings['selling_interval'] ?? 365;
        $stockCoverInterval = $settings['stock_cover_interval'] ?? 180;

        // Calculate the date range based on selling interval
        $startDate = now()->subDays($sellingInterval)->format('Y-m-d');

        // Get sales data for the product across all locations of the business
        $salesData = DB::table('transaction_sell_lines as tsl')
            ->join('transactions as t', 't.id', '=', 'tsl.transaction_id')
            ->join('business_locations as bl', 'bl.id', '=', 't.location_id')
            ->where('bl.business_id', $businessId)
            ->where('tsl.product_id', $productId)
            ->where('tsl.variation_id', $variationId)
            ->where('t.type', 'sell')
            ->where('t.status', 'final')
            ->where('t.transaction_date', '>=', $startDate)
            ->selectRaw('
                COALESCE(SUM(tsl.quantity), 0) as total_sold,
                COALESCE(SUM(tsl.quantity_returned), 0) as total_return
            ')
            ->first();

        $totalSold = $salesData->total_sold ?? 0;
        $totalReturn = $salesData->total_return ?? 0;
        $netSold = $totalSold - $totalReturn;

        // Calculate covered quantity
        // Formula: (Net Sold / Selling Interval) * Stock Cover Interval
        if ($sellingInterval == 0) {
            return 0;
        }

        // Daily sales rate * stock cover days = how much we need to cover
        $dailySalesRate = $netSold / $sellingInterval;
        $coveredQty = $dailySalesRate * $stockCoverInterval;

        return max(0, round($coveredQty, 2));
    }

    /**
     * Calculate covered quantity for a product at a specific location.
     *
     * @param int $productId
     * @param int $variationId
     * @param int $locationId
     * @param array $settings
     * @return float
     */
    public function calculateCoveredQuantity($productId, $variationId, $locationId, $settings)
    {
        $sellingInterval = $settings['selling_interval'] ?? 365;
        $stockCoverInterval = $settings['stock_cover_interval'] ?? 180;

        $startDate = now()->subDays($sellingInterval)->format('Y-m-d');

        // Get sales data for the product
        $salesData = DB::table('transaction_sell_lines as tsl')
            ->join('transactions as t', 't.id', '=', 'tsl.transaction_id')
            ->where('t.location_id', $locationId)
            ->where('tsl.product_id', $productId)
            ->where('tsl.variation_id', $variationId)
            ->where('t.type', 'sell')
            ->where('t.status', 'final')
            ->where('t.transaction_date', '>=', $startDate)
            ->selectRaw('
                COALESCE(SUM(tsl.quantity), 0) as total_sold,
                COALESCE(SUM(tsl.quantity_returned), 0) as total_return
            ')
            ->first();

        $totalSold = $salesData->total_sold ?? 0;
        $totalReturn = $salesData->total_return ?? 0;
        $netSold = $totalSold - $totalReturn;

        if ($sellingInterval == 0) {
            return 0;
        }

        $dailySalesRate = $netSold / $sellingInterval;
        $coveredQty = $dailySalesRate * $stockCoverInterval;

        return max(0, round($coveredQty, 2));
    }

    /**
     * Get current stock for a variation across ALL locations.
     *
     * @param int $variationId
     * @param int $businessId
     * @return float
     */
    public function getCurrentStockAllLocations($variationId, $businessId)
    {
        $stockData = VariationLocationDetails::join('business_locations', 'business_locations.id', '=', 'variation_location_details.location_id')
            ->where('variation_location_details.variation_id', $variationId)
            ->where('business_locations.business_id', $businessId)
            ->sum('variation_location_details.qty_available');

        return $stockData ?? 0;
    }

    /**
     * Get current stock for a variation at a location.
     *
     * @param int $variationId
     * @param int $locationId
     * @return float
     */
    public function getCurrentStock($variationId, $locationId)
    {
        $stockData = VariationLocationDetails::where('variation_id', $variationId)
            ->where('location_id', $locationId)
            ->first();

        return $stockData ? $stockData->qty_available : 0;
    }

    /**
     * Get pending purchase quantity for a variation across ALL locations.
     * Counts purchases that are ordered but not yet received.
     *
     * @param int $variationId
     * @param int $businessId
     * @return float
     */
    public function getPendingPurchaseQtyAllLocations($variationId, $businessId)
    {
        $pendingQty = PurchaseLine::join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
            ->join('business_locations', 'business_locations.id', '=', 'transactions.location_id')
            ->where('purchase_lines.variation_id', $variationId)
            ->where('business_locations.business_id', $businessId)
            ->where('transactions.type', 'purchase')
            ->whereIn('transactions.status', ['ordered', 'pending', 'partial'])
            ->selectRaw('COALESCE(SUM(purchase_lines.quantity - purchase_lines.quantity_returned), 0) as pending')
            ->value('pending');

        return $pendingQty ?? 0;
    }

    /**
     * Get pending purchase quantity for a variation at a location.
     *
     * @param int $variationId
     * @param int $locationId
     * @return float
     */
    public function getPendingPurchaseQty($variationId, $locationId)
    {
        $pendingQty = PurchaseLine::join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
            ->where('purchase_lines.variation_id', $variationId)
            ->where('transactions.location_id', $locationId)
            ->where('transactions.type', 'purchase')
            ->whereIn('transactions.status', ['ordered', 'partial'])
            ->selectRaw('COALESCE(SUM(purchase_lines.quantity - purchase_lines.quantity_returned), 0) as pending')
            ->value('pending');

        return $pendingQty ?? 0;
    }

    /**
     * Calculate required quantity.
     * Formula: Required Qty = Covered QTY - (Current Stock + Pending Purchase Qty)
     *
     * @param float $coveredQty
     * @param float $currentStock
     * @param float $pendingPurchaseQty
     * @return float
     */
    public function calculateRequiredQuantity($coveredQty, $currentStock, $pendingPurchaseQty)
    {
        $requiredQty = $coveredQty - ($currentStock + $pendingPurchaseQty);

        // Round up to next whole number (e.g., 2.43 → 3, 2.96 → 3)
        return max(0, ceil($requiredQty));
    }

    /**
     * Calculate all quantities for a product variation across ALL locations.
     *
     * @param int $productId
     * @param int $variationId
     * @param int $businessId
     * @param array $settings
     * @return array
     */
    public function calculateProductQuantitiesAllLocations($productId, $variationId, $businessId, $settings)
    {
        $coveredQty = $this->calculateCoveredQuantityAllLocations($productId, $variationId, $businessId, $settings);
        $currentStock = $this->getCurrentStockAllLocations($variationId, $businessId);
        $pendingPurchaseQty = $this->getPendingPurchaseQtyAllLocations($variationId, $businessId);
        $requiredQty = $this->calculateRequiredQuantity($coveredQty, $currentStock, $pendingPurchaseQty);

        return [
            'covered_qty' => $coveredQty,
            'current_stock' => $currentStock,
            'pending_purchase_qty' => $pendingPurchaseQty,
            'required_quantity' => $requiredQty,
        ];
    }

    /**
     * Calculate all quantities for a product variation at a specific location.
     *
     * @param int $productId
     * @param int $variationId
     * @param int $locationId
     * @param array $settings
     * @return array
     */
    public function calculateProductQuantities($productId, $variationId, $locationId, $settings)
    {
        $coveredQty = $this->calculateCoveredQuantity($productId, $variationId, $locationId, $settings);
        $currentStock = $this->getCurrentStock($variationId, $locationId);
        $pendingPurchaseQty = $this->getPendingPurchaseQty($variationId, $locationId);
        $requiredQty = $this->calculateRequiredQuantity($coveredQty, $currentStock, $pendingPurchaseQty);

        return [
            'covered_qty' => $coveredQty,
            'current_stock' => $currentStock,
            'pending_purchase_qty' => $pendingPurchaseQty,
            'required_quantity' => $requiredQty,
        ];
    }

    /**
     * Convert currency to EGP.
     *
     * @param float $amount
     * @param int $currencyId
     * @return float
     */
    public function convertToEgp($amount, $currencyId = null)
    {
        if (!$currencyId) {
            return $amount;
        }

        $currency = \Modules\PurchaseRequisition\Entities\PrCurrency::find($currencyId);

        if (!$currency) {
            return $amount;
        }

        return $amount * $currency->conversion_rate_to_egp;
    }

    /**
     * Calculate margin percentage.
     * Formula: ((Selling Price - Converted Price) / Converted Price) × 100
     *
     * @param float $sellingPrice
     * @param float $convertedPrice
     * @return float
     */
    public function calculateMargin($sellingPrice, $convertedPrice)
    {
        if ($convertedPrice == 0) {
            return 100;
        }

        $margin = (($sellingPrice - $convertedPrice) / $convertedPrice) * 100;

        return round($margin, 2);
    }

    /**
     * Get average unit cost for a variation across ALL locations.
     * Uses weighted average: SUM(price * net_qty) / SUM(net_qty)
     * where net_qty = quantity - quantity_returned
     *
     * @param int $variationId
     * @param int $businessId
     * @return float
     */
    public function getAverageUnitCostAllLocations($variationId, $businessId)
    {
        $result = PurchaseLine::join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
            ->join('business_locations', 'business_locations.id', '=', 'transactions.location_id')
            ->where('purchase_lines.variation_id', $variationId)
            ->where('business_locations.business_id', $businessId)
            ->where('transactions.type', 'purchase')
            ->where('transactions.status', 'received')
            ->selectRaw('
                CASE
                    WHEN SUM(purchase_lines.quantity - COALESCE(purchase_lines.quantity_returned, 0)) > 0
                    THEN SUM(purchase_lines.purchase_price * (purchase_lines.quantity - COALESCE(purchase_lines.quantity_returned, 0))) / SUM(purchase_lines.quantity - COALESCE(purchase_lines.quantity_returned, 0))
                    ELSE 0
                END as avg_cost
            ')
            ->first();

        return round($result->avg_cost ?? 0, 2);
    }

    /**
     * Get average unit cost for a variation at a location.
     * Uses weighted average: SUM(price * net_qty) / SUM(net_qty)
     * where net_qty = quantity - quantity_returned
     *
     * @param int $variationId
     * @param int $locationId
     * @return float
     */
    public function getAverageUnitCost($variationId, $locationId)
    {
        $result = PurchaseLine::join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
            ->where('purchase_lines.variation_id', $variationId)
            ->where('transactions.location_id', $locationId)
            ->where('transactions.type', 'purchase')
            ->where('transactions.status', 'received')
            ->selectRaw('
                CASE
                    WHEN SUM(purchase_lines.quantity - COALESCE(purchase_lines.quantity_returned, 0)) > 0
                    THEN SUM(purchase_lines.purchase_price * (purchase_lines.quantity - COALESCE(purchase_lines.quantity_returned, 0))) / SUM(purchase_lines.quantity - COALESCE(purchase_lines.quantity_returned, 0))
                    ELSE 0
                END as avg_cost
            ')
            ->first();

        return round($result->avg_cost ?? 0, 2);
    }

    /**
     * Get last purchase price for a variation across ALL locations.
     *
     * @param int $variationId
     * @param int $businessId
     * @return float
     */
    public function getLastPurchasePriceAllLocations($variationId, $businessId)
    {
        $lastPrice = PurchaseLine::join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
            ->join('business_locations', 'business_locations.id', '=', 'transactions.location_id')
            ->where('purchase_lines.variation_id', $variationId)
            ->where('business_locations.business_id', $businessId)
            ->where('transactions.type', 'purchase')
            ->where('transactions.status', 'received')
            ->orderBy('transactions.transaction_date', 'desc')
            ->value('purchase_lines.purchase_price');

        return round($lastPrice ?? 0, 2);
    }

    /**
     * Get last purchase price for a variation at a location.
     *
     * @param int $variationId
     * @param int $locationId
     * @return float
     */
    public function getLastPurchasePrice($variationId, $locationId)
    {
        $lastPrice = PurchaseLine::join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
            ->where('purchase_lines.variation_id', $variationId)
            ->where('transactions.location_id', $locationId)
            ->where('transactions.type', 'purchase')
            ->where('transactions.status', 'received')
            ->orderBy('transactions.transaction_date', 'desc')
            ->value('purchase_lines.purchase_price');

        return round($lastPrice ?? 0, 2);
    }

    /**
     * Check if product has previous purchases.
     *
     * @param int $productId
     * @param int $variationId
     * @return bool
     */
    public function hasPreviousPurchase($productId, $variationId)
    {
        $count = PurchaseLine::join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
            ->where('purchase_lines.product_id', $productId)
            ->where('purchase_lines.variation_id', $variationId)
            ->where('transactions.type', 'purchase')
            ->where('transactions.status', 'received')
            ->count();

        return $count > 0;
    }

    /**
     * Check if product has any opening stock entry.
     *
     * @param int $productId
     * @param int $variationId
     * @return bool
     */
    public function hasOpeningStock($productId, $variationId)
    {
        $count = PurchaseLine::join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
            ->where('purchase_lines.product_id', $productId)
            ->where('purchase_lines.variation_id', $variationId)
            ->where('transactions.type', 'opening_stock')
            ->count();

        return $count > 0;
    }
}
