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.beans.factory.annotation.Value;
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 {

    @Value("${energy.db.corpCode}")
    private String energyCorpCode;
    @Value("${energy.db.tableName}")
    private String energyTableName;
    @Value("${energy.db.eqKwhTableName}")
    private String eqKwhTableName;
    @Value("${energy.db.eRunDtlTableName}")
    private String eRunDtlTableName;

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 分页查询能耗设备信息
     * 查询表 t_auto_ymk_iot_energy，按设备名称排序
     *
     * @param deviceName 设备名称（模糊匹配）
     * @param runStatus  状态（0：离线，1：停机，2：待机，3：运行）
     * @param pageNo     页码，默认1
     * @param pageSize   每页条数，默认10
     */
    public Map<String, Object> queryEnergyList(String deviceName, String runStatus,
                                               Integer pageNo, Integer pageSize) {
        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(energyCorpCode);

        // 设备名称模糊查询
        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 = jdbcTemplate.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 = jdbcTemplate.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
        );
    }

    /**
     * 统计能耗设备各runStatus数量及总数量
     * runStatus: 0-离线, 1-停机, 2-待机, 3-运行
     */
    public Map<String, Object> queryEnergyStats() {
        String sql = "SELECT runStatus, COUNT(*) AS cnt FROM " + energyTableName
                + " WHERE corp_code = ? GROUP BY runStatus";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, energyCorpCode);

        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")
        );
    }

    /**
     * 根据dtuSn查询指定日期的设备时用电量和OEE时序
     * 1. 时用电量：从t_auto_ymk_iot_eq_kwh表获取，并计算当日总用电量
     * 2. OEE时序：从t_auto_ymk_iot_e_run_dtl表获取，统计总时长、各状态运行时长和占比
     *
     * @param dtuSn 设备序列号
     * @param date  查询日期 yyyy-MM-dd
     */
    public Map<String, Object> queryEnergyDetailByDate(String dtuSn, String date) {
        // 1. 查询时用电量数据 - 原始数据直接返回
        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 = jdbcTemplate.queryForMap(kwhSql, energyCorpCode, 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("【能耗明细】查询时用电量数据为空或异常 - dtuSn:{}, date:{}", dtuSn, date);
        }

        // 2. 查询OEE时序数据 - 原始数据直接返回
        Object oeeRawData = Collections.emptyList();
        long totalDuration = 0;
        // runStatus状态: 0-离线, 1-停机, 2-待机, 3-运行
        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 = jdbcTemplate.queryForMap(oeeSql, energyCorpCode, 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时序数据为空或异常 - dtuSn:{}, date:{}", dtuSn, date);
        }

        // 3. 构建OEE统计结果（含格式化时长和占比）
        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
                )
        );
    }

    /**
     * 根据dtuSn查询指定设备的运行时长明细
     * 核心数据为设备时用电量(eq_kwh)，其中包含每个状态的运行时长，需要统计
     * type=1(时): 传startDate，获取指定日期的时用电量明细+各状态时长统计
     * type=2(天): 传startDate和endDate，按日统计
     * type=3(月): 查本年年初到现在，按月统计
     *
     * @param dtuSn    设备序列号
     * @param type     类型：1-时，2-天，3-月
     * @param startDate 开始日期 yyyy-MM-dd (type=1,2必填)
     * @param endDate   结束日期 yyyy-MM-dd (type=2必填)
     */
    public Map<String, Object> queryEnergyRuntimeDetail(String dtuSn, String type,
                                                        String startDate, String endDate) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List<String> dateList = new ArrayList<>();

        // 根据type构建日期列表
        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);
            }
        }

        // 统计汇总数据
        long totalDurationAll = 0;
        BigDecimal totalKwhAll = BigDecimal.ZERO;
        Map<Integer, Long> statusDurationAllMap = initStatusMap();

        // type=1: 直接返回每日明细; type=2/3: 按日期聚合后返回
        List<Map<String, Object>> detailList = new ArrayList<>();
        // type=3 按月聚合用
        Map<String, Map<String, Object>> monthAggMap = "3".equals(type) ? new LinkedHashMap<>() : null;

        for (String dateStr : dateList) {
            // 从eq_kwh表查询时用电量数据
            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 = jdbcTemplate.queryForMap(kwhSql, energyCorpCode, 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));

                        // 从字段 0/1/2/3 统计各状态运行时长 + 格式化新字段
                        for (int statusKey = 0; statusKey <= 3; statusKey++) {
                            Long dur = item.getLong(String.valueOf(statusKey));
                            item.put(statusKey + "Formatted", formatDuration(dur != null ? dur : 0L));
                            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)) {
                // type=1(时): 返回原始kwhList + 当日统计
                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)) {
                // type=2(天): 返回每日汇总统计，不含kwhList
                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)) {
                // type=3(月): 按月聚合
                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);
                    }
                }
            }
        }

        // type=3 构建月度聚合结果
        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
                )
        );
    }

    /**
     * 查询能耗时序状态 - 分页查询
     * 从e_run_dtl(OEE时序)表获取指定日期的设备时序数据，每页12条
     * 同时计算每个设备的稼动率和总用电量(从eq_kwh表)
     *
     * @param date     查询日期 yyyy-MM-dd
     * @param pageNo   页码，默认1
     * @param pageSize 每页条数，默认12
     */
    public Map<String, Object> queryEnergyTimelineStatus(String date, Integer pageNo, Integer pageSize) {
        // 先查OEE时序表获取有数据的设备列表
        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 = jdbcTemplate.queryForObject(countSql, Long.class, energyCorpCode, 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 = jdbcTemplate.queryForList(querySql, energyCorpCode, 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"));

            // OEE时序数据（原始返回）
            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);

            // 从eq_kwh表获取用电量
            BigDecimal totalKwh = BigDecimal.ZERO;
            try {
                String kwhSql = "SELECT description FROM " + eqKwhTableName
                        + " WHERE corp_code = ? AND dtuSn = ? AND use_date = ?";
                Map<String, Object> kwhRow = jdbcTemplate.queryForMap(kwhSql, energyCorpCode, 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;
    }

    /**
     * 格式化时长为 xx时xx分xx秒 格式
     * 时不为0则展示xx时xx分xx秒
     * 时和分都为0则只展示xx秒
     */
    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();
    }

    /**
     * 格式化时长为 xxx.xx 时
     */
    private static String formatDurationToHours(long totalSeconds) {
        double hours = totalSeconds / 3600.0;
        return String.format("%.2f时", hours);
    }

    // ==================== eq_kwh 综合统计查询 ====================

    /**
     * 查询 eq_kwh 表的综合统计数据
     * 返回：
     *   1. 所有设备的总的稼动率 + 每个状态的时间(xxx.xx时)
     *   2. 当前设备的运行状态
     *   3. 异常机台排名(待机+停机时间最长往下排, xx时xx分xx秒)
     *   4. 每个设备统计时间内的总的0/1/2/3的时间(xx时xx分xx秒)
     *
     * @param startDate 开始日期 yyyy-MM-dd
     * @param endDate   结束日期 yyyy-MM-dd
     */
    public Map<String, Object> queryEqKwhStatistics(String startDate, String endDate) {
        log.info("========== [eq_kwh综合统计] startDate={}, endDate={} ==========", startDate, endDate);

        if (!StringUtils.hasText(startDate) || !StringUtils.hasText(endDate)) {
            return Map.of(
                    "code", 400, "msg", "参数错误: startDate和endDate必填",
                    "data", Map.of()
            );
        }

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

        // 2. 获取所有设备列表及名称
        Map<String, String> deviceNameMap = queryAllEnergyDeviceNames();

        // 3. 从 eq_kwh 表批量查询所有设备在指定日期范围内的数据
        List<Map<String, Object>> allRawData = queryEqKwhBatch(dateList);
        log.info("eq_kwh 批量查询返回 {} 条原始记录", allRawData.size());

        // 4. 按 dtuSn 分组聚合数据，计算每个设备的各状态时长
        Map<String, DeviceStatResult> deviceStatMap = aggregateByDevice(allRawData, deviceNameMap);

        // 确保所有设备都在结果中（无数据的设为0）
        for (String sn : deviceNameMap.keySet()) {
            if (!deviceStatMap.containsKey(sn)) {
                deviceStatMap.put(sn, new DeviceStatResult(sn, deviceNameMap.getOrDefault(sn, "")));
            }
        }

        // 5. 计算全局汇总
        return buildEqKwhStatisticsResult(deviceStatMap, deviceNameMap);
    }

    /** 设备统计内部结构 */
    static class DeviceStatResult {
        String dtuSn;
        String deviceName;
        long status0; // 离线时长(秒)
        long status1; // 停机时长(秒)
        long status2; // 待机时长(秒)
        long 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()
                )
        );
    }

    /** 获取能耗设备表所有dtuSn及名称 */
    private Map<String, String> queryAllEnergyDeviceNames() {
        String sql = "SELECT dtuSn, deviceName FROM " + energyTableName + " WHERE corp_code = ? ORDER BY dtuSn";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, energyCorpCode);
        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;
    }

    /** 从 eq_kwh 表批量查询指定日期范围的所有记录 */
    private List<Map<String, Object>> queryEqKwhBatch(List<String> dateList) {
        if (dateList.isEmpty()) return Collections.emptyList();

        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(energyCorpCode);

        for (String d : dateList) { sql.append("?,"); params.add(d + " 00:00:00"); }
        sql.deleteCharAt(sql.length() - 1).append(")");
        sql.append(" ORDER BY dtuSn, use_date");

        return jdbcTemplate.queryForList(sql.toString(), params.toArray());
    }

    /** 按 dtuSn 分组聚合各状态时长 */
    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;

                    // 各状态时长: 0-离线, 1-停机, 2-待机, 3-运行
                    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(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);
            // 各状态时长 - 格式化为 xx时xx分xx秒
            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("availabilityRateValue", 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;

        // ---- ② 当前设备运行状态（从energy表查） ----
        Map<String, Integer> currentStatus = queryCurrentRunStatus();

        // ---- ③ 异常机台排名(按 停机+待机 降序) ----
        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
                )
        );
    }

    /** 查询当前设备运行状态分布(从energy表) runStatus: 0-离线, 1-停机, 2-待机, 3-运行 */
    private Map<String, Integer> queryCurrentRunStatus() {
        String sql = "SELECT runStatus, COUNT(*) as cnt FROM " + energyTableName +
                " WHERE corp_code = ? GROUP BY runStatus";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, energyCorpCode);

        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);
    }
}