-- Driver mobile API performance indexes and safety notes
-- Safe to run repeatedly on MySQL 8+ only after checking duplicate index names in older environments.

CREATE INDEX idx_driver_mobile_orders_assigned
  ON orders (tenant_id, assigned_driver_id, current_status, is_delete, created_at);

CREATE INDEX idx_driver_mobile_orders_search
  ON orders (tenant_id, order_no, customer_phone, is_delete);

CREATE INDEX idx_driver_mobile_manifest_driver
  ON manifests (tenant_id, driver_id, status, is_delete, created_at);

CREATE INDEX idx_driver_mobile_manifest_orders_lookup
  ON manifest_orders (tenant_id, manifest_id, order_id, is_delete);

CREATE INDEX idx_driver_mobile_attempt_latest
  ON delivery_attempts (tenant_id, order_id, driver_id, attempted_at, is_delete);

CREATE INDEX idx_driver_mobile_cod_pending
  ON cod_collections (tenant_id, driver_id, settlement_status, pending_from_driver, is_delete);

CREATE INDEX idx_driver_mobile_location_latest
  ON driver_location_updates (tenant_id, driver_id, created_at);
