package com.dora.mapper;

import com.dora.entity.PlazaWork;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * 广场作品Mapper
 * 
 * @author 1818AI
 * @since 2025-10-26
 */
@Mapper
public interface PlazaWorkMapper {

    /**
     * 插入作品
     */
    @Insert("INSERT INTO plaza_work (work_no, user_id, task_no, task_type, model_name, prompt, result_url, " +
            "image_url, aspect_ratio, title, description, tags, is_public, status, audit_status, create_time, update_time) " +
            "VALUES (#{workNo}, #{userId}, #{taskNo}, #{taskType}, #{modelName}, #{prompt}, #{resultUrl}, " +
            "#{imageUrl}, #{aspectRatio}, #{title}, #{description}, #{tags}, #{isPublic}, #{status}, #{auditStatus}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(PlazaWork plazaWork);

    /**
     * 根据作品编号查询
     */
    @Select("SELECT * FROM plaza_work WHERE work_no = #{workNo} AND is_deleted = 0")
    PlazaWork findByWorkNo(@Param("workNo") String workNo);

    /**
     * 根据任务编号查询
     */
    @Select("SELECT * FROM plaza_work WHERE task_no = #{taskNo} AND is_deleted = 0")
    PlazaWork findByTaskNo(@Param("taskNo") String taskNo);

    /**
     * 分页查询公开作品列表（智能排序）
     */
    @Select("<script>" +
            "SELECT pw.id, pw.work_no, pw.user_id, pw.task_no, pw.task_type, pw.model_name, " +
            "       pw.prompt, pw.result_url, pw.image_url, pw.aspect_ratio, pw.title, " +
            "       pw.description, pw.tags, pw.view_count, pw.like_count, pw.share_count, " +
            "       pw.comment_count, pw.is_public, pw.status, pw.create_time, pw.update_time, " +
            "       u.username as nickname, u.avatar_url, " +
            "  <choose>" +
            "    <when test='sortBy == \"hot\"'>" +
            "      (10 * pw.like_count + 1 * pw.view_count) / " +
            "      POW(TIMESTAMPDIFF(HOUR, pw.create_time, NOW()) + 2, 1.5) as score " +
            "    </when>" +
            "    <when test='sortBy == \"quality\"'>" +
            "      CASE WHEN pw.view_count > 0 THEN (pw.like_count * 100.0 / pw.view_count) ELSE 0 END as score " +
            "    </when>" +
            "    <otherwise>" +
            "      UNIX_TIMESTAMP(pw.create_time) as score " +
            "    </otherwise>" +
            "  </choose>" +
            "FROM plaza_work pw " +
            "LEFT JOIN user u ON pw.user_id = u.id " +
            "WHERE pw.status = 'published' AND pw.audit_status = 'approved' AND pw.is_public = 1 AND pw.is_deleted = 0 " +
            "<if test='taskType != null and taskType != \"\"'>" +
            "  AND pw.task_type = #{taskType} " +
            "</if>" +
            "ORDER BY score DESC " +
            "LIMIT #{offset}, #{size}" +
            "</script>")
    List<Map<String, Object>> findPublicWorksList(
            @Param("taskType") String taskType,
            @Param("sortBy") String sortBy,
            @Param("offset") int offset,
            @Param("size") int size
    );

    /**
     * 统计公开作品总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM plaza_work " +
            "WHERE status = 'published' AND audit_status = 'approved' AND is_public = 1 AND is_deleted = 0 " +
            "<if test='taskType != null and taskType != \"\"'>" +
            "  AND task_type = #{taskType} " +
            "</if>" +
            "</script>")
    long countPublicWorks(@Param("taskType") String taskType);

    /**
     * 查询用户的作品列表
     */
    @Select("<script>" +
            "SELECT * FROM plaza_work " +
            "WHERE user_id = #{userId} AND is_deleted = 0 " +
            "<if test='status != null and status != \"\"'>" +
            "  AND status = #{status} " +
            "</if>" +
            "ORDER BY create_time DESC " +
            "LIMIT #{offset}, #{size}" +
            "</script>")
    List<PlazaWork> findUserWorks(
            @Param("userId") Long userId,
            @Param("status") String status,
            @Param("offset") int offset,
            @Param("size") int size
    );

    /**
     * 统计用户作品总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM plaza_work " +
            "WHERE user_id = #{userId} AND is_deleted = 0 " +
            "<if test='status != null and status != \"\"'>" +
            "  AND status = #{status} " +
            "</if>" +
            "</script>")
    long countUserWorks(@Param("userId") Long userId, @Param("status") String status);

    /**
     * 更新作品
     */
    @Update("UPDATE plaza_work SET " +
            "title = #{title}, " +
            "description = #{description}, " +
            "tags = #{tags}, " +
            "is_public = #{isPublic}, " +
            "status = #{status}, " +
            "audit_status = #{auditStatus}, " +
            "audit_admin_id = #{auditAdminId}, " +
            "audit_time = #{auditTime}, " +
            "audit_remark = #{auditRemark}, " +
            "update_time = NOW() " +
            "WHERE id = #{id}")
    int update(PlazaWork plazaWork);

    /**
     * 增加浏览数
     */
    @Update("UPDATE plaza_work SET view_count = view_count + 1 WHERE id = #{id}")
    int incrementViewCount(@Param("id") Long id);

    /**
     * 增加点赞数
     */
    @Update("UPDATE plaza_work SET like_count = like_count + 1 WHERE id = #{id}")
    int incrementLikeCount(@Param("id") Long id);

    /**
     * 减少点赞数
     */
    @Update("UPDATE plaza_work SET like_count = GREATEST(like_count - 1, 0) WHERE id = #{id}")
    int decrementLikeCount(@Param("id") Long id);

    /**
     * 增加分享数
     */
    @Update("UPDATE plaza_work SET share_count = share_count + 1 WHERE id = #{id}")
    int incrementShareCount(@Param("id") Long id);

    /**
     * 逻辑删除作品
     */
    @Update("UPDATE plaza_work SET is_deleted = 1, update_time = NOW() WHERE id = #{id} AND user_id = #{userId}")
    int deleteByIdAndUserId(@Param("id") Long id, @Param("userId") Long userId);

    /**
     * 根据作品编号逻辑删除
     */
    @Update("UPDATE plaza_work SET is_deleted = 1, update_time = NOW() WHERE work_no = #{workNo} AND user_id = #{userId}")
    int deleteByWorkNoAndUserId(@Param("workNo") String workNo, @Param("userId") Long userId);

    /**
     * 获取广场统计数据
     */
    @Select("SELECT " +
            "COUNT(*) as total_works, " +
            "SUM(view_count) as total_views, " +
            "SUM(like_count) as total_likes " +
            "FROM plaza_work " +
            "WHERE status = 'published' AND audit_status = 'approved' AND is_public = 1 AND is_deleted = 0")
    Map<String, Object> getPlazaStats();

    /**
     * 按任务类型统计作品数
     */
    @Select("SELECT task_type, COUNT(*) as count " +
            "FROM plaza_work " +
            "WHERE status = 'published' AND audit_status = 'approved' AND is_public = 1 AND is_deleted = 0 " +
            "GROUP BY task_type")
    List<Map<String, Object>> countWorksByType();

    /**
     * 查询作品详情（包含作者信息）
     */
    @Select("SELECT pw.id, pw.work_no, pw.user_id, pw.task_no, pw.task_type, pw.model_name, " +
            "       pw.prompt, pw.result_url, pw.image_url, pw.aspect_ratio, pw.title, " +
            "       pw.description, pw.tags, pw.view_count, pw.like_count, pw.share_count, " +
            "       pw.comment_count, pw.is_public, pw.status, pw.create_time, pw.update_time, " +
            "       u.username as nickname, u.avatar_url " +
            "FROM plaza_work pw " +
            "LEFT JOIN user u ON pw.user_id = u.id " +
            "WHERE pw.work_no = #{workNo} AND pw.is_deleted = 0")
    Map<String, Object> findWorkDetailByWorkNo(@Param("workNo") String workNo);

    /**
     * 通过任务编号查询作品详情（包含作者信息）
     */
    @Select("SELECT pw.id, pw.work_no, pw.user_id, pw.task_no, pw.task_type, pw.model_name, " +
            "       pw.prompt, pw.result_url, pw.image_url, pw.aspect_ratio, pw.title, " +
            "       pw.description, pw.tags, pw.view_count, pw.like_count, pw.share_count, " +
            "       pw.comment_count, pw.is_public, pw.status, pw.create_time, pw.update_time, " +
            "       u.username as nickname, u.avatar_url " +
            "FROM plaza_work pw " +
            "LEFT JOIN user u ON pw.user_id = u.id " +
            "WHERE pw.task_no = #{taskNo} AND pw.is_deleted = 0")
    Map<String, Object> findWorkDetailByTaskNo(@Param("taskNo") String taskNo);

    /**
     * 查询待审核作品列表（分页）
     */
    @Select("<script>" +
            "SELECT * FROM plaza_work " +
            "WHERE audit_status = 'pending' AND is_deleted = 0 " +
            "<if test='taskType != null and taskType != \"\"'> " +
            "  AND task_type = #{taskType} " +
            "</if> " +
            "<if test='userId != null'> " +
            "  AND user_id = #{userId} " +
            "</if> " +
            "ORDER BY create_time ASC " +
            "LIMIT #{offset}, #{limit}" +
            "</script>")
    List<PlazaWork> selectPendingWorks(
            @Param("taskType") String taskType,
            @Param("userId") Long userId,
            @Param("offset") int offset,
            @Param("limit") int limit
    );

    /**
     * 统计待审核作品数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM plaza_work " +
            "WHERE audit_status = 'pending' AND is_deleted = 0 " +
            "<if test='taskType != null and taskType != \"\"'> " +
            "  AND task_type = #{taskType} " +
            "</if> " +
            "<if test='userId != null'> " +
            "  AND user_id = #{userId} " +
            "</if>" +
            "</script>")
    long countPendingWorks(
            @Param("taskType") String taskType,
            @Param("userId") Long userId
    );

    /**
     * 更新作品信息
     */
    @Update("UPDATE plaza_work SET " +
            "audit_status = #{auditStatus}, " +
            "audit_admin_id = #{auditAdminId}, " +
            "audit_time = #{auditTime}, " +
            "audit_remark = #{auditRemark}, " +
            "update_time = #{updateTime} " +
            "WHERE id = #{id}")
    int updateById(PlazaWork plazaWork);

    /**
     * 统计作者的作品通过率
     */
    @Select("SELECT " +
            "  COUNT(*) as total, " +
            "  SUM(CASE WHEN audit_status = 'approved' THEN 1 ELSE 0 END) as approved " +
            "FROM plaza_work " +
            "WHERE user_id = #{userId} AND status = 'published' AND is_deleted = 0")
    java.util.Map<String, Object> getAuthorStats(@Param("userId") Long userId);

    /**
     * 查询最早的N个待审核作品
     */
    @Select("<script>" +
            "SELECT * FROM plaza_work " +
            "WHERE audit_status = 'pending' AND is_deleted = 0 " +
            "<if test='taskType != null and taskType != \"\"'>" +
            "  AND task_type = #{taskType} " +
            "</if>" +
            "ORDER BY create_time ASC " +
            "LIMIT #{count}" +
            "</script>")
    List<PlazaWork> findEarliestPendingWorks(
            @Param("count") int count,
            @Param("taskType") String taskType
    );

    /**
     * 查询用户点赞的作品列表（包含作品信息和作者信息）
     */
    @Select("SELECT pwl.id as like_id, pwl.create_time as liked_time, " +
            "       pw.id, pw.work_no, pw.user_id, pw.task_no, pw.task_type, pw.model_name, " +
            "       pw.prompt, pw.result_url, pw.image_url, pw.aspect_ratio, pw.title, " +
            "       pw.description, pw.tags, pw.view_count, pw.like_count, pw.share_count, " +
            "       pw.comment_count, pw.is_public, pw.status, pw.create_time, pw.update_time, " +
            "       u.username as nickname, u.avatar_url " +
            "FROM plaza_work_like pwl " +
            "INNER JOIN plaza_work pw ON pwl.work_id = pw.id " +
            "LEFT JOIN user u ON pw.user_id = u.id " +
            "WHERE pwl.user_id = #{userId} AND pw.is_deleted = 0 " +
            "ORDER BY pwl.create_time DESC " +
            "LIMIT #{offset}, #{size}")
    List<Map<String, Object>> findUserLikedWorks(
            @Param("userId") Long userId,
            @Param("offset") int offset,
            @Param("size") int size
    );
}

