-- setup_poi_test_log.sql
-- Trigger-Logging für Test-POI-Tabelle → poi_test_log
--
-- VORHER ausführen (Diagnose):
-- SELECT tablename FROM pg_tables WHERE schemaname = 'public'
--   AND tablename IN ('poi_test', 'sc_test', 'poi_test_log', 'sc_test_log');

-- ============================================================
-- 0. Alten Trigger zuerst (blockiert DROP FUNCTION sonst)
-- ============================================================
DROP TRIGGER IF EXISTS trg_sc_test_smart_log ON public.poi_test;
DROP TRIGGER IF EXISTS trg_smart_log_sc_test ON public.poi_test;
DROP TRIGGER IF EXISTS trg_smart_log_poi_test ON public.poi_test;
DROP TRIGGER IF EXISTS trg_sc_test_smart_log ON public.sc_test;
DROP TRIGGER IF EXISTS trg_smart_log_sc_test ON public.sc_test;
DROP TRIGGER IF EXISTS trg_smart_log_poi_test ON public.sc_test;

DROP FUNCTION IF EXISTS public.fn_smart_log_sc_test() CASCADE;

-- ============================================================
-- 1. Neue Funktion
-- ============================================================
CREATE OR REPLACE FUNCTION public.fn_smart_log_poi_test()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
    session_action_label TEXT;
    session_uid INTEGER;
    found_code INTEGER;
    recent_log_id INTEGER;
BEGIN
    session_action_label := current_setting('myapp.action', true);

    BEGIN
        session_uid := current_setting('myapp.user_id')::integer;
    EXCEPTION WHEN OTHERS THEN
        session_uid := 1;
    END;

    SELECT code_id INTO found_code FROM action_codes WHERE action_name = session_action_label;
    IF found_code IS NULL THEN found_code := 99; END IF;

    SELECT log_id INTO recent_log_id
    FROM public.poi_test_log
    WHERE poi_gid = NEW.gid
      AND changed_at > NOW() - INTERVAL '60 seconds'
    ORDER BY changed_at DESC
    LIMIT 1;

    IF recent_log_id IS NOT NULL THEN
        UPDATE public.poi_test_log
        SET changed_at = NOW(),
            new_status = NEW.status,
            action_code = found_code,
            uid = session_uid,
            cleaned_by_log = 'User ID: ' || session_uid
        WHERE log_id = recent_log_id;
    ELSE
        INSERT INTO public.poi_test_log (poi_gid, action_code, changed_at, uid, cleaned_by_log, old_status, new_status)
        VALUES (
            NEW.gid,
            found_code,
            NOW(),
            session_uid,
            'User ID: ' || session_uid,
            CASE WHEN TG_OP = 'UPDATE' THEN OLD.status ELSE NULL END,
            NEW.status
        );
    END IF;

    RETURN NEW;
END;
$function$;

-- ============================================================
-- 2. Trigger anlegen — auf der Tabelle, die wirklich existiert
--    (EXECUTE PROCEDURE = kompatibel mit PG 10+)
-- ============================================================
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = 'public' AND c.relname = 'poi_test'
    ) THEN
        CREATE TRIGGER trg_smart_log_poi_test
            AFTER UPDATE ON public.poi_test
            FOR EACH ROW
            EXECUTE PROCEDURE public.fn_smart_log_poi_test();
        RAISE NOTICE 'Trigger trg_smart_log_poi_test auf poi_test erstellt.';
    ELSIF EXISTS (
        SELECT 1 FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = 'public' AND c.relname = 'sc_test'
    ) THEN
        RAISE EXCEPTION 'Tabelle heißt noch sc_test — bitte zuerst: ALTER TABLE public.sc_test RENAME TO poi_test;';
    ELSE
        RAISE EXCEPTION 'Weder poi_test noch sc_test gefunden.';
    END IF;
END $$;

-- ============================================================
-- 3. Kontrolle (sollte 1 Zeile zeigen)
-- ============================================================
SELECT c.relname AS tabelle, t.tgname AS trigger_name, p.proname AS funktion
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;
