<?php

namespace App\Repositories;

use App\Models\BusinessProductsOrders;
use Illuminate\Filesystem\Filesystem;
use Illuminate\Mail\Mailer;
use Stripe;

class BusinessProductsOrdersRepository
{
    private function detectOrderDetailsQuantityColumn(): ?string
    {
        static $cached = null;

        if ($cached !== null) {
            return $cached;
        }

        $qtyColumnCandidates = [
            'quantity',
            'qty',
            'product_qty',
            'product_quantity',
            'quantity_ordered',
            'order_quantity',
            'qty_ordered',
        ];

        foreach ($qtyColumnCandidates as $candidate) {
            try {
                if (\Schema::hasColumn('business_products_orders_details', $candidate)) {
                    $cached = $candidate;
                    return $cached;
                }
            } catch (\Throwable $e) {
                // ignore
            }
        }

        $cached = null;
        return null;
    }

    public function getCompletedSoldQtyByProductIds(array $productIds): array
    {
        $productIds = array_values(array_unique(array_filter(array_map('intval', $productIds), function ($id) {
            return $id > 0;
        })));

        if (count($productIds) === 0) {
            return [];
        }

        $qtyColumn = $this->detectOrderDetailsQuantityColumn();
        if (! $qtyColumn) {
            return [];
        }

        $detailsTableName = \DB::getTablePrefix() . 'business_products_orders_details';
        $qualifiedQtyColumn = '`' . $detailsTableName . '`.`' . $qtyColumn . '`';

        $soldQuery = \DB::table('business_products_orders_details')
            ->join('business_products_orders', 'business_products_orders.id', '=', 'business_products_orders_details.order_id')
            ->select(
                'business_products_orders_details.product_id',
                \DB::raw('SUM(' . $qualifiedQtyColumn . ') as sold_qty')
            )
            ->whereIn('business_products_orders_details.product_id', $productIds)
            ->whereIn('business_products_orders.payment_status', ['completed', 'Completed'])
            ->where(function ($q) {
                $this->applyUniqueTransactionFilter($q, 'business_products_orders.');
            })
            ->groupBy('business_products_orders_details.product_id');

        $sold = $soldQuery->pluck('sold_qty', 'business_products_orders_details.product_id');

        return $sold ? $sold->toArray() : [];
    }

    public function createCanadaPostShipmentForOrder($order, bool $throwOnError = false): void
    {
        try {
            if ((int) ($order->delivery_option ?? 0) !== 0) {
                if ($throwOnError) {
                    throw new \RuntimeException('Delivery option is not shipping.');
                }
                return;
            }

            if (! $this->orderHasPhysicalItems($order)) {
                if ($throwOnError) {
                    throw new \RuntimeException('Order has no physical items.');
                }
                return;
            }

            $existingShipment = \App\Models\CPShipment::where('order_id', $order->order_id)->first();
            if ($existingShipment) {
                return;
            }

            $business = app('App\\Repositories\\PageRepository')->getById($order->business_id);
            $carrier = $business && !empty($business->shipping_carrier) ? $business->shipping_carrier : 'usps';
            if (!in_array($carrier, ['usps', 'canada_post'], true)) {
                $carrier = 'usps';
            }
            if ($carrier !== 'canada_post') {
                if ($throwOnError) {
                    throw new \RuntimeException('Carrier is not Canada Post for this business.');
                }
                return;
            }

            $cartArray = $this->buildCartArrayFromOrderDetails($order);
            $canadaPostService = app('App\\Services\\CanadaPostService');
            $packageData = $canadaPostService->calculatePackageFromCartItems($cartArray);

            $serviceCode = 'DOM.EP';
            $serviceName = '';
            $cost = 0.0;

            if ($business && !empty($business->zip) && !empty($order->zip)) {
                $rates = $canadaPostService->getRates($business->zip, $order->zip, $packageData['weight'], $packageData['dimensions'] ?? null);
                if (is_array($rates) && isset($rates['error']) && $rates['error']) {
                    // keep defaults
                } elseif (is_array($rates) && count($rates) > 0) {
                    $minPrice = null;
                    foreach ($rates as $rate) {
                        $p = isset($rate['price']) ? floatval($rate['price']) : 0.0;
                        if ($p <= 0) {
                            continue;
                        }
                        if ($minPrice === null || $p < $minPrice) {
                            $minPrice = $p;
                            $serviceCode = (string) ($rate['service_code'] ?? $serviceCode);
                            $serviceName = (string) ($rate['service_name'] ?? '');
                            $cost = $p;
                        }
                    }
                }
            }

            $order->cp_service_code = $serviceCode;
            $order->cp_shipping_cost = $cost;
            $this->setOrderFieldIfExists($order, 'shipping_carrier', 'canada_post');
            $this->setOrderFieldIfExists($order, 'shipping_service_code', $serviceCode);
            $this->setOrderFieldIfExists($order, 'shipping_service_name', $serviceName);
            $this->setOrderFieldIfExists($order, 'shipping_cost', $cost);
            $order->save();

            $shippingInfo = [
                'service_code' => $serviceCode,
                'cost' => $cost,
                'calculated_for_postal_code' => $order->zip,
            ];

            $this->createCanadaPostShipment($order, $shippingInfo, collect($cartArray));
        } catch (\Exception $e) {
            if ($throwOnError) {
                throw $e;
            }
        }
    }

    private function parseFirstLastName($fullName): array
    {
        $fullName = trim((string) $fullName);
        if ($fullName === '') {
            return ['first' => '', 'last' => ''];
        }

        $parts = preg_split('/\s+/', $fullName);
        $parts = array_values(array_filter($parts, function ($v) { return trim((string) $v) !== ''; }));
        if (count($parts) === 1) {
            return ['first' => (string) $parts[0], 'last' => (string) $parts[0]];
        }

        $first = (string) array_shift($parts);
        $last = (string) implode(' ', $parts);

        return ['first' => $first, 'last' => $last !== '' ? $last : $first];
    }

    private function orderHasPhysicalItems($order): bool
    {
        try {
            $orderDetails = $this->businessProductsOrdersDetailsRepository->getByOrderId($order->id);
            if (! $orderDetails || count($orderDetails) === 0) {
                return false;
            }

            foreach ($orderDetails as $detail) {
                $product = \DB::table('business_products')->where('id', $detail->product_id)->first();
                if (! $product) {
                    continue;
                }

                // If flag exists, require it; otherwise assume physical for safety.
                if (property_exists($product, 'is_product_physical')) {
                    if ((int) $product->is_product_physical === 1) {
                        return true;
                    }
                } else {
                    return true;
                }
            }

            return false;
        } catch (\Exception $e) {
            return false;
        }
    }

    private function buildCartArrayFromOrderDetails($order): array
    {
        $orderDetails = $this->businessProductsOrdersDetailsRepository->getByOrderId($order->id);
        $cartArray = [];

        foreach ($orderDetails as $detail) {
            $cartArray[] = $this->buildCartArrayItemFromOrderDetail($detail);
        }

        return $cartArray;
    }

    private function buildCartArrayItemFromOrderDetail($detail): array
    {
        $product = \DB::table('business_products')->where('id', $detail->product_id)->first();

        $variant = null;
        $variantId = $detail->productsizeid ?: $detail->product_color_id;

        $variantWeight = 0.5;
        $variantLength = 10;
        $variantWidth = 10;
        $variantHeight = 5;

        if ($product && in_array((int) ($product->product_category ?? 0), [1, 2], true)) {
            if ($variantId) {
                $variant = \DB::table('business_products_category_values')
                    ->where('id', $variantId)
                    ->first();
            }

            if ($variant) {
                $variantWeight = $variant->variant_weight ?? 0.5;
                $variantLength = $variant->variant_length ?? 10;
                $variantWidth = $variant->variant_width ?? 10;
                $variantHeight = $variant->variant_height ?? 5;

                if (isset($variant->variant_weight_unit) && $variant->variant_weight_unit === 'g') {
                    $variantWeight = $variantWeight / 1000;
                }

                if (isset($variant->variant_dimensions_unit) && $variant->variant_dimensions_unit === 'mm') {
                    $variantLength = $variantLength / 10;
                    $variantWidth = $variantWidth / 10;
                    $variantHeight = $variantHeight / 10;
                }
            }
        } elseif ($product && (int) ($product->product_category ?? 0) === 0) {
            $qtyBucket = (int) ceil((float) ($detail->quantity ?? 1));
            if ($qtyBucket < 1) {
                $qtyBucket = 1;
            }
            if ($qtyBucket > 3) {
                $qtyBucket = 3;
            }

            $variantWeight = $product->weight ?? 0.5;
            $variantLength = $product->length ?? 10;
            $variantWidth = $product->width ?? 10;
            $variantHeight = $product->height ?? 5;

            if ($qtyBucket === 2) {
                if (isset($product->length_2) && $product->length_2 !== '') {
                    $variantLength = $product->length_2;
                }
                if (isset($product->width_2) && $product->width_2 !== '') {
                    $variantWidth = $product->width_2;
                }
                if (isset($product->height_2) && $product->height_2 !== '') {
                    $variantHeight = $product->height_2;
                }
            } elseif ($qtyBucket >= 3) {
                if (isset($product->length_3) && $product->length_3 !== '') {
                    $variantLength = $product->length_3;
                }
                if (isset($product->width_3) && $product->width_3 !== '') {
                    $variantWidth = $product->width_3;
                }
                if (isset($product->height_3) && $product->height_3 !== '') {
                    $variantHeight = $product->height_3;
                }
            }

            if (isset($product->weight_unit) && $product->weight_unit === 'g') {
                $variantWeight = $variantWeight / 1000;
            }

            $dimsUnitCandidate = isset($product->dimensions_unit) ? $product->dimensions_unit : null;
            if ($qtyBucket === 2 && isset($product->dimensions_unit_2) && $product->dimensions_unit_2 !== '') {
                $dimsUnitCandidate = $product->dimensions_unit_2;
            } elseif ($qtyBucket >= 3 && isset($product->dimensions_unit_3) && $product->dimensions_unit_3 !== '') {
                $dimsUnitCandidate = $product->dimensions_unit_3;
            }

            $dimsUnitCandidate = strtolower(trim((string) $dimsUnitCandidate));
            if ($dimsUnitCandidate === 'mm') {
                $variantLength = $variantLength / 10;
                $variantWidth = $variantWidth / 10;
                $variantHeight = $variantHeight / 10;
            } elseif ($dimsUnitCandidate === 'in' || $dimsUnitCandidate === 'inch' || $dimsUnitCandidate === 'inches') {
                $variantLength = $variantLength * 2.54;
                $variantWidth = $variantWidth * 2.54;
                $variantHeight = $variantHeight * 2.54;
            }
        }

        return [
            'product_id' => $detail->product_id,
            'quantity' => $detail->quantity,
            'variant_id' => $variantId,
            'variant_data' => $variant,
            'weight' => $variantWeight,
            'length' => $variantLength,
            'width' => $variantWidth,
            'height' => $variantHeight,
        ];
    }

    private function normalizeUspsMailClassFromService($serviceCode, $serviceName): string
    {
        $serviceCode = strtoupper(trim((string) $serviceCode));
        $serviceName = trim((string) $serviceName);

        if ($serviceCode !== '') {
            // Rates often come back as USPS_<MAILCLASS>
            while (strpos($serviceCode, 'USPS_') === 0) {
                $serviceCode = substr($serviceCode, 5);
            }
            if ($serviceCode !== '') {
                return $serviceCode;
            }
        }

        if ($serviceName !== '') {
            $name = strtoupper($serviceName);

            if (strpos($name, 'PRIORITY MAIL EXPRESS') !== false || preg_match('/\bEXPRESS\b/', $name)) {
                return 'PRIORITY_MAIL_EXPRESS';
            }

            if (strpos($name, 'PRIORITY MAIL') !== false) {
                return 'PRIORITY_MAIL';
            }

            if (strpos($name, 'GROUND ADVANTAGE') !== false || strpos($name, 'RETAIL GROUND') !== false) {
                return 'USPS_GROUND_ADVANTAGE';
            }

            if (strpos($name, 'PARCEL SELECT') !== false) {
                return 'PARCEL_SELECT';
            }

            if (strpos($name, 'MEDIA MAIL') !== false) {
                return 'MEDIA_MAIL';
            }

            if (strpos($name, 'LIBRARY MAIL') !== false) {
                return 'LIBRARY_MAIL';
            }

            if (strpos($name, 'FIRST-CLASS') !== false || strpos($name, 'FIRST CLASS') !== false) {
                return 'FIRST-CLASS_PACKAGE_SERVICE';
            }
        }

        return 'USPS_GROUND_ADVANTAGE';
    }

    public function createUspsLabelForOrder($order, bool $throwOnError = false): void
    {
        $this->createUspsLabelAfterPayment($order, $throwOnError);
    }

