---

WITH stages_CTE AS (
	
	SELECT coeffs.id AS id
		, coeffs.id AS table_vesting_coeffs_id
		, '1' AS stage_number
		, coeffs.transfer_time 
		, coeffs.contributions_percent_amount 
		, coeffs.investments_percent_amount 
	FROM prod_line.table_vesting_coeffs coeffs
		UNION ALL 
	SELECT stages.id 
		, coeffs.id AS table_vesting_coeffs_id
		, stages.stage_number
		, stages.transfer_time
		, stages.contributions_percent_amount
		, stages.investments_percent_amount
	FROM prod_line.table_vesting_coeffs coeffs
	LEFT JOIN prod_line.table_vesting_coeffs_stages stages ON stages.table_vesting_coeffs_id = coeffs.id
	ORDER BY stage_number ASC
	
), summ_CTE AS (
	
	SELECT id
		, table_vesting_coeffs_id
		, contributions_percent_amount
		, investments_percent_amount
		, transfer_time
		, stage_number
		, LAG(CAST(transfer_time AS int), 1, 0 ) OVER(PARTITION BY table_vesting_coeffs_id ORDER BY stage_number ASC)
		, SUM(CAST(transfer_time AS int)) OVER (PARTITION BY table_vesting_coeffs_id ORDER BY stage_number ASC) AS s
	FROM stages_CTE
	
), contat_CTE AS (

	SELECT t.id
		, t.table_vesting_coeffs_id
		, t.contributions_percent_amount
		, t.investments_percent_amount
		, t.transfer_time
		, t.stage_number
		, LAG(CAST(t.transfer_time AS int), 1, 0 ) OVER(PARTITION BY t.table_vesting_coeffs_id ORDER BY t.stage_number ASC)
		, SUM(CAST(t.transfer_time AS int)) OVER (PARTITION BY t.table_vesting_coeffs_id ORDER BY t.stage_number ASC) - LAG(CAST(t.transfer_time AS int), 1, 0 )  OVER(PARTITION BY t.table_vesting_coeffs_id ORDER BY t.stage_number ASC) AS sm
		, summ_CTE.s
		, CONCAT(
			'ЭТАП ', CAST(t.stage_number AS text)
			, ' (количество месяцев: ', t.transfer_time, ')'
			, ' от ', LAG(summ_CTE.s, 1, 0 ) OVER(PARTITION BY t.table_vesting_coeffs_id ORDER BY t.stage_number ASC)
			, ' до ', summ_CTE.s, ' месяцев; '
			, 'ИД %: ', COALESCE(t.investments_percent_amount, 0), '; '
			, 'Взносы %: ', COALESCE(t.contributions_percent_amount, 0), ';') AS str
	FROM stages_CTE t
	JOIN summ_CTE ON summ_CTE.id = t.id

)
SELECT
  coeffs.id as id,
  contracts.number as contract_number,
  TO_CHAR( COALESCE( c_params.date_start_vesting, contracts.date ), 'DD.MM.YYYY' ) as date_start_vesting,
  amount_types.name as amount_type,
  coeffs.transfer_time as transfer_time,
  coeffs.investments_percent_amount as investments_percent_amount,
  coeffs.contributions_percent_amount as contributions_percent_amount,
  coeffs.contract_id as id_contract,
  CASE is_close_account WHEN TRUE THEN 'Да' ELSE 'Нет' END as is_close_account,
  coeffs.amount_type as amount_types,
  coeffs_stages.transfer_times as transfer_times,
  coeffs_stages.contributions_percent_amounts as contributions_percent_amounts,
  coeffs_stages.investments_percent_amounts as investments_percent_amounts,
  coeffs_stages.stage_numbers as stage_numbers,
  labels.label_stages as label_stages 
FROM prod_line.table_vesting_coeffs coeffs
JOIN ourpension.contract contracts ON contracts.id = coeffs.contract_id
LEFT JOIN ourpension.contract_params c_params ON c_params.contract_id = contracts.id
LEFT JOIN LATERAL (
	SELECT STRING_AGG(at.name, '; ')
	FROM back_office.account_type at
	WHERE at.id IN (
		SELECT CAST(subquery.id AS int) FROM JSON_ARRAY_ELEMENTS_TEXT(coeffs.amount_type) AS subquery(id)
	)
) amount_types(name) ON true
LEFT JOIN LATERAL (
	SELECT JSON_AGG(coeffs_stages.transfer_time) AS transfer_times
		, JSON_AGG(coeffs_stages.contributions_percent_amount) AS contributions_percent_amounts
		, JSON_AGG(coeffs_stages.investments_percent_amount) AS investments_percent_amounts
		, JSON_AGG(coeffs_stages.stage_number) AS stage_numbers
	FROM prod_line.table_vesting_coeffs_stages coeffs_stages
	WHERE coeffs_stages.table_vesting_coeffs_id = coeffs.id
) coeffs_stages ON true
LEFT JOIN LATERAL (
	SELECT STRING_agg(contat_CTE.str, E' <br>') AS label_stages
	FROM contat_CTE
	WHERE contat_CTE.table_vesting_coeffs_id = coeffs.id
) labels ON true
WHERE contracts.id IS NOT NULL
#if ($id_contract && !$id_contract.isEmpty())
	AND contracts.id = :id_contract
#end