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

DECLARE
	v_user_id alias for $1;
	original_amount                 numeric;
	total_amount_used               numeric;
        -- these only look at unexpired gift certificates 
	-- where amount_remaining_p is t,
        -- hence the word subset in their names
BEGIN
        SELECT coalesce(sum(amount),0)
	into original_amount
        FROM ec_gift_certificates_approved
        WHERE user_id=v_user_id
        AND amount_remaining_p='t'
        AND expires > now();

        SELECT coalesce(sum(u.amount_used),0) - 
    	    coalesce(sum(u.amount_reinstated),0)
	into total_amount_used
        FROM ec_gift_certificates_approved c, ec_gift_certificate_usage u
        WHERE c.gift_certificate_id=u.gift_certificate_id
        AND c.user_id=v_user_id
        AND c.amount_remaining_p='t'
        AND c.expires > now();

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