package com.nebula.erp.product.repository;

import com.nebula.erp.product.model.Coupon;
import com.nebula.erp.product.model.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.Optional;

public interface CouponRepository extends JpaRepository<Coupon, Long>, JpaSpecificationExecutor<Coupon> {
    // Fetch coupons by tenant
    @Query("SELECT c FROM Coupon c WHERE c.tenant = :tenantName AND c.tenant IS NOT NULL")
    Page<Coupon> findAllByTenant(Pageable pageable, @Param("tenantName") String tenantName);

    // Custom method to find by id and tenant
    @Query("SELECT c FROM Coupon c WHERE c.id = :id AND c.tenant = :tenantName")
    Optional<Coupon> findByIdAndTenant(@Param("id") Long id, @Param("tenantName") String tenantName);

    @Query("SELECT COUNT(*) FROM Coupon c WHERE c.code = :code AND c.tenant = :tenantName")
    int findByCodeAndTenant(@Param("code") String code, @Param("tenantName") String tenantName);

    @Query("SELECT COUNT(*) FROM Coupon c WHERE c.id != :id AND c.code = :code AND c.tenant = :tenantName")
    int findByCodeANDIdAndTenant(@Param("id") Long id, @Param("code") String code,
            @Param("tenantName") String tenantName);

    @Query("""
                SELECT c FROM Coupon c
                WHERE c.tenant = :tenant
                  AND (
                        LOWER(c.name) LIKE :keyword
                     OR LOWER(c.code) LIKE :keyword
                     OR CAST(c.start_date AS string) LIKE :keyword
                     OR CAST(c.end_date AS string) LIKE :keyword
                     OR CAST(c.percentage AS string) LIKE :keyword
                  )
                ORDER BY c.created_at DESC
            """)
    Page<Coupon> searchCoupons(
            @Param("tenant") String tenant,
            @Param("keyword") String keyword,
            Pageable pageable);

}