package persistence

import (
	"context"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	domainrls "github.com/nexuscrm/backend/internal/domain/rls"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

func TestIsValidFieldName(t *testing.T) {
	tests := []struct {
		name     string
		input    string
		expected bool
	}{
		{"Simple alphanumeric", "fieldName", true},
		{"With underscores", "field_name_123", true},
		{"Empty string", "", false},              // Fixed: Now returns false
		{"Qualified name", "table.column", true}, // Fixed: Now returns true
		{"SQL Injection", "field; DROP TABLE", false},
		{"With spaces", "field name", false},
		{"With backticks", "`field`", false},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := isValidFieldName(tt.input)
			assert.Equal(t, tt.expected, got, "isValidFieldName(%q) should be %v", tt.input, tt.expected)
		})
	}
}

type staticViewResolver string

func (r staticViewResolver) ResolveTableName(_ context.Context, _ *models.ObjectMetadata) string {
	return string(r)
}

func TestQueryRepository_Search_UsesResolvedReadTableForSearchConditions(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
	require.NoError(t, err)
	defer db.Close()

	repo := NewQueryRepository(db)
	repo.SetViewResolver(staticViewResolver("v_account"))

	schema := &models.ObjectMetadata{
		APIName: "Account",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: constants.FieldName, Type: constants.FieldTypeString},
			{APIName: "Phone", Type: constants.FieldTypeString},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean},
		},
	}

	expectedSQL := "SELECT `v_account`.`Id`, `v_account`.`Name` FROM `v_account` WHERE `v_account`.IsDeleted = 0 AND (LOWER(`v_account`.`Name`) LIKE ? OR LOWER(`v_account`.`Phone`) LIKE ?) LIMIT 500"
	mock.ExpectQuery(expectedSQL).
		WithArgs("%test%", "%test%").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).AddRow("001-test", "Test Account"))

	results, err := repo.Search(context.Background(), schema, "test", []string{"Name", "Phone"}, []string{"Id", "Name"}, 500, 0, "", "", "", &domainrls.Result{Skip: true})
	require.NoError(t, err)
	require.Len(t, results, 1)
	assert.Equal(t, "001-test", results[0]["Id"])
	assert.Equal(t, "Test Account", results[0]["Name"])
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestQueryRepository_Search_UsesResolvedReadTableForFormulaQueryTerm(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
	require.NoError(t, err)
	defer db.Close()

	repo := NewQueryRepository(db)
	repo.SetViewResolver(staticViewResolver("v_account"))

	schema := &models.ObjectMetadata{
		APIName: "Account",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: constants.FieldName, Type: constants.FieldTypeString},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean},
		},
	}

	expectedSQL := "SELECT `v_account`.`Id`, `v_account`.`Name` FROM `v_account` WHERE `v_account`.IsDeleted = 0 AND `v_account`.`Name` = ? LIMIT 500"
	mock.ExpectQuery(expectedSQL).
		WithArgs("Acme").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name"}).AddRow("001-acme", "Acme"))

	results, err := repo.Search(context.Background(), schema, "Name = Acme", []string{"Name"}, []string{"Id", "Name"}, 500, 0, "", "", "", &domainrls.Result{Skip: true})
	require.NoError(t, err)
	require.Len(t, results, 1)
	assert.Equal(t, "001-acme", results[0]["Id"])
	require.NoError(t, mock.ExpectationsWereMet())
}