-- =============================================================================
-- Deploys only the POST /api/dc/projects/:project_id/complete handler.
-- Uses CLOB construction to avoid SQLcl bind-variable pre-processing of
-- :status_code / :body / :project_id inside the handler source string.
-- =============================================================================
DECLARE
  v_src CLOB;

  PROCEDURE a(p IN VARCHAR2) IS
  BEGIN DBMS_LOB.APPEND(v_src, TO_CLOB(p)); END;

BEGIN
  DBMS_LOB.CREATETEMPORARY(v_src, TRUE);

  a('      DECLARE');
  a(CHR(10)||'        v_status      dc_projects.status%TYPE;');
  a(CHR(10)||'        v_now         DATE := SYSDATE;');
  a(CHR(10)||'        v_report_md   CLOB;');
  a(CHR(10)||'        v_doc_pdfs    JSON_ARRAY_T;');
  a(CHR(10));
  a(CHR(10)||'        FUNCTION body_to_clob(p_blob IN BLOB) RETURN CLOB IS');
  a(CHR(10)||'          v_clob    CLOB;');
  a(CHR(10)||'          v_dest    INTEGER := 1;');
  a(CHR(10)||'          v_src     INTEGER := 1;');
  a(CHR(10)||'          v_lang    INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;');
  a(CHR(10)||'          v_warning INTEGER;');
  a(CHR(10)||'        BEGIN');
  a(CHR(10)||'          IF p_blob IS NULL OR DBMS_LOB.GETLENGTH(p_blob) = 0 THEN RETURN NULL; END IF;');
  a(CHR(10)||'          DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);');
  a(CHR(10)||'          DBMS_LOB.CONVERTTOCLOB(v_clob, p_blob, DBMS_LOB.LOBMAXSIZE,');
  a(CHR(10)||'                                 v_dest, v_src, NLS_CHARSET_ID(''AL32UTF8''), v_lang, v_warning);');
  a(CHR(10)||'          RETURN v_clob;');
  a(CHR(10)||'        END body_to_clob;');
  a(CHR(10));
  a(CHR(10)||'        FUNCTION base64_to_blob(p_b64 IN CLOB) RETURN BLOB IS');
  a(CHR(10)||'          v_blob   BLOB;');
  a(CHR(10)||'          v_raw    RAW(32767);');
  a(CHR(10)||'          v_len    INTEGER;');
  a(CHR(10)||'          v_chunk  INTEGER := 24000;');
  a(CHR(10)||'          v_offset INTEGER := 1;');
  a(CHR(10)||'          v_sub    VARCHAR2(32767);');
  a(CHR(10)||'        BEGIN');
  a(CHR(10)||'          DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);');
  a(CHR(10)||'          v_len := DBMS_LOB.GETLENGTH(p_b64);');
  a(CHR(10)||'          WHILE v_offset <= v_len LOOP');
  a(CHR(10)||'            v_sub := DBMS_LOB.SUBSTR(p_b64, v_chunk, v_offset);');
  a(CHR(10)||'            v_raw := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(v_sub));');
  a(CHR(10)||'            DBMS_LOB.WRITEAPPEND(v_blob, UTL_RAW.LENGTH(v_raw), v_raw);');
  a(CHR(10)||'            v_offset := v_offset + v_chunk;');
  a(CHR(10)||'          END LOOP;');
  a(CHR(10)||'          RETURN v_blob;');
  a(CHR(10)||'        END base64_to_blob;');
  a(CHR(10));
  a(CHR(10)||'      BEGIN');
  a(CHR(10)||'        DECLARE');
  a(CHR(10)||'          v_body_clob CLOB;');
  a(CHR(10)||'          v_json      JSON_OBJECT_T;');
  a(CHR(10)||'        BEGIN');
  -- Use concatenation to embed the bind variable names so SQLcl doesn't pre-process them
  a(CHR(10)||'          v_body_clob := body_to_clob(' || CHR(58) || 'body);');
  a(CHR(10)||'          IF v_body_clob IS NOT NULL THEN');
  a(CHR(10)||'            v_json      := JSON_OBJECT_T.parse(v_body_clob);');
  a(CHR(10)||'            v_report_md := v_json.get_clob(''report_markdown'');');
  a(CHR(10)||'            BEGIN');
  a(CHR(10)||'              v_doc_pdfs := v_json.get_array(''document_pdfs'');');
  a(CHR(10)||'            EXCEPTION WHEN OTHERS THEN v_doc_pdfs := NULL; END;');
  a(CHR(10)||'            DBMS_LOB.FREETEMPORARY(v_body_clob);');
  a(CHR(10)||'          END IF;');
  a(CHR(10)||'        EXCEPTION WHEN OTHERS THEN NULL;');
  a(CHR(10)||'        END;');
  a(CHR(10));
  a(CHR(10)||'        SELECT status');
  a(CHR(10)||'        INTO   v_status');
  a(CHR(10)||'        FROM   dc_projects');
  a(CHR(10)||'        WHERE  id = ' || CHR(58) || 'project_id');
  a(CHR(10)||'        FOR UPDATE NOWAIT;');
  a(CHR(10));
  a(CHR(10)||'        IF v_status = ''COMPLETED'' THEN');
  a(CHR(10)||'          ' || CHR(58) || 'status_code := 409;');
  a(CHR(10)||'          HTP.P(''{"error":"Projekt ist bereits COMPLETED","project_id":'' || ' || CHR(58) || 'project_id || ''}'');');
  a(CHR(10)||'          RETURN;');
  a(CHR(10)||'        END IF;');
  a(CHR(10));
  a(CHR(10)||'        UPDATE dc_projects');
  a(CHR(10)||'        SET    status                   = ''COMPLETED'',');
  a(CHR(10)||'               completed_at            = v_now,');
  a(CHR(10)||'               progress                = 100,');
  a(CHR(10)||'               current_phase           = ''COMPLETED'',');
  a(CHR(10)||'               current_doc_id          = NULL,');
  a(CHR(10)||'               current_question_id     = NULL,');
  a(CHR(10)||'               estimated_completion_at = NULL');
  a(CHR(10)||'        WHERE  id = ' || CHR(58) || 'project_id;');
  a(CHR(10));
  a(CHR(10)||'        IF v_report_md IS NOT NULL AND DBMS_LOB.GETLENGTH(v_report_md) > 0 THEN');
  a(CHR(10)||'          UPDATE dc_projects SET report_markdown = v_report_md WHERE id = ' || CHR(58) || 'project_id;');
  a(CHR(10));
  a(CHR(10)||'          BEGIN');
  a(CHR(10)||'            DECLARE');
  a(CHR(10)||'              v_email    dc_projects.notification_email%TYPE;');
  a(CHR(10)||'              v_name     dc_projects.name%TYPE;');
  a(CHR(10)||'              v_done     dc_projects.completed_at%TYPE;');
  a(CHR(10)||'              v_mail_id  NUMBER;');
  a(CHR(10)||'              v_ok_cnt   NUMBER := 0;');
  a(CHR(10)||'              v_warn_cnt NUMBER := 0;');
  a(CHR(10)||'              v_dev_cnt  NUMBER := 0;');
  a(CHR(10)||'              v_total    NUMBER := 0;');
  a(CHR(10)||'              C_OK   CONSTANT VARCHAR2(10 CHAR) := UNISTR(''\2705'');');
  a(CHR(10)||'              C_NOK  CONSTANT VARCHAR2(10 CHAR) := UNISTR(''\274C'');');
  a(CHR(10)||'              C_WARN CONSTANT VARCHAR2(10 CHAR) := UNISTR(''\26A0\FE0F'');');
  a(CHR(10)||'              CRLF   CONSTANT VARCHAR2(2)       := CHR(13)||CHR(10);');
  a(CHR(10)||'              v_body     VARCHAR2(4000);');
  a(CHR(10)||'              v_doc_obj  JSON_OBJECT_T;');
  a(CHR(10)||'              v_pdf_b64  CLOB;');
  a(CHR(10)||'              v_pdf_blob BLOB;');
  a(CHR(10)||'              v_fname    VARCHAR2(500);');
  a(CHR(10)||'            BEGIN');
  a(CHR(10)||'              SELECT notification_email, name, completed_at');
  a(CHR(10)||'              INTO   v_email, v_name, v_done');
  a(CHR(10)||'              FROM   dc_projects WHERE id = ' || CHR(58) || 'project_id;');
  a(CHR(10));
  a(CHR(10)||'              IF v_email IS NOT NULL AND TRIM(v_email) IS NOT NULL THEN');
  a(CHR(10)||'                SELECT COUNT(*),');
  a(CHR(10)||'                       SUM(CASE WHEN deviation = 1                   THEN 1 ELSE 0 END),');
  a(CHR(10)||'                       SUM(CASE WHEN warning   = 1 AND deviation = 0 THEN 1 ELSE 0 END)');
  a(CHR(10)||'                INTO   v_total, v_dev_cnt, v_warn_cnt');
  a(CHR(10)||'                FROM   dc_results WHERE project_id = ' || CHR(58) || 'project_id;');
  a(CHR(10)||'                v_ok_cnt := v_total - v_dev_cnt - v_warn_cnt;');
  a(CHR(10));
  a(CHR(10)||'                v_body :=');
  a(CHR(10)||'                  ''Sehr geehrte Damen und Herren,'' || CRLF ||');
  a(CHR(10)||'                  CRLF ||');
  a(CHR(10)||'                  ''die automatische Dokumentenprüfung für das Projekt'' || CRLF ||');
  a(CHR(10)||'                  ''„'' || v_name || ''"'' || CRLF ||');
  a(CHR(10)||'                  ''wurde am '' || TO_CHAR(NVL(v_done, SYSDATE), ''DD.MM.YYYY'') ||');
  a(CHR(10)||'                  '' erfolgreich abgeschlossen.'' || CRLF ||');
  a(CHR(10)||'                  CRLF ||');
  a(CHR(10)||'                  ''Zusammenfassung der Prüfergebnisse:'' || CRLF ||');
  a(CHR(10)||'                  C_OK   || ''  OK:              '' || v_ok_cnt   || CRLF ||');
  a(CHR(10)||'                  C_WARN || ''  Hinweise:        '' || v_warn_cnt || CRLF ||');
  a(CHR(10)||'                  C_NOK  || ''  Abweichungen:    '' || v_dev_cnt  || CRLF ||');
  a(CHR(10)||'                  ''    Gesamt:         '' || v_total || '' Prüfpunkte'' || CRLF ||');
  a(CHR(10)||'                  CRLF ||');
  a(CHR(10)||'                  ''Die Prüfberichte der einzelnen Dokumente finden Sie als PDF-Anhänge.'' || CRLF ||');
  a(CHR(10)||'                  CRLF ||');
  a(CHR(10)||'                  ''Mit freundlichen Grüßen'' || CRLF ||');
  a(CHR(10)||'                  ''Ihr Dokumenten-Check-System'' || CRLF ||');
  a(CHR(10)||'                  ''Frigosped Express GmbH'';');
  a(CHR(10));
  a(CHR(10)||'                v_mail_id := APEX_MAIL.SEND(');
  a(CHR(10)||'                  p_to   => v_email,');
  a(CHR(10)||'                  p_from => DC_UTILS_PKG.c_mail_from,');
  a(CHR(10)||'                  p_subj => ''Prüfbericht: '' || v_name,');
  a(CHR(10)||'                  p_body => v_body');
  a(CHR(10)||'                );');
  a(CHR(10));
  a(CHR(10)||'                IF v_doc_pdfs IS NOT NULL AND v_doc_pdfs.get_size > 0 THEN');
  a(CHR(10)||'                  FOR i IN 0 .. v_doc_pdfs.get_size - 1 LOOP');
  a(CHR(10)||'                    BEGIN');
  a(CHR(10)||'                      v_doc_obj  := TREAT(v_doc_pdfs.get(i) AS JSON_OBJECT_T);');
  a(CHR(10)||'                      v_fname    := v_doc_obj.get_string(''filename'');');
  a(CHR(10)||'                      v_pdf_b64  := v_doc_obj.get_clob(''pdf_base64'');');
  a(CHR(10)||'                      IF v_pdf_b64 IS NOT NULL AND DBMS_LOB.GETLENGTH(v_pdf_b64) > 0 THEN');
  a(CHR(10)||'                        v_pdf_blob := base64_to_blob(v_pdf_b64);');
  a(CHR(10)||'                        APEX_MAIL.ADD_ATTACHMENT(');
  a(CHR(10)||'                          p_mail_id    => v_mail_id,');
  a(CHR(10)||'                          p_attachment => v_pdf_blob,');
  a(CHR(10)||'                          p_filename   => NVL(v_fname, ''Pruefbericht.pdf''),');
  a(CHR(10)||'                          p_mime_type  => ''application/pdf''');
  a(CHR(10)||'                        );');
  a(CHR(10)||'                        DBMS_LOB.FREETEMPORARY(v_pdf_blob);');
  a(CHR(10)||'                        DBMS_LOB.FREETEMPORARY(v_pdf_b64);');
  a(CHR(10)||'                      END IF;');
  a(CHR(10)||'                    EXCEPTION WHEN OTHERS THEN NULL;');
  a(CHR(10)||'                    END;');
  a(CHR(10)||'                  END LOOP;');
  a(CHR(10)||'                END IF;');
  a(CHR(10)||'              END IF;');
  a(CHR(10)||'            EXCEPTION WHEN OTHERS THEN NULL;');
  a(CHR(10)||'            END;');
  a(CHR(10)||'          END;');
  a(CHR(10)||'        ELSE');
  a(CHR(10)||'          BEGIN');
  a(CHR(10)||'            DC_UTILS_PKG.generate_report(' || CHR(58) || 'project_id);');
  a(CHR(10)||'          EXCEPTION WHEN OTHERS THEN NULL;');
  a(CHR(10)||'          END;');
  a(CHR(10)||'        END IF;');
  a(CHR(10));
  a(CHR(10)||'        ' || CHR(58) || 'status_code := 200;');
  a(CHR(10)||'        HTP.P(''{"project_id":'' || ' || CHR(58) || 'project_id');
  a(CHR(10)||'           || '',\"status\":\"COMPLETED\"''');
  a(CHR(10)||'           || '',\"completed_at\":\"'' || TO_CHAR(v_now, ''YYYY-MM-DD"T"HH24:MI:SS'') || ''\"''');
  a(CHR(10)||'           || ''}'');');
  a(CHR(10));
  a(CHR(10)||'      EXCEPTION');
  a(CHR(10)||'        WHEN NO_DATA_FOUND THEN');
  a(CHR(10)||'          ' || CHR(58) || 'status_code := 404;');
  a(CHR(10)||'          HTP.P(''{"error":"Projekt nicht gefunden","project_id":'' || ' || CHR(58) || 'project_id || ''}'');');
  a(CHR(10)||'        WHEN OTHERS THEN');
  a(CHR(10)||'          ROLLBACK;');
  a(CHR(10)||'          ' || CHR(58) || 'status_code := 500;');
  a(CHR(10)||'          HTP.P(''{"error":"'' || REPLACE(SQLERRM, ''"'', '''''''') || ''"}'');');
  a(CHR(10)||'      END;');

  ORDS.DEFINE_HANDLER(
    p_module_name => 'frigosped.dc',
    p_pattern     => 'projects/:project_id/complete',
    p_method      => 'POST',
    p_source_type => ORDS.SOURCE_TYPE_PLSQL,
    p_comments    => 'Setzt status=COMPLETED, sendet pro Dokument ein PDF per E-Mail',
    p_source      => v_src
  );

  DBMS_LOB.FREETEMPORARY(v_src);
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Handler erfolgreich deployed.');
END;
/