package com.example.demo.repository;

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

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.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.example.demo.model.UserWork;
import com.example.demo.model.enums.CommonTaskType;

/**
 * 用户作品仓库接口
 */
@Repository
public interface UserWorkRepository extends JpaRepository<UserWork, Long> {

    /**
     * 根据用户名查找作品（包括正在生成中的作品）
     * - 排除 DELETED 和 FAILED 状态
     * - PROCESSING/PENDING 状态的作品即使没有 resultUrl 也要返回
     * - COMPLETED 状态的分镜视频必须有视频URL（排除只有图片URL的）
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.username = :username AND uw.status NOT IN ('DELETED', 'FAILED') AND (uw.status IN ('PROCESSING', 'PENDING') OR (uw.resultUrl IS NOT NULL AND uw.resultUrl != '' AND (uw.workType != 'STORYBOARD_VIDEO' OR uw.resultUrl NOT LIKE '%.png'))) ORDER BY uw.createdAt DESC")
    Page<UserWork> findByUsernameOrderByCreatedAtDesc(@Param("username") String username, Pageable pageable);
    
    /**
     * 根据用户名查找作品（只返回有resultUrl的作品）
     * 用于创作页面历史记录，过滤掉没有resultUrl的作品
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.username = :username AND uw.status != 'DELETED' AND uw.resultUrl IS NOT NULL AND uw.resultUrl != '' ORDER BY uw.createdAt DESC")
    Page<UserWork> findByUsernameWithResultUrlOrderByCreatedAtDesc(@Param("username") String username, Pageable pageable);

    /**
     * 根据用户名和状态查找作品
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.username = :username AND uw.status = :status ORDER BY uw.createdAt DESC")
    List<UserWork> findByUsernameAndStatusOrderByCreatedAtDesc(@Param("username") String username, @Param("status") UserWork.WorkStatus status);

    /**
     * 根据用户名和作品类型查找作品（包括所有状态，用于历史记录）
     * 排除 DELETED 和 FAILED 状态
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.username = :username AND uw.workType = :workType AND uw.status NOT IN ('DELETED', 'FAILED') ORDER BY uw.createdAt DESC")
    Page<UserWork> findByUsernameAndWorkTypeOrderByCreatedAtDesc(@Param("username") String username, @Param("workType") CommonTaskType workType, Pageable pageable);

    /**
     * 根据用户名、作品类型和状态查找作品
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.username = :username AND uw.workType = :workType AND uw.status = :status ORDER BY uw.createdAt DESC")
    Page<UserWork> findByUsernameAndWorkTypeAndStatusOrderByCreatedAtDesc(@Param("username") String username, @Param("workType") CommonTaskType workType, @Param("status") UserWork.WorkStatus status, Pageable pageable);

    /**
     * 根据用户名查找正在进行中和排队中的作品
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.username = :username AND (uw.status = 'PROCESSING' OR uw.status = 'PENDING') ORDER BY uw.createdAt DESC")
    List<UserWork> findByUsernameAndProcessingOrPendingOrderByCreatedAtDesc(@Param("username") String username);
    
    /**
     * 查找所有PROCESSING状态的作品（用于系统重启清理）
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.status = 'PROCESSING'")
    List<UserWork> findAllByProcessingStatus();
    
    /**
     * 查找所有FAILED状态的作品（用于系统重启修复）
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.status = 'FAILED' ORDER BY uw.updatedAt DESC")
    List<UserWork> findAllByFailedStatus();

    /**
     * 根据任务ID查找作品
     */
    Optional<UserWork> findByTaskId(String taskId);

    /**
     * 根据用户名和任务ID查找作品
     */
    Optional<UserWork> findByUsernameAndTaskId(String username, String taskId);

    /**
     * 查找公开作品
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.isPublic = true AND uw.status = 'COMPLETED' ORDER BY uw.createdAt DESC")
    Page<UserWork> findPublicWorksOrderByCreatedAtDesc(Pageable pageable);

    /**
     * 根据作品类型查找公开作品
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.isPublic = true AND uw.status = 'COMPLETED' AND uw.workType = :workType ORDER BY uw.createdAt DESC")
    Page<UserWork> findPublicWorksByTypeOrderByCreatedAtDesc(@Param("workType") CommonTaskType workType, Pageable pageable);

    /**
     * 根据标签搜索作品
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.isPublic = true AND uw.status = 'COMPLETED' AND uw.tags LIKE %:tag% ORDER BY uw.createdAt DESC")
    Page<UserWork> findPublicWorksByTagOrderByCreatedAtDesc(@Param("tag") String tag, Pageable pageable);

    /**
     * 根据提示词搜索作品
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.isPublic = true AND uw.status = 'COMPLETED' AND uw.prompt LIKE %:keyword% ORDER BY uw.createdAt DESC")
    Page<UserWork> findPublicWorksByPromptOrderByCreatedAtDesc(@Param("keyword") String keyword, Pageable pageable);

    /**
     * 统计用户作品数量
     */
    @Query("SELECT COUNT(uw) FROM UserWork uw WHERE uw.username = :username AND uw.status != 'DELETED'")
    long countByUsername(@Param("username") String username);
    
