<?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.ruoyi.education.mapper.EduCourseClassMapper">
    
    <resultMap type="EduCourseClass" id="EduCourseClassResult">
        <result property="classId"          column="class_id"          />
        <result property="courseId"         column="course_id"         />
        <result property="semesterId"       column="semester_id"       />
        <result property="teacherId"        column="teacher_id"        />
        <result property="classCode"        column="class_code"        />
        <result property="capacity"         column="capacity"          />
        <result property="enrollLimit"      column="enroll_limit"      />
        <result property="enrolledCount"    column="enrolled_count"    />
        <result property="classroomId"      column="classroom_id"      />
        <result property="enrollStartTime"  column="enroll_start_time" />
        <result property="enrollEndTime"    column="enroll_end_time"   />
        <result property="teachStartDate"   column="teach_start_date"  />
        <result property="teachEndDate"     column="teach_end_date"    />
        <result property="weekday"          column="weekday"           />
        <result property="timeSlot"         column="time_slot"         />
        <result property="status"           column="status"            />
        <result property="remark"           column="remark"            />
        <result property="createBy"         column="create_by"         />
        <result property="createTime"       column="create_time"       />
        <result property="updateBy"         column="update_by"         />
        <result property="updateTime"       column="update_time"       />
        <result property="courseName"       column="course_name"       />
        <result property="courseCode"       column="course_code"       />
        <result property="teacherName"      column="teacher_name"      />
        <result property="semesterName"     column="semester_name"     />
        <result property="classroomName"    column="classroom_name"    />
    </resultMap>

    <sql id="selectEduCourseClassVo">
        select c.class_id, c.course_id, c.semester_id, c.teacher_id, c.class_code, c.capacity, 
               c.enroll_limit, c.enrolled_count, c.classroom_id, c.enroll_start_time, c.enroll_end_time, 
               c.teach_start_date, c.teach_end_date, c.weekday, c.time_slot, c.status, c.remark, 
               c.create_by, c.create_time, c.update_by, c.update_time,
               co.course_name, co.course_code,
               t.real_name as teacher_name,
               s.semester_name,
               CONCAT(cr.building, cr.room_no) as classroom_name
        from edu_course_class c
        left join edu_course co on c.course_id = co.course_id
        left join edu_teacher t on c.teacher_id = t.teacher_id
        left join edu_semester s on c.semester_id = s.semester_id
        left join edu_classroom cr on c.classroom_id = cr.classroom_id
    </sql>

    <select id="selectEduCourseClassList" parameterType="EduCourseClass" resultMap="EduCourseClassResult">
        <include refid="selectEduCourseClassVo"/>
        <where>  
            <if test="courseId != null "> and c.course_id = #{courseId}</if>
            <if test="semesterId != null "> and c.semester_id = #{semesterId}</if>
            <if test="teacherId != null "> and c.teacher_id = #{teacherId}</if>
            <if test="classCode != null  and classCode != ''"> and c.class_code like concat('%', #{classCode}, '%')</if>
            <if test="capacity != null "> and c.capacity = #{capacity}</if>
            <if test="enrollLimit != null "> and c.enroll_limit = #{enrollLimit}</if>
            <if test="enrolledCount != null "> and c.enrolled_count = #{enrolledCount}</if>
            <if test="classroomId != null "> and c.classroom_id = #{classroomId}</if>
            <if test="enrollStartTime != null "> and c.enroll_start_time = #{enrollStartTime}</if>
            <if test="enrollEndTime != null "> and c.enroll_end_time = #{enrollEndTime}</if>
            <if test="teachStartDate != null "> and c.teach_start_date = #{teachStartDate}</if>
            <if test="teachEndDate != null "> and c.teach_end_date = #{teachEndDate}</if>
            <if test="weekday != null  and weekday != ''"> and c.weekday = #{weekday}</if>
            <if test="timeSlot != null  and timeSlot != ''"> and c.time_slot = #{timeSlot}</if>
            <if test="status != null  and status != ''"> and c.status = #{status}</if>
            <if test="courseName != null  and courseName != ''"> and co.course_name like concat('%', #{courseName}, '%')</if>
            <if test="courseCode != null  and courseCode != ''"> and co.course_code like concat('%', #{courseCode}, '%')</if>
            <if test="teacherName != null  and teacherName != ''"> and t.real_name like concat('%', #{teacherName}, '%')</if>
            <if test="semesterName != null  and semesterName != ''"> and s.semester_name like concat('%', #{semesterName}, '%')</if>
        </where>
        order by c.create_time desc
    </select>
    
    <select id="selectEduCourseClassByClassId" parameterType="Long" resultMap="EduCourseClassResult">
        <include refid="selectEduCourseClassVo"/>
        where c.class_id = #{classId}
    </select>

    <select id="selectEduCourseClassByClassCode" parameterType="String" resultMap="EduCourseClassResult">
        <include refid="selectEduCourseClassVo"/>
        where c.class_code = #{classCode}
    </select>

    <select id="selectCourseClassBySemester" parameterType="Long" resultMap="EduCourseClassResult">
        <include refid="selectEduCourseClassVo"/>
        where c.semester_id = #{semesterId}
        order by c.create_time desc
    </select>

    <select id="selectCourseClassByTeacher" parameterType="Long" resultMap="EduCourseClassResult">
        <include refid="selectEduCourseClassVo"/>
        where c.teacher_id = #{teacherId}
        order by c.create_time desc
    </select>

    <select id="selectEnrollableCourseClasses" resultMap="EduCourseClassResult">
        <include refid="selectEduCourseClassVo"/>
        where c.status = '1' 
        and NOW() BETWEEN c.enroll_start_time AND c.enroll_end_time
        and c.enrolled_count &lt; IFNULL(NULLIF(c.enroll_limit, 0), c.capacity)
        order by c.create_time desc
    </select>

    <select id="checkClassroomConflict" resultType="int">
        select count(*)
        from edu_course_class
        where classroom_id = #{classroomId}
        and semester_id = #{semesterId}
        and weekday = #{weekday}
        and time_slot = #{timeSlot}
        and status in ('1', '2')
        <if test="excludeClassId != null">
            and class_id != #{excludeClassId}
        </if>
    </select>

    <select id="checkTeacherConflict" resultType="int">
        select count(*)
        from edu_course_class
        where teacher_id = #{teacherId}
        and semester_id = #{semesterId}
        and weekday = #{weekday}
        and time_slot = #{timeSlot}
        and status in ('1', '2')
        <if test="excludeClassId != null">
            and class_id != #{excludeClassId}
        </if>
    </select>
        
    <insert id="insertEduCourseClass" parameterType="EduCourseClass" useGeneratedKeys="true" keyProperty="classId">
        insert into edu_course_class
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="courseId != null">course_id,</if>
            <if test="semesterId != null">semester_id,</if>
            <if test="teacherId != null">teacher_id,</if>
            <if test="classCode != null and classCode != ''">class_code,</if>
            <if test="capacity != null">capacity,</if>
            <if test="enrollLimit != null">enroll_limit,</if>
            <if test="enrolledCount != null">enrolled_count,</if>
            <if test="classroomId != null">classroom_id,</if>
            <if test="enrollStartTime != null">enroll_start_time,</if>
            <if test="enrollEndTime != null">enroll_end_time,</if>
            <if test="teachStartDate != null">teach_start_date,</if>
            <if test="teachEndDate != null">teach_end_date,</if>
            <if test="weekday != null">weekday,</if>
            <if test="timeSlot != null">time_slot,</if>
            <if test="status != null">status,</if>
            <if test="remark != null">remark,</if>
            <if test="createBy != null">create_by,</if>
            create_time
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="courseId != null">#{courseId},</if>
            <if test="semesterId != null">#{semesterId},</if>
            <if test="teacherId != null">#{teacherId},</if>
            <if test="classCode != null and classCode != ''">#{classCode},</if>
            <if test="capacity != null">#{capacity},</if>
            <if test="enrollLimit != null">#{enrollLimit},</if>
            <if test="enrolledCount != null">#{enrolledCount},</if>
            <if test="classroomId != null">#{classroomId},</if>
            <if test="enrollStartTime != null">#{enrollStartTime},</if>
            <if test="enrollEndTime != null">#{enrollEndTime},</if>
            <if test="teachStartDate != null">#{teachStartDate},</if>
            <if test="teachEndDate != null">#{teachEndDate},</if>
            <if test="weekday != null">#{weekday},</if>
            <if test="timeSlot != null">#{timeSlot},</if>
            <if test="status != null">#{status},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            sysdate()
         </trim>
    </insert>

    <update id="updateEduCourseClass" parameterType="EduCourseClass">
        update edu_course_class
        <trim prefix="SET" suffixOverrides=",">
            <if test="courseId != null">course_id = #{courseId},</if>
            <if test="semesterId != null">semester_id = #{semesterId},</if>
            <if test="teacherId != null">teacher_id = #{teacherId},</if>
            <if test="classCode != null and classCode != ''">class_code = #{classCode},</if>
            <if test="capacity != null">capacity = #{capacity},</if>
            <if test="enrollLimit != null">enroll_limit = #{enrollLimit},</if>
            <if test="enrolledCount != null">enrolled_count = #{enrolledCount},</if>
            <if test="classroomId != null">classroom_id = #{classroomId},</if>
            <if test="enrollStartTime != null">enroll_start_time = #{enrollStartTime},</if>
            <if test="enrollEndTime != null">enroll_end_time = #{enrollEndTime},</if>
            <if test="teachStartDate != null">teach_start_date = #{teachStartDate},</if>
            <if test="teachEndDate != null">teach_end_date = #{teachEndDate},</if>
            <if test="weekday != null">weekday = #{weekday},</if>
            <if test="timeSlot != null">time_slot = #{timeSlot},</if>
            <if test="status != null">status = #{status},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            update_time = sysdate()
        </trim>
        where class_id = #{classId}
    </update>

    <update id="updateEnrolledCount">
        update edu_course_class 
        set enrolled_count = #{enrolledCount},
            status = case 
                when #{enrolledCount} >= IFNULL(NULLIF(enroll_limit, 0), capacity) then '2'
                when #{enrolledCount} > 0 then '1'
                else status
            end
        where class_id = #{classId}
    </update>

    <update id="batchUpdateStatus">
        update edu_course_class set status = #{status}
        where class_id in 
        <foreach item="classId" collection="array" open="(" separator="," close=")">
            #{classId}
        </foreach>
    </update>

    <delete id="deleteEduCourseClassByClassId" parameterType="Long">
        delete from edu_course_class where class_id = #{classId}
    </delete>

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

    <!-- 获取课程选项 -->
    <select id="getCourseOptions" resultType="java.util.HashMap">
        select course_id as courseId, course_code as courseCode, course_name as courseName,
               course_type as courseType, credit, total_hours as totalHours
        from edu_course 
        where status = '0'
        order by course_code
    </select>

    <!-- 获取教师选项 -->
    <select id="getTeacherOptions" resultType="java.util.HashMap">
        select teacher_id as teacherId, teacher_no as teacherNo, real_name as realName,
               title, dept_id as deptId
        from edu_teacher 
        where status = '0'
        order by teacher_no
    </select>

    <!-- 获取教室选项 -->
    <select id="getClassroomOptions" resultType="java.util.HashMap">
        select classroom_id as classroomId, building, room_no as roomNo,
               capacity, room_type as roomType,
               CONCAT(building, room_no) as classroomName
        from edu_classroom 
        where status = '0'
        order by building, room_no
    </select>
</mapper>