package com.talentcard.server.mapper;

import java.time.LocalDate;
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,
                     case
                         when upper(coalesce(c.card_level, '')) like '%EXCELLENT%' or coalesce(c.card_level, '') like '%卓越%' then 0
                         when upper(coalesce(c.card_level, '')) like '%OUTSTANDING%' or coalesce(c.card_level, '') like '%杰出%' then 1
                         when upper(coalesce(c.card_level, '')) like '%ELITE%' or coalesce(c.card_level, '') like '%菁英%' or coalesce(c.card_level, '') like '%精英%' then 2
                         else 9
                     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,
                     case
                         when upper(coalesce(c.card_level, '')) like '%EXCELLENT%' or coalesce(c.card_level, '') like '%卓越%' then 0
                         when upper(coalesce(c.card_level, '')) like '%OUTSTANDING%' or coalesce(c.card_level, '') like '%杰出%' then 1
                         when upper(coalesce(c.card_level, '')) like '%ELITE%' or coalesce(c.card_level, '') like '%菁英%' or coalesce(c.card_level, '') like '%精英%' then 2
                         else 9
                     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 case
                         when upper(coalesce(c.card_level, '')) like '%EXCELLENT%' or coalesce(c.card_level, '') like '%卓越%' then 0
                         when upper(coalesce(c.card_level, '')) like '%OUTSTANDING%' or coalesce(c.card_level, '') like '%杰出%' then 1
                         when upper(coalesce(c.card_level, '')) like '%ELITE%' or coalesce(c.card_level, '') like '%菁英%' or coalesce(c.card_level, '') like '%精英%' then 2
                         else 9
                     end,
                     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 case
                         when upper(coalesce(c.card_level, '')) like '%EXCELLENT%' or coalesce(c.card_level, '') like '%卓越%' then 0
                         when upper(coalesce(c.card_level, '')) like '%OUTSTANDING%' or coalesce(c.card_level, '') like '%杰出%' then 1
                         when upper(coalesce(c.card_level, '')) like '%ELITE%' or coalesce(c.card_level, '') like '%菁英%' or coalesce(c.card_level, '') like '%精英%' then 2
                         else 9
                     end,
                     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 *
            from ticket_package_claim
            where exchange_code = #{exchangeCode}
              and deleted = 0
            limit 1
            for update
            """)
    TicketPackageClaim selectByExchangeCodeForUpdate(@Param("exchangeCode") String exchangeCode);

    @Select("""
            select c.card_level as cardLevel
            from talent_card c
            where c.id = #{cardId}
              and c.deleted = 0
            limit 1
            """)
    Map<String, Object> selectCardLevelSnapshot(@Param("cardId") Long cardId);

    @Select("""
            select cl.id as claimId,
                   cl.claim_no as claimNo,
                   cl.package_id as packageId,
                   p.package_name as packageName,
                   p.total_quantity as totalQuantity,
                   cl.merchant_id as merchantId,
                   coalesce(m.merchant_name, (
                       select m0.merchant_name
                       from ticket_package_merchant pm0
                       join merchant m0 on m0.id = pm0.merchant_id and m0.deleted = 0
                       where pm0.package_id = p.id and pm0.deleted = 0
                       order by pm0.sort_no asc, pm0.id asc
                       limit 1
                   )) as merchantName,
                   (
                       select group_concat(m1.merchant_name order by pm1.sort_no asc, pm1.id asc separator '、')
                       from ticket_package_merchant pm1
                       join merchant m1 on m1.id = pm1.merchant_id and m1.deleted = 0
                       where pm1.package_id = p.id and pm1.deleted = 0
                   ) as merchantNames,
                   p.app_name as appName,
                   p.usage_text as usageText,
                   (
                       select count(*)
                       from ticket_package_code pc
                       where pc.package_id = p.id and pc.deleted = 0
                   ) as totalCodes,
                   (
                       select count(*)
                       from ticket_package_code pc
                       where pc.package_id = p.id and pc.status = 'AVAILABLE' and pc.deleted = 0
                   ) as availableCodes,
                   (
                       select count(*)
                       from ticket_package_code pc
                       where pc.package_id = p.id and pc.status = 'LOCKED' and pc.deleted = 0
                   ) as lockedCodes,
                   (
                       select count(*)
                       from ticket_package_code pc
                       where pc.package_id = p.id and pc.status = 'ISSUED' and pc.deleted = 0
                   ) as issuedCodes,
                   (
                       select count(*)
                       from ticket_package_code pc
                       where pc.package_id = p.id and pc.status = 'USED' and pc.deleted = 0
                   ) as usedCodes,
                   (
                       select count(*)
                       from ticket_package_code pc
                       where pc.package_id = p.id and pc.status = 'VOID' and pc.deleted = 0
                   ) as voidCodes,
                   (
                       select count(distinct cl2.id)
                       from ticket_package_claim cl2
                       where cl2.package_id = p.id
                         and cl2.deleted = 0
                         and cl2.status in ('ISSUED', 'USED')
                   ) as claimedCount,
                   cl.talent_id as talentId,
                   cl.card_id as cardId,
                   cl.primary_card_id as primaryCardId,
                   cl.card_level as cardLevel,
                   cl.exchange_code as exchangeCode,
                   cl.status,
                   cl.valid_end as validEnd,
                   cl.used_at as usedAt,
                   cl.created_at as createdAt
            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.id = #{claimId}
              and cl.talent_id = #{talentId}
              and cl.deleted = 0
            limit 1
            """)
    Map<String, Object> selectPortalDetail(@Param("claimId") Long claimId, @Param("talentId") Long talentId);

    @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 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 countPackageClaimedInWindow(@Param("packageId") Long packageId,
                                     @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,
                   coalesce((
                       select group_concat(m1.merchant_name order by pm1.sort_no asc, pm1.id asc separator '、')
                       from ticket_package_merchant pm1
                       join merchant m1 on m1.id = pm1.merchant_id and m1.deleted = 0
                       where pm1.package_id = p.id and pm1.deleted = 0
                   ), m.merchant_name) as merchantNames,
                   coalesce(m.merchant_name, (
                       select m0.merchant_name
                       from ticket_package_merchant pm0
                       join merchant m0 on m0.id = pm0.merchant_id and m0.deleted = 0
                       where pm0.package_id = p.id and pm0.deleted = 0
                       order by pm0.sort_no asc, pm0.id asc
                       limit 1
                   )) 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}, '%'))
              and (#{packageKeyword} is null or #{packageKeyword} = '' or p.package_name like concat('%', #{packageKeyword}, '%') or p.package_no like concat('%', #{packageKeyword}, '%'))
              and (#{talentKeyword} is null or #{talentKeyword} = '' or cl.talent_name like concat('%', #{talentKeyword}, '%') or cl.talent_mobile like concat('%', #{talentKeyword}, '%') or cl.card_no like concat('%', #{talentKeyword}, '%'))
              and (#{cardLevel} is null or #{cardLevel} = '' or cl.card_level = #{cardLevel} or cl.card_level like concat('%', #{cardLevel}, '%'))
              and (#{claimedStart} is null or cl.created_at >= #{claimedStart})
              and (#{claimedEnd} is null or cl.created_at < date_add(cast(#{claimedEnd} as date), interval 1 day))
            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("packageKeyword") String packageKeyword,
                                              @Param("talentKeyword") String talentKeyword,
                                              @Param("cardLevel") String cardLevel,
                                              @Param("claimedStart") LocalDate claimedStart,
                                              @Param("claimedEnd") LocalDate claimedEnd,
                                              @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}, '%'))
              and (#{packageKeyword} is null or #{packageKeyword} = '' or p.package_name like concat('%', #{packageKeyword}, '%') or p.package_no like concat('%', #{packageKeyword}, '%'))
              and (#{talentKeyword} is null or #{talentKeyword} = '' or cl.talent_name like concat('%', #{talentKeyword}, '%') or cl.talent_mobile like concat('%', #{talentKeyword}, '%') or cl.card_no like concat('%', #{talentKeyword}, '%'))
              and (#{cardLevel} is null or #{cardLevel} = '' or cl.card_level = #{cardLevel} or cl.card_level like concat('%', #{cardLevel}, '%'))
              and (#{claimedStart} is null or cl.created_at >= #{claimedStart})
              and (#{claimedEnd} is null or cl.created_at < date_add(cast(#{claimedEnd} as date), interval 1 day))
            """)
    Long countAdminPage(@Param("packageId") Long packageId,
                        @Param("talentId") Long talentId,
                        @Param("status") String status,
                        @Param("keyword") String keyword,
                        @Param("packageKeyword") String packageKeyword,
                        @Param("talentKeyword") String talentKeyword,
                        @Param("cardLevel") String cardLevel,
                        @Param("claimedStart") LocalDate claimedStart,
                        @Param("claimedEnd") LocalDate claimedEnd);

    /**
     * 后台「核销记录」：只列已核销（status = USED）的领取记录。
     *
     * <p>核销商家就是领取记录上的 merchant_id——核销接口要求核销方必须等于该商户，所以不需要再关联
     * benefit_verify_record。
     */
    @Select("""
            select cl.id,
                   cl.claim_no as claimNo,
                   cl.package_id as packageId,
                   p.package_name as packageName,
                   coalesce((
                       select group_concat(m1.merchant_name order by pm1.sort_no asc, pm1.id asc separator '、')
                       from ticket_package_merchant pm1
                       join merchant m1 on m1.id = pm1.merchant_id and m1.deleted = 0
                       where pm1.package_id = p.id and pm1.deleted = 0
                   ), m.merchant_name) as merchantNames,
                   m.merchant_name as verifyMerchantName,
                   cl.talent_id as talentId,
                   cl.card_id as cardId,
                   cl.card_no as cardNo,
                   cl.card_level as cardLevel,
                   cl.talent_name as talentName,
                   cl.talent_mobile as talentMobile,
                   cl.status,
                   cl.created_at as createdAt,
                   cl.used_at as usedAt
            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 cl.status = 'USED'
              and (#{packageId} is null or cl.package_id = #{packageId})
              and (#{talentKeyword} is null or #{talentKeyword} = '' or cl.talent_name like concat('%', #{talentKeyword}, '%') or cl.talent_mobile like concat('%', #{talentKeyword}, '%') or cl.card_no like concat('%', #{talentKeyword}, '%'))
              and (#{cardLevel} is null or #{cardLevel} = '' or cl.card_level = #{cardLevel} or cl.card_level like concat('%', #{cardLevel}, '%'))
              and (#{claimedStart} is null or cl.created_at >= #{claimedStart})
              and (#{claimedEnd} is null or cl.created_at < date_add(cast(#{claimedEnd} as date), interval 1 day))
              and (#{verifiedStart} is null or cl.used_at >= #{verifiedStart})
              and (#{verifiedEnd} is null or cl.used_at < date_add(cast(#{verifiedEnd} as date), interval 1 day))
              and (#{merchantKeyword} is null or #{merchantKeyword} = '' or m.merchant_name like concat('%', #{merchantKeyword}, '%'))
            order by cl.used_at desc, cl.id desc
            limit #{size} offset #{offset}
            """)
    List<Map<String, Object>> selectAdminVerificationPage(@Param("packageId") Long packageId,
                                                          @Param("talentKeyword") String talentKeyword,
                                                          @Param("cardLevel") String cardLevel,
                                                          @Param("claimedStart") LocalDate claimedStart,
                                                          @Param("claimedEnd") LocalDate claimedEnd,
                                                          @Param("verifiedStart") LocalDate verifiedStart,
                                                          @Param("verifiedEnd") LocalDate verifiedEnd,
                                                          @Param("merchantKeyword") String merchantKeyword,
                                                          @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
            left join merchant m on m.id = cl.merchant_id and m.deleted = 0
            where cl.deleted = 0
              and cl.status = 'USED'
              and (#{packageId} is null or cl.package_id = #{packageId})
              and (#{talentKeyword} is null or #{talentKeyword} = '' or cl.talent_name like concat('%', #{talentKeyword}, '%') or cl.talent_mobile like concat('%', #{talentKeyword}, '%') or cl.card_no like concat('%', #{talentKeyword}, '%'))
              and (#{cardLevel} is null or #{cardLevel} = '' or cl.card_level = #{cardLevel} or cl.card_level like concat('%', #{cardLevel}, '%'))
              and (#{claimedStart} is null or cl.created_at >= #{claimedStart})
              and (#{claimedEnd} is null or cl.created_at < date_add(cast(#{claimedEnd} as date), interval 1 day))
              and (#{verifiedStart} is null or cl.used_at >= #{verifiedStart})
              and (#{verifiedEnd} is null or cl.used_at < date_add(cast(#{verifiedEnd} as date), interval 1 day))
              and (#{merchantKeyword} is null or #{merchantKeyword} = '' or m.merchant_name like concat('%', #{merchantKeyword}, '%'))
            """)
    Long countAdminVerificationPage(@Param("packageId") Long packageId,
                                    @Param("talentKeyword") String talentKeyword,
                                    @Param("cardLevel") String cardLevel,
                                    @Param("claimedStart") LocalDate claimedStart,
                                    @Param("claimedEnd") LocalDate claimedEnd,
                                    @Param("verifiedStart") LocalDate verifiedStart,
                                    @Param("verifiedEnd") LocalDate verifiedEnd,
                                    @Param("merchantKeyword") String merchantKeyword);
}