package com.dora.mapper;

import com.dora.entity.GiftCodeUsage;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

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

/**
 * 礼品码使用记录Mapper接口
 */
@Mapper
public interface GiftCodeUsageMapper {

    /**
     * 插入使用记录
     * @param usage 使用记录
     * @return 插入结果
     */
    @Insert("INSERT INTO gift_code_usage (user_id, gift_code_id, code, type, points, points_expire_days, " +
            "target_role, duration_days, old_role, new_role, old_points, new_points, " +
            "old_points_expires_at, new_points_expires_at, old_expires_at, new_expires_at, " +
            "status, error_message, create_time, is_deleted) " +
            "VALUES (#{userId}, #{giftCodeId}, #{code}, #{type}, #{points}, #{pointsExpireDays}, " +
            "#{targetRole}, #{durationDays}, #{oldRole}, #{newRole}, #{oldPoints}, #{newPoints}, " +
            "#{oldPointsExpiresAt}, #{newPointsExpiresAt}, #{oldExpiresAt}, #{newExpiresAt}, " +
            "#{status}, #{errorMessage}, NOW(), 0)")
    int insert(GiftCodeUsage usage);

    /**
     * 检查用户是否已使用过该礼品码
     * @param userId 用户ID
     * @param giftCodeId 礼品码ID
     * @return 使用记录数量
     */
    @Select("SELECT COUNT(*) FROM gift_code_usage WHERE user_id = #{userId} AND gift_code_id = #{giftCodeId} AND is_deleted = 0")
    int countByUserAndCode(@Param("userId") Long userId, @Param("giftCodeId") Long giftCodeId);

    /**
     * 检查用户是否使用过指定类型的礼品码
     * @param userId 用户ID
     * @param type 礼品码类型（2=会员卡）
     * @return 使用记录数量
     */
    @Select("SELECT COUNT(*) FROM gift_code_usage WHERE user_id = #{userId} AND type = #{type} AND status = 1 AND is_deleted = 0")
    int countByUserAndType(@Param("userId") Long userId, @Param("type") Integer type);

    // ==================== 管理端接口 ====================

    /**
     * 分页查询使用记录列表（带关联查询）
     * @param giftCodeId 礼品码ID
     * @param userId 用户ID
     * @param code 礼品码
     * @param type 礼品码类型
     * @param status 使用状态
     * @param createTimeStart 使用时间开始
     * @param createTimeEnd 使用时间结束
     * @param sortField 排序字段
     * @param sortOrder 排序方向
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 使用记录列表
     */
    @Select("<script>" +
            "SELECT " +
            "  u.*, " +
            "  usr.username, " +
            "  gc.name as gift_code_name " +
            "FROM gift_code_usage u " +
            "LEFT JOIN user usr ON u.user_id = usr.id " +
            "LEFT JOIN gift_code gc ON u.gift_code_id = gc.id " +
            "WHERE u.is_deleted = 0" +
            "<if test='giftCodeId != null'>" +
            "  AND u.gift_code_id = #{giftCodeId}" +
            "</if>" +
            "<if test='userId != null'>" +
            "  AND u.user_id = #{userId}" +
            "</if>" +
            "<if test='code != null and code != \"\"'>" +
            "  AND u.code = #{code}" +
            "</if>" +
            "<if test='type != null'>" +
            "  AND u.type = #{type}" +
            "</if>" +
            "<if test='status != null'>" +
            "  AND u.status = #{status}" +
            "</if>" +
            "<if test='createTimeStart != null'>" +
            "  AND <![CDATA[u.create_time >= #{createTimeStart}]]>" +
            "</if>" +
            "<if test='createTimeEnd != null'>" +
            "  AND <![CDATA[u.create_time <= #{createTimeEnd}]]>" +
            "</if>" +
            " ORDER BY " +
            "<choose>" +
            "  <when test='sortField == \"code\"'>u.code</when>" +
            "  <when test='sortField == \"type\"'>u.type</when>" +
            "  <when test='sortField == \"status\"'>u.status</when>" +
            "  <when test='sortField == \"username\"'>usr.username</when>" +
            "  <otherwise>u.create_time</otherwise>" +
            "</choose>" +
            "<choose>" +
            "  <when test='sortOrder == \"asc\"'>ASC</when>" +
            "  <otherwise>DESC</otherwise>" +
            "</choose>" +
            " LIMIT #{offset}, #{limit}" +
            "</script>")
    List<java.util.Map<String, Object>> selectPageListWithUserAndGiftCode(@Param("giftCodeId") Long giftCodeId,
                                                                           @Param("userId") Long userId,
                                                                           @Param("code") String code,
                                                                           @Param("type") Integer type,
                                                                           @Param("status") Integer status,
                                                                           @Param("createTimeStart") LocalDateTime createTimeStart,
                                                                           @Param("createTimeEnd") LocalDateTime createTimeEnd,
                                                                           @Param("sortField") String sortField,
                                                                           @Param("sortOrder") String sortOrder,
                                                                           @Param("offset") Long offset,
                                                                           @Param("limit") Integer limit);

