-- Deploy jmp:cdr_with_charge to pg
-- requires: cdr
-- requires: call_rates
-- requires: customer_plans

BEGIN;

CREATE VIEW cdr_with_charge AS
	SELECT DISTINCT ON (cdr_id)
		cdr.*,
		rate,
		(CASE WHEN cdr.disposition = 'VOICEMAIL' THEN 0
		ELSE rate * ceiling(billsec / 60.0) END) AS charge
	FROM
		cdr
		INNER JOIN customer_plans USING (customer_id)
		LEFT JOIN call_rates ON
			cdr.direction = call_rates.direction AND
			customer_plans.plan_name = call_rates.plan_name AND
			cdr.tel LIKE call_rates.prefix || '%'
	ORDER BY cdr_id, prefix DESC;

COMMIT;