<?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.ScPregnancyRecordMapper">

    <resultMap type="ScPregnancyRecord" id="ScPregnancyRecordResult">
        <result property="id"    column="id"    />
        <result property="sheepId"    column="sheep_id"    />
        <result property="manageTags"    column="manage_tags"    />
        <result property="datetime"    column="datetime"    />
        <result property="result"    column="result"    />
        <result property="fetusCount"    column="fetus_count"    />
        <result property="technician"    column="technician"    />
        <result property="way"    column="way"    />
        <result property="remark"    column="remark"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <!-- 关联羊只信息字段 -->
        <result property="variety"    column="variety"    />
        <result property="monthAge"    column="month_age"    />
        <result property="parity"    column="parity"    />
        <result property="matingCounts"    column="mating_counts"    />
        <result property="sheepfoldName"    column="sheepfold_name"    />
        <result property="breedStatus"    column="breed"    />
        <result property="fatherManageTags"    column="father_manage_tags"    />
        <result property="fatherVariety"    column="father_variety"    />
        <result property="matingTypeName"    column="mating_type_name"    />
        <result property="matingDate"    column="mating_date"    />
        <result property="expectedDate"    column="expected_date"    />
        <result property="lastEventDate"    column="last_event_date"    />
        <result property="ranchName"    column="sysRanch"    />
        <result property="daysAfterMating"    column="days_after_mating"    />
        <result property="reason" column="reason" />
        <result property="donorEwe" column="donor_ewe" />
        <result property="donorRam" column="donor_ram" />
        <result property="embryoCount" column="embryo_count" />
        <result property="donorEweVariety" column="donor_ewe_variety" />
        <result property="donorRamVariety" column="donor_ram_variety" />
    </resultMap>

<!--    <sql id="selectScPregnancyRecordVo">-->
<!--        select-->
<!--            pr.id,-->
<!--            pr.sheep_id,-->
<!--            pr.datetime,-->
<!--            pr.result,-->
<!--            pr.fetus_count,-->
<!--            pr.technician,-->
<!--            pr.way,-->
<!--            pr.remark,-->
<!--            pr.create_by,-->
<!--            pr.create_time,-->
<!--            sf.bs_manage_tags as manage_tags,-->
<!--            sf.variety,-->
<!--            sf.month_age,-->
<!--            sf.parity,-->
<!--            sf.mating_counts,-->
<!--            sf.sheepfold_name,-->
<!--            sf.breed,-->
<!--            sf.expected_date,-->
<!--            sf.lambing_date as last_event_date,-->
<!--            sf.dr_ranch as sysRanch,-->

