<?php

declare(strict_types=1);

namespace App\Business\Product;

use App\Models\DberpProductSku;
use App\Support\DberpLineProductSkuFormFields;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Schema;

/**
 * 第 6 步：在「物料下仅有一条启用的规格组合」时，安全回填业务行上的 product_sku_id 与规格快照；多启用规格或未启用规格组合的行仅登记治理清单，不猜测。
 */
final class ProductSkuLineBackfillService
{
    /**
     * 第 2 步起带 product_sku_code / product_sku_name / product_sku_spec 快照列的明细表。
     *
     * @var list<string>
     */
    private const NEW_SNAPSHOT_TABLES = [
        'dberp_purchase_request_items',
        'dberp_purchase_inquiry_items',
        'dberp_sales_quote_items',
        'dberp_purchase_inquiry_quotes',
        'dberp_other_stock_in_order_items',
        'dberp_other_stock_out_order_items',
        'dberp_transfer_order_items',
        'dberp_stocktake_order_items',
        'dberp_stocktake_differences',
        'dberp_purchase_return_order_items',
        'dberp_sales_return_order_items',
    ];

    /**
     * 第 3 步起使用 ProductBusinessSnapshot 列名的单据/流水表。
     *
     * @var list<string>
     */
    private const LEGACY_DOCUMENT_SNAPSHOT_TABLES = [
        'dberp_purchase_order_items',
        'dberp_purchase_receipt_order_items',
        'dberp_sales_order_items',
        'dberp_sales_outbound_order_items',
        'dberp_inventory_transactions',
    ];

    /**
     * @var list<string>
     */
    private const BALANCE_TABLES = [
        'dberp_inventory_balances',
    ];

    /**
     * @return list<string>
     */
    public static function scannedTables(): array
    {
        return array_values(array_unique(array_merge(
            self::NEW_SNAPSHOT_TABLES,
            self::LEGACY_DOCUMENT_SNAPSHOT_TABLES,
            self::BALANCE_TABLES,
        )));
    }

    /**
     * @return array{
     *     dry_run: bool,
     *     backfilled_by_table: array<string, int>,
     *     ambiguous_rows: list<array{table: string, row_id: int|string, product_id: int, reason: string}>,
     *     report_path: string|null
     * }
     */
    public function run(bool $dryRun = false, bool $writeReport = false): array
    {
        $backfilledByTable = [];
        $ambiguousRows = [];

        foreach (self::scannedTables() as $table) {
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, 'product_sku_id') || ! Schema::hasColumn($table, 'product_id')) {
                continue;
            }

            $backfilledByTable[$table] = 0;
            DB::table($table)
                ->whereNull('product_sku_id')
                ->whereNotNull('product_id')
                ->orderBy('id')
                ->chunkById(500, function ($rows) use ($table, $dryRun, &$backfilledByTable, &$ambiguousRows): void {
                    foreach ($rows as $row) {
                        $productId = (int) $row->product_id;
                        $resolution = $this->classifyProductForBackfill($productId);
                        if ($resolution === 'ambiguous_multi_enabled') {
                            $ambiguousRows[] = [
                                'table' => $table,
                                'row_id' => $row->id,
                                'product_id' => $productId,
                                'reason' => (string) __('dberp.messages.product_sku_governance_multi_enabled'),
                            ];

                            continue;
                        }

                        if ($resolution === 'no_enabled_sku') {
                            $ambiguousRows[] = [
                                'table' => $table,
                                'row_id' => $row->id,
                                'product_id' => $productId,
                                'reason' => (string) __('dberp.messages.product_sku_governance_no_enabled_sku'),
                            ];

                            continue;
                        }

                        if ($resolution === 'no_sku_row') {
                            $ambiguousRows[] = [
                                'table' => $table,
                                'row_id' => $row->id,
                                'product_id' => $productId,
                                'reason' => (string) __('dberp.messages.product_sku_governance_no_sku'),
                            ];

                            continue;
                        }

                        if (! is_array($resolution) || ! isset($resolution['sku'])) {
                            continue;
                        }

                        /** @var DberpProductSku $sku */
                        $sku = $resolution['sku'];
                        $payload = $this->buildUpdatePayloadForTable($table, $sku);
                        if ($payload === []) {
                            continue;
                        }

                        if (! $dryRun) {
                            DB::table($table)->where('id', $row->id)->update($payload);
                        }

                        $backfilledByTable[$table]++;
                    }
                });
        }

        $reportPath = null;
        if ($writeReport) {
            $reportPath = $this->writeReportFile($dryRun, $backfilledByTable, $ambiguousRows);
        }

        return [
            'dry_run' => $dryRun,
            'backfilled_by_table' => $backfilledByTable,
            'ambiguous_rows' => $ambiguousRows,
            'report_path' => $reportPath,
        ];
    }

    /**
     * @return 'ambiguous_multi_enabled'|'no_enabled_sku'|'no_sku_row'|array{sku: DberpProductSku}
     */
    private function classifyProductForBackfill(int $productId): string|array
    {
        if ($productId <= 0) {
            return 'no_sku_row';
        }

        $enabledCount = DberpProductSku::query()
            ->where('product_id', $productId)
            ->where('is_enabled', true)
            ->count();

        if ($enabledCount > 1) {
            return 'ambiguous_multi_enabled';
        }

        if ($enabledCount === 1) {
            $sku = DberpProductSku::query()
                ->with('product')
                ->where('product_id', $productId)
                ->where('is_enabled', true)
                ->orderByDesc('is_default')
                ->orderBy('sort_order')
                ->orderBy('id')
                ->first();

            if ($sku instanceof DberpProductSku) {
                return ['sku' => $sku];
            }
        }

        $anySku = DberpProductSku::query()->where('product_id', $productId)->exists();

        return $anySku ? 'no_enabled_sku' : 'no_sku_row';
    }

    /**
     * @return array<string, mixed>
     */
    private function buildUpdatePayloadForTable(string $table, DberpProductSku $sku): array
    {
        $skuId = (int) $sku->getKey();
        if (in_array($table, self::BALANCE_TABLES, true)) {
            return ['product_sku_id' => $skuId];
        }

        if (in_array($table, self::NEW_SNAPSHOT_TABLES, true)) {
            return array_merge(
                ['product_sku_id' => $skuId],
                DberpLineProductSkuFormFields::snapshotTripleForSku($sku),
            );
        }

        if (in_array($table, self::LEGACY_DOCUMENT_SNAPSHOT_TABLES, true)) {
            return array_merge(
                ['product_sku_id' => $skuId],
                ProductBusinessSnapshot::fromSku($sku),
            );
        }

        return [];
    }

    /**
     * @param  array<string, int>  $backfilledByTable
     * @param  list<array{table: string, row_id: int|string, product_id: int, reason: string}>  $ambiguousRows
     */
    private function writeReportFile(bool $dryRun, array $backfilledByTable, array $ambiguousRows): ?string
    {
        $dir = storage_path('app/governance');
        File::ensureDirectoryExists($dir);

        $path = $dir.DIRECTORY_SEPARATOR.'product_sku_step6_'.date('Y-m-d_His').'.json';
        $payload = [
            'generated_at' => now()->toIso8601String(),
            'dry_run' => $dryRun,
            'backfilled_by_table' => $backfilledByTable,
            'ambiguous_rows' => $ambiguousRows,
            'nullable_strategy' => (string) __('dberp.messages.product_sku_governance_nullable_strategy'),
        ];

        File::put($path, json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));

        return $path;
    }
}
