package com.lemans.ds.qualitycontrol

import com.lemans.ds.flag.FlagService
import com.lemans.ds.flag.FlagValueManagerService
import com.lemans.services.LemansManager
import grails.transaction.Transactional

import java.sql.Timestamp
import java.text.SimpleDateFormat
import java.time.ZoneId
import java.time.ZonedDateTime

@Transactional
@SuppressWarnings(['ClosureAsLastMethodParameter', 'BuilderMethodWithSideEffects', 'CyclomaticComplexity', 'ParameterCount', 'MethodCount', 'AbcMetric'])
class QcChangeLogService extends LemansManager {

    FlagService flagService

    FlagValueManagerService flagValueManagerService

    QcConfigService qcConfigService

    private static final String CHANGE_LOG_INSERT_SQL = '''
INSERT 
INTO
    qc.ChangeLog
    (configId, parentEntityId, entityId, approvalStatusId, changedValue, lastApprovedValue, changedDate, changedBy)  
VALUES
    (:configId, :parentEntityId, :entityId, :approvalStatusId, :changedValue, :lastApprovedValue, GETDATE(), :changedBy)
'''

    private static final String CHANGE_LOG_DELETE_SQL = '''
UPDATE  
    qc.ChangeLog 
SET 
    dateDeleted = GETDATE(), 
    deletedBy = :userName
WHERE
    configId = :configId 
    AND entityId = :entityId 
    '''

    private static final String CHANGE_LOG_DELETE_BY_ID_SQL = '''
UPDATE  
    qc.ChangeLog 
SET 
    dateDeleted = GETDATE(), 
    deletedBy = :userName
WHERE
    changeLogId = :changeLogId
'''

    private static final String CHANGE_LOG_UPDATE_BY_ID_TO_A_SQL = '''
UPDATE  
    qc.ChangeLog 
SET 
    approvalStatusId = 13002
WHERE
    changeLogId = :changeLogId
    AND approvalStatusId = 13001
    AND dateDeleted IS NOT NULL
'''

    private static final String CHANGE_LOG_UPDATE_BY_ID_SQL = '''
UPDATE
    qc.ChangeLog
SET
    approvalStatusId = :approvalStatusId,
    qcDate = GETDATE(),
    qcBy = :userName
WHERE
    changeLogId = :changeLogId
'''

    private static final String CHANGE_LOG_UPDATE_BY_ID_TO_PA_SQL = '''
UPDATE
    qc.ChangeLog
SET
    approvalStatusId = 13001,
    changedDate = :changedDate,
    changedBy = :changedBy
WHERE
    changeLogId = :changeLogId
'''

    private final String NOT_APPROVED_OR_REJECTED = '''
    SELECT
        distinct sxr.statusCode
    FROM
        qc.vwChangeLog cl WITH(NOLOCK)
    INNER JOIN
        Common.dbo.StatusXref sxr WITH(NOLOCK)
            ON cl.approvalStatusId = sxr.statusId
            AND sxr.statusType = 'QC_ApprovalStatus\'
            AND sxr.statusCode not IN (
                'A', 'R'
            )
    WHERE
        cl.parentEntityId = :id 
        AND cl.dateDeleted IS NULL
'''

    void qcLogForASingleField(QCSingleParentEntityField qcSingleParentEntityField) {
        QCConfig config = qcSingleParentEntityField.preFetchedConfig ?: qcConfigService.fieldRequiringQualityControl(qcSingleParentEntityField)
        if (config) {
            addLogsWithPendingApproval(qcSingleParentEntityField.changes.collect { new QCSingleFieldChange().apply(qcSingleParentEntityField, it, config) })
        }
    }

    void qcLogForASingleField(QCSingleField qcSingleField) {
        QCConfig config = qcSingleField.preFetchedConfig ?: qcConfigService.fieldRequiringQualityControl(qcSingleField)
        if (config) {
            addLogsWithPendingApproval(qcSingleField.changes.collect { new QCSingleFieldChange().apply(qcSingleField, it, config) })
        }
    }

    void createAndLogForQC(String parentEntityName, String parentEntityId, String entityName, List entityIds, Timestamp date, String username) {
        qcLogForASingleField(
            new QCSingleParentEntityField().with {
                it.parentEntityName = parentEntityName
                it.parentEntityId = parentEntityId
                it.entityName = entityName
                fieldName = 'dateCreated'
                changes = entityIds.collect { id ->
                    new QCEntityChange().with {
                        currentValue = date
                        oldValue = null
                        entityId = id
                        it.username = username
                        it
                    }
                }
                it
            }
        )
    }

