{% materialization external, adapter='snowflake' %}
  {%- set relation = this -%}
  {%- set sql = model['compiled_code'] -%}
  {%- set pre_hooks = pre_hooks -%}
  {%- set post_hooks = post_hooks -%}
  

  -- Required configurations
  {%- set location = config.require('location') -%}
  {%- set file_format = config.require('file_format') -%}
  
  -- Optional configurations
  {%- set pattern = config.get('pattern') -%}
  {%- set stage = config.get('stage') -%}

  -- Extract column definitions from the SELECT statement
  {%- set column_definitions = [] -%}
  {%- set column_partitions = [] -%}
  {%- set select_lines = sql.split('\n') -%}

  {%- for line in select_lines -%}
    {%- set trimmed_line = line.strip() -%}




    {%- if trimmed_line.upper().startswith('SELECT') -%}
      {%- continue -%}
    {%- elif trimmed_line.upper().startswith('FROM') -%}
      {%- break -%}
    {%- elif  trimmed_line and not trimmed_line.startswith('--') -%}
      {%- if trimmed_line.endswith(',') -%}
        {%- set column_def = trimmed_line[:-1].strip() -%}
      {%- else -%}
        {%- set column_def = trimmed_line -%}
      {%- endif -%}
      

      {%- if column_def -%}
        {%- set column_parts = column_def.split(' AS ') -%}
        {%- if column_parts|length == 2 -%}
          {%- set column_name = column_parts[1].strip() -%}
          {%- set column_expression = column_parts[0].strip() -%}
        {%- else -%}
          {%- set column_name = column_def -%}
          {%- set column_expression = column_def -%}
        {%- endif -%}
        
        -- Determine column type based on expression
        {%- if '::VARCHAR' in column_expression.upper() -%}
          {%- set column_type = 'VARCHAR' -%}
        {%- elif '::VARIANT' in column_expression.upper() -%}
          {%- set column_type = 'VARIANT' -%}
        {%- elif '::FLOAT' in column_expression.upper() or '::NUMBER' in column_expression.upper() -%}
          {%- set column_type = 'FLOAT' -%}
        {%- elif '::INT' in column_expression.upper() or '::INTEGER' in column_expression.upper() -%}
          {%- set column_type = 'INTEGER' -%}
        {%- elif 'TRY_TO_DATE' in column_expression.upper() -%}
          {%- set column_type = 'DATE' -%}
        {%- elif 'TRY_TO_NUMBER' in column_expression.upper() -%}
          {%- set column_type = 'NUMBER' -%}
        {%- else -%}
          {%- set column_type = 'VARIANT' -%}
        {%- endif -%}

        {%- if 'METADATA$FILENAME' in column_expression.upper() -%}
          {%- do column_partitions.append(column_name) -%}
        {%- endif -%}

        {%- do column_definitions.append(column_name + ' ' + column_type + ' AS (' + column_expression + ')') -%}
      {%- endif -%}
    {%- endif -%}
  {%- endfor -%}

  -- Build the CREATE OR REPLACE EXTERNAL TABLE statement
  {%- set create_sql -%}
    CREATE OR REPLACE EXTERNAL TABLE {{ relation }}
    (
      {{ column_definitions | join(',\n      ') }},
      FILENAME  STRING AS (METADATA$FILENAME)
    )
    PARTITION BY ({{ column_partitions | join(', ') }})
    LOCATION = {{ location }}
    FILE_FORMAT = {{ file_format }}
    {%- if pattern %}
    PATTERN = '{{ pattern }}'
    {%- endif %}
    {%- if stage %}
    STAGE = {{ stage }}
    {%- endif %}
    AUTO_REFRESH = FALSE
    REFRESH_ON_CREATE = TRUE
  {%- endset -%}

  -- Run pre-hooks
  {{ run_hooks(pre_hooks, inside_transaction=False) }}
  {{ run_hooks(pre_hooks, inside_transaction=True) }}

  -- Execute the CREATE OR REPLACE EXTERNAL TABLE statement
  {% call statement('main') %}
    {{ create_sql }}
  {% endcall %}

  -- Run post-hooks
  {{ run_hooks(post_hooks, inside_transaction=True) }}
  {{ run_hooks(post_hooks, inside_transaction=False) }}

  -- Persist model documentation if it exists
  {% do persist_docs(relation, model) %}

  {{ return({'relations': [relation]}) }}

{% endmaterialization %}
