SET @column_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'usuarios'
    AND COLUMN_NAME = 'id_vendedor'
);

SET @sql := IF(
  @column_exists = 0,
  "ALTER TABLE usuarios ADD COLUMN id_vendedor INT(11) NULL AFTER id_motorista",
  "SELECT 'Column id_vendedor already exists' AS message"
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'usuarios'
    AND INDEX_NAME = 'FK_usuarios_vendedor'
);

SET @sql := IF(
  @index_exists = 0,
  "ALTER TABLE usuarios ADD INDEX FK_usuarios_vendedor (id_vendedor)",
  "SELECT 'Index FK_usuarios_vendedor already exists' AS message"
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @fk_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'usuarios'
    AND CONSTRAINT_NAME = 'FK_usuarios_vendedor'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);

SET @sql := IF(
  @fk_exists = 0,
  "ALTER TABLE usuarios ADD CONSTRAINT FK_usuarios_vendedor FOREIGN KEY (id_vendedor) REFERENCES vendedores (id) ON DELETE SET NULL ON UPDATE CASCADE",
  "SELECT 'Foreign key FK_usuarios_vendedor already exists' AS message"
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
