"""SQLAlchemy definitions for the BioSQL database of biological items.

This provides a non-Seq-based interface to the BioSQL database through python.
This is useful if you have non-seq items to put into BioSQL, and should also
expose things not touched on with the Biopython BioSQL interface. Eventually
this would be a good target for merging.

http://www.biosql.org/wiki/Main_Page

Useful URLs for declarative style:
    https://www.bitbucket.org/stephane/model2/src/tip/transifex/model.py
    http://www.sqlalchemy.org/docs/05/sqlalchemy_ext_declarative.html
"""

def _initialize(engine):
    from sqlalchemy.orm import relation, mapper, dynamic_loader
    from sqlalchemy import MetaData, Table, Column, ForeignKey, Sequence
    from sqlalchemy import String, Unicode, Integer, DateTime, Float
    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()
    Base.metadata.bind = engine
    
    # -- Standard BioSQL tables
    
    class Biodatabase(Base):
        """Entry point to BioSQL databases.
        """
        __tablename__ = 'biodatabase'
        __table_args__ = {'mysql_engine':'InnoDB', 'autoload' : True}
        biodatabase_id = Column(Integer, primary_key = True)
        entries = relation("Bioentry", backref = "biodb")

    class Bioentry(Base):
        """The main bioentry object in BioSQL, containing a biological item.
        """
        __tablename__ = 'bioentry'
        __table_args__ = {'mysql_engine':'InnoDB', 'autoload' : True}
        bioentry_id = Column(Integer, primary_key = True)
        biodatabase_id = Column(Integer,
                ForeignKey('biodatabase.biodatabase_id'))
        qualifiers = relation("BioentryQualifierValue")
        parent_maps = relation("BioentryRelationship", primaryjoin =
          "Bioentry.bioentry_id == BioentryRelationship.object_bioentry_id")
        child_maps = relation("BioentryRelationship", primaryjoin =
          "Bioentry.bioentry_id == BioentryRelationship.subject_bioentry_id",
          order_by = "BioentryRelationship.object_bioentry_id.asc()")
    
    class Ontology(Base):
        """Defined a high level dictionary of ontology key terms.
        """
        __tablename__ = 'ontology'
        __table_args__ = {'mysql_engine':'InnoDB', 'autoload' : True}
        ontology_id = Column(Integer, primary_key = True)
    
    class Term(Base):
        """Explicitly describe terminology used in key/value pair relationships
        """
        __tablename__ = 'term'
        __table_args__ = {'mysql_engine':'InnoDB', 'autoload' : True}
        term_id = Column(Integer, primary_key = True)
        ontology_id = Column(Integer, ForeignKey('ontology.ontology_id'))
        ontology = relation("Ontology", backref = "terms")

    class BioentryQualifierValue(Base):
        """A key/value annotation pair associated with a Bioentry.
        """
        __tablename__ = 'bioentry_qualifier_value'
        __table_args__ = {'mysql_engine':'InnoDB', 'autoload' : True}
        bioentry_id = Column(Integer,
                ForeignKey('bioentry.bioentry_id'), primary_key = True)
        term_id = Column(Integer, ForeignKey('term.term_id'), primary_key = True)
        rank = Column(Integer, primary_key = True)
        term = relation("Term")

    class BioentryRelationship(Base):
        """Define a relationship between two bioentry objects.
        """
        __tablename__ = 'bioentry_relationship'
        __table_args__ = {'mysql_engine':'InnoDB', 'autoload' : True}
        object_bioentry_id = Column(Integer,
                ForeignKey('bioentry.bioentry_id'), primary_key = True)
        subject_bioentry_id = Column(Integer, 
                ForeignKey('bioentry.bioentry_id'))
        term_id = Column(Integer, ForeignKey('term.term_id'),
                primary_key = True)
        rank = Column(Integer, primary_key = True)
        
        term = relation("Term")
        parent = relation("Bioentry", primaryjoin = 
          "Bioentry.bioentry_id == BioentryRelationship.object_bioentry_id")
        child = relation("Bioentry", primaryjoin =
          "Bioentry.bioentry_id == BioentryRelationship.subject_bioentry_id")
    
    # -- Add-on module -- experiment tables

    class Experiment(Base):
        """Main high level description of an experiment.
        """
        __tablename__ = 'experiment'
        __table_args__ = {'mysql_engine':'InnoDB', 'autoload' : True}
        experiment_id = Column(Integer, primary_key = True)

        qualifiers = relation("ExperimentQualifierValue")
        parts = relation("ExperimentPart", backref = "experiment")
    
    class ExperimentQualifierValue(Base):
        """Add key/value qualifiers to describe an experiment.
        """
        __tablename__ = 'experiment_qualifier_value'
        __table_args__ = {'mysql_engine':'InnoDB', 'autoload' : True}
        experiment_id = Column(Integer,
                ForeignKey('experiment.experiment_id'), primary_key = True)
        term_id = Column(Integer, ForeignKey('term.term_id'),
                primary_key = True)
        rank = Column(Integer, primary_key = True)
        term = relation(Term)
    
    class ExperimentPart(Base):
        """Describe a sub-experiment within a high level experiment.

        Experiment parts allow a high level experiment to be divided into
        groupings like replicates.
        """
        __tablename__ = 'experiment_part'
        __table_args__ = {'mysql_engine':'InnoDB', 'autoload' : True}
        experiment_part_id = Column(Integer, primary_key = True)
        experiment_id = Column(Integer,
                ForeignKey('experiment.experiment_id'))
        results = relation("ExperimentResult", backref = "part")
    
    class ExperimentResult(Base):
        __tablename__ = 'experiment_result'
        __table_args__ = {'mysql_engine':'InnoDB', 'autoload' : True}
        result_id = Column(Integer, primary_key = True)
        experiment_part_id = Column(Integer,
                ForeignKey('experiment_part.experiment_part_id'))
        bioentry_id = Column(Integer, ForeignKey('bioentry.bioentry_id'))
        value_term_id = Column(Integer, ForeignKey('term.term_id'))

        bioentry = relation("Bioentry", backref = "results")
        term = relation(Term, primaryjoin =
          "ExperimentResult.value_term_id == Term.term_id")

    # ugly assignment of classes to the top level for use
    globals()['Biodatabase'] = Biodatabase
    globals()['Bioentry'] = Bioentry
    globals()['BioentryQualifierValue'] = BioentryQualifierValue
    globals()['Ontology'] = Ontology
    globals()['Term'] = Term
    globals()['BioentryRelationship'] = BioentryRelationship
    globals()['ExperimentQualifierValue'] = ExperimentQualifierValue
    globals()['ExperimentResult'] = ExperimentResult
    globals()['ExperimentPart'] = ExperimentPart
    globals()['Experiment'] = Experiment