erp-transportistas-database-v2/ddl/03-tracking-schema-ddl.sql

-- =============================================================================
-- ERP TRANSPORTISTAS - Schema Tracking DDL
-- =============================================================================
-- Archivo: 03-tracking-schema-ddl.sql
-- Version: 1.0.0
-- Fecha: 2026-01-25
-- Descripcion: Eventos GPS, geocercas, alertas, posiciones
-- =============================================================================

-- =============================================================================
-- TIPOS ENUMERADOS ADICIONALES
-- =============================================================================

CREATE TYPE tracking.tipo_geocerca AS ENUM (
  'CLIENTE',
  'PROVEEDOR',
  'PATIO',
  'ZONA_RIESGO',
  'CASETA',
  'GASOLINERA',
  'PUNTO_CONTROL',
  'OTRO'
);

CREATE TYPE tracking.severidad_alerta AS ENUM (
  'INFO',
  'WARNING',
  'CRITICAL'
);

CREATE TYPE tracking.tipo_alerta AS ENUM (
  'ENTRADA_GEOCERCA',
  'SALIDA_GEOCERCA',
  'EXCESO_VELOCIDAD',
  'PARADA_PROLONGADA',
  'DESVIO_RUTA',
  'TEMPERATURA_FUERA_RANGO',
  'BATERIA_BAJA',
  'SIN_SENAL',
  'BOTON_PANICO',
  'APERTURA_PUERTA',
  'CONSUMO_ANOMALO'
);

-- =============================================================================
-- TABLA: posiciones_gps
-- =============================================================================

CREATE TABLE tracking.posiciones_gps (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  -- Unidad
  unidad_id UUID NOT NULL,

  -- Posición
  latitud DECIMAL(10, 7) NOT NULL,
  longitud DECIMAL(10, 7) NOT NULL,
  altitud DECIMAL(8, 2),

  -- Velocidad y dirección
  velocidad_kmh DECIMAL(6, 2),
  rumbo INT, -- 0-360 grados

  -- Timestamp
  timestamp_gps TIMESTAMPTZ NOT NULL,
  timestamp_servidor TIMESTAMPTZ DEFAULT NOW(),

  -- Datos adicionales GPS
  hdop DECIMAL(4, 2), -- Dilución de precisión horizontal
  satelites INT,

  -- Estado del vehículo
  motor_encendido BOOLEAN,
  odometro INT,

  -- Proveedor
  proveedor_gps VARCHAR(50),
  imei VARCHAR(50),

  -- Viaje asociado (si está en viaje)
  viaje_id UUID,

  -- Partición por fecha
  fecha_particion DATE NOT NULL DEFAULT CURRENT_DATE
) PARTITION BY RANGE (fecha_particion);

-- Crear particiones mensuales (ejemplo para 2026)
CREATE TABLE tracking.posiciones_gps_2026_01 PARTITION OF tracking.posiciones_gps
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE tracking.posiciones_gps_2026_02 PARTITION OF tracking.posiciones_gps
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE tracking.posiciones_gps_2026_03 PARTITION OF tracking.posiciones_gps
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

CREATE INDEX idx_posicion_unidad_fecha ON tracking.posiciones_gps(unidad_id, timestamp_gps);
CREATE INDEX idx_posicion_viaje ON tracking.posiciones_gps(viaje_id) WHERE viaje_id IS NOT NULL;
CREATE INDEX idx_posicion_geo ON tracking.posiciones_gps USING GIST (
  ST_SetSRID(ST_MakePoint(longitud, latitud), 4326)
);

-- =============================================================================
-- TABLA: eventos_tracking
-- =============================================================================