<!--        &#45;&#45; 1. 上次孕检日期逻辑-->
<!--        (SELECT MAX(datetime) FROM sc_pregnancy_record-->
<!--        WHERE sheep_id = pr.sheep_id AND datetime &lt; pr.datetime AND is_delete = 0) as last_event_date,-->
<!--        &#45;&#45; 2. 配种基础信息-->
<!--        br.create_time as mating_date,-->
<!--        br.embryo_count,-->
<!--        br.donor_ewe,-->
<!--        br.donor_ram,-->
<!--        &#45;&#45; 3. 配种类型映射逻辑-->
<!--        CASE br.breed_type-->
<!--        WHEN 1 THEN '供体母羊配种'-->
<!--        WHEN 2 THEN '同期发情人工授精'-->
<!--        WHEN 3 THEN '本交'-->
<!--        WHEN 4 THEN '自然发情人工授精'-->
<!--        WHEN 5 THEN '胚胎移植'-->
<!--        ELSE '' END as mating_type_name,-->
<!--        &#45;&#45; 4. 配种公羊品种-->
<!--        ram_sf.bs_manage_tags as father_manage_tags,-->
<!--        ram_sf.variety as father_variety,-->
<!--        &#45;&#45; 5. 供体品种逻辑 (再次关联羊档案)-->
<!--        donor_e_sf.variety as donor_ewe_variety,-->
<!--        donor_r_sf.variety as donor_ram_variety-->
<!--        FROM sc_pregnancy_record pr-->
<!--        LEFT JOIN sheep_file sf ON pr.sheep_id = sf.id-->
<!--        &#45;&#45; 关联最新的配种记录-->
<!--        LEFT JOIN (-->
<!--        SELECT t1.* FROM sc_breed_record t1-->
<!--        INNER JOIN (SELECT ewe_id, MAX(create_time) as mt FROM sc_breed_record GROUP BY ewe_id) t2-->
<!--        ON t1.ewe_id = t2.ewe_id AND t1.create_time = t2.mt-->
<!--        ) br ON pr.sheep_id = br.ewe_id-->
<!--        &#45;&#45; 关联配种公羊品种-->
<!--        LEFT JOIN sheep_file ram_sf ON br.ram_id = ram_sf.id-->
<!--        &#45;&#45; 关联供体母羊品种 (基于耳号)-->
<!--        LEFT JOIN sheep_file donor_e_sf ON br.donor_ewe = donor_e_sf.bs_manage_tags-->
<!--        &#45;&#45; 关联供体公羊品种 (基于耳号)-->
<!--        LEFT JOIN sheep_file donor_r_sf ON br.donor_ram = donor_r_sf.bs_manage_tags-->
<!--            &#45;&#45; 关联配种信息-->
<!--            ram_sf.bs_manage_tags as father_manage_tags,-->
<!--            ram_sf.variety as father_variety,-->
<!--            mating_type.dict_label as mating_type_name,-->
<!--            COALESCE(br.create_time, sf.mating_date) as mating_date,-->
<!--            &#45;&#45; 计算配后天数：孕检日期 - 配种日期-->
<!--            CASE-->
<!--                WHEN COALESCE(br.create_time, sf.mating_date) IS NOT NULL-->
<!--                    THEN DATEDIFF(pr.datetime, COALESCE(br.create_time, sf.mating_date))-->
<!--                ELSE NULL-->
<!--                END as days_after_mating-->
<!--        from sc_pregnancy_record pr-->
<!--                 INNER JOIN sheep_file sf on pr.sheep_id = sf.id-->
<!--            &#45;&#45; 关联配种记录表，获取最新的配种记录-->
<!--                 left join (-->
<!--            select br1.*-->
<!--            from sc_breed_record br1-->
<!--                     inner join (-->
<!--                select ewe_id, max(create_time) as max_time-->
<!--                from sc_breed_record-->
<!--                group by ewe_id-->
<!--            ) br2 on br1.ewe_id = br2.ewe_id and br1.create_time = br2.max_time-->
<!--        ) br on sf.id = br.ewe_id-->
<!--            &#45;&#45; 关联公羊信息-->
<!--                 left join sheep_file ram_sf on br.ram_id = ram_sf.id-->
<!--            &#45;&#45; 关联配种类型字典-->
<!--                 left join sys_dict_data mating_type on sf.mating_type_id = mating_type.dict_value-->
<!--            and mating_type.dict_type = 'breed_type' and mating_type.status = '0'-->
<!--    </sql>-->


    <sql id="selectScPregnancyRecordVo">
        select
            pr.id, pr.sheep_id, pr.datetime, pr.result, pr.fetus_count, pr.technician, pr.way, pr.remark, pr.create_by, pr.create_time,pr.reason,
            sf.bs_manage_tags as manage_tags, sf.variety, sf.month_age, sf.parity, sf.mating_counts, sf.sheepfold_name, sf.breed, sf.expected_date, sf.dr_ranch as sysRanch,
            (SELECT MAX(datetime) FROM sc_pregnancy_record WHERE sheep_id = pr.sheep_id AND datetime &lt; pr.datetime AND is_delete = 0) as last_event_date,
            br.create_time as mating_date,
            br.embryo_count, br.donor_ewe, br.donor_ram,
            mating_type.dict_label as mating_type_name,
            ram_sf.bs_manage_tags as father_manage_tags,
            ram_sf.variety as father_variety,
            donor_e_sf.variety as donor_ewe_variety,
            donor_r_sf.variety as donor_ram_variety,
            DATEDIFF(pr.datetime, br.create_time) as days_after_mating
        from sc_pregnancy_record pr
                 LEFT JOIN sheep_file sf on pr.sheep_id = sf.id
                 LEFT JOIN (
            select t1.* from sc_breed_record t1
                                 inner join (select ewe_id, max(create_time) as mt from sc_breed_record group by ewe_id) t2
                                            on t1.ewe_id = t2.ewe_id and t1.create_time = t2.mt
        ) br on pr.sheep_id = br.ewe_id
                 LEFT JOIN sheep_file ram_sf on br.ram_id = ram_sf.id
                 LEFT JOIN sys_dict_data mating_type on CAST(br.breed_type AS CHAR) = mating_type.dict_value
            and mating_type.dict_type = 'breed_type' and mating_type.status = '0'
                 LEFT JOIN sheep_file donor_e_sf on br.donor_ewe = donor_e_sf.bs_manage_tags
                 LEFT JOIN sheep_file donor_r_sf on br.donor_ram = donor_r_sf.bs_manage_tags
    </sql>

    <!-- 重写的查询列表方法 -->
    <select id="selectScPregnancyRecordList" parameterType="ScPregnancyRecord" resultMap="ScPregnancyRecordResult">
        <include refid="selectScPregnancyRecordVo"/>

        <where>
            pr.is_delete = 0

            <!-- 正确：sf.bs_manage_tags，sf是sheep_file的别名 -->
            <if test="allEarNumbers != null and allEarNumbers.size() > 0">
                AND (sf.bs_manage_tags IN
                <foreach collection="allEarNumbers" item="earNumber" open="(" separator="," close=")">
                    #{earNumber}
                </foreach>
                )
            </if>

            <if test="datetime != null">
                AND DATE(pr.datetime) = DATE(#{datetime})
            </if>
            <if test="result != null  and result != ''">
                AND pr.result = #{result}
            </if>
            <if test="technician != null  and technician != ''">
                AND pr.technician like concat('%', #{technician}, '%')
            </if>
            <if test="way != null and way != ''">
                AND pr.way = #{way}
            </if>
        </where>
        order by pr.create_time desc
    </select>

    <select id="selectScPregnancyRecordById" parameterType="Long" resultMap="ScPregnancyRecordResult">
        <include refid="selectScPregnancyRecordVo"/>
        where pr.id = #{id} and pr.is_delete = 0
    </select>

    <!-- 根据耳号查询羊只信息 -->
    <select id="selectSheepByManageTags" parameterType="String" resultType="map">
        select
            id,
            bs_manage_tags as manageTags,
            variety,
            month_age as monthAge,
            parity,
            mating_counts as matingCounts,
            sheepfold_name as sheepfoldName,
            breed as breedStatus
        from sheep_file
        where bs_manage_tags = #{manageTags}
          and is_delete = 0
            limit 1
    </select>

    <!-- 根据耳号获取配种信息 -->
    <select id="selectBreedInfoByManageTags" parameterType="String" resultType="map">
        SELECT
            sf.bs_manage_tags as manageTags,
            br.donor_ewe as donorEwe,
            br.donor_ram as donorRam,
            donor_e_sf.variety as donorEweVariety,
            donor_r_sf.variety as donorRamVariety,
            -- 核心修改：如果是胚胎移植(5)，显示供体公羊；否则显示普通配种公羊
            CASE
                WHEN br.breed_type = 5 THEN br.donor_ram
                ELSE ram_sf.bs_manage_tags
                END as fatherManageTags,
            -- 品种也需要同步切换
            CASE
                WHEN br.breed_type = 5 THEN donor_r_sf.variety
                ELSE ram_sf.variety
                END as fatherVariety,
            br.create_time as matingDate,
            mating_type.dict_label as matingTypeName,
            br.technician as breedTechnician
        FROM sheep_file sf
                 LEFT JOIN sc_breed_record br ON sf.id = br.ewe_id
                 LEFT JOIN sheep_file ram_sf ON br.ram_id = ram_sf.id
            -- 新增关联：通过耳号获取供体公羊的品种信息
                 LEFT JOIN sheep_file donor_r_sf ON br.donor_ram = donor_r_sf.bs_manage_tags
                 LEFT JOIN sheep_file donor_e_sf ON br.donor_ewe = donor_e_sf.bs_manage_tags
                 LEFT JOIN sys_dict_data mating_type ON CAST(br.breed_type AS CHAR) = mating_type.dict_value
            AND mating_type.dict_type = 'breed_type' AND mating_type.status = '0'
        WHERE sf.bs_manage_tags = #{manageTags} AND sf.is_delete = 0
        ORDER BY br.create_time DESC LIMIT 1
    </select>

    <insert id="insertScPregnancyRecord" parameterType="ScPregnancyRecord" useGeneratedKeys="true" keyProperty="id">
        insert into sc_pregnancy_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">sheep_id,</if>
            <if test="datetime != null">datetime,</if>
            <if test="result != null">result,</if>
            <if test="fetusCount != null">fetus_count,</if>
            <if test="technician != null">technician,</if>
            <if test="way != null">way,</if>
            <if test="remark != null">remark,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="reason != null">reason,</if>
            is_delete
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">#{sheepId},</if>
            <if test="datetime != null">#{datetime},</if>
            <if test="result != null">#{result},</if>
            <if test="fetusCount != null">#{fetusCount},</if>
            <if test="technician != null">#{technician},</if>
            <if test="way != null">#{way},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="reason != null">#{reason},</if>
            0
        </trim>
    </insert>

    <update id="updateScPregnancyRecord" parameterType="ScPregnancyRecord">
        update sc_pregnancy_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="datetime != null">datetime = #{datetime},</if>
            <if test="result != null">result = #{result},</if>
            <if test="fetusCount != null">fetus_count = #{fetusCount},</if>
            <if test="technician != null">technician = #{technician},</if>
            <if test="way != null">way = #{way},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="reason != null">reason = #{reason},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="deleteScPregnancyRecordById" parameterType="Long">
        update sc_pregnancy_record set is_delete = 1 where id = #{id}
    </update>

    <update id="deleteScPregnancyRecordByIds" parameterType="String">
        update sc_pregnancy_record set is_delete = 1 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <!-- 更新羊只基础表中的孕检相关字段 -->
    <update id="updateSheepPregnancyInfo" parameterType="map">
        update bas_sheep
        <set>
            <if test="pregDate != null">preg_date = #{pregDate},</if>
            <if test="breedStatusId != null">breed_status_id = #{breedStatusId},</if>
            <if test="expectedDate != null">expected_date = #{expectedDate},</if>
            <if test="gestationDay != null">gestation_day = #{gestationDay},</if>
        </set>
        where id = #{sheepId}
    </update>

    <!-- ====================== ScPregnancyRecordMapper.xml 需要添加 ====================== -->
    <!-- 统计孕检有胎的母羊数（按配种方式） -->
    <select id="countPregnantEwesByRamIdAndBreedType" resultType="Long">
        SELECT COUNT(DISTINCT pr.sheep_id)
        FROM sc_breed_record br
                 JOIN sc_pregnancy_record pr ON br.ewe_id = pr.sheep_id
        WHERE br.ram_id = #{ramManageTags}
          AND br.is_delete = 0
          AND pr.is_delete = 0
          AND br.breed_type = #{breedType}
          AND pr.result IN ('有胎', '阳性', '怀孕')
    </select>

    <!-- 模糊查询耳号列表 -->
    <select id="searchEarNumbers" resultType="java.lang.String">
        SELECT DISTINCT sf.bs_manage_tags
        FROM sheep_file sf
        WHERE sf.bs_manage_tags LIKE CONCAT(#{query}, '%')
          AND sf.is_delete = 0
        ORDER BY sf.bs_manage_tags
            LIMIT 50
    </select>
</mapper>