-- 审计与权限审计数据库迁移
-- 版本: V23
-- 描述: 创建审计日志和权限审计表
-- 创建时间: 2026-03-04

-- 1. 审计日志表
CREATE TABLE sys_audit_log (
    id                  BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
    trace_id            VARCHAR(64)     COMMENT '链路追踪ID',
    user_id             BIGINT          COMMENT '用户ID',
    user_name           VARCHAR(100)    COMMENT '用户名',
    user_ip            VARCHAR(50)     COMMENT '用户IP',
    user_agent          VARCHAR(500)   COMMENT '用户代理',
    module_code         VARCHAR(50)     NOT NULL COMMENT '模块代码',
    module_name         VARCHAR(100)    COMMENT '模块名称',
    operation_type      VARCHAR(50)     NOT NULL COMMENT '操作类型',
    operation_name      VARCHAR(100)    COMMENT '操作名称',
    business_type       VARCHAR(50)     COMMENT '业务类型',
    business_id         VARCHAR(100)    COMMENT '业务ID',
    business_no         VARCHAR(100)    COMMENT '业务编号',
    request_method      VARCHAR(10)     COMMENT '请求方法',
    request_url         VARCHAR(500)    COMMENT '请求URL',
    request_params      TEXT            COMMENT '请求参数',
    request_body        TEXT            COMMENT '请求体',
    response_code       VARCHAR(20)     COMMENT '响应码',
    response_message    VARCHAR(500)    COMMENT '响应消息',
    response_time_ms    INT             COMMENT '响应耗时(毫秒)',
    error_stack         TEXT            COMMENT '错误堆栈',
    affected_records    INT             DEFAULT 0 COMMENT '影响记录数',
    data_before         JSON            COMMENT '修改前数据',
    data_after          JSON            COMMENT '修改后数据',
    is_sensitive        TINYINT(1)      DEFAULT 0 COMMENT '是否敏感操作',
    sensitivity_level   VARCHAR(20)     COMMENT '敏感级别：NORMAL/WARNING/DANGER',
    resource_type       VARCHAR(50)     COMMENT '资源类型',
    resource_id         VARCHAR(100)    COMMENT '资源ID',
    location            VARCHAR(200)    COMMENT '地理位置',
    device_info         VARCHAR(200)    COMMENT '设备信息',
    session_id          VARCHAR(100)    COMMENT '会话ID',
    created_at         DATETIME        DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_trace_id (trace_id),
    INDEX idx_user_id (user_id),
    INDEX idx_module_operation (module_code, operation_type),
    INDEX idx_business (business_type, business_id),
    INDEX idx_created_at (created_at),
    INDEX idx_user_ip (user_ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审计日志表';

-- 2. 权限审计表
CREATE TABLE sys_permission_audit (
    id                  BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '审计ID',
    audit_type          VARCHAR(20)     NOT NULL COMMENT '审计类型：GRANT/REVOKE/TRANSFER/EXPIRE',
    target_type         VARCHAR(20)     NOT NULL COMMENT '目标类型：USER/ROLE/DEPARTMENT',
    target_id           BIGINT          NOT NULL COMMENT '目标ID',
    target_name         VARCHAR(100)    COMMENT '目标名称',
    target_code         VARCHAR(100)    COMMENT '目标代码',
    permission_type     VARCHAR(20)     NOT NULL COMMENT '权限类型：ROLE/DIRECT/MENU/DATA',
    permission_id       BIGINT          COMMENT '权限ID',
    permission_code     VARCHAR(100)    COMMENT '权限代码',
    permission_name     VARCHAR(100)    COMMENT '权限名称',
    permission_scope    VARCHAR(20)     COMMENT '权限范围：ALL/DEPARTMENT/OWN',
    department_id       BIGINT          COMMENT '部门ID',
    department_name     VARCHAR(100)    COMMENT '部门名称',
    expire_time         DATETIME        COMMENT '过期时间',
    reason              VARCHAR(500)    COMMENT '授权原因',
    source_type         VARCHAR(20)     COMMENT '来源类型：MANUAL/APPROVAL/AUTO',
    source_id           BIGINT          COMMENT '来源ID',
    source_no           VARCHAR(100)    COMMENT '来源单据编号',
    operator_id         BIGINT          COMMENT '操作人ID',
    operator_name       VARCHAR(100)    COMMENT '操作人姓名',
    operator_ip         VARCHAR(50)     COMMENT '操作人IP',
    approval_instance_id BIGINT        COMMENT '审批实例ID',
    status              VARCHAR(20)     DEFAULT 'APPROVED' COMMENT '状态：PENDING/APPROVED/REJECTED/CANCELLED',
    effective_time      DATETIME        COMMENT '生效时间',
    effect_status       VARCHAR(20)     COMMENT '生效状态：EFFECTIVE/EXPIRED/REVOKED',
    remark              VARCHAR(500)    COMMENT '备注',
    created_at         DATETIME        DEFAULT CURRENT_TIMESTAMP,
    updated_at         DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_audit_type (audit_type),
    INDEX idx_target (target_type, target_id),
    INDEX idx_permission (permission_type, permission_id),
    INDEX idx_source (source_type, source_id),
    INDEX idx_operator (operator_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限审计表';

-- 3. 用户权限快照表（定期生成）
CREATE TABLE sys_user_permission_snapshot (
    id                  BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT '快照ID',
    user_id             BIGINT          NOT NULL COMMENT '用户ID',
    snapshot_date       DATE            NOT NULL COMMENT '快照日期',
    role_ids            VARCHAR(500)    COMMENT '角色ID列表',
    role_names          VARCHAR(1000)   COMMENT '角色名称列表',
    permission_ids      TEXT            COMMENT '权限ID列表(JSON数组)',
    permission_codes    TEXT            COMMENT '权限代码列表(JSON数组)',
    permission_count    INT             DEFAULT 0 COMMENT '权限数量',
    data_scope          VARCHAR(20)    COMMENT '数据权限范围',
    department_id       BIGINT          COMMENT '主部门ID',
    department_name     VARCHAR(100)    COMMENT '主部门名称',
    all_department_ids  VARCHAR(500)    COMMENT '所有部门ID',
    is_active           TINYINT(1)      DEFAULT 1 COMMENT '是否有效',
    created_at         DATETIME        DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_date (user_id, snapshot_date),
    INDEX idx_user_id (user_id),
    INDEX idx_snapshot_date (snapshot_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户权限快照表';

-- 4. 部门层级关系表（支持树形查询）
CREATE TABLE sys_department_relation (
    id                  BIGINT          PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    ancestor_id         BIGINT          NOT NULL COMMENT '祖先部门ID',
    descendant_id       BIGINT          NOT NULL COMMENT '后代部门ID',
    depth               INT             NOT NULL DEFAULT 0 COMMENT '层级深度',
    path                VARCHAR(1000)   NOT NULL COMMENT '路径',
    is_direct           TINYINT(1)      DEFAULT 0 COMMENT '是否直接上级',
    created_at         DATETIME        DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_descendant (descendant_id, ancestor_id),
    INDEX idx_ancestor (ancestor_id),
    INDEX idx_descendant (descendant_id),
    INDEX idx_path (path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门层级关系表';
