-- diagnose_poi_test_trigger.sql
-- Schritt für Schritt ausführen oder alles auf einmal — Ergebnisse lesen.

-- 1. PostgreSQL-Version (EXECUTE FUNCTION erst ab PG 11)
SHOW server_version;

-- 2. Existieren Tabellen?
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename IN ('poi_test', 'sc_test', 'poi_test_log', 'sc_test_log')
ORDER BY tablename;

-- 3. Existieren Log-Funktionen?
SELECT n.nspname AS schema, p.proname AS funktion, pg_get_function_identity_arguments(p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname LIKE '%smart_log%'
ORDER BY p.proname;

-- 4. Trigger an poi_test / sc_test
SELECT c.relname AS tabelle, t.tgname AS trigger_name, p.proname AS funktion, t.tgenabled AS aktiv
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
JOIN pg_proc p ON p.oid = t.tgfoid
WHERE c.relname IN ('poi_test', 'sc_test')
  AND NOT t.tgisinternal
ORDER BY c.relname, t.tgname;

-- 5. Kann action_codes gelesen werden? (Trigger braucht das)
SELECT code_id, action_name FROM action_codes WHERE action_name IN ('is_clean', 'is_dirty') ORDER BY action_name;

-- 6. Mini-Test: Funktion manuell anlegbar? (nur wenn Schritt 3 fn_smart_log_poi_test NICHT zeigt)
--    → Wenn hier ERROR: Meldung genau lesen (fehlende Tabelle, Rechte, Syntax)
-- CREATE OR REPLACE FUNCTION public.fn_smart_log_poi_test() ...
