import collections
import datetime
import posixpath
from sqlalchemy.engine import create_engine

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String, Text, DateTime, Enum, Boolean
from sqlalchemy.orm import reconstructor
from sqlalchemy.orm import relationship, backref

Base = declarative_base()

_sessions = {}

def obtain_session(connection_string):
    """Gets or creates SqlAlchemy Session object for given connection string"""

    #Maybe we have a session already?
    if connection_string in _sessions.keys():
        return _sessions[connection_string]

    # an Engine, which the Session will use for connection resources
    engine = create_engine(connection_string)

    # create a configured "Session" class
    Session = sessionmaker(bind=engine)

    # create a Session
    session = Session()
    _sessions[connection_string] = session
    return session


class HierarchyElement(Base):
    """Represents detector hierarchy element. So it is something like part of bcal/hw/..."""
    __tablename__ = 'detector_hierarchy'

    id = Column(Integer, primary_key=True)
    name = Column(String(80), nullable=False)
    type = Column(String(40), nullable=False)
    parent_id = Column(Integer, ForeignKey('detector_hierarchy.id'), nullable=True)
    parent = relationship("HierarchyElement", remote_side=[id])
    children = relationship("HierarchyElement")
    channel_id = Column('chanid', Integer, ForeignKey('Channel.chanid'), nullable=True)
    channel = relationship("Channel", back_populates="elements")


class Crate(Base):
    """Represents a Crate unit"""
    __tablename__ = 'Crate'
    id = Column('crateid',Integer, primary_key=True)
    name = Column(String(45), nullable=False)

    modules = relationship("Module", cascade="all, delete, delete-orphan", back_populates="crate", order_by="Module.slot")


class Module(Base):
    """Represents a Module unit"""

    __tablename__ = 'Module'

    id = Column('moduleid', Integer, primary_key=True)

    crate_id = Column('crateid', Integer, ForeignKey('Crate.crateid'), nullable=False)
    crate = relationship("Crate", back_populates="modules")

    channels = relationship("Channel", cascade="all, delete, delete-orphan", back_populates="module")

    slot = Column(Integer, nullable=True)
    type = Column(String(45), nullable=True)
    serial = Column('SN', String(45), nullable=True)


class Channel(Base):
    """Represents a channel unit"""
    __tablename__ = 'Channel'

    id = Column('chanid', Integer, primary_key=True)

    module_id = Column ('moduleid', Integer, ForeignKey('Module.moduleid'), nullable=False)
    module = relationship("Module", back_populates="channels")

    elements = relationship("HierarchyElement", back_populates="channel")

    name = Column(String(45), nullable=True)
    channel = Column(Integer, nullable=True)
    system = Column(String(45), nullable=True)
    col_name = Column(String(45), nullable=True)
    enabled = Column('enable', Boolean, nullable=True)


#
#
#
#
# #--------------------------------------------
# # class CcdbSchemaVersion
# #--------------------------------------------
# class CcdbSchemaVersion(Base):
#     """
#     Represents CCDB directory object.
#     Directories may contain other directories or TypeTable objects
#     """
#     __tablename__ = 'schemaVersions'
#     id = Column(Integer, primary_key=True)
#     version = Column("schemaVersion", Integer)
#
#     def __repr__(self):
#         return "<CcdbSchemaVersion {0} version: '{1}'>".format(self.id, self.version)
#
#
# #--------------------------------------------
# # class Directory
# #--------------------------------------------
# class Directory(Base):
#     """
#     Represents CCDB directory object.
#     Directories may contain other directories or TypeTable objects
#     """
#     __tablename__ = 'directories'
#     id = Column(Integer, primary_key=True)
#     name = Column(String(255))
#     comment = Column(Text)
#     created = Column(DateTime, default=datetime.datetime.now)
#     modified = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
#     parent_id = Column('parentId', Integer)
#     author_id = Column('authorId', Integer, default=1)
#
#     def __init__(self):
#         self.path = ""
#         self.parent_dir = None
#         self.sub_dirs = []
#
#     @reconstructor
#     def on_load_init(self):
#         self.path = ""
#         self.parent_dir = None
#         self.sub_dirs = []
#
#     def __repr__(self):
#         return "<Directory {0} '{1}'>".format(self.id, self.name)
#
#
# #--------------------------------------------
# # class TypeTable
# #--------------------------------------------
# class TypeTable(Base):
#     __tablename__ = 'typeTables'
#     id = Column(Integer, primary_key=True)
#     name = Column(String(255))
#     comment = Column(Text)
#     created = Column(DateTime, default=datetime.datetime.now)
#     modified = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
#     parent_dir_id = Column('directoryId', Integer, ForeignKey('directories.id'))
#     parent_dir = relationship("Directory", backref=backref('type_tables', order_by=id))
#     constant_sets = relationship("ConstantSet", backref=backref('type_table'))
#     columns = relationship("TypeTableColumn",
#                            order_by="TypeTableColumn.order",
#                            cascade="all, delete, delete-orphan",
#                            backref=backref("type_table"))
#     rows_count = Column('nRows', Integer)
#     _columns_count = Column('nColumns', Integer)
#     author_id = Column('authorId', Integer, default=1)
#
#     @property
#     def columns_count(self):
#         """
#         :return: Number of columns of the table
#         :rtype: int
#         """
#         return self._columns_count
#
#     @property
#     def path(self):
#         """
#         :return: full path of the table
#         :rtype: str
#         """
#         return posixpath.join(self.parent_dir.path, self.name)
#
#     def __repr__(self):
#         return "<TypeTable {0} '{1}'>".format(self.id, self.name)
#
#
# #--------------------------------------------
# # class TypeTableColumn
# #--------------------------------------------
# class TypeTableColumn(Base):
#     __tablename__ = 'columns'
#     id = Column(Integer, primary_key=True)
#     name = Column(String(255))
#     comment = Column(Text)
#     created = Column(DateTime, default=datetime.datetime.now)
#     modified = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
#     order = Column(Integer)
#     type = Column('columnType', Enum('int', 'uint', 'long', 'ulong', 'double', 'string', 'bool'))
#     type_table_id = Column('typeId', Integer, ForeignKey('typeTables.id'))
#
#     @property
#     def path(self):
#         return posixpath.join(self.parent_dir.path, self.name)
#
#     def __repr__(self):
#         return "<TypeTableColumn '{0}'>".format(self.name)
#