package com.beagle.informix.service.impl;


import cn.hutool.core.io.FileUtil;
import cn.hutool.core.text.CharSequenceUtil;
import cn.hutool.core.util.StrUtil;
import com.beagle.informix.service.InformixService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.stereotype.Service;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.Properties;

/**
 * @author lzz
 * date 2023/5/29
 * @version 1.0
 * description:
 */
@Slf4j
@Service("informixJdbcService")
public class InformixJdbcServiceImpl implements InformixService {

    private ResourceLoader resourceLoader;

    public InformixJdbcServiceImpl(ResourceLoader resourceLoader) {
        this.resourceLoader = resourceLoader;
    }

    /**
     * informix.url=jdbc:informix-sqli://localhost:9088/sysadmin:INFORMIXSERVER=informix
     * informix.driverClassName=com.informix.jdbc.IfxDriver
     * informix.user=informix
     * informix.password=in4mix
     * 进入容器，使用dbaccess 进入informix
     * 问题： Invalid default sbspace name (sbspace).
     * 解决： onspaces -c -S sbspace -p /opt/ibm/data/spaces/sbspace.000 -o 100 -s 20000 -Df "LOGGING=ON"
     * @throws Exception
     */
    @Override
    public void doConnectJob() throws Exception{
        Driver driver = getDriver();
        Connection conn = getConnection(driver);
//        updateTable(conn);
        createTable(conn);
//        insert(conn);
//        update(conn);
//        query(conn);
//        delete(conn);
//        createTable2(conn);
        deregisterDriver(driver);
        closeConnection(conn);
    }

    private void createTable(Connection connection) throws Exception{
        String sql = "CREATE SEQUENCE if not exists engine_test45_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999;\nCREATE TABLE engine_test45 (id INTEGER DEFAULT engine_test44_id_seq.Nextval NOT NULL PRIMARY KEY,ffff TIMESTAMP(3));\nCOMMENT ON TABLE engine_test45 IS 'sdfadf';";
        Statement statement = connection.createStatement();
        statement.execute(sql);
    }
    // 不能执行ddl语句
    private void createTable2(Connection connection) throws Exception{
        String sql = "CREATE TABLE engine_test45 (id INTEGER  NOT NULL PRIMARY KEY,ffff TIMESTAMP(3));\n" +
                "CREATE TABLE engine_test46 (id INTEGER  NOT NULL PRIMARY KEY,ffff TIMESTAMP(3));\n" +
                "CREATE TABLE engine_test47 (id INTEGER  NOT NULL PRIMARY KEY,ffff TIMESTAMP(3));";
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
    }
    // ok
    private void updateTable(Connection connection) throws Exception{
        String sql = "update sc_test set name = 'test' where id = 3;\n" +
                "update sc_test set name = 'test' where id = 2;\n" +
                "update sc_test set name = 'test' where id = 4;";
        Statement statement = connection.createStatement();
        statement.execute(sql);
    }


    private void insert(Connection conn) throws Exception{
        // 创建testdb数据库、my_test_create_table表
        // 现在模拟插入、更新、删除、查询等sql
        // 插入
        String insertSql = "insert  into  cti_vccinfo_new (vccid ,vccname, effective, agentmax,ivrmax,updatekey,tclob,tblob) values (?, ? , ?, ?, ? , ?,?,? )";
        PreparedStatement stat = conn.prepareStatement(insertSql);
        stat.setObject(1, "123456");
        stat.setObject(2, "vname");
        stat.setObject(3, 1);
        stat.setObject(4, 2);
        stat.setObject(5, 3);
        stat.setObject(6, "updatekey");
        String clobContent = "This is a very very long string";
        // 处理clob字段
        StringReader reader = new StringReader(clobContent);
        stat.setClob(7, reader, clobContent.length());
        // 处理blob字段
        Resource resource = resourceLoader.getResource("classpath:translator.bak2.yaml");
        File inputStream = resource.getFile();
        FileInputStream fis = new FileInputStream(inputStream);
        stat.setBlob(8, fis);
        stat.executeUpdate();
    }
    private void update(Connection conn) throws Exception{
        // 创建testdb数据库、my_test_create_table表
        // 现在模拟插入、更新、删除、查询等sql
        // 插入
        String updateSql = "update  cti_vccinfo_new set vccname = 'informix_name' where vccid = '123456'";
        PreparedStatement stat = conn.prepareStatement(updateSql);
        stat.executeUpdate();
    }
    private void query(Connection conn) throws Exception {
        String querySQL = "select * from cti_vccinfo_new;\n" +
                "select * from engine_test14;\n" +
                "select * from sc_test;";
        PreparedStatement stat = conn.prepareStatement(querySQL);
        ResultSet resultSet = stat.executeQuery();
    }
    private void delete(Connection conn) throws Exception{
        String deleteSql = "delete from cti_vccinfo_new where vccid = '123456' ";
        conn.createStatement().executeUpdate(deleteSql);
    }
    private Connection getConnection(Driver driver) throws Exception{
        DriverManager.registerDriver(driver);
        Properties properties = new Properties();
        // 组装连接数据库信息
//        jdbc:informix-sqli://<server>:<port1526>/<database>:informixserver=<dbservername>
        StringBuilder jdbcBuilder = new StringBuilder("jdbc:dm://")
                .append("localhost:5236");
        DriverManager.setLoginTimeout(10);
        properties.put("user", "SYSDBA");
        properties.put("password", "SYSDBA");
        return DriverManager.getConnection(jdbcBuilder.toString(), properties);
    }
    private Driver getDriver() throws Exception{
        // 定义informix的驱动信息
        String driverUrl = "dm.jdbc.driver.DmDriver";
        // 还需要加入informix的jdbc驱动jar包
        Class<?> driverClass = Class.forName(driverUrl);
        return (Driver) driverClass.getConstructor().newInstance();
    }
    private void deregisterDriver(Driver driver) throws Exception{
        // 卸载驱动
        DriverManager.deregisterDriver(driver);
    }
    private void closeConnection(Connection connection) throws Exception {
        connection.close();
    }
}
