-- Script seguro para atualizar a estrutura da tabela products
USE shopify_app;

-- Função para verificar se uma coluna existe
SET @sql = '';

-- Verificar e adicionar total_inventory se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'total_inventory';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN total_inventory INT DEFAULT 0 COMMENT "Estoque total do produto";', 'SELECT "total_inventory já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar inventory_quantity se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'inventory_quantity';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN inventory_quantity INT DEFAULT 0 COMMENT "Quantidade em estoque";', 'SELECT "inventory_quantity já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar inventory_policy se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'inventory_policy';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN inventory_policy VARCHAR(50) NULL COMMENT "Política de estoque (deny, continue)";', 'SELECT "inventory_policy já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar price se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'price';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN price DECIMAL(10,2) NULL COMMENT "Preço original";', 'SELECT "price já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar compare_at_price se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'compare_at_price';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN compare_at_price DECIMAL(10,2) NULL COMMENT "Preço de comparação (preço riscado)";', 'SELECT "compare_at_price já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar requires_shipping se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'requires_shipping';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN requires_shipping BOOLEAN DEFAULT TRUE COMMENT "Se o produto requer frete";', 'SELECT "requires_shipping já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar weight se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'weight';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN weight DECIMAL(8,3) NULL COMMENT "Peso do produto em gramas";', 'SELECT "weight já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar weight_unit se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'weight_unit';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN weight_unit VARCHAR(10) DEFAULT "g" COMMENT "Unidade de peso (g, kg, lb, oz)";', 'SELECT "weight_unit já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar barcode se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'barcode';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN barcode VARCHAR(100) NULL COMMENT "Código de barras";', 'SELECT "barcode já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar sku se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'sku';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN sku VARCHAR(100) NULL COMMENT "SKU do produto";', 'SELECT "sku já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar images se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'images';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN images JSON NULL COMMENT "Array de imagens do produto";', 'SELECT "images já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar variants se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'variants';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN variants JSON NULL COMMENT "Array de variantes do produto";', 'SELECT "variants já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verificar e adicionar options se não existir
SELECT COUNT(*) INTO @exists FROM information_schema.columns 
WHERE table_schema = 'shopify_app' AND table_name = 'products' AND column_name = 'options';
SET @sql = IF(@exists = 0, 'ALTER TABLE products ADD COLUMN options JSON NULL COMMENT "Array de opções do produto";', 'SELECT "options já existe" as status;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Mostrar estrutura final
SELECT "Estrutura atualizada com sucesso!" as resultado;
DESCRIBE products;