    void createAndLogForQC(String entityName, Object entity, String username) {
        saveOrDiscardDomain(entity, username)
        if (!entity.hasErrors()) {
            qcLogForASingleField(
                    new QCSingleParentEntityField().with {
                        it.parentEntityName = entityName
                        it.parentEntityId = entity.id
                        it.entityName = entityName
                        fieldName = 'dateCreated'
                        changes = [
                                new QCEntityChange().with {
                                    currentValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(entity.dateCreated)
                                    oldValue = null
                                    entityId = entity.id
                                    it.username = username
                                    it
                                }
                        ]
                        it
                    }
            )
        }
    }

    void createAndLogForQC(String parentEntityName, String parentEntityId, String entityName, Object entity, String username) {
        saveOrDiscardDomain(entity, username)
        if (!entity.hasErrors()) {
            qcLogForASingleField(
                new QCSingleParentEntityField().with {
                    it.parentEntityName = parentEntityName
                    it.parentEntityId = parentEntityId
                    it.entityName = entityName
                    fieldName = 'dateCreated'
                    changes = [
                        new QCEntityChange().with {
                            currentValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(entity.dateCreated)
                            oldValue = null
                            entityId = entity.id
                            it.username = username
                            it
                        }
                    ]
                    it
                }
            )
        }
    }

    void createAndLogAsUpdateForQC(String parentEntityName, String parentEntityId, String entityName, Object object, String username, Map lastUpdatedValuesToOverwrite = [:]) {
        List<QCSingleFieldChange> changes = []
        qcLogChanges(changes, parentEntityName, parentEntityId, entityName, object, username)
        saveOrDiscardDomain(object, username)
        changes = changes.findAll { (it.currentValue != 'false' && it.currentValue) || it.fieldName in lastUpdatedValuesToOverwrite.keySet()  }
        changes.each { it.entityId = object.id }
        if (lastUpdatedValuesToOverwrite) { changes.each { lastUpdatedValuesToOverwrite[it.fieldName] ? it.oldValue = lastUpdatedValuesToOverwrite[it.fieldName] : null } }
        addLogsWithPendingApproval(changes)
    }

    void updateAndLogForQC(String parentEntityName, String parentEntityId, String entityName, Object object, String username) {
        List changes = []
        qcLogChanges(changes, parentEntityName, parentEntityId, entityName, object, username)
        saveOrDiscardDomain(object, username)
        addLogsWithPendingApproval(changes)
    }

    void updateAndLogForQC(String entityName, Object object, String username) {
        updateAndLogForQC(entityName, object.id.toString(), entityName, object, username)
    }

    void deleteAndLogForQC(String entityName, Object entity, String username) {
        deleteAndLogForQC(entityName, entity.id.toString(), entityName, entity, username)
    }

    void deleteAndLogForQC(String parentEntityName, String parentEntityId, String entityName, Object entity, String username) {
        softDelete(entity, username)
        if (!entity.hasErrors()) {
            qcLogForASingleField(
                new QCSingleParentEntityField().with {
                    it.parentEntityName = parentEntityName
                    it.parentEntityId = parentEntityId
                    it.entityName = entityName
                    fieldName = 'dateDeleted'
                    changes = [
                        new QCEntityChange().with {
                            currentValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(entity.dateDeleted)
                            oldValue = null
                            entityId = entity.id
                            it.username = username
                            it
                        }
                    ]
                    it
                }
            )
        }
    }

    void deleteAndLogForQC(String entityName, List<QCParentEntity> entityIdsWithParentEntityId, Timestamp date, String username) {
        Set<String> parentEntityNames = entityIdsWithParentEntityId*.parentEntityName.toSet()
        List<QCConfig> configs = qcConfigService.fieldRequiringQualityControl(entityName, parentEntityNames)

        addLogsWithPendingApproval(
                (List) entityIdsWithParentEntityId.findResults { QCParentEntity qcParentEntity ->
                QCConfig qcConfig = configs.find { it.fieldName == 'dateDeleted' }
                if (qcConfig) {
                    new QCSingleFieldChange().with {
                        it.configId = qcConfig.configId
                        parentEntityName = qcParentEntity.parentEntityName
                        parentEntityId = qcParentEntity.parentEntityId
                        it.entityName = entityName
                        it.entityId = qcParentEntity.entityId
                        fieldName = 'dateDeleted'
                        currentValue = date
                        oldValue = null
                        it.username = username
                        it
                    }
                }
            }
        )
    }

    void deleteAndLogForQC(String parentEntityName, String entityName, List<QCParentEntity> entityIdsWithParentEntityId, Timestamp date, String username) {
        qcLogForASingleField(
            new QCSingleField().with {
                it.parentEntityName = parentEntityName
                it.entityName = entityName
                fieldName = 'dateDeleted'
                changes = entityIdsWithParentEntityId.collect { QCParentEntity entityWithParent ->
                    new QCParentEntityChange().with {
                        currentValue = date
                        oldValue = null
                        entityId = entityWithParent.entityId
                        parentEntityId = entityWithParent.parentEntityId
                        it.username = username
                        it
                    }
                }
                it
            }
        )
    }

