package com.lemans.ds.qualitycontrol

import com.lemans.ds.qualitycontrol.product.QCProductAssociation
import com.lemans.ds.qualitycontrol.product.QCProductAttribute
import com.lemans.ds.qualitycontrol.product.QCProductAttributeResponse
import com.lemans.ds.qualitycontrol.product.QCProductFeature
import com.lemans.ds.qualitycontrol.product.QCProductKeyword
import com.lemans.ds.qualitycontrol.product.QCProductSizeChart
import com.lemans.ds.qualitycontrol.product.QCPublicationCategories
import com.lemans.services.LemansService
import grails.transaction.Transactional

import com.lemans.ds.qualitycontrol.product.QCProductPart

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

    def productService

    private static final String PRODUCT_DETAILS = '''
    SELECT 
        p.productId,
        p.productName,
        cl.qualifiedCategoryName,
        b.brandName + '[' + LTRIM(STR(b.brandId, 15)) + ']' AS brand
    FROM Product p WITH(NOLOCK)
        INNER JOIN dbo.CatalogInstance ci WITH(NOLOCK)
            ON ci.catalogInstanceId = p.catalogInstanceId
        LEFT OUTER JOIN dbo.Category c WITH(NOLOCK)
            ON c.categoryId = p.categoryId
        INNER JOIN dbo.vwCategoryLevel cl
            ON cl.categoryId = c.categoryId
        LEFT OUTER JOIN dbo.Brand b WITH(NOLOCK)
            ON b.brandId = p.brandId
    WHERE p.productId = :productId
'''

    private static final String PRODUCT_REJECTION_MESSAGE = '''
    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='Product QC Change Group'
    WHERE rl.parentEntity = 'Product'
    AND rl.parentEntityId = :productId
    AND tx.typeDisplayName = :qcChangeGroupName
'''

    private static final String PRODUCT_GLOBAL_REJECTION_QUERY = '''
    SELECT
    rl.rejectionMessage,
    rl.jiraId
FROM
    QC.RejectionLog rl WITH(NOLOCK)
WHERE
    rl.parentEntityId = :productId
    And rl.parentEntity = 'Product'
    And rl.qcChangeGroupId IS NULL
'''

    private static final String PRODUCT_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 
        AND c.parentEntity = 'Product'
        AND c.entity = 'Product'
        AND c.entityGroup = 'ProductDetails' 
        AND cl.entityId = CAST (:productId AS VARCHAR(10))
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 PRODUCT_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 PRODUCT_DETAILS_BRAND = '''
SELECT
    b.brandName     
FROM
    dbo.Brand b WITH(NOLOCK)   
WHERE
    b.brandId = :brandId
'''

    private static final String PRODUCT_PARTS_TO_QC = '''
SELECT
    DISTINCT p.partNumber  
FROM
    dbo.Part p 
INNER JOIN
    dbo.ProductPart pp 
        ON p.partNumber = pp.partNumber  
        AND pp.dateDeleted IS NULL
        AND pp.productId = :productId 
INNER JOIN
    qc.vwChangeLog cl WITH(NOLOCK)
        ON cl.parentEntityId = p.partNumber  
INNER JOIN
    qc.Config c 
        ON cl.configId = c.configId  
        AND c.parentEntity = 'Part' 
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 p.isDigiActive = 1        
'''

    private static final String PRODUCT_PARTS = '''
    SELECT 
        pp.productId,
        pp.productPartId,
        pp.partNumber,
        p.punctuatedPartNumber,
        p.partDescr,
        p.marketingDescr,
        p.qualifiedCategoryName,
        p.mediaUrl,
        p.extension,
        p.isDigiActive
    FROM vwProductPart pp WITH(NOLOCK)
    INNER JOIN vwPart p WITH (NOLOCK)
        ON pp.partNumber = p.partNumber
    WHERE pp.productId = :productId
'''

    private static final String PRODUCT_PARTS_QC = '''
SELECT DISTINCT
    pp.productId,
    pp.productPartId,
    pp.partNumber,
    p.punctuatedPartNumber,
    p.partDescr,
    p.marketingDescr,
    p.qualifiedCategoryName,
    p.isDigiActive,
    m.mediaUrl,
    m.extension,
    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 = 'Product'
        AND c.entity = 'ProductPart'
        AND cl.parentEntityId = CAST (:productId AS VARCHAR(10))
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.ProductPart pp WITH(NOLOCK)  
        ON pp.productPartId = cl.entityId 
INNER JOIN dbo.vwPart p WITH(NOLOCK)
        ON pp.partNumber = p.partNumber
LEFT JOIN dbo.Media m WITH(NOLOCK)
        ON p.primaryMediaId = m.mediaId
        AND m.dateDeleted IS NULL
'''

    private static final String PRODUCT_MEDIA = '''
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
FROM
    dbo.Media m WITH(NOLOCK)       
INNER JOIN
    dbo.MediaEntity me WITH(NOLOCK)           
        ON me.entityClass = 'Product'             
        AND me.mediaId = m.mediaId             
        AND me.dateDeleted IS NULL 
        AND me.entityId = CAST (:productId AS VARCHAR(10))  
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 PRODUCT_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 
        AND c.entityGroup = 'ProductDetails' 
        AND c.entityField = 'primaryMediaId'
        AND cl.entityId = CAST (:productId AS VARCHAR(10))
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 PRODUCT_DETAILS_FOR_MEDIA = '''
SELECT
    p.productId,
    p.primaryMediaId 
