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

@Mapper
public interface TicketPackageMapper extends BaseMapper<TicketPackage> {

    @Select("""
            select p.id,
                   p.package_no as packageNo,
                   p.package_name as packageName,
                   p.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   p.cover_url as coverUrl,
                   p.talent_levels as talentLevels,
                   p.claim_price as claimPrice,
                   p.per_user_limit as perUserLimit,
                   p.valid_start as validStart,
                   p.valid_end as validEnd,
                   p.app_name as appName,
                   p.app_jump_url as appJumpUrl,
                   p.usage_text as usageText,
                   p.status,
                   p.sort_no as sortNo,
                   p.created_at as createdAt,
                   p.updated_at as updatedAt,
                   count(c.id) as totalCodes,
                   sum(case when c.status = 'AVAILABLE' and c.deleted = 0 then 1 else 0 end) as availableCodes,
                   sum(case when c.status = 'LOCKED' and c.deleted = 0 then 1 else 0 end) as lockedCodes,
                   sum(case when c.status = 'ISSUED' and c.deleted = 0 then 1 else 0 end) as issuedCodes,
                   sum(case when c.status = 'USED' and c.deleted = 0 then 1 else 0 end) as usedCodes,
                   sum(case when c.status = 'VOID' and c.deleted = 0 then 1 else 0 end) as voidCodes
            from ticket_package p
            left join merchant m on m.id = p.merchant_id and m.deleted = 0
            left join ticket_package_code c on c.package_id = p.id and c.deleted = 0
            where p.deleted = 0
              and (#{merchantId} is null or p.merchant_id = #{merchantId})
              and (#{status} is null or p.status = #{status})
              and (#{keyword} is null or #{keyword} = '' or p.package_name like concat('%', #{keyword}, '%') or p.package_no like concat('%', #{keyword}, '%'))
            group by p.id
            order by p.sort_no desc, p.id desc
            limit #{size} offset #{offset}
            """)
    List<Map<String, Object>> selectAdminPage(@Param("merchantId") Long merchantId,
                                              @Param("status") Integer status,
                                              @Param("keyword") String keyword,
                                              @Param("size") int size,
                                              @Param("offset") int offset);

    @Select("""
            select count(*)
            from ticket_package p
            where p.deleted = 0
              and (#{merchantId} is null or p.merchant_id = #{merchantId})
              and (#{status} is null or p.status = #{status})
              and (#{keyword} is null or #{keyword} = '' or p.package_name like concat('%', #{keyword}, '%') or p.package_no like concat('%', #{keyword}, '%'))
            """)
    Long countAdminPage(@Param("merchantId") Long merchantId,
                        @Param("status") Integer status,
                        @Param("keyword") String keyword);

    @Select("""
            select p.id,
                   p.package_no as packageNo,
                   p.package_name as packageName,
                   p.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   p.cover_url as coverUrl,
                   p.talent_levels as talentLevels,
                   p.claim_price as claimPrice,
                   p.per_user_limit as perUserLimit,
                   p.valid_start as validStart,
                   p.valid_end as validEnd,
                   p.app_name as appName,
                   p.app_jump_url as appJumpUrl,
                   p.usage_text as usageText,
                   p.status,
                   p.sort_no as sortNo,
                   p.created_at as createdAt,
                   p.updated_at as updatedAt,
                   count(c.id) as totalCodes,
                   sum(case when c.status = 'AVAILABLE' and c.deleted = 0 then 1 else 0 end) as availableCodes,
                   sum(case when c.status = 'LOCKED' and c.deleted = 0 then 1 else 0 end) as lockedCodes,
                   sum(case when c.status = 'ISSUED' and c.deleted = 0 then 1 else 0 end) as issuedCodes,
                   sum(case when c.status = 'USED' and c.deleted = 0 then 1 else 0 end) as usedCodes,
                   sum(case when c.status = 'VOID' and c.deleted = 0 then 1 else 0 end) as voidCodes
            from ticket_package p
            left join merchant m on m.id = p.merchant_id and m.deleted = 0
            left join ticket_package_code c on c.package_id = p.id and c.deleted = 0
            where p.id = #{id} and p.deleted = 0
            group by p.id
            limit 1
            """)
    Map<String, Object> selectAdminDetail(@Param("id") Long id);

    @Select("""
            select p.id,
                   p.package_no as packageNo,
                   p.package_name as packageName,
                   p.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   p.cover_url as coverUrl,
                   p.talent_levels as talentLevels,
                   p.claim_price as claimPrice,
                   p.per_user_limit as perUserLimit,
                   p.valid_start as validStart,
                   p.valid_end as validEnd,
                   p.app_name as appName,
                   p.app_jump_url as appJumpUrl,
                   p.usage_text as usageText,
                   p.status,
                   p.sort_no as sortNo,
                   sum(case when c.status = 'AVAILABLE' and c.deleted = 0 then 1 else 0 end) as availableCodes
            from ticket_package p
            left join merchant m on m.id = p.merchant_id and m.deleted = 0
            left join ticket_package_code c on c.package_id = p.id and c.deleted = 0
            where p.deleted = 0
              and p.status = 1
              and (p.valid_start is null or p.valid_start <= current_date)
              and (p.valid_end is null or p.valid_end >= current_date)
              and (#{merchantId} is null or p.merchant_id = #{merchantId})
            group by p.id
            order by p.sort_no desc, p.id desc
            """)
    List<Map<String, Object>> selectPortalPackages(@Param("merchantId") Long merchantId);

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