baqing-shop/sql/biz_shop_finance.sql

-- 店铺财务：钱包、流水、收款账户、提现单
-- 依据：doc/平台后台/财务管理/提现审核/提现审核技术方案.md v1.0

CREATE TABLE IF NOT EXISTS `biz_shop_wallet` (
  `wallet_id`          bigint(20)     NOT NULL AUTO_INCREMENT COMMENT '钱包ID',
  `shop_id`            bigint(20)     NOT NULL COMMENT '店铺ID',
  `available_balance`  decimal(12,2)  NOT NULL DEFAULT '0.00' COMMENT '可用余额',
  `frozen_amount`      decimal(12,2)  NOT NULL DEFAULT '0.00' COMMENT '冻结金额',
  `update_time`        datetime                DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`wallet_id`),
  UNIQUE KEY `uk_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='店铺钱包';

CREATE TABLE IF NOT EXISTS `biz_shop_fund_log` (
  `log_id`           bigint(20)     NOT NULL AUTO_INCREMENT COMMENT '流水ID',
  `shop_id`          bigint(20)     NOT NULL COMMENT '店铺ID',
  `shop_name`        varchar(100)            DEFAULT NULL COMMENT '店铺名称快照',
  `biz_no`           varchar(32)    NOT NULL COMMENT '业务编号（订单号/提现编号）',
  `change_reason`    char(1)        NOT NULL COMMENT '1订单收支 2余额提现 3提现驳回 4提现完成',
  `balance_change`   decimal(12,2)  NOT NULL DEFAULT '0.00' COMMENT '余额变动',
  `balance_after`    decimal(12,2)  NOT NULL DEFAULT '0.00' COMMENT '变动后余额',
  `frozen_change`    decimal(12,2)  NOT NULL DEFAULT '0.00' COMMENT '冻结变动',
  `frozen_after`     decimal(12,2)  NOT NULL DEFAULT '0.00' COMMENT '变动后冻结',
  `ref_type`         varchar(16)    NOT NULL COMMENT 'ORDER/WITHDRAW',
  `ref_id`           bigint(20)     NOT NULL COMMENT '关联ID',
  `create_time`      datetime       NOT NULL COMMENT '变动时间',
  PRIMARY KEY (`log_id`),
  KEY `idx_shop_time` (`shop_id`, `create_time`),
  KEY `idx_ref` (`ref_type`, `ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='店铺资金流水';

CREATE TABLE IF NOT EXISTS `biz_shop_pay_account` (
  `account_id`     bigint(20)   NOT NULL AUTO_INCREMENT COMMENT '账户ID',
  `shop_id`        bigint(20)   NOT NULL COMMENT '店铺ID',
  `account_type`   char(1)      NOT NULL COMMENT '1银行卡 2支付宝 3微信',
  `real_name`      varchar(64)  NOT NULL COMMENT '真实姓名',
  `account_no`     varchar(128) NOT NULL COMMENT '账号',
  `del_flag`       char(1)      NOT NULL DEFAULT '0' COMMENT '0正常 2删除',
  `create_by`      varchar(64)           DEFAULT '' COMMENT '创建者',
  `create_time`    datetime              DEFAULT NULL COMMENT '创建时间',
  `update_by`      varchar(64)           DEFAULT '' COMMENT '更新者',
  `update_time`    datetime              DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`account_id`),
  KEY `idx_shop_del` (`shop_id`, `del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='店铺收款账户';

CREATE TABLE IF NOT EXISTS `biz_shop_withdraw` (
  `withdraw_id`         bigint(20)     NOT NULL AUTO_INCREMENT COMMENT '提现ID',
  `withdraw_no`         varchar(32)    NOT NULL COMMENT '提现编号',
  `shop_id`             bigint(20)     NOT NULL COMMENT '店铺ID',
  `shop_name`           varchar(100)            DEFAULT NULL COMMENT '店铺名称快照',
  `account_id`          bigint(20)     NOT NULL COMMENT '收款账户ID',
  `account_type`        char(1)        NOT NULL COMMENT '账户类型快照',
  `account_real_name`   varchar(64)    NOT NULL COMMENT '真实姓名快照',
  `account_no_mask`     varchar(128)   NOT NULL COMMENT '账号脱敏快照',
  `withdraw_amount`     decimal(12,2)  NOT NULL COMMENT '提现金额',
  `remark`              varchar(200)            DEFAULT NULL COMMENT '商家备注',
  `withdraw_status`     char(1)        NOT NULL COMMENT '1待审核 2审核不通过 3提现完成',
  `process_remark`      varchar(200)            DEFAULT NULL COMMENT '平台处理说明',
  `audit_by`            varchar(64)             DEFAULT NULL COMMENT '审核人',
  `audit_time`          datetime                DEFAULT NULL COMMENT '审核时间',
  `apply_time`          datetime       NOT NULL COMMENT '申请时间',
  `create_by`           varchar(64)             DEFAULT '' COMMENT '创建者',
  PRIMARY KEY (`withdraw_id`),
  UNIQUE KEY `uk_withdraw_no` (`withdraw_no`),
  KEY `idx_shop_status_time` (`shop_id`, `withdraw_status`, `apply_time`),
  KEY `idx_status_time` (`withdraw_status`, `apply_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='店铺提现单';