/*
 * Copyright (C) 2003-2011 eXo Platform SAS.
 *
 * 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 General Public License
 * along with this program; if not, see<http://www.gnu.org/licenses/>.
 */
package org.exoplatform.services.jcr.impl.util.jdbc;

import org.exoplatform.commons.utils.IOUtil;
import org.exoplatform.commons.utils.PrivilegedFileHelper;
import org.exoplatform.services.database.utils.DialectConstants;
import org.exoplatform.services.database.utils.JDBCUtils;
import org.exoplatform.services.jcr.config.RepositoryConfigurationException;
import org.exoplatform.services.jcr.config.WorkspaceEntry;
import org.exoplatform.services.jcr.impl.Constants;
import org.exoplatform.services.jcr.impl.storage.jdbc.DBConstants;
import org.exoplatform.services.jcr.impl.storage.jdbc.JDBCDataContainerConfig;
import org.exoplatform.services.jcr.impl.storage.jdbc.JDBCDataContainerConfig.DatabaseStructureType;
import org.exoplatform.services.jcr.impl.storage.jdbc.JDBCWorkspaceDataContainer;
import org.exoplatform.services.log.ExoLogger;
import org.exoplatform.services.log.Log;

import java.io.IOException;

/**
 * Created by The eXo Platform SAS.
 * 
 * <br/>Date: 2011
 *
 * @author <a href="mailto:alex.reshetnyak@exoplatform.com.ua">Alex Reshetnyak</a> 
 * @version $Id: DBInitializerHelper.java 111 2011-11-11 11:11:11Z rainf0x $
 */
public class DBInitializerHelper
{
   /**
    * Logger.
    */
   protected final static Log LOG = ExoLogger.getLogger("exo.jcr.component.core.DBInitializerHelper");

   /**
    * Table prefix for all tables used in JCR.
    */
   public static final String JCR_TABLE_PREFIX = "JCR_";

   /**
    * Returns SQL scripts for initialization database for defined {@link JDBCDataContainerConfig}.  
    */
   public static String prepareScripts(JDBCDataContainerConfig containerConfig) throws IOException
   {
      String itemTableSuffix = getItemTableSuffix(containerConfig);
      String valueTableSuffix = getValueTableSuffix(containerConfig);
      String refTableSuffix = getRefTableSuffix(containerConfig);

      boolean isolatedDB = containerConfig.dbStructureType == DatabaseStructureType.ISOLATED;

      return prepareScripts(containerConfig.initScriptPath, itemTableSuffix, valueTableSuffix, refTableSuffix, isolatedDB);
   }

   /**
    * Returns SQL scripts for initialization database for defined {@link WorkspaceEntry}. 
    * 
    * @param wsEntry
    *          workspace configuration 
    * @param dialect
    *          database dialect which is used, since {@link JDBCWorkspaceDataContainer#DB_DIALECT} parameter 
    *          can contain {@link DialectConstants#DB_DIALECT_AUTO} value it is necessary to resolve dialect
    *          before based on database connection.
    */
   public static String prepareScripts(WorkspaceEntry wsEntry, String dialect) throws IOException,
      RepositoryConfigurationException
   {
      String itemTableSuffix = getItemTableSuffix(wsEntry);
      String valueTableSuffix = getValueTableSuffix(wsEntry);
      String refTableSuffix = getRefTableSuffix(wsEntry);

      DatabaseStructureType dbType = DBInitializerHelper.getDatabaseType(wsEntry);

      boolean isolatedDB = dbType == DatabaseStructureType.ISOLATED;
      String initScriptPath = DBInitializerHelper.scriptPath(dialect, dbType.isMultiDatabase());

      return prepareScripts(initScriptPath, itemTableSuffix, valueTableSuffix, refTableSuffix, isolatedDB);
   }

   /**
    * Preparing SQL scripts for database initialization.
    */
   private static String prepareScripts(String initScriptPath, String itemTableSuffix, String valueTableSuffix,
      String refTableSuffix, boolean isolatedDB) throws IOException
   {
      String scripts = IOUtil.getStreamContentAsString(PrivilegedFileHelper.getResourceAsStream(initScriptPath));

      if (isolatedDB)
      {
         scripts =
            scripts.replace("MITEM", itemTableSuffix).replace("MVALUE", valueTableSuffix)
               .replace("MREF", refTableSuffix);
      }

      return scripts;
   }

