'''
Created on Jun 27, 2011

@author: Pravin
'''
import os
import pickle
import appconfig as config
import logging

import migrate.changeset
from migrate.changeset.constraint import ForeignKeyConstraint
from sqlalchemy import Table, Column, Integer, BLOB,String, MetaData, ForeignKey
from sqlalchemy.types import Date, FLOAT, DateTime,REAL

from orangescape.model.server.util.basicutil import __compress__,__decompress__
from orangescape.model.server.sql import engine
from orangescape.model.server.sql.SqlUtil import PK_COL_LEN, TBL_PREFIX, SYS_PREFIX, TMP_PREFIX, getValidColumnName, getValidTableName, getValidSchemaName


class AbstractVersionHandler:

    applicationLevelMismatchDict = dict()
    modelLevelMismatchDict = dict()
    deployedDirpath = os.getcwd()
    deployedDirpath = os.path.abspath(os.path.join(os.path.dirname(__file__),'../../../application'))

    def __init__(self):
        self.schema = config.SQL_SCHEMA
        self.engine = engine
        self.__orm_metadata__ = MetaData(bind = self.engine)
        self.__orm_metadata__.reflect(bind = self.engine, schema = self.schema)

    def getapplicationMetadata(self):
        readApplicationMetadataFile = open(os.path.join(self.deployedDirpath,'Application.pck'),'rb')
        applicationMetadata = pickle.load(readApplicationMetadataFile)
        readApplicationMetadataFile.close()
        return applicationMetadata[applicationMetadata['root']]['Application.SheetMetadata']

    def getApplicationDiffList(self):
        appLevelMismatchDict = dict()
        ''' Retrives the metadata JSNOON structure by parsing the application PCK binary file.  '''
        applicationSheetMetadataList = self.getapplicationMetadata()
        ''' Loop through the application Sheet metadata and retrieves the sheet metadata which having a mismatch count '''
        for modelId in applicationSheetMetadataList:
            getModelDiffDict = self.getModelDiff(modelId)
            if getModelDiffDict:
                if getModelDiffDict['mismatchPublicCellDict'] or getModelDiffDict['mismatchPrivateCellDict'] or getModelDiffDict['mismatchReferenceCellDict'] or getModelDiffDict['mismatchSystemCellDict']:
                    self.modelLevelMismatchDict[modelId] = getModelDiffDict
                    mismatchCount= len(getModelDiffDict['mismatchPublicCellDict']) + len(getModelDiffDict['mismatchPrivateCellDict']) + len(getModelDiffDict['mismatchReferenceCellDict']) + len(getModelDiffDict['mismatchSystemCellDict'])
                    appLevelMismatchDict[modelId] = {
                                                    "Sheet Name" : applicationSheetMetadataList[modelId],
                                                    "Mismatch Count" : mismatchCount,
                                                    "ModelMismatches" : getModelDiffDict
                                                }
                    self.applicationLevelMismatchDict[modelId] = {
                                                    "Sheet Name" : applicationSheetMetadataList[modelId],
                                                    "Mismatch Count" : mismatchCount,
                                                    "ModelMismatches" : getModelDiffDict
                                                }
        return appLevelMismatchDict

    ''' Checks if a specific Sheet Metadata has mismatch in any or all of its columns or foreign keys against the migrated version.
        @param : SheetMetadataId
        @returns : Dictionary ListOfMismatchColumns    '''
    def getModelDiff(self, modelId):
        if modelId:
            templateDict = dict()

            mismatchDictOfAllPublicCells = dict()
            mismatchDictOfAllPrivateCells = dict()
            mismatchDictOfAllReferenceCells = dict()
            mismatchDictOfAllSystemCells = dict()
            ''' Unpickle the deployed sheetmetadata.pck file using unPickleSheetMetadataFile
                @arguments:
                    1. sheetMetadataId
                    2.file path.
                @return:
                    SheetMetadata JSNOON structure.
            '''
            newVersionSheetMetaData = self.unPickleSheetMetadataFile(modelId,self.deployedDirpath)
            ''' Retrieves the migrated sheetmetadata from DB blob.
            @argument:
                1.sheetMetadataID
            @return:
               SheetMetadata JSNOON structure. '''
            oldVersionSheetMetadata = self.getSheetMetadataFromDB(modelId)
            if oldVersionSheetMetadata:
                ''' Get the list of public ,reference, system cell list from both old and new version '''
                newVersionPublicCellDict,newVersionReferenceCellDict,newVersionSystemCellDict = self.getPublicCellsOfModel(newVersionSheetMetaData)
                oldVersionPublicCellDict,oldVersionReferenceCellDict,oldVersionSystemCellDict = self.getPublicCellsOfModel(oldVersionSheetMetadata)
                ''' Get the list of private cell list from both old and new version '''
                newVersionPrivateCellDict = self.getPrivateCellsOfModel(newVersionSheetMetaData)
                oldVersionPrivateCellDict = self.getPrivateCellsOfModel(oldVersionSheetMetadata)
                ''' Get the list of reference cell list from both old and new version '''
