complaint-system/sql/alter_auto_dispatch_schema.sql

-- ============================================
-- 自动分派功能 - 数据库扩改动议脚本（达梦 DM8）
-- 执行前请备份当前数据！
-- ============================================

-- ====================================================
-- 1. complaint_workorder 表扩展现有结构
-- ====================================================
COMMENT ON COLUMN complaint_workorder.status IS '状态：-1 待分派 0 待受理 1 处理中 2 已办结 3 已驳回 5 复核中';

-- 扩展字段 1: 系统分派备注（记录自动分派信息）
ALTER TABLE complaint_workorder ADD dispatch_note VARCHAR(200);
COMMENT ON COLUMN complaint_workorder.dispatch_note IS '系统分派备注';

-- 扩展字段 2: 是否已归档（新增列）
ALTER TABLE complaint_workorder ADD is_archived CHAR(1) DEFAULT '0';
COMMENT ON COLUMN complaint_workorder.is_archived IS '是否已归档（0 否 1 是）';

-- 扩展字段 3: 归档时间（新增列）
ALTER TABLE complaint_workorder ADD archive_time TIMESTAMP;
COMMENT ON COLUMN complaint_workorder.archive_time IS '归档时间（T+90 天后填充）';

-- ====================================================
-- 2. 创建投诉工单归档表（物理隔离历史数据）
-- ====================================================
DROP TABLE IF EXISTS complaint_archive;
CREATE TABLE complaint_archive (
  id               BIGINT        NOT NULL IDENTITY(1,1),
  complaint_no     VARCHAR(32)   NOT NULL,
  openid           VARCHAR(64)   DEFAULT '',
  name             VARCHAR(30)   DEFAULT '',
  phone            VARCHAR(11)   DEFAULT '',
  "identity"       VARCHAR(10)   DEFAULT '',
  city             VARCHAR(30)   DEFAULT '',
  district         VARCHAR(30)   DEFAULT '',
  unit_uid         VARCHAR(64)   DEFAULT '',
  school_name      VARCHAR(100)  DEFAULT '',
  school_type      VARCHAR(20)   DEFAULT '',
  category         VARCHAR(30)   DEFAULT '',
  content          VARCHAR(1000) DEFAULT '',
  images           VARCHAR(2000) DEFAULT '',
  status           TINYINT       DEFAULT 2,
  investigation    VARCHAR(2000) DEFAULT '',
  conclusion       VARCHAR(2000) DEFAULT '',
  process_images   VARCHAR(2000) DEFAULT '',
  reject_reason    VARCHAR(1000) DEFAULT '',
  accept_time      TIMESTAMP,
  close_time       TIMESTAMP,
  create_by        VARCHAR(64)   DEFAULT '',
  create_time      TIMESTAMP,
  update_by        VARCHAR(64)   DEFAULT '',
  update_time      TIMESTAMP,
  remark           VARCHAR(500)  DEFAULT '',
  original_submit_time TIMESTAMP NOT NULL,
  archive_time     TIMESTAMP     NOT NULL,
  PRIMARY KEY (id)
);

COMMENT ON TABLE complaint_archive IS '投诉工单归档表（T+90 天自动迁移）';
COMMENT ON COLUMN complaint_archive.original_submit_time IS '原始提交时间（查询优化用）';
COMMENT ON COLUMN complaint_archive.archive_time IS '归档操作时间';

-- 归档表索引优化
CREATE INDEX idx_archive_no ON complaint_archive (complaint_no);
CREATE INDEX idx_archive_unit ON complaint_archive (unit_uid);
CREATE INDEX idx_archive_status ON complaint_archive (status);
CREATE INDEX idx_archive_create ON complaint_archive (original_submit_time);

-- ====================================================
-- 3. 重复检测辅助视图（提升查询性能）
-- ====================================================
DROP VIEW IF EXISTS v_recent_complaints;
CREATE VIEW v_recent_complaints AS
SELECT 
  phone,
  unit_uid,
  LEFT(content, 50) AS content_snippet,
  create_time,
  complaint_no
FROM complaint_workorder
WHERE del_flag = '0' AND create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- ====================================================
-- 4. 校验检查点（防止字段已存在）
-- ====================================================
SELECT 
  CASE WHEN COUNT(*) > 0 THEN '⚠️ dispatch_note 字段已存在' ELSE '✓ dispatch_note 字段不存在' END as check1
FROM syscolumns WHERE name = 'dispatch_note';

SELECT 
  CASE WHEN COUNT(*) > 0 THEN '⚠️ is_archived 字段已存在' ELSE '✓ is_archived 字段不存在' END as check2
FROM syscolumns WHERE name = 'is_archived';

SELECT 
  CASE WHEN COUNT(*) > 0 THEN '✓ complaint_archive 表已存在' ELSE '✗ complaint_archive 表不存在' END as check3
FROM sysschema s, sysobjects o WHERE s.schemaid = o.schemaid AND o.name = 'complaint_archive';

-- ====================================================
-- 结束
-- ====================================================
COMMIT;