package com.trafficaudit.reportexport.calc;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.trafficaudit.common.util.RegionUtil;
import com.trafficaudit.dataimport.entity.CityBusMonthly;
import com.trafficaudit.dataimport.entity.CityTaxiMonthly;
import com.trafficaudit.dataimport.entity.FreightTurnoverImport;
import com.trafficaudit.dataimport.entity.H2032EnterpriseMonthly;
import com.trafficaudit.dataimport.entity.PassengerEnterpriseMonthly;
import com.trafficaudit.dataimport.entity.ScaleSplitTransport;
import com.trafficaudit.dataimport.entity.WycOrderMonthly;
import com.trafficaudit.dataimport.entity.WycTotalMonthly;
import com.trafficaudit.dataimport.mapper.CityBusMonthlyMapper;
import com.trafficaudit.dataimport.mapper.CityTaxiMonthlyMapper;
import com.trafficaudit.dataimport.mapper.FreightTurnoverImportMapper;
import com.trafficaudit.dataimport.mapper.H2032EnterpriseMonthlyMapper;
import com.trafficaudit.dataimport.mapper.PassengerEnterpriseMonthlyMapper;
import com.trafficaudit.dataimport.mapper.ScaleSplitTransportMapper;
import com.trafficaudit.dataimport.mapper.WycOrderMonthlyMapper;
import com.trafficaudit.dataimport.mapper.WycTotalMonthlyMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.util.ZipSecureFile;
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.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 汇总大表「缺数清单」（只读，2026-10-12 新增，用户要求）。
 *
 * <p>与《货运基数体检》{@link FreightReadinessCheck} 互补、必须分开：
 * 那个查「不可能的数」（倒减为负、Σ市州≠省行…），本类查「该有的数没有」。
 * 2026-08 体检 0 命中、但 2026-09 出表时 8 月列仍为空 —— 就是只有前者、没有后者的后果。
 *
 * <p>检查项：
 * <ol>
 *   <li>程序逐月从库回填的页（班线包车、公交、出租、轨道轮渡）：1..目标月 逐月查来源是否有数、17 市州是否齐全；</li>
 *   <li>程序只回填目标月的页（货运、网约车）：目标月三项来源是否齐全；</li>
 *   <li>上述两页的 1..目标月-1 由母版承担：母版该月值列全空则出表即缺（库里有数、程序不回填）。</li>
 * </ol>
 *
 * <p>本类只读、不抛异常（异常转成清单文字），供导出日志 / 弹窗 / 生成件《数据缺失清单》页使用。
 */
@Slf4j
@Service
public class SummaryMissingDataCheck {

    @Resource
    private PassengerEnterpriseMonthlyMapper h2031Mapper;      // 班线包车（公路旅客企业月报）
    @Resource
    private CityBusMonthlyMapper cityBusMapper;                // 公交 + 轨道/轮渡
    @Resource
    private CityTaxiMonthlyMapper cityTaxiMapper;              // 出租（网约车拆分也用它的平均运距）
    @Resource
    private H2032EnterpriseMonthlyMapper h2032Mapper;          // 货运规上（①）
    @Resource
    private FreightTurnoverImportMapper freightTurnoverMapper; // 货运全口径（②）
    @Resource
    private ScaleSplitTransportMapper scaleSplitMapper;        // 规上规下拆分（③）
    @Resource
    private WycOrderMonthlyMapper wycOrderMapper;              // 网约车订单 + 全省 pin
    @Resource
    private WycTotalMonthlyMapper wycTotalMapper;              // 网约车 pin 专表（兜底）

    @Value("${summary.template-dir:docs/生成汇总大表}")
    private String summaryTemplateDir;

    /** 程序只回填「目标月」的页 —— 更早月份由母版承担 */
    private static final String[] TARGET_ONLY_PAGES = {" 货运", "网约车"};

    public List<String> check(String period) {
        List<String> issues = new ArrayList<>();
        if (period == null || period.length() < 7) return issues;
        int year;
        int month;
        try {
            year = Integer.parseInt(period.substring(0, 4));
            month = Integer.parseInt(period.substring(5, 7));
        } catch (Exception e) {
            return issues;
        }
        // 1) 逐月回填页：1..目标月
        for (int m = 1; m <= month; m++) {
            String p = String.format("%04d-%02d", year, m);
            checkH2031Month(p, issues);
            checkCityBusMonth(p, issues);
            checkRailFerryMonth(p, issues);
            checkCityTaxiMonth(p, issues);
        }
        // 2) 只回填目标月的两页：目标月来源
        String target = String.format("%04d-%02d", year, month);
        checkFreightTarget(target, issues);
        checkWycTarget(target, issues);
        // 3) 这两页的历史月：母版是否为空
        checkMotherHistory(target, year, month, issues);
        if (!issues.isEmpty()) log.warn("缺数清单（{}）：命中 {} 条", period, issues.size());
        return issues;
    }

