-- Create index for status filtering
CREATE INDEX IF NOT EXISTS idx_audit_leads_status ON audit_leads(status);
CREATE INDEX IF NOT EXISTS idx_audit_leads_created_at ON audit_leads(created_at);
CREATE INDEX IF NOT EXISTS idx_audit_leads_email ON audit_leads(email);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_custom_plan_email ON custom_plan_inquiries(email);
CREATE INDEX IF NOT EXISTS idx_custom_plan_status ON custom_plan_inquiries(status);
CREATE INDEX IF NOT EXISTS idx_custom_plan_created_at ON custom_plan_inquiries(created_at);

-- Admin Users indexes
CREATE INDEX IF NOT EXISTS idx_admin_users_email ON admin_users(email);
CREATE INDEX IF NOT EXISTS idx_admin_users_is_active ON admin_users(is_active);
CREATE INDEX IF NOT EXISTS idx_admin_users_deleted_at ON admin_users(deleted_at) WHERE deleted_at IS NOT NULL;

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

-- Permissions indexes
CREATE INDEX IF NOT EXISTS idx_permissions_name ON permissions(name);
CREATE INDEX IF NOT EXISTS idx_permissions_module ON permissions(module);
CREATE INDEX IF NOT EXISTS idx_permissions_action ON permissions(action);

-- Admin User Roles indexes
CREATE INDEX IF NOT EXISTS idx_admin_user_roles_admin_user_id ON admin_user_roles(admin_user_id);
CREATE INDEX IF NOT EXISTS idx_admin_user_roles_role_id ON admin_user_roles(role_id);

-- Role Permissions indexes
CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id ON role_permissions(role_id);
CREATE INDEX IF NOT EXISTS idx_role_permissions_permission_id ON role_permissions(permission_id);

-- User Sessions indexes
CREATE INDEX IF NOT EXISTS idx_user_sessions_admin_user_id ON user_sessions(admin_user_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_session_token ON user_sessions(session_token);
CREATE INDEX IF NOT EXISTS idx_user_sessions_refresh_token ON user_sessions(refresh_token);
CREATE INDEX IF NOT EXISTS idx_user_sessions_expires_at ON user_sessions(expires_at);

-- Audit Logs indexes
CREATE INDEX IF NOT EXISTS idx_audit_logs_admin_user_id ON audit_logs(admin_user_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_action ON audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_audit_logs_resource_type ON audit_logs(resource_type);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at);

-- Password Reset Tokens indexes
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_admin_user_id ON password_reset_tokens(admin_user_id);
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_token_hash ON password_reset_tokens(token_hash);
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_expires_at ON password_reset_tokens(expires_at);

-- Site Settings indexes
CREATE INDEX IF NOT EXISTS idx_site_settings_name ON site_settings(name);

-- Users indexes
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_google_id ON users(google_id);
CREATE INDEX IF NOT EXISTS idx_users_facebook_id ON users(facebook_id);
CREATE INDEX IF NOT EXISTS idx_users_github_id ON users(github_id);
CREATE INDEX IF NOT EXISTS idx_users_apple_id ON users(apple_id);
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);
CREATE INDEX IF NOT EXISTS idx_users_signup_method ON users(signup_method);
CREATE INDEX IF NOT EXISTS idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_users_email_verified ON users(email_verified);

-- Web User Sessions indexes
CREATE INDEX IF NOT EXISTS idx_web_user_sessions_user_id ON web_user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_web_user_sessions_session_token ON web_user_sessions(session_token);
CREATE INDEX IF NOT EXISTS idx_web_user_sessions_refresh_token ON web_user_sessions(refresh_token);
CREATE INDEX IF NOT EXISTS idx_web_user_sessions_expires_at ON web_user_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_web_user_sessions_last_accessed_at ON web_user_sessions(last_accessed_at);

-- Menu indexes
CREATE INDEX IF NOT EXISTS idx_menu_position ON menu(position);

-- Menu Items 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_sort_order ON menu_items(sort_order);

-- Pages indexes
CREATE INDEX IF NOT EXISTS idx_pages_page_slug ON pages(page_slug);
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);

-- Sidebars indexes
CREATE INDEX IF NOT EXISTS idx_sidebars_name ON sidebars(name);
CREATE INDEX IF NOT EXISTS idx_sidebars_status ON sidebars(status);

-- Authors indexes
CREATE INDEX IF NOT EXISTS idx_authors_slug ON authors(slug);
CREATE INDEX IF NOT EXISTS idx_authors_status ON authors(status);

-- Posts indexes
CREATE INDEX IF NOT EXISTS idx_posts_post_slug ON posts(post_slug);
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_post_sidebar_id ON posts(post_sidebar_id);
CREATE INDEX IF NOT EXISTS idx_posts_post_status ON posts(post_status);
CREATE INDEX IF NOT EXISTS idx_posts_is_featured ON posts(is_featured);
CREATE INDEX IF NOT EXISTS idx_posts_post_create_date ON posts(post_create_date);
CREATE INDEX IF NOT EXISTS idx_posts_post_category_status ON posts(post_category, post_status);
CREATE INDEX IF NOT EXISTS idx_posts_author_status ON posts(author_id, post_status);

-- Redirects indexes
CREATE INDEX IF NOT EXISTS idx_redirects_source_path ON redirects(source_path);
CREATE INDEX IF NOT EXISTS idx_redirects_destination_path ON redirects(destination_path);
CREATE INDEX IF NOT EXISTS idx_redirects_is_active ON redirects(is_active);
CREATE INDEX IF NOT EXISTS idx_redirects_updated_at ON redirects(updated_at);

