--
-- gift_certificate_amount_left/1
--
create or replace function gift_certificate_amount_left(
  integer
) returns numeric as $$

DECLARE
	v_gift_certificate_id 	alias for $1;
        original_amount         numeric;
        total_amount_used       numeric;
BEGIN
        SELECT coalesce(sum(amount_used),0) - coalesce(sum(amount_reinstated),0)
	into total_amount_used
        FROM ec_gift_certificate_usage
        WHERE gift_certificate_id = v_gift_certificate_id;

        SELECT amount
	into original_amount
        FROM ec_gift_certificates
        WHERE gift_certificate_id = v_gift_certificate_id;

        RETURN original_amount - total_amount_used;
END;$$ language plpgsql;