USE bulo;

SET @col_ultimo_lat := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'usuarios'
    AND COLUMN_NAME = 'ultimo_lat'
);
SET @sql := IF(
  @col_ultimo_lat = 0,
  'ALTER TABLE usuarios ADD COLUMN ultimo_lat DECIMAL(10,7) NULL AFTER id_veiculo_ativo',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_ultimo_lng := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'usuarios'
    AND COLUMN_NAME = 'ultimo_lng'
);
SET @sql := IF(
  @col_ultimo_lng = 0,
  'ALTER TABLE usuarios ADD COLUMN ultimo_lng DECIMAL(10,7) NULL AFTER ultimo_lat',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_ultimo_rumo := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'usuarios'
    AND COLUMN_NAME = 'ultimo_rumo'
);
SET @sql := IF(
  @col_ultimo_rumo = 0,
  'ALTER TABLE usuarios ADD COLUMN ultimo_rumo DECIMAL(6,2) NULL AFTER ultimo_lng',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_localizacao := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'usuarios'
    AND COLUMN_NAME = 'localizacao_atualizada_em'
);
SET @sql := IF(
  @col_localizacao = 0,
  'ALTER TABLE usuarios ADD COLUMN localizacao_atualizada_em DATETIME NULL AFTER ultimo_rumo',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_tempo_real := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'usuarios'
    AND INDEX_NAME = 'idx_usuarios_tempo_real'
);
SET @sql := IF(
  @idx_tempo_real = 0,
  'ALTER TABLE usuarios ADD INDEX idx_usuarios_tempo_real (tipo, disponivel, localizacao_atualizada_em)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
