# 01 - BANCO DE DADOS

## 1. VISAO GERAL

| Banco | Tabelas | Proposito |
|-------|---------|-----------|
| `db_erp` | Adianti + 67 marketplace | Banco unico: framework Adianti + todas tabelas marketplace. Usado por ERP, API e MKT |
| `db_erp_cd01` | 20 | WMS/TMS local do CD-01. Opera offline |

**Servidor:** uppi.app.br:3306 | **User:** tokstore | **Engine:** InnoDB | **Charset:** utf8mb4
**IMPORTANTE:** Nao existe banco separado para marketplace. As 67 tabelas sao criadas dentro de `db_erp`

---

## 2. db_erp - TABELAS DO MARKETPLACE POR DOMINIO (67 tabelas)
> Estas tabelas ficam dentro de `db_erp`, junto com as tabelas do framework Adianti

### 2.1 Auth e Seguranca (7 tabelas)
| Tabela | PK | Descricao |
|--------|-----|-----------|
| `users` | BIGINT UNSIGNED AI | Usuarios: uuid, email (unique), phone, password_hash (Argon2id), user_type (customer/seller/admin/operator/driver), status, MFA, avatar |
| `roles` | BIGINT | Papeis com slug unico, flag is_system |
| `permissions` | BIGINT | Permissoes agrupadas por modulo |
| `role_permissions` | BIGINT | N:N roles x permissions (CASCADE) |
| `user_roles` | BIGINT | N:N users x roles, granted_at/by |
| `user_sessions` | BIGINT | Sessoes JWT: token_hash, refresh_hash, ip, device_type, expires_at, revoked_at |
| `audit_log` | BIGINT | Auditoria: action, entity_type/id, before/after (JSON), ip |

### 2.2 Enderecos (1 tabela)
| Tabela | Descricao |
|--------|-----------|
| `addresses` | Compartilhada: label, recipient, CEP, logradouro, lat/lng, ibge_code, is_default. FK users (SET NULL) |

### 2.3 Sellers (1 tabela)
| Tabela | Descricao |
|--------|-----------|
| `sellers` | company_name, trade_name, CNPJ/CPF, tax_regime (simples/presumido/real/mei), slug, rating_avg, commission_rate, status (pending/in_review/approved/suspended/banned), dados bancarios, FK users + addresses |

### 2.4 Catalogo (6 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `categories` | Arvore hierarquica (parent_id), slug, level, path, sort_order |
| `brands` | Marcas com slug unico |
| `products` | Produto: seller_id, category_id, brand_id, uuid, sku (unique por seller), price, dados fiscais (ncm/cest/cfop/origin/ean), status (draft/pending_review/active/inactive/rejected), FULLTEXT name+description |
| `product_variants` | Variacoes: sku unico, preco, peso, ean |
| `product_variant_options` | Opcoes (Cor: Azul, Tamanho: M) |
| `product_images` | url, alt_text, sort_order, is_cover, FK variant opcional |
| `product_attributes` | Atributos livres nome/valor |

### 2.5 Avaliacoes (1 tabela)
| Tabela | Descricao |
|--------|-----------|
| `reviews` | Rating 1-5 (CHECK), is_verified, is_approved, seller_reply |

### 2.6 Carrinho (2 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `carts` | user_id ou session_id (anonimo), status (active/merged/converted/abandoned) |
| `cart_items` | Unique (cart, product, variant), preco unitario |

### 2.7 Cupons (2 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `coupons` | code unico, discount_type (percentage/fixed/free_shipping), limites, applies_to, validade |
| `coupon_usage` | Registro de uso por usuario/pedido |

### 2.8 Pedidos / OMS (4 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `orders` | uuid, order_number (UPP-YYYYMMDD-XXXXX), 11 status, payment_status (7), fulfillment_status (5), source, totais |
| `order_items` | seller_id, snapshot nome/sku/preco, commission_rate/amount, ncm, cfop, warehouse_id |
| `order_status_history` | from/to_status, changed_by, reason, metadata JSON |
| `order_splits` | Sub-pedido por seller: split_number, totais, commission_total, seller_net, warehouse_id |

### 2.9 Pagamentos (4 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `payment_methods` | Metodos salvos: tipo, gateway, token, last_four |
| `payment_transactions` | uuid, gateway_transaction_id, PIX (qr_code/copy_paste), boleto (url/barcode), 10 status, idempotency_key |
| `payment_splits` | Split por seller: gross, marketplace_fee, gateway_fee, net, settlement_date |
| `payment_webhooks` | Payload JSON, processed flag, retries |

