<?php

/**
 * 智慧农业数据库迁移
 *
 * 依据《农药管理条例》第二十七条，农药使用记录至少保存 2 年。
 * 依据《农产品质量安全法》第二十七条，农产品生产记录保存至少二年。
 * 依据《种子法》第四十条，种子来源可追溯。
 * 依据《个人信息保护法》第二十八条，操作人员信息脱敏处理。
 */

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_agri_plots', function (Blueprint $table) {
            $table->id();
            $table->string('name', 100)->comment('地块名称');
            $table->decimal('area', 10, 2)->default(0)->comment('面积（亩）');
            $table->string('type', 32)->nullable()->comment('地块类型');
            $table->string('soil_type', 32)->nullable()->comment('土壤类型');
            $table->boolean('irrigation')->default(false)->comment('是否灌溉');
            $table->decimal('longitude', 10, 7)->nullable()->comment('经度');
            $table->decimal('latitude', 10, 7)->nullable()->comment('纬度');
            $table->unsignedBigInteger('manager_id')->nullable()->comment('负责人 ID');
            $table->boolean('status')->default(true)->comment('状态');
            $table->timestamps();
            $table->softDeletes();

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

        // 2. 传感器表
        Schema::create('kylin_agri_sensors', function (Blueprint $table) {
            $table->id();
            $table->string('device_id', 64)->unique()->comment('设备编号');
            $table->string('type', 32)->comment('传感器类型');
            $table->unsignedBigInteger('plot_id')->nullable()->comment('关联地块');
            $table->json('parameters')->nullable()->comment('监控参数（JSON）');
            $table->integer('frequency')->default(300)->comment('采集频率（秒）');
            $table->string('status', 20)->default('offline')->comment('在线状态');
            $table->timestamp('last_data_at')->nullable()->comment('最后数据时间');
            $table->timestamps();

            $table->foreign('plot_id')->references('id')->on('kylin_agri_plots')->nullOnDelete();
            $table->index('status');
        });

        // 3. 传感器数据表
        Schema::create('kylin_agri_sensor_data', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('sensor_id')->comment('传感器 ID');
            $table->json('parameters')->nullable()->comment('采集数据（JSON）');
            $table->timestamp('collected_at')->comment('采集时间');
            $table->timestamp('created_at')->nullable();

            $table->foreign('sensor_id')->references('id')->on('kylin_agri_sensors')->cascadeOnDelete();
            $table->index(['sensor_id', 'collected_at']);
        });

        // 4. 种植计划表
        Schema::create('kylin_agri_planting_plans', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('plot_id')->comment('地块 ID');
            $table->string('crop', 64)->comment('作物名称');
            $table->string('variety', 64)->nullable()->comment('品种');
            $table->string('seed_source', 200)->nullable()->comment('种子来源（《种子法》第四十条）');
            $table->string('seed_supplier', 200)->nullable()->comment('种子供应商');
            $table->date('start_date')->comment('播种日期');
            $table->date('end_date')->nullable()->comment('预计收获日期');
            $table->decimal('target_yield', 10, 2)->default(0)->comment('目标产量');
            $table->boolean('status')->default(true)->comment('状态');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();
            $table->softDeletes();

            $table->foreign('plot_id')->references('id')->on('kylin_agri_plots')->cascadeOnDelete();
            $table->index('crop');
            $table->index('status');
        });

        // 5. 农事记录表
        Schema::create('kylin_agri_farm_records', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('plot_id')->comment('地块 ID');
            $table->unsignedBigInteger('planting_plan_id')->nullable()->comment('种植计划 ID');
            $table->string('type', 32)->comment('农事类型');
            $table->text('content')->nullable()->comment('操作描述');
            $table->json('images')->nullable()->comment('图片（JSON）');
            $table->unsignedBigInteger('operator_id')->nullable()->comment('操作人 ID');
            $table->timestamp('recorded_at')->comment('操作时间');
            $table->timestamps();

            $table->foreign('plot_id')->references('id')->on('kylin_agri_plots')->cascadeOnDelete();
            $table->foreign('planting_plan_id')->references('id')->on('kylin_agri_planting_plans')->nullOnDelete();
            $table->index(['plot_id', 'recorded_at']);
            $table->index('type');
        });

        // 6. 物料表
        Schema::create('kylin_agri_materials', function (Blueprint $table) {
            $table->id();
            $table->string('name', 100)->comment('物料名称');
            $table->string('category', 32)->nullable()->comment('分类: seed/fertilizer/pesticide/tool');
            $table->string('unit', 20)->default('kg')->comment('单位');
            $table->decimal('stock', 12, 2)->default(0)->comment('库存数量');
            $table->decimal('safety_stock', 12, 2)->default(0)->comment('安全库存');
            $table->decimal('price', 10, 2)->default(0)->comment('单价');
            $table->boolean('status')->default(true)->comment('状态');
            $table->timestamps();
            $table->softDeletes();

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

        // 7. 物料日志表
        Schema::create('kylin_agri_material_logs', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('material_id')->comment('物料 ID');
            $table->string('type', 20)->comment('类型: in/out');
            $table->decimal('quantity', 12, 2)->comment('数量');
            $table->unsignedBigInteger('operator_id')->nullable()->comment('操作人 ID');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->foreign('material_id')->references('id')->on('kylin_agri_materials')->cascadeOnDelete();
            $table->index('type');
        });

        // 8. 病虫害记录表
        Schema::create('kylin_agri_pest_records', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('plot_id')->comment('地块 ID');
            $table->string('pest_type', 64)->comment('病虫害类型');
            $table->text('description')->nullable()->comment('描述');
            $table->json('images')->nullable()->comment('图片（JSON）');
            $table->integer('severity')->default(1)->comment('严重程度 1-5');
            $table->unsignedBigInteger('recorder_id')->nullable()->comment('记录人 ID');
            $table->timestamp('recorded_at')->comment('记录时间');
            $table->timestamps();

            $table->foreign('plot_id')->references('id')->on('kylin_agri_plots')->cascadeOnDelete();
            $table->index('pest_type');
            $table->index('severity');
        });

        // 9. 农药使用记录表
        Schema::create('kylin_agri_pesticide_records', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('plot_id')->comment('地块 ID');
            $table->unsignedBigInteger('pest_record_id')->nullable()->comment('病虫害记录 ID');
            $table->string('pesticide', 100)->comment('农药名称');
            $table->decimal('dosage', 10, 2)->comment('用量');
            $table->decimal('area', 10, 2)->nullable()->comment('施用面积（亩）');
            $table->integer('safety_interval')->default(7)->comment('安全间隔期（天）');
            $table->date('safe_until')->nullable()->comment('安全间隔期截止日期');
            $table->unsignedBigInteger('operator_id')->nullable()->comment('操作人 ID');
            $table->timestamp('applied_at')->comment('施药时间');
            $table->timestamps();

            $table->foreign('plot_id')->references('id')->on('kylin_agri_plots')->cascadeOnDelete();
            $table->foreign('pest_record_id')->references('id')->on('kylin_agri_pest_records')->nullOnDelete();
            $table->index('pesticide');
            $table->index('safe_until');
        });

        // 10. 产量记录表
        Schema::create('kylin_agri_yield_records', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('plot_id')->comment('地块 ID');
            $table->unsignedBigInteger('planting_plan_id')->nullable()->comment('种植计划 ID');
            $table->string('crop', 64)->comment('作物名称');
            $table->decimal('quantity', 12, 2)->comment('产量');
            $table->string('unit', 20)->default('kg')->comment('单位');
            $table->decimal('cost', 12, 2)->default(0)->comment('成本');
            $table->decimal('revenue', 12, 2)->default(0)->comment('收入');
            $table->timestamp('recorded_at')->comment('记录时间');
            $table->timestamps();

            $table->foreign('plot_id')->references('id')->on('kylin_agri_plots')->cascadeOnDelete();
            $table->foreign('planting_plan_id')->references('id')->on('kylin_agri_planting_plans')->nullOnDelete();
            $table->index('crop');
        });

        // 11. 预警规则表
        Schema::create('kylin_agri_warning_rules', function (Blueprint $table) {
            $table->id();
            $table->string('name', 100)->comment('规则名称');
            $table->string('parameter', 64)->comment('监控参数');
            $table->string('operator', 10)->comment('比较运算符: >/<>=/<=/==');
            $table->decimal('threshold', 10, 2)->comment('阈值');
            $table->string('level', 20)->default('warning')->comment('预警等级: info/warning/danger');
            $table->boolean('enabled')->default(true)->comment('是否启用');
            $table->timestamps();

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

        // 12. 预警记录表
        Schema::create('kylin_agri_warnings', function (Blueprint $table) {
            $table->id();
            $table->string('type', 32)->comment('预警类型');
            $table->string('level', 20)->default('warning')->comment('预警等级');
            $table->text('content')->nullable()->comment('预警内容');
            $table->unsignedBigInteger('plot_id')->nullable()->comment('地块 ID');
            $table->unsignedBigInteger('sensor_id')->nullable()->comment('传感器 ID');
            $table->unsignedBigInteger('rule_id')->nullable()->comment('规则 ID');
            $table->string('status', 20)->default('pending')->comment('状态: pending/handled/ignored');
            $table->unsignedBigInteger('handler_id')->nullable()->comment('处理人 ID');
            $table->timestamp('handled_at')->nullable()->comment('处理时间');
            $table->timestamps();

            $table->foreign('plot_id')->references('id')->on('kylin_agri_plots')->nullOnDelete();
            $table->foreign('sensor_id')->references('id')->on('kylin_agri_sensors')->nullOnDelete();
            $table->foreign('rule_id')->references('id')->on('kylin_agri_warning_rules')->nullOnDelete();
            $table->index(['status', 'level']);
            $table->index('plot_id');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('kylin_agri_warnings');
        Schema::dropIfExists('kylin_agri_warning_rules');
        Schema::dropIfExists('kylin_agri_yield_records');
        Schema::dropIfExists('kylin_agri_pesticide_records');
        Schema::dropIfExists('kylin_agri_pest_records');
        Schema::dropIfExists('kylin_agri_material_logs');
        Schema::dropIfExists('kylin_agri_materials');
        Schema::dropIfExists('kylin_agri_farm_records');
        Schema::dropIfExists('kylin_agri_planting_plans');
        Schema::dropIfExists('kylin_agri_sensor_data');
        Schema::dropIfExists('kylin_agri_sensors');
        Schema::dropIfExists('kylin_agri_plots');
    }
};
