<?php

namespace App\Services\Sales;

use Illuminate\Database\Query\Builder;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

final class ProductSalesQuantityService
{
    public const FINALIZED_STATUSES = ['delivered', 'completed'];
    public const EXCLUDED_STATUSES = ['draft', 'pending', 'cancelled', 'canceled', 'rejected', 'void', 'failed', 'returned', 'refunded'];

    /** @return array<int,int> */
    public function quantities(array $productIds, array $filters = []): array
    {
        $ids = collect($productIds)->map(fn ($id) => (int) $id)->filter()->unique()->values()->all();
        if ($ids === [] || ! Schema::hasTable('orders') || ! Schema::hasTable('order_details')) {
            return [];
        }

        $gross = $this->grossQuery($filters)
            ->whereIn(DB::raw($this->productIdExpression()), $ids)
            ->groupBy(DB::raw($this->productIdExpression()))
            ->pluck('quantity', 'product_id');
        $returns = $this->returnQuantities($ids, $filters);

        return collect($ids)->mapWithKeys(function (int $id) use ($gross, $returns) {
            return [$id => max(0, (int) $gross->get($id, 0) - (int) ($returns[$id] ?? 0))];
        })->all();
    }

    public function quantity(int $productId, array $filters = []): int
    {
        return $this->quantities([$productId], $filters)[$productId] ?? 0;
    }

    public function aggregateQuery(array $filters = []): Builder
    {
        $gross = $this->grossQuery($filters)->groupBy(DB::raw($this->productIdExpression()));
        $returns = $this->returnAggregateQuery($filters);

        return DB::query()->fromSub($gross, 'sold_gross')
            ->leftJoinSub($returns, 'sold_returns', 'sold_returns.product_id', '=', 'sold_gross.product_id')
            ->select('sold_gross.product_id')
            ->selectRaw('GREATEST(0, sold_gross.quantity - COALESCE(sold_returns.quantity, 0)) AS sold_quantity');
    }

    public function audit(int $productId, array $filters = []): array
    {
        $rows = DB::table('order_details as od')
            ->join('orders as o', 'o.id', '=', 'od.order_id')
            ->whereRaw($this->productIdExpression('od').' = ?', [$productId])
            ->when($this->hasColumn('order_details', 'deleted_at'), fn ($q) => $q->whereNull('od.deleted_at'))
            ->when($this->hasColumn('orders', 'deleted_at'), fn ($q) => $q->whereNull('o.deleted_at'))
            ->selectRaw('od.id AS sale_item_id, o.id AS invoice_number, o.created_at AS sale_date, o.order_status AS sale_status')
            ->selectRaw($this->quantityExpression('od').' AS quantity')
            ->orderBy('o.created_at')->orderBy('od.id')->get();

        $returned = $this->returnQuantities([$productId], $filters)[$productId] ?? 0;
        $valid = $rows->whereIn('sale_status', self::FINALIZED_STATUSES)->sum('quantity');
        $cancelled = $rows->whereIn('sale_status', ['cancelled', 'canceled', 'void'])->sum('quantity');

        return [
            'product_id' => $productId,
            'gross_sold_quantity' => (int) $valid,
            'cancelled_void_quantity' => (int) $cancelled,
            'returned_quantity' => (int) $returned,
            'net_sold_quantity' => max(0, (int) $valid - (int) $returned),
            'duplicate_sale_items' => $rows->groupBy('sale_item_id')->filter(fn (Collection $group) => $group->count() > 1)->keys()->values()->all(),
            'invoices' => $rows->values()->all(),
        ];
    }

    private function grossQuery(array $filters): Builder
    {
        $query = DB::table('order_details as od')->join('orders as o', 'o.id', '=', 'od.order_id')
            ->whereIn('o.order_status', self::FINALIZED_STATUSES)
            ->when($this->hasColumn('order_details', 'deleted_at'), fn ($q) => $q->whereNull('od.deleted_at'))
            ->when($this->hasColumn('orders', 'deleted_at'), fn ($q) => $q->whereNull('o.deleted_at'))
            ->selectRaw($this->productIdExpression('od').' AS product_id')
            ->selectRaw('SUM('.$this->quantityExpression('od').') AS quantity');

        return $this->applyFilters($query, $filters, 'o');
    }

