--
-- ec_cash_amount_to_refund/2
--
create or replace function ec_cash_amount_to_refund(
  numeric,
  integer
) returns numeric as $$

DECLARE
	v_amount			alias for $1;
	v_order_id			alias for $2;
        amount_paid                     numeric;
        items_amount_paid               numeric;
        order_amount_paid               numeric;
        amount_refunded                 numeric;
        curr_gc_amount                  numeric;
        max_cash_refundable             numeric;
        cash_to_refund                  numeric;
BEGIN
        -- the maximum amount of cash refundable is equal to
        -- the amount paid (in cash + certificates) for shipped items only (since
        --  money is not paid until an item actually ships)
        -- minus the amount refunded (in cash + certificates) (only occurs for shipped items)
        -- minus the current gift certificate amount applied to this order
        -- or 0 if the result is negative

        select sum(coalesce(price_charged,0)) + sum(coalesce(shipping_charged,0)) + sum(coalesce(price_tax_charged,0)) + sum(coalesce(shipping_tax_charged,0)) into items_amount_paid from ec_items where order_id=v_order_id and shipment_id is not null and item_state <> 'void';

        select coalesce(shipping_charged,0) + coalesce(shipping_tax_charged,0) into order_amount_paid from ec_orders where order_id=v_order_id;

        amount_paid := items_amount_paid + order_amount_paid;
        amount_refunded := ec_total_refund(v_order_id);
        curr_gc_amount := ec_order_gift_cert_amount(v_order_id);
        
        max_cash_refundable := amount_paid - amount_refunded - curr_gc_amount;
        cash_to_refund := least(max_cash_refundable, v_amount);

        RETURN cash_to_refund;
END;$$ language plpgsql;