USE bulo;

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

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

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

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

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

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

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

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

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

SET @col_doc_venc := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'veiculos' AND COLUMN_NAME = 'documento_vencimento'
);
SET @sql := IF(@col_doc_venc = 0, 'ALTER TABLE veiculos ADD COLUMN documento_vencimento DATE NULL AFTER observacao_revisao', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_aprov_por := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'veiculos' AND COLUMN_NAME = 'aprovado_por'
);
SET @sql := IF(@col_aprov_por = 0, 'ALTER TABLE veiculos ADD COLUMN aprovado_por VARCHAR(80) NULL AFTER documento_vencimento', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

UPDATE usuarios
SET cnh_aprovado_por = COALESCE(cnh_aprovado_por, NULL),
    selfie_aprovado_por = COALESCE(selfie_aprovado_por, NULL)
WHERE tipo = 'motorista';
