complaint-system/sql/ry_dm_complaint.sql

-- ============================================
-- 教育投诉平台 业务表建表脚本（达梦 DM8）
-- 模式：COMPLAINT（用 COMPLAINT 用户执行）
-- 说明：本脚本仅建"业务新增表"，若依框架自带表请执行 ry_dm_schema.sql
-- ============================================

-- 1、投诉工单主表
drop table if exists complaint_workorder;
create table complaint_workorder (
  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 0,
  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 '',
  primary key (id)
);
COMMENT ON TABLE complaint_workorder IS '投诉工单主表';
COMMENT ON COLUMN complaint_workorder.complaint_no IS '工单编号（TS+yyyyMMdd+4位流水）';
COMMENT ON COLUMN complaint_workorder.openid IS '投诉人微信OpenID';
COMMENT ON COLUMN complaint_workorder.name IS '举报人真实姓名';
COMMENT ON COLUMN complaint_workorder.phone IS '联系电话（仅校方联系）';
COMMENT ON COLUMN complaint_workorder."identity" IS '举报人身份（家长/教职工/学生/社会人员）';
COMMENT ON COLUMN complaint_workorder.city IS '地市';
COMMENT ON COLUMN complaint_workorder.district IS '县区（SSQYDM名称）';
COMMENT ON COLUMN complaint_workorder.unit_uid IS '学校机构UID（东软，分派核心）';
COMMENT ON COLUMN complaint_workorder.school_name IS '学校名称';
COMMENT ON COLUMN complaint_workorder.school_type IS '学校类别';
COMMENT ON COLUMN complaint_workorder.category IS '投诉分类（字典complaint_category）';
COMMENT ON COLUMN complaint_workorder.content IS '投诉内容（≤500字）';
COMMENT ON COLUMN complaint_workorder.images IS '附件图片（JSON数组，≤5张）';
COMMENT ON COLUMN complaint_workorder.status IS '状态：0待受理 1处理中 2已办结 3已驳回 5复核中';
COMMENT ON COLUMN complaint_workorder.investigation IS '调查经过（当前值）';
COMMENT ON COLUMN complaint_workorder.conclusion IS '处置结论（当前值）';
COMMENT ON COLUMN complaint_workorder.process_images IS '处置佐证附件（JSON）';
COMMENT ON COLUMN complaint_workorder.reject_reason IS '驳回理由';
COMMENT ON COLUMN complaint_workorder.accept_time IS '受理时间';
COMMENT ON COLUMN complaint_workorder.close_time IS '办结时间';
create unique index uk_workorder_no on complaint_workorder (complaint_no);
create index idx_workorder_openid on complaint_workorder (openid);
create index idx_workorder_unit on complaint_workorder (unit_uid);
create index idx_workorder_status on complaint_workorder (status);

-- 2、处置留痕表
drop table if exists complaint_process_log;
create table complaint_process_log (
  id               bigint        not null IDENTITY(1,1),
  complaint_no     varchar(32)   not null,
  "action"           varchar(20)   default '',
  investigation    varchar(2000) default '',
  conclusion       varchar(2000) default '',
  process_images   varchar(2000) default '',
  reject_reason    varchar(1000) default '',
  operator_id      bigint        default 0,
  operator_name    varchar(30)   default '',
  create_time      timestamp,
  primary key (id)
);
COMMENT ON TABLE complaint_process_log IS '处置留痕表（受理/暂存/办结/驳回/复核）';
COMMENT ON COLUMN complaint_process_log."action" IS '动作：受理/暂存/办结/驳回/复核';
COMMENT ON COLUMN complaint_process_log.operator_id IS '操作管理员ID（sys_user或complaint_admin）';
COMMENT ON COLUMN complaint_process_log.operator_name IS '操作人姓名';
create index idx_process_no on complaint_process_log (complaint_no);

