-- 权限管理系统数据库迁移
-- 版本: V21
-- 描述: 创建权限管理核心表 (按PRD V10.2.1定义)
-- 创建时间: 2026-03-04

-- 1. 角色表（sys_role）
CREATE TABLE sys_role (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    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 COMMENT '数据权限：ALL/DEPARTMENT/OWN',
    description VARCHAR(500) COMMENT '角色描述',
    status VARCHAR(20) DEFAULT 'ENABLED' COMMENT '状态：ENABLED/DISABLED',
    created_by BIGINT COMMENT '创建人',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

-- 2. 权限表（sys_permission）
CREATE TABLE sys_permission (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    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 '操作代码',
    data_scope VARCHAR(20) COMMENT '数据范围：ALL/DEPARTMENT/OWN',
    description VARCHAR(500) COMMENT '权限描述',
    status VARCHAR(20) DEFAULT 'ENABLED' COMMENT '状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

-- 3. 用户角色关联表（sys_user_role）
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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_role (user_id, role_id, department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

-- 4. 角色权限关联表（sys_role_permission）
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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_role_permission (role_id, permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

-- 5. 部门表（sys_department）
CREATE TABLE sys_department (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

-- 6. 审批流程配置表（sys_approval_flow）
CREATE TABLE sys_approval_flow (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    flow_code VARCHAR(50) NOT NULL UNIQUE COMMENT '流程代码',
    flow_name VARCHAR(100) NOT NULL COMMENT '流程名称',
    trigger_event VARCHAR(100) NOT NULL COMMENT '触发事件',
    conditions JSON COMMENT '触发条件',
    nodes JSON NOT NULL COMMENT '审批节点配置',
    timeout_hours INT DEFAULT 24 COMMENT '超时时间（小时）',
    timeout_action VARCHAR(20) DEFAULT 'ESCALATE' COMMENT '超时动作',
    status VARCHAR(20) DEFAULT 'ENABLED' COMMENT '状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审批流程配置表';

-- 7. 审批记录表（sys_approval_record）
CREATE TABLE sys_approval_record (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    flow_id BIGINT NOT NULL COMMENT '流程配置ID',
    biz_type VARCHAR(50) NOT NULL COMMENT '业务类型',
    biz_id BIGINT NOT NULL COMMENT '业务ID',
    current_node INT NOT NULL COMMENT '当前节点',
    applicant_id BIGINT NOT NULL COMMENT '申请人',
    status VARCHAR(20) DEFAULT 'PENDING' COMMENT '状态',
    current_approver_id BIGINT COMMENT '当前审批人',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审批记录表';

-- 8. 审批历史表（sys_approval_history）
CREATE TABLE sys_approval_history (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    record_id BIGINT NOT NULL COMMENT '审批记录ID',
    node_index INT NOT NULL COMMENT '节点索引',
    approver_id BIGINT NOT NULL COMMENT '审批人',
    action VARCHAR(20) NOT NULL COMMENT '操作：APPROVE/REJECT/TRANSFER',
    comment TEXT COMMENT '审批意见',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审批历史表';

-- 9. 权限审计日志表（sys_permission_audit）
CREATE TABLE sys_permission_audit (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    operator_id BIGINT NOT NULL COMMENT '操作人ID',
    operation_type VARCHAR(50) NOT NULL COMMENT '操作类型：ASSIGN/REVOKE/BYPASS',
    target_type VARCHAR(20) NOT NULL COMMENT '目标类型：USER/ROLE/PERMISSION',
    target_id BIGINT NOT NULL COMMENT '目标ID',
    change_detail JSON COMMENT '变更详情',
    ip_address VARCHAR(50) COMMENT 'IP地址',
    reason VARCHAR(500) COMMENT '变更原因',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限审计日志表';

-- 10. 数据敏感字段配置表（sys_sensitive_field）
CREATE TABLE sys_sensitive_field (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    table_name VARCHAR(50) NOT NULL COMMENT '表名',
    field_name VARCHAR(50) NOT NULL COMMENT '字段名',
    field_type VARCHAR(20) NOT NULL COMMENT '字段类型：PHONE/ID_CARD/BANK_CARD/EMAIL',
    mask_type VARCHAR(20) NOT NULL COMMENT '脱敏方式：MASK/HIDE/HASH',
    mask_pattern VARCHAR(50) COMMENT '脱敏规则',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据敏感字段配置表';

-- 创建索引
CREATE INDEX idx_role_code ON sys_role(role_code);
CREATE INDEX idx_role_level ON sys_role(role_level);
CREATE INDEX idx_role_status ON sys_role(status);

CREATE INDEX idx_permission_code ON sys_permission(permission_code);
CREATE INDEX idx_permission_module ON sys_permission(module_code);
CREATE INDEX idx_permission_status ON sys_permission(status);

CREATE INDEX idx_user_role_user ON sys_user_role(user_id);
CREATE INDEX idx_user_role_role ON sys_user_role(role_id);

CREATE INDEX idx_role_permission_role ON sys_role_permission(role_id);
CREATE INDEX idx_role_permission_perm ON sys_role_permission(permission_id);

CREATE INDEX idx_department_parent ON sys_department(parent_id);
CREATE INDEX idx_department_code ON sys_department(dept_code);

CREATE INDEX idx_approval_flow_code ON sys_approval_flow(flow_code);
CREATE INDEX idx_approval_flow_trigger ON sys_approval_flow(trigger_event);

CREATE INDEX idx_approval_record_flow ON sys_approval_record(flow_id);
CREATE INDEX idx_approval_record_biz ON sys_approval_record(biz_type, biz_id);
CREATE INDEX idx_approval_record_applicant ON sys_approval_record(applicant_id);
CREATE INDEX idx_approval_record_status ON sys_approval_record(status);

CREATE INDEX idx_approval_history_record ON sys_approval_history(record_id);
CREATE INDEX idx_approval_history_approver ON sys_approval_history(approver_id);

CREATE INDEX idx_permission_audit_operator ON sys_permission_audit(operator_id);
CREATE INDEX idx_permission_audit_target ON sys_permission_audit(target_type, target_id);
CREATE INDEX idx_permission_audit_created ON sys_permission_audit(created_at);
