--explain analyze
with 
status(status, status_id, probability_pc) as (VALUES
	('Закрыта неуспешно',7,0),('Закрыта успешно',6,100),('Договор подписан',5,100),('Договор на подписании',15,90),('Договор на согласовании',12,70),
	('Тестирование',8,50),('Отправлено ТКП',10,20),('Внутреннее согласование',37,10),('Проработка решения',4,10),('Сбор потребностей',1,0)
),
begin_of_scale (dt) as (values ('2025-01-01'::timestamp)), -- этот изыск не дает видимого ускорения
end_of_scale (dt) as (values ('2025-12-02'::timestamp)), -- этот изыск не дает видимого ускорения
dayscale as (
	SELECT dd::date as dt
	--, DATE_PART('days', DATE_TRUNC('month', dd) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL) monthdays 
	FROM begin_of_scale, end_of_scale, generate_series( 
	 begin_of_scale.dt
	,end_of_scale.dt
	,'1 day'::interval) dd
),
line as (
	select siv.__id as siv_uid, siv.discount, siv.type as line_pricing_model_id
	,siv.hash as line_key, siv.user_description, siv.is_actual as siv_is_actual
	,siv.version::numeric as line_version
	, GREATEST(siv.date_nop,d."_plannedDueDate")::date as dt_from
	, COALESCE(siv.date_end::date, end_of_scale.dt) as dt_to
	,siv.is_easy as is_simple, siv.cnt as quantity 
	, a.__id as agreement_uid, a.contract_uid, a.is_actual as agreement_is_actual, a.deal_uid, a.index as agreement_version
	,d."_plannedDueDate", d.__name as deal, d.__status_status as deal_status_id
	,c.__name as contract
	,s.probability_pc
	,(select sum(p.price*p.cnt*(100-p.discount)/100) from elma.service_parametrs p where(p.__id = ANY (siv.params))) as cost
	from elma.deals_services siv
	join elma.additional_agreements a on (a.__id = ANY(siv.additional_agreement)) 
	join elma.deals d on (d.__id = ANY(siv.deal)) 
	join elma.aggrements c on (a.contract_uid = c.__id)
	join status s on (d.__status_status=s.status_id)
	cross join end_of_scale
	where siv.is_actual
)
-- 20241011T092847849 очень странный пример строки ... может быть, вообще не нужно для инсталлов брать базовую сделку, а просто рабочую с ее вероятностью
-- 20241011T091519330 чрезвычайно странная строка. Похоже, для инсталла сплошь и рядом меняют артикул ... но при этом прошлый инсталл будет пропадать из истории? если его игнорировать
select dayscale.dt
,(o.cost_base*(100-COALESCE(o.probability_pc,0))) as sum_base
,(
	COALESCE((o.cost*o.quantity*(100-coalesce(o.discount,0))/100*o.probability_pc/100),0)	
	+
	COALESCE((o.cost_base*o.quantity_base*(100-coalesce(o.discount_base,0))/100*(100-COALESCE(o.probability_pc,0))/100),0)
	) as sum_bounded
, cost, quantity, discount, cost_base, quantity_base, discount_base,  *
/*
,sum (o.cost*o.probability_pc/100) as sum_unbounded
,sum(o.cost*o.probability_pc/100) as sum_working
,sum(o.cost_base*(100-COALESCE(o.probability_pc,0))) as sum_base
,(
	COALESCE(sum(o.cost*o.quantity*(100-coalesce(o.discount,0))/100*o.probability_pc/100),0)	
	+
	COALESCE(sum(o.cost_base*o.quantity_base*(100-coalesce(o.discount_base,0))/100*(100-COALESCE(o.probability_pc,0))),0)
	) as sum_bounded
,max(o.dt_from) as max_dt_from, max(o.dt_from_base) as max_dt_from_base, max(o.probability_pc) as max_probability_pc
,count(*)
*/
from dayscale
LEFT OUTER JOIN (
	SELECT t.*,
	b.discount as discount_base, b.quantity as quantity_base, b.dt_from as dt_from_base, b.dt_to as dt_to_base, b.cost as cost_base
	FROM line t 
	LEFT OUTER JOIN line b on (t.line_key=b.line_key AND t.line_pricing_model_id=b.line_pricing_model_id AND b.siv_uid=(
			select siv_uid 
	 		FROM line i 
			where t.line_key=i.line_key AND t.line_pricing_model_id=i.line_pricing_model_id AND i.siv_is_actual AND i.probability_pc=100 AND i.agreement_is_actual AND i.deal_status_id in (5,6)		 
			AND ((t.agreement_version > i.agreement_version) OR (t.agreement_version = i.agreement_version AND t.line_version > i.line_version))
			ORDER BY i.agreement_version desc, i.line_version desc
			LIMIT 1
		)
	) 
	where 1=1
	AND t.line_pricing_model_id in (1)
	AND t.probability_pc > 0 -- несколько искусственное условие, убирающее несущественные и неуспешные сделки
) o 
on (dayscale.dt=o.dt_from) 
--where o.contract_uid='01927a86-23c9-729b-a416-a93b71e0c91a'
--group by dayscale.dt
order by dayscale.dt, agreement_uid