    public function getUspsRatesForOrderItem($order, $detail): array
    {
        if ((int) ($order->delivery_option ?? 0) !== 0) {
            return ['error' => true, 'message' => 'Delivery option is not shipping.'];
        }

        $business = app('App\\Repositories\\PageRepository')->getById($order->business_id);
        $carrier = $business && !empty($business->shipping_carrier) ? $business->shipping_carrier : 'usps';
        if (!in_array($carrier, ['usps', 'canada_post'], true)) {
            $carrier = 'usps';
        }
        if ($carrier !== 'usps') {
            return ['error' => true, 'message' => 'Carrier is not USPS for this business.'];
        }

        if (! $business || empty($business->zip) || empty($order->zip)) {
            return ['error' => true, 'message' => 'Missing origin/destination postal code.'];
        }

        $cartArray = [$this->buildCartArrayItemFromOrderDetail($detail)];
        $canadaPostService = app('App\\Services\\CanadaPostService');
        $packageData = $canadaPostService->calculatePackageFromCartItems($cartArray);

        $uspsService = app('App\\Services\\UspsService');
        $rates = $uspsService->getDomesticRatesFromPackage((string) $business->zip, (string) $order->zip, $packageData);

        if (is_array($rates) && isset($rates['error']) && $rates['error']) {
            return $rates;
        }

        return [
            'rates' => $rates,
            'package_info' => $packageData,
        ];
    }

    public function saveOrderItemShippingSelection($detail, array $selection): void
    {
        $carrier = trim((string) ($selection['carrier'] ?? ''));
        $serviceCode = trim((string) ($selection['service_code'] ?? ''));
        $serviceName = trim((string) ($selection['service_name'] ?? ''));
        $cost = $selection['cost'] ?? null;
        $baseCost = $selection['base_cost'] ?? null;
        $commissionPercent = $selection['commission_percent'] ?? null;
        $commissionAmount = $selection['commission_amount'] ?? null;

        $this->setOrderDetailFieldIfExists($detail, 'shipping_carrier', $carrier);
        $this->setOrderDetailFieldIfExists($detail, 'shipping_service_code', $serviceCode);
        $this->setOrderDetailFieldIfExists($detail, 'shipping_service_name', $serviceName);

        if ($cost !== null && $cost !== '') {
            $this->setOrderDetailFieldIfExists($detail, 'shipping_service_cost', (float) preg_replace('/[^0-9.\-]/', '', (string) $cost));
        }

        // Optional: store breakdown if columns exist.
        if ($baseCost !== null && $baseCost !== '') {
            $this->setOrderDetailFieldIfExists($detail, 'shipping_service_cost_base', (float) preg_replace('/[^0-9.\-]/', '', (string) $baseCost));
        }
        if ($commissionPercent !== null && $commissionPercent !== '') {
            $this->setOrderDetailFieldIfExists($detail, 'shipping_service_commission_percent', (float) preg_replace('/[^0-9.\-]/', '', (string) $commissionPercent));
        }
        if ($commissionAmount !== null && $commissionAmount !== '') {
            $this->setOrderDetailFieldIfExists($detail, 'shipping_service_commission_amount', (float) preg_replace('/[^0-9.\-]/', '', (string) $commissionAmount));
        }

        $detail->save();
    }

    public function createUspsLabelForOrderItem($order, $detail, bool $throwOnError = false): void
    {
        try {
            if ((int) ($order->delivery_option ?? 0) !== 0) {
                if ($throwOnError) {
                    throw new \RuntimeException('Delivery option is not shipping.');
                }
                return;
            }

            if (! $this->orderDetailHasPhysicalProduct($detail)) {
                if ($throwOnError) {
                    throw new \RuntimeException('Order item is not a physical product.');
                }
                return;
            }

            if (! empty($detail->tracking_number)) {
                if ($throwOnError) {
                    throw new \RuntimeException('Tracking number already exists for this order item.');
                }
                return;
            }

            $business = app('App\\Repositories\\PageRepository')->getById($order->business_id);
            $carrier = $business && !empty($business->shipping_carrier) ? $business->shipping_carrier : 'usps';
            if (!in_array($carrier, ['usps', 'canada_post'], true)) {
                $carrier = 'usps';
            }
            if ($carrier !== 'usps') {
                if ($throwOnError) {
                    throw new \RuntimeException('Carrier is not USPS for this business.');
                }
                return;
            }

            $labelDir = storage_path('app/shipping_labels/usps/items');
            $labelPath = $labelDir . '/' . $order->order_id . '_' . $detail->id . '.pdf';
            if ($this->file->exists($labelPath)) {
                return;
            }

            if (! $business || empty($business->zip) || empty($business->city) || empty($business->state) || (empty($business->street) && empty($business->full_address))) {
                if ($throwOnError) {
                    throw new \RuntimeException('Missing origin address details (business address).');
                }
                return;
            }

            if (empty($order->zip) || empty($order->city) || empty($order->state) || empty($order->address_1)) {
                if ($throwOnError) {
                    throw new \RuntimeException('Missing destination address details (customer address).');
                }
                return;
            }

            $serviceCode = (string) ($detail->shipping_service_code ?? $order->shipping_service_code ?? $order->cp_service_code ?? '');
            $serviceName = (string) ($detail->shipping_service_name ?? $order->shipping_service_name ?? '');

            if (trim($serviceCode) === '' && $throwOnError) {
                throw new \RuntimeException('No selected USPS service for this order item. Please Get Rates first.');
            }

            $mailClass = $this->normalizeUspsMailClassFromService($serviceCode, $serviceName);

            $cartArray = [$this->buildCartArrayItemFromOrderDetail($detail)];
            $canadaPostService = app('App\\Services\\CanadaPostService');
            $packageData = $canadaPostService->calculatePackageFromCartItems($cartArray);

            $weightKg = (float) ($packageData['weight'] ?? 0.1);
            $lengthCm = (float) ($packageData['length'] ?? 10);
            $widthCm = (float) ($packageData['width'] ?? 10);
            $heightCm = (float) ($packageData['height'] ?? 5);

            $weightLb = max(0.1, $weightKg * 2.2046226218);
            $lengthIn = max(0.1, $lengthCm / 2.54);
            $widthIn = max(0.1, $widthCm / 2.54);
            $heightIn = max(0.1, $heightCm / 2.54);

            $recipientName = $this->parseFirstLastName($order->full_name ?? '');
            $senderName = $this->parseFirstLastName($business->title ?? '');

            $fromStreet = $business->street ?? $business->full_address ?? '';
            $toStreet = (string) ($order->address_1 ?? '');

            $rateIndicator = strtoupper(trim((string) (config('services-usps.label_rate_indicator') ?? config('services.usps.label_rate_indicator') ?? 'SP')));
            if ($rateIndicator === '') {
                $rateIndicator = 'SP';
            }

            $processingCategory = strtoupper(trim((string) (config('services-usps.processing_category') ?? config('services.usps.processing_category') ?? 'MACHINABLE')));
            if ($processingCategory === '') {
                $processingCategory = 'MACHINABLE';
            }

            $destinationEntryFacilityType = strtoupper(trim((string) (config('services-usps.destination_entry_facility_type') ?? config('services.usps.destination_entry_facility_type') ?? 'NONE')));
            if ($destinationEntryFacilityType === '') {
                $destinationEntryFacilityType = 'NONE';
            }

            $payload = [
                'imageInfo' => [
                    'suppressPostage' => false,
                    'receiptOption' => 'NONE',
                    'imageType' => 'PDF',
                    'labelType' => '4X6LABEL',
                ],
                'toAddress' => [
                    'firstName' => $recipientName['first'] !== '' ? $recipientName['first'] : 'Customer',
                    'lastName' => $recipientName['last'] !== '' ? $recipientName['last'] : 'Customer',
                    'streetAddress' => $toStreet,
                    'secondaryAddress' => (string) ($order->address_2 ?? ''),
                    'city' => (string) ($order->city ?? ''),
                    'state' => (string) ($order->state ?? ''),
                    'ZIPCode' => (string) ($order->zip ?? ''),
                ],
                'fromAddress' => [
                    'firstName' => $senderName['first'] !== '' ? $senderName['first'] : 'Sender',
                    'lastName' => $senderName['last'] !== '' ? $senderName['last'] : 'Sender',
                    'streetAddress' => (string) $fromStreet,
                    'city' => (string) ($business->city ?? ''),
                    'state' => (string) ($business->state ?? ''),
                    'ZIPCode' => (string) ($business->zip ?? ''),
                ],
                'packageDescription' => [
                    'mailClass' => $mailClass,
                    'rateIndicator' => $rateIndicator,
                    'weightUOM' => 'lb',
                    'weight' => round($weightLb, 3),
                    'dimensionsUOM' => 'in',
                    'length' => round($lengthIn, 2),
                    'width' => round($widthIn, 2),
                    'height' => round($heightIn, 2),
                    'processingCategory' => $processingCategory,
                    'mailingDate' => date('Y-m-d'),
                    'extraServices' => [],
                    'destinationEntryFacilityType' => $destinationEntryFacilityType,
                ],
            ];

            $uspsService = app('App\\Services\\UspsService');
            $result = $uspsService->createDomesticLabel($payload);

            $trackingNumber = (string) ($result['tracking_number'] ?? '');
            $labelBinary = (string) ($result['label_binary'] ?? '');
            if ($trackingNumber === '' || $labelBinary === '') {
                if ($throwOnError) {
                    throw new \RuntimeException('USPS returned an empty tracking number or label payload.');
                }
                return;
            }

            if (! $this->file->isDirectory($labelDir)) {
                $this->file->makeDirectory($labelDir, 0777, true, true);
            }

            $this->file->put($labelPath, $labelBinary);

            $this->setOrderDetailFieldIfExists($detail, 'tracking_number', $trackingNumber);
            $this->setOrderDetailFieldIfExists($detail, 'shipping_carrier', 'usps');
            $this->setOrderDetailFieldIfExists($detail, 'shipping_service_code', $serviceCode);
            $this->setOrderDetailFieldIfExists($detail, 'shipping_service_name', $serviceName);
            $this->setOrderDetailFieldIfExists($detail, 'shipping_label_path', 'shipping_labels/usps/items/' . $order->order_id . '_' . $detail->id . '.pdf');
            $this->setOrderDetailFieldIfExists($detail, 'shipping_label_generated_at', date('Y-m-d H:i:s'));
            $detail->save();
        } catch (\Exception $e) {
            \Log::error('USPS order-item label generation failed: ' . $e->getMessage(), [
                'order_id' => $order->order_id ?? null,
                'detail_id' => $detail->id ?? null,
            ]);

            if ($throwOnError) {
                throw $e;
            }
        }
    }

