# Data Model: General CRM Backlog Fixes

Only two user stories touch persisted data shape (US4, US5). The other three (US1, US2, US3) change validation/UI/search behavior against existing schema and are not repeated here. Table/column names follow the existing `新`-suffix convention already used throughout `sistema/new`.

## Existing entities referenced (no schema change)

- **`Interacao新`**: `id`, `efetividade`, `titulo`, `descricao`, `lead`, `origem`, `data_origem`, `data_interacao`, `humor`, `canal`, `status`, `valorbrl`, `valorusd`, `arquivo`, `tipo`, `empresa`, `usuario`. Unaffected by this feature except that `InteracaoProfissional新` may now legitimately have zero rows for a given `Interacao新.id`.
- **`InteracaoProfissional新`**: junction table (`interacao`, `profissional`). No change — already tolerates being empty for an interaction (US1).
- **`Profissional新`**: existing personal/demographic columns (`sexo`, `estado_civil`, `conjuge_sexo`, `decisao`, etc.) plus the legacy single-child columns `filho`, `filho_sexo`, `filho_nome`, `filho_nascimento_ano`, which US4 supersedes (see below).

## Changed / new entities

### `Profissional新` (modified — no column changes, new accepted values)

| Field | Change |
|---|---|
| `sexo` | Now additionally accepts `nao_sei` (was `masculino` \| `feminino`) |
| `estado_civil` | Now additionally accepts `nao_sei` (was `solteiro` \| `casado` \| `separado` \| `divorciado` \| `viuvo`) |
| `conjuge_sexo` | Now additionally accepts `nao_sei` |
| `filho_sexo` (legacy) | Superseded by `ProfissionalFilho新.sexo` — see migration note below |

**Validation rule (FR-009, spec edge case)**: `nao_sei` on any of these fields is treated as "not answered" by the qualification-completeness calculation (`getDadosCargos()` / `getCargoInfoList()` in `includes/ajax/indicadores/qualificacao.php`) — i.e. `empty($dados[$key])`-style filtering must treat the literal string `nao_sei` as empty for that purpose, not as a filled value. This is the one behavior change required outside the Profissional form itself.

### `ProfissionalFilho新` (new table)

Replaces the legacy `filho_sexo` / `filho_nome` / `filho_nascimento_ano` columns on `Profissional新` with a proper one-to-many child table, mirroring the existing junction/child-row conventions already used for `Telefone新` and `InteracaoProfissional新`.

| Column | Type | Notes |
|---|---|---|
| `id` | INT, PK, AUTO_INCREMENT | |
| `profissional` | INT, FK → `Profissional新.id` | Tenant isolation is inherited transitively — every write/read is scoped by a `profissional` ID already resolved from a `grupo`-checked lookup, matching the existing `Telefone新` pattern (no direct `grupo` column on the child row). |
| `nome` | VARCHAR | Child's name |
| `sexo` | VARCHAR | `masculino` \| `feminino` \| `nao_sei` |
| `nascimento_ano` | INT, nullable | Birth year |

**Relationship**: One `Profissional新` → zero or more `ProfissionalFilho新` (FR-010, FR-011).

**Lifecycle rule (FR-013, spec edge case)**: On `profissional/update.php`, existing `ProfissionalFilho新` rows for that `profissional` are deleted and the submitted set is reinserted — the same wipe-and-reinsert pattern already used for `InteracaoProduto新`/`InteracaoProfissional新` on interaction edits. This is an intentionally destructive operation on reduction (a removed child's data is not recoverable), consistent with the spec's edge case and flagged under Constitution Principle X in `plan.md`.

**Migration note**: `Profissional新.filho`, `filho_sexo`, `filho_nome`, `filho_nascimento_ano` become read-only/legacy once `ProfissionalFilho新` exists. A one-off script under `sistema/new/migrations/` (consistent with existing one-off migration scripts in that directory, not a real migration framework) backfills any existing single-child data into `ProfissionalFilho新` before the old columns are treated as dead. Dropping the old columns outright is out of scope for this feature (Constitution Principle IV/data-retention caution) — they are simply no longer written by the updated form.

### `Telefone新` (unchanged — no schema change)

**Revised approach (see research.md §5)**: cellphone-vs-landline classification and ordering happens entirely client-side at render time (`includes/js/profissionais.js`), reusing the digit-count match against `getPhoneFormat()`'s per-country `libphonenumber` templates that `formatPhone()` already used for display masking. No `tipo_linha` column, no migration, no write-time change — `Telefone新` and `create.php`/`update.php`'s phone-write logic are untouched by this feature.

**Ordering rule (FR-015)**: applied at each of the three places a Profissional's phones are rendered (list column, info panel, edit-form repopulation) via a shared `ordenarTelefonesCelularPrimeiro()` helper — cellphones first, insertion order preserved within each group (spec edge case: relative order among same-type numbers is unchanged). Not a single sorted read consumed everywhere — each render site applies the sort itself.

## Entity relationship summary

```text
Empresa新 ──< Profissional新 ──< Telefone新 (objeto/referencia polymorphic, unchanged; sorted client-side at render time)
                     │
                     └──< ProfissionalFilho新 (new)

Interacao新 ──< InteracaoProduto新 (unchanged)
Interacao新 ──< InteracaoProfissional新 (unchanged; now legitimately empty for Não Efetivo)
```