ForumsDAO.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 received 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.forums.service;

import org.silverpeas.components.forums.model.Forum;
import org.silverpeas.components.forums.model.ForumDetail;
import org.silverpeas.components.forums.model.ForumPK;
import org.silverpeas.components.forums.model.Message;
import org.silverpeas.components.forums.model.MessagePK;
import org.silverpeas.components.forums.model.Moderator;
import org.silverpeas.core.persistence.jdbc.DBUtil;
import org.silverpeas.core.util.DateUtil;
import org.silverpeas.core.util.StringUtil;
import org.silverpeas.core.util.logging.SilverLogger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.stream.Collectors;

import static org.silverpeas.core.SilverpeasExceptionMessages.failureOnGetting;

/**
 * Class managing database accesses for forums.
 */
public class ForumsDAO {

  private static final String DELETE_FROM = "DELETE FROM ";
  private static final String INSERT_INTO = "INSERT INTO ";
  private static final String SELECT = "SELECT ";
  private static final String WHERE = " WHERE ";
  private static final String SELECT_COUNT = "SELECT COUNT(";
  private static final String EQUAL_TO_PARAM = " = ? ";
  private static final String AND = " AND ";
  private static final String ORDER_BY = " ORDER BY ";
  private static final String DESC = " DESC";

  // Forums table.
  private static final String FORUM_COLUMN_FORUM_ID = "forumId";
  private static final String FORUM_COLUMN_FORUM_NAME = "forumName";
  private static final String FORUM_COLUMN_FORUM_DESCRIPTION = "forumDescription";
  private static final String FORUM_COLUMN_FORUM_ACTIVE = "forumActive";
  private static final String FORUM_COLUMN_FORUM_PARENT = "forumParent";
  private static final String FORUM_COLUMN_CATEGORY_ID = "categoryId";
  private static final String FORUM_COLUMN_INSTANCE_ID = "instanceId";
  private static final String FORUM_COLUMN_FORUM_CREATION_DATE = "forumCreationDate";
  private static final String FORUM_COLUMN_FORUM_CREATOR = "forumCreator";
  private static final String FORUM_COLUMN_FORUM_LOCK_LEVEL = "forumLockLevel";
  private static final String FORUM_COLUMNS =
      FORUM_COLUMN_FORUM_ID + ", " + FORUM_COLUMN_FORUM_NAME + ", " +
          FORUM_COLUMN_FORUM_DESCRIPTION + ", " + FORUM_COLUMN_FORUM_ACTIVE + ", " +
          FORUM_COLUMN_FORUM_PARENT + ", " + FORUM_COLUMN_CATEGORY_ID + ", " +
          FORUM_COLUMN_INSTANCE_ID + ", " + FORUM_COLUMN_FORUM_CREATION_DATE + ", " +
          FORUM_COLUMN_FORUM_CREATOR;
  // Messages table.
  private static final String MESSAGE_TABLE = "SC_Forums_Message";
  private static final String MESSAGE_COLUMN_MESSAGE_ID = "messageId";
  private static final String MESSAGE_COLUMN_MESSAGE_TITLE = "messageTitle";
  private static final String MESSAGE_COLUMN_MESSAGE_AUTHOR = "messageAuthor";
  private static final String MESSAGE_COLUMN_FORUM_ID = "forumId";
  private static final String MESSAGE_COLUMN_MESSAGE_PARENT_ID = "messageParentId";
  private static final String MESSAGE_COLUMN_MESSAGE_DATE = "messageDate";
  private static final String MESSAGE_COLUMN_STATUS = "status";
  private static final String MESSAGE_COLUMNS =
      MESSAGE_COLUMN_MESSAGE_ID + ", " + MESSAGE_COLUMN_MESSAGE_TITLE + ", " +
          MESSAGE_COLUMN_MESSAGE_AUTHOR + ", " + MESSAGE_COLUMN_FORUM_ID + ", " +
          MESSAGE_COLUMN_MESSAGE_PARENT_ID + ", " + MESSAGE_COLUMN_MESSAGE_DATE + " , " +
          MESSAGE_COLUMN_STATUS;
  // Rights table.
  private static final String RIGHTS_TABLE = "SC_Forums_Rights";
  private static final String RIGHTS_COLUMN_USER_ID = "userId";
  private static final String RIGHTS_COLUMN_FORUM_ID = "forumId";
  private static final String RIGHTS_COLUMNS =
      RIGHTS_COLUMN_USER_ID + ", " + RIGHTS_COLUMN_FORUM_ID;
  // History table.
  private static final String HISTORY_TABLE = "SC_Forums_HistoryUser";
  private static final String HISTORY_COLUMN_USER_ID = "userId";
  private static final String HISTORY_COLUMN_MESSAGE_ID = "messageId";
  private static final String HISTORY_COLUMN_LAST_ACCESS = "lastAccess";
  private static final String HISTORY_COLUMNS =
      HISTORY_COLUMN_USER_ID + ", " + HISTORY_COLUMN_MESSAGE_ID + ", " + HISTORY_COLUMN_LAST_ACCESS;
  private static final String QUERY_GET_FORUMS_LIST =
      SELECT + FORUM_COLUMNS + " FROM sc_forums_forum WHERE instanceId = ?";
  private static final String QUERY_GET_FORUMS_IDS =
      "SELECT forumId FROM sc_forums_forum WHERE instanceId = ?";
  private static final String QUERY_GET_FORUMS_LIST_BY_CATEGORY_WITH_NOT_NULL_CATEGORY =
      SELECT + FORUM_COLUMNS + " FROM sc_forums_forum WHERE instanceId = ? AND categoryId = ?";
  private static final String QUERY_GET_FORUMS_LIST_BY_CATEGORY_WITH_NULL_CATEGORY =
      SELECT + FORUM_COLUMNS +
          " FROM sc_forums_forum WHERE instanceId = ? AND categoryId IS NULL";
  private static final String QUERY_GET_FORUM_SONS =
      "SELECT forumId FROM sc_forums_forum WHERE forumParent = ? AND instanceId = ?";
  private static final String QUERY_GET_FORUM =
      SELECT + FORUM_COLUMNS + " FROM sc_forums_forum WHERE forumId = ? AND instanceId = ?";
  private static final String QUERY_GET_FORUM_NAME =
      "SELECT forumName FROM sc_forums_forum WHERE forumId = ?";
  private static final String QUERY_IS_FORUM_ACTIVE =
      "SELECT forumActive FROM sc_forums_forum WHERE forumId = ?";
  private static final String QUERY_GET_FORUM_PARENT_ID =
      "SELECT forumParent FROM sc_forums_forum WHERE forumId = ?";
  private static final String QUERY_GET_FORUM_INSTANCE_ID =
      "SELECT instanceId FROM sc_forums_forum WHERE forumId = ?";
  private static final String QUERY_GET_FORUM_CREATOR_ID =
      "SELECT forumCreator FROM sc_forums_forum WHERE forumId = ?";
  private static final String QUERY_LOCK_FORUM =
      "UPDATE sc_forums_forum SET  forumLockLevel = ?, " +
          "forumActive = ?, forumCloseDate = ?,instanceId = ? WHERE forumId = ?";
  private static final String QUERY_UNLOCK_FORUM_GET_LEVEL =
      "SELECT forumLockLevel FROM sc_forums_forum WHERE forumId = ?";
  private static final String QUERY_UNLOCK_FORUM_SET_ACTIVE =
      "UPDATE sc_forums_forum SET forumActive = ?" + " WHERE forumId = ?";
  private static final String QUERY_CREATE_FORUM =
      "INSERT INTO sc_forums_forum (" + FORUM_COLUMNS + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
  private static final String QUERY_UPDATE_FORUM = "UPDATE sc_forums_forum SET forumName = ?, " +
      "forumDescription = ?, forumParent = ?, instanceId = ?, categoryId = ? WHERE forumId = ? ";
  private static final String QUERY_DELETE_FORUM_RIGHTS =
      DELETE_FROM + RIGHTS_TABLE + WHERE + RIGHTS_COLUMN_FORUM_ID + " = ?";
  private static final String QUERY_DELETE_FORUM_MESSAGE =
      DELETE_FROM + MESSAGE_TABLE + WHERE + MESSAGE_COLUMN_FORUM_ID + " = ?";
  private static final String QUERY_DELETE_FORUM_FORUM =
      "DELETE FROM sc_forums_forum WHERE forumId = ?";
  private static final String QUERY_GET_ALL_FORUMS_BY_INSTANCE_ID =
      "SELECT forumId FROM sc_forums_forum WHERE instanceId = ?";
  private static final String QUERY_GET_FORUM_DETAIL =
      SELECT + FORUM_COLUMNS + " FROM sc_forums_forum WHERE forumId = ?";

