-- Deploy jmp:cdr_charge to pg
-- requires: customers
-- requires: customer_plans
-- requires: plans
-- requires: cdr_with_charge

BEGIN;

CREATE FUNCTION cdr_charge(
	cdr_start TIMESTAMP,
	cdr_customer_id TEXT,
	cdr_rate NUMERIC,
	cdr_charge NUMERIC -- Basic rate*ceil(billsec/60)
) RETURNS NUMERIC AS $$
WITH
	customer_limit AS (
		SELECT COALESCE(MAX((data->>'monthly_overage_limit')::numeric), 0) AS customer_limit
		FROM customers
		WHERE customer_id=cdr_customer_id
	),
	included_charge AS (
		SELECT ((plan->'minutes'->'included')::numeric / 10000.0) AS included_charge
		FROM plans INNER JOIN customer_plans ON plan_name=plan->>'name'
		WHERE customer_id=cdr_customer_id
	),
	used AS (
		SELECT COALESCE(SUM(charge), 0) AS used
		FROM cdr_with_charge
		WHERE
			customer_id=cdr_customer_id AND
			start >= DATE_TRUNC('month', cdr_start) AND
			start < cdr_start
	)

SELECT
	CASE
		WHEN cdr_charge IS NULL OR included_charge IS NULL THEN
			0
		WHEN used + cdr_charge < included_charge + customer_limit THEN
			GREATEST(cdr_charge - GREATEST(included_charge - used, 0), 0)
		WHEN included_charge + customer_limit - (cdr_rate * 10) < used THEN
			GREATEST(cdr_charge - GREATEST(included_charge - used, 0), 0)
		ELSE
			LEAST(customer_limit + included_charge - used, customer_limit)
	END
FROM
	customer_limit
	FULL JOIN included_charge ON true
	FULL JOIN used ON true;
$$ LANGUAGE sql;

COMMIT;