    void deleteAndLogForQC(String parentEntityName, String parentEntityId, String entityName, List<String> entityIds, Timestamp date, String username) {
        qcLogForASingleField(
            new QCSingleParentEntityField().with {
                it.parentEntityName = parentEntityName
                it.parentEntityId = parentEntityId
                it.entityName = entityName
                fieldName = 'dateDeleted'
                changes = entityIds.collect { id ->
                    new QCEntityChange().with {
                        currentValue = date
                        oldValue = null
                        entityId = id
                        it.username = username
                        it
                    }
                }
                it
            }
        )
    }

    void deleteAndLogForQC(String entityAndParentEntityName, String fieldName, String oldValue, List<String> entityAndParentEntityIds, String username) {
        new QCSingleField().with {
            it.parentEntityName = entityAndParentEntityName
            it.entityName = entityAndParentEntityName
            it.fieldName = fieldName
            changes = entityAndParentEntityIds.collect { id ->
                new QCParentEntityChange().with {
                    currentValue = null
                    it.oldValue = oldValue
                    entityId = id
                    parentEntityId = id
                    it.username = username
                    it
                }
            }
            it
        }
    }

    private void qcLogChanges(List changes, String parentEntityName, String parentEntityId, String entityName, Object object, String username) {
        if (!object.hasErrors()) {
            List<QCConfig> fields = qcConfigService.allFieldsRequiringQualityControl(parentEntityName, entityName)
            if (fields) {
                changes.addAll((List<QCSingleFieldChange>)object.getChanges(fields)?.collect { QCSingleFieldChange qcSingleFieldChange ->
                    qcSingleFieldChange.with {
                        it.parentEntityName = parentEntityName
                        it.parentEntityId = parentEntityId
                        it.username = username
                        it
                    }.asMap()
                })
            }
        }
    }

    private void addLogsWithPendingApproval(List<QCSingleFieldChange> changes) {
        persistChangeLogs(changes, 13001)
    }

    private void persistChangeLogs(List<QCSingleFieldChange> changes, Integer approvalStatusId) {
        if (changes) {
            String loggedUserName = changes[0].username
            List changeLogsIdsToBeDeleted = []
            List changeLogsToBeUpdatedToPendingApproval = []
            List changeLogsToBeDeleted = []
            Timestamp now = Timestamp.from(ZonedDateTime.now(ZoneId.of('America/Chicago')).toInstant())

            List changeLogsWithEntityIds = findAllChangeLogsByEntityIds(changes*.entityId, changes*.entityName)
            def (createDeletes, updates) = changeLogsWithEntityIds.split { it.entityField in ['dateCreated', 'dateDeleted'] }
            Map existingChangesForEntity = updates?.groupBy({ it.entityId }, { it.configId })?.collectEntries { k, v ->
                [ k, v.collectEntries { key, value -> [ key, value.sort { it.changeLogId } ] } ]
            }
            Map createOrDeletes = createDeletes?.groupBy({ it.entityField }, { it.entityId })

            int[] batch = sql().withBatch(50, CHANGE_LOG_INSERT_SQL) { stmt ->
                changes.each {
                    boolean saveChange = true

                    List entitiesByConfig = existingChangesForEntity?.getAt(it.entityId)?.getAt(it.configId)

                    String changedValue = it.currentValue
                    String lastApprovedValue = entitiesByConfig ? entitiesByConfig.first().lastApprovedValue : it.oldValue

                    if (entitiesByConfig) {
                        changeLogsIdsToBeDeleted.addAll(entitiesByConfig*.changeLogId) //There is a second update on the same entityId and field
                    }

                    Map createdEntity = createOrDeletes?.getAt('dateCreated')?.getAt(it.entityId.toString())?.first()

                    if (it.fieldName == 'dateDeleted') { //Deleting, find if there are any that are created for the same entityId that are not approved
                        List existingEntitiesForEntityId = existingChangesForEntity[it.entityId.toString()]?.values()?.flatten()

                        if (createdEntity) {
                            changeLogsIdsToBeDeleted << createdEntity.changeLogId //Same entity is created and deleted before approve
                            saveChange = false
                        }

                        if (existingEntitiesForEntityId) {
                            changeLogsIdsToBeDeleted.addAll(existingEntitiesForEntityId*.changeLogId) //Same entity is being deleted, so delete the existing logs but log this delete.
                        }
                    }

                    if (it.fieldName != 'dateDeleted' && it.fieldName != 'dateCreated' && createdEntity) { //Find if there is any update after create before approve
                        if (createdEntity.approvalStatusId != 13001) {
                            changeLogsToBeUpdatedToPendingApproval << [changeLogId: createdEntity.changeLogId, changedDate: now, changedBy: it.username]
                        }
                        saveChange = false  //Same entity is updated after create, so ignore this log.
                    }

                    if (changedValue == lastApprovedValue) {
                        changeLogsToBeDeleted << [configId: it.configId, entityId: it.entityId, userName: it.username] //ChangedValue is Same as the LastApprovedValue
                        saveChange = false
                    }
                    if (saveChange) {
                        stmt.addBatch([configId: it.configId, parentEntityId: it.parentEntityId, entityId: it.entityId, approvalStatusId: approvalStatusId,
                                       changedValue: changedValue, lastApprovedValue: lastApprovedValue, changedDate: now, changedBy: it.username])
                    }
                }
            }
            updateChangeLogsToPendingApproval(changeLogsToBeUpdatedToPendingApproval)
            deleteChangeLogsByConfigAndEntity(changeLogsToBeDeleted)
            deleteChangeLogsById(changeLogsIdsToBeDeleted, loggedUserName)
        }
    }

