# Data Model: Contracts Module (Contratos)

All table/column names follow the existing `新`-suffix convention. Every table listed under "Existing" already exists in the live schema (confirmed against the local Docker MySQL, database `yebcrm_sistema`) and needs no DDL change unless a column is explicitly called out as new. See `research.md` for the reasoning behind each decision referenced here.

## Existing entities (behavior changes only, no schema change)

### `Contrato新`

`id, ativo, empresa, inicio, kickoff, vencimento, prazo, cortesia, moeda, ptax, periodicidade, divisao, nf, nfinfo, prazopagamento, prazopagamentoobservacao, pagamento, nfemail, reajustemes, reajustetipo, focal, ordem, inclusao, ampliacao, observacao, assinado, regularizado, arquivo, arquivo_s3_key (unused, out of scope), name_arquivo (unused, out of scope), datacancelamento, motivocancelamento`

- No `grupo` column — tenancy is transitive via `empresa → Empresa新.grupo` (research.md §4).
- `id` **is** "Número do Contrato" (research.md §13) — no new column. Because Renovar/Ampliar/Reduzir keep the existing duplicate-row pattern (research.md §2, reverted this session), `id`/"Número do Contrato" **changes** on every renewal or ampliação/redução — the previous row is deactivated (`ativo=0`), not deleted.
- `motivocancelamento` stays required (research.md §3, reverted this session) — no change to the existing `empty($motivo)` guard, client- or server-side.
- No behavior change: `renovar`/`ampliar`/`reduzir` on `update.php` continue to `INSERT` a new row and deactivate the old one, exactly as today.

### `ContratoProduto新` (`id, contrato, produto`) and satellites

- `ContratoProdutoSegmento新` (`contratoproduto, segmento`)
- `ContratoProdutoPacote新` (`contratoproduto, pacote`)
- `ContratoProdutoInsumoSegmento新` (`contratoproduto, segmento, insumo`)
- `ContratoProdutoClasse新` (`contratoproduto, classe`)
- `ContratoProdutoTipo新` (`contratoproduto, tipo`)
- `ContratoProdutoEntregavel新` (`contratoproduto, entregavel`)

No column changes. `segmento`/`insumo` stay `int`, now populated from SO5-sourced ids instead of the mock arrays' ids — confirmed against the live SO5 API to be plain integers, matching the existing column type (research.md §5). Wipe-and-reinsert per `contratoproduto` on every write (`create`, `editaradmin`) is unchanged; `renovar`/`ampliar`/`reduzir` continue to insert fresh satellite rows against the newly-`duplicate()`d `contrato` id, not the original — no change to that flow (research.md §2, reverted this session).

### `ContratoDemais新` (`id, contrato, profissional`)

Unchanged — contract-scoped (not produto-scoped), "Demais usuários" minus whoever is `Contrato新.focal` (FR-026), already enforced client-side; add a server-side guard when writing (`$_POST['demais']` must not contain `$_POST['focal']`) since this table is rewritten by the same `update.php` paths being touched anyway.

### `ContratoValores新` (`id, contrato, nome, valor, produto, classe, divisao`)

Unchanged shape. Values keyed by `nome` (e.g. `rstotal`, per-month keys under Personalizado) as today; `getContratoTotals()` (research.md §2) keeps reading `nome='rstotal'` as its total-value signal.

### `Produto新` (`id, grupo, nome, categoria, old_id, active`)

- `old_id` is an unrelated legacy-migration column (pre-`新` schema reference) — not reused for the SO5 link.
- **New column**: `so_brand` — `VARCHAR(64) NULL` (matches the "code format unknown yet" assumption in research.md §5; widen if SO5 turns out non-numeric — a `VARCHAR` already tolerates a purely-numeric string, so this is the safer default). Holds the SO5 integration code returned when an Admin links the produto_marca to its SO5 marca (FR-002). `NULL` = "no link yet" (FR-003 edge case: empty segmento/insumo result, not an error).

### `Classe新` (`id, grupo, nome`) / `ProdutoClasse新` (`produto, classe`) / `CategoriaClasse新` (`categoria, classe`)

No new columns. `Classe新` stays the shared, Categoria-scoped, multi-purpose lookup it already is (research.md §6) — this feature does not add a discriminator column, it works within the existing Categoria-scoping that already keeps the 3 commercial classes (ids 1–3, grupo 1) separate from the unrelated advertising-placement classes (ids 4–13).

### `Perfil新` (`id, nome`) / `AreaComercial新` (`id, grupo, nome`) / `Cargo新` (`id, grupo, nome, base, area, padrao, slug`)

