package com.iot.scheduler.service.chizhou;

import com.alibaba.fastjson.JSON;
import com.iot.scheduler.utils.MqttUtils;
import com.iot.scheduler.utils.SqlTypedValueUtils;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * 池州经开区数据上传
 */
@Slf4j
@Service
public class CzDeviceReportService {

    String broker = "112.30.143.137:5007";
    String username = "admin";
    String password = "ly@666yc";

    String jdbcUrl = "jdbc:postgresql://192.168.0.249:5432/iot";
    String jdbcUserName = "postgres";
    String jdbcPassword = "1qaz@WSX";
    String selectSql = "    SELECT\n" +
            "      de.name AS 设备名称,\n" +
            "      CASE\n" +
            "        WHEN ak2.long_v IS NULL OR (ak.bool_v = FALSE AND ak2.long_v IS NOT NULL) THEN\n" +
            "          'OFF'\n" +
            "        WHEN tkl.long_v = 1 THEN\n" +
            "          'ERROR'\n" +
            "        WHEN tkl2.long_v = 1 THEN\n" +
            "          'STAND'\n" +
            "        WHEN tkl3.long_v = 1 THEN\n" +
            "          'RUN'\n" +
            "        ELSE\n" +
            "          'OFF'\n" +
            "      END AS 设备状态,\n" +
            "      dc.credentials_id AS sn,\n" +
            "      de.organization_id, \n" +
            "      de.device_profile_id, \n" +
            "      de.id \n" +
            "    FROM\n" +
            "      device de\n" +
            "      LEFT JOIN ts_kv_latest tkl ON de.id = tkl.entity_id\n" +
            "      AND tkl.KEY = '60'\n" +
            "      LEFT JOIN ts_kv_latest tkl2 ON de.id = tkl2.entity_id\n" +
            "      AND tkl2.KEY = '59'\n" +
            "      LEFT JOIN ts_kv_latest tkl3 ON de.id = tkl3.entity_id\n" +
            "      AND tkl3.KEY = '57'\n" +
            "      LEFT JOIN attribute_kv ak ON de.id = ak.entity_id\n" +
            "      AND ak.attribute_type = 2\n" +
            "      AND ak.attribute_key = 41\n" +
            "      LEFT JOIN attribute_kv ak2 ON de.id = ak2.entity_id\n" +
            "      AND ak2.attribute_type = 2\n" +
            "      AND ak2.attribute_key = 43\n" +
            "      LEFT JOIN device_credentials dc ON dc.device_id = de.id\n" +
            "    WHERE\n" +
            "      de.device_profile_id = '5f39ebe0-f6cf-11f0-bebc-e5e3c6471a90' UNION ALL\n" +
            "    SELECT\n" +
            "      de.name AS 设备名称,\n" +
            "      CASE\n" +
            "        WHEN ak2.long_v IS NULL\n" +
            "          OR (ak.bool_v = FALSE AND ak2.long_v IS NOT NULL) THEN\n" +
            "          'OFF'\n" +
            "        ELSE\n" +
            "          'RUN'\n" +
            "      END AS 设备状态,\n" +
            "      dc.credentials_id AS sn,\n" +
            "      de.organization_id, \n" +
            "      de.device_profile_id, \n" +
            "      de.id \n" +
            "    FROM\n" +
            "      device de\n" +
            "      LEFT JOIN attribute_kv ak ON de.id = ak.entity_id\n" +
            "      AND ak.attribute_type = 2\n" +
            "      AND ak.attribute_key = 41\n" +
            "      LEFT JOIN attribute_kv ak2 ON de.id = ak2.entity_id\n" +
            "      AND ak2.attribute_type = 2\n" +
            "      AND ak2.attribute_key = 43\n" +
            "      LEFT JOIN device_credentials dc ON dc.device_id = de.id\n" +
            "    WHERE\n" +
            "      de.device_profile_id = 'c2401630-ffec-11f0-926f-2f3182abc65f' UNION ALL\n" +
            "    SELECT\n" +
            "      de.name AS 设备名称,\n" +
            "      CASE\n" +
            "        WHEN ak2.long_v IS NULL\n" +
            "          OR (ak.bool_v = FALSE AND ak2.long_v IS NOT NULL) THEN\n" +
            "          'OFF'\n" +
            "        ELSE\n" +
            "          'RUN'\n" +
            "      END AS 设备状态,\n" +
            "      dc.credentials_id AS sn,\n" +
            "      de.organization_id, \n" +
            "      de.device_profile_id, \n" +
            "      de.id \n" +
            "    FROM\n" +
            "      device de\n" +
            "      LEFT JOIN attribute_kv ak ON de.id = ak.entity_id\n" +
            "      AND ak.attribute_type = 2\n" +
            "      AND ak.attribute_key = 41\n" +
            "      LEFT JOIN attribute_kv ak2 ON de.id = ak2.entity_id\n" +
            "      AND ak2.attribute_type = 2\n" +
            "      AND ak2.attribute_key = 43\n" +
            "      LEFT JOIN device_credentials dc ON dc.device_id = de.id\n" +
            "    WHERE\n" +
            "      de.device_profile_id = '4e404b10-ffe7-11f0-926f-2f3182abc65f'";

