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

@Slf4j
@Service
public class DeviceSearchService {

    @Value("${device.db.corpCode}")
    private String deviceCorpCode;
    @Value("${device.db.tableName}")
    private String deviceTableName;
    @Value("${device.db.oeeTableName}")
    private String oeeTableName;
    @Value("${device.db.devUtilTableName}")
    private String devUtilTableName;

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

    public Map<String, Object> queryDeviceList(String deviceName, String lampState, Integer pageNo, Integer pageSize) {
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM " + deviceTableName + " WHERE corp_code = ?");
        StringBuilder querySql = new StringBuilder("SELECT id, deviceName, projectType, projectState, dtuSn, dtuId, deviceId, " +
                "lampState, startTime, duration, utilizationRate FROM " + deviceTableName + " WHERE corp_code = ?");
        List<Object> params = new java.util.ArrayList<>();
        params.add(deviceCorpCode);

        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 = jdbcTemplate.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 = jdbcTemplate.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 sql = "SELECT lampState, COUNT(*) as cnt FROM " + deviceTableName +
                " WHERE corp_code = ? GROUP BY lampState";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, deviceCorpCode);

        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 dtuSn, String date) {
        String result = devicePullService.getLampData(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 dtuSn   设备序列号（可选，为空查全部）
     * @param type    查询类型：day-日(按日期段), week-周(今年第1周~本周), month-月(今年1月~本月)
     * @param startDate 日模式下的开始日期(yyyy-MM-dd)
     * @param endDate   日模式下的结束日期(yyyy-MM-dd)
     */
    public Map<String, Object> queryOeeStats(String dtuSn, String type, String startDate, String endDate) {
        // 1. 根据类型确定日期范围
        List<String> dates = buildDateRange(type, startDate, endDate);
        log.info("OEE查询 - type:{}, dtuSn:{}, 日期范围:{}天, 起始:{}, 结束:{}", 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(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"));
            // 截断时间部分，只保留日期 yyyy-MM-dd
            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(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) {
        // 按自然周（周一~周日）分组，第一周从1月1日开始（可能不足7天）
        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);

        // 找到第一个周一：如果1月1日就是周一，则firstMonday=1月1日；否则往后推到周一
        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)) {
                    // 在第一个周一之前（含当天），属于第1周（从1月1日开始）
                    bucketIndex = 0;
                } else {
                    // 第一个周一之后，按完整自然周计算
                    long diffMs = cal.getTimeInMillis() - firstMonday.getTimeInMillis();
                    long diffDays = diffMs / (1000 * 60 * 60 * 24);
                    bucketIndex = 1 + (int)(diffDays / 7); // 第2周、第3周...
                }

                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); // yyyy-MM
            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); // "01", "02" ...
            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);
    }

    /**
     * 统计单日 lampData 各状态时长和次数
     * 返回 [offDur, offCnt, redDur, redCnt, yelDur, yelCnt, grnDur, grnCnt, bluDur, bluCnt]
     */
    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);
                    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;
    }

    /**
     * 构建 summary 汇总对象
     */
    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": {
                // 今年1月1日 ~ 今天所在周的周日（自然周，不跨年）
                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": {
                // 今年1月 ~ 本月最后一天
                Calendar now = Calendar.getInstance();
                int year = now.get(Calendar.YEAR);
                int thisMonth = now.get(Calendar.MONTH); // 0-indexed

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

    /**
     * 从 oee 表查询指定日期范围的数据
     */
    private List<Map<String, Object>> queryOeeFromDb(String dtuSn, List<String> dates, String excludeToday) {
        if (dates.isEmpty()) return Collections.emptyList();

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

        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 jdbcTemplate.queryForList(sql.toString(), params.toArray());
    }

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

    /**
     * 按设备分页查询所有设备的OEE时序数据，并计算稼动率
     *
     * @param startDate 开始日期 yyyy-MM-dd
     * @param endDate   结束日期 yyyy-MM-dd
     * @param pageNo    页码，从1开始
     * @param pageSize  每页设备数，最大20
     */
    public Map<String, Object> queryOeeTimeline(String startDate, String endDate, Integer pageNo, Integer pageSize) {
        log.info("========== [OEE时序图查询-按设备分页] startDate={}, endDate={}, pageNo={}, pageSize={} ==========",
                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();
        List<String> allDtuSns = new ArrayList<>(deviceNameMap.keySet());
        int deviceTotal = allDtuSns.size();
        log.info("设备总数: {}", deviceTotal);

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

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

        // 3. 批量从数据库查询全部设备的 OEE 数据
        List<OeeRecord> allRecords = queryOeeBatch(allDtuSns, dateList, includeToday ? todayStr : null);

        // 4. 如果包含今天，补充调用接口获取今天的实时数据
        if (includeToday) {
            supplementTodayData(allDtuSns, todayStr, allRecords);
        }

        // 5. 按 dtuSn 分组，每个设备包含该日期范围内所有天的数据
        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<>());
            }
        }

        // 6. 设备维度分页
        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);
    }

    /** 查询设备表所有dtuSn及对应设备名称 */
    private Map<String, String> queryAllDtuSnWithName() {
        String sql = "SELECT dtuSn, deviceName FROM " + deviceTableName + " WHERE corp_code = ? ORDER BY dtuSn";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, deviceCorpCode);
        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;
    }

    /** OEE内部记录结构 */
    private static class OeeRecord {
        String dtuSn;
        String oeeDate;
        JSONArray lampData;       // 该日lampData数组
        double availabilityRate;  // 稼动率 (%)

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

    /** 批量从数据库查询OEE数据 */
    private List<OeeRecord> queryOeeBatch(List<String> dtuSns, List<String> dateList, String excludeToday) {
        List<OeeRecord> records = new ArrayList<>();

        if (dateList.isEmpty()) return records;

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

        // dtuSn IN 条件
        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 = jdbcTemplate.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(List<String> dtuSns, String todayStr, List<OeeRecord> records) {
        log.info("开始补充今日({})实时OEE数据...", todayStr);
        int apiCount = 0;
        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(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);
                apiCount++;
            } catch (Exception e) {
                log.error("获取今日OEE数据异常 - dtuSn:{}", dtuSn, e);
            }
        }
        log.info("今日实时数据补充完成, 新增 {} 条", apiCount);
    }

    /**
     * 计算稼动率
     * 稼动率 = 绿灯(state=3)时长 / 总时长 * 100%
     */
    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; // 保留2位小数
    }

    /** 按设备分组转换为响应格式：每个设备一条记录，包含lampData和设备名称 */
    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());
            // 构建日期→record的快速查找map
            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;

            // 拼接所有天的 lampData 为一个数组（按日期排序）
            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
                )
        );
    }

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

    /**
     * 智能灯统计稼动率综合查询（仪表盘数据）
     * 返回：总时长、稼动率、当前机台运行状态、异常排行榜、每设备状态时长
     *
     * @param startDate 开始日期 yyyy-MM-dd
     * @param endDate   结束日期 yyyy-MM-dd
     */
    public Map<String, Object> queryLampStatistics(String startDate, String endDate) {
        log.info("========== [智能灯统计查询] startDate={}, endDate={} ==========", startDate, endDate);

        // 1. 根据前端传入的起止日期构建日期范围
        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);

        // 2. 获取所有设备及名称
        Map<String, String> deviceNameMap = queryAllDtuSnWithName();
        List<String> allDtuSns = new ArrayList<>(deviceNameMap.keySet());
        if (allDtuSns.isEmpty()) {
            return buildEmptyLampStats();
        }

        // 3. 批量从OEE表查询数据
        List<OeeRecord> allRecords = queryOeeBatch(allDtuSns, dateList, includeToday ? todayStr : null);

        // 4. 补充今天的实时数据
        if (includeToday) {
            supplementTodayData(allDtuSns, todayStr, allRecords);
        }

        // 5. 按 dtuSn 分组
        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<>());
            }
        }

        // 6. 计算各项统计数据
        return buildLampStatisticsResult(allDtuSns, deviceMap, deviceNameMap, dateList);
    }

    /** 构建空结果 */
    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) {
        // ---- ① 总时长：汇总所有设备各状态时长 ----
        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;
                    }
                }
            }

            // 稼动率 = 绿 / (红 + 黄 + 绿) * 100%
            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();

        // ---- ⑤ 异常排行榜（按红+黄时长降序） ----
        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 sql = "SELECT lampState, COUNT(*) as cnt FROM " + deviceTableName +
                " WHERE corp_code = ? GROUP BY lampState";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, deviceCorpCode);

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

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

    /**
     * 查询每台设备的开机率（基于 dev_util 表）
     * 规则：灭灯(state=0)=未开机，其他灯(state=1/2/3/4)=开机
     * 开机率 = 非灭灯时长 / 总时长 * 100%
     *
     * @param startDate 开始日期 yyyy-MM-dd
     * @param endDate   结束日期 yyyy-MM-dd
     */
    public Map<String, Object> queryBootRate(String startDate, String endDate) {
        log.info("========== [开机率查询] startDate={}, endDate={} ==========", 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);
        log.info("设备总数: {}, 包含今日({}): {}", includeToday);

        // 1. 获取所有设备列表
        Map<String, String> deviceNameMap = queryAllDtuSnWithName();
        List<String> allDtuSns = new ArrayList<>(deviceNameMap.keySet());
        if (allDtuSns.isEmpty()) {
            return Map.of("list", List.of(), "startDate", startDate, "endDate", endDate);
        }
        log.info("设备总数: {}", allDtuSns.size());

        // 2. 从 dev_util 表按 dtuSn 和日期范围汇总各状态时长（排除今天）
        String dbStartDate = includeToday && startDate.equals(todayStr) ? getNextDay(todayStr) : startDate;
        List<Map<String, Object>> rows;
        if (includeToday) {
            // 排除今天，今天走接口
            rows = queryDevUtilBatch(deviceCorpCode, 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 = jdbcTemplate.queryForList(sql, deviceCorpCode, startDate, endDate);
        }
        log.info("dev_util 查询返回 {} 条记录", rows.size());

        // 3. 按 dtuSn 构建查询结果 map
        Map<String, double[]> rawMap = new LinkedHashMap<>();  // [s0, s1, s2, s3, s4, dataDays]
        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});
        }

        // 4. 如果包含今天，补充调用稼动率接口获取今天的实时数据
        if (includeToday) {
            supplementTodayDevUtil(allDtuSns, todayStr, rawMap);
        }

        // 5. 构建最终结果
        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);
        }

        // 6. 确保所有设备都在返回列表中（没有数据的设备开机率为0）
        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
        );
    }

    /** 批量从 dev_util 表查询（排除指定日期） */
    private List<Map<String, Object>> queryDevUtilBatch(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(")");

        // 日期范围：如果开始日期=excludeToday，则从明天开始查
        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 jdbcTemplate.queryForList(sql.toString(), params.toArray());
    }

    /** 补充今天的稼动率接口实时数据 */
    private void supplementTodayDevUtil(List<String> dtuSns, String todayStr, Map<String, double[]> rawMap) {
        log.info("开机率查询 - 开始补充今日({})实时dev_util数据...", todayStr);
        int apiCount = 0;
        for (String dtuSn : dtuSns) {
            try {
                String rateResult = devicePullService.getDtuSnRateOfAction(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");

                    // 累加到已有数据上（DB已有历史数据 + 今天实时数据）
                    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});
                    }
                    apiCount++;
                }
            } catch (Exception e) {
                log.error("获取今日稼动率数据异常 - dtuSn:{}", dtuSn, e);
            }
        }
        log.info("开机率查询 - 今日实时数据补充完成, 新增 {} 条", apiCount);
    }

    /** 获取下一天的日期字符串 */
    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;
        }
    }
}