package com.trafficaudit.reportexport.calc;

import com.trafficaudit.common.util.RegionUtil;
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.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 汇总整本“数据驱动回填”引擎（M2 台账层，2026-09-09 接入）。
 *
 * 目标：生成任意月份《道路运输量汇总表（整本）》时不再依赖当月人工母版；
 * 母版只当版式底稿，当月值按已验证口径从库现算写入（班线包车 2026-07 与人工母版
 * 350 格 0 差异、公交/出租/轨道轮渡同批 0 差异）。
 *
 * 本版覆盖 4 张台账页：班线包车、公交、出租、轨道轮渡；
 * 网约车依赖《网约车订单及全省总量》拆分输入、货运页在 M3 接入，后续版本补充。
 *
 * 写入规则：
 * 1) 只写“市州明细行”的 2026 年 m 月当月值列（m 月值列 = 第 2+2*(m-1) 列，0 基，即 C 起）；
 * 2) 全省行（=17 市州求和公式）与跨页合成页均为公式，不动，Excel 打开自动重算；
 * 3) 2025 同期参照列保留母版缓存值（同比公式自动引用）；
 * 4) 源数据整月缺失时收集提示、不回填；市州当月全为 0 时保持母版空单元格。
 */
@Service
public class SummaryWorkbookFiller {

    @Resource
    private MidCalc midCalc;
    @Resource
    private PaxCalc paxCalc;

    /** 2026 年 1 月当月值列（0 基，即 C 列） */
    private static final int FIRST_MONTH_COL = 2;

    /** 回填 1..keepMonths 月台账页，返回写入格数；缺数提示写入 problems */
    public int fillMonthlyLedger(XSSFWorkbook wb, int year, int keepMonths, List<String> problems) {
        if (wb == null || keepMonths <= 0) return 0;
        if (problems == null) problems = new ArrayList<>();
        String yearPrefix = year + "-";
        int filled = 0;
        filled += fillCityRows(wb, "班线包车", midCalc.banxianMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false);
        filled += fillCityRows(wb, "公交", paxCalc.busMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false);
        filled += fillCityRows(wb, "出租", paxCalc.taxiMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false);
        filled += fillTrackFerry(wb, paxCalc.railFerryMonthly(yearPrefix, keepMonths), year, keepMonths, problems);
        return filled;
    }

