SET NAMES utf8mb4;

SET @has_business_user_id := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ad_subscriptions'
    AND COLUMN_NAME = 'business_user_id'
);
SET @sql_add_business_user_id := IF(
  @has_business_user_id = 0,
  'ALTER TABLE ad_subscriptions ADD COLUMN business_user_id INT UNSIGNED DEFAULT NULL AFTER ad_product_id',
  'SELECT 1'
);
PREPARE stmt_add_business_user_id FROM @sql_add_business_user_id;
EXECUTE stmt_add_business_user_id;
DEALLOCATE PREPARE stmt_add_business_user_id;

SET @has_idx_business_user := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ad_subscriptions'
    AND INDEX_NAME = 'idx_ad_subs_business_user'
);
SET @sql_add_idx_business_user := IF(
  @has_idx_business_user = 0,
  'ALTER TABLE ad_subscriptions ADD KEY idx_ad_subs_business_user (business_user_id)',
  'SELECT 1'
);
PREPARE stmt_add_idx_business_user FROM @sql_add_idx_business_user;
EXECUTE stmt_add_idx_business_user;
DEALLOCATE PREPARE stmt_add_idx_business_user;

SET @has_fk_business_user := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ad_subscriptions'
    AND CONSTRAINT_NAME = 'fk_ad_subs_business_user'
);
SET @sql_add_fk_business_user := IF(
  @has_fk_business_user = 0,
  'ALTER TABLE ad_subscriptions ADD CONSTRAINT fk_ad_subs_business_user FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt_add_fk_business_user FROM @sql_add_fk_business_user;
EXECUTE stmt_add_fk_business_user;
DEALLOCATE PREPARE stmt_add_fk_business_user;