from orangescape.model.server.AbstractSession import AbstractSession
from orangescape.model.server.wrapper.Model import DynamicModel
from orangescape.model.server.Context import getApplication

from com.orangescape.entity import DbMediatorService
from com.orangescape.entity import Constants
from orangescape.model.server.db.Data import PrimaryData
from orangescape.log import logging
from orangescape.model.server.db.dbUtil import criteriaWalker
from orangescape.model.server.db import DbError
from orangescape.model.server.util.SerializeUtil import getDisplayCellName

from org.hibernate import Criteria
from org.hibernate.criterion import Restrictions, InExpression, Order, Projections
from java.util import HashMap
import uuid

MAX = 'max'
MIN = 'min'
SUM = 'sum'
AVG = 'avg'
COUNT = 'count'
oper_map = {'=':Restrictions.eq, '>':Restrictions.gt, '<':Restrictions.lt, '>=':Restrictions.ge, '<=':Restrictions.le,'!=':Restrictions.ne,
            'like':Restrictions.like, 'IN':Restrictions.in, 'in':Restrictions.in,'or':Restrictions.disjunction, 'and':Restrictions.conjunction}

class DbSession(AbstractSession,DbMediatorService):

    __logger = logging.getLogger('DbSession.py')

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

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

    def generateUUID(self):
        return str(uuid.uuid1()).replace('-', '_')

    def createDataObject(self, metadata, keyname, sessionData=None):
        data = None
        if sessionData is None:
            uid=str(uuid.uuid1()).replace('-', '_')
            if keyname is None:
                from random import randint
                hackId = str(randint(11111,99999))# Not Required
                if len(metadata.getSheetName())<8:
                    key_length = len(metadata.getSheetName())+1
                else:
                    key_length=9
                keyname = metadata.getSheetName()[0:key_length-1]+'_'+uid
            data = PrimaryData(self)
            data.setSheetId(keyname)
            data.setSheetMetadataId(metadata.getSheetId())
            data.setApplicationId(metadata.getApplication().getSheetId())
            sheetName = metadata.getSheetName()
            self.save(sheetName,data)
#            self.__logger.warn('creating primary data for %s with id %s '%(metadata.getSheetId(),data.getSheetId))
        else:
            data=PrimaryData(self,sessionData)
            data.setSheetMetadataId(metadata.getSheetId())
            data.setApplicationId(metadata.getApplication().getSheetId())
