<?php

namespace App\Services;

use App\Data\AmadastCouriers;
use App\Data\PaymentGateways;
use App\Models\Order;
use App\Models\OrderItem;
use App\Models\ProductReview;
use App\Models\Ticket;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use InvalidArgumentException;

class AnalyticsService
{
    /** @var list<string> */
    public const PAID_STATUSES = ['paid', 'sent'];

    /**
     * @return array{from: Carbon, to: Carbon, label: string, period: string}
     */
    public function resolveRange(string $period, ?string $fromJalali = null, ?string $toJalali = null): array
    {
        $to = now()->endOfDay();

        return match ($period) {
            'today' => [
                'from'   => now()->startOfDay(),
                'to'     => $to,
                'label'  => 'امروز',
                'period' => 'today',
            ],
            'week' => [
                'from'   => now()->subDays(6)->startOfDay(),
                'to'     => $to,
                'label'  => '۷ روز اخیر',
                'period' => 'week',
            ],
            'month' => [
                'from'   => now()->subDays(29)->startOfDay(),
                'to'     => $to,
                'label'  => '۳۰ روز اخیر',
                'period' => 'month',
            ],
            'quarter' => [
                'from'   => now()->subDays(89)->startOfDay(),
                'to'     => $to,
                'label'  => '۹۰ روز اخیر',
                'period' => 'quarter',
            ],
            'half' => [
                'from'   => now()->subDays(179)->startOfDay(),
                'to'     => $to,
                'label'  => '۱۸۰ روز اخیر',
                'period' => 'half',
            ],
            'year' => [
                'from'   => now()->subDays(364)->startOfDay(),
                'to'     => $to,
                'label'  => '۳۶۵ روز اخیر',
                'period' => 'year',
            ],
            'all' => [
                'from'   => Carbon::create(2000, 1, 1)->startOfDay(),
                'to'     => $to,
                'label'  => 'کل بازه',
                'period' => 'all',
            ],
            'custom' => $this->resolveCustomRange($fromJalali, $toJalali),
            default => throw new InvalidArgumentException('بازه نامعتبر است.'),
        };
    }

    /**
     * @return array{from: Carbon, to: Carbon, label: string, period: string}
     */
    private function resolveCustomRange(?string $fromJalali, ?string $toJalali): array
    {
        if (!$fromJalali || !$toJalali) {
            throw new InvalidArgumentException('تاریخ شروع و پایان الزامی است.');
        }

        $from = $this->jalaliStringToCarbon($fromJalali)->startOfDay();
        $to   = $this->jalaliStringToCarbon($toJalali)->endOfDay();

        if ($from->gt($to)) {
            [$from, $to] = [$to->copy()->startOfDay(), $from->copy()->endOfDay()];
        }

        return [
            'from'   => $from,
            'to'     => $to,
            'label'  => Order::toJalaliString($from) . ' تا ' . Order::toJalaliString($to),
            'period' => 'custom',
        ];
    }

    public function jalaliStringToCarbon(string $jalali): Carbon
    {
        $jalali = strtr($jalali, [
            '۰'=>'0','۱'=>'1','۲'=>'2','۳'=>'3','۴'=>'4','۵'=>'5','۶'=>'6','۷'=>'7','۸'=>'8','۹'=>'9',
            '٠'=>'0','١'=>'1','٢'=>'2','٣'=>'3','٤'=>'4','٥'=>'5','٦'=>'6','٧'=>'7','٨'=>'8','٩'=>'9',
            '-' => '/',
        ]);

        if (!preg_match('#^(\d{3,4})/(\d{1,2})/(\d{1,2})$#', trim($jalali), $m)) {
            throw new InvalidArgumentException('فرمت تاریخ شمسی نامعتبر است.');
        }

        [$gy, $gm, $gd] = $this->jalaliToGregorian((int) $m[1], (int) $m[2], (int) $m[3]);

        return Carbon::create($gy, $gm, $gd, 0, 0, 0);
    }

