shanxia2.0/backend/scripts/migration_20260725_bx_invoice_wallet.sql

-- ============================================================
-- 报销 · 发票夹额度：建表 + 菜单（报销分组下方）
-- 可重复执行（CREATE IF NOT EXISTS；菜单 NOT EXISTS 防护）
-- ============================================================

-- 1) bx_invoice 发票档案
CREATE TABLE IF NOT EXISTS `bx_invoice` (
  `id` VARCHAR(64) NOT NULL COMMENT '主键UUID',
  `owner_user_id` VARCHAR(64) NOT NULL COMMENT '所属员工ID',
  `owner_user_name` VARCHAR(64) NOT NULL COMMENT '所属员工姓名',
  `amount` DECIMAL(14,2) NOT NULL COMMENT '入账金额',
  `upload_by` VARCHAR(64) DEFAULT NULL COMMENT '上传操作人ID',
  `upload_by_name` VARCHAR(64) DEFAULT NULL COMMENT '上传操作人姓名',
  `tenant_no` VARCHAR(64) NOT NULL DEFAULT 'shanxia2.0',
  `order_num` INT DEFAULT NULL,
  `remark` VARCHAR(512) DEFAULT NULL,
  `create_by` VARCHAR(64) DEFAULT NULL,
  `create_dept_id` VARCHAR(64) NOT NULL DEFAULT '',
  `create_time` DATETIME NOT NULL,
  `update_by` VARCHAR(64) DEFAULT NULL,
  `update_time` DATETIME NOT NULL,
  `del_flag` SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_bx_invoice_owner` (`tenant_no`,`owner_user_id`,`del_flag`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='发票档案';

-- 2) bx_invoice_ledger 额度流水（权威）
CREATE TABLE IF NOT EXISTS `bx_invoice_ledger` (
  `id` VARCHAR(64) NOT NULL,
  `owner_user_id` VARCHAR(64) NOT NULL,
  `entry_type` VARCHAR(32) NOT NULL COMMENT 'credit_upload|debit_delete|debit_reimburse|credit_release',
  `amount` DECIMAL(14,2) NOT NULL COMMENT '正数，方向由 entry_type 决定',
  `invoice_id` VARCHAR(64) DEFAULT NULL,
  `reimburse_id` VARCHAR(64) DEFAULT NULL,
  `ref_ledger_id` VARCHAR(64) DEFAULT NULL COMMENT '退回指向原占用流水',
  `tenant_no` VARCHAR(64) NOT NULL DEFAULT 'shanxia2.0',
  `order_num` INT DEFAULT NULL,
  `remark` VARCHAR(512) DEFAULT NULL,
  `create_by` VARCHAR(64) DEFAULT NULL,
  `create_dept_id` VARCHAR(64) NOT NULL DEFAULT '',
  `create_time` DATETIME NOT NULL,
  `update_by` VARCHAR(64) DEFAULT NULL,
  `update_time` DATETIME DEFAULT NULL,
  `del_flag` SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_bx_ledger_owner` (`tenant_no`,`owner_user_id`,`del_flag`,`create_time`),
  KEY `idx_bx_ledger_reimburse` (`tenant_no`,`reimburse_id`,`entry_type`,`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='发票额度流水';

-- 3) bx_reimburse_invoice 报销关联发票（M:N，可选）
CREATE TABLE IF NOT EXISTS `bx_reimburse_invoice` (
  `id` VARCHAR(64) NOT NULL,
  `reimburse_id` VARCHAR(64) NOT NULL,
  `invoice_id` VARCHAR(64) NOT NULL,
  `tenant_no` VARCHAR(64) NOT NULL DEFAULT 'shanxia2.0',
  `order_num` INT DEFAULT NULL,
  `remark` VARCHAR(512) DEFAULT NULL,
  `create_by` VARCHAR(64) DEFAULT NULL,
  `create_dept_id` VARCHAR(64) NOT NULL DEFAULT '',
  `create_time` DATETIME NOT NULL,
  `update_by` VARCHAR(64) DEFAULT NULL,
  `update_time` DATETIME DEFAULT NULL,
  `del_flag` SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_bx_ri_reimburse` (`tenant_no`,`reimburse_id`,`del_flag`),
  KEY `idx_bx_ri_invoice` (`tenant_no`,`invoice_id`,`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报销关联发票';

-- 4) bx_invoice_wallet_summary 额度缓存
CREATE TABLE IF NOT EXISTS `bx_invoice_wallet_summary` (
  `id` VARCHAR(64) NOT NULL,
  `owner_user_id` VARCHAR(64) NOT NULL,
  `owner_user_name` VARCHAR(64) NOT NULL DEFAULT '',
  `total_amount` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `used_amount` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `balance` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `invoice_count` INT NOT NULL DEFAULT 0,
  `version` INT NOT NULL DEFAULT 0,
  `tenant_no` VARCHAR(64) NOT NULL DEFAULT 'shanxia2.0',
  `order_num` INT DEFAULT NULL,
  `remark` VARCHAR(512) DEFAULT NULL,
  `create_by` VARCHAR(64) DEFAULT NULL,
  `create_dept_id` VARCHAR(64) NOT NULL DEFAULT '',
  `create_time` DATETIME NOT NULL,
  `update_by` VARCHAR(64) DEFAULT NULL,
  `update_time` DATETIME NOT NULL,
  `del_flag` SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_bx_wallet_owner` (`tenant_no`,`owner_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='发票夹额度缓存';

-- 5) 菜单：挂在「报销」分组 BX...00010 下，order_num 4/5
INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'BX00000000000000000000000000020', '发票夹管理', 'BX00000000000000000000000000010', 'C',
  'invoice-wallet-admin', '/bx/invoice-wallet/admin/index', 'bx:invoiceWallet:admin',
  1, '1', '1', 'shanxia2.0', 4, '管理员代传/删除发票；全员额度列表',
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'BX00000000000000000000000000020');

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'BX00000000000000000000000000021', '我的发票夹', 'BX00000000000000000000000000010', 'C',
  'invoice-wallet-mine', '/bx/invoice-wallet/mine/index', 'bx:invoiceWallet:mine',
  1, '1', '1', 'shanxia2.0', 5, '员工自助上传与查看本人发票夹',
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'BX00000000000000000000000000021');