chat/backend/database/migrate_roles_departments.php

<?php

/**
 * 角色与部门迁移
 * 用法: php database/migrate_roles_departments.php
 */

require __DIR__ . '/../vendor/autoload.php';

$app = new think\App();
$app->initialize();

use think\facade\Db;

function columnExists(string $table, string $column): bool
{
    $db = Db::getConfig('database') ?: env('DB_NAME', 'ai_chat');
    $rows = Db::query(
        'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1',
        [$db, $table, $column]
    );
    return !empty($rows);
}

function tableExists(string $table): bool
{
    $db = Db::getConfig('database') ?: env('DB_NAME', 'ai_chat');
    $rows = Db::query(
        'SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? LIMIT 1',
        [$db, $table]
    );
    return !empty($rows);
}

try {
    if (!tableExists('roles')) {
        Db::execute("CREATE TABLE roles (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(50) NOT NULL,
            slug VARCHAR(50) NOT NULL UNIQUE,
            permissions JSON NULL,
            sort_order INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        echo "Created table roles\n";
    }

    if (!tableExists('departments')) {
        Db::execute("CREATE TABLE departments (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            parent_id INT UNSIGNED NULL,
            sort_order INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_parent (parent_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        echo "Created table departments\n";
    }

    if (!columnExists('users', 'role_id')) {
        Db::execute('ALTER TABLE users ADD COLUMN role_id INT UNSIGNED NULL AFTER role');
        echo "Added users.role_id\n";
    }

    if (!columnExists('users', 'department_id')) {
        Db::execute('ALTER TABLE users ADD COLUMN department_id INT UNSIGNED NULL AFTER role_id');
        echo "Added users.department_id\n";
    }

    $roleCount = (int) Db::name('roles')->count();
    if ($roleCount === 0) {
        $allTrue = json_encode([
            'can_access_admin'                   => true,
            'can_manage_users'                   => true,
            'can_manage_roles'                   => true,
            'can_manage_departments'             => true,
            'can_view_all_conversations'         => true,
            'can_view_subordinate_conversations' => true,
            'can_manage_models'                  => true,
            'can_manage_settings'                => true,
            'can_manage_memberships'             => true,
        ], JSON_UNESCAPED_UNICODE);

        $deptManager = json_encode([
            'can_access_admin'                   => true,
            'can_manage_users'                   => false,
            'can_manage_roles'                   => false,
            'can_manage_departments'             => false,
            'can_view_all_conversations'         => false,
            'can_view_subordinate_conversations' => true,
            'can_manage_models'                  => false,
            'can_manage_settings'                => false,
            'can_manage_memberships'             => false,
        ], JSON_UNESCAPED_UNICODE);

        $userPerms = json_encode([
            'can_access_admin'                   => false,
            'can_manage_users'                   => false,
            'can_manage_roles'                   => false,
            'can_manage_departments'             => false,
            'can_view_all_conversations'         => false,
            'can_view_subordinate_conversations' => false,
            'can_manage_models'                  => false,
            'can_manage_settings'                => false,
            'can_manage_memberships'             => false,
        ], JSON_UNESCAPED_UNICODE);

        Db::name('roles')->insertAll([
            ['name' => '超级管理员', 'slug' => 'super_admin', 'permissions' => $allTrue, 'sort_order' => 1],
            ['name' => '部门管理员', 'slug' => 'dept_manager', 'permissions' => $deptManager, 'sort_order' => 2],
            ['name' => '普通用户', 'slug' => 'user', 'permissions' => $userPerms, 'sort_order' => 3],
        ]);
        echo "Seeded default roles\n";
    }

    $superAdminId = (int) Db::name('roles')->where('slug', 'super_admin')->value('id');
    $userRoleId = (int) Db::name('roles')->where('slug', 'user')->value('id');

    if ($superAdminId > 0) {
        Db::name('users')->where('role', 'admin')->where(function ($q) {
            $q->whereNull('role_id')->whereOr('role_id', 0);
        })->update(['role_id' => $superAdminId]);
    }

    if ($userRoleId > 0) {
        Db::name('users')->where('role', 'user')->where(function ($q) {
            $q->whereNull('role_id')->whereOr('role_id', 0);
        })->update(['role_id' => $userRoleId]);

        Db::name('users')->where(function ($q) {
            $q->whereNull('role_id')->whereOr('role_id', 0);
        })->update(['role_id' => $userRoleId]);
    }

    if ((int) Db::name('departments')->count() === 0) {
        Db::name('departments')->insert([
            'name'       => '总公司',
            'parent_id'  => null,
            'sort_order' => 0,
        ]);
        echo "Seeded root department\n";
    }

    echo "Migration completed.\n";
} catch (\Throwable $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
    exit(1);
}