SET @add_roast_loss = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'products'
        AND column_name = 'roast_loss_percent'
    ),
    'SELECT 1',
    'ALTER TABLE products ADD COLUMN roast_loss_percent DECIMAL(6,3) NOT NULL DEFAULT 15.000 AFTER roast_profile'
  )
);
PREPARE add_roast_loss_stmt FROM @add_roast_loss;
EXECUTE add_roast_loss_stmt;
DEALLOCATE PREPARE add_roast_loss_stmt;

SET @add_lot_status = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'inventory_lots'
        AND column_name = 'status'
    ),
    'SELECT 1',
    'ALTER TABLE inventory_lots ADD COLUMN status ENUM(''available'', ''depleted'') NOT NULL DEFAULT ''available'' AFTER quantity_remaining'
  )
);
PREPARE add_lot_status_stmt FROM @add_lot_status;
EXECUTE add_lot_status_stmt;
DEALLOCATE PREPARE add_lot_status_stmt;

SET @add_depleted_at = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'inventory_lots'
        AND column_name = 'depleted_at'
    ),
    'SELECT 1',
    'ALTER TABLE inventory_lots ADD COLUMN depleted_at DATETIME NULL AFTER status'
  )
);
PREPARE add_depleted_at_stmt FROM @add_depleted_at;
EXECUTE add_depleted_at_stmt;
DEALLOCATE PREPARE add_depleted_at_stmt;

ALTER TABLE reminders
  MODIFY type ENUM(
    'recurring_order', 'invoice_due', 'invoice_overdue', 'low_stock',
    'purchase_due', 'shipping', 'stocktake'
  ) NOT NULL;

UPDATE inventory_lots
SET status = 'depleted',
    depleted_at = COALESCE(depleted_at, CURRENT_TIMESTAMP)
WHERE quantity_remaining <= 0;

INSERT IGNORE INTO inventory_lots
  (inventory_item_id, lot_code, received_date, quantity_received,
   quantity_remaining, status, unit_cost, notes)
SELECT i.id, 'OPENING-STOCK', CURRENT_DATE, i.quantity_on_hand,
  i.quantity_on_hand, 'available', i.average_unit_cost,
  'Opening balance created when lot tracking was enabled'
FROM inventory_items i
WHERE i.category = 'green_bean'
  AND i.quantity_on_hand > 0
  AND NOT EXISTS (
    SELECT 1 FROM inventory_lots l WHERE l.inventory_item_id = i.id
  );