-- =====================================================================
-- tb_security_access
-- Eventos RELEVANTES de seguranca (nao TODA visita — visitas comuns vao
-- direto pro arquivo TXT em log.w19.com.br/<projeto>/security/access/).
-- Eventos:
--   block        — bloqueio aplicado (regra tb_project_security ou tb_block_ip)
--   login_fail   — tentativa de login falhou
--   login_ok    — login bem-sucedido (auditoria)
--   suspicious   — heuristica detectou padrao suspeito (rate-limit, user-agent estranho)
--   first_visit  — primeira visita de um IP (1x por IP por projeto)
-- =====================================================================
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `tb_security_access` (
  `id_security_access`         INT(11)      NOT NULL AUTO_INCREMENT,
  `id_project`                 INT(11)      NOT NULL,

  `security_access_event`      ENUM('block','login_fail','login_ok','suspicious','first_visit') NOT NULL,
  `security_access_ip`         VARCHAR(45)  NOT NULL,
  `security_access_country`    VARCHAR(2)   DEFAULT NULL,        -- ISO 3166-1 alpha-2
  `security_access_city`       VARCHAR(100) DEFAULT NULL,
  `security_access_isp`        VARCHAR(150) DEFAULT NULL,        -- provider / AS name
  `security_access_lat`        DECIMAL(10,6) DEFAULT NULL,
  `security_access_lng`        DECIMAL(10,6) DEFAULT NULL,

  `security_access_url`        VARCHAR(500) DEFAULT NULL,
  `security_access_ua`         VARCHAR(255) DEFAULT NULL,
  `security_access_reason`     VARCHAR(200) DEFAULT NULL,        -- ex: "country=CN", "rate_limit", "asn=AS14061 (DigitalOcean)"
  `security_access_blocked`    TINYINT(1)   NOT NULL DEFAULT 0,  -- 1 = request foi bloqueado

  `security_access_created`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id_security_access`),
  KEY `ix_sa_project_created` (`id_project`, `security_access_created`),
  KEY `ix_sa_ip`              (`security_access_ip`),
  KEY `ix_sa_event`           (`security_access_event`),
  KEY `ix_sa_country`         (`security_access_country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;