package com.dora.mapper;

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

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

/**
 * 广场作品审核记录Mapper
 */
@Mapper
public interface PlazaWorkAuditLogMapper {

    /**
     * 插入审核记录
     */
    @Insert("INSERT INTO plaza_work_audit_log (work_no, work_id, audit_status, audit_admin_id, audit_admin_name, audit_remark, audit_time) " +
            "VALUES (#{workNo}, #{workId}, #{auditStatus}, #{auditAdminId}, #{auditAdminName}, #{auditRemark}, #{auditTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(PlazaWorkAuditLog auditLog);

    /**
     * 查询审核历史记录（分页）
     */
    @Select("<script>" +
            "SELECT * FROM plaza_work_audit_log " +
            "WHERE 1=1 " +
            "<if test='auditStatus != null and auditStatus != \"\"'> " +
            "  AND audit_status = #{auditStatus} " +
            "</if> " +
            "<if test='auditAdminId != null'> " +
            "  AND audit_admin_id = #{auditAdminId} " +
            "</if> " +
            "<if test='startTime != null'> " +
            "  AND audit_time &gt;= #{startTime} " +
            "</if> " +
            "<if test='endTime != null'> " +
            "  AND audit_time &lt;= #{endTime} " +
            "</if> " +
            "ORDER BY audit_time DESC " +
            "LIMIT #{offset}, #{limit}" +
            "</script>")
    List<PlazaWorkAuditLog> selectAuditHistory(
            @Param("auditStatus") String auditStatus,
            @Param("auditAdminId") Long auditAdminId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("offset") int offset,
            @Param("limit") int limit
    );

    /**
     * 统计审核历史记录总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM plaza_work_audit_log " +
            "WHERE 1=1 " +
            "<if test='auditStatus != null and auditStatus != \"\"'> " +
            "  AND audit_status = #{auditStatus} " +
            "</if> " +
            "<if test='auditAdminId != null'> " +
            "  AND audit_admin_id = #{auditAdminId} " +
            "</if> " +
            "<if test='startTime != null'> " +
            "  AND audit_time &gt;= #{startTime} " +
            "</if> " +
            "<if test='endTime != null'> " +
            "  AND audit_time &lt;= #{endTime} " +
            "</if>" +
            "</script>")
    long countAuditHistory(
            @Param("auditStatus") String auditStatus,
            @Param("auditAdminId") Long auditAdminId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime
    );

    /**
     * 查询今日审核通过数量
     */
    @Select("SELECT COUNT(*) FROM plaza_work_audit_log " +
            "WHERE audit_status = 'approved' " +
            "AND DATE(audit_time) = CURDATE()")
    long countTodayApproved();

    /**
     * 查询今日审核拒绝数量
     */
    @Select("SELECT COUNT(*) FROM plaza_work_audit_log " +
            "WHERE audit_status = 'rejected' " +
            "AND DATE(audit_time) = CURDATE()")
    long countTodayRejected();

    /**
     * 统计总审核记录数
     */
    @Select("SELECT COUNT(*) FROM plaza_work_audit_log")
    long countTotal();

    /**
     * 查询某个作品的审核历史（按时间倒序）
     */
    @Select("SELECT * FROM plaza_work_audit_log " +
            "WHERE work_no = #{workNo} " +
            "ORDER BY audit_time DESC")
    List<PlazaWorkAuditLog> findByWorkNo(@Param("workNo") String workNo);

    /**
     * 统计某管理员在指定时间段的审核数据
     */
    @Select("<script>" +
            "SELECT " +
            "  COUNT(*) as total_audited, " +
            "  SUM(CASE WHEN audit_status = 'approved' THEN 1 ELSE 0 END) as approved_count, " +
            "  SUM(CASE WHEN audit_status = 'rejected' THEN 1 ELSE 0 END) as rejected_count, " +
            "  AVG(TIMESTAMPDIFF(MINUTE, w.create_time, l.audit_time)) as avg_audit_minutes " +
            "FROM plaza_work_audit_log l " +
            "LEFT JOIN plaza_work w ON l.work_id = w.id " +
            "WHERE l.audit_admin_id = #{adminId} " +
            "<if test='startTime != null and startTime != \"\"'>" +
            "  AND l.audit_time >= #{startTime} " +
            "</if>" +
            "<if test='endTime != null and endTime != \"\"'>" +
            "  AND l.audit_time &lt;= #{endTime} " +
            "</if>" +
            "</script>")
    java.util.Map<String, Object> getAdminStats(
            @Param("adminId") Long adminId,
            @Param("startTime") String startTime,
            @Param("endTime") String endTime
    );

    /**
     * 查询管理员最忙碌的日期
     */
    @Select("<script>" +
            "SELECT DATE(audit_time) as date, COUNT(*) as count " +
            "FROM plaza_work_audit_log " +
            "WHERE audit_admin_id = #{adminId} " +
            "<if test='startTime != null and startTime != \"\"'>" +
            "  AND audit_time >= #{startTime} " +
            "</if>" +
            "<if test='endTime != null and endTime != \"\"'>" +
            "  AND audit_time &lt;= #{endTime} " +
            "</if>" +
            "GROUP BY DATE(audit_time) " +
            "ORDER BY count DESC " +
            "LIMIT 1" +
            "</script>")
    java.util.Map<String, Object> getBusiestDate(
            @Param("adminId") Long adminId,
            @Param("startTime") String startTime,
            @Param("endTime") String endTime
    );
}

