<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // 禁用外键检查
        if (DB::getDriverName() !== 'sqlite') {
            DB::statement('SET FOREIGN_KEY_CHECKS=0');
        }

        // 先备份数据（如果users表存在）
        if (Schema::hasTable('users')) {
            $users = DB::table('users')->get();
        } else {
            $users = [];
        }

        // 删除引用users的外键约束（如果存在）
        if (DB::getDriverName() !== 'sqlite') {
            try { DB::statement('ALTER TABLE students DROP FOREIGN KEY students_ibfk_1'); } catch (\Exception $e) {}
            try { DB::statement('ALTER TABLE students DROP FOREIGN KEY students_ibfk_2'); } catch (\Exception $e) {}
            try { DB::statement('ALTER TABLE teachers DROP FOREIGN KEY teachers_ibfk_1'); } catch (\Exception $e) {}
        }

        // 删除旧的users表
        Schema::dropIfExists('users');

        // 创建新的users表，使用自增id
        Schema::create('users', function (Blueprint $table) {
            $table->id(); // 自增主键
            $table->string('user_id', 64)->nullable()->index(); // 保留原始ID字段但可为空，添加索引用于外键
            $table->string('username', 100)->unique()->index();
            $table->string('email', 100)->unique()->nullable()->index();
            $table->string('password_hash', 255);
            $table->string('full_name', 100);
            $table->enum('role', ['admin', 'teacher', 'student'])->default('teacher')->index();
            $table->string('phone', 20)->nullable()->index();
            $table->string('department', 100)->nullable();
            $table->boolean('is_active')->default(true)->index();
            $table->timestamps();
            $table->datetime('last_login')->nullable();
            $table->integer('login_count')->default(0);
            $table->softDeletes();

            // 索引已在上面添加
        });

        // 恢复数据（转换为自增id）
        foreach ($users as $user) {
            DB::table('users')->insert([
                'user_id' => $user->user_id ?? null,
                'username' => $user->username,
                'email' => $user->email,
                'password_hash' => $user->password_hash,
                'full_name' => $user->full_name,
                'role' => $user->role ?? 'teacher',
                'phone' => $user->phone,
                'department' => $user->department,
                'is_active' => $user->is_active ?? 1,
                'created_at' => $user->created_at ?? null,
                'updated_at' => $user->updated_at ?? null,
                'last_login' => $user->last_login ?? null,
                'login_count' => $user->login_count ?? 0,
                'deleted_at' => $user->deleted_at ?? null,
            ]);
        }

        // 重新添加外键约束（引用user_id字段）
        if (DB::getDriverName() !== 'sqlite') {
            DB::statement('ALTER TABLE students ADD CONSTRAINT students_ibfk_1 FOREIGN KEY (student_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE');
            DB::statement('ALTER TABLE students ADD CONSTRAINT students_ibfk_2 FOREIGN KEY (teacher_id) REFERENCES users(user_id) ON DELETE SET NULL ON UPDATE CASCADE');
            DB::statement('ALTER TABLE teachers ADD CONSTRAINT teachers_ibfk_1 FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE');
        }

        // 恢复外键检查
        if (DB::getDriverName() !== 'sqlite') {
            DB::statement('SET FOREIGN_KEY_CHECKS=1');
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // 禁用外键检查
        if (DB::getDriverName() !== 'sqlite') {
            DB::statement('SET FOREIGN_KEY_CHECKS=0');
        }

        // 备份现有数据
        if (Schema::hasTable('users')) {
            $users = DB::table('users')->get();
        } else {
            $users = [];
        }

        // 删除外键约束（如果存在）
        if (DB::getDriverName() !== 'sqlite') {
            try { DB::statement('ALTER TABLE students DROP FOREIGN KEY students_ibfk_1'); } catch (\Exception $e) {}
            try { DB::statement('ALTER TABLE students DROP FOREIGN KEY students_ibfk_2'); } catch (\Exception $e) {}
            try { DB::statement('ALTER TABLE teachers DROP FOREIGN KEY teachers_ibfk_1'); } catch (\Exception $e) {}
        }

        // 删除users表
        Schema::dropIfExists('users');

        // 重新创建旧的users表结构（使用user_id作为主键）
        Schema::create('users', function (Blueprint $table) {
            $table->string('user_id', 64)->primary();
            $table->string('username', 100)->unique()->index();
            $table->string('email', 100)->unique()->nullable()->index();
            $table->string('password_hash', 255);
            $table->string('full_name', 100);
            $table->enum('role', ['admin', 'teacher', 'student'])->default('teacher')->index();
            $table->string('phone', 20)->nullable()->index();
            $table->string('department', 100)->nullable();
            $table->boolean('is_active')->default(true)->index();
            $table->timestamps();
            $table->datetime('last_login')->nullable();
            $table->integer('login_count')->default(0);
            $table->softDeletes();

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

        // 恢复数据（使用原始user_id）
        foreach ($users as $user) {
            DB::table('users')->insert([
                'user_id' => $user->user_id ?? $user->id,
                'username' => $user->username,
                'email' => $user->email,
                'password_hash' => $user->password_hash,
                'full_name' => $user->full_name,
                'role' => $user->role ?? 'teacher',
                'phone' => $user->phone,
                'department' => $user->department,
                'is_active' => $user->is_active ?? 1,
                'created_at' => $user->created_at ?? null,
                'updated_at' => $user->updated_at ?? null,
                'last_login' => $user->last_login ?? null,
                'login_count' => $user->login_count ?? 0,
                'deleted_at' => $user->deleted_at ?? null,
            ]);
        }

        // 重新添加外键约束（引用user_id字段）
        if (DB::getDriverName() !== 'sqlite') {
            DB::statement('ALTER TABLE students ADD CONSTRAINT students_ibfk_1 FOREIGN KEY (student_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE');
            DB::statement('ALTER TABLE students ADD CONSTRAINT students_ibfk_2 FOREIGN KEY (teacher_id) REFERENCES users(user_id) ON DELETE SET NULL ON UPDATE CASCADE');
            DB::statement('ALTER TABLE teachers ADD CONSTRAINT teachers_ibfk_1 FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE');
        }

        // 恢复外键检查
        if (DB::getDriverName() !== 'sqlite') {
            DB::statement('SET FOREIGN_KEY_CHECKS=1');
        }
    }
};
