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

   <typeAlias alias="User" type="data.domains.User" />
   <typeAlias alias="Map" type="java.util.Map" />

   <resultMap id="UserMap" class="User">
       <result property="userId" column="id" nullValue="0"/>
       <result property="username" column="username" />
       <result property="password" column="password" />
       <result property="email" column="email" />
       <result property="uniqueCode" column="unique_key" />
       <result property="userType" column="type_id" nullValue="0"/>
       <result property="userStatus" column="status_id" nullValue="0"/>
       <result property="managerEmail" column="manager_email" />
       <result property="name" column="name" />
       <result property="surname" column="surname" />
       <result property="phone" column="phone" />
   </resultMap>

   <select id="selectUser" parameterClass="Map" resultMap="users.UserMap">
        SELECT *
               FROM user
                  WHERE username=#username# and password=#password#
   </select>

    <select id="getUsersBesideAdmin" parameterClass="long" resultMap="users.UserMap">
           SELECT *
               FROM user
                  WHERE id!=#adminId#
    </select>

    <select id="getUsersCountByUsername" parameterClass="string" resultClass="int">
           SELECT count(*)
               FROM user
                  WHERE username=#username#
    </select>

    <select id="getUsersCountByEmail" parameterClass="string" resultClass="int">
           SELECT count(*)
               FROM user
                  WHERE email=#email#
    </select>

    <insert id="addNewUser" parameterClass="User" >
           INSERT
               INTO user(id, username,email,password,unique_key,type_id,status_id)
               VALUES( #userId#, #username#,#email#,#password#,#uniqueCode#,#userType#,#userStatus#)

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

    <update id="setStatusActivated" parameterClass="User" >
        UPDATE user
           SET status_id=#userStatus#
           WHERE unique_key=#uniqueCode# and email=#email#
    </update>

    <select id="getUserByUniqueEmail" parameterClass="Map" resultMap="users.UserMap">
            SELECT *
               FROM user
                  WHERE unique_key=#uniqueCode#   and email=#email#
    </select>

    <delete id="removeUser" parameterClass="long">
        DELETE
            FROM user
              WHERE id=#userId#
    </delete>

    <select id="getUserById" parameterClass="long" resultMap="users.UserMap">
        SELECT *
           FROM user
             WHERE id=#userId#
    </select>

    <select id="selectAdministrator" resultMap="users.UserMap">
        SELECT  * FROM  user where  type_id=1
    </select>

    <select id="selectUserByEmail" resultMap="users.UserMap">
        SELECT  * FROM  user where  email = #email#
    </select>

    <update id="updatePasswordFirst" parameterClass="User">
       UPDATE user
           SET
              unique_key= #uniqueCode#,
              password = #password#
           WHERE id=#userId#
    </update>

    <update id="updatePasswordUsername" parameterClass="User">
       UPDATE user
           SET
              unique_key= #uniqueCode#,
              password = #password#,
              username = #username#
           WHERE id=#userId#
    </update>

    <update id="updatePasswordAnyTime" parameterClass="Map">
       UPDATE user
              SET
                 password = #newPassword#
              WHERE id=#userId#
    </update>

    <delete id="deleteNotActivatedUsers">
          DELETE
              FROM user
                 WHERE status_id =(SELECT id FROM status WHERE name="Pending")
                                     and user.status_change_date=user.creation_date
                                     and TIMESTAMPDIFF(DAY,user.creation_date,NOW())>10

    </delete>

    <update id="updateProfile" parameterClass="User">
        UPDATE user
            SET     username = #username#,
                    email = #email#,
                    name=#name#,
                    surname=#surname#,
                    phone=#phone#,
                    manager_email=#managerEmail#
        WHERE id=#userId#
    </update>

    <update id="updateUniqueKey" parameterClass="Map">
        UPDATE  user
            SET   unique_key = #uniqueKey#
            WHERE email = #email#
    </update>

</sqlMap>