/*--2022-11-29--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '客服端撤回消息时间限制，单位/秒（默认不限）', 'KefuDeleteMessageLimitTime', '');
/*--2022-12-01--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, 'IP地址或者refer来源黑名单', 'SystemBlackList', '');
/*--2023-01-04--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, 'IP白名单', 'SystemWhiteList', '');
/*--2023-01-11--*/
alter table visitor_ext add  `language` varchar(100) NOT NULL DEFAULT ''  COMMENT '浏览器语言';
/*--2023-01-31--*/
alter table user add  `uuid` varchar(125) NOT NULL DEFAULT '' COMMENT '企业uuid';
alter table user add   KEY `idx_uuid` (`uuid`) COMMENT 'uuid索引';
/*--2023-02-18--*/
alter table article add  `search_type` tinyint NOT NULL DEFAULT '1' COMMENT '1包含匹配,2精准匹配';
/*--2023-02-19--*/
alter table message add  KEY `created_at` (`created_at`) COMMENT '时间索引';
/*--2023-02-25--*/
alter table visitor_ext add KEY `ent_id` (`ent_id`) COMMENT '企业ID索引';
alter table visitor_ext add KEY `title` (`title`) COMMENT '页面标题索引';
/*--2023-03-02--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, 'IP白名单', 'SystemWhiteList', '');
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, 'IP登录限制(1全公开,2禁止海外,3只能内网)', 'IpLoginForbidden', '1');
/*--2023-03-04--*/
alter table welcome modify `content` varchar(5000)  COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '欢迎消息内容';
/*--2023-03-10--*/
CREATE TABLE `consumer` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `company` varchar(500) NOT NULL DEFAULT ''  COMMENT '公司名',
 `realname` varchar(500) NOT NULL DEFAULT ''  COMMENT '姓名',
 `score` varchar(100) NOT NULL DEFAULT ''  COMMENT '级别',
 `consumer_sn` varchar(500) NOT NULL DEFAULT ''  COMMENT '客户编号',
 `ent_id` varchar(100) NOT NULL DEFAULT ''  COMMENT '企业ID',
 `kefu_name` varchar(100) NOT NULL DEFAULT ''  COMMENT 'kefu名称',
 `tel` varchar(100) NOT NULL DEFAULT ''  COMMENT '手机',
 `wechat` varchar(100) NOT NULL DEFAULT ''  COMMENT '微信',
 `qq` varchar(100) NOT NULL DEFAULT ''  COMMENT 'qq',
 `email` varchar(100) NOT NULL DEFAULT ''  COMMENT '邮箱',
 `remark` varchar(1000) NOT NULL DEFAULT ''  COMMENT '备注',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
 PRIMARY KEY (`id`) COMMENT '自增主键索引',
 KEY `ent_id` (`ent_id`)  COMMENT '企业ID索引',
 KEY `consumer_sn` (`consumer_sn`)  COMMENT '客户索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '客户表';
/*--2023-03-12--*/
CREATE TABLE `learn` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `content` varchar(500) NOT NULL DEFAULT ''  COMMENT '问题内容',
 `score` int(11) NOT NULL DEFAULT '1'  COMMENT '次数',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
 `ent_id` varchar(100) NOT NULL DEFAULT ''  COMMENT '企业ID',
 `kefu_name` varchar(100) NOT NULL DEFAULT ''  COMMENT 'kefu名称',
 `finshed` tinyint(4) NOT NULL DEFAULT 1 COMMENT '是否解决，1未解决，2已解决',
 PRIMARY KEY (`id`) COMMENT '自增主键索引',
 KEY `ent_id` (`ent_id`)  COMMENT '企业ID索引',
 KEY `content` (`content`)  COMMENT '问题内容索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '机器人学习库';
/*--2023-03-13--*/
alter table article add  `score` int(11) NOT NULL DEFAULT '0' COMMENT '命中次数';
alter table article add   KEY `score` (`score`) COMMENT '命中次数索引';
/*--2023-03-18--*/
CREATE TABLE `wework_sync_msg` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sync_cursor` varchar(255) NOT NULL DEFAULT '',
  `json_txt` text NOT NULL,
  `visitor_id` varchar(100) NOT NULL DEFAULT '',
  `kefu_id` varchar(100) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ent_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `visitor_id` (`visitor_id`),
  KEY `kefu_id` (`kefu_id`),
  KEY `ent_id` (`ent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
