Subversion Repositories SmartDukaan

package com.spice.profitmandi.service.lms.meta;

import com.spice.profitmandi.common.util.ExcelUtils;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import javax.xml.XMLConstants;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.ByteArrayInputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Reads a Meta lead-ads export into header-keyed rows.
 *
 * <p><b>Why this exists rather than a call to POI.</b> Meta's export is named {@code .xls} but is
 * actually <b>SpreadsheetML 2003</b> — plain XML under
 * {@code urn:schemas-microsoft-com:office:spreadsheet}. Neither HSSF nor XSSF can open it, and every
 * other spreadsheet reader in this codebase hard-codes {@code new XSSFWorkbook(...)}, which throws on
 * it. So the format is detected from the <em>content</em>, never the file extension — this file is
 * the proof that the extension lies.
 *
 * <p><b>Rows are keyed by header name, not column index.</b> A Meta form's custom questions become
 * columns, so two forms produce different layouts; positional access would silently read the wrong
 * field the first time someone exports a different form.
 */
@Component
public class MetaLeadFileParser {

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

    private static final String SPREADSHEETML_NS = "urn:schemas-microsoft-com:office:spreadsheet";

    /** Enough bytes to recognise a format without buffering an entire upload twice. */
    private static final int SNIFF_BYTES = 512;

    /** What the parser produced, plus anything the caller should be told about the file itself. */
    public static class ParseResult {
        public final List<String> headers;
        /** One map per data row, keyed by header. Missing cells are present as empty strings. */
        public final List<Map<String, String>> rows;
        public final String detectedFormat;

        ParseResult(List<String> headers, List<Map<String, String>> rows, String detectedFormat) {
            this.headers = headers;
            this.rows = rows;
            this.detectedFormat = detectedFormat;
        }
    }

    /**
     * @param content   the whole uploaded file
     * @param fileName  used only for error messages — never for format detection
     * @throws IllegalArgumentException with a message fit to show a user
     */
    public ParseResult parse(byte[] content, String fileName) {
        if (content == null || content.length == 0) {
            throw new IllegalArgumentException("The uploaded file is empty.");
        }
        String head = new String(content, 0, Math.min(SNIFF_BYTES, content.length), StandardCharsets.UTF_8);

        try {
            if (head.contains(SPREADSHEETML_NS)) {
                return parseSpreadsheetMl(content);
            }
            if (content.length > 1 && content[0] == 'P' && content[1] == 'K') {
                return parseXlsx(content);
            }
            if (content.length > 7 && (content[0] & 0xFF) == 0xD0 && (content[1] & 0xFF) == 0xCF) {
                // Genuine legacy BIFF .xls. Meta does not produce these; say so plainly rather than
                // letting POI throw something unreadable.
                throw new IllegalArgumentException("This is a legacy binary .xls file. "
                        + "Re-export from Meta, or save it as .xlsx or .csv, and upload again.");
            }
            return parseCsv(content);
        } catch (IllegalArgumentException e) {
            throw e;
        } catch (Exception e) {
            LOGGER.error("Could not parse the uploaded Meta lead file {}", fileName, e);
            throw new IllegalArgumentException("Could not read the file: " + e.getMessage());
        }
    }

    // ---- SpreadsheetML 2003 (what Meta actually exports) --------------------------------------

    private ParseResult parseSpreadsheetMl(byte[] content) throws Exception {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        factory.setNamespaceAware(true);
        // The file is third-party input, so external entities stay off (XXE).
        factory.setFeature(XMLConstants.FEATURE_SECURE_PROCESSING, true);
        factory.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true);
        factory.setExpandEntityReferences(false);

        Document doc = factory.newDocumentBuilder().parse(new ByteArrayInputStream(content));
        NodeList rowNodes = doc.getElementsByTagNameNS(SPREADSHEETML_NS, "Row");
        if (rowNodes.getLength() == 0) {
            throw new IllegalArgumentException("The spreadsheet has no rows.");
        }

