USE bulo;

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

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

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

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

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

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

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

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

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

UPDATE usuarios
SET primeiro_nome = COALESCE(primeiro_nome, SUBSTRING_INDEX(nome, ' ', 1)),
    sobrenome = COALESCE(NULLIF(sobrenome, ''), NULLIF(TRIM(SUBSTRING(nome, LENGTH(SUBSTRING_INDEX(nome, ' ', 1)) + 1)), ''))
WHERE tipo = 'motorista';
