package com.talentcard.server.mapper;

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

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

@Mapper
public interface TransportParkingPlateRegistrationMapper extends BaseMapper<TransportParkingPlateRegistration> {

    @Select("""
            select c.id as cardId,
                   c.talent_id as talentId,
                   c.card_no as cardNo,
                   c.card_level as cardLevel,
                   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 m.id as merchantId,
                   m.merchant_name as merchantName,
                   m.contact_mobile as contactMobile,
                   m.address,
                   m.cover_url as coverUrl,
                   m.status as merchantStatus,
                   b.id as benefitId,
                   b.action_mode as actionMode,
                   b.status as benefitStatus
            from merchant m
            left join transport_parking_benefit b on b.merchant_id = m.id and b.deleted = 0
            where m.id = #{merchantId}
              and m.deleted = 0
              and m.merchant_type_code = 'parking'
            limit 1
            """)
    Map<String, Object> selectParkingMerchant(@Param("merchantId") Long merchantId);

    @Select("""
            select count(*)
            from transport_parking_plate_registration r
            left join merchant m on m.id = r.merchant_id
            left join transport_parking_lot l on l.id = r.parking_lot_id
                and l.merchant_id = r.merchant_id and l.deleted = 0
            left join talent_user u on u.id = r.talent_id
            left join talent_card c on c.id = coalesce(r.card_id, (
                select c2.id
                from talent_card c2
                where c2.talent_id = r.talent_id
                  and c2.deleted = 0
                  and c2.status = 1
                order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
                limit 1
            ))
            where r.deleted = 0
              and (#{merchantId} is null or r.merchant_id = #{merchantId})
              and (#{parkingLotId} is null or r.parking_lot_id = #{parkingLotId})
              and (#{cardLevel} is null or #{cardLevel} = '' or coalesce(r.card_level, c.card_level) = #{cardLevel})
              and (#{status} is null or #{status} = '' or r.status = #{status})
              and (#{submissionType} is null or #{submissionType} = '' or r.submission_type = #{submissionType})
              and (#{submittedStart} is null or r.submitted_at >= #{submittedStart})
              and (#{submittedEnd} is null or r.submitted_at < #{submittedEnd})
              and (#{keyword} is null or #{keyword} = ''
                   or r.registration_no like concat('%', #{keyword}, '%')
                   or r.plate_number like concat('%', #{keyword}, '%')
                   or coalesce(r.talent_name, u.name) like concat('%', #{keyword}, '%')
                   or coalesce(r.talent_mobile, u.mobile) like concat('%', #{keyword}, '%')
                   or coalesce(r.card_no, c.card_no) like concat('%', #{keyword}, '%'))
              and (#{talentName} is null or #{talentName} = '' or coalesce(r.talent_name, u.name) like concat('%', #{talentName}, '%'))
              and (#{talentMobile} is null or #{talentMobile} = '' or coalesce(r.talent_mobile, u.mobile) like concat('%', #{talentMobile}, '%'))
              and (#{plateNumber} is null or #{plateNumber} = '' or r.plate_number like concat('%', #{plateNumber}, '%'))
              and (#{serviceKeyword} is null or #{serviceKeyword} = ''
                   or m.merchant_name like concat('%', #{serviceKeyword}, '%')
                   or coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) like concat('%', #{serviceKeyword}, '%'))
            """)
    Long countAdminPage(@Param("merchantId") Long merchantId,
                        @Param("parkingLotId") Long parkingLotId,
                        @Param("keyword") String keyword,
                        @Param("talentName") String talentName,
                        @Param("talentMobile") String talentMobile,
                        @Param("plateNumber") String plateNumber,
                        @Param("serviceKeyword") String serviceKeyword,
                        @Param("cardLevel") String cardLevel,
                        @Param("status") String status,
                        @Param("submissionType") String submissionType,
                        @Param("submittedStart") LocalDateTime submittedStart,
                        @Param("submittedEnd") LocalDateTime submittedEnd);

