CREATE TABLE IF NOT EXISTS `item_new` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `unique_code` varchar(50) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `product_code` varchar(120) DEFAULT 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 DEFAULT 0.00,
  `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,
  `source_product_stock_id` int(11) DEFAULT NULL,
  `source_sequence_number` int(11) DEFAULT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `unique_code` (`unique_code`),
  UNIQUE KEY `uniq_item_new_product_stock_sequence` (`source_product_stock_id`, `source_sequence_number`),
  KEY `idx_item_new_current_location` (`current_location_id`),
  KEY `idx_item_new_product_code` (`product_code`),
  KEY `idx_item_new_source_product_stock` (`source_product_stock_id`),
  CONSTRAINT `fk_item_new_current_location` FOREIGN KEY (`current_location_id`) REFERENCES `branches` (`branch_id`),
  CONSTRAINT `fk_item_new_source_product_stock` FOREIGN KEY (`source_product_stock_id`) REFERENCES `product_stock` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `item_new` (
  `unique_code`,
  `item_name`,
  `product_code`,
  `description`,
  `comment`,
  `shelf_status`,
  `type`,
  `unit_price`,
  `created_by`,
  `condition_status`,
  `category`,
  `brand`,
  `status`,
  `serial_number`,
  `model`,
  `warranty_months`,
  `technical_specs`,
  `current_location_id`,
  `barcode_type`,
  `barcode_data`,
  `barcode_image_path`,
  `source_product_stock_id`,
  `source_sequence_number`
)
SELECT
  CONCAT('PENDING-PS', ps.`id`, '-', LPAD(qs.`sequence_number`, 5, '0')) AS `unique_code`,
  LEFT(ps.`product_name`, 100) AS `item_name`,
  ps.`barcode` AS `product_code`,
  ps.`product_description` AS `description`,
  CONCAT('Imported from product_stock row ', ps.`id`, '. Unique code pending update.') AS `comment`,
  'on_shelf' AS `shelf_status`,
  ps.`unit_measure` AS `type`,
  0.00 AS `unit_price`,
  NULL AS `created_by`,
  NULL AS `condition_status`,
  NULL AS `category`,
  NULL AS `brand`,
  NULL AS `status`,
  NULL AS `serial_number`,
  NULL AS `model`,
  0 AS `warranty_months`,
  ps.`product_description` AS `technical_specs`,
  ps.`branch_id` AS `current_location_id`,
  'CODE128' AS `barcode_type`,
  NULL AS `barcode_data`,
  NULL AS `barcode_image_path`,
  ps.`id` AS `source_product_stock_id`,
  qs.`sequence_number` AS `source_sequence_number`
FROM `product_stock` ps
INNER JOIN (
  SELECT ones.n + (tens.n * 10) + (hundreds.n * 100) + (thousands.n * 1000) + 1 AS sequence_number
  FROM (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) ones
  CROSS JOIN (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) tens
  CROSS JOIN (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) hundreds
  CROSS JOIN (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) thousands
) qs
  ON qs.`sequence_number` <= GREATEST(0, FLOOR(ps.`available_quantity`))
WHERE ps.`barcode` IS NOT NULL
  AND ps.`barcode` != ''
ON DUPLICATE KEY UPDATE
  `item_name` = VALUES(`item_name`),
  `product_code` = VALUES(`product_code`),
  `description` = VALUES(`description`),
  `type` = VALUES(`type`),
  `technical_specs` = VALUES(`technical_specs`),
  `current_location_id` = VALUES(`current_location_id`),
  `barcode_data` = VALUES(`barcode_data`),
  `source_product_stock_id` = VALUES(`source_product_stock_id`),
  `source_sequence_number` = VALUES(`source_sequence_number`);
