Snowflake CoWorkで自然言語問い合わせできる簡易データカタログを作ってみた

USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS CATDEMO_SALES_ANALYST;
CREATE ROLE IF NOT EXISTS CATDEMO_DATA_STEWARD;
CREATE ROLE IF NOT EXISTS CATDEMO_CATALOG_ADMIN;

GRANT ROLE CATDEMO_SALES_ANALYST TO ROLE SYSADMIN;
GRANT ROLE CATDEMO_DATA_STEWARD  TO ROLE SYSADMIN;
GRANT ROLE CATDEMO_CATALOG_ADMIN TO ROLE SYSADMIN;
-- デモ用に3ロールとも検証ユーザーへ付与
GRANT ROLE CATDEMO_SALES_ANALYST TO USER <検証ユーザー>;
GRANT ROLE CATDEMO_DATA_STEWARD  TO USER <検証ユーザー>;
GRANT ROLE CATDEMO_CATALOG_ADMIN TO USER <検証ユーザー>;

CREATE WAREHOUSE IF NOT EXISTS CATDEMO_WH
  WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;
GRANT USAGE, OPERATE ON WAREHOUSE CATDEMO_WH TO ROLE CATDEMO_SALES_ANALYST;
GRANT USAGE, OPERATE ON WAREHOUSE CATDEMO_WH TO ROLE CATDEMO_DATA_STEWARD;
GRANT USAGE, OPERATE ON WAREHOUSE CATDEMO_WH TO ROLE CATDEMO_CATALOG_ADMIN;

CREATE DATABASE IF NOT EXISTS CATDEMO_RETAIL_DB;
CREATE DATABASE IF NOT EXISTS CATDEMO_CATALOG_DB;

-- Cortex 利用に必要な DB ロール
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE CATDEMO_SALES_ANALYST;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE CATDEMO_DATA_STEWARD;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE CATDEMO_CATALOG_ADMIN;

USE DATABASE CATDEMO_RETAIL_DB;

CREATE SCHEMA IF NOT EXISTS SALES     COMMENT = '販売ドメイン: 店舗・商品・注文';
CREATE SCHEMA IF NOT EXISTS CRM       COMMENT = '顧客ドメイン: 会員情報（PIIを含む）';
CREATE SCHEMA IF NOT EXISTS HR        COMMENT = '人事ドメイン: 従業員・給与（機密）';
CREATE SCHEMA IF NOT EXISTS FINANCE   COMMENT = '財務ドメイン: 決済・役員報酬';
CREATE SCHEMA IF NOT EXISTS ANALYTICS COMMENT = '分析ドメイン: 集計マート';
CREATE SCHEMA IF NOT EXISTS ADMIN     COMMENT = 'ガバナンス用: タグ・マスキングポリシー';

CREATE TAG IF NOT EXISTS ADMIN.DATA_CLASS
  ALLOWED_VALUES 'PII', 'CONFIDENTIAL', 'INTERNAL', 'PUBLIC';
CREATE TAG IF NOT EXISTS ADMIN.SENSITIVITY
  ALLOWED_VALUES 'PUBLIC', 'INTERNAL', 'CONFIDENTIAL', 'RESTRICTED';

CREATE OR REPLACE MASKING POLICY ADMIN.MASK_PII_STRING AS (val STRING) RETURNS STRING ->
CASE
  WHEN COALESCE(TRY_CAST(SYS_CONTEXT('SNOWFLAKE$CURRENT','IS_AGENT_ACTIVATED') AS BOOLEAN), FALSE)
    THEN '***MASKED(AGENT)***'
  WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN','CATDEMO_DATA_STEWARD','CATDEMO_CATALOG_ADMIN')
    THEN val
  ELSE '***MASKED***'
END;
CREATE OR REPLACE MASKING POLICY ADMIN.MASK_PII_DATE AS (val DATE) RETURNS DATE ->
CASE
  WHEN COALESCE(TRY_CAST(SYS_CONTEXT('SNOWFLAKE$CURRENT','IS_AGENT_ACTIVATED') AS BOOLEAN), FALSE)
    THEN NULL
  WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN','CATDEMO_DATA_STEWARD','CATDEMO_CATALOG_ADMIN')
    THEN val
  ELSE NULL
END;
ALTER TAG ADMIN.DATA_CLASS SET MASKING POLICY ADMIN.MASK_PII_STRING;
ALTER TAG ADMIN.DATA_CLASS SET MASKING POLICY ADMIN.MASK_PII_DATE;

CREATE OR REPLACE TABLE CRM.CUSTOMERS (
  CUSTOMER_ID   NUMBER  COMMENT '顧客ID',
  CUSTOMER_NAME VARCHAR COMMENT '氏名（PII）',
  EMAIL         VARCHAR COMMENT 'メールアドレス（PII）',
  PHONE_NUMBER  VARCHAR COMMENT '電話番号（PII）',
  BIRTH_DATE    DATE    COMMENT '生年月日（PII）',
  PREF          VARCHAR COMMENT '居住都道府県',
  MEMBER_RANK   VARCHAR COMMENT '会員ランク（BRONZE/SILVER/GOLD）',
  REGISTERED_AT DATE    COMMENT '会員登録日'
) COMMENT = '会員マスタ。会員登録済みで顧客IDを持つ個人。PII列はタグベースマスキング適用';

INSERT INTO CRM.CUSTOMERS
SELECT S + 1,
       'デモ顧客' || LPAD(S+1, 4, '0'),
       'demo_user' || LPAD(S+1, 4, '0') || '@example.com',
       '090-0000-' || LPAD(MOD(S, 10000), 4, '0'),
       DATEADD(day, -1 * UNIFORM(6600, 25000, RANDOM()), CURRENT_DATE()),
       DECODE(MOD(S,5), 0,'東京都',1,'神奈川県',2,'大阪府',3,'愛知県',4,'福岡県'),
       DECODE(MOD(S,6), 0,'GOLD',1,'SILVER',2,'SILVER',3,'BRONZE',4,'BRONZE',5,'BRONZE'),
       DATEADD(day, -1 * UNIFORM(10, 2000, RANDOM()), CURRENT_DATE())
FROM (SELECT SEQ4() AS S FROM TABLE(GENERATOR(ROWCOUNT => 200)));

ALTER TABLE CRM.CUSTOMERS MODIFY COLUMN CUSTOMER_NAME SET TAG ADMIN.DATA_CLASS = 'PII';
ALTER TABLE CRM.CUSTOMERS MODIFY COLUMN EMAIL         SET TAG ADMIN.DATA_CLASS = 'PII';
ALTER TABLE CRM.CUSTOMERS MODIFY COLUMN PHONE_NUMBER  SET TAG ADMIN.DATA_CLASS = 'PII';
ALTER TABLE CRM.CUSTOMERS MODIFY COLUMN BIRTH_DATE    SET TAG ADMIN.DATA_CLASS = 'PII';
ALTER TABLE CRM.CUSTOMERS SET TAG ADMIN.SENSITIVITY = 'CONFIDENTIAL';

