# 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
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()),
)

# 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),
)