wifi-etl/README.md

# 📡 WiFi ETL Pipeline

---

## 🧠 Visão Geral

Este projeto tem como objetivo realizar a ingestão periódica de dados de duas APIs distintas:

* **API de Autenticação (WiFeed)** → Nome, CPF, sexo, MAC
* **API do Access Point (Ruijie)** → MAC, tempo conectado, AP

Os dados serão cruzados pelo **MAC Address** e persistidos em um banco **PostgreSQL** para análise e consulta.

---

## ⚙️ Arquitetura

```mermaid
flowchart TD

    A["Cron (Docker)\na cada 5 minutos"]
    B["Python ETL Script"]

    C["API Autenticação\nnome, cpf, sexo, MAC"]
    D["API do AP\nMAC, tempo, AP"]

    E["Transform\nmerge pelo MAC"]
    F["PostgreSQL\nusers + sessions"]

    A --> B
    B --> C
    B --> D

    C --> E
    D --> E

    E --> F
```



<!-- Inserir diagrama aqui -->

<!-- Exemplo: ![Arquitetura do ETL](./docs/diagram.png) -->

### Fluxo

1. Execução automática a cada 5 minutos (cron via Docker)
2. Extração dos dados das APIs
3. Transformação (merge pelo MAC + normalização)
4. Carga no banco PostgreSQL

---

## 🏗️ Estrutura do Projeto

```
app/
├── core/              # Configurações e conexão com banco
│   ├── config.py
│   └── db.py
│
├── extractor/         # Extração das APIs externas
│   ├── ruijie.py
│   └── wifeed.py
│
├── transform/         # Regras de transformação
│   └── merge_mac.py
│
├── load/              # Persistência no banco
│   └── load_database.py
│
└── main.py            # Orquestrador do pipeline

infra/
├── Dockerfile
├── docker-compose.yml
└── crontab

.env.example
requirements.txt
README.md
```

---

## 🚀 Como executar

> ⚠️ Observação: o projeto ainda está em fase inicial e algumas partes podem não estar totalmente implementadas.

### 1. Clonar o repositório

```bash
git clone <seu-repo>
cd wifi-etl
```

---

### 2. Configurar variáveis de ambiente

Crie um `.env` baseado no exemplo:

```bash
cp .env.example .env
```
Preencha com suas credenciais (NUNCA commitar o `.env`)
```env
# PostgreSQL
DB_HOST=localhost
DB_PORT=5432
DB_NAME=wifi_etl
DB_USER=postgres
DB_PASSWORD=sua_senha

# Ruijie (AP)
RUIJIE_BASE_URL=https://cloud-eu.ruijienetworks.com
RUIJIE_APPID=seu_appid
RUIJIE_SECRET=**********
RUIJIE_ACCESS_TOKEN=**********************************
RUIJIE_GROUP_ID=9290679

# WiFeed (autenticação usuários)
WIFEED_BASE_URL=https://api.wifeed.com.br
WIFEED_CLIENT_ID=**********-*****-****-****-************
WIFEED_CLIENT_SECRET=****************

LOG_LEVEL=INFO
```

---

### 3. **Modo Desenvolvedor (local)**

```bash
# a) Crie venv
python -m venv .venv
.venv\Scripts\activate   # Windows
# source .venv/bin/activate   # Linux/Mac

# b) Instale deps
pip install -r requirements.txt

# c) Suba PostgreSQL (Docker)
docker run -d --name wifi-db \
  -e POSTGRES_PASSWORD=$DB_PASSWORD \
  -e POSTGRES_DB=$DB_NAME \
  -p 5432:5432 postgres:15-alpine

# d) Crie o schema
docker exec -i wifi-db psql -U postgres -d wifi_etl < infra/init.sql

# e) Rode o ETL (uma vez)
python -m app.main

# f) (Opcional) Agende localmente
# crontab -e
# */5 * * * * cd /caminho/wifi-etl && PYTHONPATH=. python -m app.main >> /var/log/wifi-etl.log 2>&1
```

---

### 4. **Modo Produção (Docker Compose)**

```bash
# Build e sobe Postgres + ETL (ambiente dev completo)
docker-compose up --build -d

# Logs
docker-compose logs -f etl

# Executa manualmente (debug)
docker-compose exec etl python /app/main.py
```

---

#### 5. Deploy Automático (CI/CD)

Ao fazer `git push` na branch `main`, o workflow executa no runner da VM de produção:

