FormsOnlineDAOJdbc.java

/*
 * Copyright (C) 2000 - 2024 Silverpeas
 *
 * 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.
 *
 * As a special exception to the terms and conditions of version 3.0 of
 * the GPL, you may redistribute this Program in connection with Free/Libre
 * Open Source Software ("FLOSS") applications as described in Silverpeas's
 * FLOSS exception. You should have recieved a copy of the text describing
 * the FLOSS exception, and it is also available here:
 * "https://www.silverpeas.org/legal/floss_exception.html"
 *
 * 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 Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
 */

package org.silverpeas.components.formsonline.model;

import org.silverpeas.components.formsonline.model.RequestCriteria.QUERY_ORDER_BY;
import org.silverpeas.core.SilverpeasRuntimeException;
import org.silverpeas.core.admin.PaginationPage;
import org.silverpeas.core.contribution.ContributionStatus;
import org.silverpeas.core.persistence.Transaction;
import org.silverpeas.core.persistence.jdbc.DBUtil;
import org.silverpeas.core.persistence.jdbc.sql.JdbcSqlQueries;
import org.silverpeas.core.persistence.jdbc.sql.JdbcSqlQuery;
import org.silverpeas.core.persistence.jdbc.sql.SelectResultRowProcess;
import org.silverpeas.core.util.CollectionUtil;
import org.silverpeas.core.util.Mutable;
import org.silverpeas.core.util.Pair;
import org.silverpeas.core.util.SilverpeasArrayList;
import org.silverpeas.core.util.SilverpeasList;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.time.Instant;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static java.util.stream.Collectors.toMap;
import static java.util.stream.Collectors.toSet;
import static org.apache.commons.lang3.ArrayUtils.isNotEmpty;
import static org.silverpeas.components.formsonline.model.FormDetail.ALL_RIGHT_TYPES;
import static org.silverpeas.components.formsonline.model.FormDetail.SENDERS_TYPE;
import static org.silverpeas.components.formsonline.model.FormInstanceValidationType.fromRightsCode;
import static org.silverpeas.components.formsonline.model.RequestCriteria.QUERY_ORDER_BY.CREATION_DATE_DESC;
import static org.silverpeas.components.formsonline.model.RequestCriteria.QUERY_ORDER_BY.ID_DESC;
import static org.silverpeas.core.SilverpeasExceptionMessages.*;
import static org.silverpeas.core.contribution.ContributionStatus.VALIDATED;
import static org.silverpeas.core.persistence.jdbc.sql.JdbcSqlQuery.*;

public class FormsOnlineDAOJdbc implements FormsOnlineDAO {

  // General infos
  private static final String FORMS_TABLENAME = "SC_FormsOnline_Forms";
  private static final String FORMS_INSTANCES_TABLENAME = "SC_FormsOnline_FormInstances";
  private static final String FORMS_INSTANCE_VALIDATIONS_TABLENAME = "SC_FormsOnline_FormInstVali";
  private static final String USER_RIGHTS_TABLENAME = "SC_FormsOnline_UserRights";
  private static final String GROUP_RIGHTS_TABLENAME = "SC_FormsOnline_GroupRights";

  private static final String SELECT_FROM = "SELECT * FROM ";
  private static final String INSERT_INTO = "INSERT INTO ";
  private static final String DELETE_FROM = "DELETE FROM ";

  // Queries about Forms
  private static final String QUERY_LOAD_FORM =
      SELECT_FROM + FORMS_TABLENAME + " where instanceId = ? and id = ?";
  private static final String QUERY_INSERT_FORM = INSERT_INTO + FORMS_TABLENAME +
      "(id, xmlFormName, name, description, title, creatorId, creationDate, state, " +
      "instanceId, hierarchicalValidation, formInstExchangeReceiver, deleteAfterFormInstExchange) " +
      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  private static final String QUERY_UPDATE_FORM = "update " + FORMS_TABLENAME +
      " set xmlFormName = ?, name = ?, description = ?, title = ?, creatorId = ?, creationDate = " +
      "?, state = ?, hierarchicalValidation = ?, formInstExchangeReceiver = ?, " +
      "deleteAfterFormInstExchange = ? where instanceId = ? and id= ? ";
  private static final String QUERY_DELETE_FORM =
      DELETE_FROM + FORMS_TABLENAME + " where instanceId = ? and id = ? ";

  // Queries about Forms instances
  private static final String COUNT_REQUESTS_BY_FORM =
      "select formid, count(*) from " + FORMS_INSTANCES_TABLENAME + " where instanceId = ? group by formid";

