-- Users table indexes
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_phone_number ON users(phone_number);
CREATE INDEX IF NOT EXISTS idx_users_user_role ON users(user_role);
CREATE INDEX IF NOT EXISTS idx_users_is_verified ON users(is_verified);
CREATE INDEX IF NOT EXISTS idx_users_user_status ON users(user_status);
CREATE INDEX IF NOT EXISTS idx_users_verify_code ON users(verify_code);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_users_last_login ON users(last_login DESC);

-- Authors table indexes
CREATE INDEX IF NOT EXISTS idx_authors_is_active ON authors(is_active);
CREATE INDEX IF NOT EXISTS idx_authors_created_at ON authors(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_authors_name ON authors(name);

-- Activity logs table indexes
CREATE INDEX IF NOT EXISTS idx_activity_logs_user_id ON activity_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_activity_logs_created_at ON activity_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_activity_logs_action ON activity_logs(action);

-- Roles table indexes
CREATE INDEX IF NOT EXISTS idx_roles_name ON roles(name);

-- Permission table indexes
CREATE INDEX IF NOT EXISTS idx_permission_name ON permission(name);

-- Role_permission table indexes
CREATE INDEX IF NOT EXISTS idx_role_permission_role_id ON role_permission(role_id);
CREATE INDEX IF NOT EXISTS idx_role_permission_permission_id ON role_permission(permission_id);
CREATE INDEX IF NOT EXISTS idx_role_permission_composite ON role_permission(role_id, permission_id);

-- Staff table indexes
CREATE INDEX IF NOT EXISTS idx_staff_email ON staff(email);
CREATE INDEX IF NOT EXISTS idx_staff_role_id ON staff(role_id);
CREATE INDEX IF NOT EXISTS idx_staff_user_status ON staff(user_status);
CREATE INDEX IF NOT EXISTS idx_staff_verify_code ON staff(verify_code);
CREATE INDEX IF NOT EXISTS idx_staff_created_at ON staff(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_staff_last_login ON staff(last_login DESC);

-- Site_settings table indexes
CREATE INDEX IF NOT EXISTS idx_site_settings_created_at ON site_settings(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_site_settings_updated_at ON site_settings(updated_at DESC);

-- Categories table indexes
CREATE INDEX IF NOT EXISTS idx_categories_parent_id ON categories(parent_id);
CREATE INDEX IF NOT EXISTS idx_categories_is_indexable ON categories(is_indexable);
CREATE INDEX IF NOT EXISTS idx_categories_is_active ON categories(is_active);
CREATE INDEX IF NOT EXISTS idx_categories_created_at ON categories(created_at DESC);

-- Categories_translations table indexes
CREATE INDEX IF NOT EXISTS idx_categories_translations_category_id ON categories_translations(category_id);
CREATE INDEX IF NOT EXISTS idx_categories_translations_name ON categories_translations(name);
CREATE INDEX IF NOT EXISTS idx_categories_translations_slug ON categories_translations(slug);
CREATE INDEX IF NOT EXISTS idx_categories_translations_language ON categories_translations(language);
CREATE INDEX IF NOT EXISTS idx_categories_translations_composite ON categories_translations(category_id, language);
CREATE INDEX IF NOT EXISTS idx_categories_translations_created_at ON categories_translations(created_at DESC);

-- Posts table indexes
CREATE INDEX IF NOT EXISTS idx_posts_post_category ON posts(post_category);
CREATE INDEX IF NOT EXISTS idx_posts_author_id ON posts(author_id);
CREATE INDEX IF NOT EXISTS idx_posts_is_indexable ON posts(is_indexable);
CREATE INDEX IF NOT EXISTS idx_posts_is_active ON posts(is_active);
CREATE INDEX IF NOT EXISTS idx_posts_post_create_date ON posts(post_create_date DESC);
CREATE INDEX IF NOT EXISTS idx_posts_post_update_date ON posts(post_update_date DESC);

-- Post_translations table indexes
CREATE INDEX IF NOT EXISTS idx_post_translations_post_id ON post_translations(post_id);
CREATE INDEX IF NOT EXISTS idx_post_translations_post_title ON post_translations(post_title);
CREATE INDEX IF NOT EXISTS idx_post_translations_post_slug ON post_translations(post_slug);
CREATE INDEX IF NOT EXISTS idx_post_translations_language ON post_translations(language);
CREATE INDEX IF NOT EXISTS idx_post_translations_composite ON post_translations(post_id, language);
CREATE INDEX IF NOT EXISTS idx_post_translations_post_meta_title ON post_translations(post_meta_title);

-- Pages table indexes
CREATE INDEX IF NOT EXISTS idx_pages_is_indexable ON pages(is_indexable);
CREATE INDEX IF NOT EXISTS idx_pages_is_active ON pages(is_active);
CREATE INDEX IF NOT EXISTS idx_pages_page_create_date ON pages(page_create_date DESC);
CREATE INDEX IF NOT EXISTS idx_pages_page_update_date ON pages(page_update_date DESC);

-- Pages_translations table indexes
CREATE INDEX IF NOT EXISTS idx_pages_translations_page_id ON pages_translations(page_id);
CREATE INDEX IF NOT EXISTS idx_pages_translations_page_title ON pages_translations(page_title);
CREATE INDEX IF NOT EXISTS idx_pages_translations_page_slug ON pages_translations(page_slug);
CREATE INDEX IF NOT EXISTS idx_pages_translations_language ON pages_translations(language);
CREATE INDEX IF NOT EXISTS idx_pages_translations_composite ON pages_translations(page_id, language);
CREATE INDEX IF NOT EXISTS idx_pages_translations_page_meta_title ON pages_translations(page_meta_title);

-- Products table indexes
CREATE INDEX IF NOT EXISTS idx_products_category_id ON products(category_id);
CREATE INDEX IF NOT EXISTS idx_products_is_indexable ON products(is_indexable);
CREATE INDEX IF NOT EXISTS idx_products_is_active ON products(is_active);
CREATE INDEX IF NOT EXISTS idx_products_product_create_date ON products(product_create_date DESC);
CREATE INDEX IF NOT EXISTS idx_products_product_update_date ON products(product_update_date DESC);

-- Products_translations table indexes
CREATE INDEX IF NOT EXISTS idx_products_translations_product_id ON products_translations(product_id);
CREATE INDEX IF NOT EXISTS idx_products_translations_product_title ON products_translations(product_title);
CREATE INDEX IF NOT EXISTS idx_products_translations_product_slug ON products_translations(product_slug);
CREATE INDEX IF NOT EXISTS idx_products_translations_language ON products_translations(language);
CREATE INDEX IF NOT EXISTS idx_products_translations_composite ON products_translations(product_id, language);
CREATE INDEX IF NOT EXISTS idx_products_translations_product_meta_title ON products_translations(product_meta_title);

-- Component_templates table indexes
CREATE INDEX IF NOT EXISTS idx_component_templates_template_name ON component_templates(template_name);
CREATE INDEX IF NOT EXISTS idx_component_templates_component_type ON component_templates(component_type);
CREATE INDEX IF NOT EXISTS idx_component_templates_category ON component_templates(category);
CREATE INDEX IF NOT EXISTS idx_component_templates_is_active ON component_templates(is_active);
CREATE INDEX IF NOT EXISTS idx_component_templates_created_at ON component_templates(created_at DESC);

-- Services table indexes
CREATE INDEX IF NOT EXISTS idx_services_is_indexable ON services(is_indexable);
CREATE INDEX IF NOT EXISTS idx_services_is_active ON services(is_active);
CREATE INDEX IF NOT EXISTS idx_services_service_create_date ON services(service_create_date DESC);
CREATE INDEX IF NOT EXISTS idx_services_service_update_date ON services(service_update_date DESC);

-- Services_translations table indexes
CREATE INDEX IF NOT EXISTS idx_services_translations_service_id ON services_translations(service_id);
CREATE INDEX IF NOT EXISTS idx_services_translations_service_title ON services_translations(service_title);
CREATE INDEX IF NOT EXISTS idx_services_translations_service_slug ON services_translations(service_slug);
CREATE INDEX IF NOT EXISTS idx_services_translations_language ON services_translations(language);
CREATE INDEX IF NOT EXISTS idx_services_translations_composite ON services_translations(service_id, language);
CREATE INDEX IF NOT EXISTS idx_services_translations_service_meta_title ON services_translations(service_meta_title);

-- Projects table indexes
CREATE INDEX IF NOT EXISTS idx_projects_category_id ON projects(category_id);
CREATE INDEX IF NOT EXISTS idx_projects_is_indexable ON projects(is_indexable);
CREATE INDEX IF NOT EXISTS idx_projects_is_active ON projects(is_active);
CREATE INDEX IF NOT EXISTS idx_projects_project_date ON projects(project_date DESC);
CREATE INDEX IF NOT EXISTS idx_projects_project_location ON projects(project_location);
CREATE INDEX IF NOT EXISTS idx_projects_project_create_date ON projects(project_create_date DESC);

-- Projects_translations table indexes
CREATE INDEX IF NOT EXISTS idx_projects_translations_project_id ON projects_translations(project_id);
CREATE INDEX IF NOT EXISTS idx_projects_translations_project_title ON projects_translations(project_title);
CREATE INDEX IF NOT EXISTS idx_projects_translations_project_slug ON projects_translations(project_slug);
CREATE INDEX IF NOT EXISTS idx_projects_translations_language ON projects_translations(language);
CREATE INDEX IF NOT EXISTS idx_projects_translations_composite ON projects_translations(project_id, language);
CREATE INDEX IF NOT EXISTS idx_projects_translations_project_meta_title ON projects_translations(project_meta_title);

-- Menu table indexes
CREATE INDEX IF NOT EXISTS idx_menu_position ON menu(position);
CREATE INDEX IF NOT EXISTS idx_menu_language ON menu(language);
CREATE INDEX IF NOT EXISTS idx_menu_composite ON menu(position, language);
CREATE INDEX IF NOT EXISTS idx_menu_created_at ON menu(created_at DESC);

-- Menu_items table indexes
CREATE INDEX IF NOT EXISTS idx_menu_items_menu_id ON menu_items(menu_id);
CREATE INDEX IF NOT EXISTS idx_menu_items_parent_id ON menu_items(parent_id);
CREATE INDEX IF NOT EXISTS idx_menu_items_reference_id ON menu_items(reference_id);
CREATE INDEX IF NOT EXISTS idx_menu_items_type ON menu_items(type);
CREATE INDEX IF NOT EXISTS idx_menu_items_language ON menu_items(language);
CREATE INDEX IF NOT EXISTS idx_menu_items_sort_order ON menu_items(sort_order);
CREATE INDEX IF NOT EXISTS idx_menu_items_composite ON menu_items(menu_id, parent_id, sort_order);

-- Reviews table indexes
CREATE INDEX IF NOT EXISTS idx_reviews_rating ON reviews(rating);
CREATE INDEX IF NOT EXISTS idx_reviews_is_active ON reviews(is_active);
CREATE INDEX IF NOT EXISTS idx_reviews_created_at ON reviews(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_reviews_name ON reviews(name);