"""
 Some answers to questions.txt ,
 using SqlSoup and some raw sql.

 Jim M | Oct 31 2012
"""

from sqlalchemy import *
from sqlalchemy.orm import backref
from sqlalchemy.ext.sqlsoup import SqlSoup

db = SqlSoup('sqlite:///planet_express.db')

# -- Define short names for the SqlSoup objects for each of the tables

Shipment = db.Shipment
Planet = db.Planet
Employee = db.Employee
Client = db.Client
HasClearance = db.Has_Clearance
Package = db.Package

# -- Set up additional object fields that return SqlSoup objects
#    for some of the relations between tables. (The data within
#    each table is already accessible this way, e.g. Employee.name .)

#    Shipment.manager, Employee.shipments
Shipment.relate('manager', Employee, backref=backref("shipments"))

#    Shipment.planet, Planet.shipments
Shipment.relate('planet', Planet, backref=backref("shipments"))

#    Employee.planets_delivered_to, Planet.shipment_managers
Employee.relate('planets_delivered_to', Planet,
                 secondary=Shipment._table, backref=backref("shipment_managers"))

#    HasClearence.employee, Employee.clearances
HasClearance.relate('employee', Employee, backref=backref("clearances"))

#    HasClearence.planet, Planet.clearances
HasClearance.relate('planet', Planet, backref=backref("clearances"))

#    Employee.planets_with_clearances, Planet.employees_with_clearance
Employee.relate('planets_with_clearances', Planet,
                 secondary=Shipment._table, backref=backref("employees_with_clearance"))

#    Package.shipment, Shipment.packages
Package.relate('shipment', Shipment, backref=backref("packages"))

#    Package.sender, Client.packages_sent
Package.relate('sender', Client, 
               primaryjoin=Package.sender_id==Client.account_number,
               backref=backref("packages_sent"))

#    Shipment.senders, Client.shipments_sent
Shipment.relate('senders', Client, 
                secondary=Package._table, 
                secondaryjoin=Package.sender_id==Client.account_number,
                backref=backref("shipments_sent"))

#    Package.recipient, Client.packages_received
Package.relate('recipient', Client, 
               primaryjoin=Package.recipient_id==Client.account_number,
               backref=backref("packages_received"))

#    Shipment.recipients, Client.shipments_received
Shipment.relate('recipients', Client, 
                secondary=Package._table, 
                secondaryjoin=Package.recipient_id==Client.account_number,
                backref=backref("shipments_received"))

# -- OK, now with all that in place, on to the questions.

# 1. Who receieved a 1.5kg pckage?
pkg_15 = Package.filter_by(weight=1.5).one()
guy = pkg_15.recipient
print "A package with weight {} was sent to {}.".format(
    pkg_15.weight, guy.name)

# 2. What is the total weight of all his sent packages?
print "The total weight he sent is {}.".format(
    sum((x.weight for x in guy.packages_sent)))

# 3. Who has managed those shipments?
print "His shipments are managed by {}.".format(
    ", ".join([x.manager.name for x in guy.shipments_sent]))


# 4. Add to the database a 100.3kg package sent by Ray Bradbury to Leo
#    Wong, the day after Leo last got something. Of course Ray would
#    choose the person with the most experience to handle the delivery.

# So this one is much more involved, with several intermediate steps.

# First, we need the two clients who send and recieve it.
# One we fetch, the other needs to be created.
#
#   -- aside on creating, modifying, and saving things --
#
#   I'm assuming the database is in its state as created with
#   initialize_db, i.e. that this script or other hasn't modified it.
#   If that assumption is incorrect, and these changes are added to
#   the database on top of similar earlier changes, then with the
#   current naive code there will be multiple "Ray Bradbury"s in the
#   database.
#
#   See the "Advanced Use" section of 
#   http://docs.sqlalchemy.org/en/rel_0_6/orm/extensions/sqlsoup.html
#   for a discussion of flushing and commiting. By default,
#   SqlSoup sets autoflush=True, autocommit=False .
#     autoflush = True
#       means that every query does a flush first, so that the queries
#       in the running program give results consistent with previous changes.
#       (However, raw sql stuff isn't consistent until a db.flush())
#     autocommit = False
#       means that changes have not yet been made permanent
#       in the database. This is a "transaction" approach.
#       Changes can be udone with db.rollback() or finalized with db.commit().
#
#   A better version of the code below would be to implement a
#   find_or_create function (like Rails' ActiveRecord has) of some
#   sort, first Class.filter(...).one() to try fetching it, and if
#   that fails, then creating it, instead of assuming which records
#   are or aren't already in the database. But for now I'm ignoring this.
#
# OK, back to business.

