# Banco `lab2_zapi` — App Z-API

Banco dedicado da **app Z-API**, parte do ecossistema f19. Armazena contatos, grupos, vínculos, regras de bloqueio e histórico de moderação do WhatsApp via Z-API.

> **Por que banco separado?** Apps no framework f19 usam banco isolado da `global` (`lab2_bd`). Isso preserva o core estável da plataforma e permite que cada app evolua de schema sem afetar outros. Limitação: **FK cross-database não funciona em MySQL** — `id_project` aqui é referência lógica a `tb_project` da `lab2_bd`, sem constraint.

## Conexão

```
host : 129.121.54.153
db   : lab2_zapi
user : lab2_user
pass : 1Milhao@2026*
```

## Visão geral — diagrama de relacionamentos

```
                           ┌──────────────────────┐
                           │ tb_group_category    │◄── self-ref (parent)
                           │ (categorias/sub)     │
                           └──┬───────────────────┘
                              │
                ┌─────────────┴──────────────┐
                ▼                            ▼
    ┌──────────────────┐         ┌────────────────────┐
    │   tb_contact     │         │     tb_group       │
    │ phone, name, ... │         │ zapi_id, name, ... │
    └──────┬───────────┘         └─────────┬──────────┘
           │                               │
           │   ┌───────────────────────────┘
           │   │
           ▼   ▼
    ┌──────────────────────────┐
    │   tb_group_contact       │ (junction N:N)
    │ joined_at, left_at       │
    └──────────────────────────┘

    ┌──────────────────────┐
    │  tb_group_security   │  (regras de bloqueio)
    │ name, condition_*,   │
    │ status               │
    └──┬───────────────────┘
       │
       ├─────► tb_contact_block  (contatos bloqueados — histórico)
       │
       └─────► tb_message_block  (mensagens bloqueadas — histórico)
```

## Tabelas

### 1. `tb_group_category` — categorias e subcategorias

Hierárquica via self-reference (`id_group_category_parent`).

| Coluna | Tipo | Notas |
|---|---|---|
| `id_group_category` | INT PK AUTO | |
| `group_category_name` | VARCHAR(100) NOT NULL | |
| `id_group_category_parent` | INT NULL | `NULL` = raiz |
| `id_project` | INT NOT NULL | ref `tb_project` (lab2_bd) |
| `group_category_status` | TINYINT(1) DEFAULT 1 | 0=inativa, 1=ativa |
| `group_category_created` | DATETIME | auto |
| `group_category_updated` | DATETIME | auto |

**Constraints / índices:**
- `fk_gc_parent` → self, `ON DELETE SET NULL`
- `ix_gc_parent`, `ix_gc_project`

---

### 2. `tb_group_security` — regras de bloqueio

Define condições automáticas que disparam moderação no recebimento de mensagens/contatos.

| Coluna | Tipo | Notas |
|---|---|---|
| `id_group_security` | INT PK AUTO | |
| `group_security_name` | VARCHAR(150) NOT NULL | nome humano da regra |
| `group_security_condition_type` | ENUM | `text` \| `image` \| `video` \| `link` \| `file` \| `number` \| `phone_prefix` \| `other` |
| `group_security_condition_value` | VARCHAR(500) NULL | valor de comparação (regex, lista, prefix etc) |
| `group_security_condition_variable` | VARCHAR(100) NULL | campo do webhook avaliado (ex: `participantPhone`, `text.message`) |
| `group_security_status` | TINYINT(1) DEFAULT 1 | 0=inativa, 1=ativa |
| `group_security_created` | DATETIME | |
| `group_security_updated` | DATETIME | |
| `id_project` | INT NOT NULL | |

**Exemplo de regra**: bloqueia números fora do Brasil
```sql
INSERT INTO tb_group_security
  (group_security_name, group_security_condition_type,
   group_security_condition_value, group_security_condition_variable,
   id_project)
VALUES
  ('Bloqueio internacional', 'phone_prefix', '!55', 'participantPhone', 7);
```

---

### 3. `tb_contact` — contatos do WhatsApp