#            self.__logger.warn('generating primary data for %s with id %s '%(metadata.getSheetId(),data.getSheetId))
        return data

    def getListFromDatastore(self, model, referenceCell):
        cache_key = model.getSheetId()+'.'+referenceCell.getCellName()+'.'+referenceCell.getSheetMetadata().getSheetName()
        if self.one2many_cache.get(cache_key):
            return self.one2many_cache[cache_key]
        criteria = self.hibernateSession.createCriteria(referenceCell.getSheetMetadata().getSheetName())
        criteria.add(Restrictions.eq(referenceCell.getSheetId(), model.getDataObject().getSessionData()))
        criteria.setCacheable(True)
        sessionList = self.executeQuery(criteria)
        dbList = {}
        for session_data in sessionList:
            entity = self.createDataObject(referenceCell.getSheetMetadata(), None, session_data)
            dbList[entity.getSheetId()] = entity

        self.one2many_cache[cache_key] = dbList
        return dbList

    FUNCTION_NAME_MAPPING = {
                             'DSUM':'sum',
                             'DCOUNT':'count',
                             'DCOUNTA':'count',
                             'DCONCATENATE':'concat',
                             'DMIN':'min',
                             'DMAX':'max'
                             }

    FUNCTION_EMPTYLIST_MAPPING = {
                             'DSUM':0,
                             'DCOUNT':0,
                             'DCOUNTA':0,
                             'DCONCATENATE':'',
                             'DMIN':0,
                             'DMAX':0
                             }

    def executeAggregateFunction(self, et, sheet, referenceCell, aggNode, fnNode, criteria, orderBy, reverse):
        ref_name = referenceCell.getCellName()+'_'+referenceCell.getSheetId()
        childList=DbMediatorService.getListFromDatastore(self,sheet.getSheetMetadata().getSheetName(),ref_name,sheet.getSheetId())
        if childList is None:
            return self.FUNCTION_EMPTYLIST_MAPPING.get(fnNode.getNodeValue())
        filterString=self.constructQueryFilterString(fnNode.getNodeValue(),aggNode.getCellMetadata().getSheetId(),criteria,orderBy,reverse)
        self.hibernateSession.flush()
        aggQuery=self.hibernateSession.createFilter(childList, filterString);
        return aggQuery.list().get(0)

    def constructQueryFilterString(self,aggFunctionName,cellId,criteriaNode,orderBy,reverse):
        queryStringBuffer = list()
        sql_function_name=self.FUNCTION_NAME_MAPPING.get(aggFunctionName)
        if sql_function_name is None:
            raise NotImplementedError('SQL function not available for name "%s"'%(aggFunctionName))
        queryStringBuffer.append('select %s(%s) '%(sql_function_name,cellId))
        if criteriaNode is not None:
            whereClause = criteriaWalker(None,criteriaNode)
        if orderBy:
            queryStringBuffer.append(' order by').append(orderBy)
        if reverse:
            queryStringBuffer.append(' desc')
        return "".join(queryStringBuffer)

    def readFromDatastore(self,sheetId, metadata):
        if sheetId:
            sessiondata = self.getFromDatastore(metadata.getSheetName(),sheetId)
        else:
            raise DbError('Id to restore "%s" record from database is None'%(metadata.getSheetName()))
        if sessiondata is not None:
            data = PrimaryData(self,sessiondata)
            data.setSheetMetadataId(metadata.getSheetId())
            data.setApplicationId(metadata.getApplication().getSheetId())
            return data
        else:
            return None

    def getMultiFromDatastore(self, metadataIdDict):
        instances = {}
        application = getApplication()
        for instanceId in metadataIdDict.keys():
            metadataId = metadataIdDict[instanceId]
            sheetMetadata = application.getSheetMetadataById(metadataId)
            instance=self.readFromDatastore(instanceId,sheetMetadata)
            instances[instanceId]=instance
        return instances

    def createModel(self, metadata, keyName):
        metadataId = metadata.getSheetId()
        data = self.createDataObject(metadata,keyName)
        data.setSheetMetadataId(metadataId)
        data.setApplicationId(self.applicationId)
        model = DynamicModel(data, metadata, self)
        sheets = self.updatedSheets.get(metadataId)
        if sheets is None:
            sheets = dict()
            self.updatedSheets[metadataId] = sheets
        sheets[model.getSheetId()] = model
        return model

    def getUser(self):
        return self.user

#    def query(self, metadata, limit=2000, baseQuery=None, criteriaNode=None, orderBy = None, isDescending = False, offset=0):
#        import appconfig as config
#        if config.env != config.SL and config.LOG_LEVEL==config.DEBUG or PERFMONITOR_ENABLED:
#            baseQueryStr = self.getQS(metadata, baseQuery, '')
#            orderByStr = ''
#            if orderBy:
#                orderByStr = 'ORDER BY %s'%(metadata.getCellMetadataById(orderBy).getCellName())
#                if isDescending:
#                    orderByStr+=' desc'
#            if config.env != config.SL and config.LOG_LEVEL==config.DEBUG:
#                self.__logger.debug('Query: SELECT * FROM %s WHERE %s %s'%(metadata.getSheetName(), baseQueryStr, orderByStr))
#            if PERFMONITOR_ENABLED:
#                pm = PerfMonitor("DbSession - Executing query \"SELECT * FROM %s WHERE %s %s\""%(metadata.getSheetName(), baseQueryStr, orderByStr),"command")
#                pm.start()
#
#        dbResults = [DynamicModel(data, metadata, self) for data in self.queryFromDataStore(metadata, baseQuery, limit, offset, orderBy, isDescending)]
#
#        if criteriaNode is not None:
#            from orangescape.model.engine.execution.ExecutionTree import ExecutionTree
#            et = ExecutionTree(self)
#            dbResults = [model for model in dbResults if et.executeNode(criteriaNode, model)]
#
#        if PERFMONITOR_ENABLED:
#            pm.stop()
#        return dbResults

    def applyFilter(self, criteria, metadata, baseQuery, orderBy=None, isDescending=False):
