SET @customer_category_pricing_tier_id = (
  SELECT IF(
    EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'customer_categories'
        AND column_name = 'pricing_tier_id'
    ),
    'SELECT 1',
    'ALTER TABLE customer_categories ADD COLUMN pricing_tier_id BIGINT UNSIGNED NULL AFTER description'
  )
);
PREPARE customer_category_pricing_tier_id_stmt FROM @customer_category_pricing_tier_id;
EXECUTE customer_category_pricing_tier_id_stmt;
DEALLOCATE PREPARE customer_category_pricing_tier_id_stmt;

SET @customer_category_pricing_tier_index = (
  SELECT IF(
    EXISTS (
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE()
        AND table_name = 'customer_categories'
        AND index_name = 'idx_customer_categories_pricing_tier'
    ),
    'SELECT 1',
    'ALTER TABLE customer_categories ADD INDEX idx_customer_categories_pricing_tier (pricing_tier_id, active)'
  )
);
PREPARE customer_category_pricing_tier_index_stmt FROM @customer_category_pricing_tier_index;
EXECUTE customer_category_pricing_tier_index_stmt;
DEALLOCATE PREPARE customer_category_pricing_tier_index_stmt;

SET @customer_category_pricing_tier_fk = (
  SELECT IF(
    EXISTS (
      SELECT 1 FROM information_schema.referential_constraints
      WHERE constraint_schema = DATABASE()
        AND constraint_name = 'fk_customer_categories_pricing_tier'
    ),
    'SELECT 1',
    'ALTER TABLE customer_categories ADD CONSTRAINT fk_customer_categories_pricing_tier FOREIGN KEY (pricing_tier_id) REFERENCES pricing_tiers(id) ON DELETE SET NULL'
  )
);
PREPARE customer_category_pricing_tier_fk_stmt FROM @customer_category_pricing_tier_fk;
EXECUTE customer_category_pricing_tier_fk_stmt;
DEALLOCATE PREPARE customer_category_pricing_tier_fk_stmt;