from sqlobject  import \
     SQLObject, UnicodeCol, StringCol, CurrencyCol, IntCol, \
     DateTimeCol, BoolCol, \
     ForeignKey, MultipleJoin, RelatedJoin, DatabaseIndex

from turbogears.database import PackageHub
#from datetime import datetime

# the TurboGears "package" hub to manage connections / transactions
hub = PackageHub("gears2go")
__connection__ = hub

class Category(SQLObject):
    """ A Category can have many Products """
    name = UnicodeCol(length=80, title="Category", alternateID=True)
    description = UnicodeCol(length=255, title="Description", alternateID=True)
    parent = ForeignKey('Category', default=None)
    subcategories = MultipleJoin('Category', joinColumn='parent_id')
    products = MultipleJoin('Product')

    def _get_ancestors(self):
        ancestor = self.parent
        while ancestor:
            yield ancestor
            ancestor = ancestor.parent

class Product(SQLObject):
    """ A Product can have many Items """
    name = UnicodeCol(length=80, alternateID=True)
    description = UnicodeCol(length=255, varchar=True, alternateID=True)
    category = ForeignKey('Category')
    items = MultipleJoin('Item')

class Item(SQLObject):
    """ an item in the Store """
    sku = UnicodeCol(length=10, alternateID=True)
    list_price = CurrencyCol(notNone=True)
    unit_cost = CurrencyCol(notNone=True)
    status = UnicodeCol(length=2)
    attr1 = UnicodeCol(length=80, title="attribute 1")
    attr2 = UnicodeCol(length=80, title="attribute 2")
    attr3 = UnicodeCol(length=80, title="attribute 3")
    product = ForeignKey('Product')

class Inventory(SQLObject):
    item = ForeignKey('Item', alternateID=True)
    quantity = IntCol(notNone=True)

# your Shopping Cart, sir

class Order(SQLObject):
    """ an order can have many line-items """
    items = MultipleJoin('OrderItem', joinColumn='order_id')
    odate = DateTimeCol(title="Date")
    status = UnicodeCol(length=2)
    user_id = UnicodeCol(length=80, alternateID=True)

    class sqlmeta:
        table = 'orders'
    
class OrderItem(SQLObject):
    """ a line-item in an order """
    order_id = ForeignKey('Order', alternateID=True)
    item = ForeignKey('Item', alternateID=True)
    quantity = IntCol(notNone=True)