    /**
     * @return array{0:int,1:int,2:int}
     */
    private function jalaliToGregorian(int $jy, int $jm, int $jd): array
    {
        $jy -= 979;
        $jm -= 1;
        $jd -= 1;
        $jDayNo = 365 * $jy + intdiv($jy, 33) * 8 + intdiv(($jy % 33) + 3, 4);
        for ($i = 0; $i < $jm; ++$i) {
            $jDayNo += ($i < 6) ? 31 : 30;
        }
        $jDayNo += $jd;
        $gDayNo = $jDayNo + 79;
        $gy = 1600 + 400 * intdiv($gDayNo, 146097);
        $gDayNo %= 146097;
        $leap = true;
        if ($gDayNo >= 36525) {
            $gDayNo--;
            $gy += 100 * intdiv($gDayNo, 36524);
            $gDayNo %= 36524;
            if ($gDayNo >= 365) {
                $gDayNo++;
            } else {
                $leap = false;
            }
        }
        $gy += 4 * intdiv($gDayNo, 1461);
        $gDayNo %= 1461;
        if ($gDayNo >= 366) {
            $leap = false;
            $gDayNo--;
            $gy += intdiv($gDayNo, 365);
            $gDayNo %= 365;
        }
        $gd = $gDayNo + 1;
        $salA = [0, 31, ($leap ? 29 : 28), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];
        for ($gm = 1; $gm <= 12 && $gd > $salA[$gm]; $gm++) {
            $gd -= $salA[$gm];
        }

        return [$gy, $gm, $gd];
    }