### 2.10 Financeiro / Ledger (4 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `finance_accounts` | owner_type/id, account_type (9 tipos), balance, blocked_balance |
| `finance_entries` | Lancamentos credit/debit, balance_after, reference |
| `finance_transactions` | Double-entry: debit_account + credit_account, 9 tipos, idempotency_key |
| `finance_settlements` | Liquidacoes sellers: periodo, gross/net, status (pending->transferred->reversed) |

### 2.11 Armazens / CDs (3 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `warehouses` | tipo (owned/seller/3pl/dropship), prioridade, capacity_m3, operating_hours JSON, database_host/name, api_endpoint |
| `warehouse_zones` | Zonas: receiving/storage/picking/packing/shipping/returns |
| `warehouse_locations` | Posicoes: aisle, rack, shelf, bin, limites peso/volume |

### 2.12 WMS - Estoque (2 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `wms_stock` | quantity, reserved, available (GENERATED = qty - reserved), min/max, lote, validade, unit_cost |
| `wms_stock_movements` | 10 tipos (inbound/outbound/reserve/release/adjustment/transfer_in/out/return/damage/count) |

### 2.13 WMS - Recebimento (2 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `wms_receiving` | tipo (purchase/return/transfer/adjustment), supplier, invoice, expected/received/rejected |
| `wms_receiving_items` | expected/received/rejected_qty, location, lote, validade |

### 2.14 WMS - Picking (2 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `wms_picking_orders` | tipo (single/batch/wave/zone), priority, assigned_to, total/picked_items |
| `wms_picking_items` | quantity_required/picked, location_id, status |

### 2.15 WMS - Inventario (2 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `wms_inventory_counts` | tipo (cycle/full/spot/abc), zone_id, status (planned->approved) |
| `wms_inventory_count_items` | system_qty, counted_qty, variance (GENERATED), adjustment_applied |

### 2.16 TMS - Expedicao (3 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `carriers` | tipo, api_provider (correios/melhor_envio/etc), api_credentials JSON |
| `shipments` | tracking_code/url, label_url, invoice_key, 11 status, delivery_signature/photo |
| `shipment_tracking` | Eventos: status, description, location, occurred_at, raw_data JSON |

### 2.17 TMS Extra (7 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `tms_vehicles` | Frota: plate, model, type, capacity, status, current_driver |
| `tms_drivers` | CNH, vehicle_id, GPS (lat/lng), rating_avg, total_deliveries |
| `tms_routes` | route_number, driver, vehicle, date, total_stops, total_distance_km |
| `tms_route_stops` | Paradas: shipment_id, stop_order, coordenadas, status, attempt_count |
| `tms_delivery_proofs` | POD: signature/photo/pin_code, recipient, coordenadas |
| `tms_delivery_attempts` | Historico tentativas (max 3), failure_reason |
| `tms_delivery_zones` | Zonas por CD: range de CEP, delivery_days, fee |

### 2.18 Devolucoes / RMA (2 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `returns` | reason (8 motivos), refund_method, refund_amount, 8 status |
| `return_items` | quantity, condition (new/used/damaged/unsellable), restock flag |

### 2.19 Fiscal (4 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `fiscal_documents` | NF-e/NFC-e, serie, number, access_key (44), CFOP, impostos, xml_signed/authorized, environment |
| `fiscal_document_items` | NCM, CEST, CFOP, todos impostos por item (ICMS, ICMS-ST, PIS, COFINS, IPI) |
| `fiscal_events` | authorization, cancellation, cc_e, disabling, manifestation |
| `fiscal_certificates` | Certificados A1: certificate BLOB, password_enc, validade |

### 2.20 Ads Engine (4 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `ads_campaigns` | tipo, bid (cpc/cpa/cpm), budget, metricas, target JSON, frequency_cap |
| `ads_creatives` | product_id, title, image_url, destination_url |
| `ads_impressions` | placement, cost |
| `ads_clicks` | custo, ip, is_valid, fraud_score |
| `ads_conversions` | click_id, order_id, conversion_value, attribution |

### 2.21 Apoio (6 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `notifications` | channel (app/email/sms/push/whatsapp), title, body, action_url |
| `system_settings` | group + key (unique), value, value_type |
| `event_store` | event_type, aggregate, payload JSON, version, published |
| `shipping_quotes` | cotacoes com validade 1h |
| `shipping_rules` | regras por seller: tipo, conditions/action JSON |
| `wishlists` | Favoritos: unique (user, product) |