-- 3、消息推送日志表
drop table if exists complaint_message_log;
create table complaint_message_log (
  id               bigint        not null IDENTITY(1,1),
  complaint_no     varchar(32)   default '',
  msg_type         varchar(10)   default '',
  receiver         varchar(500)  default '',
  templet_id       varchar(50)   default '',
  content          varchar(2000) default '',
  msg_id           varchar(64)   default '',
  "result"           tinyint       default 0,
  error_msg        varchar(500)  default '',
  create_time      timestamp,
  primary key (id)
);
COMMENT ON TABLE complaint_message_log IS '消息推送日志表（短信/公众号）';
COMMENT ON COLUMN complaint_message_log.msg_type IS '消息类型：sms/wechat';
COMMENT ON COLUMN complaint_message_log.receiver IS '接收人信息（姓名/手机号/学工号）';
COMMENT ON COLUMN complaint_message_log.templet_id IS '模板id（短信templet_id/公众号wechat_templet_id）';
COMMENT ON COLUMN complaint_message_log.msg_id IS '统一通讯平台返回的消息id';
COMMENT ON COLUMN complaint_message_log."result" IS '推送结果：1成功 0失败';
create index idx_msg_no on complaint_message_log (complaint_no);
create index idx_msg_time on complaint_message_log (create_time);

-- 4、免登令牌表
drop table if exists complaint_token;
create table complaint_token (
  id               bigint        not null IDENTITY(1,1),
  token            varchar(64)   not null,
  complaint_no     varchar(32)   default '',
  unit_uid         varchar(64)   default '',
  expire_time      timestamp,
  used             tinyint       default 0,
  create_time      timestamp,
  primary key (id)
);
COMMENT ON TABLE complaint_token IS '免登令牌表';
COMMENT ON COLUMN complaint_token.token IS '随机token（UUID）';
COMMENT ON COLUMN complaint_token.unit_uid IS '绑定学校（防越权）';
COMMENT ON COLUMN complaint_token.expire_time IS '过期时间（建议72小时）';
COMMENT ON COLUMN complaint_token.used IS '是否已用：0未用 1已用（一次性）';
create unique index uk_token on complaint_token (token);

-- 5、学校机构同步缓存表（UNITINFO_1）
drop table if exists unit_school;
create table unit_school (
  unit_uid         varchar(64)   not null,
  unit_name        varchar(200)  default '',
  ssqydm           varchar(20)   default '',
  ssqymc           varchar(50)   default '',
  dwlx             varchar(10)   default '',
  dwlxmc           varchar(50)   default '',
  xddm             varchar(10)   default '',
  yxbs             varchar(2)    default '1',
  data_update_time timestamp,
  data_update_flog char(1)       default 'I',
  sync_time        timestamp,
  primary key (unit_uid)
);
COMMENT ON TABLE unit_school IS '学校机构同步缓存表（东软UNITINFO_1）';
COMMENT ON COLUMN unit_school.unit_uid IS '机构UID（主键）';
COMMENT ON COLUMN unit_school.unit_name IS '机构名称';
COMMENT ON COLUMN unit_school.ssqydm IS '所属区域代码（HBW/WD/HN）';
COMMENT ON COLUMN unit_school.ssqymc IS '所属区域名称';
COMMENT ON COLUMN unit_school.dwlx IS '单位类型代码（DWLX）';
COMMENT ON COLUMN unit_school.dwlxmc IS '单位类型名称';
COMMENT ON COLUMN unit_school.xddm IS '学段代码（XDDM）';
COMMENT ON COLUMN unit_school.yxbs IS '有效标识：0无效 1有效';
COMMENT ON COLUMN unit_school.data_update_flog IS '增量标识：I新增 U更新 D删除';
create index idx_unit_dwlx on unit_school (dwlx);
create index idx_unit_ssqy on unit_school (ssqydm);

-- 6、学校管理员表（学校提供名单，手动维护）
drop table if exists complaint_admin;
create table complaint_admin (
  id               bigint        not null IDENTITY(1,1),
  unit_uid         varchar(64)   default '',
  admin_name       varchar(30)   default '',
  phone            varchar(11)   default '',
  staff_no         varchar(32)   default '',
  "identity"         varchar(20)   default '',
  status           tinyint       default 1,
  create_by        varchar(64)   default '',
  create_time      timestamp,
  update_by        varchar(64)   default '',
  update_time      timestamp,
  primary key (id)
);
COMMENT ON TABLE complaint_admin IS '学校管理员表（学校提供名单，后台手动维护）';
COMMENT ON COLUMN complaint_admin.unit_uid IS '所属学校UID';
COMMENT ON COLUMN complaint_admin.admin_name IS '管理员姓名';
COMMENT ON COLUMN complaint_admin.phone IS '手机号（短信接收，必填）';
COMMENT ON COLUMN complaint_admin.staff_no IS '学工号（公众号消息接收，选填）';
COMMENT ON COLUMN complaint_admin."identity" IS '职务（校长/主任/经办人等）';
COMMENT ON COLUMN complaint_admin.status IS '1启用 0停用';
create index idx_admin_unit on complaint_admin (unit_uid);
create index idx_admin_phone on complaint_admin (phone);

