erp-mecanicas-diesel-database-v2/init/14-gps-tracking-schema.sql

-- ===========================================
-- MECANICAS DIESEL - Schema gps_tracking
-- ===========================================
-- Integracion GPS, posiciones, geocercas, rutas
-- Modulo: MMD-014 GPS Integration

-- -------------------------------------------
-- SCHEMA CREATION
-- -------------------------------------------
CREATE SCHEMA IF NOT EXISTS gps_tracking;
COMMENT ON SCHEMA gps_tracking IS 'Tracking GPS, posiciones en tiempo real, geocercas y calculo de rutas';

GRANT USAGE ON SCHEMA gps_tracking TO mecanicas_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA gps_tracking TO mecanicas_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA gps_tracking GRANT ALL ON TABLES TO mecanicas_user;

SET search_path TO gps_tracking, public;

-- -------------------------------------------
-- GPS_DEVICES - Dispositivos GPS vinculados a unidades
-- -------------------------------------------
CREATE TABLE gps_tracking.gps_devices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Vinculacion a unidad de flota
    unit_id UUID NOT NULL,  -- FK a vehicle_management.vehicles
    unit_type VARCHAR(20) DEFAULT 'vehicle'
        CHECK (unit_type IN ('vehicle', 'trailer', 'equipment', 'technician')),

    -- Identificacion externa (plataforma GPS)
    external_device_id VARCHAR(100) NOT NULL,
    platform VARCHAR(30) NOT NULL DEFAULT 'traccar'
        CHECK (platform IN ('traccar', 'wialon', 'samsara', 'geotab', 'manual')),

    -- Identificadores del dispositivo
    imei VARCHAR(20),
    serial_number VARCHAR(50),
    phone_number VARCHAR(20),
    model VARCHAR(50),
    manufacturer VARCHAR(50),

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    last_position_at TIMESTAMP WITH TIME ZONE,
    last_position_lat DECIMAL(10, 7),
    last_position_lng DECIMAL(10, 7),

    -- Configuracion
    position_interval_seconds INTEGER DEFAULT 30 CHECK (position_interval_seconds >= 5),
    metadata JSONB DEFAULT '{}',

    -- Auditoria
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID,

    CONSTRAINT uq_device_external UNIQUE (tenant_id, platform, external_device_id),
    CONSTRAINT uq_device_unit UNIQUE (tenant_id, unit_id)
);

CREATE INDEX idx_gps_devices_tenant ON gps_tracking.gps_devices(tenant_id);
CREATE INDEX idx_gps_devices_unit ON gps_tracking.gps_devices(unit_id);
CREATE INDEX idx_gps_devices_external ON gps_tracking.gps_devices(external_device_id);
CREATE INDEX idx_gps_devices_platform ON gps_tracking.gps_devices(platform);
CREATE INDEX idx_gps_devices_active ON gps_tracking.gps_devices(is_active) WHERE is_active = TRUE;

CREATE TRIGGER trg_gps_devices_updated_at
    BEFORE UPDATE ON gps_tracking.gps_devices
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

SELECT create_tenant_rls_policies('gps_tracking', 'gps_devices');

COMMENT ON TABLE gps_tracking.gps_devices IS 'Dispositivos GPS vinculados a unidades de la flota';
COMMENT ON COLUMN gps_tracking.gps_devices.external_device_id IS 'ID del dispositivo en la plataforma GPS externa (Traccar, etc.)';
COMMENT ON COLUMN gps_tracking.gps_devices.platform IS 'Plataforma GPS: traccar (recomendado), wialon, samsara, geotab, manual';

