import os, sys, datetime

import sqlalchemy
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Unicode
from sqlalchemy import DateTime
from sqlalchemy import Boolean
from sqlalchemy import String
from sqlalchemy.schema import ForeignKey

from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Query

Base = declarative_base()


class NpmTransactionManager():
    def __init__(self):
        print 'init', self

    def getTransaction(self, i__tcn=None, i__tot=None):
        l__query = NpmTransaction.query
        if i__tcn:
            l__query = l__query.filter(NpmTransaction.tr_tcn.in_([i__tcn]))
        if i__tot:
            l__query = l__query.filter(NpmTransaction.tr_type==i__tot)
        return l__query.all()


class NpmTransaction(Base):
    __tablename__ = 'transaction'
    tr_id = Column(Integer, primary_key=True)
    tr_creation = Column(DateTime, default=datetime.datetime.now())
    tr_update = Column(DateTime, default=datetime.datetime.now(), onupdate=datetime.datetime.now())
    tr_processing = Column(Boolean, default=False)
    tr_finished = Column(Boolean, default=False)
    tr_inerror = Column(Boolean, default=False)
    tr_type = Column(String(6))
    tr_tmpdir = Column(String(256))
    tr_tcn = Column(String(256))
    tr_extid = Column(String(256))
    m__messages = relationship('NpmMessage', backref='transaction')

    def addMessage(self):
        l__msg = NpmMessage()
        l__msg.m__transaction = self
        return l__msg

class NpmMessage(Base):
    __tablename__ = 'message'
    msg_id = Column(Integer, primary_key=True)
    tr_id = Column(Integer, ForeignKey('transaction.tr_id'))
    msg_tot = Column(String(6))
    msg_creation = Column(DateTime, default=datetime.datetime.now())
    msg_update = Column(DateTime, default=datetime.datetime.now(), onupdate=datetime.datetime.now())
    msg_processing = Column(Boolean, default=False)
    msg_finished = Column(Boolean, default=False)
    msg_inerror = Column(Boolean, default=False)
    m__transaction = relationship('NpmTransaction', backref='messages')
    m__messagedata = relationship('NpmMessageData', backref='message')
    m__procevent =  relationship('NpmProcEvent', backref='message')

    def addMessageData(self):
        l__msgdata = NpmMessageData()
        l__msgdata.m__message = self
        return l__msgdata

    def addProcEvent(self):
        l__procevent = NpmProcEvent()
        l__procevent.m__message = self
        return l__msgdata

class NpmMessageData(Base):
    __tablename__ = 'messagedata'
    mdt_id = Column(Integer, primary_key=True)
    msg_id = Column(Integer, ForeignKey('message.msg_id'))
    mdt_type = Column(String(256))
    mdt_value = Column(String(65000))   # 64k for serializing objects
    m__message = relationship('NpmMessage', backref='messagedata')

class NpmProcEvent(Base):
    __tablename__ = 'procevent'
    evt_id = Column(Integer, primary_key=True)
    msg_id = Column(Integer, ForeignKey('message.msg_id'))
    evt_type = Column(String(256))
    evt_start = Column(DateTime, default=datetime.datetime.now())
    evt_end = Column(DateTime, default=datetime.datetime.now())
    evt_endcode = Column(Boolean, default=False)
    evt_extradata = Column(String(65000))
    m__message = relationship('NpmMessage', backref='procevent')

def make_session():
    engine = sqlalchemy.create_engine('mysql://root:root@localhost/npm')
    #engine = sqlalchemy.create_engine('sqlite:///npmtesting.db')
    DBSession = scoped_session(sessionmaker(autoflush=True))
    DBSession.configure(bind=engine)
    #Base.metadata.bind = engine
    print Base.metadata.create_all(engine)
    Base.query = DBSession.query_property(Query)
    return DBSession

if __name__ == '__main__':
    # create the engine
    l__dbsession = make_session()
    l__dbsession2 = make_session()

    # create a transaction and manager
    l__tr = NpmTransaction()
    l__m = NpmTransactionManager()
    l__tcn = str(datetime.datetime.now())
    l__tr.tr_tcn = l__tcn
    l__tr.tr_processing = True
    l__tr.tr_type = 'XXX'
    l__tr.tr_extid = 'zob'
    # add a message
    print 'adding first msg for', l__tcn
    l__msg = l__tr.addMessage()
    l__msg.msg_tot = 'MSG1'
    # then all associated message data
    for l__cnt in range(5):
        l__md = l__msg.addMessageData()
        l__md.mdt_type = '3'
        l__md.mdt_value = 'xxxx'
    l__dbsession.add(l__tr)
    # commit data
    l__dbsession.commit()

    # get the previously inserted transaction
    l__transactions = l__m.getTransaction(i__tcn=l__tcn)
    print 'adding 2nd msg for', l__tcn
    l__msg = l__transactions[0].addMessage()
    l__msg.msg_tot = 'MSG2'
    # then all associated message data
    for l__cnt in range(5):
        l__md = l__msg.addMessageData()
        l__md.mdt_type = '4'
        l__md.mdt_value = 'yyyy'
    l__dbsession.commit()

    # get the previously inserted transaction
    l__transactions = l__m.getTransaction(i__tcn=l__tcn)
    print 'adding 3rd msg for', l__tcn
    l__msg = l__transactions[0].addMessage()
    l__msg.msg_tot = 'MSG3'
    # then all associated message data
    for l__cnt in range(5):
        l__md = l__msg.addMessageData()
        l__md.mdt_type = '4'
        l__md.mdt_value = 'yyyy'
    l__dbsession.commit()

    """ UNCOMMENT THIS BLOCK
    # get the previously inserted transaction
    l__transactions = l__m.getTransaction(i__tcn=l__tcn)
    print 'adding 3rd msg'
    l__msg = l__transactions[0].addMessage()
    l__msg.msg_tot = 'MSG3'
    # then all associated message data
    for l__cnt in range(5):
        l__md = l__msg.addMessageData()
        l__md.mdt_type = '4'
        l__md.mdt_value = 'zzzz'
    l__dbsession2.commit()
    """

    #l__result = l__m.getTransaction(i__tcn=l__tcn)
    #print l__result[0].m__messages