<?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="shop.alien.mapper.LifeUserMapper">

    <!-- 查询用户列表，支持分页和动态条件 -->
    <select id="getUserOrderDetail" resultType="shop.alien.entity.store.vo.LifeUserOrderVo">
        WITH OrderDetails AS (
        SELECT
        uo.user_id,
        coupon.id AS couponId,
        coupon.NAME AS couponName,
        store.store_name,
        su.phone,
        uo.buy_time,
        uo.used_time,
        uo.final_price,
        uo.status,
        CASE
        WHEN coupon.type = 1 THEN '代金券'
        WHEN coupon.type = 2 THEN '团购（套餐）'
        ELSE '其他类型'
        END AS type
        FROM
        life_user_order uo
        LEFT JOIN life_coupon coupon ON uo.quan_id = coupon.id
        LEFT JOIN store_info store ON coupon.store_id = store.id
        LEFT JOIN store_user su ON su.store_id = store.id
        WHERE
        1 = 1
        <if test="userId != null and userId != ''">
            and uo.user_id = #{userId}
        </if>
        )
        SELECT
        COUNT(1) AS purchaseQuantity,
        user_id,
        couponId,
        couponName,
        store_name,
        phone as user_phone,
        buy_time,
        status,
        used_time,
        ROUND(SUM(final_price), 2) AS total_final_price,
        type
        FROM
        OrderDetails
        GROUP BY
        user_id,
        couponId,
        couponName,
        store_name,
        phone,
        buy_time,
        status,
        used_time,
        type;
    </select>

    <!-- 查询已删除的用户 -->
    <select id="getRemoveUser" resultType="shop.alien.entity.store.vo.LifeUserVo">
        select concat(user_name,"(账号已注销)") as name,user_phone FROM life_user where id = #{id}
    </select>

</mapper>