CREATE OR REPLACE TABLE HR.EMPLOYEE_SALARY (
  EMP_ID        NUMBER  COMMENT '従業員ID（HR.EMPLOYEES への参照）',
  FISCAL_YEAR   NUMBER  COMMENT '会計年度',
  ANNUAL_SALARY NUMBER  COMMENT '年収（円・機密）',
  GRADE         VARCHAR COMMENT '等級'
) COMMENT = '従業員給与。人事部のみ扱う機密データ。一般アナリストには存在自体を非公開';
ALTER TABLE HR.EMPLOYEE_SALARY SET TAG ADMIN.SENSITIVITY = 'CONFIDENTIAL';

CREATE OR REPLACE TABLE FINANCE.EXEC_COMPENSATION (
  EXEC_ID     NUMBER  COMMENT '役員ID',
  EXEC_NAME   VARCHAR COMMENT '役員氏名（PII）',
  FISCAL_YEAR NUMBER  COMMENT '会計年度',
  TOTAL_COMP  NUMBER  COMMENT '報酬総額（円・最高機密）'
) COMMENT = '役員報酬。最高機密。カタログ上も管理者以外には存在自体を非公開';
ALTER TABLE FINANCE.EXEC_COMPENSATION MODIFY COLUMN EXEC_NAME SET TAG ADMIN.DATA_CLASS = 'PII';
ALTER TABLE FINANCE.EXEC_COMPENSATION SET TAG ADMIN.SENSITIVITY = 'RESTRICTED';

CREATE OR REPLACE TABLE ANALYTICS.DAILY_SALES
  COMMENT = '日次売上集計（認定済み）。ORDERS/ORDER_ITEMS/PRODUCTS から日次バッチで生成'
AS
SELECT DATE_TRUNC('day', o.ORDER_TS)::DATE AS SALES_DATE,
       o.STORE_ID,
       p.CATEGORY,
       SUM(oi.AMOUNT) AS SALES_AMOUNT,
       COUNT(DISTINCT o.ORDER_ID) AS ORDER_COUNT
FROM SALES.ORDERS o
JOIN SALES.ORDER_ITEMS oi ON o.ORDER_ID = oi.ORDER_ID
JOIN SALES.PRODUCTS p ON oi.PRODUCT_ID = p.PRODUCT_ID
WHERE o.STATUS = 'COMPLETED'
GROUP BY 1, 2, 3;

USE DATABASE CATDEMO_CATALOG_DB;
CREATE SCHEMA IF NOT EXISTS CORE;
CREATE SCHEMA IF NOT EXISTS GOVERNANCE;
CREATE SCHEMA IF NOT EXISTS SEMANTIC;
CREATE SCHEMA IF NOT EXISTS SEARCH;
CREATE SCHEMA IF NOT EXISTS AGENT;

CREATE TABLE IF NOT EXISTS CORE.ASSETS (
  FULL_NAME          VARCHAR COMMENT 'アセット完全名 DB.SCHEMA.OBJECT',
  ASSET_TYPE         VARCHAR COMMENT 'TABLE/VIEW',
  DATABASE_NAME      VARCHAR,
  SCHEMA_NAME        VARCHAR,
  OBJECT_NAME        VARCHAR,
  DESCRIPTION        VARCHAR COMMENT '説明（COMMENT 由来）',
  DOMAIN             VARCHAR COMMENT '業務ドメイン',
  SENSITIVITY        VARCHAR COMMENT '機密度（SENSITIVITY タグ由来）',
  ROW_COUNT          NUMBER,
  BYTES              NUMBER,
  CREATED_AT         TIMESTAMP_LTZ,
  LAST_ALTERED       TIMESTAMP_LTZ,
  TRUST_LEVEL        VARCHAR COMMENT 'CERTIFIED/REVIEWED/UNVERIFIED',
  LIFECYCLE_STATUS   VARCHAR COMMENT 'DRAFT/ACTIVE/DEPRECATED/RETIRED',
  IS_RECOMMENDED     BOOLEAN,
  REPLACED_BY        VARCHAR COMMENT '置き換え先アセットの完全名',
  DEPRECATION_DATE   DATE,
  USAGE_GUIDANCE     VARCHAR,
  ACCESS_REQUESTABLE BOOLEAN,
  LAST_COLLECTED_AT  TIMESTAMP_LTZ COMMENT 'カタログ収集日時',
  FRESHNESS_STATUS   VARCHAR COMMENT 'FRESH/STALE/UNKNOWN',
  QUERY_COUNT_30D    NUMBER,
  DISTINCT_USERS_30D NUMBER,
  USAGE_SOURCE       VARCHAR COMMENT '利用統計の取得元（ACCOUNT_USAGE/SEEDED）'
);

CREATE TABLE IF NOT EXISTS CORE.COLUMNS_META (
  FULL_NAME      VARCHAR,
  COLUMN_NAME    VARCHAR,
  ORDINAL        NUMBER,
  DATA_TYPE      VARCHAR,
  DESCRIPTION    VARCHAR,
  DATA_CLASS     VARCHAR COMMENT 'DATA_CLASS タグ由来',
  IS_PII         BOOLEAN,
  MASKING_POLICY VARCHAR COMMENT '適用中のマスキングポリシー名',
  SAMPLE_VALUES  VARCHAR COMMENT 'サンプル値。PII/機密列は収集対象外（生値を保管しない）'
);

CREATE TABLE IF NOT EXISTS CORE.ASSET_VISIBILITY (
  FULL_NAME              VARCHAR,
  VISIBILITY_LEVEL       NUMBER  COMMENT '必要クリアランス 10/20/30/40',
  VISIBILITY_REASON_CODE VARCHAR COMMENT 'PUBLIC_CATALOG/INTERNAL/CONFIDENTIAL/RESTRICTED',
  HIDE_EXISTENCE_FLG     BOOLEAN COMMENT 'TRUE: レベル未満のロールには存在自体を隠す'
) COMMENT = '未登録アセットは fail-closed（誰にも見えない）。管理者のみ参照可';

CREATE TABLE IF NOT EXISTS CORE.ROLE_CLEARANCE (
  ROLE_NAME       VARCHAR,
  CLEARANCE_LEVEL NUMBER
) COMMENT = 'ロール→クリアランスのマッピング。管理者のみ参照可';

CREATE TABLE IF NOT EXISTS CORE.COLLECTION_RUNS (
  RUN_ID          VARCHAR,
  STARTED_AT      TIMESTAMP_LTZ,
  FINISHED_AT     TIMESTAMP_LTZ,
  STATUS          VARCHAR COMMENT 'SUCCESS/PARTIAL_SUCCESS/FAILED/RUNNING',
  REQUESTED_SCOPE VARCHAR,
  ERROR_SUMMARY   VARCHAR
) COMMENT = 'カタログ収集の実行履歴。回答の鮮度の根拠';

SET RUN_ID = (SELECT UUID_STRING());

MERGE INTO CORE.CATALOG_SCOPE s USING (SELECT 'CATDEMO_RETAIL_DB' AS D) x
  ON s.DATABASE_NAME = x.D
  WHEN NOT MATCHED THEN INSERT (DATABASE_NAME) VALUES (x.D);

