zkxw/docs/sql/biz_per_sample_payment_presign.sql

-- 按样本缴费 + 预签约订单 + 协议退费登记
-- MySQL 5.7 / utf8mb4
-- 说明：
--   1) biz_collection_protocol 增量字段用 information_schema 判断后 ADD，可重复执行
--   2) payment_model_version 默认 2；首次升级将存量行置为 1（已有 version=1 则跳过 UPDATE）
--   3) 新表 CREATE TABLE IF NOT EXISTS，校对规则与存量库一致：utf8mb4_unicode_ci
--   4) 第 9 节 CONVERT 用于已建表纠偏（可重复执行）

-- ---------------------------------------------------------------------------
-- 1) 采集协议单增量字段
-- ---------------------------------------------------------------------------
SET @db := DATABASE();

SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'biz_collection_protocol' AND COLUMN_NAME = 'protocol_total_amount'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `biz_collection_protocol` ADD COLUMN `protocol_total_amount` DECIMAL(18,2) DEFAULT NULL COMMENT ''手填协议总金额'' AFTER `collector_user_id`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'biz_collection_protocol' AND COLUMN_NAME = 'source_presign_order_id'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `biz_collection_protocol` ADD COLUMN `source_presign_order_id` VARCHAR(64) DEFAULT NULL COMMENT ''来源预签约订单ID'' AFTER `protocol_total_amount`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'biz_collection_protocol' AND COLUMN_NAME = 'payment_model_version'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `biz_collection_protocol` ADD COLUMN `payment_model_version` SMALLINT NOT NULL DEFAULT 2 COMMENT ''缴费模型版本 1旧/2新'' AFTER `source_presign_order_id`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 存量协议走旧模型；若库中已有 version=1 则视为已回填，跳过（避免重跑误伤新单）
SET @has_v1 := (
  SELECT IF(EXISTS(
    SELECT 1 FROM `biz_collection_protocol` WHERE `payment_model_version` = 1 LIMIT 1
  ), 1, 0)
);
UPDATE `biz_collection_protocol`
SET `payment_model_version` = 1
WHERE @has_v1 = 0;

-- ---------------------------------------------------------------------------
-- 2) 样本缴费方案
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `biz_protocol_sample_payment` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `collection_protocol_id` varchar(64) NOT NULL COMMENT '采集协议单ID',
  `protocol_sample_id` varchar(64) DEFAULT NULL COMMENT '协议样本ID（可空兜底）',
  `sample_code` varchar(32) NOT NULL COMMENT '样本编码（业务关联键）',
  `pay_mode` varchar(8) NOT NULL COMMENT 'dj趸交 fq分期',
  `contract_amount` decimal(18,2) DEFAULT NULL COMMENT '该样本合同金额',
  `installment_count` int(11) DEFAULT NULL COMMENT '分期期数',
  `installment_interval_months` decimal(10,2) DEFAULT NULL COMMENT '每期间隔（月）',
  `installment_start_date` char(8) DEFAULT NULL COMMENT '分期开始YYYYMMDD',
  `installment_advance_days` int(11) DEFAULT NULL COMMENT '第2期起应缴费提前天数',
  `installment_ratios` varchar(256) DEFAULT NULL COMMENT '各期缴费比例JSON数组',
  `lump_sum_deadline` char(8) DEFAULT NULL COMMENT '趸交截止时间YYYYMMDD',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint(6) NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_psp_tenant_protocol` (`tenant_no`,`collection_protocol_id`,`del_flag`),
  KEY `idx_psp_tenant_sample` (`tenant_no`,`sample_code`,`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采集协议单-按样本缴费方案';

-- ---------------------------------------------------------------------------
-- 3) 应缴批次
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `biz_protocol_payment_schedule` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `sample_payment_id` varchar(64) NOT NULL COMMENT '样本缴费方案ID',
  `collection_protocol_id` varchar(64) NOT NULL COMMENT '采集协议单ID',
  `sample_code` varchar(32) NOT NULL COMMENT '样本编码',
  `schedule_no` int(11) DEFAULT NULL COMMENT '期次序号',
  `due_amount` decimal(18,2) DEFAULT NULL COMMENT '应缴金额',
  `due_date` char(8) DEFAULT NULL COMMENT '应缴日期YYYYMMDD',
  `schedule_status` varchar(16) DEFAULT NULL COMMENT '批次状态 open/cleared/void',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint(6) NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_pps_tenant_protocol` (`tenant_no`,`collection_protocol_id`,`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采集协议单-应缴批次';

-- ---------------------------------------------------------------------------
-- 4) 转账笔（无退费字段）
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `biz_protocol_payment_transfer` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `schedule_id` varchar(64) NOT NULL COMMENT '应缴批次ID',
  `sample_payment_id` varchar(64) NOT NULL COMMENT '样本缴费方案ID',
  `collection_protocol_id` varchar(64) NOT NULL COMMENT '采集协议单ID',
  `sample_code` varchar(32) NOT NULL COMMENT '样本编码',
  `transfer_amount` decimal(18,2) DEFAULT NULL COMMENT '转账金额',
  `transfer_time` datetime DEFAULT NULL COMMENT '转账时间',
  `transfer_type` varchar(16) DEFAULT NULL COMMENT '转账方式',
  `transfer_attachments` text COMMENT '转账附件',
  `pay_status` varchar(32) NOT NULL DEFAULT 'dfj' COMMENT '缴费状态 dfj/jfqrz/yjf',
  `finance_confirm_attachments` text COMMENT '缴费财务确认附件',
  `finance_remark` varchar(512) DEFAULT NULL COMMENT '缴费财务确认备注',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint(6) NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_ppt_tenant_protocol` (`tenant_no`,`collection_protocol_id`,`del_flag`),
  KEY `idx_ppt_tenant_sample` (`tenant_no`,`sample_code`,`del_flag`),
  KEY `idx_ppt_tenant_pay_status` (`tenant_no`,`pay_status`,`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采集协议单-缴费转账笔';

