import logging
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
import datetime
import appconfig as config

from orangescape.model.server.sql.SqlUtil import getManyToOneName, getPKName
from orangescape.model.server.sql import getMappedClass, Session
from orangescape.model.server.AbstractSession import AbstractSession
from orangescape.model.server.wrapper.Model import DynamicModel
from orangescape.model.server.db.DbError import MappingError, DB00001, DB00002
from sqlalchemy import or_, and_

#from orangescape.tests.mock.MockMemcache import cache
from google.appengine.api import memcache as cache

MAX = 'max'
MIN = 'min'
SUM = 'sum'
AVG = 'avg'
COUNT = 'count'
oper_map = {'=':'__eq__', '>':'__gt__', '<':'__lt__', '>=':'__ge__', '<=':'__le__', '!=':'__ne__', 'like':'like', 'IN':'in_', 'in':'in_', 'or':or_, 'and':and_}

class SQLSession(AbstractSession):

    def __init__(self, application):
        self.__sql_session__ = Session()
        self.readCache = {}
        self.one2many_cache={}
        AbstractSession.__init__(self, application)

    def getMappedClass(self, metadata):
        return getMappedClass(metadata)

    def getSysMappedClass(self, metadata):
        return getSysMappedClass(metadata)

    def createDataObject(self, metadata, keyname):
        metadata_class = self.getMappedClass(metadata)
        pk = keyname or self.__generate_pk__(metadata)
        dataObj = metadata_class(pk)
        dataObj.setSQLSession(self)
        return dataObj

    def readFromDatastore(self, sheetId, metadata):
        if not sheetId:
            return None
        entity = self.memcacheGet(sheetId)
        if not entity:
            metadata_class = self.getMappedClass(metadata)
            entity=None
            from sqlalchemy.exc import ResourceClosedError
            try:
                st = datetime.datetime.now()
#                logging.info('SQLSession :: Read from ds for %s %s'%(sheetId, metadata.getSheetName()))
                entity = self.__sql_session__.query(metadata_class).get(sheetId)
                if not entity:
                    return None
                self.memcacheSet(sheetId, entity)
            except (ResourceClosedError, IndexError), e:
                logging.error('Error while reading data for the model %s with the id %s. Error %s'%(metadata.getSheetName(), sheetId, e))
#        else:
#            logging.info('READ:: entity %s(%s)exists in cache'%(metadata.getSheetName(), sheetId))
        if entity:
            entity.setSQLSession(self)
        return entity

    def getUser(self):
        return self.user

    def getQueryCacheKey(self, foreignKeyValue, referenceCell):
        return ''.join([foreignKeyValue, '.', referenceCell.getSheetId(), '.', referenceCell.getRowMetadata().getSheetMetadata().getSheetId()])

    def removeFromCache(self, id):
        if self.one2many_cache.has_key(id):
            del self.one2many_cache[id]

    def getListFromDatastore(self, model, referenceCell):
        cacheKey = self.getQueryCacheKey(model.getSheetId(), referenceCell)
        if self.one2many_cache.has_key(cacheKey):
#            logging.info('Getlist from cache for %s %s'%(model.getSheetId(), referenceCell.getSheetMetadata().getSheetName()))
            return self.one2many_cache[cacheKey]
#        logging.info('Getlist from ds for %s %s'%(model.getSheetId(), referenceCell.getRowMetadata().getSheetMetadata().getSheetName()))
        childMetadata = referenceCell.getRowMetadata().getSheetMetadata()
        self.attach_by_metadataId(childMetadata.getSheetId())
        childList={}
        baseQuery = {(referenceCell.getSheetId(), '='):model.getSheetId()}
        for sessionChild in self.queryFromDataStore(childMetadata, baseQuery, 1000):
            childList[sessionChild.getSheetId()] = sessionChild
        self.detach_all()
        self.one2many_cache[cacheKey] = childList
        return childList

    def applyFilter(self, criteria, metadata, baseQuery, orderBy=None, isDescending=False):
        metadata_class = self.getMappedClass(metadata)
        for f in baseQuery:
            if f[1]=='=':
                criteria = criteria.filter(getattr(metadata_class,f[0])==(baseQuery[f] or None))
            elif f[1]=='>':
                criteria = criteria.filter(getattr(metadata_class,f[0])>baseQuery[f])
            elif f[1]=='<':
                criteria = criteria.filter(getattr(metadata_class,f[0])<baseQuery[f])
            elif f[1]=='<=':
                criteria = criteria.filter(getattr(metadata_class,f[0])<=baseQuery[f])
            elif f[1]=='>=':
                criteria = criteria.filter(getattr(metadata_class,f[0])>=baseQuery[f])
            elif f[1]=='!=':
                criteria = criteria.filter(getattr(metadata_class,f[0])!=baseQuery[f])
            elif f[1]=='like':
                criteria = criteria.filter(getattr(metadata_class,f[0]).like(self.getNullCheckedString(baseQuery[f])))
            elif f[1]=='in':
                criteria = self.addInFilter(criteria, getattr(metadata_class,f[0]), baseQuery[f])
        if orderBy:
            if isDescending:
                criteria = criteria.order_by(getattr(metadata_class, orderBy).desc())
            else:
                criteria = criteria.order_by(getattr(metadata_class, orderBy))
        return criteria

    def addInFilter(self, criteria, lhs, values):
        if len(values)>0:
            return criteria.filter(lhs.in_(values))
        else:
            return criteria.filter(lhs.in_(['need to be removed']))
            self.__logger.info('the size of value list for IN filter is 0. Ignoring it from the query criteria.')

    def getNullCheckedString(self, oldValue):
        if oldValue:
            return '%'+str(oldValue)+'%'
        else:
            return '%'

    def prepare_result_set(self, rs):
        return map(lambda e:(self.readCache.get(e.getSheetId()) or e).setSQLSession(self), rs)

    def queryFromDataStore(self, metadata, baseQuery, limit=None, offset=0, orderBy=None, isDescending=False):
        metadata_class = self.getMappedClass(metadata)
        self.attach_by_metadataId(metadata.getSheetId())
        criteria = self.__sql_session__.query(metadata_class)