INSERT INTO CORE.COLLECTION_RUNS (RUN_ID, STARTED_AT, STATUS, REQUESTED_SCOPE)
SELECT $RUN_ID, CURRENT_TIMESTAMP(), 'RUNNING', LISTAGG(DATABASE_NAME, ',') FROM CORE.CATALOG_SCOPE;

DELETE FROM CORE.ASSETS;
INSERT INTO CORE.ASSETS
  (FULL_NAME, ASSET_TYPE, DATABASE_NAME, SCHEMA_NAME, OBJECT_NAME, DESCRIPTION, DOMAIN,
   ROW_COUNT, BYTES, CREATED_AT, LAST_ALTERED, TRUST_LEVEL, LIFECYCLE_STATUS, IS_RECOMMENDED,
   ACCESS_REQUESTABLE, LAST_COLLECTED_AT, FRESHNESS_STATUS, USAGE_SOURCE)
SELECT t.TABLE_CATALOG || '.' || t.TABLE_SCHEMA || '.' || t.TABLE_NAME,
       IFF(t.TABLE_TYPE = 'VIEW', 'VIEW', 'TABLE'),
       t.TABLE_CATALOG, t.TABLE_SCHEMA, t.TABLE_NAME,
       t.COMMENT,
       CASE t.TABLE_SCHEMA
         WHEN 'SALES' THEN '販売' WHEN 'CRM' THEN '顧客' WHEN 'HR' THEN '人事'
         WHEN 'FINANCE' THEN '財務' WHEN 'ANALYTICS' THEN '分析' ELSE 'その他' END,
       t.ROW_COUNT, t.BYTES, t.CREATED, t.LAST_ALTERED,
       'UNVERIFIED', 'ACTIVE', FALSE, TRUE, CURRENT_TIMESTAMP(),
       IFF(t.LAST_ALTERED >= DATEADD(day, -7, CURRENT_TIMESTAMP()), 'FRESH', 'STALE'),
       NULL
FROM CATDEMO_RETAIL_DB.INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_CATALOG IN (SELECT DATABASE_NAME FROM CORE.CATALOG_SCOPE)
  AND t.TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'ADMIN');

MERGE INTO CORE.ASSET_VISIBILITY v USING (
  SELECT FULL_NAME,
         CASE WHEN FULL_NAME = 'CATDEMO_RETAIL_DB.FINANCE.EXEC_COMPENSATION' THEN 40
              WHEN SCHEMA_NAME IN ('HR', 'FINANCE') THEN 30
              ELSE 20 END AS LVL
  FROM CORE.ASSETS) n
ON v.FULL_NAME = n.FULL_NAME
WHEN NOT MATCHED THEN INSERT (FULL_NAME, VISIBILITY_LEVEL, VISIBILITY_REASON_CODE, HIDE_EXISTENCE_FLG)
  VALUES (n.FULL_NAME, n.LVL,
          CASE n.LVL WHEN 40 THEN 'RESTRICTED' WHEN 30 THEN 'CONFIDENTIAL' ELSE 'INTERNAL' END,
          n.LVL >= 30);

DECLARE
  c_assets CURSOR FOR SELECT FULL_NAME FROM CORE.ASSETS;
