from pymongo import MongoClient
# pprint library is used to make the output look more pretty
from pprint import pprint

mongodb_url = "mongodb://localhost:27017"

# connect to MongoDB
client = MongoClient(mongodb_url)
db=client.Northwind
# Issue the serverStatus command and print the results
serverStatusResult=db.command("serverStatus")
#pprint(serverStatusResult)

# NOTE: The code is really well commented, each step is explained in some details.
# This would not be done for code in production but this is a code used to illustrate learning
# and therefore we decided explain each step in details. Of course this makes the code less
# readable but it should make it easier to understand the flow and the actions taken by each step

orders = db["order-details"].aggregate([
        # lookup is used to outer join the order_details and the orders tables
        {
            "$lookup":
                {
                    "from" : "orders",
                    "localField" : "OrderID",
                    "foreignField" : "OrderID",
                    "as" : "order"
                }
        },
        # Only get records that have CustomerID = ALFKI
        { "$match" : { "order.CustomerID":"ALFKI" } },
        # Group records together by OrderId
        # and when grouping increment a orderID counter by one for each record that is combined
        { "$group" : {"_id":"$OrderID", "OrderID_counter":{"$sum":1}} },
        # Use match to filter out the lines which contain orderID counter greater than (gt) 1
        {
            "$match": {
                "OrderID_counter": {
                    "$gt": 1
                }
            }
        },
        # Lookup is used to outer join the order-details table with the result from previous operations
        {
            "$lookup":
                {
                    "from" : "order-details",
                    "localField" : "_id",
                    "foreignField" : "OrderID",
                    "as" : "order"
                }
        },
        # Here unwind deconstructs the array field "order" created by the previous lookup
        # and outputs a document for each element in the order array
        { "$unwind" : "$order" },
        # Use project to extract the OrderID and the ProductID information for each document
        { "$project" : {"OrderId":"$_id", "ProductID":"$order.ProductID", "_id":0} },
        # Lookup is used to outer join the product table with the result from previous operations
        # this is done to get the product name for each product ID
        {
            "$lookup":
                {
                    "from" : "products",
                    "localField" : "ProductID",
                    "foreignField" : "ProductID",
                    "as" : "productinfo"
                }
        },
        # Here unwind deconstructs the array field "productinfo" created by the previous lookup
        # and outputs a document for each element in the "productinfo" array
        { "$unwind" : "$productinfo" },
        # Finally the project operation extracts only the information that is relevant for the wanted result
        { "$project" : {"OrderId":1, "ProductID":1, "ProductName":"$productinfo.ProductName"} },
    ])

# Print the result in a nice way
print "Results for exercise 4.2 MongoDB"
print
print "{OrderID: <10} {ProductID: <12} {ProductName: <14}".format(
    OrderID = "Order ID",ProductID = "Product ID", ProductName = "Product Name")
print "----------------------------------------"
counter = 0
for order in orders:
    counter += 1
    print "{OrderID: <10} {ProductID: <12} {ProductName: <14}".format(
        OrderID = order['OrderId'],ProductID = order['ProductID'], ProductName = order['ProductName'].encode('utf-8'))

print
print "Total number of lines: {}".format(counter)