package whelk.export.servlet;

import whelk.util.LegacyIntegrationTools;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.stream.XMLOutputFactory;
import javax.xml.stream.XMLStreamException;
import javax.xml.stream.XMLStreamWriter;
import java.io.IOException;
import java.sql.*;

import io.prometheus.client.Counter;

public class ListSets
{
    private final static String RESUMPTION_PARAM = "resumptionToken";

    private static final Counter failedRequests = Counter.build()
            .name("oaipmh_failed_listsets_requests_total").help("Total failed ListSets requests.")
            .labelNames("error").register();

    /**
     * Verifies the integrity of a OAI-PMH request with the verb 'ListSets' and sends a proper response.
     */
    public static void handleListSetsRequest(HttpServletRequest request, HttpServletResponse response)
            throws IOException, XMLStreamException, SQLException
    {
        // Parse and verify the parameters allowed for this request
        String resumptionToken = request.getParameter(RESUMPTION_PARAM); // exclusive, not supported/used

        if (ResponseCommon.errorOnExtraParameters(request, response, RESUMPTION_PARAM))
            return;

        // We do not use resumption tokens.
        if (resumptionToken != null)
        {
            failedRequests.labels(OaiPmh.OAIPMH_ERROR_BAD_RESUMPTION_TOKEN).inc();
            ResponseCommon.sendOaiPmhError(OaiPmh.OAIPMH_ERROR_BAD_RESUMPTION_TOKEN,
                    "No such resumption token was issued", request, response);
            return;
        }

        // Build the xml response feed
        XMLOutputFactory xmlOutputFactory = XMLOutputFactory.newInstance();
        xmlOutputFactory.setProperty("escapeCharacters", false); // Inline xml must be left untouched.
        XMLStreamWriter writer = xmlOutputFactory.createXMLStreamWriter(response.getOutputStream(), "UTF-8");

        ResponseCommon.writeOaiPmhHeader(writer, request, true);
        writer.writeStartElement("ListSets");

        // Static sets
        writer.writeStartElement("set");
        writer.writeStartElement("setSpec");
        writer.writeCharacters("auth");
        writer.writeEndElement(); // setSpec
        writer.writeStartElement("setName");
        writer.writeCharacters("Authority records");
        writer.writeEndElement(); // setName
        writer.writeEndElement(); // set

        writer.writeStartElement("set");
        writer.writeStartElement("setSpec");
        writer.writeCharacters("bib");
        writer.writeEndElement(); // setSpec
        writer.writeStartElement("setName");
        writer.writeCharacters("Bibliographic records");
        writer.writeEndElement(); // setName
        writer.writeEndElement(); // set

        writer.writeStartElement("set");
        writer.writeStartElement("setSpec");
        writer.writeCharacters("hold");
        writer.writeEndElement(); // setSpec
        writer.writeStartElement("setName");
        writer.writeCharacters("Holding records");
        writer.writeEndElement(); // setName
        writer.writeEndElement(); // set

        // Dynamic sigel-sets
        try (Connection dbconn = OaiPmh.s_postgreSqlComponent.getConnection();
             PreparedStatement preparedStatement = prepareStatement(dbconn);
             ResultSet resultSet = preparedStatement.executeQuery())
        {
            while (resultSet.next())
            {
                String sigel = resultSet.getString("sigel");
                if (sigel == null)
                    continue;
                sigel = LegacyIntegrationTools.uriToLegacySigel(sigel);
                if (sigel == null)
                    continue;

                writer.writeStartElement("set");
                writer.writeStartElement("setSpec");
                writer.writeCharacters("hold:"+sigel.replace("\"", ""));
                writer.writeEndElement(); // setSpec
                writer.writeStartElement("setName");
                writer.writeCharacters("Holding records for sigel: " + sigel);
                writer.writeEndElement(); // setName
                writer.writeEndElement(); // set

                writer.writeStartElement("set");
                writer.writeStartElement("setSpec");
                writer.writeCharacters("bib:"+sigel.replace("\"", ""));
                writer.writeEndElement(); // setSpec
                writer.writeStartElement("setName");
                writer.writeCharacters("Bibliographic records for sigel: " + sigel);
                writer.writeEndElement(); // setName
                writer.writeEndElement(); // set
            }
        }

        writer.writeEndElement(); // ListSets
        ResponseCommon.writeOaiPmhClose(writer, request);
    }

    private static PreparedStatement prepareStatement(Connection dbconn)
            throws SQLException
    {
        String tableName = OaiPmh.configuration.getProperty("sqlMaintable");

        // Construct the query
        String selectSQL = "SELECT DISTINCT data#>>'{@graph,1,heldBy,@id}' AS sigel FROM " + tableName +
                " WHERE collection = 'hold' ";
        PreparedStatement preparedStatement = dbconn.prepareStatement(selectSQL);
        preparedStatement.setFetchSize(512);

        return preparedStatement;
    }
}

@@ -5026,24 +5026,25 @@
 e query%0A
+%0A
         
 String s
@@ -5039,151 +5039,876 @@
     
-String selectSQL = %22SELECT DISTINCT data#%3E%3E'%7B@graph,1,heldBy,@id%7D' AS sigel FROM %22 + tableName +%0A                %22 WHERE collection = 'hold' %22;
+// The SELECT DISTINCT is semantically correct, but too slow because postgresql does not handle%0A        // loose indexscans, which can instead be simulated using the below query, courtesy of:%0A        // https://wiki.postgresql.org/wiki/Loose_indexscan%0A%0A        /*String selectSQL = %22SELECT DISTINCT data#%3E%3E'%7B@graph,1,heldBy,@id%7D' AS sigel FROM %22 + tableName +%0A                %22 WHERE collection = 'hold' %22;*/%0A%0A        String selectSQL = %22WITH RECURSIVE t AS (%5Cn%22 +%0A                %22   SELECT MIN(data#%3E%3E'%7B@graph,1,heldBy,@id%7D') AS col FROM lddb%5Cn%22 +%0A                %22   UNION ALL%5Cn%22 +%0A                %22   SELECT (SELECT MIN(data#%3E%3E'%7B@graph,1,heldBy,@id%7D') FROM lddb WHERE data#%3E%3E'%7B@graph,1,heldBy,@id%7D' %3E t.col)%5Cn%22 +%0A                %22   FROM t WHERE t.col IS NOT NULL%5Cn%22 +%0A                %22   )%5Cn%22 +%0A                %22SELECT col as sigel FROM t WHERE col IS NOT NULL%22;%0A
 %0A   

Use postgresql workaround for loose index scans, to get quick OAIPMH ListSets