# coding: utf-8

"""
    Database schema
"""

from sqlalchemy import *

#
# Object Mapping
# (used externally)
#

# for ORM definitions; outside of this module
from sqlalchemy.orm import mapper, sessionmaker, relation, backref
from sqlalchemy.orm import eagerload, eagerload_all

# used by Application.session
session_factory = sessionmaker()

#
# Table Schema
#

# our schema
schema = MetaData()

# customers are organizations or natural persons who are serving as the renting side of an order
customers = Table('customers', schema,
        Column('id', Integer, primary_key=True),

        # organization or person name (may match with contact name)
        Column('name', Unicode, nullable=False, unique=True),

        # free-form internal notes
        Column('notes', Unicode),

        # raw CREATE/UPDATE timestamp
        Column('created_at', DateTime, nullable=False, default=func.now()),
        Column('updated_at', DateTime, nullable=True, onupdate=func.current_timestamp()),
)

# contacts are phone/email contact details for named persons in organizations who are active in some order
contacts = Table('contacts', schema,
        Column('id', Integer, primary_key=True),

        # reference to contact being primary for specified customer (organization or themselves)
        Column('customer_id', None, ForeignKey('customers.id'), nullable=True),

        # person's natural name
        Column('name', Unicode, nullable=True),

        # freeform telephone number
        Column('phone', String, nullable=True),

        # freeform email address
        Column('email', String, nullable=True),

        # raw CREATE/UPDATE timestamp
        Column('created_at', DateTime, nullable=False, default=func.now()),
        Column('updated_at', DateTime, nullable=True, onupdate=func.current_timestamp()),
)

# orders are the transactions that some customer/contact makes, and involve checking out some inventory for some
# duration
orders = Table('orders', schema,
        Column('id', Integer, primary_key=True),

        # ordering customer, if entered (i.e. who to bill)
        Column('customer_id', None, ForeignKey('customers.id'), nullable=True),

        # active contact at customer side for order (i.e. who to call)
        Column('contact_id', None, ForeignKey('contacts.id'), nullable=True),

        # short description of customer event where these items are going
        Column('event_name', Unicode, nullable=True),

        # longer description of what the event is
        Column('event_description', Unicode, nullable=True),
        
        # rough duration of event; time interval during which items are reserved
        Column('event_start', DateTime),
        Column('event_end', DateTime),
        
        # raw CREATE/UPDATE timestamp
        Column('created_at', DateTime, nullable=False, default=func.now()),
        Column('updated_at', DateTime, nullable=True, onupdate=func.current_timestamp()),
)

# static inventory items
items = Table('items', schema,
        Column('id', Integer, primary_key=True),

        # item is part of a larger collection
        Column('parent_id', None, ForeignKey('items.id'), nullable=True),

        # short name
        Column('name', Unicode, nullable=False),

        # longer description
        Column('detail', Unicode, nullable=True),

        # total quantity, or NULL for uncounted items
        Column('quantity', Integer, nullable=True),
)

# items included in order
order_items = Table('order_items', schema,
        # order we are part of
        Column('order_id', None, ForeignKey('orders.id'), primary_key=True),
        
        # which item is selected
        Column('item_id', None, ForeignKey('items.id'), primary_key=True),

        # how many out of total quantity
        Column('quantity', Integer, nullable=True),
)

## editable contract terms for customer order
## provision for standard terms, or more specific ones for certain customers
#contract_terms = Table('contract_terms', schema,
#        Column('id', Integer, primary_key=True),
#
#        # short description
#        Column('name', Unicode, nullable=False),
#
#        # full terms in formatted code
#        Column('terms', Unicode, nullable=False),
#)