-- setup_prod_poi_rename_cutover.sql
-- ============================================================
-- NUR WÄHREND GEPLANTER DOWNTIME AUSFÜHREN
-- Voraussetzung: App nutzt bereits config.php mit poi / poi_log (Views).
-- Ziel: Echte Tabellen poi + poi_log, Views entfernen.
-- ============================================================
--
-- VORHER (Dry Run, ohne Downtime):
--   SELECT * FROM pg_views WHERE viewname IN ('poi', 'poi_log');
--   SELECT c.relname, t.tgname, p.proname
--   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 = 'stolpersteine' AND NOT t.tgisinternal;
--
-- ABLAUF:
--   1. App offline (Wartungsseite / kein Traffic)
--   2. Dieses Skript
--   3. Smoke-Test (fetch_pois, update cleaning status)
--   4. App online
--
-- config.php: prod-Zweig bleibt poi / poi_log — KEINE Code-Änderung nötig.

BEGIN;

-- 1. Views entfernen (blockieren RENAME sonst)
DROP VIEW IF EXISTS public.poi CASCADE;
DROP VIEW IF EXISTS public.poi_log CASCADE;

-- 2. Tabellen umbenennen (Trigger wandern mit)
ALTER TABLE public.stolpersteine RENAME TO poi;
ALTER TABLE public.stolpersteine_log RENAME TO poi_log;

-- 3. Alten Trigger entfernen (Name ggf. anpassen — siehe Diagnose oben)
DROP TRIGGER IF EXISTS trg_stolpersteine_smart_log ON public.poi;
DROP TRIGGER IF EXISTS trg_smart_log_stolpersteine ON public.poi;
-- ... weitere Namen falls vorhanden

DROP FUNCTION IF EXISTS public.fn_smart_log_stolpersteine() CASCADE;

-- 4. Log-Funktion neu (Body: poi_log statt stolpersteine_log)
--    → Inhalt aus fn_smart_log_poi_test übernehmen, nur Log-Tabelle = poi_log
CREATE OR REPLACE FUNCTION public.fn_smart_log_poi()
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_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_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_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$;

CREATE TRIGGER trg_smart_log_poi
    AFTER UPDATE ON public.poi
    FOR EACH ROW
    EXECUTE PROCEDURE public.fn_smart_log_poi();

COMMIT;

-- 5. Kontrolle
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name IN ('poi', 'poi_log', 'stolpersteine', 'stolpersteine_log')
ORDER BY table_name;

SELECT c.relname, t.tgname, p.proname
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 = 'poi' AND NOT t.tgisinternal;