        List<List<String>> table = new ArrayList<>();
        for (int i = 0; i < rowNodes.getLength(); i++) {
            table.add(spreadsheetMlCells((Element) rowNodes.item(i)));
        }
        return toResult(table, "SpreadsheetML 2003");
    }

    /**
     * Excel omits empty cells and signals the jump with {@code ss:Index}, so cells must be placed at
     * their declared position. Reading them in document order would shift every later column left —
     * which is exactly how a phone number ends up in the city field.
     */
    private List<String> spreadsheetMlCells(Element row) {
        List<String> values = new ArrayList<>();
        NodeList children = row.getChildNodes();
        for (int i = 0; i < children.getLength(); i++) {
            Node node = children.item(i);
            if (node.getNodeType() != Node.ELEMENT_NODE
                    || !"Cell".equals(node.getLocalName())
                    || !SPREADSHEETML_NS.equals(node.getNamespaceURI())) {
                continue;
            }
            Element cell = (Element) node;
            String index = cell.getAttributeNS(SPREADSHEETML_NS, "Index");
            if (index != null && !index.isEmpty()) {
                int target = Integer.parseInt(index) - 1;
                while (values.size() < target) {
                    values.add("");
                }
            }
            values.add(firstDataText(cell));
        }
        return values;
    }

    private String firstDataText(Element cell) {
        NodeList children = cell.getChildNodes();
        for (int i = 0; i < children.getLength(); i++) {
            Node node = children.item(i);
            if (node.getNodeType() == Node.ELEMENT_NODE
                    && "Data".equals(node.getLocalName())
                    && SPREADSHEETML_NS.equals(node.getNamespaceURI())) {
                String text = node.getTextContent();
                return text == null ? "" : text.trim();
            }
        }
        return "";
    }

    // ---- xlsx ---------------------------------------------------------------------------------

    private ParseResult parseXlsx(byte[] content) throws Exception {
        List<List<String>> table = new ArrayList<>();
        try (XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(content))) {
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                throw new IllegalArgumentException("The workbook has no sheets.");
            }
            for (Row row : sheet) {
                List<String> values = new ArrayList<>();
                // getLastCellNum, not the cell iterator: the iterator skips undefined cells, which
                // would shift columns exactly as the ss:Index case above.
                short last = row.getLastCellNum();
                for (int c = 0; c < last; c++) {
                    Cell cell = row.getCell(c);
                    values.add(cell == null ? "" : ExcelUtils.getCellValue(cell).trim());
                }
                table.add(values);
            }
        }
        return toResult(table, "xlsx");
    }

    // ---- csv ----------------------------------------------------------------------------------

    private ParseResult parseCsv(byte[] content) throws Exception {
        List<List<String>> table = new ArrayList<>();
        // Explicit UTF-8 rather than the platform default, and the BOM is stripped — Excel writes
        // one when saving CSV, and it would otherwise corrupt the first header name.
        int offset = (content.length > 2 && (content[0] & 0xFF) == 0xEF
                && (content[1] & 0xFF) == 0xBB && (content[2] & 0xFF) == 0xBF) ? 3 : 0;
        try (CSVParser parser = new CSVParser(
                new InputStreamReader(
                        new ByteArrayInputStream(content, offset, content.length - offset),
                        StandardCharsets.UTF_8),
                CSVFormat.DEFAULT)) {
            for (CSVRecord record : parser) {
                List<String> values = new ArrayList<>();
                for (int i = 0; i < record.size(); i++) {
                    String v = record.get(i);
                    values.add(v == null ? "" : v.trim());
                }
                table.add(values);
            }
        }
        return toResult(table, "csv");
    }

    // ---- shared -------------------------------------------------------------------------------

    private ParseResult toResult(List<List<String>> table, String format) {
        if (table.isEmpty()) {
            throw new IllegalArgumentException("The file has no rows.");
        }
        List<String> headers = new ArrayList<>();
        for (String raw : table.get(0)) {
            headers.add(raw == null ? "" : raw.trim());
        }
        if (headers.isEmpty()) {
            throw new IllegalArgumentException("The first row has no column headings.");
        }
        if (table.size() < 2) {
            throw new IllegalArgumentException("The file has column headings but no data rows.");
        }

        List<Map<String, String>> rows = new ArrayList<>();
        for (int i = 1; i < table.size(); i++) {
            List<String> cells = table.get(i);
            if (isBlank(cells)) {
                // Trailing blank rows are normal in exports; they are not data and not errors.
                continue;
            }
            Map<String, String> row = new LinkedHashMap<>();
            for (int c = 0; c < headers.size(); c++) {
                String header = headers.get(c);
                if (header.isEmpty()) {
                    continue;
                }
                row.put(header, c < cells.size() && cells.get(c) != null ? cells.get(c) : "");
            }
            rows.add(row);
        }
        LOGGER.info("Parsed a {} Meta lead file: {} columns, {} data rows", format, headers.size(), rows.size());
        return new ParseResult(headers, rows, format);
    }

    private boolean isBlank(List<String> cells) {
        for (String c : cells) {
            if (c != null && !c.trim().isEmpty()) {
                return false;
            }
        }
        return true;
    }
}