erp-transportistas-database-v2/ddl/10-offline-schema-ddl.sql

-- =============================================================================
-- ERP TRANSPORTISTAS - Schema Offline DDL
-- =============================================================================
-- Archivo: 10-offline-schema-ddl.sql
-- Version: 1.0.0
-- Fecha: 2026-01-28
-- Descripcion: Cola de operaciones offline para sincronizacion
-- Basado en: OfflineQueue.entity.ts (TASK-007)
-- =============================================================================

-- =============================================================================
-- SCHEMA OFFLINE
-- =============================================================================

CREATE SCHEMA IF NOT EXISTS offline;
COMMENT ON SCHEMA offline IS 'Sistema de sincronizacion offline para operaciones en campo';

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

-- Tipos de operacion que pueden ser encoladas offline
CREATE TYPE offline.tipo_operacion_offline AS ENUM (
  -- GPS Operations
  'GPS_POSICION',
  'GPS_EVENTO',

  -- Dispatch Operations
  'VIAJE_ESTADO',
  'VIAJE_EVENTO',
  'CHECKIN',
  'CHECKOUT',

  -- POD Operations
  'POD_FOTO',
  'POD_FIRMA',
  'POD_DOCUMENTO',

  -- Checklist Operations
  'CHECKLIST_ITEM',
  'CHECKLIST_COMPLETADO',

  -- Generic
  'CUSTOM'
);

COMMENT ON TYPE offline.tipo_operacion_offline IS 'Tipos de operaciones que pueden encolarse offline';

-- Estados de sincronizacion para operaciones encoladas
CREATE TYPE offline.estado_sincronizacion AS ENUM (
  'PENDIENTE',    -- Esperando sincronizacion
  'EN_PROCESO',   -- Sincronizacion en progreso
  'COMPLETADO',   -- Sincronizado exitosamente
  'ERROR',        -- Error durante sincronizacion
  'CONFLICTO',    -- Conflicto de datos detectado
  'DESCARTADO'    -- Operacion descartada
);

COMMENT ON TYPE offline.estado_sincronizacion IS 'Estados del proceso de sincronizacion';

-- Niveles de prioridad para cola de sincronizacion
CREATE TYPE offline.prioridad_sync AS ENUM (
  '1',  -- CRITICA: Posiciones GPS, eventos de seguridad
  '2',  -- ALTA: POD, cambios de estado
  '3',  -- NORMAL: Items de checklist, notas
  '4'   -- BAJA: Fotos, documentos
);

COMMENT ON TYPE offline.prioridad_sync IS 'Prioridades de sincronizacion (1=critica, 4=baja)';

-- =============================================================================
-- TABLA: offline_queue
-- =============================================================================

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

  -- Referencias opcionales
  dispositivo_id UUID,                -- Dispositivo que genero la operacion
  usuario_id UUID,                    -- Usuario que genero la operacion
  unidad_id UUID,                     -- Unidad relacionada
  viaje_id UUID,                      -- Viaje relacionado

  -- Detalles de la operacion
  tipo_operacion offline.tipo_operacion_offline NOT NULL,
  estado offline.estado_sincronizacion DEFAULT 'PENDIENTE',
  prioridad offline.prioridad_sync DEFAULT '3',

  -- Payload - datos a sincronizar (JSONB)
  payload JSONB NOT NULL,

  -- Metadata de la operacion
  endpoint_destino VARCHAR(255) NOT NULL,      -- URL/endpoint destino
  metodo_http VARCHAR(10) DEFAULT 'POST',      -- GET, POST, PUT, PATCH, DELETE

  -- Timestamps offline
  creado_offline_en TIMESTAMPTZ NOT NULL,      -- Timestamp cuando se creo offline
  cliente_id VARCHAR(100),                     -- UUID generado en cliente para deduplicacion

  -- Tracking de sincronizacion
  intentos_sync INT DEFAULT 0,                 -- Contador de intentos
  max_intentos INT DEFAULT 5,                  -- Maximo de intentos permitidos
  ultimo_intento_en TIMESTAMPTZ,               -- Timestamp del ultimo intento
  sincronizado_en TIMESTAMPTZ,                 -- Timestamp cuando se sincronizo exitosamente

  -- Manejo de errores
  ultimo_error TEXT,                           -- Ultimo mensaje de error
  historial_errores JSONB DEFAULT '[]',        -- Array de {timestamp, error}

  -- Resolucion de conflictos
  version_servidor INT,                        -- Version del servidor para conflictos
  resolucion_conflicto VARCHAR(50),            -- 'CLIENT_WINS', 'SERVER_WINS', 'MERGE', 'MANUAL'

  -- Optimizacion de ancho de banda
  tamano_bytes INT,                            -- Tamano del payload en bytes
  comprimido BOOLEAN DEFAULT FALSE,            -- Si el payload esta comprimido

  -- Auditoria
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================================================
-- INDICES
-- =============================================================================

