sources:
  spanner-source:
    kind: "spanner"
    project: ${SPANNER_PROJECT}
    instance: ${SPANNER_INSTANCE}
    database: ${SPANNER_DATABASE}
    dialect: "postgresql"

tools:
  execute_sql:
    kind: spanner-execute-sql
    source: spanner-source
    description: Use this tool to execute DML SQL. Please use the PostgreSQL interface for Spanner.

  execute_sql_dql:
    kind: spanner-execute-sql
    source: spanner-source
    description: Use this tool to execute DQL SQL. Please use the PostgreSQL interface for Spanner.
    readOnly: true

  list_tables:
    kind: spanner-sql
    source: spanner-source
    readOnly: true
    description: "Lists detailed schema information (object type, columns, constraints, indexes, triggers, owner, comment) as JSON for user-created tables (ordinary or partitioned). Filters by a comma-separated list of names. If names are omitted, lists all tables in user schemas."
    statement: |
      WITH table_info_cte AS (
          SELECT
            T.TABLE_SCHEMA,
            T.TABLE_NAME,
            T.TABLE_TYPE,
            T.PARENT_TABLE_NAME,
            T.ON_DELETE_ACTION
          FROM INFORMATION_SCHEMA.TABLES AS T
          WHERE
            T.TABLE_SCHEMA = 'public'
            AND T.TABLE_TYPE = 'BASE TABLE'
            AND (
            NULLIF(TRIM($1), '') IS NULL OR
            T.TABLE_NAME IN (
              SELECT table_name
              FROM UNNEST(regexp_split_to_array($1, '\s*,\s*')) AS table_name)
            )
        ),

        columns_info_cte AS (
          SELECT
            C.TABLE_SCHEMA,
            C.TABLE_NAME,
            ARRAY_AGG(
              CONCAT(
                '{',
                '"column_name":"', COALESCE(REPLACE(C.COLUMN_NAME, '"', '\"'), ''), '",',
                '"data_type":"', COALESCE(REPLACE(C.SPANNER_TYPE, '"', '\"'), ''), '",',
                '"ordinal_position":', C.ORDINAL_POSITION::TEXT, ',',
                '"is_not_nullable":', CASE WHEN C.IS_NULLABLE = 'NO' THEN 'true' ELSE 'false' END, ',',
                '"column_default":', CASE WHEN C.COLUMN_DEFAULT IS NULL THEN 'null' ELSE CONCAT('"', REPLACE(C.COLUMN_DEFAULT::text, '"', '\"'), '"') END,
                '}'
              ) ORDER BY C.ORDINAL_POSITION
            ) AS columns_json_array_elements
          FROM INFORMATION_SCHEMA.COLUMNS AS C
          WHERE C.TABLE_SCHEMA = 'public'
            AND EXISTS (SELECT 1 FROM table_info_cte TI WHERE C.TABLE_SCHEMA = TI.TABLE_SCHEMA AND C.TABLE_NAME = TI.TABLE_NAME)
          GROUP BY C.TABLE_SCHEMA, C.TABLE_NAME
        ),

        constraint_columns_agg_cte AS (
          SELECT
            CONSTRAINT_CATALOG,
            CONSTRAINT_SCHEMA,
            CONSTRAINT_NAME,
            ARRAY_AGG('"' || REPLACE(COLUMN_NAME, '"', '\"') || '"' ORDER BY ORDINAL_POSITION) AS column_names_json_list
          FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
          WHERE CONSTRAINT_SCHEMA = 'public'
          GROUP BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
        ),

        constraints_info_cte AS (
          SELECT
            TC.TABLE_SCHEMA,
            TC.TABLE_NAME,
            ARRAY_AGG(
              CONCAT(
                '{',
                '"constraint_name":"', COALESCE(REPLACE(TC.CONSTRAINT_NAME, '"', '\"'), ''), '",',
                '"constraint_type":"', COALESCE(REPLACE(TC.CONSTRAINT_TYPE, '"', '\"'), ''), '",',
                '"constraint_definition":',
                  CASE TC.CONSTRAINT_TYPE
                    WHEN 'CHECK' THEN CASE WHEN CC.CHECK_CLAUSE IS NULL THEN 'null' ELSE CONCAT('"', REPLACE(CC.CHECK_CLAUSE, '"', '\"'), '"') END
                    WHEN 'PRIMARY KEY' THEN CONCAT('"', 'PRIMARY KEY (', array_to_string(COALESCE(KeyCols.column_names_json_list, ARRAY[]::text[]), ', '), ')', '"')
                    WHEN 'UNIQUE' THEN CONCAT('"', 'UNIQUE (', array_to_string(COALESCE(KeyCols.column_names_json_list, ARRAY[]::text[]), ', '), ')', '"')
                    WHEN 'FOREIGN KEY' THEN CONCAT('"', 'FOREIGN KEY (', array_to_string(COALESCE(KeyCols.column_names_json_list, ARRAY[]::text[]), ', '), ') REFERENCES ',
                                            COALESCE(REPLACE(RefKeyTable.TABLE_NAME, '"', '\"'), ''),
                                            ' (', array_to_string(COALESCE(RefKeyCols.column_names_json_list, ARRAY[]::text[]), ', '), ')', '"')
                    ELSE 'null'
                  END, ',',
                '"constraint_columns":[', array_to_string(COALESCE(KeyCols.column_names_json_list, ARRAY[]::text[]), ','), '],',
                '"foreign_key_referenced_table":', CASE WHEN RefKeyTable.TABLE_NAME IS NULL THEN 'null' ELSE CONCAT('"', REPLACE(RefKeyTable.TABLE_NAME, '"', '\"'), '"') END, ',',
                '"foreign_key_referenced_columns":[', array_to_string(COALESCE(RefKeyCols.column_names_json_list, ARRAY[]::text[]), ','), ']',
                '}'
              ) ORDER BY TC.CONSTRAINT_NAME
            ) AS constraints_json_array_elements
          FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
          LEFT JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS CC
            ON TC.CONSTRAINT_CATALOG = CC.CONSTRAINT_CATALOG AND TC.CONSTRAINT_SCHEMA = CC.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
          LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
            ON TC.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND TC.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
          LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS RefConstraint
            ON RC.UNIQUE_CONSTRAINT_CATALOG = RefConstraint.CONSTRAINT_CATALOG AND RC.UNIQUE_CONSTRAINT_SCHEMA = RefConstraint.CONSTRAINT_SCHEMA AND RC.UNIQUE_CONSTRAINT_NAME = RefConstraint.CONSTRAINT_NAME
          LEFT JOIN INFORMATION_SCHEMA.TABLES AS RefKeyTable
            ON RefConstraint.TABLE_CATALOG = RefKeyTable.TABLE_CATALOG AND RefConstraint.TABLE_SCHEMA = RefKeyTable.TABLE_SCHEMA AND RefConstraint.TABLE_NAME = RefKeyTable.TABLE_NAME
          LEFT JOIN constraint_columns_agg_cte AS KeyCols
            ON TC.CONSTRAINT_CATALOG = KeyCols.CONSTRAINT_CATALOG AND TC.CONSTRAINT_SCHEMA = KeyCols.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = KeyCols.CONSTRAINT_NAME
          LEFT JOIN constraint_columns_agg_cte AS RefKeyCols
            ON RC.UNIQUE_CONSTRAINT_CATALOG = RefKeyCols.CONSTRAINT_CATALOG AND RC.UNIQUE_CONSTRAINT_SCHEMA = RefKeyCols.CONSTRAINT_SCHEMA AND RC.UNIQUE_CONSTRAINT_NAME = RefKeyCols.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
          WHERE TC.TABLE_SCHEMA = 'public'
            AND EXISTS (SELECT 1 FROM table_info_cte TI WHERE TC.TABLE_SCHEMA = TI.TABLE_SCHEMA AND TC.TABLE_NAME = TI.TABLE_NAME)
          GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME
        ),

        index_key_columns_agg_cte AS (
          SELECT
            TABLE_CATALOG,
            TABLE_SCHEMA,
            TABLE_NAME,
            INDEX_NAME,
            ARRAY_AGG(
              CONCAT(
                '{"column_name":"', COALESCE(REPLACE(COLUMN_NAME, '"', '\"'), ''), '",',
                '"ordering":"', COALESCE(REPLACE(COLUMN_ORDERING, '"', '\"'), ''), '"}'
              ) ORDER BY ORDINAL_POSITION
            ) AS key_column_json_details
          FROM INFORMATION_SCHEMA.INDEX_COLUMNS
          WHERE ORDINAL_POSITION IS NOT NULL
            AND TABLE_SCHEMA = 'public'
          GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
        ),

        index_storing_columns_agg_cte AS (
          SELECT
            TABLE_CATALOG,
            TABLE_SCHEMA,
            TABLE_NAME,
            INDEX_NAME,
            ARRAY_AGG(CONCAT('"', REPLACE(COLUMN_NAME, '"', '\"'), '"') ORDER BY COLUMN_NAME) AS storing_column_json_names
          FROM INFORMATION_SCHEMA.INDEX_COLUMNS
          WHERE ORDINAL_POSITION IS NULL
            AND TABLE_SCHEMA = 'public'
          GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
        ),

        indexes_info_cte AS (
          SELECT
            I.TABLE_SCHEMA,
            I.TABLE_NAME,
            ARRAY_AGG(
              CONCAT(
                '{',
                '"index_name":"', COALESCE(REPLACE(I.INDEX_NAME, '"', '\"'), ''), '",',
                '"index_type":"', COALESCE(REPLACE(I.INDEX_TYPE, '"', '\"'), ''), '",',
                '"is_unique":', CASE WHEN I.IS_UNIQUE = 'YES' THEN 'true' ELSE 'false' END, ',',
                '"is_null_filtered":', CASE WHEN I.IS_NULL_FILTERED = 'YES' THEN 'true' ELSE 'false' END, ',',
                '"interleaved_in_table":', CASE WHEN I.PARENT_TABLE_NAME IS NULL OR I.PARENT_TABLE_NAME = '' THEN 'null' ELSE CONCAT('"', REPLACE(I.PARENT_TABLE_NAME, '"', '\"'), '"') END, ',',
                '"index_key_columns":[', COALESCE(array_to_string(KeyIndexCols.key_column_json_details, ','), ''), '],',
                '"storing_columns":[', COALESCE(array_to_string(StoringIndexCols.storing_column_json_names, ','), ''), ']',
                '}'
              ) ORDER BY I.INDEX_NAME
            ) AS indexes_json_array_elements
          FROM INFORMATION_SCHEMA.INDEXES AS I
          LEFT JOIN index_key_columns_agg_cte AS KeyIndexCols
            ON I.TABLE_CATALOG = KeyIndexCols.TABLE_CATALOG AND I.TABLE_SCHEMA = KeyIndexCols.TABLE_SCHEMA AND I.TABLE_NAME = KeyIndexCols.TABLE_NAME AND I.INDEX_NAME = KeyIndexCols.INDEX_NAME
          LEFT JOIN index_storing_columns_agg_cte AS StoringIndexCols
            ON I.TABLE_CATALOG = StoringIndexCols.TABLE_CATALOG AND I.TABLE_SCHEMA = StoringIndexCols.TABLE_SCHEMA AND I.TABLE_NAME = StoringIndexCols.TABLE_NAME AND I.INDEX_NAME = StoringIndexCols.INDEX_NAME
          AND I.INDEX_TYPE IN ('LOCAL', 'GLOBAL')
          WHERE I.TABLE_SCHEMA = 'public'
            AND EXISTS (SELECT 1 FROM table_info_cte TI WHERE I.TABLE_SCHEMA = TI.TABLE_SCHEMA AND I.TABLE_NAME = TI.TABLE_NAME)
          GROUP BY I.TABLE_SCHEMA, I.TABLE_NAME
        )

      SELECT
        TI.TABLE_SCHEMA AS schema_name,
        TI.TABLE_NAME AS object_name,
        CONCAT(
          '{',
          '"schema_name":"', COALESCE(REPLACE(TI.TABLE_SCHEMA, '"', '\"'), ''), '",',
          '"object_name":"', COALESCE(REPLACE(TI.TABLE_NAME, '"', '\"'), ''), '",',
          '"object_type":"', COALESCE(REPLACE(TI.TABLE_TYPE, '"', '\"'), ''), '",',
          '"columns":[', COALESCE(array_to_string(CI.columns_json_array_elements, ','), ''), '],',
          '"constraints":[', COALESCE(array_to_string(CONSI.constraints_json_array_elements, ','), ''), '],',
          '"indexes":[', COALESCE(array_to_string(II.indexes_json_array_elements, ','), ''), ']',
          '}'
        ) AS object_details
      FROM table_info_cte AS TI
      LEFT JOIN columns_info_cte AS CI
        ON TI.TABLE_SCHEMA = CI.TABLE_SCHEMA AND TI.TABLE_NAME = CI.TABLE_NAME
      LEFT JOIN constraints_info_cte AS CONSI
        ON TI.TABLE_SCHEMA = CONSI.TABLE_SCHEMA AND TI.TABLE_NAME = CONSI.TABLE_NAME
      LEFT JOIN indexes_info_cte AS II
        ON TI.TABLE_SCHEMA = II.TABLE_SCHEMA AND TI.TABLE_NAME = II.TABLE_NAME
      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 user-accessible schemas will be listed."
toolsets:
  spanner-postgres-database-tools:
    - execute_sql
    - execute_sql_dql
    - list_tables
