erp-transportistas-database-v2/ddl/03a-gps-devices-ddl.sql

-- =============================================================================
-- ERP TRANSPORTISTAS - GPS Devices DDL (Extension)
-- =============================================================================
-- Archivo: 03a-gps-devices-ddl.sql
-- Version: 1.0.0
-- Fecha: 2026-01-28
-- Descripcion: Tablas adicionales para modulo GPS (dispositivos, eventos geocerca, segmentos)
-- Basado en: erp-mecanicas-diesel MMD-014 GPS Integration
-- =============================================================================

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

DO $$ BEGIN
  CREATE TYPE tracking.plataforma_gps AS ENUM (
    'traccar',
    'wialon',
    'samsara',
    'geotab',
    'manual'
  );
EXCEPTION WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
  CREATE TYPE tracking.tipo_unidad_gps AS ENUM (
    'tractora',
    'remolque',
    'caja',
    'equipo',
    'operador'
  );
EXCEPTION WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
  CREATE TYPE tracking.tipo_evento_geocerca AS ENUM (
    'entrada',
    'salida',
    'permanencia'
  );
EXCEPTION WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
  CREATE TYPE tracking.tipo_segmento AS ENUM (
    'hacia_destino',
    'en_destino',
    'retorno',
    'entre_paradas',
    'otro'
  );
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- =============================================================================
-- TABLA: dispositivos_gps
-- =============================================================================

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

  -- Link to fleet unit (fleet.unidades)
  unidad_id UUID NOT NULL,
  tipo_unidad tracking.tipo_unidad_gps DEFAULT 'tractora',

  -- External platform identification
  external_device_id VARCHAR(100) NOT NULL,
  plataforma tracking.plataforma_gps DEFAULT 'traccar',

  -- Device identifiers
  imei VARCHAR(20),
  numero_serie VARCHAR(50),
  telefono VARCHAR(20),
  modelo VARCHAR(50),
  fabricante VARCHAR(50),

  -- Status
  activo BOOLEAN DEFAULT TRUE,
  ultima_posicion_at TIMESTAMPTZ,
  ultima_posicion_lat DECIMAL(10, 7),
  ultima_posicion_lng DECIMAL(10, 7),

  -- Configuration
  intervalo_posicion_segundos INTEGER DEFAULT 30,
  metadata JSONB DEFAULT '{}',

  -- Audit
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_by UUID,

  -- Constraints
  CONSTRAINT uq_dispositivo_external UNIQUE (tenant_id, plataforma, external_device_id),
  CONSTRAINT uq_dispositivo_unidad UNIQUE (tenant_id, unidad_id) WHERE activo = TRUE
);

CREATE INDEX IF NOT EXISTS idx_dispositivos_gps_tenant ON tracking.dispositivos_gps(tenant_id);
CREATE INDEX IF NOT EXISTS idx_dispositivos_gps_unidad ON tracking.dispositivos_gps(unidad_id);
CREATE INDEX IF NOT EXISTS idx_dispositivos_gps_external ON tracking.dispositivos_gps(external_device_id);
CREATE INDEX IF NOT EXISTS idx_dispositivos_gps_plataforma ON tracking.dispositivos_gps(plataforma);

-- =============================================================================
-- TABLA: eventos_geocerca
-- =============================================================================

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

  -- References
  geocerca_id UUID NOT NULL REFERENCES tracking.geocercas(id),
  dispositivo_id UUID NOT NULL REFERENCES tracking.dispositivos_gps(id),
  unidad_id UUID NOT NULL,

  -- Event type
  tipo_evento tracking.tipo_evento_geocerca NOT NULL,

  -- Position that triggered the event
  posicion_id UUID,
  latitud DECIMAL(10, 7) NOT NULL,
  longitud DECIMAL(10, 7) NOT NULL,

  -- Timestamps
  tiempo_evento TIMESTAMPTZ NOT NULL,
  procesado_at TIMESTAMPTZ DEFAULT NOW(),

  -- Link to viaje (if applicable)
  viaje_id UUID,

  -- Metadata
  metadata JSONB DEFAULT '{}'
);

CREATE INDEX IF NOT EXISTS idx_eventos_geocerca_tenant ON tracking.eventos_geocerca(tenant_id);
CREATE INDEX IF NOT EXISTS idx_eventos_geocerca_geocerca ON tracking.eventos_geocerca(geocerca_id);
CREATE INDEX IF NOT EXISTS idx_eventos_geocerca_dispositivo ON tracking.eventos_geocerca(dispositivo_id);
CREATE INDEX IF NOT EXISTS idx_eventos_geocerca_unidad ON tracking.eventos_geocerca(unidad_id);
CREATE INDEX IF NOT EXISTS idx_eventos_geocerca_tiempo ON tracking.eventos_geocerca(tiempo_evento);
CREATE INDEX IF NOT EXISTS idx_eventos_geocerca_viaje ON tracking.eventos_geocerca(viaje_id) WHERE viaje_id IS NOT NULL;

