# ============================================================
# ISO 20022 SPARQL Query Templates
# Version: 1.0.0
# ============================================================

PREFIX iso: <http://example.org/iso20022#>
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: Generate pain.001 Credit Transfer Message
# ============================================================
CONSTRUCT {
  ?msg a iso:CustomerCreditTransferInitiation ;
       iso:hasGroupHeader ?header ;
       iso:hasPaymentInformation ?pmtInf .

  ?header a iso:GroupHeader ;
          iso:messageIdentification ?msgId ;
          iso:creationDateTime ?createdAt ;
          iso:numberOfTransactions ?numTxs ;
          iso:controlSum ?ctrlSum .

  ?pmtInf a iso:PaymentInformation ;
          iso:paymentInformationIdentification ?pmtInfId ;
          iso:paymentMethod "TRF" ;
          iso:requestedExecutionDate ?execDate ;
          iso:hasDebtor ?debtor ;
          iso:hasDebtorAgent ?dbtrAgt ;
          iso:hasDebtorAccount ?dbtrAcct ;
          iso:hasTransaction ?tx .

  ?debtor a iso:Debtor ;
          iso:partyName ?debtorName .

  ?dbtrAgt a iso:DebtorAgent ;
           iso:hasFinancialInstitutionIdentification ?dbtrBic .

  ?dbtrBic a iso:BIC ;
           iso:bic ?debtorBic .

  ?dbtrAcct a iso:DebtorAccount ;
            iso:hasAccountIdentification ?dbtrIban .

  ?dbtrIban a iso:IBAN ;
            iso:iban ?debtorIban .

  ?tx a iso:CreditTransferTransactionInformation ;
      iso:endToEndIdentification ?e2eId ;
      iso:hasInstructedAmount ?amt ;
      iso:hasCreditor ?creditor ;
      iso:hasCreditorAgent ?cdtrAgt ;
      iso:hasCreditorAccount ?cdtrAcct ;
      iso:hasRemittanceInformation ?rmtInf .

  ?amt a iso:InstructedAmount ;
       iso:amountValue ?amtValue ;
       iso:hasCurrency ?ccy .

  ?ccy a iso:Currency ;
       iso:currencyCode ?ccyCode .

  ?creditor a iso:Creditor ;
            iso:partyName ?creditorName .

  ?cdtrAgt a iso:CreditorAgent ;
           iso:hasFinancialInstitutionIdentification ?cdtrBic .

  ?cdtrBic a iso:BIC ;
           iso:bic ?creditorBic .

  ?cdtrAcct a iso:CreditorAccount ;
            iso:hasAccountIdentification ?cdtrIban .

  ?cdtrIban a iso:IBAN ;
            iso:iban ?creditorIban .

  ?rmtInf a iso:UnstructuredRemittanceInformation ;
          iso:unstructuredRemittance ?rmtText .
}
WHERE {
  # Bind input parameters here or via VALUES clause
  BIND("MSG-20250101-001" AS ?msgId)
  BIND(NOW() AS ?createdAt)
  BIND(1 AS ?numTxs)
  BIND(1000.00 AS ?ctrlSum)
  BIND("PMT-001" AS ?pmtInfId)
  BIND("2025-01-15"^^xsd:date AS ?execDate)
  BIND("Acme Corporation" AS ?debtorName)
  BIND("DEUTDEFF" AS ?debtorBic)
  BIND("DE89370400440532013000" AS ?debtorIban)
  BIND("E2E-REF-001" AS ?e2eId)
  BIND(1000.00 AS ?amtValue)
  BIND("EUR" AS ?ccyCode)
  BIND("Widget Supplier Ltd" AS ?creditorName)
  BIND("BNPAFRPP" AS ?creditorBic)
  BIND("FR1420041010050500013M02606" AS ?creditorIban)
  BIND("Invoice INV-2025-001" AS ?rmtText)
}

# ============================================================
# Query 2: Extract Payment Details from RDF Graph
# ============================================================
SELECT ?msgId ?pmtInfId ?e2eId ?debtorName ?creditorName ?amount ?currency ?rmtInfo
WHERE {
  ?msg a iso:CustomerCreditTransferInitiation ;
       iso:hasGroupHeader ?header ;
       iso:hasPaymentInformation ?pmtInf .

  ?header iso:messageIdentification ?msgId .

  ?pmtInf iso:paymentInformationIdentification ?pmtInfId ;
          iso:hasDebtor ?debtor ;
          iso:hasTransaction ?tx .

  ?debtor iso:partyName ?debtorName .

  ?tx iso:endToEndIdentification ?e2eId ;
      iso:hasInstructedAmount ?amt ;
      iso:hasCreditor ?creditor ;
      iso:hasRemittanceInformation ?rmtInf .

  ?amt iso:amountValue ?amount ;
       iso:hasCurrency ?ccy .

  ?ccy iso:currencyCode ?currency .

  ?creditor iso:partyName ?creditorName .

  ?rmtInf iso:unstructuredRemittance ?rmtInfo .
}

