CREATE TABLE `inventory_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL COMMENT 'Product being adjusted',
  `type` enum('adjustment','purchase','sale','return','used_battery') NOT NULL COMMENT 'Log type',
  `quantity` int(11) NOT NULL DEFAULT 0 COMMENT 'Delta (positive = in, negative = out)',
  `balance_after` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Stock level after change',
  `reference_type` varchar(50) NULL DEFAULT NULL COMMENT 'e.g. manual, order, purchase',
  `reference_id` int(10) unsigned NULL DEFAULT NULL,
  `notes` varchar(500) NULL DEFAULT NULL,
  `created_by` int(10) unsigned NULL DEFAULT NULL COMMENT 'Admin user ID',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_reference` (`reference_type`,`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