alter table reply_group add `ent_id` varchar(255) NOT NULL DEFAULT '' COMMENT '客服企业ID';
alter table reply_group add `is_team` tinyint NOT NULL DEFAULT '1' COMMENT '1个人,2团队';
alter table reply_group modify `group_name` varchar(255) NOT NULL DEFAULT '' COMMENT '组名';
alter table reply_group modify `user_id` varchar(255) NOT NULL DEFAULT '' COMMENT '客服账户';
alter table reply_item add `ent_id` varchar(255) NOT NULL DEFAULT '' COMMENT '客服企业ID';
alter table reply_item  modify `item_name` varchar(255) NOT NULL DEFAULT '' COMMENT '快捷回复标题';
alter table reply_item  modify `user_id` varchar(255) NOT NULL DEFAULT '' COMMENT '客服账户';
/*--2023-03-22--*/
alter table visitor_attr add `max_message_num` varchar(100) NOT NULL DEFAULT '10'  COMMENT '访客最大消息数';
/*--2023-03-27--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, 'OpenAI API最大消息数量', 'OpenAiApiMaxNum', '100');
/*--2023-04-14--*/
alter table article modify  `score` int(11) NOT NULL DEFAULT '0' COMMENT '命中次数';
/*--2023-04-27--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '基于GPT的向量知识库服务接口', 'BaseGPTKnowledge', 'http://127.0.0.1:8083');
/*--2023-05-05--*/
alter table visitor add  `state` varchar(100) NOT NULL DEFAULT ''  COMMENT '访客状态位';
/*--2023-05-18--*/
alter table reply_item modify `content` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '快捷回复内容';
/*--2023-05-29--*/
DROP TABLE IF EXISTS `ai_file`;
CREATE TABLE `ai_file` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `file_name` varchar(1000) NOT NULL DEFAULT ''  COMMENT '文件名',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
 `collect_name` varchar(500) NOT NULL DEFAULT ''  COMMENT '集合名称',
 PRIMARY KEY (`id`) COMMENT '自增主键索引',
 KEY `collect_name` (`collect_name`)  COMMENT 'AI集合文件列表'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'AI集合文件向量映射表';
DROP TABLE IF EXISTS `ai_file_points`;
CREATE TABLE `ai_file_points` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `file_id` varchar(500) NOT NULL DEFAULT ''  COMMENT '文件表自增ID',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
 `collect_name` varchar(500) NOT NULL DEFAULT ''  COMMENT '集合名称',
 `points_id` varchar(500) NOT NULL DEFAULT ''  COMMENT '向量ID',
 PRIMARY KEY (`id`) COMMENT '自增主键索引',
 KEY `collect_name` (`collect_name`)  COMMENT '集合名索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'AI集合文件向量映射表';
/*--2023-06-14--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, 'QDRANT向量数据库地址', 'QdrantBase', '127.0.0.1');
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, 'QDRANT向量数据库端口', 'QdrantPort', '6333');
alter table ai_file add  `file_size` varchar(500) NOT NULL DEFAULT ''  COMMENT '字符数';
/*--2023-07-18--*/
alter table message add KEY `idx_kefuid_mestype_status` (`kefu_id`,`mes_type`,`status`) COMMENT '联合索引';
/*--2023-08-07--*/
DROP TABLE IF EXISTS `aigc_session_collect`;
CREATE TABLE `aigc_session_collect` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(1000) NOT NULL DEFAULT ''  COMMENT '集合标题',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
 `kefu_name` varchar(500) NOT NULL DEFAULT ''  COMMENT '客服名称',
 `ent_id` varchar(500) NOT NULL DEFAULT ''  COMMENT '企业ID',
 PRIMARY KEY (`id`) COMMENT '自增主键索引',
 KEY `kefu_name` (`kefu_name`)  COMMENT '客服名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'AIGC会话集合';
DROP TABLE IF EXISTS `aigc_session_message`;
CREATE TABLE `aigc_session_message` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `collect_id` int(11) NOT NULL DEFAULT '0'  COMMENT '集合ID',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
 `kefu_avatar` varchar(500) NOT NULL DEFAULT ''  COMMENT '客服头像',
 `ai_avatar` varchar(500) NOT NULL DEFAULT ''  COMMENT 'AI头像',
 `content` text   COMMENT '内容',
 `kefu_name` varchar(500) NOT NULL DEFAULT ''  COMMENT '客服名称',
 `msg_type` varchar(500) NOT NULL DEFAULT ''  COMMENT '消息类型',
 `ent_id` varchar(500) NOT NULL DEFAULT ''  COMMENT '企业ID',
 PRIMARY KEY (`id`) COMMENT '自增主键索引',
 KEY `kefu_name` (`kefu_name`)  COMMENT '客服名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'AIGC会话消息';
