-- 06_triggers.sql: Trigger behavior — updated_at auto-update, notify trigger
-- pg_regress test for ulak
-- ============================================================================
-- UPDATED_AT TRIGGER ON ENDPOINTS
-- ============================================================================
-- Create endpoint and capture initial timestamps
SELECT ulak.create_endpoint(
    'trigger_test',
    'http',
    '{"url": "http://example.com/hook", "method": "POST"}'::jsonb
) IS NOT NULL AS created;
INFO:  [ulak] Created endpoint with ID 13
 created 
---------
 t
(1 row)

-- Record created_at for comparison
SELECT created_at = updated_at AS timestamps_equal_at_creation
FROM ulak.endpoints
WHERE name = 'trigger_test';
 timestamps_equal_at_creation 
------------------------------
 t
(1 row)

-- Wait briefly then update to ensure updated_at changes
-- Use pg_sleep to guarantee time difference
SELECT pg_sleep(0.1);
 pg_sleep 
----------
 
(1 row)

UPDATE ulak.endpoints
SET config = '{"url": "http://example.com/hook-v2", "method": "POST"}'::jsonb
WHERE name = 'trigger_test';
-- updated_at should now be >= created_at (trigger fired)
SELECT updated_at >= created_at AS updated_at_advanced
FROM ulak.endpoints
WHERE name = 'trigger_test';
 updated_at_advanced 
---------------------
 t
(1 row)

-- ============================================================================
-- UPDATED_AT TRIGGER ON QUEUE
-- ============================================================================
-- Insert a queue message
INSERT INTO ulak.queue (endpoint_id, payload)
SELECT id, '{"trigger_test": true}'::jsonb
FROM ulak.endpoints
WHERE name = 'trigger_test';
-- Check initial state
SELECT created_at = updated_at AS queue_timestamps_equal_at_insert
FROM ulak.queue
WHERE payload @> '{"trigger_test": true}'::jsonb;
 queue_timestamps_equal_at_insert 
----------------------------------
 t
(1 row)

SELECT pg_sleep(0.1);
 pg_sleep 
----------
 
(1 row)

-- Update the queue entry
UPDATE ulak.queue
SET status = 'processing'
WHERE payload @> '{"trigger_test": true}'::jsonb;
-- updated_at should have advanced
SELECT updated_at >= created_at AS queue_updated_at_advanced
FROM ulak.queue
WHERE payload @> '{"trigger_test": true}'::jsonb;
 queue_updated_at_advanced 
---------------------------
 t
(1 row)

-- ============================================================================
-- NOTIFY TRIGGER (verify trigger exists and fires)
-- ============================================================================
-- The notify trigger fires on INSERT to queue (AFTER INSERT FOR EACH STATEMENT)
-- We can verify the trigger exists on the queue table
SELECT tgname, tgenabled
FROM pg_trigger
WHERE tgrelid = 'ulak.queue'::regclass
  AND tgname = 'notify_new_message_trigger';
           tgname           | tgenabled 
----------------------------+-----------
 notify_new_message_trigger | O
(1 row)

-- We can verify the trigger function exists
SELECT EXISTS(
    SELECT 1
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = 'ulak' AND p.proname = 'notify_new_message'
) AS notify_function_exists;
 notify_function_exists 
------------------------
 t
(1 row)

-- ============================================================================
-- CLEANUP
-- ============================================================================
DELETE FROM ulak.queue WHERE endpoint_id = (SELECT id FROM ulak.endpoints WHERE name = 'trigger_test');
SELECT ulak.drop_endpoint('trigger_test');
INFO:  [ulak] Dropped endpoint 'trigger_test'
 drop_endpoint 
---------------
 t
(1 row)