package com.iot.scheduler.service;

import com.alibaba.fastjson.JSON;
import com.iot.scheduler.model.XkdDeviceData;
import com.iot.scheduler.model.XkdDeviceInfo;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.http.Consts;
import org.apache.http.HttpEntity;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.ContentType;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.Date;

/**
 * 新康达设备属性上报
 */
@Service
@Slf4j
public class XinkangdaDeviceReportService {

    @Value("${xkd.third.devicesRegisterUrl:http://117.64.210.63:8087/api/v1/devices/register}")
    private String devicesRegisterUrl;

    @Value("${xkd.third.dataUploadUrl:http://117.64.210.63:8087/api/v1/data/upload}")
    private String dataUploadUrl;

    @Value("${xkd.third.enterpriseId:PLMOKNJIB8}")
    private String enterpriseId;

    @Value("${xkd.third.apiKey:UiOpAsDfGhJkLzXcVbNmQwEr6TyUiOpAsD}")
    private String apiKey;

    @Value("${xkd.third.jdbcUrl:jdbc:postgresql://192.168.0.61:5433/thingskit}")
    private String jdbcUrl;

    @Value("${xkd.third.jdbcUserName:postgres}")
    private String jdbcUserName;

    @Value("${xkd.third.jdbcPassword:postgres}")
    private String jdbcPassword;

    @Value("${xkd.third.selectSql:SELECT * FROM ts_kv_dictionary;}")
    private String selectSql;


    /**
     * 批量企业设备上报
     */
    public void batchDeviceReport() {
        List<Object> needSyncDataList = initConnectAndSelectData();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");
        String dataBatch = simpleDateFormat.format(new Date());

        ZonedDateTime now = ZonedDateTime.now();
        String iso8601 = now.format(DateTimeFormatter.ISO_OFFSET_DATE_TIME);
        for (int i = 0; i < needSyncDataList.size(); i++) {
            Object needSyncData = needSyncDataList.get(i);
            try {
                List<Object> dataList = (ArrayList) needSyncData;
                String deviceId = dataList.get(0).toString();

                log.info("开始处理设备[{}]的数据，进度: {}/{}", deviceId, i + 1, needSyncDataList.size());

                XkdDeviceData data = new XkdDeviceData();
                data.setDeviceId(deviceId);
                String status = dataList.get(2).toString();
                data.setStatus(status);
                data.setDataBatch(dataBatch);
                data.setCollectionTime(iso8601);
                data.setAlarm(false);
                data.setAlarmName("无");
                data.setEfficiency(86.7D);

                boolean sendResult = sendDataUpload(data);
            } catch (Exception e) {

            }
        }
    }

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

    private Object getTypedValue(ResultSet rs, int index, int sqlType) throws SQLException {
        Object value;

        try {
            switch (sqlType) {
                case Types.BIT:
                case Types.BOOLEAN:
                    value = rs.getBoolean(index);
                    return rs.wasNull() ? null : value;

                case Types.TINYINT:
                case Types.SMALLINT:
                case Types.INTEGER:
                    value = rs.getInt(index);
                    return rs.wasNull() ? null : value;

                case Types.BIGINT:
                    value = rs.getLong(index);
                    return rs.wasNull() ? null : value;

                case Types.FLOAT:
                case Types.REAL:
                    value = rs.getFloat(index);
                    return rs.wasNull() ? null : value;

                case Types.DOUBLE:
                    value = rs.getDouble(index);
                    return rs.wasNull() ? null : value;

                case Types.NUMERIC:
                case Types.DECIMAL:
                    value = rs.getBigDecimal(index);
                    return rs.wasNull() ? null : value;

                case Types.CHAR:
                case Types.VARCHAR:
                case Types.LONGVARCHAR:
                case Types.NCHAR:
                case Types.NVARCHAR:
                case Types.LONGNVARCHAR:
                    value = rs.getString(index);
                    return rs.wasNull() ? null : value;

                case Types.DATE:
                    java.sql.Date date = rs.getDate(index);
                    return date != null ? date.toLocalDate() : null;

                case Types.TIME:
                    Time time = rs.getTime(index);
                    return time != null ? time.toLocalTime() : null;

                case Types.TIMESTAMP:
                    Timestamp timestamp = rs.getTimestamp(index);
                    return timestamp != null ? timestamp.toLocalDateTime() : null;

                case Types.BINARY:
                case Types.VARBINARY:
                case Types.LONGVARBINARY:
                    value = rs.getBytes(index);
                    return rs.wasNull() ? null : value;

                case Types.BLOB:
                    value = rs.getBlob(index);
                    return rs.wasNull() ? null : value;

                case Types.CLOB:
                    value = rs.getClob(index);
                    return rs.wasNull() ? null : value;

                default:
                    value = rs.getObject(index);
                    return rs.wasNull() ? null : value;
            }
        } catch (SQLException e) {
            log.error("获取结果集第{}列数据时发生异常，数据类型: {}", index, sqlType, e);
            throw e;
        }
    }

//    private void dataUpload(XkdDeviceInfo info) {
//        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");
//        String dataBatch = simpleDateFormat.format(new Date());
//        XkdDeviceData data = new XkdDeviceData();
//        data.setDeviceId(info.getDeviceId());
//        data.setDataBatch(dataBatch);
//        ZonedDateTime now = ZonedDateTime.now();
//        String iso8601 = now.format(DateTimeFormatter.ISO_OFFSET_DATE_TIME);
//        data.setCollectionTime(iso8601);
//        data.setStatus("RUN");
//        data.setAlarm(false);
//        data.setAlarmName("无");
//        data.setEfficiency(78D);
//
//        sendDataUpload(data);
//    }

