<?php

declare(strict_types=1);

namespace App\Business\Report;

use App\Models\DberpCustomer;
use App\Models\DberpProduct;
use App\Models\DberpProductSku;
use App\Models\DberpSalesOrder;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

final class SalesReportService
{
    /**
     * 将 Filament 表格筛选状态转为销售报表服务入参（与导出 normalize 口径对齐）。
     *
     * @param  array<string, mixed>|null  $tableFilters
     * @return array<string, mixed>
     */
    public static function filtersFromTableState(?array $tableFilters): array
    {
        if ($tableFilters === null || $tableFilters === []) {
            return [];
        }

        $pick = static function (string $key) use ($tableFilters): mixed {
            $nested = $tableFilters[$key] ?? null;

            return is_array($nested) ? ($nested['value'] ?? null) : ($tableFilters[$key] ?? null);
        };

        $customerId = (int) ($pick('customer_id') ?? 0);
        $handlerEmployeeId = (int) ($pick('handler_employee_id') ?? 0);
        $categoryId = (int) ($pick('category_id') ?? 0);
        $productId = (int) ($pick('product_id') ?? 0);
        $productSkuId = (int) ($pick('product_sku_id') ?? 0);
        $status = $pick('status');

        $businessTime = $tableFilters['business_time'] ?? [];
        $businessTimeFrom = is_array($businessTime) ? ($businessTime['from'] ?? null) : null;
        $businessTimeUntil = is_array($businessTime) ? ($businessTime['until'] ?? null) : null;

        return array_filter([
            'customer_id' => $customerId > 0 ? $customerId : null,
            'handler_employee_id' => $handlerEmployeeId > 0 ? $handlerEmployeeId : null,
            'category_id' => $categoryId > 0 ? $categoryId : null,
            'product_id' => $productId > 0 ? $productId : null,
            'product_sku_id' => $productSkuId > 0 ? $productSkuId : null,
            'status' => is_string($status) && $status !== '' ? $status : null,
            'business_time_from' => $businessTimeFrom,
            'business_time_until' => $businessTimeUntil,
        ], static fn ($v): bool => $v !== null && $v !== '' && $v !== 0);
    }

    /**
     * 销售分析 / 聚合用：不含 with。
     */
    public function salesOrdersFilteredQuery(array $filters = []): Builder
    {
        $query = DberpSalesOrder::query()->whereNot('status', 'draft');

        return $this->applySalesOrderFilters($query, $filters);
    }

    public function statisticsQuery(array $filters = []): Builder
    {
        $query = DberpSalesOrder::query()
            ->with(['customer', 'handlerEmployee'])
            ->whereNot('status', 'draft');

        return $this->applySalesOrderFilters($query, $filters);
    }

    public function customerRankingQuery(array $filters = [])
    {
        $salesOrders = DberpSalesOrder::query()
            ->selectRaw('COALESCE(SUM(final_amount), 0)')
            ->whereColumn('customer_id', 'dberp_customers.id')
            ->whereNot('status', 'draft');

        $receivedOrders = DberpSalesOrder::query()
            ->selectRaw('COALESCE(SUM(received_amount), 0)')
            ->whereColumn('customer_id', 'dberp_customers.id')
            ->whereNot('status', 'draft');

        $orderCount = DberpSalesOrder::query()
            ->selectRaw('COUNT(*)')
            ->whereColumn('customer_id', 'dberp_customers.id')
            ->whereNot('status', 'draft');

        $this->applySalesOrderFilters($salesOrders, $filters);
        $this->applySalesOrderFilters($receivedOrders, $filters);
        $this->applySalesOrderFilters($orderCount, $filters);

        $existsQuery = DberpSalesOrder::query()
            ->selectRaw('1')
            ->whereColumn('customer_id', 'dberp_customers.id')
            ->whereNot('status', 'draft');

        $this->applySalesOrderFilters($existsQuery, $filters);

        return DberpCustomer::query()
            ->with('salesperson')
            ->addSelect([
                'sales_amount' => $salesOrders,
                'received_amount_total' => $receivedOrders,
                'order_count' => $orderCount,
            ])
            ->whereExists($existsQuery)
            ->orderByDesc('sales_amount')
            ->orderBy('id');
    }

