-- Deterministic daily-AU lookup for an already resolved game query plan.
-- Physical user-master objects are selected only from SG_GAME_CATALOG.
-- No game name, alias, prefix, or object name is embedded in this function.

CREATE OR REPLACE FUNCTION sg_game_daily_au_lookup(
  p_query_plan IN CLOB,
  p_base_date  IN DATE DEFAULT NULL
) RETURN CLOB AUTHID DEFINER IS
  v_plan             JSON_OBJECT_T;
  v_targets          JSON_ARRAY_T;
  v_target           JSON_OBJECT_T;
  v_result           JSON_OBJECT_T := JSON_OBJECT_T();
  v_items            JSON_ARRAY_T := JSON_ARRAY_T();
  v_item             JSON_OBJECT_T;
  v_game_key         VARCHAR2(128);
  v_game_id          VARCHAR2(128);
  v_game_name        VARCHAR2(512);
  v_object_name      VARCHAR2(128);
  v_safe_object_name VARCHAR2(128);
  v_effective_date   DATE;
  v_au_count         NUMBER;
  v_column_count     PLS_INTEGER;
  v_object_count     PLS_INTEGER;
  v_seen             SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  v_target_count     PLS_INTEGER := 0;

  FUNCTION is_seen(p_game_key IN VARCHAR2) RETURN BOOLEAN IS
  BEGIN
    FOR i IN 1 .. v_seen.COUNT LOOP
      IF v_seen(i) = p_game_key THEN
        RETURN TRUE;
      END IF;
    END LOOP;
    RETURN FALSE;
  END;

  PROCEDURE add_status(
    p_game_key IN VARCHAR2,
    p_status   IN VARCHAR2,
    p_reason   IN VARCHAR2
  ) IS
  BEGIN
    v_item := JSON_OBJECT_T();
    v_item.put('gameKey', p_game_key);
    v_item.put('status', p_status);
    v_item.put('reason', p_reason);
    v_items.append(v_item);
  END;
BEGIN
  IF p_query_plan IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'queryPlan is required');
  END IF;

  v_plan := JSON_OBJECT_T.parse(p_query_plan);
  v_targets := v_plan.get_array('dataEligibleTargets');
  IF v_targets IS NULL THEN
    v_targets := v_plan.get_array('targets');
  END IF;

  IF v_targets IS NOT NULL AND v_targets.get_size > 0 THEN
    FOR i IN 0 .. v_targets.get_size - 1 LOOP
      v_target := TREAT(v_targets.get(i) AS JSON_OBJECT_T);
      IF v_target IS NULL OR NOT v_target.has('gameKey') THEN
        CONTINUE;
      END IF;
      v_game_key := v_target.get_string('gameKey');
      IF v_game_key IS NULL OR is_seen(v_game_key) THEN
        CONTINUE;
      END IF;
      v_seen.EXTEND;
      v_seen(v_seen.COUNT) := v_game_key;
      v_target_count := v_target_count + 1;

      BEGIN
        SELECT game_id, game_nm, user_master_object_name
          INTO v_game_id, v_game_name, v_object_name
          FROM sg_game_catalog
         WHERE game_key = v_game_key
           AND active_yn = 'Y';
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          add_status(v_game_key, 'UNAVAILABLE', 'Catalog target is not active.');
          CONTINUE;
      END;

      IF v_object_name IS NULL THEN
        add_status(v_game_key, 'UNAVAILABLE', 'No approved user-master object is registered.');
        CONTINUE;
      END IF;

      v_safe_object_name := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(v_object_name));
      SELECT COUNT(*) INTO v_object_count
        FROM user_objects
       WHERE object_name = v_safe_object_name
         AND object_type IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW')
         AND status = 'VALID';
      SELECT COUNT(*) INTO v_column_count
        FROM user_tab_columns
       WHERE table_name = v_safe_object_name
         AND column_name IN ('GUID', 'BASE_DT', 'AU_FLAG', 'EXPT_USER_YN');
      IF v_object_count = 0 OR v_column_count <> 4 THEN
        add_status(v_game_key, 'UNAVAILABLE', 'Approved user-master object is not query-ready.');
        CONTINUE;
      END IF;

      IF p_base_date IS NULL THEN
        EXECUTE IMMEDIATE 'SELECT MAX(BASE_DT) FROM ' || v_safe_object_name
          INTO v_effective_date;
      ELSE
        v_effective_date := TRUNC(p_base_date);
      END IF;
      IF v_effective_date IS NULL THEN
        add_status(v_game_key, 'NO_DATA', 'No available base date in the selected object.');
        CONTINUE;
      END IF;

      EXECUTE IMMEDIATE
          'SELECT COUNT(DISTINCT GUID) FROM ' || v_safe_object_name
          || ' WHERE BASE_DT = :1 AND AU_FLAG = 1 AND EXPT_USER_YN = ''N'''
        INTO v_au_count USING v_effective_date;

      v_item := JSON_OBJECT_T();
      v_item.put('gameKey', v_game_key);
      v_item.put('gameId', v_game_id);
      v_item.put('gameName', v_game_name);
      v_item.put('objectName', v_safe_object_name);
      v_item.put('baseDate', TO_CHAR(v_effective_date, 'YYYY-MM-DD'));
      v_item.put('auCount', v_au_count);
      v_item.put('status', 'READY');
      v_item.put('sqlTemplate',
          'SELECT COUNT(DISTINCT GUID) AS AU_COUNT FROM <catalog_user_master_object> '
          || 'WHERE BASE_DT = :baseDate AND AU_FLAG = 1 AND EXPT_USER_YN = ''N''');
      v_items.append(v_item);
    END LOOP;
  END IF;

  v_result.put('status', CASE WHEN v_target_count = 0 THEN 'NO_GAME_TARGET' ELSE 'GAME_AU_LOOKUP' END);
  v_result.put('targetType', NVL(v_plan.get_string('targetType'), 'NONE'));
  IF p_base_date IS NULL THEN
    v_result.put_null('requestedBaseDate');
  ELSE
    v_result.put('requestedBaseDate', TO_CHAR(TRUNC(p_base_date), 'YYYY-MM-DD'));
  END IF;
  v_result.put('targetCount', v_target_count);
  v_result.put('items', v_items);
  RETURN v_result.to_clob;
END;
/