-- ============================================
-- 增量扩改动议脚本（仅创建新表和新视图）
-- 适用场景：主表字段已存在，只需补充归档表
-- ============================================

-- ====================================================
-- 1. 创建投诉工单归档表（如果不存在）
-- ====================================================
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);

-- ====================================================
-- 2. 重复检测辅助视图
-- ====================================================
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 create_time >= ADD_MONTHS(CURRENT_DATE, -1);

COMMIT;