-- =============================================
-- 审计日志服务数据库初始化脚本
-- =============================================

CREATE DATABASE IF NOT EXISTS erp_audit DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE erp_audit;

-- 审计日志主表
CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  `tenant_id` BIGINT UNSIGNED DEFAULT 0 COMMENT '租户ID',
  `user_id` BIGINT UNSIGNED COMMENT '用户ID',
  `username` VARCHAR(64) NOT NULL COMMENT '用户名',
  `real_name` VARCHAR(128) COMMENT '真实姓名',
  `module` VARCHAR(64) NOT NULL COMMENT '操作模块',
  `operation_type` VARCHAR(32) NOT NULL COMMENT '操作类型：CREATE/UPDATE/DELETE/READ/LOGIN/LOGOUT/EXPORT/APPROVAL',
  `description` VARCHAR(512) COMMENT '操作描述',
  `request_method` VARCHAR(16) COMMENT 'HTTP请求方法',
  `request_url` VARCHAR(512) COMMENT '请求URL',
  `request_params` TEXT COMMENT '请求参数(JSON)',
  `response_status` INT DEFAULT 200 COMMENT '响应状态码',
  `response_body` TEXT COMMENT '响应结果',
  `ip_address` VARCHAR(64) COMMENT 'IP地址',
  `user_agent` VARCHAR(512) COMMENT '用户终端',
  `biz_id` VARCHAR(64) COMMENT '关联业务ID',
  `biz_type` VARCHAR(64) COMMENT '关联业务类型',
  `duration` BIGINT COMMENT '执行时长(ms)',
  `error_msg` TEXT COMMENT '错误信息',
  `sensitive` TINYINT(1) DEFAULT 0 COMMENT '是否敏感操作',
  `audit_status` VARCHAR(16) DEFAULT 'SUCCESS' COMMENT '审计状态：SUCCESS/FAIL/PENDING',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` VARCHAR(512) COMMENT '备注',
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_username` (`username`),
  INDEX `idx_module` (`module`),
  INDEX `idx_operation_type` (`operation_type`),
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_audit_status` (`audit_status`),
  INDEX `idx_sensitive` (`sensitive`),
  INDEX `idx_biz_type_id` (`biz_type`, `biz_id`),
  INDEX `idx_ip_address` (`ip_address`),
  INDEX `idx_request_url` (`request_url`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='审计日志表';

-- 敏感操作规则配置表
CREATE TABLE IF NOT EXISTS `sensitive_operation_rule` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  `rule_name` VARCHAR(128) NOT NULL COMMENT '规则名称',
  `rule_code` VARCHAR(64) NOT NULL UNIQUE COMMENT '规则编码',
  `module` VARCHAR(64) COMMENT '模块',
  `operation_type` VARCHAR(32) COMMENT '操作类型',
  `url_pattern` VARCHAR(256) COMMENT 'URL匹配模式',
  `param_pattern` TEXT COMMENT '参数匹配规则(JSON)',
  `alert_level` VARCHAR(16) DEFAULT 'MEDIUM' COMMENT '告警级别：LOW/MEDIUM/HIGH/CRITICAL',
  `enabled` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  `notify_channels` VARCHAR(128) DEFAULT 'ROCKETMQ' COMMENT '通知渠道：SMS/EMAIL/WEBHOOK/ROCKETMQ',
  `notify_users` VARCHAR(256) COMMENT '通知用户',
  `trigger_threshold` INT DEFAULT 1 COMMENT '连续触发次数阈值',
  `time_window_minutes` INT DEFAULT 60 COMMENT '时间窗口(分钟)',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` VARCHAR(512) COMMENT '备注',
  INDEX `idx_rule_code` (`rule_code`),
  INDEX `idx_enabled` (`enabled`),
  INDEX `idx_alert_level` (`alert_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='敏感操作规则表';

-- 敏感操作告警记录表
CREATE TABLE IF NOT EXISTS `sensitive_alert_record` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  `audit_log_id` BIGINT UNSIGNED COMMENT '关联审计日志ID',
  `rule_id` BIGINT UNSIGNED COMMENT '关联规则ID',
  `rule_code` VARCHAR(64) COMMENT '规则编码',
  `alert_level` VARCHAR(16) NOT NULL COMMENT '告警级别',
  `alert_content` TEXT COMMENT '告警内容',
  `alert_status` VARCHAR(16) DEFAULT 'PENDING' COMMENT '告警状态：PENDING/SENT/READ/ACKED',
  `notify_channel` VARCHAR(64) COMMENT '通知渠道',
  `notify_user` VARCHAR(256) COMMENT '通知用户',
  `notify_time` DATETIME COMMENT '通知时间',
  `handle_time` DATETIME COMMENT '处理时间',
  `handler` VARCHAR(64) COMMENT '处理人',
  `handle_result` TEXT COMMENT '处理结果',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  INDEX `idx_audit_log_id` (`audit_log_id`),
  INDEX `idx_rule_id` (`rule_id`),
  INDEX `idx_alert_status` (`alert_status`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='敏感操作告警记录表';

-- 合规审计报告表
CREATE TABLE IF NOT EXISTS `compliance_report` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  `report_no` VARCHAR(64) NOT NULL UNIQUE COMMENT '报告编号',
  `report_type` VARCHAR(32) NOT NULL COMMENT '报告类型：DAILY/WEEKLY/MONTHLY/QUARTERLY/ANNUAL/CUSTOM',
  `report_name` VARCHAR(256) NOT NULL COMMENT '报告名称',
  `tenant_id` BIGINT UNSIGNED COMMENT '租户ID',
  `start_time` DATETIME NOT NULL COMMENT '审计开始时间',
  `end_time` DATETIME NOT NULL COMMENT '审计结束时间',
  `audit_scope` TEXT COMMENT '审计范围(JSON)',
  `audit_conclusion` VARCHAR(16) DEFAULT 'PASS' COMMENT '审计结论：PASS/FAIL/WARNING',
  `issue_count` INT DEFAULT 0 COMMENT '问题数量',
  `high_risk_count` INT DEFAULT 0 COMMENT '高风险问题数',
  `medium_risk_count` INT DEFAULT 0 COMMENT '中风险问题数',
  `low_risk_count` INT DEFAULT 0 COMMENT '低风险问题数',
  `report_status` VARCHAR(16) DEFAULT 'DRAFT' COMMENT '报告状态：DRAFT/GENERATED/DISTRIBUTED/ARCHIVED',
  `file_path` VARCHAR(512) COMMENT '报告文件路径',
  `content_json` TEXT COMMENT '报告内容(JSON)',
  `generated_by` VARCHAR(64) COMMENT '生成人',
  `reviewed_by` VARCHAR(64) COMMENT '审核人',
  `reviewed_at` DATETIME COMMENT '审核时间',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX `idx_report_no` (`report_no`),
  INDEX `idx_report_type` (`report_type`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_report_status` (`report_status`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='合规审计报告表';

-- =============================================
-- 初始化敏感操作规则数据
-- =============================================
INSERT INTO `sensitive_operation_rule` (`rule_name`, `rule_code`, `module`, `operation_type`, `url_pattern`, `alert_level`, `enabled`, `notify_channels`, `notify_users`, `trigger_threshold`, `time_window_minutes`, `remark`) VALUES
('数据删除告警', 'DATA_DELETE', '通用', 'DELETE', NULL, 'HIGH', 1, 'ROCKETMQ,WEBHOOK', 'admin', 1, 60, '检测所有删除操作'),
('批量导出告警', 'BATCH_EXPORT', '通用', 'EXPORT', '/export', 'MEDIUM', 1, 'ROCKETMQ', 'admin', 3, 30, '批量导出数据超过1000条时告警'),
('权限变更告警', 'PERMISSION_CHANGE', '权限', 'UPDATE', '/permission', 'CRITICAL', 1, 'ROCKETMQ,EMAIL', 'admin', 1, 60, '权限变更操作'),
('用户密码修改', 'PASSWORD_CHANGE', '用户', 'UPDATE', '/password', 'HIGH', 1, 'ROCKETMQ', 'admin', 1, 60, '用户密码修改'),
('系统配置修改', 'SYSTEM_CONFIG', '系统', 'UPDATE', '/system/config', 'CRITICAL', 1, 'ROCKETMQ,EMAIL,WEBHOOK', 'admin', 1, 60, '系统配置修改'),
('财务数据操作', 'FINANCE_OPERATION', '财务', NULL, '/finance', 'CRITICAL', 1, 'ROCKETMQ,EMAIL', 'admin,finance_manager', 1, 60, '财务相关操作');
