#coding:utf-8

import os
import sys
import sunhpc
import threading
import subprocess
import sqlalchemy
import sqlalchemy.exc
from sqlalchemy import create_engine
from sunhpc.db.mappings.base import *

threadlocal = threading.local()

class Database(object):
    """
    This class should proxy all the connection to the database.

    There are two main internal objects inside this class which come from
    sqlalchemy:

    - session: this is used by the ORM layer
    - connection: this is used by the execute statement, so every time
              you use pure sql

    These two objects have two separate DB connections, which means
    that DB status can be different when queried through them.
    
    Usage Example::

      db = Database()
      db.setVerbose()
      db.connect()
    """

    def __init__(self):
        self.conn       = None
        self.engine     = None
        self.verbose    = False
        self.results    = False
        self._dbPath    = "/opt/sunhpc/data"
        self._dbFile    = "sunhpc.db"
        self._dbLock    = ".database"
        self._datafile   = os.path.join(self._dbPath, self._dbFile)

    def setVerbose(self, verbose):
        """
        If the verbose is true all the sql will be printed to 
        stdout. This function must be called before the connect

        :type verbose: bool
        :param verbose: if verbose should be set to True

        """
        self.verbose = verbose

    def getDBFile(self):
        return self._datafile

    def connect(self):
        """
        It start the connection to the DB and create all the internal
        data structure
        """

        if 'SUNHPCDEBUG' in os.environ:
            self.setVerbose(True)

        url = 'sqlite:///' + self._dbPath + '/' + self._dbFile

        if self.verbose:
            import logging
            logging.basicConfig()
            logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)

        if self.verbose:
            print ("Database connection URL: ", url)

        self.engine   = create_engine(url)
        self.conn     = self.engine.connect()

    def reconnect(self):
        self.engine.dispose()
        self.conn = self.engine.connect()
        
    def getSession(self):
        session = getattr(threadlocal, "session", None)

        if session:
            return session
        elif self.engine:
            Session = sqlalchemy.orm.sessionmaker(bind=self.engine)
            session = Session()
            setattr(threadlocal, "session", session)
            return session
        else:
            return None

    def closeSession(self):
        session = getattr(threadlocal, "session", None)
        if session:
            session.close()
            setattr(threadlocal, "session", None)
            return
        return

    def commit(self):
        """
        Commit the current session if it existsi. *It does not touch the
        connection.*
        """
        session = self.getSession()
        if session:
            session.commit()
        else:
            pass

    def search(self, command):
        self.execute(command)
        rows = self.fetchall()
        self.execute(command)
        return len(rows)
        

    def execute(self, command):
        if self.conn:
            if '%' in command:
                command = command.replace('%', '%%')
            try:
                self.results    = self.conn.execute(command)
            except sqlalchemy.exc.OperationalError as e:
                self.renewConnection()
                self.results    = self.conn.execute(command)

            return self.results.rowcount
        else:
            return None

    
    def fetchone(self):
        """
        Fetch one row from the results of the previous query

        :rtype: tuple
        :return: a tuple containing the values of the fetched row.
                         It really returns a :class:`sqlalchemy.engine.result.RowProxy`
                         but it can be treated as a tuple
        """
        if self.results:
            return self.results.fetchone()
        return ()
    
    def fetchall(self):
        """
        Fetch all rows from the results of the previous query

        :rtype: list
        :return: a list of tuples containing the values of the fetched rows
        """
        if self.results:
            return self.results.fetchall()
        return ()
    
    def close(self):
        """
        It closes the connection only. You also need to close the
        session, if you want to release all the DB resources 
        :meth:`closeSession`
        """
        if self.results:
            self.results.close()
            self.results = None
        if self.conn:
            self.conn.close()

    def renewConnection(self):
        """
        It renews the connection, if inactive for few hours mysql
        closes down the connection, so you might need to renew it.
        """
        self.close()
        self.conn = self.engine.connect()


if __name__ == "__main__":
    d = Database()
    d.connect()
    conn = d.getSession()
    print (conn)
    
    qry = conn.query(Node)
    print (qry.all())












