-- =============================================
-- JefCounts 统计系统 - 完整数据库初始化脚本
-- 版本: 2.0 (完整版)
-- 更新: 2025-10-30
-- 说明: 基于实际运行环境生成的完整安装脚本
-- 包含: 所有表结构、索引、默认数据、用户偏好持久化
-- =============================================

-- 设置字符集和外键检查
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =============================================
-- 1. 页面访问记录表 (pageviews)
-- =============================================
-- 核心统计数据表，存储所有页面访问记录

DROP TABLE IF EXISTS `pageviews`;

CREATE TABLE `pageviews` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `site_key` VARCHAR(32) COLLATE utf8mb4_unicode_ci DEFAULT 'default' COMMENT '站点标识符（关联sites表）',
  `ip` VARCHAR(45) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '访客IP地址（支持IPv4和IPv6）',
  `user_agent` VARCHAR(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '浏览器User-Agent（用于设备、浏览器、OS统计）',
  `page_url` VARCHAR(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '访问页面URL',
  `referer` VARCHAR(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '来源URL（HTTP Referer，用于流量来源分析）',
  `visit_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '访问时间',
  `is_bot` TINYINT(1) DEFAULT 0 COMMENT '是否为机器人：0-真实用户，1-机器人/爬虫',
  `bot_score` TINYINT DEFAULT 0 COMMENT '机器人评分（0-100，越高越可能是机器人）',
  `bot_name` VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '机器人名称（如：Google Bot, Ahrefs Bot等）',
  
  -- 主键
  PRIMARY KEY (`id`),
  
  -- 性能优化索引
  KEY `idx_site_key` (`site_key`) COMMENT '站点查询索引',
  KEY `idx_visit_time` (`visit_time`) COMMENT '时间范围查询索引',
  KEY `idx_ip` (`ip`) COMMENT 'IP统计索引',
  KEY `idx_page_url` (`page_url`(100)) COMMENT '页面URL前缀索引',
  KEY `idx_is_bot` (`is_bot`) COMMENT '机器人过滤索引',
  KEY `idx_bot_score` (`bot_score`) COMMENT '机器人评分索引',
  KEY `idx_bot_name` (`bot_name`) COMMENT '机器人名称索引',
  KEY `idx_date_ip` (`visit_time`, `ip`) COMMENT '时间+IP组合索引（用于UV统计）'
  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='页面访问记录表';

-- =============================================
-- 2. 站点管理表 (sites)
-- =============================================
-- 多站点管理，每个站点有唯一的site_key

DROP TABLE IF EXISTS `sites`;

CREATE TABLE `sites` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '站点ID（自增主键）',
  `name` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '站点名称',
  `domain` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '站点域名',
  `site_key` VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '站点唯一标识符（16位哈希）',
  `description` TEXT COLLATE utf8mb4_unicode_ci COMMENT '站点描述',
  `status` ENUM('active','inactive') COLLATE utf8mb4_unicode_ci DEFAULT 'active' COMMENT '站点状态：active-活跃，inactive-停用',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  
  -- 主键和唯一键
  PRIMARY KEY (`id`),
  UNIQUE KEY `site_key` (`site_key`) COMMENT '站点标识唯一约束',
  
  -- 索引
  KEY `idx_site_key` (`site_key`) COMMENT '站点查询索引',
  KEY `idx_domain` (`domain`) COMMENT '域名查询索引',
  KEY `idx_status` (`status`) COMMENT '状态筛选索引'
  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='站点管理表';

-- =============================================
-- 3. 用户偏好表 (user_preferences)
-- =============================================
-- 用户个性化设置，保存用户选择的默认站点

DROP TABLE IF EXISTS `user_preferences`;

CREATE TABLE `user_preferences` (
  `username` VARCHAR(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名（主键）',
  `default_site_id` INT DEFAULT NULL COMMENT '默认站点ID（关联sites.id）',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  
  -- 主键
  PRIMARY KEY (`username`),
  
  -- 索引
  KEY `idx_site_id` (`default_site_id`) COMMENT '站点ID查询索引'
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户偏好表（保存用户默认站点选择）';

-- =============================================
-- 4. 插入默认数据
-- =============================================

-- 插入默认站点（系统必需）
INSERT INTO `sites` (`name`, `domain`, `site_key`, `description`, `status`) VALUES 
('默认站点', 'localhost', 'default', '系统默认站点，用于初始化和测试', 'active')
ON DUPLICATE KEY UPDATE 
  `name` = VALUES(`name`),
  `description` = VALUES(`description`);

-- =============================================
-- 5. 恢复外键检查
-- =============================================

SET FOREIGN_KEY_CHECKS = 1;

-- =============================================
-- 安装完成说明
-- =============================================

/*
✅ 数据库安装完成！

📋 已创建的表：
1. pageviews - 页面访问记录表（核心统计数据）
2. sites - 站点管理表（多站点支持）
3. user_preferences - 用户偏好表（默认站点持久化）

🔑 核心功能：
✅ 多站点统计支持
✅ 机器人识别和过滤
✅ 用户偏好持久化（站点选择记忆）
✅ 完整的索引优化（支持高性能查询）
✅ UV/PV统计支持
✅ 流量来源分析
✅ 设备/浏览器/OS统计
✅ IP地理位置统计

📊 表结构说明：

1. pageviews（页面访问记录）
   - 存储每次页面访问的详细信息
   - 支持IPv4和IPv6地址
   - 包含机器人检测字段（is_bot, bot_score）
   - 优化索引确保查询性能
   - 支持时间范围、站点、IP等多维度查询

2. sites（站点管理）
   - 管理多个被统计的网站
   - 每个站点有唯一的 site_key（16位哈希）
   - 支持站点状态管理（启用/停用）
   - 记录创建和更新时间

3. user_preferences（用户偏好）
   - 存储用户的个性化设置
   - 保存用户选择的默认站点
   - 用户退出再登录时保持上次选择
   - 解决站点选择不持久的问题

🚀 性能优化：
- 所有时间查询字段都有索引
- site_key 索引优化站点筛选
- visit_time + ip 组合索引优化UV统计
- page_url 使用前缀索引节省空间
- is_bot 索引优化机器人过滤
- 使用 InnoDB 引擎支持事务

💡 使用建议：
1. 在"站点管理"中添加您的网站
2. 复制统计代码到您的网站页面
3. 系统会自动记住您选择的默认站点
4. 支持分页浏览大量数据
5. 建议定期备份数据库

⚠️ 数据保留策略：
- 建议通过设置页面配置数据自动清理周期
- 或使用定时任务定期清理旧数据
- 示例：DELETE FROM pageviews WHERE visit_time < DATE_SUB(NOW(), INTERVAL 365 DAY);
- 提示：清理前建议先备份历史数据

🔧 故障排查：
1. 如果站点选择不持久 → 检查 user_preferences 表是否存在
2. 如果分页报错 → 检查所有索引是否创建成功
3. 如果查询慢 → 检查 visit_time 和 site_key 索引
4. 如果UV统计不准 → 检查 idx_date_ip 组合索引

📝 版本历史：
- v2.0 (2025-10-30): 基于实际运行环境生成的完整版本
  * 修复用户偏好表创建问题
  * 添加所有必要的索引
  * 完善注释和说明文档
  * 确保与实际运行环境完全一致

- v1.1 (2025-10-29): 添加用户偏好功能
- v1.0 (2025-09-27): 初始版本
*/

