package com.lemans.ds.qualitycontrol

import com.lemans.ds.part.PartManagerService
import com.lemans.services.LemansService
import grails.transaction.Transactional
import org.apache.commons.lang3.concurrent.BasicThreadFactory

import java.util.concurrent.CompletableFuture
import java.util.concurrent.ExecutorService
import java.util.concurrent.Executors
import java.util.concurrent.Future
import java.util.function.Supplier
import java.util.stream.Collectors

@Transactional(readOnly = true)
@SuppressWarnings(['ExplicitCallToGetAtMethod', 'ClosureAsLastMethodParameter', 'GStringExpressionWithinString', 'CyclomaticComplexity', 'AbcMetric'])
class PartReviewService extends LemansService {

    private final ExecutorService pool = Executors.newFixedThreadPool(5, new BasicThreadFactory.Builder()
            .namingPattern('DS-QC-PART-REVIEW-%d')
            .build())

    def partService

    private static final String PART_DETAILS = '''
SELECT
    p.qualifiedCategoryName,
    p.marketingDescr,
    pm.partSpecificText,
    pm.catalogCorrections,
    pm.carbNote,
    pm.certificationUS,
    pm.certificationEU
FROM
    vwPart p WITH(NOLOCK)     
LEFT OUTER JOIN
    vwPartMetaData pm WITH(NOLOCK)         
        ON p.partNumber = pm.partNumber     
WHERE
    p.partNumber = :partNumber
'''
    private static final String PART_REJECTION_QUERY = '''
    SELECT 
        rl.rejectionMessage,
        rl.jiraId
FROM qc.RejectionLog rl WITH(NOLOCK)
    inner join Common.dbo.TypeXref tx WITH(NOLOCK)
        on rl.qcChangeGroupId = tx.typeId and tx.type='Part QC Change Group'
WHERE 
    rl.parentEntity = 'Part'
    AND rl.parentEntityId = :partNumber
    And tx.typeDisplayName = :qcChangeGroupId
'''
    private static final String PART_GLOBAL_REJECTION_QUERY = '''
    SELECT
    rl.rejectionMessage,
    rl.jiraId
FROM
    QC.RejectionLog rl WITH(NOLOCK)
WHERE
    rl.parentEntityId = :partNumber
    And rl.parentEntity = 'Part'
    And rl.qcChangeGroupId IS NULL
'''

    private static final String PART_DETAILS_QC = '''
SELECT
    cl.changeLogId,
    c.entityField,
    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'
        ) 
WHERE
    c.entityGroup = 'PartDetails' 
    AND c.entityField != 'primaryMediaId' 
    AND cl.entityId = :partNumber
'''
    private static final String PART_DETAILS_CATEGORY = '''
SELECT
    cl.qualifiedCategoryName     
FROM
    dbo.Category c WITH(NOLOCK)  
INNER JOIN
    dbo.vwCategoryLevel cl WITH(NOLOCK)  
        ON cl.categoryId = c.categoryId  
WHERE
    c.categoryId = :categoryId
'''