-- -------------------------------------------
-- GPS_POSITIONS - Posiciones registradas (time-series)
-- -------------------------------------------
CREATE TABLE gps_tracking.gps_positions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    device_id UUID NOT NULL REFERENCES gps_tracking.gps_devices(id) ON DELETE CASCADE,
    unit_id UUID NOT NULL,  -- Denormalizado para queries rapidos

    -- Posicion
    latitude DECIMAL(10, 7) NOT NULL CHECK (latitude >= -90 AND latitude <= 90),
    longitude DECIMAL(10, 7) NOT NULL CHECK (longitude >= -180 AND longitude <= 180),
    altitude DECIMAL(8, 2),  -- metros sobre nivel del mar

    -- Movimiento
    speed DECIMAL(6, 2) CHECK (speed >= 0),  -- km/h
    course DECIMAL(5, 2) CHECK (course >= 0 AND course < 360),  -- grados (0 = norte)

    -- Precision
    accuracy DECIMAL(6, 2) CHECK (accuracy >= 0),  -- metros
    hdop DECIMAL(4, 2),  -- Horizontal Dilution of Precision

    -- Atributos adicionales (del dispositivo)
    attributes JSONB DEFAULT '{}',
    -- attributes puede contener: ignition, fuel, odometer, engineHours, batteryLevel, etc.

    -- Timestamps
    device_time TIMESTAMP WITH TIME ZONE NOT NULL,  -- Hora del dispositivo
    server_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),  -- Hora de recepcion
    fix_time TIMESTAMP WITH TIME ZONE,  -- Hora del fix GPS

    -- Validacion
    is_valid BOOLEAN DEFAULT TRUE,  -- Si paso filtros anti-ruido

    -- Indices para busquedas
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Particionar por fecha si hay muchos registros
-- CREATE TABLE gps_tracking.gps_positions_YYYY_MM PARTITION OF gps_tracking.gps_positions
-- FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE INDEX idx_gps_positions_tenant ON gps_tracking.gps_positions(tenant_id);
CREATE INDEX idx_gps_positions_device ON gps_tracking.gps_positions(device_id);
CREATE INDEX idx_gps_positions_unit ON gps_tracking.gps_positions(unit_id);
CREATE INDEX idx_gps_positions_device_time ON gps_tracking.gps_positions(device_id, device_time DESC);
CREATE INDEX idx_gps_positions_time ON gps_tracking.gps_positions(device_time DESC);
CREATE INDEX idx_gps_positions_valid ON gps_tracking.gps_positions(is_valid) WHERE is_valid = TRUE;

-- Indice espacial para queries geograficos
CREATE INDEX idx_gps_positions_location ON gps_tracking.gps_positions
    USING GIST (ST_SetSRID(ST_MakePoint(longitude, latitude), 4326))
    WHERE longitude IS NOT NULL AND latitude IS NOT NULL;

SELECT create_tenant_rls_policies('gps_tracking', 'gps_positions');

COMMENT ON TABLE gps_tracking.gps_positions IS 'Posiciones GPS registradas - datos time-series';
COMMENT ON COLUMN gps_tracking.gps_positions.attributes IS 'Atributos adicionales: ignition, fuel, odometer, engineHours, etc.';
COMMENT ON COLUMN gps_tracking.gps_positions.is_valid IS 'Si la posicion paso los filtros anti-ruido';

-- -------------------------------------------
-- GEOFENCES - Geocercas (areas de interes)
-- -------------------------------------------
CREATE TABLE gps_tracking.geofences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Identificacion
    name VARCHAR(100) NOT NULL,
    code VARCHAR(30),
    description TEXT,

    -- Tipo de geocerca
    geofence_type VARCHAR(20) NOT NULL DEFAULT 'circle'
        CHECK (geofence_type IN ('circle', 'polygon')),

    -- Para tipo CIRCLE
    center_lat DECIMAL(10, 7),
    center_lng DECIMAL(10, 7),
    radius_meters DECIMAL(10, 2) CHECK (radius_meters > 0),

    -- Para tipo POLYGON (formato GeoJSON)
    polygon_geojson JSONB,
    -- Ejemplo: {"type": "Polygon", "coordinates": [[[lng1,lat1],[lng2,lat2],...]]}

    -- Geometria PostGIS (calculada)
    geometry GEOMETRY(Geometry, 4326),

    -- Categoria operativa
    category VARCHAR(30) NOT NULL DEFAULT 'custom'
        CHECK (category IN ('base', 'coverage', 'restricted', 'high_risk', 'client', 'custom')),

    -- Configuracion de eventos
    trigger_on_enter BOOLEAN DEFAULT TRUE,
    trigger_on_exit BOOLEAN DEFAULT TRUE,
    dwell_time_seconds INTEGER DEFAULT 0,  -- Tiempo minimo para disparar evento

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    color VARCHAR(7) DEFAULT '#3388ff',  -- Color en mapa (hex)
    metadata JSONB DEFAULT '{}',

    -- Auditoria
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID
);

CREATE INDEX idx_geofences_tenant ON gps_tracking.geofences(tenant_id);
CREATE INDEX idx_geofences_category ON gps_tracking.geofences(category);
CREATE INDEX idx_geofences_active ON gps_tracking.geofences(is_active) WHERE is_active = TRUE;

-- Indice espacial
CREATE INDEX idx_geofences_geometry ON gps_tracking.geofences USING GIST (geometry);

CREATE TRIGGER trg_geofences_updated_at
    BEFORE UPDATE ON gps_tracking.geofences
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

