
CREATE TABLE IF NOT EXISTS product_barcodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    barcode VARCHAR(50) NOT NULL UNIQUE,
    quantity_multiplier INT DEFAULT 1 COMMENT '1 for single, >1 for bundles',
    description VARCHAR(100) COMMENT 'e.g. Carton, Box',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

ALTER TABLE product_barcodes ADD INDEX idx_barcode (barcode);