1. Atualiza o código em `/opt/wifi-etl`
2. Copia o `.env` seguro
3. Rebuilda o container (`docker-compose`)
4. Sobe o serviço automaticamente

> ⚠️ O deploy roda diretamente na VM (self-hosted runner)


### ⏱️ Agendamento (Cron)

O ETL é executado a cada 5 minutos via cron dentro do container:

```bash
*/5 * * * * PYTHONPATH=/app /usr/local/bin/python -m app.main >> /var/log/wifi-etl.log 2>&1

```
⚠️ Importante: o PYTHONPATH=/app é necessário para o cron encontrar os módulos Python.

#### Variáveis de Ambiente no Servidor

No servidor de produção, crie `/opt/wifi-etl/.env` com mesmas variáveis do `.env` local (ou use docker-compose override).

```bash
# No servidor (primeira vez)
mkdir -p /opt/wifi-etl
# Copie arquivos: docker-compose.prod.yml + .env (via scp)

# Deploy manual (força rebuild)
./deploy.sh prod
```

---


### 6. Consultas Úteis

```sql
-- Sessões com dados do usuário
SELECT u.name, u.cpf, s.access_point_name,
       s.online_time, s.offline_time,
       EXTRACT(EPOCH FROM (s.offline_time - s.online_time))/60 AS mins
FROM sessions s
JOIN users u ON u.mac_address = s.mac_address
ORDER BY s.online_time DESC
LIMIT 20;

-- Sessões por prédio (último dia)
SELECT building_name, COUNT(*) AS total_sessions,
       SUM(active_time_ms)/60000 AS total_minutes
FROM sessions
WHERE online_time >= NOW() - INTERVAL '1 day'
GROUP BY building_name;

-- Usuários únicos (ontem)
SELECT COUNT(DISTINCT mac_address) AS usuarios_unicos
FROM sessions
WHERE online_time::date = CURRENT_DATE - 1;
```

---

## 📈 Observabilidade

Logs do ETL podem ser consultados via:

```bash
docker exec wifi_etl_worker tail -f /var/log/wifi-etl.log

```

Para ver status do container:
``` bash
docker ps
```


## 📊 Modelo de Dados

```
users
  id (PK)
  mac_address (UNIQUE, normalized)
  name, cpf, gender, email, birthdate
  client_id (WiFeed), host_type, local_name
  created_at, updated_at

sessions
  id (PK)
  mac_address (FK → users)
  access_point_name, building_name, band, channel
  rssi, user_ip, bytes_up/down/total
  online_time (TIMESTAMP), offline_time (TIMESTAMP)
  active_time_ms
  created_at
  UNIQUE (mac_address, online_time)  ← idempotência

watermarks
  source (PK)   -- 'ruijie' | 'wifeed'
  last_value    -- epoch ms (Ruijie) | 'YYYY-MM-DD' (WiFeed)
  last_run_at   -- timestamp da última extração
```

---

## 🔒 Segurança

- **Credenciais**: apenas no `.env` (nunca commitar)
- **DB em VM separada**: `docker-compose.prod.yml` só sobe ETL; `DB_HOST` aponta para IP externo
- **Watermarks**: evitam vazamento de dados históricos acidental
- **Idempotência**: constraint única impede duplicatas mesmo em retry

---

## 🐛 Troubleshooting

| Problema | Solução |
|---|---|
| `psycopg2` erro ao conectar | Verifique `DB_HOST`, `DB_PORT`, firewall |
| Token Ruijie 401 | Rode `get_access_token()` manual ou atualize `RUIJIE_ACCESS_TOKEN` |
| Watermark não avança | Verifique `onlineTime` dos registros (deve ser > watermark anterior) |
| MAC não normaliza | Logs Warn — verifique formato de entrada (Cisco vs colon) |
| Docker build lento | Use `--no-cache` se mudar dependências; senão cache funciona |

---

## 📌 Roadmap

- [x] Schema mínimo + watermarks
- [x] Extractor Ruijie com paginação + watermark
- [x] Extractor WiFeed (report/access)
- [x] Transform: normalize_mac + mapeamento campos
- [x] Load: upsert users + insert sessions idempotente
- [x] Docker + cron
- [x] CI/CD (push → deploy)
- [ ] Retry com backoff nas APIs
- [ ] Automação de envio de erros para equipe
- [ ] Testes unitários (pytest)

---

## 📝 Licença

Proprietário — Sothis