package dbfit.environment;

import dbfit.annotations.DatabaseEnvironment;
import dbfit.api.AbstractDbEnvironment;
import dbfit.util.DbParameterAccessor;
import dbfit.util.DbParameterAccessorsMapBuilder;
import dbfit.util.Direction;
import static dbfit.util.Direction.*;
import static dbfit.util.LangUtils.enquoteAndJoin;
import dbfit.util.TypeNormaliserFactory;
import static dbfit.environment.SqlServerTypeNameNormaliser.normaliseTypeName;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import static org.apache.commons.lang3.ObjectUtils.defaultIfNull;

@DatabaseEnvironment(name="SqlServer", driver="com.microsoft.sqlserver.jdbc.SQLServerDriver")
public class SqlServerEnvironment extends AbstractDbEnvironment {

    public SqlServerEnvironment(String driverClassName) {
        super(driverClassName);
        defaultParamPatternString = "@([A-Za-z0-9_]+)";
        TypeNormaliserFactory.setNormaliser(java.sql.Time.class,
                new MillisecondTimeNormaliser());
    }

    public boolean supportsOuputOnInsert() {
        return false;
    }

    @Override
    protected String getConnectionString(String dataSource) {
        return "jdbc:sqlserver://" + dataSource;
    }

    @Override
    protected String getConnectionString(String dataSource, String database) {
        return getConnectionString(dataSource) + ";database=" + database;
    }

    @Override
    public void connect(String connectionString, Properties info) throws SQLException {
        // Add sendTimeAsDatetime=false option to enforce sending Time as
        // java.sql.Time (otherwise some precision is lost in conversions)
        super.connect(connectionString + ";sendTimeAsDatetime=false", info);
    }

    public Map<String, DbParameterAccessor> getAllColumns(String tableOrViewName)
            throws SQLException {
        String qry = " select c.[name], TYPE_NAME(c.system_type_id) as [Type], c.max_length, "
                + " 0 As is_output, 0 As is_cursor_ref "
                + " from " + objectDatabasePrefix(tableOrViewName) + "sys.columns c "
                + " where c.object_id = OBJECT_ID(?) "
                + " order by column_id";
        return readIntoParams(tableOrViewName, qry);
    }

    private Map<String, DbParameterAccessor> readIntoParams(String objname,
            String query) throws SQLException {
        DbParameterAccessorsMapBuilder params = new DbParameterAccessorsMapBuilder(dbfitToJdbcTransformerFactory);

        objname = objname.replaceAll("[^a-zA-Z0-9_.#$]", "");
        String bracketedName = enquoteAndJoin(objname.split("\\."), ".", "[", "]");

        try (PreparedStatement dc = currentConnection.prepareStatement(query)) {
            dc.setString(1, bracketedName);
            ResultSet rs = dc.executeQuery();

            while (rs.next()) {
                String paramName = defaultIfNull(rs.getString(1), "");
                params.add(paramName,
                           getParameterDirection(rs.getInt(4), paramName),
                           getSqlType(rs.getString(2)),
                           getJavaClass(rs.getString(2)));
            }
        }

        return params.toMap();
    }

    // List interface has sequential search, so using list instead of array to
    // map types
    private static List<String> stringTypes = Arrays.asList(new String[] {
            "VARCHAR", "NVARCHAR", "CHAR", "NCHAR", "TEXT", "NTEXT",
            "UNIQUEIDENTIFIER" });
    private static List<String> intTypes = Arrays
            .asList(new String[] { "INT" });
    private static List<String> booleanTypes = Arrays
            .asList(new String[] { "BIT" });
    private static List<String> floatTypes = Arrays
            .asList(new String[] { "REAL" });
    private static List<String> doubleTypes = Arrays
            .asList(new String[] { "FLOAT" });
    private static List<String> longTypes = Arrays
            .asList(new String[] { "BIGINT" });
    private static List<String> shortTypes = Arrays.asList(new String[] {
            "TINYINT", "SMALLINT" });

    private static List<String> numericTypes = Arrays.asList("NUMERIC");
    private static List<String> decimalTypes = Arrays.asList(new String[] {
            "DECIMAL", "MONEY", "SMALLMONEY" });
    private static List<String> timestampTypes = Arrays.asList(new String[] {
            "SMALLDATETIME", "DATETIME", "DATETIME2" });
    private static List<String> dateTypes = Arrays.asList("DATE");
    private static List<String> timeTypes = Arrays.asList("TIME");

    // private static List<String> refCursorTypes = Arrays.asList(new String[] {
    // });
    // private static List<String> doubleTypes=Arrays.asList(new
    // String[]{"DOUBLE"});

