package fr.ifremer.adagio.core.dao.technical;

/*
 * #%L
 * Tutti :: Persistence API
 * $Id: TuttiEntities.java 1578 2014-02-07 15:31:18Z tchemit $
 * $HeadURL: http://svn.forge.codelutin.com/svn/tutti/trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/entities/TuttiEntities.java $
 * %%
 * Copyright (C) 2012 Ifremer
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * #L%
 */

import java.io.File;
import java.math.BigDecimal;
import java.math.MathContext;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.cfg.AvailableSettings;
import org.hibernate.cfg.Environment;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.jdbc.datasource.DataSourceUtils;

import com.google.common.base.Preconditions;

import fr.ifremer.adagio.core.config.AdagioConfiguration;

/**
 * Usefull method around DAO and entities.
 *
 * @author tchemit <chemit@codelutin.com>
 * @author Benoit Lavenier <benoit.lavenier@e-is.pro>
 * @since 3.5
 */
public class DaoUtils {

    private final static String JDBC_URL_PREFIX_HSQLDB = "jdbc:hsqldb:file:";
    
    /** Logger. */
    private static final Log log = LogFactory.getLog(DaoUtils.class);
    
    protected DaoUtils() {
        // helper class does not instanciate
    }
    
    /**
     * Create a new hibernate configuration, with all hbm.xml files
     * for the schema need for app
     * 
     * @return the hibernate Configuration
     */
    public static Properties getConnectionProperties(String jdbcUrl, String username, String password, String schema, String dialect, String driver) {

        // Building a new configuration
        Properties p = new Properties();

        // Set driver
        p.setProperty(Environment.DRIVER, driver);

        // Set hibernate dialect
        p.setProperty(Environment.DIALECT, dialect);

        // To be able to retrieve connection
        p.setProperty(Environment.URL, jdbcUrl);
        p.setProperty(Environment.USER, username);
        p.setProperty(Environment.PASS, password);

        if (StringUtils.isNotBlank(schema)) {
            p.setProperty(Environment.DEFAULT_SCHEMA, schema);
        }

        // Try with synonyms enable
        p.setProperty(AvailableSettings.ENABLE_SYNONYMS, "true");

        // Pour tester avec le metadata generic (normalement plus long pour Oracle)
        // cfg.setProperty("hibernatetool.metadatadialect", "org.hibernate.cfg.rveng.dialect.JDBCMetaDataDialect");
        if (jdbcUrl.startsWith("jdbc:oracle")) {
            p.setProperty("hibernatetool.metadatadialect", "org.hibernate.cfg.rveng.dialect.OracleMetaDataDialect");
        }

        return p;
    }

    public static void closeSilently(Statement statement) {
        try {
            if (statement != null && !statement.isClosed()) {
                statement.close();
            }
        } catch (AbstractMethodError e) {
            try {
                statement.close();
            } catch (SQLException e1) {
            }            
            if (log.isDebugEnabled()) {
                log.debug("Fix this linkage error, damned hsqlsb 1.8.0.7:(");
            }
        } catch (IllegalAccessError e) {
            if (log.isDebugEnabled()) {
                log.debug("Fix this IllegalAccessError error, damned hsqlsb 1.8.0.7:(");
            }
        } catch (Exception e) {
            if (log.isErrorEnabled()) {
                log.error("Could not close statement, but do not care", e);
            }
        }
    }
    
    public static void closeSilently(Connection connection) {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (Exception e) {
            if (log.isErrorEnabled()) {
                log.error("Could not close connection, but do not care", e);
            }
        }
    }

    public static void closeSilently(ResultSet statement) {
        try {
            if (statement != null && !statement.isClosed()) {

                statement.close();
            }
        } catch (AbstractMethodError e) {
            try {
                statement.close();
            } catch (SQLException e1) {
            }            
            if (log.isDebugEnabled()) {
                log.debug("Fix this linkage error, damned hsqlsb 1.8.0.7:(");
            }
        } catch (IllegalAccessError e) {
            if (log.isDebugEnabled()) {
                log.debug("Fix this IllegalAccessError error, damned hsqlsb 1.8.0.7:(");
            }
        } catch (Exception e) {
            if (log.isErrorEnabled()) {
                log.error("Could not close statement, but do not care", e);
            }
        }
    }

    public static void closeSilently(Session session) {
        try {
            if (session != null && session.isOpen()) {

                session.close();
            }
        } catch (Exception e) {
            if (log.isErrorEnabled()) {
                log.error("Could not close session, but do not care", e);
            }
        }
    }
    
    public static Connection createConnection(Properties connectionProperties) throws SQLException {
        return createConnection(
                connectionProperties.getProperty(Environment.URL),
                connectionProperties.getProperty(Environment.USER),
                connectionProperties.getProperty(Environment.PASS)
        );
    }

    public static String getUrl(Properties connectionProperties) {
        return connectionProperties.getProperty(Environment.URL);
    }

