import csv
import logging
from orangescape.model.server.util.ConversionUtil import convertToEngineFormat, getCellCategory, getCategoryNameById
from orangescape.model.server.util.metadatautil import getCellByName

class UserCsvImportService():
    def __init__(self, component, parentSheet, referenceCell, impMapping):
        self.component = component
        self.session = component.getSession()
        self.parentSheet = parentSheet
        self.referenceCell = referenceCell
        self.impMapping = impMapping

        self.oneToMany_Ref = {}
        self.refCell_list = {}
        self.csvColumnMapping = {}

        self.naturlKeys_Id_Map = {}
        self.naturlKeys = []

    def __resolve_mapping__(self):
        metadata = self.referenceCell.getSheetMetadata()
        parentMetadata = self.parentSheet.getSheetMetadata()

        for cellId, mappingParams in self.impMapping.iteritems():
            cell = metadata.getCellMetadataById(cellId)
            csvColumnName = mappingParams.get('CSVColumn') or cell.getCellName()

            propertyMap = {'Cell' : cell}
            cellList = self.csvColumnMapping.setdefault(csvColumnName.upper(), list())
            cellList.append(propertyMap)
            if mappingParams.has_key('Category'):
                propertyMap['Category'] = mappingParams['Category']
                if mappingParams.has_key('Format'):
                    propertyMap['Format'] = mappingParams['Format']

            if mappingParams.get('IsNaturalKey'):
                self.naturlKeys.append(cell)

            if cell.isReferenceCell():
                parentOneToManyCellId = mappingParams.get('OneToManyReference')
                if parentOneToManyCellId:
                    displayCell = self.__getDisplayCell__(cell)

                    self.oneToMany_Ref[cell.getSheetId()] = parentOneToManyCellId+'.'+displayCell.getSheetId()
                    if not self.refCell_list.has_key(parentOneToManyCellId+'.'+displayCell.getSheetId()):
                        displayCell_Id_Map = {}
                        self.refCell_list[parentOneToManyCellId+'.'+displayCell.getSheetId()] = displayCell_Id_Map

                        parentOneToManyRefCell = parentMetadata.getCellMetadataById(parentOneToManyCellId)
                        if parentOneToManyRefCell:
                            childList = self.session.getList(self.parentSheet, parentOneToManyRefCell)
                            for child in childList:
                                if child.get(displayCell) is not None:
                                    displayCell_Id_Map[child.get(displayCell)] = child.getSheetId()

        if len(self.naturlKeys)==0:
            logging.info('No explicit natural key(s) specified, taking SheetName as natural key for the model %s'%(self.referenceCell.getSheetMetadata().getSheetName()))
            self.naturlKeys = [self.referenceCell.getSheetMetadata().getPublicCellMetadata('SheetName')]

    def __prepare_naturalkeyid_map__(self):
        for child in self.session.getList(self.parentSheet, self.referenceCell):
            self.naturlKeys_Id_Map[tuple([child.get(naturalKey) for naturalKey in self.naturlKeys])] = child.getSheetId()

    def __getDisplayCell__(self, cell):
        if len(cell.getSearchDefinitionList()) > 0:
            return cell.getSearchDefinitionList()[0].getDisplayCell()
        return cell.getReferredSheetMetadata().getPublicCellMetadata('SheetName')

    def csvImport(self, fileContent):
        csvString = fileContent.encode('utf-8')
        s = csv.StringIO(csvString)
        dataSeq = csv.DictReader(s, delimiter=",")
        self.__process__(dataSeq)

    def __prepare_db_record__(self, csvRecord):
        dbRecord = {}
        for csvColumn, csvValue in csvRecord.iteritems():
            csvColumn = csvColumn.upper()
            """resolve data format"""
            if self.csvColumnMapping.has_key(csvColumn):
                cellPropertyMapList = self.csvColumnMapping[csvColumn]
                for cellPropertyMap in cellPropertyMapList:
                    cell = cellPropertyMap['Cell']

                    if cellPropertyMap.has_key('Category'):
                        category = getCategoryNameById(cellPropertyMap['Category'])
                        formatStr = cellPropertyMap.get('Format') or cell.getFinalFormat()
                    else:
                        category = getCellCategory(cell)
                        formatStr = cell.getFinalFormat()

                    engineValue = convertToEngineFormat(csvValue, category, formatStr)
                    if not cell.isReferenceCell() or engineValue == '' or engineValue is None:
                        dbRecord[cell.getCellName()] = engineValue
                    else:
                        oneToMany_Ref = self.oneToMany_Ref.get(cell.getSheetId())
                        if oneToMany_Ref:
                            displayCell_Id_Map = self.refCell_list.get(oneToMany_Ref)
                            refSheetId = displayCell_Id_Map.get(engineValue)
                            if refSheetId is not None:
                                dbRecord[cell.getCellName()] = refSheetId
                            else:
                                logging.warn('Referred model %s by the value %s not found in db under the parent %s(%s) '%(cell.getReferredSheetMetadata().getSheetName(), csvValue, self.parentSheet.getSheetId(), self.parentSheet.getSheetMetadata().getSheetName()))
            else:
                cell = getCellByName(self.referenceCell.getSheetMetadata(), csvColumn)
                if cell:
                    category = getCellCategory(cell)
                    formatStr = cell.getFinalFormat()
                    engineValue = convertToEngineFormat(csvValue, category, formatStr)
                    if not cell.isReferenceCell() or engineValue == '' or engineValue is None:
                        dbRecord[cell.getCellName()] = engineValue
                    else:
                        referredSheet = self.session.getByCellValue(engineValue, cell.getReferredSheetMetadata(), self.__getDisplayCell__(cell))
                        if referredSheet:
                            dbRecord[cell.getCellName()] = referredSheet.getSheetId()
                        else:
                            errorMessage = 'Parent sheet with SheetName %s not found for the reference cell %s for the record %s'%(csvValue, cell.getCellName(), dbRecord)
                            logging.warn(errorMessage)
                else:
                    logging.warn("CSV Column %s does't map to any column in the model %s"%(csvColumn, self.referenceCell.getSheetMetadata().getSheetName()))

        naturalKey = tuple([dbRecord.get(naturlKeyCell.getCellName()) for naturlKeyCell in self.naturlKeys])
        existingRecordId = self.naturlKeys_Id_Map.get(naturalKey)
        if existingRecordId:
            dbRecord['SheetId'] = existingRecordId
            return dbRecord
        else:
            return dbRecord

    def __process__(self, dataSeq):
        self.__resolve_mapping__()
        self.__prepare_naturalkeyid_map__()

        for csvRecord in dataSeq:
            dbRecord = self.__prepare_db_record__(csvRecord)
            if dbRecord.has_key('SheetId'):
                child = self.session.get(dbRecord['SheetId'], self.referenceCell.getSheetMetadata())
                self.component.update(child, dbRecord)
            else:
                child = self.component.create(self.parentSheet, self.referenceCell, dbRecord)