FROM
    dbo.Product p WITH(NOLOCK)   
WHERE
    p.productId = :productId
'''
    private static final String PRODUCT_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 = 'Product'
        AND c.entity = 'MediaEntity'
        AND cl.parentEntityId = CAST (:productId AS VARCHAR(10))  
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 = 'Product'
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 PRODUCT_FEATURES = '''
    SELECT 
        pf.productFeatureId,
        pf.productId,
        pf.featureTypeId,
        ft.typeDescr AS featureTypeDescr,
        pf.featureText,
        pf.sequence
    FROM dbo.ProductFeature pf WITH(NOLOCK)
    INNER JOIN 
    dbo.TypeXref ft WITH(NOLOCK)
        ON ft.typeId = pf.featureTypeId
    WHERE pf.productId = :productId
        AND pf.dateDeleted IS NULL
'''

    private static final String PRODUCT_FEATURES_QC = '''
SELECT
    pf.productFeatureId,
    pf.productId,
    pf.featureTypeId,
    ft.typeDescr AS featureTypeDescr,
    pf.featureText,
    pf.sequence,
    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 = 'Product'
        AND c.entity = 'ProductFeature'
        AND cl.parentEntityId = CAST (:productId AS VARCHAR(10))
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.ProductFeature pf WITH(NOLOCK)  
        ON pf.productFeatureId = cl.entityId 
 --       AND pf.dateDeleted IS NULL
INNER JOIN 
    dbo.TypeXref ft WITH(NOLOCK)
        ON ft.typeId = pf.featureTypeId
'''

    private static final String FEATURE_TYPES = '''
SELECT
    txr.typeId,
    txr.typeDescr 
FROM
    dbo.TypeXref txr WITH(NOLOCK) 
WHERE
    txr.type = \'FeatureType\'
'''

    private static final String PRODUCT_CATEGORY_ATTRIBUTES = '''
SELECT 
    pca.productCategoryAttributeId,
    pca.productId,
    pca.categoryAttributeId,
    pca.isDropdown,
    pca.isHidden,
    pca.isSplit,
    pca.isGroup,
    pca.isKeyAttribute,
    
    ca.attributeNameId
    
FROM dbo.ProductCategoryAttribute pca WITH(NOLOCK)
INNER JOIN dbo.Product p WITH(NOLOCK)
        ON p.productId = pca.productId
INNER JOIN dbo.CategoryAttribute ca WITH(NOLOCK)
        ON ca.categoryAttributeId = pca.categoryAttributeId
WHERE p.productId = :productId
    AND pca.dateDeleted IS NULL
