# SPARQL Templates for Notification & Messaging Hub
# 12+ essential queries for enterprise messaging operations

PREFIX nmh: <http://ggen.ai/ontology/notification-messaging-hub#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

# ==================== Query 1: Get All Pending Messages ====================
# Retrieves messages awaiting delivery with priority sorting

SELECT ?message ?subject ?priority ?scheduledAt ?channel
WHERE {
    ?message a nmh:Message ;
             nmh:subject ?subject ;
             nmh:priority ?priority ;
             nmh:deliveredVia ?channel .

    OPTIONAL { ?message nmh:scheduledAt ?scheduledAt }

    ?message nmh:hasDeliveryStatus ?status .
    ?status nmh:deliveryStatus "pending" .
}
ORDER BY DESC(?priority) ?scheduledAt
LIMIT 1000

# ==================== Query 2: Get Messages by Campaign ====================
# Retrieves all messages for a specific campaign with delivery metrics

SELECT ?message ?subject ?recipient ?status ?deliveredAt
WHERE {
    ?message a nmh:Message ;
             nmh:partOfCampaign ?campaign ;
             nmh:subject ?subject ;
             nmh:hasRecipient ?recipient ;
             nmh:hasDeliveryStatus ?statusObj .

    ?statusObj nmh:deliveryStatus ?status .
    OPTIONAL { ?statusObj nmh:deliveredAt ?deliveredAt }

    FILTER(?campaign = <http://ggen.ai/campaign/{{CAMPAIGN_ID}}>)
}
ORDER BY ?deliveredAt

# ==================== Query 3: Get Channel Performance Metrics ====================
# Analyzes delivery performance by channel

SELECT ?channel ?channelType (COUNT(?message) AS ?totalMessages)
       (SUM(IF(?status = "delivered", 1, 0)) AS ?successCount)
       (SUM(IF(?status = "failed", 1, 0)) AS ?failureCount)
       (AVG(?attemptCount) AS ?avgAttempts)
WHERE {
    ?message a nmh:Message ;
             nmh:deliveredVia ?channel ;
             nmh:hasDeliveryStatus ?statusObj .

    ?channel nmh:channelType ?channelType .
    ?statusObj nmh:deliveryStatus ?status .

    OPTIONAL {
        ?message nmh:hasDeliveryStatus/nmh:attemptCount ?attemptCount
    }

    FILTER(?message IN (
        SELECT ?msg WHERE {
            ?msg nmh:createdAt ?created .
            FILTER(?created >= "{{START_DATE}}"^^xsd:dateTime &&
                   ?created <= "{{END_DATE}}"^^xsd:dateTime)
        }
    ))
}
GROUP BY ?channel ?channelType
ORDER BY DESC(?totalMessages)

# ==================== Query 4: Get Rate Limit Status ====================
# Monitors rate limiting status across channels

SELECT ?channel ?channelType ?maxRequestsPerMinute ?currentUsage
WHERE {
    ?channel a nmh:Channel ;
             nmh:channelType ?channelType ;
             nmh:hasRateLimit ?rateLimit .

    ?rateLimit nmh:maxRequestsPerMinute ?maxRequestsPerMinute .

    OPTIONAL {
        SELECT ?channel (COUNT(?message) AS ?currentUsage)
        WHERE {
            ?message nmh:deliveredVia ?channel ;
                     nmh:createdAt ?created .

            FILTER(?created >= NOW() - "PT1M"^^xsd:duration)
        }
        GROUP BY ?channel
    }
}
ORDER BY DESC(?currentUsage)

# ==================== Query 5: Get Failed Deliveries with Retry ====================
# Identifies failed messages eligible for retry

SELECT ?message ?subject ?channel ?failureReason ?attemptCount ?lastAttempt
WHERE {
    ?message a nmh:Message ;
             nmh:subject ?subject ;
             nmh:deliveredVia ?channel ;
             nmh:hasDeliveryStatus ?status .

    ?status nmh:deliveryStatus "failed" .

    ?message nmh:hasDeliveryStatus ?attempt .
    ?attempt nmh:attemptCount ?attemptCount ;
             nmh:failureReason ?failureReason .

    OPTIONAL { ?attempt nmh:createdAt ?lastAttempt }

    FILTER(?attemptCount < 3)
}
ORDER BY ?attemptCount ?lastAttempt

# ==================== Query 6: Get Recipient Preferences ====================
# Retrieves notification preferences for recipients

SELECT ?recipient ?emailAddress ?phoneNumber ?channel ?optedIn
WHERE {
    ?recipient a nmh:Recipient .

    OPTIONAL { ?recipient nmh:emailAddress ?emailAddress }
    OPTIONAL { ?recipient nmh:phoneNumber ?phoneNumber }

    ?recipient nmh:hasPreferences ?prefs .
    ?prefs nmh:channel ?channel ;
           nmh:optedIn ?optedIn .

    FILTER(?recipient = <http://ggen.ai/recipient/{{RECIPIENT_ID}}>)
}

# ==================== Query 7: Get Template Usage Statistics ====================
# Analyzes template usage and performance

SELECT ?template ?templateId (COUNT(?message) AS ?usageCount)
       (AVG(?deliveryTime) AS ?avgDeliveryTime)
WHERE {
    ?message a nmh:Message ;
             nmh:usesTemplate ?template ;
             nmh:hasDeliveryStatus ?status .

    ?template nmh:templateId ?templateId .
    ?status nmh:deliveryStatus "delivered" ;
            nmh:deliveredAt ?deliveredAt .

    ?message nmh:createdAt ?createdAt .

    BIND((?deliveredAt - ?createdAt) AS ?deliveryTime)
}
GROUP BY ?template ?templateId
ORDER BY DESC(?usageCount)

# ==================== Query 8: Get Provider Health Status ====================
# Monitors health and performance of message providers

SELECT ?provider ?providerName ?channel
       (COUNT(?message) AS ?totalMessages)
       (SUM(IF(?status = "delivered", 1, 0)) AS ?successCount)
       (AVG(?responseTime) AS ?avgResponseTime)
WHERE {
    ?channel a nmh:Channel ;
             nmh:handledBy ?provider .

    ?provider nmh:providerName ?providerName .

    ?message nmh:deliveredVia ?channel ;
             nmh:hasDeliveryStatus ?statusObj .

    ?statusObj nmh:deliveryStatus ?status .

    OPTIONAL {
        ?statusObj nmh:responseTime ?responseTime
    }

    FILTER(?message IN (
        SELECT ?msg WHERE {
            ?msg nmh:createdAt ?created .
            FILTER(?created >= NOW() - "PT1H"^^xsd:duration)
        }
    ))
}
GROUP BY ?provider ?providerName ?channel
ORDER BY DESC(?totalMessages)

# ==================== Query 9: Get Queue Depth Analysis ====================
# Monitors message queue depth and processing rates

SELECT ?queue ?queueType (COUNT(?message) AS ?queueDepth)
       (AVG(?waitTime) AS ?avgWaitTime)
WHERE {
    ?message a nmh:Message ;
             nmh:queuedIn ?queue ;
             nmh:hasDeliveryStatus ?status .

    ?queue a ?queueType .
    ?status nmh:deliveryStatus "queued" .

    ?message nmh:createdAt ?createdAt .

    BIND((NOW() - ?createdAt) AS ?waitTime)
}
GROUP BY ?queue ?queueType
ORDER BY DESC(?queueDepth)

# ==================== Query 10: Get Campaign Performance Summary ====================
# Comprehensive campaign performance metrics

SELECT ?campaign ?campaignName ?totalRecipients
       (SUM(IF(?status = "delivered", 1, 0)) AS ?delivered)
       (SUM(IF(?status = "opened", 1, 0)) AS ?opened)
       (SUM(IF(?status = "clicked", 1, 0)) AS ?clicked)
       (AVG(?deliveryTime) AS ?avgDeliveryTime)
WHERE {
    ?campaign a nmh:Campaign ;
              nmh:campaignName ?campaignName .

    ?message nmh:partOfCampaign ?campaign ;
             nmh:hasRecipient ?recipient ;
             nmh:hasDeliveryStatus ?statusObj ;
             nmh:createdAt ?createdAt .

    ?statusObj nmh:deliveryStatus ?status .
    OPTIONAL { ?statusObj nmh:deliveredAt ?deliveredAt }

    BIND((?deliveredAt - ?createdAt) AS ?deliveryTime)

    {
        SELECT ?campaign (COUNT(DISTINCT ?recipient) AS ?totalRecipients)
        WHERE {
            ?message nmh:partOfCampaign ?campaign ;
                     nmh:hasRecipient ?recipient .
        }
        GROUP BY ?campaign
    }
}
GROUP BY ?campaign ?campaignName ?totalRecipients
ORDER BY DESC(?delivered)

# ==================== Query 11: Get Delivery Anomalies ====================
# Detects unusual patterns in message delivery

SELECT ?channel ?channelType ?hour
       (COUNT(?message) AS ?messageCount)
       (AVG(?failureRate) AS ?avgFailureRate)
WHERE {
    ?message a nmh:Message ;
             nmh:deliveredVia ?channel ;
             nmh:createdAt ?createdAt ;
             nmh:hasDeliveryStatus ?status .

    ?channel nmh:channelType ?channelType .
    ?status nmh:deliveryStatus ?deliveryStatus .

    BIND(HOURS(?createdAt) AS ?hour)
    BIND(IF(?deliveryStatus = "failed", 1, 0) AS ?failureRate)

    FILTER(?createdAt >= NOW() - "P1D"^^xsd:duration)
}
GROUP BY ?channel ?channelType ?hour
HAVING (?avgFailureRate > 0.1)
ORDER BY DESC(?avgFailureRate)

# ==================== Query 12: Get Scheduled Messages Timeline ====================
# Retrieves upcoming scheduled messages

SELECT ?message ?subject ?scheduledAt ?channel ?recipientCount
WHERE {
    ?message a nmh:Message ;
             nmh:subject ?subject ;
             nmh:scheduledAt ?scheduledAt ;
             nmh:deliveredVia ?channel .

    {
        SELECT ?message (COUNT(?recipient) AS ?recipientCount)
        WHERE {
            ?message nmh:hasRecipient ?recipient .
        }
        GROUP BY ?message
    }

    FILTER(?scheduledAt > NOW() &&
           ?scheduledAt < NOW() + "P7D"^^xsd:duration)
}
ORDER BY ?scheduledAt

# ==================== Query 13: Get Multi-Channel Message Correlation ====================
# Tracks messages sent to same recipient across multiple channels

SELECT ?recipient ?emailAddress
       (COUNT(DISTINCT ?emailMsg) AS ?emailCount)
       (COUNT(DISTINCT ?smsMsg) AS ?smsCount)
       (COUNT(DISTINCT ?pushMsg) AS ?pushCount)
WHERE {
    ?recipient a nmh:Recipient ;
               nmh:emailAddress ?emailAddress .

    OPTIONAL {
        ?emailMsg a nmh:EmailMessage ;
                  nmh:hasRecipient ?recipient .
    }

    OPTIONAL {
        ?smsMsg a nmh:SMSMessage ;
                nmh:hasRecipient ?recipient .
    }

    OPTIONAL {
        ?pushMsg a nmh:PushNotification ;
                 nmh:hasRecipient ?recipient .
    }

    FILTER EXISTS {
        ?anyMsg nmh:hasRecipient ?recipient ;
                nmh:createdAt ?created .
        FILTER(?created >= NOW() - "P30D"^^xsd:duration)
    }
}
GROUP BY ?recipient ?emailAddress
HAVING (?emailCount + ?smsCount + ?pushCount > 10)
ORDER BY DESC(?emailCount + ?smsCount + ?pushCount)
