-- Função para obtener clientes con documentos pendientes
-- Ejecutar en Supabase SQL Editor

CREATE OR REPLACE FUNCTION get_clientes_con_documentos_pendientes(p_asesor_id UUID DEFAULT NULL)
RETURNS TABLE (
  id UUID,
  razon_social TEXT,
  nombre_comercial TEXT,
  docs_pendientes BIGINT,
  docs_procesados_ia BIGINT,
  ultimo_documento TIMESTAMPTZ,
  ultimo_estado TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    c.id,
    c.razon_social,
    c.nombre_comercial,
    COUNT(d.id) FILTER (WHERE d.estado = 'pendiente') as docs_pendientes,
    COUNT(d.id) FILTER (WHERE d.estado = 'procesado_ia') as docs_procesados_ia,
    MAX(d.fecha_subida) as ultimo_documento,
    (
      SELECT d2.estado 
      FROM documentos d2 
      WHERE d2.cliente_id = c.id 
      AND d2.estado IN ('pendiente', 'procesado_ia')
      ORDER BY d2.fecha_subida DESC 
      LIMIT 1
    ) as ultimo_estado
  FROM clientes c
  LEFT JOIN documentos d ON d.cliente_id = c.id 
    AND d.estado IN ('pendiente', 'procesado_ia')
  WHERE 
    (p_asesor_id IS NULL OR c.asesor_id = p_asesor_id)
    AND EXISTS (
      SELECT 1 FROM documentos d3 
      WHERE d3.cliente_id = c.id 
      AND d3.estado IN ('pendiente', 'procesado_ia')
    )
  GROUP BY c.id, c.razon_social, c.nombre_comercial
  ORDER BY MAX(d.fecha_subida) DESC NULLS LAST;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Permisos
GRANT EXECUTE ON FUNCTION get_clientes_con_documentos_pendientes(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION get_clientes_con_documentos_pendientes(UUID) TO service_role;
