"""
File defining classes to handle the table in the iotlab dedicated database.
"""

# from sqlalchemy import create_engine
# from sqlalchemy.orm import sessionmaker
# from sfa.util.config import Config
# from sfa.util.sfalogging import logger
from sfa.storage.model import Base, AlchemyObj
from sqlalchemy import Column, Integer, String
# from sqlalchemy import Table, MetaData
# # from sqlalchemy.ext.declarative import declarative_base

# # from sqlalchemy.dialects import postgresql

# from sqlalchemy.exc import NoSuchTableError


#Dict holding the columns names of the table as keys
#and their type, used for creation of the table
slice_table = {'record_id_user': 'integer PRIMARY KEY references X ON DELETE \
                CASCADE ON UPDATE CASCADE', 'oar_job_id': 'integer DEFAULT -1',
               'record_id_slice': 'integer', 'slice_hrn': 'text NOT NULL'}

#Dict with all the specific iotlab tables
# tablenames_dict = {'lease_table': slice_table}


# TestbedBase = declarative_base()


# class LeaseTableXP (TestbedBase):
# class LeaseTableXP (Base,AlchemyObj):
class LeaseTableXP (Base):
    """ SQL alchemy class to manipulate the rows of the slice_iotlab table in
    lease_table database. Handles the records representation and creates the
    table if it does not exist yet.

    """
    __tablename__ = 'lease_table'

    slice_hrn = Column(String)
    experiment_id = Column(Integer, primary_key=True)
    end_time = Column(Integer, nullable=False)

    def __init__(self, slice_hrn=None, experiment_id=None,  end_time=None):
        """
        Defines a row of the slice_iotlab table
        """
        if slice_hrn:
            self.slice_hrn = slice_hrn
        if experiment_id:
            self.experiment_id = experiment_id
        if end_time:
            self.end_time = end_time

    def __repr__(self):
        """Prints the SQLAlchemy record to the format defined
        by the function.
        """
        result = "<lease_table : slice_hrn = %s , experiment_id %s end_time = %s" \
            % (self.slice_hrn, self.experiment_id, self.end_time)
        result += ">"
        return result


# class TestbedAdditionalSfaDB(object):
#     """ SQL Alchemy connection class.
#     From alchemy.py
#     """
#     # Stores the unique Singleton instance-
#     _connection_singleton = None
#     # defines the database name
#     dbname = "testbed_xp"

#     class Singleton:
#         """
#         Class used with this Python singleton design pattern to allow the
#         definition of one single instance of iotlab db session in the whole
#         code. Wherever a conenction to the database is needed, this class
#         returns the same instance every time. Removes the need for global
#         variable throughout the code.
#         """

#         def __init__(self, config, debug=False):
#             self.testbed_engine = None
#             self.testbed_session = None
#             self.url = None
#             self.create_testbed_engine(config, debug)
#             self.session()

#         def create_testbed_engine(self, config, debug=False):
#             """Creates the SQLAlchemy engine, which is the starting point for
#             any SQLAlchemy application.
#             :param config: configuration object created by SFA based on the
#             configuration file in /etc
#             :param debug: if set to true, echo and echo pool will be set to true
#             as well. If echo is True, all statements as well as a repr() of
#             their parameter lists to the engines logger, which defaults to
#             sys.stdout. If echo_pool is True, the connection pool will log all
#             checkouts/checkins to the logging stream. A python logger can be
#             used to configure this logging directly but so far it has not been
#             configured. Refer to sql alchemy engine documentation.

#             :type config: Config instance (sfa.util.config)
#             :type debug: bool

#             """

#             if debug is True:
#                 l_echo_pool = True
#                 l_echo = True
#             else:
#                 l_echo_pool = False
#                 l_echo = False
#              # the former PostgreSQL.py used the psycopg2 directly and was doing
#             #self.connection.set_client_encoding("UNICODE")
#             # it's unclear how to achieve this in sqlalchemy, nor if it's needed
#             # at all
#             # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
#             # we indeed have /var/lib/pgsql/data/postgresql.conf where
#             # this setting is unset, it might be an angle to tweak that if need
#             # be try a unix socket first
#             #  - omitting the hostname does the trick
#             unix_url = "postgresql+psycopg2://%s:%s@:%s/%s" \
#                 % (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
#                    config.SFA_DB_PORT, TestbedAdditionalSfaDB.dbname)

#             # the TCP fallback method
#             tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s" \
#                 % (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
#                     config.SFA_DB_HOST, config.SFA_DB_PORT, TestbedAdditionalSfaDB.dbname)

