-- Test: subscribe and unsubscribe functions
CREATE EXTENSION pg_reactive;
-- Create test tables
CREATE TABLE orders (id serial PRIMARY KEY, customer_id int, total numeric, status text);
CREATE TABLE customers (id serial PRIMARY KEY, name text);
-- Subscribe to a simple query
SELECT pgr.subscribe('q1', 'SELECT * FROM orders WHERE status = ''pending''');
                                subscribe                                 
--------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 1, "query_id": "q1"}
(1 row)

-- Verify it appears in subscriptions
SELECT query_id, num_tables FROM pgr.subscriptions;
 query_id | num_tables 
----------+------------
 q1       |          1
(1 row)

-- Subscribe to a multi-table query
SELECT pgr.subscribe('q2', 'SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id');
                                subscribe                                 
--------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 2, "query_id": "q2"}
(1 row)

-- Should now have 2 subscriptions
SELECT query_id, num_tables FROM pgr.subscriptions ORDER BY query_id;
 query_id | num_tables 
----------+------------
 q1       |          1
 q2       |          2
(2 rows)

-- active_subscriptions should be 2
SELECT metric, value FROM pgr.stats() WHERE metric = 'active_subscriptions';
        metric        | value 
----------------------+-------
 active_subscriptions | 2
(1 row)

-- Unsubscribe q1
SELECT pgr.unsubscribe('q1');
 unsubscribe 
-------------
 t
(1 row)

-- Only q2 should remain
SELECT query_id, num_tables FROM pgr.subscriptions;
 query_id | num_tables 
----------+------------
 q2       |          2
(1 row)

-- Unsubscribe non-existent should return false
SELECT pgr.unsubscribe('does_not_exist');
 unsubscribe 
-------------
 f
(1 row)

-- Re-subscribe q1 (should work, updating the entry)
SELECT pgr.subscribe('q1', 'SELECT * FROM orders');
                                subscribe                                 
--------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 1, "query_id": "q1"}
(1 row)

SELECT query_id, num_tables FROM pgr.subscriptions ORDER BY query_id;
 query_id | num_tables 
----------+------------
 q1       |          1
 q2       |          2
(2 rows)

-- active_subscriptions should be 2 again after re-subscribe
SELECT metric, value FROM pgr.stats() WHERE metric = 'active_subscriptions';
        metric        | value 
----------------------+-------
 active_subscriptions | 2
(1 row)

-- Cleanup
SELECT pgr.unsubscribe('q1');
 unsubscribe 
-------------
 t
(1 row)

SELECT pgr.unsubscribe('q2');
 unsubscribe 
-------------
 t
(1 row)

-- Should be empty
SELECT count(*) FROM pgr.subscriptions;
 count 
-------
     0
(1 row)

DROP TABLE customers;
DROP TABLE orders;
DROP EXTENSION pg_reactive;