# ============================================================================
# CRM Customer Management SPARQL Query Templates
# ============================================================================

# ----------------------------------------------------------------------------
# Query 1: Extract Sales Pipeline by Stage
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?stageName ?opportunityCount (SUM(?amount) AS ?totalValue)
       (SUM(?expectedRevenue) AS ?weightedValue) ?avgProbability
WHERE {
  ?opportunity a crm:Opportunity ;
               crm:stage ?stage ;
               crm:amount ?amount ;
               crm:probability ?probability ;
               crm:closeDate ?closeDate .

  ?stage rdfs:label ?stageName .

  BIND(?amount * (?probability / 100.0) AS ?expectedRevenue)

  FILTER(?closeDate >= "{{start_date}}"^^xsd:date && ?closeDate <= "{{end_date}}"^^xsd:date)
  FILTER(?stage NOT IN (crm:ClosedWon, crm:ClosedLost))
}
GROUP BY ?stageName
ORDER BY ?avgProbability DESC

# ----------------------------------------------------------------------------
# Query 2: Calculate Win Rates
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?salesRep
       (COUNT(?opportunity) AS ?totalOpportunities)
       (SUM(IF(?stage = crm:ClosedWon, 1, 0)) AS ?wonCount)
       (SUM(IF(?stage = crm:ClosedLost, 1, 0)) AS ?lostCount)
       (SUM(IF(?stage = crm:ClosedWon, 1, 0)) / COUNT(?opportunity) AS ?winRate)
       (SUM(IF(?stage = crm:ClosedWon, ?amount, 0)) AS ?wonRevenue)
WHERE {
  ?opportunity a crm:Opportunity ;
               crm:owner ?owner ;
               crm:stage ?stage ;
               crm:amount ?amount ;
               crm:closeDate ?closeDate .

  ?owner rdfs:label ?salesRep .

  FILTER(?closeDate >= "{{start_date}}"^^xsd:date && ?closeDate <= "{{end_date}}"^^xsd:date)
  FILTER(?stage IN (crm:ClosedWon, crm:ClosedLost))
}
GROUP BY ?salesRep
ORDER BY ?winRate DESC

# ----------------------------------------------------------------------------
# Query 3: Generate Sales Forecast
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?month ?stageName (SUM(?expectedRevenue) AS ?forecastedRevenue)
WHERE {
  ?opportunity a crm:Opportunity ;
               crm:stage ?stage ;
               crm:amount ?amount ;
               crm:probability ?probability ;
               crm:closeDate ?closeDate .

  ?stage rdfs:label ?stageName .

  BIND(?amount * (?probability / 100.0) AS ?expectedRevenue)
  BIND(CONCAT(STR(YEAR(?closeDate)), "-",
              IF(MONTH(?closeDate) < 10, CONCAT("0", STR(MONTH(?closeDate))), STR(MONTH(?closeDate))))
       AS ?month)

  FILTER(?closeDate >= "{{start_date}}"^^xsd:date && ?closeDate <= "{{end_date}}"^^xsd:date)
  FILTER(?stage NOT IN (crm:ClosedWon, crm:ClosedLost))
}
GROUP BY ?month ?stageName
ORDER BY ?month, ?stageName

# ----------------------------------------------------------------------------
# Query 4: Lead Scoring and Qualification
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?leadName ?company ?leadSourceName ?scoreValue ?statusName
WHERE {
  ?lead a crm:Lead ;
        crm:leadName ?leadName ;
        crm:company ?company ;
        crm:leadSource ?source ;
        crm:leadScore ?score ;
        crm:leadStatus ?status .

  ?source rdfs:label ?leadSourceName .
  ?score crm:scoreValue ?scoreValue .
  ?status rdfs:label ?statusName .

  FILTER(?scoreValue >= {{min_score}})
  FILTER(?status != crm:Converted && ?status != crm:Disqualified)
}
ORDER BY ?scoreValue DESC