# ============================================================
# Query 3: Validate IBAN Format
# ============================================================
SELECT ?account ?iban (STRLEN(?iban) AS ?length) (REGEX(?iban, "^[A-Z]{2}[0-9]{2}[A-Z0-9]+$") AS ?valid)
WHERE {
  ?account iso:hasAccountIdentification ?ibanId .
  ?ibanId a iso:IBAN ;
          iso:iban ?iban .
}

# ============================================================
# Query 4: Validate BIC Format
# ============================================================
SELECT ?agent ?bic (STRLEN(?bic) AS ?length) (REGEX(?bic, "^[A-Z]{6}[A-Z0-9]{2}([A-Z0-9]{3})?$") AS ?valid)
WHERE {
  ?agent iso:hasFinancialInstitutionIdentification ?bicId .
  ?bicId a iso:BIC ;
         iso:bic ?bic .
  FILTER(STRLEN(?bic) = 8 || STRLEN(?bic) = 11)
}

# ============================================================
# Query 5: Generate camt.053 Bank Statement
# ============================================================
CONSTRUCT {
  ?stmt a iso:BankToCustomerStatement ;
        iso:hasGroupHeader ?header ;
        iso:hasStatement ?acctStmt .

  ?header a iso:GroupHeader ;
          iso:messageIdentification ?msgId ;
          iso:creationDateTime ?createdAt .

  ?acctStmt a iso:AccountStatement ;
            iso:hasAccount ?account ;
            iso:hasEntry ?entry .

  ?account iso:hasAccountIdentification ?iban .

  ?entry a iso:StatementEntry ;
         iso:amountValue ?txAmount ;
         iso:creditDebitIndicator ?cdtDbtInd ;
         iso:bookingDate ?bookingDate ;
         iso:valueDate ?valueDate .
}
WHERE {
  # Bind statement parameters
  BIND("STMT-20250101-001" AS ?msgId)
  BIND(NOW() AS ?createdAt)
}

# ============================================================
# Query 6: Find All SEPA Payments
# ============================================================
SELECT ?pmtInf ?execDate ?debtor ?creditor ?amount
WHERE {
  ?msg a iso:CustomerCreditTransferInitiation ;
       iso:hasPaymentInformation ?pmtInf .

  ?pmtInf a iso:SEPACreditTransfer ;
          iso:requestedExecutionDate ?execDate ;
          iso:hasDebtor ?debtor ;
          iso:hasTransaction ?tx .

  ?tx iso:hasCreditor ?creditor ;
      iso:hasInstructedAmount ?amt .

  ?amt iso:amountValue ?amount ;
       iso:hasCurrency ?ccy .

  ?ccy iso:currencyCode "EUR" .
}

# ============================================================
# Query 7: Calculate Total Payment Amount by Currency
# ============================================================
SELECT ?currency (SUM(?amount) AS ?totalAmount) (COUNT(?tx) AS ?txCount)
WHERE {
  ?pmtInf iso:hasTransaction ?tx .

  ?tx iso:hasInstructedAmount ?amt .

  ?amt iso:amountValue ?amount ;
       iso:hasCurrency ?ccy .

  ?ccy iso:currencyCode ?currency .
}
GROUP BY ?currency
ORDER BY DESC(?totalAmount)

# ============================================================
# Query 8: Find Payments Above Threshold
# ============================================================
SELECT ?e2eId ?debtor ?creditor ?amount ?currency
WHERE {
  ?tx a iso:CreditTransferTransactionInformation ;
      iso:endToEndIdentification ?e2eId ;
      iso:hasInstructedAmount ?amt .

  ?amt iso:amountValue ?amount ;
       iso:hasCurrency ?ccy .

  ?ccy iso:currencyCode ?currency .

  ?pmtInf iso:hasTransaction ?tx ;
          iso:hasDebtor ?debtorParty .

  ?debtorParty iso:partyName ?debtor .

  ?tx iso:hasCreditor ?creditorParty .

  ?creditorParty iso:partyName ?creditor .

  FILTER(?amount > 10000)
}
ORDER BY DESC(?amount)

