SET @users_role_client = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'users'
        AND column_name = 'role'
        AND column_type LIKE '%client%'
    ),
    'SELECT 1',
    'ALTER TABLE users MODIFY role ENUM(''admin'', ''client'', ''sales'', ''production'', ''warehouse'', ''accounts'') NOT NULL DEFAULT ''client'''
  )
);
PREPARE users_role_client_stmt FROM @users_role_client;
EXECUTE users_role_client_stmt;
DEALLOCATE PREPARE users_role_client_stmt;

SET @users_customer_id = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'users'
        AND column_name = 'customer_id'
    ),
    'SELECT 1',
    'ALTER TABLE users ADD COLUMN customer_id BIGINT UNSIGNED NULL AFTER role'
  )
);
PREPARE users_customer_id_stmt FROM @users_customer_id;
EXECUTE users_customer_id_stmt;
DEALLOCATE PREPARE users_customer_id_stmt;

SET @users_phone = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'users'
        AND column_name = 'phone'
    ),
    'SELECT 1',
    'ALTER TABLE users ADD COLUMN phone VARCHAR(60) NULL AFTER customer_id'
  )
);
PREPARE users_phone_stmt FROM @users_phone;
EXECUTE users_phone_stmt;
DEALLOCATE PREPARE users_phone_stmt;

SET @users_job_title = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'users'
        AND column_name = 'job_title'
    ),
    'SELECT 1',
    'ALTER TABLE users ADD COLUMN job_title VARCHAR(120) NULL AFTER phone'
  )
);
PREPARE users_job_title_stmt FROM @users_job_title;
EXECUTE users_job_title_stmt;
DEALLOCATE PREPARE users_job_title_stmt;

SET @users_customer_index = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE()
        AND table_name = 'users'
        AND index_name = 'idx_users_customer'
    ),
    'SELECT 1',
    'ALTER TABLE users ADD INDEX idx_users_customer (customer_id, active)'
  )
);
PREPARE users_customer_index_stmt FROM @users_customer_index;
EXECUTE users_customer_index_stmt;
DEALLOCATE PREPARE users_customer_index_stmt;