    private function createUspsLabelAfterPayment($order, bool $throwOnError = false): void
    {
        try {
            if ((int) ($order->delivery_option ?? 0) !== 0) {
                if ($throwOnError) {
                    throw new \RuntimeException('Delivery option is not shipping.');
                }
                return;
            }

            if (! $this->orderHasPhysicalItems($order)) {
                if ($throwOnError) {
                    throw new \RuntimeException('Order has no physical items.');
                }
                return;
            }

            if (! empty($order->tracking_number)) {
                if ($throwOnError) {
                    throw new \RuntimeException('Tracking number already exists; label PDF not found locally.');
                }
                return;
            }

            $business = app('App\\Repositories\\PageRepository')->getById($order->business_id);
            $carrier = $business && !empty($business->shipping_carrier) ? $business->shipping_carrier : 'usps';
            if (!in_array($carrier, ['usps', 'canada_post'], true)) {
                $carrier = 'usps';
            }

            if ($carrier !== 'usps') {
                if ($throwOnError) {
                    throw new \RuntimeException('Carrier is not USPS for this business.');
                }
                return;
            }

            $labelDir = storage_path('app/shipping_labels/usps');
            $labelPath = $labelDir . '/' . $order->order_id . '.pdf';
            if ($this->file->exists($labelPath)) {
                return;
            }

            if (! $business || empty($business->zip) || empty($business->city) || empty($business->state) || (empty($business->street) && empty($business->full_address))) {
                // Missing origin address details
                if ($throwOnError) {
                    throw new \RuntimeException('Missing origin address details (business address).');
                }
                return;
            }

            if (empty($order->zip) || empty($order->city) || empty($order->state) || empty($order->address_1)) {
                // Missing destination address details
                if ($throwOnError) {
                    throw new \RuntimeException('Missing destination address details (customer address).');
                }
                return;
            }

            // Determine selected USPS service (best-effort)
            $serviceCode = (string) ($order->shipping_service_code ?? $order->cp_service_code ?? '');
            $serviceName = (string) ($order->shipping_service_name ?? '');

            $selected = session('selected_shipping');
            if (is_array($selected) && ($selected['carrier'] ?? '') === 'usps') {
                $serviceCode = (string) ($selected['service_code'] ?? $serviceCode);
                $serviceName = (string) ($selected['service_name'] ?? $serviceName);
            }

            $uspsShipping = session('usps_shipping');
            if (is_array($uspsShipping) && $serviceCode === '') {
                $serviceCode = (string) ($uspsShipping['service_code'] ?? '');
                $serviceName = (string) ($uspsShipping['service_name'] ?? '');
            }

            $mailClass = $this->normalizeUspsMailClassFromService($serviceCode, $serviceName);

            $cartArray = $this->buildCartArrayFromOrderDetails($order);
            if (empty($cartArray)) {
                if ($throwOnError) {
                    throw new \RuntimeException('Unable to build package contents from order details.');
                }
                return;
            }

            $canadaPostService = app('App\\Services\\CanadaPostService');
            $packageData = $canadaPostService->calculatePackageFromCartItems($cartArray);

            $weightKg = (float) ($packageData['weight'] ?? 0.1);
            $lengthCm = (float) ($packageData['length'] ?? 10);
            $widthCm = (float) ($packageData['width'] ?? 10);
            $heightCm = (float) ($packageData['height'] ?? 5);

            $weightLb = max(0.1, $weightKg * 2.2046226218);
            $lengthIn = max(0.1, $lengthCm / 2.54);
            $widthIn = max(0.1, $widthCm / 2.54);
            $heightIn = max(0.1, $heightCm / 2.54);

            $recipientName = $this->parseFirstLastName($order->full_name ?? '');
            $senderName = $this->parseFirstLastName($business->title ?? '');

            $fromStreet = $business->street ?? $business->full_address ?? '';
            $toStreet = (string) ($order->address_1 ?? '');

            $rateIndicator = strtoupper(trim((string) (config('services-usps.label_rate_indicator') ?? config('services.usps.label_rate_indicator') ?? 'SP')));
            if ($rateIndicator === '') {
                $rateIndicator = 'SP';
            }

            $processingCategory = strtoupper(trim((string) (config('services-usps.processing_category') ?? config('services.usps.processing_category') ?? 'MACHINABLE')));
            if ($processingCategory === '') {
                $processingCategory = 'MACHINABLE';
            }

            $destinationEntryFacilityType = strtoupper(trim((string) (config('services-usps.destination_entry_facility_type') ?? config('services.usps.destination_entry_facility_type') ?? 'NONE')));
            if ($destinationEntryFacilityType === '') {
                $destinationEntryFacilityType = 'NONE';
            }

            $payload = [
                'imageInfo' => [
                    'suppressPostage' => false,
                    'receiptOption' => 'NONE',
                    'imageType' => 'PDF',
                    'labelType' => '4X6LABEL',
                ],
                'toAddress' => [
                    'firstName' => $recipientName['first'] !== '' ? $recipientName['first'] : 'Customer',
                    'lastName' => $recipientName['last'] !== '' ? $recipientName['last'] : 'Customer',
                    'streetAddress' => $toStreet,
                    'secondaryAddress' => (string) ($order->address_2 ?? ''),
                    'city' => (string) ($order->city ?? ''),
                    'state' => (string) ($order->state ?? ''),
                    'ZIPCode' => (string) ($order->zip ?? ''),
                ],
                'fromAddress' => [
                    'firstName' => $senderName['first'] !== '' ? $senderName['first'] : 'Sender',
                    'lastName' => $senderName['last'] !== '' ? $senderName['last'] : 'Sender',
                    'streetAddress' => (string) $fromStreet,
                    'city' => (string) ($business->city ?? ''),
                    'state' => (string) ($business->state ?? ''),
                    'ZIPCode' => (string) ($business->zip ?? ''),
                ],
                'packageDescription' => [
                    'mailClass' => $mailClass,
                    'rateIndicator' => $rateIndicator,
                    'weightUOM' => 'lb',
                    'weight' => round($weightLb, 3),
                    'dimensionsUOM' => 'in',
                    'length' => round($lengthIn, 2),
                    'width' => round($widthIn, 2),
                    'height' => round($heightIn, 2),
                    'processingCategory' => $processingCategory,
                    'mailingDate' => date('Y-m-d'),
                    'extraServices' => [],
                    'destinationEntryFacilityType' => $destinationEntryFacilityType,
                ],
            ];

            $uspsService = app('App\\Services\\UspsService');
            $result = $uspsService->createDomesticLabel($payload);

            $trackingNumber = (string) ($result['tracking_number'] ?? '');
            $labelBinary = (string) ($result['label_binary'] ?? '');
            if ($trackingNumber === '' || $labelBinary === '') {
                if ($throwOnError) {
                    throw new \RuntimeException('USPS returned an empty tracking number or label payload.');
                }
                return;
            }

            if (! $this->file->isDirectory($labelDir)) {
                $this->file->makeDirectory($labelDir, 0777, true, true);
            }

            $this->file->put($labelPath, $labelBinary);

            $order->tracking_number = $trackingNumber;

            // Store shipping method/service if columns exist (no migrations enforced).
            $this->setOrderFieldIfExists($order, 'shipping_carrier', 'usps');
            $this->setOrderFieldIfExists($order, 'shipping_service_code', $serviceCode);
            $this->setOrderFieldIfExists($order, 'shipping_service_name', $serviceName);
            $order->save();
        } catch (\Exception $e) {
            // Do not block order completion if label generation fails.
            \Log::error('USPS label generation failed: ' . $e->getMessage(), [
                'order_id' => $order->order_id ?? null,
                'id' => $order->id ?? null,
            ]);

            if ($throwOnError) {
                throw $e;
            }
        }
    }

    private function orderTableHasColumn(string $column): bool
    {
        static $cache = [];
        if (array_key_exists($column, $cache)) {
            return (bool) $cache[$column];
        }

        try {
            $cache[$column] = (class_exists('Schema') || class_exists('\\Schema'))
                ? \Schema::hasColumn('business_products_orders', $column)
                : false;
        } catch (\Exception $e) {
            $cache[$column] = false;
        }

        return (bool) $cache[$column];
    }

    private function setOrderFieldIfExists($order, string $column, $value): void
    {
        if ($this->orderTableHasColumn($column)) {
            $order->{$column} = $value;
        }
    }

    private function orderDetailsTableHasColumn(string $column): bool
    {
        static $cache = [];
        if (array_key_exists($column, $cache)) {
            return (bool) $cache[$column];
        }

        try {
            $cache[$column] = (class_exists('Schema') || class_exists('\\Schema'))
                ? \Schema::hasColumn('business_products_orders_details', $column)
                : false;
        } catch (\Exception $e) {
            $cache[$column] = false;
        }

        return (bool) $cache[$column];
    }

    private function setOrderDetailFieldIfExists($detail, string $column, $value): void
    {
        if ($this->orderDetailsTableHasColumn($column)) {
            $detail->{$column} = $value;
        }
    }

    private function orderDetailHasPhysicalProduct($detail): bool
    {
        try {
            $product = \DB::table('business_products')->where('id', $detail->product_id)->first();
            if (! $product) {
                return false;
            }

            if (property_exists($product, 'is_product_physical')) {
                return (int) $product->is_product_physical === 1;
            }

            return true;
        } catch (\Exception $e) {
            return false;
        }
    }

    private function normalizePercent($value)
    {
        return (float) preg_replace('/[^0-9.\-]/', '', (string) $value);
    }

    private function getTaxLinesFromPage($pageId)
    {
        $page = null;
        if ($pageId) {
            $page = app('App\\Repositories\\PageRepository')->getById($pageId);
        }

        if (! $page) {
            return [];
        }

        $taxLines = [];

        $p1 = $this->normalizePercent($page->product_tax);
        if ($p1 > 0) {
            $name = trim((string) $page->product_tax_name);
            $taxLines[] = ['name' => ($name !== '' ? $name : 'Tax'), 'percentage' => $p1];
        }

        $p2 = $this->normalizePercent($page->product_tax_1);
        if ($p2 > 0) {
            $name = trim((string) $page->product_tax_name_1);
            $taxLines[] = ['name' => ($name !== '' ? $name : 'Tax'), 'percentage' => $p2];
        }

        $p3 = $this->normalizePercent($page->product_tax_2);
        if ($p3 > 0) {
            $name = trim((string) $page->product_tax_name_2);
            $taxLines[] = ['name' => ($name !== '' ? $name : 'Tax'), 'percentage' => $p3];
        }

        return $taxLines;
    }

    private function computeTaxes($taxableAmount, array $taxLines)
    {
        $taxableAmount = (float) $taxableAmount;
        if ($taxableAmount < 0) {
            $taxableAmount = 0;
        }

        $lines = [];
        $totalPercent = 0.0;
        $totalAmount = 0.0;

        foreach ($taxLines as $line) {
            $percentage = $this->normalizePercent($line['percentage'] ?? 0);
            if ($percentage <= 0) {
                continue;
            }

            $name = trim((string) ($line['name'] ?? 'Tax'));
            if ($name === '') {
                $name = 'Tax';
            }

            $amount = ($taxableAmount * $percentage) / 100;
            $totalPercent += $percentage;
            $totalAmount += $amount;

            $lines[] = ['name' => $name, 'percentage' => $percentage, 'amount' => $amount];
        }

        return [
            'lines' => $lines,
            'total_percentage' => $totalPercent,
            'total_amount' => $totalAmount,
        ];
    }

    public function __construct(
        BusinessProductsOrders $businessProductsOrders,
        BusinessProductsRepository $businessProductsRepository,
        Filesystem $filesystem,
        Mailer $mailer
    ) {
        $this->model = $businessProductsOrders;
        $this->file = $filesystem;
        $this->mailer = $mailer;
        $this->businessProductsRepository = $businessProductsRepository;
        $this->businessProductsCartRepository = app('App\\Repositories\\BusinessProductsCartRepository');
        $this->businessProductsOrdersDetailsRepository = app('App\\Repositories\\BusinessProductsOrdersDetailsRepository');
    }

    public function getById($id)
    {
        return $this->model->where('id', $id)->first();
    }

