<?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.zhyc.module.produce.breed.mapper.ScBreedRecordMapper">

    <!--    <resultMap type="ScBreedRecord" id="ScBreedRecordResult">-->
    <!--        <result property="id"    column="id"    />-->
    <!--        <result property="sheepId"    column="sheep_id"    />-->
    <!--        <result property="ramId"    column="ram_id"    />-->
    <!--        <result property="eweId"    column="ewe_id"    />-->
    <!--        <result property="technician"    column="technician"    />-->
    <!--        <result property="breedDrugs"    column="breed_drugs"    />-->
    <!--        <result property="breedType"    column="breed_type"    />-->
    <!--        <result property="createBy"    column="create_by"    /  >-->
    <!--        <result property="createTime"    column="create_time"    />-->
    <!--        &lt;!&ndash; 显示字段 &ndash;&gt;-->
    <!--        <result property="eweManageTags"    column="ewe_manage_tags"    />-->
    <!--        <result property="eweVariety"    column="ewe_variety"    />-->
    <!--        <result property="ramManageTags"    column="ram_manage_tags"    />-->
    <!--        <result property="ramVariety"    column="ram_variety"    />-->
    <!--        <result property="eweParity"    column="ewe_parity"    />-->
    <!--        <result property="eweMonthAge"    column="ewe_month_age"    />-->
    <!--        <result property="eweSheepfoldName"    column="ewe_sheepfold_name"    />-->
    <!--        <result property="eweBreedStatus"    column="ewe_breed_status"    />-->
    <!--        <result property="eweControlled"    column="ewe_controlled"    />-->
    <!--        <result property="eweComment"    column="ewe_comment"    />-->
    <!--        <result property="ranchName"    column="ranch_name"    />-->
    <!--        <result property="matingType"    column="mating_type"    />-->
    <!--        <result property="sheepType"    column="sheep_type"    />-->
    <!--        <result property="matingCount"    column="mating_count"    />-->
    <!--        <result property="timeSincePlanning"    column="time_since_planning"    />-->
    <!--        &lt;!&ndash; 孕检相关字段 &ndash;&gt;-->
    <!--        <result property="pregnancyCheckDate"    column="pregnancy_check_date"    />-->
    <!--        <result property="pregnancyResult"    column="pregnancy_result"    />-->
    <!--        <result property="pregnancyWay"    column="pregnancy_way"    />-->
    <!--        <result property="fetusCount"    column="fetus_count"    />-->
    <!--        <result property="pregnancyTechnician"    column="pregnancy_technician"    />-->
    <!--        <result property="pregnancyRemark"    column="pregnancy_remark"    />-->
    <!--        <result property="pregnancyRecordId"    column="pregnancy_record_id"    />-->
    <!--        <result property="daysToPregnancyCheck"    column="days_to_pregnancy_check"    />-->
    <!--        <result property="isPregnancyChecked"    column="is_pregnancy_checked"    />-->
    <!--    </resultMap>-->
    <resultMap type="ScBreedRecord" id="ScBreedRecordResult">
        <result property="id"    column="id"    />
        <result property="sheepId"    column="sheep_id"    />
        <result property="ramId"    column="ram_id"    />
        <result property="eweId"    column="ewe_id"    />
        <result property="technician"    column="technician"    />
        <result property="breedDrugs"    column="breed_drugs"    />
        <result property="breedType"    column="breed_type"    />
        <result property="frozenSemenNo" column="frozen_sperm_no" /> <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="comment" column="comment" />

        <result property="embryoCount" column="embryo_count" />
        <result property="embryoSubType" column="embryoSubType" />
        <result property="donorEweNo" column="donor_ewe" />
        <result property="donorRamNo" column="donor_ram" />

        <result property="eweManageTags"    column="ewe_manage_tags"    />
        <result property="eweVariety"    column="ewe_variety"    />
        <result property="ramManageTags"    column="ram_manage_tags"    />
        <result property="ramVariety"    column="ram_variety"    />
        <result property="eweParity"    column="ewe_parity"    />
        <result property="eweMonthAge"    column="ewe_month_age"    />
        <result property="eweSheepfoldName"    column="ewe_sheepfold_name"    />
        <result property="eweBreedStatus"    column="ewe_breed_status"    />
        <result property="eweControlled"    column="ewe_controlled"    />
        <result property="eweComment"    column="ewe_comment"    />
        <result property="ranchName"    column="ranch_name"    />
        <result property="sheepType"    column="sheep_type"    />
        <result property="matingCount"    column="mating_count"    />

        <result property="timeSincePlanning"    column="time_since_planning"    />
        <result property="daysPostLambing"      column="days_post_lambing" />
        <result property="daysLactation"        column="days_lactation" />
        <result property="daysPostMating"       column="days_post_mating" />
        <result property="lastMatingDate"       column="last_mating_date" />

        <result property="pregnancyCheckDate"    column="pregnancy_check_date"    />
        <result property="pregnancyResult"    column="pregnancy_result"    />
        <result property="eweParity"    column="eweParity"    />


        <result property="pregnancyRecordId"    column="pregnancy_record_id"    />
        <result property="daysToPregnancyCheck"    column="days_to_pregnancy_check"    />

    </resultMap>
    <!--    <sql id="selectScBreedRecordVo">-->
    <!--        select DISTINCT-->
    <!--            br.id,-->
    <!--            br.sheep_id,-->
    <!--            br.ram_id,-->
    <!--            br.ewe_id,-->
    <!--            br.technician,-->
    <!--            br.breed_drugs,-->
    <!--            br.breed_type,-->
    <!--            br.create_by,-->
    <!--            br.create_time,-->
    <!--            &#45;&#45; 母羊信息(从视图获取)-->
    <!--            ewe_view.bs_manage_tags as ewe_manage_tags,-->
    <!--            ewe_view.variety as ewe_variety,-->
    <!--            ewe_view.parity as ewe_parity,-->
    <!--            ewe_view.month_age as ewe_month_age,-->
    <!--            ewe_view.sheepfold_name as ewe_sheepfold_name,-->
    <!--            ewe_view.breed as ewe_breed_status,-->
    <!--            ewe_view.controlled as ewe_controlled,-->
    <!--            ewe_view.comment as ewe_comment,-->
    <!--            ewe_view.dr_ranch as ranch_name,-->
    <!--            ewe_view.name as sheep_type,-->
    <!--            ewe_view.mating_total as mating_count,-->
    <!--            &#45;&#45; 公羊信息(从视图获取)-->
    <!--            ram_view.bs_manage_tags as ram_manage_tags,-->
    <!--            ram_view.variety as ram_variety,-->

    <!--            &#45;&#45; 配种方式显示-->
    <!--            CASE br.breed_type-->
    <!--                WHEN 1 THEN '同期发情'-->
    <!--                WHEN 2 THEN '本交'-->
    <!--                WHEN 3 THEN '冲胚'-->
    <!--                WHEN 4 THEN '自然发情人工授精'-->
    <!--                ELSE '未知'-->
    <!--                END as mating_type,-->
    <!--            &#45;&#45; 发情后配种时间(小时数)-->
    <!--            TIMESTAMPDIFF(HOUR, br.create_time, NOW()) as time_since_planning,-->
    <!--            &#45;&#45; 孕检相关信息-->
    <!--            pr.datetime as pregnancy_check_date,-->
    <!--            pr.result as pregnancy_result,-->
    <!--            pr.way as pregnancy_way,-->
    <!--            pr.fetus_count,-->
    <!--            pr.technician as pregnancy_technician,-->
    <!--            pr.remark as pregnancy_remark,-->
    <!--            pr.id as pregnancy_record_id,-->
    <!--            &#45;&#45; 配种到孕检间隔天数-->
    <!--            CASE-->
    <!--                WHEN pr.datetime IS NOT NULL THEN DATEDIFF(pr.datetime, br.create_time)-->
    <!--                ELSE NULL-->
    <!--                END as days_to_pregnancy_check,-->
    <!--            &#45;&#45; 是否已孕检-->
    <!--            CASE-->
    <!--                WHEN pr.id IS NOT NULL THEN 1-->
    <!--                ELSE 0-->
    <!--                END as is_pregnancy_checked-->
    <!--        from sc_breed_record br-->
    <!--                 left join sheep_file ewe_view on br.ewe_id = ewe_view.id-->
    <!--                 left join sheep_file ram_view on br.ram_id = ram_view.id-->
    <!--            &#45;&#45; 修复：使用子查询确保只返回一条孕检记录-->
    <!--                 left join sc_pregnancy_record pr on pr.id = (-->
    <!--            select pr2.id-->
    <!--            from sc_pregnancy_record pr2-->
    <!--            where pr2.sheep_id = br.ewe_id-->
    <!--              and pr2.is_delete = 0-->
    <!--              and pr2.datetime >= br.create_time-->
    <!--            order by pr2.datetime asc, pr2.id asc-->
    <!--            limit 1-->
    <!--            )-->
    <!--    </sql>-->
    <sql id="selectScBreedRecordVo">
        select DISTINCT
            br.id,
            br.sheep_id,
            br.ram_id,
            br.ewe_id,
            br.technician,
            br.breed_drugs,
            br.breed_type,
            br.frozen_sperm_no, -- 冻精号
            br.embryoSubType,
            br.comment,
            br.create_by,
            br.create_time,

            -- 胚胎移植相关字段 (如果不存表里，需要join sc_embryo_flush，这里假设存表或已有字段，如需Join请参考下方注释)
            -- 假设表里已有这些列，或者我们通过Join sheep_file 拿(但sheep_file没有donor信息)
            -- 这里按照最佳实践，应在插入时将Donor信息固化在breed_record表中
            br.donor_ewe,
            br.donor_ram,
            br.embryo_count,

            -- 母羊信息
            ewe_view.bs_manage_tags as ewe_manage_tags,
            ewe_view.variety as ewe_variety,
            ewe_view.parity as ewe_parity,
            ewe_view.month_age as ewe_month_age,
            ewe_view.sheepfold_name as ewe_sheepfold_name,
            ewe_view.breed as ewe_breed_status,
            ewe_view.controlled as ewe_controlled,
            ewe_view.comment as ewe_comment,
            ewe_view.dr_ranch as ranch_name,
            ewe_view.name as sheep_type,
            ewe_view.mating_total as mating_count,

            -- 公羊信息
            ram_view.bs_manage_tags as ram_manage_tags,
            ram_view.variety as ram_variety,
            (SELECT variety FROM sheep_file WHERE bs_manage_tags = br.donor_ewe LIMIT 1) as donorEweVariety,
            (SELECT variety FROM sheep_file WHERE bs_manage_tags = br.donor_ram LIMIT 1) as donorRamVariety,
            -- 发情后配种时间(小时数)
            TIMESTAMPDIFF(HOUR, br.create_time, NOW()) as time_since_planning,

            -- ================= 计算字段实现 =================

            -- 1. 配种时产后天数 (距离上一次产羔: 找最近一次小于配种时间的产羔日期)
            DATEDIFF(br.create_time, (
                SELECT lambing_date
                FROM sheep_file
                WHERE id = br.ewe_id
            )) as days_post_lambing,

            -- 2. 配种时泌乳天数 (距离上一次孕检且怀孕)
            DATEDIFF(br.create_time, (
                SELECT MAX(pr_sub.datetime)
                FROM sc_pregnancy_record pr_sub
                WHERE pr_sub.sheep_id = br.ewe_id
                  AND pr_sub.result = '怀孕'
                  AND pr_sub.is_delete = 0
                  AND pr_sub.datetime &lt; br.create_time
            )) as days_lactation,

            -- 3. 配种时配后天数 (距离上一次配种)
            DATEDIFF(br.create_time, (
                SELECT MAX(br_sub.create_time)
                FROM sc_breed_record br_sub
                WHERE br_sub.ewe_id = br.ewe_id
                  AND br_sub.create_time &lt; br.create_time
            )) as days_post_mating,

            -- 4. 上次配种日期
            (
                SELECT MAX(br_sub.create_time)
                FROM sc_breed_record br_sub
                WHERE br_sub.ewe_id = br.ewe_id
                  AND br_sub.create_time &lt; br.create_time
            ) as last_mating_date,

            -- ===============================================

            -- 孕检相关信息
            pr.datetime as pregnancy_check_date,
            pr.result as pregnancy_result,
            pr.way as pregnancy_way,
            pr.fetus_count,
            pr.technician as pregnancy_technician,
            pr.remark as pregnancy_remark,
            pr.id as pregnancy_record_id,
            -- 配种到孕检间隔天数
            CASE
                WHEN pr.datetime IS NOT NULL THEN DATEDIFF(pr.datetime, br.create_time)
                ELSE NULL
                END as days_to_pregnancy_check,
            -- 是否已孕检
            CASE
                WHEN pr.id IS NOT NULL THEN 1
                ELSE 0
                END as is_pregnancy_checked

        from sc_breed_record br
                 left join sheep_file ewe_view on br.ewe_id = ewe_view.id
                 left join sheep_file ram_view on br.ram_id = ram_view.id
                 left join sc_pregnancy_record pr on pr.id = (
            select pr2.id
            from sc_pregnancy_record pr2
            where pr2.sheep_id = br.ewe_id
              and pr2.is_delete = 0
              and pr2.datetime >= br.create_time
            order by pr2.datetime asc, pr2.id asc
            limit 1
            )
    </sql>
    <!--    <select id="selectScBreedRecordList" parameterType="ScBreedRecord" resultMap="ScBreedRecordResult">-->
    <!--        <include refid="selectScBreedRecordVo"/>-->
    <!--        <where>-->
    <!--            <if test="sheepId != null "> and br.sheep_id = #{sheepId}</if>-->
    <!--            <if test="ramId != null  and ramId != ''"> and br.ram_id = #{ramId}</if>-->
    <!--            <if test="eweId != null  and eweId != ''"> and br.ewe_id = #{eweId}</if>-->
    <!--            <if test="breedType != null"> and br.breed_type = #{breedType}</if>-->
    <!--            <if test="technician != null  and technician != ''"> and br.technician like concat('%', #{technician}, '%')</if>-->
    <!--            <if test="breedDrugs != null  and breedDrugs != ''"> and br.breed_drugs like concat('%', #{breedDrugs}, '%')</if>-->
    <!--            <if test="createBy != null  and createBy != ''"> and br.create_by like concat('%', #{createBy}, '%')</if>-->
    <!--            <if test="createTime != null "> and date_format(br.create_time,'%y-%m-%d') = date_format(#{createTime},'%y-%m-%d')</if>-->
    <!--            &lt;!&ndash; 新增耳号查询条件 &ndash;&gt;-->
    <!--            <if test="eweManageTags != null and eweManageTags != ''"> and ewe_view.bs_manage_tags like concat('%', #{eweManageTags}, '%')</if>-->
    <!--            <if test="ramManageTags != null and ramManageTags != ''"> and ram_view.bs_manage_tags like concat('%', #{ramManageTags}, '%')</if>-->
    <!--            <if test="eweVariety != null and eweVariety != ''"> and ewe_view.variety like concat('%', #{eweVariety}, '%')</if>-->
    <!--            <if test="ramVariety != null and ramVariety != ''"> and ram_view.variety like concat('%', #{ramVariety}, '%')</if>-->
    <!--            <if test="ranchId != null"> and ewe_view.ranch_id = #{ranchId}</if>-->
    <!--            &lt;!&ndash; 孕检相关查询条件 &ndash;&gt;-->
    <!--            <if test="pregnancyResult != null and pregnancyResult != ''"> and pr.result like concat('%', #{pregnancyResult}, '%')</if>-->
    <!--            <if test="isPregnancyChecked != null">-->
    <!--                <if test="isPregnancyChecked == 1"> and pr.id IS NOT NULL</if>-->
    <!--                <if test="isPregnancyChecked == 0"> and pr.id IS NULL</if>-->
    <!--            </if>-->
    <!--        </where>-->
    <!--        order by br.create_time desc-->
    <!--    </select>-->
    <select id="selectScBreedRecordList" parameterType="ScBreedRecord" resultMap="ScBreedRecordResult">
        <include refid="selectScBreedRecordVo"/>
        <where>
            <if test="sheepId != null "> and br.sheep_id = #{sheepId}</if>
            <if test="ramId != null  and ramId != ''"> and br.ram_id = #{ramId}</if>
            <if test="eweId != null  and eweId != ''"> and br.ewe_id = #{eweId}</if>
            <if test="breedType != null"> and br.breed_type = #{breedType}</if>
            <if test="createTime != null "> and date_format(br.create_time,'%y-%m-%d') = date_format(#{createTime},'%y-%m-%d')</if>
            <if test="eweManageTags != null and eweManageTags != ''">
                and (
                ewe_view.bs_manage_tags like concat('%', #{eweManageTags}, '%')
                OR br.donor_ewe_no like concat('%', #{eweManageTags}, '%') -- Also search donor
                )
            </if>
            <if test="ramManageTags != null and ramManageTags != ''">
                and (
                ram_view.bs_manage_tags like concat('%', #{ramManageTags}, '%')
                OR br.donor_ram like concat('%', #{ramManageTags}, '%') -- Also search donor
                )
            </if>
            <if test="frozenSemenNo != null and frozenSemenNo != ''"> and br.frozen_sperm_no like concat('%', #{frozenSemenNo}, '%')</if>
        </where>
        order by br.create_time desc
    </select>

    <select id="selectScBreedRecordById" parameterType="Long" resultMap="ScBreedRecordResult">
        <include refid="selectScBreedRecordVo"/>
        where br.id = #{id}
    </select>



    <!-- 根据母羊耳号查询羊只ID -->
    <select id="getSheepIdByManageTags" parameterType="String" resultType="Long">
        select id from sheep_file where bs_manage_tags = #{manageTags} and is_delete = 0
    </select>

    <!-- 根据公羊耳号查询羊只ID -->
    <select id="getRamIdByManageTags" parameterType="String" resultType="Long">
        select id from sheep_file where bs_manage_tags = #{manageTags} and gender = 2 and is_delete = 0
    </select>

    <!-- 根据耳号查询羊只详细信息 -->
    <select id="getSheepInfoByTags" parameterType="String" resultType="map">
        select
            id,
            bs_manage_tags as manage_tags,
            ranch_id,
            dr_ranch as ranch_name,
            sheepfold_id,
            sheepfold_name,
            electronic_tags,
            variety_id,
            variety,
            family,
            name as type_name,
            gender,
            birthday,
            day_age,
            month_age,
            parity,
            birth_weight,
            weaning_date,
            status_id,
            weaning_weight,
            current_weight,
            breed_status_id,
            breed as breed_status,
            bs_father_id as father_id,
            father_manage_tags,
            bs_mother_id as mother_id,
            mother_manage_tags,
            receptor_id,
            receptor_manage_tags,
            mating_date,
            mating_type_id,
            preg_date,
            lambing_date,
            lambing_day,
            mating_day,
            gestation_day,
            expected_date,
            post_lambing_day,
            lactation_day,
            anestrous_day,
            mating_counts,
            mating_total,
            miscarriage_counts,
            comment,
            controlled,
            body,
            breast,
            source,
            source_date,
            source_ranch_id,
            source_ranch,
            update_by,
            update_time,
            create_by,
            create_time
        from sheep_file
        where bs_manage_tags = #{manageTags} and is_delete = 0
    </select>

    <!-- 根据母羊耳号获取配种计划信息 -->
    <select id="getBreedPlanByEweTags" parameterType="String" resultType="map">
        select
            bp.id as plan_id,
            bp.ewe_id,
            ewe_view.bs_manage_tags as ewe_manage_tags,
            bp.ram_id,
            ram_view.bs_manage_tags as ram_manage_tags,
            bp.breed_type,
            CASE bp.breed_type
                WHEN 1 THEN '同期发情'
                WHEN 2 THEN '本交'
                WHEN 3 THEN '冲胚'
                WHEN 4 THEN '自然发情人工授精'
                ELSE '未知'
                END as breed_type_name,
            TIMESTAMPDIFF(HOUR, NOW(), NOW()) as hours_since_plan
        from sc_breed_plan bp
                 left join sheep_file ewe_view on bp.ewe_id = ewe_view.id
                 left join sheep_file ram_view on bp.ram_id = ram_view.id
        where ewe_view.bs_manage_tags = #{manageTags}
        order by bp.id desc
            limit 1
    </select>

    <!-- 根据母羊耳号获取最新的配种计划信息(从配种计划生成表) -->
    <select id="getLatestBreedPlanByEweTags" parameterType="String" resultType="map">
        select
            bpg.id as plan_generate_id,
            bpt.ewe_id,
            ewe_view.bs_manage_tags as ewe_manage_tags,
            bpt.ram_id,
            ram_view.bs_manage_tags as ram_manage_tags,
            bpt.breed_type,
            CASE bpt.breed_type
                WHEN 1 THEN '同期发情'
                WHEN 2 THEN '本交'
                WHEN 3 THEN '冲胚'
                WHEN 4 THEN '自然发情人工授精'
                ELSE '未知'
                END as breed_type_name,
            bpg.create_time as plan_create_time
        from sc_breed_plan_generate bpg
                 inner join sc_breed_plan_temp bpt on bpg.id = bpt.plan_generate_id
                 left join sheep_file ewe_view on bpt.ewe_id = ewe_view.id
                 left join sheep_file ram_view on bpt.ram_id = ram_view.id
        where ewe_view.bs_manage_tags = #{manageTags}
          and bpg.status = 1  -- 已审批的计划
        order by bpg.create_time desc, bpt.id desc
            limit 1
    </select>

    <!--    <insert id="insertScBreedRecord" parameterType="ScBreedRecord" useGeneratedKeys="true" keyProperty="id">-->
    <!--        insert into sc_breed_record-->
    <!--        <trim prefix="(" suffix=")" suffixOverrides=",">-->
    <!--            <if test="sheepId != null">sheep_id,</if>-->
    <!--            <if test="ramId != null">ram_id,</if>-->
    <!--            <if test="eweId != null">ewe_id,</if>-->
    <!--            <if test="technician != null">technician,</if>-->
    <!--            <if test="breedDrugs != null">breed_drugs,</if>-->
    <!--            <if test="breedType != null">breed_type,</if>-->
    <!--            <if test="createBy != null">create_by,</if>-->
    <!--            <if test="createTime != null">create_time,</if>-->
    <!--        </trim>-->
    <!--        <trim prefix="values (" suffix=")" suffixOverrides=",">-->
    <!--            <if test="sheepId != null">#{sheepId},</if>-->
    <!--            <if test="ramId != null">#{ramId},</if>-->
    <!--            <if test="eweId != null">#{eweId},</if>-->
    <!--            <if test="technician != null">#{technician},</if>-->
    <!--            <if test="breedDrugs != null">#{breedDrugs},</if>-->
    <!--            <if test="breedType != null">#{breedType},</if>-->
    <!--            <if test="createBy != null">#{createBy},</if>-->
    <!--            <if test="createTime != null">#{createTime},</if>-->
    <!--        </trim>-->
    <!--    </insert>-->
    <insert id="insertScBreedRecord" parameterType="ScBreedRecord" useGeneratedKeys="true" keyProperty="id">
        insert into sc_breed_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">sheep_id,</if>
            <if test="ramId != null">ram_id,</if>
            <if test="eweId != null">ewe_id,</if>
            <if test="technician != null">technician,</if>
            <if test="breedDrugs != null">breed_drugs,</if>
            <if test="breedType != null">breed_type,</if>
            <if test="frozenSemenNo != null">frozen_sperm_no,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="comment != null">comment,</if>
            <if test="embryoCount != null">embryo_count,</if>
            <if test="donorEweNo != null">donor_ewe,</if>
            <if test="donorRamNo != null">donor_ram,</if>
            <if test="embryoSubType != null">embryoSubType,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">#{sheepId},</if>
            <if test="ramId != null">#{ramId},</if>
            <if test="eweId != null">#{eweId},</if>
            <if test="technician != null">#{technician},</if>
            <if test="breedDrugs != null">#{breedDrugs},</if>
            <if test="breedType != null">#{breedType},</if>
            <if test="frozenSemenNo != null">#{frozenSemenNo},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="comment != null">#{comment},</if>
            <if test="embryoCount != null">#{embryoCount},</if>
            <if test="donorEweNo != null">#{donorEweNo},</if>
            <if test="donorRamNo != null">#{donorRamNo},</if>
            <if test="embryoSubType != null">#{embryoSubType},</if>
        </trim>
    </insert>

    <!--    <update id="updateScBreedRecord" parameterType="ScBreedRecord">-->
    <!--        update sc_breed_record-->
    <!--        <trim prefix="SET" suffixOverrides=",">-->
    <!--            <if test="sheepId != null">sheep_id = #{sheepId},</if>-->
    <!--            <if test="ramId != null">ram_id = #{ramId},</if>-->
    <!--            <if test="eweId != null">ewe_id = #{eweId},</if>-->
    <!--            <if test="technician != null">technician = #{technician},</if>-->
    <!--            <if test="breedDrugs != null">breed_drugs = #{breedDrugs},</if>-->
    <!--            <if test="breedType != null">breed_type = #{breedType},</if>-->
    <!--            <if test="createBy != null">create_by = #{createBy},</if>-->
    <!--            <if test="createTime != null">create_time = #{createTime},</if>-->
    <!--        </trim>-->
    <!--        where id = #{id}-->
    <!--    </update>-->
    <update id="updateScBreedRecord" parameterType="ScBreedRecord">
        update sc_breed_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="sheepId != null">sheep_id = #{sheepId},</if>
            <if test="ramId != null">ram_id = #{ramId},</if>
            <if test="eweId != null">ewe_id = #{eweId},</if>
            <if test="technician != null">technician = #{technician},</if>
            <if test="breedDrugs != null">breed_drugs = #{breedDrugs},</if>
            <if test="breedType != null">breed_type = #{breedType},</if>
            <if test="frozenSemenNo != null">frozen_sperm_no = #{frozenSemenNo},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="comment != null">comment = #{comment},</if>
            <if test="embryoCount != null">embryo_count = #{embryoCount},</if>
            <if test="donorEweNo != null">donor_ewe = #{donorEweNo},</if>
            <if test="donorRamNo != null">donor_ram = #{donorRamNo},</if>
            <if test="embryoSubType != null">embryoSubType = #{embryoSubType},</if>
        </trim>
        where id = #{id}
    </update>
    <select id="getFlushRecordByEweNo" parameterType="String" resultType="map">
        SELECT
            transferred,
            donor_female_no,
            donor_male_no,
            embryo_type,
            storage_method
        FROM sc_embryo_flush
        WHERE id = (
            -- Assuming linking via recipient tags is complicated,
            -- usually this is done by matching a recent flush record
            -- For now, this mimics the logic in your ServiceImpl
            SELECT id FROM sc_embryo_flush WHERE donor_female_no LIKE concat('%', #{manageTags}, '%')
            ORDER BY flush_time DESC LIMIT 1
            )
        -- Note: If your sc_embryo_flush structure is different (one row per recipient), adjust accordingly.
        -- Based on provided file, it has `recipient_cnt`.
    </select>
    <delete id="deleteScBreedRecordById" parameterType="Long">
        delete from sc_breed_record where id = #{id}
    </delete>

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

    <!-- 根据羊只ID和配种时间查询配种记录 -->
    <select id="selectBreedRecordByMatingTime" resultMap="ScBreedRecordResult">
        <include refid="selectScBreedRecordVo"/>
        <where>
            <if test="sheepId != null">and br.ewe_id = #{sheepId}</if>
            <if test="matingDateStart != null and matingDateStart != ''">
                and br.create_time >= #{matingDateStart}
            </if>
            <if test="matingDateEnd != null and matingDateEnd != ''">
                and br.create_time &lt;= #{matingDateEnd}
            </if>
        </where>
        order by br.create_time desc
    </select>

    <!-- 更新配种记录的孕检信息 -->
    <update id="updatePregnancyInfo">
        update sc_breed_record
        set pregnancy_record_id = #{pregnancyRecordId}
        where id = #{breedRecordId}
    </update>

    <!-- 如果还需要更新配种次数（可选） -->
    <update id="incrementSheepMatingCount">
        update bas_sheep
        set mating_counts = IFNULL(mating_counts, 0) + 1,
            mating_total = IFNULL(mating_total, 0) + 1,
            mating_date = #{matingDate},
            update_time = NOW(),
            update_by = #{updateBy}
        where id = #{sheepId}
          and is_delete = 0
    </update>
    <!-- ====================== ScBreedRecordMapper.xml 需要添加 ====================== -->
    <!-- 统计该公羊配了多少母羊（去重） -->
    <select id="countMatedEwesByRamId" resultType="Long">
        SELECT COUNT(DISTINCT ewe_id)
        FROM sc_breed_record
        WHERE ram_id = #{ramManageTags} AND is_delete = 0
    </select>

</mapper>