package com.talentcard.server.mapper;

import java.util.List;
import java.util.Map;

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

@Mapper
public interface TicketPackageCodeMapper extends BaseMapper<TicketPackageCode> {

    @Select("""
            select *
            from ticket_package_code
            where id = #{id}
              and deleted = 0
            limit 1
            for update
            """)
    TicketPackageCode selectByIdForUpdate(@Param("id") Long id);

    @Select("""
            select *
            from ticket_package_code
            where exchange_code = #{exchangeCode}
              and deleted = 0
              and (#{packageId} is null or package_id = #{packageId})
            limit 1
            for update
            """)
    TicketPackageCode selectByExchangeCodeForUpdate(@Param("exchangeCode") String exchangeCode,
                                                    @Param("packageId") Long packageId);

    @Select("""
            select *
            from ticket_package_code
            where package_id = #{packageId}
              and status = 'AVAILABLE'
              and deleted = 0
            order by id asc
            limit 1
            for update
            """)
    TicketPackageCode selectAvailableCodeForUpdate(@Param("packageId") Long packageId);

    @Select("""
            select c.*
            from ticket_package_code c
            join ticket_package p on p.id = c.package_id and p.deleted = 0 and p.status = 1
            where c.deleted = 0
              and c.status = 'AVAILABLE'
              and (p.merchant_id = #{merchantId} or exists (
                  select 1 from ticket_package_merchant pm
                  where pm.package_id = p.id and pm.merchant_id = #{merchantId} and pm.deleted = 0
              ))
              and (p.valid_start is null or p.valid_start <= current_date)
              and (p.valid_end is null or p.valid_end >= current_date)
            order by c.id asc
            limit 1
            for update
            """)
    TicketPackageCode selectAvailableByMerchantForUpdate(@Param("merchantId") Long merchantId);

    @Update("""
            update ticket_package_code
            set status = 'LOCKED',
                locked_by_talent_id = #{talentId},
                locked_by_card_id = #{cardId},
                locked_at = now()
            where id = #{codeId}
              and status = 'AVAILABLE'
              and deleted = 0
            """)
    int lockForIssue(@Param("codeId") Long codeId,
                     @Param("talentId") Long talentId,
                     @Param("cardId") Long cardId);

    @Update("""
            update ticket_package_code
            set status = 'AVAILABLE',
                locked_by_talent_id = null,
                locked_by_card_id = null,
                locked_at = null
            where id = #{codeId}
              and status = 'LOCKED'
              and deleted = 0
            """)
    int releaseLock(@Param("codeId") Long codeId);

    @Select("""
            select c.id,
                   c.package_id as packageId,
                   p.package_no as packageNo,
                   p.package_name as packageName,
                   p.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.valid_start as validStart,
                   p.valid_end as validEnd,
                   p.status as packageStatus,
                   c.exchange_code as exchangeCode,
                   c.batch_no as batchNo,
                   c.status,
                   c.locked_by_talent_id as lockedByTalentId,
                   c.locked_by_card_id as lockedByCardId,
                   c.locked_at as lockedAt,
                   c.issued_claim_id as issuedClaimId,
                   c.issued_at as issuedAt,
                   c.used_at as usedAt,
                   c.remark,
                   c.created_at as createdAt,
                   c.updated_at as updatedAt,
                   cl.claim_no as claimNo,
                   cl.status as claimStatus,
                   cl.talent_id as claimTalentId,
                   cl.talent_name as claimTalentName,
                   cl.talent_mobile as claimTalentMobile,
                   cl.card_no as claimCardNo,
                   cl.card_level as claimCardLevel,
                   cl.idempotency_key as idempotencyKey,
                   cl.valid_end as claimValidEnd,
                   cl.used_at as claimUsedAt
            from ticket_package_code c
            join ticket_package p on p.id = c.package_id and p.deleted = 0
            left join merchant m on m.id = p.merchant_id and m.deleted = 0
            left join ticket_package_claim cl on cl.code_id = c.id and cl.deleted = 0
            where c.id = #{id}
              and c.deleted = 0
            limit 1
            """)
    Map<String, Object> selectAdminDetailById(@Param("id") Long id);