    private static final String RELATED_PARTS = '''
SELECT
    pa.partAssociationId,
    pa.partNumber,
    pa.relatedPartNumber,
    pa.punctuatedRelatedPartNumber,
    pa.relatedPartDescr,
    pa.relatedMediaUrl,
    pa.relatedExtension,
    pa.associationTypeId     
FROM
    vwPartAssociation pa WITH(NOLOCK)     
WHERE
    pa.partNumber = :partNumber
'''
    private static final String PART_ASSOCIATION_TYPES = '''
SELECT
    txr.typeId,
    txr.typeCode,
    txr.typeDescr,
    txr.typeDisplayName
FROM
    Common.dbo.TypeXref txr WITH(NOLOCK) 
WHERE
    [type] = 'PartAssociationType'  
    AND isActive = 1
'''
    private static final String RELATED_PARTS_QC = '''
SELECT
    pa.partAssociationId,
    pa.partNumber,
    pa.relatedPartNumber,
    p.punctuatedPartNumber AS punctuatedRelatedPartNumber,
    p.partDescr AS relatedPartDescr,
    pa.associationTypeId,
    m.mediaUrl AS relatedMediaUrl,
    m.extension AS relatedExtension,
    cl.changeLogId,
    c.entityField,
    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.parentEntity = 'Part'
        AND c.entity = 'PartAssociation'
        AND c.entityGroup = 'RelatedParts' 
        AND cl.parentEntityId = :partNumber 
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 pa.partAssociationId = cl.entityId 
INNER JOIN dbo.Part p WITH(NOLOCK)
        ON pa.relatedPartNumber = p.partNumber
        AND p.dateDeleted IS NULL        
LEFT JOIN dbo.Media m WITH(NOLOCK)
        ON p.primaryMediaId = m.mediaId
        AND m.dateDeleted IS NULL
'''
    private static final String PART_MEDIA = '''
SELECT
    m.mediaId,
    m.mediaUrl,
    m.extension,
    m.description,
    m.originalFileName,
    me.mediaEntityId,
    me.isHidden,
    m.mediaTypeId, 
    mt.typeCode AS mediaTypeCode,
    mt.typeDescr AS mediaTypeDescription,
    me.sequence
FROM
    dbo.Media m WITH(NOLOCK)       
INNER JOIN
    dbo.MediaEntity me WITH(NOLOCK)           
        ON me.entityClass = 'Part'             
        AND me.entityId = :partNumber             
        AND me.mediaId = m.mediaId             
        AND me.dateDeleted IS NULL  
INNER JOIN 
    dbo.TypeXref mt WITH(NOLOCK)
        ON mt.typeId = m.mediaTypeId
        AND mt.dateDeleted IS NULL   
WHERE
    m.dateDeleted IS NULL
'''
    private static final String PART_DETAILS_FOR_MEDIA_QC = '''
SELECT
    cl.changeLogId,
    c.entityField,
    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'
        ) 
WHERE
    c.entityGroup = 'PartDetails' 
    AND cl.entityId = :partNumber
    AND c.entityField = 'primaryMediaId'
'''
    private static final String PART_DETAILS_FOR_MEDIA = '''
SELECT
    p.partNumber,
    p.primaryMediaId 
FROM
    dbo.Part p WITH(NOLOCK)   
WHERE
    p.partNumber = :partNumber
'''
    private static final String PART_MEDIA_QC = '''
SELECT
    m.mediaId,
    m.mediaUrl,
    m.extension,
    m.description,
    m.originalFileName,
    me.mediaEntityId,
    me.isHidden,
    me.sequence,
    m.mediaTypeId, 
    mt.typeCode AS mediaTypeCode,
    mt.typeDescr AS mediaTypeDescription,
    cl.changeLogId,
    c.entityField,
    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.parentEntity = 'Part'
        AND c.entity IN ('MediaEntity', 'Part') 
        AND cl.parentEntityId = :partNumber 
        AND c.entityGroup = 'Media' 
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.MediaEntity me WITH(NOLOCK)           
        ON me.mediaEntityId = cl.entityId 
        AND me.entityClass = 'Part' 
INNER JOIN
    dbo.Media m WITH(NOLOCK)   
        ON m.mediaId = me.mediaId
INNER JOIN 
    dbo.TypeXref mt WITH(NOLOCK)
        ON mt.typeId = m.mediaTypeId
'''

    private static final String PART_ATTRIBUTE_VALUES = '''
SELECT
    pa.partAttributeId,
    pa.attributeNameId,      
    av.attributeValueId,
    av.attributeValue
FROM
    dbo.AttributeValue av WITH (NOLOCK)              
INNER JOIN
    dbo.PartAttribute pa WITH (NOLOCK)                               
        ON av.attributeValueId = pa.attributeValueId                                
        AND pa.dateDeleted IS NULL             
WHERE
    av.dateDeleted IS NULL          
    AND pa.partNumber = '${partNumber}'                   
'''
    private static final String PART_ATTRIBUTE_QC = '''
SELECT
    pa.partAttributeId,
    pa.attributeNameId,
    pa.attributeValueId,
    av.attributeValue,
    cl.changeLogId,
    c.entityField,
    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.parentEntity = 'Part'  
        AND c.entity = 'PartAttribute' 
        AND cl.parentEntityId = '${partNumber}'
        AND c.entityGroup = 'Attributes'  
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.PartAttribute pa WITH(NOLOCK)           
        ON pa.partAttributeId = cl.entityId
INNER JOIN 
    dbo.AttributeValue av WITH (NOLOCK)
        ON av.attributeValueId = pa.attributeValueId  
'''
    private static final String CATEGORY_ATTRIBUTE_NAMES = '''
SELECT
    ca.categoryAttributeId,
    ca.categoryId,
    ca.attributeNameId,
    an.attributeName,
    an.attributeDisplayName,
    ca.[sequence],
    ca.isRequired,
    ca.allowMultipleValues,
    ca.isDropdown,
    ca.isHidden,
    ca.isGroup,
    ca.isKeyAttribute,
    ca.dateDeleted  
FROM
    dbo.CategoryAttribute ca WITH(NOLOCK)   
INNER JOIN
    dbo.AttributeName an WITH(NOLOCK)             
        ON an.attributeNameId = ca.attributeNameId             
        AND an.dateDeleted IS NULL   
INNER JOIN
    dbo.Part p WITH(NOLOCK)             
        ON p.categoryId = ca.categoryId             
        AND p.dateDeleted IS NULL   
WHERE
    ca.dateDeleted IS NULL        
    AND p.partNumber = '${partNumber}'
    
UNION
--Deleted CategoryAttributes that have changes
SELECT
    ca.categoryAttributeId,
    ca.categoryId,
    ca.attributeNameId,
    an.attributeName,
    an.attributeDisplayName,
    ca.[sequence],
    ca.isRequired,
    ca.allowMultipleValues,
    ca.isDropdown,
    ca.isHidden,
    ca.isGroup,
    ca.isKeyAttribute,
    ca.dateDeleted           
FROM
    qc.vwChangeLog cl WITH(NOLOCK)  
INNER JOIN
    qc.Config c WITH(NOLOCK)                    
        ON cl.configId = c.configId            
        AND c.parentEntity = 'Part'         
        AND c.entity = 'PartAttribute'         
        AND cl.parentEntityId = '${partNumber}' 
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.PartAttribute pa WITH(NOLOCK)  
            ON pa.partAttributeId = cl.entityId  
    INNER JOIN
        dbo.Part p WITH(NOLOCK)  
            ON p.partNumber = pa.partNumber   
            AND p.dateDeleted IS NULL 
    INNER JOIN
        dbo.AttributeName an WITH(NOLOCK)  
            ON an.attributeNameId = pa.attributeNameId  
    INNER JOIN
        dbo.CategoryAttribute ca WITH(NOLOCK)  
            ON ca.attributeNameId = an.attributeNameId   
            AND ca.categoryId = p.categoryId   
            AND ca.dateDeleted IS NOT NULL  
'''

