<?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="ranch"    />
        <result property="daysAfterMating"    column="days_after_mating"    />
    </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 ranch,
            -- 关联配种信息
            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,
            -- 计算配后天数：孕检日期 - 配种日期
            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
            -- 关联配种记录表，获取最新的配种记录
                 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
            -- 关联公羊信息
                 left join sheep_file ram_sf on br.ram_id = ram_sf.id
            -- 关联配种类型字典
                 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>

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

            <!-- 耳号搜索 - 支持多种匹配方式 -->
            <if test="manageTags != null and manageTags != ''">
                <bind name="searchTag" value="manageTags.trim()" />
                AND (
                <!-- 精确匹配 -->
                sf.bs_manage_tags = #{searchTag}
                <!-- 模糊匹配 -->
                OR sf.bs_manage_tags LIKE CONCAT('%', #{searchTag}, '%')
                <!-- 处理逗号分隔的多个耳号 -->
                OR FIND_IN_SET(sf.bs_manage_tags, REPLACE(#{searchTag}, ' ', '')) > 0
                <!-- 反向匹配：输入包含数据库中的耳号 -->
                OR #{searchTag} LIKE CONCAT('%', sf.bs_manage_tags, '%')
                )
            </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,
            -- 从配种记录表获取公羊耳号
            ram_sf.bs_manage_tags as fatherManageTags,
            ram_sf.variety as fatherVariety,
            br.create_time as matingDate,
            mating_type.dict_label as matingTypeName,
            br.technician as breedTechnician,
            br.create_time as breedCreateTime
        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 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'
        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>
            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>
            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>
        </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>
</mapper>