leo = Client.filter_by(name="Leo Wong").one()  # Fetch a object.
ray = Client(name="Ray Bradbury")              # Create a object.
db.flush()                                     # Add new stuff to transaction.

# OK, next, find the busiest shipper.
print "Looking for busiest"
template = "  {:<12}  {:<12} {:<48}"
print template.format("shipments", "mass", "name")
print template.format("-"*12, "-"*12, "-"*48)
for e in Employee.all():
    mass = sum([p.weight for s in e.shipments for p in s.packages])
    print template.format(len(e.shipments), mass, e.name)

# And here's an explicit request for the employee with the most shipments.
busiest = max([(len(e.shipments), e) for e in Employee.all()])[1]
print "Python says the busiest is {}".format(busiest.name)

# Just for fun, here's what the last two queries look like using raw
# SQL through the db object.  If there were many records, using MAX()
# could be *much* faster than fetching 'em all and letting Python look
# for the biggest.

print "Raw SQL version of table of (number of shipments, client name) :"
result = db.execute("""
   SELECT COUNT(1) as "shipments", e.name as "name"
     FROM Employee as e
       INNER JOIN Shipment as s ON e.employee_id = s.manager_id
     GROUP BY e.employee_id
   ;     
""")
for row in result:
    print "  {:<12}  {:<48} ".format(row["shipments"], row["name"])

result = db.execute("""
SELECT MAX(shipments) as shipments, name FROM (
   SELECT COUNT(1) as "shipments", e.name as "name"
     FROM Employee as e
       INNER JOIN Shipment as s ON e.employee_id = s.manager_id
     GROUP BY e.employee_id
);
""")
print "Raw SQL says the busiest is {}".format(result.fetchone()["name"])

# Now on to the date that Leo last received something.
print "Here are Leo's shipments: "
leo = Client.filter(Client.name.like("%Leo%")).one()
print leo.shipments_received
# Oops : his single shipment is undated.

# Well, let's just take the last date that anything was shipped then.
last_date = max([s.date for s in Shipment.all()])
print "The last date anything shipped was " + last_date

# We can ask sqlite to get the date one week after that,
# since sqlite understands some date manipulations (for dates as "YYYY-MM-DD");
# see http://www.sqlite.org/lang_datefunc.html .
result = db.execute("SELECT date('" + last_date + "', '+7 day');")
shipment_date = result.fetchone()[0]
print "One week after that is  " + shipment_date

# Now we just need to create a new shipment,
# and a new package to go it in.
# Since the problem didn't say, I'll make it a package of books.
# The planet is also unspecied; so let's just pick Mars.
mars = Planet.filter_by(name = "Mars").one()
new_shipment = Shipment(date = shipment_date,
                        manager = busiest,
                        planet = mars)
new_package = Package(shipment = new_shipment,
                      package_number = 1,
                      contents = "books",
                      weight = 100.3,
                      sender = ray,
                      recipient = leo)
db.flush()  # add recent changes to the current transaction.

# And we should be all set,
# *except* for commiting (finalizing) this transaction.

# Let's check explicitly with some raw SQL.
result = db.execute("""
 SELECT p.contents, p.weight, s.date,
       Planet.name as planet, sender.name as sender, recipient.name as recipent
    FROM Package as p
      INNER JOIN Shipment as s on p.shipment_id = s.shipment_id
      INNER JOIN Planet on s.planet_id = Planet.planet_id
      INNER JOIN Client as sender on sender.account_number = p.sender_id
      INNER JOIN Client as recipient on recipient.account_number=p.recipient_id
    WHERE p.weight = 100.3;
""")
print "And here's the verification of that new data : "
print "columns: " + str(result.keys())
print "values : " + str(result.fetchall()[0])

if __name__ == "__main__":
    print
    choice = raw_input("Should these changes be written to the database? (y/n) ")   
    if choice[0] == 'y':
        db.commit()
        print "OK, the database on the disk has been modified."
    else:
        db.rollback()
        print "OK, the database on the disk has been left unchanged."

# So there you are.