-- 数据范围：各项目生产配置指向的主业务库；不查询 aizhct_* 辅助库。
-- 时间范围：全量历史数据，不限制订单状态、支付状态或日期。
-- 时区：业务库原始日期字段，项目约定为 Asia/Shanghai。

-- 1. 用户数
SELECT COUNT(*) AS user_num
FROM ydy_staff;

-- 2. 订单数、订单金额
SELECT
    COUNT(*) AS order_num,
    COALESCE(SUM(total_price), 0) AS total_price,
    MIN(meal_date) AS first_meal_date,
    MAX(meal_date) AS last_meal_date
FROM ydy_meal_order;

-- 3. 订单菜品统计
-- 订单明细表：ydy_initial_menu
-- 菜品数量：number 之和；菜品重量：weight 之和（克）；菜品种类：不同 dishes_uuid 数量。
-- 营养数据按系统现有小票统计口径：ydy_dishes 单份营养值 × number。
SELECT
    COUNT(*) AS order_dish_row_num,
    COALESCE(SUM(CAST(im.number AS DECIMAL(20,4))), 0) AS dish_quantity,
    COALESCE(SUM(CAST(im.weight AS DECIMAL(20,4))), 0) AS dish_weight_g,
    COUNT(DISTINCT NULLIF(im.dishes_uuid, '')) AS dish_type_num,
    COALESCE(SUM(COALESCE(d.energy, 0) * CAST(im.number AS DECIMAL(20,4))), 0) AS energy_kcal,
    COALESCE(SUM(COALESCE(d.carbohydrate, 0) * CAST(im.number AS DECIMAL(20,4))), 0) AS carbohydrate_g,
    COALESCE(SUM(COALESCE(d.protein, 0) * CAST(im.number AS DECIMAL(20,4))), 0) AS protein_g,
    COALESCE(SUM(COALESCE(d.fat, 0) * CAST(im.number AS DECIMAL(20,4))), 0) AS fat_g,
    SUM(CASE WHEN d.uuid IS NULL THEN 1 ELSE 0 END) AS unmatched_dish_row_num
FROM ydy_initial_menu AS im
INNER JOIN ydy_meal_order AS mo
    ON mo.id = im.meal_order_id
LEFT JOIN (
    SELECT
        uuid,
        MAX(energy) AS energy,
        MAX(carbohydrate) AS carbohydrate,
        MAX(protein) AS protein,
        MAX(fat) AS fat
    FROM ydy_dishes
    GROUP BY uuid
) AS d
    ON d.uuid = im.dishes_uuid;

-- 4. 每天、分餐段的订单数和订单金额
-- meal_times：1 早餐，2 午餐，3 晚餐，4 夜宵；其他值保留为“其他(n)”。
SELECT
    meal_date,
    meal_times,
    CASE meal_times
        WHEN 1 THEN '早餐'
        WHEN 2 THEN '午餐'
        WHEN 3 THEN '晚餐'
        WHEN 4 THEN '夜宵'
        ELSE CONCAT('其他(', meal_times, ')')
    END AS meal_segment,
    COUNT(*) AS order_num,
    COALESCE(SUM(total_price), 0) AS total_price
FROM ydy_meal_order
GROUP BY meal_date, meal_times
ORDER BY meal_date, meal_times;

-- 5. 回算校验：按日餐段汇总必须与订单总表一致
SELECT
    SUM(x.order_num) AS reconciled_order_num,
    SUM(x.total_price) AS reconciled_total_price
FROM (
    SELECT
        meal_date,
        meal_times,
        COUNT(*) AS order_num,
        COALESCE(SUM(total_price), 0) AS total_price
    FROM ydy_meal_order
    GROUP BY meal_date, meal_times
) AS x;