package com.lemans.ds.attribute

import com.lemans.services.LemansService
import groovy.json.JsonSlurper
import org.json.XML

import java.sql.Clob

/**
 * Created by vramisetti on 8/18/2017.
 */
class AttributeService extends LemansService {

    private static final String ATTR_FROM = '''FROM vwPartAttribute x INNER JOIN Part p ON x.partNumber = p.partNumber
                                                 LEFT OUTER JOIN dbo.vwCategory c WITH (NOLOCK)
                                                    ON c.categoryId = p.categoryId
                                                 LEFT OUTER JOIN dbo.vwMedia m WITH (NOLOCK)
                                                    ON m.mediaId = p.primaryMediaId'''

    private static final String ATTR_GROUP_BY = '''p.partNumber,x.attributeValueId,
x.attributeValue,  x.partNumber, p.punctuatedPartNumber, p.categoryId, p.partDescr, c.categoryName, m.mediaUrl, c.qualifiedCategoryName, p.isDigiActive'''

    private static final String ATTR_NAME_GROUP_BY = '''p.partNumber, x.attributeNameId, x.attributeName, x.partNumber, p.punctuatedPartNumber, p.categoryId, p.partDescr, c.categoryName, m.mediaUrl, c.qualifiedCategoryName, p.isDigiActive'''

    /**
     * Find parts by attribute name.
     *
     * @param criteria containing attributeNameId, attributeValueId and categoryId
     *
     * @return Map containing results and totalRecords
     */
    Map partsByAttributeName(Map criteria) {
        criteria.groupBy  = ATTR_NAME_GROUP_BY
        partsByAttribute(criteria + [attrSelect: 'x.attributeNameId, x.attributeName',
                                     groupingAttrSelect: 'pa.attributeValue, pa.attributeValueId', groupingAttrName: 'attributeValues'])
    }

    /**
     * Find parts by attribute value.
     *
     * @param criteria containing attributeNameId, attributeValueId and categoryId
     *
     * @return Map containing results and totalRecords
     */
    Map partsByAttributeValue(Map criteria) {
        criteria.groupBy = ATTR_GROUP_BY
        partsByAttribute(criteria + [attrSelect: 'x.attributeValueId, x.attributeValue',
                                     groupingAttrSelect: 'pa.attributeName, pa.attributeNameId', groupingAttrName: 'attributeNames'])
    }

    private Map partsByAttribute(Map criteria) {
        Map data = dqx(criteria).executeSelectFrom(selectQuery(criteria), ATTR_FROM, partsAttributeClauses(criteria))
        addGroupingAttr(criteria, data)
        data << [totalRecords: data.results ? data.results.size() : 0]
        data
    }

    private String selectQuery(Map criteria) {
        """SELECT ${criteria.attrSelect}, x.partNumber, p.punctuatedPartNumber, p.categoryId, p.partDescr, c.categoryName, m.mediaUrl,
        c.qualifiedCategoryName, p.isDigiActive, (
        SELECT DISTINCT ${criteria.groupingAttrSelect}
        FROM vwPartAttribute pa 
        WHERE pa.partNumber = p.partNumber AND ${criteria.attributeNameId ? 
                "pa.attributeNameId = ${criteria.attributeNameId}" : "pa.attributeValueId = ${criteria.attributeValueId}"}
        FOR XML PATH('attributes')) AS attributes"""
    }

    private Map addGroupingAttr(Map criteria, Map data) {
        JsonSlurper slurper = new JsonSlurper()
        data.results.each {
            it[criteria.groupingAttrName] = it.attributes instanceof Clob ? slurper.parseText(XML.toJSONArray(it.attributes.characterStream.text).toString()).attributes : it.attributes
            it.remove('attributes')
        }
        data
    }

    /**
     * Find categories by attributeValue
     *
     * @param criteria containing attributeValueId
     *
     * @return Map containing results and totalRecords
     */
    Map categoriesByAttributeValue(Map criteria) {
        criteria.sorting = criteria.sorting ?: 'categoryAttributeValueId'
        dqx(criteria).executeFrom('vwCategoryAttributeValue', attributeClauses(criteria))
    }

    /**
     * Find categories by attributeName
     *
     * @param criteria containing attributeNameId
     *
     * @return Map containing results and totalRecords
     */
    Map categoriesByAttributeName(Map criteria) {
        criteria.sorting = criteria.sorting ?: 'categoryAttributeId'
        dqx(criteria).executeFrom('vwCategoryAttribute', attributeClauses(criteria))
    }

    private List partsAttributeClauses(Map criteria) {
        List clauses = attributeClauses(criteria)
        if (criteria.categoryId) { clauses << 'p.categoryId = :categoryId' }
        if (criteria.isDigiActive) { clauses << 'p.isDigiActive = :isDigiActive' }
        clauses
    }

    private List attributeClauses(Map criteria) {
        List clauses = []
        if (criteria.attributeNameId) { clauses << 'x.attributeNameId = :attributeNameId' }
        if (criteria.attributeValueId) { clauses << 'x.attributeValueId = :attributeValueId' }
        clauses
    }
}