    // ---------- 各页检查 ----------

    private void checkH2031Month(String p, List<String> issues) {
        try {
            List<PassengerEnterpriseMonthly> rows = h2031Mapper.selectList(
                new LambdaQueryWrapper<PassengerEnterpriseMonthly>().eq(PassengerEnterpriseMonthly::getReportPeriod, p));
            if (rows.isEmpty()) {
                issues.add(String.format("缺数：班线包车 %s 无《公路旅客企业月报》来源 → 该月客运量/周转量、中口径「公路班线」将为空", p));
                return;
            }
            Set<String> got = new LinkedHashSet<>();
            for (PassengerEnterpriseMonthly r : rows) {
                String c = RegionUtil.cityByCode(r.getRegionCode());
                if (c != null) got.add(c);
            }
            addMissingCities("班线包车", p, got, issues);
        } catch (Exception e) {
            issues.add("缺数检查异常：班线包车 " + p + " :: " + e.getMessage());
        }
    }

    private void checkCityBusMonth(String p, List<String> issues) {
        try {
            List<CityBusMonthly> rows = cityBusMapper.selectList(
                new LambdaQueryWrapper<CityBusMonthly>().eq(CityBusMonthly::getReportPeriod, p));
            if (rows.isEmpty()) {
                issues.add(String.format("缺数：公交 %s 无《城市公交月报》来源 → 公交页为空、城市客运页将缺公交部分", p));
                return;
            }
            addMissingCities("公交", p, citySetOf(rows), issues);
        } catch (Exception e) {
            issues.add("缺数检查异常：公交 " + p + " :: " + e.getMessage());
        }
    }

    private void checkRailFerryMonth(String p, List<String> issues) {
        try {
            List<CityBusMonthly> rows = cityBusMapper.selectList(
                new LambdaQueryWrapper<CityBusMonthly>().eq(CityBusMonthly::getReportPeriod, p));
            if (rows.isEmpty()) {
                issues.add(String.format("缺数：轨道、轮渡 %s 无《城市公交月报》来源（轨道/轮渡同表）→ 该页为空", p));
                return;
            }
            boolean rail = false;
            boolean ferry = false;
            for (CityBusMonthly r : rows) {
                if (r.getRailPassengerVolume() != null) rail = true;
                if (r.getFerryPassengerVolume() != null) ferry = true;
            }
            List<String> miss = new ArrayList<>();
            if (!rail) miss.add("轨道");
            if (!ferry) miss.add("轮渡");
            if (!miss.isEmpty()) {
                issues.add(String.format("缺数：轨道、轮渡 %s 的 %s 字段全空 → 该块将为空",
                    p, String.join("、", miss)));
            }
        } catch (Exception e) {
            issues.add("缺数检查异常：轨道、轮渡 " + p + " :: " + e.getMessage());
        }
    }

    private void checkCityTaxiMonth(String p, List<String> issues) {
        try {
            List<CityTaxiMonthly> rows = cityTaxiMapper.selectList(
                new LambdaQueryWrapper<CityTaxiMonthly>().eq(CityTaxiMonthly::getReportPeriod, p));
            if (rows.isEmpty()) {
                issues.add(String.format("缺数：出租 %s 无《巡游出租汽车月报》来源 → 出租页为空，且网约车拆分取不到平均运距", p));
                return;
            }
            Set<String> got = new LinkedHashSet<>();
            for (CityTaxiMonthly r : rows) {
                String c = RegionUtil.normalizeCityName(r.getCity());
                if (c != null && !c.isEmpty()) got.add(c);
            }
            addMissingCities("出租", p, got, issues);
        } catch (Exception e) {
            issues.add("缺数检查异常：出租 " + p + " :: " + e.getMessage());
        }
    }