alter table user_attr add  `aigc_session_score` int(11) unsigned NOT NULL DEFAULT 0  COMMENT 'AIGC积分';
/*--2023-08-27--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '微信支付AppID', 'WechatPayAppId', '');
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '微信支付商户号', 'WechatPayMchID', '');
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '微信支付商户证书序列号', 'WechatPayMchCertificateSerialNumber', '');
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '微信支付商户API V3密钥', 'WechatPayMchAPIv3Key', '');
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '微信支付商户私钥', 'WechatPayMchPrivateKey', '');
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '微信支付回调URL', 'WechatPayNotifyUrl', '');
alter table user_order add  `new_expire_time` varchar(100) NOT NULL DEFAULT '' COMMENT '新的到期时间';
/*--2023-10-23--*/
alter table reply_item add KEY `ent_id` (`ent_id`) COMMENT '企业id索引';
alter table reply_group add KEY `ent_id` (`ent_id`) COMMENT '企业id索引';
/*--2023-11-13--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '是否验证强密码', 'CheckStrongPass', 'false');
/*--2023-11-14--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '抖音client_key', 'DouyinClientKey', '');
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '抖音client_secret', 'DouyinClientSecret', '');
/*--2023-11-16--*/
CREATE TABLE `user_douyin` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `kefu_name` varchar(100) NOT NULL DEFAULT ''  COMMENT '客服账户',
 `nickname` varchar(500) NOT NULL DEFAULT ''  COMMENT '抖音昵称',
 `avatar` varchar(500) NOT NULL DEFAULT ''  COMMENT '抖音头像',
 `open_id` varchar(500) NOT NULL DEFAULT ''  COMMENT '抖音OpenId',
 `union_id` varchar(500) NOT NULL DEFAULT ''  COMMENT '抖音union_id',
 `access_token` varchar(500) NOT NULL DEFAULT ''  COMMENT '抖音AccessToken',
 `expires_in` datetime   COMMENT '抖音AccessToken过期时间',
 `refresh_token` varchar(500) NOT NULL DEFAULT ''  COMMENT '抖音refresh_token',
 `refresh_expires_in` datetime  COMMENT '抖音refresh_token过期时间',
 `client_token` varchar(500) NOT NULL DEFAULT ''  COMMENT '抖音client_token',
 `client_token_expires` datetime   COMMENT '抖音client_token过期时间',
 `ent_id` varchar(100) NOT NULL DEFAULT ''  COMMENT '企业ID',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
 PRIMARY KEY (`id`) COMMENT '自增主键索引',
 KEY `kefu_name` (`kefu_name`)  COMMENT '客服账户索引',
 KEY `ent_id` (`ent_id`)  COMMENT '企业ID索引',
 KEY `open_id` (`open_id`)  COMMENT '抖音OpenId索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '客服抖音绑定表';
/*--2023-12-15--*/
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '阿里云短信模板CODE', 'ALI_SMS_CODE', '');
/*--2023-12-18--*/
CREATE TABLE `douyin_webhook` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `kefu_name` varchar(100) NOT NULL DEFAULT ''  COMMENT '客服账户',
 `event` varchar(500) NOT NULL DEFAULT ''  COMMENT 'event',
 `from_user_id` varchar(500) NOT NULL DEFAULT ''  COMMENT 'from_user_id',
 `to_user_id` varchar(500) NOT NULL DEFAULT ''  COMMENT 'to_user_id',
 `client_key` varchar(500) NOT NULL DEFAULT ''  COMMENT 'client_key',
 `content` TEXT DEFAULT NULL  COMMENT 'content',
 `ent_id` varchar(100) NOT NULL DEFAULT ''  COMMENT '企业ID',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
 PRIMARY KEY (`id`) COMMENT '自增主键索引',
 KEY `kefu_name` (`kefu_name`)  COMMENT '客服账户索引',
 KEY `ent_id` (`ent_id`)  COMMENT '企业ID索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '抖音webhooks日志表';
/*--2024-04-04--*/
CREATE TABLE `wechat_login` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `kefu_name` varchar(500) NOT NULL DEFAULT ''  COMMENT '客服账户',
 `open_id` varchar(500) NOT NULL DEFAULT ''  COMMENT '微信公众号openid',
 `temp_kefu_id` varchar(500) NOT NULL DEFAULT ''  COMMENT '临时客服ID',
 `status` varchar(500) NOT NULL DEFAULT ''  COMMENT '当前状态',
 `ent_id` varchar(100) NOT NULL DEFAULT ''  COMMENT '企业ID',
 `login_ip` varchar(100) NOT NULL DEFAULT ''  COMMENT '登录IP',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
 PRIMARY KEY (`id`) COMMENT '自增主键索引',
 KEY `temp_kefu_id` (`kefu_name`)  COMMENT '临时客服ID索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '微信扫码登录表';
