<?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.dairyProducts.mapper.NpSheepMilkAnalysisMapper">

    <!-- 1) 获取distinct sheep_id（支持按sheep_file.bs_manage_tags模糊搜索） -->
    <select id="selectDistinctSheepIds" resultType="string" parameterType="map">
        SELECT DISTINCT a.sheep_id
        FROM np_milk_prod_classes a
        LEFT JOIN sheep_file sf ON a.sheep_id = sf.id
        <where>
            <if test="manageEarTag != null and manageEarTag != ''">
                AND sf.bs_manage_tags LIKE CONCAT('%', #{manageEarTag}, '%')
            </if>
        </where>
        ORDER BY a.sheep_id
    </select>

    <!-- 2) 获取某只羊的所有班次记录（按班次日期升序） -->
    <!-- 假设表中字段：class_date, system_milk, corrected_milk, parity -->
    <select id="selectMilkRecordsBySheepId" resultType="map" parameterType="string">
        SELECT
            datetime AS classDate,
            milk AS systemMilk,
            corrected_milk AS correctedMilk,
            classes AS parity
        FROM np_milk_prod_classes
        WHERE sheep_id = #{sheepId}
        ORDER BY datetime ASC
    </select>

    <!-- 3) 获取该羊在sheep_file视图中的基础信息 -->
    <!-- 假设字段名称：bs_manage_tags、variety、lactation_day、name、birthday、parity、month_age、current_weight、breed、father_manage_tags、mother_manage_tags、dr_ranch、family -->
    <select id="selectSheepFileBySheepId" resultType="map" parameterType="string">
        SELECT
            id AS sheep_id,
            bs_manage_tags AS manageEarTag,
            variety,
            lactation_day AS lactationDay,
            name AS sheepType,
            birthday,
            parity AS currentParity,
            month_age AS monthAge,
            current_weight AS currentWeight,
            breed AS breedStatus,
            father_manage_tags AS fatherManageTags,
            mother_manage_tags AS motherManageTags,
            dr_ranch AS ranchName,
            family
        FROM sheep_file
        WHERE id = #{sheepId}
    </select>

    <!-- 4) 兼容旧list查询（返回domain对象列表），但在我们的实现中service会构造最终的NpSheepMilkAnalysis列表 -->
    <select id="selectNpSheepMilkAnalysisList" resultType="com.zhyc.module.dairyProducts.domain.NpSheepMilkAnalysis" parameterType="com.zhyc.module.dairyProducts.domain.NpSheepMilkAnalysis">
        <!-- 如果你仍需基于某张表的简单映射，可在此实现；当前我们在ServiceImpl中组装对象，所以该查询不做复杂实现 -->
        SELECT 1 FROM dual WHERE 1=0
    </select>

    <!-- 5) 导出奶产量分析记录 -->
    <select id="selectNpSheepMilkAnalysisForExport" resultType="com.zhyc.module.dairyProducts.domain.NpSheepMilkAnalysis" parameterType="com.zhyc.module.dairyProducts.domain.NpSheepMilkAnalysis">
        <!-- 这里需要根据实际表结构编写SQL查询 -->
        <!-- 示例SQL，需要根据实际表结构调整 -->
        SELECT
        sf.id as sheepId,
        sf.bs_manage_tags as manageEarTag,
        sf.variety,
        <!-- 其他字段 -->
        FROM sheep_file sf
        LEFT JOIN np_milk_prod_classes m ON sf.id = m.sheep_id
        <where>
            <if test="manageEarTag != null and manageEarTag != ''">
                AND sf.bs_manage_tags LIKE CONCAT('%', #{manageEarTag}, '%')
            </if>
        </where>
        GROUP BY sf.id
    </select>

</mapper>