#        self.__logger.warn('querying model %s with following filters:'%(metadata.getSheetName()))
        for filter in baseQuery:
            cellMetadata=metadata.getCellMetadataById(filter[0])
            if cellMetadata is not None:
                if cellMetadata.getReferredSheetMetadata() is not None:
                        if filter[1]=='=':
                            criteria.createCriteria(filter[0]).add(Restrictions.eq('SheetId',baseQuery[filter]))
                        elif filter[1]=='in':
                            in_criteria = criteria.createCriteria(filter[0])
                            self.addInFilter(in_criteria, 'SheetId', baseQuery[filter])
                        else:
                            raise NotImplementedError, '%s operator is not allowed for constraining referred objects!' % filter[1]
                else:
#                    self.__logger.warn('filters: '+str(filter[0])+','+str(filter[1])+','+str(baseQuery[filter]))
                    {#TODO need to maintain these operator symbols as global variables
                      '=': lambda criteria: criteria.add(Restrictions.eq(filter[0],baseQuery[filter])),
                      '>': lambda criteria: criteria.add(Restrictions.gt(filter[0],baseQuery[filter])),
                      '<': lambda criteria: criteria.add(Restrictions.lt(filter[0],baseQuery[filter])),
                      '<=': lambda criteria: criteria.add(Restrictions.le(filter[0],baseQuery[filter])),
                      '>=': lambda criteria: criteria.add(Restrictions.ge(filter[0],baseQuery[filter])),
                      '!=': lambda criteria: criteria.add(Restrictions.ne(filter[0],baseQuery[filter])),
                      'like': lambda criteria: criteria.add(Restrictions.like(filter[0],'%'+self.getNullCheckedString(baseQuery[filter])+'%')),
                      'in': lambda criteria: self.addInFilter(criteria,filter[0],baseQuery[filter])
                    }[filter[1]](criteria)

        if orderBy is not None:
            if isDescending:
                criteria.addOrder(Order.desc(orderBy))
            else:
                criteria.addOrder(Order.asc(orderBy))


    def queryFromDataStore(self, metadata, baseQuery, limit=None, offset=0, orderBy=None, isDescending=False):
#        if PERFMONITOR_ENABLED:
#            pm = PerfMonitor("DBSession - Query from DataStore","session",{"Model":metadata.getSheetName(),"Query":baseQuery})
#            pm.start()
        criteria=self.hibernateSession.createCriteria(metadata.getSheetName())
        self.applyFilter(criteria, metadata, baseQuery, orderBy, isDescending)
        if limit is not None:
            criteria.setMaxResults(limit)
        criteria.setFirstResult(offset);
        list=self.executeQuery(criteria)
        session_list = []
        for session_data in list:
            session_list.append(self.createDataObject(metadata, None, session_data))