#        criteria =  self.applyAdvFilter(metadata_class, criteria, baseQuery, orderBy, isDescending)
        criteria = self.applyFilter(criteria, metadata, baseQuery, orderBy, isDescending)
        if limit is None:
            limit=1000
        queryResultlist=criteria[offset:offset+limit]
        self.detach_all()
#        logging.info('Query from ds for %s %s'%(metadata.getSheetName(), baseQuery))
        return self.prepare_result_set(queryResultlist)

    def applyAdvFilter(self, metadata_class, criteria, baseQuery, orderBy=None, isDescending=False):
        if baseQuery:
            f = self.constructFilter(metadata_class, baseQuery)
            if str(f): criteria = criteria.filter(f)
        if orderBy:
            if isDescending:
                criteria = criteria.order_by(getattr(metadata_class, orderBy).desc())
            else:
                criteria = criteria.order_by(getattr(metadata_class, orderBy))
        logging.debug('Query: %s'%(criteria))
        return criteria

    def constructFilter(self, metadata_class, baseQuery):
        bool_operator, filter_params = baseQuery
        f = list()
        for tup in filter_params:
            if len(tup)==2:
                nested = self.constructFilter(metadata_class, tup)
                if str(nested): f.append(nested)
            else:
                lhs, operator, rhs = tup
                if operator.lower()=='like':
                    rhs = self.getNullCheckedString(rhs)
                f.append(getattr(getattr(metadata_class, lhs), oper_map[operator])(rhs))
        return oper_map[bool_operator](*tuple(f))

    def adv_query_from_ds(self, metadata, baseQuery, limit=None, offset=0, orderBy=None, isDescending=False):
#        logging.info('Adv Query from ds for %s %s'%(metadata.getSheetName(), baseQuery))
        metadata_class = self.getMappedClass(metadata)
        criteria = self.__sql_session__.query(metadata_class)
        criteria = self.applyAdvFilter(metadata_class, criteria, baseQuery, orderBy, isDescending)
        if limit is None: limit = 1000
        queryResultlist = criteria[offset:offset+limit]
        self.detach_all()
#        session_list = queryResultlist
#        for sessionObject in session_list:
#            sessionObject.setSQLSession(self)
        return self.prepare_result_set(queryResultlist)

    def report_query(self, metadata, limit, baseQuery, properties, orderBy = None, isDescending = False, offset=0, inMemorySorting=False):
#        logging.info('Report query from ds for %s %s'%(metadata.getSheetName(), baseQuery))
        metadata_class = self.getMappedClass(metadata)
        columnList = map(lambda x: getattr(metadata_class, metadata.getPublicCellMetadata(x).getSheetId()), properties)
        criteria = self.__sql_session__.query(*(columnList))
#        criteria = self.applyFilter(criteria, metadata, baseQuery, orderBy, isDescending)
        criteria = self.applyAdvFilter(metadata_class, criteria, baseQuery, orderBy, isDescending)
        if limit is None:
            limit=1000
        rs = criteria[offset:offset+limit]
        rows = map(lambda tup:list(tup), rs)
        return rows

    def checkWithMemcache(self, metadataIdDict, dbResults = None):
        if not metadataIdDict:
            return dict()
        cachedEntities = self.memcacheGetMulti(metadataIdDict.keys())
        for id in metadataIdDict:
            entity = cachedEntities.get(id)
            if entity:
                entity.setSQLSession(self)
            else:
                cachedEntities[id] = self.readFromDatastore(id, self.getApplication().getSheetMetadataById(metadataIdDict[id]))
        return cachedEntities

    def getMultiFromDatastore(self, metadataIdDict):