    private void checkFreightTarget(String p, List<String> issues) {
        try {
            // ① H203-2 企业月报（规上货运量/周转量）
            List<H2032EnterpriseMonthly> h = h2032Mapper.selectList(
                new LambdaQueryWrapper<H2032EnterpriseMonthly>().eq(H2032EnterpriseMonthly::getReportPeriod, p));
            if (h.isEmpty()) {
                issues.add(String.format("缺数：货运 %s 无①《H203-2 道路货物运输月度生产情况》→ 规上货运量/周转量取不到", p));
            } else {
                Set<String> got = new LinkedHashSet<>();
                for (H2032EnterpriseMonthly r : h) {
                    String c = RegionUtil.cityByCode(r.getRegionCode());
                    if (c != null) got.add(c);
                }
                addMissingCities("货运①规上", p, got, issues);
            }
            // ② 货运量周转量表（合计 = 规上+规下）
            List<FreightTurnoverImport> f = freightTurnoverMapper.selectList(
                new LambdaQueryWrapper<FreightTurnoverImport>().eq(FreightTurnoverImport::getReportPeriod, p));
            if (f.isEmpty()) {
                issues.add(String.format("缺数：货运 %s 无②《货运量周转量表》→ 合计货运量/周转量、规下（倒减）全部取不到", p));
            } else {
                Set<String> got = new LinkedHashSet<>();
                for (FreightTurnoverImport r : f) {
                    String c = RegionUtil.normalizeCityName(r.getRegionName());
                    if (c != null && !RegionUtil.cityList().contains(c) && !"湖北省".equals(c)) continue;
                    if (c != null && RegionUtil.cityList().contains(c)) got.add(c);
                }
                addMissingCities("货运②全口径", p, got, issues);
            }
            // ③ 拆分表（规上/合计周转量），当月 + 累计
            List<ScaleSplitTransport> s = scaleSplitMapper.selectList(
                new LambdaQueryWrapper<ScaleSplitTransport>().eq(ScaleSplitTransport::getReportPeriod, p));
            if (s.isEmpty()) {
                issues.add(String.format("缺数：货运 %s 无③《规上规下拆分表》→ 规上/合计周转量取不到（该月周转量将为空/0）", p));
            } else {
                Set<String> month = new LinkedHashSet<>();
                Set<String> cum = new LinkedHashSet<>();
                for (ScaleSplitTransport r : s) {
                    String c = RegionUtil.normalizeCityName(r.getRegionName());
                    if (c == null || !RegionUtil.cityList().contains(c)) continue;
                    if ("MONTH".equals(r.getPeriodType())) month.add(c);
                    else if ("CUMULATIVE".equals(r.getPeriodType())) cum.add(c);
                }
                addMissingCities("货运③拆分(当月)", p, month, issues);
                addMissingCities("货运③拆分(累计)", p, cum, issues);
            }
        } catch (Exception e) {
            issues.add("缺数检查异常：货运 " + p + " :: " + e.getMessage());
        }
    }

    private void checkWycTarget(String p, List<String> issues) {
        try {
            List<WycOrderMonthly> orders = wycOrderMapper.selectList(
                new LambdaQueryWrapper<WycOrderMonthly>().eq(WycOrderMonthly::getReportPeriod, p));
            boolean okOrders = false;
            boolean okPin = false;
            if (!orders.isEmpty()) {
                WycOrderMonthly o = orders.get(0);
                double sum = nz(o.getOrderWuhan()) + nz(o.getOrderHuangshi()) + nz(o.getOrderShiyan()) + nz(o.getOrderYichang())
                    + nz(o.getOrderXiangyang()) + nz(o.getOrderEzhou()) + nz(o.getOrderJingmen()) + nz(o.getOrderXiaogan())
                    + nz(o.getOrderJingzhou()) + nz(o.getOrderHuanggang()) + nz(o.getOrderXianning()) + nz(o.getOrderSuizhou())
                    + nz(o.getOrderEnshi()) + nz(o.getOrderXiantao()) + nz(o.getOrderQianjiang()) + nz(o.getOrderTianmen())
                    + nz(o.getOrderShennong());
                okOrders = sum > 0;
                okPin = nz(o.getPinK()) > 0 && nz(o.getPinCK()) > 0 && nz(o.getPinZ()) > 0 && nz(o.getPinCZ()) > 0;
            }
            if (!okOrders) {
                issues.add(String.format("缺数：网约车 %s 无订单数据（wyc_order_monthly 17 市州订单）→ 拆分无法计算，该页将为空", p));
            }
            if (!okPin) {
                List<WycTotalMonthly> tot = wycTotalMapper.selectList(
                    new LambdaQueryWrapper<WycTotalMonthly>().eq(WycTotalMonthly::getReportPeriod, p));
                boolean okTot = !tot.isEmpty()
                    && nz(tot.get(0).getPinK()) > 0 && nz(tot.get(0).getPinCK()) > 0
                    && nz(tot.get(0).getPinZ()) > 0 && nz(tot.get(0).getPinCZ()) > 0;
                if (!okTot) {
                    issues.add(String.format("缺数：网约车 %s 缺全省 pin（order 表与 wyc_total_monthly 都没有）→ 拆分无法计算", p));
                }
            }
            List<CityTaxiMonthly> taxi = cityTaxiMapper.selectList(
                new LambdaQueryWrapper<CityTaxiMonthly>().eq(CityTaxiMonthly::getReportPeriod, p));
            Set<String> got = new LinkedHashSet<>();
            for (CityTaxiMonthly r : taxi) {
                String c = RegionUtil.normalizeCityName(r.getCity());
                if (c != null && !c.isEmpty()) got.add(c);
            }
            addMissingCities("网约车(依赖出租平均运距)", p, got, issues);
        } catch (Exception e) {
            issues.add("缺数检查异常：网约车 " + p + " :: " + e.getMessage());
        }
    }

