CREATE TABLE IF NOT EXISTS `product_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `barcode` varchar(50) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_description` text DEFAULT NULL,
  `unit_measure` varchar(50) NOT NULL DEFAULT 'EA',
  `available_quantity` int(11) NOT NULL DEFAULT 0,
  `branch_id` int(11) NOT NULL,
  `branch_name` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_product_stock_barcode_branch` (`barcode`, `branch_id`),
  KEY `idx_product_stock_branch` (`branch_id`),
  CONSTRAINT `fk_product_stock_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `product_stock` (
  `barcode`,
  `product_name`,
  `product_description`,
  `unit_measure`,
  `available_quantity`,
  `branch_id`,
  `branch_name`
) VALUES
  ('20260526081800842', '10M VGA CABLE', '10M VGA CABLE', 'EA', 8, 4, 'LIMBE BRANCH'),
  ('20260527130330989', 'CCTV POWER SUPPLY', 'CCTV POWER SUPPLY', 'EA', 38, 4, 'LIMBE BRANCH'),
  ('20260523064955292', 'Laptop Power Cable', 'Laptop Power Cable', 'EA', 20, 13, 'TEST_BRANCH'),
  ('20260523123808286', 'INTERNAL LAPTOP KEYBOARD', 'INTERNAL LAPTOP KEYBOARD', 'EA', 50, 13, 'TEST_BRANCH'),
  ('20260522093417613', 'VGA Cable(Back to Back)', 'VGA Cable(Back to Back)', 'EA', 32, 13, 'TEST_BRANCH'),
  ('20260523141420339', 'HDMI SPITTER', 'HDMI SPITTER', 'EA', 100, 13, 'TEST_BRANCH')
ON DUPLICATE KEY UPDATE
  `product_name` = VALUES(`product_name`),
  `product_description` = VALUES(`product_description`),
  `unit_measure` = VALUES(`unit_measure`),
  `available_quantity` = VALUES(`available_quantity`),
  `branch_name` = VALUES(`branch_name`),
  `updated_at` = current_timestamp();
