# OT Creation Flow - High Level Review

## Overview

This document provides a high-level review of the complete OT (Orden de Trabajo) creation flow, including all involved database tables, data transformations, business logic decisions, and process steps.

---
c

---

## Complete Table Reference

### Tables Directly Modified

#### 1. **OT** (Primary Table)
- **Operation**: INSERT
- **When**: Step 7.1
- **Fields Written**:
  - `id_ot` (auto-generated)
  - `rut_cliente` (FK → Clientes)
  - `rut_tecnico` (FK → Tecnicos)
  - `direccion_instalacion`
  - `comuna_instalacion`
  - `telefono_instalacion`
  - `email_instalacion`
  - `estado_ot` (default: 'pendiente')
  - `empresa`
  - `taller`
  - `motivo`
  - `logo_taller`
  - `logo_servicio`
  - `creado_en` (auto)
  - `actualizado_en` (auto)
- **Dependencies**: Clientes, Tecnicos must exist

#### 2. **Clientes** (Reference Table)
- **Operation**: SELECT, INSERT, UPDATE
- **When**: Step 4
- **Fields Read**: `rut_cliente`, `nombre`, `direccion`, `comuna`, `telefono`, `email`
- **Fields Written** (on create): All above fields
- **Fields Written** (on update): Only fields that differ
- **Behavior**: Auto-create if not exists, auto-update if data differs

#### 3. **Vehiculos** (Reference Table)
- **Operation**: INSERT OR IGNORE
- **When**: Step 7.2.A
- **Fields Written**: `patente`, `marca`, `modelo`, `color`, `anio`
- **Behavior**: Upsert - insert if new, ignore if exists (no update)

#### 4. **OT_Vehiculos** (Junction Table)
- **Operation**: INSERT
- **When**: Step 7.2.B
- **Fields Written**: `id_ot`, `patente`
- **Dependencies**: OT and Vehiculos must exist
- **Creates**: Many-to-Many relationship between OT and Vehiculos

### Tables Read Only

#### 5. **Tecnicos** (Reference Table)
- **Operation**: SELECT
- **When**: Step 3
- **Fields Read**: `rut_tecnico`, `nombre`, `idgpsimple`
- **Purpose**: Verify technician exists before creating OT
- **Constraint**: Must exist (cannot be auto-created)

---

## Data Flow Summary

### Input Data Structure
```json
{
  "cliente": {
    "rut": "12345678",           // Raw RUT (no formatting)
    "nombre": "Juan Pérez",
    "direccion": "Av. Principal 123",
    "comuna": "Santiago",
    "telefono": "+56912345678",
    "email": "cliente@example.com"
  },
  "tecnico": {
    "rut": "98765432"            // Raw RUT (no formatting)
  },
  "servicio": {
    "presGPS": "gpsimple",       // GPS provider code
    "tiposerv": "instalacion_gps", // Service type code
    "taller_install": "cartec"   // Taller code
  },
  "vehiculos": [
    {
      "patente": "ABCD12",
      "marca": "Toyota",
      "modelo": "Corolla",
      "color": "Blanco",
      "anio": 2020
    }
  ]
}
```

### Data Transformations

#### RUT Transformation
- **Input**: `"12345678"` (raw, no formatting)
- **Process**: Clean → Validate → Calculate verification digit
- **Output**: `"12345678-9"` (formatted with verification digit)

#### Business Logic Transformations

**GPS Provider Mapping**:
- **Input**: `presGPS = "gpsimple"`
- **Output**: 
  - `empresa = "gpsimple"`
  - `logo_servicio = "https://media.recuperauto.cl/Media/Cartec/gpsimple-logo.png"`

**Service Type Mapping**:
- **Input**: `tiposerv = "instalacion_gps"` + `presGPS = "gpsimple"`
- **Output**: `motivo = "Instalación GPS"`

**Taller Mapping**:
- **Input**: `taller_install = "cartec"`
- **Output**:
  - `taller = "domicilio"`
  - `logo_taller = "https://media.recuperauto.cl/Media/Cartec/cartec-logo.png"`

### Output Data Structure
```json
{
  "success": true,
  "message": "OT creada exitosamente",
  "data": {
    "id_ot": 12345,
    "estado": "pendiente",
    "created_at": "2024-01-15T10:30:00Z"
  }
}
```

---

## Database Operations Sequence

### Read Operations (Before Transaction)
1. **SELECT** from `Tecnicos` - Verify technician exists
2. **SELECT** from `Clientes` - Check if client exists

### Write Operations (Within Transaction)
1. **INSERT** into `Clientes` - Create client if not exists (or UPDATE if data differs)
2. **INSERT** into `OT` - Create order of work record
3. **INSERT OR IGNORE** into `Vehiculos` - Create vehicles (one per vehicle)
4. **INSERT** into `OT_Vehiculos` - Link vehicles to OT (one per vehicle)

### Transaction Boundaries
- **Start**: Before OT creation
- **Commit**: After all vehicles are linked
- **Rollback**: On any error during Steps 1-4 above

---

## Business Rules Applied

### 1. RUT Validation Rule
- **Rule**: RUTs must be valid Chilean RUT format
- **Applied**: Step 2
- **Action**: Calculate verification digit, format with dash
- **Failure**: Return 400 Bad Request

