-- ============================================================
-- 数据库索引优化脚本 - SQLite
-- 执行方式: sqlite3 instance/ctf.db < create_indexes_sqlite.sql
-- ============================================================

-- ============================================================
-- 1. 外键索引
-- ============================================================

-- users 表
CREATE INDEX IF NOT EXISTS idx_users_preferred_system_ai 
ON users(preferred_system_ai_config_id);

-- challenge_records 表
CREATE INDEX IF NOT EXISTS idx_challenge_records_user_id 
ON challenge_records(user_id);

CREATE INDEX IF NOT EXISTS idx_challenge_records_challenge_type 
ON challenge_records(challenge_type);

-- deployments 表（原 deployment_records）
CREATE INDEX IF NOT EXISTS idx_deployments_challenge_id 
ON deployments(challenge_id);

CREATE INDEX IF NOT EXISTS idx_deployments_user_id 
ON deployments(user_id);

-- deployment_status_logs 表已移除（状态日志已合并到 deployments.status_history JSON 字段）

-- category_admins 表
CREATE INDEX IF NOT EXISTS idx_category_admins_category_id 
ON category_admins(category_id);

CREATE INDEX IF NOT EXISTS idx_category_admins_user_id 
ON category_admins(user_id);

-- prompt_templates 表已移除（不再使用）

-- user_sessions 表
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id 
ON user_sessions(user_id);

-- ai_provider_configs 表
CREATE INDEX IF NOT EXISTS idx_ai_provider_configs_user_id 
ON ai_provider_configs(user_id);

-- augment_configs 表
CREATE INDEX IF NOT EXISTS idx_augment_configs_user_id 
ON augment_configs(user_id);

-- ============================================================
-- 2. 常用查询字段索引
-- ============================================================

-- category_configs 表
CREATE INDEX IF NOT EXISTS idx_category_configs_enabled 
ON category_configs(enabled);

CREATE INDEX IF NOT EXISTS idx_category_configs_sort_order 
ON category_configs(sort_order);

-- challenge_records 表
CREATE INDEX IF NOT EXISTS idx_challenge_records_created_at 
ON challenge_records(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_challenge_records_generation_status 
ON challenge_records(generation_status);

-- deployments 表（原 deployment_records）
CREATE INDEX IF NOT EXISTS idx_deployments_status 
ON deployments(status);

CREATE INDEX IF NOT EXISTS idx_deployments_created_at 
ON deployments(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_deployments_expires_at 
ON deployments(expires_at);

-- ai_provider_configs 表
CREATE INDEX IF NOT EXISTS idx_ai_provider_configs_is_active 
ON ai_provider_configs(is_active);

CREATE INDEX IF NOT EXISTS idx_ai_provider_configs_is_default 
ON ai_provider_configs(is_default);

CREATE INDEX IF NOT EXISTS idx_ai_provider_configs_provider_type 
ON ai_provider_configs(provider_type);

-- users 表
CREATE INDEX IF NOT EXISTS idx_users_role 
ON users(role);

CREATE INDEX IF NOT EXISTS idx_users_email 
ON users(email);

-- user_sessions 表
CREATE INDEX IF NOT EXISTS idx_user_sessions_expires_at 
ON user_sessions(expires_at);

CREATE INDEX IF NOT EXISTS idx_user_sessions_session_token 
ON user_sessions(session_token);

-- system_configs 表
CREATE INDEX IF NOT EXISTS idx_system_configs_key_enabled 
ON system_configs(key, enabled);

-- system_ai_configs 表
CREATE INDEX IF NOT EXISTS idx_system_ai_configs_key 
ON system_ai_configs(key);

-- augment_session_tokens 表
CREATE INDEX IF NOT EXISTS idx_augment_session_tokens_is_global 
ON augment_session_tokens(is_global);

CREATE INDEX IF NOT EXISTS idx_anyrouter_tokens_is_global_active 
ON anyrouter_tokens(is_global, is_active);

CREATE INDEX IF NOT EXISTS idx_agentrouter_tokens_is_global_active 
ON agentrouter_tokens(is_global, is_active);

-- ============================================================
-- 3. 复合索引（用于联合查询）
-- ============================================================

-- challenge_records 表
CREATE INDEX IF NOT EXISTS idx_challenge_records_user_created 
ON challenge_records(user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_challenge_records_type_created 
ON challenge_records(challenge_type, created_at DESC);

-- deployments 表（原 deployment_records）
CREATE INDEX IF NOT EXISTS idx_deployments_user_created 
ON deployments(user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_deployments_status_expires 
ON deployments(status, expires_at);

-- ai_provider_configs 表
-- 注意：SQLite 不支持部分索引，这里创建完整索引
CREATE INDEX IF NOT EXISTS idx_ai_provider_configs_user_active_default 
ON ai_provider_configs(user_id, is_active, is_default);

-- category_admins 表
CREATE INDEX IF NOT EXISTS idx_category_admins_user_category 
ON category_admins(user_id, category_id);

-- user_sessions 表
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_expires 
ON user_sessions(user_id, expires_at);

-- augment_configs 表
CREATE INDEX IF NOT EXISTS idx_augment_configs_type_user_enabled 
ON augment_configs(config_type, user_id, enabled);

-- ============================================================
-- 4. 更新统计信息（SQLite 使用 ANALYZE）
-- ============================================================

ANALYZE;

-- ============================================================
-- 验证索引创建情况
-- ============================================================
-- SQLite 查询所有索引
SELECT 
    name AS index_name,
    tbl_name AS table_name,
    sql AS index_sql
FROM sqlite_master
WHERE type = 'index'
AND name LIKE 'idx_%'
ORDER BY tbl_name, name;