BEGIN
  DELETE FROM CORE.TAG_ASSIGNMENTS;
  FOR r IN c_assets DO
    EXECUTE IMMEDIATE
      'INSERT INTO CORE.TAG_ASSIGNMENTS (FULL_NAME, TAG_NAME, TAG_VALUE, TAG_LEVEL, COLUMN_NAME) ' ||
      'SELECT OBJECT_DATABASE || ''.'' || OBJECT_SCHEMA || ''.'' || OBJECT_NAME, TAG_NAME, TAG_VALUE, "LEVEL", COLUMN_NAME ' ||
      'FROM TABLE(CATDEMO_RETAIL_DB.INFORMATION_SCHEMA.TAG_REFERENCES_ALL_COLUMNS(''' || r.FULL_NAME || ''', ''table''))';
  END FOR;
END;

-- カラムレベル: データ分類・PII フラグ
UPDATE CORE.COLUMNS_META cm
  SET DATA_CLASS = t.TAG_VALUE, IS_PII = (t.TAG_VALUE = 'PII')
  FROM CORE.TAG_ASSIGNMENTS t
  WHERE t.FULL_NAME = cm.FULL_NAME AND t.COLUMN_NAME = cm.COLUMN_NAME
    AND t.TAG_NAME = 'DATA_CLASS' AND t.TAG_LEVEL = 'COLUMN';

-- テーブルレベル: 機密度（ASSETS.SENSITIVITY へ反映）
UPDATE CORE.ASSETS a
  SET SENSITIVITY = t.TAG_VALUE
  FROM CORE.TAG_ASSIGNMENTS t
  WHERE t.FULL_NAME = a.FULL_NAME
    AND t.TAG_NAME = 'SENSITIVITY' AND t.TAG_LEVEL = 'TABLE';
UPDATE CORE.ASSETS SET SENSITIVITY = 'INTERNAL' WHERE SENSITIVITY IS NULL;

DECLARE
  c_sample_cols CURSOR FOR
    SELECT cm.FULL_NAME AS FN, cm.COLUMN_NAME AS CN
    FROM CORE.COLUMNS_META cm
    JOIN CORE.ASSETS a ON a.FULL_NAME = cm.FULL_NAME
    WHERE COALESCE(cm.IS_PII, FALSE) = FALSE
      AND cm.DATA_CLASS IS NULL
      AND COALESCE(a.SENSITIVITY, 'INTERNAL') NOT IN ('CONFIDENTIAL', 'RESTRICTED')
      AND cm.DATA_TYPE = 'TEXT';
BEGIN
  FOR r IN c_sample_cols DO
    EXECUTE IMMEDIATE
      'UPDATE CORE.COLUMNS_META SET SAMPLE_VALUES = LEFT((' ||
      'SELECT LISTAGG(V, '' | '') FROM (SELECT DISTINCT LEFT(TO_VARCHAR(' || r.CN || '), 40) AS V ' ||
      'FROM ' || r.FN || ' SAMPLE (50 ROWS) LIMIT 3) S), 130) ' ||
      'WHERE FULL_NAME = ''' || r.FN || ''' AND COLUMN_NAME = ''' || r.CN || '''';
  END FOR;
END;

DELETE FROM CORE.GRANTS_EDGES;
SHOW GRANTS TO ROLE CATDEMO_SALES_ANALYST;
INSERT INTO CORE.GRANTS_EDGES (FULL_NAME, PRIVILEGE, GRANTED_ON, GRANTEE_ROLE)
SELECT "name", "privilege", "granted_on", "grantee_name"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "granted_on" IN ('TABLE', 'VIEW') AND "privilege" = 'SELECT'
  AND "name" LIKE 'CATDEMO_RETAIL_DB.%';

SHOW GRANTS OF ROLE CATDEMO_SALES_ANALYST;
INSERT INTO CORE.ROLE_EDGES (PARENT_ROLE, CHILD_ROLE)
SELECT "role", "grantee_name" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "granted_to" = 'ROLE';

DELETE FROM CORE.EFFECTIVE_ACCESS;
INSERT INTO CORE.EFFECTIVE_ACCESS (FULL_NAME, ROLE_NAME, PRIVILEGE, VIA_PATH, NOTE)
WITH RECURSIVE holders AS (
  SELECT FULL_NAME, PRIVILEGE, GRANTEE_ROLE AS ROLE_NAME, GRANTEE_ROLE AS VIA_PATH
  FROM CORE.GRANTS_EDGES
  UNION ALL
  SELECT h.FULL_NAME, h.PRIVILEGE, re.CHILD_ROLE, h.VIA_PATH || ' -> ' || re.CHILD_ROLE
  FROM holders h
  JOIN CORE.ROLE_EDGES re ON re.PARENT_ROLE = h.ROLE_NAME
  WHERE LEN(h.VIA_PATH) < 500
)
SELECT DISTINCT FULL_NAME, ROLE_NAME, PRIVILEGE, VIA_PATH,
       '権限経路の情報です。実データの可視行・可視列は Row Access Policy / Masking Policy の実行時評価に従います'
FROM holders;

DELETE FROM CORE.ROLE_CLEARANCE;
INSERT INTO CORE.ROLE_CLEARANCE VALUES
  ('CATDEMO_SALES_ANALYST', 20),
  ('CATDEMO_DATA_STEWARD',  30),
  ('CATDEMO_CATALOG_ADMIN', 40),
  ('ACCOUNTADMIN',          40);  -- 収集・運用用

CREATE OR REPLACE ROW ACCESS POLICY GOVERNANCE.RAP_ASSET_VISIBILITY
AS (ASSET_FN VARCHAR) RETURNS BOOLEAN ->
  EXISTS (
    SELECT 1
    FROM CATDEMO_CATALOG_DB.CORE.ASSET_VISIBILITY v
    JOIN CATDEMO_CATALOG_DB.CORE.ROLE_CLEARANCE rc
      ON rc.CLEARANCE_LEVEL >= v.VISIBILITY_LEVEL
    WHERE v.FULL_NAME = ASSET_FN
      AND rc.ROLE_NAME = CURRENT_ROLE()
  );

-- リネージは両端が可視のときだけ行を返す（片側が隠しアセットならエッジごと不可視）
CREATE OR REPLACE ROW ACCESS POLICY GOVERNANCE.RAP_LINEAGE_VISIBILITY
AS (SRC_FN VARCHAR, TGT_FN VARCHAR) RETURNS BOOLEAN ->
  EXISTS (
    SELECT 1 FROM CATDEMO_CATALOG_DB.CORE.ASSET_VISIBILITY v
    JOIN CATDEMO_CATALOG_DB.CORE.ROLE_CLEARANCE rc ON rc.CLEARANCE_LEVEL >= v.VISIBILITY_LEVEL
    WHERE v.FULL_NAME = SRC_FN AND rc.ROLE_NAME = CURRENT_ROLE()
  )
  AND EXISTS (
    SELECT 1 FROM CATDEMO_CATALOG_DB.CORE.ASSET_VISIBILITY v
    JOIN CATDEMO_CATALOG_DB.CORE.ROLE_CLEARANCE rc ON rc.CLEARANCE_LEVEL >= v.VISIBILITY_LEVEL
    WHERE v.FULL_NAME = TGT_FN AND rc.ROLE_NAME = CURRENT_ROLE()
  );

ALTER TABLE CORE.ASSETS               ADD ROW ACCESS POLICY GOVERNANCE.RAP_ASSET_VISIBILITY ON (FULL_NAME);
ALTER TABLE CORE.COLUMNS_META         ADD ROW ACCESS POLICY GOVERNANCE.RAP_ASSET_VISIBILITY ON (FULL_NAME);
ALTER TABLE CORE.OWNERS               ADD ROW ACCESS POLICY GOVERNANCE.RAP_ASSET_VISIBILITY ON (FULL_NAME);
ALTER TABLE CORE.TAG_ASSIGNMENTS      ADD ROW ACCESS POLICY GOVERNANCE.RAP_ASSET_VISIBILITY ON (FULL_NAME);
ALTER TABLE CORE.GRANTS_EDGES         ADD ROW ACCESS POLICY GOVERNANCE.RAP_ASSET_VISIBILITY ON (FULL_NAME);
ALTER TABLE CORE.EFFECTIVE_ACCESS     ADD ROW ACCESS POLICY GOVERNANCE.RAP_ASSET_VISIBILITY ON (FULL_NAME);
ALTER TABLE CORE.DATA_HEALTH          ADD ROW ACCESS POLICY GOVERNANCE.RAP_ASSET_VISIBILITY ON (FULL_NAME);
ALTER TABLE CORE.ACCESS_REQUEST_GUIDE ADD ROW ACCESS POLICY GOVERNANCE.RAP_ASSET_VISIBILITY ON (FULL_NAME);
ALTER TABLE CORE.ASSET_TERM_RELATIONS ADD ROW ACCESS POLICY GOVERNANCE.RAP_ASSET_VISIBILITY ON (FULL_NAME);
ALTER TABLE CORE.LINEAGE_EDGES        ADD ROW ACCESS POLICY GOVERNANCE.RAP_LINEAGE_VISIBILITY ON (SOURCE_FULL_NAME, TARGET_FULL_NAME);

CREATE OR ALTER SEMANTIC VIEW CATDEMO_CATALOG_DB.SEMANTIC.SV_DATA_CATALOG

  TABLES (
    assets AS CATDEMO_CATALOG_DB.CORE.ASSETS
      PRIMARY KEY (FULL_NAME)
      WITH SYNONYMS = ('アセット', 'テーブル', 'データ資産', 'オブジェクト', 'asset')
      COMMENT = 'データアセットのインベントリ。行が返らない場合は「該当するデータは見つかりませんでした」と回答する（存在の有無・権限の有無には言及しない）',
    cols AS CATDEMO_CATALOG_DB.CORE.COLUMNS_META
      PRIMARY KEY (FULL_NAME, COLUMN_NAME)
      WITH SYNONYMS = ('カラム', '列', 'column')
      COMMENT = 'カラムメタデータ。PII 列のサンプル値は収集していない',
    owners AS CATDEMO_CATALOG_DB.CORE.OWNERS
      PRIMARY KEY (FULL_NAME, OWNER_TYPE)
      WITH SYNONYMS = ('オーナー', '所有者', 'データオーナー', 'owner')
      COMMENT = 'データオーナー（TECHNICAL=技術 / BUSINESS=ビジネス / STEWARD=スチュワード）',
    access AS CATDEMO_CATALOG_DB.CORE.EFFECTIVE_ACCESS
      PRIMARY KEY (FULL_NAME, ROLE_NAME, PRIVILEGE, VIA_PATH)
      WITH SYNONYMS = ('アクセス権', '権限', '実効アクセス', 'access')
      COMMENT = '権限経路の情報。実データの可視行・可視列は Row Access Policy / Masking Policy の実行時評価に従う点を必ず添える',
    lineage AS CATDEMO_CATALOG_DB.CORE.LINEAGE_EDGES
      PRIMARY KEY (SOURCE_FULL_NAME, TARGET_FULL_NAME, COLLECTED_VIA)
      WITH SYNONYMS = ('リネージ', '系譜', 'データの流れ', 'lineage')
      COMMENT = 'データリネージ。COLLECTED_VIA（取得方法）と COLLECTED_AT（取得日時）を回答に添える',
    health AS CATDEMO_CATALOG_DB.CORE.DATA_HEALTH
      PRIMARY KEY (FULL_NAME)
      WITH SYNONYMS = ('品質', '鮮度', 'データ品質', 'health')
      COMMENT = 'データ品質・鮮度。品質ステータスはデモ用シード値を含む',
    guide AS CATDEMO_CATALOG_DB.CORE.ACCESS_REQUEST_GUIDE
      PRIMARY KEY (FULL_NAME)
      WITH SYNONYMS = ('アクセス申請', '利用申請', '申請方法')
      COMMENT = 'アクセス申請の導線。可視なアセットについてのみ案内する',
    glossary AS CATDEMO_CATALOG_DB.CORE.BUSINESS_GLOSSARY
      PRIMARY KEY (TERM_ID)
      WITH SYNONYMS = ('用語集', 'ビジネス用語', '用語定義', 'glossary')
      COMMENT = 'ビジネス用語集。業務概念の正式定義',
    mobj AS CATDEMO_CATALOG_DB.CORE.MANAGED_OBJECTS
      PRIMARY KEY (OBJECT_TYPE, NAME)
      WITH SYNONYMS = ('管理オブジェクト', 'ウェアハウス', 'ポリシー一覧')
      COMMENT = '管理対象オブジェクト（ウェアハウス・ロール・タグ・ポリシー）',
    runs AS CATDEMO_CATALOG_DB.CORE.COLLECTION_RUNS
      PRIMARY KEY (RUN_ID)
      WITH SYNONYMS = ('収集履歴', 'カタログ更新履歴')
      COMMENT = 'カタログ収集の実行履歴。カタログ情報の鮮度の根拠'
  )

  RELATIONSHIPS (
    cols_to_assets    AS cols    (FULL_NAME) REFERENCES assets (FULL_NAME),
    owners_to_assets  AS owners  (FULL_NAME) REFERENCES assets (FULL_NAME),
    access_to_assets  AS access  (FULL_NAME) REFERENCES assets (FULL_NAME),
    lineage_to_assets AS lineage (TARGET_FULL_NAME) REFERENCES assets (FULL_NAME),
    health_to_assets  AS health  (FULL_NAME) REFERENCES assets (FULL_NAME),
    guide_to_assets   AS guide   (FULL_NAME) REFERENCES assets (FULL_NAME)
  )

  FACTS (
    assets.row_count AS assets.ROW_COUNT
      WITH SYNONYMS = ('行数', 'レコード数') COMMENT = 'テーブルの行数',
    assets.bytes AS assets.BYTES
      WITH SYNONYMS = ('サイズ', '容量') COMMENT = 'テーブルサイズ（バイト）',
    assets.query_count_30d AS assets.QUERY_COUNT_30D
      WITH SYNONYMS = ('クエリ数', '利用回数') COMMENT = '直近30日のクエリ数（USAGE_SOURCE=SEEDED はデモ用架空値）',
    assets.distinct_users_30d AS assets.DISTINCT_USERS_30D
      WITH SYNONYMS = ('利用ユーザー数') COMMENT = '直近30日の利用ユーザー数（USAGE_SOURCE=SEEDED はデモ用架空値）'
  )

  DIMENSIONS (
    assets.full_name AS assets.FULL_NAME
      WITH SYNONYMS = ('完全名', 'フルネーム') COMMENT = 'DB.SCHEMA.OBJECT 形式の完全名',
    assets.object_name AS assets.OBJECT_NAME
      WITH SYNONYMS = ('テーブル名', 'オブジェクト名') COMMENT = 'オブジェクト名',
    assets.schema_name AS assets.SCHEMA_NAME
      WITH SYNONYMS = ('スキーマ') COMMENT = 'スキーマ名',
    assets.asset_type AS assets.ASSET_TYPE COMMENT = 'TABLE/VIEW',
    assets.description AS assets.DESCRIPTION
      WITH SYNONYMS = ('説明', '概要') COMMENT = 'アセットの説明',
    assets.domain AS assets.DOMAIN
      WITH SYNONYMS = ('ドメイン', '業務領域', '業務ドメイン') COMMENT = '業務ドメイン（販売/顧客/人事/財務/分析）',
    assets.sensitivity AS assets.SENSITIVITY
      WITH SYNONYMS = ('機密度', '機密レベル') COMMENT = '機密度（SENSITIVITY タグ由来）',
    assets.trust_level AS assets.TRUST_LEVEL
      WITH SYNONYMS = ('信頼度', '認定', '認定状態') COMMENT = 'CERTIFIED=認定済み / REVIEWED=レビュー済み / UNVERIFIED=未検証',
    assets.lifecycle_status AS assets.LIFECYCLE_STATUS
      WITH SYNONYMS = ('ライフサイクル', '廃止予定', 'ステータス') COMMENT = 'DEPRECATED は置き換え先（REPLACED_BY）を必ず案内する',
    assets.is_recommended AS assets.IS_RECOMMENDED
      WITH SYNONYMS = ('推奨') COMMENT = '推奨アセットフラグ',
    assets.replaced_by AS assets.REPLACED_BY
      WITH SYNONYMS = ('置き換え先', '後継') COMMENT = '置き換え先アセット',
    assets.deprecation_date AS assets.DEPRECATION_DATE
      WITH SYNONYMS = ('廃止予定日') COMMENT = '廃止予定日',
    assets.usage_guidance AS assets.USAGE_GUIDANCE
      WITH SYNONYMS = ('利用ガイダンス', '利用上の注意') COMMENT = '利用上の注意',
    assets.freshness_status AS assets.FRESHNESS_STATUS
      WITH SYNONYMS = ('鮮度') COMMENT = 'FRESH/STALE',
    assets.last_altered AS assets.LAST_ALTERED
      WITH SYNONYMS = ('最終更新', '最終更新日時') COMMENT = '最終更新日時',
    assets.last_collected_at AS assets.LAST_COLLECTED_AT
      WITH SYNONYMS = ('収集日時', 'カタログ収集日時') COMMENT = 'カタログへの収集日時。回答の鮮度の根拠として添える',
    assets.usage_source AS assets.USAGE_SOURCE
      WITH SYNONYMS = ('統計取得元') COMMENT = 'SEEDED はデモ用架空値のため回答時にその旨を添える',
    cols.column_name AS cols.COLUMN_NAME
      WITH SYNONYMS = ('カラム名', '列名') COMMENT = 'カラム名',
    cols.data_type AS cols.DATA_TYPE COMMENT = 'データ型',
    cols.col_description AS cols.DESCRIPTION
      WITH SYNONYMS = ('カラム説明') COMMENT = 'カラムの説明',
    cols.data_class AS cols.DATA_CLASS
      WITH SYNONYMS = ('データ分類', '分類') COMMENT = 'DATA_CLASS タグ由来（PII/CONFIDENTIAL 等）',
    cols.is_pii AS cols.IS_PII
      WITH SYNONYMS = ('個人情報', 'PII') COMMENT = 'PII フラグ',
    cols.masking_policy AS cols.MASKING_POLICY
      WITH SYNONYMS = ('マスキング', 'マスキングポリシー') COMMENT = '適用中のマスキングポリシー',
    cols.sample_values AS cols.SAMPLE_VALUES
      WITH SYNONYMS = ('サンプル値', 'サンプル') COMMENT = 'PII/機密列は収集対象外（生値をカタログに保管しない方針）',
    owners.owner_type AS owners.OWNER_TYPE COMMENT = 'TECHNICAL/BUSINESS/STEWARD',
    owners.owner_name AS owners.OWNER_NAME
      WITH SYNONYMS = ('オーナー名', '所有者名', '担当部署') COMMENT = 'オーナー名',
    owners.contact AS owners.CONTACT
      WITH SYNONYMS = ('連絡先') COMMENT = '連絡先',
    access.role_name AS access.ROLE_NAME
      WITH SYNONYMS = ('ロール', 'ロール名') COMMENT = 'アクセス経路を持つロール',
    access.privilege AS access.PRIVILEGE
      WITH SYNONYMS = ('権限種別') COMMENT = '権限（SELECT 等）',
    access.via_path AS access.VIA_PATH
      WITH SYNONYMS = ('経路', '継承経路') COMMENT = 'ロール継承の経路',
    access.note AS access.NOTE COMMENT = '実データ可視性に関する注記。回答に必ず添える',
    lineage.source_full_name AS lineage.SOURCE_FULL_NAME
      WITH SYNONYMS = ('上流', 'ソース', '元テーブル') COMMENT = '上流アセット',
    lineage.target_full_name AS lineage.TARGET_FULL_NAME
      WITH SYNONYMS = ('下流', 'ターゲット') COMMENT = '下流アセット',
    lineage.collected_via AS lineage.COLLECTED_VIA
      WITH SYNONYMS = ('取得方法') COMMENT = 'GET_LINEAGE=システム実測 / MANUAL=手動申告',
    lineage.lineage_collected_at AS lineage.COLLECTED_AT COMMENT = 'リネージ取得日時',
    health.health_freshness AS health.FRESHNESS_STATUS
      WITH SYNONYMS = ('鮮度ステータス') COMMENT = 'FRESH/STALE/UNKNOWN',
    health.quality_status AS health.QUALITY_STATUS
      WITH SYNONYMS = ('品質ステータス') COMMENT = 'PASS/WARN/FAIL/UNKNOWN（デモ用シード値）',
    health.last_refreshed_at AS health.LAST_REFRESHED_AT
      WITH SYNONYMS = ('最終リフレッシュ') COMMENT = '最終データ更新日時',
    guide.requestable AS guide.REQUESTABLE
      WITH SYNONYMS = ('申請可能') COMMENT = 'アクセス申請可能か',
    guide.request_channel AS guide.REQUEST_CHANNEL
      WITH SYNONYMS = ('申請方法', '申請窓口') COMMENT = '申請チャネル',
    guide.approver AS guide.APPROVER
      WITH SYNONYMS = ('承認者') COMMENT = '承認者',
    guide.standard_lead_time_days AS guide.STANDARD_LEAD_TIME_DAYS
      WITH SYNONYMS = ('リードタイム') COMMENT = '標準リードタイム（営業日）',
    glossary.term_name_ja AS glossary.TERM_NAME_JA
      WITH SYNONYMS = ('用語', '用語名') COMMENT = 'ビジネス用語',
    glossary.definition_ja AS glossary.DEFINITION_JA
      WITH SYNONYMS = ('定義', '業務定義') COMMENT = '用語の業務定義',
    glossary.term_domain AS glossary.DOMAIN COMMENT = '用語の業務ドメイン',
    glossary.approval_status AS glossary.APPROVAL_STATUS COMMENT = 'APPROVED/DRAFT',
    mobj.object_type AS mobj.OBJECT_TYPE
      WITH SYNONYMS = ('オブジェクト種別') COMMENT = 'WAREHOUSE/ROLE/TAG/MASKING_POLICY/ROW_ACCESS_POLICY',
    mobj.mobj_name AS mobj.NAME
      WITH SYNONYMS = ('管理オブジェクト名') COMMENT = 'オブジェクト名',
    mobj.detail AS mobj.DETAIL COMMENT = '詳細（サイズ・所有者等）',
    runs.run_started_at AS runs.STARTED_AT
      WITH SYNONYMS = ('収集開始') COMMENT = '収集開始日時',
    runs.run_status AS runs.STATUS COMMENT = '収集結果（SUCCESS/PARTIAL_SUCCESS/FAILED）'
  )

  METRICS (
    assets.asset_count AS COUNT(assets.FULL_NAME)
      WITH SYNONYMS = ('アセット数', 'テーブル数', '件数') COMMENT = 'アセット件数',
    assets.total_rows AS SUM(assets.ROW_COUNT)
      WITH SYNONYMS = ('総行数') COMMENT = '行数の合計',
    cols.column_count AS COUNT(cols.COLUMN_NAME)
      WITH SYNONYMS = ('カラム数') COMMENT = 'カラム件数',
    cols.pii_column_count AS SUM(CASE WHEN cols.IS_PII THEN 1 ELSE 0 END)
      WITH SYNONYMS = ('PIIカラム数', '個人情報カラム数') COMMENT = 'PII カラムの件数',
    access.accessible_role_count AS COUNT(DISTINCT access.ROLE_NAME)
      WITH SYNONYMS = ('アクセス可能ロール数') COMMENT = 'アクセス経路を持つロール数'
  )

  COMMENT = 'AIデータカタログの Semantic View。回答原則: (1)結果が0件のときは「該当するデータは見つかりませんでした」とだけ回答し、存在や権限には言及しない (2)鮮度・利用統計は取得元を添える (3)権限の回答には「実データの可視性は実行時ポリシー評価に従う」注記を添える (4)DEPRECATED アセットは置き換え先を案内する'

  AI_VERIFIED_QUERIES (
    q_domain_inventory AS (
      QUESTION 'どんなデータがありますか？ドメイン別に教えて'
      ONBOARDING_QUESTION TRUE
      SQL 'SELECT DOMAIN, COUNT(*) AS ASSET_COUNT, LISTAGG(OBJECT_NAME, '', '') WITHIN GROUP (ORDER BY OBJECT_NAME) AS OBJECTS FROM CATDEMO_CATALOG_DB.CORE.ASSETS GROUP BY DOMAIN ORDER BY DOMAIN'
    ),
    q_pii_tables AS (
      QUESTION '個人情報（PII）を含むテーブルはどこにありますか？'
      ONBOARDING_QUESTION TRUE
      SQL 'SELECT a.FULL_NAME, a.DESCRIPTION, LISTAGG(c.COLUMN_NAME, '', '') WITHIN GROUP (ORDER BY c.COLUMN_NAME) AS PII_COLUMNS FROM CATDEMO_CATALOG_DB.CORE.ASSETS a JOIN CATDEMO_CATALOG_DB.CORE.COLUMNS_META c ON c.FULL_NAME = a.FULL_NAME WHERE c.IS_PII GROUP BY 1, 2 ORDER BY 1'
    ),
    q_owner_customers AS (
      QUESTION 'CUSTOMERS テーブルのオーナーは誰ですか？'
      ONBOARDING_QUESTION TRUE
      SQL 'SELECT a.FULL_NAME, o.OWNER_TYPE, o.OWNER_NAME, o.CONTACT FROM CATDEMO_CATALOG_DB.CORE.ASSETS a JOIN CATDEMO_CATALOG_DB.CORE.OWNERS o ON o.FULL_NAME = a.FULL_NAME WHERE a.OBJECT_NAME = ''CUSTOMERS'' ORDER BY o.OWNER_TYPE'
    ),
    q_keyword_where AS (
      QUESTION '売上に関するテーブルはどこにありますか？'
      SQL 'SELECT a.FULL_NAME, a.DESCRIPTION, a.TRUST_LEVEL, o.OWNER_NAME FROM CATDEMO_CATALOG_DB.CORE.ASSETS a LEFT JOIN CATDEMO_CATALOG_DB.CORE.OWNERS o ON o.FULL_NAME = a.FULL_NAME AND o.OWNER_TYPE = ''BUSINESS'' WHERE a.DESCRIPTION LIKE ''%売上%'' OR a.OBJECT_NAME LIKE ''%SALES%'' ORDER BY a.FULL_NAME'
    ),
    q_access_customers AS (
      QUESTION 'CUSTOMERS テーブルには誰がアクセスできますか？経路も教えて'
      SQL 'SELECT FULL_NAME, ROLE_NAME, PRIVILEGE, VIA_PATH, NOTE FROM CATDEMO_CATALOG_DB.CORE.EFFECTIVE_ACCESS WHERE FULL_NAME = ''CATDEMO_RETAIL_DB.CRM.CUSTOMERS'' ORDER BY ROLE_NAME, VIA_PATH'
    ),
    q_lineage_daily_sales AS (
      QUESTION 'DAILY_SALES は何から作られていますか？上流の鮮度も教えて'
      SQL 'SELECT l.SOURCE_FULL_NAME, l.COLLECTED_VIA, l.CONFIDENCE, l.COLLECTED_AT, h.FRESHNESS_STATUS, h.LAST_REFRESHED_AT FROM CATDEMO_CATALOG_DB.CORE.LINEAGE_EDGES l LEFT JOIN CATDEMO_CATALOG_DB.CORE.DATA_HEALTH h ON h.FULL_NAME = l.SOURCE_FULL_NAME WHERE l.TARGET_FULL_NAME = ''CATDEMO_RETAIL_DB.ANALYTICS.DAILY_SALES'' ORDER BY l.SOURCE_FULL_NAME'
    ),
    q_masked_columns AS (
      QUESTION 'マスキングが適用されているカラムの一覧を教えて'
      SQL 'SELECT FULL_NAME, COLUMN_NAME, DATA_CLASS, MASKING_POLICY FROM CATDEMO_CATALOG_DB.CORE.COLUMNS_META WHERE MASKING_POLICY IS NOT NULL ORDER BY FULL_NAME, COLUMN_NAME'
    ),
    q_popular_tables AS (
      QUESTION '直近30日でよく使われているテーブルのトップ5は？'
      SQL 'SELECT FULL_NAME, QUERY_COUNT_30D, DISTINCT_USERS_30D, USAGE_SOURCE FROM CATDEMO_CATALOG_DB.CORE.ASSETS ORDER BY QUERY_COUNT_30D DESC NULLS LAST LIMIT 5'
    ),
    q_managed_objects AS (
      QUESTION '管理対象のウェアハウスやポリシーの一覧を見せて'
      SQL 'SELECT OBJECT_TYPE, NAME, DETAIL, COMMENT_TEXT FROM CATDEMO_CATALOG_DB.CORE.MANAGED_OBJECTS ORDER BY OBJECT_TYPE, NAME'
    ),
    q_deprecated AS (
      QUESTION '廃止予定のテーブルと置き換え先は？'
      SQL 'SELECT FULL_NAME, DEPRECATION_DATE, REPLACED_BY, USAGE_GUIDANCE FROM CATDEMO_CATALOG_DB.CORE.ASSETS WHERE LIFECYCLE_STATUS = ''DEPRECATED'' ORDER BY DEPRECATION_DATE'
    ),
    q_glossary_sales AS (
      QUESTION '「売上」の定義を教えて'
      SQL 'SELECT TERM_NAME_JA, DEFINITION_JA, DOMAIN, DATA_OWNER, APPROVAL_STATUS FROM CATDEMO_CATALOG_DB.CORE.BUSINESS_GLOSSARY WHERE TERM_NAME_JA = ''売上'''
    ),
    q_catalog_freshness AS (
      QUESTION 'このカタログの情報はいつ収集されたものですか？'
      SQL 'SELECT RUN_ID, STARTED_AT, FINISHED_AT, STATUS, REQUESTED_SCOPE FROM CATDEMO_CATALOG_DB.CORE.COLLECTION_RUNS ORDER BY STARTED_AT DESC LIMIT 3'
    )
  )
