Dokumenten-Check/Scripts/DC_UTILS_PKG.sql

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


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

    -- -------------------------------------------------------------------------
    -- Erstellt einen Markdown-Prüfbericht für ein Projekt und speichert ihn
    -- in dc_projects.report_markdown.
    --
    -- Aufbau des Berichts:
    --   1. Kopf (Projektname, Abschlussdatum)
    --   2. Zusammenfassung (OK / Hinweise / Abweichungen)
    --   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);

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;

        -- 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
            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
            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
        INTO   v_name, v_status, v_done
        FROM   dc_projects
        WHERE  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;

        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();
        ln(C_OK   || '  OK: '           || v_ok_cnt);
        ln(C_WARN || '  Hinweise: '     || v_warn_cnt);
        ln(C_NOK  || '  Abweichungen: ' || v_dev_cnt);
        IF v_unkl_cnt > 0 THEN
            ln(C_UNKL || '  Unklar: '   || v_unkl_cnt);
        END IF;
        ln('**Gesamt: ' || v_total || '**');
        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();

            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);

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

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;
/
*/