-- 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
CREATE EXTENSION pg_promise_guard;
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_no_vacio CHECK (v <> '');
SELECT count(*) AS hallazgos_en_esquema_sano FROM check_promises('pgd');
 hallazgos_en_esquema_sano 
---------------------------
                         0
(1 row)

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

-- ===========================================================================
-- PROMISE 1: "this column is unique"
-- ===========================================================================
CREATE TABLE pgd.clientes (id int, rut text);
INSERT INTO pgd.clientes 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 clientes_rut_uk ON pgd.clientes (rut);
ERROR:  could not create unique index "clientes_rut_uk"
DETAIL:  Key (rut)=(dup) is duplicated.
-- THE PREMISE, not the detection: is uniqueness actually gone?
INSERT INTO pgd.clientes VALUES (3, 'dup');
SELECT count(*) AS filas_con_el_mismo_rut FROM pgd.clientes WHERE rut = 'dup';
 filas_con_el_mismo_rut 
------------------------
                      3
(1 row)

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

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

-- ===========================================================================
-- PROMISE 3: "the audit trigger is running"
-- ===========================================================================
CREATE TABLE pgd.auditoria (que text);
CREATE FUNCTION pgd.anotar() RETURNS trigger LANGUAGE plpgsql AS
  $$ BEGIN INSERT INTO pgd.auditoria VALUES (TG_TABLE_NAME); RETURN NEW; END $$;
CREATE TRIGGER audita AFTER INSERT ON pgd.facturas
  FOR EACH ROW EXECUTE FUNCTION pgd.anotar();
INSERT INTO pgd.facturas VALUES (2, 100);
ALTER TABLE pgd.facturas DISABLE TRIGGER audita;
INSERT INTO pgd.facturas VALUES (3, 200);
SELECT count(*) AS filas_insertadas FROM pgd.facturas WHERE monto > 0;
 filas_insertadas 
------------------
                2
(1 row)

SELECT count(*) AS filas_auditadas FROM pgd.auditoria;
 filas_auditadas 
-----------------
               1
(1 row)

-- ===========================================================================
-- PROMISE 4: "row level security protects this table"
-- ===========================================================================
CREATE TABLE pgd.tenant_data (tenant text, dato text);
ALTER TABLE pgd.tenant_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY solo_lo_mio 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.audita          | pgd.facturas    | breach
 invalid_unique_index | pgd.clientes_rut_uk | pgd.clientes    | breach
 unenforced_rls       | pgd.tenant_data     | pgd.tenant_data | breach
 not_valid_constraint | pgd.monto_positivo  | pgd.facturas    | gap
(4 rows)

SELECT promises_kept('pgd') AS promesas_cumplidas;
 promesas_cumplidas 
--------------------
 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, monto numeric);
INSERT INTO pgd2.t VALUES (1, -1);
ALTER TABLE pgd2.t ADD CONSTRAINT m_pos CHECK (monto > 0) NOT VALID;
SELECT count(*) AS solo_gaps FROM check_promises('pgd2');
 solo_gaps 
-----------
         1
(1 row)

SELECT promises_kept('pgd2') AS sigue_verde_con_un_gap;
 sigue_verde_con_un_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.facturas ENABLE TRIGGER audita;
SELECT count(*) AS triggers_apagados FROM check_promises('pgd')
WHERE kind = 'disabled_trigger';
 triggers_apagados 
-------------------
                 0
(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.clientes
drop cascades to table pgd.facturas
drop cascades to table pgd.auditoria
drop cascades to function pgd.anotar()
drop cascades to table pgd.tenant_data
DROP SCHEMA pgd2 CASCADE;
NOTICE:  drop cascades to table pgd2.t
DROP EXTENSION pg_promise_guard;