CREATE DATABASE IF NOT EXISTS jadlog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE jadlog;

CREATE TABLE IF NOT EXISTS leads (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  cpf               VARCHAR(14) NOT NULL,
  nome              VARCHAR(255) DEFAULT NULL,
  email             VARCHAR(255) DEFAULT NULL,
  telefone          VARCHAR(30)  DEFAULT NULL,
  endereco          TEXT         DEFAULT NULL,
  metodo_pagamento  VARCHAR(50)  DEFAULT NULL,
  produto           VARCHAR(255) DEFAULT NULL,
  valor             VARCHAR(50)  DEFAULT NULL,
  pago_em           DATETIME     DEFAULT NULL,
  created_at        DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS access_logs (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  ts    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip    VARCHAR(45)  NOT NULL,
  host  VARCHAR(120) DEFAULT NULL,
  path  VARCHAR(500) DEFAULT NULL,
  cpf   VARCHAR(11)  DEFAULT NULL,
  acao  VARCHAR(30)  DEFAULT NULL,
  ua    VARCHAR(300) DEFAULT NULL,
  INDEX idx_ts     (ts),
  INDEX idx_ip     (ip),
  INDEX idx_cpf    (cpf),
  INDEX idx_acao   (acao),
  INDEX idx_ts_cpf (ts, cpf)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ip_blacklist (
  ip       VARCHAR(45) PRIMARY KEY,
  added_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  note     VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ip_dismissed (
  ip       VARCHAR(45) PRIMARY KEY,
  added_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS monitor_settings (
  k VARCHAR(50) PRIMARY KEY,
  v TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO monitor_settings (k, v) VALUES ('reset_at', '2000-01-01 00:00:00');
