/**
 * $Revision$
 * $Date$
 *
 * Copyright (C) 2005-2008 Jive Software. All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (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.apache.org/licenses/LICENSE-2.0
 *
 * 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 org.jivesoftware.database;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

import org.jivesoftware.database.bugfix.OF33;
import org.jivesoftware.openfire.XMPPServer;
import org.jivesoftware.openfire.container.Plugin;
import org.jivesoftware.openfire.container.PluginManager;
import org.jivesoftware.util.JiveGlobals;
import org.jivesoftware.util.LocaleUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Manages database schemas for Openfire and Openfire plugins. The manager uses the
 * ofVersion database table to figure out which database schema is currently installed
 * and then attempts to automatically apply database schema changes as necessary.<p>
 *
 * Running database schemas automatically requires appropriate database permissions.
 * Without those permissions, the automatic installation/upgrade process will fail
 * and users will be prompted to apply database changes manually.
 *
 * @see DbConnectionManager#getSchemaManager()
 *
 * @author Matt Tucker
 */
public class SchemaManager {

	private static final Logger Log = LoggerFactory.getLogger(SchemaManager.class);

    private static final String CHECK_VERSION_OLD =
            "SELECT minorVersion FROM jiveVersion";
    private static final String CHECK_VERSION =
            "SELECT version FROM ofVersion WHERE name=?";
    private static final String CHECK_VERSION_JIVE =
            "SELECT version FROM jiveVersion WHERE name=?";

    /**
     * Current Openfire database schema version.
     */
    private static final int DATABASE_VERSION = 21;

    /**
     * Creates a new Schema manager.
     */
    SchemaManager() {

    }

    /**
     * Checks the Openfire database schema to ensure that it's installed and up to date.
     * If the schema isn't present or up to date, an automatic update will be attempted.
     *
     * @param con a connection to the database.
     * @return true if database schema checked out fine, or was automatically installed
     *      or updated successfully.
     */
    public boolean checkOpenfireSchema(Connection con) {
        // Change 'wildfire' to 'openfire' in ofVersion table (update to new name)
        updateToOpenfire(con);
        try {
            return checkSchema(con, "openfire", DATABASE_VERSION,
                    new ResourceLoader() {
                        public InputStream loadResource(String resourceName) {
                            File file = new File(JiveGlobals.getHomeDirectory() + File.separator +
                                    "resources" + File.separator + "database", resourceName);
                            try {
                                return new FileInputStream(file);
                            }
                            catch (FileNotFoundException e) {
                                return null;
                            }
                        }
                    });
        }
        catch (Exception e) {
            Log.error(LocaleUtils.getLocalizedString("upgrade.database.failure"), e);
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.failure"));
        }
        return false;
    }

    /**
     * Checks the plugin's database schema (if one is required) to ensure that it's
     * installed and up to date. If the schema isn't present or up to date, an automatic
     * update will be attempted.
     *
     * @param plugin the plugin.
     * @return true if database schema checked out fine, or was automatically installed
     *      or updated successfully, or if it isn't needed. False will only be returned
     *      if there is an error.
     */
    public boolean checkPluginSchema(final Plugin plugin) {
        final PluginManager pluginManager = XMPPServer.getInstance().getPluginManager();
        String schemaKey = pluginManager.getDatabaseKey(plugin);
        int schemaVersion = pluginManager.getDatabaseVersion(plugin);
        // If the schema key or database version aren't defined, then the plugin doesn't
        // need database tables.
        if (schemaKey == null || schemaVersion == -1) {
            return true;
        }
        Connection con = null;
        try {
            con = DbConnectionManager.getConnection();
            return checkSchema(con, schemaKey, schemaVersion, new ResourceLoader() {
                public InputStream loadResource(String resourceName) {
                    File file = new File(pluginManager.getPluginDirectory(plugin) +
                            File.separator + "database", resourceName);
                    try {
                        return new FileInputStream(file);
                    }
                    catch (FileNotFoundException e) {
                        return null;
                    }
                }
            });
        }
        catch (Exception e) {
            Log.error(LocaleUtils.getLocalizedString("upgrade.database.failure"), e);
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.failure"));
        }
        finally {
            DbConnectionManager.closeConnection(con);
        }
        return false;
    }

