USE bulo;

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

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

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

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

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

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

UPDATE usuarios
SET
  cnh_status = CASE
    WHEN status_verificacao = 'aprovado' THEN 'aprovado'
    WHEN status_verificacao = 'reprovado' THEN 'reprovado'
    ELSE 'pendente'
  END,
  documento_veiculo_status = CASE
    WHEN status_verificacao = 'aprovado' THEN 'aprovado'
    WHEN status_verificacao = 'reprovado' THEN 'reprovado'
    ELSE 'pendente'
  END,
  selfie_status = CASE
    WHEN status_verificacao = 'aprovado' THEN 'aprovado'
    WHEN status_verificacao = 'reprovado' THEN 'reprovado'
    ELSE 'pendente'
  END
WHERE tipo = 'motorista';

UPDATE usuarios
SET
  status_verificacao = CASE
    WHEN cnh_status = 'aprovado' AND documento_veiculo_status = 'aprovado' AND selfie_status = 'aprovado' THEN 'aprovado'
    WHEN cnh_status = 'reprovado' OR documento_veiculo_status = 'reprovado' OR selfie_status = 'reprovado' THEN 'reprovado'
    ELSE 'pendente'
  END,
  observacao_verificacao = CASE
    WHEN cnh_status = 'aprovado' AND documento_veiculo_status = 'aprovado' AND selfie_status = 'aprovado' THEN NULL
    WHEN cnh_status = 'reprovado' OR documento_veiculo_status = 'reprovado' OR selfie_status = 'reprovado' THEN 'Um ou mais documentos foram reprovados.'
    ELSE 'Aguardando revisao dos documentos.'
  END
WHERE tipo = 'motorista';
