/*
 * RHQ Management Platform
 * Copyright (C) 2005-2008 Red Hat, Inc.
 * All rights reserved.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License, version 2, as
 * published by the Free Software Foundation, and/or the GNU Lesser
 * General Public License, version 2.1, also as published by the Free
 * Software Foundation.
 *
 * 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 and the GNU Lesser General Public License
 * for more details.
 *
 * You should have received a copy of the GNU General Public License
 * and the GNU Lesser General Public License along with this program;
 * if not, write to the Free Software Foundation, Inc.,
 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
 */
package org.rhq.core.util.jdbc;

import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Take care of some JDBC related stuff. Mostly closing resources
 *
 * @author Heiko W. Rupp
 */
public class JDBCUtil {

    private static final Log log = LogFactory.getLog(JDBCUtil.class);
    private static final String SQL_ERROR = "Error closing a resource: ";

    /*
     * The next constants specify how to generate the next value in a sequence for
     * table %s
     */
    private static final String POSTGRES_NEXTVAL_SQL = "(SELECT nextval('%s_id_seq'::text))";
    private static final String ORACLE_NEXTVAL_SQL = "%s_id_seq.nextval";
    private static final String H2_NEXTVAL_SQL = "(SELECT nextval('%s_id_seq'))";

    public static void safeClose(Statement stm, ResultSet rs) {
        safeClose(rs);
        safeClose(stm);
    }

    public static void safeClose(Connection conn, Statement stm, ResultSet rs) {
        safeClose(rs);
        safeClose(stm);
        safeClose(conn);
    }

    public static void safeClose(Connection con) {
        if (con != null) {
            try {
                con.close();
            } catch (Exception e) {
                log.error(SQL_ERROR, e);
            }
        }
    }

    public static void safeClose(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                log.error(SQL_ERROR, e);
            }
        }
    }

    public static void safeClose(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                log.error(SQL_ERROR, e);
            }
        }
    }

    /**
     * Bind the passed values to the parameters of the {@link PreparedStatement}, starting at position startBinding
     *
     * @param  ps              PreparedStatement to use
     * @param  values          the array of int values to fill in
     * @param  startingBinding the starting position of the parameter to fill in
     *
     * @throws SQLException if the {@link Statement}.setInt() call fails
     */
    public static void bindNTimes(PreparedStatement ps, int[] values, int startingBinding) throws SQLException {
        for (int i = 0; i < values.length; i++) {
            ps.setInt(startingBinding + i, values[i]);
        }
    }

    /**
     * Populate the passed query with bindCount number of placeholders '?'. The method will not put necessary parenteses
     * around the placeholders.
     *
     * @param  query       the original query including text to replace
     * @param  replaceable the part of the original query that should be replaced by '?'s.
     * @param  bindCount   how many placeholders '?' should be generated
     *
     * @return The modified query
     */
    public static String transformQueryForMultipleInParameters(String query, String replaceable, int bindCount) {
        String replacement = generateInBinds(bindCount);
        return query.replace(replaceable, replacement);
    }

    /**
     * Generate count '? separated by comma
     */
    public static String generateInBinds(int count) {
        StringBuilder b = new StringBuilder();
        for (int i = 0; i < count; i++) {
            if (i > 0) {
                b.append(",");
            }

            b.append("?");
        }

        return b.toString();
    }

    /**
     * Generate the correct SQL statement to obtain the next value from a sequence/table
     * generator for the passed table. The passed connection gets closed in case of an error.
     * @param conn  A valid database connection
     * @param tableName The name of the table to use
     * @return A statement that obtains the next value for the passed table 
     */
    public static String getNextValSql(Connection conn, String tableName) {
        String nextvalSql;
        try {
            DatabaseMetaData meta = conn.getMetaData();
            String name = meta.getDatabaseProductName().toLowerCase();
            if (name.contains("postgres")) {
                nextvalSql = POSTGRES_NEXTVAL_SQL;
            } else if (name.contains("oracle")) {
                nextvalSql = ORACLE_NEXTVAL_SQL;
            } else if (name.contains("h2")) {
                nextvalSql = H2_NEXTVAL_SQL;
            } else {
                JDBCUtil.safeClose(conn);
                throw new IllegalStateException("Unsupported database type: " + name);
            }
        } catch (Exception e) {
            JDBCUtil.safeClose(conn);
            throw new IllegalStateException("Failed to determine database type.");
        }

        nextvalSql = String.format(nextvalSql, tableName);
        return nextvalSql;
    }

    /**
     * @deprecated This should correctly be handled at the place where the stream is about to be closed JBNADM-2600
     */
    @Deprecated
    public static void safeClose(InputStream in) {
        if (in != null) {
            try {
                in.close();
            } catch (Exception e) {
                log.error(SQL_ERROR, e);
            }
        }
    }

    /**
     * @deprecated This should correctly be handled at the place where the stream is about to be closed JBNADM-2600
     */
    @Deprecated
    public static void safeClose(OutputStream out) {
        if (out != null) {
            try {
                out.close();
            } catch (Exception e) {
                log.error(SQL_ERROR, e);
            }
        }
    }

    /**
     * @deprecated This should correctly be handled at the place where the stream is about to be closed JBNADM-2600
     */
    @Deprecated
    public static void safeClose(Reader reader) {
        if (reader != null) {
            try {
                reader.close();
            } catch (Exception e) {
                log.error(SQL_ERROR, e);
            }
        }
    }

    /**
     * @deprecated This should correctly be handled at the place where the stream is about to be closed JBNADM-2600
     */
    @Deprecated
    public static void safeClose(Writer writer) {
        if (writer != null) {
            try {
                writer.close();
            } catch (Exception e) {
                log.error(SQL_ERROR, e);
            }
        }
    }

    public static String convertSQLExceptionToString(SQLException e) {
        StringBuilder result = new StringBuilder(e.toString());
        if (e.getNextException() != null) {
            result.append(" - causes:");
            SQLException cause = e;
            while ((cause = cause.getNextException()) != null) {
                result.append("\n\t").append(e);
            }
        }
        return result.toString();
    }

}
