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

    <!-- 基础结果映射 -->
    <resultMap type="ScLambingRecord" id="ScLambingRecordResult">
        <result property="id"    column="id"    />
        <result property="sheepId"    column="sheep_id"    />
        <result property="parity"    column="parity"    />
        <result property="lambsBorn"    column="lambs_born"    />
        <result property="survival"    column="survival"    />
        <result property="technician"    column="technician"    />
        <result property="score"    column="score"    />
        <result property="comment"    column="comment"    />
        <result property="createBy"    column="create_by"    />
        <!-- 修复：数据库字段是create_tme，不是create_time -->
        <result property="createTime"    column="create_tme"    />
    </resultMap>

    <!-- 详细结果映射（包含关联信息） -->
    <resultMap type="ScLambingRecord" id="ScLambingRecordDetailResult">
        <result property="id"    column="id"    />
        <result property="sheepId"    column="sheep_id"    />
        <result property="parity"    column="parity"    />
        <result property="lambsBorn"    column="lambs_born"    />
        <result property="survival"    column="survival"    />
        <result property="technician"    column="technician"    />
        <result property="score"    column="score"    />
        <result property="comment"    column="comment"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_tme"    />

        <!-- 母羊信息 -->
        <result property="femaleEarNumber"    column="female_ear_number"    />
        <result property="femaleBreed"    column="female_breed"    />
        <result property="monthAge"    column="month_age"    />
        <result property="currentShed"    column="current_shed"    />
        <result property="farm"    column="farm"    />

        <!-- 公羊信息 -->
        <result property="maleEarNumber"    column="male_ear_number"    />
        <result property="maleBreed"    column="male_breed"    />

        <!-- 配种信息 -->
        <result property="breedingDate"    column="breeding_date"    />
        <result property="pregnancyDays"    column="pregnancy_days"    />

        <!-- 统计信息 -->
        <result property="maleCount"    column="male_count"    />
        <result property="femaleCount"    column="female_count"    />
        <result property="retainedMaleCount"    column="retained_male_count"    />
        <result property="retainedFemaleCount"    column="retained_female_count"    />
        <result property="unretainedMaleCount"    column="unretained_male_count"    />
        <result property="unretainedFemaleCount"    column="unretained_female_count"    />
    </resultMap>

    <!-- 基础查询SQL -->
    <sql id="selectScLambingRecordVo">
        select id, sheep_id, parity, lambs_born, survival, technician, score, comment, create_by, create_tme from sc_lambing_record
    </sql>

    <!-- 详细查询SQL（包含关联信息） -->
    <sql id="selectScLambingRecordDetailVo">
        SELECT
            lr.id, lr.sheep_id, lr.parity, lr.lambs_born, lr.survival,
            lr.technician, lr.score, lr.comment, lr.create_by, lr.create_tme,

            -- 从sheep_file视图获取母羊信息
            mother.bs_manage_tags as female_ear_number,
            mother.variety as female_breed,
            mother.month_age as month_age,
            mother.sheepfold_name as current_shed,
            mother.dr_ranch as farm,

            -- 从sc_breed_record表获取配种信息
            br.create_time as breeding_date,
            DATEDIFF(CURDATE(), br.create_time) as pregnancy_days,

            -- 从sheep_file视图获取公羊信息
            father.bs_manage_tags as male_ear_number,
            father.variety as male_breed,

            -- 统计羔羊信息（从sc_lamb_detail表统计）
            (SELECT COUNT(*) FROM sc_lamb_detail sld WHERE sld.lambing_record_id = lr.id AND sld.gender = 1) as male_count,
            (SELECT COUNT(*) FROM sc_lamb_detail sld WHERE sld.lambing_record_id = lr.id AND sld.gender = 0) as female_count,
            (SELECT COUNT(*) FROM sc_lamb_detail sld WHERE sld.lambing_record_id = lr.id AND sld.gender = 1 AND sld.is_retained = 1) as retained_male_count,
            (SELECT COUNT(*) FROM sc_lamb_detail sld WHERE sld.lambing_record_id = lr.id AND sld.gender = 0 AND sld.is_retained = 1) as retained_female_count,
            (SELECT COUNT(*) FROM sc_lamb_detail sld WHERE sld.lambing_record_id = lr.id AND sld.gender = 1 AND sld.is_retained = 0) as unretained_male_count,
            (SELECT COUNT(*) FROM sc_lamb_detail sld WHERE sld.lambing_record_id = lr.id AND sld.gender = 0 AND sld.is_retained = 0) as unretained_female_count

        FROM sc_lambing_record lr
                 LEFT JOIN sheep_file mother ON lr.sheep_id = mother.id
                 LEFT JOIN sc_breed_record br ON lr.sheep_id = br.ewe_id AND br.create_time = (
            SELECT MAX(br2.create_time)
            FROM sc_breed_record br2
            WHERE br2.ewe_id = lr.sheep_id
              AND br2.create_time &lt;= lr.create_tme
        )
                 LEFT JOIN sheep_file father ON br.ram_id = father.id
    </sql>

    <!-- 查询产羔记录列表（包含关联信息） -->
    <select id="selectScLambingRecordList" parameterType="ScLambingRecord" resultMap="ScLambingRecordDetailResult">
        <include refid="selectScLambingRecordDetailVo"/>
        <where>
            <if test="femaleEarNumber != null and femaleEarNumber != ''"> and mother.bs_manage_tags LIKE CONCAT('%', #{femaleEarNumber}, '%')</if>
            <if test="femaleBreed != null and femaleBreed != ''"> and mother.variety LIKE CONCAT('%', #{femaleBreed}, '%')</if>
            <if test="farm != null and farm != ''"> and mother.dr_ranch LIKE CONCAT('%', #{farm}, '%')</if>
            <if test="sheepId != null and sheepId != ''"> and lr.sheep_id = #{sheepId}</if>
            <if test="parity != null"> and lr.parity = #{parity}</if>
            <if test="lambsBorn != null"> and lr.lambs_born = #{lambsBorn}</if>
            <if test="survival != null"> and lr.survival = #{survival}</if>
            <if test="technician != null and technician != ''"> and lr.technician LIKE CONCAT('%', #{technician}, '%')</if>
            <if test="score != null"> and lr.score = #{score}</if>
            <if test="comment != null and comment != ''"> and lr.comment LIKE CONCAT('%', #{comment}, '%')</if>
            <if test="createBy != null and createBy != ''"> and lr.create_by = #{createBy}</if>
            <if test="createTime != null"> and DATE(lr.create_tme) = #{createTime}</if>
            <if test="params.beginBreedingDate != null and params.beginBreedingDate != ''"><!-- 配种日期开始 -->
                and DATE(br.create_time) &gt;= #{params.beginBreedingDate}
            </if>
            <if test="params.endBreedingDate != null and params.endBreedingDate != ''"><!-- 配种日期结束 -->
                and DATE(br.create_time) &lt;= #{params.endBreedingDate}
            </if>
            and mother.is_delete = 0
        </where>
        ORDER BY lr.create_tme DESC
    </select>

    <!-- 查询产羔记录基础信息 -->
    <select id="selectScLambingRecordById" parameterType="Long" resultMap="ScLambingRecordResult">
        <include refid="selectScLambingRecordVo"/>
        where id = #{id}
    </select>

    <!-- 查询产羔记录详细信息（包含关联信息） -->
    <select id="selectScLambingRecordDetailById" parameterType="Long" resultMap="ScLambingRecordDetailResult">
        <include refid="selectScLambingRecordDetailVo"/>
        where lr.id = #{id} and mother.is_delete = 0
    </select>

    <!-- 根据母羊耳号查询最新配种记录 -->
    <select id="getLatestBreedingByEarNumber" parameterType="String" resultType="java.util.Map">
        SELECT
            sf.id as sheep_id,
            sf.bs_manage_tags as female_ear_number,
            sf.variety as female_breed,
            sf.parity as parity,
            br.ram_id as ram_id,
            ram.bs_manage_tags as male_ear_number,
            ram.variety as male_breed,
            br.create_time as breeding_date,
            DATEDIFF(CURDATE(), br.create_time) as pregnancy_days,
            br.technician as technician
        FROM sheep_file sf
                 LEFT JOIN sc_breed_record br ON sf.id = br.ewe_id
                 LEFT JOIN sheep_file ram ON br.ram_id = ram.id
        WHERE sf.bs_manage_tags = #{earNumber}
          AND sf.is_delete = 0
          AND br.create_time = (
            SELECT MAX(br2.create_time)
            FROM sc_breed_record br2
            WHERE br2.ewe_id = sf.id
        )
    </select>

    <!-- 新增产羔记录 -->
    <insert id="insertScLambingRecord" parameterType="ScLambingRecord" useGeneratedKeys="true" keyProperty="id">
        insert into sc_lambing_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">sheep_id,</if>
            <if test="parity != null">parity,</if>
            <if test="lambsBorn != null">lambs_born,</if>
            <if test="survival != null">survival,</if>
            <if test="technician != null">technician,</if>
            <if test="score != null">score,</if>
            <if test="comment != null">comment,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_tme,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="sheepId != null">#{sheepId},</if>
            <if test="parity != null">#{parity},</if>
            <if test="lambsBorn != null">#{lambsBorn},</if>
            <if test="survival != null">#{survival},</if>
            <if test="technician != null">#{technician},</if>
            <if test="score != null">#{score},</if>
            <if test="comment != null">#{comment},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
        </trim>
    </insert>

    <!-- 修改产羔记录 -->
    <update id="updateScLambingRecord" parameterType="ScLambingRecord">
        update sc_lambing_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="sheepId != null">sheep_id = #{sheepId},</if>
            <if test="parity != null">parity = #{parity},</if>
            <if test="lambsBorn != null">lambs_born = #{lambsBorn},</if>
            <if test="survival != null">survival = #{survival},</if>
            <if test="technician != null">technician = #{technician},</if>
            <if test="score != null">score = #{score},</if>
            <if test="comment != null">comment = #{comment},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>

    <!-- 删除产羔记录 -->
    <delete id="deleteScLambingRecordById" parameterType="Long">
        delete from sc_lambing_record where id = #{id}
    </delete>

    <!-- 批量删除产羔记录 -->
    <delete id="deleteScLambingRecordByIds" parameterType="String">
        delete from sc_lambing_record where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- ====================== ScLambingRecordMapper.xml 需要添加 ====================== -->
    <!-- 统计公羊的后代数量 -->
    <select id="countOffspringByRamId" resultType="Long">
        SELECT IFNULL(SUM(lr.lambs_born), 0)
        FROM sc_breed_record br
                 JOIN sc_lambing_record lr ON br.ewe_id = lr.sheep_id
        WHERE br.ram_id = #{ramManageTags} AND br.is_delete = 0
    </select>
</mapper>