SELECT create_tenant_rls_policies('gps_tracking', 'geofences');

COMMENT ON TABLE gps_tracking.geofences IS 'Geocercas para detectar entrada/salida de areas';
COMMENT ON COLUMN gps_tracking.geofences.category IS 'Categoria: base (taller), coverage (zona atendida), restricted, high_risk, client, custom';
COMMENT ON COLUMN gps_tracking.geofences.dwell_time_seconds IS 'Segundos minimos dentro/fuera para disparar evento';

-- Trigger para calcular geometria automaticamente
CREATE OR REPLACE FUNCTION gps_tracking.calculate_geofence_geometry()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.geofence_type = 'circle' AND NEW.center_lat IS NOT NULL AND NEW.center_lng IS NOT NULL THEN
        -- Crear circulo
        NEW.geometry := ST_Buffer(
            ST_SetSRID(ST_MakePoint(NEW.center_lng, NEW.center_lat), 4326)::geography,
            NEW.radius_meters
        )::geometry;
    ELSIF NEW.geofence_type = 'polygon' AND NEW.polygon_geojson IS NOT NULL THEN
        -- Crear poligono desde GeoJSON
        NEW.geometry := ST_SetSRID(ST_GeomFromGeoJSON(NEW.polygon_geojson::text), 4326);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_geofences_geometry
    BEFORE INSERT OR UPDATE ON gps_tracking.geofences
    FOR EACH ROW EXECUTE FUNCTION gps_tracking.calculate_geofence_geometry();

-- -------------------------------------------
-- GEOFENCE_EVENTS - Eventos de entrada/salida
-- -------------------------------------------
CREATE TABLE gps_tracking.geofence_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    geofence_id UUID NOT NULL REFERENCES gps_tracking.geofences(id) ON DELETE CASCADE,
    device_id UUID NOT NULL REFERENCES gps_tracking.gps_devices(id) ON DELETE CASCADE,
    unit_id UUID NOT NULL,

    -- Tipo de evento
    event_type VARCHAR(10) NOT NULL CHECK (event_type IN ('enter', 'exit', 'dwell')),

    -- Posicion que disparo el evento
    position_id UUID REFERENCES gps_tracking.gps_positions(id),
    latitude DECIMAL(10, 7) NOT NULL,
    longitude DECIMAL(10, 7) NOT NULL,

    -- Timestamps
    event_time TIMESTAMP WITH TIME ZONE NOT NULL,
    processed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Vinculacion a operaciones
    related_incident_id UUID,  -- FK a rescue_order si aplica

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

CREATE INDEX idx_geofence_events_tenant ON gps_tracking.geofence_events(tenant_id);
CREATE INDEX idx_geofence_events_geofence ON gps_tracking.geofence_events(geofence_id);
CREATE INDEX idx_geofence_events_device ON gps_tracking.geofence_events(device_id);
CREATE INDEX idx_geofence_events_unit ON gps_tracking.geofence_events(unit_id);
CREATE INDEX idx_geofence_events_time ON gps_tracking.geofence_events(event_time DESC);
CREATE INDEX idx_geofence_events_incident ON gps_tracking.geofence_events(related_incident_id)
    WHERE related_incident_id IS NOT NULL;

SELECT create_tenant_rls_policies('gps_tracking', 'geofence_events');

COMMENT ON TABLE gps_tracking.geofence_events IS 'Eventos de entrada/salida de geocercas';

-- -------------------------------------------
-- ROUTE_SEGMENTS - Segmentos de ruta para km facturables
-- -------------------------------------------
CREATE TABLE gps_tracking.route_segments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Vinculacion a incidente/servicio
    incident_id UUID,  -- FK a rescue_order
    unit_id UUID NOT NULL,
    device_id UUID REFERENCES gps_tracking.gps_devices(id),

    -- Posiciones de inicio/fin
    start_position_id UUID REFERENCES gps_tracking.gps_positions(id),
    end_position_id UUID REFERENCES gps_tracking.gps_positions(id),

    -- Coordenadas (denormalizadas)
    start_lat DECIMAL(10, 7) NOT NULL,
    start_lng DECIMAL(10, 7) NOT NULL,
    end_lat DECIMAL(10, 7) NOT NULL,
    end_lng DECIMAL(10, 7) NOT NULL,

    -- Distancias
    distance_km DECIMAL(10, 3) NOT NULL CHECK (distance_km >= 0),
    raw_distance_km DECIMAL(10, 3),  -- Antes de filtros

    -- Tiempos
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    duration_minutes DECIMAL(8, 2) CHECK (duration_minutes >= 0),

    -- Tipo de segmento
    segment_type VARCHAR(30) NOT NULL DEFAULT 'other'
        CHECK (segment_type IN ('to_incident', 'at_incident', 'return', 'between_incidents', 'other')),

    -- Validacion
    is_valid BOOLEAN DEFAULT TRUE,
    validation_notes TEXT,

    -- Polyline codificada (para visualizacion)
    encoded_polyline TEXT,

    -- Metadata
    metadata JSONB DEFAULT '{}',

    -- Auditoria
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    calculated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT chk_segment_times CHECK (end_time >= start_time)
);

