<?php

namespace PortalPeople\shippingcosts\services;

use Yii;
use PortalPeople\shippingcosts\models\Shippingcosts;
use backend\models\Accounts;
use backend\models\Cartsbe;
use yii\db\Expression;

/**
 * ShippingCostCalculatorService
 *
 * Handles core shipping cost calculation logic extracted from Shippingcosts model.
 * Manages pallet calculations, haulier lookups, and cost determination.
 */
class ShippingCostCalculatorService
{
    const DefaultHaulierID = 38051; //Palletforce Example haulier ID for volume discount

    /**
     * Calculate shipping costs for a cart
     *
     * @param string $cartId Cart ID to calculate costs for
     * @param string|null $method If null, returns summary; otherwise returns detailed data
     * @return array|null Shipping costs broken down by haulier
     */
    public static function calculateCartCosts($cartId, $method = null): ?array
    {
        $cart = Cartsbe::findOne(['id' => $cartId]);
        if (!$cart) {
            return null;
        }

        $fullpostcode = $cart->fulladdresses['shipping']->postcode ?? '';
        $area = substr($fullpostcode, -3, 3);
        $postcode = str_replace([$area, ' '], ['', ''], $fullpostcode);

        $out = [];

        // Calculate pallet dimensions from cart items
        $palletConfig = self::calculatePalletConfig($cart);
        // Process each delivery cart line
        foreach ($cart->deliveryCartlines as $cartline) {
            $out = self::processCartLine(
                $cartline,
                $cart,
                $postcode,
                $fullpostcode,
                $palletConfig,
                $out
            );
        }

        // Calculate final totals and discounts
        return self::finalizeCostsOutput($out, $method);
    }

    /**
     * Calculate pallet configuration details from cart items
     *
     * @param Cartsbe $cart Cart to analyze
     * @return array Configuration with pallet size, count, items per pallet, and sleepers
     */
    private static function calculatePalletConfig($cart): array
    {
        $config = [
            'pallet_size' => 0.0,
            'pallet_count' => 0.0,
            'items_per_pallet' => 0.0,
            'sleepers' => 0,
        ];

        foreach ($cart->cartlines as $cartline) {
            if ($cartline->product->data->shipping->number_on_pallet > 1) {
                $qty = $cartline->erpqty;

                $config['pallet_size'] = max(
                    $config['pallet_size'],
                    $cartline->product->data->shipping->size_of_pallet
                );

                $config['pallet_count'] = max(
                    $config['pallet_count'],
                    $cartline->product->data->shipping->number_of_pallets
                );

                $config['items_per_pallet'] = max(
                    $config['items_per_pallet'],
                    $cartline->product->data->shipping->number_on_pallet
                );
            }
        }

        // Calculate sleepers if multi-pallet config
        if ($config['pallet_count'] > 1) {
            foreach ($cart->cartlines as $cartline) {
                if (
                    $cartline->product->data->shipping->number_on_pallet > 1 &&
                    $cartline->product->data->shipping->pallet_split == 1
                ) {
                    $qty = $cartline->erpqty;

                    if ($cartline->product->data->shipping->number_of_pallets > 1) {
                        $config['sleepers'] += $qty;
                    } else {
                        $config['sleepers'] += $qty / 2;
                    }
                }
            }
        }

        return $config;
    }

    /**
     * Process a single cart line and lookup corresponding costs
     *
     * @param mixed $cartline Cart line item
     * @param mixed $cart Full cart object
     * @param string $postcode Postcode (without area)
     * @param string $fullpostcode Full postcode
     * @param array $palletConfig Pallet configuration
     * @param array $out Accumulator for output
     * @return array Updated output accumulator
     */
    private static function processCartLine($cartline, $cart, $postcode, $fullpostcode, $palletConfig, $out): array
    {
        // Skip non-shippable items
        if (
            $cartline->product->data->shipping->number_on_pallet <= 0.00 ||
            in_array($cartline->product_name, ['Delivery', 'Collection'])
        ) {
            return $out;
        }

        $qty = $cartline->qty;
        $haulier = $cartline->haulierint;

        // Determine pallet configuration and sizing
        $palletInfo = self::calculatePalletInfo($qty, $cartline, $palletConfig);

        // Look up shipping costs for this configuration
        $out = self::findAndAddCosts(
            $out,
            $palletInfo,
            $haulier,
            $postcode,
            $fullpostcode,
            $cart,
            $cartline,
            $qty
        );
        return $out;
    }