    private static final String PART_FITMENT_YEARS = '''
SELECT
    pf.partFitmentId,
    pf.partNumber,
    my.modelYearId,
    my.[year],
    pf.footnote,
    txr.typeDisplayName AS modelReleaseType,
    my.modelId 
FROM
    dbo.PartFitment pf WITH(NOLOCK)  
INNER JOIN
    dbo.ModelYear my WITH(NOLOCK) 
        ON pf.modelYearId = my.modelYearId   
        AND my.dateDeleted IS NULL
LEFT OUTER JOIN
    dbo.vwTypeXref txr WITH(NOLOCK)
        ON pf.modelReleaseTypeId = txr.typeId
        AND txr.type = 'ModelRelease' 
WHERE
    pf.partNumber = '${partNumber}'  
    AND pf.dateDeleted IS NULL
'''
    private static final String PART_FITMENT_POSITIONS = '''
SELECT
    pfp.partFitmentId,
    pf.partNumber,
    pf.modelYearId,
    my.[year],
    my.modelId,
    pfp.partFitmentPositionId,
    pfp.positionTypeId,
    txr.typeDisplayName AS positionType        
FROM
    dbo.PartFitment pf      
INNER JOIN
    dbo.ModelYear my WITH(NOLOCK)                     
        ON pf.modelYearId = my.modelYearId
        AND my.dateDeleted IS NULL   
INNER JOIN
    dbo.PartFitmentPosition pfp WITH(NOLOCK)                     
        ON pfp.partFitmentId = pf.partFitmentId   
        AND pfp.dateDeleted IS NULL
INNER JOIN
    Common.dbo.TypeXref txr                               
        ON pfp.positionTypeId = txr.typeId         
        AND txr.type = 'PositionType'  
WHERE
    pf.partNumber = '${partNumber}'                    
    AND pf.dateDeleted IS NULL   
'''
    private static final String PART_FITMENT_MODELS = '''
SELECT
    m.makeId,
    m.modelId,
    m.modelName,
    m.modelNameFormat,
    m.modelFamilyId,
    mf.familyName,
    m.vehicleTypeId,
    txr.typeDescr AS vehicleTypeDescr   
FROM
    dbo.Model m WITH(NOLOCK)    
LEFT OUTER JOIN
    dbo.ModelFamily mf WITH(NOLOCK)                     
        ON m.modelFamilyId = mf.modelFamilyId                     
        AND mf.dateDeleted IS NULL   
LEFT JOIN
    Common.dbo.TypeXref txr WITH(NOLOCK)                        
        ON m.vehicleTypeId = txr.typeId                       
        AND txr.type = 'VehicleType'                       
        AND txr.dateDeleted IS NULL   
WHERE
    m.modelId IN (
        SELECT
            modelId                     
        FROM
            dbo.ModelYear my WITH(NOLOCK)                    
        INNER JOIN
            dbo.PartFitment pf WITH(NOLOCK)                                     
                ON my.modelYearId = pf.modelYearId                                      
                AND pf.dateDeleted IS NULL                    
        WHERE
            pf.partNumber = '${partNumber}'                            
            AND my.dateDeleted IS NULL                                    
        UNION
        SELECT
            my.modelId                     
        FROM
            qc.vwChangeLog cl WITH(NOLOCK)           
        INNER JOIN
            qc.Config c WITH(NOLOCK)           
                ON cl.configId = c.configId  
                AND cl.parentEntityId = '${partNumber}' 
                AND c.parentEntity = 'Part'     
                AND c.entity = 'PartFitment'           
                AND c.entityField = 'dateDeleted'  
        INNER JOIN 
            dbo.PartFitment pf WITH(NOLOCK)                                       
                ON cl.entityId = pf.partFitmentId       
        INNER JOIN
            dbo.ModelYear my WITH(NOLOCK)
                ON my.modelYearId = pf.modelYearId   
        INNER JOIN
            Common.dbo.StatusXref sxr WITH(NOLOCK)  
                ON cl.approvalStatusId = sxr.statusId  
                AND sxr.statusType = 'QC_ApprovalStatus'  
                AND sxr.statusCode IN (
                    'PA', 'R'
                )                           
    )            
'''
    private static final String PART_FITMENT_MAKES = '''
SELECT
    m.makeId,
    m.makeName,
    m.modelNameFormat  
FROM
    dbo.Make m WITH(NOLOCK)  
WHERE
    m.makeId IN (
        SELECT
            mo.makeId            
        FROM
            dbo.Model mo WITH(NOLOCK)           
        INNER JOIN
            dbo.ModelYear my WITH(NOLOCK)                    
                ON mo.modelId = my.modelId                     
                AND my.dateDeleted IS NULL           
        INNER JOIN
            dbo.PartFitment pf WITH(NOLOCK)                    
                ON my.modelYearId = pf.modelYearId                     
                AND pf.dateDeleted IS NULL           
        WHERE
            pf.partNumber = '${partNumber}'                
            AND my.dateDeleted IS NULL
        UNION
        SELECT
            mo.makeId            
        FROM
            qc.vwChangeLog cl WITH(NOLOCK)           
        INNER JOIN
            qc.Config c WITH(NOLOCK)           
                ON cl.configId = c.configId  
                AND c.parentEntity = 'Part'         
                AND c.entity = 'PartFitment'           
                AND c.entityField = 'dateDeleted'
                AND cl.parentEntityId = '${partNumber}'
        INNER JOIN
            dbo.PartFitment pf WITH(NOLOCK)      
                 ON cl.entityId = pf.partFitmentId                  
        INNER JOIN
            dbo.ModelYear my WITH(NOLOCK)                    
                ON my.modelYearId = pf.modelYearId   
        INNER JOIN            
            dbo.Model mo WITH(NOLOCK)  
                ON mo.modelId = my.modelId  
        INNER JOIN
            Common.dbo.StatusXref sxr WITH(NOLOCK)  
                ON cl.approvalStatusId = sxr.statusId  
                AND sxr.statusType = 'QC_ApprovalStatus'  
                AND sxr.statusCode IN (
                    'PA', 'R'
                )                             
    )       
'''
    private static final String PART_FITMENT_YEARS_QC = '''
SELECT
    pf.partFitmentId,
    pf.partNumber,
    pf.modelYearId,
    my.[year],
    pf.footnote,
    txr.typeDisplayName AS modelReleaseType, 
    my.modelId,
    cl.changeLogId,
    c.entityField,
    cl.changedDate,
    cl.changedValue,
    cl.lastApprovedValue,
    txr1.typeDisplayName AS lastApprovedModelReleaseTypeValue,
    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.parentEntity = 'Part'                  
        AND c.entity = 'PartFitment'  
        AND cl.parentEntityId = '${partNumber}'
        AND c.entityGroup = 'Fitments' 
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 pf.partFitmentId = cl.entityId   
INNER JOIN
    dbo.ModelYear my WITH(NOLOCK)            
        ON pf.modelYearId = my.modelYearId
LEFT OUTER JOIN
    dbo.vwTypeXref txr WITH(NOLOCK)
        ON pf.modelReleaseTypeId = txr.typeId
        AND txr.type = 'ModelRelease'  
LEFT OUTER JOIN
    dbo.vwTypeXref txr1 WITH(NOLOCK)
        ON CAST(txr1.typeId AS CHAR(50)) = cl.lastApprovedValue
        AND c.entityField = 'modelReleaseTypeId'
        AND txr1.type = 'ModelRelease'   
'''
    private static final String PART_FITMENT_YEAR_POSITIONS_QC = '''
SELECT
    pf.partFitmentId,
    pf.partNumber,
    pf.modelYearId,
    my.[year],
    my.modelId,
    pfp.partFitmentPositionId,
    pfp.positionTypeId,
    txr.typeDisplayName AS positionType,
    cl.changeLogId,
    c.entityField,
    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.parentEntity = 'Part'                            
        AND c.entity = 'PartFitmentPosition'
        AND cl.parentEntityId = '${partNumber}'  
        AND c.entityGroup = 'Fitments'    
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.PartFitmentPosition pfp WITH(NOLOCK)                     
        ON pfp.partFitmentPositionId = cl.entityId   
INNER JOIN
    dbo.PartFitment pf WITH(NOLOCK)                             
        ON pfp.partFitmentId = pf.partFitmentId   
INNER JOIN
    dbo.ModelYear my WITH(NOLOCK)                     
        ON pf.modelYearId = my.modelYearId    
INNER JOIN
    Common.dbo.TypeXref txr                               
        ON pfp.positionTypeId = txr.typeId         
        AND txr.type = 'PositionType'      
'''