    @Select("""
            select r.id,
                   r.registration_no as registrationNo,
                   r.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   r.benefit_id as benefitId,
                   r.parking_lot_id as parkingLotId,
                   coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) as parkingLotName,
                   r.talent_id as talentId,
                   r.card_id as cardId,
                   coalesce(r.talent_name, u.name) as talentName,
                   coalesce(r.talent_mobile, u.mobile) as talentMobile,
                   coalesce(r.card_no, c.card_no) as cardNo,
                   coalesce(r.card_level, c.card_level) as cardLevel,
                   r.plate_number as plateNumber,
                   r.submission_type as submissionType,
                   r.submitted_at as submittedAt,
                   r.status,
                   r.handled_by as handledBy,
                   coalesce(op.display_name, mop.display_name) as handledByName,
                   r.handled_at as handledAt,
                   r.handle_remark as handleRemark,
                   r.created_at as createdAt,
                   r.updated_at as updatedAt
            from transport_parking_plate_registration r
            left join merchant m on m.id = r.merchant_id
            left join transport_parking_lot l on l.id = r.parking_lot_id
                and l.merchant_id = r.merchant_id and l.deleted = 0
            left join talent_user u on u.id = r.talent_id
            left join talent_card c on c.id = coalesce(r.card_id, (
                select c2.id
                from talent_card c2
                where c2.talent_id = r.talent_id
                  and c2.deleted = 0
                  and c2.status = 1
                order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
                limit 1
            ))
            left join admin_operator op on op.id = r.handled_by and op.deleted = 0
            left join merchant_operator mop on mop.id = r.handled_by and mop.deleted = 0
            where r.deleted = 0
              and (#{merchantId} is null or r.merchant_id = #{merchantId})
              and (#{parkingLotId} is null or r.parking_lot_id = #{parkingLotId})
              and (#{cardLevel} is null or #{cardLevel} = '' or coalesce(r.card_level, c.card_level) = #{cardLevel})
              and (#{status} is null or #{status} = '' or r.status = #{status})
              and (#{submissionType} is null or #{submissionType} = '' or r.submission_type = #{submissionType})
              and (#{submittedStart} is null or r.submitted_at >= #{submittedStart})
              and (#{submittedEnd} is null or r.submitted_at < #{submittedEnd})
              and (#{keyword} is null or #{keyword} = ''
                   or r.registration_no like concat('%', #{keyword}, '%')
                   or r.plate_number like concat('%', #{keyword}, '%')
                   or coalesce(r.talent_name, u.name) like concat('%', #{keyword}, '%')
                   or coalesce(r.talent_mobile, u.mobile) like concat('%', #{keyword}, '%')
                   or coalesce(r.card_no, c.card_no) like concat('%', #{keyword}, '%'))
              and (#{talentName} is null or #{talentName} = '' or coalesce(r.talent_name, u.name) like concat('%', #{talentName}, '%'))
              and (#{talentMobile} is null or #{talentMobile} = '' or coalesce(r.talent_mobile, u.mobile) like concat('%', #{talentMobile}, '%'))
              and (#{plateNumber} is null or #{plateNumber} = '' or r.plate_number like concat('%', #{plateNumber}, '%'))
              and (#{serviceKeyword} is null or #{serviceKeyword} = ''
                   or m.merchant_name like concat('%', #{serviceKeyword}, '%')
                   or coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) like concat('%', #{serviceKeyword}, '%'))
            order by r.submitted_at desc, r.id desc
            limit #{size} offset #{offset}
            """)
    List<Map<String, Object>> selectAdminPage(@Param("merchantId") Long merchantId,
                                              @Param("parkingLotId") Long parkingLotId,
                                              @Param("keyword") String keyword,
                                              @Param("talentName") String talentName,
                                              @Param("talentMobile") String talentMobile,
                                              @Param("plateNumber") String plateNumber,
                                              @Param("serviceKeyword") String serviceKeyword,
                                              @Param("cardLevel") String cardLevel,
                                              @Param("status") String status,
                                              @Param("submissionType") String submissionType,
                                              @Param("submittedStart") LocalDateTime submittedStart,
                                              @Param("submittedEnd") LocalDateTime submittedEnd,
                                              @Param("size") int size,
                                              @Param("offset") int offset);

