package com.iot.scheduler.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import jakarta.annotation.Resource;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
@Service
public class EnergySearchService {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private CorpConfigService corpConfigService;

    /**
     * 分页查询能耗设备信息
     */
    public Map<String, Object> queryEnergyList(String corpCode, String deviceName, String runStatus,
                                               Integer pageNo, Integer pageSize) {
        String energyTableName = corpConfigService.getEnergyTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);

        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM " + energyTableName + " WHERE corp_code = ?");
        StringBuilder querySql = new StringBuilder(
                "SELECT id, deviceName, projectType, projectState, dtuSn, dtuId, deviceId, " +
                        "evalue, duration, runStatus, created_at, updated_at " +
                        "FROM " + energyTableName + " WHERE corp_code = ?");
        List<Object> params = new ArrayList<>();
        params.add(corpCode);

        if (StringUtils.hasText(deviceName)) {
            countSql.append(" AND deviceName LIKE ?");
            querySql.append(" AND deviceName LIKE ?");
            params.add("%" + deviceName + "%");
        }
        if (StringUtils.hasText(runStatus)) {
            countSql.append(" AND runStatus = ?");
            querySql.append(" AND runStatus = ?");
            params.add(runStatus);
        }

        Long total = jt.queryForObject(countSql.toString(), Long.class, params.toArray());
        int offset = (pageNo - 1) * pageSize;
        querySql.append(" ORDER BY deviceName ASC LIMIT ?, ?");
        params.add(offset);
        params.add(pageSize);