    private static final String OEM_PARTS = '''
SELECT 
    oem.oemPartId,
    oem.partNumber,
    oem.makeId,
    m.makeName,
    oem.oemPartNumber
FROM 
    dbo.OEMPart oem WITH(NOLOCK)
INNER JOIN
    dbo.Part p WITH(NOLOCK)
        ON p.partNumber = oem.partNumber
        AND p.dateDeleted IS NULL
LEFT JOIN 
    dbo.Make m WITH(NOLOCK)
        ON m.makeId = oem.makeId
WHERE oem.partNumber = :partNumber
    AND oem.dateDeleted IS NULL
'''
    private static final String QC_OEM_PARTS = '''
SELECT
    oem.oemPartId,
    oem.partNumber,
    oem.makeId,
    m.makeName,
    oem.oemPartNumber,
    cl.changeLogId,
    c.entityField,
    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.parentEntity = 'Part'                         
        AND c.entity = 'OEMPart' 
        AND cl.parentEntityId = :partNumber  
        AND c.entityGroup = 'OEMParts' 
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 oem WITH(NOLOCK)
        ON oem.oemPartId = cl.entityId
INNER JOIN 
    dbo.Part p WITH(NOLOCK)
        ON p.partNumber = oem.partNumber
        AND p.dateDeleted IS NULL
LEFT JOIN 
    dbo.Make m WITH(NOLOCK)
        ON m.makeId = oem.makeId
'''

