-- ----------------------------
-- 工单类别表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `__PREFIX__workorder_category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `pid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上级类别',
  `name` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '名称',
  `logo_image` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '图片路径',
  `desc` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '描述',
  `we_ids` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '关联工程师',
  `kbs_ids` varchar(200) NOT NULL DEFAULT '' COMMENT '关联知识点',
  `wo_number` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '工单数',
  `wo_complete_number` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '工单完成数',
  `status` enum('1','0') CHARACTER SET utf8 NOT NULL DEFAULT '1' COMMENT '状态:0=隐藏,1=正常',
  `weigh` int(10) NOT NULL DEFAULT '0' COMMENT '权重',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  `deletetime` int(10) unsigned DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='工单类别表';
-- ----------------------------
-- 插入默认分类
-- ----------------------------
BEGIN;
INSERT IGNORE INTO `__PREFIX__workorder_category` VALUES ('1', '0', '服务', '/assets/addons/workorder/img/fff_logo.png', '对指定服务进行提问', '1', '', '0', '0', '1', '1', '1597409892', null);
INSERT IGNORE INTO `__PREFIX__workorder_category` VALUES ('2', '0', '产品', '/assets/addons/workorder/img/fff_logo.png', '对指定产品进行提问', '1', '', '0', '0', '1', '2', '1597409909', null);
INSERT IGNORE INTO `__PREFIX__workorder_category` VALUES ('3', '1', '服务一', '/assets/addons/workorder/img/fff_logo.png', '我是服务一的介绍', '1', '1,2', '0', '0', '1', '3', '1597409964', null);
INSERT IGNORE INTO `__PREFIX__workorder_category` VALUES ('4', '2', '产品一', '/assets/addons/workorder/img/fff_logo.png', '我是产品一的介绍', '1', '1,2', '0', '0', '1', '4', '1597409946', null);
INSERT IGNORE INTO `__PREFIX__workorder_category` VALUES ('5', '2', '产品二', '/assets/addons/workorder/img/fff_logo.png', '我是产品二的介绍', '1', '1,2', '0', '0', '1', '5', '1597409953', null);
COMMIT;

