<?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.qmth.themis.business.dao.TEExamActivityMapper">

    <select id="existTable" resultType="java.lang.Integer">
        select
            count(*)
        from
            information_schema.TABLES
        where
            LCASE(TABLE_NAME)= #{tableName}
        and TABLE_SCHEMA = #{schema}
    </select>

    <update id="dropTable">
        DROP TABLE IF EXISTS ${tableName}
    </update>

    <update id="createNewTable">
        CREATE TABLE ${tableName} (
          `id` bigint NOT NULL COMMENT '主键',
          `exam_id` bigint NOT NULL COMMENT '批次id',
          `code` varchar(50) NOT NULL COMMENT '场次代码',
          `prepare_seconds` int NOT NULL COMMENT '提前多长时间开始候考(秒)',
          `max_duration_seconds` int DEFAULT NULL COMMENT '最大考试时长',
          `enable` tinyint DEFAULT '1' COMMENT '是否启用',
          `opening_seconds` int DEFAULT NULL COMMENT '允许开考时长(秒)',
          `start_time` bigint DEFAULT NULL COMMENT '开考时间',
          `finish_time` bigint DEFAULT NULL COMMENT '结束时间',
          `create_time` bigint DEFAULT NULL COMMENT '创建时间',
          `update_time` bigint DEFAULT NULL COMMENT '更新时间',
          `create_id` bigint DEFAULT NULL COMMENT '创建人id',
          `update_id` bigint DEFAULT NULL COMMENT '更新人id',
          PRIMARY KEY (`id`),
          UNIQUE KEY `${tableName}_examId_code_Idx` (`exam_id`,`code`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='考试场次'
    </update>

    <insert id="insertInfo" parameterType="com.qmth.themis.business.entity.TEExamActivity">
        INSERT INTO ${tableName}
            (id, exam_id, code, prepare_seconds, max_duration_seconds, enable, opening_seconds, start_time, finish_time, create_time, update_time, create_id, update_id)
        VALUES(
            #{TEExamActivity.id},
            #{TEExamActivity.examId},
            #{TEExamActivity.code},
            #{TEExamActivity.prepareSeconds},
            #{TEExamActivity.maxDurationSeconds},
            #{TEExamActivity.enable},
            #{TEExamActivity.openingSeconds},
            #{TEExamActivity.startTime},
            #{TEExamActivity.finishTime},
            #{TEExamActivity.createTime},
            #{TEExamActivity.updateTime},
            #{TEExamActivity.createId},
            #{TEExamActivity.updateId})
    </insert>

    <select id="selectListPage" resultType="com.qmth.themis.business.entity.TEExamActivity">
        select * from ${tableName}
    </select>

    <select id="examActivityQuery" resultType="com.qmth.themis.business.dto.response.TEExamActivityQueryDto">
        select
        t.id,
        t.code,
        t.examId,
        t.prepareSeconds,
        t.maxDurationSeconds,
        t.enable,
        t.openingSeconds,
        t.startTime,
        t.finishTime,
        if(t.updateName is not null, t.updateName, t.createName) as updateName,
        if(t.updateTime is not null, t.updateTime, t.createTime) as updateTime
        from
        (
        select
        teea.id, teea.code, teea.exam_id as examId, teea.prepare_seconds as prepareSeconds, teea.max_duration_seconds as
        maxDurationSeconds, teea.enable, teea.opening_seconds as openingSeconds, teea.start_time as startTime,
        teea.finish_time as finishTime, teea.create_time as createTime, teea.update_time as updateTime,(
        select
        t1.name
        from
        t_b_user t1
        where
        t1.id = teea.create_id) as createName, (
        select
        t1.name
        from
        t_b_user t1
        where
        t1.id = teea.update_id) as updateName
        from t_e_exam_activity teea
        left join t_e_exam tee on teea.exam_id = tee.id
        <where>
            <if test="id != null and id != ''">
                and teea.id = #{id}
            </if>
            <if test="examId != null and examId != ''">
                and teea.exam_id = #{examId}
            </if>
            <if test="code != null and code != ''">
                and teea.code like concat('%', #{code}, '%')
            </if>
            <if test="startDate != null and startDate != ''">
                and teea.start_time = #{startDate}
            </if>
            <if test="finishDate != null and finishDate != ''">
                and teea.finish_time = #{finishDate}
            </if>
        </where> ) t order by t.code
    </select>

    <select id="getWaitingExam" resultType="com.qmth.themis.business.dto.response.TEExamActivityDto">
         select
            teea.id,
            teea.code,
            tees.id as examStudentId,
            tees.course_code as courseCode,
            tees.course_name as courseName,
            IFNULL(teea.opening_seconds,tee.opening_seconds) as openingSeconds,
            teea.opening_seconds as activityOpeningSeconds,
            (teea.start_time / 1000 - IFNULL(teea.prepare_seconds, tee.prepare_seconds)) * 1000 as prepareTime,
            IFNULL(teea.start_time, tee.start_time) as minStartTime,
            (teea.start_time / 1000 + IFNULL(teea.opening_seconds, tee.opening_seconds)) * 1000 as maxStartTime,
            IF(tee.mode = 'TOGETHER' and tee.force_finish = 1, IFNULL(teea.finish_time , tee.end_time), null) as maxFinishTime,
            tee.monitor_video_source as monitorVideoSourceStr,
            IFNULL(teea.prepare_seconds,tee.prepare_seconds) as prepareSeconds,
            teea.prepare_seconds as activityPrepareSeconds,
            tee.min_duration_seconds as minDurationSeconds,
            IFNULL(teea.max_duration_seconds,tee.max_duration_seconds) as maxDurationSeconds,
            teea.max_duration_seconds as activityMaxDurationSeconds,
            tee.exam_count as examCount,
            tee.force_finish as forceFinish,
            tee.entry_authentication_policy as entryAuthenticationPolicy,
            tee.in_process_face_verify as inProcessFaceVerify,
            tee.in_process_face_stranger_ignore as inProcessFaceStrangerIgnore,
            tee.in_process_liveness_verify as inProcessLivenessVerify,
            tee.in_process_liveness_fixed_range as inProcessLivenessFixedRangeStr,
            tee.in_process_liveness_judge_policy as inProcessLivenessJudgePolicy,
            tee.camera_photo_upload as cameraPhotoUpload,
            tee.mobile_photo_upload as mobilePhotoUpload,
            teea.start_time as startTime,
            teea.finish_time as finishTime,
            tee.id as examId
        from
            t_e_exam_student tees
        left join t_e_exam tee on
            tee.id = tees.exam_id
        left join t_e_exam_activity teea on
            teea.id = tees.exam_activity_id
        <where> 1 = 1
            and teea.id in
            <foreach collection="examActivityIds" item="examActivityId" index="index" open="(" close=")" separator=",">
                #{examActivityId}
            </foreach>
            <if test="studentId != null and studentId != ''">
                and tees.student_id = #{studentId}
            </if>
            and tee.enable = 1
            and teea.enable = 1
            and tees.enable = 1
            <!--<if test="mode != null and mode != '' and mode == 'TOGETHER'">
                and teea.start_time <![CDATA[ >= ]]> date_sub(now(), interval IFNULL(teea.opening_seconds, tee.opening_seconds) second)
            </if>-->
        </where>
    </select>
</mapper>