No schema change, no new rows. "Gestão" is **not** a new `Perfil新` value (`Perfil新` has no `grupo` column — it's a global lookup, currently `1 Padrão` / `2 Administrador`). It's the existing `AreaComercial新` row `id=3, grupo=1, nome='Gestão'`, reached from a user via `Usuarios.cargo → Cargo新.id → Cargo新.area → AreaComercial新.id`. Access = `perfil == 2` OR (`perfil == 1` AND that user's `Cargo新.area == 3`) — research.md §8.

### `Logs新` (`id, data, tipo, objeto, alvo, responsavel, observacao`)

No schema change. `observacao` now carries a JSON-encoded `{field: {old, new}}` diff for Contrato actions going forward (research.md §9) — it's already a free-form `text` column, existing rows with plain-string `observacao` remain valid and are simply rendered as-is by the new history view.

## New entities

None. Every requirement in the spec is satisfied by extending existing tables/columns (`Produto新.so_brand` is the only new column) plus behavior changes to existing write paths — no new table, and no new rows in any lookup table ("Gestão" already exists in `AreaComercial新`).

## Field-level validation rules (from Functional Requirements)

| Rule | Enforced in | Ref |
|---|---|---|
| Contract-level Classe selection: `Plataforma`(1)+`VHP`(3) mutually exclusive; `Serviço Padrão`(2) requires `Plataforma`(1) | `contrato/create.php`, `contrato/update.php` (`editaradmin`) — new check on submitted `classe-{produto}` per produto. **Not** enforced in `produto/create.php`/`update.php` — a produto_marca can be configured with any subset of the three as available options (corrected this session, see research.md §6) | FR-004 |
| A produto_insumo marked in only one segmento per produto_marca | `contrato/create.php`, `update.php` — reject if the same `insumo` id appears under >1 `segmento` in the posted matrix for the same produto | FR-015 |
| Prazo de contratação = Vencimento − Início (calendar days), read-only | Computed server-side on save (as today), never accepted from client input | FR-019 |
| PDF-only, ≤10MB upload | `contrato/create.php` + every `update.php` mode accepting `arquivo`, copying `interacoes/create.php`'s existing check | FR-028 |
| Ampliar can't shrink / Reduzir can't grow (total classes, total valor) | `getContratoTotals()` comparison against old-vs-duplicated-row — unchanged (research.md §2, reverted this session) | FR-035 |
| "Demais usuários" excludes the selected "Ponto focal" | Client-side (existing) + new server-side guard on write | FR-026 |
| Access restricted to Perfil Administrador, or Perfil Padrão + área Gestão | `usuario_pode_gerir_contratos($sql)` helper (new, `includes/funcoes/funcoes.php`), called from every contracts-module page + every `includes/ajax/contrato/*.php` entry touched by this feature (not `includes/ajax/produto/*.php` — Cadastro > Produto is a separate, general-purpose module FR-001 does not restrict) | FR-001, research.md §8 |
| Tenant scoping via `Contrato新.empresa → Empresa新.grupo` | `read.php`, `download.php` (both currently missing this check — added as part of this feature per research.md §4) | Constitution Principle I |
| Old contracts' segmento/insumo not recognized by current SO5 data are flagged, not hidden or erroring | `formproduto.php`, rendering an existing contract's produto_marca section (research.md §11) | FR-040 |
| Pacote Padrão selection pre-marks insumos under the first segmento; changing it resets the marks | `includes/js/contratos.js`, new pacote-change handler (research.md §10) | FR-014 |

## Entity relationship summary

```text
Empresa新 (grupo) ──< Contrato新 (no direct grupo column; tenant-scoped via empresa)
                          │
                          ├──< ContratoProduto新 ──< ContratoProdutoSegmento新 (SO5-sourced)
                          │                      ├──< ContratoProdutoInsumoSegmento新 (SO5-sourced)
                          │                      ├──< ContratoProdutoPacote新
                          │                      ├──< ContratoProdutoClasse新 ──> Classe新 (shared, Categoria-scoped)
                          │                      ├──< ContratoProdutoTipo新
                          │                      └──< ContratoProdutoEntregavel新
                          ├──< ContratoDemais新 ──> Profissional新 (existing)
                          └──< ContratoValores新

Produto新 (grupo, categoria) ──< ProdutoClasse新 ──> Classe新 ──< CategoriaClasse新 ──> Categoria新
        │
        └─ so_brand (new) ── [SO5 outbound call, unrelated to the Classe/Categoria chain above]

Logs新 (objeto='Contrato', alvo=Contrato新.id) — history for Renovar/Estender/Ampliar/Reduzir/Cancelar/Criar/Editar

Usuarios.perfil ──> Perfil新 (existing: 1='Padrão', 2='Administrador' — unchanged)
Usuarios.cargo ──> Cargo新.area ──> AreaComercial新 (existing: id=3 'Gestão' — unchanged, no new rows)
```