'''

    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 
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.Product p WITH(NOLOCK)             
        ON p.categoryId = ca.categoryId             
        AND p.dateDeleted IS NULL   
WHERE
    ca.dateDeleted IS NULL        
    AND p.productId = :productId
    
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        
FROM
    qc.vwChangeLog cl WITH(NOLOCK)
INNER JOIN
    qc.Config c WITH(NOLOCK)  
        ON cl.configId = c.configId 
        AND c.parentEntity = 'Product'
        AND c.entity = 'ProductCategoryAttribute'
        AND cl.parentEntityId = CAST (:productId AS VARCHAR(10))  
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.ProductCategoryAttribute pca WITH(NOLOCK)  
        ON pca.productCategoryAttributeId = cl.entityId 
INNER JOIN dbo.CategoryAttribute ca WITH(NOLOCK)
        ON ca.categoryAttributeId = pca.categoryAttributeId
        AND ca.dateDeleted IS NOT NULL
INNER JOIN
    dbo.AttributeName an WITH(NOLOCK)             
        ON an.attributeNameId = ca.attributeNameId 
'''

    private static final String PRODUCT_CATEGORY_ATTRIBUTE_NAMES_QC = '''
SELECT
    pca.productCategoryAttributeId,
    pca.productId,
    pca.categoryAttributeId,
    pca.isDropdown,
    pca.isHidden,
    pca.isSplit,
    pca.isGroup,
    pca.isKeyAttribute,
    
    ca.attributeNameId,
    
    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 = 'Product'
        AND c.entity = 'ProductCategoryAttribute'
        AND cl.parentEntityId = CAST (:productId AS VARCHAR(10))
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.ProductCategoryAttribute pca WITH(NOLOCK)  
        ON pca.productCategoryAttributeId = cl.entityId 
INNER JOIN dbo.CategoryAttribute ca WITH(NOLOCK)
        ON ca.categoryAttributeId = pca.categoryAttributeId
'''

    private static final String RELATED_PRODUCTS = '''
SELECT
    pa.productAssociationId,
    pa.productId,
    pa.relatedProductId,
    pa.relatedProductName,
    pa.relatedMediaUrl,
    pa.relatedExtension,
    pa.associationTypeId     
FROM
    vwProductAssociation pa WITH(NOLOCK)     
WHERE
    pa.productId = :productId
'''
    private static final String PRODUCT_ASSOCIATION_TYPES = '''
SELECT
    txr.typeId,
    txr.typeCode,
    txr.typeDescr,
    txr.typeDisplayName
FROM
    Common.dbo.TypeXref txr WITH(NOLOCK) 
WHERE
    [type] = 'ProductAssociationType'  
    AND isActive = 1
'''
    private static final String RELATED_PRODUCTS_QC = '''
SELECT
    pa.productAssociationId,
    pa.productId,
    pa.relatedProductId,
    p.productId AS relatedProductId,
    p.productName AS relatedProductName,
    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 = 'Product'
        AND c.entity = 'ProductAssociation'
        AND cl.parentEntityId = CAST (:productId AS VARCHAR(10))
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 pa.productAssociationId = cl.entityId 
INNER JOIN dbo.Product p WITH(NOLOCK) 
        ON pa.relatedProductId = p.productId
        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 PRODUCT_PUBLICATION_CATEGORIES = '''
    SELECT
        ppc.productId,
        ppc.categoryId, 
        ppc.qualifiedCategoryName,
        ppc.productPublicationCategoryId
    FROM vwProductPublicationCategory ppc
    WHERE ppc.productId = :productId
'''
    private static final String PRODUCT_PUBLICATION_CATEGORIES_QC = '''
SELECT
    pcl.qualifiedCategoryName,
    ppc.productPublicationCategoryId,
    ppc.productId,
    ppc.categoryId,
    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 = 'Product'
        AND c.entity = 'ProductPublicationCategory'
        AND cl.parentEntityId = CAST (:productId AS VARCHAR(10)) 
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 ppc.productPublicationCategoryId = cl.entityId
INNER JOIN dbo.vwPublicationCategoryLevel pcl
        ON ppc.categoryId = pcl.categoryId