    @Select("""
            select c.id,
                   c.package_id as packageId,
                   p.package_no as packageNo,
                   p.package_name as packageName,
                   p.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.valid_start as validStart,
                   p.valid_end as validEnd,
                   p.status as packageStatus,
                   c.exchange_code as exchangeCode,
                   c.batch_no as batchNo,
                   c.status,
                   c.locked_by_talent_id as lockedByTalentId,
                   c.locked_by_card_id as lockedByCardId,
                   c.locked_at as lockedAt,
                   c.issued_claim_id as issuedClaimId,
                   c.issued_at as issuedAt,
                   c.used_at as usedAt,
                   c.remark,
                   c.created_at as createdAt,
                   c.updated_at as updatedAt,
                   cl.claim_no as claimNo,
                   cl.status as claimStatus,
                   cl.talent_id as claimTalentId,
                   cl.talent_name as claimTalentName,
                   cl.talent_mobile as claimTalentMobile,
                   cl.card_no as claimCardNo,
                   cl.card_level as claimCardLevel,
                   cl.idempotency_key as idempotencyKey,
                   cl.valid_end as claimValidEnd,
                   cl.used_at as claimUsedAt
            from ticket_package_code c
            join ticket_package p on p.id = c.package_id and p.deleted = 0
            left join merchant m on m.id = p.merchant_id and m.deleted = 0
            left join ticket_package_claim cl on cl.code_id = c.id and cl.deleted = 0
            where c.exchange_code = #{exchangeCode}
              and c.deleted = 0
              and (#{packageId} is null or c.package_id = #{packageId})
            limit 1
            """)
    Map<String, Object> selectAdminDetailByExchangeCode(@Param("exchangeCode") String exchangeCode,
                                                        @Param("packageId") Long packageId);

    @Select("""
            select c.id,
                   c.package_id as packageId,
                   p.package_no as packageNo,
                   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,
                   c.exchange_code as exchangeCode,
                   c.batch_no as batchNo,
                   c.status,
                   c.locked_by_talent_id as lockedByTalentId,
                   c.locked_by_card_id as lockedByCardId,
                   coalesce(cl.talent_name, u.name) as lockedTalentName,
                   coalesce(cl.talent_mobile, u.mobile) as lockedTalentMobile,
                   c.locked_at as lockedAt,
                   c.issued_claim_id as issuedClaimId,
                   c.issued_at as issuedAt,
                   c.used_at as usedAt,
                   c.remark,
                   c.created_at as createdAt,
                   c.updated_at as updatedAt
            from ticket_package_code c
            join ticket_package p on p.id = c.package_id and p.deleted = 0
            left join merchant m on m.id = p.merchant_id and m.deleted = 0
            left join ticket_package_claim cl on cl.code_id = c.id and cl.deleted = 0
            left join talent_user u on u.id = c.locked_by_talent_id and u.deleted = 0
            where c.deleted = 0
              and (#{packageId} is null or c.package_id = #{packageId})
              and (#{status} is null or #{status} = '' or c.status = #{status})
              and (#{batchNo} is null or #{batchNo} = '' or c.batch_no = #{batchNo})
              and (#{keyword} is null or #{keyword} = '' or cast(c.id as char) like concat('%', #{keyword}, '%') or c.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 (#{merchantKeyword} is null or #{merchantKeyword} = '' or m.merchant_name like concat('%', #{merchantKeyword}, '%') or exists (
                  select 1
                  from ticket_package_merchant pm
                  join merchant mm on mm.id = pm.merchant_id and mm.deleted = 0
                  where pm.package_id = p.id and pm.deleted = 0 and mm.merchant_name like concat('%', #{merchantKeyword}, '%')
              ))
            order by c.id desc
            limit #{size} offset #{offset}
            """)
    List<Map<String, Object>> selectAdminPage(@Param("packageId") Long packageId,
                                              @Param("status") String status,
                                              @Param("batchNo") String batchNo,
                                              @Param("keyword") String keyword,
                                              @Param("packageKeyword") String packageKeyword,
                                              @Param("merchantKeyword") String merchantKeyword,
                                              @Param("size") int size,
                                              @Param("offset") int offset);

    @Select("""
            select count(*)
            from ticket_package_code c
            join ticket_package p on p.id = c.package_id and p.deleted = 0
            left join merchant m on m.id = p.merchant_id and m.deleted = 0
            where c.deleted = 0
              and (#{packageId} is null or c.package_id = #{packageId})
              and (#{status} is null or #{status} = '' or c.status = #{status})
              and (#{batchNo} is null or #{batchNo} = '' or c.batch_no = #{batchNo})
              and (#{keyword} is null or #{keyword} = '' or cast(c.id as char) like concat('%', #{keyword}, '%') or c.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 (#{merchantKeyword} is null or #{merchantKeyword} = '' or m.merchant_name like concat('%', #{merchantKeyword}, '%') or exists (
                  select 1
                  from ticket_package_merchant pm
                  join merchant mm on mm.id = pm.merchant_id and mm.deleted = 0
                  where pm.package_id = p.id and pm.deleted = 0 and mm.merchant_name like concat('%', #{merchantKeyword}, '%')
              ))
            """)
    Long countAdminPage(@Param("packageId") Long packageId,
                        @Param("status") String status,
                        @Param("batchNo") String batchNo,
                        @Param("keyword") String keyword,
                        @Param("packageKeyword") String packageKeyword,
                        @Param("merchantKeyword") String merchantKeyword);
}