""" I have followed the orm tutorial and I extended it a little , too see if I understand the relationship() part.

If someone could take a look at this and maby give me and example of how I use this in a session.add(User) and query it would be most helpful.
"""

"""Sqlalchemy imports"""
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import mapper, relationship, backref, sessionmaker
from sqlalchemy.dialects.mysql import DATETIME, TIMESTAMP

"""system imports"""
from datetime import datetime



"""load the databse"""
db = 'mysql+mysqldb://user:>password>@localhost/database'
engine = create_engine(db, echo=True)

Base = declarative_base()
metadata = Base.metadata


"""build a session"""
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()




class User(Base):
    """Registrate members
    """
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    nick = Column(String(50))
    password = Column(String(10))
    created = Column(TIMESTAMP())
    modified = Column(TIMESTAMP())
    active = Column(Integer)

    timezones = relationship('Timezone', order_by='Timezone.id')
    groups = relationship('Groups', order_by='Groups.id')
    userhosts = relationship('Userhosts', order_by='Userhosts.id')

    def __init__(self, nick, password, created, modified, timezoneGmt, country, active):
        self.nick = nick
        self.password = password
        self.created = created
        self.modified = modified
        self.active = active

    def __repr__(self):
        return "<User('%s', '%s', '%s', '%s', '%s', '%s', '%s')>" % (self.nick, self.password, self.created, self.modified, self.timezoneGmt, self.country,  self.active)


class Timezone(Base):
    """reason for this table is , we dont need to store timezones and countries twice or more"""
    __tablename__ = 'timezones'

    id = Column(Integer, primary_key=True)
    timezoneGmt = Column(String(3))
    country = Column(String(20))
    user_id = Column(Integer, ForeignKey('users.id'))

    def __init__(self, timezoneGmt, country):
        self.timezoneGmt = timezoneGmt
        self.country = country

    def __repr__(self):
        return "<Timezone('%s', '%s')>" % (self.timezoneGmt, self.country)



class Groups(Base):
    """Bind users.id to a group for define levels of security in ircbot.
    """
    __tablename__ = 'groups'

    id = Column(Integer, primary_key=True)
    group = Column(String(20))
    user_id = Column(Integer, ForeignKey('users.id'))

    def __init__(self, group):
        self.group = group

    def __repr__(self):
        return "<Groups('%s')>" % self.group




class Userhosts(Base):

    """A user in a ircserver will have more than one /userhost everytime a user get a new userhost they will have to do a login and new userhost will end up in this table. My thought were , this is the same as the Address table in the ormtutorial example"""

    __tablename__ = 'userhosts'

    id = Column(Integer, primary_key=True)
    host = Column(String(200))
    user_id = Column(Integer, ForeignKey('users.id'))

    def __init__(self, host):
        self.host = host

    def __repr__(self):
        return "<Userhosts('%s')>" % self.host



class Help(Base):

    __tablename__ = 'help'

    id = Column(Integer, primary_key=True)
    command = Column(String(20))
    argument = Column(String(20))
    helptext = Column(String(200))

    def __init__(self, command, argument, helptext):
        self.command = command
        self.argument = argument
        self.helptext = helptext

    def __repr__(self):
        return "<Help('%s', '%s', '%s')>" % (self.command, self.argument, self.helptext)