
ALTER TABLE wms_inbound ADD COLUMN location_id INT NULL AFTER product_id;
ALTER TABLE wms_outbound ADD COLUMN location_id INT NULL AFTER product_id;
ALTER TABLE wms_inbound ADD CONSTRAINT fk_in_loc FOREIGN KEY (location_id) REFERENCES wms_locations(id);
ALTER TABLE wms_outbound ADD CONSTRAINT fk_out_loc FOREIGN KEY (location_id) REFERENCES wms_locations(id);

-- Ensure aggregate stock table exists
CREATE TABLE IF NOT EXISTS wms_stock (
  product_id INT PRIMARY KEY,
  qty INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES wms_products(id) ON DELETE CASCADE
);
