-- Generic Few-shot structure for a filtered transaction/order detail request.
-- It fixes the output grain through an approved object pattern, not a global
-- instruction or a customer-specific answer.

DECLARE
  v_input     CLOB;
  v_embedding VECTOR;
  v_exists    NUMBER;
BEGIN
  v_input := TO_CLOB('Question pattern: List individual payment orders that match a business date and an amount condition.')
    || CHR(10) || 'Logical object role: SALES_TRANSACTION'
    || CHR(10) || 'Required output: transaction identifiers, game, user, payment timestamp, and payment amount.';
  v_embedding := DBMS_VECTOR.UTL_TO_EMBEDDING(
    v_input,
    JSON(sg_qa_vector_params('search_document'))
  );

  SELECT COUNT(*)
    INTO v_exists
    FROM sg_qa_vector_example
   WHERE source_type = 'POLICY_TEMPLATE'
     AND source_case_id = 'FILTERED_TRANSACTION_DETAIL';

  IF v_exists = 0 THEN
    INSERT INTO sg_qa_vector_example (
      question, answer_sql, answer_text, embedding_input, embedding, embedding_model,
      reference_status, reference_kind, target_type, object_role,
      inspection_status, inspection_note, verified_at, verified_by,
      source_case_id, source_type
    ) VALUES (
      'List payment orders matching a date and amount condition.',
      TO_CLOB('SELECT t."PAYMT_TRANSAC_ID" AS "PAYMENT_TRANSACTION_ID",' || CHR(10)
        || '       t."PAYMT_TRANSAC_DTL_ID" AS "PAYMENT_TRANSACTION_DETAIL_ID",' || CHR(10)
        || '       t."GAME_ID" AS "GAME_ID",' || CHR(10)
        || '       t."GUID" AS "USER_ID",' || CHR(10)
        || '       t."PAYMT_DTM" AS "PAYMENT_DATETIME",' || CHR(10)
        || '       t."PAYMT_AMT" AS "PAYMENT_AMOUNT"' || CHR(10)
        || 'FROM "SGMP_POC"."COMN_SALES_TXN" t' || CHR(10)
        || 'WHERE t."PAYMT_DTM" >= <BUSINESS_DATE_START>' || CHR(10)
        || '  AND t."PAYMT_DTM" < <BUSINESS_DATE_END>' || CHR(10)
        || '  AND CAST(t."PAYMT_AMT" AS NUMBER) <AMOUNT_CONDITION>' || CHR(10)
        || '  AND t."EXPT_USER_YN" = ''N''' || CHR(10)
        || 'ORDER BY t."PAYMT_DTM", t."PAYMT_TRANSAC_ID", t."PAYMT_TRANSAC_DTL_ID"'),
      'Structural Few-shot only. Replace placeholders using the original request and approved metadata. Use the business payment timestamp for a payment-date condition. This pattern is for individual transaction detail; do not substitute an aggregate-only result for a requested order list.',
      v_input,
      v_embedding,
      'cohere.embed-v4.0',
      'APPROVED', 'SQL_TEMPLATE', 'NONE', 'SALES_TRANSACTION',
      'VERIFIED',
      'Generic transaction-detail output shape with no customer date, amount, game, or result value; not an executable answer key.',
      SYSTIMESTAMP, 'SGMP_POC_METADATA_REVIEW',
      'FILTERED_TRANSACTION_DETAIL', 'POLICY_TEMPLATE'
    );
  ELSE
    UPDATE sg_qa_vector_example
       SET embedding_input = v_input,
           embedding = v_embedding,
           verified_at = SYSTIMESTAMP,
           verified_by = 'SGMP_POC_METADATA_REVIEW'
     WHERE source_type = 'POLICY_TEMPLATE'
       AND source_case_id = 'FILTERED_TRANSACTION_DETAIL';
  END IF;
  COMMIT;
END;
/

SELECT example_id, reference_status, target_type, object_role, source_case_id
  FROM sg_qa_vector_example
 WHERE source_type = 'POLICY_TEMPLATE'
   AND source_case_id = 'FILTERED_TRANSACTION_DETAIL';