-- Point 5: Stock reservation / deduction / release rules
-- Use this when managing schema manually. If using local Sequelize sync, run: npm run db:sync

ALTER TABLE order_items
  ADD COLUMN IF NOT EXISTS reserved_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS picked_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS packed_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS dispatched_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS delivered_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS stock_reservation_status ENUM('PENDING','RESERVED','COMMITTED','RELEASED','FAILED') NOT NULL DEFAULT 'PENDING';

ALTER TABLE orders
  ADD COLUMN IF NOT EXISTS stock_validated_at DATETIME NULL,
  ADD COLUMN IF NOT EXISTS stock_reserved_at DATETIME NULL,
  ADD COLUMN IF NOT EXISTS stock_committed_at DATETIME NULL;

-- MySQL enum modification is shown explicitly because existing databases may need it.
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') NOT NULL;

CREATE INDEX IF NOT EXISTS idx_inv_stock_available_reserve ON inventory_stocks (tenant_id, client_id, warehouse_id, sku_code, available_qty, reserved_qty, is_delete);
CREATE INDEX IF NOT EXISTS idx_order_item_reservation ON order_items (tenant_id, order_id, stock_reservation_status, is_delete);
CREATE INDEX IF NOT EXISTS idx_stock_movement_ref ON stock_movements (tenant_id, reference_type, reference_id, is_delete);
