<?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="cn.com.ctop.common.module.material.mapper.MaterialVideoInfoMapper">

    <delete id="deleteByMd5" >
        delete from ctop_material_video_info where md5=#{md5}
    </delete>

    <select id="joinMaterialInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            CONCAT(t1.md5, t2.project_id) AS id,
            t1.md5 as code,
            t1.url,
            t1.cover_url AS coverUrl,
            t3.clip AS userId,
            t1.description AS materialDescribe,
            t2.project_id AS projectId,
            'VIDEO' AS type,
            t1.supplier_code as supplierCode
        FROM
            ctop_material_video_info t1
                LEFT JOIN ctop_material_video_project t2 ON t1.md5 = t2.video_md5
                LEFT JOIN ctop_platform_material t3 ON t1.md5 = t3.video_id
        WHERE
            t1.md5 = #{md5}
        limit 1
    </select>

    <select id="joinMaterialAscription" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            video_md5 as materialId,
            clip_id as clipId,
            clip_id as clipId,
            clip_id as clipId,
            video_md5 as code
        FROM
            ctop_material_video_author
        WHERE
            video_md5 = #{md5}
        limit 1
    </select>

    <select id="joinMaterialCutFrame" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            video_md5 as videoSignature,
            md5 as signature,
            url,
            frame_index as imageIndex,
            create_time as createTime
        FROM
            ctop_material_video_frame
        WHERE
            video_md5 = #{md5}
    </select>

    <select id="joinMaterialParameter" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            md5 as materialId,
            duration as 'second',
            format,
            width,
            height,
            ROUND(size/1000000,2) as size,
            create_time as createTime
        FROM
            ctop_material_video_info
        WHERE
            md5 = #{md5}
        limit 1
    </select>

</mapper>