CREATE TABLE IF NOT EXISTS product_size_options (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  product_id BIGINT UNSIGNED NOT NULL,
  label VARCHAR(80) NOT NULL,
  sku VARCHAR(100) NULL UNIQUE,
  pack_size_grams INT NOT NULL,
  wholesale_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  retail_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_size_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  INDEX idx_size_product (product_id, active)
);

INSERT IGNORE INTO product_size_options
  (product_id, label, sku, pack_size_grams, wholesale_price, retail_price)
SELECT id,
  CONCAT(TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM FORMAT(pack_size_grams / 1000, 3))), 'kg'),
  NULL,
  pack_size_grams,
  wholesale_price,
  retail_price
FROM products
WHERE NOT EXISTS (
  SELECT 1 FROM product_size_options s WHERE s.product_id = products.id
);

SET @add_order_size = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'order_items'
        AND column_name = 'product_size_id'
    ),
    'SELECT 1',
    'ALTER TABLE order_items ADD COLUMN product_size_id BIGINT UNSIGNED NULL AFTER product_id'
  )
);
PREPARE add_order_size_stmt FROM @add_order_size;
EXECUTE add_order_size_stmt;
DEALLOCATE PREPARE add_order_size_stmt;

SET @add_recurring_size = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'recurring_order_items'
        AND column_name = 'product_size_id'
    ),
    'SELECT 1',
    'ALTER TABLE recurring_order_items ADD COLUMN product_size_id BIGINT UNSIGNED NULL AFTER product_id'
  )
);
PREPARE add_recurring_size_stmt FROM @add_recurring_size;
EXECUTE add_recurring_size_stmt;
DEALLOCATE PREPARE add_recurring_size_stmt;

SET @add_po_flexible_inventory = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'purchase_order_items'
        AND column_name = 'inventory_item_id'
        AND is_nullable = 'YES'
    ),
    'SELECT 1',
    'ALTER TABLE purchase_order_items MODIFY inventory_item_id BIGINT UNSIGNED NULL'
  )
);
PREPARE add_po_flexible_inventory_stmt FROM @add_po_flexible_inventory;
EXECUTE add_po_flexible_inventory_stmt;
DEALLOCATE PREPARE add_po_flexible_inventory_stmt;