-- Reusable SINGLE-scope pattern for country-grouped daily AU.

UPDATE sg_ai_qa_question
   SET expected_focus = 'Country-grouped daily standard AU uses the resolved game user master with BASE_DT, AU_FLAG=1 and EXPT_USER_YN=''N''. '
       || 'Group by LAST_CONN_COUNTRY_CD and use the approved country dimension only for display/classification. '
       || 'STD_USER_YN is optional and cannot replace AU_FLAG for the daily metric.',
       evaluation_rule_json = '{"required_sql_terms":["CZN_COMN_USER_MST","AU_FLAG","EXPT_USER_YN","LAST_CONN_COUNTRY_CD","COUNT"],"recommended_sql_terms":["BASE_DT","COMN_COUNTRY_BAS","STD_USER_YN"],"optional_sql_terms":["STD_USER_YN"],"required_result_shape":"COUNTRY_GROUPED"}',
       updated_at = SYSTIMESTAMP
 WHERE question_code = 'CZN-06';

/

DECLARE
  v_input CLOB;
  v_embedding VECTOR;
  v_exists NUMBER;
BEGIN
  v_input := TO_CLOB('Question pattern: show daily active-user counts by country for one resolved game and one business date.')
    || CHR(10) || 'Question pattern Korean: 한 게임의 기준일 주요 국가별 표준 AU 수를 알려줘.'
    || CHR(10) || 'Logical object role: COUNTRY_GROUPED_DAILY_AU'
    || CHR(10) || 'Required metric: AU_FLAG=1 and excluded-user filtering; group by the last connection country.';
  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 = 'COUNTRY_DAILY_AU';
  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 (
      '한 게임의 기준일 주요 국가별 표준 AU 수를 알려줘.',
      TO_CLOB('SELECT u."LAST_CONN_COUNTRY_CD" AS "COUNTRY_CD",' || CHR(10)
        || '       c."COUNTRY_KR_NM" AS "COUNTRY_NAME",' || CHR(10)
        || '       COUNT(DISTINCT u."GUID") AS "STANDARD_AU_COUNT"' || CHR(10)
        || 'FROM <RESOLVED_GAME_USER_MASTER> u' || CHR(10)
        || 'LEFT JOIN <APPROVED_COUNTRY_DIMENSION> c' || CHR(10)
        || '  ON c."COUNTRY_2CHAR_CD" = u."LAST_CONN_COUNTRY_CD"' || CHR(10)
        || 'WHERE u."BASE_DT" = <BUSINESS_DATE>' || CHR(10)
        || '  AND u."AU_FLAG" = 1' || CHR(10)
        || '  AND u."EXPT_USER_YN" = ''N''' || CHR(10)
        || 'GROUP BY u."LAST_CONN_COUNTRY_CD", c."COUNTRY_KR_NM"'),
      'Applicable metric reference: country-grouped daily AU must use AU_FLAG=1 and EXPT_USER_YN=''N''; do not replace AU_FLAG with STD_USER_YN alone. Group by LAST_CONN_COUNTRY_CD. Use an approved country dimension for country display or a current approved major-country classification when the request requires it.',
      v_input, v_embedding, 'cohere.embed-v4.0',
      'APPROVED', 'SQL_TEMPLATE', 'SINGLE', 'COUNTRY_GROUPED_DAILY_AU',
      'VERIFIED', 'Reusable country-grouped daily-AU pattern; no customer game, date, result, or physical object is embedded.',
      SYSTIMESTAMP, 'SGMP_POC_METADATA_REVIEW', 'COUNTRY_DAILY_AU', 'POLICY_TEMPLATE'
    );
  ELSE
    UPDATE sg_qa_vector_example
       SET embedding_input = v_input, embedding = v_embedding,
           reference_status = 'APPROVED', inspection_status = 'VERIFIED',
           verified_at = SYSTIMESTAMP, verified_by = 'SGMP_POC_METADATA_REVIEW'
     WHERE source_type = 'POLICY_TEMPLATE' AND source_case_id = 'COUNTRY_DAILY_AU';
  END IF;
  COMMIT;
END;

/