<?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.m2pool.pool.mapper.UserAccountMapper">

    <insert id="userAddMinerAccount" useGeneratedKeys="true" keyProperty="id">
        insert into user_miner_account(
            `user`,
            miner_user,
            coin,
            <if test="vo.remark != null and vo.remark != ''">remark,</if>
        create_date) values(
            #{vo.user},
            #{vo.ma},
            #{vo.coin},
            <if test="vo.remark != null and vo.remark != ''">#{vo.remark},</if>
            sysdate()
        )
    </insert>

    <insert id="accountAddBalance">
        insert into user_account_balance(
            `ma_id`,
            balance,
            amount
            <if test="vo.active != null and vo.active != 0">,#{vo.active}</if>
        ) values (
            #{accountId},
            #{vo.balance},
            #{vo.amount}
        <if test="vo.active != null and vo.active != 0">,#{vo.active}</if>
        )
    </insert>

    <insert id="createReadOnlyPage">
        insert into user_page_info(
            `user`,
            account,
            coin,
            config,
            url_key,
            lang
            <if test="vo.remark != null and vo.remark != ''">,remark</if>
            ,create_time
        ) values (
            #{vo.email},
            #{vo.account},
            #{vo.coin},
            #{vo.config},
            #{vo.key},
            #{vo.lang}
            <if test="vo.remark != null and vo.remark != ''">,#{vo.remark}</if>
            ,sysdate()
        )
    </insert>

    <insert id="userCreateApiKey">
        insert into user_api(
            `user`,
            api_key,
            api_ip,
            perms
        ) value (
            #{vo.user},
            #{vo.key},
            #{vo.apiIp},
            #{vo.permissions}
        )

    </insert>

    <update id="batchDelAccount">
        update user_miner_account set status = 11 where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach>;
    </update>

    <update id="accountStopBalance">
        update user_account_balance set status = 99 where id = #{id}
    </update>

    <update id="updateBalance">
        update
            user_account_balance
        set
            amount = #{vo.amount}
            ,active = #{vo.active}
        where id = #{id} and status = 0
    </update>

    <update id="updateMinerAccountRemark">
        update
            user_miner_account
        set
            remark = #{vo.remarks}
        where id = #{accountId} and status = 0
    </update>

    <update id="changeReadOnlyPage">
        update
            user_page_info
        set
            config = #{vo.config}
            <if test="vo.remark != null and vo.remark != ''">, remark = #{vo.remark}</if>
        where url_key =#{vo.key}
    </update>

    <update id="updateApiKey" useGeneratedKeys="true">
        update
            user_api
        set
            api_ip = #{vo.ip}
            <if test="vo.perms != null and vo.perms != ''">, perms = #{vo.perms}</if>
        where id =#{vo.id} and `user` =#{user}
    </update>

    <delete id="batchDeletePageByKeyAndUser">
        delete from user_page_info
        where url_key  in
        <foreach item="key" collection="keys" open="(" separator="," close=")">
            #{key}
        </foreach>
        and `user` = #{user}
    </delete>

    <delete id="batchDeleteAPIByIdsAndUser">
        delete from user_api
            where id  in
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
        and `user` = #{user}
    </delete>
    <delete id="deleteBatchReadOnlyPage">
        DELETE FROM user_page_info
        WHERE (
        <foreach collection="list" item="item" separator=" OR ">
            `user` = #{item.user}
            AND account = #{item.account}
            AND coin = #{item.coin}
        </foreach>
        )
    </delete>


    <select id="getAccountByUserAndMinerAccount" resultType="com.m2pool.pool.dto.UserAccountDto">
        select
            `user`,
            miner_user account,
            coin
        from
            user_miner_account
        where
            `user` = #{user}
            and miner_user = #{ma}
            and status = 0
        limit 1
    </select>

    <select id="getAccountByUserAndCoin" resultType="com.m2pool.pool.dto.UserAccountListDto">
        select
            a.id id,
            a.miner_user ma,
            a.coin,
            a.remark,
            b.balance addr
        from
            user_miner_account a
            left join user_account_balance b on a.id = b.ma_id
        where
            `user` = #{user} and coin != 'enx'
            and a.status = 0
            and b.status = 0
    </select>

    <select id="getBalanceByMinerAccount" resultType="com.m2pool.pool.dto.MinerAccountBalanceDto">
        select
            b.id,
            b.balance,
            b.active,
            b.amount,
            a.remark remark
        from
            user_account_balance b
            left join user_miner_account a on a.id = b.ma_id
        where
            `ma_id` = #{maId}
            and a.status = 0
            and b.status = 0
        order by create_time desc
        limit 1
    </select>

    <select id="getAccountByUserAccountAndCoin" resultType="com.m2pool.pool.dto.UserAccountDto">
        select
            id,
            `user`,
            miner_user account,
            coin
        from
            user_miner_account
        where
            `user` = #{user}
            and miner_user = #{account}
            and coin = #{coin}
            and status = 0
    </select>

    <select id="getAccountNumberByUserAccountAndCoin" resultType="java.lang.Integer">
        select
            count(miner_user)
        from
            user_miner_account
        where
            `user` = #{user}
            and coin = #{coin}
            and status = 0
    </select>

    <select id="getAccountByUserAccountId" resultType="com.m2pool.pool.dto.UserAccountDto">
        select
            id,
            `user`,
            miner_user account,
            coin
        from
            user_miner_account
        where
            `user` = #{user}
            and `id` = #{id}
    </select>

    <select id="checkAccountIfExist" resultType="java.lang.Boolean">
        select count(1) from   user_miner_account where miner_user=#{account} and  coin=#{coin}
    </select>

    <select id="getBalanceByCoin" resultType="com.m2pool.pool.dto.BalanceListDto">
        select
            a.miner_user `user`,
            b.balance address,
            b.amount amount
        from
            user_account_balance b
            left join user_miner_account a on b.ma_id = a.id
        where
            a.coin= #{coin}
            and b.active = 0
            and b.status = 0
            and a.status = 0
    </select>

    <select id="checkKeyIfExist" resultType="java.lang.Boolean">
        select count(1) from user_page_info where url_key=#{key}
    </select>

    <select id="getPageListByUser" resultType="com.m2pool.pool.entity.UserReadOnlyPageInfo">
        select
            id,
            url_key `key`,
            account,
            coin,
            config,
            remark,
            lang
        from
            user_page_info
        where
            `user` =#{user}
        order by coin,create_time desc
    </select>

    <select id="getPageInfoByKey" resultType="com.m2pool.pool.entity.UserReadOnlyPageInfo">
        select
            id,
            `user`,
            url_key `key`,
            account,
            coin,
            config,
            remark,
            lang
        from
            user_page_info
        where
            `url_key` =#{key}
        limit 1
    </select>

    <select id="getApiKeyCountByUser" resultType="java.lang.Integer">
        select count(1) from user_api where `user`=#{user}
    </select>

    <select id="getAPIListByUser" resultType="com.m2pool.pool.entity.UserApi">
        select
            id,
            `user`,
            api_key `key`,
            api_ip ip,
            perms
        from
            user_api
        where
            `user` =#{user}
        order by id
    </select>

    <select id="getAPIInfoByUserAndId" resultType="com.m2pool.pool.entity.UserApi">
        select
            id,
            `user`,
            api_key `key`,
            api_ip ip,
            perms
        from
            user_api
        where
            `user` =#{user}
            and id = #{id}
        limit 1
    </select>
    <select id="getUserMinerByUserEmail" resultType="com.m2pool.pool.dto.UserAccountDto">
        select
            id,
            `user`,
            miner_user account,
            coin
        from
            user_miner_account
        where
            `user` = #{user}
            and status = 0
    </select>


</mapper>