    @Select("""
            select r.id,
                   r.registration_no as registrationNo,
                   r.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   m.contact_mobile as merchantContactMobile,
                   m.address as merchantAddress,
                   r.benefit_id as benefitId,
                   r.parking_lot_id as parkingLotId,
                   coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) as parkingLotName,
                   coalesce(r.parking_lot_address_snapshot, l.address) as parkingLotAddress,
                   r.talent_id as talentId,
                   r.card_id as cardId,
                   coalesce(r.talent_name, u.name) as talentName,
                   coalesce(r.talent_mobile, u.mobile) as talentMobile,
                   coalesce(r.card_no, c.card_no) as cardNo,
                   coalesce(r.card_level, c.card_level) as cardLevel,
                   r.plate_number as plateNumber,
                   r.submission_type as submissionType,
                   r.submitted_at as submittedAt,
                   r.status,
                   r.handled_by as handledBy,
                   coalesce(op.display_name, mop.display_name) as handledByName,
                   r.handled_at as handledAt,
                   r.handle_remark as handleRemark,
                   r.created_at as createdAt,
                   r.updated_at as updatedAt
            from transport_parking_plate_registration r
            left join merchant m on m.id = r.merchant_id
            left join transport_parking_lot l on l.id = r.parking_lot_id
                and l.merchant_id = r.merchant_id and l.deleted = 0
            left join talent_user u on u.id = r.talent_id
            left join talent_card c on c.id = coalesce(r.card_id, (
                select c2.id
                from talent_card c2
                where c2.talent_id = r.talent_id
                  and c2.deleted = 0
                  and c2.status = 1
                order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
                limit 1
            ))
            left join admin_operator op on op.id = r.handled_by and op.deleted = 0
            left join merchant_operator mop on mop.id = r.handled_by and mop.deleted = 0
            where r.id = #{id} and r.deleted = 0
            limit 1
            """)
    Map<String, Object> selectAdminDetail(@Param("id") Long id);

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

    @Select("""
            select id, registration_no as registrationNo, status
            from transport_parking_plate_registration
            where idempotency_key = #{idempotencyKey} and deleted = 0
            limit 1
            """)
    Map<String, Object> selectByIdempotencyKey(@Param("idempotencyKey") String idempotencyKey);
    @Select("""
            select count(*)
            from transport_parking_plate_registration r
            left join transport_parking_lot l on l.id = r.parking_lot_id
                and l.merchant_id = r.merchant_id and l.deleted = 0
            left join talent_user u on u.id = r.talent_id
            left join talent_card c on c.id = coalesce(r.card_id, (
                select c2.id
                from talent_card c2
                where c2.talent_id = r.talent_id
                  and c2.deleted = 0
                  and c2.status = 1
                order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
                limit 1
            ))
            where r.deleted = 0
              and r.merchant_id = #{merchantId}
              and (#{parkingLotId} is null or r.parking_lot_id = #{parkingLotId})
              and (#{cardLevel} is null or #{cardLevel} = '' or coalesce(r.card_level, c.card_level) = #{cardLevel})
              and (#{status} is null or #{status} = '' or r.status = #{status})
              and (#{submissionType} is null or #{submissionType} = '' or r.submission_type = #{submissionType})
              and (#{submittedStart} is null or r.submitted_at >= #{submittedStart})
              and (#{submittedEnd} is null or r.submitted_at < #{submittedEnd})
              and (#{keyword} is null or #{keyword} = ''
                   or r.registration_no like concat('%', #{keyword}, '%')
                   or r.plate_number like concat('%', #{keyword}, '%')
                   or coalesce(r.talent_name, u.name) like concat('%', #{keyword}, '%')
                   or coalesce(r.talent_mobile, u.mobile) like concat('%', #{keyword}, '%')
                   or coalesce(r.card_no, c.card_no) like concat('%', #{keyword}, '%')
                   or coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) like concat('%', #{keyword}, '%'))
            """)
    Long countMerchantPage(@Param("merchantId") Long merchantId,
                           @Param("parkingLotId") Long parkingLotId,
                           @Param("keyword") String keyword,
                           @Param("cardLevel") String cardLevel,
                           @Param("status") String status,
                           @Param("submissionType") String submissionType,
                           @Param("submittedStart") LocalDateTime submittedStart,
                           @Param("submittedEnd") LocalDateTime submittedEnd);