| Coluna | Tipo | Mapeia Z-API |
|---|---|---|
| `id_contact` | INT PK AUTO | |
| `contact_phone` | VARCHAR(20) NOT NULL | `phone` (`"559999999999"`) |
| `contact_name` | VARCHAR(150) | `name` (Nome e sobrenome) |
| `contact_short` | VARCHAR(100) | `short` (Nome do contato) |
| `contact_notify` | VARCHAR(150) | `notify` (Nome no WhatsApp) |
| `contact_vname` | VARCHAR(150) | `vname` (Nome no vcard) |
| `id_group_category` | INT NULL | FK |
| `id_project` | INT NOT NULL | |
| `contact_status` | TINYINT(1) DEFAULT 1 | |
| `contact_created` / `_updated` | DATETIME | auto |

**Constraints:**
- `UNIQUE (id_project, contact_phone)` — mesmo phone pode existir em projetos diferentes
- `fk_contact_category` → `tb_group_category` (`ON DELETE SET NULL`)

---

### 4. `tb_group` — grupos do WhatsApp

| Coluna | Tipo | Mapeia Z-API |
|---|---|---|
| `id_group` | INT PK AUTO | id interno |
| `group_zapi_id` | VARCHAR(64) NOT NULL | `phone` (`"120363048358995900-group"`) |
| `id_group_category` | INT NULL | FK |
| `group_name` | VARCHAR(200) | `name` |
| `group_photo` | VARCHAR(500) | URL |
| `group_description` | TEXT | |
| `group_link` | VARCHAR(500) | link de convite |
| `group_permission` | ENUM | `admin` \| `member` — papel da NOSSA instância no grupo |
| `group_last_message_time` | DATETIME | converter de `lastMessageTime` (ms → datetime) |
| `group_contacts_count` | INT DEFAULT 0 | denormalizado pra performance |
| `id_project` | INT NOT NULL | |
| `group_status` | TINYINT(1) DEFAULT 1 | |
| `group_created` / `_updated` | DATETIME | auto |

**Constraints:**
- `UNIQUE (id_project, group_zapi_id)`
- `fk_group_category` → `tb_group_category` (`ON DELETE SET NULL`)

---

### 5. `tb_group_contact` — junction N:N (grupo ↔ contato)

PK composta. Não duplica entradas — um contato aparece **1×** por grupo. Para entradas/saídas múltiplas, `group_contact_left_at = NULL` significa "ainda dentro"; ao re-entrar, atualizar `joined_at`.

| Coluna | Tipo | Notas |
|---|---|---|
| `id_group` | INT NOT NULL | PK + FK |
| `id_contact` | INT NOT NULL | PK + FK |
| `group_contact_joined_at` | DATETIME NULL | data de entrada |
| `group_contact_left_at` | DATETIME NULL | `NULL` = ativo no grupo |
| `id_project` | INT NOT NULL | |
| `group_contact_created` / `_updated` | DATETIME | auto |

**Constraints:**
- PK composta `(id_group, id_contact)`
- `fk_groupcontact_group` → `tb_group` (`CASCADE`)
- `fk_groupcontact_contact` → `tb_contact` (`CASCADE`)

---

### 6. `tb_contact_block` — histórico de contatos bloqueados

Cada linha = uma ocorrência de bloqueio. Manter histórico permite auditoria.

| Coluna | Tipo | Notas |
|---|---|---|
| `id_contact_block` | INT PK AUTO | |
| `id_contact` | INT NOT NULL | FK |
| `id_group_security` | INT NULL | regra que disparou; `NULL` = bloqueio manual |
| `id_group` | INT NULL | `NULL` = bloqueio geral (fora de grupo) |
| `contact_block_motive` | VARCHAR(200) | texto livre, snapshot do nome da regra |
| `contact_block_date` | DATETIME DEFAULT NOW | |
| `id_project` | INT NOT NULL | |

**Constraints:**
- `fk_cb_contact` → `tb_contact` (`CASCADE`)
- `fk_cb_security` → `tb_group_security` (`SET NULL` — preserva histórico se regra for removida)
- `fk_cb_group` → `tb_group` (`SET NULL`)

