-- Novalon管理系统索引优化脚本
-- 版本: V5
-- 描述: 为表创建必要的索引以提升查询性能

-- 用户表索引
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
CREATE INDEX IF NOT EXISTS idx_users_deleted_at ON users(deleted_at);

-- 角色表索引
CREATE INDEX IF NOT EXISTS idx_roles_role_key ON roles(role_key);
CREATE INDEX IF NOT EXISTS idx_roles_status ON roles(status);
CREATE INDEX IF NOT EXISTS idx_roles_deleted_at ON roles(deleted_at);

-- 菜单表索引
CREATE INDEX IF NOT EXISTS idx_sys_menu_parent_id ON sys_menu(parent_id);
CREATE INDEX IF NOT EXISTS idx_sys_menu_status ON sys_menu(status);
CREATE INDEX IF NOT EXISTS idx_sys_menu_deleted_at ON sys_menu(deleted_at);

-- 字典类型表索引
CREATE INDEX IF NOT EXISTS idx_sys_dict_type_dict_type ON sys_dict_type(dict_type);
CREATE INDEX IF NOT EXISTS idx_sys_dict_type_status ON sys_dict_type(status);
CREATE INDEX IF NOT EXISTS idx_sys_dict_type_deleted_at ON sys_dict_type(deleted_at);

-- 字典数据表索引
CREATE INDEX IF NOT EXISTS idx_sys_dict_data_dict_type ON sys_dict_data(dict_type);
CREATE INDEX IF NOT EXISTS idx_sys_dict_data_dict_value ON sys_dict_data(dict_value);
CREATE INDEX IF NOT EXISTS idx_sys_dict_data_status ON sys_dict_data(status);
CREATE INDEX IF NOT EXISTS idx_sys_dict_data_deleted_at ON sys_dict_data(deleted_at);

-- 字典表索引
CREATE INDEX IF NOT EXISTS idx_sys_dictionary_type ON sys_dictionary(type);
CREATE INDEX IF NOT EXISTS idx_sys_dictionary_type_code ON sys_dictionary(type, code);
CREATE INDEX IF NOT EXISTS idx_sys_dictionary_deleted_at ON sys_dictionary(deleted_at);

-- 系统配置表索引
CREATE INDEX IF NOT EXISTS idx_sys_config_config_key ON sys_config(config_key);
CREATE INDEX IF NOT EXISTS idx_sys_config_config_type ON sys_config(config_type);
CREATE INDEX IF NOT EXISTS idx_sys_config_deleted_at ON sys_config(deleted_at);

-- 登录日志表索引
CREATE INDEX IF NOT EXISTS idx_sys_login_log_username ON sys_login_log(username);
CREATE INDEX IF NOT EXISTS idx_sys_login_log_ip ON sys_login_log(ip);
CREATE INDEX IF NOT EXISTS idx_sys_login_log_status ON sys_login_log(status);
CREATE INDEX IF NOT EXISTS idx_sys_login_log_login_time ON sys_login_log(login_time);

-- 异常日志表索引
CREATE INDEX IF NOT EXISTS idx_sys_exception_log_username ON sys_exception_log(username);
CREATE INDEX IF NOT EXISTS idx_sys_exception_log_exception_name ON sys_exception_log(exception_name);
CREATE INDEX IF NOT EXISTS idx_sys_exception_log_create_time ON sys_exception_log(create_time);

-- 操作日志表索引
CREATE INDEX IF NOT EXISTS idx_operation_log_username ON operation_log(username);
CREATE INDEX IF NOT EXISTS idx_operation_log_operation ON operation_log(operation);
CREATE INDEX IF NOT EXISTS idx_operation_log_created_at ON operation_log(created_at);
CREATE INDEX IF NOT EXISTS idx_operation_log_status ON operation_log(status);
CREATE INDEX IF NOT EXISTS idx_operation_log_deleted_at ON operation_log(deleted_at);

-- 系统公告表索引
CREATE INDEX IF NOT EXISTS idx_sys_notice_notice_type ON sys_notice(notice_type);
CREATE INDEX IF NOT EXISTS idx_sys_notice_status ON sys_notice(status);
CREATE INDEX IF NOT EXISTS idx_sys_notice_deleted_at ON sys_notice(deleted_at);

-- 用户消息表索引
CREATE INDEX IF NOT EXISTS idx_sys_user_message_user_id ON sys_user_message(user_id);
CREATE INDEX IF NOT EXISTS idx_sys_user_message_notice_id ON sys_user_message(notice_id);
CREATE INDEX IF NOT EXISTS idx_sys_user_message_is_read ON sys_user_message(is_read);
CREATE INDEX IF NOT EXISTS idx_sys_user_message_deleted_at ON sys_user_message(deleted_at);

-- 文件管理表索引
CREATE INDEX IF NOT EXISTS idx_sys_file_file_type ON sys_file(file_type);
CREATE INDEX IF NOT EXISTS idx_sys_file_deleted_at ON sys_file(deleted_at);

-- OAuth2客户端表索引
CREATE INDEX IF NOT EXISTS idx_oauth2_client_client_id ON oauth2_client(client_id);
CREATE INDEX IF NOT EXISTS idx_oauth2_client_enabled ON oauth2_client(enabled);
CREATE INDEX IF NOT EXISTS idx_oauth2_client_deleted_at ON oauth2_client(deleted_at);