package com.example.volunteer.repository;

import com.example.volunteer.model.Signup;
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.List;

/**
 * ============================================================================
 * (e) Repository — SignupRepository
 * ============================================================================
 *
 * 【题目 / Question (e)】
 * EN — Write all the necessary Controller and JPA Repository code to implement the REST API
 *      for creating sign-up records. (5 marks)
 * 中 — 写出实现“创建报名记录”REST 接口所需的 Controller 与 JPA Repository 代码。（5 分）
 *
 * 【样卷 Q4(b)：聚合查询 / Aggregation query】
 * EN — "Top ten, whose total exceeds N" = GROUP BY + HAVING + ORDER BY aggregate + row limit.
 *      JPQL has no LIMIT, so the limit is passed through Pageable and translated per dialect.
 * 中 — “累计超过 N 的前十名” = GROUP BY + HAVING + 按聚合排序 + 限行数。
 *      JPQL 没有 LIMIT，所以通过 Pageable 传条数，Spring Data 按数据库方言翻译。
 *
 * 【为什么返回 Object[] / Why Object[]】
 * EN — The query selects two things (the volunteer and the total hours), not one entity,
 *      so the result cannot be List<Volunteer>. The service converts each row into a record.
 * 中 — 查询选了两样东西（志愿者 + 累计时数），不是单个实体，所以返回不了 List<Volunteer>；
 *      Service 再把每行转成 record。
 *
 * 【样卷 Q4(c)：动态查询 / Dynamic query】
 * EN — JpaSpecificationExecutor adds findAll(Specification), so the WHERE clause can be built
 *      at runtime and only the filters that are present get applied.
 * 中 — 继承 JpaSpecificationExecutor 后多出 findAll(Specification)，可在运行时拼 WHERE，
 *      只让“有值”的条件生效。
 */
public interface SignupRepository extends JpaRepository<Signup, Long>,
                                          JpaSpecificationExecutor<Signup> {

    /** 派生查询 / derived query：该活动与志愿者的报名是否存在 */
    boolean existsByEventIdAndVolunteerId(Long eventId, Long volunteerId);

    /**
     * 聚合查询 / aggregate query — 样卷 Q4(b)
     * 返回 / returns: [0] = Volunteer, [1] = 累计时数 (Long)
     */
    @Query("select s.volunteer, sum(s.hours) from Signup s " +
           "group by s.volunteer " +
           "having sum(s.hours) >= :minHours " +
           "order by sum(s.hours) desc")
    List<Object[]> findTopVolunteers(@Param("minHours") int minHours, Pageable pageable);
}