<?php

/**
 * 制造业生产管理数据库迁移
 *
 * 依据《安全生产法》第三十六条，生产经营单位应当对安全设备进行经常性维护、保养。
 * 依据《产品质量法》第十四条，生产者应当对其生产的产品质量负责，建立质量检验制度。
 * 依据 ISO 22400-2 OEE 标准，计算设备综合效率。
 * 依据 GB/T 19022 测量管理体系，检验设备需定期校准。
 * 依据《特种设备安全法》第十五条，特种设备使用单位应当建立特种设备安全技术档案。
 */

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

return new class extends Migration
{
    public function up(): void
    {
        // 1. 生产线表
        Schema::create('kylin_mfg_production_lines', function (Blueprint $table) {
            $table->id();
            $table->string('name', 100)->comment('生产线名称');
            $table->string('code', 32)->unique()->comment('生产线编码');
            $table->string('workshop', 100)->nullable()->comment('车间');
            $table->text('description')->nullable()->comment('描述');
            $table->string('status', 20)->default('active')->comment('状态: active/idle/maintenance/stopped');
            $table->timestamps();

            $table->index('status');
            $table->index('workshop');
        });

        // 2. 设备表
        Schema::create('kylin_mfg_equipment', function (Blueprint $table) {
            $table->id();
            $table->string('equipment_code', 32)->unique()->comment('设备编码');
            $table->string('equipment_name', 100)->comment('设备名称');
            $table->string('model', 64)->nullable()->comment('型号');
            $table->string('manufacturer', 100)->nullable()->comment('制造商');
            $table->string('location', 200)->nullable()->comment('安装位置');
            $table->unsignedBigInteger('production_line_id')->nullable()->comment('所属生产线 ID');
            $table->unsignedBigInteger('parent_id')->nullable()->comment('父设备 ID');
            $table->date('purchase_date')->nullable()->comment('购置日期');
            $table->date('warranty_end_date')->nullable()->comment('保修截止日');
            $table->string('status', 20)->default('idle')->comment('状态: idle/running/maintenance/fault/offline');
            $table->integer('theoretical_output_per_hour')->default(0)->comment('理论产能（件/小时）');
            $table->timestamps();

            $table->foreign('production_line_id')->references('id')->on('kylin_mfg_production_lines')->nullOnDelete();
            $table->foreign('parent_id')->references('id')->on('kylin_mfg_equipment')->nullOnDelete();
            $table->index('status');
            $table->index('equipment_code');
        });

        // 3. 工单表
        Schema::create('kylin_mfg_work_orders', function (Blueprint $table) {
            $table->id();
            $table->string('work_order_no', 32)->unique()->comment('工单号');
            $table->string('title', 200)->comment('工单标题');
            $table->unsignedBigInteger('bom_id')->nullable()->comment('BOM ID');
            $table->unsignedBigInteger('process_route_id')->nullable()->comment('工艺路线 ID');
            $table->integer('planned_quantity')->default(0)->comment('计划数量');
            $table->integer('actual_quantity')->default(0)->comment('实际数量');
            $table->integer('qualified_quantity')->default(0)->comment('合格数量');
            $table->integer('scrap_quantity')->default(0)->comment('废品数量');
            $table->string('status', 32)->default('pending')->comment('状态: pending/released/in_progress/completed/closed/cancelled');
            $table->string('priority', 20)->default('medium')->comment('优先级: low/medium/high/urgent');
            $table->unsignedBigInteger('production_line_id')->nullable()->comment('生产线 ID');
            $table->timestamp('planned_start_at')->nullable()->comment('计划开始时间');
            $table->timestamp('planned_end_at')->nullable()->comment('计划结束时间');
            $table->timestamp('actual_start_at')->nullable()->comment('实际开始时间');
            $table->timestamp('actual_end_at')->nullable()->comment('实际结束时间');
            $table->unsignedBigInteger('creator_id')->nullable()->comment('创建人 ID');
            $table->unsignedBigInteger('assignee_id')->nullable()->comment('指派人 ID');
            $table->timestamps();

            $table->foreign('production_line_id')->references('id')->on('kylin_mfg_production_lines')->nullOnDelete();
            $table->index(['status', 'planned_start_at']);
            $table->index('work_order_no');
            $table->index('assignee_id');
        });

        // 4. 检验标准表
        Schema::create('kylin_mfg_inspection_standards', function (Blueprint $table) {
            $table->id();
            $table->string('name', 100)->comment('标准名称');
            $table->string('code', 32)->unique()->comment('标准编码');
            $table->string('type', 32)->comment('检验类型: incoming/in_process/final');
            $table->string('inspection_method', 100)->nullable()->comment('检验方法');
            $table->decimal('standard_value', 12, 4)->default(0)->comment('标准值');
            $table->decimal('tolerance', 12, 4)->default(0)->comment('公差');
            $table->decimal('aql_level', 5, 2)->default(0)->comment('AQL 接收质量限');
            $table->decimal('sample_ratio', 8, 4)->default(0)->comment('抽样比例');
            $table->string('status', 20)->default('active')->comment('状态: active/inactive');
            $table->text('description')->nullable()->comment('描述');
            $table->timestamps();

            $table->index('type');
            $table->index('status');
        });

        // 5. 检验记录表
        Schema::create('kylin_mfg_inspection_records', function (Blueprint $table) {
            $table->id();
            $table->string('inspection_no', 32)->unique()->comment('检验编号');
            $table->string('type', 32)->comment('检验类型: incoming/in_process/final');
            $table->unsignedBigInteger('work_order_id')->nullable()->comment('工单 ID');
            $table->string('material_batch_no', 64)->nullable()->comment('物料批次号');
            $table->string('product_serial_no', 64)->nullable()->comment('产品序列号');
            $table->string('process_step', 64)->nullable()->comment('工序');
            $table->unsignedBigInteger('standard_id')->nullable()->comment('检验标准 ID');
            $table->string('result', 20)->default('pending')->comment('检验结果: pending/passed/failed/conditional');
            $table->unsignedBigInteger('inspector_id')->nullable()->comment('检验员 ID');
            $table->timestamp('inspected_at')->nullable()->comment('检验时间');
            $table->json('inspection_items')->nullable()->comment('检验项目（JSON）');
            $table->text('remarks')->nullable()->comment('备注');
            $table->timestamps();

            $table->foreign('work_order_id')->references('id')->on('kylin_mfg_work_orders')->cascadeOnDelete();
            $table->foreign('standard_id')->references('id')->on('kylin_mfg_inspection_standards')->nullOnDelete();
            $table->index(['type', 'result']);
            $table->index('inspector_id');
        });

        // 6. 异常记录表
        Schema::create('kylin_mfg_abnormal_records', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('work_order_id')->nullable()->comment('工单 ID');
            $table->unsignedBigInteger('equipment_id')->nullable()->comment('设备 ID');
            $table->string('type', 32)->comment('异常类型: equipment/quality/safety/material/process');
            $table->text('description')->comment('异常描述');
            $table->string('severity', 20)->default('medium')->comment('严重程度: low/medium/high/critical');
            $table->unsignedBigInteger('reporter_id')->nullable()->comment('报告人 ID');
            $table->unsignedBigInteger('handler_id')->nullable()->comment('处理人 ID');
            $table->text('solution')->nullable()->comment('解决方案');
            $table->string('status', 20)->default('open')->comment('状态: open/in_progress/resolved/closed');
            $table->timestamp('reported_at')->comment('报告时间');
            $table->timestamp('resolved_at')->nullable()->comment('解决时间');
            $table->timestamps();

            $table->foreign('work_order_id')->references('id')->on('kylin_mfg_work_orders')->cascadeOnDelete();
            $table->foreign('equipment_id')->references('id')->on('kylin_mfg_equipment')->cascadeOnDelete();
            $table->index(['type', 'status']);
            $table->index('severity');
        });

        // 7. 维护计划表
        Schema::create('kylin_mfg_maintenance_plans', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('equipment_id')->comment('设备 ID');
            $table->string('type', 32)->comment('维护类型: daily/weekly/monthly/quarterly/yearly');
            $table->text('content')->comment('维护内容');
            $table->integer('cycle_days')->default(30)->comment('周期（天）');
            $table->date('next_date')->comment('下次执行日期');
            $table->timestamp('last_executed_at')->nullable()->comment('上次执行时间');
            $table->string('status', 20)->default('active')->comment('状态: active/paused/completed');
            $table->timestamps();

            $table->foreign('equipment_id')->references('id')->on('kylin_mfg_equipment')->cascadeOnDelete();
            $table->index('next_date');
            $table->index('status');
        });

        // 8. 维修工单表
        Schema::create('kylin_mfg_repair_orders', function (Blueprint $table) {
            $table->id();
            $table->string('repair_no', 32)->unique()->comment('维修单号');
            $table->unsignedBigInteger('equipment_id')->comment('设备 ID');
            $table->unsignedBigInteger('reporter_id')->nullable()->comment('报告人 ID');
            $table->unsignedBigInteger('assignee_id')->nullable()->comment('指派人 ID');
            $table->text('fault_description')->comment('故障描述');
            $table->string('fault_type', 32)->nullable()->comment('故障类型: mechanical/electrical/hydraulic/pneumatic/control/other');
            $table->text('repair_method')->nullable()->comment('维修方法');
            $table->string('status', 20)->default('pending')->comment('状态: pending/in_progress/completed/verified/cancelled');
            $table->timestamp('reported_at')->comment('报告时间');
            $table->timestamp('completed_at')->nullable()->comment('完成时间');
            $table->timestamps();

            $table->foreign('equipment_id')->references('id')->on('kylin_mfg_equipment')->cascadeOnDelete();
            $table->index(['equipment_id', 'status']);
            $table->index('repair_no');
        });

        // 9. OEE 记录表
        Schema::create('kylin_mfg_oee_records', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('equipment_id')->comment('设备 ID');
            $table->date('date')->comment('统计日期');
            $table->integer('planned_time')->default(0)->comment('计划时间（分钟）');
            $table->integer('run_time')->default(0)->comment('运行时间（分钟）');
            $table->integer('actual_output')->default(0)->comment('实际产量');
            $table->integer('qualified_output')->default(0)->comment('合格产量');
            $table->decimal('availability', 6, 2)->default(0)->comment('可用率（%）');
            $table->decimal('performance', 6, 2)->default(0)->comment('表现指数（%）');
            $table->decimal('quality', 6, 2)->default(0)->comment('质量指数（%）');
            $table->decimal('oee', 6, 2)->default(0)->comment('OEE 综合效率（%）');
            $table->timestamps();

            $table->foreign('equipment_id')->references('id')->on('kylin_mfg_equipment')->cascadeOnDelete();
            $table->unique(['equipment_id', 'date']);
            $table->index('date');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('kylin_mfg_oee_records');
        Schema::dropIfExists('kylin_mfg_repair_orders');
        Schema::dropIfExists('kylin_mfg_maintenance_plans');
        Schema::dropIfExists('kylin_mfg_abnormal_records');
        Schema::dropIfExists('kylin_mfg_inspection_records');
        Schema::dropIfExists('kylin_mfg_inspection_standards');
        Schema::dropIfExists('kylin_mfg_work_orders');
        Schema::dropIfExists('kylin_mfg_equipment');
        Schema::dropIfExists('kylin_mfg_production_lines');
    }
};