-- ----------------------------
-- 工程师表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `__PREFIX__workorder_engineers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `class` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '分类:0=内部工程师,1=服务商工程师',
  `title` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '职称',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '关联用户',
  `qq` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT 'QQ号',
  `wx` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '微信号',
  `openid` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '公众号openid(预留)',
  `introduce` varchar(200) NOT NULL DEFAULT '' COMMENT '介绍',
  `status` enum('1','0') CHARACTER SET utf8 NOT NULL DEFAULT '1' COMMENT '状态:0=隐藏,1=正常',
  `lastreceivetime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上次接单时间',
  `work_order_quantity` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '接单量(含经手)',
  `avg_response_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '平均响应时间(秒)',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  `deletetime` int(10) unsigned DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='工程师表';
-- ----------------------------
-- 插入默认工程师
-- ----------------------------
BEGIN;
INSERT IGNORE INTO `__PREFIX__workorder_engineers` VALUES ('1', '0', '工程师小A', '1', '小A的QQ', '小A的微信', '', '小A的介绍', '1', '0', '1602613427', null);
COMMIT;

-- ----------------------------
-- 工单评价表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `__PREFIX__workorder_evaluate` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `order_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '工单',
  `category_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '工单分类',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户',
  `stars` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '星级:1=一星,2=二星,3=三星,4=四星,5=五星',
  `solved` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否解决:0=未解决,1=已解决',
  `feedback` varchar(200) NOT NULL DEFAULT '' COMMENT '反馈内容',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '评价时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='工单评价表';

-- ----------------------------
-- 表单字段表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `__PREFIX__workorder_fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `position` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '字段位置:0=工单提交表单,1=用户反馈表单,2=工程师回复表单',
  `name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '名称',
  `title` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '标题',
  `type_list` enum('city','radio','checkbox','switch','files','file','images','image','selects','select','datetime','time','date','number','editor','text','textarea','string') CHARACTER SET utf8 NOT NULL DEFAULT 'text' COMMENT '类型:string=输入框,textarea=多行输入框,text=文本,editor=富文本,number=数字,date=日期,time=时间,datetime=日期时间,select=列表,selects=多选列表,image=图片,images=图片(多),file=文件,files=文件(多),switch=开关,checkbox=复选框,radio=单选框,city=城市选择框',
  `decimals` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '小数点长度',
  `minimum` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '最少选择',
  `maximum` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '最大选择',
  `default` varchar(100) CHARACTER SET utf8 DEFAULT '' COMMENT '默认值',
  `values` text CHARACTER SET utf8 COMMENT '条目列表',
  `rule` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '验证规则',
  `errormsg` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '错误消息',
  `notice` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '提示消息',
  `field_length` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '字段长度',
  `isfilter` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否列表筛选:0=否,1=是',
  `isbasicinfo` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '基本信息字段:0=否,1=是',
  `status` enum('1','0') CHARACTER SET utf8 NOT NULL DEFAULT '1' COMMENT '状态:0=隐藏,1=正常',
  `extend` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '扩展属性',
  `weigh` int(10) NOT NULL DEFAULT '0' COMMENT '权重',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='工单字段管理';
-- ----------------------------
-- 插入默认字段
-- ----------------------------
BEGIN;
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('1', '0', 'title', '标题', 'string', '0', '0', '0', '', '', 'required', '', '请输入标题', '255', '1', '1', '1', '', '99', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('2', '0', 'describe', '描述', 'textarea', '0', '0', '0', '', '', 'required', '', '请输入描述', '500', '0', '0', '1', '', '98', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('3', '0', 'confidential', '机密信息', 'textarea', '0', '0', '0', '', '', '', '', '请在此处填写账号、密码等机密信息，机密信息不在任何情况公开', '200', '0', '0', '1', '', '97', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('4', '0', 'attachment', '附件', 'files', '0', '0', '5', '', '', '', '', '可通过QQ截图后在此粘贴上传图片', '300', '0', '0', '1', '', '96', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('5', '0', 'mobile', '手机', 'string', '0', '0', '0', '', '', 'required,mobile', '', '紧急情况时，工程师会与您电话联系，请填写正确手机号', '11', '1', '1', '1', '', '95', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('6', '0', 'email', '邮箱', 'string', '0', '0', '0', '', '', 'email', '', '请输入邮箱地址', '100', '1', '1', '1', '', '94', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('7', '0', 'remind', '接受提醒', 'radio', '0', '0', '0', '0', '0|不接受\r\n1|手机短信\r\n2|邮件', '', '', '工程师回复时，通过您选择的方式提醒您', '1', '1', '1', '1', '', '93', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('8', '0', 'public', '是否公开', 'radio', '0', '0', '0', '0', '0|否\r\n1|是', '', '', '允许工程师在必要时公开此工单内容，机密信息不公开。', '1', '1', '1', '1', '', '92', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('9', '1', 'reply_describe', '反馈内容', 'textarea', '0', '0', '0', '', '', 'required', '', '请输入', '255', '1', '0', '1', 'role=\"reply-describe\"', '49', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('10', '1', 'reply_confidential', '机密信息', 'textarea', '0', '0', '0', '', '', '', '', '请在此处填写账号、密码等机密信息，机密信息不在任何情况公开', '200', '0', '1', '1', '', '48', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('11', '1', 'reply_attachment', '附件', 'files', '0', '0', '5', '', '', '', '', '可通过QQ截图后在此粘贴上传图片', '300', '0', '1', '1', '', '47', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('12', '2', 'reply_describe', '回复内容', 'editor', '0', '0', '0', '', '', 'required', '', '请输入', '0', '0', '1', '1', 'role=\"reply-describe\"', '19', '1601669299');
INSERT IGNORE INTO `__PREFIX__workorder_fields` VALUES ('13', '2', 'reply_attachment', '附件', 'files', '0', '0', '5', '', '', '', '', '可通过QQ截图后在此粘贴上传图片', '300', '0', '1', '1', '', '18', '1601669299');
COMMIT;

-- ----------------------------
-- 知识库表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `__PREFIX__workorder_kbs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `category_id` varchar(100) NOT NULL DEFAULT '' COMMENT '类别',
  `title` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '标题',
  `content` text CHARACTER SET utf8 NOT NULL COMMENT '内容',
  `url` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '地址',
  `views` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '浏览量',
  `likes` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '有帮助数',
  `dislikes` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '无帮助数',
  `memo` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '备注',
  `status` enum('1','0') CHARACTER SET utf8 NOT NULL DEFAULT '1' COMMENT '状态:0=隐藏,1=正常',
  `weigh` int(10) NOT NULL DEFAULT '0' COMMENT '权重',
  `updatetime` int(10) unsigned DEFAULT NULL COMMENT '更新时间',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  `deletetime` int(10) unsigned DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='知识库管理';
-- ----------------------------
-- 插入默认知识点
-- ----------------------------
BEGIN;
INSERT IGNORE INTO `__PREFIX__workorder_kbs` VALUES ('1', '3,4,5', '此知识点仅供示例使用', '此知识点仅供示例使用', '', '0', '0', '0', '', '1', '3', '1601874263', '1601874263', null);
INSERT IGNORE INTO `__PREFIX__workorder_kbs` VALUES ('2', '3,4,5', '知识点将被推荐为解决方案', '知识点将被推荐为解决方案', '', '0', '0', '', '1', '1', '4', '1601874263', '1601874263', null);
COMMIT;