#        self.__logger.debug('the number of items returned for the query is '+str(len(session_list)))
#        if PERFMONITOR_ENABLED:
#            pm.stop()
        return session_list

    def applyAdvFilter(self, criteria, metadata, baseQuery, orderBy=None, isDescending=False):
        if baseQuery:
            criteria.add(self.constructFilter(metadata, baseQuery))
        if orderBy is not None:
            if isDescending:
                criteria.addOrder(Order.desc(orderBy))
            else:
                criteria.addOrder(Order.asc(orderBy))
        return criteria

    def constructFilter(self, metadata, baseQuery):
        bool_operator, filter_params = baseQuery
        f = oper_map[bool_operator]()
        for tup in filter_params:
            if len(tup)==2:
                f.add(self.constructFilter(metadata, tup))
            else:
                lhs, operator, rhs = tup
                if operator.lower()=='like':
                    rhs = rhs or ''
                    rhs = '%'+rhs+'%'
                elif operator.lower()=='in':
                    rhs = rhs or ['dummy-val']
                f.add(oper_map[operator](lhs, rhs))
        return f

    def adv_query_from_ds(self, metadata, baseQuery, limit=None, offset=0, orderBy=None, isDescending=False):
        criteria=self.hibernateSession.createCriteria(metadata.getSheetName())
        self.applyAdvFilter(criteria, metadata, baseQuery, orderBy, isDescending)
        if limit is not None:
            criteria.setMaxResults(limit)
        criteria.setFirstResult(offset);
        list=self.executeQuery(criteria)
        session_list = []
        for session_data in list:
            session_list.append(self.createDataObject(metadata, None, session_data))
        return session_list

    def applyProjection(self, criteria, metadata, measures, dimensions, operations):
        manyToOneAlias = {}
        manyToOneGroupBy = {}
        projectionList = Projections.projectionList()

        def split_refcell(cellName):
            refCellName, fetchCellName = cellName.split('.')
            parentMetadata = manyToOneAlias.get(refCellName)
            if not parentMetadata:
                refCell = metadata.getPublicCellMetadata(refCellName)
                parentMetadata = refCell.getReferredSheetMetadata()
                manyToOneAlias[refCell.getSheetId()] = refCellName
            fetchCell = parentMetadata.getPublicCellMetadata(fetchCellName)
            return refCellName, fetchCell

        for i in xrange(len(dimensions)):
            cellName = dimensions[i]
            if cellName.find('.')>0:
                refCellName, fetchCell = split_refcell(cellName)
                projectionList.add(Projections.groupProperty("%s.%s"%(refCellName, fetchCell.getSheetId())))
            else:
                cell = metadata.getPublicCellMetadata(cellName)
                if cell.isReferenceCell():
                    parentMetadata = cell.getReferredSheetMetadata()
                    manyToOneAlias[cell.getSheetId()] = cell.getCellName()
                    fetchCellName = getDisplayCellName(cell)
                    projectionList.add(Projections.groupProperty("%s.%s"%(cell.getCellName(), parentMetadata.getPublicCellMetadata(fetchCellName).getSheetId())))
                    manyToOneGroupBy[cell.getCellName()] = 'SheetId'
                else:
                    projectionList.add(Projections.groupProperty(cell.getSheetId()))

        execute_operations = {MIN:lambda cellid:Projections.min(cellid), MAX:lambda cellid:Projections.max(cellid),
                              SUM:lambda cellid:Projections.sum(cellid), AVG:lambda cellid:Projections.avg(cellid),
                              COUNT:lambda cellid:Projections.count(cellid)
                              }
        for i in xrange(len(measures)):
            operation = operations[i]
            cellName = measures[i]
            if cellName.find('.')>0:
                refCellName, fetchCell = split_refcell(cellName)
                aggCellId = refCellName+'.'+fetchCell.getSheetId()
            else:
                aggCellId = metadata.getPublicCellMetadata(measures[i]).getSheetId()
            projectionList.add(execute_operations[operation](aggCellId))

        for cellId, aliasName in manyToOneAlias.iteritems():
            criteria.createAlias(cellId, aliasName, Criteria.LEFT_JOIN)
        for aliasName, groupByCellName in manyToOneGroupBy.iteritems():
            projectionList.add(Projections.groupProperty("%s.%s"%(aliasName, groupByCellName)))

        criteria.setProjection(projectionList)

    def aggregate_query(self, metadata, limit, baseQuery, measures, dimensions, operations, orderBy = None, isDescending = False, offset=0):
        from orangescape.model.engine.exception.Error import ReportEngineError
        from orangescape.model.engine.exception.ErrorMessage import Report1000
        if len(measures)!=len(operations):
            raise ReportEngineError, Report1000%(measures, operations)

        criteria=self.hibernateSession.createCriteria(metadata.getSheetName())
#        self.applyFilter(criteria, metadata, baseQuery, orderBy, isDescending)
        self.applyAdvFilter(criteria, metadata, baseQuery, orderBy, isDescending)
        self.applyProjection(criteria, metadata, measures, dimensions, operations)
        if limit is not None:
            criteria.setMaxResults(limit)
        criteria.setFirstResult(offset);

        dbResult = self.executeQuery(criteria)
        sz = len(dimensions)+len(measures)
        return [[row for row in dbRow[:sz]] for dbRow in dbResult]

    def applySelectProperties(self, criteria, metadata, properties):
        manyToOneAlias = {}
