-- 001: 蜘蛛访问统计表（v0.0.2 初始迁移，已通过 ensure_spider_tables 自动创建的可跳过）
CREATE TABLE IF NOT EXISTS `spider_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `spider_name` VARCHAR(40) NOT NULL DEFAULT '' COMMENT '蜘蛛名称',
    `domain` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '被访问域名',
    `url` VARCHAR(800) NOT NULL DEFAULT '' COMMENT '被访问URL路径',
    `status_code` SMALLINT UNSIGNED NOT NULL DEFAULT 200 COMMENT 'HTTP状态码',
    `ip` VARCHAR(45) NOT NULL DEFAULT '' COMMENT '蜘蛛IP',
    `user_agent` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '完整UA',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_spider_name` (`spider_name`),
    KEY `idx_domain` (`domain`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='蜘蛛访问明细日志';

CREATE TABLE IF NOT EXISTS `spider_daily` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `day` DATE NOT NULL COMMENT '日期',
    `spider_name` VARCHAR(40) NOT NULL DEFAULT '',
    `domain` VARCHAR(200) NOT NULL DEFAULT '',
    `hits` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '访问次数',
    `status_2xx` INT UNSIGNED NOT NULL DEFAULT 0,
    `status_3xx` INT UNSIGNED NOT NULL DEFAULT 0,
    `status_4xx` INT UNSIGNED NOT NULL DEFAULT 0,
    `status_5xx` INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_day_spider_domain` (`day`, `spider_name`, `domain`),
    KEY `idx_day` (`day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='蜘蛛访问每日汇总'