#                newVersionReferenceCellDict = self.getReferenceCellsOfModel(newVersionSheetMetaData)
#                oldVersionReferenceCellDict = self.getReferenceCellsOfModel(oldVersionSheetMetadata)
                logging.info('priting newVersionSystemCellDict')
                logging.info(newVersionSystemCellDict)
                logging.info('priting oldVersionSystemCellDict')
                logging.info(oldVersionSystemCellDict)
                ''' Check the added public cells in new version '''
                for publicCell in newVersionPublicCellDict:
                    publicMismatchTypeList = []
                    publicRemarksList = []
                    if not oldVersionPublicCellDict.has_key(publicCell):
                        publicMismatchTypeList.append('Added')
                        publicRemarksList.append('New Column ' + newVersionPublicCellDict[publicCell].get("Cell Name") + ' is added')
                        mismatchDictOfAllPublicCells[publicCell] = {'newVerDetails' :{
                                                                           'Cell Name' : newVersionPublicCellDict[publicCell].get("Cell Name"),
                                                                           'Data Type' : newVersionPublicCellDict[publicCell].get("Data Type"),
                                                                           'Text Length' : newVersionPublicCellDict[publicCell].get("Text Length")
                                                                           },
                                                                     'oldVerDetails' :{
                                                                           'Cell Name' : '.',
                                                                           'Data Type' : '.',
                                                                           'Text Length' : '.'
                                                                           },
                                                                    'Mismatch Type' : publicMismatchTypeList,
                                                                    'Remarks' : publicRemarksList
                                                                    }

                for publicCell in oldVersionPublicCellDict:
                    publicMismatchTypeList = []
                    publicRemarksList = []
                    ''' Check the missing public cells in new version from old version '''
                    if not newVersionPublicCellDict.has_key(publicCell):
                        publicMismatchTypeList.append('Deleted')
                        publicRemarksList.append('Column ' + oldVersionPublicCellDict[publicCell].get("Cell Name") + ' is deleted')
                        mismatchDictOfAllPublicCells[publicCell] = {'oldVerDetails' :{
                                                                           'Cell Name' : oldVersionPublicCellDict[publicCell].get("Cell Name"),
                                                                           'Data Type' : oldVersionPublicCellDict[publicCell].get("Data Type"),
                                                                           'Text Length' : oldVersionPublicCellDict[publicCell].get("Text Length")
                                                                           },
                                                                     'newVerDetails' :{
                                                                           'Cell Name' : '.',
                                                                           'Data Type' : '.',
                                                                           'Text Length' : '.'
                                                                           },
                                                                    'Mismatch Type' : publicMismatchTypeList,
                                                                    'Remarks' : publicRemarksList
                                                                    }
                    else:
                        ''' Check the renamed public cells in new version from old version '''
                        if not oldVersionPublicCellDict[publicCell].get('Cell Name') == newVersionPublicCellDict[publicCell].get('Cell Name'):
                            publicMismatchTypeList.append('Renamed')
                            publicRemarksList.append("Column " + oldVersionPublicCellDict[publicCell].get("Cell Name") + " is renamed to " + newVersionPublicCellDict[publicCell].get("Cell Name"))

                        ''' Check the data type change of public cells in new version from old version '''
                        if not oldVersionPublicCellDict[publicCell].get("Data Type") == newVersionPublicCellDict[publicCell].get("Data Type"):
                            publicMismatchTypeList.append('Data Type Changed')
                            publicRemarksList.append("Data type of column " + oldVersionPublicCellDict[publicCell].get("Cell Name") + " is changed from "+oldVersionPublicCellDict[publicCell].get("Data Type") + " to "+newVersionPublicCellDict[publicCell].get("Data Type"))

                        if oldVersionPublicCellDict[publicCell].get("Data Type") == newVersionPublicCellDict[publicCell].get("Data Type"):
                            if oldVersionPublicCellDict[publicCell].get("Text Length") or newVersionPublicCellDict[publicCell].get("Text Length"):
                                if not oldVersionPublicCellDict[publicCell].get("Text Length") == newVersionPublicCellDict[publicCell].get("Text Length"):
                                    publicMismatchTypeList.append('Text Length Changed')
                                    publicRemarksList.append("Text Length for column" + oldVersionPublicCellDict[publicCell].get("Cell Name") + " is changed from " + oldVersionPublicCellDict[publicCell].get("Text Length") + " to "+newVersionPublicCellDict[publicCell].get("Text Length"))

                        if publicMismatchTypeList:
                            mismatchDictOfAllPublicCells[publicCell] = {'newVerDetails' :{
                                                                           'Cell Name' : newVersionPublicCellDict[publicCell].get("Cell Name"),
                                                                           'Data Type' : newVersionPublicCellDict[publicCell].get("Data Type"),
                                                                           'Text Length' : newVersionPublicCellDict[publicCell].get("Text Length")
                                                                           },
                                                                     'oldVerDetails' :{
                                                                           'Cell Name' : oldVersionPublicCellDict[publicCell].get("Cell Name"),
                                                                           'Data Type' : oldVersionPublicCellDict[publicCell].get("Data Type"),
                                                                           'Text Length' : oldVersionPublicCellDict[publicCell].get("Text Length")
                                                                           },
                                                                    'Mismatch Type' : publicMismatchTypeList,
                                                                    'Remarks' : publicRemarksList
                                                                    }

                for privateCell in oldVersionPrivateCellDict:
                    privateMismatchTypeList = []
                    privateRemarksList = []
                    ''' Check the missing private cells in new version from old version '''
                    if not newVersionPrivateCellDict.has_key(privateCell):
                        privateMismatchTypeList.append('Deleted')
                        privateRemarksList.append('Column ' + oldVersionPrivateCellDict[privateCell].get("Cell Name") + ' is deleted')
                        mismatchDictOfAllPrivateCells[privateCell] = {'oldVerDetails' : {
                                                                            'Cell Name' : oldVersionPrivateCellDict[privateCell].get("Cell Name"),
                                                                            'Data Type' : oldVersionPrivateCellDict[privateCell].get("Data Type"),
                                                                            'Text Length' : oldVersionPrivateCellDict[privateCell].get("Text Length")
                                                                            },
                                                                      'newVerDetails' : {
                                                                            'Cell Name' : '.',
                                                                            'Data Type' : '.',
                                                                            'Text Length' : '.'
                                                                            },
                                                                      'Mismatch Type' : privateMismatchTypeList,
                                                                      'Remarks' :  privateRemarksList
                                                                     }
                    else:
                        ''' Check the private cells data type change in new version from old version '''
                        if not oldVersionPrivateCellDict[privateCell].get("Data Type") == newVersionPrivateCellDict[privateCell].get("Data Type"):
                            privateMismatchTypeList.append('Data Type Changed')
                            privateRemarksList.append("Data type of column " + oldVersionPrivateCellDict[privateCell].get("Cell Name") + " is changed from " + oldVersionPrivateCellDict[privateCell].get("Data Type") + " to " + newVersionPrivateCellDict[privateCell].get("Data Type"))

                        if oldVersionPrivateCellDict[privateCell].get("Data Type") == newVersionPrivateCellDict[privateCell].get("Data Type"):
                            if oldVersionPrivateCellDict[privateCell].get("Text Length") or newVersionPrivateCellDict[privateCell].get("Text Length"):
                                if not oldVersionPrivateCellDict[privateCell].get("Text Length") == newVersionPrivateCellDict[privateCell].get("Text Length"):
                                    privateMismatchTypeList.append('Text Length Changed')
                                    privateRemarksList.append("Text Length for column" + oldVersionPrivateCellDict[privateCell].get("Cell Name") + " is changed from " + oldVersionPrivateCellDict[privateCell].get("Text Length") + " to "+newVersionPrivateCellDict[privateCell].get("Text Length"))

                        if privateMismatchTypeList:
                            mismatchDictOfAllPrivateCells[privateCell] = {'oldVerDetails' : {
                                                                            'Cell Name' : oldVersionPrivateCellDict[privateCell].get("Cell Name"),
                                                                            'Data Type' : oldVersionPrivateCellDict[privateCell].get("Data Type"),
                                                                            'Text Length' : oldVersionPrivateCellDict[privateCell].get("Text Length")
                                                                            },
                                                                      'newVerDetails' : {
                                                                                'Cell Name' : newVersionPrivateCellDict[privateCell].get("Cell Name"),
                                                                                'Data Type' : newVersionPrivateCellDict[privateCell].get("Data Type"),
                                                                                'Text Length' : newVersionPrivateCellDict[privateCell].get("Text Length")
                                                                                },
                                                                      'Mismatch Type' : privateMismatchTypeList,
                                                                      'Remarks' :  privateRemarksList
                                                                     }
                    ''' Check the added private cells in new version '''
                for privateCell in newVersionPrivateCellDict:
                    privateMismatchTypeList = []
                    privateRemarksList = []
                    if not oldVersionPrivateCellDict.has_key(privateCell):
                        privateMismatchTypeList.append('Added')
                        privateRemarksList.append('New Column ' + newVersionPrivateCellDict[privateCell].get("Cell Name") + ' is added')
                        mismatchDictOfAllPrivateCells[privateCell] = {'newVerDetails' : {
                                                                            'Cell Name' : newVersionPrivateCellDict[privateCell].get("Cell Name"),
                                                                            'Data Type' : newVersionPrivateCellDict[privateCell].get("Data Type"),
                                                                            'Text Length' : newVersionPrivateCellDict[privateCell].get("Text Length")
                                                                            },
                                                                      'oldVerDetails' : {
                                                                            'Cell Name' : '.',
                                                                            'Data Type' : '.',
                                                                            'Text Length' : '.'
                                                                            },
                                                                      'Mismatch Type' : privateMismatchTypeList,
                                                                      'Remarks' :  privateRemarksList
                                                                     }

                for referenceCell in oldVersionReferenceCellDict:
                    referenceMismatchTypeList = []
                    referenceRemarksList = []
                    ''' Check the missing reference cells in new version from old version '''
                    if not newVersionReferenceCellDict.has_key(referenceCell):
                        referenceMismatchTypeList.append('Deleted')
                        referenceRemarksList.append("Reference for the column " + oldVersionReferenceCellDict[referenceCell].get("Cell Name") + " is deleted")

                        mismatchDictOfAllReferenceCells[referenceCell] = {'oldVerDetails' : {
                                                                              "Cell Name" : oldVersionReferenceCellDict[referenceCell].get("Cell Name"),
                                                                              "Referred Sheet Name" : oldVersionReferenceCellDict[referenceCell].get("Referred Sheet Name")
                                                                              },
                                                                          'newVerDetails' : {
                                                                              "Cell Name" : '.',
                                                                              "Referred Sheet Name" : '.'
                                                                              },
                                                                          'Mismatch Type' : referenceMismatchTypeList,
                                                                          'Remarks' : referenceRemarksList
                                                                         }
                    else:
                        ''' Check the reference cell sheet name change in new version from old version '''
                        if not oldVersionReferenceCellDict[referenceCell].get("Referred Sheet Name") == newVersionReferenceCellDict[referenceCell].get("Referred Sheet Name"):
                            referenceMismatchTypeList.append('Referred Sheet Changed')
                            referenceRemarksList.append("Referred sheet name for the column " + oldVersionReferenceCellDict[referenceCell].get("Cell Name") + " is changed from " + oldVersionReferenceCellDict[referenceCell].get("Referred Sheet Name") + " to " + newVersionReferenceCellDict[referenceCell].get("Referred Sheet Name"))
                            mismatchDictOfAllReferenceCells[referenceCell] = {'oldVerDetails' : {
                                                                                                 "Cell Name" : oldVersionReferenceCellDict[referenceCell].get("Cell Name"),
                                                                                                 "Referred Sheet Name" : oldVersionReferenceCellDict[referenceCell].get("Referred Sheet Name")
                                                                                                 },
                                                                              'newVerDetails' : {
                                                                                                 "Cell Name" : newVersionReferenceCellDict[referenceCell].get("Cell Name"),
                                                                                                 "Referred Sheet Name" : newVersionReferenceCellDict[referenceCell].get("Referred Sheet Name")
                                                                                                 },
                                                                              'Mismatch Type' : referenceMismatchTypeList,
                                                                              'Remarks' : referenceRemarksList
                                                                              }

                for referenceCell in newVersionReferenceCellDict:
                    referenceMismatchTypeList = []
                    referenceRemarksList = []
                    if not oldVersionReferenceCellDict.has_key(referenceCell):
                        referenceMismatchTypeList.append('Added')
                        referenceRemarksList.append('New Reference for the column ' + newVersionReferenceCellDict[referenceCell].get("Cell Name") + ' is added')
                        mismatchDictOfAllReferenceCells[referenceCell] = {'newVerDetails' : {
                                                                              "Cell Name" : newVersionReferenceCellDict[referenceCell].get("Cell Name"),
                                                                              "Referred Sheet Name" : newVersionReferenceCellDict[referenceCell].get("Referred Sheet Name")
                                                                              },
                                                                          'oldVerDetails' : {
                                                                              "Cell Name" : '.',
                                                                              "Referred Sheet Name" : '.'
                                                                              },
                                                                          'Mismatch Type' : referenceMismatchTypeList,
                                                                          'Remarks' : referenceRemarksList
                                                                         }

                for systemCell in oldVersionSystemCellDict:
                    systemMismatchTypeList = []
                    systemRemarksList = []
                    ''' Check the missing private cells in new version from old version '''
                    if not newVersionSystemCellDict.has_key(systemCell):
                        systemMismatchTypeList.append('Deleted')
                        systemRemarksList.append('Column ' + oldVersionSystemCellDict[systemCell].get("Cell Name") + ' is deleted')
                        mismatchDictOfAllSystemCells[systemCell] = {'oldVerDetails' : {
                                                                            'Cell Name' : oldVersionSystemCellDict[systemCell].get("Cell Name")
                                                                            },
                                                                      'newVerDetails' : {
                                                                            'Cell Name' : '.'
                                                                            },
                                                                      'Mismatch Type' : systemMismatchTypeList,
                                                                      'Remarks' :  systemRemarksList
                                                                     }
                for systemCell in newVersionSystemCellDict:
                    systemMismatchTypeList = []
                    systemRemarksList = []
                    if not oldVersionSystemCellDict.has_key(systemCell):
                        systemMismatchTypeList.append('Added')
                        systemRemarksList.append('New column ' + newVersionSystemCellDict[systemCell].get("Cell Name") + ' is added')
                        mismatchDictOfAllSystemCells[systemCell] = {'newVerDetails' : {
                                                                              "Cell Name" : newVersionSystemCellDict[systemCell].get("Cell Name")
                                                                              },
                                                                          'oldVerDetails' : {
                                                                              "Cell Name" : '.'
                                                                              },
                                                                          'Mismatch Type' : systemMismatchTypeList,
                                                                          'Remarks' : systemRemarksList
                                                                         }
                
                        

                templateDict['mismatchPublicCellDict'] = mismatchDictOfAllPublicCells
                templateDict['mismatchPrivateCellDict'] = mismatchDictOfAllPrivateCells
                templateDict['mismatchReferenceCellDict'] = mismatchDictOfAllReferenceCells
                templateDict['mismatchSystemCellDict'] = mismatchDictOfAllSystemCells

                return templateDict

    def getSheetDiff(self, modelid):
