package com.talentcard.server.mapper;

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

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.talentcard.server.entity.TicketPackageClaim;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface TicketPackageClaimMapper extends BaseMapper<TicketPackageClaim> {

    @Select("""
            select c.id as cardId,
                   c.talent_id as talentId,
                   c.card_no as cardNo,
                   c.card_level as cardLevel,
                   c.card_type as cardType,
                   u.name as talentName,
                   u.mobile as talentMobile
            from talent_card c
            join talent_user u on u.id = c.talent_id and u.deleted = 0 and u.status = 1
            where c.deleted = 0
              and c.status = 1
              and (c.valid_start is null or c.valid_start <= current_date)
              and (c.valid_end is null or c.valid_end >= current_date)
              and (c.expire_time is null or c.expire_time > now())
              and ((#{cardId} is not null and c.id = #{cardId}) or (#{cardId} is null and c.talent_id = #{talentId}))
              and (#{talentId} is null or c.talent_id = #{talentId})
            order by case when c.card_type = 'PERSONAL' then 0 else 1 end, c.id desc
            limit 1
            for update
            """)
    Map<String, Object> selectActiveCardSnapshotForUpdate(@Param("talentId") Long talentId, @Param("cardId") Long cardId);

    @Select("""
            select c.id as cardId,
                   c.talent_id as talentId,
                   c.card_no as cardNo,
                   c.card_level as cardLevel,
                   c.card_type as cardType,
                   u.name as talentName,
                   u.mobile as talentMobile
            from talent_card c
            join talent_user u on u.id = c.talent_id and u.deleted = 0 and u.status = 1
            where c.deleted = 0
              and c.status = 1
              and (c.valid_start is null or c.valid_start <= current_date)
              and (c.valid_end is null or c.valid_end >= current_date)
              and (c.expire_time is null or c.expire_time > now())
              and ((#{cardId} is not null and c.id = #{cardId}) or (#{cardId} is null and c.talent_id = #{talentId}))
              and (#{talentId} is null or c.talent_id = #{talentId})
            order by case when c.card_type = 'PERSONAL' then 0 else 1 end, c.id desc
            limit 1
            """)
    Map<String, Object> selectActiveCardSnapshot(@Param("talentId") Long talentId, @Param("cardId") Long cardId);
    @Select("""
            select c.id as cardId,
                   c.talent_id as talentId,
                   c.card_no as cardNo,
                   c.card_level as cardLevel,
                   c.card_type as cardType,
                   u.name as talentName,
                   u.mobile as talentMobile
            from family_card_application f
            join talent_card c on c.talent_id = f.talent_id and c.card_type = 'PERSONAL'
            join talent_user u on u.id = c.talent_id and u.deleted = 0 and u.status = 1
            where f.deleted = 0
              and f.status = '已通过'
              and f.family_card_id = #{familyCardId}
              and c.deleted = 0
              and c.status = 1
              and (c.valid_start is null or c.valid_start <= current_date)
              and (c.valid_end is null or c.valid_end >= current_date)
              and (c.expire_time is null or c.expire_time > now())
            order by c.id desc
            limit 1
            for update
            """)
    Map<String, Object> selectPrimaryCardByFamilyCardForUpdate(@Param("familyCardId") Long familyCardId);

    @Select("""
            select c.id as cardId,
                   c.talent_id as talentId,
                   c.card_no as cardNo,
                   c.card_level as cardLevel,
                   c.card_type as cardType,
                   u.name as talentName,
                   u.mobile as talentMobile
            from family_card_application f
            join talent_card c on c.talent_id = f.talent_id and c.card_type = 'PERSONAL'
            join talent_user u on u.id = c.talent_id and u.deleted = 0 and u.status = 1
            where f.deleted = 0
              and f.status = '已通过'
              and f.family_card_id = #{familyCardId}
              and c.deleted = 0
              and c.status = 1
              and (c.valid_start is null or c.valid_start <= current_date)
              and (c.valid_end is null or c.valid_end >= current_date)
              and (c.expire_time is null or c.expire_time > now())
            order by c.id desc
            limit 1
            """)
    Map<String, Object> selectPrimaryCardByFamilyCard(@Param("familyCardId") Long familyCardId);
    @Select("""
            select *
            from ticket_package_claim
            where idempotency_key = #{idempotencyKey} and deleted = 0
            limit 1
            """)
    TicketPackageClaim selectByIdempotencyKey(@Param("idempotencyKey") String idempotencyKey);
    @Select("""
            select *
            from ticket_package_claim
            where code_id = #{codeId}
              and deleted = 0
            limit 1
            """)
    TicketPackageClaim selectByCodeId(@Param("codeId") Long codeId);

    @Select("""
            select count(*)
            from ticket_package_claim
            where deleted = 0
              and package_id = #{packageId}
              and primary_card_id = #{primaryCardId}
              and status in ('ISSUED', 'USED')
              and created_at >= #{windowStart}
            """)
    Long countClaimedInWindow(@Param("packageId") Long packageId,
                              @Param("primaryCardId") Long primaryCardId,
                              @Param("windowStart") LocalDateTime windowStart);

    @Select("""
            select count(*)
            from ticket_package_claim
            where deleted = 0
              and primary_card_id = #{primaryCardId}
              and status in ('ISSUED', 'USED')
              and created_at >= #{windowStart}
            """)
    Long countTotalClaimedInWindow(@Param("primaryCardId") Long primaryCardId,
                                   @Param("windowStart") LocalDateTime windowStart);

    @Select("""
            select cl.id,
                   cl.claim_no as claimNo,
                   cl.package_id as packageId,
                   p.package_name as packageName,
                   cl.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   cl.talent_id as talentId,
                   cl.card_id as cardId,
                   cl.primary_card_id as primaryCardId,
                   cl.card_no as cardNo,
                   cl.card_level as cardLevel,
                   cl.talent_name as talentName,
                   cl.talent_mobile as talentMobile,
                   cl.exchange_code as exchangeCode,
                   cl.idempotency_key as idempotencyKey,
                   cl.status,
                   cl.valid_end as validEnd,
                   cl.used_at as usedAt,
                   cl.created_at as createdAt,
                   cl.updated_at as updatedAt
            from ticket_package_claim cl
            join ticket_package p on p.id = cl.package_id and p.deleted = 0
            left join merchant m on m.id = cl.merchant_id and m.deleted = 0
            where cl.deleted = 0
              and (#{packageId} is null or cl.package_id = #{packageId})
              and (#{talentId} is null or cl.talent_id = #{talentId})
              and (#{status} is null or #{status} = '' or cl.status = #{status})
              and (#{keyword} is null or #{keyword} = '' or cl.claim_no like concat('%', #{keyword}, '%') or cl.exchange_code like concat('%', #{keyword}, '%')
                   or cl.talent_name like concat('%', #{keyword}, '%') or cl.talent_mobile like concat('%', #{keyword}, '%'))
            order by cl.created_at desc, cl.id desc
            limit #{size} offset #{offset}
            """)
    List<Map<String, Object>> selectAdminPage(@Param("packageId") Long packageId,
                                              @Param("talentId") Long talentId,
                                              @Param("status") String status,
                                              @Param("keyword") String keyword,
                                              @Param("size") int size,
                                              @Param("offset") int offset);

    @Select("""
            select count(*)
            from ticket_package_claim cl
            join ticket_package p on p.id = cl.package_id and p.deleted = 0
            where cl.deleted = 0
              and (#{packageId} is null or cl.package_id = #{packageId})
              and (#{talentId} is null or cl.talent_id = #{talentId})
              and (#{status} is null or #{status} = '' or cl.status = #{status})
              and (#{keyword} is null or #{keyword} = '' or cl.claim_no like concat('%', #{keyword}, '%') or cl.exchange_code like concat('%', #{keyword}, '%')
                   or cl.talent_name like concat('%', #{keyword}, '%') or cl.talent_mobile like concat('%', #{keyword}, '%'))
            """)
    Long countAdminPage(@Param("packageId") Long packageId,
                        @Param("talentId") Long talentId,
                        @Param("status") String status,
                        @Param("keyword") String keyword);
}