    private void updateChangeLogsToPendingApproval(List changeLogsToBeUpdated) {
        if (changeLogsToBeUpdated) {
            sql().withBatch(50, CHANGE_LOG_UPDATE_BY_ID_TO_PA_SQL) { stmt ->
                changeLogsToBeUpdated.each {
                    stmt.addBatch([changeLogId: it.changeLogId, changedDate: it.changedDate, changedBy: it.changedBy])
                }
            }
        }
    }

    private deleteChangeLogsById(List changeLogsIdsToBeDeleted, String userName) {
        if (changeLogsIdsToBeDeleted) {
            sql().withBatch(50, CHANGE_LOG_DELETE_BY_ID_SQL) { stmt ->
                changeLogsIdsToBeDeleted.each {
                    stmt.addBatch([changeLogId: it, userName: userName])
                }
            }
        }
    }

    private setApprovedToDeletedPendingChangeLogs(List changeLogsToBeSetToApproved) {
        if (changeLogsToBeSetToApproved) {
            sql().withBatch(50, CHANGE_LOG_UPDATE_BY_ID_TO_A_SQL) { stmt ->
                changeLogsToBeSetToApproved.each {
                    stmt.addBatch([changeLogId: it])
                }
            }
        }
    }

    private void deleteChangeLogsByConfigAndEntity(List changeLogsToBeDeleted) {
        if (changeLogsToBeDeleted) {
            sql().withBatch(50, CHANGE_LOG_DELETE_SQL) { stmt ->
                changeLogsToBeDeleted.each {
                    stmt.addBatch([configId: it.configId, entityId: it.entityId, userName: it.userName])
                }
            }
        }
    }

    void approveOrDeclineChangeLog(List changeLogIds, Integer statusId, String auditUserName) {
        sql().withBatch(50, CHANGE_LOG_UPDATE_BY_ID_SQL) { stmt ->
            changeLogIds.each {
                stmt.addBatch([approvalStatusId: statusId, changeLogId: it, userName: auditUserName])
            }
        }
    }

    void deleteQCPriorityFlag(String entityId, String entityClass, String username) {
        Integer flagId = flagService.findFlags([entityClass: entityClass, flagName: ['QC Priority']])?.results[0]?.flagId
        flagValueManagerService.deleteFlagValues([flagId], entityId, [], username)
    }

    void deleteQCPriorityFlagIfApprovedOrRejected(String entityId, String entityClass, String username) {
        if(isApprovedOrRejected(entityId)) {
            deleteQCPriorityFlag(entityId as String, entityClass,  username)
        }
    }

    List<String> getParentEntityIds(List<Integer> changeLogIds) {
        sql().rows("""
                SELECT 
                    DISTINCT parentEntityId 
                FROM qc.vwChangeLog cl WITH(NOLOCK) 
                WHERE 
                    cl.changeLogId IN ('${changeLogIds.join("', '")}')
                    AND cl.dateDeleted IS NULL
        """)*.parentEntityId
    }

    Boolean hasAllChangeLogsApproved(List<String> parentEntityIds) {
        List hasAllApproved = sql().rows(
                """SELECT 
            cl.*
         FROM qc.changeLog cl WITH(NOLOCK)
         WHERE 
             cl.parentEntityId in ('${parentEntityIds.join("', '")}')
             AND cl.approvalStatusId <> 13002
             AND cl.dateDeleted IS NULL""")*.changeLogId
        hasAllApproved.size() <= 0
    }

    Boolean isApprovedOrRejected(String parentEntityId) {
        List hasAllApproved = sql().rows([id: parentEntityId], NOT_APPROVED_OR_REJECTED)
        hasAllApproved.size() == 0
    }

