/*
 * 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.classifieds.dao;

import org.silverpeas.components.classifieds.model.ClassifiedDetail;
import org.silverpeas.components.classifieds.model.Subscribe;
import org.silverpeas.core.persistence.jdbc.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Locale;

public class ClassifiedsDAO {

  private ClassifiedsDAO() {
    throw new IllegalAccessError("Utility class");
  }

  /**
   * Create a classified
   * @param con : Connection
   * @param classified : ClassifiedDetail
   * @return classifiedId : String
   * @throws SQLException
   */
  public static String createClassified(Connection con, ClassifiedDetail classified)
      throws SQLException {
    // Création d'une nouvelle petite annonce
    String id = "";
    PreparedStatement prepStmt = null;
    try {
      int newId = DBUtil.getNextId("SC_Classifieds_Classifieds", "classifiedId");
      id = Integer.toString(newId);
      // création de la requete
      String query =
          "insert into SC_Classifieds_Classifieds (classifiedId, instanceId, title, description, " +
              "price, creatorId, creationDate, " +
              "updateDate, status, validatorId, validateDate) " + "values (?,?,?,?,?,?,?,?,?,?,?)";
      // initialisation des paramètres
      prepStmt = con.prepareStatement(query);
      initParam(prepStmt, newId, classified);
      prepStmt.executeUpdate();
    } finally {
      DBUtil.close(prepStmt);
    }
    return id;
  }


  /**
   * update a classified
   * @param con : Connection
   * @param classified : ClassifiedDetail
   * @throws SQLException
   */
  public static void updateClassified(Connection con, ClassifiedDetail classified)
      throws SQLException {
    PreparedStatement prepStmt = null;
    try {
      // création de la requete
      String query =
          "update SC_Classifieds_Classifieds set title = ? , description = ? , price = ? , status" +
              " = ?  , updateDate = ? , validatorId = ? , validateDate = ? " +
              " where classifiedId = ? ";
      // initialisation des paramètres
      prepStmt = con.prepareStatement(query);
      prepStmt.setString(1, classified.getTitle());
      prepStmt.setString(2, classified.getDescription());
      prepStmt.setInt(3, classified.getPrice());
      prepStmt.setString(4, classified.getStatus());
      if (classified.getUpdateDate() != null) {
        prepStmt.setString(5, Long.toString((classified.getUpdateDate()).getTime()));
      } else {
        prepStmt.setString(5, null);
      }
      prepStmt.setString(6, classified.getValidatorId());
      if (classified.getValidateDate() != null) {
        prepStmt.setString(7, Long.toString((classified.getValidateDate()).getTime()));
      } else {
        prepStmt.setString(7, null);
      }
      prepStmt.setInt(8, classified.getClassifiedId());
      prepStmt.executeUpdate();
    } finally {
      DBUtil.close(prepStmt);
    }
  }

  /**
   * delete the classified corresponding to classifiedId
   * @param con : Connection
   * @param classifiedId : String
   * @throws SQLException
   */
  public static void deleteClassified(Connection con, String classifiedId) throws SQLException {
    PreparedStatement prepStmt = null;
    try {
      // création de la requete
      String query = "delete from SC_Classifieds_Classifieds where classifiedId = ? ";
      // initialisation des paramètres
      prepStmt = con.prepareStatement(query);
      prepStmt.setInt(1, Integer.valueOf(classifiedId));
      prepStmt.executeUpdate();
    } finally {
      DBUtil.close(prepStmt);
    }
  }

  /**
   * get the classified correspond to classifiedId
   * @param con : Connection
   * @param classifiedId : String
   * @return classified : ClassifiedDetail
   * @throws SQLException
   */
  public static ClassifiedDetail getClassified(Connection con, String classifiedId)
      throws SQLException {
    // récupérer la petite annonce
    String query = "select * from SC_Classifieds_Classifieds where classifiedId = ? ";
    ClassifiedDetail classified = null;
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
      prepStmt = con.prepareStatement(query);
      prepStmt.setInt(1, Integer.parseInt(classifiedId));
      rs = prepStmt.executeQuery();
      while (rs.next()) {
        classified = recupClassified(rs);
      }
    } finally {
      DBUtil.close(rs, prepStmt);
    }
    return classified;
  }

  /**
   * get all classifieds of a instance corresponding to instanceId
   * @param con : Connection
   * @param instanceId : String
   * @return a collection of ClassifiedDetail
   * @throws SQLException
   */
  public static Collection<ClassifiedDetail> getAllClassifieds(Connection con, String instanceId)
      throws SQLException {
    // récupérer toutes les petites annonces
    ArrayList<ClassifiedDetail> listClassifieds = new ArrayList<>();
    String query = "select * from SC_Classifieds_Classifieds where instanceId = ? ";
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
      prepStmt = con.prepareStatement(query);
      prepStmt.setString(1, instanceId);
      rs = prepStmt.executeQuery();
      while (rs.next()) {
        ClassifiedDetail classified = recupClassified(rs);
        listClassifieds.add(classified);
      }
    } finally {
      DBUtil.close(rs, prepStmt);
    }
    return listClassifieds;
  }

  /**
   * get the number of classifieds to be validated for an instance corresponding to instanceId
   * @param con : Connection
   * @param instanceId : String
   * @return the number : String
   * @throws SQLException
   */
  public static String getNbTotalClassifieds(Connection con, String instanceId)
      throws SQLException {
    // récupérer le nombre total d'annonces validées
    String nb = "";
    String query =
        "select count(classifiedId) from SC_Classifieds_Classifieds where instanceId = ? and " +
            "status = ? ";
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
      prepStmt = con.prepareStatement(query);
      prepStmt.setString(1, instanceId);
      prepStmt.setString(2, ClassifiedDetail.VALID);
      rs = prepStmt.executeQuery();
      while (rs.next()) {
        nb = rs.getString(1);
      }
    } finally {
      DBUtil.close(rs, prepStmt);
    }
    return nb;
  }

  /**
   * get all classifieds for user and instance, corresponding to userId and instanceId
   * @param con : Connection
   * @param instanceId : String
   * @param userId : String
   * @return a collection of ClassifiedDetail
   * @throws SQLException
   */
  public static List<ClassifiedDetail> getClassifiedsByUser(Connection con, String instanceId,
      String userId) throws SQLException {
    // récupérer toutes les petites annonces de l'utilisateur
    List<ClassifiedDetail> listClassifieds = new ArrayList<>();
    String query =
        "SELECT * FROM SC_Classifieds_Classifieds WHERE instanceId = ? AND creatorId = ? ORDER BY" +
            " CASE WHEN updatedate IS NULL THEN creationdate ELSE updatedate END DESC, " +
            "classifiedId DESC";
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
      prepStmt = con.prepareStatement(query);
      prepStmt.setString(1, instanceId);
      prepStmt.setString(2, userId);
      rs = prepStmt.executeQuery();
      while (rs.next()) {
        ClassifiedDetail classified = recupClassified(rs);
        listClassifieds.add(classified);
      }
    } finally {
      DBUtil.close(rs, prepStmt);
    }
    return listClassifieds;
  }

  /**
   * get all classifieds with given status for an instance corresponding to instanceId
   * @param con : Connection
   * @param instanceId : String
   * @param status : status
   * @return a list of ClassifiedDetail
   * @throws SQLException
   */
  public static List<ClassifiedDetail> getClassifiedsWithStatus(Connection con, String instanceId,
      String status, int firstItemIndex, int elementsPerPage) throws SQLException {
    List<ClassifiedDetail> listClassifieds = new ArrayList<>();
    String query = "select * from SC_Classifieds_Classifieds where instanceId = ? and status = ? " +
        " order by CASE WHEN validatedate IS NULL THEN " +
        " CASE WHEN updatedate IS NULL THEN creationdate ELSE updatedate END " +
        " ELSE validatedate END DESC, " +
        " validatedate DESC, updatedate DESC, creationdate DESC";
    PreparedStatement prepStmt = null;
    ResultSet rs = null;

    int lastIndexResult = firstItemIndex + elementsPerPage - 1;
    boolean displayAllElements = false;
    if (elementsPerPage == -1) {
      displayAllElements = true;
    }

    try {
      prepStmt = con.prepareStatement(query);
      prepStmt.setString(1, instanceId);
      prepStmt.setString(2, status);
      rs = prepStmt.executeQuery();
      int index = 0;
      while (rs.next()) {
        if (displayAllElements || (index >= firstItemIndex && index <= lastIndexResult)) {
          ClassifiedDetail classified = recupClassified(rs);
          listClassifieds.add(classified);
        }
        index++;
      }
    } finally {
      DBUtil.close(rs, prepStmt);
    }
    return listClassifieds;
  }

  /**
   * get all expiring classifieds (corresponding of a number of day nbDays)
   * @param con : Connection
   * @param nbDays : int
   * @param instanceId : component instance id
   * @return a list of ClassifiedDetail
   * @throws SQLException
   */
  public static List<ClassifiedDetail> getAllClassifiedsToUnpublish(Connection con, int nbDays,
      String instanceId) throws SQLException {
    ArrayList<ClassifiedDetail> listClassifieds = new ArrayList<>();

    // calcul de la date de fin
    Calendar calendar = Calendar.getInstance(Locale.FRENCH);
    calendar.add(Calendar.DATE, -nbDays);
    Date date = calendar.getTime();
    String query =
        "select * from SC_Classifieds_Classifieds where ( (updateDate is null and creationDate < " +
            "?) or (updateDate is not null and updateDate < ?) ) and instanceId = ? and status = " +
            "'Valid'";
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
      prepStmt = con.prepareStatement(query);
      prepStmt.setString(1, Long.toString(date.getTime()));
      prepStmt.setString(2, Long.toString(date.getTime()));
      prepStmt.setString(3, instanceId);

      rs = prepStmt.executeQuery();
      while (rs.next()) {
        ClassifiedDetail classified = recupClassified(rs);
        listClassifieds.add(classified);
      }
    } finally {
      DBUtil.close(rs, prepStmt);
    }
    return listClassifieds;
  }

  /**
   * create a subscription
   * @param con : Connection
   * @param subscribe : Subscribe
   * @return subscribeId : String
   * @throws SQLException
   */
  public static String createSubscribe(Connection con, Subscribe subscribe) throws SQLException {
    String id = "";
    PreparedStatement prepStmt = null;
    try {
      int newId = DBUtil.getNextId("SC_Classifieds_Subscribes", "subscribeId");
      id = Integer.toString(newId);
      // création de la requete
      String query =
          "insert into SC_Classifieds_Subscribes (subscribeId, userId, instanceId, field1, " +
              "field2) " +
              "values (?,?,?,?,?)";
      // initialisation des paramètres
      prepStmt = con.prepareStatement(query);
      initParamSubscribe(prepStmt, newId, subscribe);
      prepStmt.executeUpdate();
    } finally {
      DBUtil.close(prepStmt);
    }
    return id;
  }

  /**
   * delete a subscription corresponding to subscribeId
   * @param con : Connection
   * @param subscribeId : String
   * @throws SQLException
   */
  public static void deleteSubscribe(Connection con, String subscribeId) throws SQLException {
    PreparedStatement prepStmt = null;
    try {
      // création de la requete
      String query = "delete from SC_Classifieds_Subscribes where subscribeId = ? ";
      // initialisation des paramètres
      prepStmt = con.prepareStatement(query);
      prepStmt.setInt(1, Integer.valueOf(subscribeId));
      prepStmt.executeUpdate();
    } finally {
      DBUtil.close(prepStmt);
    }
  }

  /**
   * get all subscriptions for an instance corresponding to instanceId
   * @param con : connection
   * @param instanceId : String
   * @return a collection of Subscribe
   * @throws SQLException
   */
  public static Collection<Subscribe> getAllSubscribes(Connection con, String instanceId)
      throws SQLException {
    // récupérer tous les abonnements
    ArrayList<Subscribe> listSubscribes = new ArrayList<>();
    String query = "select * from SC_Classifieds_Subscribes where instanceId = ? ";
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
      prepStmt = con.prepareStatement(query);
      prepStmt.setString(1, instanceId);
      rs = prepStmt.executeQuery();
      while (rs.next()) {
        Subscribe subscribe = recupSubscribe(rs);
        listSubscribes.add(subscribe);
      }
    } finally {
      DBUtil.close(rs, prepStmt);
    }
    return listSubscribes;
  }

  /**
   * get all subscriptions for user and instance corresponding to userId and instanceId
   * @param con : Connection
   * @param instanceId : String
   * @param userId : String
   * @return a collection of Subscribe
   * @throws SQLException
   */
  public static Collection<Subscribe> getSubscribesByUser(Connection con, String instanceId,
      String userId) throws SQLException {
    // récupérer tous les abonnements de l'utilisateur
    ArrayList<Subscribe> listSubscribes = new ArrayList<>();
    String query = "select * from SC_Classifieds_Subscribes where instanceId = ? and userId = ? ";
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
      prepStmt = con.prepareStatement(query);
      prepStmt.setString(1, instanceId);
      prepStmt.setString(2, userId);
      rs = prepStmt.executeQuery();
      while (rs.next()) {
        Subscribe subscribe = recupSubscribe(rs);
        listSubscribes.add(subscribe);
      }
    } finally {
      DBUtil.close(rs, prepStmt);
    }
    return listSubscribes;
  }

  /**
   * get all subscribing users to a search corresponding to fields field1 and field2
   * @param con : Connection
   * @param field1 : String
   * @param field2 : String
   * @return a collection of userId (String)
   * @throws SQLException
   */
  public static Collection<String> getUsersBySubscribe(Connection con, String field1, String field2)
      throws SQLException {
    // récupérer tous les utilisateurs abonnés à une recherche
    ArrayList<String> listUsers = new ArrayList<>();
    String query = "select userId from SC_Classifieds_Subscribes where (field1 = ? and field2 = ?) or (field1 = ? and field2='') or (field1='' and field2 = ?)";
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
      prepStmt = con.prepareStatement(query);
      prepStmt.setString(1, field1);
      prepStmt.setString(2, field2);
      prepStmt.setString(3, field1);
      prepStmt.setString(4, field2);
      rs = prepStmt.executeQuery();
      while (rs.next()) {
        String userId = rs.getString("userId");
        listUsers.add(userId);
      }
    } finally {
      DBUtil.close(rs, prepStmt);
    }
    return listUsers;
  }

  /**
   * get the classified to resultSet
   * @param rs : ResultSet
   * @return classified : ClassifiedDetail
   * @throws SQLException
   */
  private static ClassifiedDetail recupClassified(ResultSet rs) throws SQLException {
    ClassifiedDetail classified = new ClassifiedDetail();
    int classifiedId = rs.getInt("classifiedId");
    String instanceId = rs.getString("instanceId");
    String title = rs.getString("title");
    String description = rs.getString("description");
    Integer price = 0;
    if (rs.getString("price") != null) {
      price = Integer.valueOf(rs.getString("price"));
    }
    String creatorId = rs.getString("creatorId");
    Date creationDate = new Date(Long.parseLong(rs.getString("creationDate")));
    Date updateDate = null;
    if (rs.getString("updateDate") != null) {
      updateDate = new Date(Long.parseLong(rs.getString("updateDate")));
    }
    String status = rs.getString("status");
    String validatorId = rs.getString("validatorId");
    Date validateDate = null;
    if (rs.getString("validateDate") != null) {
      validateDate = new Date(Long.parseLong(rs.getString("validateDate")));
    }
    classified.setClassifiedId(classifiedId);
    classified.setInstanceId(instanceId);
    classified.setTitle(title);
    classified.setDescription(description);
    classified.setPrice(price);
    classified.setCreatorId(creatorId);
    classified.setCreationDate(creationDate);
    classified.setUpdateDate(updateDate);
    classified.setStatus(status);
    classified.setValidatorId(validatorId);
    classified.setValidateDate(validateDate);

    return classified;
  }

  /**
   * initialise parameters
   * @param prepStmt : PreparedStatement
   * @param classifiedId : String
   * @param classified : ClassifiedDetail
   * @throws SQLException
   */
  private static void initParam(PreparedStatement prepStmt, int classifiedId,
      ClassifiedDetail classified) throws SQLException {
    prepStmt.setInt(1, classifiedId);
    prepStmt.setString(2, classified.getInstanceId());
    prepStmt.setString(3, classified.getTitle());
    prepStmt.setString(4, classified.getDescription());
    prepStmt.setInt(5, classified.getPrice());
    prepStmt.setString(6, classified.getCreatorId());
    prepStmt.setString(7, Long.toString((classified.getCreationDate()).getTime()));
    if (classified.getUpdateDate() != null) {
      prepStmt.setString(8, Long.toString((classified.getUpdateDate()).getTime()));
    } else {
      prepStmt.setString(8, null);
    }
    prepStmt.setString(9, classified.getStatus());
    prepStmt.setString(10, classified.getValidatorId());
    if (classified.getValidateDate() != null) {
      prepStmt.setString(11, Long.toString((classified.getValidateDate()).getTime()));
    } else {
      prepStmt.setString(11, null);
    }
  }

  /**
   * get a subscription to resultSet
   * @param rs : ResultSet
   * @return Subscribe
   * @throws SQLException
   */
  private static Subscribe recupSubscribe(ResultSet rs) throws SQLException {
    Subscribe subscribe = new Subscribe();
    int subscribeId = rs.getInt("subscribeId");
    String userId = rs.getString("userId");
    String instanceId = rs.getString("instanceId");
    String field1 = rs.getString("field1");
    String field2 = rs.getString("field2");

    subscribe.setSubscribeId(Integer.toString(subscribeId));
    subscribe.setUserId(userId);
    subscribe.setInstanceId(instanceId);
    subscribe.setField1(field1);
    subscribe.setField2(field2);
    return subscribe;
  }

  /**
   * initialise parameters
   * @param prepStmt : PreparedStatement
   * @param subscribeId : String
   * @param subscribe : Subscribe
   * @throws SQLException
   */
  private static void initParamSubscribe(PreparedStatement prepStmt, int subscribeId,
      Subscribe subscribe) throws SQLException {
    prepStmt.setInt(1, subscribeId);
    prepStmt.setString(2, subscribe.getUserId());
    prepStmt.setString(3, subscribe.getInstanceId());
    prepStmt.setString(4, subscribe.getField1());
    prepStmt.setString(5, subscribe.getField2());
  }

  /**
   * get the unpublished classified
   * @param con : Connection
   * @param instanceId : String
   * @param userId : String
   * @return a collection of ClassifiedDetail
   * @throws SQLException
   */
  public static Collection<ClassifiedDetail> getUnpublishedClassifieds(Connection con,
      String instanceId, String userId) throws SQLException {
    ArrayList<ClassifiedDetail> listClassifieds = new ArrayList<>();
    String query =
        "select * from SC_Classifieds_Classifieds where instanceId = ? and status = 'Unpublished'" +
            " and creatorId = ? ";
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    try {
      prepStmt = con.prepareStatement(query);
      prepStmt.setString(1, instanceId);
      prepStmt.setString(2, userId);

      rs = prepStmt.executeQuery();
      while (rs.next()) {
        ClassifiedDetail classified = recupClassified(rs);
        listClassifieds.add(classified);
      }
    } finally {
      DBUtil.close(rs, prepStmt);
    }
    return listClassifieds;
  }
}
