-- 数字债权服务系统数据库表结构
-- 创建数据库
CREATE DATABASE IF NOT EXISTS `anxin_digital_credit` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

USE `anxin_digital_credit`;

-- ===========================
-- 核心业务表 (ruoyi-credit模块)
-- ===========================

-- 1. 服务合同表
DROP TABLE IF EXISTS `dc_service_contract`;
CREATE TABLE `dc_service_contract` (
  `contract_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '合同ID',
  `contract_number` varchar(100) NOT NULL COMMENT '合同编号',
  `security_company_id` bigint(20) NOT NULL COMMENT '安保公司ID',
  `client_company_id` bigint(20) NOT NULL COMMENT '甲方单位ID',
  `service_content` text COMMENT '服务内容(加密)',
  `contract_amount` varchar(500) COMMENT '合同金额(加密)',
  `start_date` datetime NOT NULL COMMENT '服务开始日期',
  `end_date` datetime NOT NULL COMMENT '服务结束日期',
  `payment_due_date` datetime NOT NULL COMMENT '付款到期日',
  `contract_status` varchar(50) NOT NULL DEFAULT 'DRAFT' COMMENT '合同状态',
  `blockchain_hash` varchar(255) COMMENT '区块链哈希',
  `data_hash` varchar(255) COMMENT '敏感数据哈希',
  `encryption_key_id` varchar(100) COMMENT '加密密钥ID',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`contract_id`),
  UNIQUE KEY `uk_contract_number` (`contract_number`),
  KEY `idx_security_company` (`security_company_id`),
  KEY `idx_client_company` (`client_company_id`),
  KEY `idx_contract_status` (`contract_status`),
  KEY `idx_payment_due_date` (`payment_due_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='服务合同表';

-- 2. 数字债权凭证表
DROP TABLE IF EXISTS `dc_digital_credit`;
CREATE TABLE `dc_digital_credit` (
  `credit_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '债权ID',
  `credit_number` varchar(100) NOT NULL COMMENT '债权编号',
  `contract_id` bigint(20) NOT NULL COMMENT '关联合同ID',
  `credit_amount` varchar(500) COMMENT '债权金额(加密)',
  `issue_date` datetime NOT NULL COMMENT '发行日期',
  `maturity_date` datetime NOT NULL COMMENT '到期日期',
  `credit_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '债权状态',
  `blockchain_hash` varchar(255) COMMENT '区块链哈希',
  `digital_signature` text COMMENT '数字签名(加密)',
  `encryption_key_id` varchar(100) COMMENT '加密密钥ID',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`credit_id`),
  UNIQUE KEY `uk_credit_number` (`credit_number`),
  KEY `idx_contract_id` (`contract_id`),
  KEY `idx_credit_status` (`credit_status`),
  KEY `idx_maturity_date` (`maturity_date`),
  CONSTRAINT `fk_credit_contract` FOREIGN KEY (`contract_id`) REFERENCES `dc_service_contract` (`contract_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='数字债权凭证表';

-- 3. 融资申请表
DROP TABLE IF EXISTS `dc_financing_application`;
CREATE TABLE `dc_financing_application` (
  `application_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '申请ID',
  `application_number` varchar(100) NOT NULL COMMENT '申请编号',
  `credit_id` bigint(20) NOT NULL COMMENT '债权ID',
  `financial_institution_id` bigint(20) NOT NULL COMMENT '金融机构ID',
  `request_amount` varchar(500) COMMENT '申请金额(加密)',
  `approved_amount` varchar(500) COMMENT '批准金额(加密)',
  `interest_rate` varchar(200) COMMENT '利率(加密)',
  `application_status` varchar(50) NOT NULL DEFAULT 'PENDING' COMMENT '申请状态',
  `application_date` datetime NOT NULL COMMENT '申请日期',
  `approval_date` datetime COMMENT '审批日期',
  `approval_comments` text COMMENT '审批意见(加密)',
  `encryption_key_id` varchar(100) COMMENT '加密密钥ID',
  `access_log` varchar(255) COMMENT '访问日志ID',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`application_id`),
  UNIQUE KEY `uk_application_number` (`application_number`),
  KEY `idx_credit_id` (`credit_id`),
  KEY `idx_financial_institution` (`financial_institution_id`),
  KEY `idx_application_status` (`application_status`),
  CONSTRAINT `fk_financing_credit` FOREIGN KEY (`credit_id`) REFERENCES `dc_digital_credit` (`credit_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='融资申请表';

-- 4. 公司信息表
DROP TABLE IF EXISTS `dc_company_info`;
CREATE TABLE `dc_company_info` (
  `company_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '公司ID',
  `company_name` varchar(200) NOT NULL COMMENT '公司名称',
  `company_type` varchar(50) NOT NULL COMMENT '公司类型',
  `registration_number` varchar(100) COMMENT '注册号',
  `legal_representative` varchar(100) COMMENT '法定代表人',
  `contact_person` varchar(100) COMMENT '联系人',
  `contact_phone` varchar(50) COMMENT '联系电话',
  `contact_email` varchar(100) COMMENT '联系邮箱',
  `address` varchar(500) COMMENT '地址',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '状态（0正常 1停用）',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`company_id`),
  KEY `idx_company_name` (`company_name`),
  KEY `idx_company_type` (`company_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='公司信息表';

-- 5. 合同附件表
DROP TABLE IF EXISTS `dc_contract_attachment`;
CREATE TABLE `dc_contract_attachment` (
  `attachment_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '附件ID',
  `contract_id` bigint(20) NOT NULL COMMENT '合同ID',
  `file_name` varchar(200) NOT NULL COMMENT '文件名',
  `file_path` varchar(500) NOT NULL COMMENT '文件路径',
  `file_size` bigint(20) NOT NULL COMMENT '文件大小',
  `file_type` varchar(50) NOT NULL COMMENT '文件类型',
  `file_hash` varchar(255) COMMENT '文件哈希',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`attachment_id`),
  KEY `idx_contract_id` (`contract_id`),
  CONSTRAINT `fk_attachment_contract` FOREIGN KEY (`contract_id`) REFERENCES `dc_service_contract` (`contract_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='合同附件表';

-- ===========================
-- 支付结算表 (ruoyi-payment模块)
-- ===========================

-- 6. 资金监管账户表
DROP TABLE IF EXISTS `dc_escrow_account`;
CREATE TABLE `dc_escrow_account` (
  `account_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '账户ID',
  `account_number` varchar(100) NOT NULL COMMENT '账户号码',
  `application_id` bigint(20) NOT NULL COMMENT '关联融资申请ID',
  `total_amount` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT '总金额',
  `available_amount` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT '可用金额',
  `frozen_amount` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT '冻结金额',
  `account_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '账户状态',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `uk_account_number` (`account_number`),
  KEY `idx_application_id` (`application_id`),
  KEY `idx_account_status` (`account_status`),
  CONSTRAINT `fk_escrow_application` FOREIGN KEY (`application_id`) REFERENCES `dc_financing_application` (`application_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='资金监管账户表';

-- 7. 资金流转记录表
DROP TABLE IF EXISTS `dc_fund_transfer`;
CREATE TABLE `dc_fund_transfer` (
  `transfer_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '流转ID',
  `transfer_number` varchar(100) NOT NULL COMMENT '流转编号',
  `from_account_id` bigint(20) COMMENT '转出账户ID',
  `to_account_id` bigint(20) COMMENT '转入账户ID',
  `transfer_amount` decimal(15,2) NOT NULL COMMENT '转账金额',
  `transfer_type` varchar(50) NOT NULL COMMENT '转账类型',
  `transfer_status` varchar(50) NOT NULL DEFAULT 'PENDING' COMMENT '转账状态',
  `description` varchar(500) COMMENT '描述',
  `transfer_time` datetime NOT NULL COMMENT '转账时间',
  `blockchain_tx_hash` varchar(255) COMMENT '区块链交易哈希',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`transfer_id`),
  UNIQUE KEY `uk_transfer_number` (`transfer_number`),
  KEY `idx_from_account` (`from_account_id`),
  KEY `idx_to_account` (`to_account_id`),
  KEY `idx_transfer_status` (`transfer_status`),
  KEY `idx_transfer_time` (`transfer_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='资金流转记录表';

-- 8. 支付记录表
DROP TABLE IF EXISTS `dc_payment_record`;
CREATE TABLE `dc_payment_record` (
  `payment_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '支付ID',
  `payment_number` varchar(100) NOT NULL COMMENT '支付编号',
  `contract_id` bigint(20) NOT NULL COMMENT '合同ID',
  `payer_id` bigint(20) NOT NULL COMMENT '付款方ID',
  `payee_id` bigint(20) NOT NULL COMMENT '收款方ID',
  `payment_amount` decimal(15,2) NOT NULL COMMENT '支付金额',
  `payment_method` varchar(50) NOT NULL COMMENT '支付方式',
  `payment_status` varchar(50) NOT NULL DEFAULT 'PENDING' COMMENT '支付状态',
  `payment_time` datetime COMMENT '支付时间',
  `confirmation_time` datetime COMMENT '确认时间',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`payment_id`),
  UNIQUE KEY `uk_payment_number` (`payment_number`),
  KEY `idx_contract_id` (`contract_id`),
  KEY `idx_payment_status` (`payment_status`),
  CONSTRAINT `fk_payment_contract` FOREIGN KEY (`contract_id`) REFERENCES `dc_service_contract` (`contract_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='支付记录表';

-- 9. 账户余额表
DROP TABLE IF EXISTS `dc_account_balance`;
CREATE TABLE `dc_account_balance` (
  `balance_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '余额ID',
  `account_id` bigint(20) NOT NULL COMMENT '账户ID',
  `balance_date` date NOT NULL COMMENT '余额日期',
  `opening_balance` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT '期初余额',
  `closing_balance` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT '期末余额',
  `total_income` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT '总收入',
  `total_expense` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT '总支出',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`balance_id`),
  UNIQUE KEY `uk_account_date` (`account_id`, `balance_date`),
  KEY `idx_balance_date` (`balance_date`),
  CONSTRAINT `fk_balance_account` FOREIGN KEY (`account_id`) REFERENCES `dc_escrow_account` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='账户余额表';

-- 10. 交易日志表
DROP TABLE IF EXISTS `dc_transaction_log`;
CREATE TABLE `dc_transaction_log` (
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `transaction_id` varchar(100) NOT NULL COMMENT '交易ID',
  `transaction_type` varchar(50) NOT NULL COMMENT '交易类型',
  `account_id` bigint(20) COMMENT '账户ID',
  `amount` decimal(15,2) NOT NULL COMMENT '金额',
  `balance_before` decimal(15,2) NOT NULL COMMENT '交易前余额',
  `balance_after` decimal(15,2) NOT NULL COMMENT '交易后余额',
  `transaction_time` datetime NOT NULL COMMENT '交易时间',
  `description` varchar(500) COMMENT '描述',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`log_id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_account_id` (`account_id`),
  KEY `idx_transaction_time` (`transaction_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='交易日志表';
-- ===========================
-- 区块链集成表 (ruoyi-blockchain模块)
-- ===========================

-- 11. 区块链交易表
DROP TABLE IF EXISTS `dc_blockchain_transaction`;
CREATE TABLE `dc_blockchain_transaction` (
  `tx_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '交易ID',
  `tx_hash` varchar(255) NOT NULL COMMENT '交易哈希',
  `block_number` bigint(20) COMMENT '区块号',
  `block_hash` varchar(255) COMMENT '区块哈希',
  `from_address` varchar(255) COMMENT '发送地址',
  `to_address` varchar(255) COMMENT '接收地址',
  `contract_address` varchar(255) COMMENT '合约地址',
  `gas_used` bigint(20) COMMENT '消耗Gas',
  `gas_price` bigint(20) COMMENT 'Gas价格',
  `tx_status` varchar(50) NOT NULL DEFAULT 'PENDING' COMMENT '交易状态',
  `business_type` varchar(50) NOT NULL COMMENT '业务类型',
  `business_id` bigint(20) NOT NULL COMMENT '业务ID',
  `data_hash` varchar(255) NOT NULL COMMENT '数据哈希',
  `tx_time` datetime NOT NULL COMMENT '交易时间',
  `confirmation_time` datetime COMMENT '确认时间',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`tx_id`),
  UNIQUE KEY `uk_tx_hash` (`tx_hash`),
  KEY `idx_business` (`business_type`, `business_id`),
  KEY `idx_tx_status` (`tx_status`),
  KEY `idx_tx_time` (`tx_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='区块链交易表';

-- 12. 智能合约表
DROP TABLE IF EXISTS `dc_smart_contract`;
CREATE TABLE `dc_smart_contract` (
  `contract_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '合约ID',
  `contract_name` varchar(100) NOT NULL COMMENT '合约名称',
  `contract_address` varchar(255) NOT NULL COMMENT '合约地址',
  `contract_abi` text COMMENT '合约ABI',
  `contract_bytecode` text COMMENT '合约字节码',
  `deploy_tx_hash` varchar(255) COMMENT '部署交易哈希',
  `deploy_block_number` bigint(20) COMMENT '部署区块号',
  `contract_version` varchar(50) NOT NULL COMMENT '合约版本',
  `contract_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '合约状态',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`contract_id`),
  UNIQUE KEY `uk_contract_address` (`contract_address`),
  KEY `idx_contract_name` (`contract_name`),
  KEY `idx_contract_status` (`contract_status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='智能合约表';

-- 13. 链上事件表
DROP TABLE IF EXISTS `dc_chain_event`;
CREATE TABLE `dc_chain_event` (
  `event_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '事件ID',
  `tx_hash` varchar(255) NOT NULL COMMENT '交易哈希',
  `block_number` bigint(20) NOT NULL COMMENT '区块号',
  `log_index` int(11) NOT NULL COMMENT '日志索引',
  `contract_address` varchar(255) NOT NULL COMMENT '合约地址',
  `event_name` varchar(100) NOT NULL COMMENT '事件名称',
  `event_signature` varchar(255) NOT NULL COMMENT '事件签名',
  `event_data` text COMMENT '事件数据',
  `topics` text COMMENT '事件主题',
  `event_time` datetime NOT NULL COMMENT '事件时间',
  `processed` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已处理',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`event_id`),
  UNIQUE KEY `uk_event_unique` (`tx_hash`, `log_index`),
  KEY `idx_contract_address` (`contract_address`),
  KEY `idx_event_name` (`event_name`),
  KEY `idx_processed` (`processed`),
  KEY `idx_event_time` (`event_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='链上事件表';

-- 14. 哈希映射表
DROP TABLE IF EXISTS `dc_hash_mapping`;
CREATE TABLE `dc_hash_mapping` (
  `mapping_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '映射ID',
  `business_type` varchar(50) NOT NULL COMMENT '业务类型',
  `business_id` bigint(20) NOT NULL COMMENT '业务ID',
  `data_hash` varchar(255) NOT NULL COMMENT '数据哈希',
  `blockchain_hash` varchar(255) COMMENT '区块链哈希',
  `hash_algorithm` varchar(50) NOT NULL DEFAULT 'SHA256' COMMENT '哈希算法',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`mapping_id`),
  UNIQUE KEY `uk_business_hash` (`business_type`, `business_id`),
  KEY `idx_data_hash` (`data_hash`),
  KEY `idx_blockchain_hash` (`blockchain_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='哈希映射表';

-- ===========================
-- 安全管理表 (ruoyi-security模块)
-- ===========================

-- 15. 审计日志表
DROP TABLE IF EXISTS `dc_audit_log`;
CREATE TABLE `dc_audit_log` (
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `user_name` varchar(50) NOT NULL COMMENT '用户名',
  `operation_type` varchar(50) NOT NULL COMMENT '操作类型',
  `resource_type` varchar(50) NOT NULL COMMENT '资源类型',
  `resource_id` varchar(100) COMMENT '资源ID',
  `operation_desc` varchar(500) COMMENT '操作描述',
  `request_method` varchar(10) COMMENT '请求方法',
  `request_url` varchar(500) COMMENT '请求URL',
  `request_ip` varchar(50) COMMENT '请求IP',
  `user_agent` varchar(500) COMMENT '用户代理',
  `operation_result` varchar(50) NOT NULL COMMENT '操作结果',
  `error_message` text COMMENT '错误信息',
  `operation_time` datetime NOT NULL COMMENT '操作时间',
  `cost_time` bigint(20) COMMENT '耗时(毫秒)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`log_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_operation_type` (`operation_type`),
  KEY `idx_resource_type` (`resource_type`),
  KEY `idx_operation_time` (`operation_time`),
  KEY `idx_request_ip` (`request_ip`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='审计日志表';

-- 16. 安全事件表
DROP TABLE IF EXISTS `dc_security_event`;
CREATE TABLE `dc_security_event` (
  `event_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '事件ID',
  `event_type` varchar(50) NOT NULL COMMENT '事件类型',
  `event_level` varchar(20) NOT NULL COMMENT '事件级别',
  `event_source` varchar(100) NOT NULL COMMENT '事件源',
  `event_title` varchar(200) NOT NULL COMMENT '事件标题',
  `event_desc` text COMMENT '事件描述',
  `user_id` bigint(20) COMMENT '用户ID',
  `user_name` varchar(50) COMMENT '用户名',
  `source_ip` varchar(50) COMMENT '源IP',
  `target_resource` varchar(200) COMMENT '目标资源',
  `event_data` text COMMENT '事件数据',
  `event_status` varchar(50) NOT NULL DEFAULT 'OPEN' COMMENT '事件状态',
  `handled_by` varchar(50) COMMENT '处理人',
  `handled_time` datetime COMMENT '处理时间',
  `event_time` datetime NOT NULL COMMENT '事件时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`event_id`),
  KEY `idx_event_type` (`event_type`),
  KEY `idx_event_level` (`event_level`),
  KEY `idx_event_status` (`event_status`),
  KEY `idx_event_time` (`event_time`),
  KEY `idx_source_ip` (`source_ip`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='安全事件表';

-- 17. 加密密钥表
DROP TABLE IF EXISTS `dc_encryption_key`;
CREATE TABLE `dc_encryption_key` (
  `key_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '密钥ID',
  `key_name` varchar(100) NOT NULL COMMENT '密钥名称',
  `key_alias` varchar(100) NOT NULL COMMENT '密钥别名',
  `key_type` varchar(50) NOT NULL COMMENT '密钥类型',
  `algorithm` varchar(50) NOT NULL COMMENT '加密算法',
  `key_length` int(11) NOT NULL COMMENT '密钥长度',
  `key_data` text COMMENT '密钥数据(加密存储)',
  `key_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '密钥状态',
  `expire_time` datetime COMMENT '过期时间',
  `rotation_time` datetime COMMENT '轮换时间',
  `usage_count` bigint(20) NOT NULL DEFAULT 0 COMMENT '使用次数',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`key_id`),
  UNIQUE KEY `uk_key_alias` (`key_alias`),
  KEY `idx_key_type` (`key_type`),
  KEY `idx_key_status` (`key_status`),
  KEY `idx_expire_time` (`expire_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='加密密钥表';

-- 18. 多因素认证令牌表
DROP TABLE IF EXISTS `dc_mfa_token`;
CREATE TABLE `dc_mfa_token` (
  `token_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '令牌ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `token_type` varchar(50) NOT NULL COMMENT '令牌类型',
  `token_value` varchar(200) NOT NULL COMMENT '令牌值',
  `secret_key` varchar(200) COMMENT '密钥',
  `token_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '令牌状态',
  `expire_time` datetime NOT NULL COMMENT '过期时间',
  `used_time` datetime COMMENT '使用时间',
  `verify_count` int(11) NOT NULL DEFAULT 0 COMMENT '验证次数',
  `max_verify_count` int(11) NOT NULL DEFAULT 3 COMMENT '最大验证次数',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`token_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_token_type` (`token_type`),
  KEY `idx_token_status` (`token_status`),
  KEY `idx_expire_time` (`expire_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='多因素认证令牌表';

-- 19. 访问控制表
DROP TABLE IF EXISTS `dc_access_control`;
CREATE TABLE `dc_access_control` (
  `control_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '控制ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `resource_type` varchar(50) NOT NULL COMMENT '资源类型',
  `resource_id` varchar(100) COMMENT '资源ID',
  `permission_type` varchar(50) NOT NULL COMMENT '权限类型',
  `access_level` varchar(50) NOT NULL COMMENT '访问级别',
  `field_permissions` text COMMENT '字段权限配置',
  `ip_whitelist` text COMMENT 'IP白名单',
  `time_restrictions` text COMMENT '时间限制',
  `access_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '访问状态',
  `effective_time` datetime COMMENT '生效时间',
  `expire_time` datetime COMMENT '过期时间',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`control_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_resource` (`resource_type`, `resource_id`),
  KEY `idx_access_status` (`access_status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='访问控制表';
-- ===========================
-- 通知服务表 (ruoyi-notification模块)
-- ===========================

-- 20. 通知日志表
DROP TABLE IF EXISTS `dc_notification_log`;
CREATE TABLE `dc_notification_log` (
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `notification_id` varchar(100) NOT NULL COMMENT '通知ID',
  `template_id` bigint(20) COMMENT '模板ID',
  `recipient_type` varchar(50) NOT NULL COMMENT '接收者类型',
  `recipient_id` varchar(100) NOT NULL COMMENT '接收者ID',
  `recipient_address` varchar(200) NOT NULL COMMENT '接收地址',
  `notification_type` varchar(50) NOT NULL COMMENT '通知类型',
  `notification_channel` varchar(50) NOT NULL COMMENT '通知渠道',
  `subject` varchar(200) COMMENT '主题',
  `content` text NOT NULL COMMENT '内容',
  `priority` varchar(20) NOT NULL DEFAULT 'NORMAL' COMMENT '优先级',
  `send_status` varchar(50) NOT NULL DEFAULT 'PENDING' COMMENT '发送状态',
  `send_time` datetime COMMENT '发送时间',
  `delivery_time` datetime COMMENT '投递时间',
  `retry_count` int(11) NOT NULL DEFAULT 0 COMMENT '重试次数',
  `max_retry_count` int(11) NOT NULL DEFAULT 3 COMMENT '最大重试次数',
  `error_message` text COMMENT '错误信息',
  `business_type` varchar(50) COMMENT '业务类型',
  `business_id` varchar(100) COMMENT '业务ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`log_id`),
  UNIQUE KEY `uk_notification_id` (`notification_id`),
  KEY `idx_recipient` (`recipient_type`, `recipient_id`),
  KEY `idx_send_status` (`send_status`),
  KEY `idx_notification_type` (`notification_type`),
  KEY `idx_send_time` (`send_time`),
  KEY `idx_business` (`business_type`, `business_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='通知日志表';

-- 21. 消息模板表
DROP TABLE IF EXISTS `dc_message_template`;
CREATE TABLE `dc_message_template` (
  `template_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '模板ID',
  `template_name` varchar(100) NOT NULL COMMENT '模板名称',
  `template_code` varchar(100) NOT NULL COMMENT '模板编码',
  `template_type` varchar(50) NOT NULL COMMENT '模板类型',
  `notification_channel` varchar(50) NOT NULL COMMENT '通知渠道',
  `subject_template` varchar(200) COMMENT '主题模板',
  `content_template` text NOT NULL COMMENT '内容模板',
  `template_variables` text COMMENT '模板变量',
  `template_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '模板状态',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`template_id`),
  UNIQUE KEY `uk_template_code` (`template_code`),
  KEY `idx_template_type` (`template_type`),
  KEY `idx_notification_channel` (`notification_channel`),
  KEY `idx_template_status` (`template_status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='消息模板表';

-- 22. 定时任务表
DROP TABLE IF EXISTS `dc_scheduled_task`;
CREATE TABLE `dc_scheduled_task` (
  `task_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '任务ID',
  `task_name` varchar(100) NOT NULL COMMENT '任务名称',
  `task_group` varchar(50) NOT NULL COMMENT '任务组',
  `task_type` varchar(50) NOT NULL COMMENT '任务类型',
  `cron_expression` varchar(100) COMMENT 'Cron表达式',
  `trigger_time` datetime COMMENT '触发时间',
  `task_class` varchar(200) COMMENT '任务类',
  `task_method` varchar(100) COMMENT '任务方法',
  `task_params` text COMMENT '任务参数',
  `task_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '任务状态',
  `last_run_time` datetime COMMENT '上次运行时间',
  `next_run_time` datetime COMMENT '下次运行时间',
  `run_count` bigint(20) NOT NULL DEFAULT 0 COMMENT '运行次数',
  `success_count` bigint(20) NOT NULL DEFAULT 0 COMMENT '成功次数',
  `fail_count` bigint(20) NOT NULL DEFAULT 0 COMMENT '失败次数',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`task_id`),
  UNIQUE KEY `uk_task_name_group` (`task_name`, `task_group`),
  KEY `idx_task_type` (`task_type`),
  KEY `idx_task_status` (`task_status`),
  KEY `idx_next_run_time` (`next_run_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='定时任务表';

-- 23. 投递状态表
DROP TABLE IF EXISTS `dc_delivery_status`;
CREATE TABLE `dc_delivery_status` (
  `status_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '状态ID',
  `notification_id` varchar(100) NOT NULL COMMENT '通知ID',
  `delivery_channel` varchar(50) NOT NULL COMMENT '投递渠道',
  `delivery_status` varchar(50) NOT NULL COMMENT '投递状态',
  `delivery_time` datetime COMMENT '投递时间',
  `read_time` datetime COMMENT '阅读时间',
  `response_time` datetime COMMENT '响应时间',
  `delivery_result` text COMMENT '投递结果',
  `error_code` varchar(50) COMMENT '错误码',
  `error_message` text COMMENT '错误信息',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`status_id`),
  KEY `idx_notification_id` (`notification_id`),
  KEY `idx_delivery_channel` (`delivery_channel`),
  KEY `idx_delivery_status` (`delivery_status`),
  KEY `idx_delivery_time` (`delivery_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='投递状态表';

-- ===========================
-- 报表分析表 (ruoyi-analytics模块)
-- ===========================

-- 24. 报表配置表
DROP TABLE IF EXISTS `dc_report_config`;
CREATE TABLE `dc_report_config` (
  `config_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '配置ID',
  `report_name` varchar(100) NOT NULL COMMENT '报表名称',
  `report_code` varchar(100) NOT NULL COMMENT '报表编码',
  `report_type` varchar(50) NOT NULL COMMENT '报表类型',
  `data_source` varchar(100) NOT NULL COMMENT '数据源',
  `query_sql` text COMMENT '查询SQL',
  `report_template` text COMMENT '报表模板',
  `output_format` varchar(50) NOT NULL DEFAULT 'EXCEL' COMMENT '输出格式',
  `schedule_type` varchar(50) COMMENT '调度类型',
  `cron_expression` varchar(100) COMMENT 'Cron表达式',
  `report_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '报表状态',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`config_id`),
  UNIQUE KEY `uk_report_code` (`report_code`),
  KEY `idx_report_type` (`report_type`),
  KEY `idx_report_status` (`report_status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='报表配置表';

-- 25. 报表实例表
DROP TABLE IF EXISTS `dc_report_instance`;
CREATE TABLE `dc_report_instance` (
  `instance_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '实例ID',
  `config_id` bigint(20) NOT NULL COMMENT '配置ID',
  `report_name` varchar(100) NOT NULL COMMENT '报表名称',
  `report_params` text COMMENT '报表参数',
  `generate_status` varchar(50) NOT NULL DEFAULT 'PENDING' COMMENT '生成状态',
  `file_path` varchar(500) COMMENT '文件路径',
  `file_size` bigint(20) COMMENT '文件大小',
  `record_count` bigint(20) COMMENT '记录数量',
  `start_time` datetime COMMENT '开始时间',
  `end_time` datetime COMMENT '结束时间',
  `cost_time` bigint(20) COMMENT '耗时(毫秒)',
  `error_message` text COMMENT '错误信息',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`instance_id`),
  KEY `idx_config_id` (`config_id`),
  KEY `idx_generate_status` (`generate_status`),
  KEY `idx_create_time` (`create_time`),
  CONSTRAINT `fk_instance_config` FOREIGN KEY (`config_id`) REFERENCES `dc_report_config` (`config_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='报表实例表';

-- 26. 仪表板组件表
DROP TABLE IF EXISTS `dc_dashboard_widget`;
CREATE TABLE `dc_dashboard_widget` (
  `widget_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '组件ID',
  `widget_name` varchar(100) NOT NULL COMMENT '组件名称',
  `widget_type` varchar(50) NOT NULL COMMENT '组件类型',
  `widget_title` varchar(200) NOT NULL COMMENT '组件标题',
  `data_source` varchar(100) NOT NULL COMMENT '数据源',
  `query_config` text COMMENT '查询配置',
  `chart_config` text COMMENT '图表配置',
  `refresh_interval` int(11) NOT NULL DEFAULT 300 COMMENT '刷新间隔(秒)',
  `widget_order` int(11) NOT NULL DEFAULT 0 COMMENT '显示顺序',
  `widget_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '组件状态',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`widget_id`),
  KEY `idx_widget_type` (`widget_type`),
  KEY `idx_widget_status` (`widget_status`),
  KEY `idx_widget_order` (`widget_order`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='仪表板组件表';

-- 27. 统计缓存表
DROP TABLE IF EXISTS `dc_statistics_cache`;
CREATE TABLE `dc_statistics_cache` (
  `cache_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '缓存ID',
  `cache_key` varchar(200) NOT NULL COMMENT '缓存键',
  `cache_type` varchar(50) NOT NULL COMMENT '缓存类型',
  `cache_data` text NOT NULL COMMENT '缓存数据',
  `data_version` varchar(50) COMMENT '数据版本',
  `expire_time` datetime NOT NULL COMMENT '过期时间',
  `hit_count` bigint(20) NOT NULL DEFAULT 0 COMMENT '命中次数',
  `last_hit_time` datetime COMMENT '最后命中时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`cache_id`),
  UNIQUE KEY `uk_cache_key` (`cache_key`),
  KEY `idx_cache_type` (`cache_type`),
  KEY `idx_expire_time` (`expire_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='统计缓存表';
-- ===========================
-- 工作流管理表 (ruoyi-workflow模块)
-- ===========================

-- 28. 工作流定义表
DROP TABLE IF EXISTS `dc_workflow_definition`;
CREATE TABLE `dc_workflow_definition` (
  `definition_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '定义ID',
  `workflow_key` varchar(100) NOT NULL COMMENT '工作流键',
  `workflow_name` varchar(100) NOT NULL COMMENT '工作流名称',
  `workflow_version` int(11) NOT NULL DEFAULT 1 COMMENT '版本号',
  `workflow_category` varchar(50) COMMENT '工作流分类',
  `process_definition` text NOT NULL COMMENT '流程定义',
  `deployment_id` varchar(100) COMMENT '部署ID',
  `workflow_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '工作流状态',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`definition_id`),
  UNIQUE KEY `uk_workflow_key_version` (`workflow_key`, `workflow_version`),
  KEY `idx_workflow_category` (`workflow_category`),
  KEY `idx_workflow_status` (`workflow_status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='工作流定义表';

-- 29. 工作流实例表
DROP TABLE IF EXISTS `dc_workflow_instance`;
CREATE TABLE `dc_workflow_instance` (
  `instance_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '实例ID',
  `process_instance_id` varchar(100) NOT NULL COMMENT '流程实例ID',
  `definition_id` bigint(20) NOT NULL COMMENT '定义ID',
  `workflow_key` varchar(100) NOT NULL COMMENT '工作流键',
  `business_key` varchar(100) COMMENT '业务键',
  `business_type` varchar(50) COMMENT '业务类型',
  `business_id` varchar(100) COMMENT '业务ID',
  `instance_name` varchar(200) COMMENT '实例名称',
  `current_activity` varchar(100) COMMENT '当前活动',
  `instance_status` varchar(50) NOT NULL DEFAULT 'RUNNING' COMMENT '实例状态',
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `end_time` datetime COMMENT '结束时间',
  `duration` bigint(20) COMMENT '持续时间(毫秒)',
  `start_user_id` bigint(20) COMMENT '启动用户ID',
  `start_user_name` varchar(50) COMMENT '启动用户名',
  `variables` text COMMENT '流程变量',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`instance_id`),
  UNIQUE KEY `uk_process_instance_id` (`process_instance_id`),
  KEY `idx_definition_id` (`definition_id`),
  KEY `idx_business_key` (`business_key`),
  KEY `idx_instance_status` (`instance_status`),
  KEY `idx_start_time` (`start_time`),
  CONSTRAINT `fk_instance_definition` FOREIGN KEY (`definition_id`) REFERENCES `dc_workflow_definition` (`definition_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='工作流实例表';

-- 30. 工作流任务表
DROP TABLE IF EXISTS `dc_workflow_task`;
CREATE TABLE `dc_workflow_task` (
  `task_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '任务ID',
  `task_def_key` varchar(100) NOT NULL COMMENT '任务定义键',
  `task_name` varchar(200) NOT NULL COMMENT '任务名称',
  `instance_id` bigint(20) NOT NULL COMMENT '实例ID',
  `process_instance_id` varchar(100) NOT NULL COMMENT '流程实例ID',
  `execution_id` varchar(100) COMMENT '执行ID',
  `assignee` varchar(50) COMMENT '办理人',
  `candidate_users` varchar(500) COMMENT '候选用户',
  `candidate_groups` varchar(500) COMMENT '候选组',
  `task_status` varchar(50) NOT NULL DEFAULT 'CREATED' COMMENT '任务状态',
  `priority` int(11) NOT NULL DEFAULT 50 COMMENT '优先级',
  `due_date` datetime COMMENT '到期时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `claim_time` datetime COMMENT '签收时间',
  `complete_time` datetime COMMENT '完成时间',
  `duration` bigint(20) COMMENT '持续时间(毫秒)',
  `form_key` varchar(200) COMMENT '表单键',
  `task_variables` text COMMENT '任务变量',
  `comments` text COMMENT '任务备注',
  PRIMARY KEY (`task_id`),
  KEY `idx_instance_id` (`instance_id`),
  KEY `idx_process_instance_id` (`process_instance_id`),
  KEY `idx_assignee` (`assignee`),
  KEY `idx_task_status` (`task_status`),
  KEY `idx_due_date` (`due_date`),
  CONSTRAINT `fk_task_instance` FOREIGN KEY (`instance_id`) REFERENCES `dc_workflow_instance` (`instance_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='工作流任务表';

-- 31. 审批记录表
DROP TABLE IF EXISTS `dc_approval_record`;
CREATE TABLE `dc_approval_record` (
  `record_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `task_id` bigint(20) NOT NULL COMMENT '任务ID',
  `instance_id` bigint(20) NOT NULL COMMENT '实例ID',
  `approver_id` bigint(20) NOT NULL COMMENT '审批人ID',
  `approver_name` varchar(50) NOT NULL COMMENT '审批人姓名',
  `approval_action` varchar(50) NOT NULL COMMENT '审批动作',
  `approval_result` varchar(50) NOT NULL COMMENT '审批结果',
  `approval_comments` text COMMENT '审批意见',
  `approval_time` datetime NOT NULL COMMENT '审批时间',
  `next_assignee` varchar(50) COMMENT '下一办理人',
  `attachment_ids` varchar(500) COMMENT '附件ID列表',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`record_id`),
  KEY `idx_task_id` (`task_id`),
  KEY `idx_instance_id` (`instance_id`),
  KEY `idx_approver_id` (`approver_id`),
  KEY `idx_approval_time` (`approval_time`),
  CONSTRAINT `fk_approval_task` FOREIGN KEY (`task_id`) REFERENCES `dc_workflow_task` (`task_id`),
  CONSTRAINT `fk_approval_instance` FOREIGN KEY (`instance_id`) REFERENCES `dc_workflow_instance` (`instance_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='审批记录表';

-- ===========================
-- 集成适配表 (ruoyi-integration模块)
-- ===========================

-- 32. 外部系统表
DROP TABLE IF EXISTS `dc_external_system`;
CREATE TABLE `dc_external_system` (
  `system_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '系统ID',
  `system_name` varchar(100) NOT NULL COMMENT '系统名称',
  `system_code` varchar(100) NOT NULL COMMENT '系统编码',
  `system_type` varchar(50) NOT NULL COMMENT '系统类型',
  `system_url` varchar(500) COMMENT '系统URL',
  `api_version` varchar(50) COMMENT 'API版本',
  `auth_type` varchar(50) COMMENT '认证类型',
  `auth_config` text COMMENT '认证配置',
  `connection_config` text COMMENT '连接配置',
  `system_status` varchar(50) NOT NULL DEFAULT 'ACTIVE' COMMENT '系统状态',
  `health_check_url` varchar(500) COMMENT '健康检查URL',
  `last_health_check` datetime COMMENT '最后健康检查时间',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`system_id`),
  UNIQUE KEY `uk_system_code` (`system_code`),
  KEY `idx_system_type` (`system_type`),
  KEY `idx_system_status` (`system_status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='外部系统表';

-- ===========================
-- 系统监控表 (ruoyi-monitor模块)
-- ===========================

-- 33. 系统监控表
DROP TABLE IF EXISTS `dc_system_monitor`;
CREATE TABLE `dc_system_monitor` (
  `monitor_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '监控ID',
  `monitor_time` datetime NOT NULL COMMENT '监控时间',
  `cpu_usage` decimal(5,2) COMMENT 'CPU使用率',
  `memory_usage` decimal(5,2) COMMENT '内存使用率',
  `disk_usage` decimal(5,2) COMMENT '磁盘使用率',
  `network_in` bigint(20) COMMENT '网络入流量',
  `network_out` bigint(20) COMMENT '网络出流量',
  `active_connections` int(11) COMMENT '活跃连接数',
  `thread_count` int(11) COMMENT '线程数',
  `heap_memory_used` bigint(20) COMMENT '堆内存使用量',
  `heap_memory_max` bigint(20) COMMENT '堆内存最大值',
  `gc_count` bigint(20) COMMENT 'GC次数',
  `gc_time` bigint(20) COMMENT 'GC时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`monitor_id`),
  KEY `idx_monitor_time` (`monitor_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='系统监控表';

-- ===========================
-- 索引优化
-- ===========================

-- 为经常查询的字段添加复合索引
CREATE INDEX `idx_contract_company_status` ON `dc_service_contract` (`security_company_id`, `client_company_id`, `contract_status`);
CREATE INDEX `idx_credit_contract_status` ON `dc_digital_credit` (`contract_id`, `credit_status`);
CREATE INDEX `idx_financing_credit_status` ON `dc_financing_application` (`credit_id`, `application_status`);
CREATE INDEX `idx_payment_contract_status` ON `dc_payment_record` (`contract_id`, `payment_status`);
CREATE INDEX `idx_audit_user_time` ON `dc_audit_log` (`user_id`, `operation_time`);
CREATE INDEX `idx_notification_recipient_status` ON `dc_notification_log` (`recipient_type`, `recipient_id`, `send_status`);

-- ===========================
-- 初始化数据
-- ===========================

-- 插入系统配置数据
INSERT INTO `dc_company_info` (`company_id`, `company_name`, `company_type`, `registration_number`, `legal_representative`, `contact_person`, `contact_phone`, `contact_email`, `address`, `status`, `create_by`, `remark`) VALUES
(1, '安信数字债权服务平台', 'PLATFORM', 'REG001', '张三', '李四', '13800138000', 'admin@anxin.com', '北京市朝阳区', '0', 'admin', '平台运营方'),
(2, '示例安保公司', 'SECURITY', 'REG002', '王五', '赵六', '13800138001', 'security@example.com', '上海市浦东新区', '0', 'admin', '示例安保服务提供商'),
(3, '示例客户公司', 'CLIENT', 'REG003', '孙七', '周八', '13800138002', 'client@example.com', '广州市天河区', '0', 'admin', '示例客户单位'),
(4, '示例金融机构', 'FINANCIAL', 'REG004', '吴九', '郑十', '13800138003', 'finance@example.com', '深圳市南山区', '0', 'admin', '示例金融服务机构');

-- 插入消息模板数据
INSERT INTO `dc_message_template` (`template_name`, `template_code`, `template_type`, `notification_channel`, `subject_template`, `content_template`, `template_variables`, `template_status`, `create_by`, `remark`) VALUES
('合同确认通知', 'CONTRACT_CONFIRM', 'BUSINESS', 'EMAIL', '合同确认通知 - ${contractNumber}', '尊敬的${recipientName}，您的合同${contractNumber}已确认，请及时查看。', 'contractNumber,recipientName', 'ACTIVE', 'admin', '合同确认业务通知模板'),
('付款提醒通知', 'PAYMENT_REMINDER', 'BUSINESS', 'SMS', '付款提醒', '您的合同${contractNumber}将于${dueDate}到期，请及时付款。', 'contractNumber,dueDate', 'ACTIVE', 'admin', '付款提醒业务通知模板'),
('融资审批通知', 'FINANCING_APPROVAL', 'BUSINESS', 'EMAIL', '融资审批结果通知', '您的融资申请${applicationNumber}审批结果：${result}，详情请登录系统查看。', 'applicationNumber,result', 'ACTIVE', 'admin', '融资审批结果通知模板');

-- 插入加密密钥数据（示例，实际部署时需要生成真实密钥）
INSERT INTO `dc_encryption_key` (`key_name`, `key_alias`, `key_type`, `algorithm`, `key_length`, `key_status`, `create_by`, `remark`) VALUES
('主密钥', 'MASTER_KEY_001', 'MASTER', 'AES-256-GCM', 256, 'ACTIVE', 'admin', '系统主加密密钥'),
('数据加密密钥', 'DATA_KEY_001', 'DATA', 'AES-256-GCM', 256, 'ACTIVE', 'admin', '敏感数据加密密钥');

-- 提交事务
COMMIT;