package com.dora.mapper;

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

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

/**
 * 礼品码Mapper接口
 */
@Mapper
public interface GiftCodeMapper {

    /**
     * 根据礼品码查询
     * @param code 礼品码
     * @return 礼品码信息
     */
    @Select("SELECT * FROM gift_code WHERE code = #{code} AND is_deleted = 0 AND is_active = 1")
    GiftCode selectByCode(@Param("code") String code);

    /**
     * 更新礼品码使用次数
     * @param id 礼品码ID
     * @param usedCount 已使用次数
     * @return 更新结果
     */
    @Update("UPDATE gift_code SET used_count = #{usedCount}, update_time = NOW() WHERE id = #{id}")
    int updateUsedCount(@Param("id") Long id, @Param("usedCount") Integer usedCount);

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

    /**
     * 根据ID查询礼品码（包含已删除的）
     * @param id 礼品码ID
     * @return 礼品码信息
     */
    @Select("SELECT * FROM gift_code WHERE id = #{id} AND is_deleted = 0")
    GiftCode selectById(@Param("id") Long id);

    /**
     * 分页查询礼品码列表
     * @param keyword 搜索关键词
     * @param type 礼品码类型
     * @param isActive 启用状态
     * @param createTimeStart 创建时间开始
     * @param createTimeEnd 创建时间结束
     * @param sortField 排序字段
     * @param sortOrder 排序方向
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 礼品码列表
     */
    @Select("<script>" +
            "SELECT * FROM gift_code WHERE is_deleted = 0" +
            "<if test='keyword != null and keyword != \"\"'>" +
            "  AND (code LIKE CONCAT('%', #{keyword}, '%') OR name LIKE CONCAT('%', #{keyword}, '%'))" +
            "</if>" +
            "<if test='type != null'>" +
            "  AND type = #{type}" +
            "</if>" +
            "<if test='isActive != null'>" +
            "  AND is_active = #{isActive}" +
            "</if>" +
            "<if test='createTimeStart != null'>" +
            "  AND <![CDATA[create_time >= #{createTimeStart}]]>" +
            "</if>" +
            "<if test='createTimeEnd != null'>" +
            "  AND <![CDATA[create_time <= #{createTimeEnd}]]>" +
            "</if>" +
            " ORDER BY " +
            "<choose>" +
            "  <when test='sortField == \"code\"'>code</when>" +
            "  <when test='sortField == \"name\"'>name</when>" +
            "  <when test='sortField == \"type\"'>type</when>" +
            "  <when test='sortField == \"totalCount\"'>total_count</when>" +
            "  <when test='sortField == \"usedCount\"'>used_count</when>" +
            "  <when test='sortField == \"isActive\"'>is_active</when>" +
            "  <when test='sortField == \"expireTime\"'>expire_time</when>" +
            "  <otherwise>create_time</otherwise>" +
            "</choose>" +
            "<choose>" +
            "  <when test='sortOrder == \"asc\"'>ASC</when>" +
            "  <otherwise>DESC</otherwise>" +
            "</choose>" +
            " LIMIT #{offset}, #{limit}" +
            "</script>")
    List<GiftCode> selectPageList(@Param("keyword") String keyword,
                                  @Param("type") Integer type,
                                  @Param("isActive") Integer isActive,
                                  @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 keyword 搜索关键词
     * @param type 礼品码类型
     * @param isActive 启用状态
     * @param createTimeStart 创建时间开始
     * @param createTimeEnd 创建时间结束
     * @return 总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM gift_code WHERE is_deleted = 0" +
            "<if test='keyword != null and keyword != \"\"'>" +
            "  AND (code LIKE CONCAT('%', #{keyword}, '%') OR name LIKE CONCAT('%', #{keyword}, '%'))" +
            "</if>" +
            "<if test='type != null'>" +
            "  AND type = #{type}" +
            "</if>" +
            "<if test='isActive != null'>" +
            "  AND is_active = #{isActive}" +
            "</if>" +
            "<if test='createTimeStart != null'>" +
            "  AND <![CDATA[create_time >= #{createTimeStart}]]>" +
            "</if>" +
            "<if test='createTimeEnd != null'>" +
            "  AND <![CDATA[create_time <= #{createTimeEnd}]]>" +
            "</if>" +
            "</script>")
    Long countPageList(@Param("keyword") String keyword,
                       @Param("type") Integer type,
                       @Param("isActive") Integer isActive,
                       @Param("createTimeStart") LocalDateTime createTimeStart,
                       @Param("createTimeEnd") LocalDateTime createTimeEnd);

    /**
     * 插入礼品码
     * @param giftCode 礼品码信息
     * @return 插入结果
     */
    @Insert("INSERT INTO gift_code (code, name, description, type, points, points_expire_days, " +
            "target_role, duration_days, total_count, used_count, is_active, expire_time, " +
            "create_time, update_time, is_deleted) " +
            "VALUES (#{code}, #{name}, #{description}, #{type}, #{points}, #{pointsExpireDays}, " +
            "#{targetRole}, #{durationDays}, #{totalCount}, #{usedCount}, #{isActive}, #{expireTime}, " +
            "NOW(), NOW(), 0)")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(GiftCode giftCode);