;

GRANT USAGE ON SCHEMA SEMANTIC TO ROLE CATDEMO_SALES_ANALYST;
GRANT USAGE ON SCHEMA SEMANTIC TO ROLE CATDEMO_DATA_STEWARD;
GRANT USAGE ON SCHEMA SEMANTIC TO ROLE CATDEMO_CATALOG_ADMIN;
GRANT SELECT ON SEMANTIC VIEW SEMANTIC.SV_DATA_CATALOG TO ROLE CATDEMO_SALES_ANALYST;
GRANT SELECT ON SEMANTIC VIEW SEMANTIC.SV_DATA_CATALOG TO ROLE CATDEMO_DATA_STEWARD;
GRANT SELECT ON SEMANTIC VIEW SEMANTIC.SV_DATA_CATALOG TO ROLE CATDEMO_CATALOG_ADMIN;

USE ROLE CATDEMO_SALES_ANALYST;
SELECT * FROM SEMANTIC_VIEW(
  CATDEMO_CATALOG_DB.SEMANTIC.SV_DATA_CATALOG
  DIMENSIONS assets.domain
  METRICS assets.asset_count
) ORDER BY 1;

CREATE OR REPLACE TABLE SEARCH.SEARCH_DOC_GENERAL AS
SELECT a.FULL_NAME,
       a.OBJECT_NAME,
       a.SCHEMA_NAME,
       a.DOMAIN,
       a.SENSITIVITY,
       a.ASSET_TYPE,
       a.OBJECT_NAME || ' — ' || COALESCE(a.DESCRIPTION, '') ||
       ' / ドメイン: ' || COALESCE(a.DOMAIN, '') ||
       ' / 機密度: ' || COALESCE(a.SENSITIVITY, '') ||
       ' / 信頼度: ' || COALESCE(a.TRUST_LEVEL, '') ||
       ' / カラム: ' || COALESCE(c.COLS_TEXT, '') ||
       ' / オーナー: ' || COALESCE(o.OWNERS_TEXT, '') AS SEARCH_TEXT