    /** 货运/网约车的历史月（1..M-1）由母版承担（母版没有时按「同期备份母版」还原）：
     *  只有母版与备份母版都没有数，才算真正的出表缺口（否则会误报 7 月那种「骨架母版 + donor 还原」的情况）。 */
    private void checkMotherHistory(String period, int year, int month, List<String> issues) {
        File mother = resolveMother(year, month);
        if (mother == null) {
            issues.add(String.format("缺数：未找到母版《%d年%d月道路运输量汇总表.xlsx》（%s）", year, month, summaryTemplateDir));
            return;
        }
        File donor = resolveDonor(year, month);
        double saved = ZipSecureFile.getMinInflateRatio();
        ZipSecureFile.setMinInflateRatio(0.0001);
        try (InputStream in = new FileInputStream(mother);
             Workbook wb = WorkbookFactory.create(in);
             InputStream in2 = (donor != null) ? new FileInputStream(donor) : null;
             Workbook dwb = (in2 != null) ? WorkbookFactory.create(in2) : null) {
            for (String pageName : TARGET_ONLY_PAGES) {
                List<String> blankMonths = new ArrayList<>();
                for (int m = 1; m < month; m++) {
                    if (monthHasData(wb, pageName, year, m)) continue;
                    if (dwb != null && monthHasData(dwb, pageName, year, m)) continue;
                    blankMonths.add(String.format("%d年%d月", year, m));
                }
                if (!blankMonths.isEmpty()) {
                    issues.add(String.format("缺数：%s 母版（%s 期）的 %s 值列全空%s，而程序只回填目标月 → 出表该月将为空（库里可能有数据）",
                        pageName.trim(), period, String.join("、", blankMonths),
                        donor == null ? "" : "（同期备份母版也没有）"));
                }
            }
        } catch (Exception e) {
            issues.add("缺数检查异常：读母版 " + mother.getName() + " :: " + e.getMessage());
        } finally {
            ZipSecureFile.setMinInflateRatio(saved);
        }
    }

    /** 该工作簿该页、该年该月的「值列」是否已有数值（表头行之外） */
    private boolean monthHasData(Workbook wb, String pageName, int year, int m) {
        Sheet sheet = findSheet(wb, pageName);
        if (sheet == null) return false;
        int[] ref = detectYearMonthCols(sheet, year).get(m);
        if (ref == null) return false;
        return countNumericInColumn(sheet, ref[1], ref[0]) > 0;
    }

    private Sheet findSheet(Workbook wb, String pageName) {
        Sheet s = wb.getSheet(pageName);
        if (s == null && " 货运".equals(pageName)) s = wb.getSheet("货运");
        return s;
    }

