# Copyright 2026 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

kind: source
name: snowflake-source
type: snowflake
account: ${SNOWFLAKE_ACCOUNT}
user: ${SNOWFLAKE_USER}
password: ${SNOWFLAKE_PASSWORD}
database: ${SNOWFLAKE_DATABASE}
schema: ${SNOWFLAKE_SCHEMA}
warehouse: ${SNOWFLAKE_WAREHOUSE}
role: ${SNOWFLAKE_ROLE}
---
kind: tool
name: execute_sql
type: snowflake-execute-sql
source: snowflake-source
description: Use this tool to execute SQL.
---
kind: tool
name: list_tables
type: snowflake-sql
source: snowflake-source
description: Lists detailed schema information (object type, columns, constraints, indexes, owner, comment) as JSON for user-created tables. Filters by a comma-separated list of names. If names are omitted, lists all tables in the specified database and schema.
statement: |
  WITH
  input_param AS (
       SELECT ? AS param -- Single bind variable here
  )
  ,
  all_tables_mode AS (
       SELECT COALESCE(TRIM(param), '') = '' AS is_all_tables
       FROM input_param
  ) --SELECT * FROM all_tables_mode;
  ,
  filtered_table_names AS (
       SELECT DISTINCT TRIM(LOWER(value)) AS table_name
       FROM input_param, all_tables_mode, TABLE(SPLIT_TO_TABLE(param, ','))
       WHERE NOT is_all_tables
  ) -- SELECT * FROM filtered_table_names;
  ,
  table_info AS (
       SELECT
           t.TABLE_CATALOG,
           t.TABLE_SCHEMA,
           t.TABLE_NAME,
           t.TABLE_TYPE,
           t.TABLE_OWNER,
           t.COMMENT
       FROM
           all_tables_mode
           CROSS JOIN ${SNOWFLAKE_DATABASE}.INFORMATION_SCHEMA.TABLES T
       WHERE
           t.TABLE_TYPE = 'BASE TABLE'
           AND t.TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA')
           AND t.TABLE_SCHEMA = '${SNOWFLAKE_SCHEMA}'
           AND is_all_tables OR LOWER(T.TABLE_NAME) IN (SELECT table_name FROM filtered_table_names)
   ) -- SELECT * FROM table_info;
   ,
   columns_info AS (
       SELECT
           c.TABLE_CATALOG AS database_name,
           c.TABLE_SCHEMA AS schema_name,
           c.TABLE_NAME AS table_name,
           c.COLUMN_NAME AS column_name,
           c.DATA_TYPE AS data_type,
           c.ORDINAL_POSITION AS column_ordinal_position,
           c.IS_NULLABLE AS is_nullable,
           c.COLUMN_DEFAULT AS column_default,
           c.COMMENT AS column_comment
       FROM
           ${SNOWFLAKE_DATABASE}.INFORMATION_SCHEMA.COLUMNS c
           INNER JOIN table_info USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
   )
   ,
   constraints_info AS (
       SELECT
           tc.TABLE_CATALOG AS database_name,
           tc.TABLE_SCHEMA AS schema_name,
           tc.TABLE_NAME AS table_name,
           tc.CONSTRAINT_NAME AS constraint_name,
           tc.CONSTRAINT_TYPE AS constraint_type
       FROM
           ${SNOWFLAKE_DATABASE}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
           INNER JOIN table_info USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
       GROUP BY
           tc.TABLE_CATALOG, tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE
   )
   SELECT
       ti.TABLE_SCHEMA AS schema_name,
       ti.TABLE_NAME AS object_name,
       OBJECT_CONSTRUCT(
           'schema_name', ti.TABLE_SCHEMA,
           'object_name', ti.TABLE_NAME,
           'object_type', ti.TABLE_TYPE,
           'owner', ti.TABLE_OWNER,
           'comment', ti.COMMENT,
           'columns', COALESCE(
               (SELECT ARRAY_AGG(
                   OBJECT_CONSTRUCT(
                       'column_name', ci.column_name,
                       'data_type', ci.data_type,
                       'ordinal_position', ci.column_ordinal_position,
                       'is_nullable', ci.is_nullable,
                       'column_default', ci.column_default,
                       'column_comment', ci.column_comment
                   )
               ) FROM columns_info ci WHERE ci.table_name = ti.TABLE_NAME AND ci.schema_name = ti.TABLE_SCHEMA),
               ARRAY_CONSTRUCT()
           ),
           'constraints', COALESCE(
               (SELECT ARRAY_AGG(
                   OBJECT_CONSTRUCT(
                       'constraint_name', cons.constraint_name,
                       'constraint_type', cons.constraint_type
                   )
               ) FROM constraints_info cons WHERE cons.table_name = ti.TABLE_NAME AND cons.schema_name = ti.TABLE_SCHEMA),
               ARRAY_CONSTRUCT()
           )
       ) AS object_details
   FROM table_info ti
   ORDER BY ti.TABLE_SCHEMA, ti.TABLE_NAME;
parameters:
- name: table_names
  type: string
  description: "Optional: A comma-separated list of table names. If empty, details for all tables in the specified database and schema will be listed."
---
kind: toolset
name: snowflake_tools
tools:
- execute_sql
- list_tables