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.text.SimpleDateFormat;
import java.util.*;

@Slf4j
@Service
public class DeviceSearchService {

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

    public Map<String, Object> queryDeviceList(String corpCode, String deviceName, String lampState, Integer pageNo, Integer pageSize) {
        String tableName = corpConfigService.getDeviceTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);

        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM " + tableName + " WHERE corp_code = ?");
        StringBuilder querySql = new StringBuilder("SELECT id, deviceName, projectType, projectState, dtuSn, dtuId, deviceId, " +
                "lampState, startTime, duration, utilizationRate FROM " + tableName + " WHERE corp_code = ?");
        List<Object> params = new java.util.ArrayList<>();
        params.add(corpCode);

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

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

        List<Map<String, Object>> list = jt.queryForList(querySql.toString(), params.toArray());

        return Map.of(
                "total", total != null ? total : 0,
                "pageNo", pageNo,
                "pageSize", pageSize,
                "list", list
        );
    }

    public Map<String, Object> queryDeviceStats(String corpCode) {
        String tableName = corpConfigService.getDeviceTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);

        String sql = "SELECT lampState, COUNT(*) as cnt FROM " + tableName +
                " WHERE corp_code = ? GROUP BY lampState";
        List<Map<String, Object>> rows = jt.queryForList(sql, corpCode);

        long all = 0;
        long red = 0, yellow = 0, green = 0, blue = 0, off = 0;
        for (Map<String, Object> row : rows) {
            String state = (String) row.get("lampState");
            long cnt = ((Number) row.get("cnt")).longValue();
            all += cnt;
            switch (state) {
                case "0" -> off = cnt;
                case "1" -> red = cnt;
                case "2" -> yellow = cnt;
                case "3" -> green = cnt;
                case "4" -> blue = cnt;
            }
        }

        return Map.of(
                "all", all,
                "red", red,
                "yellow", yellow,
                "green", green,
                "blue", blue,
                "off", off
        );
    }

    public Map<String, Object> queryLampData(String corpCode, String dtuSn, String date) {
        String result = devicePullService.getLampData(corpCode, dtuSn, date);
        Map<String, Object> res = com.alibaba.fastjson.JSON.parseObject(result, new com.alibaba.fastjson.TypeReference<>() {
        });
        Integer code = (Integer) res.get("code");
        if (code == null || code != 200) {
            return Map.of("lampDurationStats", Map.of(), "list", List.of());
        }

        JSONArray dataList = (JSONArray) res.get("data");
        if (dataList == null || dataList.isEmpty()) {
            return Map.of("lampDurationStats", Map.of(), "list", List.of());
        }

        // 统计各灯状态时长
        long offTotal = 0, redTotal = 0, yellowTotal = 0, greenTotal = 0, blueTotal = 0;
        for (int i = 0; i < dataList.size(); i++) {
            JSONObject item = (JSONObject) dataList.get(i);
            JSONArray lampDataList = item.getJSONArray("lampData");
            if (lampDataList == null) continue;

            for (int j = 0; j < lampDataList.size(); j++) {
                JSONObject lamp = (JSONObject) lampDataList.get(j);
                int state = lamp.getIntValue("lampState");
                long duration = lamp.getLongValue("duration");
                switch (state) {
                    case 0 -> offTotal += duration;
                    case 1 -> redTotal += duration;
                    case 2 -> yellowTotal += duration;
                    case 3 -> greenTotal += duration;
                    case 4 -> blueTotal += duration;
                }
            }
        }

        Map<String, String> stats = new LinkedHashMap<>();
        stats.put("off", formatDuration(offTotal));
        stats.put("red", formatDuration(redTotal));
        stats.put("yellow", formatDuration(yellowTotal));
        stats.put("green", formatDuration(greenTotal));
        stats.put("blue", formatDuration(blueTotal));

        return Map.of(
                "lampDurationStats", stats,
                "list", dataList
        );
    }

    private String formatDuration(long totalSeconds) {
        long hours = totalSeconds / 3600;
        long minutes = (totalSeconds % 3600) / 60;
        long seconds = totalSeconds % 60;

        if (hours > 0) {
            return hours + "时" + minutes + "分" + seconds + "秒";
        }
        if (minutes > 0) {
            return minutes + "分" + seconds + "秒";
        }
        return seconds + "秒";
    }

    /**
     * 稼动率/OEE统计查询
     *
     * @param corpCode  公司编码
     * @param dtuSn     设备序列号（可选，为空查全部）
     * @param type      查询类型：day-日(按日期段), week-周(今年第1周~本周), month-月(今年1月~本月)
     * @param startDate 日模式下的开始日期(yyyy-MM-dd)
     * @param endDate   日模式下的结束日期(yyyy-MM-dd)
     */
    public Map<String, Object> queryOeeStats(String corpCode, String dtuSn, String type, String startDate, String endDate) {
        // 1. 根据类型确定日期范围
        List<String> dates = buildDateRange(type, startDate, endDate);
        log.info("OEE查询 - corpCode:{}, type:{}, dtuSn:{}, 日期范围:{}天, 起始:{}, 结束:{}", corpCode, type, dtuSn, dates.size(), dates.get(0), dates.get(dates.size() - 1));

        // 判断是否包含今天
        String todayStr = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        boolean includeToday = dates.contains(todayStr);

        // 2. 从数据库查询 oee 表数据
        List<Map<String, Object>> dbRecords = queryOeeFromDb(corpCode, dtuSn, dates, includeToday ? todayStr : null);
        log.info("OEE查询 - DB返回记录数:{}, 排除today:{}", dbRecords.size(), includeToday ? todayStr : "无");

        Map<String, JSONObject> dbDataMap = new LinkedHashMap<>();
        for (Map<String, Object> record : dbRecords) {
            String oeeDate = String.valueOf(record.get("oee_date"));
            if (oeeDate.length() > 10) {
                oeeDate = oeeDate.substring(0, 10);
            }
            String lamp1 = (String) record.get("triColorLamp1");
            String lamp2 = (String) record.get("triColorLamp2");
            String fullJson = (lamp1 != null ? lamp1 : "") + (lamp2 != null ? lamp2 : "");
            if (!fullJson.isEmpty()) {
                try {
                    JSONArray arr = JSONArray.parseArray(fullJson);
                    dbDataMap.put(oeeDate, new JSONObject().fluentPut("lampData", arr));
                } catch (Exception e) {
                    log.warn("解析OEE JSON异常 - date:{}, dtuSn:{}", oeeDate, dtuSn, e);
                }
            }
        }

        // 3. 如果包含今天且数据库没有今天的实时数据，则调用接口获取
        if (includeToday && !dbDataMap.containsKey(todayStr)) {
            if (StringUtils.hasText(dtuSn)) {
                String apiResult = devicePullService.getLampData(corpCode, dtuSn, todayStr);
                if (StringUtils.hasText(apiResult)) {
                    Map<String, Object> res = com.alibaba.fastjson.JSON.parseObject(apiResult,
                            new com.alibaba.fastjson.TypeReference<>() {
                            });
                    Integer code = (Integer) res.get("code");
                    if (code != null && code == 200) {
                        JSONArray dataList = (JSONArray) res.get("data");
                        if (dataList != null && !dataList.isEmpty()) {
                            JSONObject dataObj = (JSONObject) dataList.get(0);
                            dbDataMap.put(todayStr, dataObj);
                        }
                    }
                }
            }
        }
        log.info("OEE查询 - 最终有效数据日期数:{}, 日期列表:{}", dbDataMap.size(), dbDataMap.keySet());

        // 4. 根据 type 决定统计粒度
        List<Map<String, Object>> statsList;
        Map<String, Object> summary;

        if ("day".equals(type)) {
            Map<String, Object> result = aggregateByDay(dates, dbDataMap);
            statsList = (List<Map<String, Object>>) result.get("list");
            summary = (Map<String, Object>) result.get("summary");
        } else if ("week".equals(type)) {
            Map<String, Object> result = aggregateByWeek(dates, dbDataMap);
            statsList = (List<Map<String, Object>>) result.get("list");
            summary = (Map<String, Object>) result.get("summary");
        } else {
            Map<String, Object> result = aggregateByMonth(dates, dbDataMap);
            statsList = (List<Map<String, Object>>) result.get("list");
            summary = (Map<String, Object>) result.get("summary");
        }

        return Map.of(
                "summary", summary,
                "list", statsList
        );
    }

    private Map<String, Object> aggregateByDay(List<String> dates, Map<String, JSONObject> dbDataMap) {
        List<Map<String, Object>> dailyStats = new ArrayList<>();
        long totalOffDur = 0, totalRedDur = 0, totalYellowDur = 0, totalGreenDur = 0, totalBlueDur = 0;
        int totalOffCnt = 0, totalRedCnt = 0, totalYellowCnt = 0, totalGreenCnt = 0, totalBlueCnt = 0;

        for (String d : dates) {
            long[] counts = countLampData(dbDataMap.get(d));

            totalOffDur += counts[0];
            totalRedDur += counts[2];
            totalYellowDur += counts[4];
            totalGreenDur += counts[6];
            totalBlueDur += counts[8];
            totalOffCnt += (int) counts[1];
            totalRedCnt += (int) counts[3];
            totalYellowCnt += (int) counts[5];
            totalGreenCnt += (int) counts[7];
            totalBlueCnt += (int) counts[9];

            Map<String, Object> dayStat = new LinkedHashMap<>();
            dayStat.put("label", d);
            dayStat.put("date", d);
            dayStat.put("off", Map.of("duration", formatDuration(counts[0]), "seconds", counts[0], "count", (int) counts[1]));
            dayStat.put("red", Map.of("duration", formatDuration(counts[2]), "seconds", counts[2], "count", (int) counts[3]));
            dayStat.put("yellow", Map.of("duration", formatDuration(counts[4]), "seconds", counts[4], "count", (int) counts[5]));
            dayStat.put("green", Map.of("duration", formatDuration(counts[6]), "seconds", counts[6], "count", (int) counts[7]));
            dayStat.put("blue", Map.of("duration", formatDuration(counts[8]), "seconds", counts[8], "count", (int) counts[9]));
            dailyStats.add(dayStat);
        }

        Map<String, Object> summary = buildSummary(totalOffDur, totalRedDur, totalYellowDur, totalGreenDur, totalBlueDur,
                totalOffCnt, totalRedCnt, totalYellowCnt, totalGreenCnt, totalBlueCnt);

        return Map.of("list", dailyStats, "summary", summary);
    }

    private Map<String, Object> aggregateByWeek(List<String> dates, Map<String, JSONObject> dbDataMap) {
        List<List<String>> weekBuckets = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        String yearStr = dates.get(0).substring(0, 4);
        int year = Integer.parseInt(yearStr);
        Calendar jan1 = Calendar.getInstance();
        jan1.set(year, Calendar.JANUARY, 1);
        int jan1Dow = jan1.get(Calendar.DAY_OF_WEEK);

        Calendar firstMonday = Calendar.getInstance();
        firstMonday.setTime(jan1.getTime());
        int daysToAdd = jan1Dow == Calendar.MONDAY ? 0 : (Calendar.MONDAY - jan1Dow + 7) % 7;
        firstMonday.add(Calendar.DATE, daysToAdd);

        for (String d : dates) {
            try {
                Date date = sdf.parse(d);
                Calendar cal = Calendar.getInstance();
                cal.setTime(date);
                cal.set(Calendar.HOUR_OF_DAY, 0);
                cal.set(Calendar.MINUTE, 0);
                cal.set(Calendar.SECOND, 0);
                cal.set(Calendar.MILLISECOND, 0);

                int bucketIndex;
                if (!cal.after(firstMonday)) {
                    bucketIndex = 0;
                } else {
                    long diffMs = cal.getTimeInMillis() - firstMonday.getTimeInMillis();
                    long diffDays = diffMs / (1000 * 60 * 60 * 24);
                    bucketIndex = 1 + (int) (diffDays / 7);
                }

                while (weekBuckets.size() <= bucketIndex) {
                    weekBuckets.add(new ArrayList<>());
                }
                weekBuckets.get(bucketIndex).add(d);
            } catch (Exception e) {
                log.warn("解析日期失败: {}", d);
            }
        }

        List<Map<String, Object>> weeklyStats = new ArrayList<>();
        long tOffD = 0, tRedD = 0, tYelD = 0, tGrnD = 0, tBluD = 0;
        int tOffC = 0, tRedC = 0, tYelC = 0, tGrnC = 0, tBluC = 0;

        for (int i = 0; i < weekBuckets.size(); i++) {
            List<String> daysInWeek = weekBuckets.get(i);
            if (daysInWeek.isEmpty()) continue;
            long wOffD = 0, wRedD = 0, wYelD = 0, wGrnD = 0, wBluD = 0;
            int wOffC = 0, wRedC = 0, wYelC = 0, wGrnC = 0, wBluC = 0;

            for (String d : daysInWeek) {
                long[] cnt = countLampData(dbDataMap.get(d));
                wOffD += cnt[0];
                wRedD += cnt[2];
                wYelD += cnt[4];
                wGrnD += cnt[6];
                wBluD += cnt[8];
                wOffC += (int) cnt[1];
                wRedC += (int) cnt[3];
                wYelC += (int) cnt[5];
                wGrnC += (int) cnt[7];
                wBluC += (int) cnt[9];
            }

            tOffD += wOffD;
            tRedD += wRedD;
            tYelD += wYelD;
            tGrnD += wGrnD;
            tBluD += wBluD;
            tOffC += wOffC;
            tRedC += wRedC;
            tYelC += wYelC;
            tGrnC += wGrnC;
            tBluC += wBluC;

            Collections.sort(daysInWeek);
            Map<String, Object> ws = new LinkedHashMap<>();
            ws.put("label", (i + 1) + "周");
            ws.put("week", yearStr + "-W" + String.format("%02d", i + 1));
            ws.put("startDate", daysInWeek.get(0));
            ws.put("endDate", daysInWeek.get(daysInWeek.size() - 1));
            ws.put("off", Map.of("duration", formatDuration(wOffD), "seconds", wOffD, "count", wOffC));
            ws.put("red", Map.of("duration", formatDuration(wRedD), "seconds", wRedD, "count", wRedC));
            ws.put("yellow", Map.of("duration", formatDuration(wYelD), "seconds", wYelD, "count", wYelC));
            ws.put("green", Map.of("duration", formatDuration(wGrnD), "seconds", wGrnD, "count", wGrnC));
            ws.put("blue", Map.of("duration", formatDuration(wBluD), "seconds", wBluD, "count", wBluC));
            weeklyStats.add(ws);
        }

        Map<String, Object> summary = buildSummary(tOffD, tRedD, tYelD, tGrnD, tBluD,
                tOffC, tRedC, tYelC, tGrnC, tBluC);
        return Map.of("list", weeklyStats, "summary", summary);
    }

    private Map<String, Object> aggregateByMonth(List<String> dates, Map<String, JSONObject> dbDataMap) {
        Map<String, List<String>> monthGroups = new LinkedHashMap<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        for (String d : dates) {
            String monthKey = d.substring(0, 7);
            monthGroups.computeIfAbsent(monthKey, k -> new ArrayList<>()).add(d);
        }

        List<Map<String, Object>> monthlyStats = new ArrayList<>();
        long tOffD = 0, tRedD = 0, tYelD = 0, tGrnD = 0, tBluD = 0;
        int tOffC = 0, tRedC = 0, tYelC = 0, tGrnC = 0, tBluC = 0;

        for (Map.Entry<String, List<String>> entry : monthGroups.entrySet()) {
            String monthKey = entry.getKey();
            List<String> daysInMonth = entry.getValue();
            long mOffD = 0, mRedD = 0, mYelD = 0, mGrnD = 0, mBluD = 0;
            int mOffC = 0, mRedC = 0, mYelC = 0, mGrnC = 0, mBluC = 0;

            for (String d : daysInMonth) {
                long[] cnt = countLampData(dbDataMap.get(d));
                mOffD += cnt[0];
                mRedD += cnt[2];
                mYelD += cnt[4];
                mGrnD += cnt[6];
                mBluD += cnt[8];
                mOffC += (int) cnt[1];
                mRedC += (int) cnt[3];
                mYelC += (int) cnt[5];
                mGrnC += (int) cnt[7];
                mBluC += (int) cnt[9];
            }

            tOffD += mOffD;
            tRedD += mRedD;
            tYelD += mYelD;
            tGrnD += mGrnD;
            tBluD += mBluD;
            tOffC += mOffC;
            tRedC += mRedC;
            tYelC += mYelC;
            tGrnC += mGrnC;
            tBluC += mBluC;

            String monthNum = monthKey.substring(5);
            Map<String, Object> ms = new LinkedHashMap<>();
            ms.put("label", Integer.parseInt(monthNum) + "月");
            ms.put("month", monthKey);
            ms.put("off", Map.of("duration", formatDuration(mOffD), "seconds", mOffD, "count", mOffC));
            ms.put("red", Map.of("duration", formatDuration(mRedD), "seconds", mRedD, "count", mRedC));
            ms.put("yellow", Map.of("duration", formatDuration(mYelD), "seconds", mYelD, "count", mYelC));
            ms.put("green", Map.of("duration", formatDuration(mGrnD), "seconds", mGrnD, "count", mGrnC));
            ms.put("blue", Map.of("duration", formatDuration(mBluD), "seconds", mBluD, "count", mBluC));
            monthlyStats.add(ms);
        }

        Map<String, Object> summary = buildSummary(tOffD, tRedD, tYelD, tGrnD, tBluD,
                tOffC, tRedC, tYelC, tGrnC, tBluC);
        return Map.of("list", monthlyStats, "summary", summary);
    }

    private long[] countLampData(JSONObject dayData) {
        long[] result = new long[10];
        if (dayData != null) {
            JSONArray lampArr = dayData.getJSONArray("lampData");
            if (lampArr != null) {
                for (int i = 0; i < lampArr.size(); i++) {
                    JSONObject item = lampArr.getJSONObject(i);
                    if (item == null) continue;
                    int state = item.getIntValue("lampState");
                    long dur = item.getLongValue("duration");
                    switch (state) {
                        case 0 -> {
                            result[0] += dur;
                            result[1]++;
                        }
                        case 1 -> {
                            result[2] += dur;
                            result[3]++;
                        }
                        case 2 -> {
                            result[4] += dur;
                            result[5]++;
                        }
                        case 3 -> {
                            result[6] += dur;
                            result[7]++;
                        }
                        case 4 -> {
                            result[8] += dur;
                            result[9]++;
                        }
                    }
                }
            }
        }
        return result;
    }

    private Map<String, Object> buildSummary(long offD, long redD, long yelD, long grnD, long bluD,
                                             int offC, int redC, int yelC, int grnC, int bluC) {
        Map<String, Object> summary = new LinkedHashMap<>();
        summary.put("off", Map.of("duration", formatDuration(offD), "seconds", offD, "count", offC));
        summary.put("red", Map.of("duration", formatDuration(redD), "seconds", redD, "count", redC));
        summary.put("yellow", Map.of("duration", formatDuration(yelD), "seconds", yelD, "count", yelC));
        summary.put("green", Map.of("duration", formatDuration(grnD), "seconds", grnD, "count", grnC));
        summary.put("blue", Map.of("duration", formatDuration(bluD), "seconds", bluD, "count", bluC));
        return summary;
    }

    private List<String> buildDateRange(String type, String startDate, String endDate) {
        List<String> result = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        switch (type) {
            case "day": {
                if (StringUtils.hasText(startDate) && 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)) {
                            result.add(sdf.format(cur.getTime()));
                            cur.add(Calendar.DAY_OF_MONTH, 1);
                        }
                    } catch (Exception e) {
                        log.error("日期解析失败: {} ~ {}", startDate, endDate, e);
                    }
                }
                break;
            }
            case "week": {
                Calendar now = Calendar.getInstance();
                int currentYear = now.get(Calendar.YEAR);
                Calendar startCal = Calendar.getInstance();
                startCal.set(currentYear, Calendar.JANUARY, 1);
                Calendar endCal = (Calendar) now.clone();
                int todayDow = endCal.get(Calendar.DAY_OF_WEEK);
                int toSunday = todayDow == Calendar.SUNDAY ? 0 : (7 - todayDow);
                endCal.add(Calendar.DATE, toSunday);
                Calendar cur = Calendar.getInstance();
                cur.setTime(startCal.getTime());
                while (!cur.after(endCal)) {
                    result.add(sdf.format(cur.getTime()));
                    cur.add(Calendar.DAY_OF_MONTH, 1);
                }
                break;
            }
            case "month": {
                Calendar now = Calendar.getInstance();
                int year = now.get(Calendar.YEAR);
                int thisMonth = now.get(Calendar.MONTH);
                for (int m = 0; m <= thisMonth; m++) {
                    Calendar cal = Calendar.getInstance();
                    cal.set(year, m, 1);
                    int lastDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
                    for (int d = 1; d <= lastDay; d++) {
                        cal.set(year, m, d);
                        result.add(sdf.format(cal.getTime()));
                    }
                }
                break;
            }
        }
        return result;
    }

    private List<Map<String, Object>> queryOeeFromDb(String corpCode, String dtuSn, List<String> dates, String excludeToday) {
        if (dates.isEmpty()) return Collections.emptyList();

        String oeeTableName = corpConfigService.getOeeTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);

        StringBuilder sql = new StringBuilder(
                "SELECT oee_date, triColorLamp1, triColorLamp2 FROM " + oeeTableName + " WHERE corp_code = ?");
        List<Object> params = new ArrayList<>();
        params.add(corpCode);

        if (StringUtils.hasText(dtuSn)) {
            sql.append(" AND dtuSn = ?");
            params.add(dtuSn);
        }

        sql.append(" AND oee_date IN (");
        List<Object> dateParams = new ArrayList<>();
        for (String d : dates) {
            if (d.equals(excludeToday)) continue;
            sql.append("?,");
            dateParams.add(d);
        }
        if (dateParams.isEmpty()) return Collections.emptyList();
        sql.deleteCharAt(sql.length() - 1).append(")");
        params.addAll(dateParams);
        sql.append(" ORDER BY oee_date ASC");

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

    // ==================== OEE 时序图分页查询（含稼动率计算） ====================

    public Map<String, Object> queryOeeTimeline(String corpCode, String startDate, String endDate, Integer pageNo, Integer pageSize) {
        log.info("========== [OEE时序图查询-按设备分页] corpCode={}, startDate={}, endDate={}, pageNo={}, pageSize={} ==========",
                corpCode, startDate, endDate, pageNo, pageSize);

        if (!StringUtils.hasText(startDate) || !StringUtils.hasText(endDate)) {
            return Map.of("total", 0, "pageNo", pageNo, "pageSize", pageSize, "list", List.of());
        }
        int ps = Math.min(pageSize != null ? pageSize : 20, 20);
        int pn = pageNo != null && pageNo > 0 ? pageNo : 1;

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String todayStr = sdf.format(new Date());
        boolean includeToday = isDateInRange(todayStr, startDate, endDate);
        log.info("包含今日({}): {}", todayStr, includeToday);

        // 1. 从设备表查询所有 dtuSn 及设备名称
        Map<String, String> deviceNameMap = queryAllDtuSnWithName(corpCode);
        List<String> allDtuSns = new ArrayList<>(deviceNameMap.keySet());
        int deviceTotal = allDtuSns.size();
        log.info("设备总数: {}", deviceTotal);

        if (allDtuSns.isEmpty()) {
            return buildPageResult(0, pn, ps, List.of());
        }

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

        List<OeeRecord> allRecords = queryOeeBatch(corpCode, allDtuSns, dateList, includeToday ? todayStr : null);

        if (includeToday) {
            supplementTodayData(corpCode, allDtuSns, todayStr, allRecords);
        }

        Map<String, List<OeeRecord>> deviceMap = new LinkedHashMap<>();
        for (OeeRecord r : allRecords) {
            deviceMap.computeIfAbsent(r.dtuSn, k -> new ArrayList<>()).add(r);
        }
        for (String sn : allDtuSns) {
            if (!deviceMap.containsKey(sn)) {
                deviceMap.put(sn, new ArrayList<>());
            }
        }

        List<Map<String, Object>> pageList;
        int offset = (pn - 1) * ps;
        List<String> pagedDevices = new ArrayList<>(deviceMap.keySet()).subList(
                Math.min(offset, deviceTotal), Math.min(offset + ps, deviceTotal));
        pageList = convertDeviceGroupToResponse(pagedDevices, deviceMap, dateList, deviceNameMap);

        return buildPageResult(deviceTotal, pn, ps, pageList);
    }

    private Map<String, String> queryAllDtuSnWithName(String corpCode) {
        String tableName = corpConfigService.getDeviceTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);
        String sql = "SELECT dtuSn, deviceName FROM " + tableName + " 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 boolean isDateInRange(String dateStr, String start, String end) {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date target = sdf.parse(dateStr);
            Date s = sdf.parse(start);
            Date e = sdf.parse(end);
            return !target.before(s) && !target.after(e);
        } catch (Exception e) {
            return false;
        }
    }

    private List<String> buildDayList(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;
    }

    static class OeeRecord {
        String dtuSn;
        String oeeDate;
        JSONArray lampData;
        double availabilityRate;

        OeeRecord(String dtuSn, String oeeDate) {
            this.dtuSn = dtuSn;
            this.oeeDate = oeeDate;
            this.availabilityRate = 0.0;
        }
    }

    private List<OeeRecord> queryOeeBatch(String corpCode, List<String> dtuSns, List<String> dateList, String excludeToday) {
        List<OeeRecord> records = new ArrayList<>();
        if (dateList.isEmpty()) return records;

        String oeeTableName = corpConfigService.getOeeTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);

        StringBuilder sql = new StringBuilder(
                "SELECT dtuSn, oee_date, triColorLamp1, triColorLamp2 FROM " + oeeTableName +
                        " WHERE corp_code = ? AND dtuSn IN (");
        List<Object> params = new ArrayList<>();
        params.add(corpCode);

        for (String sn : dtuSns) {
            sql.append("?,");
            params.add(sn);
        }
        sql.deleteCharAt(sql.length() - 1).append(")");

        sql.append(" AND oee_date IN (");
        List<Object> dateParams = new ArrayList<>();
        for (String d : dateList) {
            if (d.equals(excludeToday)) continue;
            sql.append("?,");
            dateParams.add(d);
        }
        if (dateParams.isEmpty()) return records;
        sql.deleteCharAt(sql.length() - 1).append(")");
        params.addAll(dateParams);

        List<Map<String, Object>> rows = jt.queryForList(sql.toString(), params.toArray());
        log.info("DB批量查询返回 {} 行OEE数据", rows.size());

        for (Map<String, Object> row : rows) {
            String sn = (String) row.get("dtuSn");
            String oeeDate = String.valueOf(row.get("oee_date"));
            if (oeeDate.length() > 10) oeeDate = oeeDate.substring(0, 10);
            String lamp1 = (String) row.get("triColorLamp1");
            String lamp2 = (String) row.get("triColorLamp2");

            OeeRecord record = new OeeRecord(sn, oeeDate);
            try {
                String fullJson = (lamp1 != null ? lamp1 : "") + (lamp2 != null ? lamp2 : "");
                if (!fullJson.isEmpty()) {
                    record.lampData = JSONArray.parseArray(fullJson);
                } else {
                    record.lampData = new JSONArray();
                }
            } catch (Exception e) {
                log.warn("解析OEE JSON异常 - dtuSn:{}, date:{}", sn, oeeDate, e);
                record.lampData = new JSONArray();
            }

            record.availabilityRate = calcAvailabilityRate(record.lampData);
            records.add(record);
        }
        return records;
    }

    private void supplementTodayData(String corpCode, List<String> dtuSns, String todayStr, List<OeeRecord> records) {
        log.info("开始补充今日({})实时OEE数据...", todayStr);
        for (String dtuSn : dtuSns) {
            boolean exists = false;
            for (OeeRecord r : records) {
                if (r.dtuSn.equals(dtuSn) && r.oeeDate.equals(todayStr)) {
                    exists = true;
                    break;
                }
            }
            if (exists) continue;
            try {
                String apiResult = devicePullService.getLampData(corpCode, dtuSn, todayStr);
                if (!StringUtils.hasText(apiResult)) continue;
                Map<String, Object> res = JSON.parseObject(apiResult, new com.alibaba.fastjson.TypeReference<>() {
                });
                Integer code = (Integer) res.get("code");
                if (code == null || code != 200) continue;
                JSONArray dataList = (JSONArray) res.get("data");
                if (dataList == null || dataList.isEmpty()) continue;
                JSONObject dataObj = (JSONObject) dataList.get(0);
                OeeRecord record = new OeeRecord(dtuSn, todayStr);
                record.lampData = dataObj.getJSONArray("lampData");
                if (record.lampData == null) record.lampData = new JSONArray();
                record.availabilityRate = calcAvailabilityRate(record.lampData);
                records.add(record);
            } catch (Exception e) {
                log.error("获取今日OEE数据异常 - dtuSn:{}", dtuSn, e);
            }
        }
    }

    private double calcAvailabilityRate(JSONArray lampData) {
        if (lampData == null || lampData.isEmpty()) return 0.0;
        long totalDuration = 0;
        long greenDuration = 0;
        for (int i = 0; i < lampData.size(); i++) {
            JSONObject item = lampData.getJSONObject(i);
            if (item == null) continue;
            int state = item.getIntValue("lampState");
            long dur = item.getLongValue("duration");
            totalDuration += dur;
            if (state == 3) greenDuration += dur;
        }
        if (totalDuration == 0) return 0.0;
        return Math.round(greenDuration * 10000.0 / totalDuration) / 100.0;
    }

    private List<Map<String, Object>> convertDeviceGroupToResponse(List<String> pagedDevices,
                                                                   Map<String, List<OeeRecord>> deviceMap,
                                                                   List<String> dateList,
                                                                   Map<String, String> deviceNameMap) {
        List<Map<String, Object>> list = new ArrayList<>(pagedDevices.size());
        for (String dtuSn : pagedDevices) {
            Map<String, Object> item = new LinkedHashMap<>();
            item.put("dtuSn", dtuSn);
            item.put("deviceName", deviceNameMap.getOrDefault(dtuSn, ""));

            List<OeeRecord> dayRecords = deviceMap.getOrDefault(dtuSn, Collections.emptyList());
            Map<String, OeeRecord> recordByDate = new LinkedHashMap<>();
            for (OeeRecord r : dayRecords) {
                recordByDate.put(r.oeeDate, r);
            }

            long totalDur = 0, greenDur = 0;
            long offD = 0, redD = 0, yellowD = 0, greenD = 0, blueD = 0;
            int offC = 0, redC = 0, yellowC = 0, greenC = 0, blueC = 0;

            JSONArray allLampData = new JSONArray();
            List<Map<String, Object>> dailyDetails = new ArrayList<>();

            for (String d : dateList) {
                OeeRecord rec = recordByDate.get(d);
                if (rec != null && rec.lampData != null) {
                    for (int i = 0; i < rec.lampData.size(); i++) {
                        JSONObject lamp = rec.lampData.getJSONObject(i);
                        if (lamp == null) continue;
                        int state = lamp.getIntValue("lampState");
                        long dur = lamp.getLongValue("duration");
                        switch (state) {
                            case 0 -> {
                                offD += dur;
                                offC++;
                            }
                            case 1 -> {
                                redD += dur;
                                redC++;
                            }
                            case 2 -> {
                                yellowD += dur;
                                yellowC++;
                            }
                            case 3 -> {
                                greenD += dur;
                                greenC++;
                            }
                            case 4 -> {
                                blueD += dur;
                                blueC++;
                            }
                        }
                        totalDur += dur;
                        if (state == 3) greenDur += dur;
                    }
                    allLampData.addAll(rec.lampData);
                }
                double dayRate = (rec != null) ? rec.availabilityRate : 0.0;
                dailyDetails.add(Map.of("oeeDate", d, "availabilityRate", dayRate, "hasData", rec != null));
            }

            double overallRate = (totalDur > 0) ? Math.round(greenDur * 10000.0 / totalDur) / 100.0 : 0.0;

            item.put("availabilityRatio", String.format("%.2f%%", overallRate));
            item.put("offDuration", formatDuration(offD));
            item.put("redDuration", formatDuration(redD));
            item.put("yellowDuration", formatDuration(yellowD));
            item.put("greenDuration", formatDuration(greenD));
            item.put("blueDuration", formatDuration(blueD));
            item.put("dataDays", recordByDate.size());
            item.put("totalDays", dateList.size());
            item.put("lampData", allLampData);
            item.put("dailyDetails", dailyDetails);
            list.add(item);
        }
        return list;
    }

    private List<Map<String, Object>> convertToResponse(List<OeeRecord> records) {
        List<Map<String, Object>> list = new ArrayList<>(records.size());
        for (OeeRecord r : records) {
            Map<String, Object> item = new LinkedHashMap<>();
            item.put("dtuSn", r.dtuSn);
            item.put("oeeDate", r.oeeDate);
            item.put("availabilityRate", r.availabilityRate);
            item.put("availabilityRateStr", r.availabilityRate + "%");
            long offDur = 0, redDur = 0, yellowDur = 0, greenDur = 0, blueDur = 0;
            int offCnt = 0, redCnt = 0, yellowCnt = 0, greenCnt = 0, blueCnt = 0;
            if (r.lampData != null) {
                for (int i = 0; i < r.lampData.size(); i++) {
                    JSONObject lamp = r.lampData.getJSONObject(i);
                    if (lamp == null) continue;
                    int state = lamp.getIntValue("lampState");
                    long dur = lamp.getLongValue("duration");
                    switch (state) {
                        case 0 -> {
                            offDur += dur;
                            offCnt++;
                        }
                        case 1 -> {
                            redDur += dur;
                            redCnt++;
                        }
                        case 2 -> {
                            yellowDur += dur;
                            yellowCnt++;
                        }
                        case 3 -> {
                            greenDur += dur;
                            greenCnt++;
                        }
                        case 4 -> {
                            blueDur += dur;
                            blueCnt++;
                        }
                    }
                }
            }
            item.put("off", Map.of("duration", formatDuration(offDur), "seconds", offDur, "count", offCnt));
            item.put("red", Map.of("duration", formatDuration(redDur), "seconds", redDur, "count", redCnt));
            item.put("yellow", Map.of("duration", formatDuration(yellowDur), "seconds", yellowDur, "count", yellowCnt));
            item.put("green", Map.of("duration", formatDuration(greenDur), "seconds", greenDur, "count", greenCnt));
            item.put("blue", Map.of("duration", formatDuration(blueDur), "seconds", blueDur, "count", blueCnt));
            item.put("lampData", r.lampData != null ? r.lampData : new JSONArray());
            list.add(item);
        }
        return list;
    }

    private Map<String, Object> buildPageResult(long total, int pageNo, int pageSize, List<Map<String, Object>> list) {
        return Map.of("data", Map.of("total", total, "size", pageSize, "current", pageNo, "records", list));
    }

    // ==================== 智能灯统计稼动率查询（仪表盘） ====================

    public Map<String, Object> queryLampStatistics(String corpCode, String startDate, String endDate) {
        log.info("========== [智能灯统计查询] corpCode={}, startDate={}, endDate={} ==========", corpCode, startDate, endDate);

        List<String> dateList = buildDayList(startDate, endDate);
        if (dateList.isEmpty()) {
            return buildEmptyLampStats();
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String todayStr = sdf.format(new Date());
        boolean includeToday = dateList.contains(todayStr);
        log.info("日期范围共 {} 天, 包含今日({}): {}", dateList.size(), todayStr, includeToday);

        Map<String, String> deviceNameMap = queryAllDtuSnWithName(corpCode);
        List<String> allDtuSns = new ArrayList<>(deviceNameMap.keySet());
        if (allDtuSns.isEmpty()) {
            return buildEmptyLampStats();
        }

        List<OeeRecord> allRecords = queryOeeBatch(corpCode, allDtuSns, dateList, includeToday ? todayStr : null);
        if (includeToday) {
            supplementTodayData(corpCode, allDtuSns, todayStr, allRecords);
        }

        Map<String, List<OeeRecord>> deviceMap = new LinkedHashMap<>();
        for (OeeRecord r : allRecords) {
            deviceMap.computeIfAbsent(r.dtuSn, k -> new ArrayList<>()).add(r);
        }
        for (String sn : allDtuSns) {
            if (!deviceMap.containsKey(sn)) {
                deviceMap.put(sn, new ArrayList<>());
            }
        }

        return buildLampStatisticsResult(allDtuSns, deviceMap, deviceNameMap, dateList, corpCode);
    }

    private Map<String, Object> buildEmptyLampStats() {
        return Map.of("totalDuration", Map.of("off", Map.of("duration", "0时0分0秒", "seconds", 0),
                        "red", Map.of("duration", "0时0分0秒", "seconds", 0), "yellow", Map.of("duration", "0时0分0秒", "seconds", 0),
                        "green", Map.of("duration", "0时0分0秒", "seconds", 0), "blue", Map.of("duration", "0时0分0秒", "seconds", 0)),
                "availabilityRate", "0.00%", "currentStatus", Map.of("off", 0, "red", 0, "yellow", 0, "green", 0, "blue", 0),
                "abnormalRanking", List.of(), "deviceList", List.of());
    }

    private Map<String, Object> buildLampStatisticsResult(List<String> allDtuSns,
                                                          Map<String, List<OeeRecord>> deviceMap,
                                                          Map<String, String> deviceNameMap,
                                                          List<String> dateList,
                                                          String corpCode) {
        long totalOff = 0, totalRed = 0, totalYellow = 0, totalGreen = 0, totalBlue = 0;
        List<Map<String, Object>> deviceStatList = new ArrayList<>();

        for (String dtuSn : allDtuSns) {
            List<OeeRecord> dayRecords = deviceMap.getOrDefault(dtuSn, Collections.emptyList());
            long devOff = 0, devRed = 0, devYellow = 0, devGreen = 0, devBlue = 0;
            long devTotalDur = 0, devGreenDur = 0;

            for (OeeRecord rec : dayRecords) {
                if (rec.lampData != null) {
                    for (int i = 0; i < rec.lampData.size(); i++) {
                        JSONObject lamp = rec.lampData.getJSONObject(i);
                        if (lamp == null) continue;
                        int state = lamp.getIntValue("lampState");
                        long dur = lamp.getLongValue("duration");
                        switch (state) {
                            case 0 -> devOff += dur;
                            case 1 -> devRed += dur;
                            case 2 -> devYellow += dur;
                            case 3 -> {
                                devGreen += dur;
                                devGreenDur += dur;
                            }
                            case 4 -> devBlue += dur;
                        }
                        devTotalDur += dur;
                    }
                }
            }
            long devRygDur = devRed + devYellow + devGreen;
            double rate = (devRygDur > 0) ? Math.round(devGreenDur * 10000.0 / devRygDur) / 100.0 : 0.0;
            long abnormalDur = devRed + devYellow;

            Map<String, Object> devStat = new LinkedHashMap<>();
            devStat.put("dtuSn", dtuSn);
            devStat.put("deviceName", deviceNameMap.getOrDefault(dtuSn, ""));
            devStat.put("offDuration", formatDuration(devOff));
            devStat.put("offSeconds", devOff);
            devStat.put("redDuration", formatDuration(devRed));
            devStat.put("redSeconds", devRed);
            devStat.put("yellowDuration", formatDuration(devYellow));
            devStat.put("yellowSeconds", devYellow);
            devStat.put("greenDuration", formatDuration(devGreen));
            devStat.put("greenSeconds", devGreen);
            devStat.put("blueDuration", formatDuration(devBlue));
            devStat.put("blueSeconds", devBlue);
            devStat.put("availabilityRatio", String.format("%.2f%%", rate));
            devStat.put("rygTotalDuration", formatDuration(devRygDur));
            devStat.put("abnormalDuration", abnormalDur);
            deviceStatList.add(devStat);

            totalOff += devOff;
            totalRed += devRed;
            totalYellow += devYellow;
            totalGreen += devGreen;
            totalBlue += devBlue;
        }

        long totalRygDur = totalRed + totalYellow + totalGreen;
        double overallRate = (totalRygDur > 0) ? Math.round(totalGreen * 10000.0 / totalRygDur) / 100.0 : 0.0;
        Map<String, Integer> currentStatus = queryCurrentDeviceStatus(corpCode);

        deviceStatList.sort((a, b) -> Long.compare(((Number) b.get("abnormalDuration")).longValue(), ((Number) a.get("abnormalDuration")).longValue()));
        for (Map<String, Object> d : deviceStatList) {
            d.remove("abnormalDuration");
        }

        return Map.of("totalDuration", Map.of("off", Map.of("duration", formatDuration(totalOff), "seconds", totalOff),
                        "red", Map.of("duration", formatDuration(totalRed), "seconds", totalRed),
                        "yellow", Map.of("duration", formatDuration(totalYellow), "seconds", totalYellow),
                        "green", Map.of("duration", formatDuration(totalGreen), "seconds", totalGreen),
                        "blue", Map.of("duration", formatDuration(totalBlue), "seconds", totalBlue)),
                "availabilityRate", String.format("%.2f%%", overallRate), "rygTotalDuration", formatDuration(totalRygDur),
                "currentStatus", currentStatus, "abnormalRanking", deviceStatList, "deviceList", deviceStatList);
    }

    private Map<String, Integer> queryCurrentDeviceStatus(String corpCode) {
        String tableName = corpConfigService.getDeviceTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);
        String sql = "SELECT lampState, COUNT(*) as cnt FROM " + tableName + " WHERE corp_code = ? GROUP BY lampState";
        List<Map<String, Object>> rows = jt.queryForList(sql, corpCode);
        int off = 0, red = 0, yellow = 0, green = 0, blue = 0;
        for (Map<String, Object> row : rows) {
            String state = String.valueOf(row.get("lampState"));
            int cnt = ((Number) row.get("cnt")).intValue();
            switch (state) {
                case "0" -> off = cnt;
                case "1" -> red = cnt;
                case "2" -> yellow = cnt;
                case "3" -> green = cnt;
                case "4" -> blue = cnt;
            }
        }
        return Map.of("off", off, "red", red, "yellow", yellow, "green", green, "blue", blue);
    }

    // ==================== 开机率查询 ====================

    public Map<String, Object> queryBootRate(String corpCode, String startDate, String endDate) {
        log.info("========== [开机率查询] corpCode={}, startDate={}, endDate={} ==========", corpCode, startDate, endDate);
        if (!StringUtils.hasText(startDate) || !StringUtils.hasText(endDate)) {
            return Map.of("list", List.of(), "startDate", startDate, "endDate", endDate);
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String todayStr = sdf.format(new Date());
        boolean includeToday = isDateInRange(todayStr, startDate, endDate);

        Map<String, String> deviceNameMap = queryAllDtuSnWithName(corpCode);
        List<String> allDtuSns = new ArrayList<>(deviceNameMap.keySet());
        if (allDtuSns.isEmpty()) {
            return Map.of("list", List.of(), "startDate", startDate, "endDate", endDate);
        }
        log.info("设备总数: {}", allDtuSns.size());

        String devUtilTableName = corpConfigService.getDevUtilTableName(corpCode);
        JdbcTemplate jt = corpConfigService.getJdbcTemplate(corpCode);

        List<Map<String, Object>> rows;
        if (includeToday) {
            rows = queryDevUtilBatch(jt, devUtilTableName, corpCode, allDtuSns, startDate, endDate, todayStr);
        } else {
            String sql = "SELECT dtuSn, SUM(`0`) as s0, SUM(`1`) as s1, SUM(`2`) as s2, SUM(`3`) as s3, SUM(`4`) as s4, COUNT(*) as days " +
                    "FROM " + devUtilTableName + " WHERE corp_code = ? AND date_util BETWEEN ? AND ? GROUP BY dtuSn";
            rows = jt.queryForList(sql, corpCode, startDate, endDate);
        }
        log.info("dev_util 查询返回 {} 条记录", rows.size());

        Map<String, double[]> rawMap = new LinkedHashMap<>();
        for (Map<String, Object> row : rows) {
            String sn = (String) row.get("dtuSn");
            double s0 = ((Number) row.get("s0")).doubleValue();
            double s1 = ((Number) row.get("s1")).doubleValue();
            double s2 = ((Number) row.get("s2")).doubleValue();
            double s3 = ((Number) row.get("s3")).doubleValue();
            double s4 = ((Number) row.get("s4")).doubleValue();
            int dataDays = ((Number) row.get("days")).intValue();
            rawMap.put(sn, new double[]{s0, s1, s2, s3, s4, dataDays});
        }

        if (includeToday) {
            supplementTodayDevUtil(corpCode, allDtuSns, todayStr, rawMap);
        }

        Map<String, Map<String, Object>> rateMap = new LinkedHashMap<>();
        for (Map.Entry<String, double[]> entry : rawMap.entrySet()) {
            String sn = entry.getKey();
            double[] vals = entry.getValue();
            double totalDur = vals[0] + vals[1] + vals[2] + vals[3] + vals[4];
            double onDur = vals[1] + vals[2] + vals[3] + vals[4];
            double bootRate = totalDur > 0 ? Math.round(onDur * 10000.0 / totalDur) / 100.0 : 0.0;
            Map<String, Object> item = new LinkedHashMap<>();
            item.put("dtuSn", sn);
            item.put("deviceName", deviceNameMap.getOrDefault(sn, ""));
            item.put("totalDuration", formatDuration((long) totalDur));
            item.put("totalSeconds", Math.round(totalDur));
            item.put("onDuration", formatDuration((long) onDur));
            item.put("onSeconds", Math.round(onDur));
            item.put("offDuration", formatDuration((long) vals[0]));
            item.put("offSeconds", Math.round(vals[0]));
            item.put("bootRate", String.format("%.2f%%", bootRate));
            item.put("bootRateValue", bootRate);
            item.put("dataDays", (int) vals[5]);
            rateMap.put(sn, item);
        }

        List<Map<String, Object>> resultList = new ArrayList<>(allDtuSns.size());
        for (String sn : allDtuSns) {
            if (rateMap.containsKey(sn)) {
                resultList.add(rateMap.get(sn));
            } else {
                Map<String, Object> emptyItem = new LinkedHashMap<>();
                emptyItem.put("dtuSn", sn);
                emptyItem.put("deviceName", deviceNameMap.getOrDefault(sn, ""));
                emptyItem.put("totalDuration", "0时0分0秒");
                emptyItem.put("totalSeconds", 0);
                emptyItem.put("onDuration", "0时0分0秒");
                emptyItem.put("onSeconds", 0);
                emptyItem.put("offDuration", "0时0分0秒");
                emptyItem.put("offSeconds", 0);
                emptyItem.put("bootRate", "0.00%");
                emptyItem.put("bootRateValue", 0.0);
                emptyItem.put("dataDays", 0);
                resultList.add(emptyItem);
            }
        }

        double sumTotal = 0, sumOn = 0, sumOff = 0;
        for (Map<String, Object> r : rateMap.values()) {
            sumTotal += ((Number) r.get("totalSeconds")).doubleValue();
            sumOn += ((Number) r.get("onSeconds")).doubleValue();
            sumOff += ((Number) r.get("offSeconds")).doubleValue();
        }
        double overallBootRate = sumTotal > 0 ? Math.round(sumOn * 10000.0 / sumTotal) / 100.0 : 0.0;

        return Map.of("summary", Map.of("totalDevices", allDtuSns.size(),
                        "totalDuration", formatDuration((long) sumTotal), "totalSeconds", Math.round(sumTotal),
                        "onDuration", formatDuration((long) sumOn), "onSeconds", Math.round(sumOn),
                        "offDuration", formatDuration((long) sumOff), "offSeconds", Math.round(sumOff),
                        "overallBootRate", String.format("%.2f%%", overallBootRate), "overallBootRateValue", overallBootRate),
                "list", resultList, "startDate", startDate, "endDate", endDate);
    }

    private List<Map<String, Object>> queryDevUtilBatch(JdbcTemplate jt, String devUtilTableName, String corpCode,
                                                        List<String> dtuSns, String startDate, String endDate, String excludeToday) {
        StringBuilder sql = new StringBuilder("SELECT dtuSn, SUM(`0`) as s0, SUM(`1`) as s1, SUM(`2`) as s2, SUM(`3`) as s3, SUM(`4`) as s4, COUNT(*) as days " +
                "FROM " + devUtilTableName + " WHERE corp_code = ? AND dtuSn IN (");
        List<Object> params = new ArrayList<>();
        params.add(corpCode);
        for (String sn : dtuSns) {
            sql.append("?,");
            params.add(sn);
        }
        sql.deleteCharAt(sql.length() - 1).append(")");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String actualStart = startDate.equals(excludeToday) ? getNextDay(excludeToday) : startDate;
        try {
            Date startD = sdf.parse(actualStart);
            Date endD = sdf.parse(endDate);
            Date excD = sdf.parse(excludeToday);
            if (!startD.before(excD) || endD.before(startD)) return Collections.emptyList();
        } catch (Exception e) {
            log.error("日期解析失败: {} ~ {}, 排除: {}", actualStart, endDate, excludeToday, e);
            return Collections.emptyList();
        }
        sql.append(" AND date_util BETWEEN ? AND ?");
        params.add(actualStart);
        params.add(endDate);
        sql.append(" GROUP BY dtuSn");
        return jt.queryForList(sql.toString(), params.toArray());
    }

    private void supplementTodayDevUtil(String corpCode, List<String> dtuSns, String todayStr, Map<String, double[]> rawMap) {
        log.info("开机率查询 - 开始补充今日({})实时dev_util数据...", todayStr);
        for (String dtuSn : dtuSns) {
            try {
                String rateResult = devicePullService.getDtuSnRateOfAction(corpCode, dtuSn, todayStr, todayStr);
                if (StringUtils.hasText(rateResult)) {
                    Map<String, Object> res = JSON.parseObject(rateResult, new com.alibaba.fastjson.TypeReference<>() {
                    });
                    Integer code = (Integer) res.get("code");
                    if (code == null || code != 200) continue;
                    JSONArray dataList = (JSONArray) res.get("data");
                    if (dataList == null || dataList.isEmpty()) continue;
                    JSONObject dayData = (JSONObject) dataList.get(0);
                    JSONArray realRateList = dayData.getJSONArray("realRate");
                    if (realRateList == null || realRateList.isEmpty()) continue;
                    JSONObject rateObj = (JSONObject) realRateList.get(0);
                    double s0 = rateObj.getDoubleValue("0");
                    double s1 = rateObj.getDoubleValue("1");
                    double s2 = rateObj.getDoubleValue("2");
                    double s3 = rateObj.getDoubleValue("3");
                    double s4 = rateObj.getDoubleValue("4");
                    double[] existing = rawMap.get(dtuSn);
                    if (existing != null) {
                        existing[0] += s0;
                        existing[1] += s1;
                        existing[2] += s2;
                        existing[3] += s3;
                        existing[4] += s4;
                        existing[5] += 1;
                    } else {
                        rawMap.put(dtuSn, new double[]{s0, s1, s2, s3, s4, 1});
                    }
                }
            } catch (Exception e) {
                log.error("获取今日稼动率数据异常 - dtuSn:{}", dtuSn, e);
            }
        }
    }

    private static String getNextDay(String dateStr) {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Calendar cal = Calendar.getInstance();
            cal.setTime(sdf.parse(dateStr));
            cal.add(Calendar.DAY_OF_MONTH, 1);
            return sdf.format(cal.getTime());
        } catch (Exception e) {
            return dateStr;
        }
    }
}