    private function returnAggregateQuery(array $filters): Builder
    {
        if (! Schema::hasTable('showroom_return_items')) {
            return DB::query()->fromSub(DB::query()->selectRaw('NULL AS product_id, 0 AS quantity')->whereRaw('1=0'), 'no_returns');
        }

        $query = DB::table('showroom_return_items as ri')->join('orders as ro', 'ro.id', '=', 'ri.order_id');
        $product = $this->hasColumn('showroom_return_items', 'item_id') ? 'ri.item_id' : null;
        if (! $product && $this->hasColumn('showroom_return_items', 'order_detail_id')) {
            $query->join('order_details as rod', 'rod.id', '=', 'ri.order_detail_id');
            $product = $this->productIdExpression('rod');
        }
        if (! $product || ! $this->hasColumn('showroom_return_items', 'quantity')) {
            return DB::query()->fromSub(DB::query()->selectRaw('NULL AS product_id, 0 AS quantity')->whereRaw('1=0'), 'no_returns');
        }

        $query->when($this->hasColumn('showroom_return_items', 'deleted_at'), fn ($q) => $q->whereNull('ri.deleted_at'))
            ->selectRaw($product.' AS product_id, SUM(ri.quantity) AS quantity')->groupBy(DB::raw($product));
        $movements = $this->applyFilters($query, $filters, 'ro');

        if (Schema::hasTable('order_exchanges') && Schema::hasTable('order_exchange_items')
            && $this->hasColumn('order_exchange_items', 'item_id')
            && $this->hasColumn('order_exchange_items', 'quantity')) {
            $exchange = DB::table('order_exchange_items as ei')
                ->join('order_exchanges as ex', 'ex.id', '=', 'ei.order_exchange_id')
                ->join('orders as eo', 'eo.id', '=', 'ex.order_id')
                ->where('ei.direction', 'return')
                ->when($this->hasColumn('order_exchanges', 'exchange_status'), fn ($q) => $q->where('ex.exchange_status', 'posted'))
                ->when($this->hasColumn('order_exchanges', 'voided_at'), fn ($q) => $q->whereNull('ex.voided_at'))
                ->selectRaw('ei.item_id AS product_id, SUM(ei.quantity) AS quantity')
                ->groupBy('ei.item_id');
            $movements->unionAll($this->applyFilters($exchange, $filters, 'eo'));
        }

        return DB::query()->fromSub($movements, 'return_movements')
            ->select('product_id')->selectRaw('SUM(quantity) AS quantity')->groupBy('product_id');
    }

    /** @return array<int,int> */
    private function returnQuantities(array $ids, array $filters): array
    {
        return DB::query()->fromSub($this->returnAggregateQuery($filters), 'return_totals')
            ->whereIn('product_id', $ids)->pluck('quantity', 'product_id')
            ->mapWithKeys(fn ($quantity, $id) => [(int) $id => (int) $quantity])->all();
    }

    private function applyFilters(Builder $query, array $filters, string $ordersAlias): Builder
    {
        return $query
            ->when($filters['from'] ?? null, fn ($q, $v) => $q->where($ordersAlias.'.created_at', '>=', $v))
            ->when($filters['to'] ?? null, fn ($q, $v) => $q->where($ordersAlias.'.created_at', '<=', $v))
            ->when(($filters['store_id'] ?? null) && $this->hasColumn('orders', 'store_id'), fn ($q) => $q->where($ordersAlias.'.store_id', $filters['store_id']))
            ->when(($filters['warehouse_id'] ?? null) && $this->hasColumn('orders', 'warehouse_id'), fn ($q) => $q->where($ordersAlias.'.warehouse_id', $filters['warehouse_id']))
            ->when(($filters['channel'] ?? null) && $this->hasColumn('orders', 'sale_channel'), fn ($q) => $q->where($ordersAlias.'.sale_channel', $filters['channel']));
    }

    private function productIdExpression(string $alias = 'od'): string
    {
        foreach (['item_id', 'product_id'] as $column) {
            if ($this->hasColumn('order_details', $column)) {
                return "NULLIF({$alias}.{$column}, 0)";
            }
        }
        throw new \RuntimeException('Sales quantity requires a stable product_id/item_id column.');
    }

    private function quantityExpression(string $alias = 'od'): string
    {
        foreach (['quantity', 'qty'] as $column) {
            if ($this->hasColumn('order_details', $column)) {
                return "COALESCE({$alias}.{$column}, 0)";
            }
        }
        throw new \RuntimeException('Sales quantity column is missing.');
    }

    private function hasColumn(string $table, string $column): bool
    {
        static $cache = [];
        return $cache[$table][$column] ??= Schema::hasColumn($table, $column);
    }
}
