# Tablas de Base de Datos - Sistema de Envío SMS

## Resumen de Tablas

El sistema de envío SMS requiere **5 tablas principales** para funcionar correctamente:

### 1. `sms_contactos`
**Propósito:** Almacena contactos manuales agregados por el usuario

**Campos:**
- `id_contacto` (INT, PK, AUTO_INCREMENT) - ID único del contacto
- `nombre` (VARCHAR(255)) - Nombre completo del contacto
- `telefono` (VARCHAR(20)) - Número de teléfono (único)
- `tipo` (VARCHAR(50)) - Tipo: cliente, proveedor, personal, otro
- `notas` (TEXT) - Notas adicionales sobre el contacto
- `fecha_creacion` (DATETIME) - Fecha de creación
- `creado_por` (VARCHAR(100)) - Usuario que creó el contacto
- `activo` (TINYINT(1)) - Estado activo/inactivo

**Índices:**
- `unique_telefono` - Teléfono único
- `idx_tipo` - Búsqueda por tipo
- `idx_activo` - Filtrado por estado

---

### 2. `sms_plantillas`
**Propósito:** Almacena plantillas de mensajes reutilizables

**Campos:**
- `id_plantilla` (INT, PK, AUTO_INCREMENT) - ID único de la plantilla
- `nombre` (VARCHAR(255)) - Nombre de la plantilla (único)
- `mensaje` (TEXT) - Contenido del mensaje con variables
- `categoria` (VARCHAR(50)) - Categoría: alertas, recordatorios, informativos, promocionales, otro
- `fecha_creacion` (DATETIME) - Fecha de creación
- `fecha_actualizacion` (DATETIME) - Fecha de última actualización
- `creado_por` (VARCHAR(100)) - Usuario que creó la plantilla
- `activo` (TINYINT(1)) - Estado activo/inactivo

**Índices:**
- `unique_nombre` - Nombre único
- `idx_categoria` - Búsqueda por categoría
- `idx_activo` - Filtrado por estado

**Variables disponibles en plantillas:**
- `{nombre}` - Nombre del destinatario
- `{vehiculo}` - Nombre/placa del vehículo
- `{fecha}` - Fecha actual
- `{hora}` - Hora actual
- `{velocidad}` - Velocidad del vehículo
- `{ubicacion}` - Ubicación del vehículo

---

### 3. `sms_mensajes`
**Propósito:** Almacena el historial de todos los mensajes SMS enviados y recibidos

**Campos:**
- `id_mensaje` (INT, PK, AUTO_INCREMENT) - ID único del mensaje
- `telefono` (VARCHAR(20)) - Número de teléfono destinatario/remitente
- `mensaje` (TEXT) - Contenido del mensaje
- `tipo` (ENUM) - 'enviado' o 'recibido'
- `estado` (ENUM) - 'pendiente', 'enviando', 'enviado', 'entregado', 'fallido', 'error'
- `fecha_envio` (DATETIME) - Fecha y hora de envío
- `fecha_entrega` (DATETIME) - Fecha y hora de entrega (si aplica)
- `enviado_por` (VARCHAR(100)) - Usuario que envió el mensaje
- `id_contacto` (INT, FK) - Referencia al contacto (opcional)
- `id_plantilla` (INT, FK) - Referencia a la plantilla usada (opcional)
- `id_emp` (INT) - ID de empresa
- `error_mensaje` (TEXT) - Mensaje de error si falló

**Índices:**
- `idx_telefono` - Búsqueda por teléfono
- `idx_fecha_envio` - Ordenamiento por fecha
- `idx_estado` - Filtrado por estado
- `idx_tipo` - Filtrado por tipo
- `idx_id_contacto` - Relación con contactos
- `idx_id_plantilla` - Relación con plantillas
- `idx_id_emp` - Filtrado por empresa

**Relaciones:**
- `fk_sms_contacto` → `sms_contactos.id_contacto`
- `fk_sms_plantilla` → `sms_plantillas.id_plantilla`

---