    private boolean sendDataUpload(XkdDeviceData data) {
        String deviceId = data.getDeviceId();
        log.info("开始上报设备[{}]的数据，请求URL: {}", deviceId, dataUploadUrl);

        HttpPost httpPost = new HttpPost(dataUploadUrl);
        CloseableHttpClient httpClient = HttpClients.createDefault();
        String requestBody = JSON.toJSONString(data);

        log.info("设备[{}]上报请求体: {}", deviceId, requestBody);

        StringEntity entity = new StringEntity(requestBody, ContentType.create("application/json", Consts.UTF_8));
        httpPost.setEntity(entity);
        httpPost.setHeader("Content-Type", "application/json;charset=UTF-8");
        httpPost.setHeader("X-Enterprise-ID", enterpriseId);
        httpPost.setHeader("X-API-Key", apiKey);

        try (CloseableHttpResponse execute = httpClient.execute(httpPost)) {
            int statusCode = execute.getStatusLine().getStatusCode();

            HttpEntity res = execute.getEntity();
            String responseBody = "";
            if (res != null) {
                try (InputStream is = res.getContent();
                     ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()) {
                    byte[] buf = new byte[128];
                    int len;
                    while ((len = is.read(buf)) != -1) {
                        byteArrayOutputStream.write(buf, 0, len);
                    }
                    responseBody = byteArrayOutputStream.toString("UTF-8");
                }
            }

            log.info("设备[{}]上报响应，状态码: {}，响应内容: {}", deviceId, statusCode, responseBody);

            if (statusCode >= 200 && statusCode < 300) {
                return true;
            } else {
                log.error("设备[{}]上报失败，HTTP状态码: {}，响应: {}", deviceId, statusCode, responseBody);
                return false;
            }

        } catch (IOException e) {
            log.error("设备[{}]上报请求发生IO异常", deviceId, e);
            return false;
        } finally {
            try {
                httpClient.close();
            } catch (IOException e) {
                log.error("关闭HTTP客户端时发生异常", e);
            }
        }
    }

    // 设备注册，只需要执行一次，本地执行完成
    public void devicesRegister() {
        String filePath = "C:\\Users\\PC\\Desktop\\xdkdevice.xlsx";
        try (InputStream in = new FileInputStream(new File(filePath));
             // 使用 WorkbookFactory 自动根据文件格式创建合适的 Workbook
             Workbook workbook = WorkbookFactory.create(in)) {
            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            for (int index = 1; index <= lastRowNum; index++) {
                Row row = sheet.getRow(index);
                XkdDeviceInfo info = new XkdDeviceInfo();
                info.setDeviceId(row.getCell(4).getStringCellValue());
                info.setDeviceName(row.getCell(3).getStringCellValue());
                info.setDeviceType(row.getCell(5).getStringCellValue());
                info.setLocation(row.getCell(1).getStringCellValue() + "-" + row.getCell(2).getStringCellValue());
                sendReportDevice(info);
//                dataUpload(info);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private boolean sendReportDevice(XkdDeviceInfo info) {
        String deviceId = info.getDeviceId();
        log.info("开始上报设备[{}]的数据，请求URL: {}", deviceId, devicesRegisterUrl);

        HttpPost httpPost = new HttpPost(devicesRegisterUrl);
        CloseableHttpClient httpClient = HttpClients.createDefault();
        String requestBody = JSON.toJSONString(info);

        log.info("设备[{}]上报请求体: {}", deviceId, requestBody);

        StringEntity entity = new StringEntity(requestBody, ContentType.create("application/json", Consts.UTF_8));
        httpPost.setEntity(entity);
        httpPost.setHeader("Content-Type", "application/json;charset=UTF-8");
        httpPost.setHeader("X-Enterprise-ID", enterpriseId);
        httpPost.setHeader("X-API-Key", apiKey);

        try (CloseableHttpResponse execute = httpClient.execute(httpPost)) {
            int statusCode = execute.getStatusLine().getStatusCode();

            HttpEntity res = execute.getEntity();
            String responseBody = "";
            if (res != null) {
                try (InputStream is = res.getContent();
                     ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()) {
                    byte[] buf = new byte[128];
                    int len;
                    while ((len = is.read(buf)) != -1) {
                        byteArrayOutputStream.write(buf, 0, len);
                    }
                    responseBody = byteArrayOutputStream.toString("UTF-8");
                }
            }

            log.info("设备[{}]上报响应，状态码: {}，响应内容: {}", deviceId, statusCode, responseBody);

            if (statusCode >= 200 && statusCode < 300) {
                return true;
            } else {
                log.error("设备[{}]上报失败，HTTP状态码: {}，响应: {}", deviceId, statusCode, responseBody);
                return false;
            }

        } catch (IOException e) {
            log.error("设备[{}]上报请求发生IO异常", deviceId, e);
            return false;
        } finally {
            try {
                httpClient.close();
            } catch (IOException e) {
                log.error("关闭HTTP客户端时发生异常", e);
            }
        }
    }
}