-- Migration 006: tabela de violacoes de localizacao
-- Registra cada chamada de endpoint fora do raio cadastrado.
-- Permite master ver historico granular (nao so' agregado em seedkey_logs).
-- Idempotente.

CREATE TABLE IF NOT EXISTS location_violations (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id INT UNSIGNED NOT NULL,
  company_id INT UNSIGNED NOT NULL,
  endpoint VARCHAR(60) NOT NULL COMMENT 'nome do endpoint (libraries, workshop_credentials, seedkey, ping)',
  lat DECIMAL(10,7) NOT NULL,
  lng DECIMAL(10,7) NOT NULL,
  distance_km DECIMAL(8,2) NOT NULL,
  blocked TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = chamada bloqueada (block_far_usage=1)',
  ip_address VARCHAR(45) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_device (device_id),
  KEY idx_company (company_id),
  KEY idx_created (created_at),
  KEY idx_endpoint (endpoint),
  CONSTRAINT fk_lv_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_lv_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
