#################################################################
# 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.ext.hybrid import hybrid_method
from sqlalchemy import type_coerce

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

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

    @as_currency.expression
    def as_currency(self, target, other_currency):
        if hasattr(target, '__clause_element__'):
            target = target.__clause_element__()
        currency = target.type.currency
        return Amount(
                    currency_lookup[(currency, other_currency)]
                     * type_coerce(target, Numeric),
                    other_currency)

    def __str__(self):
        try:
            amt = int(self.amount)
            return "%2.4f %s" % (self.amount, self.currency)
        except TypeError:
            return "%s %s" % (self.amount, self.currency)

    def __repr__(self):
        return "Amount(%s)" % self

# 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 a "bank_account" table that will store amounts 
# in USD.   We will use a new type AmountType to coerce
# Amount objects on the Python side to numeric objects
# on the database side, and back.

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


class AmountType(TypeDecorator):
    impl = Numeric

    def __init__(self, currency):
        self.currency = currency
        super(AmountType, self).__init__()

    def process_bind_param(self, value, dialect):
        if value is not None:
            value = value.as_currency(self.currency).amount
        return value

    def process_result_value(self, value, dialect):
        if value is not None:
            value = Amount(value, self.currency)
        return value


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

    balance = Column('balance', AmountType("usd"))

    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, where the Amount
# object normalizes within __eq__():
print session.query(BankAccount).\
    filter(BankAccount.balance == Amount(10000, "cad")).one()

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

# 4d. we can query the column directly now with the ORM, get back Amount values.
print session.query(BankAccount.balance).all()

# 4e. works just as well at the SQL expression level

from sqlalchemy import select
bank_account = BankAccount.__table__
for amount, in session.execute(
            select([bank_account.c.balance]).\
            where(Amount.as_currency(bank_account.c.balance, "cad") > Amount(9999, "cad"))
        ).fetchall():
    print "Amount:", amount, "As USD:", amount.as_currency("usd"), "As CAD:", amount.as_currency("cad")


# 4f. query all amounts, converting to "CAD" on the DB side
print session.query(Amount.as_currency(BankAccount.balance, "cad").amount).all()

# 4g. average balance in EUR
print session.query(func.avg(Amount.as_currency(BankAccount.balance, "eur").amount)).scalar()