package com.talentcard.server.mapper;

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

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

@Mapper
public interface TransportCarWashCredentialMapper extends BaseMapper<TransportCarWashCredential> {
    @Select("""
            select c.*, m.merchant_name as merchantName,
                   u.name as talentName, u.mobile as talentMobile,
                   tc.card_no as cardNo, tc.card_type as cardType,
                   pc.card_no as primaryCardNo
            from transport_car_wash_credential c
            join merchant m on m.id = c.merchant_id and m.deleted = 0
            join talent_user u on u.id = c.talent_id and u.deleted = 0
            join talent_card tc on tc.id = c.card_id and tc.deleted = 0
            join talent_card pc on pc.id = c.primary_card_id and pc.deleted = 0
            where c.id = #{id} and c.deleted = 0
            limit 1
            """)
    Map<String, Object> selectDetail(@Param("id") Long id);

    @Select("""
            <script>
            select c.*, m.merchant_name as merchantName,
                   u.name as talentName, u.mobile as talentMobile,
                   tc.card_no as cardNo, tc.card_type as cardType,
                   pc.card_no as primaryCardNo
            from transport_car_wash_credential c
            join merchant m on m.id = c.merchant_id and m.deleted = 0
            join talent_user u on u.id = c.talent_id and u.deleted = 0
            join talent_card tc on tc.id = c.card_id and tc.deleted = 0
            join talent_card pc on pc.id = c.primary_card_id and pc.deleted = 0
            where c.deleted = 0
              <if test="merchantId != null">and c.merchant_id = #{merchantId}</if>
              <if test="status != null">and c.status = #{status}</if>
              <if test="keyword != null and keyword != ''">and (c.credential_no like concat('%', #{keyword}, '%') or u.name like concat('%', #{keyword}, '%') or u.mobile like concat('%', #{keyword}, '%') or tc.card_no like concat('%', #{keyword}, '%'))</if>
              <if test="startTime != null">and c.submitted_at &gt;= #{startTime}</if>
              <if test="endTime != null">and c.submitted_at &lt; #{endTime}</if>
            order by c.submitted_at desc, c.id desc
            limit #{size} offset #{offset}
            </script>
            """)
    List<Map<String, Object>> selectPage(@Param("merchantId") Long merchantId,
                                         @Param("status") Integer status,
                                         @Param("keyword") String keyword,
                                         @Param("startTime") LocalDateTime startTime,
                                         @Param("endTime") LocalDateTime endTime,
                                         @Param("size") int size,
                                         @Param("offset") int offset);

    @Select("""
            <script>
            select count(*) from transport_car_wash_credential c
            left join talent_user u on u.id = c.talent_id and u.deleted = 0
            left join talent_card tc on tc.id = c.card_id and tc.deleted = 0
            where c.deleted = 0
              <if test="merchantId != null">and c.merchant_id = #{merchantId}</if>
              <if test="status != null">and c.status = #{status}</if>
              <if test="keyword != null and keyword != ''">and (c.credential_no like concat('%', #{keyword}, '%') or u.name like concat('%', #{keyword}, '%') or u.mobile like concat('%', #{keyword}, '%') or tc.card_no like concat('%', #{keyword}, '%'))</if>
              <if test="startTime != null">and c.submitted_at &gt;= #{startTime}</if>
              <if test="endTime != null">and c.submitted_at &lt; #{endTime}</if>
            </script>
            """)
    Long countPage(@Param("merchantId") Long merchantId,
                   @Param("status") Integer status,
                   @Param("keyword") String keyword,
                   @Param("startTime") LocalDateTime startTime,
                   @Param("endTime") LocalDateTime endTime);

    @Select("""
            select c.id as cardId, c.talent_id as talentId, c.card_no as cardNo,
                   c.card_type as cardType, c.card_level as cardLevel,
                   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
            where c.id = #{cardId} and c.talent_id = #{talentId} 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())
            limit 1
            """)
    Map<String, Object> selectActiveCard(@Param("talentId") Long talentId, @Param("cardId") Long cardId);

    @Select("""
            select a.talent_id as primaryTalentId, pc.id as primaryCardId,
                   pc.card_no as primaryCardNo, pc.card_level as cardLevel
            from family_card_application a
            join talent_card pc on pc.talent_id = a.talent_id and pc.card_type = 'PERSONAL'
              and pc.deleted = 0 and pc.status = 1
            where a.family_card_id = #{familyCardId} and a.deleted = 0
            order by a.id desc, pc.id desc limit 1
            """)
    Map<String, Object> selectPrimaryCard(@Param("familyCardId") Long familyCardId);

    @Select("select count(*) from merchant where id = #{merchantId} and merchant_type_code = 'carWash' and deleted = 0 and status = 1")
    Long countActiveMerchant(@Param("merchantId") Long merchantId);

    @Select("""
            select coalesce(sum(verify_amount), 0) from verify_record
            where card_id = #{primaryCardId} and verify_type = 'CAR_WASH'
              and status = 1 and verify_time &gt;= #{windowStart}
            """)
    Long countUsedQuota(@Param("primaryCardId") Long primaryCardId,
                        @Param("windowStart") LocalDateTime windowStart);

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