-- Customer QA is evaluation data, never production Few-shot context.
-- Preserve it for SG_AI_QA_* baseline/history audit while retiring its vector copies.

UPDATE sg_qa_vector_example
   SET reference_status = 'RETIRED',
       inspection_note = 'Evaluation-only customer QA. Excluded from production Few-shot retrieval.',
       verified_at = SYSTIMESTAMP,
       verified_by = 'SGMP_POC_EVALUATION_SEPARATION'
 WHERE source_type = 'CUSTOMER_QA_BENCHMARK';

COMMIT;

CREATE OR REPLACE FUNCTION sg_qa_vector_search(
  p_question    IN CLOB,
  p_top_k       IN PLS_INTEGER DEFAULT 3,
  p_target_type IN VARCHAR2 DEFAULT 'ANY'
) RETURN SYS_REFCURSOR
AUTHID DEFINER
IS
  v_query_vector VECTOR;
  v_results      SYS_REFCURSOR;
  v_target_type  VARCHAR2(16) := UPPER(TRIM(NVL(p_target_type, 'ANY')));
BEGIN
  IF p_question IS NULL THEN
    RAISE_APPLICATION_ERROR(-20003, 'question is required.');
  END IF;
  IF p_top_k IS NULL OR p_top_k < 1 OR p_top_k > 20 THEN
    RAISE_APPLICATION_ERROR(-20004, 'top_k must be between 1 and 20.');
  END IF;
  IF v_target_type NOT IN ('NONE', 'SINGLE', 'MULTI', 'ALL', 'ANY') THEN
    RAISE_APPLICATION_ERROR(-20005, 'target_type must be NONE, SINGLE, MULTI, ALL, or ANY.');
  END IF;

  v_query_vector := DBMS_VECTOR.UTL_TO_EMBEDDING(
    p_question,
    JSON(sg_qa_vector_params('search_query'))
  );

  OPEN v_results FOR
    SELECT example_id,
           question,
           answer_sql,
           answer_text,
           embedding_model,
           reference_kind,
           target_type,
           object_role,
           source_case_id,
           source_type,
           vector_distance(embedding, v_query_vector, COSINE) AS cosine_distance
      FROM sg_qa_vector_example
     WHERE reference_status = 'APPROVED'
       AND (source_type IS NULL OR source_type <> 'CUSTOMER_QA_BENCHMARK')
       AND (target_type = 'ANY' OR v_target_type = 'ANY' OR target_type = v_target_type)
     ORDER BY vector_distance(embedding, v_query_vector, COSINE), example_id
     FETCH FIRST p_top_k ROWS ONLY;
  RETURN v_results;
END;
/

SELECT source_type, reference_status, COUNT(*) AS example_count
  FROM sg_qa_vector_example
 GROUP BY source_type, reference_status
 ORDER BY source_type, reference_status;