USE requerimentos_loteamentos;

SET @db_name = DATABASE();

SET @sql = IF (
    EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'requerimentos' AND COLUMN_NAME = 'tipo_requerimento'
    ),
    'SELECT 1',
    "ALTER TABLE requerimentos ADD COLUMN tipo_requerimento ENUM('LOTEAMENTO','CONSTRUCAO') NOT NULL DEFAULT 'LOTEAMENTO' AFTER previsao_entrega"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF (
    EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'requerimentos' AND COLUMN_NAME = 'numero_correspondencia'
    ),
    'SELECT 1',
    "ALTER TABLE requerimentos
        ADD COLUMN numero_correspondencia VARCHAR(20) DEFAULT NULL AFTER endereco_correspondencia,
        ADD COLUMN municipio VARCHAR(120) DEFAULT 'CÁSSIA' AFTER bairro,
        ADD COLUMN uf VARCHAR(5) DEFAULT 'MG' AFTER municipio,
        ADD COLUMN endereco_obra VARCHAR(255) DEFAULT NULL AFTER endereco_projeto,
        ADD COLUMN numero_obra VARCHAR(20) DEFAULT NULL AFTER endereco_obra,
        ADD COLUMN lote VARCHAR(20) DEFAULT NULL AFTER numero_obra,
        ADD COLUMN quadra VARCHAR(20) DEFAULT NULL AFTER lote,
        ADD COLUMN area_terreno VARCHAR(50) DEFAULT NULL AFTER area_total,
        ADD COLUMN area_construir VARCHAR(50) DEFAULT NULL AFTER area_terreno,
        ADD COLUMN area_existente VARCHAR(50) DEFAULT NULL AFTER area_construir,
        ADD COLUMN valor_venal VARCHAR(50) DEFAULT NULL AFTER area_existente,
        ADD COLUMN area_regularizar VARCHAR(50) DEFAULT NULL AFTER valor_venal,
        ADD COLUMN alvara_construcao TINYINT(1) NOT NULL DEFAULT 0 AFTER metragem_linear_arruamento,
        ADD COLUMN regularizacao TINYINT(1) NOT NULL DEFAULT 0 AFTER alvara_construcao,
        ADD COLUMN mpp TINYINT(1) NOT NULL DEFAULT 0 AFTER regularizacao,
        ADD COLUMN ampliacao TINYINT(1) NOT NULL DEFAULT 0 AFTER mpp,
        ADD COLUMN demolicao_art TINYINT(1) NOT NULL DEFAULT 0 AFTER ampliacao,
        ADD COLUMN reforma_sem_acrescimo TINYINT(1) NOT NULL DEFAULT 0 AFTER demolicao_art,
        ADD COLUMN construcao_30m TINYINT(1) NOT NULL DEFAULT 0 AFTER reforma_sem_acrescimo,
        ADD COLUMN aprovacao_fibra TINYINT(1) NOT NULL DEFAULT 0 AFTER construcao_30m,
        ADD COLUMN liberacao_erb TINYINT(1) NOT NULL DEFAULT 0 AFTER aprovacao_fibra,
        ADD COLUMN cert_localizacao TINYINT(1) NOT NULL DEFAULT 0 AFTER liberacao_erb,
        ADD COLUMN cert_numero TINYINT(1) NOT NULL DEFAULT 0 AFTER cert_localizacao,
        ADD COLUMN cert_averbacao TINYINT(1) NOT NULL DEFAULT 0 AFTER cert_numero,
        ADD COLUMN cert_incra TINYINT(1) NOT NULL DEFAULT 0 AFTER cert_averbacao,
        ADD COLUMN taxa_decreto_desmembramento TINYINT(1) NOT NULL DEFAULT 0 AFTER cert_incra,
        ADD COLUMN habite_se TINYINT(1) NOT NULL DEFAULT 0 AFTER taxa_decreto_desmembramento,
        ADD COLUMN taxa_aprovacao_projetos TINYINT(1) NOT NULL DEFAULT 0 AFTER habite_se,
        ADD COLUMN ligacao_esgoto TINYINT(1) NOT NULL DEFAULT 0 AFTER taxa_aprovacao_projetos,
        ADD COLUMN declaracao_uso_solo TINYINT(1) NOT NULL DEFAULT 0 AFTER ligacao_esgoto,
        ADD COLUMN exame_erb TINYINT(1) NOT NULL DEFAULT 0 AFTER declaracao_uso_solo,
        ADD COLUMN taxa_valor_venal_5 TINYINT(1) NOT NULL DEFAULT 0 AFTER exame_erb,
        ADD COLUMN codigo_interno VARCHAR(30) DEFAULT NULL AFTER observacoes,
        ADD COLUMN excluido TINYINT(1) NOT NULL DEFAULT 0 AFTER codigo_interno"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
