# ============================================================================
# Supply Chain Management SPARQL Query Templates
# ============================================================================

# ----------------------------------------------------------------------------
# Query 1: Purchase Recommendations
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>

SELECT ?itemCode ?itemName ?quantityOnHand ?reorderPoint ?economicOrderQuantity ?recommendedOrder
WHERE {
  ?item a sc:InventoryItem ;
        sc:itemCode ?itemCode ;
        rdfs:label ?itemName ;
        sc:reorderPoint ?reorderPoint ;
        sc:economicOrderQuantity ?economicOrderQuantity .

  ?level sc:item ?item ;
         sc:quantityOnHand ?quantityOnHand .

  BIND(IF(?quantityOnHand <= ?reorderPoint, ?economicOrderQuantity, 0) AS ?recommendedOrder)

  FILTER(?recommendedOrder > 0)
}
ORDER BY ?quantityOnHand ASC

# ----------------------------------------------------------------------------
# Query 2: Optimal Order Quantities
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>

SELECT ?itemCode ?annualDemand ?orderingCost ?holdingCost ?eoq
WHERE {
  ?item a sc:InventoryItem ;
        sc:itemCode ?itemCode ;
        sc:annualDemand ?annualDemand ;
        sc:orderingCost ?orderingCost ;
        sc:holdingCostPerUnit ?holdingCost .

  BIND(SQRT((2 * ?annualDemand * ?orderingCost) / ?holdingCost) AS ?eoq)
}

# ----------------------------------------------------------------------------
# Query 3: Shipment Schedules
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?shipmentNumber ?carrierName ?origin ?destination ?shippedDate ?expectedDelivery ?status
WHERE {
  ?shipment a sc:Shipment ;
            sc:shipmentNumber ?shipmentNumber ;
            sc:carrier ?carrier ;
            sc:origin ?origin ;
            sc:destination ?destination ;
            sc:shippedDate ?shippedDate ;
            sc:expectedDeliveryDate ?expectedDelivery ;
            sc:status ?status .

  ?carrier rdfs:label ?carrierName .

  FILTER(?shippedDate >= "{{start_date}}"^^xsd:dateTime)
  FILTER(?status != sc:Delivered)
}
ORDER BY ?expectedDelivery

# ----------------------------------------------------------------------------
# Query 4: Supplier Performance
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>

SELECT ?supplierName ?onTimeRate ?qualityRating ?totalOrders ?avgLeadTime
WHERE {
  ?supplier a sc:Supplier ;
            sc:supplierName ?supplierName .

  ?performance sc:supplier ?supplier ;
               sc:onTimeDeliveryRate ?onTimeRate ;
               sc:qualityRating ?qualityRating .

  OPTIONAL {
    SELECT ?supplier (COUNT(?po) AS ?totalOrders) (AVG(?leadTime) AS ?avgLeadTime)
    WHERE {
      ?po a sc:PurchaseOrder ;
          sc:supplier ?supplier ;
          sc:leadTime ?leadTime .
    }
    GROUP BY ?supplier
  }

  BIND(COALESCE(?totalOrders, 0) AS ?totalOrders)
}
ORDER BY ?onTimeRate DESC, ?qualityRating DESC

# ----------------------------------------------------------------------------
# Query 5: Demand Forecast
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>

SELECT ?itemCode ?forecastPeriod ?forecastQuantity ?confidence
WHERE {
  ?forecast a sc:DemandForecast ;
            sc:item ?item ;
            sc:forecastPeriod ?forecastPeriod ;
            sc:forecastQuantity ?forecastQuantity ;
            sc:confidenceLevel ?confidence .

  ?item sc:itemCode ?itemCode .

  FILTER(?forecastPeriod >= "{{start_period}}" && ?forecastPeriod <= "{{end_period}}")
}
ORDER BY ?itemCode, ?forecastPeriod

# ----------------------------------------------------------------------------
# Query 6: Warehouse Utilization
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>

SELECT ?warehouseCode ?totalBins ?occupiedBins ?utilizationRate
WHERE {
  ?warehouse a sc:Warehouse ;
             sc:warehouseCode ?warehouseCode .

  {
    SELECT ?warehouse (COUNT(?bin) AS ?totalBins)
    WHERE {
      ?bin sc:warehouse ?warehouse .
    }
    GROUP BY ?warehouse
  }

  {
    SELECT ?warehouse (COUNT(?bin) AS ?occupiedBins)
    WHERE {
      ?bin sc:warehouse ?warehouse ;
           sc:occupied true .
    }
    GROUP BY ?warehouse
  }

  BIND((?occupiedBins / ?totalBins) * 100 AS ?utilizationRate)
}
ORDER BY ?utilizationRate DESC

# ----------------------------------------------------------------------------
# Query 7: Inventory Turnover
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>

