# MonthlyReportTotalsFullParser.java

    package cloud.wconnect.workperformance.service.hotelkpi.m3;

    import com.fasterxml.jackson.databind.ObjectMapper;
    import com.fasterxml.jackson.databind.SerializationFeature;
    import org.apache.pdfbox.pdmodel.PDDocument;
    import org.apache.pdfbox.text.PDFTextStripper;

    import java.io.IOException;
    import java.io.InputStream;
    import java.util.*;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;

    public class MonthlyReportTotalsFullParser {
        private static final Logger LOGGER = Logger.getLogger(MonthlyReportTotalsFullParser.class.getName());

        // 常量定义
        private static final String CURRENCY_AND_PERCENTAGE_PATTERN = "…?\\(?-?\\$[\\d,]+\\.\\d{0,2}\\)?|\\(?-?\\$[\\d,]+\\)?|\\(?\\d+\\.\\d+%\\)?|\\(?\\d+%\\)?|\\d+\\.\\d+%|\\d+%";
        private static final String PAGE_NUMBER_PATTERN = "\\d+/\\d+";
        private static final String REPORT_HEADER_PATTERN = "F & B Monthly Report Totals.*MLO_Cube";
        private static final int MINIMUM_DATA_FIELDS = 4;
        private static final int EXPECTED_FIELD_COUNT = 6;
        private static final int PAGE_CONTEXT_RANGE = 2;
        private static final String TOTAL_PREFIX = "total";

        private final ObjectMapper objectMapper = new ObjectMapper();
        private final Pattern dataFieldPattern = Pattern.compile(CURRENCY_AND_PERCENTAGE_PATTERN);
        private final Pattern pageNumberPattern = Pattern.compile(PAGE_NUMBER_PATTERN);

        public static class ReportItem {
            public String name;
            public String month_total;
            public String mtd_budget;
            public String mtd_vs_budget_percent;
            public String month_total_ly;
            public String mtd_budget_ly;
        }

        public static class PropertyGroup {
            public String category;
            public String property;
            public List<ReportItem> items = new ArrayList<>();
        }

        private static final List<String> SECTIONS = Arrays.asList(
                "Food & Beverage Revenue",
                "Banquet Revenue",
                "Other Revenue - Gift Shop, Parking & Cafe");

        // 配置数据 - 酒店名称列表
        private static final List<String> HOTEL_NAMES = Arrays.asList(
                "APA HOTEL Woodbridge",
                "Crowne Plaza Edison",
                "Crowne Plaza Newark Airport",
                "Embassy Suites Piscataway-Somerset",
                "Holiday Inn South Plainfield",
                "Garden Executive Hotel",
                "Holiday Inn Newark Intl Airport North");

        public MonthlyReportTotalsFullParser() {
            objectMapper.enable(SerializationFeature.INDENT_OUTPUT);
        }

        public String parse(InputStream in) throws IOException {
            try (PDDocument document = PDDocument.load(in)) {
                PDFTextStripper stripper = new PDFTextStripper();
                String text = stripper.getText(document);

                if (text == null || text.trim().isEmpty()) {
                    throw new IOException("PDF contains no readable text");
                }

                List<String> lines = Arrays.asList(text.split("\r?\n"));
                LOGGER.log(Level.INFO, "Processing PDF with {0} lines", lines.size());

                // 三轮处理流程
                List<String> filteredLines = filterUselessLines(lines);
                List<String> mergedLines = mergeIncompleteLines(filteredLines);

                LOGGER.log(Level.INFO, "After processing: {0} filtered lines, {1} merged lines",
                        new Object[]{filteredLines.size(), mergedLines.size()});

                return parseStructuredData(mergedLines);
            } catch (IOException e) {
                LOGGER.log(Level.SEVERE, "Error processing input stream", e);
                throw e;
            }
        }

        /**
         * 第一轮：过滤页眉、页码、表头等无用行
         */
        private List<String> filterUselessLines(List<String> lines) {
            List<String> filtered = new ArrayList<>();

            for (int i = 0; i < lines.size(); i++) {
                String line = lines.get(i).trim();
                if (line.isEmpty())
                    continue;

                // 检查是否为页码行
                if (isPageNumber(line)) {
                    continue;
                }

                // 检查当前行是否在页码行的影响范围内
                if (isInPageNumberRange(lines, i)) {
                    continue;
                }

                if (shouldSkipLine(line))
                    continue;

                filtered.add(line);
            }

            return filtered;
        }

        /**
         * 判断是否为页码行
         */
        private boolean isPageNumber(String line) {
            return pageNumberPattern.matcher(line).matches();
        }

        /**
         * 判断当前行是否在页码行的影响范围内（前两行或后一行）
         */
        private boolean isInPageNumberRange(List<String> lines, int currentIndex) {
            // 检查后面指定范围内是否有页码
            int endIndex = Math.min(currentIndex + PAGE_CONTEXT_RANGE, lines.size() - 1);
            for (int j = currentIndex + 1; j <= endIndex; j++) {
                if (isPageNumber(lines.get(j).trim())) {
                    return true;
                }
            }

            // 检查前面一行是否有页码
            return currentIndex > 0 && isPageNumber(lines.get(currentIndex - 1).trim());
        }

        /**
         * 判断是否应该跳过该行
         */
        private boolean shouldSkipLine(String line) {
            // 页眉：包含报告标题和时间戳
            if (line.matches(REPORT_HEADER_PATTERN)) {
                return true;
            }

            // 页码：纯数字/数字格式
            if (isPageNumber(line)) {
                return true;
            }

            // 跳过表头行
            return line.contains("Filter statement") || line.contains("Filters") ||
                    line.contains("Months in Date") || line.contains("PropertyName") ||
                    line.contains("Outlet Name") || line.contains("Revenue Name") ||
                    line.contains("Outlet Item Name") || line.contains("Month Total") ||
                    line.contains("MTD Budget") || line.contains("MTD vs Budget");
        }

        /**
         * 第二轮：合并因换行导致的不完整行
         */
        private List<String> mergeIncompleteLines(List<String> filteredLines) {
            List<String> mergedLines = new ArrayList<>();

            for (int i = 0; i < filteredLines.size(); i++) {
                String line = filteredLines.get(i);

                // Section行：直接添加
                if (isSectionLine(line)) {
                    mergedLines.add(line);
                    continue;
                }

                // 数值行：直接添加（不需要合并）
                if (isDataLine(line)) {
                    mergedLines.add(line);
                    continue;
                }

                // 非数值行：需要检查是否需要与后续行合并
                StringBuilder merged = new StringBuilder(line);
                int j = i + 1;
                boolean foundDataLine = false;

                // 向前查找，直到找到数值行或到达文件末尾
                while (j < filteredLines.size()) {
                    String nextLine = filteredLines.get(j).trim();

                    // 如果遇到Section行，停止合并
                    if (isSectionLine(nextLine)) {
                        break;
                    }

                    // 如果遇到数值行，合并并停止
                    if (isDataLine(nextLine)) {
                        merged.append(" ").append(nextLine);
                        foundDataLine = true;
                        i = j; // 跳过已处理的行
                        break;
                    }

                    // 如果是非数值行，继续合并（去掉开头和结尾空格）
                    merged.append(" ").append(nextLine.trim());
                    j++;
                }

                // 如果没有找到数值行，回退到最后一个非数值行
                if (!foundDataLine && j > i + 1) {
                    i = j - 1; // 回退一行，因为最后一行没有被处理
                }

                String mergedLine = merged.toString().trim();
                if (!mergedLine.isEmpty()) {
                    mergedLines.add(mergedLine);
                }
            }

            return mergedLines;
        }

        /**
         * 判断是否为Section行
         */
        private boolean isSectionLine(String line) {
            return SECTIONS.contains(line.trim());
        }

        /**
         * 判断是否为数值行
         */
        private boolean isDataLine(String line) {
            // 数值行特征：包含$符号，且有多个数值字段
            if (!line.contains("$")) {
                return false;
            }

            return countDataFields(line) >= MINIMUM_DATA_FIELDS;
        }

        /**
         * 计算行中的数值字段数量
         */
        private int countDataFields(String line) {
            Matcher matcher = dataFieldPattern.matcher(line);
            int count = 0;
            while (matcher.find()) {
                count++;
            }
            return count;
        }

        /*
         *
         * 第三轮：解析结构化数据
         */

        private String parseStructuredData(List<String> mergedLines) throws IOException {
            String currentSection = null;
            String currentHotel = null;
            Map<String, List<PropertyGroup>> sectionMap = new LinkedHashMap<>();

            for (String line : mergedLines) {
                // Section识别
                if (isSectionLine(line)) {
                    currentSection = line;
                    currentHotel = null; // 重置当前酒店
                    continue;
                }

                if (currentSection == null)
                    continue;

                // 数值行解析
                if (isDataLine(line)) {
                    // 检查是否包含酒店名称
                    String hotel = extractHotelName(line);
                    if (hotel != null) {
                        currentHotel = hotel; // 更新当前酒店
                    }

                    // 如果有当前酒店，则解析数据行
                    if (currentHotel != null) {
                        parseDataRow(sectionMap, currentSection, currentHotel, line);
                    }
                }
            }

            List<PropertyGroup> result = new ArrayList<>();
            sectionMap.values().forEach(result::addAll);

            return objectMapper.writeValueAsString(result);
        }

        /**
         * 提取酒店名称
         */
        private String extractHotelName(String line) {
            // 首先尝试完整匹配
            String exactMatch = HOTEL_NAMES.stream()
                    .filter(line::contains)
                    .findFirst()
                    .orElse(null);

            if (exactMatch != null) {
                return exactMatch;
            }

            // 处理换行导致的不完整酒店名称
            // 检查是否包含酒店名称的开头部分
            for (String hotelName : HOTEL_NAMES) {
                String[] words = hotelName.split("\\s+");
                if (words.length >= 2) {
                    // 检查是否包含酒店名称的前几个词
                    String prefix = words[0] + " " + words[1];
                    if (line.contains(prefix)) {
                        return hotelName; // 返回完整的酒店名称
                    }
                }
            }

            return null;
        }

        /**
         * 解析数据行
         */
        private void parseDataRow(Map<String, List<PropertyGroup>> sectionMap,
                                  String currentSection, String currentHotel, String line) {

            try {
                DataRowInfo rowInfo = extractDataRowInfo(line);
                if (!isValidDataRow(rowInfo)) {
                    return;
                }

                ReportItem item = createReportItem(rowInfo);
                addItemToPropertyGroup(sectionMap, currentSection, currentHotel, item);

            } catch (Exception e) {
                LOGGER.log(Level.WARNING, "Error parsing data row: " + line, e);
            }
        }

        /**
         * 数据行信息封装类
         */
        private static class DataRowInfo {
            boolean isTotal;
            List<String> fields;
            String metricName;

            DataRowInfo(boolean isTotal, List<String> fields, String metricName) {
                this.isTotal = isTotal;
                this.fields = fields;
                this.metricName = metricName;
            }
        }

        /**
         * 提取数据行信息
         */
        private DataRowInfo extractDataRowInfo(String line) {
            String hotel = extractHotelName(line);
            String workingLine = hotel != null ? line.substring(hotel.length()).trim() : line;

            boolean isTotal = workingLine.toLowerCase().startsWith(TOTAL_PREFIX);
            if (isTotal) {
                workingLine = workingLine.substring(TOTAL_PREFIX.length()).trim();
            }

            List<String> fields = extractDataFields(workingLine);
            String metricName = extractMetricName(workingLine);

            return new DataRowInfo(isTotal, fields, metricName);
        }

        /**
         * 提取数值字段
         */
        private List<String> extractDataFields(String line) {
            List<String> fields = new ArrayList<>();
            Matcher matcher = dataFieldPattern.matcher(line);
            while (matcher.find()) {
                fields.add(matcher.group());
            }

            // 补齐到预期字段数量
            while (fields.size() < EXPECTED_FIELD_COUNT) {
                fields.add("");
            }

            return fields;
        }

        /**
         * 提取指标名称
         */
        private String extractMetricName(String line) {
            String metricName = line.replaceAll(CURRENCY_AND_PERCENTAGE_PATTERN, "").trim();

            // 清理酒店名称信息
            metricName = cleanHotelNameFromMetric(metricName);

            // 清理多余空格
            return metricName.replaceAll("\\s+", " ").trim();
        }

        /**
         * 从指标名称中清理酒店名称
         */
        private String cleanHotelNameFromMetric(String metricName) {
            for (String hotelName : HOTEL_NAMES) {
                if (metricName.contains(hotelName)) {
                    int hotelIndex = metricName.indexOf(hotelName);
                    if (hotelIndex >= 0) {
                        return metricName.substring(hotelIndex + hotelName.length()).trim();
                    }
                }
            }

            // 处理换行导致的不完整酒店名称
            // 例如："Embassy Suites Piscataway- Somerset" 应该被清理掉
            String[] hotelParts = {
                    "Embassy Suites Piscataway-",
                    "Embassy Suites Piscataway- Somerset",
                    "Somerset", // 处理换行后的第二部分
                    "APA HOTEL",
                    "Crowne Plaza",
                    "Holiday Inn"
            };

            for (String part : hotelParts) {
                if (metricName.contains(part)) {
                    // 移除这个部分
                    int partIndex = metricName.indexOf(part);
                    String before = metricName.substring(0, partIndex);
                    String after = metricName.substring(partIndex + part.length());
                    return (before + " " + after).trim();
                }
            }

            return metricName;
        }

        /**
         * 验证数据行是否有效
         */
        private boolean isValidDataRow(DataRowInfo rowInfo) {
            if (rowInfo.isTotal) {
                return true;
            }

            return !rowInfo.metricName.isEmpty() &&
                    !rowInfo.metricName.matches("[\\s%().-]+");
        }

        /**
         * 创建报告项
         */
        private ReportItem createReportItem(DataRowInfo rowInfo) {
            ReportItem item = new ReportItem();
            List<String> fields = rowInfo.fields;

            item.name = rowInfo.isTotal ? "Total" : rowInfo.metricName;
            item.month_total = getFieldSafely(fields, 0);
            item.mtd_budget = getFieldSafely(fields, 1);
            item.mtd_vs_budget_percent = getFieldSafely(fields, 2);
            item.month_total_ly = getFieldSafely(fields, 3);
            item.mtd_budget_ly = getFieldSafely(fields, 4);

            return item;
        }

        /**
         * 安全获取字段值
         */
        private String getFieldSafely(List<String> fields, int index) {
            return index < fields.size() ? fields.get(index) : "";
        }

        /**
         * 将项目添加到属性组
         */
        private void addItemToPropertyGroup(Map<String, List<PropertyGroup>> sectionMap,
                                            String currentSection, String currentHotel, ReportItem item) {

            List<PropertyGroup> groupList = sectionMap.computeIfAbsent(currentSection, k -> new ArrayList<>());
            PropertyGroup targetGroup = findOrCreatePropertyGroup(groupList, currentSection, currentHotel);
            targetGroup.items.add(item);
        }

        /**
         * 查找或创建属性组
         */
        private PropertyGroup findOrCreatePropertyGroup(List<PropertyGroup> groupList,
                                                        String currentSection, String currentHotel) {

            return groupList.stream()
                    .filter(g -> g.property.equals(currentHotel))
                    .findFirst()
                    .orElseGet(() -> {
                        PropertyGroup newGroup = new PropertyGroup();
                        newGroup.category = currentSection;
                        newGroup.property = currentHotel;
                        groupList.add(newGroup);
                        return newGroup;
                    });
        }
    }