    public void deviceReport() {
//        List<String> deviceIdList = Arrays.asList("TCKJ-001", "TCKJ-002", "TCKJ-003", "TCKJ-004", "TCKJ-005", "TCKJ-006",
//                "TCKJ-007", "TCKJ-008", "TCKJ-009", "TCKJ-010", "TCKJ-011", "TCKJ-012", "TCKJ-013", "TCKJ-014", "TCKJ-015",
//                "TCKJ-016", "TCKJ-017", "TCKJ-018", "TCKJ-019", "TCKJ-020", "TCKJ-021", "TCKJ-022", "TCKJ-023", "TCKJ-024",
//                "TCKJ-025", "TCKJ-026", "TCKJ-027", "TCKJ-028", "TCKJ-029", "TCKJ-030", "TCKJ-031", "TCKJ-032", "TCKJ-033",
//                "TCKJ-034", "TCKJ-035", "TCKJ-036", "TCKJ-037", "TCKJ-038", "TCKJ-039", "TCKJ-040", "TCKJ-041",
//                "vJAzZWk1Q2I8cdq7kx3z", "LmiW3ljI4KP487CeMmol", "ceK7YSNLrTjVSSPaFmn3", "6KePBYyCiEgo10iJXIrF",
//                "9vrxIQhbaZ542DvCVne0", "XMLN-001", "XMLN-002", "XMLN-003", "XMLN-004", "XMLN-005", "XMLN-006", "XMLN-007",
//                "XMLN-008", "XMLN-009", "XMLN-010", "XMLN-011", "XMLN-012", "XMLN-013", "XMLN-014", "XMLN-015", "XMLN-016",
//                "XMLN-017", "XMLN-018", "XMLN-019", "XMLN-020", "XMLN-021", "XMLN-022", "XMLN-023", "XMLN-024", "XMLN-025",
//                "XMLN-026", "XMLN-027", "XMLN-028", "XMLN-029", "XMLN-030", "XMLN-031", "XMLN-032", "XMLN-033", "XMLN-034",
//                "XMLN-035", "XMLN-036", "XMLN-037", "XMLN-038", "XMLN-039", "XMLN-040", "XMLN-041", "XMLN-042", "XMLN-043",
//                "XMLN-044", "XMLN-045", "XMLN-046", "XMLN-047", "XMLN-048", "XMLN-049", "XMLN-050", "XMLN-051", "XMLN-052",
//                "GYDPF-014", "GYDPF-006", "KBS-1-1", "KBS-1-2", "KBS-3-1", "KBS-3-5", "KBS-3-3", "CS-001", "uzGf4mjzkOPwGO4aCOf3",
//                "GYDPF-001", "GYDPF-010", "KBS-1-5", "KBS-2-2", "KBS-3-7", "CS-003", "LBKXrYxLQN9W7qmmqzp5", "rba7VggnqGdNyPvVNs1n",
//                "GYDPF-002", "GYDPF-016", "GYDPF-017", "KBS-1-6", "EqFMlJarb2aDwpwvUi6J", "x2173A2HKOiPqeRsKtWY", "GYDPF-005",
//                "KBS-1-3", "KBS-2-1", "KBS-3-4", "1fzR6X57k2aafbbkH4po", "lbqMr5pmkNJLWEPSa0yp", "mYz1kUajPOROg0Ewcfhr",
//                "6TLlfKBbTMaUZGgHtvuH", "XTE8PKwLSYp29gE1umfW", "MqYu4y0OycHQkzZhothe", "IFeN0lAXlAVQ1fD7gwk1", "hZb9NsRCqtVBSagFqnPS",
//                "r22r53PNgY7TeqCK5y14", "KCGSwfTvGCtBfd0qsH63", "4KzFQCncmwFh7UT4vbHp", "GYDPF-007", "KBS-3-2", "t2kkUTZYqxmKLN4X4VPB",
//                "9vWKALGoat3XalEtQaRB", "vYtwLMkppPbM3CtB3U9U", "4vwTfMqi8oEtWxdEUrE8", "vVvK2cTLvjYtZX1dksyd", "GYDPF-003",
//                "KBS-3-8", "2E6eUu66qxl5Llr6ib5v", "iWbeBfwg36lIpBRMDnvO", "GYDPF-015", "KBS-2-4", "OSbA7X7FGETF0TXub4OS",
//                "GYDPF-008", "KBS-2-3", "KBS-2-7", "pRhGGECbXu6FBshyg2yg", "zrVVNRo8QIbjeYjdoCUq", "fjPj3oHW7cL5m0AE1t3y",
//                "8dJsgqjmxIIduP4Sr2rL", "GYDPF-012", "KBS-1-4", "JZ8Tbzye8rKotYTbHUJw", "dwToSYEWr04sPtBUxaOH", "GYDPF-009",
//                "oe3OmagW5Wi9yw9LfUr3", "2mDiH94D7uSh4CtD2bi0", "ceGyDxlK7xzMoUacIwCv", "gxECiKjMEsVkYyowKIMd", "irAqF3fmzaM5GIo8ZLAx",
//                "GYDPF-013", "KBS-2-5", "KBS-3-6", "dxPdbg2UqqYADPMy9TVe", "cjFUpeVPTOh4Uf6wnVH1", "VR5XgpqhFWGMcwdy8yPQ",
//                "GYDPF-004", "KBS-1-7", "KBS-2-8", "HEXmiSz0sXsahRHV2bQm", "0lIIGWP49Xuo91TnTvVx", "C99WsNGFSGQSV4n3YmrW",
//                "3paJSiHK32wvVM6f1tO8", "GYDPF-011", "KBS-1-8", "KBS-2-6", "yrk5Cxa6EJncWX2kTywa", "xOxHHscmrEBhLTlmyRrF",
//                "e3HzLoIl0p3iEM860EdK");

        List<Object> needSyncDataList = initConnectAndSelectData();
        log.info("总设备数量: {}台", needSyncDataList.size());

        Map<String, String> organizeIdAndClientIdMap = new HashMap<>(3);
        organizeIdAndClientIdMap.put("63934b6f-1e02-4d29-ac14-1a64649e2231", "2020672119054331904"); // 安徽鑫米兰电子科技有限公司
        organizeIdAndClientIdMap.put("35bcdb94-31ec-4750-9ee9-cc855aa66e17", "2020672015207559169"); // 安徽同池科技有限公司
        organizeIdAndClientIdMap.put("1697500a-dc11-45cc-88f5-2ad47472a9bb", "2020672228886376448"); // 凯盛信息显示材料（池州）有限公司

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String formattedDate = sdf.format(new Date());
        log.info("上报时间统一为: {}", formattedDate);

        // 用于统计上报结果
        int successCount = 0;
        int failCount = 0;
        List<String> failedDevices = new ArrayList<>();
        for (int index = 0; index < needSyncDataList.size(); index++) {
            Object needSyncData = needSyncDataList.get(index);
            List<Object> dataList = (ArrayList) needSyncData;
            String deviceId = dataList.get(2).toString();
            String status = dataList.get(1).toString();
            String organizeId = dataList.get(3).toString();
            String deviceProfileId = dataList.get(4).toString();
            String id = dataList.get(5).toString();

            // 根据index确定clientId
            String clientId = organizeIdAndClientIdMap.get(organizeId);
            String topic = MessageFormat.format("/{0}/{1}/properties/report", clientId, deviceId);

            // 获取设备状态
            int deviceState = getDeviceState(status);

            // 计算当日用电量和用水量
            double electricity = calculateDailyElectricity(id, deviceProfileId);
            double water = calculateDailyWater(id, deviceProfileId);

            Map<String, Object> properties = new HashMap<>(5);
            properties.put("type", 1);
            properties.put("state", deviceState);
            properties.put("time", formattedDate);
            properties.put("output", 0); // 当日产量，还未确定，暂定为0
            properties.put("energy", 0); // 当日能耗，还未确定，暂定为0
            properties.put("electricity", electricity); // 当日用电量
            properties.put("water", water); // 当日用水量

            Map<String, Object> deviceMap = new HashMap<>(2);
            deviceMap.put("deviceId", deviceId);
            deviceMap.put("properties", properties);

            // 记录调试信息
//            if (log.isDebugEnabled()) {
//                log.debug("准备上报设备信息: deviceId={}, clientId={}, state={}, topic={}",
//                        deviceId, clientId, deviceState, topic);
//            }

            try {
                log.debug("开始MQTT发布: deviceId={}", deviceId);
                MqttUtils.publish(broker, topic, username, password, clientId, JSON.toJSONString(deviceMap));
                successCount++;

                // 每10个设备记录一次进度
//                if ((index + 1) % 10 == 0) {
//                    log.info("已处理 {} 个设备，当前处理: {}", index + 1, deviceId);
//                }

            } catch (Exception e) {
                failCount++;
                failedDevices.add(deviceId);
                log.error("设备上报失败: deviceId={}, clientId={}, topic={}, 错误信息: {}",
                        deviceId, clientId, topic, e.getMessage());
                log.error("详细异常信息:", e);
            }

            // 可选：添加延迟，避免发送过快
            try {
                Thread.sleep(10); // 10毫秒延迟
            } catch (InterruptedException e) {
                log.warn("线程休眠被中断", e);
                Thread.currentThread().interrupt();
            }
        }

        // 任务完成，输出统计信息
        log.info("========== 设备状态上报任务完成 ==========");
        log.info("成功上报: {}台", successCount);
        log.info("失败上报: {}台", failCount);

        if (failCount > 0) {
            log.warn("失败设备列表: {}", failedDevices);
        }

        // 任务执行耗时
        log.info("========== 任务执行结束 ==========");
    }

