-- =====================================================================
-- tb_security_ip_cache
-- Cache de GeoIP/ASN — evita bater na API ip-api.com a cada visita.
-- ip-api.com (free tier: 45 req/min) retorna: country, countryCode, region,
-- city, lat, lon, isp, org, as, query, status.
-- TTL recomendado: 30 dias (cron limpa entries com security_ip_cache_updated > 30d).
-- =====================================================================
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `tb_security_ip_cache` (
  `security_ip_cache_ip`                VARCHAR(45)  NOT NULL,
  `security_ip_cache_country`           VARCHAR(2)   DEFAULT NULL,    -- ISO 3166-1 alpha-2 (BR, US, CN, …)
  `security_ip_cache_country_name`      VARCHAR(100) DEFAULT NULL,    -- "Brazil"
  `security_ip_cache_city`              VARCHAR(100) DEFAULT NULL,
  `security_ip_cache_region`            VARCHAR(100) DEFAULT NULL,    -- estado/UF
  `security_ip_cache_isp`               VARCHAR(150) DEFAULT NULL,
  `security_ip_cache_org`               VARCHAR(150) DEFAULT NULL,
  `security_ip_cache_as`                VARCHAR(150) DEFAULT NULL,    -- ASN ex: "AS14061 DigitalOcean"
  `security_ip_cache_lat`               DECIMAL(10,6) DEFAULT NULL,
  `security_ip_cache_lng`               DECIMAL(10,6) DEFAULT NULL,
  `security_ip_cache_is_datacenter`     TINYINT(1)   NOT NULL DEFAULT 0,  -- ASN é DC/proxy/VPN

  `security_ip_cache_created`           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `security_ip_cache_updated`           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`security_ip_cache_ip`),
  KEY `ix_ipc_country`  (`security_ip_cache_country`),
  KEY `ix_ipc_updated`  (`security_ip_cache_updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;