Subversion Repositories SmartDukaan

package com.smartdukaan.cron.scheduled;

import com.spice.profitmandi.common.model.ProfitMandiConstants;
import com.spice.profitmandi.dao.entity.auth.AuthUser;
import com.spice.profitmandi.dao.repository.cs.CsService;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.util.Arrays;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * Daily list of smartphone items with no catalog.item.brand_identifier, for brands whose mobile
 * brand_category row (category_id 3) has brand_identifier_required set. Upload keeps the column optional;
 * this report is how the gaps get filled. Excludes feature phones (HSN 85171400), non-channel suppliers
 * ONEJMD 500 / Longlife 515 and "Any Colour" placeholders. Mail body = items listed or in stock with partners/us;
 * CSV = all. Goes to every level of the category team.
 */
@Component
@Transactional(rollbackFor = Throwable.class)
public class BrandIdentifierGapMonitor {

    private static final Logger LOGGER = LogManager.getLogger(BrandIdentifierGapMonitor.class);

    private static final String[] HEADERS = {"Item Id", "Catalog Id", "Brand", "Model", "Color", "Created On", "Live Since", "Listed",
            "Partner Stock", "Our Stock"};

    private static final String GAP_QUERY =
            "SELECT i.id, i.catalog_item_id, b.name, " +
            "TRIM(CONCAT_WS(' ', NULLIF(i.model_name, ''), i.model_number)) model, " +
            "TRIM(REPLACE(REPLACE(i.color, 'f_', ''), 'F_', '')) color, DATE(i.addedOn) created_on, " +
            "(SELECT DATE(MIN(COALESCE(tl.start_date, tl.create_timestamp))) FROM catalog.tag_listing tl WHERE tl.item_id = i.id AND tl.tag_id = 4) live_since, " +
            "IF(EXISTS(SELECT 1 FROM catalog.tag_listing tl WHERE tl.item_id = i.id AND tl.tag_id = 4 AND tl.active = 1), 'Y', 'N') listed, " +
            "COALESCE((SELECT SUM(cis.availability) FROM fofo.current_inventory_snapshot cis WHERE cis.item_id = i.id AND cis.availability > 0), 0) partner_stock, " +
            "COALESCE((SELECT SUM(ii.currentQuantity) FROM warehouse.inventoryItem ii WHERE ii.itemId = i.id AND ii.currentQuantity > 0), 0) our_stock " +
            "FROM catalog.item i " +
            "JOIN catalog.catalog c ON c.id = i.catalog_item_id " +
            "JOIN catalog.brand b ON b.id = c.brand_id " +
            "JOIN catalog.brand_category bc ON bc.brand_id = b.id AND bc.category_id = 3 AND bc.brand_identifier_required = 1 " +
            "WHERE i.category = 10006 AND i.hsnCode IN ('85171300', '85171290') " +
            "AND (i.brand_identifier IS NULL OR TRIM(i.brand_identifier) = '') " +
            "AND i.preferredVendor NOT IN (500, 515) " +
            "AND LOWER(TRIM(REPLACE(REPLACE(i.color, 'f_', ''), 'F_', ''))) <> 'any colour' " +
            "ORDER BY listed DESC, partner_stock + our_stock DESC, i.addedOn DESC, i.id DESC";

    private static final String[] EXTRA_RECIPIENTS = {"alok.divedi@smartdukaan.com", "gaurav.sharma@smartdukaan.com"};

    @Autowired
    private SessionFactory sessionFactory;

    @Autowired
    private CsService csService;

    @Autowired
    @Qualifier(value = "googleMailSender")
    private JavaMailSender googleMailSender;

    @SuppressWarnings("unchecked")
    public void monitor() throws Exception {
        List<Object[]> rows = sessionFactory.getCurrentSession().createNativeQuery(GAP_QUERY).list();
        List<Object[]> liveRows = rows.stream().filter(x -> "Y".equals(String.valueOf(x[7]))
                || ((Number) x[8]).intValue() > 0 || ((Number) x[9]).intValue() > 0).collect(Collectors.toList());
        LOGGER.info("Brand identifier gaps: {} items, {} listed or in stock", rows.size(), liveRows.size());
        if (!rows.isEmpty()) {
            this.sendReport(rows, liveRows);
        }
    }

    private void sendReport(List<Object[]> rows, List<Object[]> liveRows) {
        try {
            StringBuilder sb = new StringBuilder();
            sb.append("<html><body>");
            sb.append("<h3>Missing Brand Identifier</h3>");
            sb.append("<p>Smartphones of flagged brands without a brand identifier: <b>").append(rows.size())
              .append("</b>, of which listed or in stock with partners/us: <b>").append(liveRows.size()).append("</b>.</p>");
            sb.append("<p>All items are in the attached CSV. Listed or in-stock items:</p>");
            sb.append("<table border='1' cellpadding='5' cellspacing='0'><tr>");
            for (String header : HEADERS) {
                sb.append("<th>").append(header).append("</th>");
            }
            sb.append("</tr>");
            for (Object[] row : liveRows) {
                sb.append("<tr>");
                for (Object cell : row) {
                    sb.append("<td>").append(cell != null ? cell.toString() : "").append("</td>");
                }
                sb.append("</tr>");
            }
            sb.append("</table></body></html>");

            MimeMessage message = googleMailSender.createMimeMessage();
            MimeMessageHelper helper = new MimeMessageHelper(message, true);
            helper.setSubject("Missing Brand Identifier - " + LocalDate.now());
            helper.setText(sb.toString(), true);
            helper.addAttachment("missing-brand-identifier-" + LocalDate.now() + ".csv",
                    new ByteArrayResource(this.toCsv(rows).getBytes(StandardCharsets.UTF_8)));
            Set<String> to = csService.getAuthUserByCategoryId(ProfitMandiConstants.TICKET_CATEGORY_CATEGORY).stream()
                    .map(AuthUser::getEmailId).filter(Objects::nonNull).collect(Collectors.toCollection(LinkedHashSet::new));
            to.addAll(Arrays.asList(EXTRA_RECIPIENTS));
            helper.setTo(to.toArray(new String[0]));
            helper.setCc(new String[]{"sdtech@smartdukaan.com", "amit.gupta@smartdukaan.com"});
            helper.setFrom(new InternetAddress("sdtech@smartdukaan.com", "Smart Dukaan"));
            googleMailSender.send(message);
            LOGGER.info("Brand identifier gap report emailed");
        } catch (Exception e) {
            LOGGER.error("Failed to send brand identifier gap report", e);
        }
    }

    private String toCsv(List<Object[]> rows) {
        StringBuilder sb = new StringBuilder(String.join(",", HEADERS)).append("\n");
        for (Object[] row : rows) {
            for (int i = 0; i < row.length; i++) {
                String cell = row[i] != null ? row[i].toString() : "";
                sb.append(i > 0 ? "," : "").append('"').append(cell.replace("\"", "\"\"")).append('"');
            }
            sb.append("\n");
        }
        return sb.toString();
    }
}