-- Deploy camper:redsys_decode_response to pg
-- requires: roles
-- requires: schema_camper
-- requires: extension_pgcrypto
-- requires: decode_base64url
-- requires: redsys_encrypt
-- requires: redsys_response
-- requires: company
-- requires: currency
-- requires: to_price

begin;

set search_path to camper, public;

create or replace function redsys_decode_response(company_id integer, encoded_data text, encoded_signature text, signature_version text) returns redsys_response as
$$
select row (
		  obj ->> 'Ds_MerchantCode'
		, to_number((obj ->> 'Ds_Terminal'), '999')::integer
		, to_number((obj ->> 'Ds_Response'), '9999')::integer
		, to_timestamp((obj ->> 'Ds_Date') || ' ' || (obj ->> 'Ds_Hour'), 'DD/MM/YYYY HH24:MI')
		, obj ->> 'Ds_SecurePayment' = '1'
		, (obj ->> 'Ds_TransactionType')::integer
		, to_price((obj ->> 'Ds_Amount')::integer, decimal_digits)
		, currency_code
		, obj ->> 'Ds_Order'
		, coalesce(obj ->> 'Ds_AuthorisationCode', '')
		, coalesce(obj ->> 'Ds_ErrorCode', '')
	)::redsys_response
	from (
		select convert_to(encoded_data, 'UTF-8') as raw
		     , convert_from(decode_base64url(encoded_data), 'UTF-8')::jsonb as obj
		     , decode_base64url(encoded_signature) as signature
		     , signature_version
	) as response
		join currency on currency.redsys_code = (obj ->> 'Ds_Currency')::integer
	where signature = hmac(raw, redsys_encrypt(company_id, convert_to(obj ->> 'Ds_Order', 'UTF-8')), 'sha256')
	  and signature_version = 'HMAC_SHA256_V1';
$$
	language sql
	stable
;

revoke execute on function redsys_decode_response(integer, text, text, text) from public;
grant execute on function redsys_decode_response(integer, text, text, text) to guest;
grant execute on function redsys_decode_response(integer, text, text, text) to employee;
grant execute on function redsys_decode_response(integer, text, text, text) to admin;

commit;