    private List findAllChangeLogsByEntityIds(List<String> entityIds, List<String> entityNames) {
        if (entityIds) {
            String query = """
SELECT
    cl.changeLogId,
    c.configId,
    c.entity,
    cl.entityId,
    c.entityField,
    cl.approvalStatusId,
    cl.changedValue,
    cl.lastApprovedValue,
    cl.changedDate  
FROM
    qc.vwChangeLog cl WITH(NOLOCK) 
INNER JOIN
    qc.Config c WITH(NOLOCK) 
        ON cl.configId = c.configId     
        AND c.isActive = 1
 
INNER JOIN
    Common.dbo.StatusXref sxr WITH(NOLOCK)  
        ON cl.approvalStatusId = sxr.statusId  
        AND sxr.statusType = 'QC_ApprovalStatus'  
        AND sxr.statusCode IN (
            'PA', 'R'
        ) 
WHERE
    cl.entityId IN ('${entityIds.join('\', \'')}') 
    AND c.entity IN ('${entityNames.join('\', \'')}')
"""
          sql().rows(query)
        }
    }

    void findAndDeleteChangeLogs(String entityName, List parentEntityIds, String userName) {
        List pendingOrDeclinedChangeLogs = getPendingOrDeclinedEntitiesByEntity(entityName, parentEntityIds)
        deleteChangeLogsById(pendingOrDeclinedChangeLogs, userName)
        setApprovedToDeletedPendingChangeLogs(pendingOrDeclinedChangeLogs)
    }

    void findAndDeleteChangeLogs(String entityName, Object entity, List entityIds, String userName) {
        if (!entity.hasErrors()) {
            deleteChangeLogsById(getPendingOrDeclinedEntitiesByEntity(entityName, entityIds), userName)
        }
    }

    private List getPendingOrDeclinedEntitiesByEntity(String entity, List entityIds) {
        List changeLogs = []
        switch (entity) {
            case 'PartAssociation':
               changeLogs = partAssociationChanges(entityIds)
                break
            case 'PartFitment':
                changeLogs = partFitmentChanges(entityIds)
                break
            case 'PartFitmentPosition':
                changeLogs = partFitmentPositionChanges(entityIds)
                break
            case 'OEMPart':
                changeLogs = oemPartChanges(entityIds)
                break
            case 'ProductAssociation':
                changeLogs = productAssociationChanges(entityIds)
                break
            case 'ProductPublicationCategory':
                changeLogs = productPublicationCategoryChanges(entityIds)
                break
            case 'ProductSizeChart':
                changeLogs = productSizeChartChanges(entityIds)
                break
        //TODO: Add for all Product Change Logs
            case 'Keyword':
                changeLogs = keywordChanges(entityIds)
        }
        changeLogs
    }

    private List partAssociationChanges(List entityIds) {
        String query = """
SELECT
    pa.partAssociationId,
    pa.partNumber,
    pa.relatedPartNumber,
    pa.associationTypeId,
    cl.changeLogId,
    c.entity,
    cl.entityId,
    c.entityField,
    cl.configId,
    c.entityGroup,
    cl.changedDate,
    cl.changedValue,
    cl.lastApprovedValue,
    cl.approvalStatusId,
    sxr.statusCode,
    sxr.statusDescr 
FROM
    qc.vwChangeLog cl WITH(NOLOCK) 
INNER JOIN
    qc.Config c WITH(NOLOCK)  
        ON cl.configId = c.configId 
INNER JOIN
    Common.dbo.StatusXref sxr WITH(NOLOCK)  
        ON cl.approvalStatusId = sxr.statusId  
        AND sxr.statusType = 'QC_ApprovalStatus'  
        AND sxr.statusCode IN (
            'PA', 'R'
        ) 
INNER JOIN
    dbo.PartAssociation pa WITH(NOLOCK)  
        ON CAST(pa.partAssociationId AS VARCHAR) = cl.entityId 
WHERE
    c.entity = 'PartAssociation'  
    AND pa.partNumber IN ('${entityIds.join('\', \'')}')
ORDER BY
    changeLogId ASC
"""
        List partAssociationChanges = sql().rows(query)
        List cleanedChangeLogs = []

        partAssociationChanges.groupBy( { it.partNumber }, { it.relatedPartNumber }, { it.associationTypeId } ).each {  String partNumber, Map partAssociationsByPart ->
            partAssociationsByPart.each { String relatedPart, Map partAssociationsByRelatedPart ->
                partAssociationsByRelatedPart.each { Integer associationTypeId, List partAssociationsByType ->
                    cleanedChangeLogs.addAll(cleanLogValues(partAssociationsByType)*.changeLogId)
                }
            }
        }
        List changeLogsThatCanBeDeleted = partAssociationChanges*.changeLogId - cleanedChangeLogs
        changeLogsThatCanBeDeleted
    }

