Unified_Authentication_Platform/sql/V6__add_organizations.sql

-- V6__add_organizations.sql
-- 用户所属组织（扁平列表），users.organization_id 可空、无默认

CREATE TABLE IF NOT EXISTS organizations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '组织名称（全局唯一）',
    description TEXT NULL COMMENT '描述',
    sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_organizations_name (name),
    INDEX idx_organizations_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户所属组织（扁平）';

ALTER TABLE users
ADD COLUMN organization_id INT NULL COMMENT '所属组织ID' AFTER english_name;

ALTER TABLE users
ADD CONSTRAINT fk_users_organization
FOREIGN KEY (organization_id) REFERENCES organizations(id)
ON DELETE SET NULL;

CREATE INDEX idx_users_organization_id ON users(organization_id);