-- Indice principal por tenant
CREATE INDEX idx_offline_queue_tenant
  ON offline.offline_queue(tenant_id);

-- Indice por dispositivo para sincronizacion
CREATE INDEX idx_offline_queue_dispositivo
  ON offline.offline_queue(dispositivo_id)
  WHERE dispositivo_id IS NOT NULL;

-- Indice por estado para procesamiento de cola
CREATE INDEX idx_offline_queue_estado
  ON offline.offline_queue(estado);

-- Indice por prioridad y timestamp para ordenamiento de cola FIFO con prioridad
CREATE INDEX idx_offline_queue_prioridad
  ON offline.offline_queue(prioridad, creado_offline_en);

-- Indice compuesto para operaciones pendientes por tenant
CREATE INDEX idx_offline_queue_pendientes
  ON offline.offline_queue(tenant_id, estado, prioridad)
  WHERE estado IN ('PENDIENTE', 'EN_PROCESO', 'ERROR');

-- Indice para buscar por cliente_id (deduplicacion)
CREATE UNIQUE INDEX idx_offline_queue_cliente_id
  ON offline.offline_queue(tenant_id, cliente_id)
  WHERE cliente_id IS NOT NULL;

-- Indice por viaje para consultas relacionadas
CREATE INDEX idx_offline_queue_viaje
  ON offline.offline_queue(viaje_id)
  WHERE viaje_id IS NOT NULL;

-- Indice por unidad para consultas relacionadas
CREATE INDEX idx_offline_queue_unidad
  ON offline.offline_queue(unidad_id)
  WHERE unidad_id IS NOT NULL;

-- Indice para limpieza de registros antiguos sincronizados
CREATE INDEX idx_offline_queue_sincronizado
  ON offline.offline_queue(sincronizado_en)
  WHERE estado = 'COMPLETADO';

-- =============================================================================
-- TRIGGER: Actualizar updated_at automaticamente
-- =============================================================================

CREATE OR REPLACE FUNCTION offline.update_offline_queue_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_offline_queue_updated_at
  BEFORE UPDATE ON offline.offline_queue
  FOR EACH ROW
  EXECUTE FUNCTION offline.update_offline_queue_timestamp();

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

ALTER TABLE offline.offline_queue ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_offline_queue ON offline.offline_queue
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

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

COMMENT ON TABLE offline.offline_queue IS 'Cola de operaciones pendientes de sincronizacion desde dispositivos offline';

COMMENT ON COLUMN offline.offline_queue.tipo_operacion IS 'Tipo de operacion: GPS, POD, Checklist, etc.';
COMMENT ON COLUMN offline.offline_queue.estado IS 'Estado actual de la sincronizacion';
COMMENT ON COLUMN offline.offline_queue.prioridad IS 'Prioridad de sincronizacion (1=critica, 4=baja)';
COMMENT ON COLUMN offline.offline_queue.payload IS 'Datos de la operacion en formato JSON';
COMMENT ON COLUMN offline.offline_queue.endpoint_destino IS 'URL o endpoint API destino para la sincronizacion';
COMMENT ON COLUMN offline.offline_queue.creado_offline_en IS 'Timestamp original cuando la operacion fue creada offline';
COMMENT ON COLUMN offline.offline_queue.cliente_id IS 'UUID generado en el cliente para deduplicacion de operaciones';
COMMENT ON COLUMN offline.offline_queue.intentos_sync IS 'Numero de intentos de sincronizacion realizados';
COMMENT ON COLUMN offline.offline_queue.historial_errores IS 'Historial de errores como array JSON [{timestamp, error}]';
COMMENT ON COLUMN offline.offline_queue.resolucion_conflicto IS 'Estrategia de resolucion: CLIENT_WINS, SERVER_WINS, MERGE, MANUAL';
COMMENT ON COLUMN offline.offline_queue.comprimido IS 'Indica si el payload esta comprimido (gzip)';

-- =============================================================================
-- FUNCIONES AUXILIARES
-- =============================================================================

