-- pg_promise_guard: the tests break the promises for real and then check that
-- the extension says so. Two things are asserted on purpose:
--
--   1. that the guarantee IS actually gone (the duplicate goes in, the audit
--      row is missing). Without this the suite would prove that the extension
--      reads the catalog, not that the catalog fact means what we claim.
--   2. that a healthy schema reports NOTHING. A checker that always finds
--      something gets ignored, and then it is not a checker.
--
-- SHOW_CONTEXT never: the CONTEXT line carries source positions that shift with
-- every edit of the SQL, and the test would break on cosmetic changes.
\set SHOW_CONTEXT never
-- CASCADE because since 0.2.0 watch() relies on pg_living_assertions: reading the
-- catalog is still this extension's job, REMEMBERING that it was read is not.
CREATE EXTENSION pg_promise_guard CASCADE;
NOTICE:  installing required extension "pg_living_assertions"
-- The extension installs into its own schema (see the .control), so without
-- this every call below resolves to nothing and the suite would pass while
-- testing none of the detection.
SET search_path = promise_guard, public;
CREATE SCHEMA pgd;
-- ===========================================================================
-- A clean schema must be silent.
-- ===========================================================================
CREATE TABLE pgd.ok (id int PRIMARY KEY, v text NOT NULL);
CREATE UNIQUE INDEX ok_v ON pgd.ok (v);
ALTER TABLE pgd.ok ADD CONSTRAINT v_not_empty CHECK (v <> '');
SELECT count(*) AS findings_in_a_healthy_schema FROM check_promises('pgd');
 findings_in_a_healthy_schema 
------------------------------
                            0
(1 row)

SELECT promises_kept('pgd') AS all_promises_kept;
 all_promises_kept 
-------------------
 t
(1 row)

-- ===========================================================================
-- PROMISE 1: "this column is unique"
-- ===========================================================================
CREATE TABLE pgd.customers (id int, tax_id text);
INSERT INTO pgd.customers VALUES (1, 'dup'), (2, 'dup');
-- The real-world shape: a migration runs this and the error scrolls past in a
-- deploy log. CONCURRENTLY is what leaves the corpse behind.
CREATE UNIQUE INDEX CONCURRENTLY customers_tax_id_uk ON pgd.customers (tax_id);
ERROR:  could not create unique index "customers_tax_id_uk"
DETAIL:  Key (tax_id)=(dup) is duplicated.
-- THE PREMISE, not the detection: is uniqueness actually gone?
INSERT INTO pgd.customers VALUES (3, 'dup');
SELECT count(*) AS rows_with_the_same_tax_id FROM pgd.customers WHERE tax_id = 'dup';
 rows_with_the_same_tax_id 
---------------------------
                         3
(1 row)

SELECT kind, object, severity FROM check_promises('pgd') ORDER BY kind, object;
         kind         |         object          | severity 
----------------------+-------------------------+----------
 invalid_unique_index | pgd.customers_tax_id_uk | breach
(1 row)

-- ===========================================================================
-- PROMISE 2: "this CHECK is guaranteed"
-- ===========================================================================
CREATE TABLE pgd.invoices (id int, amount numeric);
INSERT INTO pgd.invoices VALUES (1, -500);
ALTER TABLE pgd.invoices ADD CONSTRAINT amount_positive CHECK (amount > 0) NOT VALID;
SELECT count(*) AS rows_violating_the_check FROM pgd.invoices WHERE amount <= 0;
 rows_violating_the_check 
--------------------------
                        1
(1 row)

-- ===========================================================================
-- PROMISE 3: "the audit trigger is running"
-- ===========================================================================
CREATE TABLE pgd.audit_log (what text);
CREATE FUNCTION pgd.log_insert() RETURNS trigger LANGUAGE plpgsql AS
  $$ BEGIN INSERT INTO pgd.audit_log VALUES (TG_TABLE_NAME); RETURN NEW; END $$;
CREATE TRIGGER audit_insert AFTER INSERT ON pgd.invoices
  FOR EACH ROW EXECUTE FUNCTION pgd.log_insert();
INSERT INTO pgd.invoices VALUES (2, 100);
ALTER TABLE pgd.invoices DISABLE TRIGGER audit_insert;
INSERT INTO pgd.invoices VALUES (3, 200);
SELECT count(*) AS rows_inserted FROM pgd.invoices WHERE amount > 0;
 rows_inserted 
---------------
             2
(1 row)

SELECT count(*) AS rows_audited FROM pgd.audit_log;
 rows_audited 
--------------
            1
(1 row)

-- ===========================================================================
-- PROMISE 4: "row level security protects this table"
-- ===========================================================================
CREATE TABLE pgd.tenant_data (tenant text, payload text);
ALTER TABLE pgd.tenant_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY only_mine ON pgd.tenant_data
  USING (tenant = current_setting('app.tenant', true));
-- ===========================================================================
-- The whole report
-- ===========================================================================
SELECT kind, object, relation, severity FROM check_promises('pgd')
ORDER BY severity, kind, object;
         kind         |         object          |    relation     | severity 
