package com.talentcard.server.mapper;

import java.util.Map;

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

@Mapper
public interface ServiceApplicationMapper extends BaseMapper<ServiceApplication> {

    @Select("""
            select c.id as cardId,
                   c.talent_id as talentId,
                   c.card_no as cardNo,
                   c.card_level as talentLevel,
                   c.card_type as cardType,
                   u.name as talentName,
                   u.mobile as talentMobile,
                   u.id_card_no as talentIdCardNo
            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 id as merchantId,
                   merchant_name as merchantName,
                   merchant_type as merchantType,
                   contact_mobile as contactMobile,
                   address,
                   longitude,
                   latitude
            from merchant
            where id = #{merchantId} and deleted = 0 and status = 1
            limit 1
            """)
    Map<String, Object> selectMerchantSnapshot(@Param("merchantId") Long merchantId);

    @Select("""
            select m.id as merchantId,
                   m.merchant_name as merchantName,
                   m.merchant_type as merchantType,
                   m.contact_mobile as contactMobile,
                   m.address,
                   m.longitude,
                   m.latitude
            from merchant m
            join premium_topic_merchant mapping on mapping.merchant_id = m.id and mapping.deleted = 0
            where m.id = #{merchantId}
              and mapping.category_key = #{categoryKey}
              and m.deleted = 0
              and m.status = 1
            limit 1
            """)
    Map<String, Object> selectPremiumMerchantSnapshot(@Param("merchantId") Long merchantId, @Param("categoryKey") String categoryKey);

    @Select("""
            select p.id as packageId,
                   p.merchant_id as merchantId,
                   p.package_name as packageName,
                   p.stock,
                   p.status,
                   p.unavailable_reason as unavailableReason
            from medical_package p
            where p.id = #{packageId}
              and (#{merchantId} is null or p.merchant_id = #{merchantId})
              and p.deleted = 0
            limit 1
            """)
    Map<String, Object> selectMedicalPackage(@Param("packageId") Long packageId, @Param("merchantId") Long merchantId);

    @Select("""
            select id as productId,
                   merchant_id as merchantId,
                   product_name as productName,
                   stock,
                   status
            from merchant_product
            where id = #{productId}
              and (#{merchantId} is null or merchant_id = #{merchantId})
              and deleted = 0
            limit 1
            """)
    Map<String, Object> selectMerchantProduct(@Param("productId") Long productId, @Param("merchantId") Long merchantId);

    @Select("""
            select id as schoolId,
                   school_name as schoolName,
                   address,
                   latitude,
                   longitude,
                   contact_mobile as contactMobile,
                   enabled
            from children_entrustment_school
            where id = #{schoolId} and deleted = 0
            limit 1
            """)
    Map<String, Object> selectChildrenSchool(@Param("schoolId") Long schoolId);

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