-- 权限管理系统数据库迁移
-- 版本: V21
-- 描述: 创建权限管理核心表
-- 创建时间: 2026-03-04

-- 1. 角色表
CREATE TABLE sys_role (
    id              BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '角色ID',
    role_code       VARCHAR(50)     NOT NULL UNIQUE COMMENT '角色代码',
    role_name       VARCHAR(100)    NOT NULL COMMENT '角色名称',
    role_level      VARCHAR(20)     NOT NULL COMMENT '角色层级：SYSTEM/MANAGER/EXECUTOR/AUDIT',
    data_scope      VARCHAR(20)     NOT NULL DEFAULT 'DEPARTMENT' COMMENT '数据权限：ALL/DEPARTMENT/OWN',
    description     VARCHAR(500)    COMMENT '角色描述',
    is_core         TINYINT(1)     DEFAULT 0 COMMENT '是否核心角色：0-扩展/1-核心',
    status          VARCHAR(20)     DEFAULT 'ENABLED' COMMENT '状态：ENABLED/DISABLED',
    created_by     BIGINT          COMMENT '创建人',
    created_at     DATETIME        DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted        TINYINT(1)     DEFAULT 0 COMMENT '删除标志：0-未删除/1-已删除',
    INDEX idx_role_code (role_code),
    INDEX idx_status (status),
    INDEX idx_role_level (role_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

-- 2. 权限表
CREATE TABLE sys_permission (
    id                  BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '权限ID',
    permission_code     VARCHAR(100)    NOT NULL UNIQUE COMMENT '权限代码',
    permission_name     VARCHAR(100)    NOT NULL COMMENT '权限名称',
    module_code         VARCHAR(50)     NOT NULL COMMENT '模块代码',
    resource_code       VARCHAR(50)     COMMENT '资源代码',
    operation_code      VARCHAR(50)     COMMENT '操作代码：view/create/edit/delete/approve/export/execute',
    data_scope          VARCHAR(20)     COMMENT '数据范围：ALL/DEPARTMENT/OWN',
    description         VARCHAR(500)    COMMENT '权限描述',
    sort_order         INT             DEFAULT 0 COMMENT '排序',
    status              VARCHAR(20)     DEFAULT 'ENABLED' COMMENT '状态',
    created_at         DATETIME        DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_permission_code (permission_code),
    INDEX idx_module_code (module_code),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

-- 3. 角色权限关联表
CREATE TABLE sys_role_permission (
    id              BIGINT      PRIMARY KEY AUTO_INCREMENT,
    role_id         BIGINT      NOT NULL COMMENT '角色ID',
    permission_id   BIGINT      NOT NULL COMMENT '权限ID',
    created_at     DATETIME    DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_role_permission (role_id, permission_id),
    INDEX idx_role_id (role_id),
    INDEX idx_permission_id (permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

-- 4. 用户角色关联表
CREATE TABLE sys_user_role (
    id              BIGINT      PRIMARY KEY AUTO_INCREMENT,
    user_id         BIGINT      NOT NULL COMMENT '用户ID',
    role_id         BIGINT      NOT NULL COMMENT '角色ID',
    department_id   BIGINT      COMMENT '部门ID',
    created_by     BIGINT      COMMENT '分配人',
    created_at     DATETIME    DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_role_dept (user_id, role_id, department_id),
    INDEX idx_user_id (user_id),
    INDEX idx_role_id (role_id),
    INDEX idx_department_id (department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

-- 5. 部门表
CREATE TABLE sys_department (
    id              BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
    dept_name       VARCHAR(100)    NOT NULL COMMENT '部门名称',
    parent_id       BIGINT          COMMENT '父部门ID',
    dept_code       VARCHAR(50)     COMMENT '部门编码',
    leader_id       BIGINT          COMMENT '部门负责人',
    sort_order      INT             DEFAULT 0 COMMENT '排序',
    status          VARCHAR(20)     DEFAULT 'ENABLED' COMMENT '状态',
    created_at     DATETIME        DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_parent_id (parent_id),
    INDEX idx_dept_code (dept_code),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

-- 6. 用户直接权限表（用于临时权限）
CREATE TABLE sys_user_permission (
    id              BIGINT      PRIMARY KEY AUTO_INCREMENT,
    user_id         BIGINT      NOT NULL COMMENT '用户ID',
    permission_id   BIGINT      NOT NULL COMMENT '权限ID',
    expire_time     DATETIME    COMMENT '过期时间',
    created_by     BIGINT      COMMENT '分配人',
    created_at     DATETIME    DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_permission (user_id, permission_id),
    INDEX idx_user_id (user_id),
    INDEX idx_permission_id (permission_id),
    INDEX idx_expire_time (expire_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户直接权限表';