    public function productRankingQuery(array $filters = [])
    {
        $salesItemQuery = DB::table('dberp_sales_order_items')
            ->join('dberp_sales_orders', 'dberp_sales_orders.id', '=', 'dberp_sales_order_items.order_id')
            ->whereColumn('dberp_sales_order_items.product_id', 'dberp_products.id')
            ->where('dberp_sales_orders.status', '!=', 'draft');

        $salesAmount = (clone $salesItemQuery)->selectRaw('COALESCE(SUM(dberp_sales_order_items.final_amount), 0)');
        $salesQuantity = (clone $salesItemQuery)->selectRaw('COALESCE(SUM(dberp_sales_order_items.base_quantity), 0)');
        $orderCount = (clone $salesItemQuery)->selectRaw('COUNT(DISTINCT dberp_sales_order_items.order_id)');

        $this->applySalesItemFilters($salesAmount, $filters);
        $this->applySalesItemFilters($salesQuantity, $filters);
        $this->applySalesItemFilters($orderCount, $filters);

        $existsQuery = DB::table('dberp_sales_order_items')
            ->selectRaw('1')
            ->join('dberp_sales_orders', 'dberp_sales_orders.id', '=', 'dberp_sales_order_items.order_id')
            ->whereColumn('dberp_sales_order_items.product_id', 'dberp_products.id')
            ->where('dberp_sales_orders.status', '!=', 'draft');

        $this->applySalesItemFilters($existsQuery, $filters);

        return DberpProduct::query()
            ->with(['category', 'unit'])
            ->addSelect([
                'sales_amount' => $salesAmount,
                'sales_quantity' => $salesQuantity,
                'sales_order_count' => $orderCount,
            ])
            ->whereExists($existsQuery)
            ->orderByDesc('sales_amount')
            ->orderBy('id');
    }

    /**
     * 按「商品规格」维度的销售排行（仅统计行上已绑定 product_sku_id 的明细）。
     */
    public function productSkuRankingQuery(array $filters = [])
    {
        $salesItemQuery = DB::table('dberp_sales_order_items')
            ->join('dberp_sales_orders', 'dberp_sales_orders.id', '=', 'dberp_sales_order_items.order_id')
            ->whereColumn('dberp_sales_order_items.product_sku_id', 'dberp_product_skus.id')
            ->where('dberp_sales_orders.status', '!=', 'draft');

        $salesAmount = (clone $salesItemQuery)->selectRaw('COALESCE(SUM(dberp_sales_order_items.final_amount), 0)');
        $salesQuantity = (clone $salesItemQuery)->selectRaw('COALESCE(SUM(dberp_sales_order_items.base_quantity), 0)');
        $orderCount = (clone $salesItemQuery)->selectRaw('COUNT(DISTINCT dberp_sales_order_items.order_id)');

        $this->applySalesItemFilters($salesAmount, $filters);
        $this->applySalesItemFilters($salesQuantity, $filters);
        $this->applySalesItemFilters($orderCount, $filters);

        $existsQuery = DB::table('dberp_sales_order_items')
            ->selectRaw('1')
            ->join('dberp_sales_orders', 'dberp_sales_orders.id', '=', 'dberp_sales_order_items.order_id')
            ->whereColumn('dberp_sales_order_items.product_sku_id', 'dberp_product_skus.id')
            ->where('dberp_sales_orders.status', '!=', 'draft');

        $this->applySalesItemFilters($existsQuery, $filters);

        return DberpProductSku::query()
            ->with(['product.category', 'product.unit'])
            ->addSelect([
                'sales_amount' => $salesAmount,
                'sales_quantity' => $salesQuantity,
                'sales_order_count' => $orderCount,
            ])
            ->whereExists($existsQuery)
            ->orderByDesc('sales_amount')
            ->orderBy('id');
    }

    private function applySalesOrderFilters(Builder $query, array $filters): Builder
    {
        return $query
            ->when($filters['status'] ?? null, fn (Builder $builder, $status): Builder => $builder->where('status', $status))
            ->when(($filters['customer_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('customer_id', (int) $filters['customer_id']))
            ->when(($filters['handler_employee_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('handler_employee_id', (int) $filters['handler_employee_id']))
            ->when($filters['business_time_from'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '<=', $date))
            ->when(($filters['product_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereHas('items', fn (Builder $itemQuery) => $itemQuery->where('product_id', (int) $filters['product_id'])))
            ->when(($filters['product_sku_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereHas('items', fn (Builder $itemQuery) => $itemQuery->where('product_sku_id', (int) $filters['product_sku_id'])))
            ->when(($filters['category_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereHas('items.product', fn (Builder $productQuery) => $productQuery->where('category_id', (int) $filters['category_id'])));
    }

    private function applySalesItemFilters(\Illuminate\Database\Query\Builder $query, array $filters): \Illuminate\Database\Query\Builder
    {
        return $query
            ->when($filters['status'] ?? null, fn ($builder, $status) => $builder->where('dberp_sales_orders.status', $status))
            ->when(($filters['customer_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_sales_orders.customer_id', (int) $filters['customer_id']))
            ->when(($filters['handler_employee_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_sales_orders.handler_employee_id', (int) $filters['handler_employee_id']))
            ->when($filters['business_time_from'] ?? null, fn ($builder, $date) => $builder->whereDate('dberp_sales_orders.business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn ($builder, $date) => $builder->whereDate('dberp_sales_orders.business_time', '<=', $date))
            ->when(($filters['category_id'] ?? 0) > 0, fn ($builder) => $builder->join('dberp_products', 'dberp_products.id', '=', 'dberp_sales_order_items.product_id')->where('dberp_products.category_id', (int) $filters['category_id']))
            ->when(($filters['product_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_sales_order_items.product_id', (int) $filters['product_id']))
            ->when(($filters['product_sku_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_sales_order_items.product_sku_id', (int) $filters['product_sku_id']));
    }
}
