package de.effigenix.infrastructure.inventory.persistence.repository;

import de.effigenix.infrastructure.inventory.persistence.entity.StockEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

public interface StockJpaRepository extends JpaRepository<StockEntity, String> {

    Optional<StockEntity> findByArticleIdAndStorageLocationId(String articleId, String storageLocationId);

    boolean existsByArticleIdAndStorageLocationId(String articleId, String storageLocationId);

    List<StockEntity> findAllByStorageLocationId(String storageLocationId);

    List<StockEntity> findAllByArticleId(String articleId);

    @Query(value = """
            SELECT DISTINCT s.* FROM stocks s \
            JOIN stock_batches b ON b.stock_id = s.id \
            WHERE (b.status IN ('AVAILABLE', 'EXPIRING_SOON') AND b.expiry_date < :today) \
               OR (s.minimum_shelf_life_days IS NOT NULL AND b.status = 'AVAILABLE' \
                   AND b.expiry_date >= :today \
                   AND b.expiry_date < :today + s.minimum_shelf_life_days * INTERVAL '1 day')""",
            nativeQuery = true)
    List<StockEntity> findAllWithExpiryRelevantBatches(@Param("today") LocalDate today);

    @Query(value = """
            SELECT DISTINCT s.* FROM stocks s \
            LEFT JOIN stock_batches b ON b.stock_id = s.id AND b.status IN ('AVAILABLE', 'EXPIRING_SOON') \
            WHERE s.minimum_level_amount IS NOT NULL \
            GROUP BY s.id \
            HAVING COALESCE(SUM(b.quantity_amount), 0) < s.minimum_level_amount""",
            nativeQuery = true)
    List<StockEntity> findAllBelowMinimumLevel();
}