    private static final String PART_KEYWORDS = '''
    SELECT 
    k.keywordId,
    k.entity,
    k.entityId AS partNumber,
    k.keyword
FROM 
    dbo.keyword k WITH(NOLOCK)
INNER JOIN
    dbo.Part p WITH(NOLOCK)
        ON p.partNumber = k.entityId
        AND p.dateDeleted IS NULL
WHERE k.entityId = :partNumber
    AND k.entity = 'part'
    AND k.dateDeleted IS NULL
'''

    private static final String QC_PART_KEYWORDS = '''
SELECT
    k.keywordId,
    k.entity,
    k.entityId As partNumber,
    k.keyword,
    cl.changeLogId,
    c.entityField,
    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.parentEntity = 'Part'                         
        AND c.entity = 'Keyword' 
        AND cl.parentEntityId = :partNumber  
        AND c.entityGroup = 'PartKeywords' 
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.Keyword k WITH(NOLOCK)
        ON k.keywordId = cl.entityId
        AND k.entity = 'Part'
INNER JOIN 
    dbo.Part p WITH(NOLOCK)
        ON p.partNumber = k.entityId
        AND p.dateDeleted IS NULL
'''

    private static final String CERTIFICATION_PARTS = '''
SELECT
    pmd.partNumber,
    pmd.certificationUS,
    pmd.certificationEU
FROM
    dbo.PartMetadata pmd WITH(NOLOCK)
INNER JOIN
    dbo.Part p WITH(NOLOCK)
        ON p.partNumber = pmd.partNumber
        AND p.dateDeleted IS NULL
WHERE pmd.partNumber = :partNumber
'''

    private static final String PART_QC_STATUS = '''
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 IN (
            'PA', 'R'
        )
WHERE
    cl.parentEntityId IN (:partNumber);
'''

    private static final String PART_CATALOG = '''
SELECT [catalogId]
      ,[catalogName]
      ,[catalogInstanceId]
      ,[catalogInstanceName]
      ,[flipBookUrl]
      ,[catalogCode]
      ,[region]
      ,[year]
      ,[partNumber]
      ,[partDescr]
      ,[productId]
      ,[productName]
      ,[pageNumber]
  FROM [PartsSource_DS].[dbo].[vwCatalogPartinfo]
  where
    partNumber= :partNumber and
    activeDate is not null and
    inactiveDate is null and
    PartDateDeleted is null
    and year in (select top 2 year from vwCatalogPartinfo where 
    partNumber = :partNumber and
    activeDate is not null and
    inactiveDate is null and
    PartDateDeleted is null
    group by year order by year desc)
'''