   /**
    * Returns path where SQL scripts for database initialization is stored.
    */
   public static String scriptPath(String dbDialect, boolean multiDb)
   {
      String suffix = multiDb ? "m" : "s";

      String sqlPath = null;
      if (dbDialect.startsWith(DBConstants.DB_DIALECT_ORACLE))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.ora.sql";
      }
      else if (dbDialect.startsWith(DBConstants.DB_DIALECT_PGSQL))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.pgsql.sql";
      }
      else if (dbDialect.equals(DBConstants.DB_DIALECT_MYSQL))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.mysql.sql";
      }
      else if (dbDialect.equals(DBConstants.DB_DIALECT_MYSQL_NDB))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.mysql-ndb.sql";
      }
      else if (dbDialect.equals(DBConstants.DB_DIALECT_MYSQL_NDB_UTF8))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.mysql-ndb-utf8.sql";
      }
      else if (dbDialect.equals(DBConstants.DB_DIALECT_MYSQL_MYISAM))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.mysql-myisam.sql";
      }
      else if (dbDialect.equals(DBConstants.DB_DIALECT_MYSQL_UTF8))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.mysql-utf8.sql";
      }
      else if (dbDialect.equals(DBConstants.DB_DIALECT_MYSQL_MYISAM_UTF8))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.mysql-myisam-utf8.sql";
      }
      else if (dbDialect.startsWith(DBConstants.DB_DIALECT_MSSQL))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.mssql.sql";
      }
      else if (dbDialect.startsWith(DBConstants.DB_DIALECT_DERBY))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.derby.sql";
      }
      else if (dbDialect.equals(DBConstants.DB_DIALECT_DB2V8))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.db2v8.sql";
      }
      else if (dbDialect.startsWith(DBConstants.DB_DIALECT_DB2))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.db2.sql";
      }
      else if (dbDialect.startsWith(DBConstants.DB_DIALECT_SYBASE))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.sybase.sql";
      }
      else if (dbDialect.startsWith(DBConstants.DB_DIALECT_INGRES))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.ingres.sql";
      }
      else if (dbDialect.startsWith(DBConstants.DB_DIALECT_H2))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.h2.sql";
      }
      else if (dbDialect.startsWith(DBConstants.DB_DIALECT_HSQLDB))
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.sql";
      }
      else
      {
         sqlPath = "/conf/storage/jcr-" + suffix + "jdbc.sql";
      }

      return sqlPath;
   }

   /**
    * Initialization script for root node based on {@link JDBCDataContainerConfig}.
    */
   public static String getRootNodeInitializeScript(JDBCDataContainerConfig containerConfig)
   {
      boolean multiDb = containerConfig.dbStructureType.isMultiDatabase();
      String itemTableName = getItemTableName(containerConfig);

      return getRootNodeInitializeScript(itemTableName, multiDb);
   }

   /**
    * Initialization script for root node.
    */
   public static String getRootNodeInitializeScript(String itemTableName, boolean multiDb)
   {
      String singeDbScript =
         "insert into " + itemTableName + "(ID, PARENT_ID, NAME, CONTAINER_NAME, VERSION, I_CLASS, I_INDEX, "
            + "N_ORDER_NUM) VALUES('" + Constants.ROOT_PARENT_UUID + "', '" + Constants.ROOT_PARENT_UUID + "', '"
            + Constants.ROOT_PARENT_NAME + "', '" + Constants.ROOT_PARENT_CONAINER_NAME + "', 0, 0, 0, 0)";

      String multiDbScript =
         "insert into " + itemTableName + "(ID, PARENT_ID, NAME, VERSION, I_CLASS, I_INDEX, " + "N_ORDER_NUM) VALUES('"
            + Constants.ROOT_PARENT_UUID + "', '" + Constants.ROOT_PARENT_UUID + "', '" + Constants.ROOT_PARENT_NAME
            + "', 0, 0, 0, 0)";

      return multiDb ? multiDbScript : singeDbScript;
   }

   public static String getItemTableSuffix(WorkspaceEntry wsConfig) throws RepositoryConfigurationException
   {
      return getTableSuffix(getDatabaseType(wsConfig), getDBTableSuffix(wsConfig), "ITEM");
   }

   public static String getValueTableSuffix(WorkspaceEntry wsConfig) throws RepositoryConfigurationException
   {
      return getTableSuffix(getDatabaseType(wsConfig), getDBTableSuffix(wsConfig), "VALUE");
   }

   public static String getRefTableSuffix(WorkspaceEntry wsConfig) throws RepositoryConfigurationException
   {
      return getTableSuffix(getDatabaseType(wsConfig), getDBTableSuffix(wsConfig), "REF");
   }

   public static String getItemTableName(WorkspaceEntry wsConfig) throws RepositoryConfigurationException
   {
      return JCR_TABLE_PREFIX + getTableSuffix(getDatabaseType(wsConfig), getDBTableSuffix(wsConfig), "ITEM");
   }

   public static String getValueTableName(WorkspaceEntry wsConfig) throws RepositoryConfigurationException
   {
      return JCR_TABLE_PREFIX + getTableSuffix(getDatabaseType(wsConfig), getDBTableSuffix(wsConfig), "VALUE");
   }

   public static String getRefTableName(WorkspaceEntry wsConfig) throws RepositoryConfigurationException
   {
      return JCR_TABLE_PREFIX + getTableSuffix(getDatabaseType(wsConfig), getDBTableSuffix(wsConfig), "REF");
   }

   public static String getItemTableSuffix(JDBCDataContainerConfig containerConfig)
   {
      return getTableSuffix(containerConfig.dbStructureType, containerConfig.dbTableSuffix, "ITEM");
   }

   public static String getValueTableSuffix(JDBCDataContainerConfig containerConfig)
   {
      return getTableSuffix(containerConfig.dbStructureType, containerConfig.dbTableSuffix, "VALUE");
   }

   public static String getRefTableSuffix(JDBCDataContainerConfig containerConfig)
   {
      return getTableSuffix(containerConfig.dbStructureType, containerConfig.dbTableSuffix, "REF");
   }

   public static String getItemTableName(JDBCDataContainerConfig containerConfig)
   {
      return JCR_TABLE_PREFIX + getTableSuffix(containerConfig.dbStructureType, containerConfig.dbTableSuffix, "ITEM");
   }

   public static String getValueTableName(JDBCDataContainerConfig containerConfig)
   {
      return JCR_TABLE_PREFIX + getTableSuffix(containerConfig.dbStructureType, containerConfig.dbTableSuffix, "VALUE");
   }

   public static String getRefTableName(JDBCDataContainerConfig containerConfig)
   {
      return JCR_TABLE_PREFIX + getTableSuffix(containerConfig.dbStructureType, containerConfig.dbTableSuffix, "REF");
   }

   private static String getTableSuffix(JDBCDataContainerConfig.DatabaseStructureType dbType, String dbTableSuffix,
      String forTable)
   {
      String tableSuffix = "";
      switch (dbType)
      {
         case MULTI :
            tableSuffix = "M" + forTable;
            break;
         case SINGLE :
            tableSuffix = "S" + forTable;
            break;
         case ISOLATED :
            tableSuffix = forTable.substring(0, 1) + dbTableSuffix;
            break;
      }
      return tableSuffix;
   }

   /**
    * Returns SQL script for create objects such as index, primary of foreign key. 
    */
   public static String getObjectScript(String objectName, boolean multiDb, String dialect, WorkspaceEntry wsEntry)
      throws RepositoryConfigurationException, IOException
   {
      String scripts = prepareScripts(wsEntry, dialect);

      String sql = null;
      for (String query : JDBCUtils.splitWithSQLDelimiter(scripts))
      {
         String q = JDBCUtils.cleanWhitespaces(query);
         if (q.contains(objectName))
         {
            if (sql != null)
            {
               throw new RepositoryConfigurationException("Can't find unique script for object creation. Object name: "
                  + objectName);
            }

            sql = q;
         }
      }

      if (sql != null)
      {
         return sql;
      }

      throw new RepositoryConfigurationException("Script for object creation is not found. Object name: " + objectName);
   }

   /**
    * Returns {@link DatabaseStructureType} based on workspace configuration.
    */
   public static DatabaseStructureType getDatabaseType(WorkspaceEntry wsConfig) throws RepositoryConfigurationException
   {
      try
      {
         if (wsConfig.getContainer().getParameterBoolean("multi-db"))
         {
            return JDBCDataContainerConfig.DatabaseStructureType.MULTI;
         }
         else
         {
            return JDBCDataContainerConfig.DatabaseStructureType.SINGLE;
         }
      }
      catch (Exception e)
      {
         String dbStructureType =
            wsConfig.getContainer().getParameterValue(JDBCWorkspaceDataContainer.DB_STRUCTURE_TYPE).toUpperCase();
         return JDBCDataContainerConfig.DatabaseStructureType.valueOf(dbStructureType);
      }
   }

   /**
    * Returns value of {@link JDBCWorkspaceDataContainer#DB_TABLENAME_SUFFIX} parameter 
    * from workspace configuration.
    */
   public static String getDBTableSuffix(WorkspaceEntry wsConfig)
   {
      String defaultSuffix = replaceIncorrectChars(wsConfig.getName());

      String suffix =
         wsConfig.getContainer().getParameterValue(JDBCWorkspaceDataContainer.DB_TABLENAME_SUFFIX, defaultSuffix);
      return suffix;
   }

   /**
    * Tries to fix name of the workspace if it is not corresponding to SQL table name specification.
    */
   private static String replaceIncorrectChars(String workspaceName)
   {
      return workspaceName.replaceAll("[^A-Za-z_0-9]", "").toUpperCase();
   }

   /**
    * Returns database dialects stored in configuration and {@link DialectConstants#DB_DIALECT_AUTO}
    * in other case. Always in upper register.
    */
   public static String getDatabaseDialect(WorkspaceEntry wsConfig)
   {
      String dialect =
         wsConfig.getContainer().getParameterValue(JDBCWorkspaceDataContainer.DB_DIALECT, DBConstants.DB_DIALECT_AUTO);

      return dialect.toUpperCase();
   }
}
