package com.iot.scheduler.service;

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