    // private static string[] BinaryTypes=new string[] {"BINARY","VARBINARY", "TIMESTAMP"};
    // private static string[] GuidTypes = new string[] { "UNIQUEIDENTIFIER" };
    // private static string[] VariantTypes = new string[] { "SQL_VARIANT" };

    private String objectDatabasePrefix(String dbObjectName) {
        String objectDatabasePrefix = "";
        String[] objnameParts = dbObjectName.split("\\.");
        if (objnameParts.length == 3) {
            objectDatabasePrefix = objnameParts[0] + ".";
        }
        return objectDatabasePrefix;
    }

    private static Direction getParameterDirection(int isOutput, String name) {
        if (name.isEmpty()) {
            return RETURN_VALUE;
        }

        return (isOutput == 1) ? INPUT_OUTPUT : INPUT;
    }

    private static int getSqlType(String dataType) {
        // todo:strip everything from first blank
        dataType = normaliseTypeName(dataType);

        if (stringTypes.contains(dataType))
            return java.sql.Types.VARCHAR;
        if (numericTypes.contains(dataType))
            return java.sql.Types.NUMERIC;
        if (decimalTypes.contains(dataType))
            return java.sql.Types.DECIMAL;
        if (intTypes.contains(dataType))
            return java.sql.Types.INTEGER;
        if (timestampTypes.contains(dataType))
            return java.sql.Types.TIMESTAMP;
        if (dateTypes.contains(dataType))
            return java.sql.Types.DATE;
        if (timeTypes.contains(dataType))
            return java.sql.Types.TIME;
        if (booleanTypes.contains(dataType))
            return java.sql.Types.BOOLEAN;
        if (floatTypes.contains(dataType))
            return java.sql.Types.FLOAT;
        if (doubleTypes.contains(dataType))
            return java.sql.Types.DOUBLE;

        if (longTypes.contains(dataType))
            return java.sql.Types.BIGINT;
        if (shortTypes.contains(dataType))
            return java.sql.Types.SMALLINT;

        throw new UnsupportedOperationException("Type " + dataType
                + " is not supported");
    }

    public Class<?> getJavaClass(String dataType) {
        dataType = normaliseTypeName(dataType);
        if (stringTypes.contains(dataType))
            return String.class;
        if (numericTypes.contains(dataType))
            return BigDecimal.class;
        if (decimalTypes.contains(dataType))
            return BigDecimal.class;
        if (intTypes.contains(dataType))
            return Integer.class;
        if (timestampTypes.contains(dataType))
            return java.sql.Timestamp.class;
        if (dateTypes.contains(dataType))
            return java.sql.Date.class;
        if (timeTypes.contains(dataType))
            return java.sql.Time.class;
        if (booleanTypes.contains(dataType))
            return Boolean.class;
        if (floatTypes.contains(dataType))
            return Float.class;
        if (doubleTypes.contains(dataType))
            return Double.class;
        if (longTypes.contains(dataType))
            return Long.class;
        if (shortTypes.contains(dataType))
            return Short.class;

        throw new UnsupportedOperationException("Type " + dataType
                + " is not supported");
    }

    public Map<String, DbParameterAccessor> getAllProcedureParameters(
            String procName) throws SQLException {
        return readIntoParams(
                procName,
                "select [name], [Type], max_length, is_output, is_cursor_ref from "
                    + "("
                    + "   select "
                    + "       p.[name], TYPE_NAME(p.system_type_id) as [Type], "
                    + "       p.max_length, p.is_output, p.is_cursor_ref, "
                    + "       p.parameter_id, 0 as set_id, p.object_id "
                    + "   from " + objectDatabasePrefix(procName) + "sys.parameters p "
                    + "   union all select "
                    + "        '' as [name], 'int' as [Type], "
                    + "        4 as max_length, 1 as is_output, 0 as is_cursor_ref, "
                    + "        null as parameter_id, 1 as set_id, object_id "
                    + "   from " + objectDatabasePrefix(procName) + "sys.objects where type in (N'P', N'PC') "
                    + ") as u where object_id = OBJECT_ID(?) order by set_id, parameter_id");
    }

    public String buildInsertCommand(String tableName,
            DbParameterAccessor[] accessors) {
        StringBuilder sb = new StringBuilder("insert into ");
        sb.append(tableName).append("(");
        String comma = "";

        StringBuilder values = new StringBuilder();

        for (DbParameterAccessor accessor : accessors) {
            if (accessor.hasDirection(Direction.INPUT)) {
                sb.append(comma);
                values.append(comma);
                //This will allow column names that have spaces or are keywords.
                sb.append("[" + accessor.getName() + "]");
                values.append("?");
                comma = ",";
            }
        }
        sb.append(") values (");
        sb.append(values);
        sb.append(")");
        return sb.toString();
    }
}

