package com.oguerreiro.resilient.repository;

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;
import java.util.stream.Stream;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.oguerreiro.resilient.domain.InputData;
import com.oguerreiro.resilient.domain.InputDataUpload;
import com.oguerreiro.resilient.domain.InventoryData;
import com.oguerreiro.resilient.domain.Organization;
import com.oguerreiro.resilient.domain.Period;
import com.oguerreiro.resilient.domain.Variable;
import com.oguerreiro.resilient.domain.enumeration.InputMode;
import com.oguerreiro.resilient.domain.enumeration.UnitValueType;
import com.oguerreiro.resilient.domain.enumeration.UploadType;

/**
 * Spring Data JPA repository for the InputData entity.
 */
@Repository
public interface InputDataRepository
    extends ResilientJpaRepository<InputData, Long>, JpaSpecificationExecutor<InputData>, InputDataRepositoryExtended {
  /* Interface ResilientJpaRepository eager fetching Override methods */
  /* **************************************************************** */
  @Override
  @Query(value = "select inputData from InputData inputData left join fetch inputData.variable left join fetch inputData.period left join fetch inputData.sourceUnit left join fetch inputData.unit left join fetch inputData.owner", countQuery = "select count(inputData) from InputData inputData")
  Page<InputData> findAllWithEagerRelationships(Pageable pageable);

  @Override
  @Query("select inputData from InputData inputData left join fetch inputData.variable left join fetch inputData.period left join fetch inputData.sourceUnit left join fetch inputData.unit left join fetch inputData.owner")
  List<InputData> findAllWithEagerRelationships();

  @Override
  @Query("select inputData from InputData inputData left join fetch inputData.variable left join fetch inputData.period left join fetch inputData.sourceUnit left join fetch inputData.unit left join fetch inputData.owner where inputData.id =:id")
  Optional<InputData> findOneWithEagerRelationships(@Param("id") Long id);

  /* Custom InputDataRepository methods */
  /* ********************************** */
  /**
   * Delete all InputData values that meets the provided arguments AND only
   * applies to {@link Variable Variable's} of {@link InputMode#DATAFILE}<br>
   * This is used to clear Inputdata before a new Datafile is imported
   * 
   * @param organization, the organization that this data belongs to
   * @param period,       the period of the data
   */
  @Modifying // Needed to tell hibernate that changes where done to this entity data. Without it, this records probably should still be shown
  @Query("delete from InputData id where id.owner=:organization and id.period=:period and id.variable.inputMode=com.oguerreiro.resilient.domain.enumeration.InputMode.DATAFILE")
  void deleteAllInputDataFromDatafile(@Param("organization") Organization organization, @Param("period") Period period);

  /**
   * Delete all InputData values that meets the provided arguments AND only
   * applies to imported InputData of the same uploadType<br>
   * This is used to clear Inputdata before a new Datafile is imported
   * 
   * @param organization, the organization that this data belongs to
   * @param period,       the period of the data
   * @param uploadType,   the uploadType of the related InputDataUpload. This guarantees that only data imported by the
   *                      same UploadType will be deleted
   */
  @Modifying // Needed to tell hibernate that changes where done to this entity data. Without it, this records probably should still be shown
  @Query("delete from InputData id where id.owner=:organization and id.period=:period and id.sourceInputDataUpload.type=:uploadType")
  void deleteAllInputDataFromDatafile(@Param("organization") Organization organization, @Param("period") Period period,
      @Param("uploadType") UploadType uploadType);

  /**
   * Delete all InputData values that meets the provided arguments<br>
   * This is used to clear Inputdata associated to a SourceInputDataUpload
   * 
   * @param inputDataUpload, the InputDataUpload that is to be cleared
   */
  @Modifying // Needed to tell hibernate that changes where done to this entity data. Without it, this records probably should still be shown
  @Query("delete from InputData id where id.sourceInputDataUpload=:inputDataUpload")
  void deleteAllInputDataWithSource(@Param("inputDataUpload") InputDataUpload inputDataUpload);

  @Query("SELECT SUM(id.imputedValue) from InputData id where id.owner=:organization and id.period=:period and id.variable.code LIKE :variableCode")
  BigDecimal sumAllInputsFor(@Param("organization") Organization organization, @Param("period") Period period,
      @Param("variableCode") String variableCode);

  /**
   * Sums all InputData that meet the conditions. Pay attention to the variableCode parameter, that allows wildcard
   * search.<br>
   * InputData with value type {@link UnitValueType#STRING} will be ignored in the query
   * 
   * @param  organization      par
   * @param  period
   * @param  variableCode,     this argument allows wildcard search, the constraint is a LIKE clause. Ex. '0101%'
   * @param  variableClassCode
   * @return
   */
  @Query("SELECT SUM(id.imputedValue) from InputData id where id.unit.unitType.valueType!=com.oguerreiro.resilient.domain.enumeration.UnitValueType.STRING and id.owner=:organization and id.period=:period and id.variable.code LIKE :variableCode and id.variableClassCode=:variableClassCode")
  BigDecimal sumAllInputsFor(@Param("organization") Organization organization, @Param("period") Period period,
      @Param("variableCode") String variableCode, @Param("variableClassCode") String variableClassCode);

  @Query("SELECT i FROM InputData i WHERE i.period.id = :periodId ORDER BY i.period.name, i.owner.code, i.variable.code")
  Stream<InputData> streamAll(@Param("periodId") Long periodId);

  @Query("""
          SELECT
            new com.oguerreiro.resilient.domain.InventoryData(
                  id.variable,
                  id.variableClassCode,
                  id.variableClassName,
                  id.unit,
                  id.stringValue,
                  SUM(id.imputedValue)
                )
          FROM
            InputData id
                LEFT JOIN Organization o ON id.owner = o
                LEFT JOIN Variable v ON id.variable = v
          WHERE
            id.period.id = :periodId
            AND (:orgIds IS NULL OR o.id IN (:orgIds))
            AND (:scopeId IS NULL OR v.variableScope.id=:scopeId)
            AND (:categoryId IS NULL OR v.variableCategory.id=:categoryId)
            AND id.unit.unitType.valueType NOT IN (
                  com.oguerreiro.resilient.domain.enumeration.UnitValueType.BOOLEAN
                )
            AND (:hiddenForMain=false OR v.hiddenForMain IS NULL OR (:hiddenForMain=true AND v.hiddenForMain=false))
          group by
            id.variable,
            CASE
                WHEN TRIM(id.variableClassCode) = '' THEN 'EMPTY'
                WHEN id.variableClassCode IS NULL THEN 'EMPTY'
                ELSE id.variableClassCode
            END,
            CASE
                WHEN TRIM(id.variableClassName) = '' THEN 'EMPTY'
                WHEN id.variableClassName IS NULL THEN 'EMPTY'
                ELSE id.variableClassName
            END,
            id.unit,
            id.stringValue
          order by
            v.name,
            id.variableClassName
      """)
  Stream<InventoryData> streamAllInventory(@Param("periodId") Long periodId,
      @Param("orgIds") List<Long> organizationIds, @Param("scopeId") Long scopeId, @Param("categoryId") Long categoryId,
      @Param("hiddenForMain") Boolean hiddenForMain);
}
