package com.trafficaudit.reportexport.layout;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.GregorianCalendar;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 汇总大表「按月扩列器」（Step 1，2026-10-09 首次实现）。
 *
 * <p>把 N 月母版推进到 N+1 月版式（不预留空列）：在「累计列」前插入 2 列（当月值列 + 同比列），
 * 克隆两行纵向合并标题、重写累计枚举与累计同比分母、右移 2025/2024/2023 历史块及其公式引用，
 * 并同步平移「其它页指向已扩列页、列号 >= 插入列」的跨页引用。
 *
 * <p>规则依据：{@code docs/汇总大表母版扩列配置_核对版_2026-10-09.md}（8 个可见台账页；
 * 4 个隐藏页保留隐藏、不扩列，但其跨页引用需同步平移）。只做版式，当月数据列留空。
 */
@Slf4j
@Component
public class SummaryLayoutExpander {

    private static final List<String> EXPAND_PAGES = Arrays.asList(
            " 货运", "班线包车", "城市客运", "公交", "出租", "网约车", "轨道、轮渡", "中口径明细");

    private static final int HEADER_SCAN_ROWS = 15;

    /** 标题由程序在出表时现写的 7 个页签（母版里的标题文字需按母版期次重写）。 */
    private static final List<String> TITLE_PAGES = Arrays.asList(
            "货运量排名", "货运周转量排名", "城市汇总", "城市分市州", "中口径排名", "中口径分析", "道路运输周转量");

    /** 标题识别标记（命中其一才改写，避免误伤备注等文字）。 */
    private static final List<String> TITLE_MARKERS = Arrays.asList(
            "全省分市州累计完成公路货运量情况", "全省分市州完成公路货运量情况",
            "全省分市州累计完成公路货物运输周转量情况", "全省分市州完成公路货物运输周转量情况",
            "全省累计完成城市客运量情况", "全省完成城市客运量情况",
            "分市州1-", "全省分市州累计完成道路客运生产情况", "全省分市州完成道路客运生产情况",
            "中口径客运量及周转量",
            "全省累计完成交通周转量情况", "全省完成交通周转量情况");

    /** 公式里的 A1 引用（可带工作表前缀）。 */
    private static final Pattern REF = Pattern.compile(
            "(?:('([^']+)'|([A-Za-z_\\u4e00-\\u9fff][A-Za-z0-9_.\\u4e00-\\u9fff\u3001]*))!)?(\\$?)([A-Z]{1,3})(\\$?)(\\d+)");

    private static final Pattern MONTH_TEXT = Pattern.compile("^(\\d{4})\\s*年\\s*(\\d{1,2})\\s*月$");