### 2.22 Chat (2 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `conversations` | buyer-seller, order_id opcional, status (open/closed/escalated) |
| `messages` | body, attachment_url, read_at |

### 2.23 CDs e Regras (4 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `cd_sync_log` | Log sync: direction, entity_type, action, status, retries |
| `cd_stock_snapshot` | Reconciliacao: central_qty vs cd_qty, variance (GENERATED) |
| `rules` | 9 dominios, conditions/actions JSON, priority, validade |
| `rules_execution_log` | Auditoria: input, result, matched, execution_ms |

### 2.24 Auxiliares (2 tabelas)
| Tabela | Descricao |
|--------|-----------|
| `idempotency_keys` | Lock para operacoes idem potentes, TTL 1h, cleanup via MySQL EVENT |
| `idempotency_cache` | Cache de respostas idempotentes, TTL 24h |

### 2.25 Estoque Consolidado (1 tabela)
| Tabela | Descricao |
|--------|-----------|
| `stock_consolidated` | Cache materializado: product_id, variant_id, warehouse_id, quantity, reserved, available. Atualizado via sync |

---

## 3. db_erp_cd01 - TABELAS LOCAIS DO CD (20 tabelas)

| Tabela | Descricao |
|--------|-----------|
| `cd_config` | warehouse_code, api_central_url, api_key, sync_interval, status |
| `products_local` | Catalogo sincronizado (ID, SKU, EAN, NCM, dimensoes) |
| `orders_local` | Pedidos do central (order_split_id, endereco entrega) |
| `order_items_local` | Itens do pedido local |
| `warehouse_zones` | 6 tipos de zona |
| `warehouse_locations` | corredor/rack/prateleira/bin |
| `wms_stock` | available (GENERATED) |
| `wms_stock_movements` | 10 tipos |
| `wms_receiving` | Recebimento com NF-e |
| `wms_receiving_items` | Conferencia |
| `wms_picking_orders` | 4 tipos (single/batch/wave/zone) |
| `wms_picking_items` | Separacao |
| `wms_inventory_counts` | Contagem |
| `wms_inventory_count_items` | Variancia calculada |
| `shipments` | 11 status |
| `tms_routes` | Rotas locais |
| `tms_route_stops` | POD: foto, nome, lat/lng |
| `sync_outbox` | Fila de eventos -> central |
| `users_local` | Operadores (operator/supervisor/manager/driver) |

---

## 4. SEED INICIAL (db_marketplace)

### Roles
```sql
INSERT INTO roles (name, slug, is_system) VALUES
('Administrador', 'admin', 1),
('Vendedor', 'seller', 1),
('Cliente', 'customer', 1),
('Operador CD', 'operator', 1),
('Motorista', 'driver', 1);
```

### Admin Padrao
```sql
INSERT INTO users (uuid, name, email, password_hash, user_type, status) VALUES
(UUID(), 'Admin UPPI', 'admin@uppi.app.br', '$argon2id$...', 'admin', 'active');
```

### Categorias Raiz
```
Eletronicos, Moda, Casa e Decoracao, Esportes, Beleza,
Brinquedos, Automotivo, Alimentos, Pet, Livros
```

### Warehouse Padrao
```sql
INSERT INTO warehouses (name, type, priority, is_active) VALUES
('CD Sao Paulo 01', 'owned', 100, 1);
```

### System Settings
```
marketplace.commission_default = 0.10
payment.pix_timeout_minutes = 30
payment.boleto_timeout_hours = 72
shipping.free_threshold = 199.90
fiscal.environment = 2 (homologacao)
```

---

## 5. CONVENCOES

- **PK:** `BIGINT UNSIGNED AUTO_INCREMENT`
- **FK:** sufixo `_id`, CASCADE ou SET NULL conforme dominio
- **Soft Delete:** coluna `deleted_at TIMESTAMP NULL`
- **Money:** `DECIMAL(15,2)` (nunca float)
- **Status:** `VARCHAR` com `CHECK` constraint (sem ENUM nativo)
- **UUID:** `CHAR(36)` com indice UNIQUE para exposicao publica
- **Timestamps:** `created_at DEFAULT CURRENT_TIMESTAMP`, `updated_at ON UPDATE CURRENT_TIMESTAMP`
- **JSON:** campos flexiveis (conditions, payload, metadata, operating_hours)
- **Coluna GENERATED:** available = quantity - reserved, variance = cd_qty - central_qty