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

SET @sql := IF(
  @column_exists = 0,
  "ALTER TABLE usuarios ADD COLUMN id_motorista INT(11) NULL AFTER id_conta",
  "SELECT 'Column id_motorista 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_motorista'
);

SET @sql := IF(
  @index_exists = 0,
  "ALTER TABLE usuarios ADD INDEX FK_usuarios_motorista (id_motorista)",
  "SELECT 'Index FK_usuarios_motorista 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_motorista'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);

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

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