### 4. `sms_envios_masivos`
**Propósito:** Registra los envíos masivos de SMS para seguimiento y reportes

**Campos:**
- `id_envio_masivo` (INT, PK, AUTO_INCREMENT) - ID único del envío masivo
- `nombre_envio` (VARCHAR(255)) - Nombre descriptivo del envío (opcional)
- `mensaje` (TEXT) - Mensaje enviado a todos
- `total_destinatarios` (INT) - Total de destinatarios
- `enviados_exitosos` (INT) - Cantidad enviados exitosamente
- `enviados_fallidos` (INT) - Cantidad que fallaron
- `estado` (ENUM) - 'pendiente', 'en_proceso', 'completado', 'cancelado', 'error'
- `fecha_inicio` (DATETIME) - Fecha de inicio del envío
- `fecha_fin` (DATETIME) - Fecha de finalización
- `creado_por` (VARCHAR(100)) - Usuario que inició el envío
- `id_plantilla` (INT, FK) - Plantilla usada (opcional)

**Índices:**
- `idx_estado` - Filtrado por estado
- `idx_fecha_inicio` - Ordenamiento por fecha
- `idx_creado_por` - Filtrado por usuario

**Relaciones:**
- `fk_sms_masivo_plantilla` → `sms_plantillas.id_plantilla`

---

### 5. `sms_destinatarios_masivos`
**Propósito:** Detalle de cada destinatario en un envío masivo

**Campos:**
- `id_destinatario` (INT, PK, AUTO_INCREMENT) - ID único del destinatario
- `id_envio_masivo` (INT, FK) - Referencia al envío masivo
- `telefono` (VARCHAR(20)) - Número de teléfono
- `nombre` (VARCHAR(255)) - Nombre del destinatario
- `estado` (ENUM) - 'pendiente', 'enviado', 'entregado', 'fallido'
- `fecha_envio` (DATETIME) - Fecha de envío individual
- `fecha_entrega` (DATETIME) - Fecha de entrega individual
- `error_mensaje` (TEXT) - Mensaje de error si falló
- `id_mensaje` (INT, FK) - Referencia al mensaje individual

**Índices:**
- `idx_id_envio_masivo` - Relación con envío masivo
- `idx_telefono` - Búsqueda por teléfono
- `idx_estado` - Filtrado por estado
- `idx_id_mensaje` - Relación con mensaje

**Relaciones:**
- `fk_sms_envio_masivo` → `sms_envios_masivos.id_envio_masivo` (CASCADE DELETE)
- `fk_sms_dest_mensaje` → `sms_mensajes.id_mensaje`

---

## Instalación

### Opción 1: Ejecutar script SQL completo
```sql
-- Ejecutar el archivo: create-tables-sms.sql
SOURCE /ruta/a/create-tables-sms.sql;
```

### Opción 2: Las tablas se crean automáticamente
Las tablas se crean automáticamente cuando se usan por primera vez mediante `CREATE TABLE IF NOT EXISTS` en las APIs:
- `sms_contactos` - Se crea en `save-contact.php` y `get-contacts.php`
- `sms_plantillas` - Se crea en `save-template.php` y `get-templates.php`
- `sms_mensajes` - Se crea en `send-sms.php` y `get-messages.php`

---

## Flujo de Datos

1. **Contactos:**
   - Se obtienen de `chofer`, `personas`, `usuario` (tablas existentes)
   - Se pueden agregar manualmente en `sms_contactos`

2. **Envío Individual:**
   - Se guarda en `sms_mensajes`
   - Se puede vincular a `sms_contactos` y `sms_plantillas`

3. **Envío Masivo:**
   - Se crea registro en `sms_envios_masivos`
   - Se crean registros individuales en `sms_destinatarios_masivos`
   - Cada destinatario tiene su registro en `sms_mensajes`

---

## Notas Importantes

- Todas las tablas usan `utf8mb4` para soportar emojis y caracteres especiales
- Las relaciones con `ON DELETE SET NULL` preservan datos históricos
- Los índices optimizan las consultas frecuentes
- El campo `activo` permite soft-delete sin perder datos históricos