-- Funcion para obtener operaciones pendientes ordenadas por prioridad
CREATE OR REPLACE FUNCTION offline.get_pending_operations(
  p_tenant_id UUID,
  p_limit INT DEFAULT 100
)
RETURNS TABLE (
  id UUID,
  tipo_operacion offline.tipo_operacion_offline,
  prioridad offline.prioridad_sync,
  payload JSONB,
  endpoint_destino VARCHAR(255),
  metodo_http VARCHAR(10),
  creado_offline_en TIMESTAMPTZ,
  intentos_sync INT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    oq.id,
    oq.tipo_operacion,
    oq.prioridad,
    oq.payload,
    oq.endpoint_destino,
    oq.metodo_http,
    oq.creado_offline_en,
    oq.intentos_sync
  FROM offline.offline_queue oq
  WHERE oq.tenant_id = p_tenant_id
    AND oq.estado IN ('PENDIENTE', 'ERROR')
    AND oq.intentos_sync < oq.max_intentos
  ORDER BY oq.prioridad ASC, oq.creado_offline_en ASC
  LIMIT p_limit;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Funcion para marcar operacion como en proceso
CREATE OR REPLACE FUNCTION offline.start_sync_operation(
  p_operation_id UUID
)
RETURNS BOOLEAN AS $$
DECLARE
  v_updated INT;
BEGIN
  UPDATE offline.offline_queue
  SET
    estado = 'EN_PROCESO',
    ultimo_intento_en = NOW(),
    intentos_sync = intentos_sync + 1
  WHERE id = p_operation_id
    AND estado IN ('PENDIENTE', 'ERROR');

  GET DIAGNOSTICS v_updated = ROW_COUNT;
  RETURN v_updated > 0;
END;
$$ LANGUAGE plpgsql;

-- Funcion para completar operacion exitosamente
CREATE OR REPLACE FUNCTION offline.complete_sync_operation(
  p_operation_id UUID
)
RETURNS BOOLEAN AS $$
DECLARE
  v_updated INT;
BEGIN
  UPDATE offline.offline_queue
  SET
    estado = 'COMPLETADO',
    sincronizado_en = NOW()
  WHERE id = p_operation_id
    AND estado = 'EN_PROCESO';

  GET DIAGNOSTICS v_updated = ROW_COUNT;
  RETURN v_updated > 0;
END;
$$ LANGUAGE plpgsql;

-- Funcion para registrar error en operacion
CREATE OR REPLACE FUNCTION offline.fail_sync_operation(
  p_operation_id UUID,
  p_error_message TEXT
)
RETURNS BOOLEAN AS $$
DECLARE
  v_updated INT;
  v_historial JSONB;
BEGIN
  -- Obtener historial actual
  SELECT historial_errores INTO v_historial
  FROM offline.offline_queue
  WHERE id = p_operation_id;

  -- Agregar nuevo error al historial
  v_historial = v_historial || jsonb_build_object(
    'timestamp', NOW()::TEXT,
    'error', p_error_message
  );

  UPDATE offline.offline_queue
  SET
    estado = 'ERROR',
    ultimo_error = p_error_message,
    historial_errores = v_historial
  WHERE id = p_operation_id
    AND estado = 'EN_PROCESO';

  GET DIAGNOSTICS v_updated = ROW_COUNT;
  RETURN v_updated > 0;
END;
$$ LANGUAGE plpgsql;

-- Funcion para limpiar operaciones antiguas completadas
CREATE OR REPLACE FUNCTION offline.cleanup_old_operations(
  p_days_to_keep INT DEFAULT 30
)
RETURNS INT AS $$
DECLARE
  v_deleted INT;
BEGIN
  DELETE FROM offline.offline_queue
  WHERE estado = 'COMPLETADO'
    AND sincronizado_en < NOW() - (p_days_to_keep || ' days')::INTERVAL;

  GET DIAGNOSTICS v_deleted = ROW_COUNT;
  RETURN v_deleted;
END;
$$ LANGUAGE plpgsql;

-- =============================================================================
-- GRANTS
-- =============================================================================

-- Permisos para funciones
GRANT EXECUTE ON FUNCTION offline.get_pending_operations(UUID, INT) TO PUBLIC;
GRANT EXECUTE ON FUNCTION offline.start_sync_operation(UUID) TO PUBLIC;
GRANT EXECUTE ON FUNCTION offline.complete_sync_operation(UUID) TO PUBLIC;
GRANT EXECUTE ON FUNCTION offline.fail_sync_operation(UUID, TEXT) TO PUBLIC;
GRANT EXECUTE ON FUNCTION offline.cleanup_old_operations(INT) TO PUBLIC;

-- =============================================================================
-- FIN DDL OFFLINE
-- =============================================================================