package cn.com.poc.common.utils;

import cn.com.yict.framemax.core.exception.BusinessException;
import cn.com.yict.framemax.data.model.PagingInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import cn.com.poc.common.domain.database.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author alex.yao
 * @date 2025/4/27
 */
public class DatabaseUtil {

    private static Logger logger = LoggerFactory.getLogger(DatabaseUtil.class);

    private static final String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";

    private static final int CONNECT_TIMEOUT = 30000; // 连接超时时间（毫秒）

    private static final int SOCKET_TIMEOUT = 30000; // socket超时时间（毫秒）

    private static final String SQL_TABLE_INFO = "SELECT " +
            "    TABLE_NAME ," +
            "    TABLE_COMMENT," +
            "    TABLE_ROWS," +
            "    (SELECT COUNT(*) " +
            "     FROM INFORMATION_SCHEMA.COLUMNS " +
            "     WHERE TABLE_SCHEMA = t.TABLE_SCHEMA " +
            "       AND TABLE_NAME = t.TABLE_NAME) AS TABLE_COLUMN " +
            "FROM INFORMATION_SCHEMA.TABLES t " +
            "WHERE TABLE_SCHEMA = ?";

    /**
     * 获取表结构
     */
    public static ResultSet getTableStructure(String host, int port, String username, String password, String database, String tableName) {
        String url = "jdbc:mysql://" + host + ":" + port + "/" + database
                + "?connectTimeout=" + CONNECT_TIMEOUT // 连接超时（毫秒）
                + "&socketTimeout=" + SOCKET_TIMEOUT; // socket超时（毫秒）

        String sql = "SELECT t.table_name," +
                "       c.column_name," +
                "        c.data_type, " +
                "        c.numeric_precision, " +
                "        ISNULL(CHARACTER_MAXIMUM_LENGTH,0) AS SIZE " +
                "FROM INFORMATION_SCHEMA.TABLES AS t " +
                "INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON" +
                "    (t.table_name = c.table_name) z" +
                "WHERE t.table_name = ? " +
                "ORDER BY t.table_name";
        try {
            Class.forName(DRIVER_NAME);
            Connection connection = DriverManager.getConnection(url, username, password);
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, tableName);
//            preparedStatement.setString(2, tableName);
            ResultSet resultSet = preparedStatement.executeQuery();
//            List<TableInfo> tableInfoList = new ArrayList<>();
//            while (resultSet.next()) {
//                TableInfo tableInfo = new TableInfo();
//                tableInfo.setTABLE_NAME(resultSet.getString("TABLE_NAME"));
//                tableInfo.setTABLE_COMMENT(resultSet.getString("TABLE_COMMENT"));
//                tableInfo.setTABLE_ROWS(resultSet.getInt("TABLE_ROWS"));
//                tableInfo.setTABLE_COLUMN(resultSet.getInt("TABLE_COLUMN"));
//                tableInfoList.add(tableInfo);
//            }
            connection.close();
            return resultSet;
        } catch (Exception e) {
            logger.error("连接数据库失败", e);
            return null;
        }
    }

    /**
     * 获取表数据
     */
