package persistence

import (
	"context"
	"database/sql"
	"fmt"
	"slices"
	"testing"

	"github.com/nexuscrm/kernel/pkg/ports"
	domainrls "github.com/nexuscrm/kernel/pkg/rls"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestQueryRepositorySearchTextAndIDOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()
	createQuerySearchTables(t, db)

	repo := NewQueryRepository(db)
	schema := querySearchSimpleSchema()
	tests := []struct {
		name         string
		term         string
		searchFields []string
		opts         ports.SearchOptions
		wantIDs      []string
	}{
		{
			name:         "canonical ID is exact",
			term:         "001xx0000000001AAA",
			searchFields: []string{constants.FieldID},
			wantIDs:      []string{"001xx0000000001AAA"},
		},
		{
			name:         "safe ID prefix is anchored",
			term:         "001xx",
			searchFields: []string{constants.FieldID},
			wantIDs:      []string{"001xx0000000002AAA", "001xx0000000001AAA"},
		},
		{
			name:         "non ID term does not widen an ID-only search",
			term:         "ada",
			searchFields: []string{constants.FieldID},
		},
		{
			name:         "substring search is case insensitive and stably ordered",
			term:         "alpha",
			searchFields: []string{constants.FieldName},
			wantIDs:      []string{"001xx0000000002AAA", "001xx0000000001AAA"},
		},
		{
			name:         "prefix search matches only the beginning",
			term:         "alpha",
			searchFields: []string{constants.FieldName},
			opts:         ports.SearchOptions{Prefix: true, ForceBaseTable: true},
			wantIDs:      []string{"001xx0000000001AAA"},
		},
		{
			name:         "prefix treats percent as a literal",
			term:         "a%b",
			searchFields: []string{constants.FieldName},
			opts:         ports.SearchOptions{Prefix: true, ForceBaseTable: true},
			wantIDs:      []string{"001xz0000000004AAA"},
		},
		{
			name:         "prefix treats underscore as a literal",
			term:         "a_b",
			searchFields: []string{constants.FieldName},
			opts:         ports.SearchOptions{Prefix: true, ForceBaseTable: true},
			wantIDs:      []string{"001xz0000000006AAA"},
		},
		{
			name:         "prefix treats formula operators as text",
			term:         "ab=cd",
			searchFields: []string{constants.FieldName},
			opts:         ports.SearchOptions{Prefix: true, ForceBaseTable: true},
			wantIDs:      []string{"001xz0000000008AAA"},
		},

		{
			name:         "wildcard lists nondeleted rows",
			term:         "*",
			searchFields: []string{constants.FieldName},
			wantIDs: []string{
				"001xz0000000008AAA", "001xz0000000007AAA", "001xz0000000006AAA",
				"001xz0000000005AAA", "001xz0000000004AAA", "001xy0000000003AAA",
				"001xx0000000002AAA", "001xx0000000001AAA",
			},
		},
	}

	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			rows, err := repo.Search(t.Context(), schema, tc.term, tc.searchFields,
				[]string{constants.FieldID}, 50, 0, "", "", "", nil, tc.opts)
			if err != nil {
				t.Fatalf("Search: %v", err)
			}
			if got := querySearchRecordIDs(rows); !slices.Equal(got, tc.wantIDs) {
				t.Fatalf("record IDs = %v, want %v", got, tc.wantIDs)
			}
		})
	}

	t.Run("compound name prefix is anchored to the composed display name", func(t *testing.T) {
		rows, err := repo.Search(t.Context(), querySearchPersonSchema(), "lov",
			[]string{constants.FieldName}, []string{constants.FieldID},
			50, 0, "", "", "", nil, ports.SearchOptions{Prefix: true, ForceBaseTable: true})
		if err != nil {
			t.Fatalf("Search: %v", err)
		}
		if got, want := querySearchRecordIDs(rows), []string{"person-lov-first"}; !slices.Equal(got, want) {
			t.Fatalf("record IDs = %v, want %v", got, want)
		}
	})
}

func TestQueryRepositorySearchSourceRoutingOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()
	createQuerySearchTables(t, db)

	t.Run("all physical substring search keeps the base table", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchDeal")
		rows := runQuerySearch(t, repo, querySearchDealSchema(), "acme",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", nil,
			ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Acme Small", "Acme Large"})
	})

	t.Run("default search uses the resolved view", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchDeal")
		rows := runQuerySearch(t, repo, querySearchDealSchema(), "acme",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", nil,
			ports.SearchOptions{})
		querySearchRequireNames(t, rows, []string{"Acme Small [view]", "Acme Large [view]"})
	})

	t.Run("view-only projection demotes to the view", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchDeal")
		rows := runQuerySearch(t, repo, querySearchDealSchema(), "acme",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName, "Score"}, "", "", "", nil,
			ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Acme Small [view]", "Acme Large [view]"})
		if len(rows) != 2 || rows[0]["Score"] == nil || rows[1]["Score"] == nil {
			t.Fatalf("view-only Score projection missing: %#v", rows)
		}
	})

	t.Run("view-only filter and sort demote to the view", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchDeal")
		rows := runQuerySearch(t, repo, querySearchDealSchema(), "acme",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "Score", constants.SortDESC,
			"Score > 100", nil, ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Acme Large [view]"})
	})

	t.Run("physical parsed term and sort keep the base table", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchDeal")
		rows := runQuerySearch(t, repo, querySearchDealSchema(), "Amount = 5",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, constants.FieldName, constants.SortASC,
			"", nil, ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Acme Small"})
	})

	t.Run("computed name search demotes to the view", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchTicket")
		rows := runQuerySearch(t, repo, querySearchTicketSchema(), "ada",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", nil,
			ports.SearchOptions{Prefix: true, ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Ada Lovelace"})
	})

	t.Run("compound name projection demotes while base composition still searches", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchPerson")
		rows := runQuerySearch(t, repo, querySearchPersonSchema(), "ada",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", nil,
			ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Lovelace Ada", "Ada Lovelace"})
	})

	t.Run("compound parsed term demotes to the logical view", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchPerson")
		rows := runQuerySearch(t, repo, querySearchPersonSchema(), "Name = Ada Lovelace",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", nil,
			ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Ada Lovelace"})
	})
}

func TestQueryRepositorySearchRLSRoutingOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()
	createQuerySearchTables(t, db)

	t.Run("opaque view-only RLS demotes to the view", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchDeal")
		rls := &domainrls.Result{
			ObjectAPIName:        "SearchDeal",
			CriteriaSQLFragments: []string{"`Score` > 100"},
		}
		rows := runQuerySearch(t, repo, querySearchDealSchema(), "acme",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", rls,
			ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Acme Large [view]"})
	})

	t.Run("prefix search also demotes for opaque view-only RLS", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchDeal")
		rls := &domainrls.Result{
			ObjectAPIName:        "SearchDeal",
			CriteriaSQLFragments: []string{"`Score` > 100"},
		}
		rows := runQuerySearch(t, repo, querySearchDealSchema(), "acme",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", rls,
			ports.SearchOptions{Prefix: true, ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Acme Large [view]"})
	})

	t.Run("normalized physical RLS keeps the base table", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchDeal")
		rls := &domainrls.Result{
			ObjectAPIName:        "SearchDeal",
			CriteriaSQLFragments: []string{"`Amount` > 10"},
			CriteriaExpressions:  []string{"Amount > 10"},
		}
		rows := runQuerySearch(t, repo, querySearchDealSchema(), "acme",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", rls,
			ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Acme Large"})
	})

	t.Run("opaque aliased-field RLS demotes to the view", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchAlias")
		rls := &domainrls.Result{
			ObjectAPIName:        "SearchAlias",
			CriteriaSQLFragments: []string{"`Score` > 100"},
		}
		rows := runQuerySearch(t, repo, querySearchAliasSchema(), "alpha",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", rls,
			ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Alpha High [view]"})
	})

	t.Run("normalized aliased-field RLS maps to the base column", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchAlias")
		rls := &domainrls.Result{
			ObjectAPIName:        "SearchAlias",
			CriteriaSQLFragments: []string{"`Score` > 100"},
			CriteriaExpressions:  []string{"Score > 100"},
		}
		rows := runQuerySearch(t, repo, querySearchAliasSchema(), "alpha",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", rls,
			ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Alpha High"})
	})

	t.Run("direct aliased RLS field maps on the base table", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchAlias")
		rls := &domainrls.Result{
			ObjectAPIName: "SearchAlias",
			HasOwnerField: true,
			OwnerUserIDs:  []string{"reader"},
		}
		rows := runQuerySearch(t, repo, querySearchAliasSchema(), "alpha",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", rls,
			ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Alpha High"})
	})

	t.Run("parsed aliased field term demotes to the logical view", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchAlias")
		rows := runQuerySearch(t, repo, querySearchAliasSchema(), "Score = 900",
			[]string{constants.FieldName}, []string{constants.FieldID, constants.FieldName}, "", "", "", nil,
			ports.SearchOptions{ForceBaseTable: true})
		querySearchRequireNames(t, rows, []string{"Alpha High [view]"})
	})
}