'''
    private static final String PRODUCT_KEYWORDS = '''
    SELECT 
    k.keywordId,
    k.entity,
    k.entityId AS productId,
    k.keyword
FROM 
    dbo.keyword k WITH(NOLOCK)
INNER JOIN
    dbo.Product p WITH(NOLOCK)
        ON p.productId = k.entityId
        AND p.dateDeleted IS NULL
WHERE k.entityId = CAST (:productId AS VARCHAR(10))
    AND k.entity = 'Product'
    AND k.dateDeleted IS NULL
'''

    private static final String QC_PRODUCT_KEYWORDS = '''
SELECT
    k.keywordId,
    k.entity,
    k.entityId As productId,
    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 = 'Product'                         
        AND c.entity = 'Keyword' 
        AND cl.parentEntityId = CAST (:productId AS VARCHAR(10))  
        AND c.entityGroup = 'ProductKeywords' 
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 = 'Product'
INNER JOIN 
    dbo.Product p WITH(NOLOCK)
        ON p.productId = k.entityId
        AND p.dateDeleted IS NULL
'''

    private static final String PRODUCT_SIZE_CHARTS = '''
SELECT
    psc.productId,
    psc.sizeChartId,
    sc.name AS sizeChartName,
    sc.description AS sizeChartDescription,
    sc.sourceUrl AS sizeChartSourceUrl,
    psc.isHidden
FROM
    vwProductSizeChart psc
INNER JOIN
    SizeChart sc WITH(NOLOCK)
        ON psc.sizeChartId = sc.sizeChartId
WHERE
    psc.productId = :productId
'''

    private static final String QC_PRODUCT_SIZE_CHARTS = '''
SELECT
    psc.productSizeChartId,
    psc.productId,
    psc.sizeChartId,
    psc.isHidden,
    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 = 'Product'                         
        AND c.entity = 'ProductSizeChart' 
        AND cl.parentEntityId = CAST (:productId AS VARCHAR(10))  
        AND c.entityGroup = 'SizeCharts' 
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.ProductSizeChart psc WITH(NOLOCK)
        ON psc.productSizeChartId = cl.entityId
