PREFIX dct:        <http://purl.org/dc/terms/>
PREFIX schema:     <https://schema.org/>

CONSTRUCT
{
    GRAPH ?graph
    {
        # the employee's title, exactly as employees.rq writes it (the CSV carries the last name for that
        # alone): the two imports run concurrently, and whichever reaches an employee's document first
        # creates it, which a document without a dct:title cannot be
        ?graph dct:title ?lastName .

        ?employee schema:areaServed ?territory
    }
}
WHERE
{
    ?employee_territory <#employeeID> ?employeeID ;
        <#lastName> ?lastName ;
        <#territoryID> ?territoryID .

    BIND(uri(concat(str($base), "employees/")) AS ?container)
    BIND(uri(concat(str(?container), encode_for_uri(?employeeID), "/")) AS ?graph)
    BIND(uri(concat(str(?graph), "#this")) AS ?employee)
    BIND(uri(concat(str($base), "territories/", encode_for_uri(?territoryID), "/#this")) AS ?territory)
}

PREFIX foaf:       <http://xmlns.com/foaf/0.1/>
PREFIX dct:        <http://purl.org/dc/terms/>
PREFIX schema:     <https://schema.org/>
PREFIX xsd:        <http://www.w3.org/2001/XMLSchema#>
PREFIX geo:        <http://www.w3.org/2003/01/geo/wgs84_pos#>

CONSTRUCT
{
    GRAPH ?graph
    {
        ?graph dct:title ?lastName ;
            foaf:primaryTopic ?employee ;
            foaf:topic ?postalAddress .

        ?employee a schema:Person ;
            schema:identifier ?employeeID ;
            dct:title ?lastName ;
            dct:description ?title ;
            schema:givenName ?firstName ;
            schema:familyName ?lastName ;
            schema:jobTitle ?title ;
            schema:honorificPrefix  ?titleOfCourtesy ;
            schema:birthDate ?birthDate;
            # ?hireDate
            schema:address ?postalAddress ;
            schema:telephone ?homePhone ;
            schema:telephone ?extension ;
            schema:description ?notes ;
            schema:sponsor ?reportsToEmployee ;
            geo:lat ?lat ;
            geo:long ?long ;
            foaf:img ?photo .

        ?postalAddress a schema:PostalAddress ;
            foaf:page ?graph ;
            schema:addressCountry ?country ;
            schema:addressLocality ?city ;
            schema:postalCode ?postalCode ;
            schema:streetAddress ?address ;
            schema:addressRegion ?region .
    }
}
WHERE
{
    ?employee_row <#employeeID> ?employeeID ;
        <#lastName> ?lastName ;
        <#firstName> ?firstName ;
        <#title> ?title ;
        <#titleOfCourtesy> ?titleOfCourtesy ;
        <#birthDate> ?birthDateString ;
        <#hireDate> ?hireDateString ;
        <#address> ?address ;
        <#city> ?city ;
        <#postalCode> ?postalCode ;
        <#country> ?country ;
        <#homePhone> ?homePhone ;
        <#extension> ?extension ;
        <#photoHash> ?photoHash ;
        <#notes> ?notes ;
        <#lat> ?lat_string ;
        <#long> ?long_string .

    OPTIONAL {
        ?employee_row <#region> ?region
    }
    OPTIONAL {
        ?employee_row <#reportsTo> ?reportsTo
        BIND(uri(concat(str($base), "employees/", encode_for_uri(?reportsTo), "/#this")) AS ?reportsToEmployee) # cannot use ?container since it's undefined in the OPTIONAL scope
    }

    BIND(uri(concat(str($base), "employees/")) AS ?container)
    BIND(uri(concat(str(?container), encode_for_uri(?employeeID), "/")) AS ?graph)
    BIND(uri(concat(str(?graph), "#this")) AS ?employee)
    BIND(uri(concat(str(?graph), "#address")) AS ?postalAddress)
    BIND(xsd:float(?lat_string) AS ?lat)
    BIND(xsd:float(?long_string) AS ?long)
    BIND(strdt(substr(?birthDateString, 1, 10), xsd:date) AS ?birthDate)
    BIND(strdt(substr(?hireDateString, 1, 10), xsd:date) AS ?hireDate)
    BIND(uri(concat(str($base), "uploads/", encode_for_uri(?photoHash))) AS ?photo)
}

PREFIX schema: <https://schema.org/>

SELECT ?employeeName (SUM(?sale) AS ?totalSales)
WHERE {
    GRAPH ?orderGraph {
        ?order schema:broker ?employee ;
               schema:orderedItem ?orderItem .
        ?orderItem schema:price ?sale .
    }
    GRAPH ?employeeGraph {
        ?employee schema:givenName ?givenName ;
                  schema:familyName ?familyName .
        BIND(CONCAT(?givenName, " ", ?familyName) AS ?employeeName)
    }
}
GROUP BY ?employee ?employeeName
ORDER BY DESC(?totalSales)

PREFIX  schema: <https://schema.org/>

  SELECT DISTINCT  ?employee
  WHERE
    { GRAPH ?doc
        { ?employee  a                 schema:Person ;
                    schema:familyName  ?lastName
          OPTIONAL
            { ?employee  schema:areaServed  ?territory }
          OPTIONAL
            { ?employee  schema:sponsor  ?reportsToEmployee }
          FILTER NOT EXISTS { GRAPH ?supplierGraph
                                { ?supplier  a               schema:Corporation ;
                                            schema:employee  ?employee
                                }
                            }
        }
    }
  ORDER BY ?lastName