    /**
     * Calculate pallet information for a cart line
     *
     * @param float $qty Quantity
     * @param mixed $cartline Cart line object
     * @param array $palletConfig Pallet configuration
     * @return array Pallet info with size and count
     */
    private static function calculatePalletInfo($qty, $cartline, $palletConfig): array
    {
        $pallets = 0;
        $size = 0;
        $shipping = $cartline->product->data->shipping;
        if (
            $shipping->size_of_pallet == 1.00 ||
            $shipping->size_of_pallet == 0
        ) {
            $pallets = $shipping->number_on_pallet > 0 ?
                ceil($qty / $shipping->number_on_pallet) : $qty;

            if ($shipping->number_of_pallets > 1) {
                $pallets = $pallets * $shipping->number_of_pallets;
            }

            $size = (float) $pallets;
        } elseif ($palletConfig['sleepers'] > 0) {
            $pallets = $palletConfig['items_per_pallet'] > 0 ?
                ceil($palletConfig['sleepers'] / $palletConfig['items_per_pallet']) :
                $palletConfig['sleepers'];

            if ($palletConfig['pallet_count'] > 1) {
                $pallets = $pallets * $palletConfig['pallet_count'];
            }

            $size = $palletConfig['pallet_size'];
        } else {
            $pallets = $shipping->number_on_pallet > 0 ?
                ceil($qty / $shipping->number_on_pallet) : $qty;

            if ($shipping->number_of_pallets > 1) {
                $pallets = $pallets * $shipping->number_of_pallets;
            }

            $size = $shipping->size_of_pallet;
        }

        return [
            'size' => $size,
            'count' => (float) number_format($pallets, 2),
        ];
    }

    /**
     * Find and add costs for a pallet configuration
     *
     * @param array $out Output accumulator
     * @param array $palletInfo Pallet sizing info
     * @param mixed $haulier Haulier ID or array of IDs
     * @param string $postcode Postcode
     * @param string $fullpostcode Full postcode
     * @param mixed $cart Cart object
     * @param mixed $cartline Cart line object
     * @param float $qty Quantity
     * @return array Updated accumulator
     */
    private static function findAndAddCosts(
        $out,
        $palletInfo,
        $haulier,
        $postcode,
        $fullpostcode,
        $cart,
        $cartline,
        $qty
    ): array {
        $shipping = $cartline->product->data->shipping;
        $palletCount = $palletInfo['count'];
        $palletSize = $shipping->size_of_pallet;

        // Try exact postcode match first
        $costs = self::queryCosts($haulier, $postcode, $cart, $qty, (float) $palletSize);

        if (!$costs || !$costs['pricea']) {
            // Fall back to generic/wildcard costs
            $costs = self::queryCosts($haulier, '*', $cart, $qty, (float) $palletSize);
        }

        if ($costs) {
            $haulierId = is_array($haulier) ? $haulier[0] : $haulier;
            $calculatedCost = self::calculateCost($costs, $palletCount, $qty);

            if (!isset($out[$haulierId])) {
                $out[$haulierId] = [
                    'data' => [],
                    'pallets' => 0,
                    'total_qty' => 0,
                ];
            }
            $out[$haulierId]['data'][] = [
                'productid' => $cartline->productid,
                'product' => $cartline->product_name,
                'split' => $shipping->pallet_split,
                'fullpostcode' => $fullpostcode,
                'postcode' => $postcode,
                'shipment_type' => $cart->shipment->delivery,
                'qty' => $qty,
                'weight' => $qty * (floatval($shipping->palletweight) ?? 0),
                'pallets' => $palletCount,
                'pallet_size' => $palletSize,
                'pallet_qty' => $shipping->number_on_pallet,
                'cost-qty' => $costs['priceqty'] ?? null,
                'single-cost' => $calculatedCost,
            ];

            $out[$haulierId]['pallets'] += (float) $palletCount;
            $out[$haulierId]['total_qty'] += (float) $shipping->number_on_pallet ?? 0;
        }
        return $out;
    }

