/*
 * 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:
 * "http://www.silverpeas.org/docs/core/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.questionreply.service;

import org.silverpeas.components.questionreply.QuestionReplyException;
import org.silverpeas.components.questionreply.index.QuestionIndexer;
import org.silverpeas.components.questionreply.model.Question;
import org.silverpeas.components.questionreply.model.Recipient;
import org.silverpeas.components.questionreply.model.Reply;
import org.silverpeas.components.questionreply.service.notification.SubscriptionNotifier;
import org.silverpeas.core.ResourceReference;
import org.silverpeas.core.WAPrimaryKey;
import org.silverpeas.core.admin.service.OrganizationController;
import org.silverpeas.core.admin.user.model.User;
import org.silverpeas.core.annotation.Service;
import org.silverpeas.core.contribution.content.wysiwyg.service.WysiwygController;
import org.silverpeas.core.contribution.contentcontainer.content.ContentManagerException;
import org.silverpeas.core.i18n.I18NHelper;
import org.silverpeas.core.persistence.jdbc.DBUtil;
import org.silverpeas.core.persistence.jdbc.bean.*;
import org.silverpeas.kernel.util.StringUtil;
import org.silverpeas.kernel.logging.SilverLogger;

import javax.inject.Inject;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;

import static org.silverpeas.core.notification.user.builder.helper.UserNotificationHelper.buildAndSend;
import static org.silverpeas.core.persistence.jdbc.bean.BeanCriteria.OPERATOR.GREATER;
import static org.silverpeas.core.persistence.jdbc.bean.BeanCriteria.OPERATOR.NOT_EQUALS;

@Service
@SuppressWarnings("deprecation")
public class SilverpeasQuestionManager implements QuestionManager {

  private static final String STATUS = "status";
  private static final String INSTANCE_ID = "instanceId";
  private static final String QUESTION_ID = "questionId";
  @Inject
  private QuestionIndexer questionIndexer;
  private SilverpeasBeanDAO<Question> questionDao = null;
  private SilverpeasBeanDAO<Reply> replyDao = null;
  private SilverpeasBeanDAO<Recipient> recipientDao = null;
  @Inject
  private QuestionReplyContentManager contentManager;
  @Inject
  private OrganizationController controller;

  SilverpeasQuestionManager() {
    try {
      questionDao = SilverpeasBeanDAOFactory.getDAO(Question.class);
      replyDao = SilverpeasBeanDAOFactory.getDAO(Reply.class);
      recipientDao = SilverpeasBeanDAOFactory.getDAO(Recipient.class);
    } catch (PersistenceException ex) {
      SilverLogger.getLogger(this).error(ex);
    }
  }

  /**
   * Create and persist a question with targeted recipient (be careful recipient doesn't have
   * question identifier set)
   * @param question the question to create
   * @return the generated question identifier
   * @throws QuestionReplyException if an error occurs
   */
  @Override
  public long createQuestion(Question question) throws QuestionReplyException {
    try (Connection con = DBUtil.openConnection()) {
      Collection<Recipient> recipients = question.readRecipients();
      IdPK pkQ = (IdPK) questionDao.add(con, question);
      question.setPK(pkQ);
      questionIndexer.createIndex(question, Collections.emptyList());
      long idQ = pkQ.getIdAsLong();
      if (recipients != null) {
        for (Recipient recipient : recipients) {
          recipient.setQuestionId(idQ);
          createRecipient(con, recipient);
        }
      }
      question.setPK(pkQ);
      contentManager.createSilverContent(con, question);
      return idQ;
    } catch (SQLException | ContentManagerException | PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * enregistre une réponse à une question => met à jour publicReplyNumber et/ou privateReplyNumber
   * et replyNumber de la question ainsi que le status à 1
   */
  @Override
  public long createReply(Reply reply, Question question) throws QuestionReplyException {
    try (Connection con = DBUtil.openConnection()) {
      IdPK pkR = (IdPK) replyDao.add(con, reply);
      WysiwygController.createFileAndAttachment(reply.readCurrentWysiwygContent(),
          new ResourceReference(pkR), reply.getCreatorId(), I18NHelper.DEFAULT_LANGUAGE);
      long idR = pkR.getIdAsLong();
      if (question.hasNewStatus()) {
        question.waitForAnswer();
      }
      updateQuestion(con, question);
      questionIndexer.updateIndex(question, getAllReplies(reply.getQuestionId(), question.
          getInstanceId()));
      notifySubscribers(question, reply);
      return idR;
    } catch (SQLException | PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * enregistre un destinataire
   */
  private void createRecipient(Connection con, Recipient recipient) throws QuestionReplyException {
    try {
      recipientDao.add(con, recipient);
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * supprime tous les destinataires d'une question
   */
  private void deleteRecipients(Connection con, long questionId) throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion(QUESTION_ID, questionId);
      recipientDao.removeBy(con, criteria);
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Clos une liste de questions : updateQuestion
   */
  @Override
  public void closeQuestions(Collection<Long> questionIds) throws QuestionReplyException {
    if (questionIds != null) {
      try (Connection con = DBUtil.openConnection()) {
        for (Long idQ : questionIds) {
          Question question = getQuestion(idQ);
          question.close();
          updateQuestion(con, question);
        }
      } catch (SQLException e) {
        throw new QuestionReplyException(e);
      }
    }
  }

  @Override
  public void openQuestions(Collection<Long> questionIds) throws QuestionReplyException {
    if (questionIds != null) {
      try (Connection con = DBUtil.openConnection()) {
        for (Long idQ : questionIds) {
          Question question = getQuestion(idQ);
          question.waitForAnswer();
          updateQuestion(con, question);
        }
      } catch (SQLException e) {
        throw new QuestionReplyException(e);
      }
    }
  }

  /*
   * Modifie les destinataires d'une question : deleteRecipients, createRecipient
   */
  @Override
  public void updateQuestionRecipients(Question question) throws QuestionReplyException {
    try (Connection con = DBUtil.openConnection()) {
      Collection<Recipient> recipients = question.readRecipients();
      deleteRecipients(con, ((IdPK) question.getPK()).getIdAsLong());
      if (recipients != null) {
        for (Recipient recipient : recipients) {
          recipient.setQuestionId(((IdPK) question.getPK()).getIdAsLong());
          createRecipient(con, recipient);
        }
      }
    } catch (SQLException | QuestionReplyException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Affecte le status public à 0 de toutes les réponses d'une liste de questions : updateReply
   * Affecte le nombre de réponses publiques de la question à 0 : updateQuestion si question en
   * attente, on a demandé à la supprimer : deleteQuestion
   */
  @Override
  public void updateQuestionRepliesPublicStatus(Collection<Long> questionIds)
      throws QuestionReplyException {
    try (Connection con = DBUtil.openConnection()) {
      if (questionIds != null) {
        for (Long idQ : questionIds) {
          Question question = getQuestion(idQ);
          Collection<Reply> replies = getQuestionPublicReplies(idQ, question.getInstanceId());
          if (replies != null) {
            for (Reply reply : replies) {
              reply.setPublicReply(0);
              addComponentId(reply, question.getPK().getInstanceId());
              updateReply(con, reply);
            }
            updateQuestion(con, question);
          }
          if (question.hasNewStatus()) {
            deleteQuestion(con, idQ);
          }
        }
      }
    } catch (SQLException | QuestionReplyException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Affecte le status private à 0 de toutes les réponses d'une liste de questions : updateReply
   * Affecte le nombre de réponses privées de la question à 0 : updateQuestion
   */
  @Override
  public void updateQuestionRepliesPrivateStatus(Collection<Long> questionIds)
      throws QuestionReplyException {
    try (Connection con = DBUtil.openConnection()) {
      if (questionIds != null) {
        for (Long idQ : questionIds) {
          Question question = getQuestion(idQ);
          Collection<Reply> replies = getQuestionPrivateReplies(idQ, question.getInstanceId());
          if (replies != null) {
            for (Reply reply : replies) {
              reply.setPrivateReply(0);
              addComponentId(reply, question.getPK().getInstanceId());
              updateReply(con, reply);
            }
            updateQuestion(con, question);
          }
        }
      }
    } catch (SQLException | QuestionReplyException e) {
      throw new QuestionReplyException(e);
    }
  }

  private void addComponentId(Reply reply, String componentId) {
    reply.getPK().setComponentName(componentId);
  }

  /*
   * Affecte le status public à 0 d'une liste de réponses : updateReply Décremente le nombre de
   * réponses publiques de la question d'autant : updateQuestion
   */
  @Override
  public void updateRepliesPublicStatus(Collection<Long> replyIds, Question question)
      throws QuestionReplyException {
    try (Connection con = DBUtil.openConnection()) {
      if (replyIds != null) {
        for (Long idR : replyIds) {
          Reply reply = getReply(idR);
          if (reply != null) {
            reply.setPublicReply(0);
            addComponentId(reply, question.getPK().getInstanceId());
            updateReply(con, reply);
          }
        }
        updateQuestion(con, question);
      }
    } catch (SQLException | QuestionReplyException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Affecte le status private à 0 d'une liste de réponses : updateReply Décremente le nombre de
   * réponses privées de la question d'autant : updateQuestion
   */
  @Override
  public void updateRepliesPrivateStatus(Collection<Long> replyIds, Question question)
      throws QuestionReplyException {
    try (Connection con = DBUtil.openConnection()) {
      if (replyIds != null) {
        for (Long idR : replyIds) {
          Reply reply = getReply(idR);
          if (reply != null) {
            reply.setPrivateReply(0);
            addComponentId(reply, question.getPK().getInstanceId());
            updateReply(con, reply);
          }
        }
        updateQuestion(con, question);
      }
    } catch (SQLException | QuestionReplyException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Modifie une question => la question est supprimée si publicReplyNumber et privateReplyNumber
   * sont à 0 et que la question est close => met à jour publicReplyNumber et/ou privateReplyNumber
   * et replyNumber de la question
   */
  private void updateQuestion(Connection con, Question question) throws QuestionReplyException {
    try {
      long idQ = ((IdPK) question.getPK()).getIdAsLong();
      question.setReplyNumber(getQuestionRepliesNumber(idQ));
      question.setPublicReplyNumber(getQuestionPublicRepliesNumber(idQ));
      question.setPrivateReplyNumber(getQuestionPrivateRepliesNumber(idQ));
      if ((question.getReplyNumber() == 0) && question.hasClosedStatus()) {
        deleteQuestion(con, idQ);
      } else {
        questionDao.update(con, question);
        questionIndexer.updateIndex(question, getAllReplies(idQ, question.getInstanceId()));
        question.getPK().setComponentName(question.getInstanceId());
        contentManager.updateSilverContentVisibility(question);
      }
    } catch (Exception e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Modifie une question => la question est supprimée si publicReplyNumber et privateReplyNumber
   * sont à 0 et que la question est close => met à jour publicReplyNumber et/ou privateReplyNumber
   * et replyNumber de la question
   */
  @Override
  public void updateQuestion(Question question) throws QuestionReplyException {
    try {
      long idQ = ((IdPK) question.getPK()).getIdAsLong();
      question.setReplyNumber(getQuestionRepliesNumber(idQ));
      question.setPublicReplyNumber(getQuestionPublicRepliesNumber(idQ));
      question.setPrivateReplyNumber(getQuestionPrivateRepliesNumber(idQ));
      if ((question.getReplyNumber() == 0) && question.hasClosedStatus()) {
        deleteQuestion(idQ);
      } else {
        questionDao.update(question);
        questionIndexer.updateIndex(question, getAllReplies(idQ, question.getInstanceId()));
        question.getPK().setComponentName(question.getInstanceId());
        contentManager.updateSilverContentVisibility(question);
      }
    } catch (Exception e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Modifie une réponse => La réponse est supprimée si le status public et le status private sont à
   * 0
   */
  private void updateReply(Connection con, Reply reply) throws QuestionReplyException {
    try {
      Question question = getQuestion(reply.getQuestionId());
      reply.getPK().setComponentName(question.getInstanceId());
      if ((reply.getPublicReply() == 0) && (reply.getPrivateReply() == 0)) {
        deleteReply(con, reply.getPK());
      } else {
        replyDao.update(con, reply);
        updateWysiwygContent(reply);
      }
      questionIndexer.updateIndex(question, getAllReplies(reply.getQuestionId(), question.
          getInstanceId()));
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Modifie une réponse => La réponse est supprimée si le status public et le status private sont à
   * 0
   */
  @Override
  public void updateReply(Reply reply) throws QuestionReplyException {
    try {
      Question question = getQuestion(reply.getQuestionId());
      if ((reply.getPublicReply() == 0) && (reply.getPrivateReply() == 0)) {
        deleteReply(((IdPK) reply.getPK()).getIdAsLong());
      } else {
        replyDao.update(reply);
        updateReplyWysiwygContent(reply);
      }
      questionIndexer.updateIndex(question, getAllReplies(reply.getQuestionId(), question.
          getInstanceId()));
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  private void updateReplyWysiwygContent(final Reply reply) {
    updateWysiwygContent(reply);
  }

  private void deleteQuestion(Connection con, long questionId) throws QuestionReplyException {
    try {
      deleteRecipients(con, questionId);
      IdPK pk = new IdPK();
      pk.setIdAsLong(questionId);
      Question question = getQuestion(questionId);
      String peasId = question.getInstanceId();
      questionDao.remove(con, pk);
      questionIndexer.deleteIndex(question);
      pk.setComponentName(peasId);
      contentManager.deleteSilverContent(con, pk);
    } catch (Exception e) {
      throw new QuestionReplyException(e);
    }
  }

  private void deleteQuestion(long questionId) throws QuestionReplyException {
    try(Connection connection = DBUtil.openConnection()) {
      deleteQuestion(connection, questionId);
    } catch (SQLException e) {
      throw new QuestionReplyException(e);
    }
  }

  @Override
  public void deleteQuestionAndReplies(Collection<Long> questionIds) throws QuestionReplyException {
    // pour chaque question
    for (Long questionId : questionIds) {
      Connection con = null;
      try {
        con = DBUtil.openConnection();
        deleteRecipients(con, questionId);
        IdPK pk = new IdPK();
        pk.setIdAsLong(questionId);
        Question question = getQuestion(questionId);
        String peasId = question.getInstanceId();
        // rechercher les réponses
        Collection<Reply> replies = getAllReplies(questionId, peasId);
        for (Reply reply : replies) {
          long replyId = Long.parseLong(reply.getPK().getId());
          addComponentId(reply, question.getInstanceId());
          // supprimer la réponse et son index
          deleteReply(replyId);
        }
        questionIndexer.deleteIndex(question);
        // supprimer la question
        questionDao.remove(con, pk);
        pk.setComponentName(peasId);
        contentManager.deleteSilverContent(con, pk);
      } catch (Exception e) {
        throw new QuestionReplyException(e);
      } finally {
        DBUtil.close(con);
      }

    }
  }

  @Override
  public List<Reply> getAllReplies(long questionId, String instanceId)
      throws QuestionReplyException {
    List<Reply> allReplies = new ArrayList<>();
    try {
      Collection<Reply> privateReplies = getQuestionPrivateReplies(questionId, instanceId);
      allReplies.addAll(privateReplies);
      Collection<Reply> publicReplies = getQuestionPublicReplies(questionId, instanceId);
      allReplies.addAll(publicReplies);
      return allReplies;
    } catch (Exception e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * supprime une réponse
   */
  private void deleteReply(long replyId) throws QuestionReplyException {
    try (Connection con = DBUtil.openConnection()) {
      IdPK pk = new IdPK();
      pk.setIdAsLong(replyId);
      replyDao.remove(con, pk);
    } catch (SQLException | PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * supprime une réponse
   */
  private void deleteReply(Connection con, WAPrimaryKey replyId) throws QuestionReplyException {
    try {
      replyDao.remove(con, replyId);
      WysiwygController
          .deleteFile(replyId.getInstanceId(), replyId.getId(), I18NHelper.DEFAULT_LANGUAGE);
    } catch (PersistenceException e) {

      throw new QuestionReplyException(e);
    }
  }

  /*
   * recupère une question
   */
  @Override
  public Question getQuestion(long questionId) throws QuestionReplyException {
    try {
      IdPK pk = new IdPK();
      pk.setIdAsLong(questionId);
      return questionDao.findByPrimaryKey(pk);
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  @Override
  public Question getQuestionAndReplies(long questionId) throws QuestionReplyException {
    try {
      IdPK pk = new IdPK();
      pk.setIdAsLong(questionId);
      Question question = questionDao.findByPrimaryKey(pk);
      Collection<Reply> replies = getQuestionReplies(questionId, question.getInstanceId());
      question.writeReplies(replies);
      return question;
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  @Override
  public List<Question> getQuestionsByIds(List<String> ids) throws QuestionReplyException {
    BeanCriteria criteria = ids.isEmpty() ? BeanCriteria.emptyCriteria() :
        BeanCriteria.addCriterion("id",
            ids.stream().map(Integer::parseInt).collect(Collectors.toSet()));
    try {
      return new ArrayList<>(questionDao.findBy(criteria));
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * recupère la liste des réponses d'une question
   */
  @Override
  public List<Reply> getQuestionReplies(long questionId, String instanceId)
      throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion(QUESTION_ID, questionId);
      List<Reply> replies = new ArrayList<>(
          replyDao.findBy(criteria));
      for (Reply reply : replies) {
        reply.getPK().setComponentName(instanceId);
        reply.loadWysiwygContent();
      }
      return replies;
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * recupère la liste des réponses publiques d'une question
   */
  @Override
  public List<Reply> getQuestionPublicReplies(long questionId, String instanceId)
      throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion("publicReply", 1)
          .and(QUESTION_ID, questionId);
      List<Reply> replies = new ArrayList<>(replyDao.findBy(criteria));
      for (Reply reply : replies) {
        reply.getPK().setComponentName(instanceId);
        reply.loadWysiwygContent();
      }
      return replies;
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * recupère la liste des réponses privées d'une question
   */
  @Override
  public List<Reply> getQuestionPrivateReplies(long questionId, String instanceId)
      throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion("privateReply", 1)
          .and(QUESTION_ID, questionId);
      List<Reply> replies = new ArrayList<>(replyDao.findBy(criteria));
      for (Reply reply : replies) {
        reply.getPK().setComponentName(instanceId);
        reply.loadWysiwygContent();
      }
      return replies;
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * recupère la liste des destinataires d'une question
   */
  @Override
  public List<Recipient> getQuestionRecipients(long questionId) throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion(QUESTION_ID, questionId);
      return new ArrayList<>(recipientDao.findBy(criteria));
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * recupère une réponse
   */
  @Override
  public Reply getReply(long replyId) throws QuestionReplyException {
    try {
      IdPK pk = new IdPK();
      pk.setIdAsLong(replyId);
      Reply reply = replyDao.findByPrimaryKey(pk);
      if (reply != null) {
        reply.loadWysiwygContent();
      }
      return reply;
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Recupère la liste des questions emises par un utilisateur => Q dont il est l'auteur qui ne sont
   * pas closes ou closes avec réponses privées
   */
  @Override
  public List<Question> getSendQuestions(String userId, String instanceId)
      throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion(INSTANCE_ID, instanceId)
          .and("creatorId", Integer.parseInt(userId))
          .and(BeanCriteria.addCriterion(STATUS, NOT_EQUALS,  Question.CLOSED)
              .or("privateReplyNumber", GREATER, 0));
      return new ArrayList<>(questionDao.findBy(criteria));
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Recupère la liste des questions recues par un utilisateur => Q dont il est le destinataire et
   * qui ne sont pas closes
   */
  @Override
  public List<Question> getReceiveQuestions(String userId, String instanceId)
      throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion(INSTANCE_ID, instanceId)
          .and(STATUS, NOT_EQUALS, Question.CLOSED)
          .andSubQuery("id", BeanCriteria.OPERATOR.IN,
              "questionId from SC_QuestionReply_Recipient",
              BeanCriteria.addCriterion("userId", Integer.parseInt(userId)));
      return new ArrayList<>(questionDao.findBy(criteria));
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Recupère la liste des questions qui ne sont pas closes ou closes avec réponses publiques
   */
  @Override
  public List<Question> getQuestions(String instanceId) throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion(INSTANCE_ID, instanceId)
          .and(BeanCriteria.addCriterion(STATUS, NOT_EQUALS,  Question.CLOSED)
              .or("publicReplyNumber", GREATER, 0));
      criteria.setDescOrderBy("creationDate", "id");
      return new ArrayList<>(questionDao.findBy(criteria));
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * Recupère la liste de toutes les questions avec toutes ses réponses
   */
  @Override
  public List<Question> getAllQuestions(String instanceId) throws QuestionReplyException {
    List<Question> allQuestions = getQuestions(instanceId);
    List<Question> questions = new ArrayList<>(allQuestions.size());
    for (Question question : allQuestions) {
      Question fullQuestion = getQuestionAndReplies(Long.parseLong(question.getPK().getId()));
      questions.add(fullQuestion);
    }
    if (isSortable(instanceId)) {
      questions.sort(QuestionRegexpComparator.getInstance());
    }
    return questions;
  }

  @Override
  public List<Question> getAllQuestionsByCategory(String instanceId, String categoryId)
      throws QuestionReplyException {
    List<Question> allQuestions = getQuestions(instanceId);
    List<Question> questions = new ArrayList<>(allQuestions.size());
    for (Question question : allQuestions) {
      if ((StringUtil.isNotDefined(question.getCategoryId()) &&
          StringUtil.isNotDefined(categoryId)) ||
          (StringUtil.isDefined(categoryId) && StringUtil.isDefined(question.getCategoryId()) &&
              question.getCategoryId().equals(categoryId))) {
        // la question est sans catégorie
        Question fullQuestion = getQuestionAndReplies(Long.parseLong(question.getPK().getId()));
        questions.add(fullQuestion);
      }
    }
    if (isSortable(instanceId)) {
      questions.sort(QuestionRegexpComparator.getInstance());
    }
    return questions;
  }

  /*
   * Recupère la liste des questions publiques avec réponses
   */
  @Override
  public List<Question> getPublicQuestions(String instanceId) throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion(INSTANCE_ID, instanceId)
          .and("publicReplyNumber", GREATER, 0);
      criteria.setAscOrderBy("id");
      return new ArrayList<>(questionDao.findBy(criteria));
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /**
   * Save and persist question and reply given in parameter
   * @param question the new question
   * @param reply the answer linked to the given question
   * @return the created question identifier
   * @throws QuestionReplyException if an error occurs
   */
  @Override
  public long createQuestionReply(Question question, Reply reply) throws QuestionReplyException {
    Connection con = null;
    long idQ;
    try {
      con = DBUtil.openConnection();
      IdPK pkQ = (IdPK) questionDao.add(con, question);
      idQ = pkQ.getIdAsLong();
      reply.setQuestionId(idQ);
      WAPrimaryKey pkR = replyDao.add(con, reply);
      reply.getPK().setId(pkR.getId());
      WysiwygController.createFileAndAttachment(reply.readCurrentWysiwygContent(),
          new ResourceReference(pkR), reply.getCreatorId(), I18NHelper.DEFAULT_LANGUAGE);
      questionIndexer.createIndex(question, Collections.singletonList(reply));
      Question updatedQuestion = getQuestion(idQ);
      contentManager.createSilverContent(con, updatedQuestion);
      notifySubscribers(question, reply);
    } catch (Exception e) {
      throw new QuestionReplyException(e);
    } finally {
      DBUtil.close(con);
    }
    return idQ;
  }

  /*
   * recupère le nombre de réponses d'une question
   */
  private int getQuestionRepliesNumber(long questionId) throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion(QUESTION_ID, questionId);
      Collection<Reply> replies = replyDao.findBy(criteria);
      return replies.size();
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * recupère le nombre de réponses publiques d'une question
   */
  private int getQuestionPublicRepliesNumber(long questionId) throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion("publicReply", 1)
          .and(QUESTION_ID, questionId);
      Collection<Reply> replies =
          replyDao.findBy(criteria);
      return replies.size();
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  /*
   * recupère le nombre de réponses privées d'une question
   */
  private int getQuestionPrivateRepliesNumber(long questionId) throws QuestionReplyException {
    try {
      BeanCriteria criteria = BeanCriteria.addCriterion("privateReply", 1)
          .and(QUESTION_ID, questionId);
      Collection<Reply> replies = replyDao.findBy(criteria);
      return replies.size();
    } catch (PersistenceException e) {
      throw new QuestionReplyException(e);
    }
  }

  protected void updateWysiwygContent(Reply reply) {
    if (WysiwygController.haveGotWysiwyg(reply.getPK().getInstanceId(), reply.getPK().getId(),
        I18NHelper.DEFAULT_LANGUAGE)) {
      WysiwygController
          .updateFileAndAttachment(reply.readCurrentWysiwygContent(), reply.getPK().getInstanceId(),
              reply.getPK().getId(), reply.getCreatorId(), I18NHelper.DEFAULT_LANGUAGE);
    } else {
      WysiwygController.createUnindexedFileAndAttachment(reply.readCurrentWysiwygContent(),
          new ResourceReference(reply.getPK()),
              reply.getCreatorId(), I18NHelper.DEFAULT_LANGUAGE);
    }
  }

  protected boolean isSortable(String instanceId) {
    return StringUtil
        .getBooleanValue(controller.getComponentParameterValue(instanceId, "sortable"));
  }

  private void notifySubscribers(Question question, Reply reply) {
    if (reply.getPublicReply() == 1) {
      final User sender = reply.readAuthor();
      buildAndSend(new SubscriptionNotifier(sender, question, reply));
    }
  }
}