    private List partFitmentChanges(List entityIds) {
        String query = """
SELECT
    pf.partFitmentId,
    pf.partNumber,
    pf.modelYearId,
    my.[year],
    my.modelId,
    cl.changeLogId,
    c.entity,
    cl.entityId,
    c.entityField,
    cl.configId,
    c.entityGroup,
    cl.changedDate,
    cl.changedValue,
    cl.lastApprovedValue,
    cl.approvalStatusId,
    sxr.statusCode,
    sxr.statusDescr  
FROM
    qc.vwChangeLog cl WITH(NOLOCK)  
INNER JOIN
    qc.Config c WITH(NOLOCK)           
        ON cl.configId = c.configId          
        AND c.entity = 'PartFitment' 
INNER JOIN
    Common.dbo.StatusXref sxr WITH(NOLOCK)           
        ON cl.approvalStatusId = sxr.statusId           
        AND sxr.statusType = 'QC_ApprovalStatus'           
        AND sxr.statusCode IN (
            'PA', 'R'         
        )  
INNER JOIN
    dbo.PartFitment pf WITH(NOLOCK)           
        ON CAST(pf.partFitmentId AS VARCHAR) = cl.entityId  
INNER JOIN
    dbo.ModelYear my WITH(NOLOCK)   
        ON pf.modelYearId = my.modelYearId 
WHERE
    pf.partNumber IN ('${entityIds.join('\', \'')}')
ORDER BY
    changeLogId ASC
"""
        List partFitmentChanges = sql().rows(query)
        List cleanedChangeLogs = []

        partFitmentChanges.groupBy( { it.modelId }, { it.year } ).each { Integer modelId, Map partFitmentsByModelId ->
            partFitmentsByModelId.each { Integer modelYear, List partFitmentsByYear ->
                cleanedChangeLogs.addAll(cleanLogValues(partFitmentsByYear)*.changeLogId)
            }
        }
        List changeLogsThatCanBeDeleted = partFitmentChanges*.changeLogId - cleanedChangeLogs
        changeLogsThatCanBeDeleted
    }

    private List partFitmentPositionChanges(List entityIds) {
        String query = """
SELECT
    pf.partFitmentId,
    pf.partNumber,
    pf.modelYearId,
    my.[year],
    pfp.partFitmentPositionId,
    pfp.positionTypeId,
    cl.changeLogId,
    c.entity,
    cl.entityId,
    c.entityField,
    cl.configId,
    c.entityGroup,
    cl.changedDate,
    cl.changedValue,
    cl.lastApprovedValue,
    cl.approvalStatusId,
    sxr.statusCode,
    sxr.statusDescr  
FROM
    qc.vwChangeLog cl WITH(NOLOCK)  
INNER JOIN
    qc.Config c WITH(NOLOCK)           
        ON cl.configId = c.configId          
        AND c.entity = 'PartFitmentPosition' 
INNER JOIN
    Common.dbo.StatusXref sxr WITH(NOLOCK)           
        ON cl.approvalStatusId = sxr.statusId           
        AND sxr.statusType = 'QC_ApprovalStatus'           
        AND sxr.statusCode IN (
            'PA', 'R'         
        )  
INNER JOIN
    dbo.PartFitmentPosition pfp WITH(NOLOCK)   
        ON CAST(pfp.partFitmentPositionId AS VARCHAR) = cl.entityId 
INNER JOIN
    dbo.PartFitment pf WITH(NOLOCK)           
        ON pf.partFitmentId = pfp.partFitmentId 
INNER JOIN
    dbo.ModelYear my WITH(NOLOCK)   
        ON my.modelYearId = pf.modelYearId 
WHERE
    pf.partNumber IN ('${entityIds.join('\', \'')}')
ORDER BY
    changeLogId ASC
"""
        List partFitmentPositions = sql().rows(query)
        List cleanedChangeLogs = []

        partFitmentPositions.groupBy( { it.partNumber }, { it.year }, { it.positionTypeId } ).each {  String partNumber, Map positionsByPart ->
            positionsByPart.each { Integer modelYear, Map positionsByYear ->
                positionsByYear.each { Integer positionTypeId, List positionsByType ->
                    cleanedChangeLogs.addAll(cleanLogValues(positionsByType)*.changeLogId)
                }
            }
        }
        List changeLogsThatCanBeDeleted = partFitmentPositions*.changeLogId - cleanedChangeLogs
        changeLogsThatCanBeDeleted
    }

