from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, Boolean, Date, UniqueConstraint
from sqlalchemy import create_engine
from sqlalchemy.orm import relation, scoped_session, sessionmaker
import datetime
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('sqlite:///t.db', echo=True)
Session = scoped_session(sessionmaker(transactional=True, autoflush=True, bind=engine))
metadata = MetaData(bind=engine)
Base = declarative_base(metadata=metadata)

class PairKeyword(Base):
    __tablename__ = "pair_keyword"
    id         = Column('id', Integer, primary_key=True)
    keyword_id = Column('keyword_id', ForeignKey('keyword.keyword_id'))
    pair_id    = Column('pair_id', ForeignKey('pair.pair_id'))

    constraint = UniqueConstraint('keyword_id', 'pair_id')

class PairAnnotation(Base):
    __tablename__ = "pair_annotation"
    id            = Column('id', Integer, primary_key=True)
    annotation_id = Column('annotation_id', ForeignKey('annotation.annotation_id'))
    pair_id       = Column('pair_id', ForeignKey('pair.pair_id'))

    constraint    = UniqueConstraint('annotation_id', 'pair_id')


class Keyword(Base):
    __tablename__ = "keyword"
    keyword_id = Column('keyword_id', Integer, primary_key=True)
    keyword    = Column('keyword', String(30), unique=True)


class Annotation(Base):
    __tablename__ = "annotation"
    annotation_id = Column('annotation_id', Integer, primary_key=True)
    annotation    = Column('annotation', String(30), unique=True)


class Feature(Base):
    __tablename__ = "feature"
    feature_id   = Column('feature_id', Integer, primary_key=True)
    feature_type = Column('feature_type', String(24), index=True)
    pair_id      = Column('pair_id', Integer, ForeignKey('pair.pair_id'))
    name         = Column('name', String(40), index=True)
    q_or_s       = Column('q_or_s', String(1))
    chromosome   = Column('chromosome', String(15), index=True)
    start        = Column('start', Integer, index=True)
    stop         = Column('stop',  Integer, index=True)
    strand       = Column('strand',  Integer)

class Pair(Base):
    __tablename__ = "pair"
    pair_id      = Column('pair_id', Integer, primary_key=True)
    orientation  = Column('orientation', String(20))
    date_created = Column('date_created', Date(default=datetime.date.today))
    # pair has_a genespace...
    # pair has a feature.
    genespace    = relation('Genespace', uselist=False, backref="pair")
    features    = relation(Feature,  uselist=True,  backref="pair")


Pair.keywords  = relation(Keyword
        , secondary=PairKeyword.__table__
        , primaryjoin=Pair.__table__.c.pair_id==PairKeyword.__table__.c.pair_id
        , secondaryjoin=Keyword.__table__.c.keyword_id==PairKeyword.__table__.c.keyword_id)

Pair.annotations = relation(Annotation
        , secondary=PairAnnotation.__table__
        , primaryjoin=Pair.__table__.c.pair_id==PairAnnotation.__table__.c.pair_id
        , secondaryjoin=Annotation.__table__.c.annotation_id==PairAnnotation.__table__.c.annotation_id)

    #keywords    = relation(

class Genespace(Base):
    __tablename__ = "genespace"
    genespace_id = Column('genespace_id', Integer, primary_key=True)
    created_by   = Column('created_by', String(20))
    revisit      = Column('revisit', Boolean)
    qdups        = Column('qdups', Integer),
    sdups        = Column('sdups', Integer),
    pair_id      = Column('pair_id', ForeignKey('pair.pair_id'))
    #Column('annotation_id', Integer, ForeignKey('annotation.annotation_id))
    # keywords


keywords = ('bigfoot', 'appressed', 'duplicate hsps', 'special')
annos    = ('missed exon(s)', 'false exon(s) called', 'splits and fusions')
                #, 'created new Os loc' , 'created new Sb loc' , 'created new pair')


Base.metadata.create_all()

if __name__ == "__main__":

    from sqlalchemy.exceptions import IntegrityError

    for k in keywords:
        k = Keyword(keyword=k)
        Session.save(k)
    for a in annos:
        a = Annotation(annotation=a)
        Session.save(a)
    Session.commit()


    try:
        k = Keyword(keyword='bigfoot')
        Session.save(k)
        Session.commit()
    except IntegrityError:
        print "UniqueConstraint effective"
        #Session.delete(k)
        Session.clear()
        Session.rollback()

    p = Pair()
    p.features.append(Feature())
    p.features.append(Feature())
    g = Genespace()
    p.genespace = g
    a1 = Session.query(Annotation).get_by(annotation='splits and fusions')
    a2 = Session.query(Annotation).get_by(annotation='missed exon(s)')
    print a1.annotation
    print a2.annotation
    p.annotations.append(a2)
    p.annotations.append(a1)
    print dir(p)
    Session.save(p)
    Session.commit()
    print [a.annotation for annotation in p.annotations]
    # ['splits and fusions', 'splits and fusions']