complaint-system/sql/alter_auto_dispatch_schema_safe.sql

-- ============================================
-- 自动分派功能 - 数据库扩改动议脚本（达梦 DM8）- V2.0
-- 安全版：自动检测字段是否已存在，避免重复创建
-- ============================================

SET SERVEROUTPUT ON;

-- ====================================================
-- 1. complaint_workorder 表扩展现有结构
-- ====================================================

-- 扩展字段 1: dispatch_note (仅当不存在时添加)
DECLARE
    v_count INT;
BEGIN
    SELECT COUNT(*) INTO v_count 
    FROM dba_tab_columns 
    WHERE table_name = 'COMPLAINT_WORKORDER' 
      AND column_name = 'DISPATCH_NOTE';
    
    IF v_count = 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE complaint_workorder ADD dispatch_note VARCHAR(200)';
        DBMS_OUTPUT.PUT_LINE('✓ dispatch_note 字段已创建');
    ELSE
        DBMS_OUTPUT.PUT_LINE('⚠️ dispatch_note 字段已存在，跳过');
    END IF;
END;
/

COMMENT ON COLUMN complaint_workorder.dispatch_note IS '系统分派备注';

-- 扩展字段 2: is_archived (仅当不存在时添加)
DECLARE
    v_count INT;
BEGIN
    SELECT COUNT(*) INTO v_count 
    FROM dba_tab_columns 
    WHERE table_name = 'COMPLAINT_WORKORDER' 
      AND column_name = 'IS_ARCHIVED';
    
    IF v_count = 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE complaint_workorder ADD is_archived CHAR(1) DEFAULT ''0'' ';
        DBMS_OUTPUT.PUT_LINE('✓ is_archived 字段已创建');
    ELSE
        DBMS_OUTPUT.PUT_LINE('⚠️ is_archived 字段已存在，跳过');
    END IF;
END;
/

COMMENT ON COLUMN complaint_workorder.is_archived IS '是否已归档（0 否 1 是）';

-- 扩展字段 3: archive_time (仅当不存在时添加)
DECLARE
    v_count INT;
BEGIN
    SELECT COUNT(*) INTO v_count 
    FROM dba_tab_columns 
    WHERE table_name = 'COMPLAINT_WORKORDER' 
      AND column_name = 'ARCHIVE_TIME';
    
    IF v_count = 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE complaint_workorder ADD archive_time TIMESTAMP';
        DBMS_OUTPUT.PUT_LINE('✓ archive_time 字段已创建');
    ELSE
        DBMS_OUTPUT.PUT_LINE('⚠️ archive_time 字段已存在，跳过');
    END IF;
END;
/

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

-- ====================================================
-- 4. 最终校验输出
-- ====================================================
SELECT '==== 扩改进度总结 ==== ' as message;

SELECT 
  CASE WHEN COUNT(*) > 0 THEN '✓ dispatch_note 已就绪' ELSE '✗ dispatch_note 缺失' END as check1
FROM dba_tab_columns WHERE table_name = 'COMPLAINT_WORKORDER' AND column_name = 'DISPATCH_NOTE';

SELECT 
  CASE WHEN COUNT(*) > 0 THEN '✓ is_archived 已就绪' ELSE '✗ is_archived 缺失' END as check2
FROM dba_tab_columns WHERE table_name = 'COMPLAINT_WORKORDER' AND column_name = 'IS_ARCHIVED';

SELECT 
  CASE WHEN COUNT(*) > 0 THEN '✓ archive_time 已就绪' ELSE '✗ archive_time 缺失' END as check3
FROM dba_tab_columns WHERE table_name = 'COMPLAINT_WORKORDER' AND column_name = 'ARCHIVE_TIME';

SELECT 
  CASE WHEN COUNT(*) > 0 THEN '✓ complaint_archive 表已就绪' ELSE '✗ complaint_archive 表缺失' END as check4
FROM dba_tables WHERE table_name = 'COMPLAINT_ARCHIVE';

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