-- 1. Create the table for storing incoming webhooks
CREATE TABLE IF NOT EXISTS webhook_events (
    id SERIAL PRIMARY KEY,
    event_type TEXT,
    payload JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. Create the trigger function
CREATE OR REPLACE FUNCTION notify_new_webhook() 
RETURNS TRIGGER AS $$
BEGIN
  -- Notify the 'new_webhook' channel with the row data
  PERFORM pg_notify('new_webhook', row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 3. Create the trigger
DROP TRIGGER IF EXISTS trg_notify_webhook ON webhook_events;
CREATE TRIGGER trg_notify_webhook
AFTER INSERT ON webhook_events
FOR EACH ROW EXECUTE PROCEDURE notify_new_webhook();