# ----------------------------------------------------------------------------
# Query 5: Activity Timeline for Account
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?activityDate ?activityType ?subject ?salesRep ?completed
WHERE {
  ?activity a ?type ;
            crm:activityDate ?activityDate ;
            crm:subject ?subject ;
            crm:relatedTo ?account ;
            crm:assignedTo ?rep ;
            crm:completed ?completed .

  ?rep rdfs:label ?salesRep .

  BIND(
    IF(?type = crm:Call, "Call",
    IF(?type = crm:Email, "Email",
    IF(?type = crm:Meeting, "Meeting",
    IF(?type = crm:Task, "Task", "Other"))))
    AS ?activityType
  )

  FILTER(?account = <{{account_uri}}>)
  FILTER(?activityDate >= "{{start_date}}"^^xsd:dateTime && ?activityDate <= "{{end_date}}"^^xsd:dateTime)
}
ORDER BY ?activityDate DESC

# ----------------------------------------------------------------------------
# Query 6: Campaign Performance Analysis
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?campaignName ?targetAudience ?responses ?responseRate
       ?budget ?actualCost ?roi
WHERE {
  ?campaign a crm:Campaign ;
            crm:campaignName ?campaignName ;
            crm:targetAudience ?targetAudience ;
            crm:budget ?budget ;
            crm:actualCost ?actualCost .

  OPTIONAL {
    SELECT ?campaign (COUNT(?member) AS ?responses)
    WHERE {
      ?member a crm:CampaignMember ;
              crm:campaign ?campaign ;
              crm:response crm:Responded .
    }
    GROUP BY ?campaign
  }

  BIND(COALESCE(?responses, 0) AS ?responses)
  BIND((?responses / ?targetAudience) * 100 AS ?responseRate)
  BIND(((?responses * {{avg_revenue_per_lead}}) - ?actualCost) / ?actualCost * 100 AS ?roi)

  FILTER(?campaign IN (SELECT ?c WHERE { ?c crm:endDate ?ed . FILTER(?ed >= "{{start_date}}"^^xsd:date) }))
}
ORDER BY ?roi DESC

# ----------------------------------------------------------------------------
# Query 7: Customer Lifecycle Distribution
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>

SELECT ?lifecycleStageName (COUNT(?account) AS ?accountCount)
       (SUM(?totalRevenue) AS ?stageRevenue)
WHERE {
  ?account a crm:Account ;
           crm:lifecycleStage ?stage .

  ?stage rdfs:label ?lifecycleStageName .

  OPTIONAL {
    SELECT ?account (SUM(?amount) AS ?totalRevenue)
    WHERE {
      ?opportunity a crm:Opportunity ;
                   crm:account ?account ;
                   crm:stage crm:ClosedWon ;
                   crm:amount ?amount .
    }
    GROUP BY ?account
  }

  BIND(COALESCE(?totalRevenue, 0) AS ?totalRevenue)
}
GROUP BY ?lifecycleStageName
ORDER BY ?accountCount DESC

# ----------------------------------------------------------------------------
# Query 8: Territory Performance Comparison
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?territoryName ?salesRep ?quotaAmount ?actualRevenue ?achievement
WHERE {
  ?quota a crm:Quota ;
         crm:territory ?territory ;
         crm:quotaAmount ?quotaAmount ;
         crm:quotaPeriod "{{period}}" ;
         crm:salesRep ?rep .

  ?territory crm:territoryName ?territoryName .
  ?rep rdfs:label ?salesRep .

  OPTIONAL {
    SELECT ?rep (SUM(?amount) AS ?actualRevenue)
    WHERE {
      ?opportunity a crm:Opportunity ;
                   crm:owner ?rep ;
                   crm:stage crm:ClosedWon ;
                   crm:amount ?amount ;
                   crm:closeDate ?closeDate .
      FILTER(?closeDate >= "{{start_date}}"^^xsd:date && ?closeDate <= "{{end_date}}"^^xsd:date)
    }
    GROUP BY ?rep
  }

  BIND(COALESCE(?actualRevenue, 0) AS ?actualRevenue)
  BIND((?actualRevenue / ?quotaAmount) * 100 AS ?achievement)
}
ORDER BY ?achievement DESC

