-- =====================================================
-- SISTEMA DE TRAZABILIDAD - VINCULACIONES EXTERNAS
-- =====================================================
-- Este esquema permite vincular registros de sistemas externos
-- (Ingresos, Carretas, Salidas, Rechazos) con los lotes del sistema.
-- Los registros externos se consultan via Metabase API (solo lectura).

-- =====================================================
-- TABLA: vinculaciones_externas
-- =====================================================
-- Relaciona registros de tablas externas con lotes locales.
-- Cardinalidad: N registros → 1 lote

CREATE TABLE IF NOT EXISTS vinculaciones_externas (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- Identificación del registro externo
  tipo_registro     TEXT NOT NULL,                    -- 'ingreso', 'carreta', 'salida', 'rechazo'
  registro_id       BIGINT NOT NULL,                  -- ID del registro en la tabla externa

  -- Vinculación al lote local
  lote_id           UUID NOT NULL REFERENCES lotes(id) ON DELETE CASCADE,

  -- Metadatos de la vinculación
  fecha_vinculacion TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  usuario_id        TEXT,                             -- Usuario de Authentik que hizo la vinculación
  observaciones     TEXT,

  -- Cache de datos del registro externo (para evitar consultas repetidas)
  datos_cache       JSONB,                            -- Snapshot del registro al momento de vincular

  -- Periodo de cosecha (para filtros y unicidad)
  periodo_cosecha   TEXT NOT NULL DEFAULT '25-26',    -- '25-26' para cosecha 2025-2026

  -- Un registro externo solo puede vincularse una vez por periodo
  CONSTRAINT vinculaciones_registro_unico UNIQUE (tipo_registro, registro_id, periodo_cosecha),

  CONSTRAINT vinculaciones_tipo_valido CHECK (tipo_registro IN (
    'ingreso',
    'carreta',
    'salida',
    'rechazo'
  ))
);

-- Índices para consultas frecuentes
CREATE INDEX IF NOT EXISTS idx_vinculaciones_lote ON vinculaciones_externas(lote_id);
CREATE INDEX IF NOT EXISTS idx_vinculaciones_tipo ON vinculaciones_externas(tipo_registro);
CREATE INDEX IF NOT EXISTS idx_vinculaciones_periodo ON vinculaciones_externas(periodo_cosecha);
CREATE INDEX IF NOT EXISTS idx_vinculaciones_registro ON vinculaciones_externas(tipo_registro, registro_id);
CREATE INDEX IF NOT EXISTS idx_vinculaciones_fecha ON vinculaciones_externas(fecha_vinculacion DESC);

-- Comentarios
COMMENT ON TABLE vinculaciones_externas IS 'Vincula registros de sistemas externos (Ingresos, Carretas, Salidas, Rechazos) con lotes locales';
COMMENT ON COLUMN vinculaciones_externas.tipo_registro IS 'Tipo de registro externo: ingreso, carreta, salida, rechazo';
COMMENT ON COLUMN vinculaciones_externas.registro_id IS 'ID del registro en la tabla externa (Metabase)';
COMMENT ON COLUMN vinculaciones_externas.lote_id IS 'FK al lote local al que se vincula';
COMMENT ON COLUMN vinculaciones_externas.datos_cache IS 'Snapshot JSONB del registro al momento de vincular (para evitar consultas repetidas)';
COMMENT ON COLUMN vinculaciones_externas.periodo_cosecha IS 'Periodo de cosecha en formato YY-YY (ej: 25-26)';


-- =====================================================
-- VISTA: vista_lotes_con_vinculaciones
-- =====================================================
-- Muestra cada lote con el conteo de vinculaciones por tipo.

CREATE OR REPLACE VIEW vista_lotes_con_vinculaciones AS
SELECT
  l.id,
  l.codigo,
  l.tipo,
  l.fecha_creado,
  l.cantidad_kg,
  l.meta,
  l.lugar_id,
  COUNT(*) FILTER (WHERE v.tipo_registro = 'ingreso') AS ingresos_vinculados,
  COUNT(*) FILTER (WHERE v.tipo_registro = 'carreta') AS carretas_vinculadas,
  COUNT(*) FILTER (WHERE v.tipo_registro = 'salida') AS salidas_vinculadas,
  COUNT(*) FILTER (WHERE v.tipo_registro = 'rechazo') AS rechazos_vinculados,
  COUNT(v.id) AS total_vinculaciones
FROM lotes l
LEFT JOIN vinculaciones_externas v ON v.lote_id = l.id
GROUP BY l.id, l.codigo, l.tipo, l.fecha_creado, l.cantidad_kg, l.meta, l.lugar_id;

COMMENT ON VIEW vista_lotes_con_vinculaciones IS 'Muestra lotes con conteo de registros vinculados por tipo';


-- =====================================================
-- FUNCIÓN: get_estadisticas_vinculacion
-- =====================================================
-- Obtiene estadísticas de vinculación para un periodo dado.

CREATE OR REPLACE FUNCTION get_estadisticas_vinculacion(p_periodo TEXT DEFAULT '25-26')
RETURNS TABLE (
  tipo_registro   TEXT,
  total           BIGINT,
  vinculados      BIGINT,
  sin_vincular    BIGINT,
  porcentaje      NUMERIC
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    v.tipo_registro,
    COUNT(*)::BIGINT AS total,
    COUNT(*)::BIGINT AS vinculados,
    0::BIGINT AS sin_vincular,  -- Se calculará con datos de Metabase
    100.0 AS porcentaje
  FROM vinculaciones_externas v
  WHERE v.periodo_cosecha = p_periodo
  GROUP BY v.tipo_registro
  ORDER BY v.tipo_registro;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_estadisticas_vinculacion IS 'Retorna estadísticas de vinculación por tipo de registro para un periodo';


-- =====================================================
-- MENSAJES DE ÉXITO
-- =====================================================
DO $$
BEGIN
  RAISE NOTICE '✓ Esquema de vinculaciones externas creado exitosamente';
  RAISE NOTICE '  - Tabla vinculaciones_externas creada';
  RAISE NOTICE '  - Vista vista_lotes_con_vinculaciones creada';
  RAISE NOTICE '  - Función get_estadisticas_vinculacion() creada';
END $$;