<?php

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

/**
 * 用户扩展字段与扩展数据表
 *
 * 为后台用户(admin_users)和前台用户(users)提供可动态扩展的自定义字段机制。
 * 扩展字段定义存储字段元信息（标识、类型、选项等），
 * 扩展数据按用户+字段存储实际值。
 *
 * 应用可通过扩展字段为用户附加业务属性（如工号、职称、专长等），
 * 无需修改用户主表结构。应用卸载时清理 app_id 对应的扩展字段定义和数据。
 */
return new class extends Migration
{
    public function up(): void
    {
        // 后台用户扩展字段定义
        Schema::create('admin_user_ext_fields', function (Blueprint $table) {
            $table->id();
            $table->string('field_name', 50)->comment('字段标识，如 staff_no');
            $table->string('field_label', 50)->comment('显示名称，如 工号');
            $table->string('field_type', 20)->default('text')->comment('字段类型：text/number/select/textarea/switch/date/json');
            $table->json('field_options')->nullable()->comment('select 类型的选项 JSON，如 [{"value":"doctor","label":"医生"}]');
            $table->string('app_id', 100)->nullable()->comment('来源应用ID，应用卸载时据此清理');
            $table->unsignedInteger('sort')->default(0)->comment('排序');
            $table->unsignedTinyInteger('status')->default(1)->comment('状态：1启用 0禁用');
            $table->timestamp('create_time')->nullable();
            $table->timestamp('update_time')->nullable();
            $table->unique('field_name');
        });

        // 前台用户扩展字段定义
        Schema::create('user_ext_fields', function (Blueprint $table) {
            $table->id();
            $table->string('field_name', 50)->comment('字段标识');
            $table->string('field_label', 50)->comment('显示名称');
            $table->string('field_type', 20)->default('text')->comment('字段类型');
            $table->json('field_options')->nullable()->comment('选项 JSON');
            $table->string('app_id', 100)->nullable()->comment('来源应用ID');
            $table->unsignedInteger('sort')->default(0)->comment('排序');
            $table->unsignedTinyInteger('status')->default(1)->comment('状态');
            $table->timestamp('create_time')->nullable();
            $table->timestamp('update_time')->nullable();
            $table->unique('field_name');
        });

        // 后台用户扩展数据
        Schema::create('admin_user_ext_data', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('admin_user_id')->comment('关联 admin_users.id');
            $table->unsignedBigInteger('field_id')->comment('关联 admin_user_ext_fields.id');
            $table->text('field_value')->nullable()->comment('字段值');
            $table->timestamp('create_time')->nullable();
            $table->timestamp('update_time')->nullable();
            $table->unique(['admin_user_id', 'field_id']);
            $table->foreign('admin_user_id')->references('id')->on('admin_users')->onDelete('cascade');
            $table->foreign('field_id')->references('id')->on('admin_user_ext_fields')->onDelete('cascade');
        });

        // 前台用户扩展数据
        Schema::create('user_ext_data', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id')->comment('关联 users.id');
            $table->unsignedBigInteger('field_id')->comment('关联 user_ext_fields.id');
            $table->text('field_value')->nullable()->comment('字段值');
            $table->timestamp('create_time')->nullable();
            $table->timestamp('update_time')->nullable();
            $table->unique(['user_id', 'field_id']);
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('field_id')->references('id')->on('user_ext_fields')->onDelete('cascade');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('user_ext_data');
        Schema::dropIfExists('admin_user_ext_data');
        Schema::dropIfExists('user_ext_fields');
        Schema::dropIfExists('admin_user_ext_fields');
    }
};