    private int getDeviceState(String status) {
        int deviceState;
        if ("RUN".equals(status)) {
            deviceState = 1;
        } else if ("STAND".equals(status)) {
            deviceState = 2;
        } else if ("OFF".equals(status)) {
            deviceState = 3;
        } else {
            deviceState = 4;
        }

        return deviceState;
    }

    /**
     * 计算当日用电量
     * deviceProfileId = c2401630-ffec-11f0-926f-2f3182abc65f，key=64，读取dbl_v
     * @param deviceId 设备ID（entity_id）
     * @param deviceProfileId 设备配置ID
     * @return 当日用电量
     */
    private double calculateDailyElectricity(String deviceId, String deviceProfileId) {
        // 非用电设备类型，返回0
        if (!"c2401630-ffec-11f0-926f-2f3182abc65f".equals(deviceProfileId)) {
            return 0;
        }

        String sql = "SELECT " +
                "  (SELECT COALESCE(dbl_v, 0) FROM ts_kv " +
                "   WHERE entity_id = ? AND key = 64 AND ts >= ? AND ts <= ? AND dbl_v IS NOT NULL " +
                "   ORDER BY ts ASC LIMIT 1) AS first_value, " +
                "  (SELECT COALESCE(dbl_v, 0) FROM ts_kv " +
                "   WHERE entity_id = ? AND key = 64 AND ts >= ? AND ts <= ? AND dbl_v IS NOT NULL " +
                "   ORDER BY ts DESC LIMIT 1) AS last_value";

        return calculateDailyConsumption(deviceId, sql, "用电量");
    }