    @Select("""
            select r.id,
                   r.registration_no as registrationNo,
                   r.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   r.benefit_id as benefitId,
                   r.parking_lot_id as parkingLotId,
                   coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) as parkingLotName,
                   r.talent_id as talentId,
                   r.card_id as cardId,
                   coalesce(r.talent_name, u.name) as talentName,
                   coalesce(r.talent_mobile, u.mobile) as talentMobile,
                   coalesce(r.card_no, c.card_no) as cardNo,
                   coalesce(r.card_level, c.card_level) as cardLevel,
                   r.plate_number as plateNumber,
                   r.submission_type as submissionType,
                   r.submitted_at as submittedAt,
                   r.status,
                   r.handled_by as handledBy,
                   mo.display_name as handledByName,
                   r.handled_at as handledAt,
                   r.handle_remark as handleRemark,
                   r.created_at as createdAt,
                   r.updated_at as updatedAt
            from transport_parking_plate_registration r
            left join merchant m on m.id = r.merchant_id
            left join transport_parking_lot l on l.id = r.parking_lot_id
                and l.merchant_id = r.merchant_id and l.deleted = 0
            left join talent_user u on u.id = r.talent_id
            left join talent_card c on c.id = coalesce(r.card_id, (
                select c2.id
                from talent_card c2
                where c2.talent_id = r.talent_id
                  and c2.deleted = 0
                  and c2.status = 1
                order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
                limit 1
            ))
            left join merchant_operator mo on mo.id = r.handled_by and mo.deleted = 0
            where r.deleted = 0
              and r.merchant_id = #{merchantId}
              and (#{parkingLotId} is null or r.parking_lot_id = #{parkingLotId})
              and (#{cardLevel} is null or #{cardLevel} = '' or coalesce(r.card_level, c.card_level) = #{cardLevel})
              and (#{status} is null or #{status} = '' or r.status = #{status})
              and (#{submissionType} is null or #{submissionType} = '' or r.submission_type = #{submissionType})
              and (#{submittedStart} is null or r.submitted_at >= #{submittedStart})
              and (#{submittedEnd} is null or r.submitted_at < #{submittedEnd})
              and (#{keyword} is null or #{keyword} = ''
                   or r.registration_no like concat('%', #{keyword}, '%')
                   or r.plate_number like concat('%', #{keyword}, '%')
                   or coalesce(r.talent_name, u.name) like concat('%', #{keyword}, '%')
                   or coalesce(r.talent_mobile, u.mobile) like concat('%', #{keyword}, '%')
                   or coalesce(r.card_no, c.card_no) like concat('%', #{keyword}, '%')
                   or coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) like concat('%', #{keyword}, '%'))
            order by r.submitted_at desc, r.id desc
            limit #{size} offset #{offset}
            """)
    List<Map<String, Object>> selectMerchantPage(@Param("merchantId") Long merchantId,
                                                 @Param("parkingLotId") Long parkingLotId,
                                                 @Param("keyword") String keyword,
                                                 @Param("cardLevel") String cardLevel,
                                                 @Param("status") String status,
                                                 @Param("submissionType") String submissionType,
                                                 @Param("submittedStart") LocalDateTime submittedStart,
                                                 @Param("submittedEnd") LocalDateTime submittedEnd,
                                                 @Param("size") int size,
                                                 @Param("offset") int offset);

    @Select("""
            select r.id,
                   r.registration_no as registrationNo,
                   r.merchant_id as merchantId,
                   m.merchant_name as merchantName,
                   m.contact_mobile as merchantContactMobile,
                   m.address as merchantAddress,
                   r.benefit_id as benefitId,
                   r.parking_lot_id as parkingLotId,
                   coalesce(r.parking_lot_name_snapshot, l.parking_lot_name) as parkingLotName,
                   coalesce(r.parking_lot_address_snapshot, l.address) as parkingLotAddress,
                   r.talent_id as talentId,
                   r.card_id as cardId,
                   coalesce(r.talent_name, u.name) as talentName,
                   coalesce(r.talent_mobile, u.mobile) as talentMobile,
                   coalesce(r.card_no, c.card_no) as cardNo,
                   coalesce(r.card_level, c.card_level) as cardLevel,
                   r.plate_number as plateNumber,
                   r.submission_type as submissionType,
                   r.submitted_at as submittedAt,
                   r.status,
                   r.handled_by as handledBy,
                   mo.display_name as handledByName,
                   r.handled_at as handledAt,
                   r.handle_remark as handleRemark,
                   r.created_at as createdAt,
                   r.updated_at as updatedAt
            from transport_parking_plate_registration r
            left join merchant m on m.id = r.merchant_id
            left join transport_parking_lot l on l.id = r.parking_lot_id
                and l.merchant_id = r.merchant_id and l.deleted = 0
            left join talent_user u on u.id = r.talent_id
            left join talent_card c on c.id = coalesce(r.card_id, (
                select c2.id
                from talent_card c2
                where c2.talent_id = r.talent_id
                  and c2.deleted = 0
                  and c2.status = 1
                order by case when c2.card_type = 'PERSONAL' then 0 else 1 end, c2.id desc
                limit 1
            ))
            left join merchant_operator mo on mo.id = r.handled_by and mo.deleted = 0
            where r.id = #{id} and r.merchant_id = #{merchantId} and r.deleted = 0
            limit 1
            """)
    Map<String, Object> selectMerchantDetail(@Param("id") Long id, @Param("merchantId") Long merchantId);
}