    public function saveOrder($pageId,
        $delivery_option,
        $pickup_date,
        $delivery_date,
        $delivery_full_name,
        $delivery_address_1,
        $delivery_address_2,
        $delivery_city,
        $delivery_state,
        $delivery_zip,
        $delivery_country,
        $delivery_mobile,
        $product_tax,
        $order_id,
        $community_id,
        $askPickupSchedule,
		$defaultCountry,
		$carrierCode,
		$current_currency,
		$shipping_cost = null,
		$shipping_service_code = null,
		$shipping_service_name = null,
		$buyer_user_id = null,
		$guest_session_id = null)
    {

        $userId = $buyer_user_id ?: \Auth::id();
        if (! $userId) {
            return null;
        }

        // For guest checkout, build the order from the guest session cart (session_id + user_id NULL)
        // so we never accidentally include the email owner's existing cart items.
        $cartList = null;
        if (! \Auth::check() && $guest_session_id) {
            $cartList = $this->businessProductsCartRepository->getGuestCartListByBusiness($pageId, $guest_session_id, $community_id);
        } else {
            $cartList = $this->businessProductsCartRepository->getCartListByBusiness($pageId, $userId, $community_id);
        }

        if (! $cartList || (method_exists($cartList, 'count') && $cartList->count() === 0)) {
            throw new \RuntimeException('Your cart is empty.');
        }

        // Validate stock before creating the order (avoid overselling).
        try {
            $requiredQtyByProductId = [];
            $productNameById = [];
            $totalStockById = [];

            foreach ($cartList as $cart) {
                $product = isset($cart->product) ? $cart->product : null;
                if (! $product || ! isset($product->id)) {
                    continue;
                }

                // Existing UI treats wearable (category 2) as not quantity-tracked.
                if (isset($product->product_category) && (int) $product->product_category === 2) {
                    continue;
                }

                $totalStock = isset($product->quantity_available) ? (int) $product->quantity_available : null;
                if ($totalStock === null) {
                    continue;
                }

                $pid = (int) $product->id;
                $requiredQtyByProductId[$pid] = ($requiredQtyByProductId[$pid] ?? 0) + (int) ($cart->quantity ?? 0);
                $productNameById[$pid] = (string) ($product->product_name ?? ('Product #' . $pid));
                $totalStockById[$pid] = $totalStock;
            }

            if ($requiredQtyByProductId) {
                $soldByProductId = $this->getCompletedSoldQtyByProductIds(array_keys($requiredQtyByProductId));
                foreach ($requiredQtyByProductId as $pid => $requiredQty) {
                    $soldQty = (int) ($soldByProductId[$pid] ?? 0);
                    $totalStock = (int) ($totalStockById[$pid] ?? 0);
                    $available = max(0, $totalStock - $soldQty);
                    if ($requiredQty > $available) {
                        throw new \RuntimeException('Insufficient stock for ' . ($productNameById[$pid] ?? 'this product') . '. Available: ' . $available);
                    }
                }
            }
        } catch (\RuntimeException $e) {
            throw $e;
        } catch (\Throwable $e) {
            // If validation fails unexpectedly, do not block checkout.
        }

        $order = $this->model->newInstance();
        $userid = $userId;
        $order->business_id = $pageId;
        $order->user_id = $userid;
        $order->delivery_option = $delivery_option;
        $order->community_id = $community_id;

        if ($delivery_option == 0) {
            $order->full_name = $delivery_full_name;
            $order->address_1 = $delivery_address_1;
            $order->address_2 = $delivery_address_2;
            $order->city = $delivery_city;
            $order->state = $delivery_state;
            $order->zip = $delivery_zip;
            $order->country = $delivery_country;
            $order->mobile = $delivery_mobile;
        } else {
            if ($pickup_date) {
                $pickup_date1 = new \DateTime($pickup_date);
                $order_date_time = date_format($pickup_date1, 'Y-m-d H:i:s');
                $order->order_date_time = $order_date_time;
            }

            $order->mobile = $delivery_mobile;
        }

        $order->business_id = $pageId;
        $order->order_id = $order_id;

        // IMPORTANT: `carrier_code` is used elsewhere for phone calling codes.
        // Do not reuse it for shipping service codes.
                
        
       // if ($delivery_option == 0) {            
            $order->country_iso_code = $defaultCountry;
            $order->carrier_code = $carrierCode;
       // }
        
        $order->currency = $current_currency;
        
        
        $orderShippingCost = 0;
        $orderShippingServiceCode = '';
        $orderShippingServiceName = '';        

        if ($delivery_option == 0) {
            // Static per-product shipping only.
            // Do NOT calculate/apply carrier rates during checkout, and do not persist service codes.
            $order->cp_service_code = '';
            $order->cp_shipping_cost = 0;
            $this->setOrderFieldIfExists($order, 'shipping_carrier', '');
            $this->setOrderFieldIfExists($order, 'shipping_service_code', '');
            $this->setOrderFieldIfExists($order, 'shipping_service_name', '');
            $this->setOrderFieldIfExists($order, 'shipping_cost', 0);
        }
        
        $order->save();

        $totalQuantity = 0;
        $totalAmount = 0;
        $totalShipping = 0;

        // For reporting/DB fields
        // - totalAmountBeforeDiscount: total before product-level discounts and before tax
        // - totalProductDiscount: total discount amount applied from products
        $totalAmountBeforeDiscount = 0;
        $totalProductDiscount = 0;
        
        foreach ($cartList as $cart) {
            $product = isset($cart->product) ? $cart->product : null;
            if (!$product) continue; // Skip if no product data

            $actualPrice = 0;
            if ($product->product_category == 1) {
                $priceRecord = app('App\\Repositories\\BusinessProductsCategoryValuesRepository')->getProductValueById($cart->productsizeid, $product->id, 'liquid-size');
                if ($priceRecord) {
                    $actualPrice = $priceRecord->price;
                }
            } elseif ($product->product_category == 2) {
                $priceRecord = app('App\\Repositories\\BusinessProductsCategoryValuesRepository')->getProductValueById($cart->product_color_id, $product->id, 'wearable-size');
                if ($priceRecord) {
                    $actualPrice = $priceRecord->price;
                }
            } else {
                $actualPrice = $product->price;
            }

            $price = $actualPrice;
            $savedPriceSave = 0;
            $lineActualSubtotal = (float) $actualPrice * (float) $cart->quantity;
            $lineDiscountAmount = 0.0;

            // Determine shipping cost per item
            $shippingCost = 0.0;
            if ($delivery_option == 0) {
                $isPhysicalProduct = isset($product->is_product_physical) ? ((int) $product->is_product_physical === 1) : true;
                $isFreeShipping = isset($product->is_free_shipping) ? ((int) $product->is_free_shipping === 1) : false;
                if (! $isPhysicalProduct || $isFreeShipping) {
                    $shippingCost = 0.0;
                } else {
                    $qty = (int) $cart->quantity;
                    if ($qty <= 1) {
						if($product->shipping_cost_free=='no'){
                        	$shippingCost = floatval($product->shipping_cost ?? 0);
						}	
                    } elseif ($qty === 2) {
						if($product->shipping_cost_2_free=='no'){
                        	$shippingCost = floatval($product->shipping_cost_2 ?? 0);
						}	
                    } elseif ($qty === 3) {
						if($product->shipping_cost_3_free=='no'){
                       		 $shippingCost = floatval($product->shipping_cost_3 ?? 0);
						}	 
                    } elseif ($qty === 4 && isset($product->shipping_cost_4)) {
                        $shippingCost = floatval($product->shipping_cost_4 ?? 0);
                    } elseif ($qty === 5 && isset($product->shipping_cost_5)) {
                        $shippingCost = floatval($product->shipping_cost_5 ?? 0);
                    } else {
                        $shippingCost = 0.0;
                    }
                }
            }

            if ($product->discount) {
                $savedPrice = ($product->discount / 100) * $actualPrice;
                $price = $actualPrice - $savedPrice;
                $savedPriceSave = ($product->discount / 100) * ($actualPrice * $cart->quantity);
                $lineDiscountAmount = (float) $savedPriceSave;
            }

            $subTotal = $price * $cart->quantity;

            // Keep track of totals before/after discount (shipping never discounted)
            $totalAmountBeforeDiscount = $totalAmountBeforeDiscount + $lineActualSubtotal + (float) $shippingCost;
            $totalProductDiscount = $totalProductDiscount + $lineDiscountAmount;

            $totalQuantity = $totalQuantity + $cart->quantity;
            $totalAmount = $totalAmount + $subTotal + $shippingCost;
            $totalShipping = $totalShipping + $shippingCost;

            $order_date_time = '0000-00-00 00:00:00';
            if ($askPickupSchedule == 1) {
                $order_date_time = $cart->order_date_time;
            }

            $this->businessProductsOrdersDetailsRepository->addProduct(
                $order->id,
                $product->id,
                $pageId,
                $userid,
                $cart->quantity,
                $subTotal,
                $actualPrice,
                $product->discount,
                $shippingCost,
                $community_id,
                $product->business_id,
                $order_date_time,
                $cart->productsizeid,
                $cart->product_color_id);
        }
        
        // Add Canada Post shipping cost to total (applied once to entire order)
        if ($orderShippingCost > 0) {
            $totalAmount += $orderShippingCost;
            $totalShipping += $orderShippingCost;

            // Shipping is not discounted, but it is part of actual_amount
            $totalAmountBeforeDiscount += $orderShippingCost;
        }

        // Taxes are calculated on product amount only (excluding shipping)
        $taxLines = [];
        if ($pageId) {
            $taxLines = $this->getTaxLinesFromPage($pageId);
        } elseif ($product_tax) {
            $perc = $this->normalizePercent($product_tax);
            if ($perc > 0) {
                $taxLines = [['name' => 'Tax', 'percentage' => $perc]];
            }
        }

        // Persist base amounts for reporting
        // - actual_amount: total before discounts and before tax
        // - discount_*: total discount from product-level discounts (cart flow)
        $order->actual_amount = $totalAmountBeforeDiscount;
        $order->discount_price = round((float) $totalProductDiscount, 2);
        $order->discount_percentage = $totalAmountBeforeDiscount > 0
            ? round(((float) $totalProductDiscount / (float) $totalAmountBeforeDiscount) * 100, 2)
            : 0;

        $taxableAmount = $totalAmount - $totalShipping;
        $taxData = $this->computeTaxes($taxableAmount, $taxLines);
        $taxTotalAmount = (float) $taxData['total_amount'];
        $taxTotalPercentage = (float) $taxData['total_percentage'];

        $order->quantity = $totalQuantity;

        // Persist a total tax percentage for backward compatibility
        // Also persist the 3 individual taxes on existing columns.
        $t1 = $taxData['lines'][0] ?? null;
        $t2 = $taxData['lines'][1] ?? null;
        $t3 = $taxData['lines'][2] ?? null;

        $order->product_tax_name = $t1['name'] ?? null;
        $order->product_tax = isset($t1['percentage']) ? (float) $t1['percentage'] : $this->normalizePercent($product_tax);

        $order->product_tax_name_1 = $t2['name'] ?? null;
        $order->product_tax_1 = isset($t2['percentage']) ? (float) $t2['percentage'] : 0;

        $order->product_tax_name_2 = $t3['name'] ?? null;
        $order->product_tax_2 = isset($t3['percentage']) ? (float) $t3['percentage'] : 0;

        // Keep backward compat: if older code expects a single combined percent, it can use product_tax + product_tax_1 + product_tax_2.

        $order->amount = $totalAmount + $taxTotalAmount;
        $order->save();
        
		if($community_id==0){
            // addBusinessOrder() expects product subtotal only (no shipping)
            $this->addBusinessOrder($order, ($totalAmount - $totalShipping), $totalQuantity, $pageId, $order->order_date_time, $askPickupSchedule);
        }
			
        /*if ($orderShippingCost > 0 && $delivery_option == 0){
            session([
                'canada_post_pending_shipment' => [
                    'order_id' => $order->order_id,
                    'shipping_info' => [
                        'cost' => $orderShippingCost,
                        'service_code' => $orderShippingServiceCode,
                        'service_name' => $orderShippingServiceName
                    ],
                    'cart_items' => $cartList->toArray()
                ]
            ]);
        }*/ //Commented

        return $order;
    }

    public function addBusinessOrder($b_order, $b_order_amount, $b_order_quantity, $community_business_id, $order_date_time, $askPickupSchedule)
    {
        // $b_order_amount now contains only product amounts from community (no shipping)
        // We just need to add tax if applicable
        
        $productAmountOnly = $b_order_amount; // This is already products only

        $taxLines = [];
        if ($this->normalizePercent($b_order->product_tax) > 0) {
            $taxLines[] = ['name' => ($b_order->product_tax_name ?: 'Tax'), 'percentage' => $b_order->product_tax];
        }
        if ($this->normalizePercent($b_order->product_tax_1) > 0) {
            $taxLines[] = ['name' => ($b_order->product_tax_name_1 ?: 'Tax'), 'percentage' => $b_order->product_tax_1];
        }
        if ($this->normalizePercent($b_order->product_tax_2) > 0) {
            $taxLines[] = ['name' => ($b_order->product_tax_name_2 ?: 'Tax'), 'percentage' => $b_order->product_tax_2];
        }

        $taxData = $this->computeTaxes($productAmountOnly, $taxLines);
        $taxTotalAmount = (float) $taxData['total_amount'];
        $taxTotalPercentage = (float) $taxData['total_percentage'];

        $totalAmmount = $productAmountOnly + $taxTotalAmount; // Product + tax (NO shipping)
        


        $totalAmmount = number_format($totalAmmount, 2, '.', '');

        $userid = (int) ($b_order->user_id ?? 0);
        if (! $userid) {
            $userid = (int) \Auth::id();
        }
        if (! $userid) {
            return null;
        }
        $order = $this->model->newInstance();
        $order->business_id = 0;
        $order->community_business_id = $community_business_id;
        $order->community_id = $b_order->community_id;
        $order->user_id = $userid;
        $order->quantity = $b_order_quantity;
        $order->amount = $totalAmmount;
        $order->delivery_option = $b_order->delivery_option;
        if ($b_order->delivery_option == 1) {
            if ($askPickupSchedule == 1) {
                $order->order_date_time = $order_date_time;
                $order->picked_date = $order_date_time;
            } else {
                $order->order_date_time = $b_order->order_date_time;
                $order->picked_date = $b_order->picked_date;
            }

        }
        $order->full_name = $b_order->full_name;
        $order->address_1 = $b_order->address_1;
        $order->address_2 = $b_order->address_2;
        $order->city = $b_order->city;
        $order->state = $b_order->state;
        $order->zip = $b_order->zip;
        $order->country = $b_order->country;
        $order->mobile = $b_order->mobile;
		
		if($b_order->country_iso_code){
			$order->country_iso_code = $b_order->country_iso_code;
		}
		
		if($b_order->carrier_code){
			$order->carrier_code = $b_order->carrier_code;
		}
		
        $order->order_id = $b_order->order_id;
        // Persist individual taxes (existing columns)
        $t1 = $taxData['lines'][0] ?? null;
        $t2 = $taxData['lines'][1] ?? null;
        $t3 = $taxData['lines'][2] ?? null;

        $order->product_tax_name = $t1['name'] ?? null;
        $order->product_tax = isset($t1['percentage']) ? (float) $t1['percentage'] : 0;

        $order->product_tax_name_1 = $t2['name'] ?? null;
        $order->product_tax_1 = isset($t2['percentage']) ? (float) $t2['percentage'] : 0;

        $order->product_tax_name_2 = $t3['name'] ?? null;
        $order->product_tax_2 = isset($t3['percentage']) ? (float) $t3['percentage'] : 0;

        $order->currency = $b_order->currency;
        $order->business_order_id = $b_order->id;
        $order->save();

        return $order;
    }

