<?php

declare(strict_types=1);

namespace App\Business\Inventory;

use App\Business\Inventory\Enums\InventoryBatchStatus;
use App\Business\Inventory\Enums\InventoryExpiryStatus;
use App\Models\DberpInventoryBatch;
use Carbon\CarbonInterface;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

/**
 * 批次效期判定与列表筛选口径一致：数量以 DberpInventoryBatch 的
 * realtime_quantity / available_quantity / locked_quantity 为准，无独立「累计收货」列。
 */
final class InventoryExpiryService
{
    public function resolveForBatch(DberpInventoryBatch $batch, ?CarbonInterface $today = null): InventoryExpiryStatus
    {
        $today ??= now()->startOfDay();

        if ($batch->status === InventoryBatchStatus::Disabled->value || ! $batch->is_enabled) {
            return InventoryExpiryStatus::Blocked;
        }

        if ($batch->expiry_date === null) {
            return InventoryExpiryStatus::Normal;
        }

        $expiryDate = Carbon::parse((string) $batch->getAttribute('expiry_date'))->startOfDay();

        if ($today->greaterThan($expiryDate)) {
            return InventoryExpiryStatus::Expired;
        }

        $warningDays = max(0, (int) ($batch->product?->expiry_warning_days ?? 0));

        if ($warningDays > 0 && $today->diffInDays($expiryDate, false) <= $warningDays) {
            return InventoryExpiryStatus::NearExpiry;
        }

        return InventoryExpiryStatus::Normal;
    }

    public function isBlockedForSale(DberpInventoryBatch $batch): bool
    {
        return in_array($this->resolveForBatch($batch), [InventoryExpiryStatus::Expired, InventoryExpiryStatus::Blocked], true);
    }

    /**
     * 将批次查询限制为与 resolveForBatch() 同一口径的效期状态（用于列表筛选，避免全表加载）。
     */
    public function constrainBatchQueryToExpiryStatus(Builder $query, string $status): Builder
    {
        $table = (new DberpInventoryBatch)->getTable();
        $today = now()->startOfDay()->toDateString();

        return match ($status) {
            InventoryExpiryStatus::Blocked->value => $query->where(function (Builder $q) use ($table): void {
                $q->where($table.'.status', InventoryBatchStatus::Disabled->value)
                    ->orWhere($table.'.is_enabled', false);
            }),
            InventoryExpiryStatus::Expired->value => $query
                ->where($table.'.is_enabled', true)
                ->where($table.'.status', '!=', InventoryBatchStatus::Disabled->value)
                ->whereNotNull($table.'.expiry_date')
                ->whereDate($table.'.expiry_date', '<', $today),
            InventoryExpiryStatus::NearExpiry->value => $query
                ->where($table.'.is_enabled', true)
                ->where($table.'.status', '!=', InventoryBatchStatus::Disabled->value)
                ->whereNotNull($table.'.expiry_date')
                ->whereDate($table.'.expiry_date', '>=', $today)
                ->whereExists(function (QueryBuilder $q) use ($table, $today): void {
                    $q->selectRaw('1')
                        ->from('dberp_products')
                        ->whereColumn('dberp_products.id', $table.'.product_id')
                        ->where('dberp_products.expiry_warning_days', '>', 0)
                        ->whereRaw(
                            $this->batchExpiryOnOrBeforeWarningThresholdSql($table),
                            [$today],
                        );
                }),
            InventoryExpiryStatus::Normal->value => $query
                ->where($table.'.is_enabled', true)
                ->where($table.'.status', '!=', InventoryBatchStatus::Disabled->value)
                ->where(function (Builder $q) use ($table, $today): void {
                    $q->whereNull($table.'.expiry_date')
                        ->orWhere(function (Builder $q2) use ($table, $today): void {
                            $q2->whereDate($table.'.expiry_date', '>=', $today)
                                ->where(function (Builder $q3) use ($table, $today): void {
                                    $q3->whereExists(function (QueryBuilder $sq) use ($table): void {
                                        $sq->selectRaw('1')
                                            ->from('dberp_products')
                                            ->whereColumn('dberp_products.id', $table.'.product_id')
                                            ->where('dberp_products.expiry_warning_days', '<=', 0);
                                    })
                                        ->orWhereExists(function (QueryBuilder $sq) use ($table, $today): void {
                                            $sq->selectRaw('1')
                                                ->from('dberp_products')
                                                ->whereColumn('dberp_products.id', $table.'.product_id')
                                                ->where('dberp_products.expiry_warning_days', '>', 0)
                                                ->whereRaw(
                                                    $this->batchExpiryAfterWarningThresholdSql($table),
                                                    [$today],
                                                );
                                        });
                                });
                        });
                }),
            default => $query,
        };
    }

    private function batchExpiryOnOrBeforeWarningThresholdSql(string $batchTable): string
    {
        return match (DB::connection()->getDriverName()) {
            'sqlite' => "{$batchTable}.expiry_date <= date(?, '+' || CAST(dberp_products.expiry_warning_days AS TEXT) || ' days')",
            default => "{$batchTable}.expiry_date <= DATE_ADD(?, INTERVAL dberp_products.expiry_warning_days DAY)",
        };
    }

    private function batchExpiryAfterWarningThresholdSql(string $batchTable): string
    {
        return match (DB::connection()->getDriverName()) {
            'sqlite' => "{$batchTable}.expiry_date > date(?, '+' || CAST(dberp_products.expiry_warning_days AS TEXT) || ' days')",
            default => "{$batchTable}.expiry_date > DATE_ADD(?, INTERVAL dberp_products.expiry_warning_days DAY)",
        };
    }
}