#        logging.info('pritngin applevel mismatches dict')
        mismatchModelList = self.applicationLevelMismatchDict
#        logging.info(mismatchModelList)
#        logging.info('priting sheet diff')
#        logging.info(mismatchModelList[modelid].get('ModelMismatches'))
        return mismatchModelList[modelid].get('ModelMismatches')

    def getSheetMetadataFromDB(self,modelId):
        ''' Getting the Version Table metadata and querying on it to get the metadata old version atatchment
            @arguments:
                Model Id:
            @returns: SheetMetadata old version attachment.
        '''
        '''fetch the version table metadata'''
        modelMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema) + '.' + getValidTableName('tblMetadata'))
        if modelMetadata is not None:
            ''' Query the version table to fetch the old version object
                @arguments:
                    1 . ModelId = The model which we want to query to.
                    2 . IsLatestVersion = The flag which will verify the old version metadata.
                @return:The query result.
            '''
            metadataQuery = modelMetadata.select(modelMetadata.c.ModelId == modelId)
            executeQueryResult = metadataQuery.execute()
            recordEntry = executeQueryResult.fetchone()
            if recordEntry is not None:
                curVersion = recordEntry.CurrentVersion
                if curVersion is not None:
                    modelVerMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema) +'.'+ getValidTableName('ModelVersion'))
                    verQuery = modelVerMetadata.select((modelVerMetadata.c.ModelVersion_Id == curVersion))
                    executeQueryRes = verQuery.execute().fetchone()
                    attachmentContent  = __decompress__(executeQueryRes.Content)
                    return  pickle.loads(attachmentContent)

    def getPublicCellsOfModel(self,sheetMetaData):
            ''' Get Vivek's code and use it intelligently.'''
            publicCellDict = dict()
            referenceCellDict = dict()
            systemCellDict = dict()
            ''' This will fetch the all public cells exists in the specified Sheet Metadata '''
            sheetPublicCellList = sheetMetaData[sheetMetaData['root']]['SheetMetadata.CellMetadata']
            ''' Loop through the sheetCellMetadata to fetch the public cell names '''
            for publicCellId in sheetPublicCellList:
                publicCellMetadata = sheetMetaData[publicCellId]
                publicCellFormat = self.getCellDataType(sheetMetaData,publicCellId)
                if publicCellMetadata.get('TextLength'):
                        textLength = str(int(publicCellMetadata.get('TextLength')))
                elif publicCellFormat == 'Text':
                    textLength = '255'
                else:
                    textLength = ''
                if publicCellMetadata.get('RowNumber') < 0 or publicCellMetadata.get('ColumnNumber') < 0:
                    systemCellDict[publicCellId] =  {
                                                    "Cell Name" : publicCellMetadata.get('CellName')
                                                    }

                if publicCellMetadata.get('RowNumber') > 0 and publicCellMetadata.get('ColumnNumber') >0:
                    publicCellDict[publicCellId] = {
                                                    "Cell Name" : publicCellMetadata.get('CellName'),
                                                    "Data Type" : publicCellFormat,
                                                    "Text Length" : textLength
                                                   }

            if publicCellMetadata.get('ReferredSheetMetadata'):
                referredSheetMetadata = publicCellMetadata['ReferredSheetMetadata']
                for referenceSheetName in referredSheetMetadata:
                    referenceCellDict[publicCellId] =  {
                                                        "Cell Name" : publicCellMetadata.get('CellName'),
                                                        "Referred Sheet Name" : referredSheetMetadata[referenceSheetName]
                                                       }

            return publicCellDict, referenceCellDict, systemCellDict

    def getPrivateCellsOfModel(self,sheetMetaData):
        privateCellDict = dict()
        rowMetadata = sheetMetaData[sheetMetaData['root']]['SheetMetadata.RowMetadata']
        ''' Loop through the rowMetadata and fetch the private cell names,
            while fetching,check whether the cell has a data structure and it has the sheetmetadata i.e parent ref,
            if so then it is a public cell else it is private .
        '''
        for rowMetadataId in rowMetadata:
            rowCellList = sheetMetaData[rowMetadataId]['RowMetadata.CellMetadata']
            for privateCellId in rowCellList:
                if sheetMetaData.get(privateCellId):
                    if not sheetMetaData[privateCellId].get('SheetMetadata'):
                        privateCellFormat = self.getCellDataType(sheetMetaData,privateCellId)
                        if sheetMetaData[privateCellId].get('TextLength'):
                                textLength = str(int(sheetMetaData[privateCellId].get('TextLength')))
                        elif sheetMetaData[privateCellId] == 'Text':
                            textLength = '255'
                        else:
                            textLength = ''
                        privateCellDict[privateCellId] = {
#                                                          "Cell Name" : rowCellList[privateCellId],
                                                          "Cell Name" : privateCellId,
                                                          "Data Type" : privateCellFormat,
                                                          "Text Length" : textLength
                                                         }
        return privateCellDict