----------------------+-------------------------+-----------------+----------
 disabled_trigger     | pgd.audit_insert        | pgd.invoices    | breach
 invalid_unique_index | pgd.customers_tax_id_uk | pgd.customers   | breach
 unenforced_rls       | pgd.tenant_data         | pgd.tenant_data | breach
 not_valid_constraint | pgd.amount_positive     | pgd.invoices    | gap
(4 rows)

SELECT promises_kept('pgd') AS all_promises_kept;
 all_promises_kept 
-------------------
 f
(1 row)

-- A gap alone must NOT turn the monitor red: a NOT VALID constraint is a
-- legitimate mid-migration state, and a check that is red on purpose gets
-- silenced -- taking the real breaches with it.
CREATE SCHEMA pgd2;
CREATE TABLE pgd2.t (id int, amount numeric);
CREATE UNIQUE INDEX t_id_uk ON pgd2.t (id);
INSERT INTO pgd2.t VALUES (1, -1);
ALTER TABLE pgd2.t ADD CONSTRAINT amount_pos CHECK (amount > 0) NOT VALID;
SELECT count(*) AS only_gaps FROM check_promises('pgd2');
 only_gaps 
-----------
         1
(1 row)

SELECT promises_kept('pgd2') AS still_green_with_a_gap;
 still_green_with_a_gap 
------------------------
 t
(1 row)

-- Fixing the promise must clear the finding: a checker that keeps complaining
-- after the fix is a checker that gets turned off.
ALTER TABLE pgd.invoices ENABLE TRIGGER audit_insert;
SELECT count(*) AS disabled_triggers FROM check_promises('pgd')
WHERE kind = 'disabled_trigger';
 disabled_triggers 
-------------------
                 0
(1 row)

-- ===========================================================================
-- 0.2.0 -- THE SCANNER'S MEMORY
--
-- Everything above answers "what is broken NOW". The three questions a
-- stateless scanner CANNOT answer, and that decide the criterion declared
-- before this port was written:
--   1. when it was last scanned
--   2. what it said the previous time
--   3. whether anyone ever scanned it
-- The third is the one that matters: without state, "clean" and "nobody looked" give the
-- SAME empty result, and an empty result reads as a clean bill of health.
-- ===========================================================================
-- QUESTION 3, in the direction nobody tests: before anything is registered, the
-- answer is not "clean", it is "nothing is watching".
SELECT living_assertions.state('promises:pgd2') AS before_watching;
 before_watching 
-----------------
 unregistered
(1 row)

SELECT promise_guard.watch('pgd2') > 0 AS watched;
 watched 
---------
 t
(1 row)

SELECT living_assertions.state('promises:pgd2') AS with_only_a_gap;
 with_only_a_gap 
-----------------
 holds
(1 row)

-- QUESTION 1: the age travels with the verdict.
SELECT name, state, age IS NOT NULL AS carries_its_age
  FROM living_assertions.status WHERE name = 'promises:pgd2';
     name      | state | carries_its_age 
---------------+-------+-----------------
 promises:pgd2 | holds | t
(1 row)

-- The scanner finds a real BREACH: an invalid UNIQUE index is
-- exactly the case in the header -- the catalog says UNIQUE and the
-- duplicates go in without an error or a line of log.
-- Both flags, as a CREATE UNIQUE INDEX CONCURRENTLY that failed on duplicates leaves them
-- (measured: indisvalid = false, indisready = false, and duplicates go in). An index that is
-- invalid but READY does enforce uniqueness for new rows; that one is a gap (0.2.8).
UPDATE pg_index SET indisvalid = false, indisready = false
 WHERE indexrelid = 'pgd2.t_id_uk'::regclass;
SELECT living_assertions.run('promises:pgd2') IS NOT NULL AS rescanned;
 rescanned 
-----------
 t
(1 row)

SELECT state, detail FROM living_assertions.status WHERE name = 'promises:pgd2';
 state  |                detail                
--------+--------------------------------------
 broken | invalid_unique_index on pgd2.t_id_uk
(1 row)

-- QUESTION 2: what it said the previous time. This is what 0.1.0 could not answer
-- in any way, because it stored nothing.
SELECT count(*) AS times_scanned,
       count(*) FILTER (WHERE state = 'holds')  AS times_clean,
       count(*) FILTER (WHERE state = 'broken') AS times_with_a_breach
  FROM living_assertions.checks c
  JOIN living_assertions.assertions a ON a.id = c.assertion
 WHERE a.name = 'promises:pgd2';
 times_scanned | times_clean | times_with_a_breach 
---------------+-------------+---------------------
             2 |           1 |                   1
(1 row)

DROP SCHEMA pgd CASCADE;
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to table pgd.ok
drop cascades to table pgd.customers
drop cascades to table pgd.invoices
drop cascades to table pgd.audit_log
drop cascades to function pgd.log_insert()
drop cascades to table pgd.tenant_data
DROP SCHEMA pgd2 CASCADE;
NOTICE:  drop cascades to table pgd2.t
DROP EXTENSION pg_promise_guard;
DROP EXTENSION pg_living_assertions CASCADE;