-- =====================================================
-- 课题经费拨付对接财务报销 - 开发库增量脚本
--
-- 背景：每条 `research_fund_record` 拨款记录改为自动生成并提交一张财务报销单
--       （finance_reimbursement，1:1、金额一致），财务审批结果由
--       ResearchFundStatusListener 回写本台账状态。
--
-- 执行库：脚本用 DATABASE() 判定，与库名无关，故可对任意目标库执行。
--         2026-09-29 已在 application-local.yaml 指向的 mom-jssm 执行并核对通过；
--         其余环境（dev/test 指向 ruoyi-vue-pro，yzfx/wtxc/zsjc/chdl/kyzn 指向各自 mom-* 库）按需另跑。
--         本脚本幂等，可重复执行。
-- 内容：① research_fund_record 新增 4 列 + status 注释更新 + 索引
--       ② system_dict_data 的 research_fund_status 补「报销审批中/已驳回」
-- 注意：本脚本不含 tenant_id（项目不使用多租户模式）。
-- =====================================================

-- ----- 1. research_fund_record 新增 4 列（ALTER TABLE ADD COLUMN 不支持 IF NOT EXISTS，用 information_schema 守卫） -----

SET @c = (SELECT COUNT(*) FROM information_schema.columns
          WHERE table_schema = DATABASE() AND table_name = 'research_fund_record' AND column_name = 'reimbursement_id');
SET @sql = IF(@c = 0,
    'ALTER TABLE research_fund_record ADD COLUMN reimbursement_id bigint DEFAULT NULL COMMENT ''关联财务报销单 ID（finance_reimbursement.id）'' AFTER status',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c = (SELECT COUNT(*) FROM information_schema.columns
          WHERE table_schema = DATABASE() AND table_name = 'research_fund_record' AND column_name = 'reimbursement_no');
SET @sql = IF(@c = 0,
    'ALTER TABLE research_fund_record ADD COLUMN reimbursement_no varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '''' COMMENT ''关联财务报销单号'' AFTER reimbursement_id',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c = (SELECT COUNT(*) FROM information_schema.columns
          WHERE table_schema = DATABASE() AND table_name = 'research_fund_record' AND column_name = 'reject_reason');
SET @sql = IF(@c = 0,
    'ALTER TABLE research_fund_record ADD COLUMN reject_reason varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '''' COMMENT ''报销驳回原因（BPM 回写）'' AFTER reimbursement_no',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c = (SELECT COUNT(*) FROM information_schema.columns
          WHERE table_schema = DATABASE() AND table_name = 'research_fund_record' AND column_name = 'reject_time');
SET @sql = IF(@c = 0,
    'ALTER TABLE research_fund_record ADD COLUMN reject_time datetime DEFAULT NULL COMMENT ''报销驳回时间'' AFTER reject_reason',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- status 列注释更新为「0-待拨付 15-报销审批中 20-已拨付 30-已驳回」
ALTER TABLE research_fund_record
  MODIFY COLUMN status tinyint NOT NULL DEFAULT '0' COMMENT '状态：0-待拨付 15-报销审批中 20-已拨付 30-已驳回';

-- 按报销单号回查台账的索引
SET @c = (SELECT COUNT(*) FROM information_schema.statistics
          WHERE table_schema = DATABASE() AND table_name = 'research_fund_record' AND index_name = 'idx_reimbursement_id');
SET @sql = IF(@c = 0,
    'ALTER TABLE research_fund_record ADD INDEX idx_reimbursement_id (reimbursement_id) USING BTREE',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ----- 2. 字典 research_fund_status 补 2 条 + 排序调整 + 类型备注 -----

UPDATE system_dict_type SET remark = '课题经费待拨付/报销审批中/已拨付/已驳回', update_time = NOW()
WHERE `type` = 'research_fund_status';

-- 先删后插，保证幂等（同 research_module.sql 的写法）
DELETE FROM system_dict_data WHERE dict_type = 'research_fund_status';
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8100401, 1, '待拨付', '0', 'research_fund_status', 0, 'warning', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8100403, 2, '报销审批中', '15', 'research_fund_status', 0, 'processing', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8100402, 3, '已拨付', '20', 'research_fund_status', 0, 'success', '', '1', NOW(), '1', NOW(), b'0');
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
VALUES (8100404, 4, '已驳回', '30', 'research_fund_status', 0, 'danger', '', '1', NOW(), '1', NOW(), b'0');