    QCDetailsResponse partDetails(String partNumber) {
        List qcPartDetails = sql().rows([partNumber: partNumber], PART_DETAILS_QC)
        Map partDetails = partService.transformMetadataClobToText(sql().firstRow([partNumber: partNumber], PART_DETAILS))
        Map globalRejectionMessage = partService.transformMetadataClobToText(sql().firstRow([partNumber: partNumber], PART_GLOBAL_REJECTION_QUERY))

        Map qcPartDetailsByField = morphedPartDetails(qcPartDetails?.groupBy { it.entityField }, partDetails)

        List mergedData = partDetails.collect { k, v ->
            Map entity = qcPartDetailsByField[k]?.first() ?: [:]
            if (k in PartManagerService.PART_METADATA_FIELDS) {
                entity = entity ?: (qcPartDetailsByField['dateCreated']?.first() ?: [:]) /*PartMetaData create*/
            }
            new QCDetailsEntity([key: k, value: v] + entity)
        }

        QCDetailsResponse response = new QCDetailsResponse(mergedData, null, globalRejectionMessage)
        if("R" == response?.statusCode) {
            Map rejectionMessage = partService.transformMetadataClobToText(sql().firstRow([partNumber: partNumber, qcChangeGroupId: "Details"], PART_REJECTION_QUERY))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    private Map morphedPartDetails(Map qcLogsByField, Map partDetails) {
        Map oldCategory = partDetailsCategory(qcLogsByField['categoryId']?.first(), partDetails)
        if (oldCategory) {
            qcLogsByField.qualifiedCategoryName = [oldCategory]
        }
        qcLogsByField
    }

    private Map partDetailsCategory(Map categoryChangeLog, Map partDetails) {
        if (categoryChangeLog) {
            Integer categoryId = categoryChangeLog.lastApprovedValue?.toInteger()
            String qualifiedCategoryName = categoryId ? sql().firstRow([categoryId: categoryId], PART_DETAILS_CATEGORY)?.qualifiedCategoryName : null
            categoryChangeLog + [entityField: 'qualifiedCategoryName', changedValue: partDetails.qualifiedCategoryName, lastApprovedValue: qualifiedCategoryName]
        }
    }

    QCHolderResponse partMedia(String partNumber) {
        List partMedia = sql().rows([partNumber: partNumber], PART_MEDIA)
        Integer partPrimaryMediaId = sql().firstRow([partNumber: partNumber], PART_DETAILS_FOR_MEDIA)?.primaryMediaId
        List qcPartDetails = sql().rows([partNumber: partNumber], PART_MEDIA_QC)


        Map qcPartDetailsByMediaEntityId = qcPartDetails.groupBy { it.mediaEntityId }

        Map partPrimaryMediaIdQC = sql().firstRow([partNumber: partNumber], PART_DETAILS_FOR_MEDIA_QC)
        Integer lastApprovedPartPrimaryMediaId = partPrimaryMediaIdQC?.lastApprovedValue?.toInteger()

        List<QCMediaEntity> mergedParts = partMedia.collect {
            boolean wasPrimary = (lastApprovedPartPrimaryMediaId && (lastApprovedPartPrimaryMediaId == it.mediaId))
            boolean isPrimary = (partPrimaryMediaId == it.mediaId)
            new QCMediaEntity(it + [isPrimary: isPrimary, wasPrimary: wasPrimary, entities: [
                                    (qcPartDetailsByMediaEntityId[it.mediaEntityId]?.first() ?: [:]),
                                    (isPrimary || wasPrimary ? partPrimaryMediaIdQC : [:])
                                    ].findAll()])
        } + qcPartDetails.findAll { it.entityField == 'dateDeleted' }.collect {
            boolean wasPrimary = (lastApprovedPartPrimaryMediaId && (lastApprovedPartPrimaryMediaId == it.mediaId))
            new QCMediaEntity(it + [entities: [it, (wasPrimary ? partPrimaryMediaIdQC : [:])].findAll(), wasPrimary: wasPrimary])
        }

        QCHolderResponse response = new QCHolderResponse(mergedParts, null)
        if("R" == response?.statusCode) {
            Map rejectionMessage = partService.transformMetadataClobToText(sql().firstRow([partNumber: partNumber, qcChangeGroupId: "Media"], PART_REJECTION_QUERY))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    QCRelatedResponse relatedParts(String partNumber) {
        List relatedParts = sql().rows([partNumber: partNumber], RELATED_PARTS)
        List partAssociationTypes = sql().rows(PART_ASSOCIATION_TYPES)
        List qcPartDetails = sql().rows([partNumber: partNumber], RELATED_PARTS_QC)


        Map qcPartDetailsByRelatedPartNumber = qcPartDetails.groupBy({ it.relatedPartNumber }, { it.associationTypeId })

        List<QCPartAssociation> mergedParts = relatedParts.collect {
            new QCPartAssociation(it + (qcPartDetailsByRelatedPartNumber.getAt(it.relatedPartNumber)?.getAt(it.associationTypeId)?.first() ?: [:]))
        } + qcPartDetails.findAll { it.entityField == 'dateDeleted' }.collect { new QCPartAssociation(it) }

        QCRelatedResponse response = new QCRelatedResponse(mergedParts, partAssociationTypes, null)
        if("R" == response?.statusCode) {
            Map rejectionMessage = partService.transformMetadataClobToText(sql().firstRow([partNumber: partNumber, qcChangeGroupId: "Related Parts"], PART_REJECTION_QUERY))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    QCPartAttributeResponse partAttributes(String partNumber) {
        List<Future> futures = [
                completableFuture(partNumber, PART_ATTRIBUTE_VALUES),
                completableFuture(partNumber, CATEGORY_ATTRIBUTE_NAMES),
                completableFuture(partNumber, PART_ATTRIBUTE_QC)
        ]

        def (partAttributeValues, categoryAttributeNames, partAttributesQC) = futures.parallelStream().map { it.join() }.collect(Collectors.toList())

        Map partAttributesQCByAttributeValue = partAttributesQC.groupBy({ it.attributeNameId }, { it.attributeValueId })

        List mergedPartAttributes = partAttributeValues.collect {
            new QCPartAttribute(it + (partAttributesQCByAttributeValue?.getAt(it.attributeNameId)?.getAt(it.attributeValueId)?.first() ?: [:]))
        } + partAttributesQC.findAll { it.entityField == 'dateDeleted' }.collect { new QCPartAttribute(it) }

        QCPartAttributeResponse response = new QCPartAttributeResponse(categoryAttributeNames
                .sort { it.dateDeleted }
                .unique(false) { a, b -> a.categoryId <=> b.categoryId ?: a.attributeNameId <=> b.attributeNameId }
                .collect { row ->
                    row.remove('dateDeleted')
                    row
                }, mergedPartAttributes, null)
        if("R" == response?.statusCode) {
            Map rejectionMessage = partService.transformMetadataClobToText(sql().firstRow([partNumber: partNumber, qcChangeGroupId: "Attributes & Values"], PART_REJECTION_QUERY))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    QCPartFitmentResponse partFitments(String partNumber) {
        List<Future> futures = [
                completableFuture(partNumber, PART_FITMENT_YEARS),
                completableFuture(partNumber, PART_FITMENT_POSITIONS),
                completableFuture(partNumber, PART_FITMENT_MODELS),
                completableFuture(partNumber, PART_FITMENT_MAKES),
                completableFuture(partNumber, PART_FITMENT_YEARS_QC),
                completableFuture(partNumber, PART_FITMENT_YEAR_POSITIONS_QC)
        ]
        def (years, fitmentPositions, models, makes, qcYears, qcFitmentPositions) = futures.parallelStream().map { it.join() }.collect(Collectors.toList())

        Map qcFitmentsByYear = qcYears.groupBy({ it.modelId }, { it.year })
        List<QCFitmentYear> mergedPartFitmentYears = years.collect {
            List entities = qcFitmentsByYear?.getAt(it.modelId)?.getAt(it.year)
            new QCFitmentYear(it + [entities: entities])
        } + qcYears.findAll { it.entityField == 'dateDeleted' }.collect { new QCFitmentYear(it + [entities: [it]]) }

        Map qcFitmentPositionsByPosition = qcFitmentPositions.groupBy({ it.modelId }, { it.year }, { it.positionTypeId })
        List<QCFitmentYearPosition> mergedPartFitmentPositions = fitmentPositions.collect {
            new QCFitmentYearPosition(it + (qcFitmentPositionsByPosition?.getAt(it.modelId)?.getAt(it.year)?.getAt(it.positionTypeId)?.first() ?: [:]))
        } + qcFitmentPositions.findAll { it.entityField == 'dateDeleted' }.collect { new QCFitmentYearPosition(it) }

        Map fitmentPositionsByFitmentId = mergedPartFitmentPositions.groupBy({ it.modelId }, { it.year })
        List<QCFitmentYear> yearsWithPositions = mergedPartFitmentYears.collect { QCFitmentYear year ->
            year.positions = fitmentPositionsByFitmentId?.getAt(year.modelId)?.getAt(year.year)
            year
        }

        QCPartFitmentResponse  qcPartFitmentResponse = new QCPartFitmentResponse(makes, models, yearsWithPositions, null)

        if("R" == qcPartFitmentResponse?.statusCode) {
            Map rejectionMessage = partService.transformMetadataClobToText(sql().firstRow([partNumber: partNumber, qcChangeGroupId: "Fitment"], PART_REJECTION_QUERY))
            if(rejectionMessage?.rejectionMessage) {
                qcPartFitmentResponse.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }

        log.error("QC Part Fitment ChangeLogs: ${partNumber} : ${qcPartFitmentResponse.changeLogIds}")
        qcPartFitmentResponse
    }

    OEMQCResponse oemParts(String partNumber) {
        List oemParts = sql().rows([partNumber: partNumber], OEM_PARTS)
        List qcOemParts = sql().rows([partNumber: partNumber], QC_OEM_PARTS)

        Map groupedParts = qcOemParts.groupBy { it.oemPartId }
        List mergedOEMParts = oemParts.collect {
            new QCOEMPart(it + (groupedParts[it?.oemPartId]?.first() ?: [:]))
        } + qcOemParts.findAll { it.entityField == 'dateDeleted' }.collect { new QCOEMPart(it) }

        OEMQCResponse response = new OEMQCResponse(mergedOEMParts, null)
        if("R" == response?.statusCode) {
            Map rejectionMessage = partService.transformMetadataClobToText(sql().firstRow([partNumber: partNumber, qcChangeGroupId: "OEM Part Numbers"], PART_REJECTION_QUERY))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    QCHolderResponse certificationParts(String partNumber) {
        List certificationParts = sql().rows([partNumber: partNumber], CERTIFICATION_PARTS)
        Map rejectionMessage = partService.transformMetadataClobToText(sql().firstRow([partNumber: partNumber, qcChangeGroupId: "Details"], PART_REJECTION_QUERY))

        List<QCPartCertification> qcCertifications =  certificationParts.collect {
            String clobDataUS = (it.certificationUS != null) ? it.certificationUS.asciiStream.text : null;
            String clobDataEU = (it.certificationEU != null) ? it.certificationEU.asciiStream.text : null;
            new QCPartCertification(partNumber:it.partNumber, certificationUS:clobDataUS, certificationEU:clobDataEU)
        }
        new QCHolderResponse(qcCertifications, rejectionMessage)
    }

    QCResponse partKeywords(String partNumber) {
        List partKeywords = sql().rows([partNumber: partNumber], PART_KEYWORDS)
        List qcPartKeywords = sql().rows([partNumber: partNumber], QC_PART_KEYWORDS)

        Map groupedParts = qcPartKeywords.groupBy { it.keywordId }
        List mergedPartKeywords = partKeywords.collect {
            new QCPartKeyword(it + (groupedParts[it?.keywordId]?.first() ?: [:]))
        } + qcPartKeywords.findAll { it.entityField == 'dateDeleted' }.collect { new QCPartKeyword(it) }
        QCResponse response = new QCResponse(mergedPartKeywords, null)
        if("R" == response?.statusCode) {
            Map rejectionMessage = partService.transformMetadataClobToText(sql().firstRow([partNumber: partNumber, qcChangeGroupId: "Keywords"], PART_REJECTION_QUERY))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    private CompletableFuture completableFuture(String partNumber, String query) {
        CompletableFuture.supplyAsync(new Supplier<List>() {
            @Override
            List get() {
                //TODO: Dunno why prepared statement is taking 150 times more time, using templating to convert to string.
                sql().rows(new groovy.text.SimpleTemplateEngine().createTemplate(query).make([partNumber: partNumber]).toString())
            }
        }, pool)
    }

    Map getQcStatus(String partNumber) {
        List qcPartDetails = sql().rows([partNumber: partNumber], PART_QC_STATUS)
        String status
        if(qcPartDetails.size() == 0) {
            status = "QC Approved"
        } else if(qcPartDetails.size() == 1) {
            if(qcPartDetails.get(0).statusCode == "PA") {
                status = "QC Pending Approval"
            } else {
                status = "QC Rejected"
            }
        } else {
            status = "QC Pending Approval with some rejection"
        }
        return [
                status: status
        ]
    }

    List<Map> partCatalog(String partNumber) {
        if (!partNumber) {
            return []
        }

        List<Map> results = sql().rows([partNumber: partNumber], PART_CATALOG)

        results.each { entry ->
            if (entry.pageNumber) {
                entry.pageNumbers = entry.pageNumber.split(',')
                entry.remove('pageNumber')
            }
        }
        return results
    }


}