<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fs.doctor.mapper.DoctorCompanyUserMapper">
    
    <resultMap type="DoctorCompanyUser" id="DoctorCompanyUserResult">
        <result property="id"    column="id"    />
        <result property="doctorId"    column="doctor_id"    />
        <result property="companyUserId"    column="company_user_id"    />
        <result property="companyUserName"    column="company_user_name"    />
        <result property="companyId"    column="company_id"    />
        <result property="product"    column="product"    />
        <result property="bindtime"    column="bindtime"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="deleted"    column="deleted"    />
    </resultMap>

    <sql id="selectDoctorCompanyUserVo">
        select id, doctor_id, company_user_id, company_user_name, company_id, product, bindtime, create_time, update_time, deleted from doctor_company_user
    </sql>

    <select id="selectDoctorCompanyUserList" parameterType="DoctorCompanyUser" resultMap="DoctorCompanyUserResult">
        <include refid="selectDoctorCompanyUserVo"/>
        <where>  
            <if test="doctorId != null  and doctorId != ''"> and doctor_id = #{doctorId}</if>
            <if test="companyUserId != null  and companyUserId != ''"> and company_user_id = #{companyUserId}</if>
            <if test="companyUserName != null  and companyUserName != ''"> and company_user_name like concat('%', #{companyUserName}, '%')</if>
            <if test="companyId != null "> and company_id = #{companyId}</if>
            <if test="product != null  and product != ''"> and product = #{product}</if>
            <if test="bindtime != null "> and bindtime = #{bindtime}</if>
            <if test="deleted != null "> and deleted = #{deleted}</if>
        </where>
        order by create_time desc

    </select>
    
    <select id="selectDoctorCompanyUserById" parameterType="Long" resultMap="DoctorCompanyUserResult">
        <include refid="selectDoctorCompanyUserVo"/>
        where id = #{id}
    </select>
        
    <insert id="insertDoctorCompanyUser" parameterType="DoctorCompanyUser" useGeneratedKeys="true" keyProperty="id">
        insert into doctor_company_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="doctorId != null and doctorId != ''">doctor_id,</if>
            <if test="companyUserId != null and companyUserId != ''">company_user_id,</if>
            <if test="companyUserName != null">company_user_name,</if>
            <if test="companyId != null">company_id,</if>
            <if test="product != null">product,</if>
            <if test="bindtime != null">bindtime,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="deleted != null">deleted,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="doctorId != null and doctorId != ''">#{doctorId},</if>
            <if test="companyUserId != null and companyUserId != ''">#{companyUserId},</if>
            <if test="companyUserName != null">#{companyUserName},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="product != null">#{product},</if>
            <if test="bindtime != null">#{bindtime},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="deleted != null">#{deleted},</if>
         </trim>
    </insert>

    <update id="updateDoctorCompanyUser" parameterType="DoctorCompanyUser">
        update doctor_company_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="doctorId != null and doctorId != ''">doctor_id = #{doctorId},</if>
            <if test="companyUserId != null and companyUserId != ''">company_user_id = #{companyUserId},</if>
            <if test="companyUserName != null">company_user_name = #{companyUserName},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="product != null">product = #{product},</if>
            <if test="bindtime != null">bindtime = #{bindtime},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="deleted != null">deleted = #{deleted},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteDoctorCompanyUserById" parameterType="Long">
        delete from doctor_company_user where id = #{id}
    </delete>

    <delete id="deleteDoctorCompanyUserByIds" parameterType="String">
        delete from doctor_company_user where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>


    <select id="queryProductGroup"
            resultType="com.fs.doctor.domain.DoctorCompanyUser">
        select
        id,
        doctor_id,
        company_user_id,
        company_user_name,
        company_id,
        product,
        bindtime,
        create_time,
        update_time,
        deleted
        from doctor_company_user
        where deleted = 0
        and company_id = #{companyId}
        and doctor_id = #{doctorId}
    </select>


    <select id="selectCompanyProductByProductCodes" resultType="com.fs.company.domain.CompanyProduct">
        select
        id,
        company_id,
        product_code,
        product_name,
        status,
        sort,
        create_time,
        update_time,
        del_flag,
        remark
        from company_product
        <where>
            del_flag = 0
            <if test="codes != null and codes.size() > 0">
                and product_code in
                <foreach collection="codes" item="code" open="(" separator="," close=")">
                    #{code}
                </foreach>
            </if>
            <if test="codes == null or codes.size() == 0">
                and 1 = 0
            </if>
        </where>
        order by sort asc, id asc
    </select>


    <select id="selectDoctorOptionsByCompanyUser"
            resultType="com.fs.doctor.vo.DoctorAndCompanyUserInfoVO">
        SELECT
        d.id      AS doctorId,
        d.doctor_name    AS doctorName,
        d.mobile         AS mobile,
        d.institution    AS institution,
        d.department     AS department,
        d.job_title      AS jobTitle
        FROM doctor_company_user rel
        INNER JOIN doctor d
        ON d.doctor_id = rel.doctor_id
        AND d.deleted = 0
        AND d.status = 1
        WHERE rel.deleted = 0
        AND rel.company_id = #{companyId}
        AND rel.company_user_id = #{companyUserId}

        <!-- ✅ productCode 过滤：rel.product 是逗号分割字符串 -->
        <if test="productCode != null and productCode != ''">
            AND rel.product IS NOT NULL
            AND rel.product &lt;&gt; ''
            AND FIND_IN_SET(#{productCode}, REPLACE(rel.product,' ','')) &gt; 0
        </if>

        <if test="keyword != null and keyword != ''">
            AND (
            d.doctor_name LIKE CONCAT('%', #{keyword}, '%')
            OR d.mobile LIKE CONCAT('%', #{keyword}, '%')
            )
        </if>

        ORDER BY rel.update_time DESC
        LIMIT 20
    </select>



    <select id="getProductOptionsByCompanyUser"
            resultType="com.fs.doctor.vo.CompanyProductOptionVO">
        SELECT
        cp.id           AS id,
        cp.product_code AS productCode,
        cp.product_name AS productName
        FROM company_product cp
        WHERE cp.del_flag = 0
        AND cp.status = 1
        AND cp.company_id = #{companyId}
        AND EXISTS (
        SELECT 1
        FROM doctor_company_user dcu
        WHERE dcu.deleted = 0
        AND dcu.company_id = #{companyId}
        AND dcu.company_user_id = #{companyUserId}
        AND dcu.product IS NOT NULL
        AND <![CDATA[
                dcu.product <> ''
            ]]>
        AND FIND_IN_SET(cp.product_code, REPLACE(dcu.product, ' ', '')) > 0
        )
        <if test="keyword != null and keyword != ''">
            AND (
            cp.product_name LIKE CONCAT('%', #{keyword}, '%')
            OR cp.product_code LIKE CONCAT('%', #{keyword}, '%')
            )
        </if>
        ORDER BY cp.sort ASC, cp.update_time DESC
        LIMIT 50
    </select>




</mapper>