03_exception_handler.sql

-- =============================================================================
-- FILE: 03_exception_handler.sql
-- PURPOSE: Generic handler that constructs the AI Context Object.
-- =============================================================================

CREATE OR REPLACE PROCEDURE C360.SP_HANDLE_EXCEPTION(
    "P_EXCEPTION_TYPE" STRING,
    "P_ERROR_CODE" NUMBER,
    "P_ERROR_MESSAGE" STRING,
    "P_RUN_ID" STRING,
    "P_STEP_STATUS" STRING,
    "P_START_TIME" TIMESTAMP_NTZ,
    "P_QUERY_TAG" STRING,
    "P_ADDITIONAL_DETAILS" OBJECT DEFAULT NULL,
    "P_SQL_STATE" STRING DEFAULT NULL
)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS '
DECLARE
    v_end_time         TIMESTAMP_NTZ;
    v_execution_time   NUMBER;
    v_overall_status   STRING DEFAULT ''FAILED'';
    v_exception_defs   OBJECT;
    v_resolution_hint  STRING DEFAULT ''Contact System Administrator'';
    v_last_query_id    STRING;

BEGIN
    v_end_time := CURRENT_TIMESTAMP();
    v_execution_time := TIMESTAMPDIFF(SECOND, p_start_time, v_end_time);
    v_last_query_id := (SELECT LAST_QUERY_ID());

    -- 1. Attempt to extract specific resolution hints if passed in details
    IF (p_additional_details:resolution_hint IS NOT NULL) THEN
        v_resolution_hint := p_additional_details:resolution_hint;
    END IF;

    -- 2. Construct AI Resolution Context (The "Prompt Context" for Cortex)
    LET ai_context OBJECT := OBJECT_CONSTRUCT(
        ''incident_id'', UUID_STRING(),
        ''error_type'', p_exception_type,
        ''sql_state'', IFNULL(p_sql_state, ''N/A''),
        ''snowflake_query_id'', v_last_query_id,
        ''failed_step_intent'', p_step_status, -- What were we trying to do?
        ''error_message'', p_error_message,
        ''variables_snapshot'', p_additional_details,
        ''suggested_direction'', v_resolution_hint
    );

    -- 3. Log to Table (Persist the AI Context)
    INSERT INTO C360.LOGS.APP_LOGS (RUN_ID, LOG_LEVEL, PROCEDURE_NAME, MESSAGE, PAYLOAD, QUERY_ID)
    VALUES (
        :p_run_id,
        ''ERROR'',
        ''UNKNOWN'', -- Can be passed as param if needed
        :p_error_message,
        :ai_context,
        :v_last_query_id
    );

    -- 4. Log to System Event Table (Backup)
    SYSTEM$LOG_ERROR(''[AI_CONTEXT] '' || TO_JSON(ai_context));

    -- 5. Return standardized object to the caller
    RETURN OBJECT_CONSTRUCT(
        ''status'',           v_overall_status,
        ''run_id'',           p_run_id,
        ''execution_time'',   v_execution_time,
        ''error_code'',       p_error_code,
        ''error_message'',    p_error_message,
        ''ai_assisted_resolution'', ai_context
    );
END;
';