    /** 同期备份母版（清理前的完整月母版），命名 _备份_{年}年{月}月…道路运输量汇总表 */
    private File resolveDonor(int year, int month) {
        String rel = summaryTemplateDir;
        while (rel.startsWith("./")) rel = rel.substring(2);
        String[] roots = {summaryTemplateDir, System.getProperty("user.dir") + "/" + rel,
            System.getProperty("user.dir") + "/../" + rel};
        for (String root : roots) {
            if (root == null || root.trim().isEmpty()) continue;
            File dir = new File(root);
            File[] files = dir.listFiles((d, name) -> name.startsWith("_备份_")
                && name.contains(year + "年" + month + "月") && name.contains("道路运输量汇总表"));
            if (files != null && files.length > 0) return files[0];
        }
        return null;
    }

    // ---------- 工具 ----------

    private Set<String> citySetOf(List<CityBusMonthly> rows) {
        Set<String> got = new LinkedHashSet<>();
        for (CityBusMonthly r : rows) {
            String c = RegionUtil.normalizeCityName(r.getCity());
            if (c != null && !c.isEmpty()) got.add(c);
        }
        return got;
    }

    private void addMissingCities(String page, String period, Set<String> got, List<String> issues) {
        List<String> miss = new ArrayList<>();
        for (String c : RegionUtil.cityList()) {
            if (!got.contains(c)) miss.add(c);
        }
        if (!miss.isEmpty()) {
            issues.add(String.format("缺数：%s %s 缺市州：%s", page, period, String.join("、", miss)));
        }
    }

    /** 母版里定位目标年各月「值列」：返回 月 -> [表头行(0基), 列(0基)]；兼容文本「2026年M月」与日期序列号 */
    private Map<Integer, int[]> detectYearMonthCols(Sheet sheet, int year) {
        Map<Integer, int[]> out = new LinkedHashMap<>();
        int lastRow = Math.min(5, sheet.getLastRowNum());
        for (int r = 0; r <= lastRow; r++) {
            Row row = sheet.getRow(r);
            if (row == null) continue;
            for (Cell c : row) {
                Integer m = monthOfCell(c, year);
                if (m != null && !out.containsKey(m)) {
                    out.put(m, new int[]{r, c.getColumnIndex()});
                }
            }
        }
        return out;
    }

    private Integer monthOfCell(Cell c, int year) {
        try {
            if (c == null) return null;
            if (c.getCellType() == CellType.STRING) {
                String s = c.getStringCellValue();
                if (s == null) return null;
                java.util.regex.Matcher mt = java.util.regex.Pattern
                    .compile("(20\\d\\d)\\s*年\\s*(\\d{1,2})\\s*月").matcher(s);
                if (mt.find()) {
                    if (Integer.parseInt(mt.group(1)) != year) return null;
                    int m = Integer.parseInt(mt.group(2));
                    return (m >= 1 && m <= 12) ? m : null;
                }
                return null;
            }
            if (c.getCellType() == CellType.NUMERIC) {
                double v = c.getNumericCellValue();
                if (v < 20000 || v > 60000 || v != Math.floor(v)) return null;
                java.util.Date d = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v);
                java.util.Calendar cal = java.util.Calendar.getInstance();
                cal.setTime(d);
                if (cal.get(java.util.Calendar.YEAR) != year) return null;
                return cal.get(java.util.Calendar.MONTH) + 1;
            }
        } catch (Exception ignore) {
            // 非月份表头
        }
        return null;
    }

    /** 统计某列（跳过表头行）非空数值格数 */
    private int countNumericInColumn(Sheet sheet, int col, int headerRow) {
        int n = 0;
        for (int r = 0; r <= sheet.getLastRowNum(); r++) {
            if (r == headerRow) continue;
            Row row = sheet.getRow(r);
            if (row == null) continue;
            Cell c = row.getCell(col);
            if (c == null) continue;
            if (c.getCellType() == CellType.NUMERIC || c.getCellType() == CellType.FORMULA) n++;
        }
        return n;
    }

    private File resolveMother(int year, int month) {
        String rel = summaryTemplateDir;
        while (rel.startsWith("./")) rel = rel.substring(2);
        String name = year + "年" + month + "月道路运输量汇总表.xlsx";
        String[] roots = {summaryTemplateDir, System.getProperty("user.dir") + "/" + rel,
            System.getProperty("user.dir") + "/../" + rel};
        for (String root : roots) {
            if (root == null || root.trim().isEmpty()) continue;
            File f = new File(root, name);
            if (f.exists() && f.isFile()) return f;
        }
        return null;
    }

    private double nz(Double v) {
        return v == null ? 0.0 : v;
    }
}