/*
 * Copyright (C) 2011 eXo Platform SAS.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 2.1 of
 * the License, or (at your option) any later version.
 *
 * This software 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
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */
package org.exoplatform.services.jcr.impl.storage.jdbc;

import org.exoplatform.commons.utils.SecurityHelper;
import org.exoplatform.services.jcr.impl.Constants;
import org.exoplatform.services.jcr.impl.InspectionLog;
import org.exoplatform.services.jcr.impl.InspectionLog.InspectionStatus;
import org.exoplatform.services.jcr.impl.storage.value.ValueDataNotFoundException;
import org.exoplatform.services.jcr.impl.storage.value.ValueStorageNotFoundException;
import org.exoplatform.services.jcr.storage.value.ValueIOChannel;
import org.exoplatform.services.jcr.storage.value.ValueStoragePluginProvider;
import org.exoplatform.services.log.ExoLogger;
import org.exoplatform.services.log.Log;

import java.io.IOException;
import java.security.PrivilegedActionException;
import java.security.PrivilegedExceptionAction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import javax.jcr.RepositoryException;

/**
 * @author <a href="mailto:skarpenko@exoplatform.com">Sergiy Karpenko</a>
 * @version $Id: exo-jboss-codetemplates.xml 34360 14 жовт. 2011 skarpenko $
 *
 */
public class JDBCWorkspaceDataContainerChecker
{
   protected static final Log LOG = ExoLogger.getLogger("exo.jcr.component.core.JDBCWorkspaceDataContainerChecker");