  private static final String WHERE_FULL_RIGHT_CLAUSE = " where instanceId = ? and formId = ? and rightType = ?";
  // Queries about Rights
  private static final String QUERY_LOAD_USER_RIGHTS = SELECT_FROM + USER_RIGHTS_TABLENAME +
      WHERE_FULL_RIGHT_CLAUSE;
  private static final String QUERY_LOAD_GROUP_RIGHTS = SELECT_FROM + GROUP_RIGHTS_TABLENAME +
      WHERE_FULL_RIGHT_CLAUSE;
  private static final String QUERY_REMOVE_USER_RIGHTS = DELETE_FROM + USER_RIGHTS_TABLENAME +
      WHERE_FULL_RIGHT_CLAUSE;
  private static final String QUERY_REMOVE_GROUP_RIGHTS = DELETE_FROM + GROUP_RIGHTS_TABLENAME +
      WHERE_FULL_RIGHT_CLAUSE;
  private static final String QUERY_INSERT_USER_RIGHTS =
      INSERT_INTO + USER_RIGHTS_TABLENAME + "(formId, instanceId, rightType, userId) " +
          "VALUES (?, ?, ?, ?)";
  private static final String QUERY_INSERT_GROUP_RIGHTS =
      INSERT_INTO + GROUP_RIGHTS_TABLENAME + "(formId, instanceId, rightType, groupId) " +
          "VALUES (?, ?, ?, ?)";

  private static final String ID = "id";
  private static final String ID_CRITERIA = ID + " = ?";
  private static final String STATE = "state";
  private static final String INSTANCE_ID = "instanceId";
  private static final String FORM_ID = "formId";
  private static final String CREATION_DATE = "creationDate";
  private static final String CREATOR_ID = "creatorId";
  private static final String REQUEST_MSG = "instance on form";
  private static final String FORM_INST_ID = "formInstId";
  private static final String VALIDATION_BY = "validationBy";
  private static final String RIGHT_TYPE = "rightType";
  private static final String VALIDATION_TYPE = "validationType";

  private static final String NOT_EXISTS_SELECT = "NOT EXISTS(SELECT 1";
  private static final String EXISTS_SELECT = "EXISTS(SELECT 1";
  private static final String FORM_INST_ID_CLAUSE = FORM_INST_ID + " = r.id";

  @Override
  public FormDetail createForm(FormDetail formDetail) throws FormsOnlineException {
    try (final Connection con = getConnection();
         final PreparedStatement stmt = con.prepareStatement(QUERY_INSERT_FORM)) {
      int id = DBUtil.getNextId(FORMS_TABLENAME, "id");
      stmt.setInt(1, id);
      stmt.setString(2, formDetail.getXmlFormName());
      stmt.setString(3, formDetail.getName());
      stmt.setString(4, formDetail.getDescription());
      stmt.setString(5, formDetail.getTitle());
      stmt.setString(6, formDetail.getCreatorId());
      prepareDateStatement(stmt, 7, formDetail.getCreationDate());
      stmt.setInt(8, formDetail.getState());
      stmt.setString(9, formDetail.getInstanceId());
      stmt.setBoolean(10, formDetail.isHierarchicalValidation());
      stmt.setString(11, formDetail.getRequestExchangeReceiver().orElse(null));
      stmt.setBoolean(12, formDetail.isDeleteAfterRequestExchange());
      stmt.executeUpdate();
      formDetail.setId(id);
      return formDetail;
    } catch (SQLException se) {
      throw new FormsOnlineException(failureOnAdding("form", formDetail.getName()), se);
    }
  }

  @Override
  public FormDetail deleteForm(FormPK pk) throws FormsOnlineException {
    FormDetail form = getForm(pk);
    try (final Connection con = getConnection();
         final PreparedStatement stmt = con.prepareStatement(QUERY_DELETE_FORM)) {
      for (final String rightType : ALL_RIGHT_TYPES) {
        removeGroupRights(con, pk, rightType);
        removeUserRights(con, pk, rightType);
      }
      stmt.setString(1, pk.getInstanceId());
      stmt.setInt(2, Integer.parseInt(pk.getId()));
      stmt.executeUpdate();
      return form;
    } catch (SQLException se) {
      throw new FormsOnlineException(failureOnDeleting("form", pk.toString()), se);
    }
  }

  @Override
  public FormDetail getForm(FormPK pk) throws FormsOnlineException {
    FormDetail form = null;
    try (final Connection con = getConnection();
         final PreparedStatement stmt = con.prepareStatement(QUERY_LOAD_FORM)) {
      stmt.setString(1, pk.getInstanceId());
      stmt.setInt(2, Integer.parseInt(pk.getId()));
      try (ResultSet rs = stmt.executeQuery()) {
        if (rs.next()) {
          form = fetchFormDetail(rs);
        }
      }
    } catch (SQLException se) {
      throw new FormsOnlineException(failureOnGetting("form", pk.toString()), se);
    }
    return form;
  }

  @Override
  public List<FormDetail> findAllForms(String instanceId, String orderBy) throws FormsOnlineException {
    try {
      final List<FormDetail> forms = new ArrayList<>();
        final JdbcSqlQuery query = JdbcSqlQuery.createSelect("*")
            .from(FORMS_TABLENAME)
            // 1st criteria : correct instanceId
            .where(INSTANCE_ID).in(instanceId)
            .orderBy(orderBy);
        query.execute(r -> {
          forms.add(fetchFormDetail(r));
          return null;
        });
    return forms;
    } catch (SQLException se) {
      throw new FormsOnlineException(
          failureOnGetting("Available forms of instance",
              String.join(",", instanceId)), se);
    }
  }