    private List oemPartChanges(List entityIds) {
        String query = """
SELECT
    op.oemPartId,
    op.oemPartNumber,
    op.makeId,
    op.partNumber,
    cl.changeLogId,
    c.entity,
    cl.entityId,
    c.entityField,
    cl.configId,
    c.entityGroup,
    cl.changedDate,
    cl.changedValue,
    cl.lastApprovedValue,
    cl.approvalStatusId,
    sxr.statusCode,
    sxr.statusDescr 
FROM
    qc.vwChangeLog cl WITH(NOLOCK) 
INNER JOIN
    qc.Config c WITH(NOLOCK)  
        ON cl.configId = c.configId
        AND c.entity = 'OEMPart'
INNER JOIN
    dbo.StatusXref sxr WITH(NOLOCK)  
        ON cl.approvalStatusId = sxr.statusId  
        AND sxr.statusType = 'QC_ApprovalStatus'  
        AND sxr.statusCode IN (
            'PA', 'R'
        ) 
INNER JOIN
    dbo.OEMPart op WITH(NOLOCK)  
        ON CAST(op.oemPartId AS VARCHAR) = cl.entityId 
WHERE
    op.partNumber IN ('${entityIds.join('\', \'')}')
ORDER BY
    changeLogId ASC
"""
        List partOEMPartRelationChanges = sql().rows(query)
        List cleanedChangeLogs = []
        partOEMPartRelationChanges.groupBy({ it.makeId }, { it.oemPartNumber }).each { Integer makeId, Map oemPartsByMake ->
            oemPartsByMake.each { String oemPartNumber, List oemPartsByOemPartNumber ->
                cleanedChangeLogs.addAll(cleanLogValues(oemPartsByOemPartNumber)*.changeLogId)
            }
        }
        List changeLogsThatCanBeDeleted = partOEMPartRelationChanges*.changeLogId - cleanedChangeLogs
        changeLogsThatCanBeDeleted
    }

    private List productAssociationChanges(List entityIds) {
        String query = """
SELECT
    pa.productAssociationId,
    pa.productId,
    pa.relatedProductId,
    pa.associationTypeId,
    cl.changeLogId,
    c.entity,
    cl.entityId,
    c.entityField,
    cl.configId,
    c.entityGroup,
    cl.changedDate,
    cl.changedValue,
    cl.lastApprovedValue,
    cl.approvalStatusId,
    sxr.statusCode,
    sxr.statusDescr 
FROM
    qc.vwChangeLog cl WITH(NOLOCK) 
INNER JOIN
    qc.Config c WITH(NOLOCK)  
        ON cl.configId = c.configId 
INNER JOIN
    Common.dbo.StatusXref sxr WITH(NOLOCK)  
        ON cl.approvalStatusId = sxr.statusId  
        AND sxr.statusType = 'QC_ApprovalStatus'  
        AND sxr.statusCode IN (
            'PA', 'R'
        ) 
INNER JOIN
    dbo.ProductAssociation pa WITH(NOLOCK)  
        ON CAST(pa.productAssociationId AS VARCHAR) = cl.entityId 
WHERE
    c.entity = 'ProductAssociation'  
    AND pa.productId IN (${entityIds.join(',')})
ORDER BY
    changeLogId ASC
"""
        List productAssociationChanges = sql().rows(query)
        List cleanedChangeLogs = []

        productAssociationChanges.groupBy( { it.productId }, { it.relatedProductId }, { it.associationTypeId } ).each {  Integer productId, Map productAssociationByProduct ->
            productAssociationByProduct.each { Integer relatedProduct, Map productAssociationByRelatedProduct ->
                productAssociationByRelatedProduct.each { Integer associationTypeId, List productAssociationsByType ->
                    cleanedChangeLogs.addAll(cleanLogValues(productAssociationsByType)*.changeLogId)
                }
            }
        }
        List changeLogsThatCanBeDeleted = productAssociationChanges*.changeLogId - cleanedChangeLogs
        changeLogsThatCanBeDeleted
    }

    private List productPublicationCategoryChanges(List entityIds) {
        String query = """
SELECT
    ppc.productPublicationCategoryId,
    ppc.categoryId,
    ppc.productId,
    cl.changeLogId,
    c.entity,
    cl.entityId,
    c.entityField,
    cl.configId,
    c.entityGroup,
    cl.changedDate,
    cl.changedValue,
    cl.lastApprovedValue,
    cl.approvalStatusId,
    sxr.statusCode,
    sxr.statusDescr 
FROM
    qc.vwChangeLog cl WITH(NOLOCK) 
INNER JOIN
    qc.Config c WITH(NOLOCK)  
        ON cl.configId = c.configId 
        AND c.entity = 'ProductPublicationCategory'
INNER JOIN
    Common.dbo.StatusXref sxr WITH(NOLOCK)  
        ON cl.approvalStatusId = sxr.statusId  
        AND sxr.statusType = 'QC_ApprovalStatus'  
        AND sxr.statusCode IN (
            'PA', 'R'
        ) 
INNER JOIN
    dbo.ProductPublicationCategory ppc WITH(NOLOCK)  
        ON CAST(ppc.productPublicationCategoryId AS VARCHAR) = cl.entityId 
WHERE
    c.entity = 'ProductPublicationCategory'  
    AND ppc.productId IN (${entityIds.join(',')})
ORDER BY
    changeLogId ASC
"""
        List productPublicationCategoryChanges = sql().rows(query)
        List cleanedChangeLogs = []

        productPublicationCategoryChanges.groupBy( { it.categoryId } ).each {  Integer categoryId, List productPublicationByCategory ->
            cleanedChangeLogs.addAll(cleanLogValues(productPublicationByCategory)*.changeLogId)
        }
        List changeLogsThatCanBeDeleted = productPublicationCategoryChanges*.changeLogId - cleanedChangeLogs
        changeLogsThatCanBeDeleted
    }


