# Auditoría de Schema - Sistema de Gestión Ozado

**Fecha**: 2026-02-04  
**Versión**: 2.1  
**Modelo elegido**: Opción 1 (receivables = documentos + CxC)  
**Estado**: ✅ IMPLEMENTACIÓN COMPLETA

---

## 1. AUDITORÍA DE COLUMNAS *_id

### Estado: ✅ TODAS LAS REFERENCIAS TIENEN TABLA DESTINO

| Tabla | Columna | Tabla Destino | FK Definido | Estado |
|-------|---------|---------------|-------------|--------|
| calendar_events | bank_account_id | bank_accounts | ✅ | OK |
| calendar_events | financial_document_id | financial_documents | ✅ | OK |
| calendar_events | hosting_domain_id | hosting_domains | ✅ | OK |
| calendar_events | task_id | tasks | ✅ | OK |
| calendar_events | client_id | clients | ✅ | OK |
| calendar_events | project_id | projects | ✅ | OK |
| calendar_events | receivable_id | receivables | ✅ | OK |
| calendar_events | client_service_id | client_services | ✅ | OK |
| receivables | quote_id | quotes | ✅ | OK |
| receivables | client_service_id | client_services | ✅ | OK |
| receivables | income_event_id | calendar_events | ✅ | OK |
| users | employee_id | employees | ✅ | OK |
| tasks | assigned_employee_id | employees | ✅ | OK |
| quote_items | package_id | service_packages | ❌ Sin FK | PENDIENTE |

### Columna sin FK (no crítico)

| Tabla | Columna | Destino | Decisión |
|-------|---------|---------|----------|
| quote_items | package_id | service_packages | Agregar FK opcional |

---

## 2. MODELO FINANCIERO: OPCIÓN 1

### Decisión Clave
- **receivables** = Documento + Cuenta por cobrar (CxC)
- **NO crear** tabla extra "estado_de_cuenta"
- Estado de cuenta = reporte calculado desde receivables

### Tablas Existentes

| Tabla | Propósito | Estado |
|-------|-----------|--------|
| receivables | Documentos/CxC del cliente | ✅ Existe |
| financial_documents | Docs formales SUNAT | ✅ Existe (redundante?) |
| financial_payments | Pagos a financial_documents | ✅ Existe |
| advances | Adelantos de clientes | ✅ Existe |
| advance_allocations | Aplicar adelantos | ✅ Existe |

### Tablas para Opción 1

| Tabla | Propósito | Estado |
|-------|-----------|--------|
| payments | Pagos generales del cliente | ✅ CREADO |
| payment_allocations | Aplicar pagos a receivables | ✅ CREADO |

---

## 3. ANÁLISIS: receivables vs financial_documents

### Campos en `receivables`
```
doc_type: VARCHAR(20) DEFAULT 'nota_ingreso'
doc_series: VARCHAR(10)
doc_number: VARCHAR(20)
doc_issue_date: DATE
doc_status: ENUM('pending','issued','voided')
amount: DECIMAL(12,2)
status: VARCHAR(20) DEFAULT 'pending'
has_detraction, detraction_pct, detraction_amount, detraction_status
```

### Campos en `financial_documents`
```
doc_type: ENUM('FACTURA','RH','NOTA_VENTA','NC','ND')
doc_series: VARCHAR(20)
doc_number: VARCHAR(30)
doc_issue_date: DATE
total_amount: DECIMAL(12,2)
status: ENUM('PENDIENTE','PARCIAL','PAGADO','ANULADO')
has_detraccion, detraccion_pct, detraccion_amount
```

### Decisión
- **Usar receivables como tabla principal** para CxC y documentos operativos
- **financial_documents** queda para documentos formales SUNAT (mantener para compatibilidad)
- **Crear payments + payment_allocations** para pagos aplicados a receivables

---

## 4. DIAGRAMA: CLIENTE COMO HUB

```
                           ┌──────────────────┐
                           │     CLIENTS      │
                           │   (HUB CENTRAL)  │
                           └────────┬─────────┘
                                    │
        ┌───────────────────────────┼───────────────────────────┐
        │                           │                           │
        ▼                           ▼                           ▼
┌───────────────┐          ┌───────────────┐          ┌───────────────┐
│   projects    │          │client_services│          │hosting_domains│
│  FK RESTRICT  │          │  FK RESTRICT  │          │  FK RESTRICT  │
└───────┬───────┘          └───────────────┘          └───────────────┘
        │
        │
        ▼
┌───────────────┐          ┌───────────────┐          ┌───────────────┐
│    quotes     │          │  receivables  │          │   advances    │
│  FK RESTRICT  │◄─────────│  FK SET NULL  │          │  FK RESTRICT  │
└───────────────┘          └───────┬───────┘          └───────┬───────┘
                                   │                          │
                                   │                          │
                    ┌──────────────┴──────────────┐           │
                    ▼                             ▼           ▼
           ┌───────────────┐            ┌────────────────────────┐
           │   payments    │            │  advance_allocations   │
           │   ✅ CREADO   │            │       ✅ EXISTE        │
           └───────┬───────┘            └────────────────────────┘
                   │
                   ▼
           ┌────────────────────┐
           │ payment_allocations│
           │     ✅ CREADO      │
           └────────────────────┘
```