    /**
     * 更新礼品码
     * @param giftCode 礼品码信息
     * @return 更新结果
     */
    @Update("UPDATE gift_code SET name = #{name}, description = #{description}, " +
            "points = #{points}, points_expire_days = #{pointsExpireDays}, " +
            "target_role = #{targetRole}, duration_days = #{durationDays}, " +
            "total_count = #{totalCount}, expire_time = #{expireTime}, " +
            "update_time = NOW() WHERE id = #{id}")
    int updateById(GiftCode giftCode);

    /**
     * 更新礼品码状态
     * @param id 礼品码ID
     * @param isActive 启用状态
     * @return 更新结果
     */
    @Update("UPDATE gift_code SET is_active = #{isActive}, update_time = NOW() WHERE id = #{id}")
    int updateStatus(@Param("id") Long id, @Param("isActive") Integer isActive);

    /**
     * 批量更新礼品码状态
     * @param ids 礼品码ID列表
     * @param isActive 启用状态
     * @return 更新结果
     */
    @Update("<script>" +
            "UPDATE gift_code SET is_active = #{isActive}, update_time = NOW() WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "  #{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdateStatus(@Param("ids") List<Long> ids, @Param("isActive") Integer isActive);

    /**
     * 逻辑删除礼品码
     * @param id 礼品码ID
     * @return 删除结果
     */
    @Update("UPDATE gift_code SET is_deleted = 1, update_time = NOW() WHERE id = #{id}")
    int deleteById(@Param("id") Long id);

    /**
     * 批量逻辑删除礼品码
     * @param ids 礼品码ID列表
     * @return 删除结果
     */
    @Update("<script>" +
            "UPDATE gift_code SET is_deleted = 1, update_time = NOW() WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "  #{id}" +
            "</foreach>" +
            "</script>")
    int batchDeleteByIds(@Param("ids") List<Long> ids);

    /**
     * 检查礼品码是否存在
     * @param code 礼品码
     * @param excludeId 排除的ID（用于更新时检查）
     * @return 存在的数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM gift_code WHERE code = #{code} AND is_deleted = 0" +
            "<if test='excludeId != null'>" +
            "  AND id != #{excludeId}" +
            "</if>" +
            "</script>")
    int countByCode(@Param("code") String code, @Param("excludeId") Long excludeId);

    /**
     * 获取礼品码统计信息
     * @return 统计信息Map
     */
    @Select("SELECT " +
            "COUNT(*) as total_count, " +
            "COUNT(CASE WHEN is_active = 1 THEN 1 END) as active_count, " +
            "COUNT(CASE WHEN is_active = 0 THEN 1 END) as inactive_count, " +
            "COUNT(CASE WHEN type = 1 THEN 1 END) as points_card_count, " +
            "COUNT(CASE WHEN type = 2 THEN 1 END) as membership_card_count, " +
            "COUNT(CASE WHEN expire_time IS NOT NULL AND NOW() > expire_time THEN 1 END) as expired_count, " +
            "SUM(used_count) as total_usage_count " +
            "FROM gift_code WHERE is_deleted = 0")
    java.util.Map<String, Object> getStatistics();

    /**
     * 获取本月新增礼品码数量
     * @return 本月新增数量
     */
    @Select("SELECT COUNT(*) FROM gift_code WHERE is_deleted = 0 " +
            "AND DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')")
    Long getThisMonthNewCount();

    /**
     * 查询所有礼品码（调试用）
     * @return 所有礼品码列表
     */
    @Select("SELECT * FROM gift_code LIMIT 10")
    List<GiftCode> selectAllForDebug();

    /**
     * 分页查询已过期的礼品码列表
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 已过期礼品码列表
     */
    @Select("SELECT * FROM gift_code WHERE is_deleted = 0 " +
            "AND expire_time IS NOT NULL AND NOW() > expire_time " +
            "ORDER BY expire_time DESC " +
            "LIMIT #{offset}, #{limit}")
    List<GiftCode> selectExpiredPageList(@Param("offset") long offset, @Param("limit") Integer limit);

    /**
     * 统计已过期的礼品码总数
     * @return 总数
     */
    @Select("SELECT COUNT(*) FROM gift_code WHERE is_deleted = 0 " +
            "AND expire_time IS NOT NULL AND NOW() > expire_time")
    Long countExpiredGiftCodes();

    /**
     * 修复礼品码使用次数数据一致性
     * 根据gift_code_usage表的实际成功使用次数更新gift_code表的used_count字段
     * @return 更新的行数
     */
    @Update("UPDATE gift_code gc " +
            "SET used_count = IFNULL((SELECT COUNT(*) FROM gift_code_usage gcu " +
            "WHERE gcu.gift_code_id = gc.id AND gcu.status = 1 AND gcu.is_deleted = 0), 0), " +
            "update_time = NOW() " +
            "WHERE gc.is_deleted = 0")
    int fixUsageCountConsistency();
} 