-- =====================================================
-- 2026企业CMS - 微信客服API数据库结构
-- =====================================================

USE `cms2026`;

-- =====================================================
-- 1. 微信客服配置表 (wechat_config)
-- =====================================================
CREATE TABLE IF NOT EXISTS `wechat_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app_id` varchar(100) DEFAULT NULL COMMENT '微信公众号AppID',
  `app_secret` varchar(100) DEFAULT NULL COMMENT '微信公众号AppSecret',
  `token` varchar(100) DEFAULT NULL COMMENT '微信Token',
  `encoding_aes_key` varchar(100) DEFAULT NULL COMMENT '消息加密密钥',
  `customer_service_enabled` tinyint(1) DEFAULT 0 COMMENT '是否启用微信客服',
  `notification_admins` varchar(255) DEFAULT NULL COMMENT '接收通知的管理员ID（逗号分隔）',
  `auto_reply` text DEFAULT NULL COMMENT '自动回复内容',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='微信客服配置表';

-- 插入默认配置
INSERT INTO `wechat_config` (`app_id`, `app_secret`, `customer_service_enabled`) VALUES
('', '', 0);

-- =====================================================
-- 2. 微信消息表 (wechat_messages)
-- =====================================================
CREATE TABLE IF NOT EXISTS `wechat_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_type` varchar(50) DEFAULT NULL COMMENT '消息类型（text/image/voice等）',
  `open_id` varchar(100) DEFAULT NULL COMMENT '用户OpenID',
  `nickname` varchar(100) DEFAULT NULL COMMENT '用户昵称',
  `content` text DEFAULT NULL COMMENT '消息内容',
  `reply` text DEFAULT NULL COMMENT '回复内容',
  `reply_admin_id` int(11) DEFAULT NULL COMMENT '回复管理员ID',
  `status` varchar(20) DEFAULT 'pending' COMMENT '状态：pending已处理/replied已回复',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `open_id` (`open_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='微信消息表';

-- =====================================================
-- 3. 微信访客表 (wechat_visitors)
-- =====================================================
CREATE TABLE IF NOT EXISTS `wechat_visitors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `open_id` varchar(100) NOT NULL COMMENT '用户OpenID',
  `nickname` varchar(100) DEFAULT NULL COMMENT '昵称',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像',
  `subscribe_time` datetime DEFAULT NULL COMMENT '关注时间',
  `last_active_time` datetime DEFAULT NULL COMMENT '最后活跃时间',
  `message_count` int(11) DEFAULT 0 COMMENT '消息数量',
  `status` varchar(20) DEFAULT 'active' COMMENT '状态：active活跃/inactive非活跃',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `open_id` (`open_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='微信访客表';