    public static Connection createConnection(String jdbcUrl,
                                              String user,
                                              String password) throws SQLException {
        Connection connection = DriverManager.getConnection(jdbcUrl,
                                                            user,
                                                            password);
        connection.setAutoCommit(false);
        return connection;
    }

    public static void fillConnectionProperties(Properties p,
                                                String url,
                                                String username,
                                                String password) {
        p.put(Environment.URL, url);
        p.put(Environment.USER, username);
        p.put(Environment.PASS, password);
    }

    public static String getJdbcUrl(File directory, String dbName) {
        String jdbcUrl = JDBC_URL_PREFIX_HSQLDB + directory.getAbsolutePath() + "/" + dbName;
        jdbcUrl = jdbcUrl.replaceAll("\\\\", "/");
        return jdbcUrl;
    }
    
    public static boolean isFileDatabase(String jdbcUrl) {
        Preconditions.checkNotNull(jdbcUrl);
        return jdbcUrl.startsWith(JDBC_URL_PREFIX_HSQLDB);
    }
    
    /**
     * Check if connection properties are valid. Try to open a SQL connection, then close it.
     * If no error occur, the connection is valid.
     * @param jdbcDriver
     * @param jdbcUrl
     * @param user
     * @param password
     * @return
     */
    public static boolean isValidConnectionProperties(
            String jdbcDriver,
            String jdbcUrl,
            String user,
            String password) {
        String driverClassName = jdbcDriver;
        try {
            Class<?> driverClass = Class.forName(driverClassName);
            DriverManager.registerDriver((java.sql.Driver)driverClass.newInstance());
        } catch (Exception e) {
            log.error("Could not load JDBC Driver: " + e.getMessage(), e);
            return false;
        } 
        
        Connection connection = null;
        try {
            connection = createConnection(
                    jdbcUrl,
                    user,
                    password);
            return true;
        } catch (SQLException e) {
            log.error("Could not connect to database: " + e.getMessage().trim());
        } finally {
            DaoUtils.closeSilently(connection);
        }
        return false;
    }


    private static final double EARTH_RADIUS = 6378288.0;

    private static final MathContext MATH_CONTEXT_4_DIGIT = new MathContext(4);

    private static DecimalFormatSymbols symbols;

    private static DecimalFormat decimalFormat;

    public static int computeDistanceInMeters(Float startLatitude,
                                              Float startLongitude,
                                              Float endLatitude,
                                              Float endLongitude) {

        double sLat = startLatitude * Math.PI / 180.0;
        double sLong = startLongitude * Math.PI / 180.0;
        double eLat = endLatitude * Math.PI / 180.0;
        double eLong = endLongitude * Math.PI / 180.0;

        Double d = EARTH_RADIUS *
                   (Math.PI / 2 - Math.asin(Math.sin(eLat) * Math.sin(sLat)
                                            + Math.cos(eLong - sLong) * Math.cos(eLat) * Math.cos(sLat)));
        return d.intValue();
    }

    public static String getDistanceInMilles(Float distance) {
        String distanceText;
        if (distance != null) {
            Float distanceInMilles = distance / 1852;
            distanceText = String.format("%.3f", distanceInMilles);

        } else {
            distanceText = "";
        }
        return distanceText;
    }

    public static float getRoundedLengthStep(float lengthStep, boolean aroundUp) {
        int intValue = (int) ((lengthStep + (aroundUp ? 0.001f : 0f)) * 10);
        float result = intValue / 10f;
        return result;
    }

    public static DecimalFormatSymbols getDecimalFormatSymbols() {
        if (symbols == null) {
            symbols = new DecimalFormatSymbols();
            symbols.setDecimalSeparator('.');
            symbols.setGroupingSeparator(' ');
        }
        return symbols;
    }

    public static DecimalFormat getDecimalFormat(int minDecimal, int maxDecimal) {
        if (decimalFormat == null) {
            decimalFormat = new DecimalFormat();
            decimalFormat.setDecimalFormatSymbols(getDecimalFormatSymbols());
            decimalFormat.setGroupingUsed(false);
        }
        decimalFormat.setMinimumFractionDigits(minDecimal);
        decimalFormat.setMaximumFractionDigits(maxDecimal);
        return decimalFormat;
    }

    public static String getWeightStringValue(Float weight) {
        String textValue;
        if (weight != null) {
            DecimalFormat weightDecimalFormat = getDecimalFormat(1, 3);
            textValue = weightDecimalFormat.format(weight);

        } else {
            textValue = "";
        }
        return textValue;
    }

    public static <N extends Number> N getValueOrComputedValue(N value, N computedValue) {
        return value == null ? computedValue : value;
    }

    public static <N extends Number> Boolean getValueOrComputedValueComputed(N value, N computedValue) {
        Boolean result;
        if (value == null) {

            result = computedValue == null ? null : true;
        } else {
            result = false;
        }
        return result;
    }

    /**
     * Round the given value to max 4 digits.
     *
     * @param value the float to round.
     * @return the rounded value
     * @since 1.0.1
     */
    public static float roundKiloGram(float value) {
        BigDecimal sumB = new BigDecimal(value);
        float result = sumB.abs(MATH_CONTEXT_4_DIGIT).floatValue();
        return result;
    }