    private List productSizeChartChanges(List entityIds) {
        String query = """
SELECT
    ppc.productSizeChartId,
    ppc.productId,
    ppc.sizeChartId,
    cl.changeLogId,
    c.entity,
    cl.entityId,
    c.entityField,
    cl.configId,
    c.entityGroup,
    cl.changedDate,
    cl.changedValue,
    cl.lastApprovedValue,
    cl.approvalStatusId,
    sxr.statusCode,
    sxr.statusDescr 
FROM
    qc.vwChangeLog cl WITH(NOLOCK) 
INNER JOIN
    qc.Config c WITH(NOLOCK)  
        ON cl.configId = c.configId 
        AND c.entity = 'ProductSizeChart'
INNER JOIN
    Common.dbo.StatusXref sxr WITH(NOLOCK)  
        ON cl.approvalStatusId = sxr.statusId  
        AND sxr.statusType = 'QC_ApprovalStatus'  
        AND sxr.statusCode IN (
            'PA', 'R'
        ) 
INNER JOIN
    dbo.ProductSizeChart ppc WITH(NOLOCK)  
        ON CAST(ppc.productSizeChartId AS VARCHAR) = cl.entityId 
WHERE
    c.entity = 'ProductSizeChart'  
    AND ppc.productId IN (${entityIds.join(',')})
ORDER BY
    changeLogId ASC
"""
        List productSizeChartChanges = sql().rows(query)
        List cleanedChangeLogs = []

        productSizeChartChanges.groupBy( { it.productId } ).each {  Integer productId, List productSizeCharts ->
            cleanedChangeLogs.addAll(cleanLogValues(productSizeCharts)*.changeLogId)
        }
        List changeLogsThatCanBeDeleted = productSizeChartChanges*.changeLogId - cleanedChangeLogs
        changeLogsThatCanBeDeleted
    }

    private List keywordChanges(List entityIds) {
        String query = """
SELECT
    k.keywordId,
    k.entityId AS keywordEntity,
    k.keyword,
    k.entity,
    cl.changeLogId,
    c.entity,
    cl.entityId,
    c.entityField,
    cl.configId,
    c.entityGroup,
    cl.changedDate,
    cl.changedValue,
    cl.lastApprovedValue,
    cl.approvalStatusId,
    sxr.statusCode,
    sxr.statusDescr 
FROM
    qc.vwChangeLog cl WITH(NOLOCK) 
INNER JOIN
    qc.Config c WITH(NOLOCK)  
        ON cl.configId = c.configId 
INNER JOIN
    Common.dbo.StatusXref sxr WITH(NOLOCK)  
        ON cl.approvalStatusId = sxr.statusId  
        AND sxr.statusType = 'QC_ApprovalStatus'  
        AND sxr.statusCode IN (
            'PA', 'R'
        ) 
INNER JOIN
    dbo.Keyword k WITH(NOLOCK)  
        ON CAST(k.keywordId AS VARCHAR) = cl.entityId 
WHERE
    c.entity = 'Keyword'  
    AND k.entityId IN ('${entityIds.join('\', \'')}')
ORDER BY
    changeLogId ASC
"""
        List keywordChanges = sql().rows(query)
        List cleanedChangeLogs = []
        keywordChanges.groupBy( { it.keywordEntity }, { it.keyword } ).each {  String keywordEntity, Map keywordsByEntity ->
            keywordsByEntity.each { String keyword, List keywordsByKeyword ->
                cleanedChangeLogs.addAll(cleanLogValues(keywordsByKeyword)*.changeLogId)
            }
        }
        List changeLogsThatCanBeDeleted = keywordChanges*.changeLogId - cleanedChangeLogs
        changeLogsThatCanBeDeleted
    }

    private List cleanLogValues(List values) {
        String previousEntityField
        values.sort { it.changeLogId }.inject([]) { List result, Map it ->
            if (!result.isEmpty() && (it.entityField == 'dateDeleted' || (it.entityField == 'dateCreated' && previousEntityField == 'dateDeleted'))) {
                result.clear()
            } else {
                result << it
            }
            previousEntityField = it.entityField
            result
        }
    }
}