func TestQueryRepositoryAnalyticsMapsDirectRLSAliasesOnTiDB(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()
	createQuerySearchTables(t, db)

	got, err := NewQueryRepository(db).RunAnalytics(t.Context(), querySearchAliasSchema(), models.AnalyticsQuery{
		Operation: OpCount,
	}, &domainrls.Result{
		ObjectAPIName: "SearchAlias",
		HasOwnerField: true,
		OwnerUserIDs:  []string{"reader"},
	})
	if err != nil {
		t.Fatalf("RunAnalytics with aliased RLS field: %v", err)
	}
	if fmt.Sprint(got) != "1" {
		t.Fatalf("count = %v, want 1", got)
	}
}

func createQuerySearchTables(t *testing.T, db *sql.DB) {
	t.Helper()
	for _, statement := range []string{
		`CREATE TABLE SearchEntry (Id VARCHAR(255) PRIMARY KEY, Name VARCHAR(255), IsDeleted BOOL NOT NULL DEFAULT 0) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
		`INSERT INTO SearchEntry (Id, Name, IsDeleted) VALUES
			('001xx0000000001AAA', 'Alpha North', 0),
			('001xx0000000002AAA', 'North Alpha', 0),
			('001xy0000000003AAA', 'Other', 0),
			('001xz0000000004AAA', 'a%b literal', 0),
			('001xz0000000005AAA', 'axb literal', 0),
			('001xz0000000006AAA', 'a_b literal', 0),
			('001xz0000000007AAA', 'acb literal', 0),
			('001xz0000000008AAA', 'ab=cd literal', 0),
			('001xz0000000009AAA', 'Soft deleted', 1)`,
		`CREATE TABLE SearchPerson (Id VARCHAR(255) PRIMARY KEY, FirstName VARCHAR(255), LastName VARCHAR(255), IsDeleted BOOL NOT NULL DEFAULT 0) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
		`INSERT INTO SearchPerson (Id, FirstName, LastName) VALUES
			('person-ada', 'Ada', 'Lovelace'),
			('person-lov-first', 'Lovelace', 'Ada')`,
		`CREATE VIEW v_SearchPerson AS SELECT Id, CONCAT_WS(' ', FirstName, LastName) AS Name, FirstName, LastName, IsDeleted FROM SearchPerson`,
		`CREATE TABLE SearchDeal (Id VARCHAR(255) PRIMARY KEY, Name VARCHAR(255), Amount DECIMAL(18,2), IsDeleted BOOL NOT NULL DEFAULT 0) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
		`INSERT INTO SearchDeal (Id, Name, Amount) VALUES ('deal-small', 'Acme Small', 5), ('deal-large', 'Acme Large', 20)`,
		`CREATE VIEW v_SearchDeal AS SELECT Id, CONCAT(Name, ' [view]') AS Name, Amount, Amount * 10 AS Score, IsDeleted FROM SearchDeal`,
		`CREATE TABLE SearchAlias (Id VARCHAR(255) PRIMARY KEY, name_text VARCHAR(255), score_val DECIMAL(18,2), owner_id VARCHAR(255), IsDeleted BOOL NOT NULL DEFAULT 0) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
		`INSERT INTO SearchAlias (Id, name_text, score_val, owner_id) VALUES ('alias-low', 'Alpha Low', 50, 'other'), ('alias-high', 'Alpha High', 900, 'reader')`,
		`CREATE VIEW v_SearchAlias AS SELECT Id, CONCAT(name_text, ' [view]') AS Name, score_val AS Score, owner_id AS OwnerId, IsDeleted FROM SearchAlias`,
		`CREATE TABLE SearchTicket (Id VARCHAR(255) PRIMARY KEY, FirstName VARCHAR(255), LastName VARCHAR(255), IsDeleted BOOL NOT NULL DEFAULT 0) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
		`INSERT INTO SearchTicket (Id, FirstName, LastName) VALUES ('ticket-ada', 'Ada', 'Lovelace')`,
		`CREATE VIEW v_SearchTicket AS SELECT Id, CONCAT_WS(' ', FirstName, LastName) AS Name, IsDeleted FROM SearchTicket`,
	} {
		if _, err := db.ExecContext(t.Context(), statement); err != nil {
			t.Fatalf("prepare query search fixture: %v\n%s", err, statement)
		}
	}
}

func querySearchSimpleSchema() *models.ObjectMetadata {
	return &models.ObjectMetadata{
		APIName: "SearchEntry", Searchable: true,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, IsNameField: true},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean, IsSystem: true},
		},
	}
}

func querySearchPersonSchema() *models.ObjectMetadata {
	return &models.ObjectMetadata{
		APIName: "SearchPerson", Searchable: true,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{
				APIName: constants.FieldName, Type: constants.FieldTypeString, IsNameField: true,
				IsCompound: true, IsVirtual: true, VirtualKind: constants.VirtualFieldCompound,
				ComposedFrom: []string{"FirstName", "LastName"},
			},
			{APIName: "FirstName", Type: constants.FieldTypeString},
			{APIName: "LastName", Type: constants.FieldTypeString},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean, IsSystem: true},
		},
	}
}

func querySearchDealSchema() *models.ObjectMetadata {
	active := "active"
	formula := "Amount * 10"
	return &models.ObjectMetadata{
		APIName: "SearchDeal", Searchable: true,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, IsNameField: true},
			{APIName: "Amount", Type: constants.FieldTypeDouble},
			{APIName: "Score", Type: constants.FieldTypeFormula, Formula: &formula, FormulaMode: &active},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean, IsSystem: true},
		},
	}
}

func querySearchAliasSchema() *models.ObjectMetadata {
	return &models.ObjectMetadata{
		APIName: "SearchAlias", Searchable: true,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, PhysicalName: "name_text", Type: constants.FieldTypeString, IsNameField: true},
			{APIName: "Score", PhysicalName: "score_val", Type: constants.FieldTypeDouble},
			{APIName: constants.FieldOwnerID, PhysicalName: "owner_id", Type: constants.FieldTypeReference},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean, IsSystem: true},
		},
	}
}

func querySearchTicketSchema() *models.ObjectMetadata {
	active := "active"
	formula := "FirstName & ' ' & LastName"
	return &models.ObjectMetadata{
		APIName: "SearchTicket", Searchable: true,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeFormula, IsNameField: true, Formula: &formula, FormulaMode: &active},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean, IsSystem: true},
		},
	}
}

type querySearchViewResolver struct {
	table string
}

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

func querySearchRepositoryWithView(db *sql.DB, view string) *QueryRepository {
	repo := NewQueryRepository(db)
	repo.SetViewResolver(querySearchViewResolver{table: view})
	return repo
}

func runQuerySearch(t *testing.T, repo *QueryRepository, schema *models.ObjectMetadata, term string, searchFields, selectFields []string, orderBy, orderDir, filterExpr string, rls *domainrls.Result, opts ports.SearchOptions) []models.SObject {
	t.Helper()
	rows, err := repo.Search(t.Context(), schema, term, searchFields, selectFields, 50, 0, orderBy, orderDir, filterExpr, rls, opts)
	if err != nil {
		t.Fatalf("Search: %v", err)
	}
	return rows
}

func querySearchRecordIDs(rows []models.SObject) []string {
	ids := make([]string, 0, len(rows))
	for _, row := range rows {
		if id, ok := row[constants.FieldID].(string); ok {
			ids = append(ids, id)
		}
	}
	return ids
}

func querySearchRequireNames(t *testing.T, rows []models.SObject, want []string) {
	t.Helper()
	got := make([]string, 0, len(rows))
	for _, row := range rows {
		if name, ok := row[constants.FieldName].(string); ok {
			got = append(got, name)
		}
	}
	if !slices.Equal(got, want) {
		t.Fatalf("record names = %v, want %v", got, want)
	}
}