---

### 7. `tb_message_block` — histórico de mensagens bloqueadas

| Coluna | Tipo | Notas |
|---|---|---|
| `id_message_block` | INT PK AUTO | |
| `message_zapi_id` | VARCHAR(64) NOT NULL | `messageId` do webhook (ex: `A5DA67FFED70F23A0BACC81D0AB3A44C`) |
| `id_contact` | INT NULL | FK opcional |
| `message_block_contact_phone` | VARCHAR(20) | **snapshot** do `participantPhone` — útil quando `id_contact = NULL` |
| `id_group_security` | INT NULL | FK |
| `id_group` | INT NULL | FK; `NULL` = bloqueio fora de grupo |
| `message_block_motive` | VARCHAR(200) | |
| `message_block_date` | DATETIME DEFAULT NOW | |
| `id_project` | INT NOT NULL | |

**Constraints:**
- `UNIQUE (id_project, message_zapi_id)` — uma mensagem não é bloqueada 2×
- `fk_mb_contact` → `tb_contact` (`SET NULL`)
- `fk_mb_security` → `tb_group_security` (`SET NULL`)
- `fk_mb_group` → `tb_group` (`SET NULL`)

---

## Mapeamento webhook Z-API → banco

Payload típico de `ReceivedCallback` (mensagem recebida em grupo):

```json
{
  "isGroup": true,
  "phone": "120363048358995900-group",   // → tb_group.group_zapi_id
  "chatName": "Nome do grupo",           // → tb_group.group_name
  "participantPhone": "5511994160875",   // → tb_contact.contact_phone
  "senderName": "Nome no whatsapp",      // → tb_contact.contact_notify
  "messageId": "A5DA67FFE...",           // → tb_message_block.message_zapi_id
  "momment": 1748881347638                // ms — usar pra last_message_time / block_date
}
```

**Conversão `momment` ms → DATETIME**: `FROM_UNIXTIME(momment / 1000)`.

## Convenções de nomes (framework f19)

- **Tabelas**: `tb_<nome_singular>` — sempre singular (`tb_contact`, não `tb_contacts`).
- **PK auto-increment**: `id_<tabela>` (`id_contact`, `id_group`).
- **FKs**: mesmo nome da PK da referenciada (`id_group_category`).
- **Campos**: `<tabela>_<campo>` em inglês (`contact_phone`, `group_zapi_id`).
- **Self-reference**: sufixo `_parent` (`id_group_category_parent`).
- **Status soft-flag**: `<tabela>_status` TINYINT(1) (0/1).
- **Datas automáticas**: `<tabela>_created` / `<tabela>_updated` com `DEFAULT CURRENT_TIMESTAMP [ON UPDATE]`.

## Convenções de design adotadas

| Decisão | Por quê |
|---|---|
| `id_group NULL` para "bloqueio geral" | NULL é semanticamente correto pra ausência; `= 0` exigia FK desabilitada |
| `message_block_contact_phone` como snapshot | A regra pode disparar antes do contato existir no banco; phone bruto permite vincular depois |
| `UNIQUE (id_project, ...)` em phones e zapi_ids | Mesmo phone/grupo pode existir em projetos diferentes (isolamento multi-tenant lógico) |
| FKs com `CASCADE` em junction, `SET NULL` em blocks | Junction não faz sentido sem ambas pontas; blocks preservam histórico mesmo se regra/grupo for deletado |
| `group_permission` ENUM('admin','member') | Limite curto, validação no banco |
| `condition_type` ENUM com 8 valores | Inclui `phone_prefix` por exigência da regra de bloqueio internacional + `other` como escape hatch |
| `group_contacts_count` denormalizado | Evita `COUNT(*)` em listagens frequentes — atualizar via trigger/sync background |
| `id_project` sem FK formal | MySQL não suporta FK cross-database (`lab2_zapi` → `lab2_bd`) |

## Como aplicar / atualizar schema