# ----------------------------------------------------------------------------
# Query 9: Account Relationship Hierarchy
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>

SELECT ?parentAccountName ?childAccountName ?relationshipType ?totalRevenue
WHERE {
  ?childAccount a crm:Account ;
                crm:parentAccount ?parentAccount ;
                crm:accountNumber ?childNumber ;
                rdfs:label ?childAccountName .

  ?parentAccount rdfs:label ?parentAccountName .

  OPTIONAL { ?childAccount crm:relationshipType ?relationshipType }

  OPTIONAL {
    SELECT ?childAccount (SUM(?amount) AS ?totalRevenue)
    WHERE {
      ?opportunity a crm:Opportunity ;
                   crm:account ?childAccount ;
                   crm:stage crm:ClosedWon ;
                   crm:amount ?amount .
    }
    GROUP BY ?childAccount
  }

  BIND(COALESCE(?totalRevenue, 0) AS ?totalRevenue)
}
ORDER BY ?parentAccountName, ?totalRevenue DESC

# ----------------------------------------------------------------------------
# Query 10: Opportunity Age Analysis
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?opportunityName ?stageName ?amount ?createdDate
       (xsd:integer((xsd:date("{{as_of_date}}") - ?createdDate) / xsd:dayTimeDuration("P1D")) AS ?daysInStage)
WHERE {
  ?opportunity a crm:Opportunity ;
               crm:opportunityName ?opportunityName ;
               crm:stage ?stage ;
               crm:amount ?amount ;
               crm:createdDate ?createdDate .

  ?stage rdfs:label ?stageName .

  FILTER(?stage NOT IN (crm:ClosedWon, crm:ClosedLost))
}
ORDER BY ?daysInStage DESC

# ----------------------------------------------------------------------------
# Query 11: Customer Engagement Score
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?accountName ?activityCount ?lastActivityDate ?engagementScore
WHERE {
  ?account a crm:Account ;
           rdfs:label ?accountName .

  OPTIONAL {
    SELECT ?account (COUNT(?activity) AS ?activityCount) (MAX(?activityDate) AS ?lastActivityDate)
    WHERE {
      ?activity a crm:Activity ;
                crm:relatedTo ?account ;
                crm:activityDate ?activityDate .
      FILTER(?activityDate >= "{{start_date}}"^^xsd:dateTime && ?activityDate <= "{{end_date}}"^^xsd:dateTime)
    }
    GROUP BY ?account
  }

  BIND(COALESCE(?activityCount, 0) AS ?activityCount)
  BIND(
    IF(?activityCount = 0, 0,
    IF(?activityCount < 5, 25,
    IF(?activityCount < 10, 50,
    IF(?activityCount < 20, 75, 100))))
    AS ?engagementScore
  )
}
ORDER BY ?engagementScore DESC, ?lastActivityDate DESC

# ----------------------------------------------------------------------------
# Query 12: Revenue Attribution by Source
# ----------------------------------------------------------------------------
PREFIX crm: <http://ggen.io/ontology/crm#>

SELECT ?leadSourceName (COUNT(?opportunity) AS ?opportunityCount)
       (SUM(?amount) AS ?totalRevenue) (AVG(?amount) AS ?avgDealSize)
WHERE {
  ?opportunity a crm:Opportunity ;
               crm:stage crm:ClosedWon ;
               crm:amount ?amount .

  ?opportunity crm:account ?account .
  ?account crm:originatedFrom ?lead .
  ?lead crm:leadSource ?source .

  ?source rdfs:label ?leadSourceName .
}
GROUP BY ?leadSourceName
ORDER BY ?totalRevenue DESC
