<?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
            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,
            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>
        ORDER BY stat.order_count DESC
    </select>
    <select id="selectMerchantList" resultType="com.ylx.project.domain.bookMerchant.vo.BookMerchantVO">
        SELECT
            mp.id,
            mp.merchant_id AS merchantId,
            m.te_nick_name AS merchantNickName,
            m.te_avatar AS merchantAvatar,
            m.n_star AS merchantStar,
            mp.project_current_price AS currentPrice,
            mp.project_users_num AS servedCount
        FROM ma_project mp
        LEFT JOIN ma_technician m ON mp.merchant_id = m.id
        WHERE mp.project_id = #{projectId}
          AND mp.audit_status = 1      -- 仅展示审核通过的
          AND mp.project_is_enable = 0 -- 未申请下架
          AND mp.is_delete = 0
        ORDER BY mp.project_users_num DESC -- 按真实销量降序排列
    </select>
</mapper>
