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;

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