    /**
     * Checks to see if the database needs to be upgraded. This method should be
     * called once every time the application starts up.
     *
     * @param con the database connection to use to check the schema with.
     * @param schemaKey the database schema key (name).
     * @param requiredVersion the version that the schema should be at.
     * @param resourceLoader a resource loader that knows how to load schema files.
     * @throws Exception if an error occured.
     * @return True if the schema update was successful.
     */
    private boolean checkSchema(Connection con, String schemaKey, int requiredVersion,
            ResourceLoader resourceLoader) throws Exception
    {
        int currentVersion = -1;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = con.prepareStatement(CHECK_VERSION);
            pstmt.setString(1, schemaKey);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                currentVersion = rs.getInt(1);
            }
        }
        catch (SQLException sqle) {
            // The database schema must not be installed.
            Log.debug("SchemaManager: Error verifying "+schemaKey+" version, probably ignorable.", sqle);
            DbConnectionManager.closeResultSet(rs);
            DbConnectionManager.closeStatement(pstmt);
            if (schemaKey.equals("openfire")) {
                try {
                    // Releases of Openfire before 3.6.0 stored the version in a jiveVersion table.
                    pstmt = con.prepareStatement(CHECK_VERSION_JIVE);
                    pstmt.setString(1, schemaKey);
                    rs = pstmt.executeQuery();
                    if (rs.next()) {
                        currentVersion = rs.getInt(1);
                    }
                }
                catch (SQLException sqlea) {
                    // The database schema must not be installed.
                    Log.debug("SchemaManager: Error verifying "+schemaKey+" version, probably ignorable.", sqlea);
                    DbConnectionManager.closeResultSet(rs);
                    DbConnectionManager.closeStatement(pstmt);
                    // Releases of Openfire before 2.6.0 stored a major and minor version
                    // number so the normal check for version can fail. Check for the
                    // version using the old format in that case.
                    try {
                        if (pstmt != null) {
                            pstmt.close();
                        }
                        pstmt = con.prepareStatement(CHECK_VERSION_OLD);
                        rs = pstmt.executeQuery();
                        if (rs.next()) {
                            currentVersion = rs.getInt(1);
                        }
                    }
                    catch (SQLException sqle2) {
                        // The database schema must not be installed.
                        Log.debug("SchemaManager: Error verifying "+schemaKey+" version, probably ignorable", sqle2);
                    }
                }
            }
        }
        finally {
            DbConnectionManager.closeResultSet(rs);
            DbConnectionManager.closeStatement(pstmt);
        }
        // If already up to date, return.
        if (currentVersion >= requiredVersion) {
            return true;
        }
        // If the database schema isn't installed at all, we need to install it.
        else if (currentVersion == -1) {
            Log.info(LocaleUtils.getLocalizedString("upgrade.database.missing_schema",
                    Arrays.asList(schemaKey)));
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.missing_schema",
                    Arrays.asList(schemaKey)));
            // Resource will be like "/database/openfire_hsqldb.sql"
            String resourceName = schemaKey + "_" +
                    DbConnectionManager.getDatabaseType() + ".sql";
            InputStream resource = resourceLoader.loadResource(resourceName);
            if (resource == null) {
                return false;
            }
            try {
                // For plugins, we will automatically convert jiveVersion to ofVersion
                executeSQLScript(con, resource, !schemaKey.equals("openfire") && !schemaKey.equals("wildfire"));
            }
            catch (Exception e) {
                Log.error(e.getMessage(), e);
                return false;
            }
            finally {
                try {
                    resource.close();
                }
                catch (Exception e) {
                    // Ignore.
                }
            }
            Log.info(LocaleUtils.getLocalizedString("upgrade.database.success"));
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.success"));
            return true;
        }
        // Must have a version of the schema that needs to be upgraded.
        else {
            // The database is an old version that needs to be upgraded.
            Log.info(LocaleUtils.getLocalizedString("upgrade.database.old_schema",
                    Arrays.asList(currentVersion, schemaKey, requiredVersion)));
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.old_schema",
                    Arrays.asList(currentVersion, schemaKey, requiredVersion)));
            // If the database type is unknown, we don't know how to upgrade it.
            if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.unknown) {
                Log.info(LocaleUtils.getLocalizedString("upgrade.database.unknown_db"));
                System.out.println(LocaleUtils.getLocalizedString("upgrade.database.unknown_db"));
                return false;
            }
            // Upgrade scripts for interbase are not maintained.
            else if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.interbase) {
                Log.info(LocaleUtils.getLocalizedString("upgrade.database.interbase_db"));
                System.out.println(LocaleUtils.getLocalizedString("upgrade.database.interbase_db"));
                return false;
            }

            // Run all upgrade scripts until we're up to the latest schema.
            for (int i = currentVersion + 1; i <= requiredVersion; i++) {
                InputStream resource = getUpgradeResource(resourceLoader, i, schemaKey);
                
                // apply the 'database-patches-done-in-java'
				try {
					if (i == 21 && schemaKey.equals("openfire")) {
						OF33.executeFix(con);
					}
				} catch (Exception e) {
					Log.error(e.getMessage(), e);
					return false;
				}
                if (resource == null) {
                    continue;
                }
                try {
                    executeSQLScript(con, resource, !schemaKey.equals("openfire") && !schemaKey.equals("wildfire"));
                }
                catch (Exception e) {
                    Log.error(e.getMessage(), e);
                    return false;
                }
                finally {
                    try {
                        resource.close();
                    }
                    catch (Exception e) {
                        // Ignore.
                    }
                }
            }
            Log.info(LocaleUtils.getLocalizedString("upgrade.database.success"));
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.success"));
            return true;
        }
    }

    private InputStream getUpgradeResource(ResourceLoader resourceLoader, int upgradeVersion,
            String schemaKey)
    {
        InputStream resource = null;
        if ("openfire".equals(schemaKey)) {
            // Resource will be like "/database/upgrade/6/openfire_hsqldb.sql"
            String path = JiveGlobals.getHomeDirectory() + File.separator + "resources" +
                    File.separator + "database" + File.separator + "upgrade" + File.separator +
                    upgradeVersion;
            String filename = schemaKey + "_" + DbConnectionManager.getDatabaseType() + ".sql";
            File file = new File(path, filename);
            try {
                resource = new FileInputStream(file);
            }
            catch (FileNotFoundException e) {
                // If the resource is null, the specific upgrade number is not available.
            }
        }
        else {
            String resourceName = "upgrade/" + upgradeVersion + "/" + schemaKey + "_" +
                    DbConnectionManager.getDatabaseType() + ".sql";
            resource = resourceLoader.loadResource(resourceName);
        }
        return resource;
    }

    private void updateToOpenfire(Connection con){
        PreparedStatement pstmt = null;
        try {
            pstmt = con.prepareStatement("UPDATE jiveVersion SET name='openfire' WHERE name='wildfire'");
            pstmt.executeUpdate();
        }
        catch (Exception ex) {
//            Log.warn("Error when trying to update to new name", ex);
            // This is "scary" to see in the logs and causes more confusion than it's worth at this point.
            // So silently move on.
        }
        finally {
            DbConnectionManager.closeStatement(pstmt);
        }
    }

    /**
     * Executes a SQL script.
     *
     * @param con database connection.
     * @param resource an input stream for the script to execute.
     * @param autoreplace automatically replace jiveVersion with ofVersion
     * @throws IOException if an IOException occurs.
     * @throws SQLException if an SQLException occurs.
     */
    private static void executeSQLScript(Connection con, InputStream resource, Boolean autoreplace) throws IOException,
            SQLException
    {
        BufferedReader in = null;
        try {
            in = new BufferedReader(new InputStreamReader(resource));
            boolean done = false;
            while (!done) {
                StringBuilder command = new StringBuilder();
                while (true) {
                    String line = in.readLine();
                    if (line == null) {
                        done = true;
                        break;
                    }
                    // Ignore comments and blank lines.
                    if (isSQLCommandPart(line)) {
                        command.append(" ").append(line);
                    }
                    if (line.trim().endsWith(";")) {
                        break;
                    }
                }
                // Send command to database.
                if (!done && !command.toString().equals("")) {
                    // Remove last semicolon when using Oracle or DB2 to prevent "invalid character error"
                    if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.oracle ||
                            DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.db2) {
                        command.deleteCharAt(command.length() - 1);
                    }
                    try {
                        String cmdString = command.toString();
                        if (autoreplace)  {
                            cmdString = cmdString.replaceAll("jiveVersion", "ofVersion");
                        }
                        Statement stmt = con.createStatement();
                        stmt.execute(cmdString);
                        stmt.close();
                    }
                    catch (SQLException e) {
                        // Lets show what failed
                        Log.error("SchemaManager: Failed to execute SQL:\n"+command.toString());
                        throw e;
                    }
                }
            }
        }
        finally {
            if (in != null) {
                try {
                    in.close();
                }
                catch (Exception e) {
                    Log.error(e.getMessage(), e);
                }
            }
        }
    }

    private static abstract class ResourceLoader {

        public abstract InputStream loadResource(String resourceName);

    }

    /**
     * Returns true if a line from a SQL schema is a valid command part.
     *
     * @param line the line of the schema.
     * @return true if a valid command part.
     */
    private static boolean isSQLCommandPart(String line) {
        line = line.trim();
        if (line.equals("")) {
            return false;
        }
        // Check to see if the line is a comment. Valid comment types:
        //   "//" is HSQLDB
        //   "--" is DB2 and Postgres
        //   "#" is MySQL
        //   "REM" is Oracle
        //   "/*" is SQLServer
        return !(line.startsWith("//") || line.startsWith("--") || line.startsWith("#") ||
                line.startsWith("REM") || line.startsWith("/*") || line.startsWith("*"));
    }
}