<?php

/**
 * 物流运输管理数据库迁移
 *
 * 依据《道路运输条例》第二十三条，运输车辆应具备有效行驶证、运营证。
 * 依据《道路交通安全法》及 JT/T 808 标准，营运车辆需安装 GPS 卫星定位装置。
 * 依据《个人信息保护法》第二十八条，司机驾驶证号、收发货人电话等敏感信息加密存储。
 * 依据《冷链物流运输温度控制规范》，冷链运输全程温度监控，记录保留 >= 1 年。
 * 依据《快递暂行条例》第二十二条，签收记录应保存 >= 1 年。
 */

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_log_waybills', function (Blueprint $table) {
            $table->id();
            $table->string('waybill_no', 32)->unique()->comment('运单号');
            $table->string('shipper_name', 100)->comment('发货人姓名');
            $table->string('shipper_phone', 32)->comment('发货人电话（加密）');
            $table->string('shipper_address', 255)->comment('发货地址');
            $table->string('consignee_name', 100)->comment('收货人姓名');
            $table->string('consignee_phone', 32)->comment('收货人电话（加密）');
            $table->string('consignee_address', 255)->comment('收货地址');
            $table->string('cargo_name', 200)->comment('货物名称');
            $table->string('cargo_type', 32)->comment('货物类型');
            $table->decimal('cargo_weight', 10, 2)->default(0)->comment('货物重量（kg）');
            $table->decimal('cargo_volume', 10, 2)->default(0)->comment('货物体积（m³）');
            $table->integer('cargo_quantity')->default(1)->comment('货物数量');
            $table->string('package_type', 32)->nullable()->comment('包装类型');
            $table->decimal('freight_amount', 12, 2)->default(0)->comment('运费');
            $table->string('settlement_type', 20)->default('prepaid')->comment('结算方式: prepaid/cod/monthly');
            $table->timestamp('required_arrival')->nullable()->comment('要求到达时间');
            $table->text('remark')->nullable()->comment('备注');
            $table->string('status', 32)->default('pending')->comment('运单状态');
            $table->unsignedBigInteger('creator_id')->nullable()->comment('创建人 ID');
            $table->timestamps();

            $table->index(['status', 'created_at']);
            $table->index('creator_id');
        });

        // 2. 车辆表
        Schema::create('kylin_log_vehicles', function (Blueprint $table) {
            $table->id();
            $table->string('plate_number', 20)->unique()->comment('车牌号');
            $table->string('vehicle_type', 32)->comment('车辆类型');
            $table->decimal('rated_load', 10, 2)->default(0)->comment('额定载重（吨）');
            $table->string('status', 20)->default('idle')->comment('状态: idle/running/maintenance/offline');
            $table->string('gps_device_id', 64)->nullable()->comment('GPS 设备 ID');
            $table->unsignedBigInteger('fleet_id')->nullable()->comment('车队 ID');
            $table->date('inspection_expire')->nullable()->comment('年检到期日');
            $table->date('insurance_expire')->nullable()->comment('保险到期日');
            $table->timestamps();

            $table->index('status');
            $table->index('fleet_id');
            $table->index('inspection_expire');
            $table->index('insurance_expire');
        });

        // 3. 司机表
        Schema::create('kylin_log_drivers', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id')->nullable()->comment('关联用户 ID');
            $table->unsignedBigInteger('vehicle_id')->nullable()->comment('绑定车辆 ID');
            $table->string('license_no', 32)->comment('驾驶证号（加密）');
            $table->string('license_type', 20)->comment('驾驶证类型: A1/A2/B1/B2/C1 等');
            $table->date('license_expire')->comment('驾驶证有效期');
            $table->string('qualification_no', 64)->nullable()->comment('从业资格证号');
            $table->date('qualification_expire')->nullable()->comment('从业资格证有效期');
            $table->timestamps();

            $table->foreign('vehicle_id')->references('id')->on('kylin_log_vehicles')->nullOnDelete();
            $table->index('user_id');
            $table->index('license_expire');
            $table->index('qualification_expire');
        });

        // 4. 路线表
        Schema::create('kylin_log_routes', function (Blueprint $table) {
            $table->id();
            $table->string('route_name', 100)->comment('路线名称');
            $table->string('origin', 200)->comment('起点');
            $table->string('destination', 200)->comment('终点');
            $table->json('waypoints')->nullable()->comment('途经点（JSON）');
            $table->decimal('total_distance', 10, 2)->default(0)->comment('总距离（公里）');
            $table->integer('estimated_duration')->default(0)->comment('预估时长（分钟）');
            $table->decimal('origin_lng', 12, 8)->nullable()->comment('起点经度');
            $table->decimal('origin_lat', 12, 8)->nullable()->comment('起点纬度');
            $table->decimal('destination_lng', 12, 8)->nullable()->comment('终点经度');
            $table->decimal('destination_lat', 12, 8)->nullable()->comment('终点纬度');
            $table->timestamps();

            $table->index('route_name');
        });

        // 5. 调度表
        Schema::create('kylin_log_dispatches', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('waybill_id')->comment('运单 ID');
            $table->unsignedBigInteger('vehicle_id')->nullable()->comment('车辆 ID');
            $table->unsignedBigInteger('driver_id')->nullable()->comment('司机 ID');
            $table->unsignedBigInteger('route_id')->nullable()->comment('路线 ID');
            $table->timestamp('dispatch_time')->comment('调度时间');
            $table->timestamp('departure_time')->nullable()->comment('出发时间');
            $table->timestamp('arrival_time')->nullable()->comment('到达时间');
            $table->string('status', 32)->default('dispatched')->comment('状态: dispatched/in_transit/arrived/cancelled');
            $table->timestamps();

            $table->foreign('waybill_id')->references('id')->on('kylin_log_waybills')->cascadeOnDelete();
            $table->foreign('vehicle_id')->references('id')->on('kylin_log_vehicles')->nullOnDelete();
            $table->foreign('driver_id')->references('id')->on('kylin_log_drivers')->nullOnDelete();
            $table->foreign('route_id')->references('id')->on('kylin_log_routes')->nullOnDelete();
            $table->index('status');
        });

        // 6. GPS 定位表
        Schema::create('kylin_log_gps_locations', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('vehicle_id')->comment('车辆 ID');
            $table->decimal('lng', 12, 8)->comment('经度');
            $table->decimal('lat', 12, 8)->comment('纬度');
            $table->decimal('speed', 5, 1)->default(0)->comment('速度（km/h）');
            $table->integer('direction')->default(0)->comment('方向（0-359 度）');
            $table->timestamp('located_at')->comment('定位时间');

            $table->foreign('vehicle_id')->references('id')->on('kylin_log_vehicles')->cascadeOnDelete();
            $table->index(['vehicle_id', 'located_at']);
        });

        // 7. GPS 轨迹表
        Schema::create('kylin_log_gps_tracks', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('vehicle_id')->comment('车辆 ID');
            $table->date('track_date')->comment('轨迹日期');
            $table->json('track_data')->nullable()->comment('轨迹数据（JSON）');

            $table->foreign('vehicle_id')->references('id')->on('kylin_log_vehicles')->cascadeOnDelete();
            $table->unique(['vehicle_id', 'track_date']);
        });

        // 8. 签收记录表
        Schema::create('kylin_log_sign_records', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('waybill_id')->comment('运单 ID');
            $table->string('signer_name', 100)->comment('签收人姓名');
            $table->string('signer_phone', 32)->nullable()->comment('签收人电话');
            $table->timestamp('sign_time')->comment('签收时间');
            $table->string('sign_type', 20)->default('manual')->comment('签收方式: manual/photo/signature/electronic');
            $table->string('sign_photo', 500)->nullable()->comment('签收照片路径');
            $table->string('sign_signature', 500)->nullable()->comment('签名图片路径');
            $table->string('cargo_status', 20)->default('normal')->comment('货物状态: normal/damaged/missing/partial');
            $table->text('exception_remark')->nullable()->comment('异常备注');
            $table->decimal('sign_lng', 12, 8)->nullable()->comment('签收经度');
            $table->decimal('sign_lat', 12, 8)->nullable()->comment('签收纬度');
            $table->timestamp('created_at')->comment('记录创建时间');

            $table->foreign('waybill_id')->references('id')->on('kylin_log_waybills')->cascadeOnDelete();
            $table->index('sign_time');
        });

        // 9. 温度记录表（冷链运输）
        Schema::create('kylin_log_temp_records', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('waybill_id')->comment('运单 ID');
            $table->string('sensor_id', 64)->nullable()->comment('传感器 ID');
            $table->decimal('temp', 4, 1)->comment('温度值（℃）');
            $table->boolean('is_abnormal')->default(false)->comment('是否异常');
            $table->timestamp('recorded_at')->comment('记录时间');

            $table->foreign('waybill_id')->references('id')->on('kylin_log_waybills')->cascadeOnDelete();
            $table->index(['waybill_id', 'recorded_at']);
            $table->index('is_abnormal');
        });

        // 10. 报警表
        Schema::create('kylin_log_alarms', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('vehicle_id')->nullable()->comment('车辆 ID');
            $table->unsignedBigInteger('waybill_id')->nullable()->comment('运单 ID');
            $table->string('alarm_type', 32)->comment('报警类型: overspeed/offroute/temp_abnormal/fatigue_driving/geo_fence');
            $table->decimal('alarm_value', 10, 2)->default(0)->comment('报警值');
            $table->decimal('threshold_value', 10, 2)->default(0)->comment('阈值');
            $table->decimal('lng', 12, 8)->nullable()->comment('经度');
            $table->decimal('lat', 12, 8)->nullable()->comment('纬度');
            $table->boolean('is_handled')->default(false)->comment('是否已处理');
            $table->unsignedBigInteger('handler_id')->nullable()->comment('处理人 ID');
            $table->timestamp('handled_at')->nullable()->comment('处理时间');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->foreign('vehicle_id')->references('id')->on('kylin_log_vehicles')->cascadeOnDelete();
            $table->foreign('waybill_id')->references('id')->on('kylin_log_waybills')->cascadeOnDelete();
            $table->index(['vehicle_id', 'alarm_type']);
            $table->index('is_handled');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('kylin_log_alarms');
        Schema::dropIfExists('kylin_log_temp_records');
        Schema::dropIfExists('kylin_log_sign_records');
        Schema::dropIfExists('kylin_log_gps_tracks');
        Schema::dropIfExists('kylin_log_gps_locations');
        Schema::dropIfExists('kylin_log_dispatches');
        Schema::dropIfExists('kylin_log_routes');
        Schema::dropIfExists('kylin_log_drivers');
        Schema::dropIfExists('kylin_log_vehicles');
        Schema::dropIfExists('kylin_log_waybills');
    }
};
