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.MerchantDisplayPolicyMerchant;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface MerchantDisplayPolicyMerchantMapper extends BaseMapper<MerchantDisplayPolicyMerchant> {

    @Select("""
            <script>
            select m.id as merchantId,
                   m.merchant_name as merchantName,
                   m.merchant_type as merchantType,
                   m.contact_name as contactName,
                   m.contact_mobile as contactMobile,
                   m.logo_url as logoUrl,
                   m.cover_url as coverUrl,
                   m.address,
                   m.business_status as businessStatus,
                   m.longitude,
                   m.latitude,
                   m.usage_instructions as handlingInstructions,
                   m.rights_text as rightsText,
                   m.service_label as serviceLabel,
                   m.is_recommend as recommend,
                   m.sort_no as sortNo,
                   m.status,
                   m.created_at as createdAt,
                   m.updated_at as updatedAt,
                   (select count(*) from merchant_card_benefit b where b.merchant_id = m.id) as policyCount
            from merchant m
            where m.deleted = 0
              <if test="merchantId != null">and m.id = #{merchantId}</if>
              <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
              <if test="status != null">and m.status = #{status}</if>
              <if test="keyword != null and keyword != ''">
                and (m.merchant_name like concat('%', #{keyword}, '%')
                     or m.contact_mobile like concat('%', #{keyword}, '%')
                     or m.address like concat('%', #{keyword}, '%'))
              </if>
              <if test="cardLevelAliases != null and cardLevelAliases.size() > 0">
                and exists (
                    select 1
                    from merchant_card_benefit b
                    where b.merchant_id = m.id
                      and b.card_level in
                      <foreach collection="cardLevelAliases" item="level" open="(" separator="," close=")">
                        #{level}
                      </foreach>
                )
              </if>
            order by m.sort_no desc, m.id desc
            limit #{size} offset #{offset}
            </script>
            """)
    List<Map<String, Object>> selectAdminPage(@Param("merchantId") Long merchantId,
                                              @Param("keyword") String keyword,
                                              @Param("merchantType") String merchantType,
                                              @Param("cardLevelAliases") List<String> cardLevelAliases,
                                              @Param("status") Integer status,
                                              @Param("size") int size,
                                              @Param("offset") int offset);

    @Select("""
            <script>
            select count(*)
            from merchant m
            where m.deleted = 0
              <if test="merchantId != null">and m.id = #{merchantId}</if>
              <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
              <if test="status != null">and m.status = #{status}</if>
              <if test="keyword != null and keyword != ''">
                and (m.merchant_name like concat('%', #{keyword}, '%')
                     or m.contact_mobile like concat('%', #{keyword}, '%')
                     or m.address like concat('%', #{keyword}, '%'))
              </if>
              <if test="cardLevelAliases != null and cardLevelAliases.size() > 0">
                and exists (
                    select 1
                    from merchant_card_benefit b
                    where b.merchant_id = m.id
                      and b.card_level in
                      <foreach collection="cardLevelAliases" item="level" open="(" separator="," close=")">
                        #{level}
                      </foreach>
                )
              </if>
            </script>
            """)
    Long countAdminPage(@Param("merchantId") Long merchantId,
                        @Param("keyword") String keyword,
                        @Param("merchantType") String merchantType,
                        @Param("cardLevelAliases") List<String> cardLevelAliases,
                        @Param("status") Integer status);

    @Select("""
            select m.id as merchantId,
                   m.merchant_name as merchantName,
                   m.merchant_type as merchantType,
                   m.contact_name as contactName,
                   m.contact_mobile as contactMobile,
                   m.logo_url as logoUrl,
                   m.cover_url as coverUrl,
                   m.address,
                   m.business_status as businessStatus,
                   m.longitude,
                   m.latitude,
                   m.usage_instructions as handlingInstructions,
                   m.rights_text as rightsText,
                   m.service_label as serviceLabel,
                   m.is_recommend as recommend,
                   m.sort_no as sortNo,
                   m.status,
                   m.created_at as createdAt,
                   m.updated_at as updatedAt
            from merchant m
            where m.deleted = 0 and m.id = #{merchantId}
            limit 1
            """)
    Map<String, Object> selectAdminDetail(@Param("merchantId") Long merchantId);

    @Select("""
            <script>
            select m.id as merchantId,
                   m.merchant_name as merchantName,
                   m.merchant_type as merchantType,
                   m.contact_name as contactName,
                   m.contact_mobile as contactMobile,
                   m.logo_url as logoUrl,
                   m.cover_url as coverUrl,
                   m.address,
                   m.longitude,
                   m.latitude,
                   m.usage_instructions as handlingInstructions,
                   m.rights_text as rightsText,
                   m.service_label as serviceLabel,
                   m.sort_no as sortNo,
                   m.status,
                   b.id as benefitId,
                   b.card_level as cardLevel,
                   b.card_name as cardName,
                   b.benefit_text as policyText,
                   b.theme,
                   b.sort_no as benefitSortNo,
                   b.updated_at as policyUpdatedAt
            from merchant m
            join merchant_card_benefit b on b.id = (
                select bb.id
                from merchant_card_benefit bb
                where bb.merchant_id = m.id
                  and bb.status = 1
                  and bb.card_level in
                  <foreach collection="cardLevelAliases" item="level" open="(" separator="," close=")">
                    #{level}
                  </foreach>
                order by bb.sort_no desc, bb.id desc
                limit 1
            )
            where m.deleted = 0
              and m.status = 1
              <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
              <if test="keyword != null and keyword != ''">
                and (m.merchant_name like concat('%', #{keyword}, '%')
                     or m.contact_mobile like concat('%', #{keyword}, '%')
                     or m.address like concat('%', #{keyword}, '%'))
              </if>
            order by b.sort_no desc, m.sort_no desc, m.id desc
            limit #{size} offset #{offset}
            </script>
            """)
    List<Map<String, Object>> selectPortalPage(@Param("cardLevelAliases") List<String> cardLevelAliases,
                                               @Param("merchantType") String merchantType,
                                               @Param("keyword") String keyword,
                                               @Param("size") int size,
                                               @Param("offset") int offset);

    @Select("""
            <script>
            select count(*)
            from merchant m
            where m.deleted = 0
              and m.status = 1
              <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
              <if test="keyword != null and keyword != ''">
                and (m.merchant_name like concat('%', #{keyword}, '%')
                     or m.contact_mobile like concat('%', #{keyword}, '%')
                     or m.address like concat('%', #{keyword}, '%'))
              </if>
              and exists (
                select 1
                from merchant_card_benefit b
                where b.merchant_id = m.id
                  and b.status = 1
                  and b.card_level in
                  <foreach collection="cardLevelAliases" item="level" open="(" separator="," close=")">
                    #{level}
                  </foreach>
              )
            </script>
            """)
    Long countPortalPage(@Param("cardLevelAliases") List<String> cardLevelAliases,
                         @Param("merchantType") String merchantType,
                         @Param("keyword") String keyword);

    @Select("""
            <script>
            select m.id as merchantId,
                   m.merchant_name as merchantName,
                   m.merchant_type as merchantType,
                   m.contact_name as contactName,
                   m.contact_mobile as contactMobile,
                   m.logo_url as logoUrl,
                   m.cover_url as coverUrl,
                   m.address,
                   m.longitude,
                   m.latitude,
                   m.usage_instructions as handlingInstructions,
                   m.rights_text as rightsText,
                   m.service_label as serviceLabel,
                   m.sort_no as sortNo,
                   m.status,
                   b.id as benefitId,
                   b.card_level as cardLevel,
                   b.card_name as cardName,
                   b.benefit_text as policyText,
                   b.theme,
                   b.sort_no as benefitSortNo,
                   b.updated_at as policyUpdatedAt
            from merchant m
            join merchant_card_benefit b on b.id = (
                select bb.id
                from merchant_card_benefit bb
                where bb.merchant_id = m.id
                  and bb.status = 1
                  and bb.card_level in
                  <foreach collection="cardLevelAliases" item="level" open="(" separator="," close=")">
                    #{level}
                  </foreach>
                order by bb.sort_no desc, bb.id desc
                limit 1
            )
            where m.deleted = 0
              and m.status = 1
              and m.id = #{merchantId}
            limit 1
            </script>
            """)
    Map<String, Object> selectPortalDetail(@Param("merchantId") Long merchantId,
                                           @Param("cardLevelAliases") List<String> cardLevelAliases);

    @Select("""
            <script>
            select e.id,
                   e.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   m.merchant_type as merchantType,
                   e.talent_id as talentId,
                   e.card_id as cardId,
                   e.primary_card_id as primaryCardId,
                   e.card_level as cardLevel,
                   e.event_type as eventType,
                   e.remark,
                   e.created_at as createdAt
            from merchant_display_policy_event e
            left join merchant m on m.id = e.merchant_id and m.deleted = 0
            where e.deleted = 0
              <if test="merchantId != null">and e.merchant_id = #{merchantId}</if>
              <if test="eventType != null and eventType != ''">and e.event_type = #{eventType}</if>
              <if test="cardLevelAliases != null and cardLevelAliases.size() > 0">
                and e.card_level in
                <foreach collection="cardLevelAliases" item="level" open="(" separator="," close=")">
                  #{level}
                </foreach>
              </if>
              <if test="keyword != null and keyword != ''">
                and (m.merchant_name like concat('%', #{keyword}, '%') or e.remark like concat('%', #{keyword}, '%'))
              </if>
            order by e.created_at desc, e.id desc
            limit #{size} offset #{offset}
            </script>
            """)
    List<Map<String, Object>> selectEventPage(@Param("merchantId") Long merchantId,
                                              @Param("eventType") String eventType,
                                              @Param("cardLevelAliases") List<String> cardLevelAliases,
                                              @Param("keyword") String keyword,
                                              @Param("size") int size,
                                              @Param("offset") int offset);

    @Select("""
            <script>
            select count(*)
            from merchant_display_policy_event e
            left join merchant m on m.id = e.merchant_id and m.deleted = 0
            where e.deleted = 0
              <if test="merchantId != null">and e.merchant_id = #{merchantId}</if>
              <if test="eventType != null and eventType != ''">and e.event_type = #{eventType}</if>
              <if test="cardLevelAliases != null and cardLevelAliases.size() > 0">
                and e.card_level in
                <foreach collection="cardLevelAliases" item="level" open="(" separator="," close=")">
                  #{level}
                </foreach>
              </if>
              <if test="keyword != null and keyword != ''">
                and (m.merchant_name like concat('%', #{keyword}, '%') or e.remark like concat('%', #{keyword}, '%'))
              </if>
            </script>
            """)
    Long countEventPage(@Param("merchantId") Long merchantId,
                        @Param("eventType") String eventType,
                        @Param("cardLevelAliases") List<String> cardLevelAliases,
                        @Param("keyword") String keyword);
}