Os SQLs estão em `global.w19.com.br/cliki/sql/apps/zapi/`. Ordem de aplicação respeitando FKs:

```
1. tb_group_category.sql         (base, sem dependências)
2. tb_group_security.sql         (base)
3. tb_contact.sql                (FK → group_category)
4. tb_group.sql                  (FK → group_category)
5. tb_group_contact.sql          (FK → group + contact)
6. tb_contact_block.sql          (FK → contact + security + group)
7. tb_message_block.sql          (FK → contact + security + group)
```

**Aplicar via PHP CLI:**

```php
$pdo = new PDO('mysql:host=129.121.54.153;dbname=lab2_zapi;charset=utf8mb4',
               'lab2_user', '1Milhao@2026*',
               [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$base = __DIR__ . '/';
foreach (['tb_group_category', 'tb_group_security', 'tb_contact', 'tb_group',
          'tb_group_contact', 'tb_contact_block', 'tb_message_block'] as $t) {
    $pdo->exec(file_get_contents($base . $t . '.sql'));
}
```

Todos os SQLs usam `CREATE TABLE IF NOT EXISTS` — idempotentes, podem ser re-executados sem erro. Para **alterações**, criar arquivos `alter_<tabela>_<motivo>.sql` separados (não editar o `tb_*.sql` original — convenção do framework).

## Queries de exemplo

**Total de mensagens bloqueadas por regra (últimos 30 dias):**
```sql
SELECT gs.group_security_name, COUNT(*) AS qtd
FROM tb_message_block mb
LEFT JOIN tb_group_security gs ON gs.id_group_security = mb.id_group_security
WHERE mb.message_block_date >= NOW() - INTERVAL 30 DAY
  AND mb.id_project = :project
GROUP BY gs.id_group_security
ORDER BY qtd DESC;
```

**Contatos ativos em um grupo:**
```sql
SELECT c.contact_phone, c.contact_notify, gc.group_contact_joined_at
FROM tb_group_contact gc
INNER JOIN tb_contact c ON c.id_contact = gc.id_contact
WHERE gc.id_group = :group
  AND gc.group_contact_left_at IS NULL;
```

**Top 10 contatos com mais bloqueios:**
```sql
SELECT c.contact_phone, c.contact_notify, COUNT(*) AS bloqueios
FROM tb_contact_block cb
INNER JOIN tb_contact c ON c.id_contact = cb.id_contact
WHERE cb.id_project = :project
GROUP BY cb.id_contact
ORDER BY bloqueios DESC
LIMIT 10;
```

**Árvore de categorias (recursiva — MySQL 8+):**
```sql
WITH RECURSIVE cat_tree AS (
    SELECT id_group_category, group_category_name, id_group_category_parent, 0 AS depth
    FROM tb_group_category
    WHERE id_group_category_parent IS NULL AND id_project = :project
    UNION ALL
    SELECT c.id_group_category, c.group_category_name, c.id_group_category_parent, ct.depth + 1
    FROM tb_group_category c
    INNER JOIN cat_tree ct ON c.id_group_category_parent = ct.id_group_category
)
SELECT CONCAT(REPEAT('  ', depth), group_category_name) AS tree
FROM cat_tree;
```

## Roadmap / pendências

- [ ] Endpoint REST para sync periódico Z-API → banco (`/zapi/sync/groups`, `/zapi/sync/contacts`)
- [ ] Trigger para incrementar/decrementar `group_contacts_count` automaticamente em INSERT/DELETE de `tb_group_contact`
- [ ] Tabela `tb_message_log` para histórico de TODAS as mensagens (não só bloqueadas) — opcional
- [ ] Webhook handler escutando `GroupParticipantsCallback` da Z-API pra preencher `joined_at` / `left_at` automaticamente

## Histórico de versões

| Data | Versão | Mudança |
|---|---|---|
| 2026-05-12 | 1.0 | Schema inicial — 7 tabelas (`tb_group_category`, `tb_group_security`, `tb_contact`, `tb_group`, `tb_group_contact`, `tb_contact_block`, `tb_message_block`) |