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

DECLARE
	v_shipment_id		alias for $1;
        v_order_id              ec_orders.order_id%TYPE;
        gift_cert_amount        numeric;
        past_ship_amount        numeric;
BEGIN
        SELECT into v_order_id order_id 
	    FROM ec_shipments WHERE shipment_id=v_shipment_id;
        gift_cert_amount := ec_order_gift_cert_amount(v_order_id);
        SELECT into past_ship_amount 
	    coalesce(sum(ec_shipment_price(shipment_id)) + sum(ec_shipment_shipping(shipment_id))+sum(ec_shipment_tax(shipment_id)),0) 
	    FROM ec_shipments WHERE order_id = v_order_id and shipment_id <> v_shipment_id;

        IF past_ship_amount > gift_cert_amount THEN
                return 0;
        ELSE
                return least(gift_cert_amount - past_ship_amount, coalesce(ec_shipment_price(v_shipment_id) + ec_shipment_shipping(v_shipment_id) + ec_shipment_tax(v_shipment_id),0));
        END IF;
END;$$ language plpgsql;