CREATE TABLE IF NOT EXISTS product_roasted_lots (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  product_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL,
  lot_code VARCHAR(120) NOT NULL,
  roasted_date DATE NOT NULL,
  quantity_roasted_kg DECIMAL(14,3) NOT NULL DEFAULT 0,
  quantity_remaining_kg DECIMAL(14,3) NOT NULL DEFAULT 0,
  status ENUM('available', 'depleted') NOT NULL DEFAULT 'available',
  depleted_at DATETIME NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_roasted_lot_code (lot_code),
  UNIQUE KEY uq_roasted_order_product (order_id, product_id),
  CONSTRAINT fk_roasted_lot_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_roasted_lot_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  INDEX idx_roasted_lot_product (product_id, status, roasted_date)
);