package com.sczhaoqi.metertest;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.opencsv.CSVReader;
import com.opencsv.exceptions.CsvException;
import com.sczhaoqi.metertest.bean.ColumnInfo;
import com.sczhaoqi.metertest.bean.ColumnType;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.springframework.core.io.ClassPathResource;

import java.io.FileReader;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

import static com.sczhaoqi.metertest.bean.ColumnType.INT_NUM;
import static com.sczhaoqi.metertest.bean.ColumnType.VARCHAR;

/**
 * @author zhaoqi
 * @date 2023-02-22 下午4:48
 */
@Slf4j
public class ExcelToDbTest {

    private final String colPrefix = "col_";

    @Test
    public void loadExcelToMysql() throws IOException, CsvException, SQLException {
        String fileName = "online_dependence1.csv";
        Boolean append = false;
        Integer tableId = 1;

        // id INT_NUM
        // parentId INT_NUM
        // parentName VARCHAR
        // parentPlanId INT_NUM
        // parentCron VARCHAR
        // parentPlanType INT_NUM
        // childId INT_NUM
        // childName VARCHAR
        // childPlanId INT_NUM
        // childCron VARCHAR
        // childPlanType INT_NUM
        // forceDepend INT_NUM
        // depType INT_NUM
        // depTime VARCHAR
        List<ColumnInfo> columns = Arrays.asList(
                ColumnInfo.builder().name("id").type(INT_NUM).comment("").build(),
                ColumnInfo.builder().name("parentId").type(INT_NUM).comment("").build(),
                ColumnInfo.builder().name("parentName").type(VARCHAR).comment("").build(),
                ColumnInfo.builder().name("parentPlanId").type(INT_NUM).comment("").build(),
                ColumnInfo.builder().name("parentCron").type(VARCHAR).comment("").build(),
                ColumnInfo.builder().name("parentPlanType").type(INT_NUM).comment("").build(),
                ColumnInfo.builder().name("childId").type(INT_NUM).comment("").build(),
                ColumnInfo.builder().name("childName").type(VARCHAR).comment("").build(),
                ColumnInfo.builder().name("childPlanId").type(INT_NUM).comment("").build(),
                ColumnInfo.builder().name("childCron").type(VARCHAR).comment("").build(),
                ColumnInfo.builder().name("childPlanType").type(INT_NUM).comment("").build(),
                ColumnInfo.builder().name("forceDepend").type(INT_NUM).comment("").build(),
                ColumnInfo.builder().name("depType").type(INT_NUM).comment("").build(),
                ColumnInfo.builder().name("depTime").type(VARCHAR).comment("").build()
        );
        String tableName = getTableNameById(tableId);
        if (StringUtils.isBlank(tableName)) {
            tableName = createTableAndSave(tableId, columns);
        }

        List<Object[]> datas = new ArrayList<>();
        try (CSVReader reader = new CSVReader(new FileReader(new ClassPathResource(fileName).getFile()))) {
            List<String[]> list = reader.readAll();
            for (String[] item : list) {
                Object[] rowData = new Object[item.length];
                for (int i = 0; i < columns.size(); i++) {
                    rowData[i] = toObject(item[i], columns.get(i).getType());
                }
                datas.add(rowData);
            }
        }
        String insertSql = String.format("insert into %s (%s) values (%s)", tableName, IntStream.range(0, columns.size()).mapToObj(i -> colPrefix + i).collect(Collectors.joining(",")), IntStream.range(0, columns.size()).mapToObj(i -> "?").collect(Collectors.joining(",")));
        log.info("insertSQL:{}", insertSql);
        try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
            // 批量插入数据
            for (int i = 1; i <= datas.size(); i++) {
                Object[] row = datas.get(i - 1);
                for (int j = 1; j <= row.length; j++) {
                    ps.setObject(j, row[j - 1]);
                }

                // 1.攒SQL
                ps.addBatch();
                // 攒够500条
                if (i % 500 == 0 || i == 20000) {
                    // 2.执行batch
                    ps.executeBatch();
                    // 3.清空batch
                    ps.clearBatch();
                }
            }
        }
    }

    public static Object toObject(String data, ColumnType columnType) {
        Object cr = null;
        if (StringUtils.isBlank(data)) {
            return cr;
        }

        switch (columnType) {
            case INT_NUM:
                cr = Integer.valueOf(data);
                break;
            case DECIMAL_NUM:
                cr = new BigDecimal(data);
                break;
            case DATE:
                cr = LocalDate.parse(data, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                break;
            case DATETIME:
                cr = LocalDateTime.parse(data, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                break;
            default:
            case VARCHAR:
            case LONG_VARCHAR:
                cr = data;
                break;
        }
        return cr;
    }

    public static final String url = "jdbc:mysql://192.168.49.2:30336/data?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
    public static final String username = "root";
    public static final String password = "root";

    private static Connection conn;

    @BeforeAll
    public static void initConnection() throws SQLException {
        conn = DriverManager.getConnection(url, username, password);
    }

    @AfterAll
    public static void closeConnection() throws SQLException {
        conn.close();
    }

    private String dataTableNameSQL = "select table_name from data_index_table where id = ?";
    private String dataIndexTable = "" +
            "create table if not exists data_index_table (" +
            "id int(11) auto_increment primary key," +
            "table_name varchar(128) not null ," +
            "col_infos mediumtext not null " +
            ");" +
            "";

    private String getTableNameById(Integer id) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(dataIndexTable)) {
            ps.execute();
        }
        String tableName = "";
        try (
                PreparedStatement ps = conn.prepareStatement(dataTableNameSQL);
        ) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    tableName = rs.getString(1);
                }
            }
        }
        log.info("{}", tableName);
        return tableName;
    }

    private String tableRecord = "" +
            "insert into data_index_table values(?,?, ?);" +
            "";

    private String createTableAndSave(Integer tableId, List<ColumnInfo> columnInfos) throws SQLException, JsonProcessingException {
        String tableName = "data_" + DateTimeFormatter.ofPattern("yyyyMMddHHmmssS").format(LocalDateTime.now());
        StringBuilder sqlBuilder = new StringBuilder("" +
                "create table if not exists ");
        sqlBuilder.append(tableName);
        sqlBuilder.append("(");
        int i = 0;
        for (i = 0; i < columnInfos.size() - 1; i++) {
            sqlBuilder.append(colSQL(i, columnInfos.get(i).getType(), columnInfos.get(i).getComment())).append(",");
        }
        sqlBuilder.append(colSQL(i, columnInfos.get(i).getType(), columnInfos.get(i).getComment())).append(")")
                .append(" comment '").append(tableName).append("';");

        try (PreparedStatement ps = conn.prepareStatement(sqlBuilder.toString());) {
            ps.execute();
        }
        try (
                PreparedStatement ps = conn.prepareStatement(tableRecord);
        ) {
            ps.setInt(1, tableId);
            ps.setString(2, tableName);
            ps.setString(3, new ObjectMapper().writeValueAsString(columnInfos));
            ps.execute();
        }
        return tableName;
    }

    private String colSQL(Integer index, ColumnType type, String comment) {
        String sql = "";
        switch (type) {
            case INT_NUM:
                sql = colPrefix + index + " int(11)";
                break;
            case DECIMAL_NUM:
                sql = colPrefix + index + " decimal(10,4)";
                break;
            case DATE:
                sql = colPrefix + index + " DATE";
                break;
            case DATETIME:
                sql = colPrefix + index + " DATETIME";
                break;
            default:
            case VARCHAR:
                sql = colPrefix + index + " varchar(256)";
                break;
            case LONG_VARCHAR:
                sql = colPrefix + index + " varchar(512)";
                break;
        }
        if (StringUtils.isNotBlank(comment)) {
            sql = String.format("%s comment '%s'", sql, comment);
        }
        return sql;
    }
}