#    def getReferenceCellsOfModel(self,sheetMetaData):
#        referenceCellDict = dict()
#        ''' Loop through the all public cells and pick only which has sheet metadata ref. '''
#        sheetPublicCellList = sheetMetaData[sheetMetaData['root']]['SheetMetadata.CellMetadata']
#        for publicCellId in sheetPublicCellList:
#            publicCellMetadata = sheetMetaData[publicCellId]
#            if publicCellMetadata.get('ReferredSheetMetadata'):
#                referredSheetMetadata = publicCellMetadata['ReferredSheetMetadata']
#                for referenceSheetName in referredSheetMetadata:
#                    referenceCellDict[publicCellId] =  {
#                                                        "Cell Name" : publicCellMetadata.get('CellName'),
#                                                        "Referred Sheet Name" : referredSheetMetadata[referenceSheetName]
#                                                       }
#        return  referenceCellDict

    def unPickleSheetMetadataFile(self,sheetMetadataId,filePath):
        ''' Open the SheetMetadata.pck file and then load it using the pickle file.Open method
            @arguments
                1. File Name
                2. read options,rb indiactes read binary
        '''
        readSheetMetadatafile = open(os.path.join(filePath,sheetMetadataId+'.pck'),'rb')
        appSheetMetaData = pickle.load(readSheetMetadatafile)
        readSheetMetadatafile.close()

        return appSheetMetaData

    def getCellDataType(self,deployedVersionSheetMetaData,cellId):
        publicCellMetadata = deployedVersionSheetMetaData[cellId]
        if publicCellMetadata.get('FinalCategory') == "Category001" or publicCellMetadata.get('FinalCategory') == "Category010":
            return "Text"
        elif publicCellMetadata.get('FinalCategory') == "Category005" or publicCellMetadata.get('CellName') == "CreatedAt" or publicCellMetadata.get('CellName') == "ModifiedAt":
            return "DateTime"
        else:
            return "Double"

    def setModelAttachments(self):
        import datetime
        insertQueryList =[]
        '''Fetching the Model Version table Metadata'''
        modelVerMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema) + '.'+getValidTableName('ModelVersion'))
        '''Fetching the Version table Metadata'''
        verMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema) + '.' + getValidTableName('Version'))
        '''Fetching the Global table Metadata'''
        globalMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema) + '.' + getValidTableName('tblGlobal'))
        '''Fetching the Metadata table Metadata'''
        modelMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema) + '.' + getValidTableName('tblMetadata'))

        if modelVerMetadata is not None:
            ''' Checking if it is the first time versioning'''
            globalQuery = globalMetadata.select(globalMetadata.c.Global_Id == 'global')
            executeQueryRes = globalQuery.execute()
            recordEntry = executeQueryRes.fetchone()
            if recordEntry is not None:
                ''' fetching the current version number and current deployed time stamp from global table'''
                curVersion = recordEntry.CurrentVersion
                curDeployedTimeStamp = recordEntry.ModelVersion
                ''' if no version exist then we should call the first time migration method'''
                if curVersion is None:
                    '''inserting new version into Version table with necessary version details '''
                    versionInsertQuery = verMetadata.insert()
                    versionPKId = self.__generate_pk__('Version')
                    versionInsertQuery.execute(Version_Id = versionPKId,
                                               Version_Number = 1,
                                               Global_Ref = 'global',
                                               ModelVersion = str(curDeployedTimeStamp)
#                                               ModelVersion = str(recordEntry.ModelVersion)
                                               )

                    applicationMetadata = self.getapplicationMetadata()
                    '''getting the list of models in the application and looping through it'''
                    for modelId in applicationMetadata:
                        if not applicationMetadata[modelId] == 'Version':
                            '''reading the pck files and compressing it using compress method to reduce the size of the table'''
                            readSheetMetadataFile = open(os.path.join(self.deployedDirpath,modelId+'.pck'),'rb')
                            fileContent = readSheetMetadataFile.read()
                            attachmentContent = __compress__(fileContent)
                            ''' generating random primary key using generate_pk method'''
                            modelPKId = self.__generate_pk__(applicationMetadata[modelId])
                            '''appending the list of Model Version insert queries which will get executed as a batch'''
                            insertQueryList.append(
                                                    {'ModelVersion_Id':modelPKId,
                                                     'IsLatestVersion':'1',
                                                     'Model_Id':modelId,
                                                     'Version_Date' : datetime.datetime.now(),
                                                     'Content':attachmentContent,
                                                     'Version_Ref':versionPKId,
                                                     'Metadata_Ref':'M_'+modelId
                                                    }
                                                  )
                            '''setting the current version for the model in Metadata table'''
                            updateMetaQuery = modelMetadata.update(modelMetadata.c.ModelId == modelId)
                            updateMetaQuery.execute(CurrentVersion = modelPKId)

                        '''setting the current version for the application in Global table'''
                        updateVersionQuery = globalMetadata.update(globalMetadata.c.Global_Id == 'global')
                        updateVersionQuery.execute(CurrentVersion = 1)

                else:
                    import appconfig as config
                    '''fetching the current version details from the version table'''
                    verQuery = verMetadata.select(verMetadata.c.Version_Number == curVersion)
                    verQueryResult = verQuery.execute()
                    recordSet = verQueryResult.fetchone()
                    if recordSet is not None:
                        '''fetching the deployed time stamp of the current version from version table.
                            checking if the global deployed time stamp and current version time stamp are same,if not do the migration'''