        List<Map<String, Object>> list = jt.queryForList(querySql.toString(), params.toArray());
        list.forEach(row -> {
            if (row.get("duration") != null) {
                long seconds = Long.parseLong(String.valueOf(row.get("duration")));
                long h = seconds / 3600;
                long m = (seconds % 3600) / 60;
                long s = seconds % 60;
                StringBuilder sb = new StringBuilder();
                if (h > 0) sb.append(h).append("时");
                if (h > 0 || m > 0) sb.append(m).append("分");
                sb.append(s).append("秒");
                row.put("duration", sb.toString());
            }
        });
        return Map.of("code", 200, "msg", "请求成功", "total", total != null ? total : 0, "pageNo", pageNo,
                "pageSize", pageSize, "list", list);
    }

    public Map<String, Object> queryEnergyStats(String corpCode) {
        String energyTableName = corpConfigService.getEnergyTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);
        String sql = "SELECT runStatus, COUNT(*) AS cnt FROM " + energyTableName + " WHERE corp_code = ? GROUP BY runStatus";
        List<Map<String, Object>> rows = jt.queryForList(sql, corpCode);
        int total = 0;
        Map<String, Integer> statusMap = new LinkedHashMap<>();
        statusMap.put("0", 0);
        statusMap.put("1", 0);
        statusMap.put("2", 0);
        statusMap.put("3", 0);
        for (Map<String, Object> row : rows) {
            String key = String.valueOf(row.get("runStatus"));
            int cnt = ((Number) row.get("cnt")).intValue();
            total += cnt;
            statusMap.merge(key, cnt, Integer::sum);
        }
        return Map.of("code", 200, "msg", "请求成功", "total", total, "0", statusMap.get("0"),
                "1", statusMap.get("1"), "2", statusMap.get("2"), "3", statusMap.get("3"));
    }

    public Map<String, Object> queryEnergyDetailByDate(String corpCode, String dtuSn, String date) {
        String eqKwhTableName = corpConfigService.getEqKwhTableName(corpCode);
        String eRunDtlTableName = corpConfigService.getERunDtlTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);

        Object kwhRawData = Collections.emptyList();
        BigDecimal totalKwh = BigDecimal.ZERO;
        try {
            String kwhSql = "SELECT description FROM " + eqKwhTableName + " WHERE corp_code = ? AND dtuSn = ? AND use_date = ?";
            Map<String, Object> kwhRow = jt.queryForMap(kwhSql, corpCode, dtuSn, date + " 00:00:00");
            if (kwhRow != null && kwhRow.get("description") != null) {
                String description = String.valueOf(kwhRow.get("description"));
                kwhRawData = JSON.parseArray(description);
                JSONArray dataArray = JSON.parseArray(description);
                for (int i = 0; i < dataArray.size(); i++) {
                    JSONObject item = dataArray.getJSONObject(i);
                    Double value = item.getDouble("value");
                    if (value != null) totalKwh = totalKwh.add(BigDecimal.valueOf(value));
                }
            }
        } catch (Exception e) {
            log.warn("【能耗明细】查询时用电量数据为空或异常 - corpCode:{}, dtuSn:{}, date:{}", corpCode, dtuSn, date, e);
        }

        Object oeeRawData = Collections.emptyList();
        long totalDuration = 0;
        Map<Integer, Long> statusDurationMap = new LinkedHashMap<>();
        statusDurationMap.put(0, 0L);
        statusDurationMap.put(1, 0L);
        statusDurationMap.put(2, 0L);
        statusDurationMap.put(3, 0L);
        try {
            String oeeSql = "SELECT runStatus1, runStatus2 FROM " + eRunDtlTableName + " WHERE corp_code = ? AND dtuSn = ? AND use_date = ?";
            Map<String, Object> oeeRow = jt.queryForMap(oeeSql, corpCode, dtuSn, date + " 00:00:00");
            if (oeeRow != null) {
                String rs1 = oeeRow.get("runStatus1") != null ? String.valueOf(oeeRow.get("runStatus1")) : "";
                String rs2 = oeeRow.get("runStatus2") != null ? String.valueOf(oeeRow.get("runStatus2")) : "";
                String jsonStr = rs1 + rs2;
                oeeRawData = JSON.parseArray(jsonStr);
                JSONArray dataArray = JSON.parseArray(jsonStr);
                for (int i = 0; i < dataArray.size(); i++) {
                    JSONObject item = dataArray.getJSONObject(i);
                    Long duration = item.getLong("duration");
                    Integer runStatus = item.getInteger("runStatus");
                    if (duration != null && duration > 0) {
                        totalDuration += duration;
                        int statusKey = runStatus != null ? runStatus : 0;
                        statusDurationMap.merge(statusKey, duration, Long::sum);
                    }
                }
            }
        } catch (Exception e) {
            log.warn("【能耗明细】查询OEE时序数据为空或异常 - corpCode:{}, dtuSn:{}, date:{}", corpCode, dtuSn, date, e);
        }

        List<Map<String, Object>> statusStats = new ArrayList<>();
        for (Map.Entry<Integer, Long> entry : statusDurationMap.entrySet()) {
            long dur = entry.getValue();
            double percent = totalDuration > 0 ? BigDecimal.valueOf(dur * 100.0 / totalDuration).setScale(2, RoundingMode.HALF_UP).doubleValue() : 0.0;
            Map<String, Object> stat = new LinkedHashMap<>();
            stat.put("status", entry.getKey());
            stat.put("durationSeconds", dur);
            stat.put("durationFormatted", formatDuration(dur));
            stat.put("percent", percent);
            statusStats.add(stat);
        }
        return Map.of("code", 200, "msg", "请求成功", "dtuSn", dtuSn, "date", date, "kwhData", Map.of("list", kwhRawData, "totalKwh", totalKwh.setScale(2, RoundingMode.HALF_UP)),
                "oeeData", Map.of("list", oeeRawData, "totalDurationFormatted", formatDuration(totalDuration), "totalDurationSeconds", totalDuration, "statusStats", statusStats));
    }

    public Map<String, Object> queryEnergyRuntimeDetail(String corpCode, String dtuSn, String type, String startDate, String endDate) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List<String> dateList = new ArrayList<>();
        if ("1".equals(type)) {
            dateList.add(startDate);
        } else if ("2".equals(type)) {
            try {
                Date start = sdf.parse(startDate);
                Date end = sdf.parse(endDate);
                Calendar cur = Calendar.getInstance();
                cur.setTime(start);
                while (!cur.getTime().after(end)) {
                    dateList.add(sdf.format(cur.getTime()));
                    cur.add(Calendar.DAY_OF_MONTH, 1);
                }
            } catch (Exception e) {
                return Map.of("code", 400, "msg", "日期格式错误");
            }
        } else if ("3".equals(type)) {
            Calendar now = Calendar.getInstance();
            int year = now.get(Calendar.YEAR);
            Calendar startCal = Calendar.getInstance();
            startCal.set(year, Calendar.JANUARY, 1);
            while (!startCal.getTime().after(now.getTime())) {
                dateList.add(sdf.format(startCal.getTime()));
                startCal.add(Calendar.DAY_OF_MONTH, 1);
            }
        }
        String eqKwhTableName = corpConfigService.getEqKwhTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);

        long totalDurationAll = 0;
        BigDecimal totalKwhAll = BigDecimal.ZERO;
        Map<Integer, Long> statusDurationAllMap = initStatusMap();
        List<Map<String, Object>> detailList = new ArrayList<>();
        Map<String, Map<String, Object>> monthAggMap = "3".equals(type) ? new LinkedHashMap<>() : null;

        for (String dateStr : dateList) {
            Object kwhRawData = Collections.emptyList();
            BigDecimal dayKwh = BigDecimal.ZERO;
            long dayTotalDuration = 0;
            Map<Integer, Long> dayStatusDuration = initStatusMap();
            try {
                String kwhSql = "SELECT description FROM " + eqKwhTableName + " WHERE corp_code = ? AND dtuSn = ? AND use_date = ?";
                Map<String, Object> kwhRow = jt.queryForMap(kwhSql, corpCode, dtuSn, dateStr + " 00:00:00");
                if (kwhRow != null && kwhRow.get("description") != null) {
                    String desc = String.valueOf(kwhRow.get("description"));
                    JSONArray dataArray = JSON.parseArray(desc);
                    for (int i = 0; i < dataArray.size(); i++) {
                        JSONObject item = dataArray.getJSONObject(i);
                        Double value = item.getDouble("value");
                        if (value != null) dayKwh = dayKwh.add(BigDecimal.valueOf(value));
                        for (int statusKey = 0; statusKey <= 3; statusKey++) {
                            Long dur = item.getLong(String.valueOf(statusKey));
                            if (dur != null && dur > 0) {
                                dayTotalDuration += dur;
                                totalDurationAll += dur;
                                dayStatusDuration.merge(statusKey, dur, Long::sum);
                                statusDurationAllMap.merge(statusKey, dur, Long::sum);
                            }
                        }
                    }
                    kwhRawData = dataArray;
                }
            } catch (Exception ignored) {
            }
            totalKwhAll = totalKwhAll.add(dayKwh);
            String periodKey = "3".equals(type) ? dateStr.substring(0, 7) : dateStr;

            if ("1".equals(type)) {
                Map<String, Object> entry = new LinkedHashMap<>();
                entry.put("date", periodKey);
                entry.put("kwhList", kwhRawData);
                entry.put("totalKwh", dayKwh.setScale(2, RoundingMode.HALF_UP));
                entry.put("totalDurationFormatted", formatDuration(dayTotalDuration));
                entry.put("totalDurationSeconds", dayTotalDuration);
                entry.put("statusStats", buildStatusStats(dayStatusDuration, dayTotalDuration));
                detailList.add(entry);
            } else if ("2".equals(type)) {
                Map<String, Object> entry = new LinkedHashMap<>();
                entry.put("date", periodKey);
                entry.put("totalKwh", dayKwh.setScale(2, RoundingMode.HALF_UP));
                entry.put("totalDurationFormatted", formatDuration(dayTotalDuration));
                entry.put("totalDurationSeconds", dayTotalDuration);
                entry.put("statusStats", buildStatusStats(dayStatusDuration, dayTotalDuration));
                detailList.add(entry);
            } else if ("3".equals(type)) {
                monthAggMap.computeIfAbsent(periodKey, k -> {
                    Map<String, Object> m = new LinkedHashMap<>();
                    m.put("date", k);
                    m.put("totalKwh", BigDecimal.ZERO);
                    m.put("totalDurationSeconds", 0L);
                    m.put("statusDurationMap", initStatusMap());
                    return m;
                });
                Map<String, Object> monthEntry = monthAggMap.get(periodKey);
                monthEntry.put("totalKwh", ((BigDecimal) monthEntry.get("totalKwh")).add(dayKwh));
                monthEntry.put("totalDurationSeconds", (Long) monthEntry.get("totalDurationSeconds") + dayTotalDuration);
                @SuppressWarnings("unchecked") Map<Integer, Long> mStatusMap = (Map<Integer, Long>) monthEntry.get("statusDurationMap");
                for (int sk = 0; sk <= 3; sk++) {
                    if (dayStatusDuration.get(sk) > 0) mStatusMap.merge(sk, dayStatusDuration.get(sk), Long::sum);
                }
            }
        }
        if ("3".equals(type) && monthAggMap != null) {
            for (Map.Entry<String, Map<String, Object>> me : monthAggMap.entrySet()) {
                Map<String, Object> m = me.getValue();
                long mDur = (Long) m.get("totalDurationSeconds");
                @SuppressWarnings("unchecked") Map<Integer, Long> mStatusMap = (Map<Integer, Long>) m.get("statusDurationMap");
                Map<String, Object> entry = new LinkedHashMap<>();
                entry.put("date", m.get("date"));
                entry.put("totalKwh", ((BigDecimal) m.get("totalKwh")).setScale(2, RoundingMode.HALF_UP));
                entry.put("totalDurationFormatted", formatDuration(mDur));
                entry.put("totalDurationSeconds", mDur);
                entry.put("statusStats", buildStatusStats(mStatusMap, mDur));
                detailList.add(entry);
            }
        }
        List<Map<String, Object>> allStatusStats = buildStatusStats(statusDurationAllMap, totalDurationAll);
        return Map.of("code", 200, "msg", "请求成功", "dtuSn", dtuSn, "type", type, "detailList", detailList,
                "summary", Map.of("totalDurationFormatted", formatDuration(totalDurationAll), "totalDurationSeconds", totalDurationAll,
                        "totalKwh", totalKwhAll.setScale(2, RoundingMode.HALF_UP), "statusStats", allStatusStats));
    }

    public Map<String, Object> queryEnergyTimelineStatus(String corpCode, String date, Integer pageNo, Integer pageSize) {
        String eRunDtlTableName = corpConfigService.getERunDtlTableName(corpCode);
        String energyTableName = corpConfigService.getEnergyTableName(corpCode);
        String eqKwhTableName = corpConfigService.getEqKwhTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);

        String countSql = "SELECT COUNT(DISTINCT r.dtuSn) FROM " + eRunDtlTableName + " r " +
                "INNER JOIN " + energyTableName + " e ON e.dtuSn = r.dtuSn AND e.corp_code = r.corp_code WHERE r.corp_code = ? AND r.use_date = ?";
        Long total = jt.queryForObject(countSql, Long.class, corpCode, date + " 00:00:00");

        int offset = (pageNo - 1) * pageSize;
        String querySql = "SELECT r.dtuSn, e.deviceName, r.runStatus1, r.runStatus2 FROM " + eRunDtlTableName + " r " +
                "INNER JOIN " + energyTableName + " e ON e.dtuSn = r.dtuSn AND e.corp_code = r.corp_code WHERE r.corp_code = ? AND r.use_date = ? " +
                "ORDER BY e.deviceName ASC LIMIT ?, ?";
        List<Map<String, Object>> rows = jt.queryForList(querySql, corpCode, date + " 00:00:00", offset, pageSize);

        List<Map<String, Object>> list = new ArrayList<>();
        for (Map<String, Object> row : rows) {
            Map<String, Object> item = new LinkedHashMap<>();
            item.put("dtuSn", row.get("dtuSn"));
            item.put("deviceName", row.get("deviceName"));
            Object oeeRawData = Collections.emptyList();
            long totalDuration = 0;
            long runDuration = 0;
            String rs1 = row.get("runStatus1") != null ? String.valueOf(row.get("runStatus1")) : "";
            String rs2 = row.get("runStatus2") != null ? String.valueOf(row.get("runStatus2")) : "";
            if (StringUtils.hasText(rs1) || StringUtils.hasText(rs2)) {
                String jsonStr = rs1 + rs2;
                try {
                    JSONArray dataArray = JSON.parseArray(jsonStr);
                    oeeRawData = dataArray;
                    for (int i = 0; i < dataArray.size(); i++) {
                        JSONObject jo = dataArray.getJSONObject(i);
                        Long duration = jo.getLong("duration");
                        Integer runStatus = jo.getInteger("runStatus");
                        if (duration != null && duration > 0 && runStatus != null) {
                            totalDuration += duration;
                            if (runStatus == 3) runDuration += duration;
                        }
                    }
                } catch (Exception ignored) {
                }
            }
            item.put("timelineList", oeeRawData);
            item.put("totalDurationFormatted", formatDuration(totalDuration));
            item.put("totalDurationSeconds", totalDuration);

            BigDecimal totalKwh = BigDecimal.ZERO;
            try {
                String kwhSql = "SELECT description FROM " + eqKwhTableName + " WHERE corp_code = ? AND dtuSn = ? AND use_date = ?";
                Map<String, Object> kwhRow = jt.queryForMap(kwhSql, corpCode, row.get("dtuSn"), date + " 00:00:00");
                if (kwhRow != null && kwhRow.get("description") != null) {
                    JSONArray dataArray = JSON.parseArray(String.valueOf(kwhRow.get("description")));
                    for (int i = 0; i < dataArray.size(); i++) {
                        Double value = dataArray.getJSONObject(i).getDouble("value");
                        if (value != null) totalKwh = totalKwh.add(BigDecimal.valueOf(value));
                    }
                }
            } catch (Exception ignored) {
            }
            item.put("totalKwh", totalKwh.setScale(2, RoundingMode.HALF_UP));
            double utilizationRate = totalDuration > 0 ? BigDecimal.valueOf(runDuration * 100.0 / totalDuration).setScale(2, RoundingMode.HALF_UP).doubleValue() : 0.0;
            item.put("utilizationRate", utilizationRate);
            list.add(item);
        }
        return Map.of("code", 200, "msg", "请求成功", "total", total != null ? total : 0, "pageNo", pageNo, "pageSize", pageSize, "list", list);
    }

    private static Map<Integer, Long> initStatusMap() {
        Map<Integer, Long> map = new LinkedHashMap<>();
        map.put(0, 0L);
        map.put(1, 0L);
        map.put(2, 0L);
        map.put(3, 0L);
        return map;
    }

    private static List<Map<String, Object>> buildStatusStats(Map<Integer, Long> statusMap, long totalDur) {
        List<Map<String, Object>> list = new ArrayList<>();
        for (Map.Entry<Integer, Long> entry : statusMap.entrySet()) {
            long dur = entry.getValue();
            double percent = totalDur > 0 ? BigDecimal.valueOf(dur * 100.0 / totalDur).setScale(2, RoundingMode.HALF_UP).doubleValue() : 0.0;
            Map<String, Object> stat = new LinkedHashMap<>();
            stat.put("status", entry.getKey());
            stat.put("durationSeconds", dur);
            stat.put("durationFormatted", formatDuration(dur));
            stat.put("percent", percent);
            list.add(stat);
        }
        return list;
    }

    private static Object itemToMap(JSONArray dataArray) {
        List<Object> list = new ArrayList<>();
        for (int i = 0; i < dataArray.size(); i++) list.add(dataArray.getJSONObject(i));
        return list;
    }

    private static String formatDuration(long totalSeconds) {
        long h = totalSeconds / 3600;
        long m = (totalSeconds % 3600) / 60;
        long s = totalSeconds % 60;
        StringBuilder sb = new StringBuilder();
        if (h > 0) sb.append(h).append("时");
        if (h > 0 || m > 0) sb.append(m).append("分");
        sb.append(s).append("秒");
        return sb.toString();
    }

    private static String formatDurationToHours(long totalSeconds) {
        double hours = totalSeconds / 3600.0;
        return String.format("%.2f时", hours);
    }

    // ==================== eq_kwh 综合统计 ====================

    public Map<String, Object> queryEqKwhStatistics(String corpCode, String startDate, String endDate) {
        log.info("========== [eq_kwh综合统计] corpCode={}, startDate={}, endDate={} ==========", corpCode, startDate, endDate);
        if (!StringUtils.hasText(startDate) || !StringUtils.hasText(endDate))
            return Map.of("code", 400, "msg", "参数错误: startDate和endDate必填", "data", Map.of());

        List<String> dateList = buildDateList(startDate, endDate);
        if (dateList.isEmpty()) return buildEmptyEqKwhStats();
        log.info("日期范围共 {} 天: {} ~ {}", dateList.size(), dateList.get(0), dateList.get(dateList.size() - 1));

        Map<String, String> deviceNameMap = queryAllEnergyDeviceNames(corpCode);
        List<Map<String, Object>> allRawData = queryEqKwhBatch(corpCode, dateList);
        log.info("eq_kwh 批量查询返回 {} 条原始记录", allRawData.size());

        Map<String, DeviceStatResult> deviceStatMap = aggregateByDevice(allRawData, deviceNameMap);
        for (String sn : deviceNameMap.keySet()) {
            if (!deviceStatMap.containsKey(sn))
                deviceStatMap.put(sn, new DeviceStatResult(sn, deviceNameMap.getOrDefault(sn, "")));
        }
        return buildEqKwhStatisticsResult(corpCode, deviceStatMap, deviceNameMap);
    }

    static class DeviceStatResult {
        String dtuSn;
        String deviceName;
        long status0, status1, status2, status3;
        double totalKwh;

        DeviceStatResult(String dtuSn, String deviceName) {
            this.dtuSn = dtuSn;
            this.deviceName = deviceName;
        }

        long totalDuration() {
            return status0 + status1 + status2 + status3;
        }

        long abnormalDuration() {
            return status1 + status2;
        }

        long activeDuration() {
            return status1 + status2 + status3;
        }
    }

    private Map<String, Object> buildEmptyEqKwhStats() {
        return Map.of("code", 200, "msg", "请求成功", "data", Map.of(
                "summary", Map.of("availabilityRate", "0.00%", "totalStatusDuration",
                        Map.of("status0", Map.of("durationFormatted", "0时0分0秒", "durationSeconds", 0L, "durationHours", "0.00时"),
                                "status1", Map.of("durationFormatted", "0时0分0秒", "durationSeconds", 0L, "durationHours", "0.00时"),
                                "status2", Map.of("durationFormatted", "0时0分0秒", "durationSeconds", 0L, "durationHours", "0.00时"),
                                "status3", Map.of("durationFormatted", "0时0分0秒", "durationSeconds", 0L, "durationHours", "0.00时")),
                        "currentStatus", Map.of("0", 0, "1", 0, "2", 0, "3", 0),
                        "abnormalRanking", List.of(), "deviceList", List.of())));
    }

    private Map<String, String> queryAllEnergyDeviceNames(String corpCode) {
        String energyTableName = corpConfigService.getEnergyTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);
        String sql = "SELECT dtuSn, deviceName FROM " + energyTableName + " WHERE corp_code = ? ORDER BY dtuSn";
        List<Map<String, Object>> rows = jt.queryForList(sql, corpCode);
        Map<String, String> result = new LinkedHashMap<>(rows.size());
        for (Map<String, Object> row : rows) result.put((String) row.get("dtuSn"), (String) row.get("deviceName"));
        return result;
    }

    private List<String> buildDateList(String startDate, String endDate) {
        List<String> result = new ArrayList<>();
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Calendar cur = Calendar.getInstance();
            cur.setTime(sdf.parse(startDate));
            Calendar endCal = Calendar.getInstance();
            endCal.setTime(sdf.parse(endDate));
            while (!cur.after(endCal)) {
                result.add(sdf.format(cur.getTime()));
                cur.add(Calendar.DAY_OF_MONTH, 1);
            }
        } catch (Exception e) {
            log.error("日期解析失败: {} ~ {}", startDate, endDate, e);
        }
        return result;
    }

    private List<Map<String, Object>> queryEqKwhBatch(String corpCode, List<String> dateList) {
        if (dateList.isEmpty()) return Collections.emptyList();
        String eqKwhTableName = corpConfigService.getEqKwhTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);
        StringBuilder sql = new StringBuilder("SELECT dtuSn, use_date, description FROM " + eqKwhTableName + " WHERE corp_code = ? AND use_date IN (");
        List<Object> params = new ArrayList<>();
        params.add(corpCode);
        for (String d : dateList) {
            sql.append("?,");
            params.add(d + "T00:00:00");
        }
        sql.deleteCharAt(sql.length() - 1).append(")").append(" ORDER BY dtuSn, use_date");
        return jt.queryForList(sql.toString(), params.toArray());
    }

    private Map<String, DeviceStatResult> aggregateByDevice(List<Map<String, Object>> rawData, Map<String, String> deviceNameMap) {
        Map<String, DeviceStatResult> resultMap = new LinkedHashMap<>();
        for (Map<String, Object> row : rawData) {
            String sn = (String) row.get("dtuSn");
            String desc = row.get("description") != null ? String.valueOf(row.get("description")) : "";
            DeviceStatResult dsr = resultMap.computeIfAbsent(sn, k -> new DeviceStatResult(k, deviceNameMap.getOrDefault(k, "")));
            if (!StringUtils.hasText(desc)) continue;
            try {
                JSONArray dataArray = JSON.parseArray(desc);
                if (dataArray == null) continue;
                for (int i = 0; i < dataArray.size(); i++) {
                    JSONObject item = dataArray.getJSONObject(i);
                    if (item == null) continue;
                    Double value = item.getDouble("value");
                    if (value != null) dsr.totalKwh += value;
                    for (int sk = 0; sk <= 3; sk++) {
                        Long dur = item.getLong(String.valueOf(sk));
                        if (dur != null && dur > 0) switch (sk) {
                            case 0 -> dsr.status0 += dur;
                            case 1 -> dsr.status1 += dur;
                            case 2 -> dsr.status2 += dur;
                            case 3 -> dsr.status3 += dur;
                        }
                    }
                }
            } catch (Exception e) {
                log.warn("解析eq_kwh description异常 - dtuSn:{}", sn, e);
            }
        }
        return resultMap;
    }

    private Map<String, Object> buildEqKwhStatisticsResult(String corpCode, Map<String, DeviceStatResult> deviceStatMap, Map<String, String> deviceNameMap) {
        long totalS0 = 0, totalS1 = 0, totalS2 = 0, totalS3 = 0;
        double totalKwhSum = 0;
        List<Map<String, Object>> deviceList = new ArrayList<>(deviceStatMap.size());
        for (Map.Entry<String, DeviceStatResult> entry : deviceStatMap.entrySet()) {
            DeviceStatResult dsr = entry.getValue();
            totalS0 += dsr.status0;
            totalS1 += dsr.status1;
            totalS2 += dsr.status2;
            totalS3 += dsr.status3;
            totalKwhSum += dsr.totalKwh;
            long active = dsr.activeDuration();
            double devRate = active > 0 ? Math.round(dsr.status3 * 10000.0 / active) / 100.0 : 0.0;
            Map<String, Object> devItem = new LinkedHashMap<>();
            devItem.put("dtuSn", dsr.dtuSn);
            devItem.put("deviceName", dsr.deviceName);
            devItem.put("status0", Map.of("durationFormatted", formatDuration(dsr.status0), "durationSeconds", dsr.status0, "durationHours", formatDurationToHours(dsr.status0)));
            devItem.put("status1", Map.of("durationFormatted", formatDuration(dsr.status1), "durationSeconds", dsr.status1, "durationHours", formatDurationToHours(dsr.status1)));
            devItem.put("status2", Map.of("durationFormatted", formatDuration(dsr.status2), "durationSeconds", dsr.status2, "durationHours", formatDurationToHours(dsr.status2)));
            devItem.put("status3", Map.of("durationFormatted", formatDuration(dsr.status3), "durationSeconds", dsr.status3, "durationHours", formatDurationToHours(dsr.status3)));
            devItem.put("totalDurationFormatted", formatDuration(dsr.totalDuration()));
            devItem.put("totalDurationSeconds", dsr.totalDuration());
            devItem.put("totalDurationHours", formatDurationToHours(dsr.totalDuration()));
            devItem.put("totalKwh", Math.round(dsr.totalKwh * 100.0) / 100.0);
            devItem.put("availabilityRate", String.format("%.2f%%", devRate));
            devItem.put("_abnormalDur", dsr.abnormalDuration());
            deviceList.add(devItem);
        }
        long totalActive = totalS1 + totalS2 + totalS3;
        double overallAvailabilityRate = totalActive > 0 ? Math.round(totalS3 * 10000.0 / totalActive) / 100.0 : 0.0;
        Map<String, Integer> currentStatus = queryCurrentRunStatus(corpCode);
        deviceList.sort((a, b) -> Long.compare(((Number) b.get("_abnormalDur")).longValue(), ((Number) a.get("_abnormalDur")).longValue()));
        for (Map<String, Object> d : deviceList) d.remove("_abnormalDur");
        return Map.of("code", 200, "msg", "请求成功", "data", Map.of(
                "summary", Map.of("availabilityRate", String.format("%.2f%%", overallAvailabilityRate), "availabilityRateValue", overallAvailabilityRate,
                        "totalDevices", deviceNameMap.size(), "totalKwh", Math.round(totalKwhSum * 100.0) / 100.0,
                        "totalStatusDuration", Map.of("status0", Map.of("durationFormatted", formatDuration(totalS0), "durationSeconds", totalS0, "durationHours", formatDurationToHours(totalS0)),
                                "status1", Map.of("durationFormatted", formatDuration(totalS1), "durationSeconds", totalS1, "durationHours", formatDurationToHours(totalS1)),
                                "status2", Map.of("durationFormatted", formatDuration(totalS2), "durationSeconds", totalS2, "durationHours", formatDurationToHours(totalS2)),
                                "status3", Map.of("durationFormatted", formatDuration(totalS3), "durationSeconds", totalS3, "durationHours", formatDurationToHours(totalS3))),
                        "currentStatus", currentStatus, "abnormalRanking", deviceList, "deviceList", deviceList)));
    }

    private Map<String, Integer> queryCurrentRunStatus(String corpCode) {
        String energyTableName = corpConfigService.getEnergyTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);
        String sql = "SELECT runStatus, COUNT(*) as cnt FROM " + energyTableName + " WHERE corp_code = ? GROUP BY runStatus";
        List<Map<String, Object>> rows = jt.queryForList(sql, corpCode);
        int s0 = 0, s1 = 0, s2 = 0, s3 = 0;
        for (Map<String, Object> row : rows) {
            String key = String.valueOf(row.get("runStatus"));
            int cnt = ((Number) row.get("cnt")).intValue();
            switch (key) {
                case "0" -> s0 = cnt;
                case "1" -> s1 = cnt;
                case "2" -> s2 = cnt;
                case "3" -> s3 = cnt;
            }
        }
        return Map.of("0", s0, "1", s1, "2", s2, "3", s3);
    }

    // ==================== eq_kwh 多设备查询 ====================

    public Map<String, Object> queryEqKwhByType(String corpCode, String type, String startDate, String endDate) {
        log.info("========== [eq_kwh多设备查询] corpCode={}, type={}, startDate={}, endDate={} ==========", corpCode, type, startDate, endDate);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List<String> dateList = new ArrayList<>();
        String actualStart;
        String actualEnd;
        if ("1".equals(type)) {
            if (!StringUtils.hasText(startDate)) return Map.of("code", 400, "msg", "参数错误: type=1时startDate必填");
            dateList.add(startDate);
            actualStart = startDate;
            actualEnd = startDate;
        } else if ("2".equals(type)) {
            if (!StringUtils.hasText(startDate)) return Map.of("code", 400, "msg", "参数错误: type=2时startDate必填");
            if (StringUtils.hasText(endDate)) {
                try {
                    Date start = sdf.parse(startDate);
                    Date end = sdf.parse(endDate);
                    Calendar cur = Calendar.getInstance();
                    cur.setTime(start);
                    while (!cur.getTime().after(end)) {
                        dateList.add(sdf.format(cur.getTime()));
                        cur.add(Calendar.DAY_OF_MONTH, 1);
                    }
                    actualStart = startDate;
                    actualEnd = endDate;
                } catch (Exception e) {
                    return Map.of("code", 400, "msg", "日期格式错误: " + startDate + " ~ " + endDate);
                }
            } else {
                try {
                    Date startD = sdf.parse(startDate);
                    Calendar cal = Calendar.getInstance();
                    cal.setTime(startD);
                    cal.set(Calendar.DAY_OF_MONTH, 1);
                    actualStart = sdf.format(cal.getTime());
                    cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH));
                    actualEnd = sdf.format(cal.getTime());
                    Calendar cur = Calendar.getInstance();
                    cur.setTime(sdf.parse(actualStart));
                    while (!cur.after(cal)) {
                        dateList.add(sdf.format(cur.getTime()));
                        cur.add(Calendar.DAY_OF_MONTH, 1);
                    }
                } catch (Exception e) {
                    return Map.of("code", 400, "msg", "日期格式错误: " + startDate);
                }
            }
        } else if ("3".equals(type)) {
            if (StringUtils.hasText(startDate) && StringUtils.hasText(endDate)) {
                try {
                    Date start = sdf.parse(startDate);
                    Date end = sdf.parse(endDate);
                    actualStart = startDate;
                    actualEnd = endDate;
                    Calendar cur = Calendar.getInstance();
                    cur.setTime(start);
                    while (!cur.getTime().after(end)) {
                        dateList.add(sdf.format(cur.getTime()));
                        cur.add(Calendar.DAY_OF_MONTH, 1);
                    }
                } catch (Exception e) {
                    return Map.of("code", 400, "msg", "日期格式错误: " + startDate + " ~ " + endDate);
                }
            } else {
                Calendar now = Calendar.getInstance();
                int year = now.get(Calendar.YEAR);
                Calendar startCal = Calendar.getInstance();
                startCal.set(year, Calendar.JANUARY, 1);
                actualStart = sdf.format(startCal.getTime());
                actualEnd = sdf.format(now.getTime());
                Calendar cur = Calendar.getInstance();
                cur.setTime(startCal.getTime());
                while (!cur.getTime().after(now.getTime())) {
                    dateList.add(sdf.format(cur.getTime()));
                    cur.add(Calendar.DAY_OF_MONTH, 1);
                }
            }
        } else {
            return Map.of("code", 400, "msg", "参数错误: type只支持1/2/3");
        }
        log.info("实际日期范围: {} ~ {}, 共{}天", actualStart, actualEnd, dateList.size());
        Map<String, String> deviceNameMap = queryAllEnergyDeviceNames(corpCode);
        List<Map<String, Object>> allRawData = queryEqKwhBatch(corpCode, dateList);
        return buildEqKwhByTypeResult(corpCode, type, dateList, allRawData, deviceNameMap, actualStart, actualEnd);
    }

    private Map<String, Object> buildEqKwhByTypeResult(String corpCode, String type, List<String> dateList, List<Map<String, Object>> allRawData, Map<String, String> deviceNameMap, String actualStart, String actualEnd) {
        Map<String, Object> rawDataMap = new LinkedHashMap<>();
        for (Map<String, Object> row : allRawData) {
            String sn = (String) row.get("dtuSn");
            String ud = String.valueOf(row.get("use_date"));
            String desc = row.get("description") != null ? String.valueOf(row.get("description")) : "";
            if (StringUtils.hasText(desc)) {
                try {
                    String dateKey = ud.length() > 10 ? ud.substring(0, 10) : ud;
                    rawDataMap.put(sn + "|" + dateKey, JSON.parseArray(desc));
                } catch (Exception ignored) {
                }
            }
        }
        if ("1".equals(type)) return buildType1Result(dateList.get(0), rawDataMap, deviceNameMap);
        if ("2".equals(type)) return buildType2Result(dateList, rawDataMap, deviceNameMap, actualStart, actualEnd);
        if ("3".equals(type)) return buildType3Result(dateList, rawDataMap, deviceNameMap, actualStart, actualEnd);
        return Map.of("code", 500, "msg", "未知type");
    }

    private Map<String, Object> buildType1Result(String dateStr, Map<String, Object> rawDataMap, Map<String, String> deviceNameMap) {
        List<Map<String, Object>> list = new ArrayList<>();
        BigDecimal totalKwhAll = BigDecimal.ZERO;
        for (String sn : deviceNameMap.keySet()) {
            String mapKey = sn + "|" + dateStr;
            Object rawObj = rawDataMap.getOrDefault(mapKey, Collections.emptyList());
            @SuppressWarnings("unchecked") JSONArray dataArray = rawObj instanceof JSONArray ? (JSONArray) rawObj : new JSONArray();
            BigDecimal dayKwh = BigDecimal.ZERO;
            for (int i = 0; i < dataArray.size(); i++) {
                JSONObject item = dataArray.getJSONObject(i);
                if (item != null && item.getDouble("value") != null)
                    dayKwh = dayKwh.add(BigDecimal.valueOf(item.getDouble("value")));
            }
            totalKwhAll = totalKwhAll.add(dayKwh);
            Map<String, Object> item = new LinkedHashMap<>();
            item.put("dtuSn", sn);
            item.put("deviceName", deviceNameMap.getOrDefault(sn, ""));
            item.put("date", dateStr);
            item.put("kwhList", dataArray);
            item.put("totalKwh", dayKwh.setScale(2, RoundingMode.HALF_UP));
            list.add(item);
        }
        return Map.of("code", 200, "msg", "请求成功", "type", "1", "date", dateStr, "totalDevices", deviceNameMap.size(), "totalKwh", totalKwhAll.setScale(2, RoundingMode.HALF_UP), "list", list);
    }

    private Map<String, Object> buildType2Result(List<String> dateList, Map<String, Object> rawDataMap, Map<String, String> deviceNameMap, String actualStart, String actualEnd) {
        BigDecimal grandTotalKwh = BigDecimal.ZERO;
        List<Map<String, Object>> deviceList = new ArrayList<>(deviceNameMap.size());
        for (String sn : deviceNameMap.keySet()) {
            Map<String, Object> devItem = new LinkedHashMap<>();
            devItem.put("dtuSn", sn);
            devItem.put("deviceName", deviceNameMap.getOrDefault(sn, ""));
            List<Map<String, Object>> dailyDataList = new ArrayList<>(dateList.size());
            BigDecimal devTotalKwh = BigDecimal.ZERO;
            long devTotalDur = 0;
            Map<Integer, Long> devStatusAll = initStatusMap();
            for (String d : dateList) {
                String mapKey = sn + "|" + d;
                Object rawObj = rawDataMap.getOrDefault(mapKey, Collections.emptyList());
                @SuppressWarnings("unchecked") JSONArray dataArray = rawObj instanceof JSONArray ? (JSONArray) rawObj : new JSONArray();
                BigDecimal dayKwh = BigDecimal.ZERO;
                long dayDur = 0;
                Map<Integer, Long> dayStatus = initStatusMap();
                for (int i = 0; i < dataArray.size(); i++) {
                    JSONObject item = dataArray.getJSONObject(i);
                    if (item == null) continue;
                    Double value = item.getDouble("value");
                    if (value != null) dayKwh = dayKwh.add(BigDecimal.valueOf(value));
                    for (int sk = 0; sk <= 3; sk++) {
                        Long dur = item.getLong(String.valueOf(sk));
                        if (dur != null && dur > 0) {
                            dayDur += dur;
                            devTotalDur += dur;
                            dayStatus.merge(sk, dur, Long::sum);
                            devStatusAll.merge(sk, dur, Long::sum);
                        }
                    }
                }
                devTotalKwh = devTotalKwh.add(dayKwh);
                grandTotalKwh = grandTotalKwh.add(devTotalKwh);
                Map<String, Object> dayEntry = new LinkedHashMap<>();
                dayEntry.put("date", d);
                dayEntry.put("totalKwh", dayKwh.setScale(2, RoundingMode.HALF_UP));
                dayEntry.put("totalDurationFormatted", formatDuration(dayDur));
                dayEntry.put("totalDurationSeconds", dayDur);
                dayEntry.put("statusStats", buildStatusStats(dayStatus, dayDur));
                dailyDataList.add(dayEntry);
            }
            devItem.put("totalKwh", devTotalKwh.setScale(2, RoundingMode.HALF_UP));
            devItem.put("totalDurationFormatted", formatDuration(devTotalDur));
            devItem.put("totalDurationSeconds", devTotalDur);
            devItem.put("statusStats", buildStatusStats(devStatusAll, devTotalDur));
            devItem.put("dailyData", dailyDataList);
            deviceList.add(devItem);
        }
        return Map.of("code", 200, "msg", "请求成功", "type", "2", "actualStartDate", actualStart, "actualEndDate", actualEnd,
                "totalDays", dateList.size(), "totalDevices", deviceList.size(), "grandTotalKwh", grandTotalKwh.setScale(2, RoundingMode.HALF_UP), "list", deviceList);
    }

    private Map<String, Object> buildType3Result(List<String> dateList, Map<String, Object> rawDataMap, Map<String, String> deviceNameMap, String actualStart, String actualEnd) {
        BigDecimal grandTotalKwh = BigDecimal.ZERO;
        List<Map<String, Object>> deviceList = new ArrayList<>(deviceNameMap.size());
        for (String sn : deviceNameMap.keySet()) {
            Map<String, Object> devItem = new LinkedHashMap<>();
            devItem.put("dtuSn", sn);
            devItem.put("deviceName", deviceNameMap.getOrDefault(sn, ""));
            Map<String, Map<String, Object>> monthAgg = new LinkedHashMap<>();
            for (String d : dateList) {
                String monthKey = d.substring(0, 7);
                monthAgg.computeIfAbsent(monthKey, k -> {
                    Map<String, Object> m = new LinkedHashMap<>();
                    m.put("month", k);
                    m.put("label", Integer.parseInt(k.substring(5)) + "月");
                    m.put("totalKwh", BigDecimal.ZERO);
                    m.put("totalDurationSeconds", 0L);
                    m.put("statusDurationMap", initStatusMap());
                    return m;
                });
                String mapKey = sn + "|" + d;
                Object rawObj = rawDataMap.getOrDefault(mapKey, Collections.emptyList());
                @SuppressWarnings("unchecked")
                JSONArray dataArray = rawObj instanceof JSONArray ? (JSONArray) rawObj : new JSONArray();

                Map<String, Object> mEntry = monthAgg.get(monthKey);
                for (int i = 0; i < dataArray.size(); i++) {
                    JSONObject item = dataArray.getJSONObject(i);
                    if (item == null) continue;
                    Double value = item.getDouble("value");
                    if (value != null) {
                        BigDecimal valBd = BigDecimal.valueOf(value);
                        mEntry.put("totalKwh", ((BigDecimal) mEntry.get("totalKwh")).add(valBd));
                    }

                    for (int sk = 0; sk <= 3; sk++) {
                        Long dur = item.getLong(String.valueOf(sk));
                        if (dur != null && dur > 0) {
                            mEntry.put("totalDurationSeconds", (Long) mEntry.get("totalDurationSeconds") + dur);
                            @SuppressWarnings("unchecked")
                            Map<Integer, Long> sMap = (Map<Integer, Long>) mEntry.get("statusDurationMap");
                            sMap.merge(sk, dur, Long::sum);
                        }
                    }
                }
            }

            // 构建该设备的月度数据列表
            List<Map<String, Object>> monthlyDataList = new ArrayList<>(monthAgg.size());
            BigDecimal devTotalKwh = BigDecimal.ZERO;
            long devTotalDur = 0;
            Map<Integer, Long> devStatusAll = initStatusMap();
            for (Map.Entry<String, Map<String, Object>> me : monthAgg.entrySet()) {
                Map<String, Object> m = me.getValue();
                long mDur = (Long) m.get("totalDurationSeconds");
                BigDecimal mKwh = (BigDecimal) m.get("totalKwh");
                @SuppressWarnings("unchecked") Map<Integer, Long> mStatus = (Map<Integer, Long>) m.get("statusDurationMap");
                devTotalKwh = devTotalKwh.add(mKwh);
                devTotalDur += mDur;
                for (int sk = 0; sk <= 3; sk++) {
                    long sd = mStatus.getOrDefault(sk, 0L);
                    if (sd > 0) devStatusAll.merge(sk, sd, Long::sum);
                }
                Map<String, Object> monthEntry = new LinkedHashMap<>();
                monthEntry.put("month", m.get("month"));
                monthEntry.put("label", m.get("label"));
                monthEntry.put("totalKwh", mKwh.setScale(2, RoundingMode.HALF_UP));
                monthEntry.put("totalDurationFormatted", formatDuration(mDur));
                monthEntry.put("totalDurationSeconds", mDur);
                monthEntry.put("statusStats", buildStatusStats(mStatus, mDur));
                monthlyDataList.add(monthEntry);
            }
            grandTotalKwh = grandTotalKwh.add(devTotalKwh);
            devItem.put("totalKwh", devTotalKwh.setScale(2, RoundingMode.HALF_UP));
            devItem.put("totalDurationFormatted", formatDuration(devTotalDur));
            devItem.put("totalDurationSeconds", devTotalDur);
            devItem.put("statusStats", buildStatusStats(devStatusAll, devTotalDur));
            devItem.put("monthlyData", monthlyDataList);
            deviceList.add(devItem);
        }
        return Map.of("code", 200, "msg", "请求成功", "type", "3", "year", actualStart.substring(0, 4), "actualStartDate", actualStart,
                "actualEndDate", actualEnd, "totalMonths", dateList.isEmpty() ? 0 : (dateList.stream().map(d -> d.substring(0, 7)).distinct().toList()).size(),
                "totalDevices", deviceList.size(), "grandTotalKwh", grandTotalKwh.setScale(2, RoundingMode.HALF_UP), "list", deviceList);
    }

    /**
     * 调试接口
     */
    public Map<String, Object> debugEqKwhInfo(String corpCode) {
        String eqKwhTableName = corpConfigService.getEqKwhTableName(corpCode);
        String energyTableName = corpConfigService.getEnergyTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);
        Long totalCount = jt.queryForObject("SELECT COUNT(*) FROM " + eqKwhTableName + " WHERE corp_code = ?", Long.class, corpCode);
        List<Map<String, Object>> dateStats = jt.queryForList("SELECT use_date, COUNT(*) as cnt FROM " + eqKwhTableName + " WHERE corp_code = ? GROUP BY use_date ORDER BY use_date DESC LIMIT 20", corpCode);
        List<Map<String, Object>> deviceStats = jt.queryForList("SELECT dtuSn, COUNT(*) as cnt FROM " + eqKwhTableName + " WHERE corp_code = ? GROUP BY dtuSn ORDER BY dtuSn", corpCode);
        Map<String, Object> lastRecord = null;
        try {
            String sql = "SELECT dtuSn, use_date, LEFT(description, 500) AS desc_preview, LENGTH(description) as desc_len FROM " + eqKwhTableName + " WHERE corp_code = ? ORDER BY use_date DESC LIMIT 1";
            lastRecord = jt.queryForMap(sql, corpCode);
        } catch (Exception ignored) {
        }
        Long deviceCount = jt.queryForObject("SELECT COUNT(*) FROM " + energyTableName + " WHERE corp_code = ?", Long.class, corpCode);
        return Map.of("code", 200, "msg", "请求成功", "tableName", eqKwhTableName, "corpCode", corpCode, "totalRecords", totalCount != null ? totalCount : 0,
                "deviceCountInEnergyTable", deviceCount != null ? deviceCount : 0, "latestDateRecords", dateStats, "perDeviceRecordCount", deviceStats, "lastRecordPreview", lastRecord);
    }
}