<?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.lease.mapper.LeaseProductMachineMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.m2pool.lease.entity.LeaseProductMachine">
        <id column="id" property="id" />
        <result column="product_id" property="productId" />
        <result column="user" property="user" />
        <result column="miner" property="miner" />
        <result column="price" property="price" />
        <result column="computing_power" property="computingPower" />
        <result column="sale_state" property="saleState" />
        <result column="state" property="state" />
        <result column="del" property="del" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, product_id, user, miner, price, computing_power, state, del, create_time, update_time
    </sql>

    <update id="updateLockState">
        UPDATE lease_product_machine
        SET
        sale_state = 1
        WHERE
        id IN
        <foreach item="id" collection="machineIds" open="(" separator="," close=")">
            #{id}
        </foreach>
        and sale_state = 0
    </update>
    <select id="getMachinesByIds" resultType="com.m2pool.lease.dto.ProductMachineDto">
        SELECT
        lpm.id,
        lpm.shop_id as shopId,
        lpm.product_id as productId,
        lpm.`user`,
        lpm.miner,
        lpm.price,
        lpm.state,
        lpm.sale_state as saleState,
        lpm.coin,
        lpm.unit,
        lpm.power_dissipation as powerDissipation,
        lpm.theory_power as theoryPower,
        lpm.theory_income as theoryIncome,
        lp.name,
        lp.algorithm,
        lpm.del,
        lpm.max_lease_days as maxLeaseDays
        FROM
        lease_product_machine lpm join lease_product lp on lp.id = lpm.product_id
        WHERE
        lpm.id IN
        <foreach item="id" collection="machineIds" open="(" separator="," close=")">
            #{id}
        </foreach>
        order by product_id
    </select>
    <select id="getUserMinersList" resultType="com.m2pool.lease.dto.UserMinerDto">
        select miner_user as `user`,coin from user_miner_account where user = #{userId} and coin = #{coin}
    </select>
    <select id="getMinerMachines" resultType="com.m2pool.lease.dto.UserMinerDto">
        SELECT `user`, miner, sum(mhs * 30 * 60) AS realPower
        FROM ${coin}_mhs30m
        WHERE `user` = #{user} AND `date` >= DATE_SUB(NOW(), INTERVAL 1 DAY)
        GROUP BY `user`, miner
    </select>
    <select id="getPriceRange" resultType="com.m2pool.lease.dto.PriceDto">
        SELECT
        MIN(price) AS priceMin,
        MAX(price) AS priceMax,
        COUNT(*) AS totalMachineNumber
        FROM
        lease_product_machine
        WHERE
        product_id = #{productId} and del = 0
    </select>

    <select id="getRecentlyFiveMinutesData" resultType="com.m2pool.lease.dto.ProductMachineDto">
        SELECT
        `user`,
        miner,
        SUM(mhs * 30 * 60) as computingPower
        FROM
        ${coin}_mhs30m
        WHERE
        `date` >= DATE_SUB(now(), INTERVAL 1 DAY)
        <if test="leaseProductMachines != null and leaseProductMachines.size() > 0">
            AND (
            <foreach collection="leaseProductMachines" item="pair" separator="OR">
                (`user` = #{pair.user} AND miner = #{pair.miner})
            </foreach>
            )
        </if>
        group by `user`, miner
    </select>
    <select id="getMinerMachinesIsSale" resultType="java.lang.String">
        select miner from lease_product_machine where `user` = #{user} and `coin`  = #{coin} and del = 0;
    </select>


    <insert id="saveOrUpdateBatchs" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO lease_product_machine (
        shop_id, product_id, user, type, coin, miner, theory_power,
        computing_power, theory_income, unit,
        power_dissipation, income_rate, cost, price, sale_state,
        state, del,max_lease_days
        )
        VALUES
        <foreach item="item" index="index" collection="leaseProductMachines" separator=",">
            (
            #{item.shopId}, #{item.productId}, #{item.user}, #{item.type}, #{item.coin}, #{item.miner},
            #{item.theoryPower}, #{item.computingPower}, #{item.theoryIncome}, #{item.unit}, #{item.powerDissipation}, #{item.incomeRate}, #{item.cost},
            #{item.price}, #{item.saleState}, #{item.state}, #{item.del}, #{item.maxLeaseDays}
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
            shop_id = VALUES(shop_id),
            product_id = VALUES(product_id),
            `type` = VALUES(`type`),
            theory_power = VALUES(theory_power),
            computing_power = VALUES(computing_power),
            theory_income = VALUES(theory_income),
            unit = VALUES(unit),
            power_dissipation = VALUES(power_dissipation),
            income_rate = VALUES(income_rate),
            cost = VALUES(cost),
            price = VALUES(price),
            sale_state = VALUES(sale_state),
            `state` = VALUES(`state`),
            del = VALUES(del),
            max_lease_days = VALUES(max_lease_days)
    </insert>
    <insert id="batchInsertPowers">
        INSERT INTO ${coin}_real_power (user,miner,power,`date`)
        VALUES
        <foreach item="item" index="index" collection="list" separator=",">
            (
                #{item.user},
                #{item.miner},
                #{item.computingPower},
                now()
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
        power = VALUES(power),
        `date` = VALUES(`date`)
    </insert>


    <select id="checkHasSaleMachineByShopId" resultType="java.lang.Integer">
        SELECT
        COUNT(*)
        FROM
        lease_product_machine
        WHERE
         shop_id = #{shopId}
        and `sale_state` = 1
        and `del` = 0
    </select>
    <select id="checkHasSaleMachineByProductId" resultType="java.lang.Integer">
        SELECT
        COUNT(*)
        FROM
        lease_product_machine
        WHERE
         product_id = #{productId}
        and `sale_state` = 1
        and `del` = 0
    </select>
    <select id="getPriceList" resultType="com.m2pool.lease.dto.MachinePayTypeDto">
        SELECT
        id as payTypeId, product_machine_id as productMachineId, price, coin,chain
        FROM
        lease_product_machine_price
        WHERE
        del = false  and product_machine_id IN
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <select id="getProductListForShopWalletConfig" resultType="com.m2pool.lease.dto.ProductMachineForWalletConfigDto">
SELECT
        lpm.id as productMachineId,
        lpm.product_id as productId,
        lpm.`user`,
        lpm.type,
        lpm.miner,
        lpm.unit,
        lpm.state,
        lpm.coin,
        lpm.sale_state as saleState,
        lpm.theory_power as theoryPower,
        lpm.theory_income as theoryIncome,
        lpm.power_dissipation as powerDissipation
FROM
        lease_product_machine lpm
WHERE
      del = false AND lpm.shop_id = #{shopId}
    </select>
    <select id="getIdsForProductId" resultType="java.lang.Long">
        SELECT
        id
        FROM
        lease_product_machine
        WHERE
        product_id = #{productId}
        and `del` = 0
    </select>
    <select id="getMachinesByPriceAndPowerAndDissipation" resultType="com.m2pool.lease.dto.ProductMachineDto">
        SELECT
        pm.id,
        pm.shop_id as shopId,
        pm.product_id as productId,
        pm.`user`,
        pm.type,
        pm.miner,
        pm.theory_power as theoryPower,
        pm.theory_income as theoryIncome,
        pm.unit,
        pm.power_dissipation as powerDissipation,
        pm.income_rate as incomeRate,
        pm.cost,
        pm.sale_state as saleState,
        pm.state,
        pm.del,
        pm.max_lease_days as maxLeaseDays,
        COALESCE(pmp.price,0) as price,
        pm.coin,
        pmp.coin as payCoin,
        COALESCE(rp.power,0) as computingPower
        FROM
        lease_product_machine pm
        LEFT JOIN lease_product_machine_price pmp
            ON pm.id = pmp.product_machine_id AND pmp.coin = #{productMachineVo.coin} AND pmp.chain = #{productMachineVo.chain} AND pmp.del = false
        LEFT JOIN ${coin}_real_power rp
            ON pm.user = rp.user AND rp.miner = pm.miner
        WHERE
        pm.del = false AND pm.`state` = 0 AND pm.product_id = #{productMachineVo.id}
        <!-- 实际算力范围筛选 -->
        <if test="productMachineVo.minPower != 0 or productMachineVo.maxPower != 0">
            AND rp.power >= #{productMachineVo.minPower} AND rp.power <![CDATA[<=]]> #{productMachineVo.maxPower}
        </if>
        <!-- 功耗范围筛选 -->
        <if test="productMachineVo.minPowerDissipation != 0 or productMachineVo.maxPowerDissipation != 0">
            AND power_dissipation >= #{productMachineVo.minPowerDissipation} AND power_dissipation <![CDATA[<=]]> #{productMachineVo.maxPowerDissipation}
        </if>
        <!-- 单价范围筛选 -->
        <if test="productMachineVo.minPrice != 0 or productMachineVo.maxPrice != 0">
            AND pmp.price >= #{productMachineVo.minPrice} AND pmp.price <![CDATA[<=]]> #{productMachineVo.maxPrice}
        </if>
<!--        <if test="productMachineVo.priceSort != null">-->
<!--            ORDER BY-->
<!--            <choose>-->
<!--                &lt;!&ndash; 价格排序 &ndash;&gt;-->
<!--                <when test="productMachineVo.priceSort">-->
<!--                    pmp.price ASC-->
<!--                </when>-->
<!--                <otherwise>-->
<!--                    pmp.price DESC-->
<!--                </otherwise>-->
<!--            </choose>-->
<!--        </if>-->
<!--        <if test="productMachineVo.powerSort != null">-->
<!--            ORDER BY-->
<!--            <choose>-->
<!--                &lt;!&ndash; 实际算力排序 &ndash;&gt;-->
<!--                <when test="productMachineVo.powerSort">-->
<!--                    rp.power ASC-->
<!--                </when>-->
<!--                <otherwise>-->
<!--                    rp.power DESC-->
<!--                </otherwise>-->
<!--            </choose>-->
<!--        </if>-->
<!--        <if test="productMachineVo.powerDissipationSort != null">-->
<!--            ORDER BY-->
<!--            <choose>-->
<!--                &lt;!&ndash; 功耗排序 &ndash;&gt;-->
<!--                <when test="productMachineVo.powerDissipationSort">-->
<!--                    power_dissipation ASC-->
<!--                </when>-->
<!--                <otherwise>-->
<!--                    power_dissipation DESC-->
<!--                </otherwise>-->
<!--            </choose>-->
<!--        </if>-->
    </select>
    <select id="getIdsForShopId" resultType="java.lang.Long">
        SELECT
        id
        FROM
        lease_machine
        WHERE
        shop_id = #{shopId}
        and `del` = 0
    </select>
</mapper>