CREATE TABLE tracking.eventos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  -- Viaje
  viaje_id UUID NOT NULL,

  -- Tipo y fuente
  tipo_evento tracking.tipo_evento NOT NULL,
  fuente tracking.fuente_evento NOT NULL,

  -- Ubicación
  latitud DECIMAL(10, 7),
  longitud DECIMAL(10, 7),
  direccion TEXT,

  -- Timestamp
  timestamp_evento TIMESTAMPTZ NOT NULL,
  timestamp_registro TIMESTAMPTZ DEFAULT NOW(),

  -- Datos específicos del evento
  datos JSONB,

  -- Parada asociada (si aplica)
  parada_id UUID,

  -- Usuario/Operador que generó
  generado_por_id UUID,
  generado_por_tipo VARCHAR(20), -- 'OPERADOR', 'SISTEMA', 'USUARIO'

  -- Evidencias
  evidencias JSONB,

  -- Observaciones
  observaciones TEXT
);

CREATE INDEX idx_evento_viaje ON tracking.eventos(viaje_id);
CREATE INDEX idx_evento_tipo ON tracking.eventos(tenant_id, tipo_evento);
CREATE INDEX idx_evento_fecha ON tracking.eventos(timestamp_evento);

-- =============================================================================
-- TABLA: geocercas
-- =============================================================================

CREATE TABLE tracking.geocercas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  -- Identificación
  codigo VARCHAR(50) NOT NULL,
  nombre VARCHAR(200) NOT NULL,
  tipo tracking.tipo_geocerca NOT NULL,

  -- Geometría (polígono o círculo)
  es_circular BOOLEAN DEFAULT FALSE,

  -- Para geocerca circular
  centro_latitud DECIMAL(10, 7),
  centro_longitud DECIMAL(10, 7),
  radio_metros DECIMAL(10, 2),

  -- Para geocerca poligonal (GeoJSON)
  poligono GEOMETRY(POLYGON, 4326),

  -- Asociación
  cliente_id UUID, -- Si tipo = CLIENTE
  direccion TEXT,

  -- Alertas
  alerta_entrada BOOLEAN DEFAULT TRUE,
  alerta_salida BOOLEAN DEFAULT TRUE,
  tiempo_permanencia_minutos INT, -- Alerta si permanece más de X minutos

  -- Configuración
  color VARCHAR(7) DEFAULT '#FF0000',
  activa BOOLEAN DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMPTZ DEFAULT NOW(),
  created_by_id UUID NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_geocerca_tenant ON tracking.geocercas(tenant_id);
CREATE INDEX idx_geocerca_tipo ON tracking.geocercas(tenant_id, tipo);
CREATE INDEX idx_geocerca_geo ON tracking.geocercas USING GIST (poligono);
CREATE UNIQUE INDEX idx_geocerca_codigo ON tracking.geocercas(tenant_id, codigo);

-- =============================================================================
-- TABLA: alertas
-- =============================================================================

CREATE TABLE tracking.alertas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  -- Tipo y severidad
  tipo tracking.tipo_alerta NOT NULL,
  severidad tracking.severidad_alerta NOT NULL,

  -- Referencias
  unidad_id UUID,
  viaje_id UUID,
  geocerca_id UUID REFERENCES tracking.geocercas(id),
  operador_id UUID,

  -- Ubicación
  latitud DECIMAL(10, 7),
  longitud DECIMAL(10, 7),

  -- Descripción
  titulo VARCHAR(200) NOT NULL,
  mensaje TEXT NOT NULL,

  -- Datos específicos
  datos JSONB,

  -- Timestamp
  timestamp_alerta TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Estado
  leida BOOLEAN DEFAULT FALSE,
  leida_por UUID,
  leida_fecha TIMESTAMPTZ,

  atendida BOOLEAN DEFAULT FALSE,
  atendida_por UUID,
  atendida_fecha TIMESTAMPTZ,
  resolucion TEXT,

  -- Notificaciones enviadas
  notificaciones_enviadas JSONB
);

CREATE INDEX idx_alerta_tenant ON tracking.alertas(tenant_id);
CREATE INDEX idx_alerta_unidad ON tracking.alertas(unidad_id);
CREATE INDEX idx_alerta_viaje ON tracking.alertas(viaje_id);
CREATE INDEX idx_alerta_no_atendida ON tracking.alertas(tenant_id, atendida) WHERE atendida = FALSE;
CREATE INDEX idx_alerta_fecha ON tracking.alertas(timestamp_alerta);

