<?php

declare(strict_types=1);

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * 物料 SKU / 规格（自 2026_05_17_100000 前移，供后续库存与单据明细 FK 引用）。
 */
return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('dberp_product_spec_groups')) {
            Schema::create('dberp_product_spec_groups', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('product_id')->constrained('dberp_products')->cascadeOnDelete()->comment('所属物料');
                $table->string('name', 120)->comment('规格组名称');
                $table->unsignedInteger('sort_order')->default(0)->comment('排序');
                $table->timestamps();

                $table->index(['product_id', 'sort_order', 'id'], 'idx_dberp_pspec_grp_prod_sort');
            });
        }

        if (! Schema::hasTable('dberp_product_spec_values')) {
            Schema::create('dberp_product_spec_values', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('product_spec_group_id')->constrained('dberp_product_spec_groups')->cascadeOnDelete()->comment('规格组');
                $table->string('value_label', 160)->comment('规格值');
                $table->unsignedInteger('sort_order')->default(0)->comment('排序');
                $table->timestamps();

                $table->index(['product_spec_group_id', 'sort_order', 'id'], 'idx_dberp_pspec_val_grp_sort');
            });
        }

        if (! Schema::hasTable('dberp_product_skus')) {
            Schema::create('dberp_product_skus', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('product_id')->constrained('dberp_products')->cascadeOnDelete()->comment('所属物料');
                $table->string('sku_code', 64)->comment('SKU 编码');
                $table->string('barcode', 64)->nullable()->comment('条码');
                $table->string('spec_text', 512)->nullable()->comment('规格摘要文本');
                $table->decimal('cost_price', 18, 4)->default(0)->comment('成本价');
                $table->decimal('sales_price', 18, 4)->default(0)->comment('销售价');
                $table->foreignId('unit_id')->nullable()->constrained('dberp_units')->nullOnDelete()->comment('计量单位（空则沿用物料默认单位）');
                $table->boolean('is_enabled')->default(true)->comment('是否启用');
                $table->boolean('is_default')->default(false)->comment('是否默认 SKU');
                $table->unsignedInteger('sort_order')->default(0)->comment('排序');
                $table->text('remark')->nullable()->comment('备注');
                $table->timestamps();

                $table->unique('sku_code', 'uq_dberp_product_sku_code');
                $table->unique('barcode', 'uq_dberp_product_sku_barcode');
                $table->index(['product_id', 'is_enabled', 'sort_order'], 'idx_dberp_psku_prod_enabled_sort');
                $table->index(['product_id', 'is_default'], 'idx_dberp_psku_prod_default');
            });
        }

        if (! Schema::hasTable('dberp_product_sku_spec_values')) {
            Schema::create('dberp_product_sku_spec_values', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('product_sku_id')->constrained('dberp_product_skus')->cascadeOnDelete()->comment('SKU');
                $table->foreignId('product_spec_group_id')->constrained('dberp_product_spec_groups')->cascadeOnDelete()->comment('规格组');
                $table->foreignId('product_spec_value_id')->constrained('dberp_product_spec_values')->cascadeOnDelete()->comment('规格值');
                $table->timestamps();

                $table->unique(['product_sku_id', 'product_spec_group_id'], 'uq_dberp_psku_spec_grp');
                $table->unique(['product_sku_id', 'product_spec_value_id'], 'uq_dberp_psku_spec_val');
                $table->index(['product_spec_value_id'], 'idx_dberp_psku_spec_val');
            });
        }

        if (Schema::hasTable('dberp_number_sequences')) {
            DB::table('dberp_number_sequences')->where('code', 'product_sku')->update([
                'applies_to' => 'product_sku',
            ]);
        }
    }

    public function down(): void
    {
        DB::table('dberp_number_sequences')->where('code', 'product_sku')->update([
            'applies_to' => 'product',
        ]);

        Schema::dropIfExists('dberp_product_sku_spec_values');
        Schema::dropIfExists('dberp_product_skus');
        Schema::dropIfExists('dberp_product_spec_values');
        Schema::dropIfExists('dberp_product_spec_groups');
    }
};
