-- Patch 1.2 - índices opcionais para relatórios cartorários inteligentes
-- Compatível com versões antigas de MySQL/MariaDB: não usa CREATE INDEX IF NOT EXISTS.

SET @db_name = DATABASE();

SET @idx_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='orders' AND INDEX_NAME='idx_orders_shipping_method_id'
);
SET @sql := IF(@idx_exists=0, 'CREATE INDEX idx_orders_shipping_method_id ON orders (shipping_method_id)', 'SELECT "idx_orders_shipping_method_id já existe"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='orders' AND INDEX_NAME='idx_orders_billing_state'
);
SET @sql := IF(@idx_exists=0, 'CREATE INDEX idx_orders_billing_state ON orders (billing_state)', 'SELECT "idx_orders_billing_state já existe"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='orders' AND INDEX_NAME='idx_orders_billing_city'
);
SET @sql := IF(@idx_exists=0, 'CREATE INDEX idx_orders_billing_city ON orders (billing_city)', 'SELECT "idx_orders_billing_city já existe"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='order_item_meta' AND INDEX_NAME='idx_meta_key_value_short'
);
SET @sql := IF(@idx_exists=0, 'CREATE INDEX idx_meta_key_value_short ON order_item_meta (normalized_key, meta_key(80))', 'SELECT "idx_meta_key_value_short já existe"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