### 2. Technician Existence Rule
- **Rule**: Technician must exist in system
- **Applied**: Step 3
- **Action**: Query Tecnicos table
- **Failure**: Return 404 Not Found

### 3. Client Auto-Creation Rule
- **Rule**: Client can be auto-created if not exists
- **Applied**: Step 4
- **Action**: Create Clientes record with provided data
- **Failure**: Return 400 Bad Request

### 4. Client Auto-Update Rule
- **Rule**: Update client data if differs from existing record
- **Applied**: Step 4
- **Action**: Compare fields, UPDATE if different
- **Failure**: Log warning, continue

### 5. Vehicle Upsert Rule
- **Rule**: Vehicles use insert-or-ignore behavior
- **Applied**: Step 7.2.A
- **Action**: INSERT OR IGNORE (no update if exists)
- **Failure**: Rollback transaction

### 6. Service Type Mapping Rule
- **Rule**: Service type depends on GPS provider
- **Applied**: Step 5
- **Action**: Map tiposerv + presGPS → motivo
- **Failure**: Use default value

### 7. Default Values Rule
- **Rule**: Apply defaults if values not provided
- **Applied**: Step 5
- **Defaults**:
  - `empresa = "gpsimple"`
  - `taller = "domicilio"`
  - `estado_ot = "pendiente"`
  - `motivo = "Visita_tecnica GPS"`

---

## Error Handling Flow

### Validation Errors (400 Bad Request)
- **When**: Steps 1, 2, 6
- **Action**: Return error immediately, no database operations
- **Tables Affected**: None

### Business Logic Errors (404 Not Found)
- **When**: Step 3 (technician not found)
- **Action**: Return error immediately, no database operations
- **Tables Affected**: None

### Database Errors (500 Internal Server Error)
- **When**: Steps 4, 7.1, 7.2
- **Action**: Rollback transaction, return error
- **Tables Affected**: All (rolled back)

### Exception Errors (500 Internal Server Error)
- **When**: Any unexpected exception
- **Action**: Log error, return generic error message
- **Tables Affected**: Depends on when exception occurred

---

## Performance Considerations

### Database Queries Count
- **Minimum**: 3 queries (technician check, client check, OT insert, vehicles insert, links insert)
- **Maximum**: 5+ queries (if client update needed, multiple vehicles)

### Transaction Duration
- **Typical**: < 100ms
- **Factors**: Number of vehicles, database load, network latency

### Optimization Opportunities
- Batch vehicle inserts (already implemented)
- Client update only if data differs (already implemented)
- Vehicle upsert prevents duplicate inserts (already implemented)

---

## Security Considerations

### Input Sanitization
- **RUT**: Cleaned of formatting before processing
- **Strings**: Trimmed and validated
- **Email**: Format validated
- **SQL Injection**: Prevented via Eloquent/Query Builder

### Data Validation
- **Server-side**: All validations performed server-side
- **Client-side**: Not trusted (validated again server-side)

### Authentication
- **Required**: API token authentication
- **Authorization**: Role-based access control (optional)

---

## Dependencies

### External Libraries
- **malahierba-lab/chile-rut**: RUT validation and verification digit calculation

### Database Tables
- **Required**: OT, Clientes, Tecnicos, Vehiculos, OT_Vehiculos
- **Optional**: Contactos (not used in creation, used later)

### System Requirements
- **PHP**: 7.2+
- **Laravel**: 6.0+
- **Database**: MySQL/MariaDB with foreign key support

---

## Post-Creation State

After successful OT creation:

### Created Records
- ✅ 1 record in `OT` table
- ✅ 0-1 records in `Clientes` table (if new client)
- ✅ N records in `Vehiculos` table (N = number of vehicles, only new ones)
- ✅ N records in `OT_Vehiculos` table (N = number of vehicles)

### OT State
- **estado_ot**: "pendiente"
- **Ready for**: Work execution phase
- **Next step**: Technician performs work, creates services

### Related Records (Not Created Yet)
- ❌ `Servicios` - Created during work execution
- ❌ `OT_Actividades` - Created during work execution
- ❌ `Contactos` - Created when signature is captured
- ❌ `OT.rut_contacto` - Set when signature is captured
- ❌ `OT.imgfirma` - Set when signature is captured

---

## Summary

### Flow Overview
1. **Validate** input data (no DB)
2. **Process** RUTs (no DB)
3. **Verify** technician exists (SELECT from Tecnicos)
4. **Verify/Create** client (SELECT/INSERT/UPDATE Clientes)
5. **Process** business logic (no DB)
6. **Validate** vehicles (no DB)
7. **Create** OT and link vehicles (INSERT into OT, Vehiculos, OT_Vehiculos)
8. **Log** and return response (no DB)

### Tables Modified
- **OT**: 1 INSERT
- **Clientes**: 0-1 INSERT, 0-1 UPDATE
- **Vehiculos**: 0-N INSERT (N = new vehicles)
- **OT_Vehiculos**: N INSERT (N = total vehicles)

### Tables Read
- **Tecnicos**: 1 SELECT
- **Clientes**: 1 SELECT

### Transaction Scope
- **Start**: Before OT creation
- **Includes**: OT insert, vehicle inserts, vehicle links
- **Excludes**: Technician check, client check (done before transaction)

---

**Document Version**: 1.0  
**Last Updated**: 2024-01-15  
**Status**: Complete