    /**
     * Full analytics payload for the selected range.
     *
     * @param array{from: Carbon, to: Carbon, label: string, period: string} $range
     */
    public function build(array $range): array
    {
        $from = $range['from'];
        $to   = $range['to'];

        $paidQuery = fn () => Order::query()
            ->whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to]);

        $allInRange = fn () => Order::query()->whereBetween('created_at', [$from, $to]);

        $ordersCount   = (clone $paidQuery())->count();
        $revenue       = (int) (clone $paidQuery())->sum('total_amount'); // کالا (بعد تخفیف) + هزینه ارسال
        $subtotalSum   = (int) (clone $paidQuery())->sum('subtotal');
        $shippingSum   = (int) (clone $paidQuery())->sum('shipping_cost');
        $payableSum    = (int) (clone $paidQuery())->sum('payable_amount');
        $netSales      = max(0, $payableSum - $shippingSum); // مبلغ پرداخت‌شده − هزینه ارسال
        $netSalesCash  = $this->netSalesForGateways($from, $to, PaymentGateways::cashGatewayIds());
        $netSalesInstallment = $this->netSalesForGateways($from, $to, PaymentGateways::installmentGatewayIds());
        $discountSum   = (int) (clone $paidQuery())->sum(DB::raw('COALESCE(product_discount,0) + COALESCE(coupon_discount,0)'));
        $couponSum     = (int) (clone $paidQuery())->sum('coupon_discount');
        $walletSum     = (int) (clone $paidQuery())->sum('wallet_used');
        $avgOrder      = $ordersCount > 0 ? (int) round($revenue / $ordersCount) : 0;
        $itemsSold     = (int) OrderItem::query()
            ->whereHas('order', function ($q) use ($from, $to) {
                $q->whereIn('status', self::PAID_STATUSES)
                  ->whereBetween('created_at', [$from, $to]);
            })
            ->sum('qty');

        $statusCounts = (clone $allInRange())
            ->select('status', DB::raw('COUNT(*) as total'))
            ->groupBy('status')
            ->pluck('total', 'status')
            ->all();

        $canceled = (int) ($statusCounts['canceled'] ?? 0);
        $returned = (int) ($statusCounts['returned'] ?? 0);
        $failed   = (int) ($statusCounts['failed'] ?? 0);
        $allCount = array_sum(array_map('intval', $statusCounts));

        $newCustomers = User::whereBetween('created_at', [$from, $to])->count();

        $customersWithOrders = (int) (clone $paidQuery())
            ->selectRaw('COUNT(DISTINCT user_id) as c')
            ->value('c');

        $buyerIds = (clone $paidQuery())->distinct()->pluck('user_id')->filter()->all();
        $returningCustomers = 0;
        if ($buyerIds !== []) {
            $returningCustomers = (int) Order::whereIn('user_id', $buyerIds)
                ->whereIn('status', self::PAID_STATUSES)
                ->where('created_at', '<', $from)
                ->distinct()
                ->count('user_id');
        }
        return [
            'range' => [
                'period'      => $range['period'],
                'label'       => $range['label'],
                'from_jalali' => Order::toJalaliString($from),
                'to_jalali'   => Order::toJalaliString($to),
            ],
            'kpis' => [
                'orders_count'       => $ordersCount,
                'items_sold'         => $itemsSold,
                'revenue'            => $revenue,
                'subtotal'           => $subtotalSum,
                'net_sales'             => $netSales,
                'net_sales_cash'        => $netSalesCash,
                'net_sales_installment' => $netSalesInstallment,
                'shipping'              => $shippingSum,
                'discounts'          => $discountSum,
                'coupon_discounts'   => $couponSum,
                'wallet_used'        => $walletSum,
                'avg_order_value'    => $avgOrder,
                'customers'          => $customersWithOrders,
                'new_customers'      => $newCustomers,
                'returning_customers'=> $returningCustomers,
                'canceled_orders'    => $canceled,
                'returned_orders'    => $returned,
                'failed_orders'      => $failed,
                'cancel_rate'        => $allCount > 0 ? round(($canceled / $allCount) * 100, 1) : 0,
                'reviews'            => ProductReview::whereBetween('created_at', [$from, $to])->count(),
                'tickets'            => Ticket::whereBetween('created_at', [$from, $to])->count(),
                'top_referrer'       => $this->topReferrerKpi($from, $to),
                'top_courier'        => $this->topCourierKpi($from, $to),
            ],
            'charts' => [
                'timeline'         => $this->timelineChart($from, $to),
                'status'           => $this->statusChart($statusCounts),
                'payment_methods'  => $this->paymentMethodsChart($from, $to),
                'shipping_methods' => $this->shippingMethodsChart($from, $to),
                'top_products'     => $this->topProductsChart($from, $to, 8),
                'referrers'        => $this->referrersChart($from, $to, 10),
            ],
            'tables' => [
                'top_products'     => $this->topProductsTable($from, $to, 15),
                'top_customers'    => $this->topCustomersTable($from, $to, 15),
                'payment_methods'  => $this->paymentMethodsTable($from, $to),
                'shipping_methods' => $this->shippingMethodsTable($from, $to),
                'coupons'          => $this->couponsTable($from, $to, 10),
                'daily'            => $this->dailyTable($from, $to),
                'referrers'        => $this->referrersTable($from, $to, 20),
            ],
        ];
    }

    private function timelineChart(Carbon $from, Carbon $to): array
    {
        $days = max(1, (int) $from->copy()->startOfDay()->diffInDays($to->copy()->startOfDay()) + 1);
        $groupByMonth = $days > 90;

        if ($groupByMonth) {
            $rows = Order::whereIn('status', self::PAID_STATUSES)
                ->whereBetween('created_at', [$from, $to])
                ->selectRaw("DATE_FORMAT(created_at, '%Y-%m-01') as bucket, COUNT(*) as orders, SUM(total_amount) as revenue, SUM(shipping_cost) as shipping")
                ->groupBy('bucket')
                ->orderBy('bucket')
                ->get()
                ->keyBy('bucket');

            $cursor = $from->copy()->startOfMonth();
            $end    = $to->copy()->startOfMonth();
            $labels = $orders = $revenue = $shipping = [];

            while ($cursor->lte($end)) {
                $key = $cursor->format('Y-m-d');
                $labels[]   = Order::toJalaliString($cursor);
                $orders[]   = (int) ($rows[$key]->orders ?? 0);
                $revenue[]  = (int) ($rows[$key]->revenue ?? 0);
                $shipping[] = (int) ($rows[$key]->shipping ?? 0);
                $cursor->addMonth();
            }

            return compact('labels', 'orders', 'revenue', 'shipping') + ['granularity' => 'month'];
        }

        $rows = Order::whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to])
            ->selectRaw('DATE(created_at) as bucket, COUNT(*) as orders, SUM(total_amount) as revenue, SUM(shipping_cost) as shipping')
            ->groupBy('bucket')
            ->get()
            ->keyBy('bucket');

        $labels = $orders = $revenue = $shipping = [];
        $cursor = $from->copy()->startOfDay();
        $end    = $to->copy()->startOfDay();

        while ($cursor->lte($end)) {
            $key = $cursor->format('Y-m-d');
            $labels[]   = Order::toJalaliString($cursor);
            $orders[]   = (int) ($rows[$key]->orders ?? 0);
            $revenue[]  = (int) ($rows[$key]->revenue ?? 0);
            $shipping[] = (int) ($rows[$key]->shipping ?? 0);
            $cursor->addDay();
        }

        return compact('labels', 'orders', 'revenue', 'shipping') + ['granularity' => 'day'];
    }

    private function statusChart(array $statusCounts): array
    {
        $map = [
            'failed'   => ['label' => 'در انتظار پرداخت', 'color' => '#ff9f43'],
            'paid'     => ['label' => 'در انتظار ارسال', 'color' => '#28c76f'],
            'sent'     => ['label' => 'ارسال شده', 'color' => '#00cfe8'],
            'canceled' => ['label' => 'لغو شده', 'color' => '#ea5455'],
            'returned' => ['label' => 'مرجوع شده', 'color' => '#82868b'],
        ];

        $labels = $values = $colors = [];
        foreach ($map as $status => $meta) {
            $count = (int) ($statusCounts[$status] ?? 0);
            if ($count <= 0) {
                continue;
            }
            $labels[] = $meta['label'];
            $values[] = $count;
            $colors[] = $meta['color'];
        }

        return compact('labels', 'values', 'colors');
    }

    private function paymentMethodsChart(Carbon $from, Carbon $to): array
    {
        $rows = Order::whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to])
            ->selectRaw("COALESCE(NULLIF(payment_method_name,''), payment_method, 'نامشخص') as name, COUNT(*) as orders, SUM(total_amount) as revenue")
            ->groupBy('name')
            ->orderByDesc('revenue')
            ->limit(8)
            ->get();

        return [
            'labels'  => $rows->pluck('name')->all(),
            'orders'  => $rows->pluck('orders')->map(fn ($v) => (int) $v)->all(),
            'revenue' => $rows->pluck('revenue')->map(fn ($v) => (int) $v)->all(),
        ];
    }

    private function shippingMethodsChart(Carbon $from, Carbon $to): array
    {
        $rows = Order::whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to])
            ->selectRaw("COALESCE(NULLIF(shipping_method_name,''), shipping_method_type, 'نامشخص') as name, COUNT(*) as orders, SUM(shipping_cost) as shipping")
            ->groupBy('name')
            ->orderByDesc('shipping')
            ->limit(8)
            ->get();

        return [
            'labels'   => $rows->pluck('name')->all(),
            'orders'   => $rows->pluck('orders')->map(fn ($v) => (int) $v)->all(),
            'shipping' => $rows->pluck('shipping')->map(fn ($v) => (int) $v)->all(),
        ];
    }

    private function topProductsChart(Carbon $from, Carbon $to, int $limit): array
    {
        $rows = $this->topProductsTable($from, $to, $limit);

        return [
            'labels'  => array_column($rows, 'title'),
            'qty'     => array_column($rows, 'qty'),
            'revenue' => array_column($rows, 'revenue'),
        ];
    }

    private function topProductsTable(Carbon $from, Carbon $to, int $limit): array
    {
        return OrderItem::query()
            ->selectRaw('product_id, product_title as title, SUM(qty) as qty, SUM(subtotal) as revenue')
            ->whereHas('order', function ($q) use ($from, $to) {
                $q->whereIn('status', self::PAID_STATUSES)
                  ->whereBetween('created_at', [$from, $to]);
            })
            ->groupBy('product_id', 'product_title')
            ->orderByDesc('qty')
            ->limit($limit)
            ->get()
            ->map(fn ($r) => [
                'product_id' => $r->product_id,
                'title'      => $r->title ?: ('محصول #' . $r->product_id),
                'qty'        => (int) $r->qty,
                'revenue'    => (int) $r->revenue,
            ])
            ->all();
    }

    private function topCustomersTable(Carbon $from, Carbon $to, int $limit): array
    {
        return Order::query()
            ->with('user:id,first_name,last_name,mobile')
            ->whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to])
            ->selectRaw('user_id, COUNT(*) as orders, SUM(total_amount) as revenue')
            ->groupBy('user_id')
            ->orderByDesc('revenue')
            ->limit($limit)
            ->get()
            ->map(function ($r) {
                $user = $r->user;
                $name = $user
                    ? trim(($user->first_name ?? '') . ' ' . ($user->last_name ?? ''))
                    : 'کاربر حذف‌شده';

                return [
                    'user_id'  => $r->user_id,
                    'name'     => $name !== '' ? $name : 'کاربر #' . $r->user_id,
                    'mobile'   => $user->mobile ?? '—',
                    'orders'   => (int) $r->orders,
                    'revenue'  => (int) $r->revenue,
                ];
            })
            ->all();
    }

    private function paymentMethodsTable(Carbon $from, Carbon $to): array
    {
        return Order::whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to])
            ->selectRaw("COALESCE(NULLIF(payment_method_name,''), payment_method, 'نامشخص') as name, COUNT(*) as orders, SUM(total_amount) as revenue")
            ->groupBy('name')
            ->orderByDesc('revenue')
            ->get()
            ->map(fn ($r) => [
                'name'    => $r->name,
                'orders'  => (int) $r->orders,
                'revenue' => (int) $r->revenue,
            ])
            ->all();
    }

    private function shippingMethodsTable(Carbon $from, Carbon $to): array
    {
        return Order::whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to])
            ->selectRaw("COALESCE(NULLIF(shipping_method_name,''), shipping_method_type, 'نامشخص') as name, COUNT(*) as orders, SUM(shipping_cost) as shipping, SUM(total_amount) as revenue")
            ->groupBy('name')
            ->orderByDesc('orders')
            ->get()
            ->map(fn ($r) => [
                'name'     => $r->name,
                'orders'   => (int) $r->orders,
                'shipping' => (int) $r->shipping,
                'revenue'  => (int) $r->revenue,
            ])
            ->all();
    }

    private function couponsTable(Carbon $from, Carbon $to, int $limit): array
    {
        return Order::whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to])
            ->whereNotNull('coupon_code')
            ->where('coupon_code', '!=', '')
            ->selectRaw('coupon_code, COUNT(*) as orders, SUM(coupon_discount) as discount')
            ->groupBy('coupon_code')
            ->orderByDesc('orders')
            ->limit($limit)
            ->get()
            ->map(fn ($r) => [
                'code'     => $r->coupon_code,
                'orders'   => (int) $r->orders,
                'discount' => (int) $r->discount,
            ])
            ->all();
    }

    /**
     * Net sales for orders paid via the given gateway IDs:
     * SUM(payable_amount) − SUM(shipping_cost), never below zero.
     *
     * @param  list<string>  $gatewayIds
     */
    private function netSalesForGateways(Carbon $from, Carbon $to, array $gatewayIds): int
    {
        if ($gatewayIds === []) {
            return 0;
        }

        $row = Order::query()
            ->whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to])
            ->whereIn('payment_method', $gatewayIds)
            ->selectRaw('COALESCE(SUM(payable_amount),0) as payable_sum, COALESCE(SUM(shipping_cost),0) as shipping_sum')
            ->first();

        $payable  = (int) ($row->payable_sum ?? 0);
        $shipping = (int) ($row->shipping_sum ?? 0);

        return max(0, $payable - $shipping);
    }

    /**
     * Aggregate paid orders by normalized referrer source.
     *
     * @return list<array{name:string,orders:int,revenue:int,share:float}>
     */
    private function referrersTable(Carbon $from, Carbon $to, int $limit = 20): array
    {
        $rows = Order::whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to])
            ->get(['referrer', 'total_amount']);

        $buckets = [];
        foreach ($rows as $row) {
            $name = Order::referrerSourceLabel($row->referrer);
            if (!isset($buckets[$name])) {
                $buckets[$name] = ['name' => $name, 'orders' => 0, 'revenue' => 0];
            }
            $buckets[$name]['orders']++;
            $buckets[$name]['revenue'] += (int) $row->total_amount;
        }

        usort($buckets, fn ($a, $b) => $b['orders'] <=> $a['orders'] ?: $b['revenue'] <=> $a['revenue']);

        $totalOrders = array_sum(array_column($buckets, 'orders'));
        $buckets = array_slice(array_values($buckets), 0, $limit);

        foreach ($buckets as &$bucket) {
            $bucket['share'] = $totalOrders > 0
                ? round(($bucket['orders'] / $totalOrders) * 100, 1)
                : 0.0;
        }
        unset($bucket);

        return $buckets;
    }

    private function referrersChart(Carbon $from, Carbon $to, int $limit = 10): array
    {
        $rows = $this->referrersTable($from, $to, $limit);

        return [
            'labels'  => array_column($rows, 'name'),
            'orders'  => array_column($rows, 'orders'),
            'revenue' => array_column($rows, 'revenue'),
            'shares'  => array_column($rows, 'share'),
        ];
    }

    /** @return array{name:string,orders:int,share:float}|null */
    private function topReferrerKpi(Carbon $from, Carbon $to): ?array
    {
        $rows = $this->referrersTable($from, $to, 1);
        if ($rows === []) {
            return null;
        }

        return [
            'name'   => $rows[0]['name'],
            'orders' => $rows[0]['orders'],
            'share'  => $rows[0]['share'],
        ];
    }

    /**
     * Most-used courier / shipping company among paid orders in range.
     *
     * @return array{name:string,orders:int,share:float}|null
     */
    private function topCourierKpi(Carbon $from, Carbon $to): ?array
    {
        $courierLabels = AmadastCouriers::labels();

        $rows = Order::query()
            ->whereIn('orders.status', self::PAID_STATUSES)
            ->whereBetween('orders.created_at', [$from, $to])
            ->leftJoin('shipping_methods as sm', 'orders.shipping_method_id', '=', 'sm.id')
            ->select([
                'orders.shipping_method_type',
                'orders.shipping_method_name',
                'sm.amadast_courier_id',
                DB::raw('COUNT(*) as orders_count'),
            ])
            ->groupBy('orders.shipping_method_type', 'orders.shipping_method_name', 'sm.amadast_courier_id')
            ->get();

        if ($rows->isEmpty()) {
            return null;
        }

        $buckets = [];
        $total = 0;
        foreach ($rows as $row) {
            $count = (int) $row->orders_count;
            if ($count <= 0) {
                continue;
            }

            $name = 'نامشخص';
            $courierId = (int) ($row->amadast_courier_id ?? 0);
            if (($row->shipping_method_type ?? '') === 'amadast' && $courierId > 0) {
                $name = $courierLabels[$courierId] ?? ('کوریر '.$courierId);
            } else {
                $fallback = trim((string) ($row->shipping_method_name ?? ''));
                if ($fallback !== '') {
                    $name = $fallback;
                }
            }

            $buckets[$name] = ($buckets[$name] ?? 0) + $count;
            $total += $count;
        }

        if ($buckets === [] || $total <= 0) {
            return null;
        }

        arsort($buckets);
        $name = (string) array_key_first($buckets);
        $orders = (int) $buckets[$name];

        return [
            'name'   => $name,
            'orders' => $orders,
            'share'  => round(($orders / $total) * 100, 1),
        ];
    }

    private function dailyTable(Carbon $from, Carbon $to): array
    {
        $days = max(1, (int) $from->copy()->startOfDay()->diffInDays($to->copy()->startOfDay()) + 1);
        if ($days > 62) {
            // For long ranges show monthly rows instead
            return Order::whereIn('status', self::PAID_STATUSES)
                ->whereBetween('created_at', [$from, $to])
                ->selectRaw("DATE_FORMAT(created_at, '%Y-%m-01') as day, COUNT(*) as orders, SUM(total_amount) as revenue, SUM(shipping_cost) as shipping")
                ->groupBy('day')
                ->orderByDesc('day')
                ->limit(24)
                ->get()
                ->map(fn ($r) => [
                    'date'     => Order::toJalaliString(Carbon::parse($r->day)),
                    'orders'   => (int) $r->orders,
                    'revenue'  => (int) $r->revenue,
                    'shipping' => (int) $r->shipping,
                ])
                ->all();
        }

        return Order::whereIn('status', self::PAID_STATUSES)
            ->whereBetween('created_at', [$from, $to])
            ->selectRaw('DATE(created_at) as day, COUNT(*) as orders, SUM(total_amount) as revenue, SUM(shipping_cost) as shipping')
            ->groupBy('day')
            ->orderByDesc('day')
            ->get()
            ->map(fn ($r) => [
                'date'     => Order::toJalaliString(Carbon::parse($r->day)),
                'orders'   => (int) $r->orders,
                'revenue'  => (int) $r->revenue,
                'shipping' => (int) $r->shipping,
            ])
            ->all();
    }
}
