<?php

namespace App\Services\Courier;

use App\Models\Order;
use App\Models\User;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class CustomerCourierPerformanceService
{
    private const SUPPORTED_COURIERS = [
        'steadfast' => 'SteadFast',
        'pathao' => 'Pathao',
        'redx' => 'RedX',
        'paperfly' => 'Paperfly',
        'inhouse' => 'Inhouse',
    ];
    private const DELIVERED_STATUSES = ['delivered', 'completed'];
    private const RETURNED_STATUSES = ['returned', 'return_pending'];
    private const CANCELLED_FAILED_STATUSES = ['canceled', 'cancelled', 'failed'];

    public function summarizeForUser(User $user): array
    {
        $summary = $this->buildSummary([
            'user_ids' => [$user->id],
            'phone_variants' => $this->phoneVariants($user->phone),
            'email_variants' => $this->emailVariants($user->email),
        ]);

        return array_merge($summary, [
            'subject' => [
                'type' => 'customer',
                'name' => trim(($user->f_name ?? '') . ' ' . ($user->l_name ?? '')) ?: 'Customer',
                'phone' => $user->phone,
                'email' => $user->email,
                'customer_id' => $user->id,
            ],
        ]);
    }

    public function summarizeForOrder(Order $order): array
    {
        $deliveryAddress = $this->decodeJson($order->delivery_address);
        $receiverDetails = $this->decodeJson($order->receiver_details);

        $name = data_get($receiverDetails, 'contact_person_name')
            ?: data_get($deliveryAddress, 'contact_person_name')
            ?: trim(($order?->customer?->f_name ?? '') . ' ' . ($order?->customer?->l_name ?? ''))
            ?: 'Customer';

        $phone = data_get($receiverDetails, 'contact_person_number')
            ?: data_get($deliveryAddress, 'contact_person_number')
            ?: $order?->customer?->phone;

        $email = data_get($receiverDetails, 'contact_person_email')
            ?: data_get($deliveryAddress, 'contact_person_email')
            ?: $order?->customer?->email;

        $summary = $this->buildSummary([
            'user_ids' => (! $order->is_guest && $order->user_id) ? [$order->user_id] : [],
            'phone_variants' => $this->phoneVariants($phone),
            'email_variants' => $this->emailVariants($email),
        ]);

        return array_merge($summary, [
            'subject' => [
                'type' => 'order',
                'name' => $name,
                'phone' => $phone,
                'email' => $email,
                'order_id' => $order->id,
            ],
        ]);
    }

    public function supportedCouriers(): array
    {
        return self::SUPPORTED_COURIERS;
    }

    private function buildSummary(array $filters): array
    {
        $rows = $this->emptyRows();
        $orderColumnMap = $this->orderColumnMap();
        $providerExpression = $this->providerExpression($orderColumnMap);
        $cancelledFailedExpression = $this->cancelledFailedExpression($orderColumnMap);

        $aggregates = Order::query()
            ->when($orderColumnMap['has_shipment_table'], function ($query) {
                $query->leftJoin('order_courier_shipments', 'orders.id', '=', 'order_courier_shipments.order_id');
            })
            ->when($orderColumnMap['has_customer_address_join'], function ($query) {
                $query->leftJoin('customer_addresses', 'orders.delivery_address_id', '=', 'customer_addresses.id');
            })
            ->selectRaw("{$providerExpression} as provider_key")
            ->selectRaw('COUNT(DISTINCT orders.id) as total_orders')
            ->selectRaw("SUM(CASE WHEN orders.order_status IN ('" . implode("','", self::DELIVERED_STATUSES) . "') THEN 1 ELSE 0 END) as delivered_orders")
            ->selectRaw("SUM(CASE WHEN orders.order_status IN ('" . implode("','", self::RETURNED_STATUSES) . "') THEN 1 ELSE 0 END) as returned_orders")
            ->selectRaw("SUM({$cancelledFailedExpression}) as cancelled_failed_orders")
            ->whereIn(DB::raw($providerExpression), array_keys(self::SUPPORTED_COURIERS))
            ->where(function (Builder $query) use ($filters, $orderColumnMap) {
                $applied = false;

                if (!empty($filters['user_ids'])) {
                    $userIds = collect($filters['user_ids'])
                        ->filter(fn ($value) => filled($value))
                        ->values()
                        ->all();

                    if (!empty($userIds)) {
                        if ($orderColumnMap['has_user_id']) {
                            $query->orWhereIn('orders.user_id', $userIds);
                            $applied = true;
                        }

                        if ($orderColumnMap['has_customer_id']) {
                            $query->orWhereIn('orders.customer_id', array_map('strval', $userIds));
                            $applied = true;
                        }

                    }
                }

                if (!empty($filters['phone_variants'])) {
                    if ($orderColumnMap['has_delivery_address']) {
                        $this->applyJsonInFilter($query, 'orders.delivery_address', '$.contact_person_number', $filters['phone_variants']);
                        $this->applyJsonInFilter($query, 'orders.delivery_address', '$.phone', $filters['phone_variants']);
                        $applied = true;
                    }

                    if ($orderColumnMap['has_receiver_details']) {
                        $this->applyJsonInFilter($query, 'orders.receiver_details', '$.contact_person_number', $filters['phone_variants']);
                        $this->applyJsonInFilter($query, 'orders.receiver_details', '$.phone', $filters['phone_variants']);
                        $applied = true;
                    }

                    if ($orderColumnMap['has_shipping_address_data']) {
                        $this->applyJsonInFilter($query, 'orders.shipping_address_data', '$.contact_person_number', $filters['phone_variants']);
                        $this->applyJsonInFilter($query, 'orders.shipping_address_data', '$.phone', $filters['phone_variants']);
                        $applied = true;
                    }

                    if ($orderColumnMap['address_has_phone']) {
                        $this->applyColumnInFilter($query, 'customer_addresses.contact_person_number', $filters['phone_variants']);
                        $applied = true;
                    }
                }

                if (!empty($filters['email_variants'])) {
                    if ($orderColumnMap['has_delivery_address']) {
                        $this->applyJsonInFilter($query, 'orders.delivery_address', '$.contact_person_email', $filters['email_variants']);
                        $this->applyJsonInFilter($query, 'orders.delivery_address', '$.email', $filters['email_variants']);
                        $applied = true;
                    }

                    if ($orderColumnMap['has_receiver_details']) {
                        $this->applyJsonInFilter($query, 'orders.receiver_details', '$.contact_person_email', $filters['email_variants']);
                        $this->applyJsonInFilter($query, 'orders.receiver_details', '$.email', $filters['email_variants']);
                        $applied = true;
                    }

                    if ($orderColumnMap['has_shipping_address_data']) {
                        $this->applyJsonInFilter($query, 'orders.shipping_address_data', '$.contact_person_email', $filters['email_variants']);
                        $this->applyJsonInFilter($query, 'orders.shipping_address_data', '$.email', $filters['email_variants']);
                        $applied = true;
                    }

                    if ($orderColumnMap['address_has_email']) {
                        $this->applyColumnInFilter($query, $orderColumnMap['address_email_column'], $filters['email_variants']);
                        $applied = true;
                    }
                }

                if (! $applied) {
                    $query->whereRaw('1 = 0');
                }
            })
            ->groupBy(DB::raw($providerExpression))
            ->get();

        foreach ($aggregates as $aggregate) {
            $providerKey = data_get($aggregate, 'provider_key');

            if (!array_key_exists($providerKey, $rows)) {
                continue;
            }

            $totalOrders = (int) data_get($aggregate, 'total_orders', 0);
            $deliveredOrders = (int) data_get($aggregate, 'delivered_orders', 0);
            $returnedOrders = (int) data_get($aggregate, 'returned_orders', 0);
            $cancelledFailedOrders = (int) data_get($aggregate, 'cancelled_failed_orders', 0);

            $rows[$providerKey] = array_merge($rows[$providerKey], [
                'total_orders' => $totalOrders,
                'delivered_orders' => $deliveredOrders,
                'returned_orders' => $returnedOrders,
                'cancelled_failed_orders' => $cancelledFailedOrders,
                'delivery_success_ratio' => $this->ratio($deliveredOrders, $totalOrders),
                'return_ratio' => $this->ratio($returnedOrders, $totalOrders),
            ]);
        }

        $rowCollection = collect(array_values($rows));

        return [
            'rows' => $rowCollection->all(),
            'has_any_history' => $rowCollection->sum('total_orders') > 0,
            'totals' => [
                'total_orders' => $rowCollection->sum('total_orders'),
                'delivered_orders' => $rowCollection->sum('delivered_orders'),
                'returned_orders' => $rowCollection->sum('returned_orders'),
                'cancelled_failed_orders' => $rowCollection->sum('cancelled_failed_orders'),
            ],
        ];
    }

    private function orderColumnMap(): array
    {
        $hasCustomerAddressJoin = Schema::hasTable('customer_addresses')
            && Schema::hasColumn('orders', 'delivery_address_id')
            && Schema::hasColumn('customer_addresses', 'id');

        return [
            'has_shipment_table' => Schema::hasTable('order_courier_shipments'),
            'has_user_id' => Schema::hasColumn('orders', 'user_id'),
            'has_customer_id' => Schema::hasColumn('orders', 'customer_id'),
            'has_delivery_address' => Schema::hasColumn('orders', 'delivery_address'),
            'has_receiver_details' => Schema::hasColumn('orders', 'receiver_details'),
            'has_shipping_address_data' => Schema::hasColumn('orders', 'shipping_address_data'),
            'has_shipping_type' => Schema::hasColumn('orders', 'shipping_type'),
            'has_delivery_type' => Schema::hasColumn('orders', 'delivery_type'),
            'has_delivery_service_name' => Schema::hasColumn('orders', 'delivery_service_name'),
            'has_shipment_provider' => Schema::hasTable('order_courier_shipments') && Schema::hasColumn('order_courier_shipments', 'provider'),
            'has_shipment_booking_status' => Schema::hasTable('order_courier_shipments') && Schema::hasColumn('order_courier_shipments', 'booking_status'),
            'has_customer_address_join' => $hasCustomerAddressJoin,
            'address_has_phone' => $hasCustomerAddressJoin && Schema::hasColumn('customer_addresses', 'contact_person_number'),
            'address_has_email' => $hasCustomerAddressJoin
                && (Schema::hasColumn('customer_addresses', 'contact_person_email') || Schema::hasColumn('customer_addresses', 'email')),
            'address_email_column' => $hasCustomerAddressJoin && Schema::hasColumn('customer_addresses', 'contact_person_email')
                ? 'customer_addresses.contact_person_email'
                : ($hasCustomerAddressJoin && Schema::hasColumn('customer_addresses', 'email')
                    ? 'customer_addresses.email'
                    : null),
        ];
    }

    private function providerExpression(array $orderColumnMap): string
    {
        $shipmentProvider = $orderColumnMap['has_shipment_provider']
            ? "NULLIF(TRIM(order_courier_shipments.provider), '')"
            : 'NULL';

        $legacyProvider = 'NULL';
        if ($orderColumnMap['has_delivery_service_name']) {
            if ($orderColumnMap['has_delivery_type']) {
                $legacyProvider = "CASE
                    WHEN orders.delivery_type = 'third_party_delivery' THEN NULLIF(TRIM(orders.delivery_service_name), '')
                    ELSE NULL
                END";
            } else {
                $legacyProvider = "NULLIF(TRIM(orders.delivery_service_name), '')";
            }
        }

        $inhouseConditionParts = [];
        if ($orderColumnMap['has_shipping_type']) {
            $inhouseConditionParts[] = "orders.shipping_type = 'inhouse_shipping'";
        }
        if ($orderColumnMap['has_delivery_type']) {
            $inhouseConditionParts[] = "orders.delivery_type IS NULL";
            $inhouseConditionParts[] = "orders.delivery_type <> 'third_party_delivery'";
        } else {
            $inhouseConditionParts[] = '1 = 1';
        }

        $inhouseCondition = implode(' OR ', $inhouseConditionParts);

        return "LOWER(COALESCE(
            {$shipmentProvider},
            {$legacyProvider},
            CASE WHEN {$inhouseCondition} THEN 'inhouse' END
        ))";
    }

    private function cancelledFailedExpression(array $orderColumnMap): string
    {
        $conditions = ["orders.order_status IN ('" . implode("','", self::CANCELLED_FAILED_STATUSES) . "')"];

        if ($orderColumnMap['has_shipment_booking_status']) {
            $conditions[] = "order_courier_shipments.booking_status = 'failed'";
        }

        return 'CASE WHEN ' . implode(' OR ', $conditions) . ' THEN 1 ELSE 0 END';
    }

    private function emptyRows(): array
    {
        return collect(self::SUPPORTED_COURIERS)->mapWithKeys(function ($label, $key) {
            return [
                $key => [
                    'provider_key' => $key,
                    'provider_name' => $label,
                    'total_orders' => 0,
                    'delivered_orders' => 0,
                    'returned_orders' => 0,
                    'cancelled_failed_orders' => 0,
                    'delivery_success_ratio' => 0.0,
                    'return_ratio' => 0.0,
                ],
            ];
        })->all();
    }

    private function ratio(int $numerator, int $denominator): float
    {
        if ($denominator <= 0) {
            return 0.0;
        }

        return round(($numerator / $denominator) * 100, 2);
    }

    private function applyJsonInFilter(Builder $query, string $column, string $path, array $values): void
    {
        $normalizedValues = collect($values)
            ->filter()
            ->map(fn ($value) => mb_strtolower(trim((string) $value)))
            ->unique()
            ->values()
            ->all();

        if (empty($normalizedValues)) {
            return;
        }

        $placeholder = implode(', ', array_fill(0, count($normalizedValues), '?'));
        $expression = "LOWER(CASE WHEN JSON_VALID({$column}) THEN JSON_UNQUOTE(JSON_EXTRACT({$column}, '{$path}')) END)";

        $query->orWhereRaw("{$expression} IN ({$placeholder})", $normalizedValues);
    }

    private function applyColumnInFilter(Builder $query, string $column, array $values): void
    {
        $normalizedValues = collect($values)
            ->filter()
            ->map(fn ($value) => mb_strtolower(trim((string) $value)))
            ->unique()
            ->values()
            ->all();

        if (empty($normalizedValues)) {
            return;
        }

        $placeholder = implode(', ', array_fill(0, count($normalizedValues), '?'));
        $expression = "LOWER(TRIM(COALESCE({$column}, '')))";

        $query->orWhereRaw("{$expression} IN ({$placeholder})", $normalizedValues);
    }

    private function phoneVariants(?string $phone): array
    {
        $digits = preg_replace('/\D+/', '', (string) $phone);

        if (blank($digits)) {
            return [];
        }

        if (str_starts_with($digits, '880') && strlen($digits) === 13) {
            $local = '0' . substr($digits, 3);
        } elseif (str_starts_with($digits, '01') && strlen($digits) === 11) {
            $local = $digits;
        } elseif (str_starts_with($digits, '1') && strlen($digits) === 10) {
            $local = '0' . $digits;
        } else {
            $local = $digits;
        }

        $international = str_starts_with($local, '0') ? '880' . substr($local, 1) : $local;

        return array_values(array_unique(array_filter([
            $local,
            '+' . $local,
            $international,
            '+' . $international,
        ])));
    }

    private function emailVariants(?string $email): array
    {
        $value = trim((string) $email);

        if (blank($value)) {
            return [];
        }

        return array_values(array_unique([
            mb_strtolower($value),
            $value,
        ]));
    }

    private function decodeJson(mixed $value): array
    {
        if (is_array($value)) {
            return $value;
        }

        if (blank($value)) {
            return [];
        }

        $decoded = json_decode((string) $value, true);

        return is_array($decoded) ? $decoded : [];
    }
}
