-- Function: gosimple.contract_exposure_direct(uuid, boolean, boolean, uuid[])

-- DROP FUNCTION gosimple.contract_exposure_direct(uuid, boolean, boolean, uuid[]);

CREATE OR REPLACE FUNCTION gosimple.contract_exposure_direct(
    IN p_contract_id uuid,
    IN p_stacked_ind boolean,
    IN p_limit_actual_ind boolean,
    IN p_valid_companies uuid[])
  RETURNS TABLE(direct_contract_ids uuid[], direct_rates numeric[], company_id uuid, company_name text, company_number text, product_id uuid, product_name text, product_number text, uom_type_id uuid, uom_type_description text, this_direct_rate numeric, this_estimated_quantity numeric, this_spend numeric, other_direct_rate numeric, total_direct_rate numeric, total_spend numeric, target_rate numeric, claim_ids uuid[], claim_amounts numeric[], total_claim_volume numeric, total_claim_amount numeric) AS
$BODY$
BEGIN
	DROP TABLE IF EXISTS tmp_params;
	DROP TABLE IF EXISTS tmp_valid_companies;
	DROP TABLE IF EXISTS direct_ids;
	DROP TABLE IF EXISTS tmp_price;
	DROP TABLE IF EXISTS direct_info;
	DROP TABLE IF EXISTS tmp_rates;
	DROP TABLE IF EXISTS tmp_base;
	DROP TABLE IF EXISTS contract_actual_direct;


	IF p_contract_id IS NULL
	THEN 
		RAISE EXCEPTION 'p_contract_id cannot be null.';
	END IF;
	
	IF p_valid_companies IS NOT NULL AND p_limit_actual_ind = true
	THEN 
		RAISE EXCEPTION 'Cannot use p_valid_companies and p_limit_actual_ind together.';
	END IF;


	CREATE TEMPORARY TABLE tmp_params AS
	SELECT cf.contractee_company_id, cf.contractee_grouping_id, crv.date_range
	FROM contract_amend_version cav
	INNER JOIN contract_renew_version crv
	ON cav.contract_renew_version_id = crv.contract_renew_version_id
	INNER JOIN contract_family cf
	ON crv.contract_family_id = cf.contract_family_id
	WHERE true
	AND cav.contract_amend_version_id = p_contract_id
	GROUP BY 1, 2, 3;

	RAISE NOTICE 'tmp_params created: %', clock_timestamp();

	CREATE TEMPORARY TABLE contract_actual_direct AS 
	SELECT cad.contract_id, cad.product_id, cad.company_id, cad.claim_ids, cad.claim_amounts, cad.total_claim_volume, cad.total_claim_amount
	FROM gosimple.contract_actual_direct(p_contract_id, p_valid_companies) cad;

	ANALYZE contract_actual_direct;

	RAISE NOTICE 'contract_actual_direct created: %', clock_timestamp();

	CREATE TEMPORARY TABLE tmp_valid_companies AS
	SELECT DISTINCT unnest(p_valid_companies) as company_id
	WHERE p_valid_companies IS NOT NULL

	UNION ALL

	SELECT DISTINCT contract_actual_direct.company_id
	FROM contract_actual_direct
	WHERE p_limit_actual_ind = true

	UNION ALL
	
	SELECT DISTINCT contractee_view_hierarchy.company_id
	FROM contractee_view_hierarchy
	WHERE contract_id = p_contract_id
	AND p_valid_companies IS NULL
	AND p_limit_actual_ind = false;

	ANALYZE tmp_valid_companies;

	RAISE NOTICE 'tmp_valid_companies created: %', clock_timestamp();
			
	CREATE TEMPORARY TABLE direct_ids AS  
	SELECT p_contract_id::uuid as contract_id

	UNION ALL

	SELECT cav.contract_amend_version_id as contract_id
	FROM contract_amend_version cav
	INNER JOIN contract_renew_version crv
	ON cav.contract_renew_version_id = crv.contract_renew_version_id
	INNER JOIN contract_family cf
	ON crv.contract_family_id = cf.contract_family_id
	INNER JOIN tmp_params
	ON true
	WHERE true
	AND cf.contractee_company_id IS NOT DISTINCT FROM tmp_params.contractee_company_id
	AND cf.contractee_grouping_id IS NOT DISTINCT FROM tmp_params.contractee_grouping_id
	AND crv.date_range && tmp_params.date_range
	AND cav.contract_state IN ('APPROVED', 'ACTIVE', 'EXPIRED')
	AND cav.contract_amend_version_id != p_contract_id
	AND EXISTS (
		SELECT 1
		FROM tmp_valid_companies tvc
		INNER JOIN contractee_view_hierarchy conv
		ON tvc.company_id = conv.company_id
		WHERE true
		AND cav.contract_amend_version_id = conv.contract_id
	)
	AND p_stacked_ind = true;

	ANALYZE direct_ids;

	RAISE NOTICE 'direct_ids created: %', clock_timestamp();

	CREATE TEMPORARY TABLE tmp_price (
	identifier uuid -- IN
	, contract_id uuid
	, company_id uuid -- IN
	, product_id uuid -- IN
	, target_uom_type_id uuid -- IN
	, resolve_date date -- IN
	, cost_basis_type enum.cost_basis_type -- IN
	, price_id uuid -- OUT
	, price numeric -- OUT
	);

	INSERT INTO tmp_price(identifier, contract_id, company_id, product_id, target_uom_type_id, resolve_date, cost_basis_type)
	SELECT gen_random_uuid()
	, cp.contract_id
	, vcmp.company_id
	, cp.product_id
	, cp.uom_type_id
	, lower(crv.date_range) + ((upper(crv.date_range) - 1) - lower(crv.date_range)) / 2
	, cf.cost_basis_type
	FROM contract_product cp
	INNER JOIN contract_amend_version cav
	ON cp.contract_id = cav.contract_amend_version_id
	INNER JOIN contract_renew_version crv
	ON cav.contract_renew_version_id = crv.contract_renew_version_id
	INNER JOIN contract_family cf
	ON crv.contract_family_id = cf.contract_family_id
	CROSS JOIN tmp_valid_companies vcmp
	WHERE true
	AND EXISTS (
		SELECT 1
		FROM direct_ids di
		WHERE true
		AND cp.contract_id = di.contract_id
	)
	AND cp.rebate_direct_type != 'NONE'
	AND (cp.rebate_direct_type = 'FIXED_PRICE'::enum.rebate_value_type OR cp.rebate_direct_decimal_model = 'PERCENT'::enum.decimal_model);

	PERFORM gosimple.get_price();

	RAISE NOTICE 'tmp_price created: %', clock_timestamp();

	CREATE TEMPORARY TABLE direct_info AS 
	SELECT x.contract_item_id, gen_random_uuid() as identifier, x.product_id, x.estimated_quantity, x.price, x.uom_type_id, x.company_ids
	FROM (
		SELECT cp.contract_item_id, cp.product_id, cp.estimated_quantity, cpp.price, cp.uom_type_id, array_agg(tvc.company_id) company_ids
		FROM contract_item_view cp
		CROSS JOIN tmp_valid_companies tvc
		LEFT JOIN tmp_price cpp
		ON cp.contract_id = cpp.contract_id
		AND cp.product_id = cpp.product_id
		AND tvc.company_id = cpp.company_id
		WHERE true
		AND ((cp.rebate_direct_type = 'FIXED_PRICE') OR (cp.rebate_direct_value != 0 AND cp.rebate_direct_type = 'FIXED_RATE'))
		AND EXISTS (
			SELECT 1
			FROM direct_ids dcid
			WHERE true
			AND dcid.contract_id = cp.contract_id
		)
		GROUP BY 1, 2, 3, 4, 5
	) x;

	ANALYZE direct_info;

	RAISE NOTICE 'direct_row created: %', clock_timestamp();

	CREATE TEMPORARY TABLE tmp_rates AS 
	SELECT cder.source_row_id as identifier, cder.rate::numeric as direct_rate, cder.explicit_zero_rate_ind
	FROM gosimple.calculate_contract_item_direct_rebate_rate((
		SELECT array_agg(row(
		di.contract_item_id, di.identifier, di.product_id, null, di.estimated_quantity, di.price, di.uom_type_id
		)::gosimple.in_calculate_contract_item_rebate_rate)
		FROM direct_info di
	)) cder;

	ANALYZE tmp_rates;

	RAISE NOTICE 'tmp_rates created: %', clock_timestamp();

	CREATE TEMPORARY TABLE tmp_base AS 
	SELECT ci.contract_id, di.product_id, unnest(di.company_ids) as company_id, tr.direct_rate, ci.estimated_quantity, ci.uom_type_id
	FROM tmp_rates tr
	INNER JOIN direct_info di
	USING (identifier)
	INNER JOIN contract_item_view ci
	USING (contract_item_id, product_id)
	WHERE true
	AND (ci.contract_id IS NOT DISTINCT FROM p_contract_id OR (ci.contract_id IS DISTINCT FROM p_contract_id AND tr.explicit_zero_rate_ind = false));

	ANALYZE tmp_base;

	RAISE NOTICE 'tmp_base created: %', clock_timestamp();

	RETURN QUERY SELECT 
	array_agg(tb.contract_id) FILTER (WHERE tb.contract_id IS DISTINCT FROM p_contract_id AND tb.direct_rate IS NOT NULL) as direct_contract_ids
	, array_agg(tb.direct_rate) FILTER (WHERE tb.contract_id IS DISTINCT FROM p_contract_id AND tb.direct_rate IS NOT NULL) as direct_rates
	, cmp.company_id
	, cmp.company_name::text
	, cmp.company_number::text
	, pr.product_id
	, pr.product_name::text
	, pr.product_number::text
	, ut.uom_type_id
	, ut.uom_type_description::text
	, sum(tb.direct_rate) FILTER (WHERE tb.contract_id IS NOT DISTINCT FROM p_contract_id) as this_direct_rate
	, sum(tb.estimated_quantity) FILTER (WHERE tb.contract_id IS NOT DISTINCT FROM p_contract_id) / cc.valid_company_count as this_estimated_quantity
	, sum(tb.direct_rate) FILTER (WHERE tb.contract_id IS NOT DISTINCT FROM p_contract_id) * sum(tb.estimated_quantity) FILTER (WHERE tb.contract_id IS NOT DISTINCT FROM p_contract_id) / cc.valid_company_count as this_spend
	, sum(tb.direct_rate) FILTER (WHERE tb.contract_id IS DISTINCT FROM p_contract_id) as other_direct_rate
	, sum(tb.direct_rate) as total_direct_rate
	, sum(tb.direct_rate) * sum(tb.estimated_quantity) FILTER (WHERE tb.contract_id IS NOT DISTINCT FROM p_contract_id) / cc.valid_company_count as total_spend
	, null::numeric as target_rate
	, max(cad.claim_ids) claim_ids
	, max(cad.claim_amounts) claim_amounts
	, sum(cad.total_claim_volume) total_claim_volume
	, sum(cad.total_claim_amount) total_claim_amount
	FROM tmp_base tb
	INNER JOIN product pr
	ON tb.product_id = pr.product_id
	INNER JOIN uom_type ut
	ON tb.uom_type_id = ut.uom_type_id
	INNER JOIN company cmp
	ON tb.company_id = cmp.company_id
	LEFT JOIN (
		SELECT count(distinct tmp_valid_companies.company_id) as valid_company_count
		FROM tmp_valid_companies 
	) as cc
	ON true
	LEFT JOIN contract_actual_direct cad
	ON tb.contract_id = cad.contract_id
	AND tb.product_id = cad.product_id
	AND tb.company_id = cad.company_id
	GROUP BY 
	  cmp.company_id
	, cmp.company_name
	, cmp.company_number
	, pr.product_id
	, pr.product_name
	, pr.product_number
	, ut.uom_type_id
	, ut.uom_type_description
	, cc.valid_company_count;

	RAISE NOTICE 'query done: %', clock_timestamp();


	DROP TABLE IF EXISTS tmp_params;
	DROP TABLE IF EXISTS tmp_valid_companies;
	DROP TABLE IF EXISTS direct_ids;
	DROP TABLE IF EXISTS tmp_price;
	DROP TABLE IF EXISTS direct_info;
	DROP TABLE IF EXISTS tmp_rates;
	DROP TABLE IF EXISTS tmp_base;
	DROP TABLE IF EXISTS contract_actual_direct;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000
  ROWS 100;
ALTER FUNCTION gosimple.contract_exposure_direct(uuid, boolean, boolean, uuid[])
  OWNER TO root;