-- Nozzle sizes the shop owns (customers pick one on a quote only when there are two or more), a "back around" date on spools, and a wait list for colors that are out of stock.
CREATE TABLE nozzles (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  size_mm      DECIMAL(3,2) NOT NULL UNIQUE,
  label        VARCHAR(80) NULL,                          -- "Fine detail", "General", "Fast"
  speed_factor DECIMAL(4,2) NOT NULL DEFAULT 1.00,        -- print speed compared with a 0.4 mm nozzle: 0.5 = twice as slow, 2 = twice as fast
  min_layer    DECIMAL(3,2) NULL,                         -- blank = a quarter of the nozzle size
  max_layer    DECIMAL(3,2) NULL,                         -- blank = three quarters of the nozzle size
  active       TINYINT(1) NOT NULL DEFAULT 1,
  sort_order   INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO nozzles (size_mm, label, speed_factor, sort_order) VALUES (0.40, 'General', 1.00, 1);

ALTER TABLE quotes ADD COLUMN nozzle_mm DECIMAL(3,2) NULL AFTER layer_height;
ALTER TABLE spools ADD COLUMN back_on DATE NULL AFTER note;

CREATE TABLE color_waitlist (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email            VARCHAR(190) NOT NULL,
  material_type_id INT UNSIGNED NOT NULL,
  color_name       VARCHAR(100) NOT NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notified_at      DATETIME NULL,
  UNIQUE KEY uq_wait (email, material_type_id, color_name),
  FOREIGN KEY (material_type_id) REFERENCES material_types(id) ON DELETE CASCADE
) ENGINE=InnoDB;