CREATE INDEX idx_route_segments_tenant ON gps_tracking.route_segments(tenant_id);
CREATE INDEX idx_route_segments_incident ON gps_tracking.route_segments(incident_id);
CREATE INDEX idx_route_segments_unit ON gps_tracking.route_segments(unit_id);
CREATE INDEX idx_route_segments_type ON gps_tracking.route_segments(segment_type);
CREATE INDEX idx_route_segments_time ON gps_tracking.route_segments(start_time DESC);
CREATE INDEX idx_route_segments_valid ON gps_tracking.route_segments(is_valid) WHERE is_valid = TRUE;

SELECT create_tenant_rls_policies('gps_tracking', 'route_segments');

COMMENT ON TABLE gps_tracking.route_segments IS 'Segmentos de ruta calculados para facturacion de km';
COMMENT ON COLUMN gps_tracking.route_segments.segment_type IS 'Tipo: to_incident (ida), at_incident (en sitio), return (regreso), between_incidents, other';
COMMENT ON COLUMN gps_tracking.route_segments.is_valid IS 'Si el segmento paso validacion anti-ruido';
COMMENT ON COLUMN gps_tracking.route_segments.encoded_polyline IS 'Polyline codificada (Google format) para visualizar en mapa';

-- -------------------------------------------
-- FUNCTIONS: Calculo de distancias
-- -------------------------------------------

-- Funcion para calcular distancia entre dos puntos (Haversine)
CREATE OR REPLACE FUNCTION gps_tracking.haversine_distance(
    lat1 DECIMAL, lng1 DECIMAL,
    lat2 DECIMAL, lng2 DECIMAL
)
RETURNS DECIMAL AS $$
DECLARE
    R DECIMAL := 6371;  -- Radio de la Tierra en km
    dlat DECIMAL;
    dlng DECIMAL;
    a DECIMAL;
    c DECIMAL;
BEGIN
    dlat := RADIANS(lat2 - lat1);
    dlng := RADIANS(lng2 - lng1);

    a := SIN(dlat/2) * SIN(dlat/2) +
         COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
         SIN(dlng/2) * SIN(dlng/2);

    c := 2 * ATAN2(SQRT(a), SQRT(1-a));

    RETURN R * c;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION gps_tracking.haversine_distance IS 'Calcula distancia en km entre dos puntos usando formula Haversine';

-- Funcion para calcular distancia total de una ruta (array de posiciones)
CREATE OR REPLACE FUNCTION gps_tracking.calculate_route_distance(
    p_device_id UUID,
    p_start_time TIMESTAMP WITH TIME ZONE,
    p_end_time TIMESTAMP WITH TIME ZONE
)
RETURNS TABLE (
    total_distance_km DECIMAL,
    position_count INTEGER,
    duration_minutes DECIMAL
) AS $$
DECLARE
    v_prev_lat DECIMAL;
    v_prev_lng DECIMAL;
    v_prev_time TIMESTAMP WITH TIME ZONE;
    v_distance DECIMAL := 0;
    v_count INTEGER := 0;
    v_rec RECORD;
BEGIN
    FOR v_rec IN
        SELECT latitude, longitude, device_time
        FROM gps_tracking.gps_positions
        WHERE device_id = p_device_id
          AND device_time BETWEEN p_start_time AND p_end_time
          AND is_valid = TRUE
        ORDER BY device_time
    LOOP
        IF v_prev_lat IS NOT NULL THEN
            v_distance := v_distance + gps_tracking.haversine_distance(
                v_prev_lat, v_prev_lng,
                v_rec.latitude, v_rec.longitude
            );
        ELSE
            v_prev_time := v_rec.device_time;
        END IF;

        v_prev_lat := v_rec.latitude;
        v_prev_lng := v_rec.longitude;
        v_count := v_count + 1;
    END LOOP;

    RETURN QUERY SELECT
        ROUND(v_distance, 3)::DECIMAL,
        v_count,
        ROUND(EXTRACT(EPOCH FROM (v_rec.device_time - v_prev_time)) / 60, 2)::DECIMAL;
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION gps_tracking.calculate_route_distance IS 'Calcula distancia total de una ruta entre dos timestamps';

