zkxw/docs/sql/migrations/2026-10-13-customer-service-tables.sql

-- 样本客户服务扩展表 + 租户字典（2026-10-13）
-- 对齐 docs/superpowers/specs/2026-10-13-customer-service-management-design.md

CREATE TABLE IF NOT EXISTS biz_sample_customer_service (
  id VARCHAR(64) NOT NULL COMMENT '主键ID',
  sample_code VARCHAR(32) NOT NULL COMMENT '样本编码',
  collection_protocol_id VARCHAR(64) DEFAULT NULL COMMENT '采集协议单ID冗余',
  sms_send_date DATE DEFAULT NULL COMMENT '短信发送日期',
  sms_send_status VARCHAR(64) DEFAULT NULL COMMENT '短信发送情况',
  microbe_followup_date DATE DEFAULT NULL COMMENT '微生物回访日期',
  microbe_followup_status VARCHAR(64) DEFAULT NULL COMMENT '微生物回访情况',
  fullmoon_followup_date DATE DEFAULT NULL COMMENT '满月回访时间',
  fullmoon_followup_status VARCHAR(64) DEFAULT NULL COMMENT '满月回访情况',
  household_registered VARCHAR(64) DEFAULT NULL COMMENT '是否上户口',
  einvoice_send_time DATETIME DEFAULT NULL COMMENT '电子发票发送时间',
  effective_address VARCHAR(512) 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_bscs_tenant_sample (tenant_no, sample_code),
  KEY idx_bscs_protocol (tenant_no, collection_protocol_id, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='样本客户服务扩展';
-- 注：uk_bscs_tenant_sample 不含 del_flag；del_flag=0 行唯一性由应用层保证（与 biz_sample_storage_cert_qc 一致）。

CREATE TABLE IF NOT EXISTS biz_customer_service_dict (
  id VARCHAR(64) NOT NULL COMMENT '主键ID',
  dict_type VARCHAR(64) NOT NULL COMMENT '字典类型',
  dict_value VARCHAR(128) NOT NULL COMMENT '选项值',
  is_system CHAR(1) NOT NULL DEFAULT '0' COMMENT '1预置 0手输',
  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_bcsd_tenant_type_value (tenant_no, dict_type, dict_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户服务字典';
-- 注：uk_bcsd_tenant_type_value 不含 del_flag；del_flag=0 行唯一性由应用层保证（与 biz_sample_storage_cert_qc 一致）。

-- 预置字典种子（tenant_no=zkxw，is_system=1；幂等 NOT EXISTS）

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'sms_send_status', '已发', '1', 'zkxw', 1, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'sms_send_status' AND dict_value = '已发' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'sms_send_status', '合并发', '1', 'zkxw', 2, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'sms_send_status' AND dict_value = '合并发' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'sms_send_status', '/', '1', 'zkxw', 3, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'sms_send_status' AND dict_value = '/' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'microbe_followup_status', '回访成功', '1', 'zkxw', 1, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'microbe_followup_status' AND dict_value = '回访成功' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'microbe_followup_status', '无人接听', '1', 'zkxw', 2, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'microbe_followup_status' AND dict_value = '无人接听' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'microbe_followup_status', '挂机', '1', 'zkxw', 3, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'microbe_followup_status' AND dict_value = '挂机' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'microbe_followup_status', '微信通知', '1', 'zkxw', 4, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'microbe_followup_status' AND dict_value = '微信通知' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'fullmoon_followup_status', '回访成功', '1', 'zkxw', 1, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'fullmoon_followup_status' AND dict_value = '回访成功' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'fullmoon_followup_status', '无人接听', '1', 'zkxw', 2, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'fullmoon_followup_status' AND dict_value = '无人接听' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'fullmoon_followup_status', '挂机', '1', 'zkxw', 3, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'fullmoon_followup_status' AND dict_value = '挂机' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'fullmoon_followup_status', '微信通知', '1', 'zkxw', 4, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'fullmoon_followup_status' AND dict_value = '微信通知' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'household_registered', '是', '1', 'zkxw', 1, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'household_registered' AND dict_value = '是' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'household_registered', '否', '1', 'zkxw', 2, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'household_registered' AND dict_value = '否' AND del_flag = 0
);

INSERT INTO biz_customer_service_dict (
  id, dict_type, dict_value, is_system, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT REPLACE(UUID(), '-', ''), 'household_registered', '未回复', '1', 'zkxw', 3, NULL,
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM biz_customer_service_dict
  WHERE tenant_no = 'zkxw' AND dict_type = 'household_registered' AND dict_value = '未回复' AND del_flag = 0
);