  @Override
  public void updateForm(FormDetail formDetail) throws FormsOnlineException {
    try (final Connection con = getConnection();
         final PreparedStatement stmt = con.prepareStatement(QUERY_UPDATE_FORM)) {
      stmt.setString(1, formDetail.getXmlFormName());
      stmt.setString(2, formDetail.getName());
      stmt.setString(3, formDetail.getDescription());
      stmt.setString(4, formDetail.getTitle());
      stmt.setString(5, formDetail.getCreatorId());
      prepareDateStatement(stmt, 6, formDetail.getCreationDate());
      stmt.setInt(7, formDetail.getState());
      stmt.setBoolean(8, formDetail.isHierarchicalValidation());
      stmt.setString(9, formDetail.getRequestExchangeReceiver().orElse(null));
      stmt.setBoolean(10, formDetail.isDeleteAfterRequestExchange());
      stmt.setString(11, formDetail.getInstanceId());
      stmt.setInt(12, formDetail.getId());
      stmt.executeUpdate();
    } catch (SQLException se) {
      throw new FormsOnlineException(failureOnUpdate("form", formDetail.getId()), se);
    }
  }

  /*
   * (non-Javadoc)
   * @see FormsOnlineDAO#getReceiversAsGroups(int,
   * java.lang.String)
   */
  @Override
  public List<String> getReceiversAsGroups(FormPK pk, String rightType) throws FormsOnlineException {
    return getGroupRights(pk, rightType);
  }

  /*
   * (non-Javadoc)
   * @see FormsOnlineDAO#getReceiversAsUsers(int, java.lang.String)
   */
  @Override
  public List<String> getReceiversAsUsers(FormPK pk, String rightType) throws FormsOnlineException {
    return getUserRights(pk, rightType);
  }

  /*
   * (non-Javadoc)
   * @see FormsOnlineDAO#getSendersAsGroups(int, java.lang.String)
   */
  @Override
  public List<String> getSendersAsGroups(FormPK pk) throws FormsOnlineException {
    return getGroupRights(pk, SENDERS_TYPE);
  }

  /*
   * (non-Javadoc)
   * @see FormsOnlineDAO#getSendersAsUsers(int, java.lang.String)
   */
  @Override
  public List<String> getSendersAsUsers(FormPK pk) throws FormsOnlineException {
    return getUserRights(pk, SENDERS_TYPE);
  }

