-- Test: SQL coverage — 11 query types all subscribe and recompute correctly
CREATE EXTENSION pg_reactive;
-- Setup base tables
CREATE TABLE products (id serial PRIMARY KEY, name text, price numeric, category text);
CREATE TABLE orders (id serial PRIMARY KEY, product_id int REFERENCES products(id), qty int, created_at timestamp DEFAULT now());
INSERT INTO products (name, price, category) VALUES
  ('Apple', 1.50, 'fruit'),
  ('Banana', 0.75, 'fruit'),
  ('Carrot', 2.00, 'vegetable'),
  ('Donut', 3.50, 'pastry');
INSERT INTO orders (product_id, qty) VALUES
  (1, 10), (2, 5), (1, 3), (3, 7), (4, 2);
-- 1. Simple SELECT
SELECT pgr.subscribe('cov_simple', 'SELECT id, name, price FROM products');
                                    subscribe                                     
----------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 1, "query_id": "cov_simple"}
(1 row)

SELECT count(*) AS simple_snap FROM pgr."_snap_cov_simple";
 simple_snap 
-------------
           4
(1 row)

-- 2. WHERE clause
SELECT pgr.subscribe('cov_where', 'SELECT id, name FROM products WHERE category = ''fruit''');
                                    subscribe                                    
---------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 1, "query_id": "cov_where"}
(1 row)

SELECT count(*) AS where_snap FROM pgr."_snap_cov_where";
 where_snap 
------------
          2
(1 row)

-- 3. INNER JOIN
SELECT pgr.subscribe('cov_join', 'SELECT o.id, p.name, o.qty FROM orders o JOIN products p ON o.product_id = p.id');
                                   subscribe                                    
--------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 2, "query_id": "cov_join"}
(1 row)

SELECT count(*) AS join_snap FROM pgr."_snap_cov_join";
 join_snap 
-----------
         5
(1 row)

-- 4. LEFT JOIN
SELECT pgr.subscribe('cov_leftjoin', 'SELECT p.name, o.qty FROM products p LEFT JOIN orders o ON o.product_id = p.id');
                                     subscribe                                      
------------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 2, "query_id": "cov_leftjoin"}
(1 row)

SELECT count(*) AS leftjoin_snap FROM pgr."_snap_cov_leftjoin";
 leftjoin_snap 
---------------
             5
(1 row)

-- 5. GROUP BY
SELECT pgr.subscribe('cov_group', 'SELECT p.category, count(*) AS cnt FROM products p GROUP BY p.category');
                                    subscribe                                    
---------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 1, "query_id": "cov_group"}
(1 row)

SELECT count(*) AS group_snap FROM pgr."_snap_cov_group";
 group_snap 
------------
          3
(1 row)

-- 6. HAVING
SELECT pgr.subscribe('cov_having', 'SELECT p.category, count(*) AS cnt FROM products p GROUP BY p.category HAVING count(*) > 1');
                                    subscribe                                     
----------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 1, "query_id": "cov_having"}
(1 row)

SELECT count(*) AS having_snap FROM pgr."_snap_cov_having";
 having_snap 
-------------
           1
(1 row)

-- 7. DISTINCT
SELECT pgr.subscribe('cov_distinct', 'SELECT DISTINCT category FROM products');
                                     subscribe                                      
------------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 1, "query_id": "cov_distinct"}
(1 row)

SELECT count(*) AS distinct_snap FROM pgr."_snap_cov_distinct";
 distinct_snap 
---------------
             3
(1 row)

-- 8. LIMIT
SELECT pgr.subscribe('cov_limit', 'SELECT id, name FROM products ORDER BY id LIMIT 2');
                                    subscribe                                    
---------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 1, "query_id": "cov_limit"}
(1 row)

SELECT count(*) AS limit_snap FROM pgr."_snap_cov_limit";
 limit_snap 
------------
          2
(1 row)

-- 9. Subquery
SELECT pgr.subscribe('cov_subq', 'SELECT id, name FROM products WHERE id IN (SELECT product_id FROM orders WHERE qty > 5)');
                                   subscribe                                    
--------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 2, "query_id": "cov_subq"}
(1 row)

SELECT count(*) AS subq_snap FROM pgr."_snap_cov_subq";
 subq_snap 
-----------
         2
(1 row)

-- 10. CTE
SELECT pgr.subscribe('cov_cte', 'WITH top_orders AS (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id) SELECT p.name, t.total FROM products p JOIN top_orders t ON p.id = t.product_id');
                                   subscribe                                   
-------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 2, "query_id": "cov_cte"}
(1 row)

SELECT count(*) AS cte_snap FROM pgr."_snap_cov_cte";
 cte_snap 
----------
        4
(1 row)