    public function completeProductOrder($paypalOrderID, $payment_status, $orderId, $currency_code, $payment_amount)
    {
        $order = $this->getById($orderId);
        $order->transaction_id = $paypalOrderID;
        $order->payment_status = $payment_status;
        // $order->currency=$currency_code;
        $order->amount = $payment_amount;
        $order->save();

        $payments = $this->getByOrderIdAll($order->order_id);
        if ($payments) {
            foreach ($payments as $payment) {
                $payment->payment_status = $payment_status;
                $payment->transaction_id = $paypalOrderID;
                // $payment->currency=$currency_code;
                $payment->save();
            }
        }
        
        // Do not auto-generate shipments/labels on order placement.

        return $order;
    }

    public function updateProductQuantity()
    {

        // $orderDetails=$this->businessProductsOrdersDetailsRepository

    }

    public function updatePayuPayment($email, $txnid, $invoice, $status, $data, $payment_amount)
    {
        if ($status == 'success') {
            $status = 'completed';
        }

        $payments = $this->getByOrderIdAll($invoice);
        if ($payments) {
            foreach ($payments as $payment) {
                $payment->payment_status = $status;
                $payment->currency = 'INR';
                $payment->transaction_id = $txnid;
                // $payment->amount=$payment_amount;
                $payment->save();
                
                // Do not auto-generate shipments/labels on order placement.
            }
        }

        return $order = $this->getByOrderId($invoice);
    }

    public function getByOrderIdAll($order_id)
    {
        return $this->model->where('order_id', '=', $order_id)->get();
    }

    public function UpdateOrderByOrderIdAll($order_id, $ownAccountTransfer)
    {
        return $this->model->where('order_id', '=', $order_id)->update(['own_account_transfer' => $ownAccountTransfer]);
    }

    public function getByOrderId($order_id)
    {
        return $this->model->where('order_id', '=', $order_id)->first();
    }

    public function invalidPayuPayment($payer_email, $transaction_id, $status, $price, $buyer_id, $order_id, $payment_amount)
    {
        $payments = $this->getByOrderIdAll($order_id);
        if ($payments) {
            foreach ($payments as $payment) {
                $payment->payment_status = $status;
                $payment->currency = 'INR';
                $payment->transaction_id = $transaction_id;
                // $payment->amount=$payment_amount;
                $payment->save();
            }
        }

        return $order = $this->getByOrderId($order_id);
        // return true;
    }

    public function saveAmount($totalAmmount, $currency, $order)
    {
        if ($currency == '') {
            $currency = 'USD';
        }

        $order->amount = $totalAmmount;
        $order->currency = $currency;
        $order->save();

        return $order;
    }

    public function getByBusinessId($business_id, $limit = 10)
    {
        return $this->model->with('user')->with('details')->where('business_id', '=', $business_id)->where('payment_status', '=', 'completed')->paginate($limit);
    }

    public function getByBusinessIdAll($business_id, $limit = 10, $pickupDropped = '')
    {
        $ordersTable = $this->model->getTable();
        $prefix = (string) $this->model->getConnection()->getTablePrefix();
        $detailsTableBase = (new \App\Models\BusinessProductsOrdersDetails())->getTable();
        $detailsTable = $prefix . $detailsTableBase;

        // Do not qualify base-table columns here because Laravel may apply a table prefix
        // in the generated FROM clause while getTable() returns the unprefixed name.
        $pickupDroppedExpr = "(CASE "
            . "WHEN IFNULL(ship_together, 0) = 1 "
            . "THEN IFNULL(pickup_dropped, 0) "
            . "ELSE IFNULL((SELECT MAX(d.pickup_dropped) FROM {$detailsTable} d WHERE d.order_id = id), 0) "
            . "END)";

        $query = $this->model
            ->select($ordersTable . '.*')
            ->selectRaw("({$pickupDroppedExpr}) as pickup_dropped_sort_val")
            ->with('user')
            ->with('details')
            // ->where('business_id', '=', $business_id)
            ->where('payment_status', '=', 'completed')
            ->where(function ($query) use ($business_id) {
                $query->where('business_id', '=', $business_id)
                    ->orWhere('community_business_id', '=', $business_id);
            })
            ->groupBy('order_id');

        $pickupDropped = trim((string) $pickupDropped);
        if ($pickupDropped === '1') {
            $query->whereRaw(
                "((IFNULL(ship_together, 0) = 1 AND pickup_dropped = 1)"
                . " OR (IFNULL(ship_together, 0) <> 1 AND EXISTS (SELECT 1 FROM {$detailsTable} d2 WHERE d2.order_id = id AND d2.pickup_dropped = 1)))"
            );
        } elseif ($pickupDropped === '0') {
            // "No" list means: still pending (NOT Yes) following shipping mode:
            // - same box (ship_together=1): check order-level pickup_dropped
            // - different boxes: check item-wise pickup_dropped
            $query->whereRaw(
                "((IFNULL(ship_together, 0) = 1 AND (pickup_dropped IS NULL OR pickup_dropped <> 1))"
                . " OR (IFNULL(ship_together, 0) <> 1 AND EXISTS (SELECT 1 FROM {$detailsTable} d3 WHERE d3.order_id = id AND (d3.pickup_dropped IS NULL OR d3.pickup_dropped <> 1)))"
                . " OR (IFNULL(ship_together, 0) <> 1 AND NOT EXISTS (SELECT 1 FROM {$detailsTable} de2 WHERE de2.order_id = id) AND (pickup_dropped IS NULL OR pickup_dropped <> 1)))"
            );
        } else {
            // All Orders: no Pickup/Dropped filter.
        }

        $query->orderBy('id', 'desc');

        // When no Pickup/Dropped filter is applied ("All"), return all records without pagination.
        if ((int) $limit <= 0) {
            return $query->get();
        }

        return $query->paginate($limit);
    }

    public function getByUserIdAll($user_id, $limit = 10)
    {
        return $this->model
            ->with('user')
            ->with('details')
            ->where('business_id', '=', 0)
            ->where('community_business_id', '=', 0)
            ->where('payment_status', '=', 'completed')
            ->where('to_user_id', '=', $user_id)
            ->where('parent_order_id', '=', 0)
            ->orderBy('id', 'desc')
            ->paginate($limit);
    }

    public function getMyOrders($limit = 10)
    {
        $userid = \Auth::user()->id;

        return $this->model->with('details')
        // ->with('business')
            ->where('user_id', '=', $userid)
            ->where('community_business_id', '=', 0)
            ->where('payment_status', '=', 'completed')
            ->orderBy('id', 'desc')
            ->paginate($limit);
    }

    public function getMyRecurringOrders($limit = 10)
    {
        $userid = \Auth::user()->id;

        return $this->model
            ->with('details')
            ->where('user_id', '=', $userid)
            ->where('community_business_id', '=', 0)
            ->where('parent_order_id', '=', 0)
            ->whereIn('payment_status', ['completed', 'Completed'])
            ->whereHas('details', function ($q) {
                $q->whereHas('product', function ($q2) {
                    $q2->where('product_type', '=', 1);
                });
            })
            ->orderBy('id', 'desc')
            ->paginate($limit);
    }

    public function getProductsOrders($product_id, $limit = 10)
    {
        $userid = \Auth::user()->id;

        return $this->model->with('details')
        // ->with('business')
        // ->where('user_id', '=', $userid)
            ->where('product_id', '=', $product_id)
            ->where('community_business_id', '=', 0)
            ->where('payment_status', '=', 'completed')
            ->orderBy('id', 'desc')
            ->paginate($limit);
    }

    public function getProductOrdersCount($product_id)
    {
        $userid = \Auth::user()->id;

        return $this->model
            ->where('product_id', '=', $product_id)
            ->where('community_business_id', '=', 0)
            ->where('payment_status', '=', 'completed')
            ->count();
    }

    public function editOrder($val, $orderId)
    {
        $expected = [
            'picked_by' => '',
            'picked_date' => '',
            'courier_by' => '',
            'courier_date' => '',
            'tracking_number' => '',
        ];

        extract(array_merge($expected, $val));

        $order = $this->getById($orderId);

        if ($order) {
            if ($order->delivery_option == 1) {
                if ($picked_date) {
                    $picked_date = new \DateTime($picked_date);
                    $picked_date = date_format($picked_date, 'Y-m-d H:i:s');
                }

                $order->picked_by = $picked_by;
                $order->picked_date = $picked_date;
                $order->save();
            } else {
                if ($courier_date) {
                    $courier_date = new \DateTime($courier_date);
                    $courier_date = date_format($courier_date, 'Y-m-d');
                }

                $order->courier_by = $courier_by;
                $order->courier_date = $courier_date;
                $order->tracking_number = $tracking_number;
                $order->save();
            }
        }
    }

    public function getByBusinessOrderId($order_id)
    {
        return $this->model->where('business_order_id', '=', $order_id)->get();
    }

    public function changePickupDate($id, $value)
    {
        $order = $this->getById($id);
        if ($order) {
            $newDate = '';

            if ($value) {
                $new_date_time = new \DateTime($value);
                $newDate = date_format($new_date_time, 'Y-m-d H:i:s');
            }

            $order->original_pickup_date_time = $order->order_date_time;
            $order->order_date_time = $newDate;
            $order->picked_date = $newDate;
            $order->save();

            return $order;
        }

        return false;
    }

    public function assertSufficientStockForProductOrder($productId, $quantity, $order_type): void
    {
        $product = $this->businessProductsRepository->getById($productId);
        $this->assertSufficientStockForProductOrderDetails($product, $quantity, $order_type);
    }

    protected function assertSufficientStockForProductOrderDetails($product, $quantity, $order_type): void
    {
        // Validate stock (avoid overselling). Throws RuntimeException on insufficient stock.
        try {
            if ($product && isset($product->id)) {
                // Do not enforce stock for courses (order_type=3 / product_type=3).
                // These are not inventory-tracked and should not block purchase.
                if ((int) $order_type === 3 || (isset($product->product_type) && (int) $product->product_type === 3)) {
                    return;
                }

                if (isset($product->product_type) && (int) $product->product_type === 2) {
                    // Do not enforce stock for digital products.
                    return;
                }

                // Product category 2 appears to be non-inventory tracked in this codebase.
                if (isset($product->product_category) && (int) $product->product_category === 2) {
                    return;
                }

                $requestedQty = (int) $quantity;
                if ($requestedQty < 1) {
                    $requestedQty = 1;
                }

                $rawTotalStock = $product->quantity_available ?? null;
                $rawTotalStockStr = is_string($rawTotalStock) ? trim($rawTotalStock) : $rawTotalStock;
                // When quantity_available is blank/null/non-numeric, treat as unlimited (do not block purchase).
                // When it is numeric (including 0), enforce stock validation.
                $totalStock = (is_numeric($rawTotalStockStr) ? (int) $rawTotalStockStr : null);

                if ($totalStock !== null) {
                    $soldByProductId = $this->getCompletedSoldQtyByProductIds([(int) $product->id]);
                    $soldQty = (int) ($soldByProductId[(int) $product->id] ?? 0);
                    $available = max(0, $totalStock - $soldQty);
                    if ($requestedQty > $available) {
                        throw new \RuntimeException('Insufficient stock. Available: ' . $available);
                    }
                }
            }
        } catch (\RuntimeException $e) {
            throw $e;
        } catch (\Throwable $e) {
            // If validation fails unexpectedly, do not block purchase.
        }
    }