    public byte[] expand(byte[] source, int year, int targetMonth) {
        if (targetMonth < 2 || targetMonth > 12) {
            throw new IllegalArgumentException("扩列：目标月份需在 2~12 之间");
        }
        try (XSSFWorkbook wb = new XSSFWorkbook(new ByteArrayInputStream(source))) {
            Set<String> expanded = new HashSet<>(EXPAND_PAGES);
            Map<String, Integer> insertColOf = new LinkedHashMap<>();
            for (String name : EXPAND_PAGES) {
                Sheet sh = wb.getSheet(name);
                if (sh == null) {
                    log.warn("扩列：找不到页签 {}", name);
                    continue;
                }
                insertColOf.put(name, expandSheet(sh, year, targetMonth));
            }
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                Sheet sh = wb.getSheetAt(i);
                if (sh == null || expanded.contains(sh.getSheetName())) continue;
                shiftQualifiedRefs(sh, insertColOf);
            }
            int titleFixed = applyTitles(wb, year, targetMonth);
            if (titleFixed > 0) {
                log.info("扩列：标题按 {}年{}月 重写 {} 格", year, targetMonth, titleFixed);
            }
            wb.setForceFormulaRecalculation(true);
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            wb.write(out);
            return out.toByteArray();
        } catch (Exception e) {
            throw new IllegalStateException("汇总大表扩列失败: " + e.getMessage(), e);
        }
    }

    /** 只把 7 个标题页的标题按给定期次重写（用于修正母版里滞后的标题）。 */
    public byte[] fixTitles(byte[] source, int year, int month) {
        try (XSSFWorkbook wb = new XSSFWorkbook(new ByteArrayInputStream(source))) {
            int n = applyTitles(wb, year, month);
            wb.setForceFormulaRecalculation(true);
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            wb.write(out);
            log.info("汇总大表标题修正完成：{}年{}月，改写 {} 格", year, month, n);
            return out.toByteArray();
        } catch (Exception e) {
            throw new IllegalStateException("汇总大表标题修正失败: " + e.getMessage(), e);
        }
    }

    /** 把 7 个页签的标题文字按「累计=1-M月、当月=M月」重写；命中标记才改，避免误伤备注。 */
    private int applyTitles(XSSFWorkbook wb, int year, int month) {
        int changed = 0;
        for (String page : TITLE_PAGES) {
            Sheet sh = wb.getSheet(page);
            if (sh == null) continue;
            int lastRow = Math.min(sh.getLastRowNum(), 40);
            for (int r = 0; r <= lastRow; r++) {
                Row row = sh.getRow(r);
                if (row == null) continue;
                for (int c = 0; c <= 1; c++) {
                    Cell cell = row.getCell(c);
                    if (cell == null || cell.getCellType() != CellType.STRING) continue;
                    String t = cell.getStringCellValue();
                    if (t == null || !looksLikeTitle(t)) continue;
                    String nt = t.replaceAll("(\\d{4})年", year + "年")
                                 .replaceAll("(\\d{1,2})月", month + "月");
                    if (!nt.equals(t)) {
                        cell.setCellValue(nt);
                        changed++;
                    }
                }
            }
        }
        return changed;
    }

    private boolean looksLikeTitle(String t) {
        for (String m : TITLE_MARKERS) {
            if (t.contains(m)) return true;
        }
        return false;
    }

    /** 对单页扩列，返回插入位置（0 基列号）。 */
    private int expandSheet(Sheet sh, int year, int targetMonth) {
        int lastMonth = targetMonth - 1;
        Map<Integer, Integer> cur = monthCols(sh, year);
        Integer lastVal = cur.get(lastMonth);
        if (lastVal == null) {
            throw new IllegalStateException("扩列：" + sh.getSheetName() + " 页未按表头识别到 " + year + "年" + lastMonth + "月列");
        }
        Map<Integer, Integer> last = monthCols(sh, year - 1);
        Integer lastYearSameMonth = last.get(targetMonth);
        if (lastYearSameMonth == null) {
            throw new IllegalStateException("扩列：" + sh.getSheetName() + " 页未识别到 " + (year - 1) + "年" + targetMonth + "月列（累计同比分母）");
        }
        List<Integer> headerRows = headerRowsOf(sh);
        int insertCol = lastVal + 2;                 // 累计列位置（8 月值列 Q=16 → 累计 S=18）
        int srcVal = lastVal, srcYoy = lastVal + 1;
        int lastRow = sh.getLastRowNum();

        // 1) 区域右移 2 列（自右向左搬格，保留值/公式/样式）
        for (int r = 0; r <= lastRow; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            int lastCell = row.getLastCellNum() - 1;
            for (int c = lastCell; c >= insertCol; c--) {
                Cell src = row.getCell(c);
                if (src == null) continue;
                copyCell(src, getOrCreate(row, c + 2));
                src.setBlank();
            }
        }
        // 2) 合并区右移（跨插入点的整体加宽）：先按下标摘除全部合并区，再按调整后的范围重新添加
        List<CellRangeAddress> merges = new ArrayList<>(sh.getMergedRegions());
        for (int i = merges.size() - 1; i >= 0; i--) {
            sh.removeMergedRegion(i);
        }
        for (CellRangeAddress mr : merges) {
            CellRangeAddress moved = mr;
            if (mr.getFirstColumn() >= insertCol) {
                moved = new CellRangeAddress(mr.getFirstRow(), mr.getLastRow(),
                        mr.getFirstColumn() + 2, mr.getLastColumn() + 2);
            } else if (mr.getLastColumn() >= insertCol) {
                moved = new CellRangeAddress(mr.getFirstRow(), mr.getLastRow(),
                        mr.getFirstColumn(), mr.getLastColumn() + 2);
            }
            sh.addMergedRegion(moved);
        }
        // 3) 本页公式引用整体 +2（指向已扩列页的引用同样 +2；指向未扩列页的不动）
        Set<String> shiftable = new HashSet<>(EXPAND_PAGES);
        shiftAllRefs(sh, insertCol, shiftable);

        // 4) 新列：列宽/隐藏照抄上月；表头（每个块的表头行）克隆值+格式+合并区；数据区只留样式与公式
        int w = sh.getColumnWidth(srcVal);
        if (w > 0) sh.setColumnWidth(insertCol, w);
        int w2 = sh.getColumnWidth(srcYoy);
        if (w2 > 0) sh.setColumnWidth(insertCol + 1, w2);
        sh.setColumnHidden(insertCol, sh.isColumnHidden(srcVal));
        sh.setColumnHidden(insertCol + 1, sh.isColumnHidden(srcYoy));
        for (int h : headerRows) {
            cloneHeaderCell(sh, srcVal, insertCol, h, year, targetMonth);
            cloneHeaderCell(sh, srcYoy, insertCol + 1, h, year, targetMonth);
            cloneHeaderMerge(sh, srcVal, insertCol, h);
            cloneHeaderMerge(sh, srcYoy, insertCol + 1, h);
        }
        for (int r = 0; r <= lastRow; r++) {
            if (headerRows.contains(r)) continue;
            Row row = sh.getRow(r);
            if (row == null) continue;
            cloneDataCell(row, srcVal, insertCol, shiftable);
            cloneYoyCell(row, srcYoy, insertCol + 1, insertCol, lastYearSameMonth + 2);
        }
        // 5) 累计列补当月项；累计同比分母补去年同月列
        int cumCol = insertCol + 2, cumYoyCol = insertCol + 3;
        int denCol = lastYearSameMonth + 2;
        String valLetter = colLetter(insertCol);
        String denLetter = colLetter(denCol);
        int cumFixed = 0, cumYoyFixed = 0;
        for (int r = 0; r <= lastRow; r++) {
            if (headerRows.contains(r)) continue;
            Row row = sh.getRow(r);
            if (row == null) continue;
            Cell cum = row.getCell(cumCol);
            if (cum != null && cum.getCellType() == CellType.FORMULA) {
                String f = cum.getCellFormula();
                if (!f.contains(valLetter + (r + 1))) {
                    cum.setCellFormula(f + "+" + valLetter + (r + 1));
                    cumFixed++;
                }
            }
            Cell cumYoy = row.getCell(cumYoyCol);
            if (cumYoy != null && cumYoy.getCellType() == CellType.FORMULA) {
                String f = cumYoy.getCellFormula();
                if (f.contains(denLetter + (r + 1))) continue;
                int close = f.lastIndexOf(')');
                if (close > 0) {
                    String inner = f.substring(0, close);
                    String sep = inner.contains(",") ? "," : "+";
                    cumYoy.setCellFormula(inner + sep + denLetter + (r + 1) + f.substring(close));
                    cumYoyFixed++;
                } else {
                    log.warn("扩列：{} 第 {} 行累计同比公式形态未识别，未补去年同月列：{}", sh.getSheetName(), r + 1, f);
                }
            }
        }
        log.info("扩列：{} 页完成——值列 {}→{}，同比列 {}→{}，累计列 {}，累计同比列 {}，去年同月列 {}；表头行 {}，末行 {}；累计补 {} 格、累计同比补 {} 格",
                sh.getSheetName(), colLetter(srcVal), colLetter(insertCol), colLetter(srcYoy), colLetter(insertCol + 1),
                colLetter(cumCol), colLetter(cumYoyCol), denLetter, headerRows, lastRow + 1, cumFixed, cumYoyFixed);
        return insertCol;
    }

    /** 表头行（0 基）：含「年+月」表头单元格的行；轨道、轮渡有两个块 → 两行。 */
    private List<Integer> headerRowsOf(Sheet sh) {
        List<Integer> rows = new ArrayList<>();
        int lastRow = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + HEADER_SCAN_ROWS);
        for (int r = sh.getFirstRowNum(); r <= lastRow; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
                if (yearMonthOf(row.getCell(c)) != null) {
                    rows.add(r);
                    break;
                }
            }
        }
        return rows;
    }

    private Map<Integer, Integer> monthCols(Sheet sh, int year) {
        Map<Integer, Integer> cols = new LinkedHashMap<>();
        int lastRow = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + HEADER_SCAN_ROWS);
        for (int r = sh.getFirstRowNum(); r <= lastRow; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
                Integer ym = yearMonthOf(row.getCell(c));
                if (ym == null || ym / 100 != year) continue;
                int mo = ym % 100;
                if (!cols.containsKey(mo)) cols.put(mo, c);
            }
        }
        return cols;
    }

    private Integer yearMonthOf(Cell c) {
        if (c == null) return null;
        try {
            CellType t = c.getCellType();
            if (t == CellType.STRING) {
                String v = c.getStringCellValue();
                if (v == null) return null;
                v = v.trim();
                if (v.isEmpty() || v.contains("累计") || v.contains("同比") || v.contains("环比")) return null;
                Matcher m = MONTH_TEXT.matcher(v);
                if (!m.matches()) return null;
                int mo = Integer.parseInt(m.group(2));
                return mo >= 1 && mo <= 12 ? Integer.parseInt(m.group(1)) * 100 + mo : null;
            }
            if (t == CellType.NUMERIC || t == CellType.FORMULA) {
                double v;
                if (t == CellType.NUMERIC) {
                    v = c.getNumericCellValue();
                } else {
                    if (c.getCachedFormulaResultType() != CellType.NUMERIC) return null;
                    v = c.getNumericCellValue();
                }
                if (v < 36526 || v > 73415) return null;
                java.util.Calendar cal = java.util.Calendar.getInstance();
                cal.setTime(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v));
                if (cal.get(java.util.Calendar.DAY_OF_MONTH) != 1) return null;
                return cal.get(java.util.Calendar.YEAR) * 100 + (cal.get(java.util.Calendar.MONTH) + 1);
            }
        } catch (Exception ignore) {
            return null;
        }
        return null;
    }

    private void cloneHeaderCell(Sheet sh, int srcCol, int dstCol, int rowIdx, int year, int targetMonth) {
        Row row = sh.getRow(rowIdx);
        if (row == null) return;
        Cell s = row.getCell(srcCol);
        if (s == null) return;
        Cell d = getOrCreate(row, dstCol);
        d.setBlank();
        d.setCellStyle(s.getCellStyle());
        if (s.getCellType() == CellType.STRING) {
            String text = s.getStringCellValue();
            if (text != null && text.contains("同比")) {
                d.setCellValue(text.replaceAll("^\\d{1,2}月", targetMonth + "月"));
            } else if (text != null && MONTH_TEXT.matcher(text.trim()).matches()) {
                d.setCellValue(year + "年" + targetMonth + "月");
            } else {
                d.setCellValue(text);
            }
        } else if (s.getCellType() == CellType.NUMERIC || s.getCellType() == CellType.FORMULA) {
            d.setCellValue(new GregorianCalendar(year, targetMonth - 1, 1).getTime());
        }
    }

    private void cloneHeaderMerge(Sheet sh, int srcCol, int dstCol, int headerRow) {
        for (CellRangeAddress mr : new ArrayList<>(sh.getMergedRegions())) {
            if (mr.getFirstColumn() == srcCol && mr.getLastColumn() == srcCol && mr.getFirstRow() == headerRow) {
                sh.addMergedRegion(new CellRangeAddress(mr.getFirstRow(), mr.getLastRow(), dstCol, dstCol));
                return;
            }
        }
    }

    private void cloneDataCell(Row row, int srcCol, int dstCol, Set<String> shiftable) {
        Cell s = row.getCell(srcCol);
        if (s == null) return;
        Cell d = getOrCreate(row, dstCol);
        d.setBlank();
        d.setCellStyle(s.getCellStyle());
        if (s.getCellType() == CellType.FORMULA) {
            d.setCellFormula(shiftRefsAll(s.getCellFormula(), shiftable));
        }
    }

    /** 当月同比列：不克隆上月公式，直接按「本月值 / 去年同月 − 1」重写（去年同月列已按表头定位） */
    private void cloneYoyCell(Row row, int srcCol, int dstCol, int valCol0, int denCol0) {
        Cell s = row.getCell(srcCol);
        if (s == null) return;
        Cell d = getOrCreate(row, dstCol);
        d.setBlank();
        d.setCellStyle(s.getCellStyle());
        if (s.getCellType() == CellType.FORMULA) {
            int excelRow = row.getRowNum() + 1;
            d.setCellFormula(colLetter(valCol0) + excelRow + "/" + colLetter(denCol0) + excelRow + "-1");
        }
    }

    /** 相对复制用的平移：把本页/已扩列页的引用整体 +2（不做列号阈值判断） */
    private String shiftRefsAll(String formula, Set<String> shiftable) {
        Matcher m = REF.matcher(formula);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            String sheet = m.group(2) != null ? m.group(2) : m.group(3);
            if ((sheet != null && !shiftable.contains(sheet)) || isFunctionName(formula, m)) {
                m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
                continue;
            }
            int colIdx = colIndex(m.group(5));
            m.appendReplacement(sb, Matcher.quoteReplacement(rebuild(m, sheet, colIdx + 2)));
        }
        m.appendTail(sb);
        return sb.toString();
    }

    private void shiftAllRefs(Sheet sh, int insertCol, Set<String> shiftable) {
        for (int r = 0; r <= sh.getLastRowNum(); r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = 0; c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell == null || cell.getCellType() != CellType.FORMULA) continue;
                String f = cell.getCellFormula();
                String nf = shiftRefs(f, insertCol, shiftable);
                if (!nf.equals(f)) cell.setCellFormula(nf);
            }
        }
    }

    /** 只平移「显式带工作表前缀、该页已扩列、且列号 >= 该页插入列」的引用（用于未扩列的页）。 */
    private void shiftQualifiedRefs(Sheet sh, Map<String, Integer> insertColOf) {
        for (int r = 0; r <= sh.getLastRowNum(); r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = 0; c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell == null || cell.getCellType() != CellType.FORMULA) continue;
                String f = cell.getCellFormula();
                String nf = shiftQualified(f, insertColOf);
                if (!nf.equals(f)) cell.setCellFormula(nf);
            }
        }
    }

    private String shiftQualified(String formula, Map<String, Integer> insertColOf) {
        Matcher m = REF.matcher(formula);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            String sheet = m.group(2) != null ? m.group(2) : m.group(3);
            Integer ins = sheet == null ? null : insertColOf.get(sheet);
            if (sheet == null || ins == null || isFunctionName(formula, m)) {
                m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
                continue;
            }
            int colIdx = colIndex(m.group(5));
            if (colIdx >= ins) {
                m.appendReplacement(sb, Matcher.quoteReplacement(rebuild(m, sheet, colIdx + 2)));
            } else {
                m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
            }
        }
        m.appendTail(sb);
        return sb.toString();
    }

    /** 平移公式中所有「列号 >= insertCol」的引用（sheet 为 null 或属于 shiftable 时生效）。 */
    private String shiftRefs(String formula, int insertCol, Set<String> shiftable) {
        Matcher m = REF.matcher(formula);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            String sheet = m.group(2) != null ? m.group(2) : m.group(3);
            if ((sheet != null && !shiftable.contains(sheet)) || isFunctionName(formula, m)) {
                m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
                continue;
            }
            int colIdx = colIndex(m.group(5));
            if (colIdx >= insertCol) {
                m.appendReplacement(sb, Matcher.quoteReplacement(rebuild(m, sheet, colIdx + 2)));
            } else {
                m.appendReplacement(sb, Matcher.quoteReplacement(m.group(0)));
            }
        }
        m.appendTail(sb);
        return sb.toString();
    }

    private boolean isFunctionName(String formula, Matcher m) {
        return m.group(1) == null && formula.indexOf('(', m.end()) == m.end();
    }

    private String rebuild(Matcher m, String sheet, int newColIdx0) {
        String prefix = sheet == null ? ""
                : (m.group(1).startsWith("'") ? "'" + sheet + "'" : sheet) + "!";
        return prefix + m.group(4) + colLetter(newColIdx0) + m.group(6) + m.group(7);
    }

    private void copyCell(Cell src, Cell dst) {
        dst.setCellStyle(src.getCellStyle());
        CellType t = src.getCellType();
        if (t == CellType.FORMULA) {
            dst.setCellFormula(src.getCellFormula());
        } else if (t == CellType.NUMERIC) {
            dst.setCellValue(src.getNumericCellValue());
        } else if (t == CellType.STRING) {
            dst.setCellValue(src.getStringCellValue());
        } else if (t == CellType.BOOLEAN) {
            dst.setCellValue(src.getBooleanCellValue());
        } else {
            dst.setBlank();
        }
    }

    private Cell getOrCreate(Row row, int col) {
        Cell c = row.getCell(col);
        return c == null ? row.createCell(col) : c;
    }

    private static int colIndex(String letters) {
        int n = 0;
        for (int i = 0; i < letters.length(); i++) n = n * 26 + (letters.charAt(i) - 'A' + 1);
        return n - 1;
    }

    private static String colLetter(int idx0) {
        StringBuilder sb = new StringBuilder();
        int n = idx0 + 1;
        while (n > 0) {
            int rem = (n - 1) % 26;
            sb.insert(0, (char) ('A' + rem));
            n = (n - 1) / 26;
        }
        return sb.toString();
    }
}