package org.springblade.datasource.transform.sqlserver;

import cn.hutool.core.collection.CollectionUtil;
import com.google.common.collect.Lists;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springblade.core.tool.exception.ServiceException;
import org.springblade.datasource.dialect.SqlServerGmetryDialect;
import org.springblade.datasource.support.DynamicDataSourceSupport;
import org.springblade.datasource.transform.*;
import org.springblade.datasource.transform.util.ListUtils;
import org.springblade.etl.entity.bi.ReportDatasource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Comparator;
import java.util.List;
import java.util.Locale;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * @author 喜欢番茄火锅
 * @date 2023/8/24 14:55
 */
@Slf4j
public class SqlserverCreateTableHandle {
    /**
     * 根据通用表信息创建表对象
     *
     * @param tableInfo
     * @param targetDatasource
     * @param deleteSourceTable
     * @param sqlServerGmetryDialect
     * @return
     * @author 王瑞
     * @date 2023/8/21
     */
    @SneakyThrows
    public void creatTable(TableInfo tableInfo, ReportDatasource targetDatasource, boolean deleteSourceTable, SqlServerGmetryDialect sqlServerGmetryDialect) {

        tableInfo.setTableName(tableInfo.getTableName().toUpperCase(Locale.ROOT));
        List<ColumnInfo> columnInfo = tableInfo.getColumnInfo();

        String columSql = buildColumnSql(targetDatasource.getDbSchema(), tableInfo.getTableName(), tableInfo.getColumnInfo(), tableInfo.getIndexInfo());
        log.info("创建表sql 语句,sql:{}", columSql);

        String tableCommentSql = buildTableCommentSql(tableInfo.getTableName(), tableInfo.getTableComment(), targetDatasource.getDbSchema());
        log.info("创建表注释sql 语句,sql:{}", tableCommentSql);

        List<String> columnCommentSql = buildColumnCommentSql(tableInfo.getTableName(), columnInfo, targetDatasource.getDbSchema());
        log.info("创建字段注释sql 语句,sql:{}", columnCommentSql);

        List<String> indexSql = buildIndexSql(targetDatasource.getDbSchema(), tableInfo.getTableName(), tableInfo.getIndexInfo(), tableInfo.getColumnInfo());
        log.info("创建表索引sql 语句,sql:{}", indexSql);

        Connection connection = DynamicDataSourceSupport.createConnection(targetDatasource);
        Statement statement = connection.createStatement();

        ResultSet tables = connection.getMetaData().getTables(null, targetDatasource.getDbSchema(), tableInfo.getTableName(), new String[]{"TABLE", "VIEW"});
        if (tables.next()) {
//                statement.executeUpdate("DROP TABLE " + tableInfo.getTableName() + "");
            List<ColumnInfo> sourceColumn = new SqlserverParseTableHandle().getSourceColumn(targetDatasource, tableInfo.getTableName(), sqlServerGmetryDialect);
            List<String> collect = tableInfo.getColumnInfo().stream().map(ColumnInfo::getColumnName).collect(Collectors.toList());
            List<String> collect2 = sourceColumn.stream().map(ColumnInfo::getColumnName).collect(Collectors.toList());
            if (!ListUtils.equalLists(collect, collect2)) {
                throw new ServiceException("sqlserver 数据源,表名称:" + tableInfo.getTableName() + "已存在且字段不符!");
            }
            //如果检测通过，直接返回，不再创建表
            return;
        }

        statement.executeUpdate(columSql);
        statement.executeUpdate(tableCommentSql);
        for (String s : columnCommentSql) {
            statement.executeUpdate(s);
        }
        for (String s : indexSql) {
            statement.executeUpdate(s);
        }
        statement.close();


    }


    private String buildColumnSql(String dbSchema, String tableName, List<ColumnInfo> columnInfos, List<IndexInfo> indexInfo) {
        StringBuilder fieldSql = new StringBuilder();

        columnInfos.stream().forEach(columnInfo -> {
//            boolean flag = false;
//            String columnName = columnInfo.getColumnName();
//            for (IndexInfo info : indexInfo) {
//                for (IndexInfo.IndexColumn indexColumn : info.getIndexColumn()) {
//                    flag = indexColumn.getColumnName().equalsIgnoreCase(columnName);
//                }
//            }

            fieldSql.append(String.format(
                    "%s %s,",
                    columnInfo.getColumnName(),
                    getColumnType(columnInfo)
//                    flag ? "PRIMARY KEY" : ""
            ));
        });
        fieldSql.deleteCharAt(fieldSql.length() - 1);
        return String.format("CREATE TABLE %s.%s (%s) ;", dbSchema, tableName, fieldSql);
    }