    /**
     * 统计用户进行中的任务数量（所有类型：文生视频、图生视频、分镜视频等）
     * 包括 PROCESSING 和 PENDING 状态
     */
    @Query("SELECT COUNT(uw) FROM UserWork uw WHERE uw.username = :username AND (uw.status = 'PROCESSING' OR uw.status = 'PENDING')")
    long countProcessingTasksByUsername(@Param("username") String username);

    /**
     * 统计用户公开作品数量
     */
    @Query("SELECT COUNT(uw) FROM UserWork uw WHERE uw.username = :username AND uw.isPublic = true AND uw.status = 'COMPLETED'")
    long countPublicWorksByUsername(@Param("username") String username);

    /**
     * 获取热门作品（按浏览次数排序）
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.isPublic = true AND uw.status = 'COMPLETED' ORDER BY uw.viewCount DESC, uw.createdAt DESC")
    Page<UserWork> findPopularWorksOrderByViewCountDesc(Pageable pageable);

    /**
     * 获取最新作品
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.isPublic = true AND uw.status = 'COMPLETED' ORDER BY uw.createdAt DESC")
    Page<UserWork> findLatestWorksOrderByCreatedAtDesc(Pageable pageable);

    /**
     * 更新作品状态
     */
    @Modifying
    @Query("UPDATE UserWork uw SET uw.status = :status, uw.updatedAt = :updatedAt, uw.completedAt = :completedAt WHERE uw.taskId = :taskId")
    int updateStatusByTaskId(@Param("taskId") String taskId, 
                           @Param("status") UserWork.WorkStatus status, 
                           @Param("updatedAt") LocalDateTime updatedAt,
                           @Param("completedAt") LocalDateTime completedAt);

    /**
     * 更新作品结果URL
     */
    @Modifying
    @Query("UPDATE UserWork uw SET uw.resultUrl = :resultUrl, uw.updatedAt = :updatedAt WHERE uw.taskId = :taskId")
    int updateResultUrlByTaskId(@Param("taskId") String taskId, 
                               @Param("resultUrl") String resultUrl,
                               @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 增加浏览次数
     */
    @Modifying
    @Query("UPDATE UserWork uw SET uw.viewCount = uw.viewCount + 1, uw.updatedAt = :updatedAt WHERE uw.id = :id")
    int incrementViewCount(@Param("id") Long id, @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 增加点赞次数
     */
    @Modifying
    @Query("UPDATE UserWork uw SET uw.likeCount = uw.likeCount + 1, uw.updatedAt = :updatedAt WHERE uw.id = :id")
    int incrementLikeCount(@Param("id") Long id, @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 增加下载次数
     */
    @Modifying
    @Query("UPDATE UserWork uw SET uw.downloadCount = uw.downloadCount + 1, uw.updatedAt = :updatedAt WHERE uw.id = :id")
    int incrementDownloadCount(@Param("id") Long id, @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 软删除作品
     */
    @Modifying
    @Query("UPDATE UserWork uw SET uw.status = 'DELETED', uw.updatedAt = :updatedAt WHERE uw.id = :id AND uw.username = :username")
    int softDeleteWork(@Param("id") Long id, @Param("username") String username, @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 批量软删除作品（仅删除属于指定用户且未被删除的）
     */
    @Modifying
    @Query("UPDATE UserWork uw SET uw.status = 'DELETED', uw.updatedAt = :updatedAt WHERE uw.id IN :ids AND uw.username = :username AND uw.status <> 'DELETED'")
    int batchSoftDeleteWorks(@Param("ids") List<Long> ids, @Param("username") String username, @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 根据 ID 列表和用户名查询作品（用于批量删除前的校验）
     */
    @Query("SELECT uw FROM UserWork uw WHERE uw.id IN :ids AND uw.username = :username AND uw.status <> 'DELETED'")
    List<UserWork> findAllByIdInAndUsername(@Param("ids") List<Long> ids, @Param("username") String username);

    /**
     * 删除过期作品（超过30天且状态为失败）
     */
    @Modifying
    @Query("DELETE FROM UserWork uw WHERE uw.status = 'FAILED' AND uw.createdAt < :expiredDate")
    int deleteExpiredFailedWorks(@Param("expiredDate") LocalDateTime expiredDate);

    /**
     * 获取用户作品统计信息
     */
    @Query("SELECT " +
           "COUNT(CASE WHEN uw.status = 'COMPLETED' THEN 1 END) as completedCount, " +
           "COUNT(CASE WHEN uw.status = 'PROCESSING' THEN 1 END) as processingCount, " +
           "COUNT(CASE WHEN uw.status = 'FAILED' THEN 1 END) as failedCount, " +
           "SUM(CASE WHEN uw.status = 'COMPLETED' THEN uw.pointsCost ELSE 0 END) as totalPointsCost " +
           "FROM UserWork uw WHERE uw.username = :username AND uw.status != 'DELETED'")
    Object[] getUserWorkStats(@Param("username") String username);
}


