Dokumenten-Check/Scripts/DC_UTILS_PKG.sql

-- =============================================================================
-- DC_UTILS_PKG
-- Hilfsprozeduren und -funktionen für den Dokumenten-Check
--
-- Deployment:
--   sql user/pass@db @"Scripts/DC_UTILS_PKG.sql"
-- =============================================================================


-- =============================================================================
-- Package Spec
-- =============================================================================
CREATE OR REPLACE PACKAGE DC_UTILS_PKG AS

    -- Absender-Adresse für Berichts-Mails (anpassen falls nötig)
    c_mail_from CONSTANT VARCHAR2(100) := 'express-o@frigosped.de';

    -- Basis-URL der APEX-App für Direktlinks in Mails (Friendly URL, ohne
    -- Session-ID -> APEX Deep-Linking leitet bei Bedarf über den Login).
    -- Die Projekt-ID wird angehängt: ...?p7_project_id=<id>
    c_app_link_base CONSTANT VARCHAR2(300) :=
        'https://apex-ai.express-o.org/ords/r/ai_dev/dokumentencheck/' ||
        'projekt-bearbeiten?p7_project_id=';

    -- -------------------------------------------------------------------------
    -- Erstellt einen Markdown-Prüfbericht für ein Projekt und speichert ihn
    -- in dc_projects.report_markdown. Anschließend wird der Bericht per Mail
    -- an notification_email versendet (sofern gesetzt).
    --
    -- Aufbau des Berichts:
    --   1. Kopf (Projektname, Abschlussdatum)
    --   2. Zusammenfassung (OK / Hinweise / Abweichungen) – gefiltert nach notify_*
    --   3. Ergebnisse: je Dokument → Kategorie (order_nr) → Frage (order_nr)
    --      Icons: ✅ OK  |  ⚠️ Hinweis  |  ❌ Abweichung  |  ❓ Unklar
    --
    -- Beispiel:
    --   BEGIN DC_UTILS_PKG.generate_report(p_project_id => 1); COMMIT; END;
    -- -------------------------------------------------------------------------
    PROCEDURE generate_report(p_project_id IN NUMBER);

    -- -------------------------------------------------------------------------
    -- Versendet den gespeicherten Markdown-Bericht per E-Mail als PDF-Anhang.
    -- Wird automatisch von generate_report aufgerufen; kann aber auch
    -- manuell ausgelöst werden (z.B. für erneuten Versand).
    -- Tut nichts wenn notification_email oder report_markdown leer ist.
    -- p_skip_pdf = TRUE: kein HTTP-Rückruf ans Java-Backend (nur Text-Mail).
    -- -------------------------------------------------------------------------
    PROCEDURE send_report_email(p_project_id IN NUMBER,
                                p_skip_pdf   IN BOOLEAN DEFAULT FALSE,
                                p_pdf        IN BLOB    DEFAULT NULL);

END DC_UTILS_PKG;
/