-- Create Users indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);
CREATE INDEX IF NOT EXISTS idx_users_deleted_at ON users(deleted_at);

-- Websites table indexes
CREATE INDEX IF NOT EXISTS idx_websites_slug ON websites(slug);
CREATE INDEX IF NOT EXISTS idx_websites_domain ON websites(domain);
CREATE INDEX IF NOT EXISTS idx_websites_category_id ON websites(category_id);
CREATE INDEX IF NOT EXISTS idx_websites_status ON websites(status);
CREATE INDEX IF NOT EXISTS idx_websites_featured ON websites(featured);
CREATE INDEX IF NOT EXISTS idx_websites_base_price ON websites(base_price);
CREATE INDEX IF NOT EXISTS idx_websites_created_at ON websites(created_at);
CREATE INDEX IF NOT EXISTS idx_websites_schemas ON websites USING gin(schemas);

-- Packages table indexes
CREATE INDEX IF NOT EXISTS idx_packages_package_type ON packages(package_type);
CREATE INDEX IF NOT EXISTS idx_packages_status ON packages(status);
CREATE INDEX IF NOT EXISTS idx_packages_base_price ON packages(base_price);
CREATE INDEX IF NOT EXISTS idx_packages_is_popular ON packages(is_popular) WHERE is_popular = true;
CREATE INDEX IF NOT EXISTS idx_packages_is_featured ON packages(is_featured) WHERE is_featured = true;
CREATE INDEX IF NOT EXISTS idx_packages_created_at ON packages(created_at);
CREATE INDEX IF NOT EXISTS idx_packages_status_type ON packages(status, package_type) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS idx_packages_features ON packages USING gin(features);

-- Package websites indexes
CREATE INDEX IF NOT EXISTS idx_package_websites_package_id ON package_websites(package_id);
CREATE INDEX IF NOT EXISTS idx_package_websites_website_id ON package_websites(website_id);

CREATE INDEX IF NOT EXISTS idx_users_whatsapp ON users(whatsapp);
CREATE INDEX IF NOT EXISTS idx_users_company ON users(company);
CREATE INDEX IF NOT EXISTS idx_users_country ON users(country);
CREATE INDEX IF NOT EXISTS idx_users_city ON users(city);

-- Orders table indexes
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_orders_package_id ON orders(package_id);
CREATE INDEX IF NOT EXISTS idx_orders_order_number ON orders(order_number);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_orders_payment_status ON orders(payment_status);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);
CREATE INDEX IF NOT EXISTS idx_orders_updated_at ON orders(updated_at);
CREATE INDEX IF NOT EXISTS idx_orders_order_type ON orders(order_type);

-- Order items indexes
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_order_items_website_id ON order_items(website_id);
CREATE INDEX IF NOT EXISTS idx_order_items_status ON order_items(status);
CREATE INDEX IF NOT EXISTS idx_order_items_created_at ON order_items(created_at);

-- Package order items indexes
CREATE INDEX IF NOT EXISTS idx_package_order_items_order_id ON package_order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_package_order_items_package_id ON package_order_items(package_id);
CREATE INDEX IF NOT EXISTS idx_package_order_items_status ON package_order_items(status);

-- Package order websites indexes
CREATE INDEX IF NOT EXISTS idx_package_order_websites_order_item_id ON package_order_websites(package_order_item_id);
CREATE INDEX IF NOT EXISTS idx_package_order_websites_website_id ON package_order_websites(website_id);

-- Order item details indexes
CREATE INDEX IF NOT EXISTS idx_order_item_details_order_item_id ON order_item_details(order_item_id);
CREATE INDEX IF NOT EXISTS idx_order_item_details_package_order_item_id ON order_item_details(package_order_item_id);
CREATE INDEX IF NOT EXISTS idx_order_item_details_content_type ON order_item_details(content_type);
CREATE INDEX IF NOT EXISTS idx_order_item_details_deadline ON order_item_details(deadline);

-- Order documents indexes
CREATE INDEX IF NOT EXISTS idx_order_documents_order_item_id ON order_documents(order_item_id);
CREATE INDEX IF NOT EXISTS idx_order_documents_package_order_item_id ON order_documents(package_order_item_id);
CREATE INDEX IF NOT EXISTS idx_order_documents_uploaded_at ON order_documents(uploaded_at);

CREATE INDEX IF NOT EXISTS idx_support_tickets_user_id ON support_tickets(user_id);
CREATE INDEX IF NOT EXISTS idx_support_tickets_status ON support_tickets(status);
CREATE INDEX IF NOT EXISTS idx_support_tickets_priority ON support_tickets(priority);
CREATE INDEX IF NOT EXISTS idx_support_tickets_created_at ON support_tickets(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_support_replies_ticket_id ON support_replies(ticket_id);
CREATE INDEX IF NOT EXISTS idx_support_replies_user_id ON support_replies(user_id);
CREATE INDEX IF NOT EXISTS idx_support_replies_admin_id ON support_replies(admin_id);


-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_countries_name ON countries(name);
CREATE INDEX IF NOT EXISTS idx_countries_continent ON countries(continent);
CREATE INDEX IF NOT EXISTS idx_countries_phone_code ON countries(phone_code);
CREATE INDEX IF NOT EXISTS idx_countries_is_active ON countries(is_active);