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

/*
您是一位专业的dba，同时会业务分析。请参考以上数据库的设计文档，给我写一个sql，关联查询出：xxx字段
AI一般会出现的问题 用户表是member_users表  技师名称最终实在用户表找到的


店铺名称是order表通过shop_id关联shop_auth_records表并判断shop_id是否存在存在则获取shop_name字段为店铺名称不存在则空着即可，
项目名称是order表的service_id关联shop_services表并判断service_id是否存在，存在则获取name字段为项目名称不存在则空着即可，
下单时间是order表id关联order_records表并判断state字段的状态为用户下单获取created_at字段为下单时间，
下单金额是order表中total_amount字段，技师名称是order表通过coach_id关联coach_users表并获取到user_id再通过user_id去关联member_users表获取nickname字段做为技师名称，
到达时间是order表id关联order_records表并state状态为已到达获取created_at字段为到达时间，区域是order表中的area_code字段，
位置是order表中的address字段，店铺联系电话是order表通过shop_id关联shop_auth_records表并判断shop_id是否存在，
存在则获取contact_phone字段为店铺联系电话不存在则空着即可，
技师联系方式是order表coach_id关联coach_users表获取到的user_id再通过user_id关联member_users表获取mobile字段做为技师联系方式，
距离是order表中distance字段，平台收入是order表通过id关联wallet_split_records并判断订单编号是否存在，
存在则通过判断split_type分账类型去获取对应的split_amount分账金额字段做为平台收入
*/

/* 
查询订单相关信息包括:
店铺名称、项目名称、下单时间、下单金额、技师名称、到达时间、区域、位置、
店铺联系电话、技师联系方式、距离、平台收入
*/
DROP TABLE IF EXISTS report_shop_emergency_order;
CREATE TABLE report_shop_emergency_order AS
WITH order_times AS (
    SELECT 
        order_id,
        MAX(CASE WHEN state = 'USER_SUBMIT' THEN created_at END) AS submit_time /* 下单时间 */,
        MAX(CASE WHEN state = 'COACH_ARRIVED' THEN created_at END) AS arrive_time /* 到达时间 */
    FROM order_records
    WHERE state IN ('USER_SUBMIT', 'COACH_ARRIVED')
    GROUP BY order_id
)
SELECT 
    ord.id as order_id /* 订单编号 */,
    COALESCE(sar.shop_name, '') as shop_name /* 店铺名称 */,
    COALESCE(ss.name, '') as service_name /* 项目名称 */,
    ot.submit_time as submit_time /* 下单时间 */,
    ord.total_amount as total_amount /* 下单金额 */,
    COALESCE(mu.nickname, '') as coach_name /* 技师名称 */,
    ot.arrive_time as arrive_time /* 到达时间 */,
    ord.area_code as area_code /* 区域 */,
    ord.address as address /* 位置 */,
    COALESCE(sar.contact_phone, '') as shop_phone /* 店铺联系电话 */,
    COALESCE(mu.mobile, '') as coach_phone /* 技师联系方式 */,
    ord.distance as distance /* 距离 */,
    COALESCE(wsr.split_amount, 0.00) as platform_income /* 平台收入 */
FROM `order` ord /* 使用反引号避免关键字冲突，使用别名ord */
LEFT JOIN shop_auth_records sar ON ord.shop_id = sar.shop_id /* 关联店铺认证信息 */
LEFT JOIN shop_services ss ON ord.service_id = ss.id /* 关联店铺服务 */
LEFT JOIN order_times ot ON ord.id = ot.order_id /* 关联订单时间 */
LEFT JOIN coach_users cu ON ord.coach_id = cu.id /* 关联技师用户 */
LEFT JOIN member_users mu ON cu.user_id = mu.id /* 关联会员用户 */
LEFT JOIN wallet_split_records wsr ON ord.id = wsr.order_id 
    AND wsr.split_type = 'PLATFORM_SHARE' /* 关联钱包分账记录-平台分成 */
WHERE 1=1
/* 这里可以添加其他筛选条件，如时间范围、订单状态等 */
ORDER BY ord.id DESC /* 按订单编号倒序排序 */