<?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.LeaseMachineMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.m2pool.lease.entity.LeaseMachine">
        <id column="id" property="id" />
        <result column="shop_id" property="shopId" />
        <result column="type" property="type" />
        <result column="max_lease_days" property="maxLeaseDays" />
        <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" />
        <result column="user" property="user" />
        <result column="miner" property="miner" />
        <result column="sale_numbers" property="saleNumbers" />
        <result column="sale_out_numbers" property="saleOutNumbers" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, shop_id, type, single_power, total_power, unit, max_lease_days, price, power_dissipation, sale_state, state, del, create_time, update_time, coin, algorithm, gpu_config_id, user, miner, sale_numbers, sale_out_numbers
    </sql>

    <select id="getShopIds" resultType="java.lang.Long">
        select shop_id from lease_machine where del = 0
    </select>

    <select id="getExistsMachineShop" resultType="com.m2pool.lease.dto.v2.ShopInfoDto">
        SELECT
        ls.id AS shopId,
        ls.name AS shopName,
        ls.image,
        ls.description,
        ls.sale_number AS saleNumber,
        GROUP_CONCAT(DISTINCT sub.type SEPARATOR ',') AS type,
        GROUP_CONCAT(DISTINCT sub.coin SEPARATOR ',') AS coin,
        GROUP_CONCAT(DISTINCT sub.algorithm SEPARATOR ',') AS algorithm
        FROM
        lease_shop ls
        LEFT JOIN (
            SELECT
                mm.shop_id,
                mm.type,
                gpc.coin,
                gpc.algorithm
            FROM
                lease_machine mm
            JOIN
                lease_machine_config gpc ON mm.id = gpc.machine_id AND mm.online_status = true
        ) sub ON ls.id = sub.shop_id
        WHERE ls.del = false AND ls.id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        <choose>
            <when test="coin != null and coin != ''">
                AND (sub.coin LIKE CONCAT('%', #{coin}, '%')
                <if test="algorithm != null and algorithm != ''">
                    AND sub.algorithm LIKE CONCAT('%', #{algorithm}, '%')
                </if>
                )
            </when>
            <otherwise>
                <if test="algorithm != null and algorithm != ''">
                    AND sub.algorithm LIKE CONCAT('%', #{algorithm}, '%')
                </if>
            </otherwise>
        </choose>
        GROUP BY ls.id, ls.name, ls.image, ls.description, ls.sale_number
        ORDER BY ls.fee_rate DESC, ls.sale_number DESC
    </select>

    <select id="getShopMachineList" resultType="com.m2pool.lease.dto.v2.MachineInfoDto">
        SELECT
            sub.id,
            sub.host_mac AS hostMac,
            sub.shop_id as shopId,
            sub.type,
            sub.`user`,
            sub.miner,
            sub.state,
            sub.sale_state AS saleState,
            sub.max_lease_days AS maxLeaseDays,
            sub.del,
            sub.power_dissipation as powerDissipation,
            sub.hashrate as theoryPower,
            sub.unit,
            sub.coin,
            sub.month_income as monthIncome,
            sub.name as model,
            sub.algorithm,
            sub.online_status AS onlineStatus,
            sub.sale_numbers as saleNumbers,
            sub.sale_out_numbers as saleOutNumbers,
            mp.price
        FROM
        (
            SELECT
                lm.id,
                lm.host_mac,
                lm.shop_id,
                lm.type,
                lm.`user`,
                lm.miner,
                lm.state,
                lm.sale_state,
                lm.max_lease_days,
                lm.del,
                lm.online_status,
                lm.sale_numbers,
                lm.sale_out_numbers,
                SUM(gpc.power_dissipation) AS power_dissipation,
                SUM(gpc.hashrate) AS hashrate,
                SUM(gpc.month_income) AS month_income,
                gpc.unit,
                gpc.coin,
                gpc.name,
                gpc.algorithm,
                ROW_NUMBER() OVER (
                PARTITION BY lm.id
                ORDER BY SUM(gpc.month_income) DESC
                ) AS rn
            FROM
            lease_machine lm
                JOIN
            lease_machine_config gpc ON lm.id = gpc.machine_id AND lm.type = #{shopMachineVo.type} AND lm.shop_id = #{shopMachineVo.shopId} AND  lm.online_status = true AND lm.sale_state = 0 AND lm.state = 0
            GROUP BY lm.id,gpc.coin,gpc.algorithm
            ) sub
        LEFT JOIN lease_machine_price mp
        ON sub.id = mp.machine_id AND mp.coin = #{shopMachineVo.coin} AND mp.chain = #{shopMachineVo.chain} AND mp.del = false AND sub.rn = 1
        WHERE
        sub.del = FALSE AND sub.rn = 1
        <if test="shopMachineVo.minPrice != 0 or shopMachineVo.maxPrice != 0">
            AND mp.price >= #{shopMachineVo.minPrice} AND mp.price <![CDATA[<=]]> #{shopMachineVo.maxPrice}
        </if>
        <if test="shopMachineVo.keyword != null and shopMachineVo.keyword != ''">
            AND (sub.coin LIKE CONCAT('%', #{shopMachineVo.keyword}, '%') OR sub.algorithm LIKE CONCAT('%', #{shopMachineVo.keyword}, '%'))
        </if>
    </select>


    <select id="getGpuConfigList" resultType="com.m2pool.lease.dto.v2.PowerIncomeInfoDto">
        SELECT
        coin,
        algorithm,
        sum(hashrate) as power,
        sum(month_income) as monthIncome,
        sum(power_dissipation) as powerDissipation,
        unit,
        machine_id
        FROM
        lease_machine_config
        WHERE
        status = true AND machine_id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        group by machine_id,coin, algorithm
    </select>

    <select id="getShopMachineListForSeller" resultType="com.m2pool.lease.dto.v2.SellerMachineInfoDto">
        SELECT
        sub.id,
        sub.shop_id as shopId,
        sub.type,
        sub.`user`,
        sub.miner,
        sub.state,
        sub.sale_state AS saleState,
        sub.max_lease_days AS maxLeaseDays,
        sub.del,
        sub.power_dissipation as powerDissipation,
        sub.hashrate as theoryPower,
        sub.unit,
        sub.coin,
        sub.month_income as monthIncome,
        sub.name,
        sub.algorithm,
        sub.online_status AS onlineStatus,
        sub.sale_numbers as saleNumbers,
        sub.sale_out_numbers as saleOutNumbers
        FROM
        (
        SELECT
        lm.id,
        lm.shop_id,
        lm.type,
        lm.`user`,
        lm.miner,
        lm.state,
        lm.sale_state,
        lm.max_lease_days,
        lm.del,
        lm.online_status,
        lm.sale_numbers,
        lm.sale_out_numbers,
        gpc.power_dissipation,
        gpc.hashrate,
        gpc.unit,
        gpc.coin,
        gpc.month_income,
        gpc.name,
        gpc.algorithm,
        ROW_NUMBER() OVER (PARTITION BY lm.id ORDER BY gpc.month_income DESC) as rn
        FROM
        lease_machine lm
        JOIN
        lease_machine_config gpc ON lm.id = gpc.machine_id AND lm.type = #{sellerMachineVo.type} AND lm.shop_id = #{shopId}
        ) sub
        where
        sub.del = false and sub.rn = 1
        <if test="sellerMachineVo.name != null and sellerMachineVo.name != ''">
            AND sub.name LIKE CONCAT('%', #{sellerMachineVo.name}, '%')
        </if>
        <if test="sellerMachineVo.keyword != null and sellerMachineVo.keyword != ''">
            AND sub.name LIKE CONCAT('%', #{sellerMachineVo.keyword}, '%')
        </if>
    </select>



    <select id="getMachinePriceList" resultType="com.m2pool.lease.dto.MachinePayTypeDto">
        select
        id as payTypeId,
        machine_id as productMachineId,
        price,
        coin,
        chain
        from lease_machine_price
        where
        <foreach collection="list" item="item" separator="OR">
            (`machine_id` = #{item.id} AND del = false)
        </foreach>
    </select>
    <select id="getShopIdsByMachineIds" resultType="java.lang.Long">
        SELECT
        DISTINCT shop_id as shopId
        FROM
        lease_machine
        WHERE
        id IN
        <foreach collection="machineIds" item="machineId" open="(" separator="," close=")">
            #{machineId}
        </foreach>

    </select>
    <select id="getMachinesByIds" resultType="com.m2pool.lease.dto.v2.CartMachineInfoDto">
        SELECT
        *
        FROM
        (
            SELECT
            sub.id,
            sub.shop_id AS shopId,
            sub.type,
            sub.`user`,
            sub.miner,
            sub.state,
            sub.sale_state AS saleState,
            sub.max_lease_days AS maxLeaseDays,
            sub.del,
            sub.power_dissipation AS powerDissipation,
            sub.hashrate AS power,
            sub.unit,
            sub.coin,
            sub.month_income AS monthIncome,
            sub.NAME,
            sub.online_status AS onlineStatus,
            sub.can_sale_numbers as canSaleNumbers,
            sub.algorithm,
            ci.id AS cartInfoId,
            ci.numbers,
            ci.lease_time AS leaseTime,
            ROW_NUMBER() OVER ( PARTITION BY sub.id ORDER BY sub.month_income DESC ) AS rn
            FROM
            (
            SELECT
                lm.id,
                lm.shop_id,
                lm.type,
                lm.`user`,
                lm.miner,
                lm.state,
                lm.sale_state,
                lm.max_lease_days,
                lm.del,
                lm.online_status,
                lm.can_sale_numbers,
                gpc.power_dissipation AS power_dissipation,
                gpc.hashrate,
                gpc.month_income AS month_income,
                gpc.unit,
                gpc.coin,
                gpc.NAME,
                gpc.algorithm
            FROM
                lease_machine lm
            JOIN lease_machine_config gpc ON lm.id = gpc.machine_id AND gpc.status = 1
            GROUP BY
                lm.id,
                gpc.coin,
                gpc.`algorithm`
            ) sub
            JOIN lease_shopping_cart_info ci ON sub.id = ci.product_machine_id
            AND ci.version = 1
            WHERE
                sub.id IN
                <foreach collection="machineIds" item="machineId" open="(" separator="," close=")">
                    #{machineId}
                </foreach>
        ) t
        WHERE
        rn = 1
    </select>
    <select id="getCoinAndAlgoList" resultType="com.m2pool.lease.dto.v2.AsicCoinAndAlgoDto">
        select
        id as coinAndPowerId,
        machine_id as productMachineId,
        hashrate as theoryPower,
        coin,
        algorithm,
        unit
        from lease_machine_config
        where
        <foreach collection="list" item="item" separator="OR">
            (`machine_id` = #{item.id} AND del = false)
        </foreach>

    </select>



    <update id="updateLockState">
        UPDATE lease_machine
        SET
        sale_state = 1,
        can_sale_numbers = 0,
        sale_out_numbers = 1
        WHERE
        id IN
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.id}
        </foreach>
        and sale_state = 0
    </update>
    <update id="updateLockNumbers">
        <foreach collection="list" item="item" separator=";">
            UPDATE lease_machine
            SET
            sale_out_numbers = #{item.saleOutNumbers},
            sale_state = CASE WHEN #{item.saleOutNumbers} = sale_numbers THEN 1 ELSE sale_state END,
            can_sale_numbers = sale_numbers - #{item.saleOutNumbers}
            WHERE
            id = #{item.id}
            AND sale_out_numbers = #{item.lockNumbers}
        </foreach>
    </update>


    <select id="getCoinAndAlgoById" resultType="com.m2pool.lease.dto.v2.MiningConfigDto">
        select
            id,
            coin,
            mining_tcp_gpu_url as pool,
            algorithm as algo
        from lease_pool_coin_config
        where
        id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <select id="getEffectMachineAndTempMachineList" resultType="com.m2pool.lease.dto.v2.MachineConfigDto">
                select
                id,
                machine_id as machineId,
                brand,
                name,
                memory,
                coin,
                algorithm,
                false as isTemp
            from lease_machine_config
            where
                machine_id = #{machineId}
        union all
            select
                id,
                machine_id as machineId,
                brand,
                name,
                memory,
                coin,
                algorithm,
                true as isTemp
            from lease_machine_temp_config
            where
                machine_id = #{machineId}

    </select>
    <select id="getSupportCoin" resultType="java.lang.String">
        SELECT DISTINCT
            sc.coin
        FROM
            lease_mining_software_config sc
                JOIN lease_pool_coin_config cc ON sc.coin = cc.coin
                AND cc.STATUS = 1
    </select>
    <select id="getSupportAlgo" resultType="java.lang.String">
        SELECT DISTINCT
            cc.`algorithm`
        FROM
            lease_mining_software_config sc
                JOIN lease_pool_coin_config cc ON sc.`algorithm` = cc.`algorithm`
                AND cc.STATUS = 1 AND sc.coin = #{coin} AND cc.coin = #{coin}

    </select>
    <select id="checkIsEffect" resultType="com.m2pool.lease.dto.v2.MachineEffectDto">
        SELECT
            DISTINCT
            sub.machine_id as machineId,
            true as effect
        FROM
            (
                SELECT
                    mp.machine_id,
                    mp.coin,
                    mp.`algorithm`
                FROM
                    lease_machine_config mp
                        JOIN lease_mining_software_config sc ON mp.coin = sc.coin
                        AND mp.`algorithm` = sc.`algorithm`
            ) sub
                JOIN lease_pool_coin_config cc ON sub.coin = cc.coin
                AND sub.`algorithm` = cc.`algorithm`
                AND cc.STATUS = 1
        WHERE
            sub.machine_id IN
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item.id}
            </foreach>
    </select>



    <update id="updateBatchSaleNumbers">
        <foreach collection="list" item="item" separator=";">
            UPDATE lease_machine
            SET
            sale_out_numbers = sale_out_numbers - #{item.saleOutNumbers},
            can_sale_numbers = can_sale_numbers + #{item.canSaleNumbers}
            WHERE
            id = #{item.id}
        </foreach>
    </update>

    <select id="checkHasSaleMachineByShopId" resultType="java.lang.Integer">
        SELECT
            COUNT(*)
        FROM
            lease_machine
        WHERE
            shop_id = #{shopId}
          and `sale_state` = 1
          and `del` = 0
    </select>

</mapper>
