<?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.ylx.massage.mapper.MaProjectMapper">

    <resultMap type="MaProject" id="MaProjectResult">
        <result property="id"    column="id"    />
        <result property="projectName"    column="project_name"    />
        <result property="projectDescribe"    column="project_describe"    />
        <result property="projectDuration"    column="project_duration"    />
        <result property="projectUsersNum"    column="project_users_num"    />
        <result property="projectOriginalPrice"    column="project_original_price"    />
        <result property="projectCurrentPrice"    column="project_current_price"    />
        <result property="projectMasterImage"    column="project_master_image"    />
        <result property="projectDetailImage"    column="project_detail_image"    />
        <result property="projectIsEnable"    column="project_is_enable"    />
        <result property="createUser"    column="create_user"    />
        <result property="updateUser"    column="update_user"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="isDelete"    column="is_delete"    />
    </resultMap>

    <sql id="selectMaProjectVo">
        select id, project_name, project_describe, project_duration, project_users_num, project_original_price, project_current_price, project_master_image, project_detail_image, project_is_enable, create_user, update_user, create_time, update_time, is_delete from ma_project
    </sql>

    <select id="selectMaProjectList" parameterType="MaProject" resultMap="MaProjectResult">
        <include refid="selectMaProjectVo"/>
        <where>
            is_delete !=1
            <if test="projectName != null  and projectName != ''"> and project_name like concat('%', #{projectName}, '%')</if>
            <if test="projectDescribe != null  and projectDescribe != ''"> and project_describe = #{projectDescribe}</if>
            <if test="projectDuration != null "> and project_duration = #{projectDuration}</if>
            <if test="projectUsersNum != null "> and project_users_num = #{projectUsersNum}</if>
            <if test="projectOriginalPrice != null "> and project_original_price = #{projectOriginalPrice}</if>
            <if test="projectCurrentPrice != null "> and project_current_price = #{projectCurrentPrice}</if>
            <if test="projectMasterImage != null  and projectMasterImage != ''"> and project_master_image = #{projectMasterImage}</if>
            <if test="projectDetailImage != null  and projectDetailImage != ''"> and project_detail_image = #{projectDetailImage}</if>
            <if test="projectIsEnable != null "> and project_is_enable = #{projectIsEnable}</if>
            <if test="createUser != null  and createUser != ''"> and create_user = #{createUser}</if>
            <if test="updateUser != null  and updateUser != ''"> and update_user = #{updateUser}</if>
        </where>
    </select>

    <select id="selectMaProjectById" parameterType="Long" resultMap="MaProjectResult">
        <include refid="selectMaProjectVo"/>
        where is_delete !=1 and id = #{id}
    </select>

    <insert id="insertMaProject" parameterType="MaProject" useGeneratedKeys="true" keyProperty="id">
        insert into ma_project
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="projectName != null and projectName != ''">project_name,</if>
            <if test="projectDescribe != null and projectDescribe != ''">project_describe,</if>
            <if test="projectDuration != null">project_duration,</if>
            <if test="projectUsersNum != null">project_users_num,</if>
            <if test="projectOriginalPrice != null">project_original_price,</if>
            <if test="projectCurrentPrice != null">project_current_price,</if>
            <if test="projectMasterImage != null and projectMasterImage != ''">project_master_image,</if>
            <if test="projectDetailImage != null and projectDetailImage != ''">project_detail_image,</if>
            <if test="projectIsEnable != null">project_is_enable,</if>
            <if test="createUser != null and createUser != ''">create_user,</if>
            <if test="updateUser != null and updateUser != ''">update_user,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="isDelete != null">is_delete,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="projectName != null and projectName != ''">#{projectName},</if>
            <if test="projectDescribe != null and projectDescribe != ''">#{projectDescribe},</if>
            <if test="projectDuration != null">#{projectDuration},</if>
            <if test="projectUsersNum != null">#{projectUsersNum},</if>
            <if test="projectOriginalPrice != null">#{projectOriginalPrice},</if>
            <if test="projectCurrentPrice != null">#{projectCurrentPrice},</if>
            <if test="projectMasterImage != null and projectMasterImage != ''">#{projectMasterImage},</if>
            <if test="projectDetailImage != null and projectDetailImage != ''">#{projectDetailImage},</if>
            <if test="projectIsEnable != null">#{projectIsEnable},</if>
            <if test="createUser != null and createUser != ''">#{createUser},</if>
            <if test="updateUser != null and updateUser != ''">#{updateUser},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="isDelete != null">#{isDelete},</if>
         </trim>
    </insert>

    <update id="updateMaProject" parameterType="MaProject">
        update ma_project
        <trim prefix="SET" suffixOverrides=",">
            <if test="projectName != null and projectName != ''">project_name = #{projectName},</if>
            <if test="projectDescribe != null and projectDescribe != ''">project_describe = #{projectDescribe},</if>
            <if test="projectDuration != null">project_duration = #{projectDuration},</if>
            <if test="projectUsersNum != null">project_users_num = #{projectUsersNum},</if>
            <if test="projectOriginalPrice != null">project_original_price = #{projectOriginalPrice},</if>
            <if test="projectCurrentPrice != null">project_current_price = #{projectCurrentPrice},</if>
            <if test="projectMasterImage != null and projectMasterImage != ''">project_master_image = #{projectMasterImage},</if>
            <if test="projectDetailImage != null and projectDetailImage != ''">project_detail_image = #{projectDetailImage},</if>
            <if test="projectIsEnable != null">project_is_enable = #{projectIsEnable},</if>
            <if test="createUser != null and createUser != ''">create_user = #{createUser},</if>
            <if test="updateUser != null and updateUser != ''">update_user = #{updateUser},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="isDelete != null">is_delete = #{isDelete},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="deleteMaProjectById" parameterType="Long">
        update ma_project set is_delete = 1 where id = #{id}
    </update>

    <delete id="deleteMaProjectByIds" parameterType="String">
        delete from ma_project where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <!-- 推荐项目-->
    <select id="getMassageProjectRecommend" resultType="com.ylx.massage.domain.vo.MassageProjectRecommendVo">
        SELECT
            pro.id                      AS projectId,
            pro.title                   AS projectName,
            pro.standard_duration       AS projectDuration,
            COUNT(o.id)                 AS sales,
            AVG(mp.project_current_price) AS avgCurrentPrice,
            pro.highlight               AS highlight
        FROM project pro
        INNER JOIN ma_project mp ON CAST(mp.project_id AS UNSIGNED) = pro.id AND mp.merchant_type = 1 AND mp.is_delete = 0 AND mp.project_is_enable = 1
        LEFT JOIN t_order o ON o.project_id = pro.id AND o.project_type = 1 AND o.status = 6 AND o.is_delete = 0
        WHERE pro.is_delete = 0
        AND EXISTS (
                SELECT 1 FROM ma_technician tech
                WHERE tech.id = CAST(mp.merchant_id AS UNSIGNED)
                  AND tech.te_area_code = #{cityCode}
                  AND tech.is_delete = 0
                  AND tech.audit_status = 2      -- 审核通过
                  AND tech.service_state = 1     -- 可服务
            )
        GROUP BY pro.id, pro.title, pro.standard_duration, pro.highlight
        ORDER BY sales DESC, pro.id ASC
    </select>
    <!-- 首页按摩推荐商户点击全部查询按摩所有商户-->
    <select id="getMassageAllMerchants" resultType="com.ylx.massage.domain.vo.MassageAllMerchantsVo">
        SELECT
        projectId,
        projectName,
        merchantName,
        merchantId,
        nNum,
        CASE
        WHEN distance_meters IS NOT NULL AND distance_meters >= 1000 THEN
        CONCAT(ROUND(distance_meters / 1000.0, 1), 'km')
        WHEN distance_meters IS NOT NULL THEN
        CONCAT(distance_meters, 'm')
        ELSE NULL
        END AS distanceShow,
        teAvatar
        FROM (
        SELECT
        p.project_id      AS projectId,
        p.project_name    AS projectName,
        t.te_name         AS merchantName,
        t.id              AS merchantId,
        COALESCE(stat.order_count, 0) AS nNum,
        CASE
        WHEN #{dto.longitude} IS NOT NULL AND #{dto.latitude} IS NOT NULL
        THEN ROUND(
        ST_Distance_Sphere(
        POINT(addr.longitude, addr.latitude),
        POINT(#{dto.longitude}, #{dto.latitude})
        ), 0
        )
        ELSE NULL
        END AS distance_meters,
        t.te_avatar       AS teAvatar
        FROM ma_project p
        INNER JOIN ma_technician t ON p.merchant_id = CAST(t.id AS CHAR)
        LEFT JOIN (
        SELECT
        merchant_id,
        longitude,
        latitude
        FROM t_address
        WHERE user_type = 2
        AND type = 1
        AND is_delete = 0
        ) addr ON t.id = addr.merchant_id
        LEFT JOIN (
        SELECT
        merchant_id,
        COUNT(*) AS order_count
        FROM t_order
        WHERE status = 6
        AND project_type = 1
        AND is_delete = 0
        GROUP BY merchant_id
        ) stat ON stat.merchant_id = t.id
        WHERE p.audit_status = 1
        AND p.is_delete = 0
        AND p.merchant_type = 1
        AND p.project_is_enable = 0
        AND t.audit_status = 2
        AND t.is_delete = 0
        AND t.service_state = 1
        AND t.te_area_code = #{dto.cityCode}
        AND addr.merchant_id IS NOT NULL
        <if test="dto.projectId != null and dto.projectId != ''">
            AND p.project_id = #{dto.projectId}
        </if>
        ) AS sub
        ORDER BY nNum DESC;
    </select>

    <select id="selectMerchantList" resultType="com.ylx.project.domain.bookMerchant.vo.BookMerchantVO">
        SELECT
        p.id,
        t.id AS merchantId,
        t.te_nick_name AS merchantNickName,
        t.te_avatar AS merchantAvatar,
        t.n_star AS merchantStar,
        t.n_num AS servedCount,
        p.project_id AS projectId,
        p.project_current_price AS currentPrice,
        <!-- 距离展示文本：米/千米格式化 -->
        CASE calc.distanceMeter
        WHEN 999999999 THEN '未知'
        WHEN calc.distanceMeter &lt; 1000 THEN CONCAT(ROUND(calc.distanceMeter, 0), 'm')
        ELSE CONCAT(ROUND(calc.distanceMeter / 1000, 2), 'km')
        END AS distanceStr,
        calc.distanceMeter AS distanceText
        FROM ma_technician t
        INNER JOIN ma_project p ON t.id = p.merchant_id
        LEFT JOIN t_address addr ON t.id = addr.merchant_id AND addr.type = 1 AND addr.is_delete = 0
        <!-- 子查询统一计算距离，消除公式重复 -->
        LEFT JOIN (
        SELECT
        addr_sub.id AS addrId,
        <!-- 有用户坐标+技师有效坐标才计算距离，否则给超大值 -->
        <if test="dto.latitude != null and dto.longitude != null and dto.latitude != 0 and dto.longitude != 0">
            IF(addr_sub.latitude IS NULL OR addr_sub.longitude IS NULL OR addr_sub.latitude=0 OR addr_sub.longitude=0,
            999999999,
            ROUND(6371 * ACOS(
            COS(RADIANS(#{dto.latitude})) * COS(RADIANS(addr_sub.latitude))
            * COS(RADIANS(addr_sub.longitude) - RADIANS(#{dto.longitude}))
            + SIN(RADIANS(#{dto.latitude})) * SIN(RADIANS(addr_sub.latitude))
            ) * 1000)
        </if>
        <if test="dto.latitude == null or dto.longitude == null or dto.latitude == 0 or dto.longitude == 0">
            999999999
        </if> AS distanceMeter
        FROM t_address addr_sub
        ) calc ON addr.id = calc.addrId

        WHERE
        p.project_id = #{dto.projectId}
        AND p.audit_status = 1
        AND p.is_delete = 0
        AND addr.latitude != 0 AND addr.longitude != 0

        <!-- 动态排序 -->
        <trim prefix="ORDER BY">
            <choose>
                <when test="dto.latitude != null and dto.longitude != null and dto.latitude != 0 and dto.longitude != 0">
                    distanceMeter ASC, t.n_num DESC
                </when>
                <otherwise>
                    t.n_num DESC
                </otherwise>
            </choose>
        </trim>
    </select>
</mapper>