    /**
     * Query shipping costs from cart calculation context
     *
     * @param mixed $haulier Haulier ID
     * @param string $postcode Postcode to search
     * @param mixed $cart Cart object
     * @param float $qty Quantity to match inside prices JSON
     * @param float $size Size to match inside prices JSON
     * @return array|null Cost record or null
     */
    private static function queryCosts($haulier, $postcode, $cart, $qty, $size): ?array
    {
        $deliveryValue = $cart->shipment->delivery ?? '';

        return self::queryCostsForContext($haulier, $postcode, $deliveryValue, $qty, $size);
    }

    private static function queryCostsForContext($haulier, $postcode, $deliveryValue, $qty, $size): ?array
    {
        $deliveryType = self::resolveDeliveryType($deliveryValue);
        $haulierId = is_array($haulier) ? (int) ($haulier[0] ?? 0) : (int) $haulier;

        $record = self::queryCostsByPriceCharacteristics(
            $haulierId,
            (string) $postcode,
            $deliveryType,
            (float) $qty,
            (float) $size,
            'Active'
        )->asArray()->one();

        if (!$record) {
            return null;
        }

        // Preserve legacy keys expected by calculateCost() and existing callers.
        $record['pricea'] = $record['price'] ?? null;
        $record['priceqty'] = $record['price_qty'] ?? null;
        $record['pricesize'] = $record['price_size'] ?? null;

        return $record;
    }

    private static function resolveDeliveryType($deliveryValue): string
    {
        if (is_array($deliveryValue)) {
            $deliveryValue = implode(' ', array_filter($deliveryValue, static function ($value) {
                return is_scalar($value) || (is_object($value) && method_exists($value, '__toString'));
            }));
        }

        $deliveryValue = (string) $deliveryValue;

        return stripos($deliveryValue, 'Eco') !== false
            || $deliveryValue === 'Standard (2-3 Working Days) - FREE'
            ? 'Eco'
            : 'Next';
    }

    /**
     * Calculate final cost based on quantity and pricing
     *
     * @param array $costs Cost record
     * @param float $palletCount Pallet count
     * @param float $qty Quantity
     * @return float Calculated cost
     */
    private static function calculateCost($costs, $palletCount, $qty): float
    {
        $basePrice = $costs['pricea'] ?? 0;
        $pricedQty = $costs['priceqty'] ?? 1;

        if (!$basePrice) {
            return 0.0;
        }

        $cost = ($pricedQty == $palletCount) ? $basePrice : ($basePrice * $palletCount);

        // Apply surcharge for small shipments on specific haulier
        if ($qty < 35 && ($costs['haulierid'] ?? null) == 38910 && $palletCount <= 1) {
            $cost += 5.00;
        }

        return (float) number_format($cost, 2);
    }