/*--2024-04-16--*/
CREATE TABLE `product_order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `ent_id` varchar(100) NOT NULL DEFAULT ''  COMMENT '企业ID',
  `kefu_name` varchar(500) NOT NULL DEFAULT ''  COMMENT '客服账户',
  `user_id` varchar(500) NOT NULL DEFAULT ''  COMMENT '用户ID',
  `order_sn` varchar(500) NOT NULL DEFAULT ''  COMMENT '订单编号',
  `order_desc` varchar(500) NOT NULL DEFAULT ''  COMMENT '订单描述',
  `order_status` varchar(100) NOT NULL DEFAULT ''  COMMENT '订单状态：pending,processing,completed,cancelled',
  `total_amount` int(11) NOT NULL DEFAULT '0' COMMENT '订单金额',
  `payment_method` varchar(100) NOT NULL DEFAULT ''  COMMENT '支付方式：wechat,alipay,bank,other',
  `payment_status` varchar(100) NOT NULL DEFAULT ''  COMMENT '支付状态：paid,unpaid,refunded',
  `shipping_address` TEXT COMMENT '收货地址',
  `email` varchar(100) NOT NULL DEFAULT ''  COMMENT '邮箱',
  `contact` varchar(100) NOT NULL DEFAULT ''  COMMENT '联系人',
  `tel` varchar(100) NOT NULL DEFAULT ''  COMMENT '手机号',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `kefu_name` (`kefu_name`)  COMMENT '客服账户索引',
  KEY `ent_id` (`ent_id`)  COMMENT '企业ID索引',
  KEY `order_sn` (`order_sn`)  COMMENT '订单号索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '产品订单表';
CREATE TABLE virtual_product (
    `id` INT NOT NULL AUTO_INCREMENT,
    `ent_id` varchar(100) NOT NULL DEFAULT ''  COMMENT '企业ID',
    `kefu_name` varchar(500) NOT NULL DEFAULT ''  COMMENT '客服账户',
    `product_name` VARCHAR(500) NOT NULL DEFAULT '',
    `product_category` VARCHAR(100) NOT NULL DEFAULT '',
    `description` TEXT,
    `price` int(11) NOT NULL DEFAULT '0' COMMENT '金额',
    `product_img` VARCHAR(1000) NOT NULL DEFAULT '',
    `resource_link` VARCHAR(1000) NOT NULL DEFAULT '',
    `is_active` tinyint(4) NOT NULL DEFAULT 1 COMMENT '在线状态，1在售，2下架',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `kefu_name` (`kefu_name`)  COMMENT '客服账户索引',
    KEY `ent_id` (`ent_id`)  COMMENT '企业ID索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '虚拟产品表';
INSERT INTO `config` (`id`, `conf_name`, `conf_key`, `conf_value`) VALUES (NULL, '微信支付JSAPI支付回调URL', 'WechatJsApiNotifyUrl', 'https://test.com/2/jsApiPayNotifyUrl');
/*--2024-04-19--*/
alter table user_attr add  `money` int(11) unsigned NOT NULL DEFAULT 0  COMMENT '总金额';
/*--2024-04-22--*/
alter table virtual_product add  `payment` VARCHAR(500) NOT NULL DEFAULT ''  COMMENT '支付方式，wechat 微信支付；nan66 南星码支付';
/*--2024-05-27--*/
alter table visitor_ext modify `url` varchar(1500) NOT NULL DEFAULT '' COMMENT '页面地址';
/*--2024-06-04--*/
alter table ent_config modify  `conf_value` text COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '配置值';
/*--2024-06-14--*/
CREATE TABLE llm_log (
    `id` INT NOT NULL AUTO_INCREMENT,
    `ent_id` varchar(100) NOT NULL DEFAULT ''  COMMENT '企业ID',
    `kefu_name` varchar(500) NOT NULL DEFAULT ''  COMMENT '客服账户',
    `model_name` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '大模型名称',
    `question` TEXT DEFAULT NULL COMMENT '提问',
    `answer` TEXT DEFAULT NULL COMMENT '回复',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `kefu_name` (`kefu_name`)  COMMENT '客服账户索引',
    KEY `ent_id` (`ent_id`)  COMMENT '企业ID索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '大模型提问日志';