-- Parcel pack/label lifecycle + delivery status + tenant staff hardening
-- Safe to run manually on MySQL if not using sequelize sync.

ALTER TABLE parcel_holdings
  MODIFY COLUMN location_id BIGINT UNSIGNED NULL,
  ADD COLUMN parcel_activation_status ENUM('INACTIVE','ACTIVE') NOT NULL DEFAULT 'INACTIVE' AFTER ready_for_dispatch_id,
  ADD COLUMN parcel_activated_at DATETIME NULL AFTER parcel_activation_status,
  ADD COLUMN label_pending TINYINT NOT NULL DEFAULT 1 AFTER parcel_activated_at;

CREATE INDEX idx_parcel_activation_status ON parcel_holdings (tenant_id, parcel_activation_status, parcel_inventory_status, is_delete);
CREATE INDEX idx_parcel_fulfilment_activation ON parcel_holdings (tenant_id, fulfilment_order_id, parcel_activation_status, is_delete);

ALTER TABLE orders
  MODIFY COLUMN requested_service_type ENUM('INBOUND','FULFILMENT_ONLY','DELIVERY_ONLY','FULFILMENT_AND_DELIVERY','PARCEL_DELIVERY','RETURN') NOT NULL DEFAULT 'DELIVERY_ONLY',
  MODIFY COLUMN delivery_status ENUM('IN_FULFILMENT','NOT_ARRIVED_IN_WAREHOUSE','READY_FOR_DISPATCH','HANDOVER_TO_DRIVER','OUT_FOR_DELIVERY','DELIVERED','FAILED','CANCELED','CANCELLED','RETURNED_TO_WAREHOUSE','PENDING','DRIVER_ASSIGNED','HANDED_OVER','RETURNED') NULL;

CREATE INDEX idx_orders_delivery_status_v11 ON orders (tenant_id, delivery_status, current_status, is_delete);
CREATE INDEX idx_timeline_order_v11 ON order_timeline_events (tenant_id, order_id, created_at, is_delete);
CREATE INDEX idx_users_tenant_phone_v11 ON users (tenant_id, phone, user_type, is_delete);
