Dokumenten-Check/Scripts/ORDS-ai-costs-Endpunkt.sql

-- =============================================================================
-- ORDS-Endpunkte: KI-Kostenverfolgung  (ai-costs)
-- Ergänzt das Modul frigosped.dc um Template 17 (POST + GET).
--
-- Voraussetzung: dc_ai_cost_log-Tabelle und DC_COSTS_PKG bereits vorhanden
--   sqlplus user/pass@db @"Scripts/KI-Kostenverfolgung.sql"
--
-- Deployment:
--   sqlplus user/pass@db @"Scripts/ORDS-ai-costs-Endpunkt.sql"
--
-- Idempotent: bestehende Handler werden zuerst gelöscht.
-- =============================================================================
SET DEFINE OFF

BEGIN

  -- Idempotenz: Handler entfernen falls vorhanden
  BEGIN
    ORDS.DELETE_HANDLER(
      p_module_name  => 'frigosped.dc',
      p_uri_template => 'ai-costs',
      p_method       => 'POST'
    );
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  BEGIN
    ORDS.DELETE_HANDLER(
      p_module_name  => 'frigosped.dc',
      p_uri_template => 'ai-costs',
      p_method       => 'GET'
    );
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  BEGIN
    ORDS.DELETE_TEMPLATE(
      p_module_name  => 'frigosped.dc',
      p_uri_template => 'ai-costs'
    );
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  -- Template anlegen
  ORDS.DEFINE_TEMPLATE(
    p_module_name => 'frigosped.dc',
    p_pattern     => 'ai-costs',
    p_priority    => 0,
    p_comments    => 'KI-Kostenprotokoll: POST speichert Aufruf, GET liefert Auswertung'
  );

  -- POST /api/dc/ai-costs
  ORDS.DEFINE_HANDLER(
    p_module_name => 'frigosped.dc',
    p_pattern     => 'ai-costs',
    p_method      => 'POST',
    p_source_type => ORDS.SOURCE_TYPE_PLSQL,
    p_comments    => '201 Created mit neuer cost_log id',
    p_source      => '
      DECLARE
        v_body    CLOB;
        v_new_id  NUMBER;
      BEGIN
        v_body := :body_text;

        INSERT INTO dc_ai_cost_log (
          provider, model_name, operation,
          project_id, catalog_id,
          prompt_tokens, completion_tokens, total_tokens,
          cost_eur
        ) VALUES (
          JSON_VALUE(v_body, ''$.provider''),
          JSON_VALUE(v_body, ''$.model_name''),
          JSON_VALUE(v_body, ''$.operation''),
          TO_NUMBER(JSON_VALUE(v_body, ''$.project_id'')),
          TO_NUMBER(JSON_VALUE(v_body, ''$.catalog_id'')),
          NVL(TO_NUMBER(JSON_VALUE(v_body, ''$.prompt_tokens'')),     0),
          NVL(TO_NUMBER(JSON_VALUE(v_body, ''$.completion_tokens'')), 0),
          NVL(TO_NUMBER(JSON_VALUE(v_body, ''$.total_tokens'')),      0),
          NVL(TO_NUMBER(JSON_VALUE(v_body, ''$.cost_eur'')),          0)
        )
        RETURNING id INTO v_new_id;

        :status_code := 201;
        HTP.P(''{"id":'' || v_new_id || ''}'');

      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          :status_code := 500;
          HTP.P(''{"error":"'' || REPLACE(SQLERRM, ''"'', '''''''') || ''"}'' );
      END;
    '
  );

  -- GET /api/dc/ai-costs
  ORDS.DEFINE_HANDLER(
    p_module_name => 'frigosped.dc',
    p_pattern     => 'ai-costs',
    p_method      => 'GET',
    p_source_type => ORDS.SOURCE_TYPE_PLSQL,
    p_comments    => 'Kostenauswertung: total, current_month, monatliche Aufschluesselung via DC_COSTS_PKG',
    p_source      => '
      DECLARE
        v_total_eur     NUMBER;
        v_total_tokens  NUMBER;
        v_total_calls   NUMBER;
        v_month_eur     NUMBER;
        v_month_tokens  NUMBER;
        v_month_calls   NUMBER;
        v_monthly_json  CLOB;
      BEGIN
        DC_COSTS_PKG.get_summary(
          v_total_eur,    v_total_tokens,   v_total_calls,
          v_month_eur,    v_month_tokens,   v_month_calls
        );

        SELECT JSON_ARRAYAGG(
                 JSON_OBJECT(
                   ''month''             VALUE month,
                   ''cost_eur''          VALUE ROUND(cost_eur, 6),
                   ''prompt_tokens''     VALUE prompt_tokens,
                   ''completion_tokens'' VALUE completion_tokens,
                   ''total_tokens''      VALUE total_tokens,
                   ''call_count''        VALUE call_count
                 )
                 ORDER BY month DESC
                 RETURNING CLOB
               )
        INTO   v_monthly_json
        FROM  (
          SELECT
            TO_CHAR(called_at, ''YYYY-MM'')  AS month,
            SUM(cost_eur)                    AS cost_eur,
            SUM(prompt_tokens)               AS prompt_tokens,
            SUM(completion_tokens)           AS completion_tokens,
            SUM(total_tokens)                AS total_tokens,
            COUNT(*)                         AS call_count
          FROM  dc_ai_cost_log
          WHERE called_at >= ADD_MONTHS(TRUNC(SYSDATE, ''MM''), -11)
          GROUP BY TO_CHAR(called_at, ''YYYY-MM'')
        );

        :status_code := 200;
        HTP.P(
          ''{"total":{''
          || ''  "cost_eur":''     || ROUND(NVL(v_total_eur,    0), 6)
          || '', "total_tokens":'' || NVL(v_total_tokens, 0)
          || '', "call_count":''   || NVL(v_total_calls,  0)
          || ''}, "current_month":{''
          || ''  "month":"''       || TO_CHAR(SYSDATE, ''YYYY-MM'') || ''"''
          || '', "cost_eur":''     || ROUND(NVL(v_month_eur,    0), 6)
          || '', "total_tokens":'' || NVL(v_month_tokens, 0)
          || '', "call_count":''   || NVL(v_month_calls,  0)
          || ''}, "monthly":''     || NVL(v_monthly_json, ''[]'')
          || ''}''
        );

      EXCEPTION
        WHEN OTHERS THEN
          :status_code := 500;
          HTP.P(''{"error":"'' || REPLACE(SQLERRM, ''"'', '''''''') || ''"}'' );
      END;
    '
  );

  COMMIT;
END;
/