@prefix ex: <https://kg.earthmetabolome.org/metrin/api/.well-known/examples/> .
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .
@prefix schema: <https://schema.org/> .
@prefix sh: <http://www.w3.org/ns/shacl#> .
@prefix spex:<https://purl.expasy.org/sparql-examples/ontology#> .

ex:006 a sh:SPARQLExecutable,
        sh:SPARQLSelectExecutable ;
    rdfs:comment "Search spectrally related features with and within the VGF151_E05 sample of a datura stramonium through counts of common peaks and loss. Return their species, family and genus names"@en ;
    sh:prefixes _:sparql_examples_prefixes ;
    sh:select """PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX emi: <https://w3id.org/emi#>
PREFIX sosa: <http://www.w3.org/ns/sosa/>

SELECT ?family_name ?genus_name ?species_name ?wd_sp ?feature ?sub_rt ?sub_parent_mass ?count
WHERE {
    # Subquery to get the top N hits with their Wikidata IDs
    {
        SELECT ?wd_sp ?feature (SAMPLE(?rt) AS ?sub_rt) (SAMPLE(?parent_mass) AS ?sub_parent_mass) (COUNT(?peakloss) AS ?count)
        WHERE {
            ?material sosa:hasSample ?extract ;
                sosa:isSampleOf* ?organism.
            ?organism emi:inTaxon ?wd_sp .
            ?extract a emi:ExtractSample ;
                sosa:isFeatureOfInterestOf ?lcms .
            ?lcms a emi:LCMSAnalysisPos ;
                sosa:hasResult ?feature_list .
            ?feature_list emi:hasLCMSFeature ?feature .
            ?feature a emi:LCMSFeature ;
                emi:hasSpec2VecDoc ?doc ;
                emi:hasParentMass ?parent_mass ;
                emi:hasRetentionTime ?rt .
            ?doc emi:hasSpec2VecLoss|emi:hasSpec2VecPeak ?peakloss .

            {
                SELECT ?peakloss WHERE {
                    ?lcms a emi:LCMSAnalysisPos ;
                        sosa:hasResult ?feature_list ;
                        sosa:hasFeatureOfInterest ?sample .
                    ?sample dcterms:identifier "VGF151_E05" ;
                        sosa:isSampleOf* ?organism .
                    ?organism emi:inTaxon/rdfs:label "datura stramonium".
                    ?feature_list emi:hasLCMSFeature ?feature .
                    ?feature a emi:LCMSFeature ;
                        emi:hasSpec2VecDoc ?doc .
                    ?doc emi:hasSpec2VecLoss|emi:hasSpec2VecPeak ?peakloss .
                }
            }
        }
        GROUP BY ?wd_sp ?feature
        ORDER BY DESC(?count)
        LIMIT 5
    }

    # Outer query to fetch species names from Wikidata
    # Fetching taxonomic information from Wikidata
    SERVICE <https://query.wikidata.org/sparql> {
        ?wd_sp wdt:P225 ?species_name .
        OPTIONAL {
            ?family wdt:P31 wd:Q16521 ;
                wdt:P105 wd:Q35409 ;
                wdt:P225 ?family_name ;
                ^wdt:P171* ?wd_sp .
        }
        OPTIONAL {
            ?genus wdt:P31 wd:Q16521 ;
                wdt:P105 wd:Q34740 ;
                wdt:P225 ?genus_name ;
                ^wdt:P171* ?wd_sp .
        }
    }
} GROUP BY ?family_name ?genus_name ?species_name ?wd_sp ?feature ?sub_rt ?sub_parent_mass ?count
ORDER BY DESC(?count)""" ;
    schema:target <https://kg.earthmetabolome.org/metrin/api/> ;
    spex:federatesWith <https://query.wikidata.org/sparql> .