package com.hwtd.mes.collect.service.impl;

import com.hwtd.mes.collect.dto.DatabaseDTO;
import com.hwtd.mes.collect.service.DataCollectionService;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;

@Service
@Slf4j
public class DataCollectionServiceImpl implements DataCollectionService {

    /**
     * 处理mdb数据库排除字段类型
     */
    private final static List<String> MDB_EXCLUDE_TYPES = Arrays.asList("java.sql.Blob");

    @Override
    public List<Map<String, Object>> getAccessData(DatabaseDTO databaseDTO) {
        List<Map<String,Object>> list = new ArrayList<>();
        try{
            Properties prop = new Properties();
            //设置编码
            prop.put("charSet", "UTF-8");
            prop.put("user",  StringUtils.isNotBlank(databaseDTO.getUserName())?databaseDTO.getUserName():"");
            prop.put("password", StringUtils.isNotBlank(databaseDTO.getPassword())?databaseDTO.getPassword():"");
            //数据地址
            String dbUrl = "jdbc:ucanaccess://" + databaseDTO.getFilePath();
            //引入驱动
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver").newInstance();
            Connection conn = null;
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            //连接数据库资源
            conn = DriverManager.getConnection(dbUrl, prop);
            try {
                //遍历获取多张表数据
                String s = "SELECT "+databaseDTO.getPointColumns()+" FROM " + databaseDTO.getTableName() + " WHERE 1=1";
                if(StringUtils.isNotBlank(databaseDTO.getMainColumn()) && StringUtils.isNotBlank(databaseDTO.getBatchCode())){
                    s+=" and " + databaseDTO.getMainColumn() + " = '" + databaseDTO.getBatchCode()+ "'";
                }
                if(StringUtils.isNotBlank(databaseDTO.getOrderColumn())){
                    String orderRule = StringUtils.isNotBlank(databaseDTO.getOrderRule())?databaseDTO.getOrderRule():"ASC";
                    s+=" ORDER BY " + databaseDTO.getOrderColumn() + " " + orderRule;
                }
                preparedStatement = conn.prepareStatement(s);
                rs = preparedStatement.executeQuery();
                ResultSetMetaData data = rs.getMetaData();
                while (rs.next()) {
                    Map<String, Object> map = new HashMap<>();
                    for (int i = 1; i <= data.getColumnCount(); i++) {
                        //列名
                        String columnName = data.getColumnName(i);
                        //列字段类型
                        String columnClassName = data.getColumnClassName(i);
                        Object columnValue = null;
                        if(!MDB_EXCLUDE_TYPES.contains(columnClassName)){
                            columnValue = rs.getObject(i);
                        }
                        map.put(columnName, columnValue);
                    }
                    list.add(map);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                closeA1l(conn, preparedStatement, rs);
            }
            return list;
        }catch (Exception e){
            throw new RuntimeException("Access数据库采集异常："+e.getMessage());
        }
    }

    @Override
    public List<Map<String, Object>> getPostgreSqlData(DatabaseDTO databaseDTO) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        try{
            String dbName = databaseDTO.getDatabaseName();
            String user = databaseDTO.getUserName();
            String password = databaseDTO.getPassword();
            // 从 GetFileDto 获取数据表名，对应【数据库表名】字段
            String table = databaseDTO.getTableName();
            // 检查数据库名和表名是否为空
            if (dbName == null || dbName.isEmpty() || table == null || table.isEmpty()) {
                throw new RuntimeException("数据库名或表名不能为空");
            }
            // 数据库连接信息
            String url = String.format("jdbc:postgresql://%s:%s/%s",databaseDTO.getIpAddress(),databaseDTO.getServerPort(),dbName);
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;

            HikariConfig config = new HikariConfig();
            config.setJdbcUrl(url);
            config.setUsername(user);
            config.setPassword(password);
            config.setMaximumPoolSize(10);
            config.setConnectionTimeout(30000);

            HikariDataSource ds = new HikariDataSource(config);
            try {
                // 建立连接
                connection = ds.getConnection();
                // 构建基础 SQL
                String sql = "SELECT "+databaseDTO.getPointColumns()+" FROM "+table+" WHERE 1=1";
                if(StringUtils.isNotBlank(databaseDTO.getMainColumn()) && StringUtils.isNotBlank(databaseDTO.getBatchCode())){
                    sql+=" AND (" + databaseDTO.getMainColumn() + " = TRIM('" + databaseDTO.getBatchCode()+ "')";
                }
                if(StringUtils.isNotBlank(databaseDTO.getOrderColumn())){
                    String orderRule = StringUtils.isNotBlank(databaseDTO.getOrderRule())?databaseDTO.getOrderRule():"ASC";
                    sql+=" ORDER BY " + databaseDTO.getOrderColumn() + " " + orderRule;
                }
                // 创建 PreparedStatement 对象执行 SQL
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                // 遍历结果集获取数据
                while (resultSet.next()) {
                    Map<String, Object> rowData = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        rowData.put(columnName, resultSet.getObject(i));
                    }
                    dataList.add(rowData);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                closeA1l(connection, preparedStatement, resultSet);
            }
            return dataList;
        }catch (Exception e){
            throw new RuntimeException("PostgreSql数据库采集异常："+e.getMessage());
        }
    }

    private static void closeA1l(Connection conn, PreparedStatement preparedStatement, ResultSet rs) {
        try {
            if (null != rs) {
                rs.close();
            }
            if (null != preparedStatement) {
                preparedStatement.close();
            }
            if (null != conn) {
                conn.close();
            }
        } catch (Exception ignore) {
        }
    }

}