package com.iot.scheduler.service;

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

import jakarta.annotation.Resource;

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

@Slf4j
@Service
public class EnergySearchService {

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

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 分页查询能耗设备信息
     * 查询表 t_auto_ymk_iot_energy，按设备名称排序
     *
     * @param deviceName 设备名称（模糊匹配）
     * @param runStatus  状态（0：离线，1：停机，2：待机，3：运行）
     * @param pageNo     页码，默认1
     * @param pageSize   每页条数，默认10
     */
    public Map<String, Object> queryEnergyList(String deviceName, String runStatus,
                                               Integer pageNo, Integer pageSize) {
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM " + energyTableName + " WHERE corp_code = ?");
        StringBuilder querySql = new StringBuilder(
                "SELECT id, deviceName, projectType, projectState, dtuSn, dtuId, deviceId, " +
                        "evalue, duration, runStatus, created_at, updated_at " +
                        "FROM " + energyTableName + " WHERE corp_code = ?");
        List<Object> params = new ArrayList<>();
        params.add(energyCorpCode);

        // 设备名称模糊查询
        if (StringUtils.hasText(deviceName)) {
            countSql.append(" AND deviceName LIKE ?");
            querySql.append(" AND deviceName LIKE ?");
            params.add("%" + deviceName + "%");
        }

        // 设备状态精确匹配
        if (StringUtils.hasText(runStatus)) {
            countSql.append(" AND runStatus = ?");
            querySql.append(" AND runStatus = ?");
            params.add(runStatus);
        }

        Long total = jdbcTemplate.queryForObject(countSql.toString(), Long.class, params.toArray());
        int offset = (pageNo - 1) * pageSize;

        querySql.append(" ORDER BY deviceName ASC LIMIT ?, ?");
        params.add(offset);
        params.add(pageSize);

        List<Map<String, Object>> list = jdbcTemplate.queryForList(querySql.toString(), params.toArray());
        list.forEach(row -> {
            if (row.get("duration") != null) {
                long seconds = Long.parseLong(String.valueOf(row.get("duration")));
                long h = seconds / 3600;
                long m = (seconds % 3600) / 60;
                long s = seconds % 60;
                StringBuilder sb = new StringBuilder();
                if (h > 0) sb.append(h).append("时");
                if (h > 0 || m > 0) sb.append(m).append("分");
                sb.append(s).append("秒");
                row.put("duration", sb.toString());
            }
        });

        return Map.of(
                "code", 200,
                "msg", "请求成功",
                "total", total != null ? total : 0,
                "pageNo", pageNo,
                "pageSize", pageSize,
                "list", list
        );
    }

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

        int total = 0;
        Map<String, Integer> statusMap = new LinkedHashMap<>();
        statusMap.put("0", 0);
        statusMap.put("1", 0);
        statusMap.put("2", 0);
        statusMap.put("3", 0);

        for (Map<String, Object> row : rows) {
            String key = String.valueOf(row.get("runStatus"));
            int cnt = ((Number) row.get("cnt")).intValue();
            total += cnt;
            statusMap.merge(key, cnt, Integer::sum);
        }