   /**
    * Check database.
    * <p>
    * Check that database is not broken, and all base relation between jcr-items are not corrupted.
    * </p>
    * 
    * @param inspectionLog - log where inspection results will be placed
    * @return InspectionLog
    * @throws RepositoryException
    * @throws IOException
    */
   public static void checkDB(JDBCWorkspaceDataContainer jdbcDataContainer, InspectionLog inspectionLog)
      throws RepositoryException, IOException
   {

      /**
       * Data class, contains a combination of SQL states, description, field names and status  
       */
      class InspectionQuery
      {
         /**
          * SQL query that must be executed.
          */
         private final String statement;

         /**
          * Inspection query description.
          */
         private final String description;

         /**
          * Field names that must be showed in inspection log if something wrong.
          */
         private final String[] fieldNames;

         /**
          * Corruption status. Is it critical - <b>ERR</b>, or not - <b>WARN</b>.
          */
         private final InspectionStatus status;

         public InspectionQuery(String statement, String[] fieldNames, String headerMessage, InspectionStatus status)
         {
            this.statement = statement;
            this.description = headerMessage;
            this.fieldNames = fieldNames;
            this.status = status;
         }

         public String getStatement()
         {
            return statement;
         }

         public String getDescription()
         {
            return description;
         }

         public String[] getFieldNames()
         {
            return fieldNames;
         }

         public InspectionStatus getStatus()
         {
            return status;
         }
      }

      Set<InspectionQuery> queries = new HashSet<InspectionQuery>();

      // preload queries
      queries.add(new InspectionQuery(jdbcDataContainer.multiDb
         ? "select * from JCR_MITEM I where NOT EXISTS(select * from JCR_MITEM P where P.ID = I.PARENT_ID)"
         : "select * from JCR_SITEM I where I.CONTAINER_NAME='" + jdbcDataContainer.containerName
            + "' and NOT EXISTS(select * from JCR_SITEM P where P.ID = I.PARENT_ID)", new String[]{
         DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME, DBConstants.COLUMN_CLASS},
         "Items that do not have parent nodes", InspectionStatus.ERR));
      queries
         .add(new InspectionQuery(
            jdbcDataContainer.multiDb
               ? "select * from JCR_MITEM N where N.I_CLASS=1 and NOT EXISTS (select * from JCR_MITEM P where P.I_CLASS=2 and P.PARENT_ID=N.ID)"
               : "select * from JCR_SITEM N where N.CONTAINER_NAME='"
                  + jdbcDataContainer.containerName
                  + "' and N.I_CLASS=1 and NOT EXISTS (select * from JCR_SITEM P where P.I_CLASS=2 and P.PARENT_ID=N.ID and P.CONTAINER_NAME='"
                  + jdbcDataContainer.containerName + "')",
            new String[]{DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME},
            "Nodes that do not have at least one property", InspectionStatus.ERR));
      queries
         .add(new InspectionQuery(
            jdbcDataContainer.multiDb
               ? "select * from JCR_MVALUE V where NOT EXISTS(select * from JCR_MITEM P where V.PROPERTY_ID = P.ID and P.I_CLASS=2)"
               : "select V.* from JCR_SVALUE V, JCR_SITEM I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='"
                  + jdbcDataContainer.containerName
                  + "' and NOT EXISTS(select * from JCR_SITEM P where P.CONTAINER_NAME='"
                  + jdbcDataContainer.containerName + "' and V.PROPERTY_ID = P.ID and P.I_CLASS=2)", new String[]{
               DBConstants.COLUMN_ID, DBConstants.COLUMN_VPROPERTY_ID},
            "All value records that has not owner-property record", InspectionStatus.ERR));
      queries
         .add(new InspectionQuery(
            jdbcDataContainer.multiDb
               ? "select * from JCR_MITEM P where P.I_CLASS=2 and NOT EXISTS( select * from JCR_MVALUE V where V.PROPERTY_ID=P.ID)"
               : "select * from JCR_SITEM P where P.CONTAINER_NAME='" + jdbcDataContainer.containerName
                  + "' and P.I_CLASS=2 and NOT EXISTS( select * from JCR_SVALUE V where V.PROPERTY_ID=P.ID)",
            new String[]{DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME},
            "All properties that have not value record.", InspectionStatus.ERR));
      
      // The differences in the queries by DB dialect.
      // Oracle doesn't work correct with default query because empty value stored as null value.
      String statement;
      if (jdbcDataContainer.dbDialect.equalsIgnoreCase(DBConstants.DB_DIALECT_SYBASE))
      {
         statement = jdbcDataContainer.multiDb
            ? "select * from JCR_MVALUE where (STORAGE_DESC is null and DATA like null) or (STORAGE_DESC is not null and not DATA like null)"
            : "select V.* from JCR_SVALUE V, JCR_SITEM I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='"
               + jdbcDataContainer.containerName
               + "'  AND ((STORAGE_DESC is null and DATA like null) or (STORAGE_DESC is not null and not DATA like null))";
      }
      else if (jdbcDataContainer.dbDialect.equalsIgnoreCase(DBConstants.DB_DIALECT_ORACLE)
         || jdbcDataContainer.dbDialect.equalsIgnoreCase(DBConstants.DB_DIALECT_ORACLEOCI))
      {
         statement =  jdbcDataContainer.multiDb
         ? "select * from JCR_MVALUE where (STORAGE_DESC is not null and DATA is not null)"
         : "select V.* from JCR_SVALUE V, JCR_SITEM I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='"
            + jdbcDataContainer.containerName
            + "'  AND (STORAGE_DESC is not null and DATA is not null)";
      } else {
         statement =  jdbcDataContainer.multiDb
            ? "select * from JCR_MVALUE where (STORAGE_DESC is null and DATA is null) or (STORAGE_DESC is not null and DATA is not null)"
            : "select V.* from JCR_SVALUE V, JCR_SITEM I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='"
               + jdbcDataContainer.containerName
               + "'  AND ((STORAGE_DESC is null and DATA is null) or (STORAGE_DESC is not null and DATA is not null))";
      }
      queries
         .add(new InspectionQuery(statement, new String[]{DBConstants.COLUMN_ID}, "Incorrect JCR_VALUE records", InspectionStatus.ERR));
      queries
         .add(new InspectionQuery(
            jdbcDataContainer.multiDb
               ? "select * from JCR_MITEM P where P.P_TYPE=9 and NOT EXISTS( select * from JCR_MREF R where P.ID=R.PROPERTY_ID)"
               : "select * from JCR_SITEM P where P.CONTAINER_NAME='" + jdbcDataContainer.containerName
                  + "' and P.P_TYPE=9 and NOT EXISTS( select * from JCR_SREF R where P.ID=R.PROPERTY_ID)",
            new String[]{DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME},
            "Reference properties without reference records", InspectionStatus.ERR));

      // properties can refer to missing node. It is possible to perform this usecase via JCR API with no exceptions 
      queries.add(new InspectionQuery(jdbcDataContainer.multiDb
         ? "select * from JCR_MREF R where NOT EXISTS(select * from JCR_MITEM N where R.NODE_ID=N.ID)"
         : "select * from JCR_SREF R, JCR_SITEM I where R.PROPERTY_ID = I.ID and I.CONTAINER_NAME='"
            + jdbcDataContainer.containerName
            + "'  and NOT EXISTS(select * from JCR_SITEM N where N.CONTAINER_NAME='"
            + jdbcDataContainer.containerName + "' and R.NODE_ID=N.ID)", new String[]{"NODE_ID", "PROPERTY_ID",
         DBConstants.COLUMN_VORDERNUM},
         "Reference records that linked to unexisted nodes. Can be normal for some usecases.", InspectionStatus.WARN));

      // an item is its own parent. 
      queries.add(new InspectionQuery(jdbcDataContainer.multiDb
         ? "select * from JCR_MITEM I where I.ID = I.PARENT_ID and I.NAME <> '" + Constants.ROOT_PARENT_NAME + "'"
         : "select * from JCR_SITEM I where I.ID = I.PARENT_ID and I.CONTAINER_NAME='"
            + jdbcDataContainer.containerName + "' and I.NAME <> '" + Constants.ROOT_PARENT_NAME + "'", new String[]{
         DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME}, "An item is its own parent.",
         InspectionStatus.ERR));

      // using existing DataSource to get a JDBC Connection.
      Connection jdbcConn = jdbcDataContainer.getConnectionFactory().getJdbcConnection();

      try
      {
         // perform all queries on-by-one
         for (InspectionQuery query : queries)
         {
            PreparedStatement st = null;
            ResultSet resultSet = null;
            try
            {
               st = jdbcConn.prepareStatement(query.getStatement());
               // the result of query is expected to be empty 
               resultSet = st.executeQuery();
               if (resultSet.next())
               {
                  // but if result not empty, then inconsistency takes place
                  inspectionLog.logInspectionDescription(query.getDescription());
                  do
                  {
                     StringBuilder record = new StringBuilder();
                     for (String fieldName : query.getFieldNames())
                     {
                        record.append(fieldName);
                        record.append('=');
                        if (fieldName.equals(DBConstants.COLUMN_NORDERNUM)
                           || fieldName.equals(DBConstants.COLUMN_VORDERNUM))
                        {
                           record.append(resultSet.getInt(fieldName));
                        }
                        else
                        {
                           record.append(resultSet.getString(fieldName));
                        }
                        record.append(' ');
                     }
                     // log inconsistency issue.
                     inspectionLog.logBrokenObjectInfo(record.toString(), "", query.getStatus());
                  }
                  while (resultSet.next());
               }
            }
            // safely free resources
            finally
            {
               if (resultSet != null)
               {
                  try
                  {
                     resultSet.close();
                  }
                  catch (SQLException e)
                  {
                     LOG.error(e.getMessage(), e);
                  }
               }
               if (st != null)
               {
                  try
                  {
                     st.close();
                  }
                  catch (SQLException e)
                  {
                     LOG.error(e.getMessage(), e);
                  }
               }
            }
         }
      }
      catch (SQLException e)
      {
         // log unexpected exceptions to log
         inspectionLog.logException("Exception during DB inspection.", e);
      }
      finally
      {
         // safely close connection
         if (jdbcConn != null)
         {
            try
            {
               jdbcConn.close();
            }
            catch (SQLException e)
            {
               LOG.error(e.getMessage(), e);
            }
         }
      }
   }