    public function saveProductOrder($productId,
        $delivery_option,
        $pickup_date,
        $delivery_date,
        $delivery_full_name,
        $delivery_address_1,
        $delivery_address_2,
        $delivery_city,
        $delivery_state,
        $delivery_zip,
        $delivery_country,
        $delivery_mobile,
        $product_tax,
        $order_id,
        $askPickupSchedule,
        $quantity,
        $order_type,
        $subscription_cycle,
        $userid = 0,
        $coupon_discount_percentage = 0,
        $customerBillingAddress = null,
        $address_line = null,
        $defaultCountry = null,
        $carrierCode = null)
    {

        $product = $this->businessProductsRepository->getById($productId);

		// Validate stock before creating the order (avoid overselling).
		$this->assertSufficientStockForProductOrderDetails($product, $quantity, $order_type);

        if ($userid == 0) {
            $userid = \Auth::user()->id;
        }

        $order = $this->model->newInstance();
        $order->order_type = $order_type;
        $order->user_id = $userid;
        $order->to_user_id = $product->user_id;
        $order->business_id = $product->business_id;
        $order->product_id = $product->id;
        $order->delivery_option = $delivery_option;
        $order->subscription_cycle = $subscription_cycle;
        $order->subscription_type = $product->recurring_order;

        if ($order_type == 1) {
            $order->subscription_date = date('Y-m-d');
            $order->completed_cycles = 1;

            if ($product->recurring_order == 1) {
                $next_payment_date = date('Y-m-d', strtotime('+7 day'));
            } elseif ($product->recurring_order == 2) {
                $next_payment_date = date('Y-m-d', strtotime('+15 day'));
            } else {
                // $next_payment_date=date('Y-m-d', strtotime("+1 Month"));
                $next_payment_date = getRecurringMonthDate(date('Y-m-d'));
            }

            $order->next_payment_date = $next_payment_date;
        }

        $order->full_name = $delivery_full_name;
        $order->address_1 = $delivery_address_1;
        $order->address_2 = $delivery_address_2;
        $order->billing_address = $customerBillingAddress;
        $order->address_line = $address_line;
        $order->city = $delivery_city;
        $order->state = $delivery_state;
        $order->zip = $delivery_zip;
        $order->country = $delivery_country;
        $order->mobile = $delivery_mobile;
        $order->country_iso_code = $defaultCountry;
        $order->carrier_code = $carrierCode;

        if ($delivery_option == 0) {
            /*$order->full_name=$delivery_full_name;
            $order->address_1=$delivery_address_1;
            $order->address_2=$delivery_address_2;
            $order->city=$delivery_city;
            $order->state=$delivery_state;
            $order->zip=$delivery_zip;
            $order->country=$delivery_country;
            $order->mobile=$delivery_mobile;*/
        } else {
            if ($pickup_date) {
                $pickup_date1 = new \DateTime($pickup_date);
                $order_date_time = date_format($pickup_date1, 'Y-m-d H:i:s');
                $order->order_date_time = $order_date_time;
            }

            $order->mobile = $delivery_mobile;
        }

        $order->order_id = $order_id;
        $order->save();

        $totalQuantity = 0;
        $totalAmount = 0;

        $price = $product->price;
        $savedPriceSave = 0;

        if ($order_type == 1) {
            $shippingCost = $product->recurring_shipping_cost;
        } else {
            if ($delivery_option == 0) {
                if ($product->is_free_shipping == 1) {
                    $shippingCost = 0;
                } else {
                    $shippingCost = $product->shipping_cost;
                    if ($shippingCost == '') {
                        $shippingCost = 0;
                    }

                    if ($quantity == 2) {
                        $shippingCost = 0;
                        if ($product->shipping_cost_2 != '') {
                            $shippingCost = $product->shipping_cost_2;
                        }
                    } elseif ($quantity == 3) {
                        $shippingCost = 0;
                        if ($product->shipping_cost_3 != '') {
                            $shippingCost = $product->shipping_cost_3;
                        }
                    } elseif ($quantity == 4) {
                        $shippingCost = 0;
                        if ($product->shipping_cost_4 != '') {
                            $shippingCost = $product->shipping_cost_4;
                        }
                    } elseif ($quantity == 5) {
                        $shippingCost = 0;
                        if ($product->shipping_cost_5 != '') {
                            $shippingCost = $product->shipping_cost_5;
                        }
                    }
                }
            } else {
                $shippingCost = 0;
            }
        }

        if ($product->discount) {
            $savedPrice = ($product->discount / 100) * $product->price;
            $price = $product->price - $savedPrice;
            $savedPriceSave = ($product->discount / 100) * ($product->price * $quantity);
        }

        $subTotal = $price * $quantity;

        $totalQuantity = $totalQuantity + $quantity;
        $totalAmount = $totalAmount + $subTotal + $shippingCost;

        // Track amounts before applying coupon discount
        $totalAmountBeforeCoupon = $totalAmount;
        $discountedAmount = 0;

        if ($coupon_discount_percentage) {
            $discountedAmount = ($totalAmount / 100) * $coupon_discount_percentage;
            $totalAmount = $totalAmount - $discountedAmount;
        }

        $order_date_time = '0000-00-00 00:00:00';
        if ($askPickupSchedule == 1) {
            $order_date_time = $cart->order_date_time;
        }

        $this->businessProductsOrdersDetailsRepository->addProduct(
            $order->id,
            $product->id,
            $product->business_id,
            $userid,
            $quantity,
            $subTotal,
            $product->price,
            $product->discount,
            $shippingCost,
            0,
            $product->business_id,
            $order_date_time,
            0,
            0);

        $order->quantity = $totalQuantity;
        $totalShipping = (float) $shippingCost;
        $taxLines = [];
        if ($product && $product->business_id) {
            $taxLines = $this->getTaxLinesFromPage($product->business_id);
        } elseif ($product_tax) {
            $perc = $this->normalizePercent($product_tax);
            if ($perc > 0) {
                $taxLines = [['name' => 'Tax', 'percentage' => $perc]];
            }
        }

        $taxableAmount = $totalAmount - $totalShipping;
        $taxData = $this->computeTaxes($taxableAmount, $taxLines);
        $taxTotalAmount = (float) $taxData['total_amount'];
        $taxTotalPercentage = (float) $taxData['total_percentage'];

        $order->amount = $totalAmount + $taxTotalAmount;

        $t1 = $taxData['lines'][0] ?? null;
        $t2 = $taxData['lines'][1] ?? null;
        $t3 = $taxData['lines'][2] ?? null;

        $order->product_tax_name = $t1['name'] ?? null;
        $order->product_tax = isset($t1['percentage']) ? (float) $t1['percentage'] : $this->normalizePercent($product_tax);

        $order->product_tax_name_1 = $t2['name'] ?? null;
        $order->product_tax_1 = isset($t2['percentage']) ? (float) $t2['percentage'] : 0;

        $order->product_tax_name_2 = $t3['name'] ?? null;
        $order->product_tax_2 = isset($t3['percentage']) ? (float) $t3['percentage'] : 0;

        // Persist amounts for reporting
        $order->actual_amount = $totalAmountBeforeCoupon;
        $order->discount_percentage = $coupon_discount_percentage;
        $order->discount_price = $discountedAmount;
        $order->save();

        return $order;
    }

    public function updatePayment($transaction_id,
        $status,
        $order_id)
    {

        $payment = $this->getByOrderId($order_id);
        if ($payment) {
            $payment->payment_status = $status;
            $payment->transaction_id = $transaction_id;
            $payment->save();

            return $payment;
        }

        return true;
    }

    public function invalidPayment($transaction_id, $status, $order_id)
    {
        $payment = $this->getByOrderId($order_id);
        if ($payment) {
            $payment->payment_status = $status;
            $payment->transaction_id = $transaction_id;
            $payment->save();

            return $payment;
        }

        return true;
    }

    public function check_txnid($txnid)
    {
        return $this->model->where('transaction_id', '=', $txnid)->first();
    }

    public function makeSubscriptionPayment()
    {
        $todaysDate = date('Y-m-d');

        $recurringPayments = $this->model->where('subscription_cycle', '>', 'completed_cycles')->where('order_type', '=', 1)->where('next_payment_date', '=', $todaysDate)->where('subscription_payment_failed', '=', 0)->where('stop_recurring', '=', 0)->where('stop_payment', '=', 0)->where('stop_payment_seller', '=', 0)->get();

        if (count($recurringPayments) > 0) {
            foreach ($recurringPayments as $order) {
                if ($order->card_customer_id) {
                    if ($order->own_account_transfer == 0) {
                        // require_once app_path('library/stripe-php/init.php');
                        $stripe = [
                            'secret_key' => config('stripe_secret_key'),
                            'publishable_key' => config('stripe_publishable_key'),
                        ];

                        if (config('stripe_mode') == 'test') {
                            $source_token = 'tok_visa';
                        } else {
                            // $source_token=$stripeToken;
                            $source_token = '';
                        }

                        \Stripe\Stripe::setApiKey($stripe['secret_key']);
                    } else {
                        $payment_settings = app('App\\Repositories\\CommunityPaymentSettingRepository')->getByCmId($order->community_id);
                        $community = app('App\\Repositories\\CommunityRepository')->getById($order->community_id);

                        if ($payment_settings and $payment_settings->activate_stripe == 1 and $community and $community->take_community_payment_in_own_account == 1) {
                            $stripe = [
                                'secret_key' => $payment_settings->stripe_secret_key,
                                'publishable_key' => $payment_settings->stripe_publishable_key,
                            ];

                            if ($payment_settings->stripe_mode == 'test') {
                                $source_token = 'tok_visa';
                            } else {
                                $source_token = '';
                            }
                        } else {
                            $stripe = [
                                'secret_key' => config('stripe_secret_key'),
                                'publishable_key' => config('stripe_publishable_key'),
                            ];

                            if (config('stripe_mode') == 'test') {
                                $source_token = 'tok_visa';
                            } else {
                                $source_token = '';
                            }
                        }
                    }

                    $orderId = rand(111111, 999999);

                    $payingAmount = $order->amount * 100;

                    try {
                        $payDetails = \Stripe\Charge::create([
                            'customer' => $order->card_customer_id,
                            'amount' => $payingAmount,
                            'currency' => 'USD',
                            'description' => 'Product subscription',
                            'metadata' => [
                                'order_id' => $orderId,
                            ],
                        ]);
                    } catch (\Stripe\Error\Base $e) {
                        // echo ($e->getMessage());
                        continue;
                    } catch (\Stripe\Error\Card $e) {
                        // echo ($e->getMessage()); // I think this is the missing one
                        continue;
                    } catch (\Stripe\Error\Authentication $e) {
                        continue;
                        // a good one to catch
                    } catch (\Stripe\Error\InvalidRequest $e) {
                        continue;
                        // and catch this one just in case
                    } catch (Exception $e) {
                        continue;
                        // catch any non-stripe exceptions
                    }

                    $paymenyResponse = $payDetails->jsonSerialize();
                    if ($paymenyResponse['amount_refunded'] == 0 && empty($paymenyResponse['failure_code']) && $paymenyResponse['paid'] == 1 && $paymenyResponse['captured'] == 1) {
                        $amountPaid = $paymenyResponse['amount'];
                        $paymentStatus = $paymenyResponse['status'];
                        $amountPaid = $amountPaid / 100;

                        if ($paymentStatus == 'succeeded') {
                            if ($order->subscription_type == 1) {
                                $next_payment_date = date('Y-m-d', strtotime('+7 day'));
                            } elseif ($order->subscription_type == 2) {
                                $next_payment_date = date('Y-m-d', strtotime('+15 day'));
                            } else {
                                // $next_payment_date=date('Y-m-d', strtotime("+1 Month"));
                                $next_payment_date = getRecurringMonthDate(date('Y-m-d'));
                            }

                            $order->next_payment_date = $next_payment_date;
                            $order->completed_cycles = $order->completed_cycles + 1;
                            $order->save();

                            $neworder = $this->model->newInstance();
                            $neworder->amount = $amountPaid;
                            $neworder->order_date_time = date('Y-m-d');
                            $neworder->order_id = $orderId;
                            $neworder->parent_order_id = $order->id;
                            $neworder->transaction_id = $paymenyResponse['balance_transaction'];
                            $neworder->save();
                        } else {
                            $order->subscription_payment_failed = 1;
                            $order->save();
                        }
                    } else {
                        $order->subscription_payment_failed = 1;
                        $order->save();
                    }
                } else {
                    $order->subscription_payment_failed = 1;
                    $order->save();
                }
            }
        }
    }

    public function getPaymentTotalByMonth($community_id, $year, $month)
    {
        $business_ids = app('App\\Repositories\\PageRepository')->getActiveBusinessIds($community_id);
        $posts_ids = app('App\\Repositories\\PostRepository')->getPostsProductIds($community_id);

        $yearlmonth = $year.'-'.$month;

        $records = $this->model
        // ->where('community_id',"=",$community_id)
            ->where(function ($query) use ($community_id, $business_ids, $posts_ids) {
                $query->where('community_id', '=', $community_id)
                    ->orWhereIn('community_business_id', $business_ids)
                // ->orWhere('community_business_id', '=', $community_id)
                    ->orWhereIn('business_id', $business_ids)
                    ->orWhereIn('product_id', $posts_ids);
            })
            ->where('created_at', 'LIKE', $yearlmonth.'%')
            ->where('payment_status', '=', 'completed')
            ->groupBy('transaction_id')
            ->get();

        $amount = 0;

        foreach ($records as $record) {
            $amount = $amount + $record->amount;
        }

        return $amount;
    }

    public function allPaymentTotal()
    {
        $posts_ids = app('App\\Repositories\\PostRepository')->getPostsProductIdsAll();
        $records = $this->model
            ->where(function ($query) use ($posts_ids) {
                $query->where('community_id', '!=', 0)
                    ->orWhere('community_business_id', '!=', 0)
                    ->orWhere('business_id', '!=', 0)
                    ->orWhereIn('product_id', $posts_ids);
            })
            ->where('payment_status', '=', 'Completed')
            ->get();

        $amount = 0;

        foreach ($records as $record) {
            $amount = $amount + $record->amount;
        }

        return $amount;
    }
	
