/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.1.1 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 */

package com.queplix.core.utils.sql.generic;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.jxb.entity.types.SqlSType;
import com.queplix.core.jxb.sqlwrapper.Property;
import com.queplix.core.utils.StringHelper;
import com.queplix.core.utils.SystemHelper;
import com.queplix.core.utils.log.AbstractLogger;
import com.queplix.core.utils.sql.SqlWrapper;
import com.queplix.core.utils.sql.error.SQLDeleteConflictException;
import com.queplix.core.utils.sql.error.SQLDuplicateKeyException;
import com.queplix.core.utils.sql.error.SQLIndexConflictException;
import com.queplix.core.utils.sql.parser.SqlTypeParser;
import org.apache.regexp.RE;
import org.apache.regexp.RESyntaxException;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

/**
 * Generic SQL operations wrapper implementation.
 * !!! Don't share the instance between EJBs.
 *
 * @author [ALB] Baranov Andrey
 * @author [ONZ] Oleg N. Zhovtanyuk
 * @version $Revision: 1.2 $ $Date: 2006/01/20 15:19:10 $
 */
public abstract class SqlWrapperImpl
        extends AbstractLogger implements SqlWrapper {

    // ================================================================== Constants

    /**
     * Property - DataSource JNDI name.
     */
    protected static final String DATASOURCE_PROPERTY = "datasource";

    /**
     * SQL stored procedure to get next key.
     */
    protected static final String NEXT_KEY_PROC = "QX_NEXT_KEY";

    // ================================================================== Fields

    // Temporary data.
    protected String dsJNDI;

    // ================================================ Interface implementation

    /* (non-Javadoc)
     * @see SqlWrapper#init
     */

    public void init(Property[] properties) {

        if(properties == null) {
            WARN("No properties found");
            return;
        }

        // Read property and initialize datasource JNDI names
        for(int i = 0; i < properties.length; i++) {
            Property p = properties[i];
            String name = p.getName();
            String value = p.getValue();

            if(name.equalsIgnoreCase(DATASOURCE_PROPERTY)) {
                this.dsJNDI = value;
            }
        }
    }

    /* (non-Javadoc)
     * @see SqlWrapper#doConnection()
     */
    public Connection doConnection() {

        // >>> debug
        // System.err.println(">>>>>>>>>>>>>>> doConnection()");
        // Thread.dumpStack();
        // <<<

        Connection con = null;
        try {
//            INFO( "### doDataSource(): " + doDataSource().getClass().getName() );
            con = doDataSource().getConnection();
        } catch (SQLException ex) {
            ERROR(ex);
            throw new GenericSystemException(
                    "Can't get connection: " + ex.getMessage(), ex);
        }
        return con;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#doConnection(boolean)
     */
    public Connection doConnection(boolean autocommit) {
        Connection con = null;
        try {
            con = doDataSource().getConnection();
            con.setAutoCommit(autocommit);
        } catch (SQLException ex) {
            ERROR(ex);
            throw new GenericSystemException(
                    "Can't get connection: " + ex.getMessage(), ex);
        }
        return con;
    }

    /* (non-Javadoc)
     * @see SqlWrapper#doStatement(Connection)
     */
    public Statement doStatement(Connection con) {
        Statement stat = null;
        try {
            stat = con.createStatement();
        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "Can`t create statement: " + ex.getMessage(), ex);
        }
        return stat;
    }

    /* (non-Javadoc)
     * @see SqlWrapper#doPreparedStatement(Connection, String)
     */
    public PreparedStatement doPreparedStatement(Connection con, String sql) {

        PreparedStatement stat = null;
        try {
            stat = con.prepareStatement(sql);
        } catch (SQLException ex) {
            ERROR(ex);
            throw new GenericSystemException(
                    "Can`t create statement for SQL '" + sql + "': " + ex
                            .getMessage(), ex);
        }

        if(!SystemHelper.isProductionMode()) {
            printSQL(sql, -1);
        }
        return stat;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#closeConnection(Connection)
     */
    public void closeConnection(Connection con) {
        try {
            if(con != null) {
                con.close();
            }
        } catch (Exception ex) {
        }
    }

    /* (non-Javadoc)
     * @see SqlWrapper#closeConnection(Statement)
     */
    public void closeConnection(Statement stat) {
        try {
            if(stat != null) {
                stat.close();
            }
        } catch (Exception ex) {
        }
    }

    /* (non-Javadoc)
     * @see SqlWrapper#closeConnection(Connection, Statement)
     */
    public void closeConnection(Connection con, Statement stat) {
        try {
            if(stat != null) {
                stat.close();
            }
        } catch (Exception ex) {
        }
        try {
            if(con != null) {
                con.close();
            }
        } catch (Exception ex) {
        }
    }

    /* (non-Javadoc)
     * @see SqlWrapper#closeResultSet(ResultSet)
     */
    public void closeResultSet(ResultSet rs) {
        try {
            rs.close();
        } catch (Exception ex) {
        }
    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeQuery(Statement, String)
     */
    public ResultSet executeQuery(Statement stat, String sql)
            throws SQLException {

        long time = 0;
        if(!SystemHelper.isProductionMode()) {
            time = System.currentTimeMillis();
        }

        ResultSet rs = stat.executeQuery(sql);

        if(!SystemHelper.isProductionMode()) {
            printSQL(sql, time);
        }

        return rs;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeQuery(PreparedStatement)
     */
    public ResultSet executeQuery(PreparedStatement stat)
            throws SQLException {
        return stat.executeQuery();
    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeUpdate(Statement, String)
     */
    public int executeUpdate(Statement stat, String sql)
            throws SQLDuplicateKeyException, SQLIndexConflictException,
            SQLDeleteConflictException, SQLException {

        // Initialization.
        long time = 0;
        boolean isProduction = SystemHelper.isProductionMode();
        if(!isProduction) {
            time = System.currentTimeMillis();
        }

        // SQL job.
        int rows = 0;
        try {
            rows = stat.executeUpdate(sql);
            if(!isProduction) {
                printSQL(sql, time);
            }
        } catch (SQLException ex) {
            throwSQLException(ex);
        }

        // Ok.
        return rows;
    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeUpdate(PreparedStatement)
     */
    public int executeUpdate(PreparedStatement stat)
            throws SQLDuplicateKeyException, SQLIndexConflictException,
            SQLDeleteConflictException, SQLException {
        int rows = 0;
        try {
            return stat.executeUpdate();
        } catch (SQLException ex) {
            throwSQLException(ex);
        }
        return rows;
    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeSql(Connection, String, int)
     */
    public final String executeSql(Connection con, String sql, int sqltype)
            throws SQLException {

        String value = null;
        Statement stat = null;

        try {
            stat = doStatement(con);
            ResultSet rs = executeQuery(stat, sql);
            if(rs.next()) {
                Object obj = getParser(sqltype).getObject(rs, 1);
                if(!rs.wasNull()) {
                    value = obj.toString();
                }
            }
        } finally {
            closeConnection(stat);
        }

        return value;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeSql(Connection, String, String, String, long)
     */
    public final String executeSql(Connection con, String tableName,
                                   String fieldName, String pkeyName, long pkey)
            throws SQLException {

        String value = null;

        // Make SQL query.
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ").append(fieldName).append(" FROM ");
        sql.append(tableName).append(" WHERE " + pkeyName + " = ");
        sql.append(pkey);

        // SQL job.
        Statement stat = null;
        try {
            stat = doStatement(con);
            ResultSet rs = executeQuery(stat, sql.toString());
            if(rs.next()) {
                value = rs.getString(1);
            }
        } finally {
            closeConnection(stat);
        }

        return value;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#executeSql(Connection, String, String, String, String)
     */
    public final String executeSql(Connection con, String tableName,
                                   String fieldName, String pkeyName,
                                   String pkey)
            throws SQLException {

        String value = null;

        // Make SQL query.
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ").append(fieldName).append(" FROM ");
        sql.append(tableName).append(" WHERE " + pkeyName + " = '");
        sql.append(pkey).append("'");

        // SQL job.
        Statement stat = null;
        try {
            stat = doStatement(con);
            ResultSet rs = executeQuery(stat, sql.toString());
            if(rs.next()) {
                value = rs.getString(1);
            }
        } finally {
            closeConnection(stat);
        }

        return value;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#getParser(int)
     */
    public final SqlTypeParser getParser(int sqltype) {

        // Get parser instance.
        SqlTypeParser parser = null;
        switch(sqltype) {
            case SqlSType.INT_TYPE:
                parser = getIntParser();
                break;
            case SqlSType.LONG_TYPE:
                parser = getLongParser();
                break;
            case SqlSType.FLOAT_TYPE:
                parser = getFloatParser();
                break;
            case SqlSType.STRING_TYPE:
                parser = getStringParser();
                break;
            case SqlSType.TIMESTAMP_TYPE:
                parser = getTimestampParser();
                break;
            case SqlSType.DATE_TYPE:
                parser = getDateParser();
                break;
            case SqlSType.TIME_TYPE:
                parser = getTimeParser();
                break;
            case SqlSType.MEMO_TYPE:
                parser = getMemoParser();
                break;
            case SqlSType.MEMO_LONG_TYPE:
                parser = getLongParser();
                break;
            case SqlSType.BINARY_TYPE:
                parser = getBinaryParser();
                break;
            default:
                throw new GenericSystemException(
                        "Can't get parser for sql type '" + sqltype + "'");
        }

        // Ok.
        return parser;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#getNextKey(Connection, String)
     */
    public long getNextKey(Connection con, String table)
            throws SQLException {
        return getNextKey(con, table, 1);
    }

    /* (non-Javadoc)
     * @see SqlWrapper#getNextKey(Connection, String, int)
     */
    public long getNextKey(Connection con, String table, int range)
            throws SQLException {

        CallableStatement cs = null;
        table = table.toLowerCase();
        long keyValue;

        // Make SQL query.
        String sql = "{call " + NEXT_KEY_PROC + "('" + table + "', " + range
                + ", ?)}";
        DEBUG("next key sql: " + sql);

        try {
            cs = con.prepareCall(sql);
            cs.registerOutParameter(1, Types.NUMERIC);
            cs.execute();

            keyValue = cs.getLong(1);
            if(cs.wasNull()) {
                keyValue = StringHelper.EMPTY_NUMBER;
            }

        } finally {
            closeConnection(cs);
        }

        // Ok.
        return keyValue;

    }

    /* (non-Javadoc)
     * @see SqlWrapper#typeMapping(int, int)
     */
    public SqlSType typeMapping(int sqlType, int sqlColumnSize) {

        // Get type.
        SqlSType sqlSType = null;
        switch(sqlType) {
            case Types.TINYINT:
            case Types.SMALLINT:
                sqlSType = SqlSType.INT;
                break;
            case Types.INTEGER:
            case Types.NUMERIC:
            case Types.DECIMAL:
                if(sqlColumnSize > 0 && sqlColumnSize < 32) {
                    sqlSType = SqlSType.INT;
                } else {
                    sqlSType = SqlSType.LONG;
                }
                break;
            case Types.BIGINT:
                sqlSType = SqlSType.LONG;
                break;
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.REAL:
                sqlSType = SqlSType.FLOAT;
                break;
            case Types.CHAR:
            case Types.VARCHAR:
                sqlSType = SqlSType.STRING;
                break;
            case Types.DATE:
                sqlSType = SqlSType.DATE;
                break;
            case Types.TIMESTAMP:
                sqlSType = SqlSType.TIMESTAMP;
                break;
            case Types.TIME:
                sqlSType = SqlSType.TIME;
                break;
            case Types.CLOB:
                sqlSType = SqlSType.MEMO;
                break;
            case Types.LONGVARCHAR:
                sqlSType
                        = SqlSType.MEMO;//text type is mapped to MEMO sql type, because till 2.6 application version it is fixing by the handmade attributes.
                break;
        }

        // Ok.
        return sqlSType;

    }

    // ======================================================= Protected methods

    /**
     * @param ex SQLException to (re-)throw
     * @throws SQLException
     * @todo Describe it!
     */
    protected abstract void throwSQLException(SQLException ex)
            throws SQLException;

    /**
     * Soundex function value getter. Eliminates the code duplication in
     * database-specific subclasses.
     *
     * @param con        database connection
     * @param soundexSQL database-specific SQL query to get soundex value
     * @param str        string to get soundex value of
     * @return soundex value
     * @throws SQLException
     */
    protected String getSoundexBySQL(Connection con, String soundexSQL,
                                     String str)
            throws SQLException {

        PreparedStatement stat = null;
        String soundex = null;

        try {
            stat = doPreparedStatement(con, soundexSQL);
            stat.setString(1, str);
            ResultSet rs = executeQuery(stat);
            if(rs.next()) {
                soundex = rs.getString(1);
            }

        } finally {
            closeConnection(stat);
        }

        return soundex;
    }

    /**
     * Current schema getter. Eliminates the code duplication in database-
     * specific subclasses.
     *
     * @param con       database connection
     * @param schemaSQL database-specific SQL query to get current schema
     * @return current schema name
     * @throws SQLException
     */
    protected String getCurrentSchemaBySQL(Connection con, String schemaSQL)
            throws SQLException {

        Statement stat = null;
        String schema = null;

        try {
            stat = doStatement(con);
            ResultSet rs = executeQuery(stat, schemaSQL);
            if(rs.next()) {
                schema = rs.getString(1);
            }

        } finally {
            closeConnection(stat);
        }

        return schema;
    }

    /**
     * Prints SQL info.
     *
     * @param sql  SQL query
     * @param time from application start
     */
    protected void printSQL(String sql, long time) {
        try {
            time = System.currentTimeMillis() - time;
            RE re = new RE("(\\n|\\t)");
            String newSql = re.subst(sql, " ");
            INFO(time + ";" + newSql);
        } catch (RESyntaxException ex) {
            ERROR(ex);
        }
    }

    // ====================================================== DataSource getters

    //
    // DataSource object getter.
    // [ALB] switched from protected to public for testing

    public synchronized javax.sql.DataSource doDataSource() {

        // !!! Don't save reference on DataSource as SqlWrapper
        // instance can be used in different contexts.
        javax.sql.DataSource ds;

        if(dsJNDI == null) {
            throw new NullPointerException("Cannot load DataSource JNDI name");
        }

        try {
            javax.naming.InitialContext context
                    = new javax.naming.InitialContext();
            ds = (javax.sql.DataSource) context.lookup(dsJNDI);

            if(getLogger().isDebugEnabled()) {
                DEBUG("Data source '" + dsJNDI + "' loaded. Class: " +
                        ds.getClass().getName());
            }

        } catch (Exception ex) {
            String err = "Cannot lookup data source by '" + dsJNDI +
                    "': " + ex.getMessage();

            ERROR(err, ex);
            throw new GenericSystemException(err, ex);
        }

        return ds;
    }
}
