/**
 *
 */
package org.springblade.datasource.dialect;

import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.util.JdbcUtils;
import lombok.extern.slf4j.Slf4j;
import org.springblade.core.tool.exception.ServiceException;
import org.springblade.datasource.AbstractGmetryDialect;
import org.springblade.datasource.enums.*;
import org.springblade.datasource.model.TableColumnDto;
import org.springblade.datasource.model.TableViewDto;
import org.springblade.datasource.support.DynamicDataSourceSupport;
import org.springblade.etl.dto.FileStructure;
import org.springblade.etl.entity.bi.ReportDatasource;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.function.Consumer;

/**
 * @author wookvn
 */
@Slf4j
@Component
public class SqlServerGmetryDialect extends AbstractGmetryDialect {

    {
        //数据类型
        columnTypeMap.put(ColumnTypeEnum.NUMBER, "decimal(30, 4)");
        columnTypeMap.put(ColumnTypeEnum.STRING, "nvarchar(1000)");
        columnTypeMap.put(ColumnTypeEnum.DATETIME, "datetime");
        columnTypeMap.put(ColumnTypeEnum.BIGINT, "bigint");

        //函数方法
        functionMethodMap.put(FunctionMethodEnum.DATE_TO_STR, "FORMAT(%s, '%s')");
        functionMethodMap.put(FunctionMethodEnum.STR_TO_DATE, "CONVERT(datetime, %s)");
        functionMethodMap.put(FunctionMethodEnum.NUMBER_TO_DATE, "CONVERT(datetime, convert(varchar(50), %s))");
        functionMethodMap.put(FunctionMethodEnum.NUMBER_JOINT_PERCENT, "CONVERT(varchar(50), %s)+'%%'");

        //日期格式化
        dateFormatMap.put(DateFormatEnum.yyyy, "yyyy");
        dateFormatMap.put(DateFormatEnum.yyyyMM, "yyyyMM");
        dateFormatMap.put(DateFormatEnum.yyyy_MM, "yyyy-MM");
        dateFormatMap.put(DateFormatEnum.yyyy$MM, "yyyy/MM");
        dateFormatMap.put(DateFormatEnum.yyyyMMdd, "yyyyMMdd");
        dateFormatMap.put(DateFormatEnum.yyyy_MM_dd, "yyyy-MM-dd");
        dateFormatMap.put(DateFormatEnum.yyyy$MM$dd, "yyyy/MM/dd");
        dateFormatMap.put(DateFormatEnum.yyyyMMddhh$mm$ss, "yyyyMMdd hh:mm:ss");
        dateFormatMap.put(DateFormatEnum.yyyy_MM_ddhh$mm$ss, "yyyy-MM-dd hh:mm:ss");
        dateFormatMap.put(DateFormatEnum.yyyy$MM$ddhh$mm$ss, "yyyy/MM/dd hh:mm:ss");
        dateFormatMap.put(DateFormatEnum.hh, "hh");
        dateFormatMap.put(DateFormatEnum.hh$mm, "hh:mm");
        dateFormatMap.put(DateFormatEnum.hh$mm$ss, "hh:mm:ss");
    }

    @Override
    public List<String> buildCreateTableSql(String tableName, String tableComment, List<FileStructure> structureList) {
        String createColumnCommentSql = "EXEC sp_addextendedproperty N'MS_Description', N'%s', N'user', N'dbo', N'table', N'%s', N'COLUMN', N'%s'",
                createTableCommentSql = "EXEC sp_addextendedproperty N'MS_Description', N'%s', N'user', N'dbo', N'table', N'%s', NULL, NULL",
                createTableSql = "create table %s (%s)";
        List<String> sqlList = new LinkedList<String>();
        StringBuilder fieldSql = new StringBuilder();
        structureList.stream().forEach(f -> {
            fieldSql.append(String.format("%s %s %s,",
                    f.getColumnName(), getColumnType(ColumnTypeEnum.valueOf(f.getColumnType().name())),
                    f.isPrimaryKey() ? "primary key" : ""));
            //添加字段注释SQL
            sqlList.add(String.format(createColumnCommentSql, f.getComment(), tableName, f.getColumnName()));
        });
        fieldSql.deleteCharAt(fieldSql.length() - 1);
        //创建表SQL
        sqlList.add(0, String.format(createTableSql, tableName, fieldSql.toString()));
        //添加表注释SQL
        sqlList.add(1, String.format(createTableCommentSql, tableComment, tableName));
        return sqlList;
    }

