USE bulo;

ALTER TABLE usuarios
  ADD COLUMN IF NOT EXISTS telefone_verificado TINYINT(1) NOT NULL DEFAULT 0 AFTER celular,
  ADD COLUMN IF NOT EXISTS telefone_verificado_em DATETIME NULL AFTER telefone_verificado,
  ADD COLUMN IF NOT EXISTS firebase_phone_uid VARCHAR(128) NULL AFTER telefone_verificado_em,
  ADD COLUMN IF NOT EXISTS firebase_phone VARCHAR(24) NULL AFTER firebase_phone_uid;

SET @idx_telefone_verificado := (
  SELECT COUNT(1)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'usuarios'
    AND index_name = 'idx_usuarios_telefone_verificado'
);
SET @sql := IF(
  @idx_telefone_verificado = 0,
  'CREATE INDEX idx_usuarios_telefone_verificado ON usuarios (telefone_verificado)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_firebase_phone_uid := (
  SELECT COUNT(1)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'usuarios'
    AND index_name = 'idx_usuarios_firebase_phone_uid'
);
SET @sql := IF(
  @idx_firebase_phone_uid = 0,
  'CREATE INDEX idx_usuarios_firebase_phone_uid ON usuarios (firebase_phone_uid)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
