56 lines
2.5 KiB
SQL
56 lines
2.5 KiB
SQL
-- Разделяем «не выбрал день/время» на три честных состояния:
|
||
-- no_input_day — ответил, день не выбрал
|
||
-- no_input_half — день выбрал, время не успел
|
||
-- no_input_both — в разных звонках не выбрал и день, и время
|
||
-- Значения совпадают с AUTOCALL_STATUS_META / AUTOCALL_STATUS_OPTIONS во фронте
|
||
-- (правило: функция в БД + бейдж + фильтр меняются вместе).
|
||
CREATE OR REPLACE FUNCTION public.autocall_status(og order_groups)
|
||
RETURNS text
|
||
LANGUAGE sql
|
||
STABLE SECURITY DEFINER
|
||
SET search_path TO 'public'
|
||
AS $function$
|
||
select case
|
||
when og.autocall_opt_out then 'opt_out'
|
||
when exists (
|
||
select 1 from public.autocall_exclusions e
|
||
where e.active
|
||
and (
|
||
(e.rule_type = 'phone' and coalesce(og.customer_phone_normalized, regexp_replace(coalesce(og.customer_phone,''),'\D','','g')) = e.value)
|
||
or
|
||
(e.rule_type = 'prefix' and coalesce(og.customer_phone_normalized, regexp_replace(coalesce(og.customer_phone,''),'\D','','g')) like e.value || '%')
|
||
)
|
||
) then 'excluded'
|
||
when not exists (select 1 from public.autocall_log l where l.order_group_id = og.id) then 'not_called'
|
||
when exists (
|
||
select 1 from public.autocall_log l
|
||
where l.order_group_id = og.id
|
||
and l.outcome in ('confirmed','confirmed_silent','confirmed_kept_previous')
|
||
) then 'confirmed'
|
||
else (
|
||
select case
|
||
when l.outcome = 'no_answer' then 'no_answer'
|
||
when l.outcome in ('no_input_day','no_input_half') then
|
||
case
|
||
when exists (select 1 from public.autocall_log l2
|
||
where l2.order_group_id = og.id and l2.outcome = 'no_input_day')
|
||
and exists (select 1 from public.autocall_log l3
|
||
where l3.order_group_id = og.id and l3.outcome = 'no_input_half')
|
||
then 'no_input_both'
|
||
when l.outcome = 'no_input_half' then 'no_input_half'
|
||
else 'no_input_day'
|
||
end
|
||
when l.outcome in ('operator','error','invalid_number') then 'manual'
|
||
else 'not_called'
|
||
end
|
||
from public.autocall_log l
|
||
where l.order_group_id = og.id
|
||
order by l.created_at desc
|
||
limit 1
|
||
)
|
||
end;
|
||
$function$;
|
||
|
||
-- Проверка: сколько заказов в каждом состоянии
|
||
-- select public.autocall_status(og), count(*) from public.order_groups og group by 1 order by 2 desc;
|