    /**
     * 统计使用记录总数
     * @param giftCodeId 礼品码ID
     * @param userId 用户ID
     * @param code 礼品码
     * @param type 礼品码类型
     * @param status 使用状态
     * @param createTimeStart 使用时间开始
     * @param createTimeEnd 使用时间结束
     * @return 总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM gift_code_usage u WHERE u.is_deleted = 0" +
            "<if test='giftCodeId != null'>" +
            "  AND u.gift_code_id = #{giftCodeId}" +
            "</if>" +
            "<if test='userId != null'>" +
            "  AND u.user_id = #{userId}" +
            "</if>" +
            "<if test='code != null and code != \"\"'>" +
            "  AND u.code = #{code}" +
            "</if>" +
            "<if test='type != null'>" +
            "  AND u.type = #{type}" +
            "</if>" +
            "<if test='status != null'>" +
            "  AND u.status = #{status}" +
            "</if>" +
            "<if test='createTimeStart != null'>" +
            "  AND <![CDATA[u.create_time >= #{createTimeStart}]]>" +
            "</if>" +
            "<if test='createTimeEnd != null'>" +
            "  AND <![CDATA[u.create_time <= #{createTimeEnd}]]>" +
            "</if>" +
            "</script>")
    Long countPageList(@Param("giftCodeId") Long giftCodeId,
                       @Param("userId") Long userId,
                       @Param("code") String code,
                       @Param("type") Integer type,
                       @Param("status") Integer status,
                       @Param("createTimeStart") LocalDateTime createTimeStart,
                       @Param("createTimeEnd") LocalDateTime createTimeEnd);

    /**
     * 根据礼品码ID查询使用记录
     * @param giftCodeId 礼品码ID
     * @return 使用记录列表
     */
    @Select("SELECT u.*, usr.username FROM gift_code_usage u " +
            "LEFT JOIN user usr ON u.user_id = usr.id " +
            "WHERE u.gift_code_id = #{giftCodeId} AND u.is_deleted = 0 " +
            "ORDER BY u.create_time DESC")
    List<java.util.Map<String, Object>> selectByGiftCodeIdWithUser(@Param("giftCodeId") Long giftCodeId);

    /**
     * 统计使用记录信息（用于统计页面）
     * @return 统计信息Map
     */
    @Select("SELECT " +
            "COUNT(*) as total_usage_count, " +
            "COUNT(CASE WHEN status = 1 THEN 1 END) as success_usage_count, " +
            "COUNT(CASE WHEN status = 0 THEN 1 END) as failed_usage_count " +
            "FROM gift_code_usage WHERE is_deleted = 0")
    java.util.Map<String, Object> getUsageStatistics();

    /**
     * 获取本月使用次数
     * @return 本月使用次数
     */
    @Select("SELECT COUNT(*) FROM gift_code_usage WHERE is_deleted = 0 AND status = 1 " +
            "AND DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')")
    Long getThisMonthUsageCount();

    /**
     * 根据用户ID查询使用记录
     * @param userId 用户ID
     * @param limit 限制数量
     * @return 使用记录列表
     */
    @Select("SELECT u.*, gc.name as gift_code_name FROM gift_code_usage u " +
            "LEFT JOIN gift_code gc ON u.gift_code_id = gc.id " +
            "WHERE u.user_id = #{userId} AND u.is_deleted = 0 " +
            "ORDER BY u.create_time DESC " +
            "LIMIT #{limit}")
    List<java.util.Map<String, Object>> selectByUserIdWithGiftCode(@Param("userId") Long userId, @Param("limit") Integer limit);

    /**
     * 获取礼品码的最近使用记录
     * @param giftCodeId 礼品码ID
     * @param limit 限制数量
     * @return 使用记录列表
     */
    @Select("SELECT u.*, usr.username FROM gift_code_usage u " +
            "LEFT JOIN user usr ON u.user_id = usr.id " +
            "WHERE u.gift_code_id = #{giftCodeId} AND u.is_deleted = 0 AND u.status = 1 " +
            "ORDER BY u.create_time DESC " +
            "LIMIT #{limit}")
    List<java.util.Map<String, Object>> selectRecentUsageByGiftCodeId(@Param("giftCodeId") Long giftCodeId, @Param("limit") Integer limit);
} 