-- =============================================================================
-- Package Body
-- =============================================================================
CREATE OR REPLACE PACKAGE BODY DC_UTILS_PKG AS

    -- -------------------------------------------------------------------------
    PROCEDURE generate_report(p_project_id IN NUMBER) IS

        v_md       CLOB;
        v_name     dc_projects.name%TYPE;
        v_status   dc_projects.status%TYPE;
        v_done     dc_projects.completed_at%TYPE;

        v_ok_cnt    NUMBER := 0;
        v_warn_cnt  NUMBER := 0;
        v_dev_cnt   NUMBER := 0;
        v_unkl_cnt  NUMBER := 0;
        v_total     NUMBER := 0;
        v_total_all NUMBER := 0;

        -- Zähler pro Dokument
        v_doc_ok_cnt    NUMBER := 0;
        v_doc_warn_cnt  NUMBER := 0;
        v_doc_dev_cnt   NUMBER := 0;
        v_doc_unkl_cnt  NUMBER := 0;
        v_doc_total     NUMBER := 0;  -- gefiltert (für ok_cnt-Berechnung)
        v_doc_total_all NUMBER := 0;  -- ungefiltert (für Gesamt-Zeile)

        v_notify_ok           dc_projects.notify_ok%TYPE;
        v_notify_hinweis      dc_projects.notify_hinweis%TYPE;
        v_notify_abweichend   dc_projects.notify_abweichend%TYPE;

        -- Emoji-Konstanten via UNISTR (vermeidet Quellcode-Encoding-Probleme)
        C_OK    CONSTANT VARCHAR2(10 CHAR) := UNISTR('\2705');           -- ✅
        C_NOK   CONSTANT VARCHAR2(10 CHAR) := UNISTR('\274C');           -- ❌
        C_WARN  CONSTANT VARCHAR2(10 CHAR) := UNISTR('\26A0\FE0F');      -- ⚠️
        C_UNKL  CONSTANT VARCHAR2(10 CHAR) := UNISTR('\2753');           -- ❓
        C_DOC   CONSTANT VARCHAR2(10 CHAR) := UNISTR('\D83D\DCC4');      -- 📄

        CURSOR c_docs IS
            SELECT d.id, d.filename
            FROM   dc_project_documents d
            WHERE  d.project_id = p_project_id
            ORDER  BY d.uploaded_at, d.id;

        CURSOR c_cats(p_doc_id NUMBER) IS
            SELECT DISTINCT qc.id, qc.name, qc.order_nr
            FROM   dc_question_categories qc
            JOIN   dc_questions           q  ON q.category_id  = qc.id
            JOIN   dc_results             r  ON r.question_id  = q.id
            WHERE  r.project_id = p_project_id
              AND  r.doc_id     = p_doc_id
              AND  (   (r.deviation = 1 AND v_notify_abweichend = 'Y')
                    OR (r.warning   = 1 AND v_notify_hinweis    = 'Y')
                    OR (r.deviation = 0 AND r.warning = 0 AND v_notify_ok = 'Y')
                   )
            ORDER  BY qc.order_nr, qc.name;

        CURSOR c_qs(p_doc_id NUMBER, p_cat_id NUMBER) IS
            SELECT q.question_text,
                   r.result_type,
                   r.score,
                   r.deviation,
                   r.warning,
                   r.answer,
                   r.the_comment
            FROM   dc_questions q
            JOIN   dc_results   r ON r.question_id = q.id
            WHERE  r.project_id  = p_project_id
              AND  r.doc_id      = p_doc_id
              AND  q.category_id = p_cat_id
              AND  (   (r.deviation = 1 AND v_notify_abweichend = 'Y')
                    OR (r.warning   = 1 AND v_notify_hinweis    = 'Y')
                    OR (r.deviation = 0 AND r.warning = 0 AND v_notify_ok = 'Y')
                   )
            ORDER  BY q.order_nr, q.id;

        v_icon VARCHAR2(20 CHAR);

        -- Fügt eine Zeile + Newline an den CLOB an. Akzeptiert CLOB damit kein
        -- implizites CLOB→VARCHAR2-Cast nötig ist (question_text etc. sind CLOBs).
        PROCEDURE ln(p_text IN CLOB DEFAULT NULL) IS
        BEGIN
            IF p_text IS NOT NULL AND DBMS_LOB.GETLENGTH(p_text) > 0 THEN
                DBMS_LOB.APPEND(v_md, p_text);
            END IF;
            DBMS_LOB.APPEND(v_md, TO_CLOB(CHR(13)||CHR(10)));
        END ln;

    BEGIN
        DBMS_LOB.CREATETEMPORARY(v_md, TRUE);

        SELECT name, status, completed_at,
               notify_ok, notify_hinweis, notify_abweichend
        INTO   v_name, v_status, v_done,
               v_notify_ok, v_notify_hinweis, v_notify_abweichend
        FROM   dc_projects
        WHERE  id = p_project_id;

        SELECT COUNT(*)
        INTO   v_total_all
        FROM   dc_results
        WHERE  project_id = p_project_id;

        SELECT SUM(CASE WHEN deviation   = 1 THEN 1 ELSE 0 END),
               SUM(CASE WHEN warning     = 1 THEN 1 ELSE 0 END),
               SUM(CASE WHEN result_type = 'UNKLAR'
                             AND deviation = 0
                             AND warning   = 0 THEN 1 ELSE 0 END),
               COUNT(*)
        INTO   v_dev_cnt, v_warn_cnt, v_unkl_cnt, v_total
        FROM   dc_results
        WHERE  project_id = p_project_id
          AND  (   (deviation = 1 AND v_notify_abweichend = 'Y')
               OR  (warning   = 1 AND v_notify_hinweis    = 'Y')
               OR  (deviation = 0 AND warning = 0 AND v_notify_ok = 'Y')
               );

        v_ok_cnt := v_total - v_dev_cnt - v_warn_cnt - v_unkl_cnt;

        -- =====================================================================
        -- Kopf
        -- =====================================================================
        ln('#  Prüfbericht: ' || v_name);
        ln();
        IF v_done IS NOT NULL THEN
            ln('**Abgeschlossen:** ' || TO_CHAR(v_done, 'DD.MM.YYYY HH24:MI'));
        ELSE
            ln('**Erstellt:** ' || TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI'));
        END IF;
        ln();
        ln('---');
        ln();

        -- =====================================================================
        -- Zusammenfassung (keine Tabelle – Plaintext für maximale Kompatibilität)
        -- =====================================================================
        ln('## Zusammenfassung');
        ln();
        IF v_notify_ok = 'Y' THEN
            ln(C_OK   || '  OK: '       || v_ok_cnt);
            IF v_unkl_cnt > 0 THEN
                ln(C_UNKL || '  Unklar: ' || v_unkl_cnt);
            END IF;
        END IF;
        IF v_notify_hinweis = 'Y' THEN
            ln(C_WARN || '  Hinweise: '     || v_warn_cnt);
        END IF;
        IF v_notify_abweichend = 'Y' THEN
            ln(C_NOK  || '  Abweichungen: ' || v_dev_cnt);
        END IF;
        ln('**Gesamt: ' || v_total_all || '**');
        ln();
        ln('---');
        ln();

        -- =====================================================================
        -- Ergebnisse je Dokument → Kategorie (order_nr) → Frage (order_nr)
        -- =====================================================================
        ln('## Ergebnisse');
        ln();

        FOR doc IN c_docs LOOP
            ln('## ' || C_DOC || ' ' || doc.filename);
            ln();

            -- Zusammenfassung je Dokument (gefiltert nach notify_*, analog zur Gesamt-Zusammenfassung)
            SELECT SUM(CASE WHEN deviation = 1
                                 AND v_notify_abweichend = 'Y'                    THEN 1 ELSE 0 END),
                   SUM(CASE WHEN warning   = 1 AND deviation = 0
                                 AND v_notify_hinweis    = 'Y'                    THEN 1 ELSE 0 END),
                   SUM(CASE WHEN result_type = 'UNKLAR' AND deviation = 0 AND warning = 0
                                 AND v_notify_ok         = 'Y'                    THEN 1 ELSE 0 END),
                   SUM(CASE WHEN (deviation = 1 AND v_notify_abweichend = 'Y')
                                 OR (warning = 1 AND v_notify_hinweis   = 'Y')
                                 OR (deviation = 0 AND warning = 0
                                     AND v_notify_ok = 'Y')                       THEN 1 ELSE 0 END),
                   COUNT(*)
            INTO   v_doc_dev_cnt, v_doc_warn_cnt, v_doc_unkl_cnt, v_doc_total, v_doc_total_all
            FROM   dc_results
            WHERE  project_id = p_project_id
              AND  doc_id     = doc.id;

            v_doc_ok_cnt := v_doc_total - v_doc_dev_cnt - v_doc_warn_cnt - v_doc_unkl_cnt;

            IF v_notify_ok = 'Y' THEN
                ln(C_OK   || '  OK:              ' || v_doc_ok_cnt);
                IF v_doc_unkl_cnt > 0 THEN
                    ln(C_UNKL || '  Unklar:          ' || v_doc_unkl_cnt);
                END IF;
            END IF;
            IF v_notify_hinweis = 'Y' THEN
                ln(C_WARN || '  Hinweise:        ' || v_doc_warn_cnt);
            END IF;
            IF v_notify_abweichend = 'Y' THEN
                ln(C_NOK  || '  Abweichungen:    ' || v_doc_dev_cnt);
            END IF;
            ln('**Gesamt: ' || v_doc_total_all || '**');
            ln();
            ln('---');
            ln();

            FOR cat IN c_cats(doc.id) LOOP
                ln();
                ln('### ' || cat.name);
                ln();

                FOR q IN c_qs(doc.id, cat.id) LOOP

                    IF    q.deviation   = 1 THEN v_icon := C_NOK;
                    ELSIF q.warning     = 1 THEN v_icon := C_WARN;
                    ELSIF q.result_type = 'UNKLAR' THEN v_icon := C_UNKL;
                    ELSE                            v_icon := C_OK;
                    END IF;


                    ln();
                    ln();
                    ln(v_icon || ' **' || q.question_text || '**');
                    ln();

                    IF q.answer IS NOT NULL AND DBMS_LOB.GETLENGTH(q.answer) > 0 THEN
                        ln(q.answer);
                        ln();
                    END IF;

                    IF q.score IS NOT NULL THEN
                        ln('*Score: ' || q.score || '%*');
                    END IF;

                    IF q.the_comment IS NOT NULL
                       AND DBMS_LOB.GETLENGTH(q.the_comment) > 0 THEN
                        ln();
                        ln('> ' || q.the_comment);
                    END IF;

                    ln();
                END LOOP;

            END LOOP;

            ln('---');
            ln();
        END LOOP;

        UPDATE dc_projects
        SET    report_markdown = v_md
        WHERE  id = p_project_id;

        DBMS_LOB.FREETEMPORARY(v_md);

        -- Mail-Versand (Fehler blockieren nie den Report-Build)
        BEGIN
            send_report_email(p_project_id);
        EXCEPTION WHEN OTHERS THEN NULL;
        END;

    EXCEPTION
        WHEN OTHERS THEN
            DBMS_LOB.FREETEMPORARY(v_md);
            RAISE;
    END generate_report;


    -- -------------------------------------------------------------------------
    PROCEDURE send_report_email(p_project_id IN NUMBER,
                                p_skip_pdf   IN BOOLEAN DEFAULT FALSE,
                                p_pdf        IN BLOB    DEFAULT NULL) IS

        v_email     dc_projects.notification_email%TYPE;
        v_name      dc_projects.name%TYPE;
        v_markdown  dc_projects.report_markdown%TYPE;
        v_done      dc_projects.completed_at%TYPE;
        v_mail_id   NUMBER;
        v_pdf       BLOB;
        v_body      VARCHAR2(4000);

        v_ok_cnt    NUMBER;
        v_warn_cnt  NUMBER;
        v_dev_cnt   NUMBER;
        v_total_all NUMBER;

        C_OK    CONSTANT VARCHAR2(10 CHAR) := UNISTR('\2705');       -- ✅
        C_NOK   CONSTANT VARCHAR2(10 CHAR) := UNISTR('\274C');       -- ❌
        C_WARN  CONSTANT VARCHAR2(10 CHAR) := UNISTR('\26A0\FE0F'); -- ⚠️

        CRLF CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);

    BEGIN
        SELECT notification_email, name, report_markdown, completed_at
        INTO   v_email, v_name, v_markdown, v_done
        FROM   dc_projects
        WHERE  id = p_project_id;

        IF v_email IS NULL OR TRIM(v_email) IS NULL THEN RETURN; END IF;
        IF v_markdown IS NULL OR DBMS_LOB.GETLENGTH(v_markdown) = 0 THEN RETURN; END IF;

        -- Zusammenfassung aus dc_results (alle Ergebnisse, ungefiltert)
        SELECT COUNT(*),
               SUM(CASE WHEN deviation = 1                   THEN 1 ELSE 0 END),
               SUM(CASE WHEN warning   = 1 AND deviation = 0 THEN 1 ELSE 0 END)
        INTO   v_total_all, v_dev_cnt, v_warn_cnt
        FROM   dc_results
        WHERE  project_id = p_project_id;

        v_ok_cnt := v_total_all - v_dev_cnt - v_warn_cnt;

        -- =====================================================================
        -- E-Mail-Beschreibungstext
        -- =====================================================================
        v_body :=
            'Sehr geehrte Damen und Herren,' || CRLF ||
            CRLF ||
            'die automatische Dokumentenprüfung für das Projekt' || CRLF ||
            '„' || v_name || '"' || CRLF ||
            'wurde am ' || TO_CHAR(NVL(v_done, SYSDATE), 'DD.MM.YYYY') ||
            ' erfolgreich abgeschlossen.' || CRLF ||
            CRLF ||
            'Zusammenfassung der Prüfergebnisse:' || CRLF ||
            C_OK   || '  OK:              ' || v_ok_cnt    || CRLF ||
            C_WARN || '  Hinweise:        ' || v_warn_cnt  || CRLF ||
            C_NOK  || '  Abweichungen:    ' || v_dev_cnt   || CRLF ||
            '    Gesamt:         ' || v_total_all || ' Prüfpunkte' || CRLF ||
            CRLF ||
            'Den vollständigen Prüfbericht finden Sie als PDF im Anhang.' || CRLF ||
            CRLF ||
            'Direkt zur Prüfung in der Anwendung:' || CRLF ||
            c_app_link_base || p_project_id || CRLF ||
            CRLF ||
            'Mit freundlichen Grüßen' || CRLF ||
            'Ihr Dokumenten-Check-System' || CRLF ||
            'Frigosped Express GmbH';

        -- =====================================================================
        -- PDF bestimmen: Priorität p_pdf (von Java mitgeliefert) >
        -- convert_markdown HTTP-Callback > kein PDF (skip_pdf oder Fehler)
        -- =====================================================================
        IF p_pdf IS NOT NULL AND DBMS_LOB.GETLENGTH(p_pdf) > 0 THEN
            -- Java hat das PDF schon fertig mitgeschickt – direkt verwenden
            v_pdf := p_pdf;
        ELSIF NOT p_skip_pdf THEN
            BEGIN
                v_pdf := DC_BACKEND_PKG.convert_markdown(v_markdown, 'PDF');
            EXCEPTION WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('PDF-Generierung fehlgeschlagen: ' || SQLERRM);
                v_pdf := NULL;
            END;
        END IF;

        IF v_pdf IS NOT NULL THEN
            -- Mail mit PDF-Anhang
            v_mail_id := APEX_MAIL.SEND(
                p_to   => v_email,
                p_from => c_mail_from,
                p_subj => 'Prüfbericht: ' || v_name,
                p_body => v_body
            );

            APEX_MAIL.ADD_ATTACHMENT(
                p_mail_id    => v_mail_id,
                p_attachment => v_pdf,
                p_filename   => 'Pruefbericht_' ||
                                REGEXP_REPLACE(v_name, '[^A-Za-z0-9_äöüÄÖÜß-]', '_') ||
                                '.pdf',
                p_mime_type  => 'application/pdf'
            );
        ELSE
            -- Fallback: Backend nicht erreichbar → Markdown direkt im Body
            APEX_MAIL.SEND(
                p_to   => v_email,
                p_from => c_mail_from,
                p_subj => 'Prüfbericht: ' || v_name,
                p_body => v_body || CRLF || CRLF ||
                          '--- Detaillierter Bericht ---' || CRLF || CRLF ||
                          v_markdown
            );
        END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN NULL;
    END send_report_email;

END DC_UTILS_PKG;
/


-- =============================================================================
-- Schnelltest (optional, auskommentiert)
-- =============================================================================
/*
BEGIN
    DC_UTILS_PKG.generate_report(p_project_id => 1);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Bericht generiert.');
END;
/
*/