-- ---------------------------------------------------------------------------
-- 5) 协议退费登记
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `biz_protocol_refund_record` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `collection_protocol_id` varchar(64) NOT NULL COMMENT '采集协议单ID',
  `refund_amount` decimal(18,2) DEFAULT NULL COMMENT '退费金额',
  `refund_time` datetime DEFAULT NULL COMMENT '退费时间',
  `refund_remark` varchar(512) DEFAULT NULL COMMENT '退费备注',
  `refund_attachments` text COMMENT '退费附件',
  `workflow_instance_id` varchar(64) DEFAULT NULL COMMENT 'OA工作流实例ID',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint(6) NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_prr_tenant_protocol` (`tenant_no`,`collection_protocol_id`,`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采集协议单-退费登记';

-- ---------------------------------------------------------------------------
-- 6) 预签约订单
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `biz_presign_order` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `order_date` char(8) DEFAULT NULL COMMENT '业务日期YYYYMMDD',
  `protocol_no` varchar(64) DEFAULT NULL COMMENT '协议编号',
  `mother_name` varchar(64) DEFAULT NULL COMMENT '母亲姓名',
  `expected_delivery_date` char(8) DEFAULT NULL COMMENT '预产期YYYYMMDD',
  `region_id` varchar(64) DEFAULT NULL COMMENT '采集地区ID',
  `hospital_id` varchar(64) DEFAULT NULL COMMENT '医院ID',
  `sample_collect_time` datetime DEFAULT NULL COMMENT '采集时间',
  `pay_mode` varchar(8) DEFAULT NULL COMMENT '整单缴费方式 dj/fq',
  `contract_amount` decimal(18,2) DEFAULT NULL COMMENT '整单合同金额',
  `collector_user_id` varchar(64) DEFAULT NULL COMMENT '采集专员用户ID',
  `sales_user_id` varchar(64) DEFAULT NULL COMMENT '市场专员用户ID',
  `region_manager_user_id` varchar(64) DEFAULT NULL COMMENT '采集经理/地区经理用户ID',
  `order_status` varchar(16) DEFAULT NULL COMMENT '订单状态 draft/active/converted/void',
  `converted_protocol_id` varchar(64) DEFAULT NULL COMMENT '转成的正式协议ID',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint(6) NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_po_tenant_protocol_no` (`tenant_no`,`protocol_no`,`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='预签约订单';

-- 已建表环境增量：采集专员
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'biz_presign_order' AND COLUMN_NAME = 'collector_user_id'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `biz_presign_order` ADD COLUMN `collector_user_id` VARCHAR(64) DEFAULT NULL COMMENT ''采集专员用户ID'' AFTER `contract_amount`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------------------
-- 7) 预签约样本类型
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `biz_presign_order_sample_type` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `presign_order_id` varchar(64) NOT NULL COMMENT '预签约订单ID',
  `sample_type_id` varchar(64) NOT NULL COMMENT '样本类型ID',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint(6) NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_post_presign` (`tenant_no`,`presign_order_id`,`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='预签约订单-样本类型';

-- ---------------------------------------------------------------------------
-- 8) 预签约意向金转账笔
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `biz_presign_transfer` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `presign_order_id` varchar(64) NOT NULL COMMENT '预签约订单ID',
  `transfer_amount` decimal(18,2) DEFAULT NULL COMMENT '转账金额',
  `transfer_time` datetime DEFAULT NULL COMMENT '转账时间',
  `transfer_type` varchar(16) DEFAULT NULL COMMENT '转账方式',
  `transfer_attachments` text COMMENT '转账附件',
  `pay_status` varchar(32) NOT NULL DEFAULT 'dfj' COMMENT '缴费状态 dfj/jfqrz/yjf',
  `finance_confirm_attachments` text COMMENT '缴费财务确认附件',
  `finance_remark` varchar(512) DEFAULT NULL COMMENT '缴费财务确认备注',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint(6) NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_pt_tenant_pay_status` (`tenant_no`,`pay_status`,`del_flag`),
  KEY `idx_pt_presign` (`tenant_no`,`presign_order_id`,`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='预签约订单-意向金转账笔';

-- ---------------------------------------------------------------------------
-- 9) 已建表校对规则对齐存量库 utf8mb4_unicode_ci（避免 JOIN 报 1267 mix of collations）
-- ---------------------------------------------------------------------------
ALTER TABLE `biz_protocol_sample_payment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `biz_protocol_payment_schedule` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `biz_protocol_payment_transfer` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `biz_protocol_refund_record` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `biz_presign_order` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `biz_presign_order_sample_type` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `biz_presign_transfer` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;