<?php

declare(strict_types=1);

namespace App\Business\Product;

use App\Models\DberpInventoryBalance;
use App\Models\DberpInventoryTransaction;
use App\Models\DberpProductSku;
use App\Models\DberpPurchaseOrderItem;
use App\Models\DberpPurchaseReceiptOrderItem;
use App\Models\DberpSalesOrderItem;
use App\Models\DberpSalesOutboundOrderItem;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Validation\ValidationException;

/**
 * 规格组合引用与结余约束：删除、停用时的业务校验。
 */
final class ProductSkuReferenceGuard
{
    public static function aggregateRealtimeQuantity(int $productSkuId): string
    {
        $sum = DberpInventoryBalance::query()
            ->where('product_sku_id', $productSkuId)
            ->sum('realtime_quantity');

        return number_format((float) $sum, 4, '.', '');
    }

    public static function hasPositiveRealtimeStock(int $productSkuId): bool
    {
        return (float) self::aggregateRealtimeQuantity($productSkuId) > 0.00005;
    }

    public static function isReferencedByOperationalDocuments(int $productSkuId): bool
    {
        if (DberpInventoryBalance::query()->where('product_sku_id', $productSkuId)->exists()) {
            return true;
        }

        if (DberpInventoryTransaction::query()->where('product_sku_id', $productSkuId)->exists()) {
            return true;
        }

        if (DberpPurchaseOrderItem::query()->where('product_sku_id', $productSkuId)->exists()) {
            return true;
        }

        if (DberpPurchaseReceiptOrderItem::query()->where('product_sku_id', $productSkuId)->exists()) {
            return true;
        }

        if (DberpSalesOrderItem::query()->where('product_sku_id', $productSkuId)->exists()) {
            return true;
        }

        if (DberpSalesOutboundOrderItem::query()->where('product_sku_id', $productSkuId)->exists()) {
            return true;
        }

        return false;
    }

    public static function assertCanPhysicallyDelete(DberpProductSku $sku): void
    {
        if (self::isReferencedByOperationalDocuments((int) $sku->getKey())) {
            throw ValidationException::withMessages([
                'product_sku_id' => (string) __('dberp.validation.product_spec_combo_delete_blocked_referenced'),
            ]);
        }
    }

    /**
     * @throws ValidationException
     */
    public static function assertCanDisable(DberpProductSku $sku): void
    {
        if (! $sku->exists) {
            return;
        }

        if (self::hasPositiveRealtimeStock((int) $sku->getKey())) {
            throw ValidationException::withMessages([
                'is_enabled' => (string) __('dberp.validation.product_spec_combo_disable_blocked_has_stock'),
            ]);
        }
    }

    /**
     * 解析物料下用于回填空关联的安全默认规格组合 ID；无法唯一确定时返回 null。
     */
    public static function resolveSafeDefaultSkuIdForProduct(int $productId): ?int
    {
        $skus = DberpProductSku::query()
            ->where('product_id', $productId)
            ->orderByDesc('is_default')
            ->orderBy('sort_order')
            ->orderBy('id')
            ->get(['id', 'is_default']);

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

        $defaults = $skus->where('is_default', true);
        if ($defaults->count() === 1) {
            return (int) $defaults->first()->getKey();
        }

        if ($skus->count() === 1) {
            return (int) $skus->first()->getKey();
        }

        if ($defaults->count() > 1) {
            return (int) $defaults->sortBy('id')->first()->getKey();
        }

        return null;
    }

    /**
     * 仅当物料下存在**恰好一条启用的**规格组合时返回该组合；否则返回 null（不根据 is_default 等在多 SKU 场景猜测）。
     */
    public static function soleEnabledSkuIdForProductOrNull(int $productId): ?int
    {
        if ($productId <= 0) {
            return null;
        }

        $ids = DberpProductSku::query()
            ->where('product_id', $productId)
            ->where('is_enabled', true)
            ->orderByDesc('is_default')
            ->orderBy('sort_order')
            ->orderBy('id')
            ->pluck('id');

        return $ids->count() === 1 ? (int) $ids->first() : null;
    }

    /**
     * @param  array<string, string>  $tablesToColumn  table => column holding product_sku_id
     * @return array{updated_rows: int, risks: list<array{table: string, row_id: int|string, product_id: int, reason: string}>}
     */
    public static function mapNullProductSkuIds(array $tablesToColumn, bool $dryRun = false): array
    {
        $updatedRows = 0;
        $risks = [];

        foreach ($tablesToColumn as $table => $column) {
            if (! self::tableHasColumn($table, $column) || ! self::tableHasColumn($table, 'product_id')) {
                continue;
            }

            DB::table($table)
                ->whereNull($column)
                ->whereNotNull('product_id')
                ->orderBy('id')
                ->chunkById(500, function ($rows) use ($table, $column, $dryRun, &$updatedRows, &$risks): void {
                    foreach ($rows as $row) {
                        $productId = (int) $row->product_id;
                        $skuId = self::soleEnabledSkuIdForProductOrNull($productId);
                        if ($skuId === null) {
                            $risks[] = [
                                'table' => $table,
                                'row_id' => $row->id,
                                'product_id' => $productId,
                                'reason' => (string) __('dberp.messages.product_sku_compat_ambiguous_default', ['table' => $table]),
                            ];

                            continue;
                        }

                        if (! $dryRun) {
                            DB::table($table)->where('id', $row->id)->update([$column => $skuId]);
                        }
                        $updatedRows++;
                    }
                });
        }

        return ['updated_rows' => $updatedRows, 'risks' => $risks];
    }

    private static function tableHasColumn(string $table, string $column): bool
    {
        if (! Schema::hasTable($table)) {
            return false;
        }

        return Schema::hasColumn($table, $column);
    }
}
