-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test

/*
用户类型是order表中的order_source字段，用户昵称/店铺是通过order的order_source字段判断是用户通过平台下单是店铺在平台下应急订单，
用户昵称是order表通过user_id关联member_users表获取nickname字段为用户名称，
店铺名称是order表通过shop_id关联shop_auth_records并判断shop_id是否存在存在则获取shop_name字段为店铺名称不存在则空着即可，
订单类型是order表中的order_type字段，项目名称是order表的service_id关联shop_services表并判断是否存在service_id存在则获取name为项目名称不存在则空着即可，
订单金额是order表total_amount字段，到店时间是order表id关联order_records表并判断状态state为已到店状态时的created_at字段为到店时间，
订单状态是order表id关联order_records表最新的状态state,
店铺分成、推广分成、平台抽成都是order表id关联wallet_split_records通过该表中的split_type不同的分账类型去参与分成最后split_amount字段就是店铺分成、推广分成、平台抽成最终字段
*/

/* 
基础订单信息与分账记录统计查询
*/
DROP TABLE IF EXISTS report_shop_order;
CREATE TABLE report_shop_order AS
WITH order_arrival AS (
    /* 获取订单到店记录 */
    SELECT 
        order_id,
        created_at AS arrival_time,
        state
    FROM order_records 
    WHERE state = 'ARRIVED' /* 已到店状态 */
),
latest_order_status AS (
    /* 获取订单最新状态 */
    SELECT 
        order_id,
        state AS latest_state
    FROM order_records 
    WHERE (order_id, created_at) IN (
        SELECT 
            order_id,
            MAX(created_at)
        FROM order_records 
        GROUP BY order_id
    )
),
split_amounts AS (
    /* 获取各类分账金额 */
    SELECT 
        order_id,
        MAX(CASE WHEN split_type = 'SHOP' THEN split_amount ELSE 0 END) AS shop_amount /* 店铺分成 */,
        MAX(CASE WHEN split_type = 'PROMOTION' THEN split_amount ELSE 0 END) AS promotion_amount /* 推广分成 */,
        MAX(CASE WHEN split_type = 'PLATFORM' THEN split_amount ELSE 0 END) AS platform_amount /* 平台抽成 */
    FROM wallet_split_records
    GROUP BY order_id
)
SELECT 
    o.order_source as order_source /* 用户类型 */,
    CASE 
        WHEN o.order_source = 'USER' THEN mu.nickname 
        WHEN o.order_source = 'SHOP' THEN sar.shop_name 
        ELSE NULL 
    END as source_name /* 用户昵称/店铺名称 */,
    o.order_type as order_type /* 订单类型 */,
    ss.name as service_name /* 项目名称 */,
    o.total_amount as total_amount /* 订单金额 */,
    oa.arrival_time as arrival_time /* 到店时间 */,
    los.latest_state as latest_state /* 订单状态 */,
    COALESCE(sa.shop_amount, 0) as shop_amount /* 店铺分成 */,
    COALESCE(sa.promotion_amount, 0) as promotion_amount /* 推广分成 */,
    COALESCE(sa.platform_amount, 0) as platform_amount /* 平台抽成 */
FROM `order` o
LEFT JOIN member_users mu ON o.user_id = mu.id /* 关联用户信息 */
LEFT JOIN shop_auth_records sar ON o.shop_id = sar.shop_id /* 关联店铺信息 */
LEFT JOIN shop_services ss ON o.service_id = ss.id /* 关联服务项目 */
LEFT JOIN order_arrival oa ON o.id = oa.order_id /* 关联到店记录 */
LEFT JOIN latest_order_status los ON o.id = los.order_id /* 关联最新状态 */
LEFT JOIN split_amounts sa ON o.id = sa.order_id /* 关联分账信息 */
ORDER BY o.id DESC /* 按订单ID倒序排列 */;