#                        if not curDeployedTimeStamp == recordSet.ModelVersion:
#                        logging.info('priting appconfig')
#                        logging.info(config.deployedat)
#                        logging.info('priting version deployed at')
#                        logging.info(recordSet.ModelVersion)
                        if not config.deployedat == recordSet.ModelVersion:
                            ''' calling alter table scripts method'''
                            self.generateAlterScripts()

                            versionInsertQuery = verMetadata.insert()
                            versionPKId = self.__generate_pk__('Version')
                            versionInsertQuery.execute(Version_Id = versionPKId,
                                                       Version_Number = recordSet.Version_Number + 1,
                                                       ModelVersion = str(config.deployedat),
                                                       Global_Ref = 'global'
                                                       )
                            '''getting the list of models which got changed and looping through it'''
                            mismatchModelList = self.applicationLevelMismatchDict
#                            logging.info('priting mismatchModelList')
#                            logging.info(mismatchModelList)
                            for mismatchModelId in mismatchModelList:
                                '''reading the pck files and compressing it using compress method to reduce the size of the table'''
                                readSheetMetadataFile = open(os.path.join(self.deployedDirpath,mismatchModelId+'.pck'),'rb')
                                fileContent = readSheetMetadataFile.read()
                                attachmentContent = __compress__(fileContent)
                                ''' generating random primary key using generate_pk method'''
                                modelPKId = self.__generate_pk__(mismatchModelList[mismatchModelId].get('Sheet Name'))
                                '''appending the list of Model Version insert queries which will get executed as a batch'''
                                insertQueryList.append(
                                                        {'ModelVersion_Id':modelPKId,
                                                         'IsLatestVersion':'1',
                                                         'Model_Id':mismatchModelId,
                                                         'Version_Date' : datetime.datetime.now(),
                                                         'Content':attachmentContent,
                                                         'Version_Ref':versionPKId,
                                                         'Metadata_Ref':'M_'+mismatchModelId
                                                        }
                                                       )
                                '''setting the current version for the model in Metadata table'''
                                updateMetaQuery = modelMetadata.update(modelMetadata.c.ModelId == mismatchModelId)
                                updateMetaQuery.execute(CurrentVersion = modelPKId)

                            '''setting the current version for the application in Global table'''
                            updateVersionQuery = globalMetadata.update(globalMetadata.c.Global_Id == 'global')
                            updateVersionQuery.execute(CurrentVersion = recordSet.Version_Number + 1)

                '''calling the executeInsertScripts method which will take the list of insert queries and execute it as a batch
                @arguments:
                    1.modelMetadata -- in which model we want to insert the data
                    2.insertRecordList -- list of insert into queries'''
                self.executeInsertScripts(modelVerMetadata, insertQueryList)

    def executeInsertScripts(self, tableMetadata, queryList):
        if queryList:
            rs = tableMetadata.insert()
            rs.execute(queryList)

    def generateAlterScripts(self):
        mismatchModelList = self.applicationLevelMismatchDict
        for modelId in mismatchModelList:
            modelName = mismatchModelList[modelId].get('Sheet Name')
            modelTblMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema)+'.'+getValidTableName(TBL_PREFIX+modelName))
            modelTmpMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema)+'.'+getValidTableName(TMP_PREFIX+modelName))
            modelSysMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema)+'.'+getValidTableName(SYS_PREFIX+modelName))

            for mismatchColumn in self.modelLevelMismatchDict[modelId]['mismatchReferenceCellDict']:
                mismatchList = self.modelLevelMismatchDict[modelId]['mismatchReferenceCellDict'][mismatchColumn]
                if 'Added' in mismatchList.get('Mismatch Type'):
                    if self.modelLevelMismatchDict[modelId]['mismatchPublicCellDict'].has_key(mismatchColumn):
                        publicmisMatchList = self.modelLevelMismatchDict[modelId]['mismatchPublicCellDict'][mismatchColumn]
                        if 'Added' in publicmisMatchList.get('Mismatch Type'):
                            self.addColumnExecution(modelTblMetadata, publicmisMatchList['newVerDetails'].get('Cell Name'), self.getSchemaDataType(publicmisMatchList['newVerDetails'].get('Data Type'),publicmisMatchList['newVerDetails'].get('Text Length')),mismatchColumn)
                        if 'Renamed' in publicmisMatchList.get('Mismatch Type') or 'Data Type Changed' in publicmisMatchList.get('Mismatch Type') or 'Text Length Changed' in publicmisMatchList.get('Mismatch Type'):
                            oldColumn = publicmisMatchList['oldVerDetails'].get('Cell Name')
                            self.alterColumnExecution(modelTblMetadata, oldColumn, publicmisMatchList['newVerDetails'].get('Cell Name'), publicmisMatchList['oldVerDetails'].get('Data Type'), publicmisMatchList['newVerDetails'].get('Data Type'),publicmisMatchList['newVerDetails'].get('Text Length'))
