-- ===================================================================== -- METADADOS — Pré-registros públicos: País → Região → Estado → Cidade → Bairro -- -- Convenções: -- - Prefixo tb_metadata_ pra todas -- - id_metadata_ pra PK -- - metadata_
_ pras colunas -- - Coordenadas: lat/lng DECIMAL(10,7) + location POINT NULL (uso futuro spatial) -- - DDD em cidade (fonte) e bairro (snapshot) -- ===================================================================== SET NAMES utf8mb4; -- --------------------------------------------------------------------- -- tb_metadata_country -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `tb_metadata_country` ( `id_metadata_country` INT(11) NOT NULL AUTO_INCREMENT, `metadata_country_name` VARCHAR(100) NOT NULL, `metadata_country_iso` VARCHAR(3) NOT NULL, -- ISO 3166-1 alpha-2/alpha-3 (BR / BRA) `metadata_country_ddi` VARCHAR(5) DEFAULT NULL, -- "+55" `metadata_country_zipcode` VARCHAR(20) DEFAULT NULL, -- CEP base/máscara (opcional) `metadata_country_lat` DECIMAL(10,7) DEFAULT NULL, `metadata_country_lng` DECIMAL(10,7) DEFAULT NULL, `metadata_country_location` POINT DEFAULT NULL, -- POINT espacial (uso futuro) `metadata_country_status` TINYINT(1) NOT NULL DEFAULT 1, `metadata_country_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `metadata_country_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id_metadata_country`), UNIQUE KEY `uk_metadata_country_iso` (`metadata_country_iso`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- tb_metadata_region — regiões do país (Brasil: N, NE, SE, S, CO) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `tb_metadata_region` ( `id_metadata_region` INT(11) NOT NULL AUTO_INCREMENT, `id_metadata_country` INT(11) NOT NULL, `metadata_region_name` VARCHAR(100) NOT NULL, `metadata_region_acronym` VARCHAR(5) NOT NULL, -- N, NE, SE, S, CO `metadata_region_zipcode` VARCHAR(20) DEFAULT NULL, `metadata_region_lat` DECIMAL(10,7) DEFAULT NULL, `metadata_region_lng` DECIMAL(10,7) DEFAULT NULL, `metadata_region_location` POINT DEFAULT NULL, `metadata_region_status` TINYINT(1) NOT NULL DEFAULT 1, `metadata_region_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `metadata_region_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id_metadata_region`), KEY `ix_mreg_country` (`id_metadata_country`), CONSTRAINT `fk_mreg_country` FOREIGN KEY (`id_metadata_country`) REFERENCES `tb_metadata_country` (`id_metadata_country`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- tb_metadata_state -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `tb_metadata_state` ( `id_metadata_state` INT(11) NOT NULL AUTO_INCREMENT, `id_metadata_country` INT(11) NOT NULL, `id_metadata_region` INT(11) DEFAULT NULL, `metadata_state_name` VARCHAR(100) NOT NULL, `metadata_state_uf` VARCHAR(2) NOT NULL, -- SP, RJ... `metadata_state_ibge_code` INT(11) DEFAULT NULL, -- código IBGE `metadata_state_zipcode` VARCHAR(20) DEFAULT NULL, -- faixa CEP (informativo) `metadata_state_lat` DECIMAL(10,7) DEFAULT NULL, `metadata_state_lng` DECIMAL(10,7) DEFAULT NULL, `metadata_state_location` POINT DEFAULT NULL, `metadata_state_status` TINYINT(1) NOT NULL DEFAULT 1, `metadata_state_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `metadata_state_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id_metadata_state`), UNIQUE KEY `uk_metadata_state_uf` (`id_metadata_country`, `metadata_state_uf`), KEY `ix_mst_region` (`id_metadata_region`), CONSTRAINT `fk_mst_country` FOREIGN KEY (`id_metadata_country`) REFERENCES `tb_metadata_country` (`id_metadata_country`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_mst_region` FOREIGN KEY (`id_metadata_region`) REFERENCES `tb_metadata_region` (`id_metadata_region`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- tb_metadata_city -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `tb_metadata_city` ( `id_metadata_city` INT(11) NOT NULL AUTO_INCREMENT, `id_metadata_state` INT(11) NOT NULL, `metadata_city_name` VARCHAR(150) NOT NULL, `metadata_city_ibge_code` INT(11) DEFAULT NULL, `metadata_city_ddd` VARCHAR(3) DEFAULT NULL, -- DDD principal da cidade `metadata_city_zipcode` VARCHAR(20) DEFAULT NULL, -- CEP base/faixa `metadata_city_lat` DECIMAL(10,7) DEFAULT NULL, `metadata_city_lng` DECIMAL(10,7) DEFAULT NULL, `metadata_city_location` POINT DEFAULT NULL, `metadata_city_status` TINYINT(1) NOT NULL DEFAULT 1, `metadata_city_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `metadata_city_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id_metadata_city`), UNIQUE KEY `uk_metadata_city_ibge` (`metadata_city_ibge_code`), KEY `ix_mci_state` (`id_metadata_state`), KEY `ix_mci_name` (`metadata_city_name`), CONSTRAINT `fk_mci_state` FOREIGN KEY (`id_metadata_state`) REFERENCES `tb_metadata_state` (`id_metadata_state`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- tb_metadata_neighborhood -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `tb_metadata_neighborhood` ( `id_metadata_neighborhood` INT(11) NOT NULL AUTO_INCREMENT, `id_metadata_city` INT(11) NOT NULL, `metadata_neighborhood_name` VARCHAR(150) NOT NULL, `metadata_neighborhood_ddd` VARCHAR(3) DEFAULT NULL, -- snapshot do DDD da cidade `metadata_neighborhood_zipcode` VARCHAR(20) DEFAULT NULL, -- CEP do bairro `metadata_neighborhood_lat` DECIMAL(10,7) DEFAULT NULL, `metadata_neighborhood_lng` DECIMAL(10,7) DEFAULT NULL, `metadata_neighborhood_location` POINT DEFAULT NULL, `metadata_neighborhood_status` TINYINT(1) NOT NULL DEFAULT 1, `metadata_neighborhood_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `metadata_neighborhood_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id_metadata_neighborhood`), KEY `ix_mnb_city` (`id_metadata_city`), KEY `ix_mnb_name` (`metadata_neighborhood_name`), CONSTRAINT `fk_mnb_city` FOREIGN KEY (`id_metadata_city`) REFERENCES `tb_metadata_city` (`id_metadata_city`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- SEEDS — Brasil (1 país + 5 regiões + 27 estados) -- ===================================================================== -- País INSERT IGNORE INTO `tb_metadata_country` (`metadata_country_name`, `metadata_country_iso`, `metadata_country_ddi`, `metadata_country_lat`, `metadata_country_lng`) VALUES ('Brasil', 'BR', '+55', -14.2350000, -51.9253000); -- Regiões (acrônimos: N, NE, SE, S, CO; lat/lng aprox de centróide) INSERT IGNORE INTO `tb_metadata_region` (`id_metadata_country`, `metadata_region_name`, `metadata_region_acronym`, `metadata_region_lat`, `metadata_region_lng`) SELECT c.`id_metadata_country`, r.name, r.acronym, r.lat, r.lng FROM `tb_metadata_country` c JOIN ( SELECT 'Norte' AS name, 'N' AS acronym, -3.4168000 AS lat, -65.8561000 AS lng UNION ALL SELECT 'Nordeste', 'NE', -8.7619000, -39.5368000 UNION ALL SELECT 'Sudeste', 'SE', -19.5680000, -45.4250000 UNION ALL SELECT 'Sul', 'S', -27.5950000, -51.2500000 UNION ALL SELECT 'Centro-Oeste', 'CO', -15.6014000, -53.0186000 ) r WHERE c.`metadata_country_iso` = 'BR'; -- Estados (UF, nome, região, IBGE code, lat/lng da capital aprox) INSERT IGNORE INTO `tb_metadata_state` (`id_metadata_country`, `id_metadata_region`, `metadata_state_name`, `metadata_state_uf`, `metadata_state_ibge_code`, `metadata_state_lat`, `metadata_state_lng`) SELECT c.`id_metadata_country`, r.`id_metadata_region`, s.name, s.uf, s.ibge, s.lat, s.lng FROM `tb_metadata_country` c JOIN ( -- Norte SELECT 'Acre' AS name, 'AC' AS uf, 12 AS ibge, 'N' AS reg, -8.7770000 AS lat, -70.5510000 AS lng UNION ALL SELECT 'Amapá', 'AP', 16, 'N', 1.4144000, -51.7866000 UNION ALL SELECT 'Amazonas', 'AM', 13, 'N', -3.4168000, -65.8561000 UNION ALL SELECT 'Pará', 'PA', 15, 'N', -3.7900000, -52.4800000 UNION ALL SELECT 'Rondônia', 'RO', 11, 'N', -10.8300000, -62.8200000 UNION ALL SELECT 'Roraima', 'RR', 14, 'N', 1.9981000, -61.3300000 UNION ALL SELECT 'Tocantins', 'TO', 17, 'N', -10.1689000, -48.3317000 -- Nordeste UNION ALL SELECT 'Alagoas', 'AL', 27, 'NE', -9.5713000, -36.7820000 UNION ALL SELECT 'Bahia', 'BA', 29, 'NE', -12.5797000, -41.7007000 UNION ALL SELECT 'Ceará', 'CE', 23, 'NE', -5.4984000, -39.3206000 UNION ALL SELECT 'Maranhão', 'MA', 21, 'NE', -4.9609000, -45.2744000 UNION ALL SELECT 'Paraíba', 'PB', 25, 'NE', -7.2400000, -36.7820000 UNION ALL SELECT 'Pernambuco', 'PE', 26, 'NE', -8.8137000, -36.9541000 UNION ALL SELECT 'Piauí', 'PI', 22, 'NE', -7.7183000, -42.7289000 UNION ALL SELECT 'Rio Grande do Norte','RN',24, 'NE', -5.4026000, -36.9541000 UNION ALL SELECT 'Sergipe', 'SE', 28, 'NE', -10.5741000, -37.3857000 -- Sudeste UNION ALL SELECT 'Espírito Santo', 'ES', 32, 'SE', -19.1834000, -40.3089000 UNION ALL SELECT 'Minas Gerais', 'MG', 31, 'SE', -18.5122000, -44.5550000 UNION ALL SELECT 'Rio de Janeiro', 'RJ', 33, 'SE', -22.9068000, -43.1729000 UNION ALL SELECT 'São Paulo', 'SP', 35, 'SE', -23.5505000, -46.6333000 -- Sul UNION ALL SELECT 'Paraná', 'PR', 41, 'S', -25.2521000, -52.0215000 UNION ALL SELECT 'Rio Grande do Sul', 'RS', 43, 'S', -30.0346000, -51.2177000 UNION ALL SELECT 'Santa Catarina', 'SC', 42, 'S', -27.2423000, -50.2189000 -- Centro-Oeste UNION ALL SELECT 'Distrito Federal', 'DF', 53, 'CO', -15.7975000, -47.8919000 UNION ALL SELECT 'Goiás', 'GO', 52, 'CO', -15.8270000, -49.8362000 UNION ALL SELECT 'Mato Grosso', 'MT', 51, 'CO', -12.6819000, -56.9211000 UNION ALL SELECT 'Mato Grosso do Sul','MS', 50, 'CO', -20.7722000, -54.7852000 ) s ON 1=1 LEFT JOIN `tb_metadata_region` r ON r.`metadata_region_acronym` = s.reg AND r.`id_metadata_country` = c.`id_metadata_country` WHERE c.`metadata_country_iso` = 'BR';