SELECT ?itemCode ?costOfGoodsSold ?avgInventory ?turnoverRate
WHERE {
  ?item a sc:InventoryItem ;
        sc:itemCode ?itemCode .

  {
    SELECT ?item (SUM(?cost) AS ?costOfGoodsSold)
    WHERE {
      ?movement a sc:StockMovement ;
                sc:item ?item ;
                sc:movementType sc:Sale ;
                sc:cost ?cost .
    }
    GROUP BY ?item
  }

  {
    SELECT ?item (AVG(?quantity) AS ?avgInventory)
    WHERE {
      ?level a sc:InventoryLevel ;
             sc:item ?item ;
             sc:quantityOnHand ?quantity .
    }
    GROUP BY ?item
  }

  BIND(?costOfGoodsSold / ?avgInventory AS ?turnoverRate)
}
ORDER BY ?turnoverRate DESC

# ----------------------------------------------------------------------------
# Query 8: Late Shipments Analysis
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?shipmentNumber ?carrierName ?expectedDelivery ?actualDelivery ?daysLate
WHERE {
  ?shipment a sc:Shipment ;
            sc:shipmentNumber ?shipmentNumber ;
            sc:carrier ?carrier ;
            sc:expectedDeliveryDate ?expectedDelivery ;
            sc:actualDeliveryDate ?actualDelivery .

  ?carrier rdfs:label ?carrierName .

  BIND(xsd:integer((?actualDelivery - ?expectedDelivery) / xsd:dayTimeDuration("P1D")) AS ?daysLate)

  FILTER(?daysLate > 0)
}
ORDER BY ?daysLate DESC

# ----------------------------------------------------------------------------
# Query 9: Stock Availability by Location
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>

SELECT ?itemCode ?warehouseCode ?quantityOnHand ?quantityReserved ?quantityAvailable
WHERE {
  ?level a sc:InventoryLevel ;
         sc:item ?item ;
         sc:warehouse ?warehouse ;
         sc:quantityOnHand ?quantityOnHand ;
         sc:quantityReserved ?quantityReserved .

  ?item sc:itemCode ?itemCode .
  ?warehouse sc:warehouseCode ?warehouseCode .

  BIND(?quantityOnHand - ?quantityReserved AS ?quantityAvailable)

  FILTER(?quantityOnHand > 0)
}
ORDER BY ?itemCode, ?warehouseCode

# ----------------------------------------------------------------------------
# Query 10: Purchase Order Status
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>

SELECT ?poNumber ?supplierName ?orderDate ?expectedDelivery ?status ?totalAmount
WHERE {
  ?po a sc:PurchaseOrder ;
      sc:poNumber ?poNumber ;
      sc:supplier ?supplier ;
      sc:orderDate ?orderDate ;
      sc:expectedDeliveryDate ?expectedDelivery ;
      sc:status ?status ;
      sc:totalAmount ?totalAmount .

  ?supplier sc:supplierName ?supplierName .

  FILTER(?status IN (sc:Pending, sc:PartiallyReceived))
}
ORDER BY ?expectedDelivery

# ----------------------------------------------------------------------------
# Query 11: Carrier Performance Comparison
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>

SELECT ?carrierName ?totalShipments ?onTimeShipments ?onTimeRate ?avgDelay
WHERE {
  ?carrier a sc:Carrier ;
           rdfs:label ?carrierName .

  {
    SELECT ?carrier (COUNT(?shipment) AS ?totalShipments)
    WHERE {
      ?shipment sc:carrier ?carrier .
    }
    GROUP BY ?carrier
  }

  {
    SELECT ?carrier (COUNT(?shipment) AS ?onTimeShipments)
    WHERE {
      ?shipment sc:carrier ?carrier ;
                sc:actualDeliveryDate ?actual ;
                sc:expectedDeliveryDate ?expected .
      FILTER(?actual <= ?expected)
    }
    GROUP BY ?carrier
  }

  BIND((?onTimeShipments / ?totalShipments) * 100 AS ?onTimeRate)
}
ORDER BY ?onTimeRate DESC

# ----------------------------------------------------------------------------
# Query 12: ABC Inventory Classification
# ----------------------------------------------------------------------------
PREFIX sc: <http://ggen.io/ontology/supply-chain#>

SELECT ?itemCode ?annualUsageValue ?cumulativePercentage ?classification
WHERE {
  ?item a sc:InventoryItem ;
        sc:itemCode ?itemCode ;
        sc:annualUsageValue ?annualUsageValue .

  # Classification logic based on Pareto principle
  BIND(
    IF(?cumulativePercentage <= 80, "A",
    IF(?cumulativePercentage <= 95, "B", "C"))
    AS ?classification
  )
}
ORDER BY ?annualUsageValue DESC