-- =============================================================================
-- TABLA: segmentos_ruta
-- =============================================================================

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

  -- Link to viaje
  viaje_id UUID,
  unidad_id UUID NOT NULL,
  dispositivo_id UUID REFERENCES tracking.dispositivos_gps(id),

  -- Start/end positions
  posicion_inicio_id UUID,
  posicion_fin_id UUID,

  -- Coordinates (denormalized)
  lat_inicio DECIMAL(10, 7) NOT NULL,
  lng_inicio DECIMAL(10, 7) NOT NULL,
  lat_fin DECIMAL(10, 7) NOT NULL,
  lng_fin DECIMAL(10, 7) NOT NULL,

  -- Distances
  distancia_km DECIMAL(10, 3) NOT NULL,
  distancia_cruda_km DECIMAL(10, 3),

  -- Times
  tiempo_inicio TIMESTAMPTZ NOT NULL,
  tiempo_fin TIMESTAMPTZ NOT NULL,
  duracion_minutos DECIMAL(8, 2),

  -- Segment type
  tipo_segmento tracking.tipo_segmento DEFAULT 'otro',

  -- Validation
  es_valido BOOLEAN DEFAULT TRUE,
  notas_validacion TEXT,

  -- Encoded polyline for visualization
  polyline_encoded TEXT,

  -- Metadata
  metadata JSONB DEFAULT '{}',

  -- Audit
  created_at TIMESTAMPTZ DEFAULT NOW(),
  calculado_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_segmentos_ruta_tenant ON tracking.segmentos_ruta(tenant_id);
CREATE INDEX IF NOT EXISTS idx_segmentos_ruta_viaje ON tracking.segmentos_ruta(viaje_id);
CREATE INDEX IF NOT EXISTS idx_segmentos_ruta_unidad ON tracking.segmentos_ruta(unidad_id);
CREATE INDEX IF NOT EXISTS idx_segmentos_ruta_tipo ON tracking.segmentos_ruta(tipo_segmento);
CREATE INDEX IF NOT EXISTS idx_segmentos_ruta_tiempo ON tracking.segmentos_ruta(tiempo_inicio);

-- =============================================================================
-- ALTER posiciones_gps para agregar dispositivo_id
-- =============================================================================

DO $$ BEGIN
  ALTER TABLE tracking.posiciones_gps ADD COLUMN IF NOT EXISTS dispositivo_id UUID REFERENCES tracking.dispositivos_gps(id);
EXCEPTION WHEN others THEN null;
END $$;

DO $$ BEGIN
  ALTER TABLE tracking.posiciones_gps ADD COLUMN IF NOT EXISTS es_valido BOOLEAN DEFAULT TRUE;
EXCEPTION WHEN others THEN null;
END $$;

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

ALTER TABLE tracking.dispositivos_gps ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracking.eventos_geocerca ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracking.segmentos_ruta ENABLE ROW LEVEL SECURITY;

DO $$ BEGIN
  CREATE POLICY tenant_isolation_dispositivos ON tracking.dispositivos_gps
    USING (tenant_id = current_setting('app.tenant_id')::uuid);
EXCEPTION WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
  CREATE POLICY tenant_isolation_eventos_geo ON tracking.eventos_geocerca
    USING (tenant_id = current_setting('app.tenant_id')::uuid);
EXCEPTION WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
  CREATE POLICY tenant_isolation_segmentos ON tracking.segmentos_ruta
    USING (tenant_id = current_setting('app.tenant_id')::uuid);
EXCEPTION WHEN duplicate_object THEN null;
END $$;

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

COMMENT ON TABLE tracking.dispositivos_gps IS 'Dispositivos GPS vinculados a unidades de flota';
COMMENT ON TABLE tracking.eventos_geocerca IS 'Eventos de entrada/salida de geocercas';
COMMENT ON TABLE tracking.segmentos_ruta IS 'Segmentos de ruta para calculo de distancias facturables';

COMMENT ON COLUMN tracking.dispositivos_gps.plataforma IS 'Proveedor GPS: traccar, wialon, samsara, geotab, manual';
COMMENT ON COLUMN tracking.dispositivos_gps.tipo_unidad IS 'Tipo de unidad: tractora, remolque, caja, equipo, operador';
COMMENT ON COLUMN tracking.segmentos_ruta.tipo_segmento IS 'Tipo: hacia_destino, en_destino, retorno, entre_paradas, otro';
COMMENT ON COLUMN tracking.segmentos_ruta.polyline_encoded IS 'Polyline codificada en formato Google para visualizacion en mapas';

-- =============================================================================
-- FIN DDL GPS DEVICES
-- =============================================================================