-- Funcion para verificar si un punto esta dentro de una geocerca
CREATE OR REPLACE FUNCTION gps_tracking.is_point_in_geofence(
    p_lat DECIMAL,
    p_lng DECIMAL,
    p_geofence_id UUID
)
RETURNS BOOLEAN AS $$
DECLARE
    v_geofence gps_tracking.geofences;
BEGIN
    SELECT * INTO v_geofence
    FROM gps_tracking.geofences
    WHERE id = p_geofence_id AND is_active = TRUE;

    IF v_geofence IS NULL THEN
        RETURN FALSE;
    END IF;

    -- Usar PostGIS para verificacion precisa
    RETURN ST_Contains(
        v_geofence.geometry,
        ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)
    );
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION gps_tracking.is_point_in_geofence IS 'Verifica si un punto esta dentro de una geocerca';

-- -------------------------------------------
-- VIEWS
-- -------------------------------------------

-- Vista de ultima posicion por unidad
CREATE OR REPLACE VIEW gps_tracking.vw_last_positions AS
SELECT DISTINCT ON (d.unit_id)
    d.id as device_id,
    d.tenant_id,
    d.unit_id,
    d.external_device_id,
    d.platform,
    d.is_active,
    p.id as position_id,
    p.latitude,
    p.longitude,
    p.speed,
    p.course,
    p.attributes,
    p.device_time,
    p.is_valid,
    EXTRACT(EPOCH FROM (NOW() - p.device_time)) / 60 as minutes_ago
FROM gps_tracking.gps_devices d
LEFT JOIN gps_tracking.gps_positions p ON d.id = p.device_id
WHERE d.is_active = TRUE
  AND d.tenant_id = get_current_tenant_id()
ORDER BY d.unit_id, p.device_time DESC;

COMMENT ON VIEW gps_tracking.vw_last_positions IS 'Vista de ultima posicion conocida por unidad con filtro RLS';

-- Vista de resumen de dispositivos
CREATE OR REPLACE VIEW gps_tracking.vw_device_summary AS
SELECT
    d.id,
    d.tenant_id,
    d.unit_id,
    d.external_device_id,
    d.platform,
    d.model,
    d.is_active,
    d.last_position_at,
    d.last_position_lat,
    d.last_position_lng,
    CASE
        WHEN d.last_position_at IS NULL THEN 'never'
        WHEN NOW() - d.last_position_at < INTERVAL '5 minutes' THEN 'online'
        WHEN NOW() - d.last_position_at < INTERVAL '1 hour' THEN 'recent'
        ELSE 'offline'
    END as connection_status,
    (SELECT COUNT(*) FROM gps_tracking.gps_positions p
     WHERE p.device_id = d.id AND p.device_time > NOW() - INTERVAL '24 hours') as positions_24h
FROM gps_tracking.gps_devices d
WHERE d.tenant_id = get_current_tenant_id();

COMMENT ON VIEW gps_tracking.vw_device_summary IS 'Vista resumida de dispositivos GPS con estado de conexion';

-- -------------------------------------------
-- TRIGGER: Actualizar ultima posicion en device
-- -------------------------------------------
CREATE OR REPLACE FUNCTION gps_tracking.update_device_last_position()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE gps_tracking.gps_devices
    SET
        last_position_at = NEW.device_time,
        last_position_lat = NEW.latitude,
        last_position_lng = NEW.longitude
    WHERE id = NEW.device_id
      AND (last_position_at IS NULL OR last_position_at < NEW.device_time);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_device_last_position
    AFTER INSERT ON gps_tracking.gps_positions
    FOR EACH ROW EXECUTE FUNCTION gps_tracking.update_device_last_position();

-- -------------------------------------------
-- DATOS INICIALES: Geocercas base
-- -------------------------------------------
-- NOTA: Ejecutar despues de tener tenant_id valido
-- INSERT INTO gps_tracking.geofences (tenant_id, name, code, geofence_type, center_lat, center_lng, radius_meters, category)
-- VALUES
--   ('{tenant_id}', 'Taller Principal', 'BASE-01', 'circle', 19.4326, -99.1332, 200, 'base');

COMMENT ON SCHEMA gps_tracking IS 'Schema completo para tracking GPS - MMD-014';