FROM CORE.ASSETS a
JOIN CORE.ASSET_VISIBILITY v
  ON v.FULL_NAME = a.FULL_NAME AND v.VISIBILITY_LEVEL <= 20   -- 事前フィルタ（存在隠蔽の徹底）
LEFT JOIN (
  SELECT FULL_NAME,
         LISTAGG(COLUMN_NAME || IFF(DESCRIPTION IS NOT NULL, '(' || DESCRIPTION || ')', ''), ', ')
           WITHIN GROUP (ORDER BY ORDINAL) AS COLS_TEXT
  FROM CORE.COLUMNS_META GROUP BY FULL_NAME
) c ON c.FULL_NAME = a.FULL_NAME
LEFT JOIN (
  SELECT FULL_NAME, LISTAGG(OWNER_TYPE || ':' || OWNER_NAME, ', ') AS OWNERS_TEXT
  FROM CORE.OWNERS GROUP BY FULL_NAME
) o ON o.FULL_NAME = a.FULL_NAME;

CREATE OR REPLACE CORTEX SEARCH SERVICE SEARCH.CATALOG_SEARCH_GENERAL
  ON SEARCH_TEXT
  ATTRIBUTES FULL_NAME, SCHEMA_NAME, DOMAIN, SENSITIVITY, ASSET_TYPE
  WAREHOUSE = CATDEMO_WH
  TARGET_LAG = '24 hours'
  EMBEDDING_MODEL = 'snowflake-arctic-embed-l-v2.0'
