package org.springblade;

import cn.hutool.json.JSONUtil;
import com.alibaba.druid.util.JdbcUtils;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springblade.datasource.context.DialectContext;
import org.springblade.datasource.dialect.SqlServerGmetryDialect;
import org.springblade.datasource.model.TableColumnDto;
import org.springblade.datasource.support.DynamicDataSourceSupport;
import org.springblade.datasource.transform.TableInfo;
import org.springblade.datasource.transform.TransformContext;
import org.springblade.datasource.transform.clickhouse.ClickhouseTransform;
import org.springblade.datasource.transform.dameng.DamengTransform;
import org.springblade.datasource.transform.general.GeneralEnum;
import org.springblade.datasource.transform.general.GeneralModel;
import org.springblade.datasource.transform.general.GeneralTransform;
import org.springblade.datasource.transform.kingbase.KingbaseTransform;
import org.springblade.datasource.transform.mysql.MysqlTransform;
import org.springblade.datasource.transform.oracle.OracleTransform;
import org.springblade.datasource.transform.postgresql.PostgreSQLTransform;
import org.springblade.datasource.transform.sqlserver.SqlserverTransform;
import org.springblade.datasource.transform.sybase.SybaseTransforn;
import org.springblade.etl.entity.bi.ReportDatasource;
import org.springblade.etl.service.bi.ReportDatasourceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

/**
 * --add-opens java.base/java.lang=ALL-UNNAMED --add-opens java.base/java.lang.reflect=ALL-UNNAMED
 * @author 喜欢番茄火锅
 * @date 2023/8/16 10:47
 */