#        logging.info('GetMulti from ds for %s'%(metadataIdDict))
        return self.checkWithMemcache(metadataIdDict)

    def count(self, metadata, baseQuery):
        metadata_class = self.getMappedClass(metadata)
        criteria = self.__sql_session__.query(getattr(metadata_class, getPKName(metadata)))
#        criteria = self.applyFilter(criteria, metadata, baseQuery)
        criteria = self.applyAdvFilter(metadata_class, criteria, baseQuery)
        return criteria.count()

    def __get_projections__(self, metadata, metadataClass, measures, dimensions, operations):
        from sqlalchemy.sql.expression import func
        manyToOneCells = {}
        groupByList = []
        measureList = []
        outerJoinList = {}

        def get_manyToOne_property(cellName):
            refCellName, fetchCellName = cellName.split('.')

            if manyToOneCells.has_key(refCellName):
                refCell, manyToOneMetadata = manyToOneCells[refCellName]
            else:
                refCell = metadata.getPublicCellMetadata(refCellName)
                manyToOneMetadata = refCell.getReferredSheetMetadata()
                manyToOneCells[refCellName] = (refCell, manyToOneMetadata)
                outerJoinList[refCellName] = getattr(metadataClass, refCell.getSheetId())
#                outerJoinList[refCellName] = getattr(metadataClass, getManyToOneName(refCell))

            manyToOneClass = self.getMappedClass(manyToOneMetadata)
            fetchCell = manyToOneMetadata.getPublicCellMetadata(fetchCellName)
            return getattr(manyToOneClass, fetchCell.getSheetId())

        for i in xrange(len(dimensions)):
            cellName = dimensions[i]
            if cellName.find('.')>0:
                groupByList.append(get_manyToOne_property(cellName))
            else:
                cell = metadata.getPublicCellMetadata(cellName)
                if cell.isReferenceCell():
                    manyToOneMetadata = cell.getReferredSheetMetadata()
                    manyToOneClass = self.getMappedClass(manyToOneMetadata)
                    outerJoinList[cellName] = getattr(metadataClass, cell.getSheetId())
#                    outerJoinList[cellName] = getattr(metadataClass, getManyToOneName(cell))
                    groupByList.append(getattr(manyToOneClass, getPKName(manyToOneMetadata)))
                else:
                    groupByList.append(getattr(metadataClass, cell.getSheetId()))

        execute_operations = {MIN:lambda cellid:func.min(cellid), MAX:lambda cellid:func.max(cellid),
                              SUM:lambda cellid:func.sum(cellid), AVG:lambda cellid:func.avg(cellid),
                              COUNT:lambda cellid:func.count(cellid)
                              }

        for i in xrange(len(measures)):
            operation = operations[i]
            cellName = measures[i]
            if cellName.find('.')>0:
                measureList.append(execute_operations[operation](get_manyToOne_property(cellName)))
            else:
                cell = metadata.getPublicCellMetadata(cellName)
                if cell.isReferenceCell():
                    manyToOneMetadata = cell.getReferredSheetMetadata()
                    manyToOneClass = self.getMappedClass(manyToOneMetadata)
                    outerJoinList[cellName] = getattr(metadataClass, cell.getSheetId())
                    measureList.append(execute_operations[operation](getattr(manyToOneClass, getPKName(manyToOneMetadata))))
                else:
                    measureList.append(execute_operations[operation](getattr(metadataClass, cell.getSheetId())))

        return tuple(groupByList), tuple(measureList), outerJoinList

    def aggregate_query(self, metadata, limit, baseQuery, measures, dimensions, operations, orderBy = None, isDescending = False, offset=0):
        from sqlalchemy import func
        if limit is None:
            limit = 2000
        metadata_class = self.getMappedClass(metadata)
        groupByArgs, measuresArgs, outerJoins = self.__get_projections__(metadata, metadata_class, measures, dimensions, operations)
        criteria = self.__sql_session__.query(*(groupByArgs+measuresArgs))

#        criteria = self.applyFilter(criteria, metadata, baseQuery, orderBy, isDescending)
        criteria = self.applyAdvFilter(metadata_class, criteria, baseQuery, orderBy, isDescending)
