complaint-system/sql/alter_auto_dispatch_schema_v4.sql

-- ============================================
-- 自动分派功能 - 数据库扩改动议脚本（达梦 DM8）- V4.0
-- 最简单版本：直接执行所有操作
-- ====================================================

-- ====================================================
-- 1. complaint_workorder 表扩展字段
-- ====================================================
ALTER TABLE complaint_workorder ADD dispatch_note VARCHAR(200);
COMMENT ON COLUMN complaint_workorder.dispatch_note IS '系统分派备注';

ALTER TABLE complaint_workorder ADD is_archived CHAR(1) DEFAULT '0';
COMMENT ON COLUMN complaint_workorder.is_archived IS '是否已归档（0 否 1 是）';

ALTER TABLE complaint_workorder ADD archive_time TIMESTAMP;
COMMENT ON COLUMN complaint_workorder.archive_time IS '归档时间（T+90 天后填充）';

-- 更新 status 列注释
COMMENT ON COLUMN complaint_workorder.status IS '状态：-1 待分派 0 待受理 1 处理中 2 已办结 3 已驳回 5 复核中';

-- ====================================================
-- 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);

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