-- API Key 相关表结构
-- 执行方式: mysql -u root -p video_parser < database/api_key_tables.sql

-- 用户 API Key 表
CREATE TABLE IF NOT EXISTS `user_api_keys` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `name` VARCHAR(100) NOT NULL COMMENT 'Key名称',
    `api_key` VARCHAR(64) NOT NULL UNIQUE COMMENT 'API Key',
    `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    `daily_limit` INT DEFAULT 100 COMMENT '每日调用限制',
    `total_calls` INT DEFAULT 0 COMMENT '总调用次数',
    `last_used_at` DATETIME COMMENT '最后使用时间',
    `last_used_ip` VARCHAR(45) COMMENT '最后使用IP',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_api_key` (`api_key`),
    INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户API Key表';

-- API Key 每日统计表
CREATE TABLE IF NOT EXISTS `api_key_daily_stats` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `api_key_id` INT NOT NULL,
    `date` DATE NOT NULL,
    `call_count` INT DEFAULT 0 COMMENT '调用次数',
    `success_count` INT DEFAULT 0 COMMENT '成功次数',
    `fail_count` INT DEFAULT 0 COMMENT '失败次数',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`api_key_id`) REFERENCES `user_api_keys`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `uk_api_key_date` (`api_key_id`, `date`),
    INDEX `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API Key每日统计表';