-- Purchase line items: durably persist what each purchase contained
-- (per-line product, quantity, unit cost, batch/expiry) instead of only
-- inferring it from merged stock rows. Additive and non-breaking.

CREATE TABLE IF NOT EXISTS `purchase_items` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `purchase_id` BIGINT NOT NULL,
  `product_id` BIGINT NOT NULL,
  `quantity` INT NOT NULL DEFAULT 0,
  `unit_cost` DECIMAL(11,2) NOT NULL DEFAULT 0.00,
  `total_cost` DECIMAL(11,2) NOT NULL DEFAULT 0.00,
  `batch_number` VARCHAR(255) NULL DEFAULT NULL,
  `expiry_date` DATE NULL DEFAULT NULL,
  `notes` TEXT NULL DEFAULT NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_purchase_items_purchase` (`purchase_id`),
  KEY `idx_purchase_items_product` (`product_id`)
);