-- =============================================================================
-- TABLA: reglas_alerta
-- =============================================================================

CREATE TABLE tracking.reglas_alerta (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  -- Identificación
  nombre VARCHAR(200) NOT NULL,
  descripcion TEXT,

  -- Tipo de alerta que genera
  tipo_alerta tracking.tipo_alerta NOT NULL,
  severidad tracking.severidad_alerta DEFAULT 'WARNING',

  -- Condiciones (JSON con configuración)
  condiciones JSONB NOT NULL,
  -- Ejemplo: { "velocidad_max": 100, "tiempo_parada_max_min": 30, "temp_min": -18, "temp_max": -15 }

  -- Aplicabilidad
  aplica_todas_unidades BOOLEAN DEFAULT TRUE,
  unidades_ids UUID[],
  aplica_todos_viajes BOOLEAN DEFAULT TRUE,

  -- Notificaciones
  notificar_email BOOLEAN DEFAULT TRUE,
  notificar_sms BOOLEAN DEFAULT FALSE,
  notificar_push BOOLEAN DEFAULT TRUE,
  destinatarios JSONB, -- Array de {tipo, id, email, telefono}

  -- Estado
  activa BOOLEAN DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMPTZ DEFAULT NOW(),
  created_by_id UUID NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_regla_tenant ON tracking.reglas_alerta(tenant_id);
CREATE INDEX idx_regla_tipo ON tracking.reglas_alerta(tipo_alerta) WHERE activa = TRUE;

-- =============================================================================
-- TABLA: eta_calculado
-- =============================================================================

CREATE TABLE tracking.eta_calculado (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  viaje_id UUID NOT NULL,
  parada_id UUID,

  -- ETA
  eta_original TIMESTAMPTZ,
  eta_actual TIMESTAMPTZ NOT NULL,
  eta_anterior TIMESTAMPTZ,

  -- Cálculo
  distancia_restante_km DECIMAL(10, 2),
  tiempo_restante_minutos INT,

  -- Factores
  factor_trafico DECIMAL(3, 2) DEFAULT 1.00,
  factor_clima DECIMAL(3, 2) DEFAULT 1.00,

  -- Estado
  estado VARCHAR(20), -- 'EN_TIEMPO', 'ADELANTADO', 'RETRASADO'
  minutos_diferencia INT,

  -- Timestamp
  calculado_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_eta_viaje ON tracking.eta_calculado(viaje_id);
CREATE INDEX idx_eta_fecha ON tracking.eta_calculado(calculado_at);

-- =============================================================================
-- RLS POLICIES
-- =============================================================================

ALTER TABLE tracking.posiciones_gps ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracking.eventos ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracking.geocercas ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracking.alertas ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracking.reglas_alerta ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracking.eta_calculado ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_posiciones ON tracking.posiciones_gps
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_eventos ON tracking.eventos
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_geocercas ON tracking.geocercas
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_alertas ON tracking.alertas
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_reglas ON tracking.reglas_alerta
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_eta ON tracking.eta_calculado
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- =============================================================================
-- COMENTARIOS
-- =============================================================================

COMMENT ON TABLE tracking.posiciones_gps IS 'Posiciones GPS de unidades (particionada por fecha)';
COMMENT ON TABLE tracking.eventos IS 'Eventos de tracking durante viajes';
COMMENT ON TABLE tracking.geocercas IS 'Geocercas/zonas de interés';
COMMENT ON TABLE tracking.alertas IS 'Alertas generadas por el sistema de tracking';
COMMENT ON TABLE tracking.reglas_alerta IS 'Reglas de configuración para generar alertas';
COMMENT ON TABLE tracking.eta_calculado IS 'Historial de cálculos de ETA';

-- =============================================================================
-- FIN DDL TRACKING
-- =============================================================================