package com.example.demo.repository;

import com.example.demo.model.PointsFreezeRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
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 jakarta.persistence.LockModeType;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 积分冻结记录仓库接口
 */
@Repository
public interface PointsFreezeRecordRepository extends JpaRepository<PointsFreezeRecord, Long> {

    /**
     * 根据任务ID查找冻结记录（返回最新的一条）
     * 注意：同一个taskId可能有多条记录（重试任务），返回最新的
     */
    @Query("SELECT pfr FROM PointsFreezeRecord pfr WHERE pfr.taskId = :taskId ORDER BY pfr.createdAt DESC LIMIT 1")
    Optional<PointsFreezeRecord> findByTaskId(@Param("taskId") String taskId);

    /**
     * 根据任务ID查找冻结记录（带悲观写锁，用于防止并发重复扣除）
     * 使用悲观锁确保在高并发场景下不会重复扣除积分
     * 只返回状态为 FROZEN 的第一条记录，避免重复记录导致的问题
     */
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT pfr FROM PointsFreezeRecord pfr WHERE pfr.taskId = :taskId AND pfr.status = 'FROZEN' ORDER BY pfr.createdAt ASC LIMIT 1")
    Optional<PointsFreezeRecord> findByTaskIdWithLock(@Param("taskId") String taskId);

    /**
     * 根据用户名查找冻结记录
     */
    List<PointsFreezeRecord> findByUsernameOrderByCreatedAtDesc(String username);

    /**
     * 根据用户名和状态查找冻结记录（按完成时间倒序）
     * 用于获取已扣除的消耗记录
     */
    List<PointsFreezeRecord> findByUsernameAndStatusOrderByCompletedAtDesc(String username, PointsFreezeRecord.FreezeStatus status);

    /**
     * 查找用户的冻结中记录
     */
    @Query("SELECT pfr FROM PointsFreezeRecord pfr WHERE pfr.username = :username AND pfr.status = 'FROZEN' ORDER BY pfr.createdAt DESC")
    List<PointsFreezeRecord> findFrozenRecordsByUsername(@Param("username") String username);

    /**
     * 统计用户冻结中的积分总数
     */
    @Query("SELECT COALESCE(SUM(pfr.freezePoints), 0) FROM PointsFreezeRecord pfr WHERE pfr.username = :username AND pfr.status = 'FROZEN'")
    Integer sumFrozenPointsByUsername(@Param("username") String username);

    /**
     * 查找过期的冻结记录（超过24小时未处理）
     */
    @Query("SELECT pfr FROM PointsFreezeRecord pfr WHERE pfr.status = 'FROZEN' AND pfr.createdAt < :expiredTime")
    List<PointsFreezeRecord> findExpiredFrozenRecords(@Param("expiredTime") LocalDateTime expiredTime);

    /**
     * 更新过期记录状态
     */
    @Modifying
    @Query("UPDATE PointsFreezeRecord pfr SET pfr.status = 'EXPIRED', pfr.updatedAt = :updatedAt, pfr.completedAt = :completedAt WHERE pfr.id = :id")
    int updateExpiredRecord(@Param("id") Long id, 
                           @Param("updatedAt") LocalDateTime updatedAt,
                           @Param("completedAt") LocalDateTime completedAt);

    /**
     * 根据任务ID更新状态
     */
    @Modifying
    @Query("UPDATE PointsFreezeRecord pfr SET pfr.status = :status, pfr.updatedAt = :updatedAt, pfr.completedAt = :completedAt WHERE pfr.taskId = :taskId")
    int updateStatusByTaskId(@Param("taskId") String taskId, 
                           @Param("status") PointsFreezeRecord.FreezeStatus status,
                           @Param("updatedAt") LocalDateTime updatedAt,
                           @Param("completedAt") LocalDateTime completedAt);

    /**
     * 删除过期记录（超过7天）
     */
    @Modifying
    @Query("DELETE FROM PointsFreezeRecord pfr WHERE pfr.createdAt < :expiredDate")
    int deleteExpiredRecords(@Param("expiredDate") LocalDateTime expiredDate);

    /**
     * 根据状态列表删除记录
     */
    @Modifying
    @Query("DELETE FROM PointsFreezeRecord pfr WHERE pfr.status IN :statuses")
    int deleteByStatusIn(@Param("statuses") List<PointsFreezeRecord.FreezeStatus> statuses);
}