	public function getMyRevenue($userid)
    {
		$year=date("Y");
		$month=date("m");
		
		$yearlmonth = $year.'-'.$month;
        
        $posts_ids = app('App\\Repositories\\PostRepository')->getMyPostsProductIdsAll($userid);
        if (!is_array($posts_ids)) {
            $posts_ids = [];
        }

        $recordsQuery = $this->model
            ->whereIn('payment_status', ['Completed', 'completed'])
            ->where('created_at', 'LIKE', $yearlmonth.'%');
        $this->applySellerOrderWhere($recordsQuery, (int) $userid, $posts_ids);
        $this->applyUniqueTransactionFilter($recordsQuery);
        $records = $recordsQuery->get();

        $amount = 0;

        foreach ($records as $record) {
            $amount = $amount + $record->amount;
        }

        return $amount;
    }
	
	public function getMyTotalRevenue($userid)
    {
        $posts_ids = app('App\\Repositories\\PostRepository')->getMyPostsProductIdsAll($userid);
        if (!is_array($posts_ids)) {
            $posts_ids = [];
        }

        $recordsQuery = $this->model
            ->whereIn('payment_status', ['Completed', 'completed']);
        $this->applySellerOrderWhere($recordsQuery, (int) $userid, $posts_ids);
        $this->applyUniqueTransactionFilter($recordsQuery);
        $records = $recordsQuery->get();

        $amount = 0;

        foreach ($records as $record) {
            $amount = $amount + $record->amount;
        }

        return $amount;
    }
	
	public function getMyStoreRevenue($userid,$store_id)
    {
        $posts_ids = [];

        $recordsQuery = $this->model
            ->whereIn('payment_status', ['Completed', 'completed']);
        $this->applyStoreOrderWhere($recordsQuery, (int) $userid, $posts_ids,$store_id);
		
        $this->applyUniqueTransactionFilter($recordsQuery);
        $records = $recordsQuery->get();

        $amount = 0;

        foreach ($records as $record) {
            $amount = $amount + $record->amount;
        }

        return $amount;
    }
	
	// private function applyStoreOrderWhere($query, int $sellerUserId, array $sellerProductIds = [],$store_id, string $ordersTablePrefix = ''): void
	private function applyStoreOrderWhere($query, int $sellerUserId, array $sellerProductIds = [],$store_id='', string $ordersTablePrefix = ''): void
    {
        $ordersTablePrefix = (string) $ordersTablePrefix;
      //  $toUserIdCol = $ordersTablePrefix . 'to_user_id';
        $businessIdCol = $ordersTablePrefix . 'business_id';
        $communityBusinessIdCol = $ordersTablePrefix . 'community_business_id';
      //  $productIdCol = $ordersTablePrefix . 'product_id';

        $businessIdsSubquery = function ($sub) use ($sellerUserId,$store_id) {
            $sub->select('id')->from('pages')->where('id', $store_id);
        };

        $query->where(function ($q) use ($sellerUserId, $sellerProductIds, $businessIdCol, $communityBusinessIdCol, $businessIdsSubquery) {
            $q->whereIn($businessIdCol, $businessIdsSubquery)
                ->orWhereIn($communityBusinessIdCol, $businessIdsSubquery);
        });
    }

    private function applySellerOrderWhere($query, int $sellerUserId, array $sellerProductIds = [], string $ordersTablePrefix = ''): void
    {
        $ordersTablePrefix = (string) $ordersTablePrefix;
        $toUserIdCol = $ordersTablePrefix . 'to_user_id';
        $businessIdCol = $ordersTablePrefix . 'business_id';
        $communityBusinessIdCol = $ordersTablePrefix . 'community_business_id';
        $productIdCol = $ordersTablePrefix . 'product_id';

        $businessIdsSubquery = function ($sub) use ($sellerUserId) {
            $sub->select('id')->from('pages')->where('user_id', $sellerUserId);
        };

        $query->where(function ($q) use ($sellerUserId, $sellerProductIds, $toUserIdCol, $businessIdCol, $communityBusinessIdCol, $productIdCol, $businessIdsSubquery) {
            $q->where($toUserIdCol, '=', $sellerUserId)
                ->orWhereIn($businessIdCol, $businessIdsSubquery)
                ->orWhereIn($communityBusinessIdCol, $businessIdsSubquery);
            if (! empty($sellerProductIds)) {
                $q->orWhereIn($productIdCol, $sellerProductIds);
            }
        });
    }

    private function applyUniqueTransactionFilter($query, string $ordersTablePrefix = ''): void
    {
        try {
            if (! \Schema::hasColumn('business_products_orders', 'transaction_id')) {
                return;
            }
        } catch (\Throwable $e) {
            return;
        }

        $ordersTablePrefix = (string) $ordersTablePrefix;
        $txnCol = $ordersTablePrefix . 'transaction_id';
        $idCol = $ordersTablePrefix . 'id';

        $canonicalOrderIds = \DB::table('business_products_orders')
            ->select(\DB::raw('MIN(id)'))
            ->whereIn('payment_status', ['Completed', 'completed'])
            ->whereNotNull('transaction_id')
            ->where('transaction_id', '!=', '')
            ->groupBy('transaction_id');

        $query->where(function ($q) use ($txnCol, $idCol, $canonicalOrderIds) {
            $q->whereNull($txnCol)
                ->orWhere($txnCol, '=', '')
                ->orWhereIn($idCol, $canonicalOrderIds);
        });
    }

    public function getMyTopSellingProducts($userid, int $limit = 5): array
    {
        $userid = (int) $userid;
        $limit = (int) $limit;
        if ($userid <= 0) {
            return [];
        }
        if ($limit <= 0) {
            $limit = 5;
        }
        if ($limit > 20) {
            $limit = 20;
        }

        $qtyColumn = $this->detectOrderDetailsQuantityColumn();
        if (! $qtyColumn) {
            return [];
        }

        $posts_ids = app('App\\Repositories\\PostRepository')->getMyPostsProductIdsAll($userid);
        if (!is_array($posts_ids)) {
            $posts_ids = [];
        }

        $detailsTableName = \DB::getTablePrefix() . 'business_products_orders_details';
        $qualifiedQtyColumn = '`' . $detailsTableName . '`.`' . $qtyColumn . '`';

        $rows = \DB::table('business_products_orders_details')
            ->join('business_products_orders', 'business_products_orders.id', '=', 'business_products_orders_details.order_id')
            ->leftJoin('business_products', 'business_products.id', '=', 'business_products_orders_details.product_id')
            ->whereIn('business_products_orders.payment_status', ['Completed', 'completed'])
            ->whereNotNull('business_products.quantity_available')
            ->where('business_products.product_category', '!=', 2)
            ->select(
                'business_products_orders_details.product_id',
                'business_products.product_name',
                'business_products.quantity_available as stock_total',
                \DB::raw('SUM(' . $qualifiedQtyColumn . ') as sold_qty')
            )
            ->where(function ($q) use ($userid, $posts_ids) {
                $this->applySellerOrderWhere($q, (int) $userid, $posts_ids, 'business_products_orders.');
            })
                ->where(function ($q) {
                    $this->applyUniqueTransactionFilter($q, 'business_products_orders.');
                })
            ->groupBy('business_products_orders_details.product_id', 'business_products.product_name', 'business_products.quantity_available')
            ->orderByDesc('sold_qty')
            ->limit($limit)
            ->get();

        $result = [];
        foreach ($rows as $row) {
            $pid = (int) ($row->product_id ?? 0);
            if ($pid <= 0) {
                continue;
            }
            $result[] = [
                'product_id' => $pid,
                'product_name' => (string) ($row->product_name ?? ('Product #' . $pid)),
                'sold_qty' => (int) ($row->sold_qty ?? 0),
                'stock_total' => (int) ($row->stock_total ?? 0),
                'stock_available' => max(0, (int) ($row->stock_total ?? 0) - (int) ($row->sold_qty ?? 0)),
            ];
        }

        return $result;
    }
	
	public function getMyCustomers($userid)
    {		
		$posts_ids = app('App\\Repositories\\PostRepository')->getMyPostsProductIdsAll($userid);
        if (!is_array($posts_ids)) {
            $posts_ids = [];
        }
        $recordsQuery = $this->model
            ->whereIn('payment_status', ['Completed', 'completed']);
        $this->applySellerOrderWhere($recordsQuery, (int) $userid, $posts_ids);
        $this->applyUniqueTransactionFilter($recordsQuery);
        $records = $recordsQuery
            ->groupBy('user_id')
            ->count();
        return $records;
    }
	
	public function getMyCustomerIds($userid)
    {		
		$posts_ids = app('App\\Repositories\\PostRepository')->getMyPostsProductIdsAll($userid);
        if (!is_array($posts_ids)) {
            $posts_ids = [];
        }
        $recordsQuery = $this->model
            ->whereIn('payment_status', ['Completed', 'completed']);
        $this->applySellerOrderWhere($recordsQuery, (int) $userid, $posts_ids);
        $this->applyUniqueTransactionFilter($recordsQuery);
        $records = $recordsQuery
            ->groupBy('user_id')
            ->pluck('user_id');
        return $records;
    }

    public function getByProductIdSuccess($product_id, $order_id)
    {
        // $records= $this->model->where('product_id', '=', $product_id)
        // $records= $this->model
        /* ->where('payment_status', '=', 'Completed') */
        /*->where(function($query) use($order_id) {
            $query->where('id', '=', $order_id)
            ->orWhere('parent_order_id', '=', $order_id);
        })*/

        $records = $this->model->where('parent_order_id', '=', $order_id)
            ->orderBy('created_at', 'desc')
            ->get();

        return $records;
    }

    public function getBusinessPaymentsUsersByCm($community_id)
    {
        $business_ids = app('App\\Repositories\\PageRepository')->getActiveBusinessIds($community_id);
        $posts_ids = app('App\\Repositories\\PostRepository')->getPostsProductIds($community_id);

        $records = $this->model
            ->where(function ($query) use ($community_id, $business_ids, $posts_ids) {
                $query->where('community_id', '=', $community_id)
                    ->orWhereIn('community_business_id', $business_ids)
                    ->orWhereIn('business_id', $business_ids)
                    ->orWhereIn('product_id', $posts_ids);
            })
            ->where('payment_status', '=', 'Completed')
            ->pluck('to_user_id');

        return is_object($records) && method_exists($records, 'toArray') ? $records->toArray() : $records;
    }

    public function getUserPaymentByCommunity($community_id, $user_id)
    {
        $business_ids = app('App\\Repositories\\PageRepository')->getActiveBusinessIdsByUser($community_id, $user_id);
        $posts_ids = app('App\\Repositories\\PostRepository')->getPostsProductIdsByUser($community_id, $user_id);

        $records = $this->model
        // ->where('community_id',"=",$community_id)
            ->where(function ($query) use ($business_ids, $posts_ids) {
                $query->orWhereIn('community_business_id', $business_ids)
                    ->orWhereIn('business_id', $business_ids)
                    ->orWhereIn('product_id', $posts_ids);
            })
            ->where('payment_status', '=', 'Completed')
            ->groupBy('transaction_id')
            ->get();

        $amount = 0;

        foreach ($records as $record) {
            $amount = $amount + $record->amount;
        }

        return $amount;
    }

    public function getProductPaymentsByUserCm($community_id, $paginate = 10)
    {
        $business_ids = app('App\\Repositories\\PageRepository')->getActiveBusinessIds($community_id);
        $posts_ids = app('App\\Repositories\\PostRepository')->getPostsProductIds($community_id);

        $records = $this->model
            ->where(function ($query) use ($community_id, $business_ids, $posts_ids) {
                $query->where('community_id', '=', $community_id)
                    ->orWhereIn('community_business_id', $business_ids)
                    ->orWhereIn('business_id', $business_ids)
                    ->orWhereIn('product_id', $posts_ids);
            })
            ->where('payment_status', '=', 'Completed')
            ->groupBy('transaction_id')
            ->paginate($paginate);

        return $records;
    }
    
