# MediaSQLQueryBuilder.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.gallery.dao;

    import org.silverpeas.core.date.DateTime;
    import org.silverpeas.components.gallery.constant.MediaType;
    import org.silverpeas.components.gallery.model.Media;
    import org.silverpeas.components.gallery.model.MediaCriteriaProcessor;
    import org.silverpeas.components.gallery.model.MediaLogicalComparator;
    import org.silverpeas.core.admin.user.model.UserDetail;
    import org.apache.commons.lang3.time.DateUtils;
    import org.silverpeas.components.gallery.model.MediaCriteria;
    import org.silverpeas.core.persistence.jdbc.sql.JdbcSqlQuery;
    import org.silverpeas.core.util.CollectionUtil;

    import java.util.ArrayList;
    import java.util.Collections;
    import java.util.Date;
    import java.util.List;

    /**
     * A dynamic builder of a SQL query.
     */
    public class MediaSQLQueryBuilder implements MediaCriteriaProcessor {

      private final boolean count;
      private StringBuilder orderBy = null;
      private boolean done = false;
      private final StringBuilder sqlQuery = new StringBuilder();
      private final StringBuilder from = new StringBuilder();
      private final StringBuilder where = new StringBuilder();
      private final List<Object> parameters = new ArrayList<>();
      private String conjunction = "";
      private List<MediaCriteria.QUERY_ORDER_BY> logicalOrderBy;
      private boolean distinct = false;
      private int resultLimit = 0;

      private MediaSQLQueryBuilder(boolean count) {
        this.count = count;
      }

      static MediaSQLQueryBuilder selectBuilder() {
        return new MediaSQLQueryBuilder(false);
      }

      static MediaSQLQueryBuilder countBuilder() {
        return new MediaSQLQueryBuilder(true);
      }

      @Override
      public void startProcessing() {
        if (!count) {
          sqlQuery.append("select M.mediaId, M.mediaType, M.instanceId")
              .append(", M.title, M.description, M.author, M.keyWord")
              .append(", M.beginVisibilityDate, M.endVisibilityDate")
              .append(", M.createDate, M.createdBy, M.lastUpdateDate, M.lastUpdatedBy ");
        } else {
          sqlQuery.append("select count(M.mediaId) ");
        }
        from.append("from SC_Gallery_Media M ");
      }

      @Override
      public void endProcessing() {
        if (distinct) {
          sqlQuery.insert((count ? 13 : 7), "distinct ");
        }
        sqlQuery.append(from.toString());
        if (where.length() > 0) {
          sqlQuery.append(" where ").append(where.toString());
        }
        if (orderBy != null && orderBy.length() > 0) {
          sqlQuery.append(orderBy.toString());
        }
        done = true;
      }

      @SuppressWarnings("unchecked")
      @Override
      public JdbcSqlQuery result() {
        return JdbcSqlQuery.create(sqlQuery.toString(), parameters)
            .configure(config -> config.withResultLimit(resultLimit));
      }

      @Override
      public MediaCriteriaProcessor then() {
        if (!done) {
          conjunction = " and ";
        }
        return this;
      }

      @Override
      public MediaCriteriaProcessor processResultLimit(final int resultLimit) {
        this.resultLimit = resultLimit;
        return this;
      }

      @Override
      public MediaCriteriaProcessor processVisibility(final MediaCriteria.VISIBILITY visibility,
          final Date dateReference, final UserDetail creator) {
        switch (visibility) {
          case VISIBLE_ONLY:
          case HIDDEN_ONLY:
          case BY_DEFAULT:
            setVisibilityClause(visibility, dateReference, creator);
            break;
          case FORCE_GET_ALL:
            // No clause
            break;
        }
        conjunction = "";
        return this;
      }

      private void setVisibilityClause(final MediaCriteria.VISIBILITY visibility,
          final Date dateReference, final UserDetail creator) {
        final StringBuilder clause;
        if (visibility == MediaCriteria.VISIBILITY.HIDDEN_ONLY) {
          clause =
              where(conjunction).append("((? < M.beginVisibilityDate or ? > M.endVisibilityDate)");
          parameters.add(dateReference.getTime());
          parameters.add(dateReference.getTime());
        } else {
          clause = where(conjunction)
              .append("((? between M.beginVisibilityDate and M.endVisibilityDate)");
          parameters.add(dateReference.getTime());
        }
        if (creator != null) {
          clause.append(" or M.createdBy = ?");
          parameters.add(creator.getId());
        }
        clause.append(")");
      }

      @Override
      public MediaCriteriaProcessor processComponentInstance(String componentInstanceId) {
        if (!done) {
          where(conjunction).append("M.instanceId = ?");
          parameters.add(componentInstanceId);
          conjunction = "";
        }
        return this;
      }

      @Override
      public MediaCriteriaProcessor processAlbums(final List<String> albumIds) {
        if (!done) {
          StringBuilder params = new StringBuilder();
          for (String albumId : albumIds) {
            if (params.length() > 0) {
              params.append(",");
            }
            params.append("?");
            parameters.add(Integer.valueOf(albumId));
          }
          distinct = true;
          from.append(
              "join SC_Gallery_Path A on M.mediaId = A.mediaId and M.instanceId = A.instanceId ");
          where(conjunction).append("A.nodeId in (").append(params.toString()).append(")");
          conjunction = "";
        }
        return this;
      }

      @Override
      public MediaCriteriaProcessor processCreator(UserDetail creator) {
        if (!done) {
          where(conjunction).append("M.createdBy = ?");
          parameters.add(creator.getId());
          conjunction = "";
        }
        return this;
      }

      @Override
      public MediaCriteriaProcessor processMediaTypes(List<MediaType> mediaTypes) {
        if (!done) {
          StringBuilder params = new StringBuilder();
          for (MediaType mediaType : mediaTypes) {
            if (params.length() > 0) {
              params.append(",");
            }
            params.append("?");
            parameters.add(mediaType.name());
          }
          where(conjunction).append("M.mediaType in (").append(params.toString()).append(")");
          conjunction = "";
        }
        return this;
      }

      @Override
      public MediaCriteriaProcessor processNbDaysBeforeThatMediaIsNotVisible(final Date referenceDate,
          final int nbDaysBeforeThatMediaIsNotVisible) {
        if (!done) {
          where(conjunction).append("M.endVisibilityDate between ? and ?");
          DateTime date =
              new DateTime(DateUtils.addDays(referenceDate, nbDaysBeforeThatMediaIsNotVisible));
          parameters.add(date.getBeginOfDay().getTime());
          parameters.add(date.getEndOfDay().getTime());
          conjunction = "";
        }
        return this;
      }

      @Override
      public MediaCriteriaProcessor processOrdering(List<MediaCriteria.QUERY_ORDER_BY> orderings) {
        if (!done) {
          for (MediaCriteria.QUERY_ORDER_BY anOrdering : orderings) {
            if (!anOrdering.isApplicableOnSQLQuery()) {
              logicalOrderBy = orderings;
              orderBy = null;
              break;
            }
            if (orderBy == null) {
              orderBy = new StringBuilder(" order by ");
            } else {
              orderBy.append(", ");
            }
            orderBy.append(anOrdering.getInstructionBase());
            orderBy.append(" ");
            orderBy.append(anOrdering.isAsc() ? "asc" : "desc");
          }
          conjunction = "";
        }
        return this;
      }

      @Override
      public MediaCriteriaProcessor processIdentifiers(List<String> identifiers) {
        if (!done) {
          StringBuilder params = new StringBuilder();
          for (String identifier : identifiers) {
            if (params.length() > 0) {
              params.append(",");
            }
            params.append("?");
            parameters.add(identifier);
          }
          where(conjunction).append("M.mediaId in (").append(params.toString()).append(")");
          conjunction = "";
        }
        return this;
      }

      @Override
      public List<Media> orderingResult(final List<Media> media) {
        if (CollectionUtil.isNotEmpty(logicalOrderBy)) {
          Collections.sort(media, MediaLogicalComparator.on(logicalOrderBy));
        }
        return media;
      }

      /**
       * Centralization to perform the where clause.
       * @param conjunction the conjunction.
       * @return the builder completed with conjunction.
       */
      private StringBuilder where(String conjunction) {
        if (where.length() > 0) {
          where.append(conjunction);
        }
        return where;
      }
    }