        return Map.of(
                "code", 200,
                "msg", "请求成功",
                "total", total,
                "0", statusMap.get("0"),
                "1", statusMap.get("1"),
                "2", statusMap.get("2"),
                "3", statusMap.get("3")
        );
    }

    /**
     * 根据dtuSn查询指定日期的设备时用电量和OEE时序
     * 1. 时用电量：从t_auto_ymk_iot_eq_kwh表获取，并计算当日总用电量
     * 2. OEE时序：从t_auto_ymk_iot_e_run_dtl表获取，统计总时长、各状态运行时长和占比
     *
     * @param dtuSn 设备序列号
     * @param date  查询日期 yyyy-MM-dd
     */
    public Map<String, Object> queryEnergyDetailByDate(String dtuSn, String date) {
        // 1. 查询时用电量数据 - 原始数据直接返回
        Object kwhRawData = Collections.emptyList();
        BigDecimal totalKwh = BigDecimal.ZERO;
        try {
            String kwhSql = "SELECT description FROM " + eqKwhTableName
                    + " WHERE corp_code = ? AND dtuSn = ? AND use_date = ?";
            Map<String, Object> kwhRow = jdbcTemplate.queryForMap(kwhSql, energyCorpCode, dtuSn, date + " 00:00:00");
            if (kwhRow != null && kwhRow.get("description") != null) {
                String description = String.valueOf(kwhRow.get("description"));
                kwhRawData = JSON.parseArray(description);
                // 计算总用电量
                JSONArray dataArray = JSON.parseArray(description);
                for (int i = 0; i < dataArray.size(); i++) {
                    JSONObject item = dataArray.getJSONObject(i);
                    Double value = item.getDouble("value");
                    if (value != null) {
                        totalKwh = totalKwh.add(BigDecimal.valueOf(value));
                    }
                }
            }
        } catch (Exception e) {
            log.warn("【能耗明细】查询时用电量数据为空或异常 - dtuSn:{}, date:{}", dtuSn, date);
        }

        // 2. 查询OEE时序数据 - 原始数据直接返回
        Object oeeRawData = Collections.emptyList();
        long totalDuration = 0;
        // runStatus状态: 0-离线, 1-停机, 2-待机, 3-运行
        Map<Integer, Long> statusDurationMap = new LinkedHashMap<>();
        statusDurationMap.put(0, 0L); // 离线
        statusDurationMap.put(1, 0L); // 停机
        statusDurationMap.put(2, 0L); // 待机
        statusDurationMap.put(3, 0L); // 运行

        try {
            String oeeSql = "SELECT runStatus1, runStatus2 FROM " + eRunDtlTableName
                    + " WHERE corp_code = ? AND dtuSn = ? AND use_date = ?";
            Map<String, Object> oeeRow = jdbcTemplate.queryForMap(oeeSql, energyCorpCode, dtuSn, date + " 00:00:00");
            if (oeeRow != null) {
                String rs1 = oeeRow.get("runStatus1") != null ? String.valueOf(oeeRow.get("runStatus1")) : "";
                String rs2 = oeeRow.get("runStatus2") != null ? String.valueOf(oeeRow.get("runStatus2")) : "";
                String jsonStr = rs1 + rs2;
                oeeRawData = JSON.parseArray(jsonStr);

                // 统计各状态时长
                JSONArray dataArray = JSON.parseArray(jsonStr);
                for (int i = 0; i < dataArray.size(); i++) {
                    JSONObject item = dataArray.getJSONObject(i);
                    Long duration = item.getLong("duration");
                    Integer runStatus = item.getInteger("runStatus");

                    if (duration != null && duration > 0) {
                        totalDuration += duration;
                        int statusKey = runStatus != null ? runStatus : 0;
                        statusDurationMap.merge(statusKey, duration, Long::sum);
                    }
                }
            }
        } catch (Exception e) {
            log.warn("【能耗明细】查询OEE时序数据为空或异常 - dtuSn:{}, date:{}", dtuSn, date);
        }

        // 3. 构建OEE统计结果（含格式化时长和占比）
        List<Map<String, Object>> statusStats = new ArrayList<>();
        for (Map.Entry<Integer, Long> entry : statusDurationMap.entrySet()) {
            long dur = entry.getValue();
            double percent = totalDuration > 0 ? BigDecimal.valueOf(dur * 100.0 / totalDuration)
                    .setScale(2, RoundingMode.HALF_UP).doubleValue() : 0.0;

            Map<String, Object> stat = new LinkedHashMap<>();
            stat.put("status", entry.getKey());
            stat.put("durationSeconds", dur);
            stat.put("durationFormatted", formatDuration(dur));
            stat.put("percent", percent);
            statusStats.add(stat);
        }

        return Map.of(
                "code", 200,
                "msg", "请求成功",
                "dtuSn", dtuSn,
                "date", date,
                "kwhData", Map.of(
                        "list", kwhRawData,
                        "totalKwh", totalKwh.setScale(2, RoundingMode.HALF_UP)
                ),
                "oeeData", Map.of(
                        "list", oeeRawData,
                        "totalDurationFormatted", formatDuration(totalDuration),
                        "totalDurationSeconds", totalDuration,
                        "statusStats", statusStats
                )
        );
    }

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

        // 根据type构建日期列表
        if ("1".equals(type)) {
            dateList.add(startDate);
        } else if ("2".equals(type)) {
            try {
                Date start = sdf.parse(startDate);
                Date end = sdf.parse(endDate);
                Calendar cur = Calendar.getInstance();
                cur.setTime(start);
                while (!cur.getTime().after(end)) {
                    dateList.add(sdf.format(cur.getTime()));
                    cur.add(Calendar.DAY_OF_MONTH, 1);
                }
            } catch (Exception e) {
                return Map.of("code", 400, "msg", "日期格式错误");
            }
        } else if ("3".equals(type)) {
            Calendar now = Calendar.getInstance();
            int year = now.get(Calendar.YEAR);
            Calendar startCal = Calendar.getInstance();
            startCal.set(year, Calendar.JANUARY, 1);
            while (!startCal.getTime().after(now.getTime())) {
                dateList.add(sdf.format(startCal.getTime()));
                startCal.add(Calendar.DAY_OF_MONTH, 1);
            }
        }

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

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

        for (String dateStr : dateList) {
            // 从eq_kwh表查询时用电量数据
            Object kwhRawData = Collections.emptyList();
            BigDecimal dayKwh = BigDecimal.ZERO;
            long dayTotalDuration = 0;
            Map<Integer, Long> dayStatusDuration = initStatusMap();

            try {
                String kwhSql = "SELECT description FROM " + eqKwhTableName
                        + " WHERE corp_code = ? AND dtuSn = ? AND use_date = ?";
                Map<String, Object> kwhRow = jdbcTemplate.queryForMap(kwhSql, energyCorpCode, dtuSn, dateStr + " 00:00:00");
                if (kwhRow != null && kwhRow.get("description") != null) {
                    String desc = String.valueOf(kwhRow.get("description"));
                    JSONArray dataArray = JSON.parseArray(desc);

                    for (int i = 0; i < dataArray.size(); i++) {
                        JSONObject item = dataArray.getJSONObject(i);
                        Double value = item.getDouble("value");
                        if (value != null) dayKwh = dayKwh.add(BigDecimal.valueOf(value));

                        // 从字段 0/1/2/3 统计各状态运行时长 + 格式化新字段
                        for (int statusKey = 0; statusKey <= 3; statusKey++) {
                            Long dur = item.getLong(String.valueOf(statusKey));
                            item.put(statusKey + "Formatted", formatDuration(dur != null ? dur : 0L));
                            if (dur != null && dur > 0) {
                                dayTotalDuration += dur;
                                totalDurationAll += dur;
                                dayStatusDuration.merge(statusKey, dur, Long::sum);
                                statusDurationAllMap.merge(statusKey, dur, Long::sum);
                            }
                        }
                    }
                    kwhRawData = dataArray;
                }
            } catch (Exception ignored) {}
            totalKwhAll = totalKwhAll.add(dayKwh);

            String periodKey = "3".equals(type) ? dateStr.substring(0, 7) : dateStr;

            if ("1".equals(type)) {
                // type=1(时): 返回原始kwhList + 当日统计
                Map<String, Object> entry = new LinkedHashMap<>();
                entry.put("date", periodKey);
                entry.put("kwhList", kwhRawData);
                entry.put("totalKwh", dayKwh.setScale(2, RoundingMode.HALF_UP));
                entry.put("totalDurationFormatted", formatDuration(dayTotalDuration));
                entry.put("totalDurationSeconds", dayTotalDuration);
                entry.put("statusStats", buildStatusStats(dayStatusDuration, dayTotalDuration));
                detailList.add(entry);
            } else if ("2".equals(type)) {
                // type=2(天): 返回每日汇总统计，不含kwhList
                Map<String, Object> entry = new LinkedHashMap<>();
                entry.put("date", periodKey);
                entry.put("totalKwh", dayKwh.setScale(2, RoundingMode.HALF_UP));
                entry.put("totalDurationFormatted", formatDuration(dayTotalDuration));
                entry.put("totalDurationSeconds", dayTotalDuration);
                entry.put("statusStats", buildStatusStats(dayStatusDuration, dayTotalDuration));
                detailList.add(entry);
            } else if ("3".equals(type)) {
                // type=3(月): 按月聚合
                monthAggMap.computeIfAbsent(periodKey, k -> {
                    Map<String, Object> m = new LinkedHashMap<>();
                    m.put("date", k);
                    m.put("totalKwh", BigDecimal.ZERO);
                    m.put("totalDurationSeconds", 0L);
                    m.put("statusDurationMap", initStatusMap());
                    return m;
                });
                Map<String, Object> monthEntry = monthAggMap.get(periodKey);
                monthEntry.put("totalKwh", ((BigDecimal) monthEntry.get("totalKwh")).add(dayKwh));
                monthEntry.put("totalDurationSeconds", (Long) monthEntry.get("totalDurationSeconds") + dayTotalDuration);
                @SuppressWarnings("unchecked")
                Map<Integer, Long> mStatusMap = (Map<Integer, Long>) monthEntry.get("statusDurationMap");
                for (int sk = 0; sk <= 3; sk++) {
                    if (dayStatusDuration.get(sk) > 0) {
                        mStatusMap.merge(sk, dayStatusDuration.get(sk), Long::sum);
                    }
                }
            }
        }

        // type=3 构建月度聚合结果
        if ("3".equals(type) && monthAggMap != null) {
            for (Map.Entry<String, Map<String, Object>> me : monthAggMap.entrySet()) {
                Map<String, Object> m = me.getValue();
                long mDur = (Long) m.get("totalDurationSeconds");
                @SuppressWarnings("unchecked")
                Map<Integer, Long> mStatusMap = (Map<Integer, Long>) m.get("statusDurationMap");

                Map<String, Object> entry = new LinkedHashMap<>();
                entry.put("date", m.get("date"));
                entry.put("totalKwh", ((BigDecimal) m.get("totalKwh")).setScale(2, RoundingMode.HALF_UP));
                entry.put("totalDurationFormatted", formatDuration(mDur));
                entry.put("totalDurationSeconds", mDur);
                entry.put("statusStats", buildStatusStats(mStatusMap, mDur));
                detailList.add(entry);
            }
        }

        List<Map<String, Object>> allStatusStats = buildStatusStats(statusDurationAllMap, totalDurationAll);

        return Map.of(
                "code", 200,
                "msg", "请求成功",
                "dtuSn", dtuSn,
                "type", type,
                "detailList", detailList,
                "summary", Map.of(
                        "totalDurationFormatted", formatDuration(totalDurationAll),
                        "totalDurationSeconds", totalDurationAll,
                        "totalKwh", totalKwhAll.setScale(2, RoundingMode.HALF_UP),
                        "statusStats", allStatusStats
                )
        );
    }

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

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

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

    /**
     * 格式化时长为 xx时xx分xx秒 格式
     * 时不为0则展示xx时xx分xx秒
     * 时和分都为0则只展示xx秒
     */
    private static String formatDuration(long totalSeconds) {
        long h = totalSeconds / 3600;
        long m = (totalSeconds % 3600) / 60;
        long s = totalSeconds % 60;
        StringBuilder sb = new StringBuilder();
        if (h > 0) sb.append(h).append("时");
        if (h > 0 || m > 0) sb.append(m).append("分");
        sb.append(s).append("秒");
        return sb.toString();
    }
}