#        manyToOneGroupBy = {}
        projectionList = Projections.projectionList()

        def split_refcell(cellName):
            refCellName, fetchCellName = cellName.split('.')
            parentMetadata = manyToOneAlias.get(refCellName)
            if not parentMetadata:
                refCell = metadata.getPublicCellMetadata(refCellName)
                parentMetadata = refCell.getReferredSheetMetadata()
                manyToOneAlias[refCell.getSheetId()] = refCellName
            fetchCell = parentMetadata.getPublicCellMetadata(fetchCellName)
            return refCellName, fetchCell

        for i in xrange(len(properties)):
            cellName = properties[i]
            if cellName.find('.')>0:
                refCellName, fetchCell = split_refcell(cellName)
                projectionList.add(Projections.property("%s.%s"%(refCellName, fetchCell.getSheetId())))
            else:
                cell = metadata.getPublicCellMetadata(cellName)
                if cell.isReferenceCell():
                    parentMetadata = cell.getReferredSheetMetadata()
                    manyToOneAlias[cell.getSheetId()] = cell.getCellName()
                    fetchCellName = getDisplayCellName(cell)
                    projectionList.add(Projections.property("%s.%s"%(cell.getCellName(), parentMetadata.getPublicCellMetadata(fetchCellName).getSheetId())))
#                    manyToOneGroupBy[cell.getCellName()] = 'SheetId'
                else:
                    projectionList.add(Projections.property(cell.getSheetId()))

        for cellId, aliasName in manyToOneAlias.iteritems():
            criteria.createAlias(cellId, aliasName, Criteria.LEFT_JOIN)
#        for aliasName, groupByCellName in manyToOneGroupBy.iteritems():
#            projectionList.add(Projections.property("%s.%s"%(aliasName, groupByCellName)))

        criteria.setProjection(projectionList)

    def report_query(self, metadata, limit, baseQuery, properties, orderBy = None, isDescending = False, offset=0, inMemorySorting=False):
        criteria=self.hibernateSession.createCriteria(metadata.getSheetName())
#        self.applyFilter(criteria, metadata, baseQuery, orderBy, isDescending)
        self.applyAdvFilter(criteria, metadata, baseQuery, orderBy, isDescending)
        self.applySelectProperties(criteria, metadata, properties)
        if limit is not None:
            criteria.setMaxResults(limit)
        criteria.setFirstResult(offset);

        dbResult = self.executeQuery(criteria)
#        sz = len(properties)
        if len(properties)==1:
            return [[dbRow] for dbRow in dbResult]
        return [[row for row in dbRow] for dbRow in dbResult]

    def count(self, metadata, baseQuery):
        criteria=self.hibernateSession.createCriteria(metadata.getSheetName())
#        self.applyFilter(criteria, metadata, baseQuery)
        self.applyAdvFilter(criteria, metadata, baseQuery)
        self.applyProjection(criteria, metadata, ['SheetId'], [], ['count'])
        dbResult = self.executeQuery(criteria)
        if dbResult:
            return dbResult[0]
        return 0

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

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

    def getAttachmentRecord(self, id):
        return self.__getAttachment__(id)

    def commitAttachments(self):
        updatedAttachments = list()
        for id, content in self.attachmentCache['created'].iteritems():
            attachment = HashMap()
            attachment.put('SheetId',self.generateUUID())
            attachment.put('instanceId',id)
            attachment.put('file',content)
            self.setAttachment(attachment)

        for id, content in self.attachmentCache['updated'].iteritems():
            attachment = self.getAttachmentRecord(id)
            attachment.put('file',content)
            self.setAttachment(attachment)

        for key in self.attachmentCache['deleted'].keys():
            self.deleteAttachment(key)

        self.clearAttachmentCache()

    def getAttachmentFromDS(self,id):
        attachmentMap = self.getAttachmentRecord(id)
        if attachmentMap is not None:
            return attachmentMap.get('file')
        else:
            return None

    def begin(self):
        DbMediatorService.begin(self)

    def commit(self):
        application=getApplication()
        for modelDict in self.deletedSheets.values():
            for id, model in modelDict.iteritems():
                primaryData=model.getDataObject()
                self.deleteInDatastore(model.getSheetMetadata().getSheetMetadataName(),primaryData.getSessionData())
        self.commitAttachments()
        DbMediatorService.commit(self)
        self.begin()

    def close(self):
        self.hibernateSession.getTransaction().rollback()
        DbMediatorService.close(self)