    /**
     * Finalize costs output with totals and discounts
     *
     * @param array $out Costs by haulier
     * @param string|null $method Return format (null for summary, 'detailed' for full data)
     * @return array Final output
     */
    private static function finalizeCostsOutput($out, $method = 'detailed'): array
    {
        $output = [];

        foreach ($out as $haulierId => $data) {
            $lineSubtotal = 0.0;
            foreach ($data['data'] as $lineData) {
                $lineSubtotal += (float) ($lineData['single-cost'] ?? 0);
            }

            $groupedCosts = self::calculateGroupedPalletSizeCosts((int) $haulierId, $data['data']);
            $subtotal = $groupedCosts['total'] > 0 ? $groupedCosts['total'] : round($lineSubtotal, 2);

            $haulierRecord = Accounts::findOne(['intid' => $haulierId]);
            $haulierData = $haulierRecord ? ($haulierRecord->data->haulier ?? null) : null;

            $fuelSurcharge = 0.0;
            if (($haulierData->fuel_surcharge ?? 0) > 0) {
                $fuelSurcharge = $subtotal * ((float) $haulierData->fuel_surcharge / 100);
                $subtotal += $fuelSurcharge;
            }

            $discount = 0.0;
            if (($haulierData->discount ?? 0) > 0) {
                $discount = $subtotal * ((float) $haulierData->discount / 100);
                $subtotal -= $discount;
            }

            $haulierName = $haulierRecord ? $haulierRecord->name : 'Unknown';
            $output[$haulierId] = [
                'haulierid' => $haulierId,
                'haulier' => $haulierName,
                'pallets' => $data['pallets'],
                'palletsize' => $data['data'][0]['pallet_size'] ?? null,
                'total_qty' => $data['total_qty'] ?? 0,
                'cost' => round($subtotal, 2),
            ];

            if ($method !== null) {
                $output[$haulierId]['data'] = $data['data'];
                $output[$haulierId]['discount'] = round($discount, 2);
                $output[$haulierId]['fuel_surcharge'] = round($fuelSurcharge, 2);
                $output[$haulierId]['line_subtotal'] = round($lineSubtotal, 2);
                $output[$haulierId]['group_subtotal'] = round($groupedCosts['total'], 2);
                $output[$haulierId]['group_costs'] = array_values($groupedCosts['groups']);
                $output[$haulierId]['subtotal'] = round($groupedCosts['total'] > 0 ? $groupedCosts['total'] : $lineSubtotal, 2);
            }
        }

        return $output;
    }

    private static function calculateGroupedPalletSizeCosts(int $haulierId, array $lineItems): array
    {
        $groups = [];

        foreach ($lineItems as $lineItem) {
            $palletSize = (float) ($lineItem['pallet_size'] ?? 0);
            $groupKey = number_format($palletSize, 2, '.', '');

            if (!isset($groups[$groupKey])) {
                $groups[$groupKey] = [
                    'pallet_size' => $palletSize,
                    'pallets' => 0.0,
                    'qty' => 0.0,
                    'postcode' => (string) ($lineItem['postcode'] ?? '*'),
                    'shipment_type' => (string) ($lineItem['shipment_type'] ?? ''),
                    'line_subtotal' => 0.0,
                    'group-cost' => 0.0,
                    'cost_source' => 'line-sum-fallback',
                    'cost-qty' => null,
                ];
            }

            $groups[$groupKey]['pallets'] += (float) ($lineItem['pallets'] ?? 0);
            $groups[$groupKey]['qty'] += (float) ($lineItem['qty'] ?? 0);
            $groups[$groupKey]['line_subtotal'] += (float) ($lineItem['single-cost'] ?? 0);
        }

        $total = 0.0;

        foreach ($groups as $groupKey => $group) {
            $group['pallets'] = (float) number_format($group['pallets'], 2, '.', '');
            $costs = null;

            try {
                $costs = self::queryCostsForContext(
                    $haulierId,
                    $group['postcode'],
                    $group['shipment_type'],
                    $group['pallets'],
                    (float) $group['pallet_size']
                );

                if ((!$costs || !($costs['pricea'] ?? null)) && $group['postcode'] !== '*') {
                    $costs = self::queryCostsForContext(
                        $haulierId,
                        '*',
                        $group['shipment_type'],
                        $group['pallets'],
                        (float) $group['pallet_size']
                    );
                }
            } catch (\Throwable $e) {
                if (class_exists('Yii')) {
                    Yii::warning([
                        'message' => 'Grouped pallet-size cost lookup failed; falling back to line subtotal.',
                        'haulierId' => $haulierId,
                        'palletSize' => $group['pallet_size'],
                        'pallets' => $group['pallets'],
                        'error' => $e->getMessage(),
                    ], 'ShippingCostCalculatorService GroupedCosts');
                }
            }

            $groupCost = $group['line_subtotal'];
            if ($costs && ($costs['pricea'] ?? null)) {
                $groupCost = self::calculateCost($costs, $group['pallets'], $group['qty']);
                $group['cost_source'] = 'group-lookup';
                $group['cost-qty'] = $costs['priceqty'] ?? null;
            }

            $group['line_subtotal'] = round($group['line_subtotal'], 2);
            $group['group-cost'] = round((float) $groupCost, 2);
            $groups[$groupKey] = $group;
            $total += $group['group-cost'];
        }

        return [
            'total' => round($total, 2),
            'groups' => $groups,
        ];
    }