#        criteria = criteria.outerjoin(*tuple(outerJoins.values()))
        for refCellName in outerJoins:
            refCell = metadata.getPublicCellMetadata(refCellName)
            pClass = self.getMappedClass(refCell.getReferredSheetMetadata())
            criteria = criteria.filter(getattr(metadata_class, refCell.getSheetId()) == getattr(pClass, getPKName(refCell.getReferredSheetMetadata())))

        rs = criteria.group_by(*groupByArgs)[offset:offset+limit]
        rows = map(lambda tup:list(tup), rs)
        return rows

    def commit_datastore(self):

        deletedIds = {}
        for modelDict in self.deletedSheets.values():
            for id, model in modelDict.iteritems():
                deletedIds[id] = id
                self.__sql_session__.delete(model.getDataObject())

        self.commitAttachments()
        dataDict = dict()
        for metadataId, instanceDict in self.updatedSheets.iteritems():
            for id, instance in instanceDict.iteritems():
                instance.getDataObject().setSQLSession(None)
                dataDict[id] = instance.getDataObject()
                self.attach(instance.getDataObject())
        try:
            self.__sql_session__.commit()
            self.memcacheSetMulti(dataDict)
            self.memcacheDeleteMulti(deletedIds)
            self.clearCache()
        except Exception, e:
            logging.info('Error while commit %s. Rolling back transaction'%(e))
            self.clearCache()
            self.__sql_session__.rollback()
            raise

    def detach(self, entity):
        self.__sql_session__.expunge(entity)

    def attach(self, entity):
        self.__sql_session__.add(entity)

    def detach_all(self):
        self.__sql_session__.expunge_all()

    def attach_all(self):
        for metadataId in self.updatedSheets:
            self.attach_by_metadataId(metadataId)

    def attach_by_metadataId(self, metadataId):
        dirtyEntities = self.updatedSheets.get(metadataId)
        if dirtyEntities:
            for instanceId, instance in dirtyEntities.iteritems():
                self.attach(instance.getDataObject())

    def getORMMetadata(self):
        return __orm_metadata__

    def getAttachmentClass(self):
        from orangescape.model.server.sql import getAttachmentClass
        return getAttachmentClass()

    def getAttachmentRecord(self, id):
        return self.__sql_session__.query(self.getAttachmentClass()).get(id)

    def commitAttachments(self):
        for id, content in self.attachmentCache['created'].iteritems():
            attachmentObj = self.getAttachmentClass()(id = id, content=content)
            self.__sql_session__.add(attachmentObj)

        for id, content in self.attachmentCache['updated'].iteritems():
            attachment = self.getAttachmentRecord(id)
            if attachment is None:
                attachmentObj = self.getAttachmentClass()(id = id)
            attachment.content=content
            self.__sql_session__.add(attachment)

        for id in self.attachmentCache['deleted'].keys():
            attachment = self.getAttachmentRecord(id)
            if attachment:
                self.__sql_session__.delete(attachment)

    def getAttachmentFromDS(self, id):
        attachment = self.getAttachmentRecord(id)
        if attachment:
            return attachment.content
        return None

    def memcacheGet(self, key):
        data = self.readCache.get(key)
        if data is None:
            data = cache.get(key)
            if data is not None:
                self.readCache[key] = data
        return data

    def memcacheSet(self, key, value):
        self.detach(value)
        self.readCache[key] = value
        return cache.set(key, value, time=config.CACHE_EXPIRATION)

    def memcacheGetMulti(self, keyList):
        dataDict = self.getDictMulti(keyList)
        if len(dataDict) > 0:
            if len(dataDict) == len(keyList):
                return dataDict
            else:
                tempList = [sheetId for sheetId in keyList if not dataDict.__contains__(sheetId)]
                cacheData = cache.get_multi(tempList)
                for k, v in cacheData.iteritems():
                    self.readCache[k] = v
                    dataDict[k] = v
        else:
            dataDict = cache.get_multi(keyList)
            self.setDictMulti(dataDict)
        return dataDict

    def memcacheSetMulti(self, mapping):
        self.setDictMulti(mapping)
        failedKeys = cache.set_multi(mapping, time=config.CACHE_EXPIRATION)
        if failedKeys:
            logging.error('Failed to update in memcache keys %s out of %s. %s'%(len(mapping), len(failedKeys), failedKeys))
            faildData = {}
            for key in failedKeys:
                faildData[key] = mapping.get(key)
            while not cache.delete_multi(faildData):
                logging.warn('memcache Delete multi failed')
            for key, entity in faildData.iteritems():
                if not cache.set(key, entity, time=config.CACHE_EXPIRATION):
                    logging.error('Re-try failed for updating memcache for the id %s'%(key))

    def memcacheDeleteMulti(self, mapping):
        self.deleteDictMulti(mapping.keys())
        return cache.delete_multi(mapping)

    def getDictMulti(self, keyList):
        localDict = dict()
        for key in keyList:
            obj = self.readCache.get(key)
            if obj is not None:
                localDict[key]=obj
        return localDict

    def setDictMulti(self, mapping):
        for key,val in mapping.iteritems():
            self.readCache[key]=val

    def deleteDictMulti(self, keyList):
        for key in keyList:
            if self.readCache.__contains__(key):
                del self.readCache[key]