#             for url in [unix_url, tcp_url]:
#                 try:
#                     self.testbed_engine = create_engine(
#                         url, echo_pool=l_echo_pool, echo=l_echo)
#                     self.check()
#                     self.url = url
#                     return
#                 except:
#                     pass
#                 self.testbed_engine = None

#             raise Exception("Could not connect to database")

#         def check(self):
#             """ Check if a table exists by trying a selection
#             on the table.

#             """
#             self.testbed_engine.execute("select 1").scalar()


#         def session(self):
#             """
#             Creates a SQLalchemy session. Once the session object is created
#             it should be used throughout the code for all the operations on
#             tables for this given database.

#             """
#             if self.testbed_session is None:
#                 Session = sessionmaker()
#                 self.testbed_session = Session(bind=self.testbed_engine)
#             return self.testbed_session

#         def close_session(self):
#             """
#             Closes connection to database.

#             """
#             if self.testbed_session is None:
#                 return
#             self.testbed_session.close()
#             self.testbed_session = None


        # def update_experiments_in_additional_sfa_db(self,
        #     experiment_list_from_testbed, experiment_list_in_db):
        #     """ Cleans the iotlab db by deleting expired and cancelled jobs.

        #     Compares the list of experiment ids given by the testbed with the
        #     experiment ids that are already in the database, deletes the
        #     experiments that are no longer in the testbed experiment id list.

        #     :param  experiment_list_from_testbed: list of experiment ids coming
        #         from testbed
        #     :type experiment_list_from_testbed: list
        #     :param experiment_list_in_db: list of experiment ids from the sfa
        #         additionnal database.
        #     :type experiment_list_in_db: list

        #     :returns: None
        #     """
        #     #Turn the list into a set
        #     set_experiment_list_in_db = set(experiment_list_in_db)

        #     kept_experiments = set(experiment_list_from_testbed).intersection(set_experiment_list_in_db)
        #     logger.debug("\r\n \t update_experiments_in_additional_sfa_db \
        #                     experiment_list_in_db %s \r\n \
        #                     experiment_list_from_testbed %s \
        #                     kept_experiments %s "
        #                  % (set_experiment_list_in_db,
        #                   experiment_list_from_testbed, kept_experiments))
        #     deleted_experiments = set_experiment_list_in_db.difference(
        #         kept_experiments)
        #     deleted_experiments = list(deleted_experiments)
        #     if len(deleted_experiments) > 0:
        #         self.testbed_session.query(LeaseTableXP).filter(LeaseTableXP.experiment_id.in_(deleted_experiments)).delete(synchronize_session='fetch')
        #         self.testbed_session.commit()
        #     return

    # def __init__(self, config, debug=False):
    #     self.sl_base = TestbedBase

    #      # Check whether we already have an instance
    #     if TestbedAdditionalSfaDB._connection_singleton is None:
    #         TestbedAdditionalSfaDB._connection_singleton = \
    #             TestbedAdditionalSfaDB.Singleton(config, debug)

    #     # Store instance reference as the only member in the handle
    #     self._EventHandler_singleton = \
    #         TestbedAdditionalSfaDB._connection_singleton

    # def __getattr__(self, aAttr):
    #     """
    #     Delegate access to implementation.

    #     :param aAttr: Attribute wanted.
    #     :returns: Attribute
    #     """
    #     return getattr(self._connection_singleton, aAttr)



    # # def __setattr__(self, aAttr, aValue):
    # #     """Delegate access to implementation.

    # #      :param attr: Attribute wanted.
    # #      :param value: Vaule to be set.
    # #      :return: Result of operation.
    # #      """
    # #     return setattr(self._connection_singleton, aAttr, aValue)

    # def exists(self, tablename):
    #     """
    #     Checks if the table specified as tablename exists.
    #     :param tablename: name of the table in the db that has to be checked.
    #     :type tablename: string
    #     :returns: True if the table exists, False otherwise.
    #     :rtype: bool

    #     """
    #     metadata = MetaData(bind=self.testbed_engine)
    #     try:
    #         table = Table(tablename, metadata, autoload=True)
    #         return True

    #     except NoSuchTableError:
    #         logger.log_exc("SLABPOSTGRES tablename %s does not exist"
    #                        % (tablename))
    #         return False

    # def createtable(self):
    #     """
    #     Creates all the table sof the engine.
    #     Uses the global dictionnary holding the tablenames and the table schema.

    #     """

    #     logger.debug("IOTLABPOSTGRES createtable \
    #                 TestbedBase.metadata.sorted_tables %s \r\n engine %s"
    #                  % (TestbedBase.metadata.sorted_tables, self.testbed_engine))
    #     TestbedBase.metadata.create_all(self.testbed_engine)
    #     return