-- Nay Store barcode admin/API patch - 2026-04-27
-- Run from phpMyAdmin if you want to force the schema update manually.
-- The patched admin product files also try to create these columns automatically.

SET @db_name := DATABASE();

SET @has_products_barcode := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'products' AND COLUMN_NAME = 'barcode'
);
SET @sql := IF(@has_products_barcode = 0,
  'ALTER TABLE `products` ADD COLUMN `barcode` VARCHAR(160) DEFAULT NULL AFTER `sku`',
  'SELECT "products.barcode already exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_variants_barcode := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'product_variants' AND COLUMN_NAME = 'barcode'
);
SET @sql := IF(@has_variants_barcode = 0,
  'ALTER TABLE `product_variants` ADD COLUMN `barcode` VARCHAR(160) DEFAULT NULL AFTER `sku`',
  'SELECT "product_variants.barcode already exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE `products`
SET `barcode` = `sku`
WHERE (`barcode` IS NULL OR `barcode` = '') AND `sku` IS NOT NULL AND `sku` != '';

UPDATE `product_variants`
SET `barcode` = `sku`
WHERE (`barcode` IS NULL OR `barcode` = '') AND `sku` IS NOT NULL AND `sku` != '';

SET @has_products_barcode_idx := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_products_barcode'
);
SET @sql := IF(@has_products_barcode_idx = 0,
  'ALTER TABLE `products` ADD INDEX `idx_products_barcode` (`barcode`)',
  'SELECT "idx_products_barcode already exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_variants_barcode_idx := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'product_variants' AND INDEX_NAME = 'idx_product_variants_barcode'
);
SET @sql := IF(@has_variants_barcode_idx = 0,
  'ALTER TABLE `product_variants` ADD INDEX `idx_product_variants_barcode` (`barcode`)',
  'SELECT "idx_product_variants_barcode already exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