    /** 通用台账页（班线包车/公交/出租）回填：按“列 A 市州名 + 列 B 指标文案”定位行，不写死行号 */
    private int fillCityRows(XSSFWorkbook wb, String sheetName,
                             Map<Integer, Map<String, double[]>> monthly,
                             int year, int keepMonths, List<String> problems, boolean wyc) {
        Sheet sh = wb.getSheet(sheetName);
        if (sh == null) {
            problems.add(sheetName + "页不存在（母版缺失该页），跳过自动回填");
            return 0;
        }
        int cap = monthColumnCapacity(sh);
        int mMax = Math.min(keepMonths, cap);
        if (mMax <= 0) {
            problems.add(sheetName + "页未识别到 2026 年累计列，跳过自动回填");
            return 0;
        }
        // 1) 市州起始行（列 A 为规范市州名）
        Map<String, Integer> cityStart = new LinkedHashMap<>();
        for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            String city = cityOf(row.getCell(0));
            if (city != null && !cityStart.containsKey(city)) cityStart.put(city, r);
        }
        if (cityStart.isEmpty()) {
            problems.add(sheetName + "页未识别到市州行，跳过自动回填");
            return 0;
        }
        List<Integer> starts = new ArrayList<>(cityStart.values());
        int filled = 0;
        List<String> missingMonths = new ArrayList<>();
        for (int m = 1; m <= mMax; m++) {
            if (!monthly.containsKey(m)) missingMonths.add(m + "月");
        }
        if (!missingMonths.isEmpty()) {
            problems.add(sheetName + "缺 " + year + " 年 " + String.join("、", missingMonths) + " 源数据（未回填）");
        }
        for (int i = 0; i < starts.size(); i++) {
            String city = findCityByStart(sh, starts.get(i));
            if (city == null) continue;
            int end = (i + 1 < starts.size()) ? starts.get(i + 1) - 1 : sh.getLastRowNum();
            // 2) 行内各指标所在行（B 列文案 → 指标位 0..3）
            int[] metricRow = new int[]{-1, -1, -1, -1};
            for (int r = starts.get(i); r <= end; r++) {
                Row row = sh.getRow(r);
                if (row == null) continue;
                int idx = metricIndexOf(text(row.getCell(1)));
                if (idx >= 0 && metricRow[idx] < 0) metricRow[idx] = r;
            }
            if (metricRow[0] < 0 && metricRow[1] < 0) continue;
            for (int m = 1; m <= mMax; m++) {
                Map<String, double[]> mm = monthly.get(m);
                if (mm == null) continue;
                double[] arr = mm.get(city);
                if (arr == null || (arr[0] == 0.0 && arr[1] == 0.0)) continue;
                int col = FIRST_MONTH_COL + 2 * (m - 1);
                for (int idx = 0; idx < 4; idx++) {
                    if (metricRow[idx] < 0) continue;
                    double val = arr[idx];
                    if (val == 0.0) continue;
                    filled += setNumeric(sh, metricRow[idx], col, val);
                }
            }
        }
        return filled;
    }

    /** 轨道轮渡页（轨道=武汉/黄石；轮渡仅武汉）专用回填 */
    private int fillTrackFerry(XSSFWorkbook wb, Map<Integer, Map<String, double[]>> monthly,
                               int year, int keepMonths, List<String> problems) {
        String sheetName = "轨道、轮渡";
        Sheet sh = wb.getSheet(sheetName);
        if (sh == null) return 0;
        int cap = monthColumnCapacity(sh);
        int mMax = Math.min(keepMonths, cap);
        if (mMax <= 0) return 0;
        // 轨道块与轮渡块标题行（列 A 文案）
        int trackTitle = -1, ferryTitle = -1;
        for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
            String a = text(sh.getRow(r) == null ? null : sh.getRow(r).getCell(0));
            if (a == null) continue;
            if (trackTitle < 0 && a.startsWith("轨道客运量")) trackTitle = r;
            else if (ferryTitle < 0 && a.startsWith("轮渡客运量")) ferryTitle = r;
        }
        if (trackTitle < 0 || ferryTitle < 0) {
            problems.add(sheetName + "页未识别到轨道/轮渡块标题，跳过自动回填");
            return 0;
        }
        int filled = 0;
        filled += fillFerryBlock(sh, monthly, trackTitle + 3, ferryTitle - 1, 0, mMax, problems, sheetName);
        filled += fillFerryBlock(sh, monthly, ferryTitle + 3, sh.getLastRowNum(), 2, mMax, problems, sheetName);
        return filled;
    }

    /** 填充一段“标题行后数据区”：base 为该块指标位基（轨道 0 / 轮渡 2）；block 内
     *  每市州 2 行：客运量行（列 A=市州名）、旅客周转量行（A 空，B 含“周转量”） */
    private int fillFerryBlock(Sheet sh, Map<Integer, Map<String, double[]>> monthly,
                               int start, int end, int base, int mMax,
                               List<String> problems, String sheetName) {
        int filled = 0;
        // 收集市州客运量行
        Map<String, Integer> paxRow = new LinkedHashMap<>();
        for (int r = start; r <= end; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            String city = cityOf(row.getCell(0));
            if (city != null) paxRow.put(city, r);
        }
        for (Map.Entry<String, Integer> e : paxRow.entrySet()) {
            String city = e.getKey();
            int rp = e.getValue();
            int rt = findTurnoverRow(sh, rp + 1, Math.min(end, rp + 6));
            if (rt < 0) continue;
            for (int m = 1; m <= mMax; m++) {
                Map<String, double[]> mm = monthly.get(m);
                if (mm == null) continue;
                double[] arr = mm.get(city);
                if (arr == null) continue;
                int col = FIRST_MONTH_COL + 2 * (m - 1);
                double pax = arr[base];
                double turn = arr[base + 1];
                if (pax != 0.0) filled += setNumeric(sh, rp, col, pax);
                if (turn != 0.0) filled += setNumeric(sh, rt, col, turn);
            }
        }
        return filled;
    }

    private int findTurnoverRow(Sheet sh, int from, int to) {
        for (int r = from; r <= to; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            String a = text(row.getCell(0));
            if (a != null && !a.trim().isEmpty()) return -1; // 进入下一市州块
            String b = text(row.getCell(1));
            if (b != null && b.contains("周转量")) return r;
        }
        return -1;
    }

    /** 写数值：格不存在则建格并复制同行 1 月列样式；返回 1 */
    private int setNumeric(Sheet sh, int r, int col, double val) {
        Row row = sh.getRow(r);
        if (row == null) row = sh.createRow(r);
        Cell c = row.getCell(col);
        if (c == null) {
            c = row.createCell(col);
            Cell ref = row.getCell(FIRST_MONTH_COL);
            if (ref != null && ref.getCellStyle() != null) c.setCellStyle(ref.getCellStyle());
        }
        c.setCellValue(val);
        return 1;
    }

    /** 行 B 列文案 → 指标位：0 客运量 / 1 旅客周转量 / 2 其中城市内客运量或其中个体客运量 / 3 对应周转量 */
    private int metricIndexOf(String b) {
        if (b == null) return -1;
        if (b.contains("其中个体旅客周转量") || b.contains("其中城市内旅客周转量")) return 3;
        if (b.contains("其中个体客运量") || b.contains("其中城市内客运量")) return 2;
        if (b.contains("旅客周转量")) return 1;
        if (b.contains("客运量")) return 0;
        return -1;
    }

    /** 列 A 文案 → 规范市州名（湖北省/全省、非市州行返回 null） */
    private String cityOf(Cell a) {
        String t = text(a);
        if (t == null || t.trim().isEmpty()) return null;
        String norm = RegionUtil.normalizeCityName(t.trim());
        return RegionUtil.cityList().contains(norm) ? norm : null;
    }

    private String findCityByStart(Sheet sh, int startRow) {
        Row row = sh.getRow(startRow);
        return row == null ? null : cityOf(row.getCell(0));
    }

    /** 月份容量：在表头（前 5 行）找“2026…累计”列，容量=(累计列-1 月列)/2 */
    private int monthColumnCapacity(Sheet sh) {
        for (int r = 0; r <= 4 && r <= sh.getLastRowNum(); r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (Cell c : row) {
                if (c == null) continue;
                String t = text(c);
                if (t != null && t.contains("2026") && t.contains("累计") && c.getColumnIndex() > FIRST_MONTH_COL) {
                    return (c.getColumnIndex() - FIRST_MONTH_COL) / 2;
                }
            }
        }
        return 7;
    }

    private String text(Cell c) {
        if (c == null) return null;
        switch (c.getCellType()) {
            case STRING: return c.getStringCellValue();
            case NUMERIC: return Double.toString(c.getNumericCellValue());
            default: return null;
        }
    }
}