    /**
     * 计算当日用水量
     * deviceProfileId = 4e404b10-ffe7-11f0-926f-2f3182abc65f，key=83，读取dbl_v或long_v
     * @param deviceId 设备ID（entity_id）
     * @param deviceProfileId 设备配置ID
     * @return 当日用水量
     */
    private double calculateDailyWater(String deviceId, String deviceProfileId) {
        // 非用水设备类型，返回0
        if (!"4e404b10-ffe7-11f0-926f-2f3182abc65f".equals(deviceProfileId)) {
            return 0;
        }

        String sql = "SELECT " +
                "  (SELECT COALESCE(dbl_v, long_v, 0) FROM ts_kv " +
                "   WHERE entity_id = ? AND key = 83 AND ts >= ? AND ts <= ? " +
                "   AND (dbl_v IS NOT NULL OR long_v IS NOT NULL) " +
                "   ORDER BY ts ASC LIMIT 1) AS first_value, " +
                "  (SELECT COALESCE(dbl_v, long_v, 0) FROM ts_kv " +
                "   WHERE entity_id = ? AND key = 83 AND ts >= ? AND ts <= ? " +
                "   AND (dbl_v IS NOT NULL OR long_v IS NOT NULL) " +
                "   ORDER BY ts DESC LIMIT 1) AS last_value";

        return calculateDailyConsumption(deviceId, sql, "用水量");
    }

    /**
     * 执行能耗计算查询
     * @param deviceId 设备ID
     * @param sql 查询SQL
     * @param logName 日志名称
     * @return 当日消耗量
     */
    private double calculateDailyConsumption(String deviceId, String sql, String logName) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        HikariDataSource dataSource = null;

