-- Metadata-driven game catalog for deterministic scope resolution.
-- No customer game names or prefixes are embedded in this script.

BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE TABLE sg_game_catalog (
      game_key       VARCHAR2(128) PRIMARY KEY,
      game_id        VARCHAR2(128) NOT NULL,
      game_prefix    VARCHAR2(128),
      game_nm        VARCHAR2(512),
      game_alias_nm  VARCHAR2(512),
      search_text    CLOB NOT NULL,
      embedding      VECTOR(1536, FLOAT32),
      active_yn      CHAR(1) DEFAULT 'Y' NOT NULL,
      priority       NUMBER DEFAULT 100 NOT NULL,
      updated_at     TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL
    )]';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -955 THEN RAISE; END IF;
END;
/

MERGE INTO sg_game_catalog c
USING (
  SELECT
    game_id AS game_key,
    game_id,
    MAX(game_prefix) AS game_prefix,
    MAX(game_nm) AS game_nm,
    LISTAGG(game_alias_nm, ' ') WITHIN GROUP (ORDER BY game_alias_nm) AS game_alias_nm,
    game_id || ' ' || MAX(NVL(game_nm, '')) || ' '
      || LISTAGG(NVL(game_alias_nm, ''), ' ') WITHIN GROUP (ORDER BY game_alias_nm)
      || ' ' || MAX(NVL(game_prefix, '')) AS search_text
  FROM comn_game_alias_bas
  WHERE use_yn = 'Y'
  GROUP BY game_id
) s
ON (c.game_key = s.game_key)
WHEN MATCHED THEN UPDATE SET
  c.game_id = s.game_id,
  c.game_prefix = s.game_prefix,
  c.game_nm = s.game_nm,
  c.game_alias_nm = s.game_alias_nm,
  c.search_text = s.search_text,
  c.active_yn = 'Y',
  c.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN INSERT
  (game_key, game_id, game_prefix, game_nm, game_alias_nm, search_text)
VALUES
  (s.game_key, s.game_id, s.game_prefix, s.game_nm, s.game_alias_nm, s.search_text);
/

COMMENT ON TABLE sg_game_catalog IS
  'Customer-owned game catalog used to resolve query scope before NL2SQL.';
COMMENT ON COLUMN sg_game_catalog.embedding IS
  'Vector representation of game names and aliases; populated by the configured embedding job.';
/

CREATE OR REPLACE FUNCTION sg_game_catalog_search(
  p_question IN CLOB,
  p_top_k IN PLS_INTEGER DEFAULT 5
) RETURN SYS_REFCURSOR AUTHID DEFINER IS
  v_query VECTOR;
  v_result SYS_REFCURSOR;
BEGIN
  v_query := DBMS_VECTOR.UTL_TO_EMBEDDING(
    p_question,
    JSON(sg_qa_vector_params('search_query'))
  );
  OPEN v_result FOR
    SELECT game_key, game_id, game_prefix, game_nm, game_alias_nm,
           VECTOR_DISTANCE(embedding, v_query, COSINE) AS cosine_distance
      FROM sg_game_catalog
     WHERE active_yn = 'Y' AND embedding IS NOT NULL
     ORDER BY VECTOR_DISTANCE(embedding, v_query, COSINE), priority, game_key
     FETCH FIRST LEAST(GREATEST(NVL(p_top_k, 5), 1), 20) ROWS ONLY;
  RETURN v_result;
END;
/