//    public static List<List<String>> getTableData(String host, int port, String username, String password, String database, String tableName, int pageNum, int pageSize) {
//        String url = "jdbc:mysql://" + host + ":" + port + "/" + database
//                + "?connectTimeout=" + CONNECT_TIMEOUT // 连接超时（毫秒）
//                + "&socketTimeout=" + SOCKET_TIMEOUT; // socket超时（毫秒）
//        try {
//            Class.forName(DRIVER_NAME);
//            Connection connection = DriverManager.getConnection(url, username, password);
//            PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM " + "?" + " LIMIT " + (pageNum - 1) * pageSize + "," + pageSize);
//            preparedStatement.setString(1, tableName);
//            ResultSet resultSet = preparedStatement.executeQuery();
//            List<List<String>> dataList = new ArrayList<>();
//            while (resultSet.next()) {
//                int columnCount = resultSet.getMetaData().getColumnCount();
//                List<String> rowData = new ArrayList<>();
//                for (int i = 1; i <= columnCount; i++) {
//                    rowData.add(resultSet.getString(i));
//                }
//                dataList.add(rowData);
//            }
//            connection.close();
//            return dataList;
//        } catch (Exception e) {
//            logger.error("连接数据库失败", e);
//            throw new BusinessException(e);
//        }
//    }


    /**
     * 测试数据库连接
     *
     * @param host
     * @param port
     * @param username
     * @param password
     * @param database
     * @return
     */
    public static boolean testConnect(String host, int port, String username, String password, String database) {
        String url = "jdbc:mysql://" + host + ":" + port + "/" + database
                + "?connectTimeout=" + CONNECT_TIMEOUT // 连接超时（毫秒）
                + "&socketTimeout=" + SOCKET_TIMEOUT; // socket超时（毫秒）
        try {
            Class.forName(DRIVER_NAME);
            Connection connection = DriverManager.getConnection(url, username, password);
            connection.close();
            return true;
        } catch (Exception e) {
            logger.error("连接数据库失败", e);
            return false;
        }
    }

    /**
     * 获取表数据方法
     */
    public static TableDataResponse getTableData(String host, int port, String username, String password,
                                                 String database, String tableName, int pageNum, int pageSize) {
        String url = "jdbc:mysql://" + host + ":" + port + "/" + database
                + "?connectTimeout=" + CONNECT_TIMEOUT
                + "&socketTimeout=" + SOCKET_TIMEOUT;

        TableDataResponse response = new TableDataResponse();
        Connection connection = null;

        try {
            Class.forName(DRIVER_NAME);
            connection = DriverManager.getConnection(url, username, password);

            // 获取总行数
            PreparedStatement countStmt = connection.prepareStatement("SELECT COUNT(*) FROM " + tableName);
            ResultSet countRs = countStmt.executeQuery();
            countRs.next();
            int totalRows = countRs.getInt(1);

            // 构建分页信息
            PagingInfo pageInfo = new PagingInfo();
            pageInfo.setPageNo(pageNum);
            pageInfo.setPageSize(pageSize);
            pageInfo.setTotalRows(totalRows);
            response.setPageInfo(pageInfo);

            // 获取数据
            PreparedStatement preparedStatement = connection.prepareStatement(
                    "SELECT * FROM " + tableName + " LIMIT " + (pageNum - 1) * pageSize + "," + pageSize);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();

            // 构建列信息
            List<String> columns = new ArrayList<>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                columns.add(metaData.getColumnName(i));
            }
            response.setColumn(columns);

            // 构建表格数据
            List<List<CellData>> tableDatas = new ArrayList<>();
            while (resultSet.next()) {
                List<CellData> row = new ArrayList<>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    CellData cell = new CellData();
                    int columnType = metaData.getColumnType(i);

                    // 根据列类型设置数据类型
                    if (columnType == Types.INTEGER || columnType == Types.BIGINT
                            || columnType == Types.DECIMAL || columnType == Types.DOUBLE) {
                        cell.setType("number");
                        cell.setValue(resultSet.getObject(i));
                    } else {
                        cell.setType("string");
                        cell.setValue(resultSet.getString(i));
                    }
                    row.add(cell);
                }
                tableDatas.add(row);
            }
            response.setTableDatas(tableDatas);

            return response;
        } catch (Exception e) {
            logger.error("连接数据库失败", e);
            throw new BusinessException(e);
        } finally {
            try {
                if (connection != null) connection.close();
            } catch (Exception e) {
                logger.error("关闭连接失败", e);
            }
        }
    }

    /**
     * 获取数据库表列表
     */
    public static List<TableInfo> getTableInfo(String host, int port, String username, String password, String database) {
        String url = "jdbc:mysql://" + host + ":" + port + "/" + database
                + "?connectTimeout=" + CONNECT_TIMEOUT // 连接超时（毫秒）
                + "&socketTimeout=" + SOCKET_TIMEOUT; // socket超时（毫秒）
        try {
            Class.forName(DRIVER_NAME);
            Connection connection = DriverManager.getConnection(url, username, password);
            PreparedStatement preparedStatement = connection.prepareStatement(SQL_TABLE_INFO);
            preparedStatement.setString(1, database);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<TableInfo> tableInfoList = new ArrayList<>();
            while (resultSet.next()) {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTABLE_NAME(resultSet.getString("TABLE_NAME"));
                tableInfo.setTABLE_COMMENT(resultSet.getString("TABLE_COMMENT"));
                tableInfo.setTABLE_ROWS(resultSet.getInt("TABLE_ROWS"));
                tableInfo.setTABLE_COLUMN(resultSet.getInt("TABLE_COLUMN"));
                tableInfoList.add(tableInfo);
            }
            connection.close();
            return tableInfoList;
        } catch (Exception e) {
            logger.error("连接数据库失败", e);
            return null;
        }
    }


}