-- 7、异议复核表
drop table if exists complaint_review;
create table complaint_review (
  id               bigint        not null IDENTITY(1,1),
  complaint_no     varchar(32)   not null,
  openid           varchar(64)   default '',
  reason           varchar(1000) default '',
  images           varchar(2000) default '',
  status           tinyint       default 0,
  review_result    varchar(1000) default '',
  review_by        varchar(30)   default '',
  review_time      timestamp,
  create_time      timestamp,
  primary key (id)
);
COMMENT ON TABLE complaint_review IS '异议复核表';
COMMENT ON COLUMN complaint_review.status IS '0待复核 1维持原判 2复核通过（重新处置）';
COMMENT ON COLUMN complaint_review.review_result IS '复核处理意见';
COMMENT ON COLUMN complaint_review.review_by IS '复核人';
create index idx_review_no on complaint_review (complaint_no);

-- 8、数据同步日志表
drop table if exists data_sync_log;
create table data_sync_log (
  id               bigint        not null IDENTITY(1,1),
  sync_type        varchar(20)   default '',
  interface_name   varchar(50)   default '',
  total            int           default 0,
  success          int           default 0,
  fail             int           default 0,
  status           tinyint       default 0,
  error_msg        varchar(1000) default '',
  create_time      timestamp,
  primary key (id)
);
COMMENT ON TABLE data_sync_log IS '数据同步日志表';
COMMENT ON COLUMN data_sync_log.sync_type IS '同步类型：init全量/daily增量';
COMMENT ON COLUMN data_sync_log.interface_name IS '接口名（UNITINFO_1等）';
COMMENT ON COLUMN data_sync_log.status IS '0失败 1成功';

-- ============================================
-- 字典初始化（投诉业务字典）
-- ============================================
SET IDENTITY_INSERT sys_dict_type ON;
insert into sys_dict_type (dict_id, dict_name, dict_type, status, create_by, create_time, remark)
values (100, '投诉分类', 'complaint_category', '0', 'admin', sysdate, '教育投诉分类');
insert into sys_dict_type (dict_id, dict_name, dict_type, status, create_by, create_time, remark)
values (101, '学校类别', 'school_type', '0', 'admin', sysdate, '学校类别（映射东软DWLX）');
SET IDENTITY_INSERT sys_dict_type OFF;

SET IDENTITY_INSERT sys_dict_data ON;
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (500, 1, '违规收费', '违规收费', 'complaint_category', 'N', '0', 'admin', sysdate, '');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (501, 2, '食品安全', '食品安全', 'complaint_category', 'N', '0', 'admin', sysdate, '');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (502, 3, '补课违规', '补课违规', 'complaint_category', 'N', '0', 'admin', sysdate, '');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (503, 4, '师德师风', '师德师风', 'complaint_category', 'N', '0', 'admin', sysdate, '');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (504, 5, '招生违规', '招生违规', 'complaint_category', 'N', '0', 'admin', sysdate, '');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (505, 6, '其他', '其他', 'complaint_category', 'N', '0', 'admin', sysdate, '');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (510, 1, '幼儿园', '幼儿园', 'school_type', 'N', '0', 'admin', sysdate, 'DWLX=11');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (511, 2, '小学', '小学', 'school_type', 'N', '0', 'admin', sysdate, 'DWLX=21');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (512, 3, '初级中学', '初级中学', 'school_type', 'N', '0', 'admin', sysdate, 'DWLX=31');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (513, 4, '普通高中', '普通高中', 'school_type', 'N', '0', 'admin', sysdate, 'DWLX=34');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (514, 5, '完全中学', '完全中学', 'school_type', 'N', '0', 'admin', sysdate, '');
insert into sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, is_default, status, create_by, create_time, remark)
values (515, 6, '职业院校', '职业院校', 'school_type', 'N', '0', 'admin', sysdate, 'DWLX=36');
SET IDENTITY_INSERT sys_dict_data OFF;


commit;