USE bulo;

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

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

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

SET @col_status := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'status_verificacao'
);
SET @sql := IF(@col_status = 0, 'ALTER TABLE usuarios ADD COLUMN status_verificacao ENUM(''pendente'',''aprovado'',''reprovado'') NOT NULL DEFAULT ''pendente'' AFTER selfie_url', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

UPDATE usuarios
SET status_verificacao = 'aprovado'
WHERE tipo = 'passageiro' AND status_verificacao = 'pendente';