'''

    private static final String PRODUCT_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 (:productId);
'''

    def reviewByProduct(Integer productId) {
        productDetails(productId).properties + [parts: productParts(productId),
                media: productMedia(productId), features: productFeatures(productId),
                merchandising: productMerchandising(productId), relatedProducts: relatedProducts(productId),
                publicationCategories: productPublicationCategories(productId), splitProducts: productSplits(productId)
        ]
    }

    QCDetailsResponse productDetails(Integer productId) {
        boolean productCreated
        boolean productDeleted
        Map productStatus
        List qcProductDetails = sql().rows([productId: productId], PRODUCT_DETAILS_QC).collect {
            if (it.entityField == 'dateCreated') {
                productCreated = true
                productStatus = it.subMap(['statusCode', 'approvalStatusId', 'statusDescr'])
                it.entityField = 'productId'
                it.changedValue = productId
                it.lastApprovedValue = null
            }
            if (it.entityField == 'dateDeleted') {
                productStatus = it.subMap(['statusCode', 'approvalStatusId', 'statusDescr'])
                productDeleted = true
                it.entityField = 'productId'
                it.lastApprovedValue = productId
                it.changedValue = null
            }
            it
        }
        Map productDetails = sql().firstRow([productId: productId], PRODUCT_DETAILS)

        Map globalRejectionMessage = sql().firstRow([productId: productId], PRODUCT_GLOBAL_REJECTION_QUERY)
        Map qcProductDetailsByField = morphedProductDetails(qcProductDetails?.groupBy { it.entityField }, productDetails)

        List mergedData = productDetails.collect { k, v -> new QCDetailsEntity([key: k, value: v] + (qcProductDetailsByField[k]?.first() ?: [:])) }
        if (productDeleted) {
            mergedData.every {
                it.statusCode = productStatus.statusCode
                it.approvalStatusId = productStatus.approvalStatusId
                it.statusDescr = productStatus.statusDescr
                it.isDeleted = true
            }
        }
        if (productCreated) {
            mergedData.every {
                it.statusCode = productStatus.statusCode
                it.approvalStatusId = productStatus.approvalStatusId
                it.statusDescr = productStatus.statusDescr
                it.isCreated = true
            }
        }
        QCDetailsResponse response = new QCDetailsResponse(mergedData, null, transformClobToText(globalRejectionMessage) )

        if("R" == response?.statusCode) {
            Map rejectionMessage = transformClobToText(sql().firstRow([productId: productId, qcChangeGroupName: "Details"], PRODUCT_REJECTION_MESSAGE))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    private Map morphedProductDetails(Map qcLogsByField, Map productDetails) {
        Map oldCategory = productDetailsCategory(qcLogsByField['categoryId']?.first(), productDetails)
        if (oldCategory) {
            qcLogsByField.qualifiedCategoryName = [oldCategory]
        }

        Map oldBrand = productDetailsBrand(qcLogsByField['brandId']?.first(), productDetails)
        if (oldBrand) {
            qcLogsByField.brand = [oldBrand]
        }
        qcLogsByField
    }

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

    private Map productDetailsBrand(Map brandChangeLog, Map productDetails) {
        if (brandChangeLog) {
            Integer brandId = brandChangeLog.lastApprovedValue?.toInteger()
            String brandName = brandId ? sql().firstRow([brandId: brandId], PRODUCT_DETAILS_BRAND)?.brandName : null
            brandChangeLog + [entityField: 'brand', changedValue: productDetails.brand, lastApprovedValue: "${brandName}[$brandId]"]
        }
    }

    QCResponse productParts(Integer productId) {
        List productParts = sql().rows([productId: productId], PRODUCT_PARTS)
        List qcProductPartDetails = sql().rows([productId: productId], PRODUCT_PARTS_QC)

        Map qcProductPartsByProductId = qcProductPartDetails.groupBy({ it.partNumber })

        List<QCProductPart> mergedParts = productParts.collect {
            new QCProductPart(it + (qcProductPartsByProductId?.getAt(it.partNumber)?.first() ?: [:]))
        } + qcProductPartDetails.findAll { it.entityField == 'dateDeleted' }.collect { new QCProductPart(it) }

        QCResponse response = new QCResponse(mergedParts.unique { it.partNumber }, null)

        if("R" == response?.statusCode) {
            Map rejectionMessage = transformClobToText(sql().firstRow([productId: productId, qcChangeGroupName: "ProductParts"], PRODUCT_REJECTION_MESSAGE))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    List<String> productPartsToQC(Integer productId) {
        sql().rows([productId: productId], PRODUCT_PARTS_TO_QC)*.partNumber
    }

    QCHolderResponse productMedia(Integer productId) {
        List productMedia = sql().rows([productId: productId], PRODUCT_MEDIA)
        Integer productPrimaryMediaId = sql().firstRow([productId: productId], PRODUCT_DETAILS_FOR_MEDIA)?.primaryMediaId
        List qcProductDetails = sql().rows([productId: productId], PRODUCT_MEDIA_QC)

        Map qcProductDetailsByMediaEntityId = qcProductDetails.groupBy { it.mediaEntityId }

        Map productPrimaryMediaIdQC = sql().firstRow([productId: productId], PRODUCT_DETAILS_FOR_MEDIA_QC)
        Integer lastApprovedProductPrimaryMediaId = productPrimaryMediaIdQC?.lastApprovedValue?.toInteger()

        List<QCMediaEntity> mergedProducts = productMedia.collect {
            boolean wasPrimary = (lastApprovedProductPrimaryMediaId && (lastApprovedProductPrimaryMediaId == it.mediaId))
            boolean isPrimary = (productPrimaryMediaId == it.mediaId)
            new QCMediaEntity(it + [isPrimary: isPrimary, wasPrimary: wasPrimary, entities: [
                    (qcProductDetailsByMediaEntityId[it.mediaEntityId]?.first() ?: [:]),
                    (isPrimary || wasPrimary ? productPrimaryMediaIdQC : [:])
            ].findAll()])
        } + qcProductDetails.findAll { it.entityField == 'dateDeleted' }.collect {
            boolean wasPrimary = (lastApprovedProductPrimaryMediaId && (lastApprovedProductPrimaryMediaId == it.mediaId))
            new QCMediaEntity(it + [entities: [it, (wasPrimary ? productPrimaryMediaIdQC : [:])].findAll(), wasPrimary: wasPrimary])
        }

        QCHolderResponse response = new QCHolderResponse(mergedProducts, null)

        if("R" == response?.statusCode) {
            Map rejectionMessage = transformClobToText(sql().firstRow([productId: productId, qcChangeGroupName: "ProductParts"], PRODUCT_REJECTION_MESSAGE))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    QCHolderResponse productFeatures(Integer productId) {
        List productFeatures = sql().rows([productId: productId], PRODUCT_FEATURES)
        List qcProductFeaturesQc = sql().rows([productId: productId], PRODUCT_FEATURES_QC)
        Map featureTypeById = sql().rows(FEATURE_TYPES).collectEntries { [it.typeId.toString(), it.typeDescr] }
        qcProductFeaturesQc.findAll { it.entityField == 'featureTypeId' }.each {
            it.changedValue = featureTypeById[it.changedValue]
            it.lastApprovedValue = featureTypeById[it.lastApprovedValue]
        }

        Map qcProductPartsByProductId = qcProductFeaturesQc.groupBy({ it.productFeatureId }, { it.featureTypeId })

        List<QCProductFeature> mergedProducts = productFeatures.collect {
            new QCProductFeature(it + ([entities: qcProductPartsByProductId.getAt(it.productFeatureId)?.getAt(it.featureTypeId)]))
        } + qcProductFeaturesQc.findAll { it.entityField == 'dateDeleted' }.collect { new QCProductFeature(it + ([entities: [it]])) }

        QCHolderResponse response = new QCHolderResponse(mergedProducts.sort { it.sequence }, null )

        if("R" == response?.statusCode) {
            Map rejectionMessage = transformClobToText(sql().firstRow([productId: productId, qcChangeGroupName: "Product Features"], PRODUCT_REJECTION_MESSAGE))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    QCProductAttributeResponse productMerchandising(Integer productId) {
        List productCategoryAttributes = sql().rows([productId: productId], PRODUCT_CATEGORY_ATTRIBUTES)
        List categoryAttributeNames = sql().rows([productId: productId], CATEGORY_ATTRIBUTE_NAMES)
        List productCategoryAttributesQC = sql().rows([productId: productId], PRODUCT_CATEGORY_ATTRIBUTE_NAMES_QC)

        Map productCategoryAttributesByCategoryAttributeId = productCategoryAttributesQC.groupBy({ it.categoryAttributeId })

        List mergedProductCategoryAttributes = productCategoryAttributes.collect {
            new QCProductAttribute(([entities:  productCategoryAttributesByCategoryAttributeId?.getAt(it.categoryAttributeId)]) + it)
        }

        QCProductAttributeResponse response =new QCProductAttributeResponse(categoryAttributeNames, mergedProductCategoryAttributes, null)

        if("R" == response?.statusCode) {
            Map rejectionMessage = transformClobToText(sql().firstRow([productId: productId, qcChangeGroupName: "Merchandising"], PRODUCT_REJECTION_MESSAGE))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    QCRelatedResponse relatedProducts(Integer productId) {
        List relatedProducts = sql().rows([productId: productId], RELATED_PRODUCTS)
        List productAssociationTypes = sql().rows(PRODUCT_ASSOCIATION_TYPES)
        List qcProductDetails = sql().rows([productId: productId], RELATED_PRODUCTS_QC)

        Map qcProductDetailsByRelatedProductId = qcProductDetails.groupBy({ it.relatedProductId }, { it.associationTypeId })

        List<QCProductAssociation> mergedProducts = relatedProducts.collect {
            new QCProductAssociation(it + (qcProductDetailsByRelatedProductId.getAt(it.relatedProductId)?.getAt(it.associationTypeId)?.first() ?: [:]))
        } + qcProductDetails.findAll { it.entityField == 'dateDeleted' }.collect { new QCProductAssociation(it) }

        QCRelatedResponse response = new QCRelatedResponse(mergedProducts, productAssociationTypes, null)

        if("R" == response?.statusCode) {
            Map rejectionMessage = transformClobToText(sql().firstRow([productId: productId, qcChangeGroupName: "Related Products"], PRODUCT_REJECTION_MESSAGE))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    QCResponse productPublicationCategories(Integer productId) {
        List productPublicationCategories = sql().rows([productId: productId], PRODUCT_PUBLICATION_CATEGORIES)
        List qcProductPublicationCategoriesQC = sql().rows([productId: productId], PRODUCT_PUBLICATION_CATEGORIES_QC)

        Map qcProductPublicationsByCategoryId = qcProductPublicationCategoriesQC.groupBy({ it.categoryId })

        List<QCPublicationCategories> mergedProducts = productPublicationCategories.collect {
            new QCPublicationCategories(it + (qcProductPublicationsByCategoryId.getAt(it.categoryId)?.first() ?: [:]))
        } + qcProductPublicationCategoriesQC.findAll { it.entityField == 'dateDeleted' }.collect { new QCPublicationCategories(it) }

        QCResponse response = new QCResponse(mergedProducts, null)

        if("R" == response?.statusCode) {
            Map rejectionMessage = transformClobToText(sql().firstRow([productId: productId, qcChangeGroupName: "Publication Categories"], PRODUCT_REJECTION_MESSAGE))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    Map productSplits(Integer productId) {
        productService.splitValidationForProduct(productId, [:])
    }

    QCResponse productKeywords(Integer productId) {
        List productKeywords = sql().rows([productId: productId], PRODUCT_KEYWORDS)
        List qcProductKeywords = sql().rows([productId: productId], QC_PRODUCT_KEYWORDS)
        Map groupedProducts = qcProductKeywords.groupBy { it.keywordId }
        List mergedProductKeywords = productKeywords.collect {
            new QCProductKeyword(it + (groupedProducts[it?.keywordId]?.first() ?: [:]))
        } + qcProductKeywords.findAll { it.entityField == 'dateDeleted' }.collect { new QCProductKeyword(it) }
        QCResponse response = new QCResponse(mergedProductKeywords, null)

        if("R" == response?.statusCode) {
            Map rejectionMessage = transformClobToText(sql().firstRow([productId: productId, qcChangeGroupName: "Keywords"], PRODUCT_REJECTION_MESSAGE))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    QCResponse productSizeCharts(Integer productId) {
        List productSizeCharts = sql().rows([productId: productId], PRODUCT_SIZE_CHARTS)
        List qcProductSizeCharts = sql().rows([productId: productId], QC_PRODUCT_SIZE_CHARTS)
        Map groupedProducts = qcProductSizeCharts.groupBy { it.sizeChartId }
        List mergedProductSizeCharts = productSizeCharts.collect {
            new QCProductSizeChart(it + (groupedProducts[it?.sizeChartId]?.first() ?: [:]))
        } + qcProductSizeCharts.findAll { it.entityField == 'dateDeleted' }.collect { new QCProductSizeChart(it) }
        QCResponse response = new QCResponse(mergedProductSizeCharts,null)

        if("R" == response?.statusCode) {
            Map rejectionMessage = transformClobToText(sql().firstRow([productId: productId, qcChangeGroupName: "Size Chart"], PRODUCT_REJECTION_MESSAGE))
            if(rejectionMessage?.rejectionMessage) {
                response.rejectionMessage = rejectionMessage?.rejectionMessage
            }
        }
        response
    }

    private Map transformClobToText(Map data) {
        data?.collectEntries { k, v ->
            if (v?.class?.simpleName == 'ClobImpl') { [k, v.characterStream.text] }
            else { [k, v] }
        }
    }

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