-- Owner/superadmin can hold back a quantity of a product at a shop so it is not
-- sold (e.g. set aside for an order or display). Available-to-sell at a shop =
-- on-hand at that shop − active reservations. Additive; sales subtract this.

CREATE TABLE IF NOT EXISTS `stock_reservations` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT NOT NULL,
  `shop_id` BIGINT NOT NULL,
  `quantity` INT NOT NULL DEFAULT 0,
  `reason` VARCHAR(255) NULL DEFAULT NULL,
  `released` TINYINT NOT NULL DEFAULT 0,
  `created_by` BIGINT NULL DEFAULT NULL,
  `released_by` BIGINT NULL DEFAULT NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_reservations_product_shop` (`product_id`, `shop_id`, `released`)
);