zkxw/docs/sql/migrations/2026-10-13-sample-storage-cert-insurance.sql

-- 样本级储存证 / 保险 + 协议级数据迁移（2026-10-13）
-- 对齐 docs/superpowers/specs/2026-10-13-customer-service-management-design.md

CREATE TABLE IF NOT EXISTS biz_sample_storage_cert (
  id VARCHAR(64) NOT NULL COMMENT '主键ID',
  sample_code VARCHAR(32) NOT NULL COMMENT '样本编码',
  collection_protocol_id VARCHAR(64) DEFAULT NULL COMMENT '采集协议单ID冗余',
  make_status CHAR(1) DEFAULT '0' COMMENT '制作状态 0-未制作 1-已制作',
  cert_image_urls VARCHAR(4000) DEFAULT NULL COMMENT '证书图片URLs',
  make_date CHAR(8) DEFAULT NULL COMMENT '制作日期YYYYMMDD',
  handle_time DATETIME DEFAULT NULL COMMENT '办理时间',
  handler_user_id VARCHAR(64) DEFAULT NULL COMMENT '办理人用户ID',
  mail_cert_time DATETIME DEFAULT NULL COMMENT '邮寄储存证时间',
  tenant_no VARCHAR(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  order_num INT 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 COMMENT '创建时间',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人id',
  update_time DATETIME NOT NULL COMMENT '更新时间',
  del_flag SMALLINT NOT NULL DEFAULT 0 COMMENT '删除标记',
  PRIMARY KEY (id),
  UNIQUE KEY uk_bssc_tenant_sample (tenant_no, sample_code),
  KEY idx_bssc_protocol (tenant_no, collection_protocol_id, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='样本储存证办理';
-- 注：uk_bssc_tenant_sample 不含 del_flag；del_flag=0 行唯一性由应用层保证（与 biz_sample_storage_cert_qc 一致）。

CREATE TABLE IF NOT EXISTS biz_sample_insurance (
  id VARCHAR(64) NOT NULL COMMENT '主键ID',
  sample_code VARCHAR(32) NOT NULL COMMENT '样本编码',
  collection_protocol_id VARCHAR(64) DEFAULT NULL COMMENT '采集协议单ID冗余',
  todo_status CHAR(1) NOT NULL DEFAULT '0' COMMENT '1已办保险 0未办保险',
  handle_date CHAR(8) DEFAULT NULL COMMENT '办理日期',
  handle_time DATETIME DEFAULT NULL COMMENT '办理时间',
  handler_user_id VARCHAR(64) DEFAULT NULL COMMENT '办理人用户ID',
  handle_remark VARCHAR(512) DEFAULT NULL COMMENT '办理备注',
  insurance_attachments VARCHAR(4000) DEFAULT NULL COMMENT '保险附件逗号分隔',
  insurance_company_id VARCHAR(64) DEFAULT NULL COMMENT '保险公司ID',
  insurance_company_name VARCHAR(200) DEFAULT NULL COMMENT '保险公司名称（冗余快照）',
  insurance_type VARCHAR(200) DEFAULT NULL COMMENT '保险险种',
  insurance_policy_no VARCHAR(100) DEFAULT NULL COMMENT '保险单号',
  insurance_effective_date CHAR(8) DEFAULT NULL COMMENT '保险单生效时间YYYYMMDD',
  insurance_expiry_date CHAR(8) DEFAULT NULL COMMENT '保险单失效时间YYYYMMDD',
  insurance_subscribed CHAR(1) DEFAULT '0' COMMENT '是否已订购保险',
  insurance_fee DECIMAL(18, 2) DEFAULT NULL COMMENT '保单费用（元）',
  policy_send_time DATETIME DEFAULT NULL COMMENT '保单发送时间',
  tenant_no VARCHAR(64) NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  order_num INT 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 COMMENT '创建时间',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人id',
  update_time DATETIME NOT NULL COMMENT '更新时间',
  del_flag SMALLINT NOT NULL DEFAULT 0 COMMENT '删除标记',
  PRIMARY KEY (id),
  UNIQUE KEY uk_bsi_tenant_sample (tenant_no, sample_code),
  KEY idx_bsi_protocol (tenant_no, collection_protocol_id, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='样本保险办理';
-- 注：uk_bsi_tenant_sample 不含 del_flag；del_flag=0 行唯一性由应用层保证（与 biz_sample_storage_cert_qc 一致）。

-- 迁移：协议级储存证 → 协议下每个未删样本一行（幂等 NOT EXISTS）

INSERT INTO biz_sample_storage_cert (
  id, sample_code, collection_protocol_id, make_status, cert_image_urls,
  make_date, handle_time, handler_user_id, mail_cert_time,
  tenant_no, order_num, remark, create_by, create_dept_id, create_time,
  update_by, update_time, del_flag
)
SELECT
  -- MySQL 5.7 同语句批量 UUID() 可能撞主键，用业务键派生稳定唯一 id
  MD5(CONCAT('bssc#', c.tenant_no, '#', ps.sample_code, '#', c.id, '#', ps.id)),
  ps.sample_code,
  c.collection_protocol_id,
  c.make_status,
  c.cert_image_urls,
  c.make_date,
  c.handle_time,
  c.handler_user_id,
  NULL,
  c.tenant_no,
  c.order_num,
  c.remark,
  c.create_by,
  c.create_dept_id,
  NOW(),
  c.update_by,
  NOW(),
  0
FROM biz_protocol_storage_cert c
INNER JOIN biz_protocol_sample ps
  ON (ps.collection_protocol_id COLLATE utf8mb4_unicode_ci) = (c.collection_protocol_id COLLATE utf8mb4_unicode_ci)
 AND (ps.tenant_no COLLATE utf8mb4_unicode_ci) = (c.tenant_no COLLATE utf8mb4_unicode_ci)
 AND ps.del_flag = 0
WHERE c.del_flag = 0
  AND ps.sample_code IS NOT NULL
  AND ps.sample_code <> ''
  AND NOT EXISTS (
    SELECT 1 FROM biz_sample_storage_cert t
    WHERE (t.tenant_no COLLATE utf8mb4_unicode_ci) = (c.tenant_no COLLATE utf8mb4_unicode_ci)
      AND (t.sample_code COLLATE utf8mb4_unicode_ci) = (ps.sample_code COLLATE utf8mb4_unicode_ci)
      AND t.del_flag = 0
  );

-- 迁移：协议级满月保险 → 协议下每个未删样本一行（幂等 NOT EXISTS）

INSERT INTO biz_sample_insurance (
  id, sample_code, collection_protocol_id, todo_status, handle_date, handle_time,
  handler_user_id, handle_remark, insurance_attachments,
  insurance_company_id, insurance_company_name, insurance_type, insurance_policy_no,
  insurance_effective_date, insurance_expiry_date, insurance_subscribed, insurance_fee,
  policy_send_time,
  tenant_no, order_num, remark, create_by, create_dept_id, create_time,
  update_by, update_time, del_flag
)
SELECT
  MD5(CONCAT('bsi#', t.tenant_no, '#', ps.sample_code, '#', t.id, '#', ps.id)),
  ps.sample_code,
  t.collection_protocol_id,
  t.todo_status,
  t.handle_date,
  t.handle_time,
  t.handler_user_id,
  t.handle_remark,
  t.insurance_attachments,
  t.insurance_company_id,
  t.insurance_company_name,
  t.insurance_type,
  t.insurance_policy_no,
  t.insurance_effective_date,
  t.insurance_expiry_date,
  t.insurance_subscribed,
  t.insurance_fee,
  NULL,
  t.tenant_no,
  t.order_num,
  t.remark,
  t.create_by,
  t.create_dept_id,
  NOW(),
  t.update_by,
  NOW(),
  0
FROM biz_protocol_fullmoon_todo t
INNER JOIN biz_protocol_sample ps
  ON (ps.collection_protocol_id COLLATE utf8mb4_unicode_ci) = (t.collection_protocol_id COLLATE utf8mb4_unicode_ci)
 AND (ps.tenant_no COLLATE utf8mb4_unicode_ci) = (t.tenant_no COLLATE utf8mb4_unicode_ci)
 AND ps.del_flag = 0
WHERE t.del_flag = 0
  AND ps.sample_code IS NOT NULL
  AND ps.sample_code <> ''
  AND NOT EXISTS (
    SELECT 1 FROM biz_sample_insurance s
    WHERE (s.tenant_no COLLATE utf8mb4_unicode_ci) = (t.tenant_no COLLATE utf8mb4_unicode_ci)
      AND (s.sample_code COLLATE utf8mb4_unicode_ci) = (ps.sample_code COLLATE utf8mb4_unicode_ci)
      AND s.del_flag = 0
  );