package com.hdkj.lt.bf.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.hdkj.lt.bf.application.FeederGroupApplication;
import com.hdkj.lt.bf.entity.FhzgSeCurrentEvent;
import com.hdkj.lt.bf.entity.FhzgSeLineLossDaily;
import com.hdkj.lt.bf.entity.FhzgSeLineLossDetail;
import com.hdkj.lt.bf.entity.FhzgDwmVoltageOverLimitDaily;
import com.hdkj.lt.bf.entity.FhzgSeCapacityDaily;
import com.hdkj.lt.bf.entity.FhzgSeVoltageEvent;
import com.hdkj.lt.bf.entity.FhzgSeSnapshotDetail;
import com.hdkj.lt.bf.entity.FhzgSeConsumerCountDaily;
import com.hdkj.lt.bf.entity.FhzgSeConsumerRelation;
import com.hdkj.lt.bf.entity.dto.TodayOutageData;
import com.hdkj.lt.bf.entity.vo.SeCapacityDashboardVO;
import com.hdkj.lt.bf.entity.vo.SeOutageDashboardVO;
import com.hdkj.lt.bf.entity.vo.SeVoltageDashboardVO;
import com.hdkj.lt.bf.entity.vo.SeVoltageIndexVO;
import com.hdkj.lt.bf.mapper.FhzgSeCurrentEventMapper;
import com.hdkj.lt.bf.mapper.FhzgSeLineLossDailyMapper;
import com.hdkj.lt.bf.mapper.FhzgSeLineLossDetailMapper;
import com.hdkj.lt.bf.mapper.FhzgDwmVoltageOverLimitDailyMapper;
import com.hdkj.lt.bf.mapper.FhzgSeCapacityDailyMapper;
import com.hdkj.lt.bf.mapper.FhzgSeVoltageEventMapper;
import com.hdkj.lt.bf.mapper.FhzgSeSnapshotDetailMapper;
import com.hdkj.lt.bf.mapper.FhzgSeConsumerCountDailyMapper;
import com.hdkj.lt.bf.mapper.FhzgSeConsumerRelationMapper;
import com.hdkj.lt.bf.service.SeIndicatorService;
import com.hdkj.lt.bf.service.SjztPwwyyqxtPrmPdrOutageMvDsService;
import com.hdkj.fhzg.optimization.response.FeederGroupTreeNode;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 状估监测体系 三个查询接口 Service 实现
 *
 * @author lsl
 * @since 2026-07-23
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class SeIndicatorServiceImpl implements SeIndicatorService {

    private final SjztPwwyyqxtPrmPdrOutageMvDsService outageService;
    private final FhzgSeVoltageEventMapper voltageEventMapper;
    private final FhzgDwmVoltageOverLimitDailyMapper voltageDailyMapper;
    private final FhzgSeSnapshotDetailMapper snapshotDetailMapper;
    private final FhzgSeCapacityDailyMapper capacityDailyMapper;
    private final FhzgSeCurrentEventMapper currentEventMapper;
    private final FhzgSeConsumerCountDailyMapper consumerCountDailyMapper;
    private final FhzgSeConsumerRelationMapper consumerRelationMapper;
    private final FhzgSeLineLossDetailMapper lineLossDetailMapper;
    private final FhzgSeLineLossDailyMapper lineLossDailyMapper;
    private final FeederGroupApplication feederGroupApplication;

    private static final DateTimeFormatter DATE_FMT = DateTimeFormatter.ofPattern("yyyyMMdd");

    /** 每天小时数（分母时户数换算用） */
    private static final BigDecimal HOURS_24 = new BigDecimal("24");

    // ============================================================
    // 1. 停电时户数（复用已有逻辑，无时间维度）
    // ============================================================

    @Override
    public SeOutageDashboardVO queryOutage(String id, Integer type) {
        if (StringUtils.isBlank(id) || type == null) {
            return null;
        }

        // 今日 — 实时表
        TodayOutageData today = outageService.getTodayOutageData(id, type);

        // 全年（1.1 ~ 昨天）— T-2表
        LocalDate todayDate = LocalDate.now();
        int year = todayDate.getYear();
        LocalDateTime yearStart = LocalDate.of(year, 1, 1).atStartOfDay();
        LocalDateTime yesterdayEnd = todayDate.minusDays(1).atTime(23, 59, 59);
        TodayOutageData yearStats = outageService.getOutageDurationHoursHouseholds(id, type, yearStart, yesterdayEnd);

        return SeOutageDashboardVO.builder()
                .outageHoursHouseholdsToday(today.getTotalHouseholds())
                .avgOutageDurationToday(today.getAvgDurationHours())
                .outageHoursHouseholdsYear(yearStats.getTotalHouseholds())
                .avgOutageDurationYear(yearStats.getAvgDurationHours())
                .build();
    }

    // ============================================================
    // 2. 电压质量
    // ============================================================

    @Override
    public SeVoltageDashboardVO queryVoltage(String id, Integer type, String timeScope) {
        if (StringUtils.isBlank(id) || type == null || StringUtils.isBlank(timeScope)) {
            return null;
        }

        SeVoltageDashboardVO vo;
        if ("today".equalsIgnoreCase(timeScope)) {
            vo = queryVoltageToday(id, type);
        } else {
            vo = queryVoltageHistory(id, type, timeScope);
        }

        // IEC 用户电压指数：与 timeScope 一致（today→day, month/year 同名）
        String indexScope = "today".equalsIgnoreCase(timeScope) ? "day" : timeScope;
        SeVoltageIndexVO indexVo = queryVoltageIndex(id, type, indexScope);
        if (indexVo != null) {
            vo.setIecIndex(indexVo.getVoltageIndex());
        }

        return vo;
    }

    /**
     * 今日电压：按 alarmLevel (feeder/mvtrans/consumer) 分别统计
     * - 条/台/户数 = 去重 deviceId（一次越限就算一个设备）
     * - 次数 = 事件总数（一天内可多次）
     * 按 firstOverTime 今日范围过滤。
     */
    private SeVoltageDashboardVO queryVoltageToday(String id, Integer type) {
        LocalDateTime todayStart = LocalDate.now().atStartOfDay();
        LocalDateTime tomorrowStart = LocalDate.now().plusDays(1).atStartOfDay();

        // 三个维度分别查
        int[] feederStats = countVoltageByAlarmLevel(id, type, "feeder", todayStart, tomorrowStart);
        int[] mvtransStats = countVoltageByAlarmLevel(id, type, "mvtrans", todayStart, tomorrowStart);
        int[] consumerStats = countVoltageByAlarmLevel(id, type, "consumer", todayStart, tomorrowStart);

        return SeVoltageDashboardVO.builder()
                // 越上限：线路/台区/用户
                .overUpperFeederCount(feederStats[0]).overUpperFeederTimes(feederStats[2])
                .overUpperAreaCount(mvtransStats[0]).overUpperAreaTimes(mvtransStats[2])
                .overUpperConsumerCount(consumerStats[0]).overUpperConsumerTimes(consumerStats[2])
                // 越下限：线路/台区/用户
                .overLowerFeederCount(feederStats[1]).overLowerFeederTimes(feederStats[3])
                .overLowerAreaCount(mvtransStats[1]).overLowerAreaTimes(mvtransStats[3])
                .overLowerConsumerCount(consumerStats[1]).overLowerConsumerTimes(consumerStats[3])
                .build();
    }

    /**
     * 按 alarmLevel 统计今日电压越限
     * @return int[4]: [0]=越上限去重数, [1]=越下限去重数, [2]=越上限次数, [3]=越下限次数
     */
    private int[] countVoltageByAlarmLevel(String id, Integer type, String alarmLevel,
                                            LocalDateTime start, LocalDateTime end) {
        QueryWrapper<FhzgSeVoltageEvent> qw = new QueryWrapper<>();
        qw.select(
                "COUNT(DISTINCT CASE WHEN alarm_type LIKE '%over%' THEN device_id END) AS over_cnt",
                "COUNT(DISTINCT CASE WHEN alarm_type LIKE '%under%' THEN device_id END) AS under_cnt",
                "COUNT(CASE WHEN alarm_type LIKE '%over%' THEN 1 END) AS over_times",
                "COUNT(CASE WHEN alarm_type LIKE '%under%' THEN 1 END) AS under_times")
                .eq("alarm_level", alarmLevel)
                .ge("first_over_time", start)
                .lt("first_over_time", end);
        if (type == 2) {
            qw.eq("county_id", id);
        } else if (type == 3) {
            qw.eq("subs_id", id);
        } else if (type == 4) {
            qw.eq("feeder_id", id);
        }

        List<Map<String, Object>> rows = voltageEventMapper.selectMaps(qw);
        if (rows.isEmpty()) return new int[]{0, 0, 0, 0};
        Map<String, Object> row = rows.get(0);
        return new int[]{
                ((Number) row.getOrDefault("over_cnt", 0)).intValue(),
                ((Number) row.getOrDefault("under_cnt", 0)).intValue(),
                ((Number) row.getOrDefault("over_times", 0)).intValue(),
                ((Number) row.getOrDefault("under_times", 0)).intValue()
        };
    }

    /**
     * 按节点+时段统计重过载事件数
     * 返回 int[2]: [0]=重载事件数, [1]=过载事件数
     */
    private int[] countCurrentEvents(String id, Integer type,
                                      LocalDateTime start, LocalDateTime end) {
        LambdaQueryWrapper<FhzgSeCurrentEvent> wrapper = new LambdaQueryWrapper<FhzgSeCurrentEvent>()
                .ge(FhzgSeCurrentEvent::getFirstOverTime, start)
                .lt(FhzgSeCurrentEvent::getFirstOverTime, end)
                .in(FhzgSeCurrentEvent::getAlarmType, "current_heavy", "current_overload");
        if (type == 2) {
            wrapper.eq(FhzgSeCurrentEvent::getCountyId, id);
        } else if (type == 3) {
            wrapper.eq(FhzgSeCurrentEvent::getSubsId, id);
        } else if (type == 4) {
            wrapper.eq(FhzgSeCurrentEvent::getFeederId, id);
        }

        List<FhzgSeCurrentEvent> events = currentEventMapper.selectList(wrapper);
        int heavy = 0, overload = 0;
        for (FhzgSeCurrentEvent e : events) {
            if ("current_heavy".equals(e.getAlarmType())) heavy++;
            else if ("current_overload".equals(e.getAlarmType())) overload++;
        }
        return new int[]{heavy, overload};
    }

    /**
     * 历史电压：查 ads_mrt_voltage_over_limit_daily_result，SUM 聚合到 SQL
     * 当月 = 月初~昨天；全年 = 1.1~昨天
     * 自适应：查中台表 MAX(over_date) 判断数据最新到哪天，
     * 中台未到昨天时 → 中台[到前天] + 事件表补[昨天]；中台已到昨天 → 中台[到昨天]
     */
    private SeVoltageDashboardVO queryVoltageHistory(String id, Integer type, String timeScope) {
        LocalDate today = LocalDate.now();
        LocalDate startDate;

        if ("month".equalsIgnoreCase(timeScope)) {
            startDate = today.withDayOfMonth(1);
        } else {
            startDate = LocalDate.of(today.getYear(), 1, 1);
        }

        String dimLevel = resolveDimLevel(type);

        // 中台覆盖到哪天（T-2，动态自适应）；无数据时 midEnd < startDate → 中台贡献为 0
        String maxOverDate = queryDailyMaxOverDate(dimLevel, id, type);
        LocalDate midEnd = startDate.minusDays(1);
        if (maxOverDate != null) {
            try {
                LocalDate mo = LocalDate.parse(maxOverDate, DATE_FMT);
                midEnd = mo.isAfter(today) ? today : mo;
            } catch (Exception e) {
                log.warn("中台 over_date 解析失败: {}, 忽略中台贡献", maxOverDate);
            }
        }

        int overUpperSevereTg = 0, overUpperSevereCust = 0, overUpperSevereFeeder = 0, overUpperSevereGen = 0;
        int overUpperMildTg = 0, overUpperMildCust = 0, overUpperMildFeeder = 0, overUpperMildGen = 0;
        int overLowerSevereTg = 0, overLowerSevereCust = 0, overLowerSevereFeeder = 0, overLowerSevereGen = 0;
        int overLowerMildTg = 0, overLowerMildCust = 0, overLowerMildFeeder = 0, overLowerMildGen = 0;
        BigDecimal totalHours = BigDecimal.ZERO;

        // ===== 1. 中台贡献区间 [startDate, midEnd]（T-2 滞后，月初/年初可能为空）=====
        if (!midEnd.isBefore(startDate)) {
            // 一条 SQL 聚合 16个count字段 + total_over_limit_hours
            QueryWrapper<FhzgDwmVoltageOverLimitDaily> qw = new QueryWrapper<>();
            qw.select(
                    "COALESCE(SUM(over_upper_severe_tg_count),0) AS over_upper_severe_tg_count",
                    "COALESCE(SUM(over_upper_severe_cust_count),0) AS over_upper_severe_cust_count",
                    "COALESCE(SUM(over_upper_severe_feeder_count),0) AS over_upper_severe_feeder_count",
                    "COALESCE(SUM(over_upper_severe_generatrix_count),0) AS over_upper_severe_generatrix_count",
                    "COALESCE(SUM(over_upper_mild_tg_count),0) AS over_upper_mild_tg_count",
                    "COALESCE(SUM(over_upper_mild_cust_count),0) AS over_upper_mild_cust_count",
                    "COALESCE(SUM(over_upper_mild_feeder_count),0) AS over_upper_mild_feeder_count",
                    "COALESCE(SUM(over_upper_mild_generatrix_count),0) AS over_upper_mild_generatrix_count",
                    "COALESCE(SUM(over_lower_severe_tg_count),0) AS over_lower_severe_tg_count",
                    "COALESCE(SUM(over_lower_severe_cust_count),0) AS over_lower_severe_cust_count",
                    "COALESCE(SUM(over_lower_severe_feeder_count),0) AS over_lower_severe_feeder_count",
                    "COALESCE(SUM(over_lower_severe_generatrix_count),0) AS over_lower_severe_generatrix_count",
                    "COALESCE(SUM(over_lower_mild_tg_count),0) AS over_lower_mild_tg_count",
                    "COALESCE(SUM(over_lower_mild_cust_count),0) AS over_lower_mild_cust_count",
                    "COALESCE(SUM(over_lower_mild_feeder_count),0) AS over_lower_mild_feeder_count",
                    "COALESCE(SUM(over_lower_mild_generatrix_count),0) AS over_lower_mild_generatrix_count",
                    "COALESCE(SUM(total_over_limit_hours),0) AS total_over_limit_hours")
                    .eq("dim_level", dimLevel)
                    .ge("over_date", startDate.format(DATE_FMT))
                    .le("over_date", midEnd.format(DATE_FMT));

            // 维度过滤
            if (type == 2) {
                qw.eq("maintainer_id", id);
            } else if (type == 3) {
                qw.eq("substation_id", id);
            } else if (type == 4) {
                qw.eq("feeder_id", id);
            }

            List<FhzgDwmVoltageOverLimitDaily> records = voltageDailyMapper.selectList(qw);
            FhzgDwmVoltageOverLimitDaily r = (records != null && !records.isEmpty()) ? records.get(0) : null;

            overUpperSevereTg = r != null ? safeInt(r.getOverUpperSevereTgCount()) : 0;
            overUpperSevereCust = r != null ? safeInt(r.getOverUpperSevereCustCount()) : 0;
            overUpperSevereFeeder = r != null ? safeInt(r.getOverUpperSevereFeederCount()) : 0;
            overUpperSevereGen = r != null ? safeInt(r.getOverUpperSevereGeneratrixCount()) : 0;
            overUpperMildTg = r != null ? safeInt(r.getOverUpperMildTgCount()) : 0;
            overUpperMildCust = r != null ? safeInt(r.getOverUpperMildCustCount()) : 0;
            overUpperMildFeeder = r != null ? safeInt(r.getOverUpperMildFeederCount()) : 0;
            overUpperMildGen = r != null ? safeInt(r.getOverUpperMildGeneratrixCount()) : 0;
            overLowerSevereTg = r != null ? safeInt(r.getOverLowerSevereTgCount()) : 0;
            overLowerSevereCust = r != null ? safeInt(r.getOverLowerSevereCustCount()) : 0;
            overLowerSevereFeeder = r != null ? safeInt(r.getOverLowerSevereFeederCount()) : 0;
            overLowerSevereGen = r != null ? safeInt(r.getOverLowerSevereGeneratrixCount()) : 0;
            overLowerMildTg = r != null ? safeInt(r.getOverLowerMildTgCount()) : 0;
            overLowerMildCust = r != null ? safeInt(r.getOverLowerMildCustCount()) : 0;
            overLowerMildFeeder = r != null ? safeInt(r.getOverLowerMildFeederCount()) : 0;
            overLowerMildGen = r != null ? safeInt(r.getOverLowerMildGeneratrixCount()) : 0;
            totalHours = r != null && r.getTotalOverLimitHours() != null
                    ? r.getTotalOverLimitHours() : BigDecimal.ZERO;
        }

        // ===== 2. 事件表补差区间 [max(midEnd+1, startDate), today]（跨月/跨年不越界）=====
        LocalDate fillStart = midEnd.plusDays(1);
        if (fillStart.isBefore(startDate)) {
            fillStart = startDate;
        }
        if (!fillStart.isAfter(today)) {
            LocalDateTime fillStartTime = fillStart.atStartOfDay();
            LocalDateTime fillEndTime = today.plusDays(1).atStartOfDay();

            QueryWrapper<FhzgSeVoltageEvent> agg = new QueryWrapper<>();
            agg.select("alarm_level", "alarm_type", "COUNT(*) AS cnt")
                    .ge("trigger_time", fillStartTime)
                    .lt("trigger_time", fillEndTime)
                    .eq("status", 2)
                    .groupBy("alarm_level", "alarm_type");
            if (type == 2) agg.eq("county_id", id);
            else if (type == 3) agg.eq("subs_id", id);
            else if (type == 4) agg.eq("feeder_id", id);

            List<Map<String, Object>> aggRows = voltageEventMapper.selectMaps(agg);
            for (Map<String, Object> row : aggRows) {
                String alarmLevel = (String) row.get("alarm_level");
                String alarmType = (String) row.get("alarm_type");
                int cnt = ((Number) row.getOrDefault("cnt", 0)).intValue();
                if (alarmType == null) continue;
                boolean isOver = alarmType.contains("over");
                boolean isUnder = alarmType.contains("under");
                if (!isOver && !isUnder) continue;
                boolean isSevere = alarmType.contains("severe");

                if ("consumer".equals(alarmLevel)) {
                    if (isOver) {
                        if (isSevere) overUpperSevereCust += cnt;
                        else overUpperMildCust += cnt;
                    } else {
                        if (isSevere) overLowerSevereCust += cnt;
                        else overLowerMildCust += cnt;
                    }
                } else if ("mvtrans".equals(alarmLevel)) {
                    if (isOver) overUpperMildTg += cnt;
                    else overLowerMildTg += cnt;
                } else if ("feeder".equals(alarmLevel)) {
                    if (isOver) overUpperMildFeeder += cnt;
                    else overLowerMildFeeder += cnt;
                }
                // generatrix → 跳过
            }
        }

        return SeVoltageDashboardVO.builder()
                .overUpperSevereTgCount(overUpperSevereTg)
                .overUpperSevereCustCount(overUpperSevereCust)
                .overUpperSevereFeederCount(overUpperSevereFeeder)
                .overUpperSevereGeneratrixCount(overUpperSevereGen)
                .overUpperMildTgCount(overUpperMildTg)
                .overUpperMildCustCount(overUpperMildCust)
                .overUpperMildFeederCount(overUpperMildFeeder)
                .overUpperMildGeneratrixCount(overUpperMildGen)
                .overLowerSevereTgCount(overLowerSevereTg)
                .overLowerSevereCustCount(overLowerSevereCust)
                .overLowerSevereFeederCount(overLowerSevereFeeder)
                .overLowerSevereGeneratrixCount(overLowerSevereGen)
                .overLowerMildTgCount(overLowerMildTg)
                .overLowerMildCustCount(overLowerMildCust)
                .overLowerMildFeederCount(overLowerMildFeeder)
                .overLowerMildGeneratrixCount(overLowerMildGen)
                .totalOverLimitHours(totalHours)
                .build();
    }

    // ============================================================
    // 3. 供电能力
    // ============================================================

    @Override
    public SeCapacityDashboardVO queryCapacity(String id, Integer type, String timeScope) {
        if (StringUtils.isBlank(id) || type == null || StringUtils.isBlank(timeScope)) {
            return null;
        }

        // 今日 → 实时查断面明细（量小）
        if ("today".equalsIgnoreCase(timeScope)) {
            return queryCapacityToday(id, type);
        }

        // month/year → 查日汇总表
        return queryCapacityHistory(id, type, timeScope);
    }

    /**
     * 今日供电能力：负载率从断面明细聚合，重过载次数从事件表统计
     */
    private SeCapacityDashboardVO queryCapacityToday(String id, Integer type) {
        LocalDate today = LocalDate.now();
        LocalDateTime startTime = today.atStartOfDay();
        LocalDateTime endTime = LocalDateTime.now();

        // 1. 负载率：从断面明细聚合（保持馈线96点时序）
        LambdaQueryWrapper<FhzgSeSnapshotDetail> snapshotWrapper = new LambdaQueryWrapper<FhzgSeSnapshotDetail>()
                .eq(FhzgSeSnapshotDetail::getDeviceType, "feeder")
                .isNotNull(FhzgSeSnapshotDetail::getLoadRate)
                .ge(FhzgSeSnapshotDetail::getSnapshotTime, startTime)
                .le(FhzgSeSnapshotDetail::getSnapshotTime, endTime);
        applySnapshotFilter(snapshotWrapper, id, type);
        List<FhzgSeSnapshotDetail> snapshots = snapshotDetailMapper.selectList(snapshotWrapper);

        SeCapacityDashboardVO vo;
        if (snapshots.isEmpty()) {
            vo = SeCapacityDashboardVO.builder().build();
        } else {
            vo = aggregateSnapshots(snapshots);
            // 馈线 → 附加96点时序
            if (type == 4) {
                vo.setLoadRateSeries(buildFeederTimeSeries(snapshots));
            }
        }

        // 2. 重过载事件次数：从事件表统计
        int[] eventCounts = countCurrentEvents(id, type, startTime, endTime);
        vo.setHeavyLoadEventCount(eventCounts[0]);
        vo.setOverloadEventCount(eventCounts[1]);
        int totalEvents = eventCounts[0] + eventCounts[1];
        vo.setTotalEventCount(totalEvents);
        if (totalEvents > 0) {
            vo.setHeavyLoadRate(new BigDecimal(eventCounts[0] * 100)
                    .divide(BigDecimal.valueOf(totalEvents), 2, RoundingMode.HALF_UP));
            vo.setOverloadRate(new BigDecimal(eventCounts[1] * 100)
                    .divide(BigDecimal.valueOf(totalEvents), 2, RoundingMode.HALF_UP));
        } else {
            vo.setHeavyLoadRate(BigDecimal.ZERO);
            vo.setOverloadRate(BigDecimal.ZERO);
        }

        // 3. 线损电量：从明细表聚合（今日未刷日汇总，用明细实时算）
        vo.setLineLossKwh(queryLineLossKwhFromDetail(id, type, startTime, endTime));

        return vo;
    }

    /**
     * 历史供电能力（分层口径）：
     * 历史部分（T+1）查日汇总表 [startDate, yesterday] —— 今天尚未入表；
     * 今日部分实时补：负载率从断面明细聚合、重过载次数从事件表统计、线损从明细 SUM。
     * month = 月初 ~ 今天；year = 1.1 ~ 今天
     */
    private SeCapacityDashboardVO queryCapacityHistory(String id, Integer type, String timeScope) {
        LocalDate today = LocalDate.now();
        LocalDate startDate;
        LocalDate endDate = today.minusDays(1);

        if ("month".equalsIgnoreCase(timeScope)) {
            startDate = today.withDayOfMonth(1);
        } else {
            startDate = LocalDate.of(today.getYear(), 1, 1);
        }

        // 历史部分统计（日汇总表 T+1，只到昨天）
        BigDecimal histMaxLoadRate = null;
        BigDecimal histSumAvg = BigDecimal.ZERO;
        int histDays = 0;
        long sumHeavy = 0;
        long sumOverload = 0;
        BigDecimal histLineLossKwh = BigDecimal.ZERO;

        if (!endDate.isBefore(startDate)) {
            LambdaQueryWrapper<FhzgSeCapacityDaily> wrapper = new LambdaQueryWrapper<FhzgSeCapacityDaily>()
                    .ge(FhzgSeCapacityDaily::getStatDate, startDate)
                    .le(FhzgSeCapacityDaily::getStatDate, endDate);
            applyCapacityDailyFilter(wrapper, id, type);
            List<FhzgSeCapacityDaily> dailyRecords = capacityDailyMapper.selectList(wrapper);

            for (FhzgSeCapacityDaily d : dailyRecords) {
                if (d.getMaxLoadRate() != null
                        && (histMaxLoadRate == null || d.getMaxLoadRate().compareTo(histMaxLoadRate) > 0)) {
                    histMaxLoadRate = d.getMaxLoadRate();
                }
                if (d.getAvgLoadRate() != null) {
                    histSumAvg = histSumAvg.add(d.getAvgLoadRate());
                    histDays++;
                }
                // 重过载事件数：日统计表存的就是当日事件表事件数，直接累加
                if (d.getHeavyCount() != null) sumHeavy += d.getHeavyCount();
                if (d.getOverloadCount() != null) sumOverload += d.getOverloadCount();
            }
            // 线损电量（历史部分）
            histLineLossKwh = queryLineLossKwhFromDaily(id, type, startDate, endDate);
        }

        // 今日部分实时聚合（断面明细 + 事件表 + 线损明细）
        SeCapacityDashboardVO todayVo = queryCapacityToday(id, type);
        BigDecimal todayMaxLoadRate = todayVo != null ? todayVo.getMaxLoadRate() : null;
        BigDecimal todayAvgLoadRate = todayVo != null ? todayVo.getAvgLoadRate() : null;
        int todayHeavy = todayVo != null ? todayVo.getHeavyLoadEventCount() : 0;
        int todayOverload = todayVo != null ? todayVo.getOverloadEventCount() : 0;
        BigDecimal todayLineLoss = todayVo != null ? todayVo.getLineLossKwh() : null;

        // 合并
        BigDecimal maxLoadRate = histMaxLoadRate;
        if (todayMaxLoadRate != null
                && (maxLoadRate == null || todayMaxLoadRate.compareTo(maxLoadRate) > 0)) {
            maxLoadRate = todayMaxLoadRate;
        }

        // 平均负载率：加权平均（历史每天一个均值 + 今日均值）
        BigDecimal avgLoadRate = null;
        if (todayAvgLoadRate != null && histDays > 0) {
            avgLoadRate = histSumAvg.add(todayAvgLoadRate)
                    .divide(BigDecimal.valueOf(histDays + 1), 2, RoundingMode.HALF_UP);
        } else if (todayAvgLoadRate != null) {
            avgLoadRate = todayAvgLoadRate;
        } else if (histDays > 0) {
            avgLoadRate = histSumAvg.divide(BigDecimal.valueOf(histDays), 2, RoundingMode.HALF_UP);
        }

        long totalHeavy = sumHeavy + todayHeavy;
        long totalOverload = sumOverload + todayOverload;
        long totalEvents = totalHeavy + totalOverload;

        BigDecimal lineLossKwh = histLineLossKwh;
        if (todayLineLoss != null) {
            lineLossKwh = lineLossKwh.add(todayLineLoss);
        }

        SeCapacityDashboardVO vo = SeCapacityDashboardVO.builder()
                .maxLoadRate(maxLoadRate)
                .avgLoadRate(avgLoadRate)
                .heavyLoadEventCount((int) totalHeavy)
                .overloadEventCount((int) totalOverload)
                .totalEventCount((int) totalEvents)
                .lineLossKwh(lineLossKwh)
                .build();
        if (totalEvents > 0) {
            vo.setHeavyLoadRate(new BigDecimal(totalHeavy * 100)
                    .divide(BigDecimal.valueOf(totalEvents), 2, RoundingMode.HALF_UP));
            vo.setOverloadRate(new BigDecimal(totalOverload * 100)
                    .divide(BigDecimal.valueOf(totalEvents), 2, RoundingMode.HALF_UP));
        } else {
            vo.setHeavyLoadRate(BigDecimal.ZERO);
            vo.setOverloadRate(BigDecimal.ZERO);
        }

        return vo;
    }

    /**
     * 内存聚合断面列表 → VO（today用，只算负载率，事件次数由 queryCapacityToday 从事件表统计）
     */
    private SeCapacityDashboardVO aggregateSnapshots(List<FhzgSeSnapshotDetail> snapshots) {
        BigDecimal maxLoadRate = snapshots.stream()
                .map(FhzgSeSnapshotDetail::getLoadRate)
                .filter(Objects::nonNull)
                .reduce(BigDecimal::max)
                .orElse(BigDecimal.ZERO);

        BigDecimal avgLoadRate = snapshots.stream()
                .map(FhzgSeSnapshotDetail::getLoadRate)
                .filter(Objects::nonNull)
                .reduce(BigDecimal.ZERO, BigDecimal::add)
                .divide(BigDecimal.valueOf(snapshots.size()), 2, RoundingMode.HALF_UP);

        return SeCapacityDashboardVO.builder()
                .maxLoadRate(maxLoadRate)
                .avgLoadRate(avgLoadRate)
                .build();
    }

    /**
     * 构建馈线今日96点时序（负载率+电流+电压+有功+无功）
     */
    private List<SeCapacityDashboardVO.TimeSeriesPoint> buildFeederTimeSeries(List<FhzgSeSnapshotDetail> snapshots) {
        DateTimeFormatter hmFmt = DateTimeFormatter.ofPattern("HH:mm");
        return snapshots.stream()
                .sorted(Comparator.comparing(FhzgSeSnapshotDetail::getSnapshotTime))
                .map(s -> SeCapacityDashboardVO.TimeSeriesPoint.builder()
                        .time(s.getSnapshotTime().format(hmFmt))
                        .loadRate(s.getLoadRate())
                        .currentValue(s.getCurrentValue())
                        .voltageValue(s.getVoltageValue())
                        .activePower(s.getActivePower())
                        .reactivePower(s.getReactivePower())
                        .build())
                .collect(Collectors.toList());
    }

    // ============================================================
    // 辅助方法
    // ============================================================

    private void applyNodeFilter(LambdaQueryWrapper<FhzgSeVoltageEvent> wrapper, String id, Integer type) {
        if (type == 2) {
            wrapper.eq(FhzgSeVoltageEvent::getCountyId, id);
        } else if (type == 3) {
            wrapper.eq(FhzgSeVoltageEvent::getSubsId, id);
        } else if (type == 4) {
            wrapper.eq(FhzgSeVoltageEvent::getFeederId, id);
        }
    }

    private void applySnapshotFilter(LambdaQueryWrapper<FhzgSeSnapshotDetail> wrapper, String id, Integer type) {
        if (type == 2) {
            wrapper.eq(FhzgSeSnapshotDetail::getCountyId, id);
        } else if (type == 3) {
            wrapper.eq(FhzgSeSnapshotDetail::getSubsId, id);
        } else if (type == 4) {
            wrapper.eq(FhzgSeSnapshotDetail::getFeederId, id);
        }
    }

    private void applyCapacityDailyFilter(LambdaQueryWrapper<FhzgSeCapacityDaily> wrapper, String id, Integer type) {
        if (type == 2) {
            wrapper.eq(FhzgSeCapacityDaily::getCountyId, id);
        } else if (type == 3) {
            wrapper.eq(FhzgSeCapacityDaily::getSubsId, id);
        } else if (type == 4) {
            wrapper.eq(FhzgSeCapacityDaily::getFeederId, id);
        }
    }

    private String resolveDimLevel(Integer type) {
        if (type == 2) return "county";
        if (type == 3) return "substation";
        if (type == 4) return "feeder";
        return "county";
    }

    private int safeInt(Integer v) {
        return v == null ? 0 : v;
    }

    /**
     * 查中台日汇总表该维度的最新 over_date
     */
    private String queryDailyMaxOverDate(String dimLevel, String id, Integer type) {
        QueryWrapper<FhzgDwmVoltageOverLimitDaily> qw = new QueryWrapper<>();
        qw.select("MAX(over_date) AS max_over_date")
                .eq("dim_level", dimLevel);
        if (type == 2) qw.eq("maintainer_id", id);
        else if (type == 3) qw.eq("substation_id", id);
        else if (type == 4) qw.eq("feeder_id", id);
        qw.last("LIMIT 1");
        List<Map<String, Object>> records = voltageDailyMapper.selectMaps(qw);
        if (records != null && !records.isEmpty()) {
            Object val = records.get(0).get("max_over_date");
            return val != null ? val.toString() : null;
        }
        return null;
    }

    // ============================================================
    // 4. IEC 用户电压指数
    // ============================================================

    /**
     * IEC 用户电压指数 = 用户电压合格率（%）
     * <p>
     * 计算：合格率 = 100 − 越限率；越限率 = 用户越限总时长(上+下) / (总用户数 × 周期天数 × 24h) × 100
     * <p>
     * 分子（组合）：
     * 历史 = 中台 ads_mrt_voltage_over_limit_daily_result.total_over_limit_hours（SQL SUM）；
     * 昨日/今日 = 事件表 fhzg_se_voltage_event(alarm_level='consumer') Σ(recover_time - first_over_time)。
     * 中台最新日期动态自适应（MAX(over_date)），事件表只补中台未覆盖的区间。
     * <p>
     * 分母 = 分层分级用户数 × 周期天数 × 24（时户数）；
     * 用户数取最近一天日表值（用户数日变化可忽略，无需逐日累加/历史回填）。
     * <p>
     * 返回值 voltageIndex 为合格率（0~100），如 98.93 表示电压合格率 98.93%。
     */
    @Override
    public SeVoltageIndexVO queryVoltageIndex(String id, Integer type, String timeScope) {
        if (StringUtils.isBlank(id) || type == null || StringUtils.isBlank(timeScope)) {
            return null;
        }

        LocalDate today = LocalDate.now();
        LocalDate yesterday = today.minusDays(1);

        BigDecimal overLimitHours;
        BigDecimal consumerHours;
        BigDecimal avgConsumerCount;

        if ("day".equalsIgnoreCase(timeScope)) {
            // day：分子 = 今日事件表时长；分母 = 最近一天用户数 × 24
            overLimitHours = sumConsumerEventHours(id, type,
                    today.atStartOfDay(), today.plusDays(1).atStartOfDay());
            int count = latestConsumerCount(id, type, yesterday);
            avgConsumerCount = BigDecimal.valueOf(count);
            consumerHours = avgConsumerCount.multiply(HOURS_24);
        } else {
            // month/year：分子 = 中台[start~endDate] + 事件表[endDate+1~今天]
            // 分母 = 最近一天用户数 × 实际天数 × 24
            LocalDate startDate;
            if ("month".equalsIgnoreCase(timeScope)) {
                startDate = today.withDayOfMonth(1);
            } else {
                startDate = LocalDate.of(today.getYear(), 1, 1);
            }

            // 中台覆盖到哪天（自适应）
            String dimLevel = resolveDimLevel(type);
            String maxOverDate = queryDailyMaxOverDate(dimLevel, id, type);
            LocalDate endDate = yesterday;
            if (maxOverDate != null) {
                try {
                    endDate = LocalDate.parse(maxOverDate, DATE_FMT);
                } catch (Exception e) {
                    log.warn("中台 over_date 解析失败: {}, 用昨天", maxOverDate);
                }
            }
            if (endDate.isAfter(yesterday)) {
                endDate = yesterday;
            }

            // 分子：中台区间 + 事件表补区间（补差起点 = max(中台最后一天+1, 业务起点)，跨月/跨年不越界）
            BigDecimal zhongtaiHours = sumZhongtaiOverLimitHours(id, type, startDate, endDate);
            LocalDate fillStart = endDate.plusDays(1);
            if (fillStart.isBefore(startDate)) {
                fillStart = startDate;
            }
            BigDecimal eventHours = fillStart.isBefore(today)
                    ? sumConsumerEventHours(id, type,
                    fillStart.atStartOfDay(), today.plusDays(1).atStartOfDay())
                    : BigDecimal.ZERO;
            overLimitHours = zhongtaiHours.add(eventHours);

            // 分母：最近一天用户数 × 实际天数 × 24（用户数日变化可忽略，无需逐日累加）
            int count = latestConsumerCount(id, type, yesterday);
            long days = ChronoUnit.DAYS.between(startDate, today) + 1;
            avgConsumerCount = BigDecimal.valueOf(count);
            consumerHours = BigDecimal.valueOf(count)
                    .multiply(BigDecimal.valueOf(days))
                    .multiply(HOURS_24);
        }

        BigDecimal voltageIndex = null;
        if (consumerHours.compareTo(BigDecimal.ZERO) > 0) {
            // 越限率 = 用户越限总时长 / 总时户数 × 100
            BigDecimal overRate = overLimitHours
                    .divide(consumerHours, 6, RoundingMode.HALF_UP)
                    .multiply(new BigDecimal("100"))
                    .setScale(2, RoundingMode.HALF_UP);
            // 合格率 = 100 − 越限率（展示口径与国网"居民客户端电压合格率"一致）
            voltageIndex = new BigDecimal("100").subtract(overRate);
        }

        return SeVoltageIndexVO.builder()
                .voltageIndex(voltageIndex)
                .overLimitHours(overLimitHours.setScale(2, RoundingMode.HALF_UP))
                .consumerHours(consumerHours.setScale(0, RoundingMode.HALF_UP))
                .avgConsumerCount(avgConsumerCount)
                .build();
    }

    /**
     * 中台表越限总时长 SUM（分子历史部分）
     * <p>
     * 注意：total_over_limit_hours 为中台提供的当日越限总时长（台区低压侧口径，近似用户维度）
     */
    private BigDecimal sumZhongtaiOverLimitHours(String id, Integer type,
                                                 LocalDate startDate, LocalDate endDate) {
        if (startDate.isAfter(endDate)) {
            return BigDecimal.ZERO;
        }
        QueryWrapper<FhzgDwmVoltageOverLimitDaily> qw = new QueryWrapper<>();
        qw.select("COALESCE(SUM(total_over_limit_hours),0) AS total_hours")
                .eq("dim_level", resolveDimLevel(type))
                .ge("over_date", startDate.format(DATE_FMT))
                .le("over_date", endDate.format(DATE_FMT));
        if (type == 2) {
            qw.eq("maintainer_id", id);
        } else if (type == 3) {
            qw.eq("substation_id", id);
        } else if (type == 4) {
            qw.eq("feeder_id", id);
        }
        List<Map<String, Object>> rows = voltageDailyMapper.selectMaps(qw);
        if (rows == null || rows.isEmpty() || rows.get(0).get("total_hours") == null) {
            return BigDecimal.ZERO;
        }
        return new BigDecimal(rows.get(0).get("total_hours").toString());
    }

    /**
     * 事件表用户越限总时长（小时）：Σ(recover_time - first_over_time)
     * 进行中事件（recover_time 为空）用当前时间兜底
     */
    private BigDecimal sumConsumerEventHours(String id, Integer type,
                                             LocalDateTime start, LocalDateTime end) {
        QueryWrapper<FhzgSeVoltageEvent> qw = new QueryWrapper<>();
        qw.select("COALESCE(SUM(TIMESTAMPDIFF(SECOND, first_over_time, COALESCE(recover_time, NOW()))),0) AS secs")
                .eq("alarm_level", "consumer")
                .ge("first_over_time", start)
                .lt("first_over_time", end);
        if (type == 2) {
            qw.eq("county_id", id);
        } else if (type == 3) {
            qw.eq("subs_id", id);
        } else if (type == 4) {
            qw.eq("feeder_id", id);
        }
        List<Map<String, Object>> rows = voltageEventMapper.selectMaps(qw);
        if (rows == null || rows.isEmpty() || rows.get(0).get("secs") == null) {
            return BigDecimal.ZERO;
        }
        long secs = ((Number) rows.get(0).get("secs")).longValue();
        return BigDecimal.valueOf(secs).divide(BigDecimal.valueOf(3600), 4, RoundingMode.HALF_UP);
    }

    /**
     * 日表最近一天的用户数（&lt;= beforeDate）
     * 日表无数据时兜底实时查用户关系表（仅首次部署/日表为空时触发，正常路径不实时查）
     */
    private int latestConsumerCount(String id, Integer type, LocalDate beforeDate) {
        QueryWrapper<FhzgSeConsumerCountDaily> qw = new QueryWrapper<>();
        qw.select("consumer_count")
                .eq("dim_level", resolveDimLevel(type))
                .le("stat_date", beforeDate)
                .orderByDesc("stat_date")
                .last("LIMIT 1");
        if (type == 2) {
            qw.eq("county_id", id);
        } else if (type == 3) {
            qw.eq("subs_id", id);
        } else if (type == 4) {
            qw.eq("feeder_id", id);
        }
        List<Map<String, Object>> rows = consumerCountDailyMapper.selectMaps(qw);
        if (rows != null && !rows.isEmpty() && rows.get(0).get("consumer_count") != null) {
            return ((Number) rows.get(0).get("consumer_count")).intValue();
        }

        // 兜底：日表未刷时实时 COUNT
        QueryWrapper<FhzgSeConsumerRelation> cw = new QueryWrapper<>();
        cw.select("COUNT(*) AS cnt");
        if (type == 2) {
            cw.eq("county_id", id);
        } else if (type == 3) {
            cw.eq("subs_id", id);
        } else if (type == 4) {
            cw.eq("feeder_id", id);
        }
        List<Map<String, Object>> crows = consumerRelationMapper.selectMaps(cw);
        if (crows != null && !crows.isEmpty() && crows.get(0).get("cnt") != null) {
            return ((Number) crows.get(0).get("cnt")).intValue();
        }
        return 0;
    }

    // ============================================================
    // 8. 线损电量查询
    // ============================================================

    /**
     * 今日线损电量：从明细表聚合 SUM(line_loss_kw) × 0.25h
     */
    private BigDecimal queryLineLossKwhFromDetail(String id, Integer type,
                                                   LocalDateTime startTime, LocalDateTime endTime) {
        QueryWrapper<FhzgSeLineLossDetail> qw = new QueryWrapper<>();
        qw.select("COALESCE(SUM(line_loss_kw), 0) AS total_loss");
        if (type == 2) qw.eq("county_id", id);
        else if (type == 3) qw.eq("subs_id", id);
        else if (type == 4) qw.eq("feeder_id", id);
        qw.ge("snap_time", startTime).lt("snap_time", endTime);
        List<Map<String, Object>> rows = lineLossDetailMapper.selectMaps(qw);
        if (rows != null && !rows.isEmpty() && rows.get(0).get("total_loss") != null) {
            BigDecimal sumKw = ((Number) rows.get(0).get("total_loss")).longValue() == 0
                    ? BigDecimal.ZERO
                    : new BigDecimal(rows.get(0).get("total_loss").toString());
            // 每个断面间隔15min = 0.25h，线损功率 × 时间 = 电量
            return sumKw.multiply(new BigDecimal("0.25")).setScale(2, RoundingMode.HALF_UP);
        }
        return BigDecimal.ZERO;
    }

    /**
     * 历史线损电量：从日汇总表聚合 SUM(line_loss_kwh)
     */
    private BigDecimal queryLineLossKwhFromDaily(String id, Integer type,
                                                  LocalDate startDate, LocalDate endDate) {
        QueryWrapper<FhzgSeLineLossDaily> qw = new QueryWrapper<>();
        qw.select("COALESCE(SUM(line_loss_kwh), 0) AS total_kwh");
        if (type == 2) qw.eq("county_id", id);
        else if (type == 3) qw.eq("subs_id", id);
        else if (type == 4) qw.eq("feeder_id", id);
        qw.ge("stat_date", startDate).le("stat_date", endDate);
        List<Map<String, Object>> rows = lineLossDailyMapper.selectMaps(qw);
        if (rows != null && !rows.isEmpty() && rows.get(0).get("total_kwh") != null) {
            return new BigDecimal(rows.get(0).get("total_kwh").toString()).setScale(2, RoundingMode.HALF_UP);
        }
        return BigDecimal.ZERO;
    }
}
