# Banking Core SPARQL Templates

PREFIX bank: <http://ggen.dev/ontology/banking-core#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

# Query 1: Get customer account summary
# PARAM: ?customerId - Customer identifier
SELECT ?account ?accountNumber ?accountType ?balance ?availableBalance ?status
WHERE {
  ?customer bank:customerId ?customerId ;
            bank:ownsAccount ?account .
  ?account bank:accountNumber ?accountNumber ;
           bank:accountType ?accountType ;
           bank:balance ?balance ;
           bank:availableBalance ?availableBalance ;
           bank:status ?status .
  FILTER (?status = "active")
}

# Query 2: Get transaction history for account
# PARAM: ?accountNumber - Account number
# PARAM: ?startDate - Start date
# PARAM: ?endDate - End date
SELECT ?transaction ?transactionId ?transactionType ?amount ?description ?transactionDate ?balance
WHERE {
  ?account bank:accountNumber ?accountNumber ;
           bank:executesTransaction ?transaction .
  ?transaction bank:transactionId ?transactionId ;
               bank:transactionType ?transactionType ;
               bank:amount ?amount ;
               bank:description ?description ;
               bank:transactionDate ?transactionDate ;
               bank:runningBalance ?balance .
  FILTER (?transactionDate >= ?startDate && ?transactionDate <= ?endDate)
}
ORDER BY DESC(?transactionDate)

# Query 3: Process ACH transfers
# PARAM: ?accountNumber - Source account
SELECT ?transfer ?transferId ?recipientAccount ?routingNumber ?amount ?status ?scheduledDate
WHERE {
  ?account bank:accountNumber ?accountNumber ;
           bank:executesTransaction ?transfer .
  ?transfer a bank:ACHTransfer ;
            bank:transferId ?transferId ;
            bank:routingNumber ?routingNumber ;
            bank:amount ?amount ;
            bank:status ?status ;
            bank:scheduledDate ?scheduledDate .
  ?transfer bank:transfersTo ?recipient .
  ?recipient bank:accountNumber ?recipientAccount .
  FILTER (?status IN ("pending", "processing"))
}

# Query 4: Calculate interest accrual
# PARAM: ?accountNumber - Account number
# PARAM: ?calculationDate - Calculation date
SELECT ?account ?balance ?interestRate ?apyValue ?accruedInterest ?compoundingFrequency
WHERE {
  ?account bank:accountNumber ?accountNumber ;
           bank:balance ?balance ;
           bank:earnsInterest ?rate ;
           bank:accrues ?accrual .
  ?rate bank:ratePercentage ?interestRate ;
        bank:compoundingFrequency ?compoundingFrequency .
  ?rate a bank:CompoundInterest ;
        bank:apyValue ?apyValue .
  ?accrual bank:accruedInterest ?accruedInterest ;
           bank:calculationDate ?calculationDate .
}

# Query 5: Monitor account limits and compliance
# PARAM: ?accountNumber - Account number
SELECT ?account ?limit ?limitType ?limitAmount ?currentUsage ?remaining
WHERE {
  ?account bank:accountNumber ?accountNumber ;
           bank:hasLimit ?limit .
  ?limit bank:limitType ?limitType ;
         bank:limitAmount ?limitAmount ;
         bank:currentUsage ?currentUsage .
  BIND(?limitAmount - ?currentUsage AS ?remaining)
  FILTER (?limitType IN ("daily_withdrawal", "monthly_transaction", "reg_d"))
}

# Query 6: Generate call report data
# PARAM: ?reportDate - Reporting date
SELECT ?account ?accountType (SUM(?balance) AS ?totalBalance) (COUNT(?account) AS ?accountCount)
WHERE {
  ?account bank:accountType ?accountType ;
           bank:balance ?balance ;
           bank:status "active" ;
           bank:reportsTo ?report .
  ?report a bank:CallReport ;
          bank:reportDate ?reportDate .
}
GROUP BY ?accountType

# Query 7: CTR (Currency Transaction Report) monitoring
# PARAM: ?startDate - Start date
# PARAM: ?endDate - End date
# PARAM: ?threshold - CTR threshold (default 10000)
SELECT ?customer ?transaction ?transactionType ?amount ?transactionDate
WHERE {
  ?customer bank:ownsAccount ?account .
  ?account bank:executesTransaction ?transaction .
  ?transaction bank:transactionType ?transactionType ;
               bank:amount ?amount ;
               bank:transactionDate ?transactionDate .
  FILTER (?amount > ?threshold)
  FILTER (?transactionDate >= ?startDate && ?transactionDate <= ?endDate)
  FILTER (?transactionType IN ("cash_deposit", "cash_withdrawal"))
}
ORDER BY DESC(?amount)

# Query 8: SAR (Suspicious Activity Report) detection
# PARAM: ?customerId - Customer identifier
SELECT ?transaction ?transactionType ?amount ?pattern ?riskScore ?detectedDate
WHERE {
  ?customer bank:customerId ?customerId ;
            bank:ownsAccount ?account .
  ?account bank:executesTransaction ?transaction .
  ?transaction bank:transactionType ?transactionType ;
               bank:amount ?amount ;
               bank:detectedPattern ?pattern ;
               bank:riskScore ?riskScore ;
               bank:detectedDate ?detectedDate .
  FILTER (?riskScore > 70)
  FILTER (?pattern IN ("structuring", "rapid_movement", "unusual_pattern"))
}
ORDER BY DESC(?riskScore)

