-- Apply once, after the base pet_shop schema has been created.
-- These indexes match the WHERE + ORDER BY clauses used by the PHP models.

ALTER TABLE customer_addresses
    ADD INDEX idx_customer_addresses_user_default_id (user_id, is_default, id);

ALTER TABLE contacts
    ADD INDEX idx_contacts_created (created_at),
    ADD INDEX idx_contacts_status_created (status, created_at);

ALTER TABLE gallery_items
    ADD INDEX idx_gallery_visible_category_sort (is_visible, category, sort_order, created_at);

ALTER TABLE order_status_logs
    ADD INDEX idx_order_status_logs_order_created (order_id, created_at);

ALTER TABLE orders
    ADD INDEX idx_orders_customer_created (customer_id, created_at),
    ADD INDEX idx_orders_customer_status_created (customer_id, status, created_at),
    ADD INDEX idx_orders_status_created (status, created_at),
    ADD INDEX idx_orders_status_completed (status, completed_at);

ALTER TABLE post_categories
    ADD INDEX idx_post_categories_active_name (is_active, name);

ALTER TABLE posts
    ADD INDEX idx_posts_status_created (status, created_at),
    ADD INDEX idx_posts_category_status_published (category_id, status, published_at);

ALTER TABLE product_categories
    ADD INDEX idx_product_categories_active_sort_name (is_active, sort_order, name);

ALTER TABLE product_images
    ADD INDEX idx_product_images_product_sort_id (product_id, sort_order, id);

ALTER TABLE product_reviews
    ADD INDEX idx_product_reviews_customer_created (customer_id, created_at),
    ADD INDEX idx_product_reviews_product_status_created (product_id, status, created_at),
    ADD INDEX idx_product_reviews_status_created (status, created_at);

ALTER TABLE products
    ADD INDEX idx_products_status_created (status, created_at),
    ADD INDEX idx_products_category_status_created (category_id, status, created_at),
    ADD INDEX idx_products_featured_rank (status, is_featured, sold_quantity, created_at);

ALTER TABLE promotions
    ADD INDEX idx_promotions_created (created_at);

ALTER TABLE users
    ADD INDEX idx_users_role_created (role, created_at);