package duckdb

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"os"
	"regexp"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/jmoiron/sqlx"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

// Helper function to create a mock fetcher
func newMockFetcher(t *testing.T, vendor, region string) (*QueryDuckDBFetcher, sqlmock.Sqlmock) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)

	dbx := sqlx.NewDb(db, "duckdb")

	fetcher := &QueryDuckDBFetcher{
		vendor:         vendor,
		region:         region,
		db:             db,
		dbx:            dbx,
		attachedTables: make(map[string]string),
	}
	return fetcher, mock
}

func TestNewQueryDuckDBFetcher_InitError_OpenDB(t *testing.T) {
	// Temporarily replace sql.Open to simulate an error
	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return nil, errors.New("failed to open database")
	}
	defer func() { sqlOpen = originalSQLOpen }()

	ctx := context.Background()
	fetcher, err := NewQueryDuckDBFetcher(ctx, "aws", "us-east-1")
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to open duckdb")
	assert.Nil(t, fetcher)
}

func TestSlowQueryDuckDBFetcher_InitError_SetMemoryLimit(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec("INSTALL httpfs; LOAD httpfs;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL azure; LOAD azure;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL cache_httpfs FROM community; LOAD cache_httpfs; SET cache_httpfs_cache_directory='cache/duckdb';SET cache_httpfs_type='on_disk'; ").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL delta; LOAD delta;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("SET enable_object_cache=true; SET enable_http_metadata_cache=true; SET enable_external_file_cache=true; SET parquet_metadata_cache=true;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("SET memory_limit='2048MB';").WillReturnError(errors.New("memory limit error"))

	err := fetcher.Init(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to set DuckDB memory_limit")
}

func TestSlowQueryDuckDBFetcher_InitError_InstallLoadHttpfs(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec("SET memory_limit='2048MB';").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL httpfs; LOAD httpfs;").WillReturnError(errors.New("httpfs error"))

	err := fetcher.Init(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to install or load httpfs")
}

func TestSlowQueryDuckDBFetcher_InitError_InstallLoadAzure(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "azure", "eastus")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec("SET memory_limit='2048MB';").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL httpfs; LOAD httpfs;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL azure; LOAD azure;").WillReturnError(errors.New("azure extension error"))

	err := fetcher.Init(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to install or load azure extension")
}

func TestSlowQueryDuckDBFetcher_InitError_InstallLoadCacheHttpfs(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec("SET memory_limit='2048MB';").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL httpfs; LOAD httpfs;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL azure; LOAD azure;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL cache_httpfs FROM community; LOAD cache_httpfs; SET cache_httpfs_cache_directory='cache/duckdb';SET cache_httpfs_type='on_disk'; ").WillReturnError(errors.New("cache httpfs error"))

	err := fetcher.Init(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to install or load cache_httpfs")
}

func TestSlowQueryDuckDBFetcher_InitError_InstallLoadDelta(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec("SET memory_limit='2048MB';").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL httpfs; LOAD httpfs;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL azure; LOAD azure;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL cache_httpfs FROM community; LOAD cache_httpfs; SET cache_httpfs_cache_directory='cache/duckdb';SET cache_httpfs_type='on_disk'; ").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL delta; LOAD delta;").WillReturnError(errors.New("delta error"))

	err := fetcher.Init(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to install or load delta")
}

func TestSlowQueryDuckDBFetcher_InitError_SetCacheConfig(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec("SET memory_limit='2048MB';").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL httpfs; LOAD httpfs;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL azure; LOAD azure;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL cache_httpfs FROM community; LOAD cache_httpfs; SET cache_httpfs_cache_directory='cache/duckdb';SET cache_httpfs_type='on_disk'; ").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL delta; LOAD delta;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("SET enable_object_cache=true; SET enable_http_metadata_cache=true; SET enable_external_file_cache=true; SET parquet_metadata_cache=true;").WillReturnError(errors.New("cache config error"))

	err := fetcher.Init(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to set cache configuration")
}

func TestSlowQueryDuckDBFetcher_InitError_AWSRegionFail(t *testing.T) {
	os.Setenv("AWS_ACCESS_KEY_ID", "test_access_key")
	os.Setenv("AWS_SECRET_ACCESS_KEY", "test_secret_key")
	defer func() {
		os.Unsetenv("AWS_ACCESS_KEY_ID")
		os.Unsetenv("AWS_SECRET_ACCESS_KEY")
	}()

	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec("SET memory_limit='2048MB';").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL httpfs; LOAD httpfs;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL azure; LOAD azure;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL cache_httpfs FROM community; LOAD cache_httpfs; SET cache_httpfs_cache_directory='cache/duckdb';SET cache_httpfs_type='on_disk'; ").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL delta; LOAD delta;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("SET enable_object_cache=true; SET enable_http_metadata_cache=true; SET enable_external_file_cache=true; SET parquet_metadata_cache=true;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("SET s3_region='us-east-1';").WillReturnError(errors.New("s3 region error"))

	err := fetcher.Init(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to set s3_region")
}

func TestSlowQueryDuckDBFetcher_InitError_AWSSecretFail(t *testing.T) {
	os.Setenv("AWS_ACCESS_KEY_ID", "test_access_key")
	os.Setenv("AWS_SECRET_ACCESS_KEY", "test_secret_key")
	defer func() {
		os.Unsetenv("AWS_ACCESS_KEY_ID")
		os.Unsetenv("AWS_SECRET_ACCESS_KEY")
	}()

	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec("SET memory_limit='2048MB';").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL httpfs; LOAD httpfs;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL azure; LOAD azure;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL cache_httpfs FROM community; LOAD cache_httpfs; SET cache_httpfs_cache_directory='cache/duckdb';SET cache_httpfs_type='on_disk'; ").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL delta; LOAD delta;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("SET enable_object_cache=true; SET enable_http_metadata_cache=true; SET enable_external_file_cache=true; SET parquet_metadata_cache=true;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("SET s3_region='us-east-1';").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(fmt.Sprintf(awsSecret, "us-east-1", "us-east-1")).WillReturnError(errors.New("aws secret error"))

	err := fetcher.Init(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to set AWS secret")
}

func TestSlowQueryDuckDBFetcher_InitError_GCPSecretFail(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "gcp", "us-central1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec("SET memory_limit='2048MB';").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL httpfs; LOAD httpfs;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL azure; LOAD azure;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL cache_httpfs FROM community; LOAD cache_httpfs; SET cache_httpfs_cache_directory='cache/duckdb';SET cache_httpfs_type='on_disk'; ").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL delta; LOAD delta;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("SET enable_object_cache=true; SET enable_http_metadata_cache=true; SET enable_external_file_cache=true; SET parquet_metadata_cache=true;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(gcsSecret).WillReturnError(errors.New("gcp secret error"))

	err := fetcher.Init(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to set GCP secret")
}

func TestSlowQueryDuckDBFetcher_InitError_AzureSecretFail(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "azure", "eastus")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec("SET memory_limit='2048MB';").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL httpfs; LOAD httpfs;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL azure; LOAD azure;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL cache_httpfs FROM community; LOAD cache_httpfs; SET cache_httpfs_cache_directory='cache/duckdb';SET cache_httpfs_type='on_disk'; ").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("INSTALL delta; LOAD delta;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("SET enable_object_cache=true; SET enable_http_metadata_cache=true; SET enable_external_file_cache=true; SET parquet_metadata_cache=true;").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(azureSecret).WillReturnError(errors.New("azure secret error"))

	err := fetcher.Init(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to set Azure secret")
}

func TestSlowQueryDuckDBFetcher_SelectSlowlogInDLWithoutFilterPushdown_CredentialRefreshSuccess(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mockTableAlias := "mock_slowlogs"
	mockTablePath := "s3://my-bucket/path/to/slowlogs"

	fetcher.mu.Lock()
	fetcher.attachedTables[mockTableAlias] = mockTablePath
	fetcher.mu.Unlock()

	// Expect the initial query to fail with a credential error
	mock.ExpectQuery("SELECT id, time, digest, date, query FROM mock_slowlogs WHERE date = '2023-01-01'").
		WillReturnError(errors.New("The provided token has expired."))

	// Expect the credential refresh
	mock.ExpectExec(regexp.QuoteMeta(fmt.Sprintf(awsReplaceSecret, "us-east-1", "us-east-1"))).
		WillReturnResult(sqlmock.NewResult(0, 0))

	// Expect the query retry to succeed
	rows := sqlmock.NewRows([]string{"id", "time", "digest", "date", "query"}).
		AddRow(1, 10.5, "digest_abc", "2023-01-01", "SELECT * FROM users;")
	mock.ExpectQuery("SELECT id, time, digest, date, query FROM mock_slowlogs WHERE date = '2023-01-01'").
		WillReturnRows(rows)

	query := "SELECT id, time, digest, date, query FROM mock_slowlogs WHERE date = '2023-01-01';"
	var results []*SlowlogInDL
	err := fetcher.SelectSlowlogInDLWithoutFilterPushdown(context.Background(), &results, mockTableAlias, mockTablePath, query)
	require.NoError(t, err)
	assert.Len(t, results, 1)
	assert.Equal(t, int64(1), results[0].ID)
}

func TestSlowQueryDuckDBFetcher_SelectSlowlogInDLWithoutFilterPushdown_CredentialRefreshFail(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mockTableAlias := "mock_slowlogs"
	mockTablePath := "s3://my-bucket/path/to/slowlogs"

	fetcher.mu.Lock()
	fetcher.attachedTables[mockTableAlias] = mockTablePath
	fetcher.mu.Unlock()

	// Expect the initial query to fail with a credential error
	mock.ExpectQuery("SELECT id, time, digest, date, query FROM mock_slowlogs WHERE date = '2023-01-01'").
		WillReturnError(errors.New("AuthenticationFailed"))

	// Expect the credential refresh to fail
	mock.ExpectExec(fmt.Sprintf(awsReplaceSecret, "us-east-1", "us-east-1")).
		WillReturnError(errors.New("refresh failed"))

	query := "SELECT id, time, digest, date, query FROM mock_slowlogs WHERE date = '2023-01-01';"
	var results []*SlowlogInDL
	err := fetcher.SelectSlowlogInDLWithoutFilterPushdown(context.Background(), &results, mockTableAlias, mockTablePath, query)
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to refresh credential chain after query failed")
	assert.Nil(t, results)
}

func TestSlowQueryDuckDBFetcher_SelectSlowlogInDLWithoutFilterPushdown_CredentialRefreshRetryFail(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mockTableAlias := "mock_slowlogs"
	mockTablePath := "s3://my-bucket/path/to/slowlogs"

	fetcher.mu.Lock()
	fetcher.attachedTables[mockTableAlias] = mockTablePath
	fetcher.mu.Unlock()

	// Expect the initial query to fail with a credential error
	mock.ExpectQuery("SELECT id, time, digest, date, query FROM mock_slowlogs WHERE date = '2023-01-01'").
		WillReturnError(errors.New("Forbidden"))

	// Expect the credential refresh to succeed
	mock.ExpectExec(regexp.QuoteMeta(fmt.Sprintf(awsReplaceSecret, "us-east-1", "us-east-1"))).
		WillReturnResult(sqlmock.NewResult(0, 0))

	// Expect the query retry to fail
	mock.ExpectQuery("SELECT id, time, digest, date, query FROM mock_slowlogs WHERE date = '2023-01-01'").
		WillReturnError(errors.New("retry query failed"))

	query := "SELECT id, time, digest, date, query FROM mock_slowlogs WHERE date = '2023-01-01';"
	var results []*SlowlogInDL
	err := fetcher.SelectSlowlogInDLWithoutFilterPushdown(context.Background(), &results, mockTableAlias, mockTablePath, query)
	require.Error(t, err)
	assert.Contains(t, err.Error(), "Forbidden")
	assert.Nil(t, results)
}

func TestSlowQueryDuckDBFetcher_SelectSlowlogInDLWithoutFilterPushdown_AttachTableError(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	// Expect the attach table call to fail
	mock.ExpectExec("attach 's3://my-bucket/path/to/slowlogs' as mock_slowlogs\\(type delta, PUSHDOWN_FILTERS 'none'\\);").
		WillReturnError(errors.New("attach error"))

	query := "SELECT id, time, digest, date, query FROM mock_slowlogs WHERE date = '2023-01-01';"
	var results []*SlowlogInDL
	err := fetcher.SelectSlowlogInDLWithoutFilterPushdown(context.Background(), &results, "mock_slowlogs", "my-bucket/path/to/slowlogs", query)
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to attach table")
	assert.Nil(t, results)
}

func TestSlowQueryDuckDBFetcher_RefreshCredentialChain_UnsupportedVendor(t *testing.T) {
	fetcher, _ := newMockFetcher(t, "unsupported", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	err := fetcher.refreshCredentialChain(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "unsupported vendor for credential refresh: unsupported")
}

func TestSlowQueryDuckDBFetcher_RefreshCredentialChain_ExecError(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	originalSQLOpen := sqlOpen
	sqlOpen = func(driverName, dataSourceName string) (*sql.DB, error) {
		return fetcher.db, nil
	}
	defer func() { sqlOpen = originalSQLOpen }()

	mock.ExpectExec(regexp.QuoteMeta(fmt.Sprintf(awsReplaceSecret, "us-east-1", "us-east-1"))).
		WillReturnError(errors.New("exec refresh error"))

	err := fetcher.refreshCredentialChain(context.Background())
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to set secret")
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestSlowQueryDuckDBFetcher_AttachTable_NewTable(t *testing.T) {
	tests := []struct {
		name        string
		vendor      string
		tableAlias  string
		tablePath   string
		expectedSQL string
	}{
		{"AWS new table", "aws", "my_data", "s3://bucket/data", "attach 's3://bucket/data' as my_data\\(type delta, PUSHDOWN_FILTERS 'none'\\);"},
		{"GCP new table", "gcp", "gcp_data", "gs://bucket/data", "attach 'gs://bucket/data' as gcp_data\\(type delta, PUSHDOWN_FILTERS 'none'\\);"},
		{"Azure new table", "azure", "azure_data", "az://bucket/data", "attach 'az://bucket/data' as azure_data\\(type delta, PUSHDOWN_FILTERS 'none'\\);"},
		{"AWS new table without prefix", "aws", "my_data_no_prefix", "bucket/data", "attach 's3://bucket/data' as my_data_no_prefix\\(type delta, PUSHDOWN_FILTERS 'none'\\);"},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			fetcher, mock := newMockFetcher(t, tt.vendor, "us-east-1")
			defer fetcher.Close(context.Background())

			mock.ExpectExec(tt.expectedSQL).WillReturnResult(sqlmock.NewResult(0, 0))

			err := fetcher.attachTable(tt.tableAlias, tt.tablePath)
			require.NoError(t, err)
			assert.Equal(t, tt.tablePath, fetcher.attachedTables[tt.tableAlias])
			assert.NoError(t, mock.ExpectationsWereMet())
		})
	}
}

func TestSlowQueryDuckDBFetcher_AttachTable_AlreadyAttached_SamePath(t *testing.T) {
	fetcher, _ := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	tableAlias := "my_data"
	tablePath := "s3://bucket/data"

	fetcher.mu.Lock()
	fetcher.attachedTables[tableAlias] = tablePath
	fetcher.mu.Unlock()

	err := fetcher.attachTable(tableAlias, tablePath)
	require.NoError(t, err)
	assert.Equal(t, tablePath, fetcher.attachedTables[tableAlias])
	// No mock expectation as no DB call should happen
}

func TestSlowQueryDuckDBFetcher_AttachTable_AlreadyAttached_DifferentPath(t *testing.T) {
	fetcher, _ := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	tableAlias := "my_data"
	tablePath := "s3://bucket/data"
	differentPath := "s3://another-bucket/data"

	fetcher.mu.Lock()
	fetcher.attachedTables[tableAlias] = tablePath
	fetcher.mu.Unlock()

	err := fetcher.attachTable(tableAlias, differentPath)
	require.Error(t, err)
	assert.Contains(t, err.Error(), fmt.Sprintf("alias '%s' already attached to a different table '%s', cannot re-attach to '%s'", tableAlias, tablePath, differentPath))
}

func TestSlowQueryDuckDBFetcher_AttachTable_UnsupportedVendor(t *testing.T) {
	fetcher, _ := newMockFetcher(t, "unsupported", "us-east-1")
	defer fetcher.Close(context.Background())

	err := fetcher.attachTable("alias", "path")
	require.Error(t, err)
	assert.Contains(t, err.Error(), "unsupported vendor for table attachment: unsupported")
}

func TestSlowQueryDuckDBFetcher_AttachTable_ExecError_AlreadyAttached(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	tableAlias := "my_data"
	tablePath := "s3://bucket/data"
	expectedSQL := "attach 's3://bucket/data' as my_data\\(type delta, PUSHDOWN_FILTERS 'none'\\);"

	mock.ExpectExec(expectedSQL).WillReturnError(errors.New("Catalog Error: Schema with name my_data already exists!"))

	err := fetcher.attachTable(tableAlias, tablePath)
	require.NoError(t, err) // Should not return error if it's "Already attached"
	assert.Equal(t, tablePath, fetcher.attachedTables[tableAlias])
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestSlowQueryDuckDBFetcher_AttachTable_ExecError_Generic(t *testing.T) {
	fetcher, mock := newMockFetcher(t, "aws", "us-east-1")
	defer fetcher.Close(context.Background())

	tableAlias := "my_data"
	tablePath := "s3://bucket/data"
	expectedSQL := "attach 's3://bucket/data' as my_data\\(type delta, PUSHDOWN_FILTERS 'none'\\);"

	mock.ExpectExec(expectedSQL).WillReturnError(errors.New("generic attach error"))

	err := fetcher.attachTable(tableAlias, tablePath)
	require.Error(t, err)
	assert.Contains(t, err.Error(), "failed to attach table: generic attach error")
	assert.NoError(t, mock.ExpectationsWereMet())
}