    /**
     * Compare two weights with rounding them to kilograms.
     *
     * @param v0 first weight to compare
     * @param v1 second weight to compare
     * @return 1 if v0 > v1, -1 if v0 < v1, 0 if v0 == v1
     */
    public static int compareWeights(float v0, float v1) {
        v0 = roundKiloGram(v0);
        v1 = roundKiloGram(v1);
        float delta = v0 - v1;
        int result;
        if (delta > 0.00001) {
            // v0 > v1
            result = 1;
        } else if (delta < -0.0001f) {
            // v0 < v1
            result = -1;
        } else {
            // v0 == v1
            result = 0;
        }
        return result;
    }

    public static boolean isSmallerWeight(float v0, float v1) {
        return compareWeights(v0, v1) < 0;
    }

    public static boolean isGreaterWeight(float v0, float v1) {
        return compareWeights(v0, v1) > 0;
    }

    public static boolean isEqualWeight(float v0, float v1) {
        return compareWeights(v0, v1) == 0;
    }

    public static boolean isNotEqualWeight(float v0, float v1) {
        return compareWeights(v0, v1) != 0;
    }
    
    /**
     * set parameter values for a query: 'statusValidCode' and 'statusTemporaryCode'
     * @param query a query with this parameters inside
     * @return the given query object
     */
    public static Query withStatus(Query query) {
        query.setString("statusValidCode", AdagioConfiguration.getInstance().getStatusCodeValid());
        query.setString("statusTemporaryCode",AdagioConfiguration.getInstance().getStatusCodeTemporary());
        return query;
    }
    
    public static Double convertToDouble(Float floatValue) {
        if (floatValue == null) return null;
        // TODO : trouver une meilleur solution (attention ŕ ne pas perdre de précision)
        return Double.parseDouble(Float.toString(floatValue));
    }
    
    public static Float convertToFloat(Double doubleValue) {
        if (doubleValue == null) return null;
        // TODO : trouver une meilleur solution (attention ŕ ne pas perdre de précision)
        return Float.parseFloat(Double.toString(doubleValue));
    }
    
    public static int sqlUpdate(DataSource dataSource, String sql) {
        Connection connection = DataSourceUtils.getConnection(dataSource);
        try {
            return sqlUpdate(connection, sql);
        } 
        finally {
            DataSourceUtils.releaseConnection(connection, dataSource);
        }
    }
    
    public static int sqlUpdate(Connection connection, String sql) {
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
        } catch (SQLException ex) {
            closeSilently(stmt);
            throw new DataAccessResourceFailureException("Could not open database connection", ex);
        }
        
        // Log using a special logger
        if (log.isDebugEnabled()) {
            log.debug(sql);
        }
        
        try {
            return stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            throw new DataIntegrityViolationException("Could not execute query: " + sql, ex);
        }
        finally {
            closeSilently(stmt);
        }
    }
    
    public static Object sqlUnique(DataSource dataSource, String sql) {
        Connection connection = DataSourceUtils.getConnection(dataSource);
        try {
            return sqlUnique(connection, sql);
        }
        finally {
            DataSourceUtils.releaseConnection(connection, dataSource);
        }
    }
    
    @SuppressWarnings("unchecked")
    public static <T extends Object> T sqlUniqueTyped(DataSource dataSource, String sql) {
        return (T)sqlUnique(dataSource, sql);
    }
    
    public static Object sqlUnique(Connection connection, String sql) {
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
        } catch (SQLException ex) {
            closeSilently(stmt);
            throw new DataAccessResourceFailureException("Could not open database connection", ex);
        }
        
        // Log using a special logger
        if (log.isDebugEnabled()) {
            log.debug(sql);
        }
        
        try {
            ResultSet rs = stmt.executeQuery(sql);
            if (!rs.next()) {
                throw new DataRetrievalFailureException("Executed query return no row: " + sql);
            }
            Object result = rs.getObject(1);
            if (rs.next()) {
                throw new DataRetrievalFailureException("Executed query has more than one row: " + sql);
            }
            return result;
            
        } catch (SQLException ex) {
            throw new DataIntegrityViolationException("Could not execute query: " + sql, ex);
        }
        finally {
            closeSilently(stmt);
        }
    }
    
    @SuppressWarnings("unchecked")
    public static <T extends Object> T sqlUniqueTyped(Connection connection, String sql) {
        return (T)sqlUnique(connection, sql);
    }
    
    public static void shutdownDatabase(Connection connection) {
        try {
            String jdbcUrl = connection.getMetaData().getURL();
            if (jdbcUrl.startsWith(JDBC_URL_PREFIX_HSQLDB)) {
                sqlUpdate(connection, "SHUTDOWN");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }        
    }
    
    public static void shutdownDatabase(Properties connectionProperties) throws SQLException {
        Connection conn = DaoUtils.createConnection(connectionProperties);
        try {
            shutdownDatabase(conn);
        } finally {
            closeSilently(conn);
        }
    }
}