    @Override
    public List<TableColumnDto> listTableColumnsByTableViewName(ReportDatasource datasource, String tableViewName) {
        String querySql = "SELECT B.NAME AS column_name,C.NAME AS data_type,B.PREC AS column_size,B.SCALE AS decimal_digits,\n" +
                "	 B.isnullable AS null_able_status,\n" +
                "    (CASE WHEN NOT F.ID IS NULL THEN 1 ELSE 0 END) AS primary_key_status,\n" +
                "    (CASE WHEN COLUMNPROPERTY(B.ID,B.NAME,'ISIDENTITY') = 1 THEN 1 ELSE 0 END) AS auto_increment_status,\n" +
                "    CONVERT(NVARCHAR(1000),ISNULL(G.VALUE,'')) AS column_comments\n" +
                "FROM SYSOBJECTS A INNER JOIN SYSCOLUMNS B ON A.ID=B.ID and A.XTYPE in ('U','V')\n" +
                "	 INNER JOIN SYSTYPES C ON B.XTYPE=C.XUSERTYPE\n" +
                "    LEFT JOIN SYSOBJECTS D ON B.ID=D.PARENT_OBJ AND D.XTYPE='PK'\n" +
                "    LEFT JOIN SYSINDEXES E ON B.ID=E.ID AND D.NAME=E.NAME\n" +
                "    LEFT JOIN SYSINDEXKEYS F ON B.ID=F.ID AND B.COLID=F.COLID AND E.INDID=F.INDID\n" +
                "    LEFT JOIN SYS.EXTENDED_PROPERTIES G ON B.ID=G.MAJOR_ID AND B.COLID=G.MINOR_ID\n" +
                "    LEFT JOIN SYS.OBJECTS H on B.id= H.object_id\n" +
                "    LEFT JOIN SYS.SCHEMAS I on H.schema_id=I.schema_id\n" +
                "WHERE OBJECT_NAME(B.ID)=N'" + tableViewName + "' AND I.NAME = N'" + dbSchema(datasource) + "'";

        // 查询索引sql
        String indexSql = "SELECT SCHEMA_NAME(t.schema_id)                          AS schema_name,\n" +
                "       t.[name]                                          AS table_name,\n" +
                "       i.[name]                                          AS index_name,\n" +
                "       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS column_name\n" +
                "FROM sys.objects t\n" +
                "         INNER JOIN sys.indexes i ON t.object_id = i.object_id\n" +
                "         CROSS APPLY (SELECT col.[name] + ', '\n" +
                "                      FROM sys.index_columns ic\n" +
                "                               INNER JOIN sys.columns col ON ic.object_id = col.object_id\n" +
                "                          AND ic.column_id = col.column_id\n" +
                "                      WHERE ic.object_id = t.object_id\n" +
                "                        AND ic.index_id = i.index_id\n" +
                "                      ORDER BY col.column_id\n" +
                "                      FOR XML PATH('')) D (column_names)\n" +
                "WHERE t.is_ms_shipped <> 1\n" +
                "  AND index_id > 0 and SCHEMA_NAME(t.schema_id) = '"+ dbSchema(datasource) + "' and t.[name] = '"+tableViewName+"'";
        List<TableColumnDto> dataList = new ArrayList<>();
        _query(datasource, querySql, rs -> {
            try {
                while (rs.next()) {
                    TableColumnDto tableColumnDto = new TableColumnDto();
                    tableColumnDto.setColumnName(rs.getString("column_name"));
                    tableColumnDto.setColumnComments(rs.getString("column_comments"));
                    tableColumnDto.setColumnSize(rs.getInt("column_size"));
                    tableColumnDto.setDataType(rs.getString("data_type"));
                    tableColumnDto.setDecimalDigits(rs.getInt("decimal_digits"));
                    tableColumnDto.setNullAbleStatus(rs.getInt("null_able_status"));
                    tableColumnDto.setPrimaryKeyStatus(rs.getInt("primary_key_status"));
                    tableColumnDto.setIndexStatus(0);
                    setUpColumnType(tableColumnDto);
                    dataList.add(tableColumnDto);
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        });
        _query(datasource, indexSql, rs -> {
            try {
                if (rs == null) {
                    return;
                }
                while (rs.next()) {
                    dataList.forEach(data -> {
                        try {
                            if (rs.getString("index_name").contains(data.getColumnName())) {
                                data.setIndexStatus(1);
                            }
                        } catch (SQLException e) {
                            throw new RuntimeException(e);
                        }
                    });
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        });
        return dataList;
    }

    @Override
    public List<TableViewDto> listTableViewsByDatasource(ReportDatasource datasource) {
        return this.listTableViewsByDatasource(datasource, null);
    }

    @Override
    public List<TableViewDto> listTableViewsByDatasource(ReportDatasource datasource, String tableName) {
        String querySql = "select top 1000 ROW_NUMBER() OVER (ORDER BY a.name) AS oder_num, a.name AS table_view_name, g.[value] AS table_comments " +
                "from sys.objects a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0) " +
                "left join sys.schemas c on a.schema_id = c.schema_id where a.type in ('V', 'U') and c.name = N'" + dbSchema(datasource) + "'";
        if (StrUtil.isNotBlank(tableName)) {
            querySql += " and a.name like N'%" + tableName + "%'";
        }
        List<TableViewDto> dataList = new ArrayList<>();
        _query(datasource, querySql, rs -> {
            try {
                while (rs.next()) {
                    TableViewDto tableView = new TableViewDto();
                    tableView.setDatasourceId(datasource.getId());
                    tableView.setTableComments(rs.getString("table_comments"));
                    tableView.setTableViewName(rs.getString("table_view_name"));
                    dataList.add(tableView);
                }
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        });
        return dataList;
    }

    @Override
    public String dbSchema(ReportDatasource datasource) {
        return StrUtil.isBlank(datasource.getDbSchema()) ? "dbo" : datasource.getDbSchema();
    }

    @Override
    public String dbTableName(ReportDatasource datasource, String tableName) {
        return dbSchema(datasource) + "." + tableName;
    }

    @Override
    public DsTypeEnum dsType() {
        return DsTypeEnum.sqlserver;
    }

    private void _query(ReportDatasource datasource, String sql, Consumer<ResultSet> consumer) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DynamicDataSourceSupport.createConnection(datasource);
            statement = connection.createStatement();
            if (statement.execute(sql)) {
                ResultSet resultSet = statement.executeQuery(sql);
                consumer.accept(resultSet);
            }
        } catch (Exception e) {
            log.error(e.getMessage());
            throw new ServiceException("查询表或字段信息出错，请稍后重试", e);
        } finally {
            JdbcUtils.close(connection);
            JdbcUtils.close(statement);
        }
    }

    private void setUpColumnType(TableColumnDto tableColumn) {
        if (StrUtil.containsAnyIgnoreCase(tableColumn.getDataType(), "char", "text")) {
            tableColumn.setColumnType(ViewColumnTypeEnum.STRING);
            tableColumn.setDetailColumnType(DetailColumnTypeEnum.STRING);
        } else if (StrUtil.containsIgnoreCase(tableColumn.getDataType(), "date")) {
            tableColumn.setColumnType(ViewColumnTypeEnum.DATETIME);
            tableColumn.setDetailColumnType(DetailColumnTypeEnum.DATETIME);
        } else if (StrUtil.containsAnyIgnoreCase(tableColumn.getDataType(), "float", "double", "real", "decimal")) {
            tableColumn.setColumnType(ViewColumnTypeEnum.NUMBER);
            tableColumn.setDetailColumnType(DetailColumnTypeEnum.FLT);
        } else if (StrUtil.containsAnyIgnoreCase(tableColumn.getDataType(), "bigint")) {
            tableColumn.setColumnType(ViewColumnTypeEnum.NUMBER);
            tableColumn.setDetailColumnType(DetailColumnTypeEnum.LONG);
        } else {
            tableColumn.setColumnType(ViewColumnTypeEnum.NUMBER);
            tableColumn.setDetailColumnType(DetailColumnTypeEnum.INT);
        }
    }
}
