SET @add_po_item_type = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'purchase_order_items'
        AND column_name = 'item_type'
    ),
    'SELECT 1',
    'ALTER TABLE purchase_order_items ADD COLUMN item_type ENUM(''inventory'', ''non_inventory'') NOT NULL DEFAULT ''inventory'' AFTER purchase_order_id'
  )
);
PREPARE add_po_item_type_stmt FROM @add_po_item_type;
EXECUTE add_po_item_type_stmt;
DEALLOCATE PREPARE add_po_item_type_stmt;

UPDATE purchase_order_items
SET item_type = CASE WHEN inventory_item_id IS NULL THEN 'non_inventory' ELSE 'inventory' END;

CREATE TABLE IF NOT EXISTS order_roasts (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  product_size_id BIGINT UNSIGNED NULL,
  roast_date DATE NULL,
  roast_level VARCHAR(80) NULL,
  planned_roasted_kg DECIMAL(12,3) NOT NULL DEFAULT 0,
  actual_green_kg DECIMAL(12,3) NULL,
  actual_roasted_kg DECIMAL(12,3) NULL,
  batch_code VARCHAR(100) NULL,
  status ENUM('planned', 'roasting', 'resting', 'packed', 'completed') NOT NULL DEFAULT 'planned',
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_order_roast_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_roast_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_order_roast_size FOREIGN KEY (product_size_id) REFERENCES product_size_options(id) ON DELETE SET NULL,
  INDEX idx_order_roast_order (order_id, roast_date),
  INDEX idx_order_roast_product (product_id, status)
);

SET @add_roasted_lot_order_index = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE()
        AND table_name = 'product_roasted_lots'
        AND index_name = 'idx_roasted_lot_order'
    ),
    'SELECT 1',
    'ALTER TABLE product_roasted_lots ADD INDEX idx_roasted_lot_order (order_id)'
  )
);
PREPARE add_roasted_lot_order_index_stmt FROM @add_roasted_lot_order_index;
EXECUTE add_roasted_lot_order_index_stmt;
DEALLOCATE PREPARE add_roasted_lot_order_index_stmt;

SET @drop_roasted_order_product = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE()
        AND table_name = 'product_roasted_lots'
        AND index_name = 'uq_roasted_order_product'
    ),
    'ALTER TABLE product_roasted_lots DROP INDEX uq_roasted_order_product',
    'SELECT 1'
  )
);
PREPARE drop_roasted_order_product_stmt FROM @drop_roasted_order_product;
EXECUTE drop_roasted_order_product_stmt;
DEALLOCATE PREPARE drop_roasted_order_product_stmt;