  /**
   * Private constructor to avoid instantiation since all methods of the class are static.
   */
  private ForumsDAO() {
  }

  /**
   * @param con The connection to the database.
   * @param forumPKs The list of forums primary keys.
   * @return The list of forums corresponding to the primary keys (ForumDetail).
   * @throws SQLException An SQL exception.
   */
  public static Collection<ForumDetail> selectByForumPKs(Connection con,
      Collection<ForumPK> forumPKs) throws SQLException {
    List<ForumDetail> forumDetails = new ArrayList<>(forumPKs.size());
    for (ForumPK forumPK : forumPKs) {
      forumDetails.add(getForumDetail(con, forumPK));
    }
    return forumDetails;
  }

  /**
   * @param con The connection to the database.
   * @param forumPKs The list of forums primary keys.
   * @return The list of forums corresponding to the primary keys (Forum).
   * @throws SQLException An SQL exception.
   */
  public static Collection<Forum> getForumsByKeys(Connection con, Collection<ForumPK> forumPKs)
      throws SQLException {
    ArrayList<Forum> forums = new ArrayList<>();
    Iterator<ForumPK> iterator = forumPKs.iterator();
    ForumPK forumPK;
    Forum forum;
    while (iterator.hasNext()) {
      forumPK = iterator.next();
      forum = getForum(con, forumPK);
      if (forum != null) {
        forums.add(forum);
      }
    }
    return forums;
  }

  /**
   * @param con The connection to the database.
   * @param messagePKs The list of messages primary keys.
   * @return The list of messages corresponding to the primary keys (Message).
   * @throws SQLException An SQL exception.
   */
  public static Collection<Message> getMessagesByKeys(Connection con,
      Collection<MessagePK> messagePKs) throws SQLException {
    return getMessagesByKeys(con, messagePKs, false);
  }

  /**
   * @param con The connection to the database.
   * @param messagePKs The list of messages primary keys.
   * @return The list of threads corresponding to the primary keys (Message).
   * @throws SQLException An SQL exception.
   */
  public static Collection<Message> getThreadsByKeys(Connection con,
      Collection<MessagePK> messagePKs) throws SQLException {
    return getMessagesByKeys(con, messagePKs, true);
  }