AS (
  SELECT SEARCH_TEXT, FULL_NAME, OBJECT_NAME, SCHEMA_NAME, DOMAIN, SENSITIVITY, ASSET_TYPE
  FROM SEARCH.SEARCH_DOC_GENERAL
);

-- ソーステーブルの変更追跡を明示的に有効化（収集時の DELETE + INSERT を索引に反映させる）
ALTER TABLE SEARCH.SEARCH_DOC_GENERAL SET CHANGE_TRACKING = TRUE;

GRANT USAGE ON SCHEMA SEARCH TO ROLE CATDEMO_SALES_ANALYST;
GRANT USAGE ON CORTEX SEARCH SERVICE SEARCH.CATALOG_SEARCH_GENERAL TO ROLE CATDEMO_SALES_ANALYST;
-- STEWARD / ADMIN にも同様に付与

CREATE OR REPLACE AGENT CATDEMO_CATALOG_DB.AGENT.DATA_CATALOG_AGENT
  COMMENT = 'AIデータカタログ: 日本語でアセット検索・オーナー・アクセス権・リネージに即答する'
  PROFILE = '{"display_name": "データカタログ アシスタント"}'
  FROM SPECIFICATION
  $$
models:
  orchestration: auto
instructions:
  response: |
    あなたは社内データカタログのアシスタントです。以下の原則で日本語で回答してください。
    1. 回答は必ずカタログ（CATDEMO_CATALOG_DB）の検索結果に基づき、出典がカタログである旨がわかるように答える。
    2. 問い合わせ結果が0件のときは「該当するデータは見つかりませんでした」とだけ答える。
       存在の有無・権限の有無・別の可能性には一切言及しない（「権限がありません」は禁止）。
    3. 鮮度や利用状況を答えるときは、取得元（USAGE_SOURCE）とカタログ収集日時（LAST_COLLECTED_AT）を添える。
       USAGE_SOURCE が SEEDED の統計は「デモ用の参考値」と明示する。
    4. アクセス権の回答は2層で答える: (a) ロール継承上の権限経路（VIA_PATH）
       (b) 実データで見える行・列は Row Access Policy / Masking Policy の実行時評価に従う旨の注記。
    5. 可視かつ申請可能なアセットへのアクセス方法を聞かれたら、申請窓口・承認者・必要な記載事項・
       標準リードタイムを ACCESS_REQUEST_GUIDE から案内する。
    6. LIFECYCLE_STATUS が DEPRECATED のアセットは、置き換え先（REPLACED_BY）と廃止予定日を必ず案内する。
    7. サンプル値について、PII・機密カラムは「ポリシーによりサンプル値は収集・提供されません」と答える。
    8. 推測で補完しない。カタログにない情報は「カタログに情報がありません」と答える。
  orchestration: |
    ツールの使い分け:
    - オーナー・アクセス権・リネージ・件数・一覧・鮮度・用語定義など構造化された質問は catalog_analyst を使う。
    - 「〜みたいなデータ」「〜に関するテーブル」などキーワード・あいまい表現の探索は catalog_search で候補を
      見つけ、必要に応じて catalog_analyst で詳細を補う。
    - 両ツールの結果が0件なら、それ以上の推測はせず「該当するデータは見つかりませんでした」と回答する。
  sample_questions:
    - question: "どんなデータがありますか？ドメイン別に教えて"
    - question: "個人情報を含むテーブルはどこにありますか？"
    - question: "CUSTOMERS テーブルのオーナーは誰ですか？"
    - question: "DAILY_SALES は何から作られていますか？"