-- ----------------------------
-- 工单表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `__PREFIX__workorder_orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户',
  `urgency` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '紧急程度',
  `category_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '分类',
  `engineer_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '责任工程师',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '工单状态:0=已提交,1=已分派,2=正在处理,3=待反馈,4=待评价,5=已结单',
  `lasturgingtime` int(10) unsigned DEFAULT NULL COMMENT '上次催办时间',
  `updatetime` int(10) unsigned DEFAULT NULL COMMENT '修改时间',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  `deletetime` int(10) unsigned DEFAULT NULL COMMENT '删除时间',
  `title` varchar(255) DEFAULT '' COMMENT '标题',
  `describe` varchar(500) DEFAULT '' COMMENT '描述',
  `confidential` varchar(200) DEFAULT '' COMMENT '机密信息',
  `attachment` varchar(300) DEFAULT '' COMMENT '附件',
  `mobile` varchar(11) DEFAULT '' COMMENT '手机',
  `email` varchar(100) DEFAULT '' COMMENT '邮箱',
  `remind` enum('0','1','2','3') DEFAULT '0' COMMENT '接受提醒:0=不接受,1=手机短信,2=邮件',
  `public` enum('0','1') DEFAULT '0' COMMENT '是否公开:0=否,1=是',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='工单表';

-- ----------------------------
-- 沟通记录表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `__PREFIX__workorder_records` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `order_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '工单',
  `batch` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '消息批次',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户',
  `engineer_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '工程师',
  `message_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '消息类型:0=富文本,1=图片,2=文件,3=系统消息,4=机密信息,5=商品卡片,6=订单卡片',
  `message` text COMMENT '消息内容',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '状态:0=未读,1=已读',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='沟通记录表';

-- ----------------------------
-- 问题提交渠道表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `__PREFIX__workorder_submit_channel` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `title` varchar(100) NOT NULL DEFAULT '' COMMENT '标题',
  `logo_image` varchar(200) NOT NULL DEFAULT '' COMMENT 'LOGO',
  `desc` varchar(200) NOT NULL DEFAULT '' COMMENT '描述',
  `url` varchar(200) NOT NULL DEFAULT '' COMMENT '链接地址',
  `weigh` int(10) NOT NULL DEFAULT '0' COMMENT '权重',
  `status` enum('1','0') NOT NULL DEFAULT '1' COMMENT '状态:0=隐藏,1=正常',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='问题提交渠道表';
-- ----------------------------
-- 插入默认渠道
-- ----------------------------
BEGIN;
INSERT IGNORE INTO `__PREFIX__workorder_submit_channel` VALUES ('1', '创建工单', '/assets/addons/workorder/img/workorder.png', '工单必回，与工程师一对一沟通，忙时需等待', 'index/workorder/create?steps=3', '99', '1', '1601949389');
INSERT IGNORE INTO `__PREFIX__workorder_submit_channel` VALUES ('2', '在线客服(需安装对应插件)', '/assets/addons/workorder/img/kefu.png', '与在线客服代表实时会话', 'addons/kefu/index', '98', '1', '1601949389');
INSERT IGNORE INTO `__PREFIX__workorder_submit_channel` VALUES ('3', '百度一下', '/assets/addons/workorder/img/fff_logo.png', '外部渠道也是支持的', 'https://baidu.com', '3', '0', '1601949389');
COMMIT;

-- ----------------------------
-- 紧急程度表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `__PREFIX__workorder_urgentrank` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '名称',
  `desc` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '描述',
  `status` enum('1','0') CHARACTER SET utf8 NOT NULL DEFAULT '1' COMMENT '状态:0=关闭,1=启用',
  `weigh` int(10) NOT NULL DEFAULT '0' COMMENT '权重',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  `deletetime` int(10) unsigned DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='紧急程度表';
-- ----------------------------
-- 插入默认紧急程度
-- ----------------------------
BEGIN;
INSERT IGNORE INTO `__PREFIX__workorder_urgentrank` VALUES ('1', '情况持续恶化', '请工程师重视此问题', '1', '1', '1597395925', null);
INSERT IGNORE INTO `__PREFIX__workorder_urgentrank` VALUES ('2', '生产已停止', '情况很紧急', '1', '2', '1597395925', null);
INSERT IGNORE INTO `__PREFIX__workorder_urgentrank` VALUES ('3', '普通', '我已查阅相关资料，但未找到问题答案', '1', '3', '1597395925', null);
COMMIT;

-- ----------------------------
-- v1.0.1
-- ----------------------------
CREATE TABLE `__PREFIX__workorder_time_statistics` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '类型:0=解决时间,1=回复时间,2=首次回复时间',
  `order_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '工单ID',
  `engineer_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '工程师ID',
  `starttime` int(10) unsigned DEFAULT NULL COMMENT '开始时间',
  `endtime` int(10) unsigned DEFAULT NULL COMMENT '结束时间',
  `time_consum` int(10) unsigned DEFAULT NULL COMMENT '耗时(秒)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='响应时间统计数据';

BEGIN;
ALTER TABLE `__PREFIX__workorder_engineers` ADD COLUMN `avg_response_time` int(10) unsigned DEFAULT '0' COMMENT '平均响应时间(秒)' AFTER `lastreceivetime`;
ALTER TABLE `__PREFIX__workorder_engineers` ADD COLUMN `work_order_quantity` int(10) unsigned DEFAULT '0' COMMENT '接单量(含经手)' AFTER `lastreceivetime`;
COMMIT;