  /**
   * @param con The connection to the database.
   * @param messagePKs The list of messages primary keys.
   * @param onlyThreads Indicates if only threads messages are searched.
   * @return The list of messages (or only threads depending on onlyThreads) corresponding to the
   * primary keys (Message).
   * @throws SQLException An SQL exception.
   */
  private static Collection<Message> getMessagesByKeys(Connection con,
      Collection<MessagePK> messagePKs, boolean onlyThreads) throws SQLException {
    ArrayList<Message> messages = new ArrayList<>();
    for (MessagePK messagePK : messagePKs) {
      Message message = (onlyThreads ? getThread(con, messagePK) : getMessage(con, messagePK));
      if (message != null) {
        String instanceId = messagePK.getComponentName();
        if (StringUtil.isDefined(instanceId)) {
          // Vérification que le message retourné fait partie d'un forum dont
          // l'instanceid correspond à celui de la clé du message.
          String forumInstanceId = getForumInstanceId(con, message.getForumId());
          if (instanceId.equals(forumInstanceId)) {
            message.setInstanceId(instanceId);
            messages.add(message);
          }
        } else {
          // Ajout systématique si l'instanceid de la clé du message n'est pas renseignée.
          messages.add(message);
        }
      }
    }
    return messages;
  }

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @return The list of forums corresponding to the primary key (Forum).
   * @throws SQLException An SQL exception.
   */
  public static List<Forum> getForumsList(Connection con, ForumPK forumPK) throws SQLException {
    List<Forum> forums = new ArrayList<>();
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_FORUMS_LIST)) {
      selectStmt.setString(1, forumPK.getComponentName());
      try (ResultSet rs = selectStmt.executeQuery()) {
        while (rs.next()) {
          forums.add(resultSet2Forum(rs));
        }
      }
    }
    return forums;
  }

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @return The list of ids of forums corresponding to the primary key.
   * @throws SQLException An SQL exception.
   */
  public static List<String> getForumsIds(Connection con, ForumPK forumPK)
      throws SQLException {
    ArrayList<String> forumsIds = new ArrayList<>();
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_FORUMS_IDS)) {
      selectStmt.setString(1, forumPK.getComponentName());
      try (ResultSet rs = selectStmt.executeQuery()) {
        while (rs.next()) {
          forumsIds.add(String.valueOf(rs.getInt(FORUM_COLUMN_FORUM_ID)));
        }
      }
    }
    return forumsIds;
  }

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @param categoryId The id of the category.
   * @return The list of forums corresponding to the primary key and the category id.
   * @throws SQLException An SQL exception.
   */
  public static List<Forum> getForumsListByCategory(Connection con, ForumPK forumPK,
      String categoryId) throws SQLException {
    String selectQuery = (StringUtil.isDefined(categoryId) ?
        QUERY_GET_FORUMS_LIST_BY_CATEGORY_WITH_NOT_NULL_CATEGORY :
        QUERY_GET_FORUMS_LIST_BY_CATEGORY_WITH_NULL_CATEGORY);
    List<Forum> forums = new ArrayList<>();
    try (PreparedStatement selectStmt = con.prepareStatement(selectQuery)) {
      selectStmt.setString(1, forumPK.getComponentName());
      if (StringUtil.isDefined(categoryId)) {
        selectStmt.setString(2, categoryId);
      }
      try (ResultSet rs = selectStmt.executeQuery()) {
        while (rs.next()) {
          forums.add(resultSet2Forum(rs));
        }
      }
    }
    return forums;
  }

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @return The list of ids of forums which parent is the forum corresponding to the primary key.
   * @throws SQLException An SQL exception.
   */
  public static List<String> getForumSonsIds(Connection con, ForumPK forumPK) throws SQLException {
    List<String> forumIds = new ArrayList<>();
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_FORUM_SONS)) {
      selectStmt.setInt(1, Integer.parseInt(forumPK.getId()));
      selectStmt.setString(2, forumPK.getComponentName());
      try (ResultSet rs = selectStmt.executeQuery()) {
        while (rs.next()) {
          forumIds.add(String.valueOf(rs.getInt(FORUM_COLUMN_FORUM_ID)));
        }
      }
    }
    return forumIds;
  }

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @return The forum corresponding to the primary key (Forum).
   * @throws SQLException An SQL exception.
   */
  public static Forum getForum(Connection con, ForumPK forumPK) throws SQLException {
    int forumId = Integer.parseInt(forumPK.getId());
    String instanceId = forumPK.getComponentName();
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_FORUM)) {
      selectStmt.setInt(1, forumId);
      selectStmt.setString(2, instanceId);
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          Forum forum = resultSet2Forum(rs);
          forum.setPk(forumPK);
          return forum;
        }
      }
    }
    return null;
  }

  /**
   * @param con The connection to the database.
   * @param forumId The id of the forum.
   * @return The name corresponding to the forum id.
   * @throws SQLException An SQL exception.
   */
  public static String getForumName(Connection con, int forumId) throws SQLException {
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_FORUM_NAME)) {
      selectStmt.setInt(1, forumId);
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          return rs.getString(FORUM_COLUMN_FORUM_NAME);
        }
      }
    }
    return null;
  }

  /**
   * @param con The connection to the database.
   * @param forumId The id of the forum.
   * @return True if the forum is active.
   * @throws SQLException An SQL exception.
   */
  public static boolean isForumActive(Connection con, int forumId) throws SQLException {
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_IS_FORUM_ACTIVE)) {
      selectStmt.setInt(1, forumId);
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          return (rs.getInt(FORUM_COLUMN_FORUM_ACTIVE) == 1);
        }
      }
    }
    return false;
  }

  /**
   * @param con The connection to the database.
   * @param forumId The id of the forum.
   * @return The id of the parent of the forum.
   * @throws SQLException An SQL exception.
   */
  public static int getForumParentId(Connection con, int forumId) throws SQLException {
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_FORUM_PARENT_ID)) {
      selectStmt.setInt(1, forumId);
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          return rs.getInt(FORUM_COLUMN_FORUM_PARENT);
        }
      }
    }
    return -1;
  }

  /**
   * @param con The connection to the database.
   * @param forumId The id of the forum.
   * @return The instance id corresponding to the forum id.
   * @throws SQLException An SQL exception.
   */
  public static String getForumInstanceId(Connection con, int forumId) throws SQLException {
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_FORUM_INSTANCE_ID)) {
      selectStmt.setInt(1, forumId);
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          return rs.getString(FORUM_COLUMN_INSTANCE_ID);
        }
      }
    }
    return null;
  }

  /**
   * @param con The connection to the database.
   * @param forumId The id of the forum.
   * @return The id of the creator of the forum.
   * @throws SQLException An SQL exception.
   */
  public static String getForumCreatorId(Connection con, int forumId) throws SQLException {
    try (PreparedStatement stmt = con.prepareStatement(QUERY_GET_FORUM_CREATOR_ID)) {
      stmt.setInt(1, forumId);
      try (ResultSet rs = stmt.executeQuery()) {
        if (rs.next()) {
          return rs.getString(FORUM_COLUMN_FORUM_CREATOR);
        } else {
          throw new ForumsRuntimeException(failureOnGetting("forum", forumId));
        }
      }
    }
  }

  /**
   * Locks the forum corresponding to the primary key.
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @param level The lock level.
   * @throws SQLException An SQL exception.
   */
  public static void lockForum(Connection con, ForumPK forumPK, int level) throws SQLException {
    try (PreparedStatement updateStmt = con.prepareStatement(QUERY_LOCK_FORUM)) {
      updateStmt.setInt(1, level);
      updateStmt.setInt(2, 0);
      updateStmt.setString(3, DateUtil.date2SQLDate(new Date()));
      updateStmt.setString(4, forumPK.getComponentName());
      updateStmt.setInt(5, Integer.parseInt(forumPK.getId()));
      updateStmt.executeUpdate();
    }
  }

  /**
   * Unlocks the forum corresponding to the primary key.
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @param level The lock level.
   * @return
   * @throws SQLException An SQL exception.
   */
  public static int unlockForum(Connection con, ForumPK forumPK, int level) throws SQLException {
    int result = 0;
    int forumLocklevel = 0;
    try (PreparedStatement declareStmt = con.prepareStatement(QUERY_UNLOCK_FORUM_GET_LEVEL)) {
      declareStmt.setInt(1, Integer.parseInt(forumPK.getId()));
      try (ResultSet rs = declareStmt.executeQuery()) {
        if (rs.next()) {
          forumLocklevel = rs.getInt(FORUM_COLUMN_FORUM_LOCK_LEVEL);
        }

        if (forumLocklevel >= level) {
          try (PreparedStatement declareStmt1 = con.prepareStatement(
              QUERY_UNLOCK_FORUM_SET_ACTIVE)) {
            declareStmt1.setInt(1, 1);
            declareStmt1.setInt(2, Integer.parseInt(forumPK.getId()));
            declareStmt1.executeUpdate();
            result = 1;
          }
        } else {
          result = 0;
        }
      }
    }
    return result;
  }

  /**
   * Creates a forum.
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @param forumName The name of the forum.
   * @param forumDescription The description of the forum.
   * @param forumCreator The creator of the forum.
   * @param forumParent The id of the forum's parent forum.
   * @param categoryId The id of the category.
   * @return The id of the newly created forum.
   * @throws SQLException An SQL exception.
   */
  public static int createForum(Connection con, ForumPK forumPK, String forumName,
      String forumDescription, String forumCreator, int forumParent, String categoryId)
      throws SQLException {
    try (PreparedStatement insertStmt = con.prepareStatement(QUERY_CREATE_FORUM)) {
      int forumId = DBUtil.getNextId("sc_forums_forum", FORUM_COLUMN_FORUM_ID);
      insertStmt.setInt(1, forumId);
      insertStmt.setString(2, forumName);
      insertStmt.setString(3, forumDescription);
      insertStmt.setInt(4, 1);
      insertStmt.setInt(5, forumParent);
      if (StringUtil.isDefined(categoryId)) {
        insertStmt.setString(6, categoryId);
      } else {
        insertStmt.setNull(6, Types.VARCHAR);
      }
      insertStmt.setString(7, forumPK.getComponentName());
      insertStmt.setString(8, DateUtil.date2SQLDate(new Date()));
      insertStmt.setString(9, forumCreator);
      insertStmt.executeUpdate();

      return forumId;
    }
  }

  /**
   * Updates the forum corresponding to the primary key.
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @param forumName The name of the forum.
   * @param forumDescription The description of the forum.
   * @param forumParent The id of the forum's parent forum.
   * @param categoryId The id of the category.
   * @throws SQLException An SQL exception.
   */
  public static void updateForum(Connection con, ForumPK forumPK, String forumName,
      String forumDescription, int forumParent, String categoryId) throws SQLException {
    try (PreparedStatement updateStmt = con.prepareStatement(QUERY_UPDATE_FORUM)) {
      updateStmt.setString(1, forumName);
      updateStmt.setString(2, forumDescription);
      updateStmt.setInt(3, forumParent);
      updateStmt.setString(4, forumPK.getComponentName());
      if (StringUtil.isDefined(categoryId)) {
        updateStmt.setString(5, categoryId);
      } else {
        updateStmt.setNull(5, Types.VARCHAR);
      }
      updateStmt.setInt(6, Integer.parseInt(forumPK.getId()));
      updateStmt.executeUpdate();
    }
  }

  /**
   * Deletes the forum corresponding to the primary key.
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @throws SQLException An SQL exception.
   */
  public static void deleteForum(Connection con, ForumPK forumPK) throws SQLException {
    String sForumId = forumPK.getId();
    int forumId = Integer.parseInt(sForumId);
    try (PreparedStatement deleteStmt1 = con.prepareStatement(QUERY_DELETE_FORUM_RIGHTS)) {
      deleteStmt1.setString(1, sForumId);
      deleteStmt1.executeUpdate();

      try (PreparedStatement deleteStmt2 = con.prepareStatement(QUERY_DELETE_FORUM_MESSAGE)) {
        deleteStmt2.setInt(1, forumId);
        deleteStmt2.executeUpdate();

        try (PreparedStatement deleteStmt3 = con.prepareStatement(QUERY_DELETE_FORUM_FORUM)){
          deleteStmt3.setInt(1, forumId);
          deleteStmt3.executeUpdate();
        }
      }
    }
  }

  private static final String FORUM_RIGHTS_DELETION = DELETE_FROM + RIGHTS_TABLE + " where " +
      FORUM_COLUMN_FORUM_ID + " in ";
  private static final String FORUM_HISTORY_DELETION = DELETE_FROM + HISTORY_TABLE +
      " where messageId in (select messageId from " + MESSAGE_TABLE +
      " as m JOIN sc_forums_forum as f on m." + FORUM_COLUMN_FORUM_ID + " = f." +
      FORUM_COLUMN_FORUM_ID + " and " + FORUM_COLUMN_INSTANCE_ID + "= ?)";
  private static final String FORUM_MESSAGES_DELETION = DELETE_FROM + MESSAGE_TABLE + " where " +
      FORUM_COLUMN_FORUM_ID + " in (select " + FORUM_COLUMN_FORUM_ID +
      " from sc_forums_forum where " + FORUM_COLUMN_INSTANCE_ID + "= ?)";
  private static final String FORUMS_DELETION = "delete from sc_forums_forum where " +
      FORUM_COLUMN_INSTANCE_ID + "= ?";


  public static void deleteAllForums(Connection con, String instanceId) throws SQLException {
    Collection<Integer> forumIds = getAllForumsByInstanceId(con, instanceId);
    if (!forumIds.isEmpty()) {
      String listOfIds =
          forumIds.stream().map(i -> "'" + i + "'").collect(Collectors.joining(",", "(", ")"));
      try (PreparedStatement statement = con.prepareStatement(FORUM_RIGHTS_DELETION + listOfIds)) {
        statement.execute();
      }
    }
    try(PreparedStatement statement = con.prepareStatement(FORUM_HISTORY_DELETION)) {
      statement.setString(1, instanceId);
      statement.execute();
    }
    try(PreparedStatement statement = con.prepareStatement(FORUM_MESSAGES_DELETION)) {
      statement.setString(1, instanceId);
      statement.execute();
    }
    try(PreparedStatement statement = con.prepareStatement(FORUMS_DELETION)) {
      statement.setString(1, instanceId);
      statement.execute();
    }
  }

  private static final String FROM = " FROM ";
  private static final String QUERY_GET_MESSAGES_LIST_BY_FORUM =
      SELECT + MESSAGE_COLUMNS + FROM + MESSAGE_TABLE + WHERE + MESSAGE_COLUMN_FORUM_ID +
          " = ?";

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @return The list of messages of the forum corresponding to the primary key (Message).
   * @throws SQLException An SQL exception.
   */
  public static List<Message> getMessagesList(Connection con, ForumPK forumPK)
      throws SQLException {
    ArrayList<Message> messages = new ArrayList<>();
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_MESSAGES_LIST_BY_FORUM)) {
      selectStmt.setInt(1, Integer.parseInt(forumPK.getId()));
      try (ResultSet rs = selectStmt.executeQuery()) {
        while (rs.next()) {
          messages.add(resultSet2Message(rs, forumPK.getInstanceId()));
        }
      }
    }
    return messages;
  }

  private static final String QUERY_GET_MESSAGES_IDS_BY_FORUM =
      SELECT + MESSAGE_COLUMN_MESSAGE_ID + FROM + MESSAGE_TABLE + WHERE + MESSAGE_COLUMN_FORUM_ID +
          EQUAL_TO_PARAM;

  private static final String QUERY_GET_MESSAGES_IDS_BY_FORUM_AND_MESSAGE =
      SELECT + MESSAGE_COLUMN_MESSAGE_ID + FROM + MESSAGE_TABLE + WHERE + MESSAGE_COLUMN_FORUM_ID +
          " = ?" + AND + MESSAGE_COLUMN_MESSAGE_PARENT_ID + " = ?";

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @param messageParentId The id of the message's parent message.
   * @return The list of ids of messages of the forum corresponding to the primary key and which
   * parent message corresponds to the message id (if it is valued).
   * @throws SQLException An SQL exception.
   */
  public static List<String> getMessagesIds(Connection con, ForumPK forumPK, int messageParentId)
      throws SQLException {
    String query = (messageParentId != -1 ? QUERY_GET_MESSAGES_IDS_BY_FORUM_AND_MESSAGE :
        QUERY_GET_MESSAGES_IDS_BY_FORUM);
    ArrayList<String> messagesIds = new ArrayList<>();
    try (PreparedStatement selectStmt = con.prepareStatement(query)) {
      selectStmt.setInt(1, Integer.parseInt(forumPK.getId()));
      if (messageParentId != -1) {
        selectStmt.setInt(2, messageParentId);
      }
      try (ResultSet rs = selectStmt.executeQuery()) {
        while (rs.next()) {
          messagesIds.add(String.valueOf(rs.getInt(MESSAGE_COLUMN_MESSAGE_ID)));
        }
      }
    }
    return messagesIds;
  }

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @return The list of ids of messages of the forum corresponding to the primary key.
   * @throws SQLException An SQL exception.
   */
  public static List<String> getMessagesIds(Connection con, ForumPK forumPK) throws SQLException {
    return getMessagesIds(con, forumPK, -1);
  }

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @return The list of ids of threads of the forum corresponding to the primary key.
   * @throws SQLException An SQL exception.
   */
  public static List<String> getSubjectsIds(Connection con, ForumPK forumPK) throws SQLException {
    return getMessagesIds(con, forumPK, 0);
  }

  private static final String QUERY_GET_NB_MESSAGES_SUBJECTS =
      SELECT_COUNT + MESSAGE_COLUMN_MESSAGE_ID + ")" + FROM + MESSAGE_TABLE + WHERE +
          MESSAGE_COLUMN_FORUM_ID + " = ?" + AND + MESSAGE_COLUMN_MESSAGE_PARENT_ID + " = 0 AND " +
          MESSAGE_COLUMN_STATUS + EQUAL_TO_PARAM;
  private static final String QUERY_GET_NB_MESSAGES_NOT_SUBJECTS =
      SELECT_COUNT + MESSAGE_COLUMN_MESSAGE_ID + ")" + FROM + MESSAGE_TABLE + WHERE +
          MESSAGE_COLUMN_FORUM_ID + " = ?" + AND + MESSAGE_COLUMN_MESSAGE_PARENT_ID + " != 0 AND " +
          MESSAGE_COLUMN_STATUS + EQUAL_TO_PARAM;

  /**
   * @param con The connection to the database.
   * @param forumId The id of the forum.
   * @param type The type of the searched messages.
   * @return The number of messages corresponding to the forum id and the type (threads or not).
   * @throws SQLException An SQL exception.
   */
  public static int getNbMessages(Connection con, int forumId, String type, String status)
      throws SQLException {
    String selectQuery = ("Subjects".equals(type) ? QUERY_GET_NB_MESSAGES_SUBJECTS :
        QUERY_GET_NB_MESSAGES_NOT_SUBJECTS);
    try (PreparedStatement prepStmt = con.prepareStatement(selectQuery)) {
      prepStmt.setInt(1, forumId);
      prepStmt.setString(2, status);
      try (ResultSet rs = prepStmt.executeQuery()) {
        if (rs.next()) {
          return rs.getInt(1);
        }
      }
    }
    return 0;
  }

  private static final String QUERY_GET_AUTHOR_NB_MESSAGES =
      SELECT_COUNT + MESSAGE_COLUMN_MESSAGE_ID + ")" + FROM + MESSAGE_TABLE + WHERE +
          MESSAGE_COLUMN_MESSAGE_AUTHOR + " = ?" + AND + MESSAGE_COLUMN_STATUS + EQUAL_TO_PARAM;

  /**
   * @param con The connection to the database.
   * @param userId The user's id.
   * @return The number of messages written by the author corresponding to the user id.
   * @throws SQLException An SQL exception.
   */
  public static int getAuthorNbMessages(Connection con, String userId, String status)
      throws SQLException {
    try (PreparedStatement prepStmt = con.prepareStatement(QUERY_GET_AUTHOR_NB_MESSAGES)) {
      prepStmt.setString(1, userId);
      prepStmt.setString(2, status);
      try (ResultSet rs = prepStmt.executeQuery()) {
        if (rs.next()) {
          return rs.getInt(1);
        }
      }
    }
    return 0;
  }

  private static final String QUERY_GET_NB_RESPONSES =
      SELECT + MESSAGE_COLUMN_MESSAGE_ID + FROM + MESSAGE_TABLE + WHERE + MESSAGE_COLUMN_FORUM_ID +
          " = ?" + AND + MESSAGE_COLUMN_MESSAGE_PARENT_ID +
          " = ? AND " + MESSAGE_COLUMN_STATUS + " = ?";

  /**
   * @param con The connection to the database.
   * @param forumId The id of the forum.
   * @param messageId The id of the message.
   * @return The number of responses to the message corresponding to the message id and the forum
   * id.
   */
  public static int getNbResponses(Connection con, int forumId, int messageId, String status) {
    ArrayList<Integer> nextMessageIds = new ArrayList<>();
    try (PreparedStatement prepStmt = con.prepareStatement(QUERY_GET_NB_RESPONSES)) {
      prepStmt.setInt(1, forumId);
      prepStmt.setInt(2, messageId);
      prepStmt.setString(3, status);
      try (ResultSet rs = prepStmt.executeQuery()) {
        while (rs.next()) {
          nextMessageIds.add(rs.getInt(MESSAGE_COLUMN_MESSAGE_ID));
        }
      }
    } catch (SQLException sqle) {
      SilverLogger.getLogger(ForumsDAO.class).error(sqle.getMessage(), sqle);
      return 0;
    }
    int nb = nextMessageIds.size();
    int nextMessageId;
    for (int i = 0, n = nextMessageIds.size(); i < n; i++) {
      nextMessageId = (Integer) nextMessageIds.get(i);
      nb += getNbResponses(con, forumId, nextMessageId, status);
    }
    return nb;
  }

  private static final String QUERY_GET_LAST_MESSAGE =
      SELECT + MESSAGE_COLUMN_MESSAGE_ID + FROM + MESSAGE_TABLE + WHERE + MESSAGE_COLUMN_FORUM_ID +
          " = ?" + AND + MESSAGE_COLUMN_MESSAGE_PARENT_ID + " != 0 AND " + MESSAGE_COLUMN_STATUS +
          EQUAL_TO_PARAM + ORDER_BY + MESSAGE_COLUMN_MESSAGE_DATE + DESC + ", " +
          MESSAGE_COLUMN_MESSAGE_ID + DESC;

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @return The last message of the forum corresponding to the forum id.
   * @throws SQLException An SQL exception.
   */
  public static Message getLastMessage(Connection con, ForumPK forumPK, String status)
      throws SQLException {
    int messageId = -1;
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_LAST_MESSAGE)) {
      selectStmt.setInt(1, Integer.parseInt(forumPK.getId()));
      selectStmt.setString(2, status);
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          messageId = rs.getInt(1);
        }
      }
    }
    if (messageId != -1) {
      MessagePK messagePK = new MessagePK(forumPK.getComponentName(), String.valueOf(messageId));
      return getMessage(con, messagePK);
    }
    return null;
  }

  /**
   * @param con The connection to the database.
   * @param forumPKs The list of forums primary keys.
   * @param count The maximum number of returned threads.
   * @return The last 'count' threads from the forums corresponding to the primary keys.
   * @throws SQLException An SQL exception.
   */
  public static List<Message> getLastThreads(Connection con, ForumPK[] forumPKs, int count)
      throws SQLException {
    ArrayList<Message> messages = new ArrayList<>();
    if (forumPKs.length > 0) {
      StringBuilder selectQuery = new StringBuilder(
          SELECT + MESSAGE_COLUMN_MESSAGE_ID + FROM + MESSAGE_TABLE + WHERE +
              MESSAGE_COLUMN_MESSAGE_PARENT_ID + " = ?" + AND + MESSAGE_COLUMN_FORUM_ID +
              " IN(");
      for (int i = 0, n = forumPKs.length; i < n; i++) {
        if (i > 0) {
          selectQuery.append(", ");
        }
        selectQuery.append(forumPKs[i].getId());
      }
      selectQuery.append(")").append(ORDER_BY).append(MESSAGE_COLUMN_MESSAGE_DATE).append(DESC);


      ArrayList<String> messageIds = new ArrayList<>(count);
      int messagesCount = 0;
      try (PreparedStatement selectStmt = con.prepareStatement(selectQuery.toString())) {
        selectStmt.setInt(1, 0);
        try (ResultSet rs = selectStmt.executeQuery()) {
          while (rs.next() && messagesCount < count) {
            messageIds.add(String.valueOf(rs.getInt(MESSAGE_COLUMN_MESSAGE_ID)));
            messagesCount++;
          }
        }
      }

      String componentName = forumPKs[0].getComponentName();
      for (int i = 0; i < messagesCount; i++) {
        MessagePK messagePK = new MessagePK(componentName, messageIds.get(i));
        messages.add(getMessage(con, messagePK));
      }
    }
    return messages;
  }

  /**
   * @param con The connection to the database.
   * @param forumPKs The list of forums primary keys.
   * @param count The maximum number of returned threads.
   * @return The last not answered 'count' threads from the forums corresponding to the primary
   * keys.
   * @throws SQLException An SQL exception.
   */
  public static Collection<Message> getNotAnsweredLastThreads(Connection con, ForumPK[] forumPKs,
      int count) throws SQLException {
    ArrayList<Message> messages = new ArrayList<>();
    if (forumPKs.length > 0) {
      StringBuilder selectQuery = new StringBuilder(
          SELECT + MESSAGE_COLUMN_MESSAGE_ID + ", " + MESSAGE_COLUMN_FORUM_ID + FROM +
              MESSAGE_TABLE + WHERE + MESSAGE_COLUMN_MESSAGE_PARENT_ID + " = ?" + AND +
              MESSAGE_COLUMN_FORUM_ID + " IN(");
      for (int i = 0, n = forumPKs.length; i < n; i++) {
        if (i > 0) {
          selectQuery.append(", ");
        }
        selectQuery.append(forumPKs[i].getId());
      }
      selectQuery.append(") ORDER BY " + MESSAGE_COLUMN_MESSAGE_DATE + DESC);


      ArrayList<String> messageIds = new ArrayList<>(count);
      int messageId;
      int forumId;
      int messagesCount = 0;
      try (PreparedStatement selectStmt = con.prepareStatement(selectQuery.toString())) {
        selectStmt.setInt(1, 0);
        try (ResultSet rs = selectStmt.executeQuery()) {
          while (rs.next() && messagesCount < count) {
            messageId = rs.getInt(MESSAGE_COLUMN_MESSAGE_ID);
            forumId = rs.getInt(MESSAGE_COLUMN_FORUM_ID);

            messagesCount += fillMessageIds(con, messageIds, messageId, forumId);
          }
        }
      }

      String componentName = forumPKs[0].getComponentName();
      for (int i = 0; i < messagesCount; i++) {
        MessagePK messagePK = new MessagePK(componentName, messageIds.get(i));
        messages.add(getMessage(con, messagePK));
      }
    }
    return messages;
  }

  private static int fillMessageIds(final Connection con, final ArrayList<String> messageIds,
      final int messageId, final int forumId) throws SQLException {
    String query = SELECT_COUNT + MESSAGE_COLUMN_MESSAGE_ID + ") FROM " + MESSAGE_TABLE + WHERE +
        MESSAGE_COLUMN_FORUM_ID + " = ?" + AND + MESSAGE_COLUMN_MESSAGE_PARENT_ID + " = ?";
    int messagesCount = 0;
    try (PreparedStatement prepStmt = con.prepareStatement(query)) {
      prepStmt.setInt(1, forumId);
      prepStmt.setInt(2, messageId);
      try (ResultSet rs2 = prepStmt.executeQuery()) {
        if (rs2.next()) {
          int sonsCount = rs2.getInt(1);
          if (sonsCount == 0) {
            messageIds.add(String.valueOf(messageId));
            messagesCount++;
          }
        }
      }
    }
    return messagesCount;
  }

  /**
   * @param con The connection to the database.
   * @param instanceId The id of the forums instance.
   * @return The list of ids of messages from the forums corresponding to the instance id.
   * @throws SQLException An SQL exception.
   */
  public static Collection<String> getLastMessageRSS(Connection con, String instanceId)
      throws SQLException {
    Collection<String> messageIds = new ArrayList<>();
    Collection<Integer> forumIds = getAllForumsByInstanceId(con, instanceId);
    Iterator<Integer> it = forumIds.iterator();
    while (it.hasNext()) {
      int forumId = it.next().intValue();
      messageIds.addAll(getAllMessageByForum(con, forumId));
    }
    return messageIds;
  }

  /**
   * @param con The connection to the database.
   * @param instanceId The id of the forums instance.
   * @return The list of ids of forums corresponding to the instance id.
   * @throws SQLException An SQL exception.
   */
  private static Collection<Integer> getAllForumsByInstanceId(Connection con, String instanceId)
      throws SQLException {
    Collection<Integer> forumIds = new ArrayList<>();
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_ALL_FORUMS_BY_INSTANCE_ID)) {
      selectStmt.setString(1, instanceId);
      try (ResultSet rs = selectStmt.executeQuery()) {
        while (rs.next()) {
          forumIds.add(Integer.valueOf(rs.getInt(1)));
        }
      }
    }
    return forumIds;
  }

  private static final String QUERY_GET_ALL_MESSAGES_BY_FORUM =
      SELECT + MESSAGE_COLUMN_MESSAGE_ID + FROM + MESSAGE_TABLE + WHERE + MESSAGE_COLUMN_FORUM_ID +
          " = ?" + ORDER_BY + MESSAGE_COLUMN_MESSAGE_DATE + " DESC, " + MESSAGE_COLUMN_MESSAGE_ID +
          DESC;

  /**
   * @param con The connection to the database.
   * @param forumId The id of the forum.
   * @return The list of ids of messages from the forum corresponding to the forum id.
   * @throws SQLException An SQL exception.
   */
  private static Collection<String> getAllMessageByForum(Connection con, int forumId)
      throws SQLException {
    Collection<String> messageIds = new ArrayList<>();
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_ALL_MESSAGES_BY_FORUM)) {
      selectStmt.setInt(1, forumId);
      try (ResultSet rs = selectStmt.executeQuery()) {

        while (rs.next()) {
          messageIds.add(String.valueOf(rs.getInt(1)));
        }
      }
    }
    return messageIds;
  }

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @param messageParentIds The ids of the parent messages.
   * @return The last message from the forum corresponding to the primary key among the messages
   * which id or parent message id belong to the list.
   * @throws SQLException An SQL exception.
   */
  public static Message getLastMessage(Connection con, ForumPK forumPK,
      List<String> messageParentIds, String status) throws SQLException {
    StringBuilder selectQuery =
        new StringBuilder(SELECT + MESSAGE_COLUMN_MESSAGE_ID + FROM + MESSAGE_TABLE);

    int messageParentIdsCount = (messageParentIds != null ? messageParentIds.size() : 0);
    if (messageParentIdsCount > 0) {
      selectQuery.append(WHERE).append(MESSAGE_COLUMN_STATUS).append(" = ? AND ");
      selectQuery.append(" (");
      for (int i = 0; i < messageParentIdsCount; i++) {
        if (i > 0) {
          selectQuery.append(" OR ");
        }
        selectQuery.append(MESSAGE_COLUMN_MESSAGE_PARENT_ID)
            .append(" = ?")
            .append(" OR ")
            .append(MESSAGE_COLUMN_MESSAGE_ID)
            .append(" = ?");
      }
      selectQuery.append(")");
    }
    selectQuery.append(ORDER_BY)
        .append(MESSAGE_COLUMN_MESSAGE_DATE)
        .append(" DESC, ")
        .append(MESSAGE_COLUMN_MESSAGE_ID)
        .append(DESC);

    String messageId = "";
    try (PreparedStatement selectStmt = con.prepareStatement(selectQuery.toString())) {
      selectStmt.setString(1, status);
      if (messageParentIdsCount > 0) {
        int index = 2;
        int messageParentId;
        for (int i = 0; i < messageParentIdsCount; i++) {
          messageParentId = Integer.parseInt((String) messageParentIds.get(i));
          selectStmt.setInt(index++, messageParentId);
          selectStmt.setInt(index++, messageParentId);
        }
      }
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          messageId = String.valueOf(rs.getInt(1));
        }
      }
    }

    Message message = null;
    if (!"".equals(messageId)) {
      MessagePK messagePK = new MessagePK(forumPK.getComponentName(), messageId);
      message = getMessage(con, messagePK);
    }
    return message;
  }

  private static final String QUERY_GET_MESSAGE_INFOS =
      SELECT + MESSAGE_COLUMNS + FROM + MESSAGE_TABLE + WHERE +
          MESSAGE_COLUMN_MESSAGE_ID + " = ?";

  /**
   * @param con The connection to the database.
   * @param messagePK The primary key of the message.
   * @return The message corresponding to the primary key (Vector).
   * @throws SQLException An SQL exception.
   */
  public static List getMessageInfos(Connection con, MessagePK messagePK) throws SQLException {
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_MESSAGE_INFOS)) {
      selectStmt.setInt(1, Integer.parseInt(messagePK.getId()));
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          return resultSet2VectorMessage(rs);
        }
      }
    }
    return new ArrayList();
  }

  private static final String QUERY_GET_MESSAGE =
      SELECT + MESSAGE_COLUMNS + FROM + MESSAGE_TABLE + WHERE +
          MESSAGE_COLUMN_MESSAGE_ID + " = ?";

  /**
   * @param con The connection to the database.
   * @param messagePK The primary key of the message.
   * @return The message corresponding to the primary key (Message).
   * @throws SQLException An SQL exception.
   */
  public static Message getMessage(Connection con, MessagePK messagePK) throws SQLException {
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_MESSAGE)) {
      selectStmt.setInt(1, Integer.parseInt(messagePK.getId()));
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          Message message = resultSet2Message(rs, messagePK.getInstanceId());
          message.setInstanceId(messagePK.getComponentName());
          message.setPk(messagePK);
          return message;
        }
      }
    }
    return null;
  }

  private static final String QUERY_GET_MESSAGE_TITLE =
      SELECT + MESSAGE_COLUMN_MESSAGE_TITLE + FROM + MESSAGE_TABLE + WHERE +
          MESSAGE_COLUMN_MESSAGE_ID + " = ?";

  /**
   * @param con The connection to the database.
   * @param messageId The id of the message.
   * @return The title of the message..
   * @throws SQLException An SQL exception.
   */
  public static String getMessageTitle(Connection con, int messageId) throws SQLException {
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_MESSAGE_TITLE)) {
      selectStmt.setInt(1, messageId);
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          return rs.getString(MESSAGE_COLUMN_MESSAGE_TITLE);
        }
      }
    }
    return "";
  }

  private static final String QUERY_GET_MESSAGE_PARENT_ID =
      SELECT + MESSAGE_COLUMN_MESSAGE_PARENT_ID + FROM + MESSAGE_TABLE + WHERE +
          MESSAGE_COLUMN_MESSAGE_ID + " = ?";

  /**
   * @param con The connection to the database.
   * @param messageId The id of the message.
   * @return The id of the parent of the message..
   * @throws SQLException An SQL exception.
   */
  public static int getMessageParentId(Connection con, int messageId) throws SQLException {
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_MESSAGE_PARENT_ID)) {
      selectStmt.setInt(1, messageId);
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          return rs.getInt(MESSAGE_COLUMN_MESSAGE_PARENT_ID);
        }
      }
    }
    return -1;
  }

  private static final String QUERY_GET_THREAD =
      SELECT + MESSAGE_COLUMNS + FROM + MESSAGE_TABLE + WHERE + MESSAGE_COLUMN_MESSAGE_ID + " = ?" +
          AND + MESSAGE_COLUMN_MESSAGE_PARENT_ID + " = ?";

  /**
   * @param con The connection to the database.
   * @param messagePK The primary key of the message.
   * @return The thread corresponding to the primary key (Message).
   * @throws SQLException An SQL exception.
   */
  public static Message getThread(Connection con, MessagePK messagePK) throws SQLException {
    int messageId = Integer.parseInt(messagePK.getId());
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_THREAD)) {
      selectStmt.setInt(1, messageId);
      selectStmt.setInt(2, 0);
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          Message message = resultSet2Message(rs, messagePK.getInstanceId());
          message.setPk(messagePK);
          return message;
        }
      }
    }
    return null;
  }

  private static final String QUERY_CREATE_MESSAGE =
      INSERT_INTO + MESSAGE_TABLE + " (" + MESSAGE_COLUMNS + ")" +
          " VALUES (?, ?, ?, ?, ?, ?, ?)";

  /**
   * Creates a message.
   * @param con The connection to the database.
   * @param messageTitle The title of the message.
   * @param messageAuthor The author of the message.
   * @param messageDate The date of creation of the message.
   * @param forumId The id of the parent forum.
   * @param messageParent The id of the parent message.
   * @return The id of the newly created message.
   * @throws SQLException An SQL exception.
   */
  public static int createMessage(Connection con, String messageTitle, String messageAuthor,
      Date messageDate, int forumId, int messageParent, String status) throws SQLException {
    Date finalMessageDate = messageDate;
    if (finalMessageDate == null) {
      finalMessageDate = new Date();
    }

    try (PreparedStatement insertStmt = con.prepareStatement(QUERY_CREATE_MESSAGE)) {
      int messageId = DBUtil.getNextId(MESSAGE_TABLE, MESSAGE_COLUMN_MESSAGE_ID);
      insertStmt.setInt(1, messageId);
      insertStmt.setString(2, messageTitle);
      insertStmt.setString(3, messageAuthor);
      insertStmt.setInt(4, forumId);
      insertStmt.setInt(5, messageParent);
      insertStmt.setTimestamp(6, new Timestamp(finalMessageDate.getTime()));
      insertStmt.setString(7, status);
      insertStmt.executeUpdate();

      // ajout pour ce message d'une date de visite
      addLastVisit(con, messageAuthor, messageId);
      return messageId;
    }
  }

  private static final String QUERY_UPDATE_MESSAGE =
      "UPDATE " + MESSAGE_TABLE + " SET " + MESSAGE_COLUMN_MESSAGE_TITLE + " = ? , " +
          MESSAGE_COLUMN_STATUS + EQUAL_TO_PARAM + WHERE + MESSAGE_COLUMN_MESSAGE_ID + " = ?";

  /**
   * Updates the message corresponding to the primary key.
   * @param con The connection to the database.
   * @param messagePK The primary key of the message.
   * @param title The title of the message.
   * @throws SQLException An SQL exception.
   */
  public static void updateMessage(Connection con, MessagePK messagePK, String title, String status)
      throws SQLException {
    try (PreparedStatement updateStmt = con.prepareStatement(QUERY_UPDATE_MESSAGE)) {
      updateStmt.setString(1, title);
      updateStmt.setString(2, status);
      updateStmt.setInt(3, Integer.parseInt(messagePK.getId()));
      updateStmt.executeUpdate();
    }
  }

  private static final String QUERY_DELETE_MESSAGE_MESSAGE =
      DELETE_FROM + MESSAGE_TABLE + WHERE + MESSAGE_COLUMN_MESSAGE_ID + " = ?";

  /**
   * Deletes the message corresponding to the primary key.
   * @param con The connection to the database.
   * @param messagePK The primary key of the message.
   * @throws SQLException An SQL exception.
   */
  public static void deleteMessage(Connection con, MessagePK messagePK) throws SQLException {
    try (PreparedStatement deleteStmt = con.prepareStatement(QUERY_DELETE_MESSAGE_MESSAGE)) {
      deleteStmt.setInt(1, Integer.parseInt(messagePK.getId()));
      deleteStmt.executeUpdate();
    }
  }

  private static final String QUERY_GET_MESSAGE_SONS =
      SELECT + MESSAGE_COLUMN_MESSAGE_ID + FROM + MESSAGE_TABLE + WHERE +
          MESSAGE_COLUMN_MESSAGE_PARENT_ID + " = ?";

  /**
   * @param con The connection to the database.
   * @param messagePK The primary key of the message.
   * @return The list of ids of the messages which parent is the message corresponding to the
   * primary key.
   * @throws SQLException An SQL exception.
   */
  public static Collection<String> getMessageSons(Connection con, MessagePK messagePK)
      throws SQLException {
    Collection<String> messagesIds = new ArrayList<>();
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_MESSAGE_SONS)) {
      selectStmt.setInt(1, Integer.parseInt(messagePK.getId()));
      try (ResultSet rs = selectStmt.executeQuery()) {
        while (rs.next()) {
          messagesIds.add(String.valueOf(rs.getInt(MESSAGE_COLUMN_MESSAGE_ID)));
        }
      }
    }
    return messagesIds;
  }

  private static final String QUERY_IS_MODERATOR =
      SELECT + RIGHTS_COLUMN_FORUM_ID + FROM + RIGHTS_TABLE + WHERE +
          RIGHTS_COLUMN_USER_ID + " = ?";

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @param userId The user's id.
   * @return True if the user owns the role of moderator on the forum corresponding to the primary
   * key, else false.
   * @throws SQLException An SQL exception.
   */
  public static boolean isModerator(Connection con, ForumPK forumPK, String userId)
      throws SQLException {
    try (PreparedStatement prepStmt = con.prepareStatement(QUERY_IS_MODERATOR)) {
      prepStmt.setString(1, userId);
      try (ResultSet rs = prepStmt.executeQuery()) {
        String forumId = forumPK.getId();
        while (rs.next()) {
          if (rs.getString(RIGHTS_COLUMN_FORUM_ID).trim().equals(forumId)) {
            return true;
          }
        }
      }
    }
    return false;
  }

  private static final String QUERY_ADD_MODERATOR =
      INSERT_INTO + RIGHTS_TABLE + " (" + RIGHTS_COLUMNS + ")" + " VALUES (?, ?)";

  /**
   * Adds the role of moderator to the user on the forum corresponding to the primary key.
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @param userId The user's id.
   * @throws SQLException An SQL exception.
   */
  public static void addModerator(Connection con, ForumPK forumPK, String userId)
      throws SQLException {
    try (PreparedStatement insertStmt = con.prepareStatement(QUERY_ADD_MODERATOR)) {
      insertStmt.setString(1, userId);
      insertStmt.setString(2, forumPK.getId());
      insertStmt.executeUpdate();
    }
  }

  private static final String QUERY_REMOVE_MODERATOR =
      DELETE_FROM + RIGHTS_TABLE + WHERE + RIGHTS_COLUMN_USER_ID + " = ?" + AND +
          RIGHTS_COLUMN_FORUM_ID + " = ?";

  /**
   * Removes the role of moderator to the user on the forum corresponding to the primary key.
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @param userId The user's id.
   * @throws SQLException An SQL exception.
   */
  public static void removeModerator(Connection con, ForumPK forumPK, String userId)
      throws SQLException {
    try (PreparedStatement deleteStmt = con.prepareStatement(QUERY_REMOVE_MODERATOR)) {
      deleteStmt.setString(1, userId);
      deleteStmt.setString(2, forumPK.getId());
      deleteStmt.executeUpdate();
    }
  }

  private static final String QUERY_REMOVE_ALL_MODERATORS =
      DELETE_FROM + RIGHTS_TABLE + WHERE + RIGHTS_COLUMN_FORUM_ID + " = ?";

  /**
   * Removes the role of moderator to all users on the forum corresponding to the primary key.
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @throws SQLException An SQL exception.
   */
  public static void removeAllModerators(Connection con, ForumPK forumPK) throws SQLException {
    try (PreparedStatement deleteStmt = con.prepareStatement(QUERY_REMOVE_ALL_MODERATORS)){
      deleteStmt.setString(1, forumPK.getId());
      deleteStmt.executeUpdate();
    }
  }

  private static final String QUERY_GET_MODERATORS =
      SELECT + RIGHTS_COLUMNS + FROM + RIGHTS_TABLE + WHERE + RIGHTS_COLUMN_FORUM_ID +
          " = ?";

  public static List<Moderator> getModerators(Connection con, int forumId) throws SQLException {
    List<Moderator> moderators = new ArrayList<>();
    try (PreparedStatement stmt = con.prepareStatement(QUERY_GET_MODERATORS)) {
      stmt.setString(1, Integer.toString(forumId));
      try(ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
          moderators.add(Moderator.from(rs.getString(RIGHTS_COLUMN_USER_ID),
              Integer.valueOf(rs.getString(RIGHTS_COLUMN_FORUM_ID))));
        }
      }
    }
    return moderators;
  }

  private static final String QUERY_MOVE_MESSAGE =
      "UPDATE " + MESSAGE_TABLE + " SET " + MESSAGE_COLUMN_FORUM_ID + " = ?" + WHERE +
          MESSAGE_COLUMN_MESSAGE_ID + " = ?";

  /**
   * Moves the message corresponding to the message primary key from a previous forum to the one
   * corresponding to the forum primary key.
   * @param con The connection to the database.
   * @param messagePK The primary key of the message.
   * @param forumPK The primary key of the forum.
   * @throws SQLException An SQL exception.
   */
  public static void moveMessage(Connection con, MessagePK messagePK, ForumPK forumPK)
      throws SQLException {
    try (PreparedStatement updateStmt = con.prepareStatement(QUERY_MOVE_MESSAGE)) {
      updateStmt.setInt(1, Integer.parseInt(forumPK.getId()));
      updateStmt.setInt(2, Integer.parseInt(messagePK.getId()));
      updateStmt.executeUpdate();
    }
  }

  /**
   * @param con The connection to the database.
   * @param messagePK The primary key of the message.
   * @return The list of ids of messages which parent is the message corresponding to the primary
   * key.
   * @throws SQLException An SQL exception.
   */
  public static Collection<String> getAllMessageSons(Connection con, MessagePK messagePK)
      throws SQLException {
    Collection<String> messagesIds = new ArrayList<>();
    Collection<String> currentMessagesIds = getMessageSons(con, messagePK);
    for (String messageId : currentMessagesIds) {
      messagesIds.add(messageId);
      messagesIds
          .addAll(getAllMessageSons(con, new MessagePK(messagePK.getInstanceId(), messageId)));
    }
    return messagesIds;
  }

  /**
   * @param con The connection to the database.
   * @param forumPK The primary key of the forum.
   * @return The forum corresponding to the primary key (ForumDetail).
   * @throws SQLException An SQL exception.
   */
  public static ForumDetail getForumDetail(Connection con, ForumPK forumPK) throws SQLException {
    try (PreparedStatement stmt = con.prepareStatement(QUERY_GET_FORUM_DETAIL)) {
      stmt.setInt(1, Integer.parseInt(forumPK.getId()));
      try (ResultSet rs = stmt.executeQuery()) {
        if (rs.next()) {
          return resultSet2ForumDetail(rs, forumPK);
        } else {
          throw new ForumsRuntimeException(failureOnGetting("forum", forumPK.getId()));
        }
      }
    }
  }

  private static final String QUERY_GET_LAST_VISIT =
      SELECT + HISTORY_COLUMN_LAST_ACCESS + FROM + HISTORY_TABLE + WHERE + HISTORY_COLUMN_USER_ID +
          " = ?" + AND + HISTORY_COLUMN_MESSAGE_ID + " = ?" + ORDER_BY +
          HISTORY_COLUMN_LAST_ACCESS + DESC;

  /**
   * @param con The connection to the database.
   * @param userId The user's id.
   * @param messageId The id of the message.
   * @return The last access date to the message corresponding to the message id.
   * @throws SQLException An SQL exception.
   */
  public static Date getLastVisit(Connection con, String userId, int messageId)
      throws SQLException {
    try (PreparedStatement selectStmt = con.prepareStatement(QUERY_GET_LAST_VISIT)) {
      selectStmt.setString(1, userId);
      selectStmt.setInt(2, messageId);
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          return new Date(Long.parseLong(rs.getString(HISTORY_COLUMN_LAST_ACCESS)));
        }
      }
    }
    return null;
  }

  /**
   * @param con The connection to the database.
   * @param userId The user's id.
   * @param messageIds The list of ids of the messages.
   * @return The last access date of the user to the messages corresponding to the list of primary
   * keys.
   * @throws SQLException An SQL exception.
   */
  public static Date getLastVisit(Connection con, String userId, List<String> messageIds)
      throws SQLException {
    StringBuilder selectQuery = new StringBuilder(
        SELECT + HISTORY_COLUMN_LAST_ACCESS + FROM + HISTORY_TABLE + WHERE +
            HISTORY_COLUMN_USER_ID + " = ?");

    int messageIdsCount = (messageIds != null ? messageIds.size() : 0);
    if (messageIdsCount > 0) {

      selectQuery.append(" AND (");
      for (int i = 0; i < messageIdsCount; i++) {
        if (i > 0) {
          selectQuery.append(" OR ");
        }
        selectQuery.append(HISTORY_COLUMN_MESSAGE_ID).append(" = ?");
      }
      selectQuery.append(") ORDER BY ").append(HISTORY_COLUMN_LAST_ACCESS).append(DESC);
    }


    Date lastVisit = null;
    try (PreparedStatement selectStmt = con.prepareStatement(selectQuery.toString())) {
      int index = 1;
      int messageId;
      selectStmt.setString(index++, userId);
      for (int i = 0; i < messageIdsCount; i++) {
        messageId = Integer.parseInt((String) messageIds.get(i));
        selectStmt.setInt(index++, messageId);
      }
      try (ResultSet rs = selectStmt.executeQuery()) {
        if (rs.next()) {
          lastVisit = new Date(Long.parseLong(rs.getString(1)));
        }
      }
    }

    return lastVisit;
  }

  private static final String QUERY_ADD_LAST_VISIT =
      INSERT_INTO + HISTORY_TABLE + " (" + HISTORY_COLUMNS + ")" + " VALUES (?, ?, ?)";

  /**
   * Adds an access date to the message corresponding to the message id by the user.
   * @param con The connection to the database.
   * @param userId The user's id.
   * @param messageId The id of the message.
   * @throws SQLException An SQL exception.
   */
  public static void addLastVisit(Connection con, String userId, int messageId)
      throws SQLException {
    // supprimer la ligne correspondante à ce user et ce message si elle existe
    deleteVisit(con, userId, messageId);

    Date date = new Date();
    try (PreparedStatement insertStmt = con.prepareStatement(QUERY_ADD_LAST_VISIT)) {
      insertStmt.setString(1, userId);
      insertStmt.setInt(2, messageId);
      insertStmt.setString(3, Long.toString(date.getTime()));
      insertStmt.executeUpdate();
    }
  }

  private static final String QUERY_DELETE_VISIT =
      DELETE_FROM + HISTORY_TABLE + WHERE + HISTORY_COLUMN_USER_ID + " = ?" + AND +
          HISTORY_COLUMN_MESSAGE_ID + " = ?";

  /**
   * Deletes the access date of the user to the message corresponding to the message id.
   * @param con The connection to the database.
   * @param userId The user's id.
   * @param messageId The id of the message.
   * @throws SQLException An SQL exception.
   */
  public static void deleteVisit(Connection con, String userId, int messageId) throws SQLException {
    try (PreparedStatement deleteStmt = con.prepareStatement(QUERY_DELETE_VISIT)) {
      deleteStmt.setString(1, userId);
      deleteStmt.setInt(2, messageId);
      deleteStmt.executeUpdate();
    }
  }

  /**
   * @param rs The database result set.
   * @param forumPK The primary key of the forum.
   * @return The forum corresponding to the primary key (ForumDetail).
   * @throws SQLException An SQL exception.
   */
  private static ForumDetail resultSet2ForumDetail(ResultSet rs, ForumPK forumPK)
      throws SQLException {
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
    Date creationDate;
    try {
      creationDate = formatter.parse(rs.getString(FORUM_COLUMN_FORUM_CREATION_DATE));
    } catch (ParseException e) {
      throw new SQLException("ForumsDAO : resultSet2ForumDetail() : internal error : " +
          "creationDate format unknown for forumPK = " + forumPK + " : " + e.toString(), e);
    }

    String name = rs.getString(FORUM_COLUMN_FORUM_NAME);
    String description = rs.getString(FORUM_COLUMN_FORUM_DESCRIPTION);
    String creatorId = rs.getString(FORUM_COLUMN_FORUM_CREATOR);
    forumPK.setComponentName(rs.getString(FORUM_COLUMN_INSTANCE_ID));

    return new ForumDetail(forumPK, name, description, creatorId, creationDate);
  }

  /**
   * @param rs The database result set.
   * @return The forum corresponding to the primary key (Forum).
   * @throws SQLException An SQL exception.
   */
  private static Forum resultSet2Forum(ResultSet rs) throws SQLException {
    String category = rs.getString(FORUM_COLUMN_CATEGORY_ID);
    category = (category != null ? category.trim() : null);
    try {
      Date date = DateUtil.parseDate(rs.getString(FORUM_COLUMN_FORUM_CREATION_DATE));
      Forum forum = new Forum(rs.getInt(FORUM_COLUMN_FORUM_ID),
          rs.getString(FORUM_COLUMN_INSTANCE_ID),
          rs.getString(FORUM_COLUMN_FORUM_NAME).trim(),
          rs.getString(FORUM_COLUMN_FORUM_DESCRIPTION),
          (rs.getInt(FORUM_COLUMN_FORUM_ACTIVE) == 1),
          rs.getInt(FORUM_COLUMN_FORUM_PARENT),
          category);
      forum.setCreationDate(date);
      return forum;
    } catch (ParseException e) {
      throw new SQLException(e);
    }
  }

  /**
   * @param rs The database result set.
   * @return The message corresponding to the primary key (Vector).
   * @throws SQLException An SQL exception.
   */
  private static List resultSet2VectorMessage(ResultSet rs) throws SQLException {
    List message = new ArrayList();
    Timestamp timestamp = rs.getTimestamp(MESSAGE_COLUMN_MESSAGE_DATE);
    Date date = (timestamp != null ? new Date(timestamp.getTime()) : null);

    message.add(String.valueOf(rs.getInt(MESSAGE_COLUMN_MESSAGE_ID)));
    message.add(rs.getString(MESSAGE_COLUMN_MESSAGE_TITLE).trim());
    message.add(rs.getString(MESSAGE_COLUMN_MESSAGE_AUTHOR));
    message.add(date);
    message.add(String.valueOf(rs.getInt(MESSAGE_COLUMN_FORUM_ID)));
    message.add(String.valueOf(rs.getInt(MESSAGE_COLUMN_MESSAGE_PARENT_ID)));
    return message;
  }

  /**
   * @param rs The database result set.
   * @return The message corresponding to the primary key (Message).
   * @throws SQLException An SQL exception.
   */
  private static Message resultSet2Message(ResultSet rs, String instanceId) throws SQLException {
    Timestamp timestamp = rs.getTimestamp(MESSAGE_COLUMN_MESSAGE_DATE);
    Date date = (timestamp != null ? new Date(timestamp.getTime()) : null);
    Message message = new Message(rs.getInt(MESSAGE_COLUMN_MESSAGE_ID),
        instanceId,
        rs.getString(MESSAGE_COLUMN_MESSAGE_TITLE).trim(),
        rs.getString(MESSAGE_COLUMN_MESSAGE_AUTHOR),
        date,
        rs.getInt(MESSAGE_COLUMN_FORUM_ID),
        rs.getInt(MESSAGE_COLUMN_MESSAGE_PARENT_ID));
    message.setStatus(rs.getString(MESSAGE_COLUMN_STATUS));
    return message;
  }
}