CREATE OR REPLACE FUNCTION public.fn_smart_log_stolpersteine()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    session_action_label TEXT;
    session_uid INTEGER;
    found_code INTEGER;
    recent_log_id INTEGER;
BEGIN
    -- 1. Variablen aus der Session abgreifen
    session_action_label := current_setting('myapp.action', true);
    
    -- UID holen (Fallback auf 1)
    BEGIN
        session_uid := current_setting('myapp.user_id')::integer;
    EXCEPTION WHEN OTHERS THEN
        session_uid := 1; 
    END;

    -- 2. Action Code suchen
    SELECT code_id INTO found_code FROM action_codes WHERE action_name = session_action_label;

    -- NEU: Wenn kein Code gefunden wurde, wird der Log-Vorgang hier abgebrochen
    IF found_code IS NULL THEN 
        RETURN NEW; -- Beendet die Funktion, der eigentliche Update am Stein passiert trotzdem
    END IF;

    -- ANTI-SPAM: 60 Minuten Intervall, bis eine Änderung an einem Stein erneut gelogt wird
    SELECT log_id INTO recent_log_id
    FROM stolpersteine_log
    WHERE poi_gid = NEW.gid
      AND changed_at > NOW() - INTERVAL '60 minutes'
    ORDER BY changed_at DESC
    LIMIT 1;

    IF recent_log_id IS NOT NULL THEN
        UPDATE stolpersteine_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 stolpersteine_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$