   /**
    * Inspect ValueStorage. 
    * <p>
    * All ValueDatas that have storage description (that means, value data stored in value storage) will be inspected:
    * <ul>
    * <li> does value exists in value storage;</li>
    * <li> is this value readable;</li>
    * <ul>
    *
    * 
    * @param vsPlugin - value storages
    * @param inspectionLog - log where inspection results will be placed
    * @return resulting InspectionLog
    * @throws RepositoryException
    * @throws IOException
    */
   public static void checkValueStorage(final JDBCWorkspaceDataContainer jdbcDataContainer,
      ValueStoragePluginProvider vsPlugin, InspectionLog inspectionLog) throws RepositoryException, IOException
   {
      final String valueRecordFormat = "ValueData[PROPERTY_ID=%s ORDER_NUM=%d STORAGE_DESC=%s]";

      Connection connection = jdbcDataContainer.getConnectionFactory().getJdbcConnection();
      PreparedStatement st = null;
      ResultSet resultSet = null;
      try
      {
         st =
            connection
               .prepareStatement(jdbcDataContainer.multiDb
                  ? "SELECT PROPERTY_ID, ORDER_NUM, STORAGE_DESC from JCR_MVALUE where STORAGE_DESC is not null"
                  : "SELECT V.PROPERTY_ID, V.ORDER_NUM, V.STORAGE_DESC from JCR_SVALUE V, JCR_SITEM I where I.CONTAINER_NAME='"
                     + jdbcDataContainer.containerName + "' and V.PROPERTY_ID = I.ID and STORAGE_DESC is not null");

         resultSet = st.executeQuery();
         // traverse all values, written to value storage
         if (resultSet.next())
         {
            ValueIOChannel channel = null;
            do
            {
               final String propertyId = resultSet.getString(DBConstants.COLUMN_VPROPERTY_ID);
               final int orderNumber = resultSet.getInt(DBConstants.COLUMN_VORDERNUM);
               final String storageDesc = resultSet.getString(DBConstants.COLUMN_VSTORAGE_DESC);

               // don't acquire channel if it is already open 
               if (channel == null || !channel.getStorageId().equals(storageDesc))
               {
                  try
                  {
                     if (channel != null)
                     {
                        channel.close();
                     }
                     channel = vsPlugin.getChannel(storageDesc);
                  }
                  catch (ValueStorageNotFoundException e)
                  {
                     inspectionLog.logBrokenObjectInfo("ValueStorage " + storageDesc + " not found. "
                        + String.format(valueRecordFormat, propertyId, orderNumber, storageDesc), e.getMessage(),
                        InspectionStatus.ERR);
                     continue;
                  }
               }

               try
               {
                  // check value data
                  final ValueIOChannel vdChannel = channel;
                  SecurityHelper.doPrivilegedExceptionAction(new PrivilegedExceptionAction<Object>()
                  {
                     public Object run() throws ValueDataNotFoundException, IOException
                     {
                        vdChannel.checkValueData(
                           jdbcDataContainer.multiDb ? propertyId : propertyId
                              .substring(jdbcDataContainer.containerName.length()), orderNumber);
                        return null;
                     }
                  });
               }
               // process exception thrown by checkValueData
               catch (PrivilegedActionException e)
               {
                  Throwable ex = e.getCause();
                  if (ex instanceof ValueDataNotFoundException)
                  {
                     inspectionLog.logBrokenObjectInfo(String.format(valueRecordFormat, propertyId, orderNumber,
                        storageDesc)
                        + " not found.", ex.getMessage(), InspectionStatus.ERR);
                  }
                  else if (ex instanceof IOException)
                  {
                     inspectionLog.logException(ex.getMessage(), (IOException)ex);
                  }
                  else
                  {
                     throw new RepositoryException(ex.getMessage(), ex);
                  }
               }
            }
            while (resultSet.next());
         }
      }
      catch (SQLException e)
      {
         // log unexpceted exception
         inspectionLog.logException("Exception during ValueStorage inspection.", e);
      }
      finally
      {
         // safely free resources
         if (resultSet != null)
         {
            try
            {
               resultSet.close();
            }
            catch (SQLException e)
            {
               LOG.error(e.getMessage(), e);
            }
         }

         if (st != null)
         {
            try
            {
               st.close();
            }
            catch (SQLException e)
            {
               LOG.error(e.getMessage(), e);
            }
         }

         if (connection != null)
         {
            try
            {
               connection.close();
            }
            catch (SQLException e)
            {
               LOG.error(e.getMessage(), e);
            }
         }
      }
   }

}