tools:
  - tool_spec:
      type: "cortex_analyst_text_to_sql"
      name: "catalog_analyst"
      description: "データカタログへの構造化問い合わせ。アセット一覧・オーナー・アクセス権・リネージ・品質・用語集・管理オブジェクト・収集履歴に答える"
  - tool_spec:
      type: "cortex_search"
      name: "catalog_search"
      description: "データカタログのあいまい検索。キーワードからアセット候補を探す"
tool_resources:
  catalog_analyst:
    semantic_view: "CATDEMO_CATALOG_DB.SEMANTIC.SV_DATA_CATALOG"
  catalog_search:
    name: "CATDEMO_CATALOG_DB.SEARCH.CATALOG_SEARCH_GENERAL"
    id_column: "FULL_NAME"
    title_column: "OBJECT_NAME"
    max_results: "10"
  $$;

CREATE SNOWFLAKE INTELLIGENCE IF NOT EXISTS SNOWFLAKE_INTELLIGENCE_OBJECT_DEFAULT;

GRANT USAGE ON SCHEMA AGENT TO ROLE CATDEMO_SALES_ANALYST;
GRANT USAGE ON AGENT AGENT.DATA_CATALOG_AGENT TO ROLE CATDEMO_SALES_ANALYST;
-- STEWARD / ADMIN にも同様に付与

ALTER SNOWFLAKE INTELLIGENCE SNOWFLAKE_INTELLIGENCE_OBJECT_DEFAULT
  ADD AGENT CATDEMO_CATALOG_DB.AGENT.DATA_CATALOG_AGENT;