# ============================================================
# Query 9: Find Rejected Payment Status
# ============================================================
SELECT ?msgId ?txId ?statusCode ?reasonCode ?additionalInfo
WHERE {
  ?statusReport a iso:PaymentStatusReport ;
                iso:hasGroupHeader ?header ;
                iso:hasTransaction ?txStatus .

  ?header iso:messageIdentification ?msgId .

  ?txStatus iso:transactionIdentification ?txId ;
            iso:hasStatus ?status .

  ?status iso:statusCode ?statusCode ;
          iso:hasStatusReason ?reason .

  ?reason iso:reasonCode ?reasonCode ;
          iso:additionalInformation ?additionalInfo .

  FILTER(?statusCode = "RJCT")
}

# ============================================================
# Query 10: Find Payments to Specific Country
# ============================================================
SELECT ?e2eId ?creditor ?countryCode ?amount
WHERE {
  ?tx iso:endToEndIdentification ?e2eId ;
      iso:hasCreditor ?creditorParty ;
      iso:hasInstructedAmount ?amt .

  ?creditorParty iso:partyName ?creditor ;
                 iso:countryCode ?countryCode .

  ?amt iso:amountValue ?amount .

  FILTER(?countryCode = "FR")
}

# ============================================================
# Query 11: Direct Debit Mandates
# ============================================================
SELECT ?mandateId ?dateOfSignature ?debtor ?creditor
WHERE {
  ?tx a iso:DirectDebitTransactionInformation ;
      iso:hasMandateRelatedInformation ?mandate .

  ?mandate iso:mandateIdentification ?mandateId ;
           iso:dateOfSignature ?dateOfSignature .

  ?pmtInf iso:hasTransaction ?tx ;
          iso:hasDebtor ?debtorParty .

  ?debtorParty iso:partyName ?debtor .

  ?tx iso:hasCreditor ?creditorParty .

  ?creditorParty iso:partyName ?creditor .
}

# ============================================================
# Query 12: Currency Conversion Query
# ============================================================
SELECT ?e2eId ?instructedAmount ?instructedCurrency ?equivalentAmount ?equivalentCurrency
WHERE {
  ?tx iso:endToEndIdentification ?e2eId ;
      iso:hasInstructedAmount ?instAmt ;
      iso:hasEquivalentAmount ?eqAmt .

  ?instAmt iso:amountValue ?instructedAmount ;
           iso:hasCurrency ?instCcy .

  ?instCcy iso:currencyCode ?instructedCurrency .

  ?eqAmt iso:amountValue ?equivalentAmount ;
         iso:hasCurrency ?eqCcy .

  ?eqCcy iso:currencyCode ?equivalentCurrency .
}

# ============================================================
# Query 13: Find Payments by Date Range
# ============================================================
SELECT ?pmtInfId ?execDate ?numTxs ?controlSum
WHERE {
  ?pmtInf iso:paymentInformationIdentification ?pmtInfId ;
          iso:requestedExecutionDate ?execDate ;
          iso:numberOfTransactions ?numTxs ;
          iso:controlSum ?controlSum .

  FILTER(?execDate >= "2025-01-01"^^xsd:date && ?execDate <= "2025-01-31"^^xsd:date)
}
ORDER BY ?execDate

# ============================================================
# Query 14: Find Intermediary Agents in Payment Chain
# ============================================================
SELECT ?e2eId ?debtorAgent ?intermediaryAgent ?creditorAgent
WHERE {
  ?tx iso:endToEndIdentification ?e2eId ;
      iso:hasIntermediaryAgent ?intAgt .

  ?intAgt iso:hasFinancialInstitutionIdentification ?intBic .
  ?intBic iso:bic ?intermediaryAgent .

  ?pmtInf iso:hasTransaction ?tx ;
          iso:hasDebtorAgent ?dbtrAgt .

  ?dbtrAgt iso:hasFinancialInstitutionIdentification ?dbtrBic .
  ?dbtrBic iso:bic ?debtorAgent .

  ?tx iso:hasCreditorAgent ?cdtrAgt .
  ?cdtrAgt iso:hasFinancialInstitutionIdentification ?cdtrBic .
  ?cdtrBic iso:bic ?creditorAgent .
}

# ============================================================
# Query 15: Structured Remittance Information
# ============================================================
SELECT ?e2eId ?creditorReference ?additionalInfo
WHERE {
  ?tx iso:endToEndIdentification ?e2eId ;
      iso:hasRemittanceInformation ?rmtInf .

  ?rmtInf a iso:StructuredRemittanceInformation ;
          iso:hasCreditorReferenceInformation ?credRefInfo .

  ?credRefInfo iso:creditorReference ?creditorReference .

  OPTIONAL {
    ?rmtInf iso:additionalInformation ?additionalInfo .
  }
}
