package query

import (
	"fmt"
	"strings"
	"testing"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestBuilder_Select_Aliasing(t *testing.T) {
	// Standard Schema with PascalCase
	schema := &models.ObjectMetadata{
		APIName: "TestObj",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, PhysicalName: constants.FieldID, Type: "ID"},
			{APIName: constants.FieldName, PhysicalName: constants.FieldName, Type: "string"},
			{APIName: "AliasedField", PhysicalName: "aliased_field_db", Type: "string"}, // Test aliasing logic
		},
	}

	// 1. Test Standard ID (No Aliasing needed)
	b := From("TestObj").WithMetadata(schema)
	b.Select([]string{constants.FieldID})

	q := b.Build()

	// Expectation: SELECT `TestObj`.`Id` FROM ... (No AS `Id`)
	if strings.Contains(q.SQL, "AS `Id`") {
		t.Errorf("FAIL: Expected NO aliasing for 'Id' when physical name matches.\nGot: %s", q.SQL)
	}
	if !strings.Contains(q.SQL, fmt.Sprintf("`TestObj`.`%s`", constants.FieldID)) {
		t.Errorf("FAIL: Expected standard 'Id' selection.\nGot: %s", q.SQL)
	} else {
		t.Logf("PASS: SQL uses standard Id: %s", q.SQL)
	}

	// 2. Test Aliasing (Physical Name Mismatch)
	b2 := From("TestObj").WithMetadata(schema)
	b2.Select([]string{"AliasedField"})
	q2 := b2.Build()

	// Expectation: SELECT `TestObj`.`aliased_field_db` AS `AliasedField` ...
	expectedAlias := "AS `AliasedField`"
	if !strings.Contains(q2.SQL, expectedAlias) {
		t.Errorf("FAIL: Expected aliasing for AliasedField.\nGot: %s", q2.SQL)
	} else {
		t.Logf("PASS: SQL contains aliasing for AliasedField: %s", q2.SQL)
	}
}

func TestBuilder_Select_IgnoresBacktickedAliasMetadataForDBColumnLookup(t *testing.T) {
	schema := &models.ObjectMetadata{
		APIName: "Notification",
		Fields: []models.FieldMetadata{
			{APIName: "created_date", PhysicalName: "`CreatedDate`", Type: "datetime"},
		},
	}

	q := From("Notification").WithMetadata(schema).Select([]string{"created_date"}).OrderBy("created_date", "DESC").Build()

	if !strings.Contains(q.SQL, "`Notification`.`created_date`") {
		t.Fatalf("expected logical DB column to be used for aliased metadata, got: %s", q.SQL)
	}
	if strings.Contains(q.SQL, "`CreatedDate`") {
		t.Fatalf("expected backticked alias metadata to be ignored for DB column lookup, got: %s", q.SQL)
	}
}