-- Parcel inventory and dispatch workflow update
-- Safe to apply manually if Sequelize sync is not used. Keep index names short for MySQL.

ALTER TABLE parcel_holdings
  ADD COLUMN fulfilment_order_id BIGINT UNSIGNED NULL AFTER order_id,
  ADD COLUMN delivery_order_id BIGINT UNSIGNED NULL AFTER fulfilment_order_id,
  ADD COLUMN ready_for_dispatch_id BIGINT UNSIGNED NULL AFTER delivery_order_id,
  ADD COLUMN parcel_source_type ENUM('TENANT_FULFILMENT','THIRD_PARTY_SHIPPING','RETURN_REATTEMPT') NOT NULL DEFAULT 'THIRD_PARTY_SHIPPING' AFTER ready_for_dispatch_id,
  ADD COLUMN parcel_inventory_status ENUM('AVAILABLE','HOLD','DAMAGE') NOT NULL DEFAULT 'AVAILABLE' AFTER parcel_source_type,
  ADD COLUMN parcel_created_at DATETIME NULL AFTER parcel_inventory_status;

CREATE INDEX idx_ph_inv_wh ON parcel_holdings (tenant_id, parcel_inventory_status, warehouse_id, is_delete);
CREATE INDEX idx_ph_src_inv ON parcel_holdings (tenant_id, parcel_source_type, parcel_inventory_status, is_delete);
CREATE INDEX idx_ph_del ON parcel_holdings (tenant_id, delivery_order_id, is_delete);
CREATE INDEX idx_ph_ful ON parcel_holdings (tenant_id, fulfilment_order_id, is_delete);

ALTER TABLE ready_for_dispatch
  ADD COLUMN parcel_holding_id BIGINT UNSIGNED NULL AFTER shipping_label_id,
  ADD COLUMN parcel_no VARCHAR(80) NULL AFTER parcel_holding_id,
  ADD COLUMN dispatch_source_type ENUM('FULFILMENT','PARCEL_DELIVERY','REATTEMPT') NOT NULL DEFAULT 'FULFILMENT' AFTER parcel_no,
  ADD COLUMN putaway_location_id BIGINT UNSIGNED NULL AFTER dispatch_source_type;

CREATE INDEX idx_rfd_parcel ON ready_for_dispatch (tenant_id, parcel_holding_id, is_delete);
CREATE INDEX idx_rfd_src_status ON ready_for_dispatch (tenant_id, dispatch_source_type, status, is_delete);

ALTER TABLE stock_movements MODIFY movement_type ENUM('INBOUND_RECEIVE','DAMAGE','HOLD','RETURNED','ADJUSTMENT','TRANSFER','RESERVE','RELEASE_RESERVE','PICK','PACK','COMMIT','DELIVERY_COMMIT','CANCEL_RELEASE','BACK_TO_SHELF','PARCEL_CREATED','PARCEL_READY_DISPATCH') NOT NULL;

ALTER TABLE orders MODIFY delivery_status ENUM('PENDING','DRIVER_ASSIGNED','HANDED_OVER','OUT_FOR_DELIVERY','DELIVERED','FAILED','RETURNED','CANCELLED') NULL;

ALTER TABLE parcel_holdings MODIFY parcel_status ENUM('PARCEL_HOLDING','READY_FOR_DISPATCH','RESERVED_FOR_DELIVERY','OUT_FOR_DELIVERY','DELIVERED','RTO','RETURNED_TO_WAREHOUSE','BACK_TO_SHELF','READY_FOR_CLIENT_RETURN','RETURNED_TO_CLIENT','DAMAGED_HOLD','CLIENT_DECISION_PENDING','CANCELLED') NOT NULL DEFAULT 'PARCEL_HOLDING';
