# Imports
from sqlalchemy import Table, Column, Integer, String, Date, MetaData, \
                       ForeignKey, create_engine, select, join
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relation, backref, join
import sqlalchemy.exc
import hashlib

# Create and map the table objects using declarative_base method.
Base = declarative_base()

class Person(Base):
    """
    This is the class for people. It defines the Person object as well
    as the person table using the sqlalchemy method declarative_base().

    """
    __tablename__ = "person"

    person_id = Column(Integer, primary_key=True)
    last_name = Column(String)
    first_name = Column(String)
    state_id = Column(String)
    agency_id = Column(String)
    username = Column(String, unique=True)
    password = Column(String)


    def __init__(self, last_name, first_name, state_id,
                 agency_id, username, password, role_id):
        """
        Inititialize person attributes.

        """
        self.last_name = last_name
        self.first_name = first_name
        self.state_id = state_id
        self.agency_id = agency_id
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()
        self.role_id = role_id

    def __repr__(self):
        return "<User('%s','%s', '%s', '%s', '%s', '%s', '%s')>" % \
               (self.last_name,
                self.first_name,
                self.state_id,
                self.agency_id,
                self.username,
                self.password,
                self.role_id)

class DOR(Base):
    """
    DOR is the class for daily observation reports, end of phase,
    and end of training reports.

    """
    __tablename__ = 'dors'

    report_id = Column(Integer, primary_key=True)
    date_observed = Column(Date)
    date_completed = Column(Date)
    fto = Column(Integer, ForeignKey('person.person_id'))
    trainee = Column(Integer, ForeignKey('person.person_id'))
    dor_number = Column(Integer)

    trainee_ = relation(Person, primaryjoin=trainee == Person.person_id)
    fto_ = relation(Person, primaryjoin=fto == Person.person_id)

    def __init__(self, date_observed, date_completed, fto, trainee,
                 dor_number):
        self.date_observed = date_observed
        self.date_completed = date_completed
        self.fto = fto
        self.trainee = trainee
        self.dor_number = dor_number

    def __repr__(self):
        return "<User('%s', '%s', '%s', '%s', '%s', '%s')>" % \
               (self.report_id,
                self.date_observed,
                self.date_completed,
                self.fto,
                self.trainee,
                self.dor_number)

def getReportList():

    """

    Return a list of tuples consisting of the record

    columns: dor_number, date, lastname, firstname


    """

    return session.query(DOR.date_observed,
                         DOR.dor_number,
                         Person.last_name,
                         Person.first_name,
                         Person.state_id).\
                             select_from(join
                                         (DOR,
                                          Person,
                                          DOR.trainee==
                                          Person.person_id)).all()

# Connect to database
engine = create_engine('sqlite:///sample.db', echo=True)

# Create the tables, if necessary, and create a new session
print 'Creating any new tables...'
Base.metadata.create_all(engine)

print 'Creating session...'

Session = sessionmaker(bind=engine)

session = Session()

result = getReportList()
print
print result

"""
Result I get:

List of tuples like -
(date_observed, dor_number, last_name, first_name, state_id)

    where the last_name and first_name is from dor.trainee

I would like -
(date_observed, dor_number, last_name, first_name, state_id,
last_name, first_name)

    where the second last_name, first_name is dor.fto

"""