<?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="networks">

    <typeAlias alias="Integer" type="java.lang.Integer"/>
    <typeAlias alias="String" type="java.lang.String" />
    <typeAlias alias="Map" type="java.util.Map" />
    <typeAlias alias="Network" type="data.domains.Network"/>
    <typeAlias alias="Device" type="data.domains.Device"/>
    <typeAlias alias="Reboot" type="data.domains.Reboot"/>

    <resultMap id="NetworkLazyMap" class="Network">
        <result property="networkId" column="id" nullValue="0"/>
        <result property="name" column="name"/>
        <result property="devices" column="id" select="networks.selectDevicesByNetworkId"/>
    </resultMap>

    <resultMap id="DeviceLazyMap" class="Device">
        <result property="serialNumber" column="serial_number" />
        <result property="manufacturer" column="manufacturer"/>
        <result property="oui" column="oui"/>
        <result property="productClass" column="product_class"/>
        <result property="subscriber" column="subscriber_id" select="subscriber.selectSubscriberById" />
        <result property="networkId" column="network_id"  nullValue="0"/>
        <result property="modelId" column="model_id" nullValue="0"/>
    </resultMap>

    <resultMap id="DevicePartMap" class="Device">
        <result property="serialNumber" column="serial_number" />
        <result property="manufacturer" column="manufacturer"/>
        <result property="oui" column="oui"/>
        <result property="productClass" column="product_class"/>
    </resultMap>


     <resultMap id="unknown" class="data.domains.Unknown">
        <result property="id" column="id" nullValue="0"/>
        <result property="serial"  column="serial"/>
        <result property="model"  column="model_name"/>
        <result property="type" column="unknown_type"/>
    </resultMap>

    <resultMap id="RebootMap" class="data.domains.Reboot">
        <result property="rebootId" column="id" nullValue="0"/>
        <result property="serialNumber" column="serial_number"/>
        <result property="rebootStatus" column="reboot_status" nullValue="0"/>
    </resultMap>

    <select id="selectDevicesByNetworkId" parameterClass="long"
            resultMap="networks.DeviceLazyMap">
        SELECT *
            FROM device
            WHERE network_id = #id#
    </select>

    <select id="selectNetworks" resultMap="networks.NetworkLazyMap">
        SELECT *
            FROM network
    </select>

    <select id="selectDeviceBySerial" resultMap="networks.DeviceLazyMap">
        SELECT *
            FROM device
            WHERE serial_number=#serial_number#
    </select>

     <select id="getDeviceCount" resultClass="Integer" parameterClass="long">
       SELECT COUNT(*) from device where network_id=#networkId#
    </select>

    <select id="getDeviceCountBySerial" resultClass="Integer" parameterClass="Map">
        SELECT COUNT(*) from device where network_id=#networkId# and serial_number like #serial#
    </select>

    <select id="getDeviceCountBySerialAndSubscriberId" resultClass="Integer" parameterClass="Map">
         SELECT COUNT(*) from device where network_id=#networkId# and subscriber_id=#subscriberId# AND serial_number like #serial#
    </select>

    <select id="getDeviceCountBySubscriberId" resultClass="Integer" parameterClass="Map">
         SELECT COUNT(*) from device where network_id=#networkId# and subscriber_id=#subscriberId#
    </select>

   <select id="getDevicesForPageByNetworkId" parameterClass="Map" resultMap="networks.DeviceLazyMap" >
           SELECT *
                 FROM device
                 Where network_id=#networkId#
                 LIMIT #countPerPage# OFFSET #offset#


   </select>

   <select id="searchDevicesForPageByNetworkId" parameterClass="Map" resultMap="networks.DeviceLazyMap" >
           SELECT *
                 FROM device
                 Where network_id=#networkId#   AND serial_number like #serial#
                 LIMIT #countPerPage# OFFSET #offset#


   </select>

   <select id="searchDevicesForPageBySubscriberId"  parameterClass="Map" resultMap="networks.DeviceLazyMap" >
              SELECT *
                 FROM device
                 Where subscriber_id=#subscriberId#   and network_id=#networkId# AND serial_number like #serial#
                 LIMIT #countPerPage# OFFSET #offset#
   </select>

   <select id="searchDeviceBySerial" parameterClass="Map" resultMap="networks.DeviceLazyMap">
           SELECT *
                 FROM device
                 Where network_id=#networkId#   AND serial_number like #serial#


   </select>

   <select id="searchDeviceBySerialAndSubscriberId" parameterClass="Map" resultMap="networks.DeviceLazyMap">
           SELECT *
                 FROM device
                 Where network_id=#networkId#  AND subscriber_id=#subscriberId# AND serial_number like #serial#
   </select>

   <select id="getRebootBySerial" parameterClass="String" resultMap="networks.RebootMap">
           Select *
                FROM reboot
                     Where  serial_number=#serial#
   </select>

   <insert id="addNewNetwork" parameterClass="Network">
         INSERT
               INTO network(id, name)
               VALUES( #networkId#, #name#)

               <selectKey resultClass="long" keyProperty="networkId">
                   SELECT LAST_INSERT_ID() AS ID
               </selectKey>
    </insert>

    <insert id="addReboot" parameterClass="Reboot"  >
         INSERT
               INTO reboot(id,serial_number,reboot_status)
               VALUES(#rebootId#, #serialNumber#, #rebootStatus#)

               <selectKey resultClass="long" keyProperty="rebootId">
                   SELECT LAST_INSERT_ID() AS ID
               </selectKey>

    </insert>

    <update id="updateReboot" parameterClass="String">
        Update reboot
                SET reboot_status=0
                    WHERE serial_number=#serial#
    </update>

    <delete id="deleteNewNetwork" parameterClass="long">
        DELETE FROM network
                WHERE id=#networkId#
    </delete>

    <select id="getDeviceBySerial" parameterClass="String" resultMap="networks.DevicePartMap">
        SELECT *
            FROM device
                WHERE serial_number=#serial#
    </select>

    <select id="selectNetworkById" parameterClass="long"  resultMap="networks.NetworkLazyMap">
        SELECT *
           FROM network
                 where id=#networkId#
    </select>

    <select id="selectUnknowns" resultClass="String" resultMap="networks.unknown">
         SELECT * FROM unknown WHERE unknown_type = #unknownType#
    </select>

    <select id="getDevicesBySubscriberId"  resultMap="networks.DeviceLazyMap" parameterClass="Map">
        SELECT *
            FROM device
                WHERE subscriber_id=#subscriberId#
                LIMIT #countPerPage# OFFSET #offset#
    </select>

    <select id="getAllDevices" resultClass="String">
         SELECT serial_number  FROM device
    </select>

    <select id="getDevicesByModelId"  parameterClass="long" resultMap="networks.DeviceLazyMap" >
         SELECT * from device where model_id=#modelId#
    </select>

    <select id="getUnknowns" resultMap="networks.unknown">
        SELECT distinct * from unknown GROUP BY model_name
    </select>

   <select id="getDeviceCountByModelId" parameterClass="long" resultClass="Integer">
        Select count(*) from device where model_id=#modelId#
   </select>

   <select id="getDevicesByModelIdForPage" parameterClass="Map" resultMap="networks.DeviceLazyMap"   >
        SELECT *
            FROM device
                WHERE model_id=#modelId#
                LIMIT #countPerPage# OFFSET #offset#

   </select>

    <select id="getAllDevicesCount" resultClass="Integer">
        Select count(*) from device
    </select>

    <select id="selectDeviceParamValue" parameterClass="Map" resultClass="String">
        SELECT dp.param_value from device_param dp, parameter p
            WHERE p.name=#paramName# and p.id=dp.param_id and dp.serial_number=#serial#
    </select>

</sqlMap>