---

SELECT sharers.id AS sharer_id
	, latest_invest_strategy."name" AS invest_strategy
	, sharer_pens_scheme.id AS scheme_id
	, sharer_pens_scheme."name" AS pension_scheme
	, sharer_pens_scheme.index_scheme AS pension_scheme_index
	, latest_obligation_type.label AS obligation_type
	, latest_sharer_class."name" AS sharer_class
	, latest_contract_model."label" AS contract_model
	, pl_warrant_periods.warranty_period AS warranty_period
	, indexation.param_value AS indexation_value
	, redemption_right.param_value IS NOT NULL AS redemption_right
FROM ourpension.sharer sharers
JOIN ourpension.contract contracts ON contracts.id = sharers.contract_id 
LEFT JOIN ourpension.sharer_params s_params ON s_params.sharer_id = sharers.id
LEFT JOIN ourpension.contract_params c_params ON c_params.contract_id = contracts.id
LEFT JOIN LATERAL (
	SELECT pl_schemas.id
		, pl_schemas."name"
		, pl_schemas.index_scheme	
		, pens_products.id AS product_id
	FROM ourpension.sharer_product_history history
	JOIN prod_line.pens_product pens_products ON pens_products.id = history.pens_product_id
	JOIN prod_line.pens_scheme pl_schemas ON pl_schemas.id = pens_products.pens_scheme_accum_stage_id 
	WHERE history.sharer_id = sharers.id
		AND CURRENT_TIMESTAMP BETWEEN history.begin_date AND COALESCE( history.end_date, 'infinity' ) 
	LIMIT 1
) sharer_pens_scheme ON true
LEFT JOIN prod_line.table_param_pl_warrant_period pl_warrant_periods ON pl_warrant_periods.pens_program_id = sharer_pens_scheme.product_id
-- prod_line.pens_product_param_pl TO-DO
LEFT JOIN LATERAL (
	SELECT invest_s."name"
	FROM ourpension.invest_strategy_history_sharer history
	JOIN prod_line.hb_invest_strategy invest_s ON invest_s.id = history.strategy_id
	WHERE history.sharer_id = sharers.id
		AND CURRENT_TIMESTAMP BETWEEN history.begin_date AND COALESCE( history.end_date, 'infinity' ) 
	LIMIT 1
) latest_invest_strategy ON true
LEFT JOIN LATERAL (
	SELECT obligation_types."label"
 	FROM ourpension.pension_obligation_types_history history
	JOIN ourpension.voc_pension_obligation_types obligation_types ON obligation_types.id = history.type_id
	WHERE history.sharer_id = sharers.id
		AND CURRENT_TIMESTAMP BETWEEN history.start_date AND COALESCE( history.end_date, 'infinity' ) 
	LIMIT 1
) latest_obligation_type ON true
LEFT JOIN LATERAL (     
		SELECT c_classes."name"
		FROM ourpension.sharer_class_history history
		JOIN ourpension.contract_classes c_classes ON c_classes.id = history.class_id
		WHERE history.sharer_id = sharers.id
			AND CURRENT_TIMESTAMP BETWEEN history.begin_date AND COALESCE( history.end_date, 'infinity' ) 
		LIMIT 1
) latest_sharer_class ON true
LEFT JOIN LATERAL (
	SELECT contract_models."label"
 	FROM ourpension.contract_models_history history
	JOIN ourpension.voc_contract_models contract_models ON contract_models.id = history.model_id
	WHERE sharer_id = sharers.id
		AND CURRENT_TIMESTAMP BETWEEN history.start_date AND COALESCE( history.end_date, 'infinity' ) 
	LIMIT 1
) latest_contract_model ON true
LEFT JOIN LATERAL (
	SELECT pp2."name" AS param_name
		, pp2.brief
		, ( SELECT * FROM prod_line.get_value_link_param( CAST( pp2.id AS integer ), CAST( pppp.value_ref AS integer ) ) ) AS param_value
		, pppp.pens_product_id AS product_id
	FROM prod_line.pens_product_param_pl pppp   
	JOIN prod_line.pens_product pp ON pp.id = pppp.pens_product_id 
	JOIN prod_line.param_pl pp2 ON pp2.id = pppp.param_pl_id     
	WHERE pp.id = sharer_pens_scheme.product_id
			AND pp2.brief = 'Индексация'
) indexation ON true
LEFT JOIN LATERAL (
	 SELECT pp2."name" AS param_name
		, pp2.brief
		, ( SELECT * FROM prod_line.get_value_link_param( CAST( pp2.id AS integer ), CAST( pppp.value_ref AS integer ) ) ) AS param_value
		, pppp.pens_product_id AS product_id
	FROM prod_line.pens_product_param_pl pppp   
	JOIN prod_line.pens_product pp ON pp.id = pppp.pens_product_id 
	JOIN prod_line.param_pl pp2 ON pp2.id = pppp.param_pl_id     
	WHERE pp.id = sharer_pens_scheme.product_id
			AND pp2.brief = 'ЕстьПравоНаВыкупнуюУчастник' 
) redemption_right ON true
WHERE sharers.id = :id_sharer