-- qbit-webhooks: add the foreign keys to an existing PostgreSQL schema
-- (one created from webhooks-postgres.sql before they were declared there).
--
-- Safe to run more than once: a foreign key that already exists with the
-- right ON DELETE (under any name) is left alone. Before each key is added,
-- rows that point at a missing parent are recorded in qqq_fk_quarantine and
-- then set to NULL (nullable column) or deleted (NOT NULL column; the whole
-- row is kept in row_data). An existing key on the column with a different
-- ON DELETE (endpoint_secret and subscription -> endpoint were NO ACTION) is
-- replaced by the named one.
--
-- Order matters: deliveries of a missing endpoint are removed before their
-- attempts are checked.

BEGIN;

CREATE TABLE IF NOT EXISTS qqq_fk_quarantine (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, table_name VARCHAR(100) NOT NULL, column_name VARCHAR(100) NOT NULL, row_id VARCHAR(100), missing_parent_id VARCHAR(100), action VARCHAR(20) NOT NULL, row_data TEXT, changeset VARCHAR(200), quarantined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE OR REPLACE FUNCTION pg_temp.qqq_add_fk(p_table TEXT, p_column TEXT, p_parent TEXT, p_on_delete TEXT) RETURNS VOID LANGUAGE plpgsql AS
$fn$
DECLARE
   v_name      TEXT    := 'fk_' || p_table || '_' || p_column;
   v_rule      "char"  := CASE p_on_delete WHEN 'CASCADE' THEN 'c' WHEN 'SET NULL' THEN 'n' WHEN 'RESTRICT' THEN 'r' ELSE 'a' END;
   v_nullable  BOOLEAN;
   v_changeset TEXT    := 'qbit-webhooks:webhooks-foreign-keys-upgrade.sql';
   v_old       RECORD;
BEGIN
   IF EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
              WHERE c.contype = 'f' AND c.conrelid = p_table::regclass AND cardinality(c.conkey) = 1 AND a.attname = p_column
                AND c.confrelid = p_parent::regclass AND c.confdeltype = v_rule) THEN
      RETURN;
   END IF;

   FOR v_old IN SELECT c.conname FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
                WHERE c.contype = 'f' AND c.conrelid = p_table::regclass AND cardinality(c.conkey) = 1 AND a.attname = p_column LOOP
      EXECUTE format('ALTER TABLE %I DROP CONSTRAINT %I', p_table, v_old.conname);
   END LOOP;

   SELECT is_nullable = 'YES' INTO v_nullable FROM information_schema.columns
   WHERE table_schema = current_schema() AND table_name = p_table AND column_name = p_column;

   IF v_nullable THEN
      EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, changeset) '
         || 'SELECT %L, %L, t.id::TEXT, t.%I::TEXT, ''NULLED'', %L FROM %I t WHERE t.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
         p_table, p_column, p_column, v_changeset, p_table, p_column, p_parent, p_column);
      EXECUTE format('UPDATE %I t SET %I = NULL WHERE t.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
         p_table, p_column, p_column, p_parent, p_column);
   ELSE
      EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) '
         || 'SELECT %L, %L, t.id::TEXT, t.%I::TEXT, ''DELETED'', row_to_json(t)::TEXT, %L FROM %I t WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
         p_table, p_column, p_column, v_changeset, p_table, p_parent, p_column);
      EXECUTE format('DELETE FROM %I t WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)', p_table, p_parent, p_column);
   END IF;

   EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES %I (id) ON DELETE %s', p_table, v_name, p_column, p_parent, p_on_delete);
END
$fn$;

SELECT pg_temp.qqq_add_fk('webhooks_endpoint_secret', 'endpoint_id', 'webhooks_endpoint', 'CASCADE');
SELECT pg_temp.qqq_add_fk('webhooks_subscription', 'endpoint_id', 'webhooks_endpoint', 'CASCADE');
SELECT pg_temp.qqq_add_fk('webhooks_delivery', 'endpoint_id', 'webhooks_endpoint', 'RESTRICT');
SELECT pg_temp.qqq_add_fk('webhooks_delivery', 'subscription_id', 'webhooks_subscription', 'SET NULL');
SELECT pg_temp.qqq_add_fk('webhooks_delivery_attempt', 'delivery_id', 'webhooks_delivery', 'CASCADE');
SELECT pg_temp.qqq_add_fk('webhooks_delivery_attempt', 'endpoint_id', 'webhooks_endpoint', 'RESTRICT');

CREATE INDEX IF NOT EXISTS webhooks_delivery_subscription_ix ON webhooks_delivery (subscription_id);

COMMIT;