-- ============================================================================
-- Dabil — Hot-path indexes for scale (July 2026)
--
-- NOTE: You normally do NOT need to run this by hand. The restaurant admin
-- panel self-heals these indexes on login (includes/schema_indexes.php),
-- skipping any that already exist. This file documents them for manual
-- installs / DBA review.
--
-- Why: every tenant-scoped query (WHERE restaurant_id=? / user_id=?) must hit
-- an index. Without these, MySQL scans entire tables — fine at 100 rows,
-- fatal at 10 million.
-- ============================================================================

ALTER TABLE orders                 ADD INDEX idx_orders_rest_status_created (restaurant_id, order_status, created_at);
ALTER TABLE orders                 ADD INDEX idx_orders_user_created        (user_id, created_at);
ALTER TABLE order_items            ADD INDEX idx_order_items_order          (order_id);
ALTER TABLE menu_items             ADD INDEX idx_menu_rest_avail_cat        (restaurant_id, is_available, category_id);
ALTER TABLE categories             ADD INDEX idx_categories_rest_sort       (restaurant_id, sort_order);
ALTER TABLE restaurant_tables      ADD INDEX idx_tables_rest_num            (restaurant_id, table_number);
ALTER TABLE notifications          ADD INDEX idx_notif_rest_read_created    (restaurant_id, is_read, created_at);
ALTER TABLE customer_notifications ADD INDEX idx_cnotif_user_read_created   (user_id, is_read, created_at);
ALTER TABLE wallet_transactions    ADD INDEX idx_wtx_user_created           (user_id, created_at);
ALTER TABLE wallet_transactions    ADD INDEX idx_wtx_rest_created           (restaurant_id, created_at);
ALTER TABLE wallet_transactions    ADD INDEX idx_wtx_order                  (order_id);
ALTER TABLE wallets                ADD INDEX idx_wallets_rest_type          (restaurant_id, wallet_type);
ALTER TABLE qr_scans               ADD INDEX idx_qr_rest_time               (restaurant_id, scanned_at);
ALTER TABLE restaurant_feedback    ADD INDEX idx_rf_rest                    (restaurant_id);
ALTER TABLE withdrawals            ADD INDEX idx_wd_rest_created            (restaurant_id, created_at);