-- =============================================================================
-- KI-Kostenverfolgung für Dokumenten-Check
-- Tabelle:  dc_ai_cost_log
-- Package:  DC_COSTS_PKG
-- =============================================================================
-- Deployment:
--   sqlplus user/pass@db @"Scripts/KI-Kostenverfolgung.sql"
--
-- Skript ist idempotent (DROP IF EXISTS vor jedem CREATE).
-- =============================================================================


-- =============================================================================
-- Tabelle: dc_ai_cost_log
-- =============================================================================
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE dc_ai_cost_log CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

CREATE TABLE dc_ai_cost_log (
  id                NUMBER         GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  called_at         TIMESTAMP      DEFAULT SYSTIMESTAMP                   NOT NULL,
  provider          VARCHAR2(30)                                           NOT NULL, -- mistral | ollama
  model_name        VARCHAR2(100)                                          NOT NULL,
  operation         VARCHAR2(30)                                           NOT NULL, -- TRANSLATE | EVALUATE | OCR | CATALOG
  project_id        NUMBER,
  catalog_id        NUMBER,
  prompt_tokens     NUMBER         DEFAULT 0                               NOT NULL,
  completion_tokens NUMBER         DEFAULT 0                               NOT NULL,
  total_tokens      NUMBER         DEFAULT 0                               NOT NULL,
  cost_eur          NUMBER(14, 8)  DEFAULT 0                               NOT NULL,
  created_at        TIMESTAMP      DEFAULT SYSTIMESTAMP                    NOT NULL,
  created_by        VARCHAR2(200)  DEFAULT 'SYSTEM'                        NOT NULL
);

COMMENT ON TABLE  dc_ai_cost_log                  IS 'Protokoll aller KI-API-Aufrufe mit Token-Verbrauch und Kosten';
COMMENT ON COLUMN dc_ai_cost_log.operation        IS 'TRANSLATE, EVALUATE, OCR, CATALOG';
COMMENT ON COLUMN dc_ai_cost_log.cost_eur         IS 'Berechnete Kosten in EUR basierend auf konfigurierten Preisen';

CREATE INDEX idx_ai_cost_called_at   ON dc_ai_cost_log (called_at);
CREATE INDEX idx_ai_cost_project_id  ON dc_ai_cost_log (project_id);
CREATE INDEX idx_ai_cost_catalog_id  ON dc_ai_cost_log (catalog_id);
CREATE INDEX idx_ai_cost_ym          ON dc_ai_cost_log (TO_CHAR(called_at, 'YYYY-MM'));


-- =============================================================================
-- PL/SQL Package: DC_COSTS_PKG
-- =============================================================================
CREATE OR REPLACE PACKAGE DC_COSTS_PKG AS

  -- -------------------------------------------------------------------------
  -- Gesamtkosten + Kosten des laufenden Monats
  -- -------------------------------------------------------------------------
  PROCEDURE get_summary(
    p_total_eur         OUT NUMBER,
    p_total_tokens      OUT NUMBER,
    p_total_calls       OUT NUMBER,
    p_month_eur         OUT NUMBER,
    p_month_tokens      OUT NUMBER,
    p_month_calls       OUT NUMBER
  );

  -- -------------------------------------------------------------------------
  -- Kosten pro Monat + Modell (für APEX-Reports / direkte Abfragen)
  -- Gibt einen REF CURSOR zurück (Spalten: month, provider, model_name,
  -- operation, call_count, prompt_tokens, completion_tokens, total_tokens, cost_eur)
  -- -------------------------------------------------------------------------
  FUNCTION get_monthly_detail RETURN SYS_REFCURSOR;

END DC_COSTS_PKG;
/

CREATE OR REPLACE PACKAGE BODY DC_COSTS_PKG AS

  PROCEDURE get_summary(
    p_total_eur         OUT NUMBER,
    p_total_tokens      OUT NUMBER,
    p_total_calls       OUT NUMBER,
    p_month_eur         OUT NUMBER,
    p_month_tokens      OUT NUMBER,
    p_month_calls       OUT NUMBER
  ) IS
  BEGIN
    -- Gesamtkosten (all time)
    SELECT NVL(SUM(cost_eur), 0),
           NVL(SUM(total_tokens), 0),
           COUNT(*)
    INTO   p_total_eur, p_total_tokens, p_total_calls
    FROM   dc_ai_cost_log;

    -- Laufender Monat
    SELECT NVL(SUM(cost_eur), 0),
           NVL(SUM(total_tokens), 0),
           COUNT(*)
    INTO   p_month_eur, p_month_tokens, p_month_calls
    FROM   dc_ai_cost_log
    WHERE  called_at >= TRUNC(SYSDATE, 'MM');

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_total_eur    := 0; p_total_tokens := 0; p_total_calls := 0;
      p_month_eur    := 0; p_month_tokens := 0; p_month_calls := 0;
  END get_summary;


  FUNCTION get_monthly_detail RETURN SYS_REFCURSOR IS
    v_cur SYS_REFCURSOR;
  BEGIN
    OPEN v_cur FOR
      SELECT
        TO_CHAR(called_at, 'YYYY-MM')  AS month,
        provider,
        model_name,
        operation,
        COUNT(*)                        AS call_count,
        SUM(prompt_tokens)              AS prompt_tokens,
        SUM(completion_tokens)          AS completion_tokens,
        SUM(total_tokens)               AS total_tokens,
        ROUND(SUM(cost_eur), 6)         AS cost_eur
      FROM  dc_ai_cost_log
      WHERE called_at >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -11)
      GROUP BY TO_CHAR(called_at, 'YYYY-MM'), provider, model_name, operation
      ORDER BY month DESC, cost_eur DESC;
    RETURN v_cur;
  END get_monthly_detail;

END DC_COSTS_PKG;
/

-- =============================================================================
-- Kurztest (optionale Verifikation nach Deployment)
-- =============================================================================
-- DECLARE
--   v_te NUMBER; v_tt NUMBER; v_tc NUMBER;
--   v_me NUMBER; v_mt NUMBER; v_mc NUMBER;
-- BEGIN
--   DC_COSTS_PKG.get_summary(v_te,v_tt,v_tc,v_me,v_mt,v_mc);
--   DBMS_OUTPUT.PUT_LINE('Total EUR: ' || v_te || ', Calls: ' || v_tc);
--   DBMS_OUTPUT.PUT_LINE('Month EUR: ' || v_me || ', Calls: ' || v_mc);
-- END;
-- /