1 files changed, 121 insertions, 1 deletions

diff --git a/c2ec/db/procedures.sql b/c2ec/db/procedures.sql
index 69a2ba6..8bac56b 100644
--- a/c2ec/db/procedures.sql
+++ b/c2ec/db/procedures.sql
@@ -1 +1,121 @@
--- generated from 
\ No newline at end of file
+BEGIN;
+
+SELECT _v.register_patch('proc-c2ec-status-listener', ARRAY['0001-c2ec-schema'], NULL);
+
+SET search_path TO c2ec;
+
+-- to create a function, the user needs USAGE privilege on arguments and return types
+CREATE OR REPLACE FUNCTION emit_withdrawal_status() 
+RETURNS TRIGGER AS $$
+BEGIN
+    PERFORM pg_notify('w_' || encode(NEW.wopid::BYTEA, 'base64'), NEW.withdrawal_status::TEXT);
+    RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+COMMENT ON FUNCTION emit_withdrawal_status 
+	IS 'The function encodes the wopid in base64 and 
+    sends a notification on the channel "w_{wopid}" 
+    with the status in the payload.';
+
+-- for creating a trigger the user must have TRIGGER pivilege on the table.
+-- to execute the trigger, the user needs EXECUTE privilege on the trigger function.
+CREATE OR REPLACE TRIGGER c2ec_withdrawal_created 
+    AFTER INSERT
+    ON withdrawal
+    FOR EACH ROW
+    EXECUTE FUNCTION emit_withdrawal_status();
+COMMENT ON TRIGGER c2ec_withdrawal_created ON withdrawal
+    IS 'After creation of the withdrawal entry a notification shall 
+    be triggered using this trigger.';
+
+CREATE OR REPLACE TRIGGER c2ec_withdrawal_changed 
+    AFTER UPDATE OF withdrawal_status
+    ON withdrawal
+    FOR EACH ROW
+    WHEN (OLD.withdrawal_status IS DISTINCT FROM NEW.withdrawal_status)
+    EXECUTE FUNCTION emit_withdrawal_status();
+COMMENT ON TRIGGER c2ec_withdrawal_changed ON withdrawal
+    IS 'After the update of the status (only the status is of interest) 
+    a notification shall be triggered using this trigger.';
+
+COMMIT;
+
+BEGIN;
+
+SELECT _v.register_patch('proc-c2ec-retry-listener', ARRAY['0001-c2ec-schema'], NULL);
+
+SET search_path TO c2ec;
+
+-- to create a function, the user needs USAGE privilege on arguments and return types
+CREATE OR REPLACE FUNCTION emit_retry_notification() 
+RETURNS TRIGGER AS $$
+BEGIN
+    PERFORM pg_notify('retry', '' || NEW.withdrawal_id);
+    RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+COMMENT ON FUNCTION emit_retry_notification 
+	IS 'The function emits the id of the withdrawal for which the last 
+    retry timestamp was updated. This shall trigger a retry operation.
+    How many retries are attempted is specified and handled by the application';
+
+-- for creating a trigger the user must have TRIGGER pivilege on the table.
+-- to execute the trigger, the user needs EXECUTE privilege on the trigger function.
+CREATE OR REPLACE TRIGGER c2ec_retry_notify 
+    AFTER UPDATE OF last_retry_ts
+    ON withdrawal
+    FOR EACH ROW
+    EXECUTE FUNCTION emit_retry_notification();
+COMMENT ON TRIGGER c2ec_retry_notify ON withdrawal
+    IS 'After setting the last retry timestamp on the withdrawal,
+    trigger the retry mechanism through the respective mechanism.';
+
+COMMIT;
+
+BEGIN;
+
+SELECT _v.register_patch('proc-c2ec-payment-notification-listener', ARRAY['0001-c2ec-schema'], NULL);
+
+SET search_path TO c2ec;
+
+-- to create a function, the user needs USAGE privilege on arguments and return types
+CREATE OR REPLACE FUNCTION emit_payment_notification() 
+RETURNS TRIGGER AS $$
+DECLARE
+    provider_name TEXT;
+BEGIN
+    SELECT p.name INTO provider_name FROM c2ec.provider AS p 
+        LEFT JOIN c2ec.terminal AS t 
+        ON t.provider_id = p.provider_id
+        LEFT JOIN c2ec.withdrawal AS w
+        ON t.terminal_id = w.terminal_id
+        WHERE w.withdrawal_id = NEW.withdrawal_id;
+    PERFORM pg_notify('payment_notification',
+        provider_name || '|' ||
+        NEW.withdrawal_id || '|' || 
+        NEW.provider_transaction_id
+    );
+    RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+COMMENT ON FUNCTION emit_payment_notification 
+	IS 'The function emits the name of the provider, row id of the withdrawal
+    and the provider_transaction_id, on the channel "payment_notification".
+    The format of the payload is as follows: 
+    "{PROVIDER_NAME}|{WITHDRAWAL_ID}|{PROVIDER_TRANSACTION_ID}". The subscriber
+    shall decide which attestation process to use, based on the name of 
+    the provider.';
+
+-- for creating a trigger the user must have TRIGGER pivilege on the table.
+-- to execute the trigger, the user needs EXECUTE privilege on the trigger function.
+CREATE OR REPLACE TRIGGER c2ec_on_payment_notify 
+    AFTER UPDATE OF provider_transaction_id
+    ON withdrawal
+    FOR EACH ROW    
+    WHEN (NEW.provider_transaction_id IS NOT NULL)
+    EXECUTE FUNCTION emit_payment_notification();
+COMMENT ON TRIGGER c2ec_on_payment_notify ON withdrawal
+    IS 'After setting the provider transaction id following a payment notification,
+    trigger the emit to the respective channel.';
+
+COMMIT;