commit b4a24cbc1e54d035572eb7ff910d361d7b638c07
parent 5a75a057c2209500b72e9fd6dd6087f81af7e9f6
Author: bohdan-potuzhnyi <bohdan.potuzhnyi@gmail.com>
Date: Thu, 21 Nov 2024 19:00:37 +0100
adding inventory realted webhooks adding
Diffstat:
1 file changed, 155 insertions(+), 0 deletions(-)
diff --git a/src/backenddb/merchant-0013.sql b/src/backenddb/merchant-0013.sql
@@ -171,4 +171,159 @@ ON merchant_categories
FOR EACH ROW
EXECUTE FUNCTION handle_category_changes();
+-- Function to handle inventory changes and notify webhooks
+CREATE OR REPLACE FUNCTION handle_inventory_changes()
+ RETURNS TRIGGER AS $$
+DECLARE
+ my_merchant_serial BIGINT;
+ resolved_body TEXT;
+ webhook RECORD; -- To iterate over all matching webhooks
+BEGIN
+ -- Fetch the merchant_serial directly from the NEW or OLD row
+ my_merchant_serial := COALESCE(OLD.merchant_serial, NEW.merchant_serial);
+
+ -- INSERT case: Notify webhooks for inventory addition
+ IF TG_OP = 'INSERT' THEN
+ FOR webhook IN
+ SELECT * FROM merchant_webhook
+ WHERE event_type = 'inventory_added'
+ AND merchant_serial = my_merchant_serial
+ LOOP
+ -- Resolve placeholders for the current webhook
+ resolved_body := webhook.body_template;
+ resolved_body := replace_placeholder(resolved_body, 'webhook_type', 'inventory_added');
+ resolved_body := replace_placeholder(resolved_body, 'product_serial', NEW.product_serial::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'product_id', NEW.product_id);
+ resolved_body := replace_placeholder(resolved_body, 'description', NEW.description);
+ resolved_body := replace_placeholder(resolved_body, 'description_i18n', encode(NEW.description_i18n, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'unit', NEW.unit);
+ resolved_body := replace_placeholder(resolved_body, 'image', encode(NEW.image, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'taxes', encode(NEW.taxes, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'price', NEW.price::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'total_stock', NEW.total_stock::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'total_sold', NEW.total_sold::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'total_lost', NEW.total_lost::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'address', encode(NEW.address, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'next_restock', NEW.next_restock::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'minimum_age', NEW.minimum_age::TEXT);
+
+ -- Insert into pending webhooks for this webhook
+ INSERT INTO merchant_pending_webhooks
+ (merchant_serial, webhook_serial, url, http_method, body)
+ VALUES
+ (webhook.merchant_serial,
+ webhook.webhook_serial,
+ webhook.url,
+ webhook.http_method,
+ resolved_body);
+ END LOOP;
+
+ -- Notify the webhook service
+ NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG;
+ END IF;
+
+ -- UPDATE case: Notify webhooks for inventory update
+ IF TG_OP = 'UPDATE' THEN
+ FOR webhook IN
+ SELECT * FROM merchant_webhook
+ WHERE event_type = 'inventory_updated'
+ AND merchant_serial = my_merchant_serial
+ LOOP
+ -- Resolve placeholders for the current webhook
+ resolved_body := webhook.body_template;
+ resolved_body := replace_placeholder(resolved_body, 'webhook_type', 'inventory_updated');
+ resolved_body := replace_placeholder(resolved_body, 'product_serial', NEW.product_serial::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'product_id', NEW.product_id);
+ resolved_body := replace_placeholder(resolved_body, 'old_description', OLD.description);
+ resolved_body := replace_placeholder(resolved_body, 'description', NEW.description);
+ resolved_body := replace_placeholder(resolved_body, 'old_description_i18n', encode(OLD.description_i18n, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'description_i18n', encode(NEW.description_i18n, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'old_unit', OLD.unit);
+ resolved_body := replace_placeholder(resolved_body, 'unit', NEW.unit);
+ resolved_body := replace_placeholder(resolved_body, 'old_image', encode(OLD.image, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'image', encode(NEW.image, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'old_taxes', encode(OLD.taxes, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'taxes', encode(NEW.taxes, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'old_price', OLD.price::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'price', NEW.price::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'old_total_stock', OLD.total_stock::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'total_stock', NEW.total_stock::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'old_total_sold', OLD.total_sold::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'total_sold', NEW.total_sold::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'old_total_lost', OLD.total_lost::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'total_lost', NEW.total_lost::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'old_address', encode(OLD.address, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'address', encode(NEW.address, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'old_next_restock', OLD.next_restock::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'next_restock', NEW.next_restock::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'old_minimum_age', OLD.minimum_age::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'minimum_age', NEW.minimum_age::TEXT);
+
+ -- Insert into pending webhooks for this webhook
+ INSERT INTO merchant_pending_webhooks
+ (merchant_serial, webhook_serial, url, http_method, body)
+ VALUES
+ (webhook.merchant_serial,
+ webhook.webhook_serial,
+ webhook.url,
+ webhook.http_method,
+ resolved_body);
+ END LOOP;
+
+ -- Notify the webhook service
+ NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG;
+ END IF;
+
+ -- DELETE case: Notify webhooks for inventory deletion
+ IF TG_OP = 'DELETE' THEN
+ FOR webhook IN
+ SELECT * FROM merchant_webhook
+ WHERE event_type = 'inventory_deleted'
+ AND merchant_serial = my_merchant_serial
+ LOOP
+ -- Resolve placeholders for the current webhook
+ resolved_body := webhook.body_template;
+ resolved_body := replace_placeholder(resolved_body, 'webhook_type', 'inventory_deleted');
+ resolved_body := replace_placeholder(resolved_body, 'product_serial', OLD.product_serial::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'product_id', OLD.product_id);
+ resolved_body := replace_placeholder(resolved_body, 'description', OLD.description);
+ resolved_body := replace_placeholder(resolved_body, 'description_i18n', encode(OLD.description_i18n, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'unit', OLD.unit);
+ resolved_body := replace_placeholder(resolved_body, 'image', encode(OLD.image, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'taxes', encode(OLD.taxes, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'price', OLD.price::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'total_stock', OLD.total_stock::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'total_sold', OLD.total_sold::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'total_lost', OLD.total_lost::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'address', encode(OLD.address, 'escape'));
+ resolved_body := replace_placeholder(resolved_body, 'next_restock', OLD.next_restock::TEXT);
+ resolved_body := replace_placeholder(resolved_body, 'minimum_age', OLD.minimum_age::TEXT);
+
+ -- Insert into pending webhooks for this webhook
+ INSERT INTO merchant_pending_webhooks
+ (merchant_serial, webhook_serial, url, http_method, body)
+ VALUES
+ (webhook.merchant_serial,
+ webhook.webhook_serial,
+ webhook.url,
+ webhook.http_method,
+ resolved_body);
+ END LOOP;
+
+ -- Notify the webhook service
+ NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG;
+ END IF;
+
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Trigger to invoke the trigger function
+CREATE TRIGGER trigger_inventory_changes
+ AFTER INSERT OR UPDATE OR DELETE
+ ON merchant_inventory
+ FOR EACH ROW
+EXECUTE FUNCTION handle_inventory_changes();
+
+
COMMIT;