---

## 5. TABLAS A CREAR

### 5.1 payments (pagos generales del cliente)

```sql
CREATE TABLE payments (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    client_id INT UNSIGNED NOT NULL,
    payment_date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'PEN',
    method VARCHAR(80) NULL,
    bank_account_id INT UNSIGNED NULL,
    reference VARCHAR(100) NULL,
    proof_url TEXT NULL,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_payments_client (client_id),
    INDEX idx_payments_date (payment_date),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
    FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);
```

### 5.2 payment_allocations (aplicar pagos a receivables)

```sql
CREATE TABLE payment_allocations (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    payment_id INT UNSIGNED NOT NULL,
    receivable_id INT UNSIGNED NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    allocation_date DATE NOT NULL,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_pa_payment (payment_id),
    INDEX idx_pa_receivable (receivable_id),
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE RESTRICT,
    FOREIGN KEY (receivable_id) REFERENCES receivables(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);
```

---

## 6. REGLAS DE NEGOCIO

### Cálculo de Saldo en Receivables

```
paid_payments = SUM(payment_allocations.amount WHERE receivable_id = X)
paid_advances = SUM(advance_allocations.amount WHERE receivable_id = X)
paid_total = paid_payments + paid_advances
balance = receivables.amount - paid_total

status:
  - 'paid' si balance <= 0
  - 'partial' si 0 < balance < amount
  - 'pending' si paid_total = 0
  - 'overdue' si due_date < TODAY AND status != 'paid'
```

### Protección de Integridad

- payments.client_id → FK RESTRICT (no borrar cliente con pagos)
- advances.client_id → FK RESTRICT (ya existe)
- payment_allocations → FK RESTRICT (no borrar pago/receivable con allocations)

---

## 7. UI REQUERIDA EN FICHA CLIENTE

| Tab | Contenido | Estado |
|-----|-----------|--------|
| Resumen | KPIs del cliente | ✅ Implementado |
| Proyectos | Lista de projects | ✅ Implementado |
| Servicios | client_services + hosting_domains | ✅ Implementado |
| Documentos | receivables con saldo/estado | ✅ Implementado |
| Pagos | payments + aplicar a documento | ✅ Implementado |
| Adelantos | advances + aplicar a documento | ✅ Implementado |
| Estado de cuenta | Balance consolidado | ✅ Implementado |
| Cotizaciones | quotes | ✅ Implementado |
| Tareas | tasks | ✅ Implementado |

---

## 8. CHECKLIST DE PRUEBAS

### 8.1 Pruebas de UI (Manual)

- [ ] Abrir ficha de cliente existente
- [ ] Verificar que aparecen las tabs: Resumen, Proyectos, Servicios, Documentos, **Pagos**, **Adelantos**, Estado de cuenta, Cotizaciones, Tareas
- [ ] Tab Pagos: verificar que aparece botón "Registrar pago"
- [ ] Tab Adelantos: verificar que aparece botón "Registrar adelanto"
- [ ] Tab Estado de cuenta: verificar KPIs (Total facturado, Total pagado, Saldo pendiente, Adelantos disponibles)

### 8.2 Pruebas Funcionales

- [ ] Crear receivable (doc) para cliente
- [ ] Registrar pago parcial desde Tab Pagos
- [ ] Aplicar pago a documento (botón "Aplicar")
- [ ] Verificar saldo actualizado en Tab Estado de cuenta
- [ ] Verificar estado cambia a 'partial' en la tabla
- [ ] Registrar pago completo y aplicar
- [ ] Verificar estado cambia a 'paid'
- [ ] Registrar adelanto desde Tab Adelantos
- [ ] Aplicar adelanto a documento (botón "Aplicar")
- [ ] Verificar saldo con adelanto aplicado
- [ ] Ver estado de cuenta consolidado con todos los movimientos

### 8.3 Servicios Creados

| Archivo | Funciones | Estado |
|---------|-----------|--------|
| app/services/payments_service.php | create_payment, apply_payment_to_receivable, list_payments, etc. | ✅ |
| app/services/advances_service.php | create_advance, apply_advance_to_receivable, list_advances, etc. | ✅ |

### 8.4 Endpoints API

| Endpoint | Acciones | Estado |
|----------|----------|--------|
| /api/payments.php | create_payment, apply_payment | ✅ |
| /api/advances.php | create_advance, apply_advance | ✅ |

---

## 9. DECISIONES PENDIENTES

| Tema | Opciones | Decisión |
|------|----------|----------|
| financial_documents vs receivables | Unificar / Mantener separados | **Mantener separados** (compatibilidad) |
| financial_payments | Migrar a payment_allocations / Mantener | **Mantener** (legacy) |
| Estado de cuenta | Tabla vs Calculado | **Calculado** (Opción 1) |