package com.aiotagro.cattletrade.business.mapper;

import com.aiotagro.cattletrade.business.entity.Member;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;

import java.util.List;
import java.util.Map;

/**
 * Member Mapper
 *
 * @author System
 * @date 2025-01-16
 */
@Mapper
public interface MemberMapper extends BaseMapper<Member> {

    /**
     * 查询用户列表（关联member_user表获取用户名）
     */
    @Select("SELECT m.id, m.mobile, COALESCE(mu.username, m.mobile) as name, m.type " +
            "FROM member m " +
            "LEFT JOIN member_user mu ON m.id = mu.member_id " +
            "WHERE CAST(m.status AS UNSIGNED) = 1 " +
            "ORDER BY m.id ASC")
    List<Map<String, Object>> selectMemberUserList();

    /**
     * 根据类型查询会员列表（分页，用于装车订单下拉框）
     */
    @Select("<script>" +
            "SELECT m.id, m.mobile, COALESCE(mu.username, m.mobile) as username, m.type, " +
            "       COALESCE(st.name, '未知租户') as tenantName " +
            "FROM member m " +
            "LEFT JOIN member_user mu ON m.id = mu.member_id " +
            "LEFT JOIN sys_tenant st ON m.tenant_id = st.id " +
            "WHERE m.status = 0 " +
            "<if test='type != null'>" +
            "   AND m.type = #{type} " +
            "</if>" +
            "<if test='username != null and username != \"\"'>" +
            "   AND (mu.username LIKE CONCAT('%', #{username}, '%') OR m.mobile LIKE CONCAT('%', #{username}, '%')) " +
            "</if>" +
            "ORDER BY m.id DESC " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<Map<String, Object>> selectMemberListByType(@Param("type") Integer type, 
                                                      @Param("username") String username,
                                                      @Param("offset") Integer offset, 
                                                      @Param("pageSize") Integer pageSize);

    /**
     * 根据类型查询会员总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) " +
            "FROM member m " +
            "LEFT JOIN member_user mu ON m.id = mu.member_id " +
            "WHERE m.status = 0 " +
            "<if test='type != null'>" +
            "   AND m.type = #{type} " +
            "</if>" +
            "<if test='username != null and username != \"\"'>" +
            "   AND (mu.username LIKE CONCAT('%', #{username}, '%') OR m.mobile LIKE CONCAT('%', #{username}, '%')) " +
            "</if>" +
            "</script>")
    Long countMemberListByType(@Param("type") Integer type, @Param("username") String username);

    /**
     * 查询用户列表（分页，用于用户管理页面）
     * 只查询member_user表中存在的用户
     */
    @Select("<script>" +
            "SELECT m.id, m.mobile, CAST(m.type AS UNSIGNED) AS type, CAST(m.status AS UNSIGNED) AS status, m.create_time, m.create_by, " +
            "       mu.username as name, " +
            "       mu.cbk_account as cbkAccount, " +
            "       mu.remark, " +
            "       su.name as createName " +
            "FROM member_user mu " +
            "INNER JOIN member m ON mu.member_id = m.id " +
            "LEFT JOIN sys_user su ON m.create_by = su.id " +
            "WHERE m.status = 0 " +
            "<if test='username != null and username != \"\"'>" +
            "  <choose>" +
            "    <when test='usernameExact == true'>" +
            "      AND mu.username = #{username} " +
            "    </when>" +
            "    <otherwise>" +
            "      AND mu.username LIKE CONCAT('%', #{username}, '%') " +
            "    </otherwise>" +
            "  </choose>" +
            "</if>" +
            "<if test='mobile != null and mobile != \"\"'>" +
            "  <choose>" +
            "    <when test='mobileExact == true'>" +
            "      AND m.mobile = #{mobile} " +
            "    </when>" +
            "    <otherwise>" +
            "      AND m.mobile LIKE CONCAT('%', #{mobile}, '%') " +
            "    </otherwise>" +
            "  </choose>" +
            "</if>" +
            "<if test='type != null'>" +
            "   AND m.type = #{type} " +
            "</if>" +
            "ORDER BY m.id DESC " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<Map<String, Object>> selectUserListForManagement(@Param("username") String username,
                                                           @Param("mobile") String mobile,
                                                           @Param("type") Integer type,
                                                           @Param("usernameExact") Boolean usernameExact,
                                                           @Param("mobileExact") Boolean mobileExact,
                                                           @Param("offset") Integer offset,
                                                           @Param("pageSize") Integer pageSize);

    /**
     * 查询用户总数（用于用户管理页面）
     * 只计算member_user表中存在的用户
     */
    @Select("<script>" +
            "SELECT COUNT(*) " +
            "FROM member_user mu " +
            "INNER JOIN member m ON mu.member_id = m.id " +
            "WHERE m.status = 0 " +
            "<if test='username != null and username != \"\"'>" +
            "  <choose>" +
            "    <when test='usernameExact == true'>" +
            "      AND mu.username = #{username} " +
            "    </when>" +
            "    <otherwise>" +
            "      AND mu.username LIKE CONCAT('%', #{username}, '%') " +
            "    </otherwise>" +
            "  </choose>" +
            "</if>" +
            "<if test='mobile != null and mobile != \"\"'>" +
            "  <choose>" +
            "    <when test='mobileExact == true'>" +
            "      AND m.mobile = #{mobile} " +
            "    </when>" +
            "    <otherwise>" +
            "      AND m.mobile LIKE CONCAT('%', #{mobile}, '%') " +
            "    </otherwise>" +
            "  </choose>" +
            "</if>" +
            "<if test='type != null'>" +
            "   AND m.type = #{type} " +
            "</if>" +
            "</script>")
    Long countUserListForManagement(@Param("username") String username,
                                    @Param("mobile") String mobile,
                                    @Param("type") Integer type,
                                    @Param("usernameExact") Boolean usernameExact,
                                    @Param("mobileExact") Boolean mobileExact);

    /**
     * 更新用户信息
     * 同时更新member表和member_user表
     */
    @org.apache.ibatis.annotations.Update("<script>" +
            "UPDATE member m " +
            "INNER JOIN member_user mu ON m.id = mu.member_id " +
            "SET m.mobile = #{mobile}, " +
            "m.type = #{type}, " +
            "m.status = #{status}, " +
            "m.update_time = NOW(), " +
            "mu.username = #{username}, " +
            "mu.cbk_account = #{cbkAccount}, " +
            "mu.remark = #{remark} " +
            "WHERE m.id = #{id}" +
            "</script>")
    int updateUserInfo(@Param("id") Integer id, 
                       @Param("mobile") String mobile, 
                       @Param("type") Integer type, 
                       @Param("status") Integer status,
                       @Param("username") String username, 
                       @Param("cbkAccount") String cbkAccount, 
                       @Param("remark") String remark);

    /**
     * 根据手机号查询member记录（用于获取新插入的member ID）
     */
    @Select("SELECT id FROM member WHERE mobile = #{mobile} ORDER BY id DESC LIMIT 1")
    Integer selectMemberIdByMobile(@Param("mobile") String mobile);

    /**
     * 查询最新插入的member记录ID（用于mobile为null时获取新插入的ID）
     */
    @Select("SELECT id FROM member ORDER BY id DESC LIMIT 1")
    Integer selectLatestMemberId();

    /**
     * 新增用户基础信息（插入member表）
     */
    @Insert("INSERT INTO member (mobile, type, status, create_time, create_by) " +
            "VALUES (#{mobile}, #{type}, #{status}, NOW(), 1)")
    int insertMember(@Param("mobile") String mobile, 
                     @Param("type") Integer type, 
                     @Param("status") Integer status);

    /**
     * 新增用户详细信息（插入member_user表）
     */
    @Insert("INSERT INTO member_user (member_id, username, cbk_account, remark, create_time) " +
            "VALUES (#{memberId}, #{username}, #{cbkAccount}, #{remark}, NOW())")
    int insertMemberUser(@Param("memberId") Integer memberId,
                         @Param("username") String username, 
                         @Param("cbkAccount") String cbkAccount, 
                         @Param("remark") String remark);

    /**
     * 根据member ID查询member和member_user关联数据
     */
    @Select("SELECT m.id, m.mobile, mu.username " +
            "FROM member m " +
            "LEFT JOIN member_user mu ON m.id = mu.member_id " +
            "WHERE m.id = #{memberId}")
    Map<String, Object> selectMemberUserById(@Param("memberId") Integer memberId);

    /**
     * 批量根据member ID查询member和member_user关联数据
     */
    @Select("<script>" +
            "SELECT m.id, m.mobile, mu.username " +
            "FROM member m " +
            "LEFT JOIN member_user mu ON m.id = mu.member_id " +
            "WHERE m.id IN " +
            "<foreach collection='memberIds' item='id' open='(' separator=',' close=')'>" +
            "   #{id}" +
            "</foreach>" +
            "</script>")
    List<Map<String, Object>> selectMemberUserByIds(@Param("memberIds") List<Integer> memberIds);

    /**
     * 更新member表的手机号
     */
    @org.apache.ibatis.annotations.Update("UPDATE member SET mobile = #{mobile}, update_time = NOW() WHERE id = #{memberId}")
    int updateMemberMobile(@Param("memberId") Integer memberId, @Param("mobile") String mobile);
}