    /**
     * Create Canada Post shipment for completed order
     */
    protected function createCanadaPostShipment($order, $shippingInfo, $cartList)
    {
        try {
            $canadaPostService = app('App\Services\CanadaPostService');
            
            // Get business information
            $business = app('App\Repositories\PageRepository')->getById($order->business_id);
            

            
            // Check for postal code mismatch
            $rateCalculationPostal = $shippingInfo['calculated_for_postal_code'] ?? null;
            if ($rateCalculationPostal && $rateCalculationPostal !== $order->zip) {
                
                // Optionally recalculate shipping cost here if needed
                // $newRates = $canadaPostService->getRates($business->zip, $order->zip, $packageData['weight']);
            }
            
            // Build cart array from order_details if no cart list provided (handles multiple products per order)
            $cartArray = [];
            if ($cartList && count($cartList) > 0) {
                // Use provided cart list (from createShipmentAfterPayment)
                $cartArray = $cartList->toArray();
            } else {
                // Get order details from database - this is where product_id lives!
                $orderDetails = $this->businessProductsOrdersDetailsRepository->getByOrderId($order->id);
                
                foreach ($orderDetails as $detail) {
                    // Get product information to check category and get dimensions
                    $product = \DB::table('business_products')->where('id', $detail->product_id)->first();
                    
                    // Initialize default values
                    $variant = null;
                    $variantId = $detail->productsizeid ?: $detail->product_color_id;
                    $variantWeight = 0.5; // Default weight
                    $variantLength = 10; // Default dimensions in cm
                    $variantWidth = 10;
                    $variantHeight = 5;
                    
                    // Only get variant data for products with category 1 or 2
                    if ($product && in_array($product->product_category, [1, 2])) {
                        if ($variantId) {
                            $variant = \DB::table('business_products_category_values')
                                ->where('id', $variantId)
                                ->first();
                        }
                        
                        // Extract variant-specific dimensions and weight
                        if ($variant) {
                            $variantWeight = $variant->variant_weight ?? 0.5;
                            $variantLength = $variant->variant_length ?? 10;
                            $variantWidth = $variant->variant_width ?? 10;
                            $variantHeight = $variant->variant_height ?? 5;
                            
                            // Convert weight to kg if needed (check variant_weight_unit)
                            if (isset($variant->variant_weight_unit) && $variant->variant_weight_unit === 'g') {
                                $variantWeight = $variantWeight / 1000; // Convert grams to kg
                            }
                            
                            // Convert dimensions to cm if needed (check variant_dimensions_unit)
                            if (isset($variant->variant_dimensions_unit) && $variant->variant_dimensions_unit === 'mm') {
                                $variantLength = $variantLength / 10; // Convert mm to cm
                                $variantWidth = $variantWidth / 10;
                                $variantHeight = $variantHeight / 10;
                            }
                        }
                    } else if ($product && $product->product_category == 0) {
                        // For category 0 products, use data from product table only
                        $variantWeight = $product->weight ?? 0.5;
                        $variantLength = $product->length ?? 10;
                        $variantWidth = $product->width ?? 10;
                        $variantHeight = $product->height ?? 5;
                        
                        // Convert weight to kg if product table stores in grams
                        if (isset($product->weight_unit) && $product->weight_unit === 'g') {
                            $variantWeight = $variantWeight / 1000;
                        }
                        
                        // Convert dimensions to cm if product table stores in mm
                        if (isset($product->dimensions_unit) && $product->dimensions_unit === 'mm') {
                            $variantLength = $variantLength / 10;
                            $variantWidth = $variantWidth / 10;
                            $variantHeight = $variantHeight / 10;
                        }
                    }
                    
                    $cartArray[] = [
                        'product_id' => $detail->product_id,  // From order_details table!
                        'quantity' => $detail->quantity,
                        'variant_id' => $variantId,
                        'variant_data' => $variant,
                        'weight' => $variantWeight,
                        'length' => $variantLength,
                        'width' => $variantWidth,
                        'height' => $variantHeight
                    ];
                }
            }
            
            // Remove old loop that depended on cartList parameter
            /*
            // Calculate package data from cart items
            $cartArray = [];
            foreach ($cartList as $cartItem) {
                // Get product information to check category and get dimensions
                $productId = isset($cartItem->product_id) ? $cartItem->product_id : (isset($cartItem->product) ? $cartItem->product->id : null);
                $product = \DB::table('business_products')->where('id', $productId)->first();
                
                // Initialize default values
                $variant = null;
                */ // End comment to remove old code
            
            $packageData = $canadaPostService->calculatePackageFromCartItems($cartArray);
            
            // Build shipment data
            $shipmentData = [
                'service_code' => $shippingInfo['service_code'],
                'origin_postal_code' => $business->zip,
                'sender' => [
                    'name' => $business->title,  // Use 'title' field for business name
                    'company' => $business->title,
                    'phone' => $business->business_contact ?? '555-555-5555',  // Use 'business_contact' 
                    'address' => $business->street ?? $business->full_address ?? 'Business Address',  // Use 'street' or 'full_address'
                    'city' => $business->city ?? 'City',
                    'province' => $this->getProvinceCode($business->state ?? 'ON'),
                    'postal_code' => $business->zip
                ],
                'destination' => [
                    'name' => $order->full_name,
                    'address' => $order->address_1,
                    'city' => $order->city,
                    'province' => $this->getProvinceCode($order->state),
                    'country_code' => 'CA',
                    'postal_code' => $order->zip
                ],
                'notification_email' => $this->getCustomerEmail($order),
                'weight' => $packageData['weight'],
                'dimensions' => [
                    'length' => $packageData['length'],
                    'width' => $packageData['width'],
                    'height' => $packageData['height']
                ],
                'customer_ref_1' => $order->order_id, // Include order ID as reference
                'customer_ref_2' => 'Business: ' . $business->title // Include business name as reference
            ];
            
            // Create shipment in Canada Post
            $shipment = $canadaPostService->createShipment(
                $order->order_id,
                $order->user_id,
                $order->business_id,
                $shipmentData,
                $cartArray
            );
            
            if ($shipment) {
                // Persist tracking + method on the order itself.
                if (empty($order->tracking_number) && !empty($shipment->tracking_pin) && $shipment->tracking_pin !== 'PROCESSING') {
                    $order->tracking_number = $shipment->tracking_pin;
                }
                $this->setOrderFieldIfExists($order, 'shipping_carrier', 'canada_post');
                $this->setOrderFieldIfExists($order, 'shipping_service_code', (string) ($shipmentData['service_code'] ?? $order->cp_service_code ?? ''));

                // Best-effort: carry over service name if we have it on the order.
                if (!empty($order->shipping_service_name)) {
                    $this->setOrderFieldIfExists($order, 'shipping_service_name', (string) $order->shipping_service_name);
                }
                $this->setOrderFieldIfExists($order, 'shipping_cost', (float) ($shippingInfo['cost'] ?? $order->cp_shipping_cost ?? 0));

                $order->save();
            }
            
        } catch (\Exception $e) {
        }
    }
    
    /**
     * Create Canada Post shipment after payment completion
     */
    public function createShipmentAfterPayment($order)
    {
        try {
            // Only Canada Post carrier should create CP shipments.
            $business = app('App\\Repositories\\PageRepository')->getById($order->business_id);
            $carrier = $business && !empty($business->shipping_carrier) ? $business->shipping_carrier : 'usps';
            if (!in_array($carrier, ['usps', 'canada_post'], true)) {
                $carrier = 'usps';
            }

            if ($carrier !== 'canada_post') {
                return;
            }

            $this->createCanadaPostShipmentForOrder($order);
            
            // Clear the pending shipment session data
            session()->forget('canada_post_pending_shipment');
            
        } catch (\Exception $e) {
        }
    }

    /**
     * Convert province names to Canada Post province codes
     */
    private function getProvinceCode($province)
    {
        $provinceMap = [
            // Full names to codes
            'Ontario' => 'ON',
            'Quebec' => 'QC',
            'British Columbia' => 'BC',
            'Alberta' => 'AB',
            'Manitoba' => 'MB',
            'Saskatchewan' => 'SK',
            'Nova Scotia' => 'NS',
            'New Brunswick' => 'NB',
            'Newfoundland and Labrador' => 'NL',
            'Prince Edward Island' => 'PE',
            'Northwest Territories' => 'NT',
            'Nunavut' => 'NU',
            'Yukon' => 'YT',
            // Handle some common non-Canadian provinces by defaulting
            'Maharashtra' => 'ON',
            'California' => 'BC',
            'New York' => 'ON',
            // Already codes (return as is)
            'ON' => 'ON',
            'QC' => 'QC',
            'BC' => 'BC',
            'AB' => 'AB',
            'MB' => 'MB',
            'SK' => 'SK',
            'NS' => 'NS',
            'NB' => 'NB',
            'NL' => 'NL',
            'PE' => 'PE',
            'NT' => 'NT',
            'NU' => 'NU',
            'YT' => 'YT'
        ];

        return $provinceMap[$province] ?? 'ON'; // Default to Ontario
    }
    
    /**
     * Get customer email for shipping notifications
     */
    private function getCustomerEmail($order)
    {
        // Try to get email from order first
        if (!empty($order->email)) {
            return $order->email;
        }
        
        // Try to get email from user
        if ($order->user_id) {
            $user = \App\Models\User::find($order->user_id);
            if ($user && !empty($user->email)) {
                return $user->email;
            }
        }
        
        // Fallback to a default email
        return 'noreply@' . (config('app.url') ? parse_url(config('app.url'), PHP_URL_HOST) : 'example.com');
    }
    
    /**
     * Update tracking numbers from Canada Post API for all shipments with PROCESSING status
     */
    public function updateCanadaPostTrackingNumbers()
    {
        try {
            $shipmentsToUpdate = \DB::table('cp_shipments')
                ->where('tracking_pin', 'PROCESSING')
                ->where('shipment_id', '!=', '')
                ->where('created_at', '>', \Carbon\Carbon::now()->subDays(30))
                ->limit(50) // Process in batches
                ->get();

            $updatedCount = 0;
            $failedCount = 0;

            foreach ($shipmentsToUpdate as $shipment) {
                $trackingNumber = $this->queryCanadaPostForTracking($shipment->shipment_id);
                
                if ($trackingNumber && $trackingNumber !== 'PROCESSING') {
                    // Update the tracking number
                    \DB::table('cp_shipments')
                        ->where('id', $shipment->id)
                        ->update([
                            'tracking_pin' => $trackingNumber,
                            'tracking_updated_at' => \Carbon\Carbon::now()
                        ]);

                    // Keep order table in sync so UI shows tracking immediately.
                    \DB::table('business_products_orders')
                        ->where('order_id', $shipment->order_id)
                        ->where(function ($q) {
                            $q->whereNull('tracking_number')
                                ->orWhere('tracking_number', '=', '')
                                ->orWhere('tracking_number', '=', 'PROCESSING');
                        })
                        ->update([
                            'tracking_number' => $trackingNumber,
                        ]);
                    
                    $updatedCount++;
                    
                } else {
                    $failedCount++;
                }
                
                // Add small delay to avoid overwhelming the API
                usleep(100000); // 0.1 second delay
            }

            return [
                'total_processed' => count($shipmentsToUpdate),
                'updated' => $updatedCount,
                'failed' => $failedCount
            ];

        } catch (\Exception $e) {
            
            return [
                'error' => $e->getMessage(),
                'total_processed' => 0,
                'updated' => 0,
                'failed' => 0
            ];
        }
    }

    /**
     * Query Canada Post API for tracking number using shipment ID
     */
    private function queryCanadaPostForTracking($shipmentId)
    {
        try {
            $username = config('canada-post-api-username');
            $password = config('canada-post-api-password');
            $customerNumber = config('canada-post-api-customer_number'); // Use underscore to match service

            if (!$username || !$password || !$customerNumber) {
                return null;
            }

            // Get base URL based on environment (sandbox vs production)
            $baseUrl = config('canada-post-api-environment') === 'production' 
                ? 'https://soa-gw.canadapost.ca' 
                : 'https://ct.soa-gw.canadapost.ca';

            // Canada Post shipment status endpoint
            $url = "{$baseUrl}/rs/{$customerNumber}/{$customerNumber}/shipment/{$shipmentId}/details";

            $ch = curl_init();
            curl_setopt_array($ch, [
                CURLOPT_URL => $url,
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_HTTPHEADER => [
                    'Accept: application/vnd.cpc.shipment-v8+xml',
                    'Authorization: Basic ' . base64_encode($username . ':' . $password),
                    'Accept-language: en-CA'
                ],
                CURLOPT_TIMEOUT => 30,
                CURLOPT_SSL_VERIFYPEER => true
            ]);

            $response = curl_exec($ch);
            $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            curl_close($ch);

            if ($httpCode === 200 && $response) {
                // Parse XML response to extract tracking number
                $xml = simplexml_load_string($response);
                
                if ($xml && isset($xml->{'shipment-info'}->{'tracking-pin'})) {
                    $trackingPin = (string)$xml->{'shipment-info'}->{'tracking-pin'};
                    
                    // Validate tracking number format (Canada Post tracking numbers are typically 16 digits)
                    if (strlen($trackingPin) >= 10 && is_numeric($trackingPin)) {
                        return $trackingPin;
                    }
                }
            }

            return null;

        } catch (\Exception $e) {
            
            return null;
        }
    }
}