-- 和传播链路题目一个思路
with recursive cte as
(select 
    task_id root_task_id,
    station_id root_station_id,
    fault_type,
    task_status,
    handling_minutes,
    task_id,
    0 depth,
    parent_task_id
from fault_dispatch_tasks
where station_id in (select station_id from charging_stations where operator_name = '极曜能源')
    and parent_task_id is null
    and date(dispatched_at) >= '2025-11-05'
    and date(dispatched_at) < '2025-11-10'
union all
select 
    cte.root_task_id root_task_id,
    cte.root_station_id root_station_id,
    fdt.fault_type fault_type,
    fdt.task_status task_status,
    fdt.handling_minutes handling_minutes,
    fdt.task_id task_id,
    cte.depth + 1 depth,
    fdt.parent_task_id parent_task_id
from fault_dispatch_tasks fdt
inner join cte on fdt.parent_task_id = cte.task_id)
select 
    a.root_task_id root_task_id,
    cs.station_name station_name,
    a.total_task_count total_task_count,
    a.max_depth max_depth,
    b.resolved_leaf_count resolved_leaf_count,
    a.distinct_fault_type_count distinct_fault_type_count,
    a.total_handling_hours total_handling_hours
from
(select 
    root_task_id, 
    root_station_id,
    count(*) total_task_count,
    max(depth) max_depth,
    count(distinct fault_type) distinct_fault_type_count,
    round(sum(handling_minutes) / 60, 2) total_handling_hours
from cte
group by root_task_id, root_station_id) a
left join
(select 
    root_task_id,
    count(case when task_status = 'resolved' then task_id else null end) resolved_leaf_count
from cte t1
 -- 注意not exists 和 not in的区别 
where not exists 
(select 1
from cte t2
where t1.task_id = t2.parent_task_id)
group by root_task_id) b
on a.root_task_id = b.root_task_id
left join charging_stations cs
on a.root_station_id = cs.station_id
order by resolved_leaf_count desc,
    max_depth desc,
    total_task_count desc,
    root_task_id