CREATE TABLE IF NOT EXISTS `iitem_new` (
  `item_id` int(11) NOT NULL,
  `unique_code` varchar(50) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `comment` text DEFAULT NULL,
  `shelf_status` varchar(50) DEFAULT 'on_shelf',
  `type` varchar(50) DEFAULT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `condition_status` varchar(50) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `status` varchar(100) DEFAULT NULL,
  `serial_number` varchar(100) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `warranty_months` int(11) DEFAULT 0,
  `technical_specs` varchar(255) DEFAULT NULL,
  `current_location_id` int(11) DEFAULT 7,
  `barcode_type` varchar(20) NOT NULL DEFAULT 'CODE128',
  `barcode_data` varchar(255) DEFAULT NULL,
  `barcode_image_path` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `unique_code` (`unique_code`),
  KEY `created_by` (`created_by`),
  KEY `idx_iitem_new_current_location` (`current_location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `iitem_new`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `iitem_new`
  ADD CONSTRAINT `fk_iitem_new_current_location` FOREIGN KEY (`current_location_id`) REFERENCES `branches` (`branch_id`),
  ADD CONSTRAINT `iitem_new_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`);
