rustjr-account-management/migrations/002_account_model_extension.sql

-- 账户管理模型扩展 - 数据库迁移脚本
-- 创建时间: 2026-01-05
-- 说明: 扩展三科目余额模型，增强交易状态机

-- =====================================================
-- 1. 扩展账户余额表 - 三科目模型
-- =====================================================

-- 添加三科目余额字段
ALTER TABLE account_balance 
ADD COLUMN personal_balance DECIMAL(20,2) DEFAULT 0 COMMENT '个人余额(可用)' AFTER account_type,
ADD COLUMN labor_balance DECIMAL(20,2) DEFAULT 0 COMMENT '劳动报酬(可用)' AFTER personal_balance;

-- 重命名 frozen_amount 为 frozen_balance（语义更清晰）
ALTER TABLE account_balance 
CHANGE COLUMN frozen_amount frozen_balance DECIMAL(20,2) DEFAULT 0 COMMENT '冻结余额(不可用)';

-- 数据迁移：将现有 system_balance 分配到 personal_balance
-- 注意：这是保守迁移，将全部可用余额放入个人余额
UPDATE account_balance 
SET personal_balance = system_balance - COALESCE(frozen_balance, 0),
    labor_balance = 0
WHERE personal_balance = 0;

-- 添加不变量校验的注释
ALTER TABLE account_balance 
COMMENT = '账户余额表 - 不变量: personal_balance + labor_balance + frozen_balance = bank_balance';

-- =====================================================
-- 2. 扩展系统交易表 - 增强状态机和幂等键
-- =====================================================

-- 添加来源幂等键（用于外部入账去重）
ALTER TABLE system_transaction
ADD COLUMN source_key VARCHAR(128) COMMENT '来源幂等键(SourceKey)' AFTER bank_ref_no,
ADD COLUMN submitted_at DATETIME COMMENT '提交银行时间' AFTER confirmed_at,
ADD COLUMN version INT DEFAULT 0 COMMENT '乐观锁版本' AFTER submitted_at;

-- 创建来源幂等键的唯一索引（MySQL 自动忽略 NULL 值的唯一约束）
CREATE UNIQUE INDEX idx_source_key ON system_transaction(source_key);

-- 修改状态枚举以支持新状态机
-- 注意：MySQL 的 ENUM 修改需要包含所有旧值和新值
ALTER TABLE system_transaction
MODIFY COLUMN status ENUM(
    'created',        -- 已创建（初始状态）
    'pending',        -- 待处理（已建立在途）
    'bank_submitted', -- 已提交银行
    'success',        -- 成功
    'failed',         -- 失败
    'timeout',        -- 超时
    'reversed',       -- 已冲正
    'processing',     -- 兼容旧状态
    'confirmed',      -- 兼容旧状态
    'mismatch'        -- 对账不匹配
) DEFAULT 'created' COMMENT '交易状态';

-- 数据迁移：将旧状态映射到新状态
UPDATE system_transaction SET status = 'bank_submitted' WHERE status = 'processing';
UPDATE system_transaction SET status = 'success' WHERE status = 'confirmed';

-- 添加提交时间索引（用于超时检测）
CREATE INDEX idx_submitted_at ON system_transaction(submitted_at);
CREATE INDEX idx_status_submitted ON system_transaction(status, submitted_at);

-- =====================================================
-- 3. 创建在途交易明细表（可选，用于细粒度在途管理）
-- =====================================================

CREATE TABLE IF NOT EXISTS transit_detail (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id BIGINT NOT NULL COMMENT '账户ID',
    txn_no VARCHAR(32) NOT NULL COMMENT '关联交易号',
    amount DECIMAL(20,2) NOT NULL COMMENT '在途金额',
    from_personal DECIMAL(20,2) DEFAULT 0 COMMENT '来自个人余额',
    from_labor DECIMAL(20,2) DEFAULT 0 COMMENT '来自劳动报酬',
    status ENUM('pending', 'settled', 'rolled_back') DEFAULT 'pending' COMMENT '状态',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    settled_at DATETIME COMMENT '结转/回退时间',
    INDEX idx_account_id (account_id),
    UNIQUE KEY uk_txn_no (txn_no),
    INDEX idx_account_status (account_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='在途交易明细表';

-- =====================================================
-- 4. 创建补偿任务表
-- =====================================================

CREATE TABLE IF NOT EXISTS compensation_task (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    txn_no VARCHAR(32) NOT NULL COMMENT '关联交易号',
    task_type ENUM('timeout_check', 'reconcile', 'reverse', 'retry') NOT NULL COMMENT '任务类型',
    status ENUM('pending', 'processing', 'completed', 'failed', 'dead_letter') DEFAULT 'pending' COMMENT '任务状态',
    retry_count INT DEFAULT 0 COMMENT '重试次数',
    max_retries INT DEFAULT 3 COMMENT '最大重试次数',
    next_retry_at DATETIME COMMENT '下次重试时间',
    error_message TEXT COMMENT '错误信息',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    completed_at DATETIME COMMENT '完成时间',
    INDEX idx_status (status),
    INDEX idx_next_retry (status, next_retry_at),
    INDEX idx_txn_no (txn_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='补偿任务表';

-- =====================================================
-- 5. 创建不变量校验日志表（审计用途）
-- =====================================================

CREATE TABLE IF NOT EXISTS invariant_check_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id BIGINT NOT NULL COMMENT '账户ID',
    check_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '校验时间',
    personal_balance DECIMAL(20,2) NOT NULL COMMENT '个人余额',
    labor_balance DECIMAL(20,2) NOT NULL COMMENT '劳动报酬',
    frozen_balance DECIMAL(20,2) NOT NULL COMMENT '冻结余额',
    bank_balance DECIMAL(20,2) NOT NULL COMMENT '银行余额',
    transit_amount DECIMAL(20,2) NOT NULL COMMENT '在途金额',
    is_valid TINYINT(1) NOT NULL COMMENT '是否有效',
    difference DECIMAL(20,2) COMMENT '差异金额',
    trigger_source VARCHAR(100) COMMENT '触发来源',
    INDEX idx_account_time (account_id, check_time),
    INDEX idx_is_valid (is_valid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='不变量校验日志表';

-- =====================================================
-- 6. 扩展对账表 - 支持三账对齐
-- =====================================================

-- 添加三账对账结果字段
ALTER TABLE reconciliation_batch
ADD COLUMN bank_total DECIMAL(20,2) COMMENT '银行账汇总' AFTER mismatch_count,
ADD COLUMN transit_net DECIMAL(20,2) COMMENT '在途净额' AFTER bank_total,
ADD COLUMN ledger_total DECIMAL(20,2) COMMENT '总账汇总' AFTER transit_net,
ADD COLUMN three_account_balanced TINYINT(1) DEFAULT NULL COMMENT '三账是否平衡' AFTER ledger_total;