#                        self.foreignKeyExecution(modelTblMetadata, mismatchList['newVerDetails'].get('Cell Name'), getValidSchemaName(self.schema)+'.'+getValidTableName(TBL_PREFIX+mismatchList['newVerDetails'].get('Referred Sheet Name')) + "." + mismatchList['newVerDetails'].get('Referred Sheet Name') + "_Id", getValidColumnName(mismatchColumn), 'ADD')
#                    else:
#                        self.foreignKeyExecution(modelTblMetadata, mismatchList['newVerDetails'].get('Cell Name'), getValidSchemaName(self.schema)+'.'+getValidTableName(TBL_PREFIX+mismatchList['newVerDetails'].get('Referred Sheet Name')) + "." + mismatchList['newVerDetails'].get('Referred Sheet Name') + "_Id", getValidColumnName(mismatchColumn), 'ADD')
#                if 'Referred Sheet Changed' in mismatchList.get('Mismatch Type'):
#                    self.foreignKeyExecution(modelTblMetadata, mismatchList['oldVerDetails'].get('Cell Name'), getValidSchemaName(self.schema)+'.'+getValidTableName(TBL_PREFIX+mismatchList['oldVerDetails'].get('Referred Sheet Name')) + "." + mismatchList['oldVerDetails'].get('Referred Sheet Name') + "_Id", None, 'Drop')
#                    self.foreignKeyExecution(modelTblMetadata, mismatchList['newVerDetails'].get('Cell Name'), getValidSchemaName(self.schema)+'.'+getValidTableName(TBL_PREFIX+mismatchList['newVerDetails'].get('Referred Sheet Name')) + "." + mismatchList['newVerDetails'].get('Referred Sheet Name') + "_Id", getValidColumnName(mismatchColumn), 'ADD')
#                if 'Deleted' in mismatchList.get('Mismatch Type'):
#                    if not self.modelLevelMismatchDict[modelId]['mismatchPublicCellDict'].has_key(mismatchColumn):
#                        self.foreignKeyExecution(modelTblMetadata, mismatchList['oldVerDetails'].get('Cell Name'), getValidSchemaName(self.schema)+'.'+getValidTableName(TBL_PREFIX+mismatchList['oldVerDetails'].get('Referred Sheet Name')) + "." + mismatchList['oldVerDetails'].get('Referred Sheet Name') + "_Id", None, 'Drop')

            for mismatchColumn in self.modelLevelMismatchDict[modelId]['mismatchPublicCellDict']:
                mismatchList = self.modelLevelMismatchDict[modelId]['mismatchPublicCellDict'][mismatchColumn]
                if 'Deleted' in mismatchList.get('Mismatch Type'):
                    if self.modelLevelMismatchDict[modelId]['mismatchReferenceCellDict'].has_key(mismatchColumn):
                        referenceMismatchList = self.modelLevelMismatchDict[modelId]['mismatchReferenceCellDict'][mismatchColumn]