        try {
            HikariConfig config = new HikariConfig();
            config.setJdbcUrl(jdbcUrl);
            config.setUsername(jdbcUserName);
            config.setPassword(jdbcPassword);
            config.setDriverClassName("org.postgresql.Driver");
            config.setMaximumPoolSize(5);
            config.setMinimumIdle(5);
            config.setConnectionTimeout(60000);
            config.setConnectionTestQuery("SELECT 1");

            dataSource = new HikariDataSource(config);
            connection = dataSource.getConnection();

            // 获取当天开始和结束时间戳（毫秒）
            Calendar calendar = Calendar.getInstance();
            calendar.set(Calendar.HOUR_OF_DAY, 0);
            calendar.set(Calendar.MINUTE, 0);
            calendar.set(Calendar.SECOND, 0);
            calendar.set(Calendar.MILLISECOND, 0);
            long startOfDay = calendar.getTimeInMillis();
            calendar.set(Calendar.HOUR_OF_DAY, 23);
            calendar.set(Calendar.MINUTE, 59);
            calendar.set(Calendar.SECOND, 59);
            calendar.set(Calendar.MILLISECOND, 999);
            long endOfDay = calendar.getTimeInMillis();

            statement = connection.prepareStatement(sql);
            statement.setObject(1, deviceId, java.sql.Types.OTHER);
            statement.setLong(2, startOfDay);
            statement.setLong(3, endOfDay);
            statement.setObject(4, deviceId, java.sql.Types.OTHER);
            statement.setLong(5, startOfDay);
            statement.setLong(6, endOfDay);

            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                double firstValue = resultSet.getDouble("first_value");
                double lastValue = resultSet.getDouble("last_value");
                double consumption = lastValue - firstValue;
                log.debug("设备 {} 当日{}计算: 首值={}, 末值={}, 消耗={}", deviceId, logName, firstValue, lastValue, consumption);
                return Math.max(consumption, 0); // 确保非负
            }
        } catch (SQLException e) {
            log.error("查询设备 {} 当日{}失败: {}", deviceId, logName, e.getMessage(), e);
        } catch (Exception e) {
            log.error("计算设备 {} 当日{}时发生异常", deviceId, logName, e);
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                log.error("关闭数据库资源时发生异常", e);
            }
            if (dataSource != null) {
                try {
                    dataSource.close();
                } catch (Exception e) {
                    log.error("关闭HikariDataSource连接池时发生异常", e);
                }
            }
        }
        return 0;
    }

    private List<Object> initConnectAndSelectData() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        HikariDataSource dataSource = null;
        List<Object> resultList = new ArrayList<>();

        log.info("开始连接数据库，URL: {}", jdbcUrl);

        try {
            HikariConfig config = new HikariConfig();
            config.setJdbcUrl(jdbcUrl);
            config.setUsername(jdbcUserName);
            config.setPassword(jdbcPassword);
            config.setDriverClassName("org.postgresql.Driver");
            config.setMaximumPoolSize(5);
            config.setMinimumIdle(5);
            config.setConnectionTimeout(60000);
            config.setConnectionTestQuery("SELECT 1");

            dataSource = new HikariDataSource(config);
            log.info("Hikari连接池配置完成");

            connection = dataSource.getConnection();
            log.info("数据库连接成功");

            statement = connection.prepareStatement(selectSql);
            log.info("执行SQL查询: {}", selectSql);

            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            log.info("查询结果集元数据获取成功，共{}列", columnCount);

            int rowCount = 0;
            while (resultSet.next()) {
                List<Object> result = new ArrayList<>(columnCount);
                for (int index = 1; index <= columnCount; index++) {
                    int columnType = metaData.getColumnType(index);
                    Object value = SqlTypedValueUtils.getTypedValue(resultSet, index, columnType);
                    result.add(value);
                }
                resultList.add(result);
                rowCount++;

                // 每处理1000行记录一次日志
                if (rowCount % 1000 == 0) {
                    log.info("已处理{}行数据", rowCount);
                }
            }

            log.info("数据查询完成，共获取{}行数据", rowCount);

        } catch (SQLException e) {
            log.error("数据库操作异常，URL: {}, 用户名: {}", jdbcUrl, jdbcUserName, e);
        } catch (Exception e) {
            log.error("初始化数据库连接或查询数据时发生异常", e);
        } finally {
            // 释放资源
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
                log.info("数据库连接资源已释放");
            } catch (SQLException e) {
                log.error("关闭数据库资源时发生异常", e);
            }

            if (dataSource != null) {
                try {
                    dataSource.close();
                    log.info("HikariDataSource连接池已关闭");
                } catch (Exception e) {
                    log.error("关闭HikariDataSource连接池时发生异常", e);
                }
            }
        }

        log.info("数据库操作完成，返回{}条记录", resultList.size());
        return resultList;
    }
}