<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
        "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="files">
    <typeAlias alias="Download" type="data.domains.Download" />
    <typeAlias alias="File" type="data.domains.UpgradeFile" />

    <resultMap id="FileMap" class="File">
        <result property="fileId" column="id" nullValue="0"/>
        <result property="path" column="path" />
        <result property="type"  column="file_type" />
        <result property="fileName"  column="name" />
        <result property="version" column="version"/>
        <result property="modelId" column="model_id" nullValue="0"/>
        <result property="fileSize"  column="file_size" nullValue="0" />
    </resultMap>

    <resultMap id="DownloadMap" class="Download">
        <result property="downloadId" column="id" nullValue="0"/>
        <result property="serialNumber" column="serial_number" />
        <result property="upgradeDate"  column="upgrade_date" />
        <result property="status"  column="status"  nullValue="0"/>
        <result property="fileId"  column="file_id" nullValue="0"/>
        <result property="url"     column="url"/>
    </resultMap>

    <select id="getDownloadForUpgradeBySerial" parameterClass="Map" resultMap="files.DownloadMap">
          SELECT d.* from download d, file f where serial_number=#serialNumber# and status=1  and  d.file_id=f.id and f.file_type=#type# AND d.upgrade_date in (SELECT MAX(upgrade_date)
                                                                                              FROM download d
                                                                                               WHERE d.serial_number = #serialNumber#   GROUP BY d.serial_number) limit 1 offset 0
    </select>

    <select id="getWaitingDownloadBySerial" parameterClass="Map" resultMap="files.DownloadMap">
          SELECT d.* from download d,file f where d.serial_number=#serialNumber# and d.status=0   and  d.file_id=f.id and f.file_type=#type#
    </select>

    <select id="getFileByVersion" parameterClass="Map" resultMap="files.FileMap" >
        SELECT * from file where version=#version# and model_id=#modelId#
    </select>

    <select id="getVersionsByFoeModel" parameterClass="long" resultClass="String" >
        Select version from file where model_id=#modelId#
    </select>

    <select id="getFileByVersionModelId" parameterClass="Map" resultMap="files.FileMap">
        Select * from file where version=#version# and model_id=#modelId#
    </select>

    <insert id="addDownload"  parameterClass="Download">
          INSERT
              INTO download(id,serial_number,upgrade_date,status,file_id,url)
                  VALUES(#downloadId#,#serialNumber#,#upgradeDate#,#status#,#fileId#,#url#)

          <selectKey resultClass="long" keyProperty="downloadId">   <!--type="post" kam "pre"-->
                SELECT LAST_INSERT_ID() AS ID
          </selectKey>
    </insert>

     <insert id="addFile"  parameterClass="File">
          INSERT
              INTO file(id,path,file_type,version,model_id,name,file_size)
                  VALUES(#fileId#,#path#,#type#,#version#,#modelId#,#fileName#,#fileSize#)

          <selectKey resultClass="long" keyProperty="fileId">   <!--type="post" kam "pre"-->
                SELECT LAST_INSERT_ID() AS ID
          </selectKey>
    </insert>

    <update id="updateDownload" parameterClass="Download">
        Update download
            SET file_id=#fileId#,
                upgrade_date=#upgradeDate#
            WHERE  serial_number=#serialNumber#
    </update>

    <update id="updateFileSize" parameterClass="Map">
        Update file
            Set file_size=#fileSize#
            where id=#fileId#
    </update>

    <update id="updateFile" parameterClass="Map">
        Update file
            Set path=#path#
            where id=#fileId#
    </update>

    <update id="updateDownloadStatusByFileId" parameterClass="long">
        Update download
            Set status=2
            Where file_id=#fileId#
    </update>

    <update id="updateDownloadStatusBySerial" parameterClass="String">
        Update download
            Set status=2
            Where serial_number=#serial# and status=1
    </update>

    <update id="updateDownloadStatusByDownloadId" parameterClass="long">
         UPDATE  download
            SET status=2
            where id=#downloadId#
    </update>

    <select id="getFileById" parameterClass="long" resultMap="files.FileMap">
        SELECT * from file WHERE id = #id#
    </select>

    <select id="getUpgradeStatusForDeviceByType" parameterClass="Map" resultClass="Integer">
        Select d.status from download d,file f where d.serial_number=#serial# AND d.file_id=f.id and f.file_type=#type# AND d.upgrade_date in (SELECT MAX(upgrade_date)
                                                                                              FROM download d
                                                                                               WHERE d.serial_number = #serial#   GROUP BY d.serial_number) limit 1 offset 0
    </select>

    <select id="getDownloadStatusByFileId" parameterClass="long" resultClass="integer">
        Select status from download where file_id=#fileId# AND upgrade_date in (SELECT MAX(upgrade_date)
                                                                                              FROM download d
                                                                                               WHERE d.file_id = #fileId#   GROUP BY d.file_id) limit 1 offset 0

    </select>

    <select id="getFileBySerialNumber" parameterClass="String" resultMap="files.FileMap">
        SELECT f.* from file f, download d
            WHERE d.serial_number=#serialNumber#  AND d.status=1 And d.file_id=f.id
    </select>

</sqlMap>