-- Driver Flutter API alignment v22
-- Safe additive migration. No existing columns are removed or renamed.

ALTER TABLE delivery_attempts
  ADD COLUMN IF NOT EXISTS rescheduled_date DATE NULL AFTER failure_reason,
  ADD COLUMN IF NOT EXISTS correct_location VARCHAR(1000) NULL AFTER rescheduled_date,
  ADD COLUMN IF NOT EXISTS correct_latitude DECIMAL(11,8) NULL AFTER correct_location,
  ADD COLUMN IF NOT EXISTS correct_longitude DECIMAL(11,8) NULL AFTER correct_latitude,
  ADD COLUMN IF NOT EXISTS cancel_reason VARCHAR(500) NULL AFTER correct_longitude;

-- Supporting indexes for assigned-order lookup, driver order tabs and COD pagination.
CREATE INDEX IF NOT EXISTS idx_orders_driver_status_mobile
  ON orders (tenant_id, assigned_driver_id, current_status, is_delete, created_at);

CREATE INDEX IF NOT EXISTS idx_delivery_attempts_driver_order_mobile
  ON delivery_attempts (tenant_id, driver_id, order_id, attempted_at, is_delete);

CREATE INDEX IF NOT EXISTS idx_cod_driver_collected_mobile
  ON cod_collections (tenant_id, driver_id, collected_at, is_delete);