    public String getColumnType(ColumnInfo columnInfo) {
        ColumnEnum columnEnum = columnInfo.getColumnType();
        Integer columnSize = columnInfo.getColumnSize();
        Integer decimalDigits = columnInfo.getDecimalDigits();
        switch (columnEnum) {

            case DECIMAL:
                Integer columnSize2 = TransformUtil.getColumnSizeOrDecimalDigits(columnSize, 18, 37, 1);
                return String.format("decimal(%s,%s)", columnSize2, TransformUtil.getColumnSizeOrDecimalDigits(decimalDigits, 0, columnSize2, 0));
            case STRING:
                Integer string = TransformUtil.getColumnSizeOrDecimalDigits(columnSize, 2000, 4000, 1000);
                if (columnSize != null && columnSize < 1) {
                    return "nvarchar(max)";
                }
                return "nvarchar(" + string + ")";
            case LONG:
                return "bigint";
            case INTEGER:
                return "int";
            case DOUBLE:
                return "float(53)";
            case FLOAT:
                return "float(24)";
            case DATE:
                return "datetime2(7)";
            default:
                throw new ServiceException("暂不支持该数据库类型!");
        }

    }

    private String buildTableCommentSql(String tableName, String tableComment, String dbSchema) {
//        String createTableCommentSql = "EXEC sp_addextendedproperty N'MS_Description', N'%s', N'user', N'" + dbSchema + "', N'table', N'%s', NULL, NULL";
        String createTableCommentSql = "EXEC sys.sp_addextendedproperty @name=N'MS_Description',\n" +
                "\n" +
                "@value=N'%s',\n" +
                "\n" +
                "@level0type=N'SCHEMA',\n" +
                "\n" +
                "@level0name=N'%s',\n" +
                "\n" +
                "@level1type=N'TABLE',\n" +
                "\n" +
                "@level1name=N'%s'";

        return String.format(createTableCommentSql, tableComment, dbSchema, tableName);
    }

    private List<String> buildColumnCommentSql(String tableName, List<ColumnInfo> columnComment, String dbSchema) {
//        String createColumnCommentSql = "EXEC sp_addextendedproperty N'MS_Description', N'%s', N'user', N'" + dbSchema + "', N'table', N'%s', N'COLUMN', N'%s'";
        String createColumnCommentSql = "EXEC sys.sp_addextendedproperty @name = N'MS_Description', \n" +
                "\t@value= N'%s', \n" +
                "\t@level0type = N'SCHEMA',\n" +
                "\t@level0name = N'%s', \n" +
                "\t@level1type = N'TABLE',\n" +
                "\t@level1name = N'%s', \n" +
                "\t@level2type = N'COLUMN',\n" +
                "\t@level2name = N'%s'";

        return columnComment.stream().map(columnInfo ->
                String.format(createColumnCommentSql, columnInfo.getColumnComments(), dbSchema, tableName, columnInfo.getColumnName())
        ).collect(Collectors.toList());
    }

    private List<String> buildIndexSql(String dbSchema, String tableName, List<IndexInfo> indexInfos, List<ColumnInfo> columnInfo) {
        List<String> list = Lists.newArrayList();

        if (CollectionUtil.isEmpty(indexInfos)) {
            return list;
        }

        for (IndexInfo indexInfo : indexInfos) {
            if (indexInfo.getPrimaryKey()) {

                for (IndexInfo.IndexColumn indexColumn : indexInfo.getIndexColumn()) {
                    StringBuilder indexSql = new StringBuilder();
                    indexSql.append(String.format(
                            "ALTER TABLE %s.%s ALTER COLUMN %s %s NOT NULL ;",
                            dbSchema,
                            tableName,
                            indexColumn.getColumnName(),
                            getColumnType(Objects.requireNonNull(getColumnInfo(indexColumn.getColumnName(), columnInfo)))
                    ));
                    list.add(indexSql.toString());
                }
            }

        }
        for (IndexInfo indexInfo : indexInfos) {
            if (indexInfo.getPrimaryKey()) {
                StringBuilder indexSql = new StringBuilder();
                indexSql.append(String.format(
                        "ALTER TABLE %s.%s ADD %s ( %s ) ;",
                        dbSchema,
                        tableName,
                        "PRIMARY KEY ",
                        getIndexColumn(indexInfo.getIndexColumn())));
                list.add(indexSql.toString());
            }
        }

        return list;
    }

    private ColumnInfo getColumnInfo(String columnName, List<ColumnInfo> columnInfo) {
        List<ColumnInfo> collect = columnInfo.stream().filter(v -> v.getColumnName().equalsIgnoreCase(columnName)).collect(Collectors.toList());
        if (CollectionUtil.isNotEmpty(collect)) {
            return collect.get(0);
        }
        throw new ServiceException("sqlserver 数据源,主键索引字段不存在:" + columnName + "!");
    }

    private String getIndexColumn(List<IndexInfo.IndexColumn> list) {
        StringBuilder sb = new StringBuilder();
        list.stream().sorted((Comparator.comparing(IndexInfo.IndexColumn::getOrdinalPosition))).forEach(v -> {
            sb.append(v.getColumnName() + ",");
        });
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }
}
