-- Phase 3: selling/buying a product in multiple units (e.g. piece + box of 24).
-- Stock is always kept in BASE units; each row here is an alternate unit with a
-- conversion factor to base and its own price/barcode. The base unit has
-- factor_to_base = 1 and is_base = 1. Additive — products with no rows behave
-- exactly as before (implicit single base unit).

CREATE TABLE IF NOT EXISTS `product_units` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT NOT NULL,
  `unit_id` BIGINT NULL DEFAULT NULL,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  `factor_to_base` DECIMAL(12,3) NOT NULL DEFAULT 1.000,
  `price` DECIMAL(11,2) NOT NULL DEFAULT 0.00,
  `wholesale_price` DECIMAL(11,2) NULL DEFAULT NULL,
  `barcode` VARCHAR(255) NULL DEFAULT NULL,
  `is_base` TINYINT NOT NULL DEFAULT 0,
  `status` INT NOT NULL DEFAULT 1,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_product_units_product` (`product_id`),
  KEY `idx_product_units_barcode` (`barcode`)
);