#################################################################
# 1.   We will be dealing with exchange rates.  The exchange rate
# conversion table will be in Python, as a dictionary 
# containing (from_sym, to_sym) as keys, Decimal objects as values:

from decimal import Decimal

symbols = ('usd', 'gbp', 'cad', 'eur', 'aud')
currency_lookup = dict(
            ((currency_from, currency_to), Decimal(str(rate)))
            for currency_to, values in zip(
                symbols,
                [
                    (1, 1.59009, 0.988611, 1.37979, 1.02962),
                    (0.628895, 1, 0.621732, 0.867748, 0.647525),
                    (1.01152, 1.6084, 1, 1.39569, 1.04148),
                    (0.724743, 1.1524, 0.716489, 1, 0.746213),
                    (0.971228, 1.54434, 0.960166, 1.34009, 1),
                ])
            for currency_from, rate in zip(symbols, values)
    )

# 1a. With the above table, we'd convert 5000 USD to AUD:
print currency_lookup[("usd", "aud")] * 5000


#############################################################
# 2. an Amount object represents a value amount
# and a particular currency symbol.  Math and comparisons
# can be performed against two Amount objects where
# the exhange rate for each is used to "normalize" operations.
# This provides a facade for our "currency_lookup" dictionary.

from sqlalchemy import literal

class Amount(object):
    def __init__(self, amount, currency):
        self.currency = currency
        self.amount = amount

    def __add__(self, other):
        return Amount(
                self.amount + 
                other.as_currency(self.currency).amount,
                self.currency
            )

    def __sub__(self, other):
        return Amount(
                self.amount -
                other.as_currency(self.currency).amount,
                self.currency
            )

    def __lt__(self, other):
        return self.amount < other.as_currency(self.currency).amount

    def __gt__(self, other):
        return self.amount > other.as_currency(self.currency).amount

    def __eq__(self, other):
        return self.amount == other.as_currency(self.currency).amount

    def as_currency(self, other_currency):
        return Amount(
                    currency_lookup[(self.currency, other_currency)] * self.amount,
                    other_currency)

    def __clause_element__(self):
        # helper method for SQLAlchemy to interpret
        # the Amount object as a SQL element
        if isinstance(self.amount, (float, int, Decimal)):
            return literal(self.amount)
        else:
            return self.amount

    def __str__(self):
        return "%2.4f %s" % (self.amount, self.currency)

# 2a. Convert 5000 US dollars into Canadian
print Amount(5000, "usd").as_currency("cad")

# 2b. Using the Amount object, we can calculate 5000 USD + 1000 USD:
print Amount(5000, "usd") + Amount(1000, "usd")

# 2c. We can calculate 5000 USD + 500 GBP:
print Amount(5000, "usd") + Amount(500, "gbp")

# 2d. We can take the above and convert to CAD:
print (Amount(5000, "usd") + Amount(500, "gbp")).as_currency("cad")


###############################################################
# 3. We create an "account" database that will store amounts 
# in USD.   The "Amount" object will be used as the public
# interface for the "balance".  We use a @hybrid to expose this.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Numeric
from sqlalchemy.ext.hybrid import hybrid_property
Base = declarative_base()

class BankAccount(Base):
    __tablename__ = 'bank_account'
    id = Column(Integer, primary_key=True)

    _balance = Column('balance', Numeric)

    @hybrid_property
    def balance(self):
        return Amount(self._balance, "usd")

    @balance.setter
    def balance(self, value):
        self._balance = value.as_currency("usd").amount

    def __repr__(self):
        return "BankAccount(%s)" % self.balance

# 3a. given an account, we can operate on "balance" 
# at the Python level, using Amount objects
# as value objects:

account = BankAccount(balance=Amount(4000, "usd"))

# 3b. print balance in usd
print account.balance

# 3c. print balance in gbp
print account.balance.as_currency("gbp")

# 3d. perform currency-agnostic comparisons, math
print account.balance > Amount(500, "cad")
print account.balance + Amount(500, "cad") - Amount(50, "eur")

###############################################################
# 4.  We will now persist the account in a database.  We can then
# query using the "BankAccount" value object interchangeably, passing
# both a SQL expression as "amount" as well as a numeric value.

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy import func

engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)

session = Session(engine)

# 4a. persist four accounts.  amounts here 
# are converted to USD in Python.
session.add_all([
    account,
    BankAccount(balance=Amount(10000, "cad")),
    BankAccount(balance=Amount(5700, "usd")),
    BankAccount(balance=Amount(89682, "aud")),
])
session.commit()

# 4b. balances are stored in USD, but we can query for 
# the account that has 10000 in CAD using as_currency:
print session.query(BankAccount).\
    filter(BankAccount.balance == Amount(10000, "cad")).one()

# alternatively we can do the calc on the DB side.  
print session.query(BankAccount).\
    filter(BankAccount.balance.as_currency("cad") > Amount(9999, "cad")).\
    filter(BankAccount.balance.as_currency("cad") < Amount(10001, "cad")).\
    one()

print session.query(BankAccount).\
    filter(BankAccount.balance.as_currency("cad") > BankAccount.balance.as_currency("eur")).\
    all()

# 4c. query all amounts, converting to "CAD" on the DB side
# (calling .amount here because query() doesn't yet call __clause_element__())
print session.query(BankAccount.balance.as_currency("cad").amount).all()

# 4d. average balance in EUR
print session.query(func.avg(BankAccount.balance.as_currency("eur"))).scalar()