{% macro disable_search_optimization() %}
  {%- set database = target.database -%}

  {%- if database | lower == "suno_prod" | lower  -%}
    {{ exceptions.raise_compiler_error("This macro must not be run on the SUNO_PROD database!") }}
  {%- endif -%}
  
  {%- set show_tables_sql -%}
    SHOW TABLES HISTORY IN DATABASE {{ database }};
  {%- endset -%}
  
  {%- call statement('show_tables', fetch_result=False) -%}
    {{ show_tables_sql }}
  {%- endcall -%}
  
  {%- set get_tables_sql -%}
    SELECT "schema_name", "name"
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    WHERE "search_optimization" = 'ON';
  {%- endset -%}
  
  {%- set tables_result = run_query(get_tables_sql) -%}
  
  {%- if tables_result -%}
    {%- for row in tables_result -%}
      {%- set schema_name = row[0] -%}
      {%- set table_name = row[1] -%}
      {%- set alter_sql -%}
        ALTER TABLE {{ database }}.{{ schema_name }}.{{ table_name }} DROP SEARCH OPTIMIZATION
      {%- endset -%}
      
      {%- call statement('disable_search_opt', fetch_result=False) -%}
        {{ alter_sql }}
      {%- endcall -%}
      
      {{ log("Disabled search optimization on " ~ database ~ "." ~ schema_name ~ "." ~ table_name, info=True) }}
    {%- endfor -%}
  {%- else -%}
    {{ log("No tables with search optimization enabled found in database " ~ database, info=True) }}
  {%- endif -%}
  
  {{ log("Completed disabling search optimization on all tables in database " ~ database, info=True) }}

{% endmacro %}