-- 11. Window function
SELECT pgr.subscribe('cov_window', 'SELECT id, name, price, rank() OVER (ORDER BY price DESC) AS price_rank FROM products');
                                    subscribe                                     
----------------------------------------------------------------------------------
 {"mode": "delta", "status": "subscribed", "tables": 1, "query_id": "cov_window"}
(1 row)

SELECT count(*) AS window_snap FROM pgr."_snap_cov_window";
 window_snap 
-------------
           4
(1 row)

----------------------------------------------------------------------
-- Now test that DML triggers correct recompute for all query types
----------------------------------------------------------------------
-- Insert a new product — should affect most queries
INSERT INTO products (name, price, category) VALUES ('Eclair', 4.00, 'pastry');
-- Verify snapshots updated correctly by checking each matches its query
-- Simple SELECT
SELECT count(*) AS simple_diff FROM (
  (SELECT id, name, price FROM products) EXCEPT (SELECT * FROM pgr."_snap_cov_simple")
) t;
 simple_diff 
-------------
           0
(1 row)

-- WHERE
SELECT count(*) AS where_diff FROM (
  (SELECT id, name FROM products WHERE category = 'fruit') EXCEPT (SELECT * FROM pgr."_snap_cov_where")
) t;
 where_diff 
------------
          0
(1 row)

-- GROUP BY (should now have pastry=2)
SELECT count(*) AS group_diff FROM (
  (SELECT p.category, count(*) AS cnt FROM products p GROUP BY p.category)
  EXCEPT (SELECT * FROM pgr."_snap_cov_group")
) t;
 group_diff 
------------
          0
(1 row)

-- HAVING (pastry now has count=2, should appear)
SELECT count(*) AS having_diff FROM (
  (SELECT p.category, count(*) AS cnt FROM products p GROUP BY p.category HAVING count(*) > 1)
  EXCEPT (SELECT * FROM pgr."_snap_cov_having")
) t;
 having_diff 
-------------
           0
(1 row)

-- DISTINCT (pastry already existed, so no change in distinct categories)
SELECT count(*) AS distinct_diff FROM (
  (SELECT DISTINCT category FROM products) EXCEPT (SELECT * FROM pgr."_snap_cov_distinct")
) t;
 distinct_diff 
---------------
             0
(1 row)

-- Window function (ranks shift with new product)
SELECT count(*) AS window_diff FROM (
  (SELECT id, name, price, rank() OVER (ORDER BY price DESC) AS price_rank FROM products)
  EXCEPT (SELECT * FROM pgr."_snap_cov_window")
) t;
 window_diff 
-------------
           0
(1 row)

-- Add an order for the new product — tests JOIN/LEFT JOIN/subquery/CTE updates
INSERT INTO orders (product_id, qty) VALUES (5, 20);
-- JOIN
SELECT count(*) AS join_diff FROM (
  (SELECT o.id, p.name, o.qty FROM orders o JOIN products p ON o.product_id = p.id)
  EXCEPT (SELECT * FROM pgr."_snap_cov_join")
) t;
 join_diff 
-----------
         0
(1 row)

-- LEFT JOIN
SELECT count(*) AS leftjoin_diff FROM (
  (SELECT p.name, o.qty FROM products p LEFT JOIN orders o ON o.product_id = p.id)
  EXCEPT (SELECT * FROM pgr."_snap_cov_leftjoin")
) t;
 leftjoin_diff 
---------------
             0
(1 row)

-- Subquery (Eclair has qty=20 > 5, should appear)
SELECT count(*) AS subq_diff FROM (
  (SELECT id, name FROM products WHERE id IN (SELECT product_id FROM orders WHERE qty > 5))
  EXCEPT (SELECT * FROM pgr."_snap_cov_subq")
) t;
 subq_diff 
-----------
         0
(1 row)

-- CTE
SELECT count(*) AS cte_diff FROM (
  (WITH top_orders AS (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id)
   SELECT p.name, t.total FROM products p JOIN top_orders t ON p.id = t.product_id)
  EXCEPT (SELECT * FROM pgr."_snap_cov_cte")
) t;
 cte_diff 
----------
        0
(1 row)

-- DELETE a product — verify cascading updates
DELETE FROM orders WHERE product_id = 2;
DELETE FROM products WHERE id = 2;
-- WHERE (fruit now only has Apple)
SELECT count(*) AS where_after_del FROM pgr."_snap_cov_where";
 where_after_del 
-----------------
               1
(1 row)

-- GROUP BY (fruit count drops to 1)
SELECT category, cnt FROM pgr."_snap_cov_group" WHERE category = 'fruit';
 category | cnt 
----------+-----
 fruit    |   1
(1 row)

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

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

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

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

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

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

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

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

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

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

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

DROP TABLE orders;
DROP TABLE products;
DROP EXTENSION pg_reactive;