  private List<String> getGroupRights(FormPK pk, String rightType)
      throws FormsOnlineException {
    List<String> groupsIds = new ArrayList<>();
    try (final Connection con = getConnection();
         final PreparedStatement stmt = con.prepareStatement(QUERY_LOAD_GROUP_RIGHTS)) {
      stmt.setString(1, pk.getInstanceId());
      stmt.setInt(2, Integer.parseInt(pk.getId()));
      stmt.setString(3, rightType);
      try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
          groupsIds.add(rs.getString("groupId"));
        }
      }
    } catch (SQLException se) {
      throw new FormsOnlineException(failureOnGetting("senders as users of form", pk.toString()),
          se);
    }
    return groupsIds;
  }

  private List<String> getUserRights(FormPK pk, String rightType)
      throws FormsOnlineException {
    List<String> userIds = new ArrayList<>();
    try (final Connection con = getConnection();
         final PreparedStatement stmt = con.prepareStatement(QUERY_LOAD_USER_RIGHTS)) {
      stmt.setString(1, pk.getInstanceId());
      stmt.setInt(2, Integer.parseInt(pk.getId()));
      stmt.setString(3, rightType);
      try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
          userIds.add(rs.getString("userId"));
        }
      }
    } catch (SQLException se) {
      throw new FormsOnlineException(
          failureOnGetting("user rights (" + rightType + ") of form", pk.toString()), se);
    }
    return userIds;
  }

  @Override
  public void updateReceivers(FormPK pk,
      Map<String, Pair<List<String>, List<String>>> userAndGroupIdsByRightTypes) throws FormsOnlineException {
    updateRights(pk, userAndGroupIdsByRightTypes.entrySet().stream().filter(e -> !e.getKey().equals(SENDERS_TYPE)));
  }

  @Override
  public void updateSenders(FormPK pk,
      Map<String, Pair<List<String>, List<String>>> userAndGroupIdsByRightTypes) throws FormsOnlineException {
    updateRights(pk, userAndGroupIdsByRightTypes.entrySet().stream().filter(e -> e.getKey().equals(SENDERS_TYPE)));
  }

  private void updateRights(final FormPK pk,
      final Stream<Map.Entry<String, Pair<List<String>, List<String>>>> userAndGroupIdsByRightTypes)
      throws FormsOnlineException {
    try (final Connection con = getConnection()) {
      userAndGroupIdsByRightTypes.forEach(e -> {
        try {
          final String rightType = e.getKey();
          removeGroupRights(con, pk, rightType);
          removeUserRights(con, pk, rightType);
          final Pair<List<String>, List<String>> ids = e.getValue();
          for (final String newUserId : ids.getFirst()) {
            addUserRights(con, pk, newUserId, rightType);
          }
          for (final String newGroupId : ids.getSecond()) {
            addGroupRights(con, pk, newGroupId, rightType);
          }
        } catch (SQLException sqlE) {
          throw new SilverpeasRuntimeException(sqlE);
        }
      });
    } catch (Exception e) {
      throw new FormsOnlineException(failureOnUpdate("user and group rights of form", pk.toString()), e);
    }
  }

  private void addUserRights(Connection con, FormPK pk, String userId, String rightType)
      throws SQLException {
    try (PreparedStatement stmt = con.prepareStatement(QUERY_INSERT_USER_RIGHTS)) {
      stmt.setInt(1, Integer.parseInt(pk.getId()));
      stmt.setString(2, pk.getInstanceId());
      stmt.setString(3, rightType);
      stmt.setString(4, userId);

      stmt.executeUpdate();
    }
  }

  private void addGroupRights(Connection con, FormPK pk, String groupId, String rightType)
      throws SQLException {
    try (PreparedStatement stmt = con.prepareStatement(QUERY_INSERT_GROUP_RIGHTS)) {
      stmt.setInt(1, Integer.parseInt(pk.getId()));
      stmt.setString(2, pk.getInstanceId());
      stmt.setString(3, rightType);
      stmt.setString(4, groupId);
      stmt.executeUpdate();
    }
  }

  private void removeGroupRights(Connection con, FormPK pk, String rightType) throws SQLException {
    try (PreparedStatement stmt = con.prepareStatement(QUERY_REMOVE_GROUP_RIGHTS)) {
      stmt.setString(1, pk.getInstanceId());
      stmt.setInt(2, Integer.parseInt(pk.getId()));
      stmt.setString(3, rightType);
      stmt.executeUpdate();
    }
  }

  private void removeUserRights(Connection con, FormPK pk, String rightType) throws SQLException {
    try (PreparedStatement stmt = con.prepareStatement(QUERY_REMOVE_USER_RIGHTS)) {
      stmt.setString(1, pk.getInstanceId());
      stmt.setInt(2, Integer.parseInt(pk.getId()));
      stmt.setString(3, rightType);
      stmt.executeUpdate();
    }
  }

  /*
   * (non-Javadoc)
   * @see FormsOnlineDAO#getUserAvailableForms(java.lang.String,
   * java.lang.String, java.lang.String[])
   */
  @Override
  public List<FormDetail> getUserAvailableForms(final Collection<String> instanceIds,
      final String userId, final String[] userGroupIds, String orderBy) throws FormsOnlineException {
    try {
      final List<FormDetail> forms = new ArrayList<>();
      JdbcSqlQuery.executeBySplittingOn(instanceIds, (idBatch, ignore) -> {
          final JdbcSqlQuery query = JdbcSqlQuery.createSelect("*")
          .from(FORMS_TABLENAME)
          // 1st criteria : correct instanceId
          .where(INSTANCE_ID).in(instanceIds)
          // 2nd criteria : state published or old forms that user has sent before unpublish
          .and("(state = ?", FormDetail.STATE_PUBLISHED)
            .or("id in (select formId from " + FORMS_INSTANCES_TABLENAME + " where creatorId = ?))", userId)
          // 3rd criteria : user has receiver rights (directly or from a group)
          .and("(id in (select formId from " + USER_RIGHTS_TABLENAME + " where rightType='S' and userId = ?)", userId);
          if (isNotEmpty(userGroupIds)) {
            query.or("id in (select formId from " + GROUP_RIGHTS_TABLENAME + " where rightType='S' and groupId").in(userGroupIds).addSqlPart(")"); }
          query.addSqlPart(")");
          query.orderBy(orderBy);
          query.execute(r -> {
            forms.add(fetchFormDetail(r));
            return null;
          });
      });
      return forms;
    } catch (SQLException se) {
      throw new FormsOnlineException(
          failureOnGetting("user (" + userId + ") available forms of instances",
              String.join(",", instanceIds)), se);
    }
  }

  /*
   * (non-Javadoc)
   * @see FormsOnlineDAO#getForms(java.util.List)
   */
  @Override
  public List<FormDetail> getForms(Collection<String> formIds) throws FormsOnlineException {

    List<FormDetail> forms = new ArrayList<>();
    if ((formIds == null) || (formIds.isEmpty())) {
      return forms;
    }

    /* Build query */
    StringBuilder query = new StringBuilder(SELECT_FROM + FORMS_TABLENAME + " where id in (");
    int pos = 0;
    for (final String formId : formIds) {
      if (pos++ != 0) {
        query.append(", ");
      }
      query.append(formId);
    }
    query.append(")");

    /* launch query */
    try (final Connection con = getConnection();
         final Statement stmt = con.createStatement()) {
      try (ResultSet rs = stmt.executeQuery(query.toString())) {
        while (rs.next()) {
          FormDetail form = fetchFormDetail(rs);
          forms.add(form);
        }
      }
    } catch (SQLException se) {
      throw new FormsOnlineException(failureOnGetting("forms", String.join(",", formIds)), se);
    }
    return forms;
  }

  /*
   * (non-Javadoc)
   * @see FormsOnlineDAO#getSentFormInstances(java.lang.String,
   * int, java.lang.String)
   */
  @Override
  public SilverpeasList<FormInstance> getSentFormInstances(FormPK pk, String userId,
      final List<Integer> states, final PaginationPage paginationPage) throws FormsOnlineException {
    return getRequestsByCriteria(RequestCriteria
        .onComponentInstanceIds(pk.getInstanceId())
        .andFormIds(pk.getId())
        .andCreatorId(userId)
        .andStates(states)
        .paginateBy(paginationPage));
  }

  /*
   * (non-Javadoc)
   * @see FormsOnlineDAO#getReceivedRequests(java.lang.String,
   * java.lang.String)
   */
  @Override
  public SilverpeasList<FormInstance> getReceivedRequests(final FormDetail form,
      final List<Integer> states, RequestValidationCriteria validationCriteria,
      final PaginationPage paginationPage) throws FormsOnlineException {
    final FormPK pk = form.getPK();
    return getRequestsByCriteria(RequestCriteria
        .onComponentInstanceIds(pk.getInstanceId())
        .andValidationCriteria(validationCriteria)
        .andFormIds(pk.getId())
        .andStates(states)
        .paginateBy(paginationPage));
  }

  /**
   * Centralization of request queries execution.<br>
   * Common filtering and ordering are applied to the given {@link JdbcSqlQuery} instance.
   * @param criteria criteria to apply.
   * @return the result list.
   */
  SilverpeasList<FormInstance> getRequestsByCriteria(final RequestCriteria criteria)
      throws FormsOnlineException {
    if (criteria.emptyResultWhenNoFilteringOnComponentInstances()) {
      return new SilverpeasArrayList<>(0);
    }
    final JdbcSqlQuery query = JdbcSqlQuery
        .createSelect("r.*")
        .from(FORMS_INSTANCES_TABLENAME + " r")
        .where("instanceid").in(criteria.getComponentInstanceIds());
    if (!criteria.getIds().isEmpty()) {
      query.and("id").in(criteria.getIds().stream().map(Integer::parseInt).collect(toSet()));
    }
    if (!criteria.getFormIds().isEmpty()) {
      query.and(FORM_ID).in(criteria.getFormIds().stream().map(Integer::parseInt).collect(toSet()));
    }
    if (!criteria.getCreatorIds().isEmpty()) {
      query.and(CREATOR_ID).in(criteria.getCreatorIds());
    }
    if (!criteria.getStates().isEmpty()) {
      query.and(STATE).in(criteria.getStates());
    }
    applyValidationCriteria(criteria, query);
    // default order by if none defined
    final List<QUERY_ORDER_BY> orderBies = criteria.getOrderByList().isEmpty()
        ? Arrays.asList(CREATION_DATE_DESC, ID_DESC)
        : criteria.getOrderByList();
    query.orderBy(orderBies.stream()
        .map(o -> o.getPropertyName() + " " + (o.isAsc() ? "asc" : "desc"))
        .collect(Collectors.joining(",")));
    // pagination
    if (criteria.getPagination() != null) {
      query.withPagination(criteria.getPagination().asCriterion());
    }
    // execution
    try (final Connection connection = DBUtil.openConnection()) {
      final SilverpeasList<FormInstance> requests = query.executeWith(connection, this::fetchFormInstance);
      return decorateWithValidations(connection, requests);
    } catch (Exception e) {
      throw new FormsOnlineException(failureOnGetting("form instance", criteria.toString()), e);
    }
  }

  private void applyValidationCriteria(final RequestCriteria criteria, final JdbcSqlQuery query) {
    final RequestValidationCriteria validationCriteria = criteria.getValidationCriteria();
    if (validationCriteria != null && !validationCriteria.isSkipValidationFiltering()) {
      final String validatorId = validationCriteria.getValidatorId();
      if (validationCriteria.isInvert()) {
        query.and("NOT (");
      } else {
        query.and("(");
      }
      if (validationCriteria.isAvoidValidatedByValidator()) {
        query.addSqlPart("1 <> 1");
      } else {
        query.addSqlPart(EXISTS_SELECT)
            .from(FORMS_INSTANCE_VALIDATIONS_TABLENAME)
            .where(FORM_INST_ID_CLAUSE)
            .and(VALIDATION_BY + " = ?)", validatorId);
      }
      if (validationCriteria.isStillNeedValidation()) {
        query.and("(1 <> 1");
      }
      applyExclusiveValidationCriteria(query, validationCriteria);
      if (validationCriteria.isStillNeedValidation()) {
        query.addSqlPart(")");
      }
      query.addSqlPart(")");
    }
  }

  private void applyExclusiveValidationCriteria(final JdbcSqlQuery query,
      final RequestValidationCriteria validationCriteria) {
    validationCriteria.getOrLastValidationType().forEach(v -> {
      query.or("(");
      query.addSqlPart(EXISTS_SELECT)
          .from(FORMS_INSTANCE_VALIDATIONS_TABLENAME)
          .where(FORM_INST_ID_CLAUSE)
          .and("status = ?", VALIDATED)
          .and(VALIDATION_TYPE + " = ?)", v.name());
      final List<String> nextValidationTypes =
          Stream.of(FormInstanceValidationType.values())
              .filter(e -> e.ordinal() > v.ordinal())
              .map(FormInstanceValidationType::name)
              .collect(Collectors.toList());
      if (!nextValidationTypes.isEmpty()) {
        query.and(NOT_EXISTS_SELECT)
            .from(FORMS_INSTANCE_VALIDATIONS_TABLENAME)
            .where(FORM_INST_ID_CLAUSE)
            .and(VALIDATION_TYPE).in(nextValidationTypes)
            .addSqlPart(")");
      }
      query.addSqlPart(")");
    });
    if (validationCriteria.isOrNoValidator()) {
      query.or(NOT_EXISTS_SELECT)
          .from(FORMS_INSTANCE_VALIDATIONS_TABLENAME)
          .where(FORM_INST_ID_CLAUSE)
          .addSqlPart(")");
    }
    if (validationCriteria.isOrValidatorIsHierarchicalOne() &&
        !validationCriteria.getManagedDomainUsers().isEmpty()) {
      query.or("(");
      query.addSqlPart(CREATOR_ID).in(validationCriteria.getManagedDomainUsers());
      query.and(NOT_EXISTS_SELECT)
          .from(FORMS_INSTANCE_VALIDATIONS_TABLENAME)
          .where(FORM_INST_ID_CLAUSE)
          .addSqlPart(")");
      query.addSqlPart(")");
    }
  }

  private SilverpeasList<FormInstance> decorateWithValidations(final Connection con,
      final SilverpeasList<FormInstance> requests) throws SQLException {
    if (!requests.isEmpty()) {
      final Mutable<Integer> min = Mutable.of(Integer.MAX_VALUE);
      final Mutable<Integer> max = Mutable.of(Integer.MIN_VALUE);
      final Map<Integer, FormInstance> indexedById = requests.stream()
          .map(i -> {
            final int idAsInt = i.getIdAsInt();
            min.set(Math.min(min.get(), idAsInt));
            max.set(Math.max(max.get(), idAsInt));
            return i;
          })
          .collect(toMap(FormInstance::getIdAsInt, r -> r));
      JdbcSqlQuery.createSelect("*")
          .from(FORMS_INSTANCE_VALIDATIONS_TABLENAME)
          .where(FORM_INST_ID + " BETWEEN ? AND ?", min.get(), max.get())
          .executeWith(con, r -> {
            final FormInstance request = indexedById.get(r.getInt(FORM_INST_ID));
            if (request != null) {
              final FormInstanceValidation validation = new FormInstanceValidation(request);
              validation.setId(r.getInt("id"));
              validation.setStatus(ContributionStatus.valueOf(r.getString("status")));
              validation.setComment(r.getString("validationComment"));
              validation.setValidationBy(r.getString(VALIDATION_BY));
              validation.setDate(r.getTimestamp("validationDate"));
              validation.setValidationType(FormInstanceValidationType.valueOf(r.getString(VALIDATION_TYPE)));
              validation.setFollower(r.getBoolean("follower"));
              request.getValidations().add(validation);
            }
            return null;
          });
    }
    return requests;
  }

  @Override
  public Map<String, Set<FormInstanceValidationType>> getValidatorFormIdsWithValidationTypes(
      String instanceId, String validatorId, String[] validatorGroupIds,
      final Collection<String> formIds) throws FormsOnlineException {
    final Map<String, Set<FormInstanceValidationType>> result = new HashMap<>();
    final JdbcSqlQuery query = JdbcSqlQuery
        .createSelect("DISTINCT formId, rightType")
        .from(USER_RIGHTS_TABLENAME)
        .join(FORMS_TABLENAME + " f").on("f." + ID + " = " + FORM_ID)
        .where("f." + INSTANCE_ID + " = ?", instanceId)
        .and(RIGHT_TYPE).in("I", "R")
        .and("userId = ?", validatorId);
    if (CollectionUtil.isNotEmpty(formIds)) {
      query.and(FORM_ID).in(formIds.stream().map(Integer::parseInt).collect(toSet()));
    }
    if (isNotEmpty(validatorGroupIds)) {
      query.union()
          .addSqlPart("SELECT DISTINCT formId, rightType")
          .from(GROUP_RIGHTS_TABLENAME)
          .join(FORMS_TABLENAME + " f").on("f." + ID + " = " + FORM_ID)
          .where("f." + INSTANCE_ID + " = ?", instanceId)
          .and(RIGHT_TYPE).in("I", "R")
          .and("groupId").in((Object[]) validatorGroupIds);
      if (CollectionUtil.isNotEmpty(formIds)) {
        query.and(FORM_ID).in(formIds.stream().map(Integer::parseInt).collect(toSet()));
      }
    }
    try {
      query.execute(fetchRightValidationTypesByForm(result));
    } catch (SQLException se) {
      throw new FormsOnlineException(
          failureOnGetting("user (" + validatorId + ") available form as receiver ids of instance",
              instanceId), se);
    }
    return result;
  }

  @Override
  public Map<String, Set<FormInstanceValidationType>> getPossibleValidationTypesByFormId(
      final Collection<String> formIds) throws FormsOnlineException {
    final Map<String, Set<FormInstanceValidationType>> result = new HashMap<>();
    if (!formIds.isEmpty()) {
      final Collection<Integer> asIntegers = formIds.stream().map(Integer::parseInt).collect(toSet());
      final JdbcSqlQuery query = JdbcSqlQuery
          .createSelect("formId, rightType")
          .from(USER_RIGHTS_TABLENAME)
          .join(FORMS_TABLENAME).on(ID + " = " + FORM_ID)
          .where(RIGHT_TYPE).in("I", "R")
          .and(FORM_ID).in(asIntegers);
      query.union()
          .addSqlPart("SELECT formId, rightType")
          .from(GROUP_RIGHTS_TABLENAME)
          .join(FORMS_TABLENAME).on(ID + " = " + FORM_ID)
          .where(RIGHT_TYPE).in("I", "R")
          .and(FORM_ID).in(asIntegers);
      query.union()
          .addSqlPart("SELECT id AS formId, 'H'")
          .from(FORMS_TABLENAME)
          .where(ID).in(asIntegers)
          .and("hierarchicalValidation = ?", true);
      try {
        query.execute(fetchRightValidationTypesByForm(result));
      } catch (SQLException se) {
        throw new FormsOnlineException(failureOnGetting("possible rights on", formIds), se);
      }
    }
    return result;
  }

  private SelectResultRowProcess<Object> fetchRightValidationTypesByForm(
      final Map<String, Set<FormInstanceValidationType>> result) {
    return r -> {
      final String formId = String.valueOf(r.getInt(FORM_ID));
      final String rightValidatorType = r.getString(RIGHT_TYPE);
      result.computeIfAbsent(formId, s -> new TreeSet<>()).add(fromRightsCode(rightValidatorType));
      return null;
    };
  }

  private FormDetail fetchFormDetail(ResultSet rs) throws SQLException {
    FormDetail form = new FormDetail();
    form.setId(rs.getInt("id"));
    form.setXmlFormName(rs.getString("xmlFormName"));
    form.setName(rs.getString("name"));
    form.setDescription(rs.getString("description"));
    form.setTitle(rs.getString("title"));
    form.setCreatorId(rs.getString(CREATOR_ID));
    form.setCreationDate(rs.getTimestamp(CREATION_DATE));
    form.setInstanceId(rs.getString(INSTANCE_ID));
    form.setState(rs.getInt(STATE));
    form.setHierarchicalValidation(rs.getBoolean("hierarchicalValidation"));
    form.setRequestExchangeReceiver(rs.getString("formInstExchangeReceiver"));
    form.setDeleteAfterRequestExchange(rs.getBoolean("deleteAfterFormInstExchange"));
    return form;
  }

  @Override
  public FormInstance saveRequest(final FormInstance request) throws FormsOnlineException {
    final boolean isInsert = !isSqlDefined(request.getId());
    try {
      final JdbcSqlQueries saveQueries = new JdbcSqlQueries();
      final int formInstId;
      final JdbcSqlQuery formInstanceSave;
      if (isInsert) {
        formInstId = DBUtil.getNextId(FORMS_INSTANCES_TABLENAME, "id");
        request.setId(formInstId);
        formInstanceSave = createInsertFor(FORMS_INSTANCES_TABLENAME);
        formInstanceSave.addInsertParam(ID, formInstId);
      } else {
        formInstId = request.getIdAsInt();
        formInstanceSave = createUpdateFor(FORMS_INSTANCES_TABLENAME);
      }
      saveQueries.add(formInstanceSave);
      formInstanceSave.addSaveParam(FORM_ID, request.getFormId(), isInsert);
      formInstanceSave.addSaveParam(STATE, request.getState(), isInsert);
      formInstanceSave.addSaveParam(INSTANCE_ID, request.getComponentInstanceId(), isInsert);
      if (isInsert) {
        formInstanceSave.addSaveParam(CREATOR_ID, request.getCreatorId(), true);
        formInstanceSave.addSaveParam(CREATION_DATE, Timestamp.from(Instant.now()), true);
      } else {
        if (request.getState() <= FormInstance.STATE_UNREAD) {
          formInstanceSave.addSaveParam(CREATION_DATE, Timestamp.from(Instant.now()), false);
        }
        formInstanceSave.where(ID_CRITERIA, formInstId);
      }
      saveQueries.addAll(prepareSaveValidations(request.getValidations()));
      saveQueries.execute();
      return request;
    } catch (Exception e) {
      final String idAsString = String.valueOf(request.getFormId());
      if (isInsert) {
        throw new FormsOnlineException(failureOnAdding(REQUEST_MSG, idAsString), e);
      } else {
        throw new FormsOnlineException(failureOnUpdate(REQUEST_MSG, idAsString), e);
      }
    }
  }

  private List<JdbcSqlQuery> prepareSaveValidations(
      final Collection<FormInstanceValidation> validations) {
    return validations.stream()
        .map(v -> {
          final JdbcSqlQuery validationSave;
          final boolean isInsert = v.getId() == null || v.getId() == -1;
          final int validationId;
          if (isInsert) {
            validationId = DBUtil.getNextId(FORMS_INSTANCE_VALIDATIONS_TABLENAME, "id");
            v.setId(validationId);
            validationSave = createInsertFor(FORMS_INSTANCE_VALIDATIONS_TABLENAME);
            validationSave.addInsertParam(ID, validationId);
          } else {
            validationId = v.getId();
            validationSave = createUpdateFor(FORMS_INSTANCE_VALIDATIONS_TABLENAME);
          }
          validationSave.addSaveParam(FORM_INST_ID, v.getFormInstance().getIdAsInt(), isInsert);
          validationSave.addSaveParam(VALIDATION_BY, v.getValidator().getId(), isInsert);
          validationSave.addSaveParam(VALIDATION_TYPE, v.getValidationType().name(), isInsert);
          validationSave.addSaveParam("status", v.getStatus().name(), isInsert);
          validationSave.addSaveParam("validationComment", v.getComment(), isInsert);
          validationSave.addSaveParam("follower", v.isFollower(), isInsert);
          if (isInsert) {
            final Timestamp validationDate = v.getDate() != null
                ? new Timestamp(v.getDate().getTime())
                : Timestamp.from(Instant.now());
            validationSave.addSaveParam("validationDate", validationDate, true);
          } else {
            validationSave.where(ID_CRITERIA, validationId);
          }
          return validationSave;
        })
        .collect(Collectors.toList());
  }

  @Override
  public void saveRequestState(final FormInstance request) throws FormsOnlineException {
    try {
      if (!isSqlDefined(request.getId())) {
        throw new FormsOnlineException(failureOnUpdate(REQUEST_MSG, request.getFormId()));
      }
      Transaction.performInOne(() -> {
        final JdbcSqlQuery formInstanceStateSave = createUpdateFor(FORMS_INSTANCES_TABLENAME);
        formInstanceStateSave.addUpdateParam(STATE, request.getState());
        formInstanceStateSave.where(ID_CRITERIA, request.getIdAsInt());
        return formInstanceStateSave.execute();
      });
    } catch (Exception e) {
      throw new FormsOnlineException(failureOnUpdate("instance state on form", request.getFormId()), e);
    }
  }

  @Override
  public FormInstance getRequest(RequestPK pk) throws FormsOnlineException {
    return unique(getRequestsByCriteria(RequestCriteria
        .onComponentInstanceIds(pk.getInstanceId())
        .andIds(pk.getId())));
  }

  @Override
  public void deleteRequest(RequestPK pk) throws FormsOnlineException {
    try {
      final JdbcSqlQueries deleteQueries = new JdbcSqlQueries();
      deleteQueries.add(JdbcSqlQuery
          .createDeleteFor(FORMS_INSTANCE_VALIDATIONS_TABLENAME)
          .where("formInstId = ?", Integer.parseInt(pk.getId())));
      deleteQueries.add(JdbcSqlQuery
          .createDeleteFor(FORMS_INSTANCES_TABLENAME)
          .where("instanceId = ?", pk.getInstanceId())
          .and("id = ?", Integer.parseInt(pk.getId())));
      deleteQueries.execute();
    } catch (Exception e) {
      throw new FormsOnlineException(failureOnDeleting("form instance", pk.toString()), e);
    }
  }

  @Override
  public Map<Integer, Integer> getNumberOfRequestsByForm(String instanceId)
      throws FormsOnlineException {
    try (final Connection con = getConnection();
         final PreparedStatement stmt = con.prepareStatement(COUNT_REQUESTS_BY_FORM)) {
      stmt.setString(1, instanceId);
      final Map<Integer, Integer> map = new HashMap<>();
      try (final ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
          map.put(rs.getInt(1), rs.getInt(2));
        }
      }
      return map;
    } catch (SQLException se) {
      throw new FormsOnlineException(
          failureOnGetting("number of instance by form of component instance", instanceId), se);
    }
  }

  @Override
  public SilverpeasList<FormInstance> getAllRequests(FormPK pk) throws FormsOnlineException {
    return getRequestsByCriteria(RequestCriteria
        .onComponentInstanceIds(pk.getInstanceId())
        .andFormIds(pk.getId()));
  }

  private FormInstance fetchFormInstance(final ResultSet rs) throws SQLException {
    final FormInstance formInstance = new FormInstance();
    formInstance.setId(rs.getInt("id"));
    formInstance.setFormId(rs.getInt(FORM_ID));
    formInstance.setState(rs.getInt(STATE));
    formInstance.setCreatorId(rs.getString(CREATOR_ID));
    formInstance.setCreationDate(rs.getTimestamp(CREATION_DATE));
    formInstance.setInstanceId(rs.getString(INSTANCE_ID));
    return formInstance;
  }

  /**
   * Get a new connection.
   * @return the initialized connection.
   */
  protected Connection getConnection() throws SQLException {
    return DBUtil.openConnection();
  }

  private static void prepareDateStatement(PreparedStatement stat, int pos, Date dateValue)
      throws SQLException {
    if (dateValue == null) {
      stat.setNull(pos, Types.DATE);
    } else {
      stat.setTimestamp(pos, new Timestamp(dateValue.getTime()));
    }
  }
}