# Query 9: OFAC screening results
# PARAM: ?customerId - Customer identifier (optional)
SELECT ?customer ?customerName ?screeningResult ?matchScore ?listName ?screenedDate
WHERE {
  ?customer bank:customerId ?customerId ;
            bank:customerName ?customerName ;
            bank:screenedBy ?screening .
  ?screening a bank:OFACScreening ;
             bank:screeningResult ?screeningResult ;
             bank:matchScore ?matchScore ;
             bank:listName ?listName ;
             bank:screenedDate ?screenedDate .
  FILTER (?screeningResult IN ("match", "potential_match"))
}
ORDER BY DESC(?matchScore)

# Query 10: Loan account details with payment schedule
# PARAM: ?accountNumber - Loan account number
SELECT ?loan ?principalBalance ?interestRate ?paymentAmount ?dueDate ?remainingPayments
WHERE {
  ?loan a bank:LoanAccount ;
        bank:accountNumber ?accountNumber ;
        bank:principalBalance ?principalBalance ;
        bank:earnsInterest ?rate ;
        bank:hasPaymentSchedule ?schedule .
  ?rate bank:ratePercentage ?interestRate .
  ?schedule bank:paymentAmount ?paymentAmount ;
            bank:nextDueDate ?dueDate ;
            bank:remainingPayments ?remainingPayments .
}

# Query 11: Credit card account with available credit
# PARAM: ?accountNumber - Credit card account number
SELECT ?card ?creditLimit ?currentBalance ?availableCredit ?minPayment ?dueDate ?apr
WHERE {
  ?card a bank:CreditCardAccount ;
        bank:accountNumber ?accountNumber ;
        bank:balance ?currentBalance ;
        bank:hasLimit ?limit ;
        bank:earnsInterest ?rate .
  ?limit a bank:CreditLimit ;
         bank:limitAmount ?creditLimit .
  ?rate bank:ratePercentage ?apr .
  ?card bank:minimumPayment ?minPayment ;
        bank:paymentDueDate ?dueDate .
  BIND(?creditLimit - ?currentBalance AS ?availableCredit)
}

# Query 12: Ledger reconciliation
# PARAM: ?accountNumber - Account number
# PARAM: ?reconciliationDate - Reconciliation date
SELECT ?account ?ledger ?debitTotal ?creditTotal ?netChange ?reconciliationStatus
WHERE {
  ?account bank:accountNumber ?accountNumber ;
           bank:recordedIn ?ledger ;
           bank:reconcilesTo ?reconciliation .
  ?ledger bank:hasEntry ?entry .
  ?reconciliation bank:reconciliationDate ?reconciliationDate ;
                  bank:reconciliationStatus ?reconciliationStatus .
  {
    SELECT ?ledger (SUM(?debitAmount) AS ?debitTotal)
    WHERE {
      ?ledger bank:hasEntry ?debit .
      ?debit a bank:DebitEntry ;
             bank:debitAmount ?debitAmount .
    }
    GROUP BY ?ledger
  }
  {
    SELECT ?ledger (SUM(?creditAmount) AS ?creditTotal)
    WHERE {
      ?ledger bank:hasEntry ?credit .
      ?credit a bank:CreditEntry ;
              bank:creditAmount ?creditAmount .
    }
    GROUP BY ?ledger
  }
  BIND(?creditTotal - ?debitTotal AS ?netChange)
}

# Query 13: Customer KYC/AML status
# PARAM: ?customerId - Customer identifier
SELECT ?customer ?customerName ?kycStatus ?amlRiskLevel ?verificationDate ?documentExpiry
WHERE {
  ?customer bank:customerId ?customerId ;
            bank:customerName ?customerName ;
            bank:passesKYC ?kyc ;
            bank:passesAML ?aml .
  ?kyc bank:verificationStatus ?kycStatus ;
       bank:verificationDate ?verificationDate ;
       bank:documentExpiry ?documentExpiry .
  ?aml bank:riskLevel ?amlRiskLevel .
}

# Query 14: Overdraft protection utilization
# PARAM: ?accountNumber - Account number
SELECT ?account ?balance ?overdraftLimit ?utilized ?available ?feeCharged
WHERE {
  ?account bank:accountNumber ?accountNumber ;
           bank:balance ?balance ;
           bank:hasProtection ?protection .
  ?protection a bank:OverdraftProtection ;
              bank:overdraftLimit ?overdraftLimit ;
              bank:utilized ?utilized ;
              bank:feeCharged ?feeCharged .
  BIND(?overdraftLimit - ?utilized AS ?available)
  FILTER (?balance < 0)
}

# Query 15: Regulatory audit trail
# PARAM: ?accountNumber - Account number
# PARAM: ?startDate - Start date
# PARAM: ?endDate - End date
SELECT ?transaction ?transactionId ?userId ?timestamp ?action ?ipAddress ?deviceId
WHERE {
  ?account bank:accountNumber ?accountNumber ;
           bank:executesTransaction ?transaction .
  ?transaction bank:transactionId ?transactionId ;
               bank:transactionDate ?timestamp ;
               bank:hasAuditTrail ?audit .
  ?audit bank:userId ?userId ;
         bank:action ?action ;
         bank:ipAddress ?ipAddress ;
         bank:deviceId ?deviceId .
  FILTER (?timestamp >= ?startDate && ?timestamp <= ?endDate)
}
ORDER BY ?timestamp