#                        self.foreignKeyExecution(modelTblMetadata, referenceMismatchList['oldVerDetails'].get('Cell Name'), getValidSchemaName(self.schema)+'.'+getValidTableName(TBL_PREFIX+referenceMismatchList['oldVerDetails'].get('Referred Sheet Name')) + "." + referenceMismatchList['oldVerDetails'].get('Referred Sheet Name') + "_Id", None, 'Drop')
                        self.dropColumnExecution(modelTblMetadata, mismatchList['oldVerDetails'].get('Cell Name'), self.getSchemaDataType(mismatchList['oldVerDetails'].get('Data Type')))
                    else:
                        self.dropColumnExecution(modelTblMetadata, mismatchList['oldVerDetails'].get('Cell Name'), self.getSchemaDataType(mismatchList['oldVerDetails'].get('Data Type')))
                if 'Added' in mismatchList.get('Mismatch Type'):
                    if not self.modelLevelMismatchDict[modelId]['mismatchReferenceCellDict'].has_key(mismatchColumn):
                        self.addColumnExecution(modelTblMetadata, mismatchList['newVerDetails'].get('Cell Name'), self.getSchemaDataType(mismatchList['newVerDetails'].get('Data Type'),mismatchList['newVerDetails'].get('Text Length')),mismatchColumn)
                if 'Renamed' in mismatchList.get('Mismatch Type') or 'Data Type Changed' in mismatchList.get('Mismatch Type') or 'Text Length Changed' in mismatchList.get('Mismatch Type'):
                    if not self.modelLevelMismatchDict[modelId]['mismatchReferenceCellDict'].has_key(mismatchColumn):
                        oldColumn = mismatchList['oldVerDetails'].get('Cell Name')
                        self.alterColumnExecution(modelTblMetadata, oldColumn, mismatchList['newVerDetails'].get('Cell Name'), mismatchList['oldVerDetails'].get('Data Type'),mismatchList['newVerDetails'].get('Data Type'),mismatchList['newVerDetails'].get('Text Length'))

            for mismatchColumn in self.modelLevelMismatchDict[modelId]['mismatchPrivateCellDict']:
                mismatchList = self.modelLevelMismatchDict[modelId]['mismatchPrivateCellDict'][mismatchColumn]
                if 'Deleted' in mismatchList.get('Mismatch Type'):
                    self.dropColumnExecution(modelTmpMetadata, mismatchList['oldVerDetails'].get('Cell Name'), self.getSchemaDataType(mismatchList['oldVerDetails'].get('Data Type')))
                if 'Added' in mismatchList.get('Mismatch Type'):
                    self.addColumnExecution(modelTmpMetadata, mismatchList['newVerDetails'].get('Cell Name'), self.getSchemaDataType(mismatchList['newVerDetails'].get('Data Type'),mismatchList['newVerDetails'].get('Text Length')),mismatchColumn)
                if 'Data Type Changed' in mismatchList.get('Mismatch Type'):
                    oldColumn = mismatchList['oldVerDetails'].get('Cell Name')
                    self.alterColumnExecution(modelTblMetadata, oldColumn, mismatchList['newVerDetails'].get('Cell Name'), mismatchList['oldVerDetails'].get('Data Type'), mismatchList['newVerDetails'].get('Data Type'),mismatchList['newVerDetails'].get('Text Length'))

            for mismatchColumn in self.modelLevelMismatchDict[modelId]['mismatchSystemCellDict']:
                mismatchList = self.modelLevelMismatchDict[modelId]['mismatchSystemCellDict'][mismatchColumn]
                if 'Deleted' in mismatchList.get('Mismatch Type'):
                    self.dropColumnExecution(modelSysMetadata, mismatchList['oldVerDetails'].get('Cell Name'), self.getSchemaDataType(mismatchList['oldVerDetails'].get('Data Type')))
                if 'Added' in mismatchList.get('Mismatch Type'):
                    self.addColumnExecution(modelSysMetadata, mismatchList['newVerDetails'].get('Cell Name'), self.getSchemaDataType(mismatchList['newVerDetails'].get('Data Type'),mismatchList['newVerDetails'].get('Text Length')),mismatchColumn)

    def addColumnExecution(self, tableMetadata, columnName, dataType,keyId):
        addColumn = Column(columnName, dataType,key=keyId)
        tableMetadata.append_column(addColumn)
        tableMetadata.create_column(addColumn)

    def dropColumnExecution(self, tableMetadata, columnName, dataType):
        dropColumn = Column(columnName, dataType)
        tableMetadata.drop_column(dropColumn)

    def alterColumnExecution(self, tableMetadata, oldColumnName, newColumnName, fromDataType, toDataType, textLength=None):
        if fromDataType == 'Text' and toDataType == 'Double':
            from sqlalchemy.sql.expression import case,cast
            colNames = {}
            colName = tableMetadata.c[oldColumnName]
            casestatement = case([(colName.op('regexp')('^[0-9.]+$'),cast(colName,REAL))],else_=0)
            colNames[oldColumnName] = casestatement
            tableMetadata.update().values(**colNames).execute()
        elif toDataType=='Text':
            from sqlalchemy.sql.expression import cast
            colNames = {}
            colName = tableMetadata.c[oldColumnName]
            casestatement = cast(colName,String)
            colNames[oldColumnName] = casestatement
            tableMetadata.update().values(**colNames).execute()
        else:
            self.updateColumnToNULL(tableMetadata, oldColumnName)

        schemaDataType = self.getSchemaDataType(toDataType, textLength)
        tableMetadata.c[oldColumnName].alter(name = newColumnName, type = schemaDataType)

    def getForeignkeyConstraintName(self, tableMetadata, foreignKeyColumn):
        for constraint in tableMetadata.c[foreignKeyColumn].foreign_keys:
            return constraint.name

    def foreignKeyExecution(self, tableMetadata, colName, referenceTableMetadata, constraintName, executionType):
        if constraintName is None:
            constraintName = self.getForeignkeyConstraintName(tableMetadata, colName)
        foreignKey = ForeignKeyConstraint([tableMetadata.c[colName]], [referenceTableMetadata], name=constraintName)
        self.updateColumnToNULL(tableMetadata, colName)
        if executionType == 'Drop':
            foreignKey.drop()
        else:
            foreignKey.create()

    def updateColumnToNULL(self, tableMetadata, columnName):
        colNames = {}
        colNames[columnName] = None
        tableMetadata.update().values(**colNames).execute()

    def getSchemaDataType(self,dataType, length=None):
        if dataType == 'Text':
            if length:
                return String(int(length))
            else:
                return String(255)
        elif dataType == 'DateTime':
            return DateTime
        else:
            return REAL()

    def __generate_pk__(self, modelName):
        return modelName[:2]+str(self.generateUUID()).replace('-', '_')

    def generateUUID(self):
        import uuid
        return uuid.uuid1()

    def generateAlterTableScripts(self):
        raise NotImplementedError('alterSchema not implemented in %s'%type(self))