    /**
     * Build a Yii Query for shipping costs by haulier, postcode, type, and price characteristics.
     * Uses JSON_TABLE to unnest the prices array and filter by qty and size.
     *
     * @param int $haulierid Haulier ID
     * @param string $postcodePattern Postcode pattern (e.g. 'BN1' — will be used in LIKE)
     * @param string $typePattern Type pattern (e.g. 'Eco' — will be used in LIKE)
     * @param float $qty Price qty to match (e.g. 1.0, 0.5)
     * @param float $size Price size to match (e.g. 0.5, 1.0)
     * @param string $status Status filter (default 'Active')
     *
     * @return \yii\db\Query
     */
    public static function queryCostsByPriceCharacteristics(
        int $haulierid,
        string $postcodePattern,
        string $typePattern,
        float $qty,
        float $size,
        string $status = 'Active'
    ): \yii\db\Query {
        return Shippingcosts::find()
            ->select([
                '{{shipping_costs}}.*',
                'prices_table.price',
                'prices_table.qty as price_qty',
                'prices_table.size as price_size',
                'prices_table.label as price_label',
            ])
            ->from('{{shipping_costs}}')
            ->leftJoin(
                "JSON_TABLE(
                    {{shipping_costs}}.prices,
                    '\$[*]' COLUMNS (
                        qty DECIMAL(10,2) PATH '\$.qty',
                        size DECIMAL(10,2) PATH '\$.size',
                        price DECIMAL(10,2) PATH '\$.price',
                        label VARCHAR(255) PATH '\$.label'
                    )
                ) prices_table",
                'prices_table.qty = :priceQty AND prices_table.size = :priceSize',
                [':priceQty' => $qty, ':priceSize' => $size]
            )
            ->where([
                '{{shipping_costs}}.haulierid' => $haulierid,
                '{{shipping_costs}}.status'    => $status,
            ])
            // Postcodes are stored as 'PL1||PL10||PL11...'. Match exact token by
            // wrapping with delimiters and searching for the token surrounded
            // by '||' to avoid partial matches (e.g. searching for 'L3' shouldn't
            // match 'PL30').
            ->andWhere(new Expression("CONCAT('||', {{shipping_costs}}.postcodes, '||') LIKE :postcode"), [':postcode' => '%||' . self::normalizePostcodeToken($postcodePattern) . '||%'])
            ->andWhere(['LIKE', '{{shipping_costs}}.type', $typePattern])
            ->andWhere(['<>', 'prices_table.price', 0]);
    }

    /**
     * Find shipping price by haulier, postcode, type, and price characteristics.
     * Uses the queryCosts() method under the hood.
     *
     * @param int $haulierid Haulier ID
     * @param string $postcodePattern Postcode pattern (e.g. 'BN1' — will be used in LIKE)
     * @param string $typePattern Type pattern (e.g. 'Eco' — will be used in LIKE)
     * @param float $qty Price qty to match (e.g. 1.0, 0.5)
     * @param float $size Price size to match (e.g. 0.5, 1.0)
     * @param string $status Status filter (default 'Active')
     * @param bool $singleRow Return single row or all matching rows (default false)
     *
     * @return array|null Array of results or single row, or null if not found
     */
    public static function findShippingPrice(
        int $haulierid,
        string $postcodePattern,
        string $typePattern,
        float $qty,
        float $size,
        string $status = 'Active',
        bool $singleRow = true
    ): ?array {
        $query = self::queryCostsByPriceCharacteristics($haulierid, $postcodePattern, $typePattern, $qty, $size, $status);

        if ($singleRow) {
            return $query->one() ?: null;
        }

        return $query->all() ?: null;
    }

    /**
     * Normalize a postcode token for matching against the '||'-delimited postcodes field.
     * - Preserve '*' wildcard
     * - Trim whitespace and uppercase the token
     */
    private static function normalizePostcodeToken(string $token): string
    {
        $token = trim($token);
        if ($token === '*' || $token === '') {
            return $token === '' ? '*' : $token;
        }

        return strtoupper(str_replace(' ', '', $token));
    }
}