@ExtendWith(SpringExtension.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@ActiveProfiles("dev") //这里
@Slf4j
public class ApplicationTest {
    @Resource
    TransformContext transformContext;
    @Resource
    OracleTransform oracleTransform;
    @Resource
    ClickhouseTransform clickhouseTransform;
    @Resource
    GeneralTransform generalTransform;
    @Autowired
    private DialectContext dialectContext;
    @Resource
    private ReportDatasourceService reportDatasourceService;
    @Resource
    private MysqlTransform mysqlTransform;
    @Resource
    private PostgreSQLTransform postgreSQLTransform;
    @Resource
    private SqlServerGmetryDialect sqlServerGmetryDialect;
    @Resource
    private SqlserverTransform sqlserverTransform;
    @Resource
    private KingbaseTransform kingbaseTransform;
    @Resource
    private DamengTransform damengTransform;
    @Resource
    private SybaseTransforn sybaseTransforn;

    /**
     * sybase
     *
     * @param
     * @return
     * @author 王瑞
     * @date 2023/8/28
     */
    @Test
    public void sybase() {
        ReportDatasource datasource = reportDatasourceService.getById(22);

        List<GeneralModel> list = generateAllFieldTypes();
        setSourceField(list, "UserID", "UserName", "Email", "CreatedAt");
        String table = "user_etl";

        TableInfo emp_copy1 = sybaseTransforn.parseTableInfo(datasource, list, table, "heihei");
        System.out.println(JSONUtil.toJsonStr(emp_copy1));
        emp_copy1.setTableName("TEST_016");
        sybaseTransforn.creatTable(emp_copy1, datasource, true);
    }
    /**
     * 达梦
     *
     * @param
     * @return
     * @author 王瑞
     * @date 2023/8/28
     */
    @Test
    public void dameng() {
        ReportDatasource datasource = reportDatasourceService.getById(21);

        List<GeneralModel> list = generateAllFieldTypes();
        setSourceField(list, "ID", "NAME", "PRICE", "TS", "TOTAL_PRICE", "REMARK", "ORGINAL_PRICE");
        String table = "TEST_001";

        TableInfo emp_copy1 = damengTransform.parseTableInfo(datasource, list, table, "heihei");
        System.out.println(JSONUtil.toJsonStr(emp_copy1));
        emp_copy1.setTableName("TEST_007");
        damengTransform.creatTable(emp_copy1, datasource, true);
    }
    /**
     * kingbase
     *
     * @param
     * @return
     * @author 王瑞
     * @date 2023/8/28
     */
    @Test
    public void kingbase() {
        ReportDatasource datasource = reportDatasourceService.getById(35);

        List<GeneralModel> list = generateAllFieldTypes();
        setSourceField(list, "name", "pay_by_quarter", "id", "schedule");
        String table = "sal_emp";

        TableInfo emp_copy1 = kingbaseTransform.parseTableInfo(datasource, list, table, "");
        System.out.println(JSONUtil.toJsonStr(emp_copy1));
        emp_copy1.setTableName("sal_emp12");
        kingbaseTransform.creatTable(emp_copy1, datasource, true);
    }

    /**
     * Sqlserver
     *
     * @param
     * @return
     * @author 王瑞
     * @date 2023/8/28
     */
    @Test
    public void Sqlserver() {
        ReportDatasource sqlserver = reportDatasourceService.getById(50);

        List<GeneralModel> list = generateAllFieldTypes();
        setSourceField(list, "id", "test_bit", "test_bool");

        TableInfo emp_copy1 = sqlserverTransform.parseTableInfo(sqlserver, list, "employees_wrm1王豫鹏", "");
        System.out.println(JSONUtil.toJsonStr(emp_copy1));
//        emp_copy1.setTableName("test_java3");
//        sqlserverTransform.creatTable(emp_copy1, sqlserver, true);
    }

    /**
     * postgreSQL
     *
     * @param
     * @return
     * @author 王瑞
     * @date 2023/8/28
     */
    @Test
    public void postgreSQL() {
        //本机 mysql
//        ReportDatasource mysql = reportDatasourceService.getById(42);
        ReportDatasource postgreSQL = reportDatasourceService.getById(44);

        List<GeneralModel> list = generateAllFieldTypes();

        setSourceField(list, "id", "test_bit", "test_bool");


        TableInfo emp_copy1 = postgreSQLTransform.parseTableInfo(postgreSQL, list, "company4", "");
        emp_copy1.setTableName("report_datasource11");
        mysqlTransform.creatTable(emp_copy1, postgreSQL, true);
    }


    /**
     * Oracle
     *
     * @param
     * @return
     * @author 王瑞
     * @date 2023/8/28
     */
    @Test
    public void Oracle() {
        ReportDatasource datasource = reportDatasourceService.getById(33);
        ReportDatasource mysql = reportDatasourceService.getById(42);

        List<GeneralModel> list = generateAllFieldTypes();
        setSourceField(list, "id", "test_bit", "test_bool");

        TableInfo emp_copy1 = oracleTransform.parseTableInfo(datasource, list, "EMP_COPY1", "");
        emp_copy1.setTableName("emp_copy2");
        mysqlTransform.creatTable(emp_copy1, mysql, true);
    }

    /**
     * Clickhouse
     *
     * @param
     * @return
     * @author 王瑞
     * @date 2023/8/24
     */
    @Test
    public void Clickhouse() {
        ReportDatasource clickhouse = reportDatasourceService.getById(14);
        List<GeneralModel> list = generateAllFieldTypes();
        setSourceField(list, "id", "name", "age", "test_bool", "boo");

        TableInfo tableInfo = clickhouseTransform.parseTableInfo(clickhouse, list, "test", "beizhu");
        tableInfo.setTableName("report_datasource12");
        mysqlTransform.creatTable(tableInfo, clickhouse, true);

    }

    /**
     * Mysql
     *
     * @param
     * @return
     * @author 王瑞
     * @date 2023/8/28
     */
    @Test
    public void Mysql() {
        ReportDatasource datasource = reportDatasourceService.getById(42);
        List<GeneralModel> list = new ArrayList<>();
        GeneralModel seatunnelModel = new GeneralModel();
        seatunnelModel.setSourceColumn(false);
        seatunnelModel.setColumnName("test");
        seatunnelModel.setDestColumnName("test");
        seatunnelModel.setColumnComments("test");
        seatunnelModel.setColumnSize(255);
        seatunnelModel.setDecimalDigits(0);
        seatunnelModel.setType("STRING");
        seatunnelModel.setPrimaryKey(true);
        list.add(seatunnelModel);

//        ArrayList<String> strings = Lists.newArrayList("id", "dt", "v", "b", "d", "dou", "f", "t");
        ArrayList<String> strings = Lists.newArrayList("id", "test_bool");
        for (String string : strings) {
            GeneralModel s = new GeneralModel();
            s.setSourceColumn(true);
            s.setColumnName(string);
            s.setDestColumnName(string);
            list.add(s);
        }
        TableInfo emp_copy1 = mysqlTransform.parseTableInfo(datasource, list, "test_copy", "");
        emp_copy1.setTableName("test_copy1");
        mysqlTransform.creatTable(emp_copy1, datasource, true);
    }


    /**
     * 查询表信息或索引信息
     * jdbc 驱动测试
     *
     * @param
     * @return
     * @author 王瑞
     * @date 2023/8/28
     */
    @Test
    public void run() {
//        ReportDatasource oracle = reportDatasourceService.getById(33);
//        ReportDatasource clickhouse = reportDatasourceService.getById(44);
//        ReportDatasource mysql = reportDatasourceService.getById(42);
//        ReportDatasource postgreSQL = reportDatasourceService.getById(45);
//        ReportDatasource sqlserver = reportDatasourceService.getById(50);
//        ReportDatasource kingbase8 = reportDatasourceService.getById(35);
//        ReportDatasource dameng = reportDatasourceService.getById(21);
        ReportDatasource sybase = reportDatasourceService.getById(22);

        String table = "user_etl";
        ReportDatasource reportDatasource = sybase;
        Connection connection = null;
        try {
            connection = DynamicDataSourceSupport.createConnection(reportDatasource);
            DatabaseMetaData metaData = connection.getMetaData();
            log.info(" ↓↓↓↓↓↓↓↓↓↓↓ getColumns ↓↓↓↓↓↓↓↓↓↓↓");
            System.out.println(getColumns(metaData, reportDatasource.getDbSchema(), table));
            log.info(" ↑↑↑↑↑↑↑↑↑↑↑ getColumns ↑↑↑↑↑↑↑↑↑↑↑");
            System.out.println();
            log.info(" ↓↓↓↓↓↓↓↓↓↓↓ getTables ↓↓↓↓↓↓↓↓↓↓↓");
            System.out.println(getTables(metaData, reportDatasource.getDbSchema(), table));
            log.info(" ↑↑↑↑↑↑↑↑↑↑↑ getTables ↑↑↑↑↑↑↑↑↑↑↑");
            System.out.println();
            log.info(" ↓↓↓↓↓↓↓↓↓↓↓ getIndexInfo ↓↓↓↓↓↓↓↓↓↓↓");
            System.out.println(getIndexInfo(metaData, reportDatasource.getDbSchema(), table));
            log.info(" ↑↑↑↑↑↑↑↑↑↑↑ getIndexInfo ↑↑↑↑↑↑↑↑↑↑↑");
            System.out.println();
            log.info(" ↓↓↓↓↓↓↓↓↓↓↓ getPrimaryKeys ↓↓↓↓↓↓↓↓↓↓↓");
            System.out.println(getPrimaryKeys(metaData, reportDatasource.getDbSchema(), table));
            log.info(" ↑↑↑↑↑↑↑↑↑↑↑ getPrimaryKeys ↑↑↑↑↑↑↑↑↑↑↑");
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            JdbcUtils.close(connection);
        }


    }

    public List<TableColumnDto> getTables(DatabaseMetaData metaData, String schema, String tableViewName) {
        List<TableColumnDto> list = new ArrayList<TableColumnDto>();
        try {
            ResultSet rs = metaData.getTables(null, schema, tableViewName, new String[]{"TABLE", "VIEW"});
            ResultSetMetaData md = rs.getMetaData();
            while (rs.next()) {
                for (int i = 1; i <= md.getColumnCount(); i++) {
                    System.out.println(md.getColumnName(i) + "==" + rs.getObject(i));
                }
                System.out.println("===================");
                System.out.println(rs.getString("REMARKS"));
            }
            rs.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return list;
    }

    public List<TableColumnDto> getColumns(DatabaseMetaData metaData, String schema, String tableViewName) {
        List<TableColumnDto> list = new ArrayList<TableColumnDto>();
        try {
            ResultSet rs = metaData.getColumns(null, schema, tableViewName, "%");
            ResultSetMetaData md = rs.getMetaData();
            while (rs.next()) {
                for (int i = 1; i <= md.getColumnCount(); i++) {
                    System.out.println(md.getColumnName(i) + "==" + rs.getObject(i));
                }
                System.out.println("===================");
            }
            rs.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return list;
    }

    public List<TableColumnDto> getIndexInfo(DatabaseMetaData metaData, String schema, String tableViewName) {
        List<TableColumnDto> list = new ArrayList<TableColumnDto>();
        try {
            ResultSet rs2 = metaData.getIndexInfo(null, schema, tableViewName, false, false);
            ResultSetMetaData md2 = rs2.getMetaData();
            while (rs2.next()) {
                for (int i = 1; i <= md2.getColumnCount(); i++) {
                    System.out.println(md2.getColumnName(i) + "==" + rs2.getObject(i));
                }
                System.out.println("===================");

            }
            rs2.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return list;
    }

    public List<TableColumnDto> getPrimaryKeys(DatabaseMetaData metaData, String schema, String tableViewName) {
        List<TableColumnDto> list = new ArrayList<TableColumnDto>();
        try {
            ResultSet rs2 = metaData.getPrimaryKeys(null, schema, tableViewName);
            ResultSetMetaData md2 = rs2.getMetaData();
            while (rs2.next()) {
                for (int i = 1; i <= md2.getColumnCount(); i++) {
                    System.out.println(md2.getColumnName(i) + "==" + rs2.getObject(i));
                }
                System.out.println("===================");

            }
            rs2.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return list;
    }

    private void setSourceField(List<GeneralModel> list, String... str) {
        ArrayList<String> strings = Lists.newArrayList(str);
        for (String string : strings) {
            GeneralModel s = new GeneralModel();
            s.setSourceColumn(true);
            s.setColumnName(string);
            s.setDestColumnName(string);
            list.add(s);
        }
    }

    private List<GeneralModel> generateAllFieldTypes() {
        List<GeneralModel> list = new ArrayList<>();
        int i = 1;
        for (GeneralEnum value : GeneralEnum.values()) {
            GeneralModel generalModel = new GeneralModel();
            generalModel.setSourceColumn(false);
            generalModel.setColumnName("S" + i);
            generalModel.setDestColumnName("S" + i);
            generalModel.setColumnComments("S" + i);
            generalModel.setColumnSize(10);
            generalModel.setDecimalDigits(0);
            generalModel.setType(value.name());
            list.add(generalModel);
            i++;
        }
        return list;
    }

}