import java.util.Objects;

/**
 *
 */
public interface BaseEnum {
    Integer getCode();

    String getDesc();

    /**
     * get byte code from current enum
     *
     * @return the byte code
     */
    default Byte getByteCode() {
        Integer code = getCode();
        if (code == null) {
            return null;
        }
        return Byte.valueOf(String.valueOf(code));
    }

    default boolean equalsByCode(Integer code) {
        return Objects.equals(code, this.getCode());
    }

    default boolean equalsByCode(Byte code) {
        return Objects.equals(code, this.getByteCode());
    }
}
@NoArgsConstructor
@Data
@AllArgsConstructor
@Builder
public class ColumnInfo {
    private Integer index;
    private String name;
    private ColumnType type;
    private String typeExt;
    private String comment;
}

public enum ColumnType implements BaseEnum {
    INT_NUM(1, "整数"),
    DECIMAL_NUM(2, "复数"),
    VARCHAR(3, "字符串"),
    LONG_VARCHAR(4, "长字符串"),
    DATE(5, "日期"),
    DATETIME(6, "时间"),
    ;

    // 成员变量
    private int code;
    private String desc;

    private ColumnType(int code, String desc) {
        this.code = code;
        this.desc = desc;
    }

    public Integer getCode() {
        return code;
    }

    public String getDesc() {
        return desc;
    }


    /**
     * 根据值获取枚举对象
     *
     * @param code 或者name 枚举值
     * @return 对象枚举对象
     */
    public static String getValue(Integer code) {
        if (code == null) {
            return null;
        }
        for (ColumnType status : values()) {
            if (status.getCode() == code) {
                return status.getDesc();
            }
        }
        return null;
    }

    public static ColumnType getByCode(Integer code) {
        if (code == null) {
            return null;
        }
        for (ColumnType status : values()) {
            if (status.getCode().equals(code)) {
                return status;
            }
        }
        return null;
    }

    public static ColumnType getByName(String name) {
        if (name == null) {
            return null;
        }
        for (ColumnType status : values()) {
            if (status.name().equals(name)) {
                return status;
            }
        }
        return null;
    }
}