package persistence

import (
	"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 TestQueryRepositoryReadSourceFindLookupAndSearchOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()
	createQueryReadSourceTables(t, db)

	repo := NewQueryRepository(db)
	schema := queryReadSourceSchema()
	visible := []string{constants.FieldID, constants.FieldName, "Amount", constants.FieldIsDeleted}

	t.Run("pagination returns an exact distinct total", func(t *testing.T) {
		result := runQueryReadSourceFind(t, repo, schema, models.QueryRequest{
			ObjectAPIName: "RecordType", Limit: 1, Offset: 1,
			OrderBy: []models.SortCriterion{{Field: constants.FieldID, Direction: constants.SortASC}},
		}, visible)
		if got, want := queryReadSourceIDs(result.Records), []string{"rt-prospect-a"}; !slices.Equal(got, want) {
			t.Fatalf("record IDs = %v, want %v", got, want)
		}
		if result.TotalSize != 3 || result.Done {
			t.Fatalf("pagination = %#v, want totalSize=3 and done=false", result)
		}
	})

	t.Run("skipped count reports an unknown next page", func(t *testing.T) {
		result := runQueryReadSourceFind(t, repo, schema, models.QueryRequest{
			ObjectAPIName: "RecordType", Limit: 1, SkipTotalSize: true,
		}, visible)
		if result.TotalSize != 1 || result.Done || !result.TotalSizeUnknown {
			t.Fatalf("pagination = %#v, want current page with unknown exact total", result)
		}
	})

	for _, tc := range []struct {
		name string
		req  models.QueryRequest
		want []string
	}{
		{
			name: "IN maps a logical field to its physical column",
			req:  models.QueryRequest{Criteria: []models.QueryCriterion{{Field: constants.FieldName, Op: "IN", Val: []interface{}{"Master"}}}},
			want: []string{"rt-master"},
		},
		{
			name: "NOT IN maps a logical field to its physical column",
			req:  models.QueryRequest{Criteria: []models.QueryCriterion{{Field: constants.FieldName, Op: "NOT IN", Val: []interface{}{"Prospecting"}}}},
			want: []string{"rt-master"},
		},
		{
			name: "comparison and sort map logical fields",
			req:  models.QueryRequest{Criteria: []models.QueryCriterion{{Field: constants.FieldName, Op: "=", Val: "Master"}}, SortField: constants.FieldName, SortDirection: constants.SortASC},
			want: []string{"rt-master"},
		},
		{
			name: "formula filter maps a logical field",
			req:  models.QueryRequest{FilterExpr: `Name = "Master"`},
			want: []string{"rt-master"},
		},
		{
			name: "structured order maps a logical field",
			req:  models.QueryRequest{Limit: 1, OrderBy: []models.SortCriterion{{Field: constants.FieldName, Direction: constants.SortASC}}},
			want: []string{"rt-master"},
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			tc.req.ObjectAPIName = "RecordType"
			tc.req.SkipTotalSize = true
			if tc.req.Limit == 0 {
				tc.req.Limit = 20
			}
			result := runQueryReadSourceFind(t, repo, schema, tc.req, visible)
			if got := queryReadSourceIDs(result.Records); !slices.Equal(got, tc.want) {
				t.Fatalf("record IDs = %v, want %v", got, tc.want)
			}
		})
	}

	searched, err := repo.Search(t.Context(), schema, "master", []string{constants.FieldName},
		[]string{constants.FieldID, constants.FieldName}, 5, 0, "", "", "", nil, ports.SearchOptions{})
	if err != nil {
		t.Fatalf("Search: %v", err)
	}
	if got, want := queryReadSourceIDs(searched), []string{"rt-master"}; !slices.Equal(got, want) {
		t.Fatalf("search record IDs = %v, want %v", got, want)
	}

	lookups, err := repo.GetLookupNames(t.Context(), schema, []string{"rt-master", "rt-archived"}, constants.FieldID, constants.FieldName)
	if err != nil {
		t.Fatalf("GetLookupNames: %v", err)
	}
	if got, want := queryReadSourceIDs(lookups), []string{"rt-master"}; !slices.Equal(got, want) {
		t.Fatalf("lookup record IDs = %v, want %v", got, want)
	}
	records, err := repo.GetRecordsByIDs(t.Context(), schema, []string{"rt-master", "rt-archived"}, []string{constants.FieldName})
	if err != nil {
		t.Fatalf("GetRecordsByIDs: %v", err)
	}
	if got, want := queryReadSourceIDs(records), []string{"rt-master"}; !slices.Equal(got, want) {
		t.Fatalf("hydrated record IDs = %v, want %v", got, want)
	}

	staticSchema := &models.ObjectMetadata{
		APIName: "StaticLookup__mdt",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, IsNameField: true},
		},
	}
	staticLookups, err := repo.GetLookupNames(t.Context(), staticSchema, []string{"meta-1"}, constants.FieldID, constants.FieldName)
	if err != nil {
		t.Fatalf("GetLookupNames without IsDeleted: %v", err)
	}
	if got, want := queryReadSourceIDs(staticLookups), []string{"meta-1"}; !slices.Equal(got, want) {
		t.Fatalf("static lookup IDs = %v, want %v", got, want)
	}
	staticRows, err := repo.GetRecordsByIDs(t.Context(), staticSchema, []string{"meta-1"}, []string{constants.FieldName})
	if err != nil {
		t.Fatalf("GetRecordsByIDs without IsDeleted: %v", err)
	}
	if got, want := queryReadSourceIDs(staticRows), []string{"meta-1"}; !slices.Equal(got, want) {
		t.Fatalf("static record IDs = %v, want %v", got, want)
	}
}

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

	t.Run("physical-only query keeps the base table", func(t *testing.T) {
		result := runQueryReadSourceFind(t, querySearchRepositoryWithView(db, "v_SearchDeal"), querySearchDealSchema(), models.QueryRequest{
			ObjectAPIName: "SearchDeal", FilterExpr: "Amount = 5", SelectFields: []string{constants.FieldID, constants.FieldName}, Limit: 5, SkipTotalSize: true,
		}, []string{constants.FieldID, constants.FieldName, "Amount", constants.FieldIsDeleted})
		if len(result.Records) != 1 || result.Records[0][constants.FieldName] != "Acme Small" {
			t.Fatalf("base result = %#v, want Acme Small", result.Records)
		}
	})

	t.Run("formula filter and projection route to the view", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchDeal")
		filtered := runQueryReadSourceFind(t, repo, querySearchDealSchema(), models.QueryRequest{
			ObjectAPIName: "SearchDeal", FilterExpr: "Score > 100", SelectFields: []string{constants.FieldID, constants.FieldName}, Limit: 5, SkipTotalSize: true,
		}, []string{constants.FieldID, constants.FieldName, "Score", constants.FieldIsDeleted})
		if len(filtered.Records) != 1 || filtered.Records[0][constants.FieldName] != "Acme Large [view]" {
			t.Fatalf("formula-filter result = %#v, want view-backed large deal", filtered.Records)
		}

		projected := runQueryReadSourceFind(t, repo, querySearchDealSchema(), models.QueryRequest{
			ObjectAPIName: "SearchDeal", FilterExpr: "Amount = 5", Limit: 5, SkipTotalSize: true,
		}, []string{constants.FieldID, "Score"})
		if len(projected.Records) != 1 || fmt.Sprint(projected.Records[0]["Score"]) != "50.00" {
			t.Fatalf("formula-projection result = %#v, want Score=50.00", projected.Records)
		}

		nonblank := runQueryReadSourceFind(t, repo, querySearchDealSchema(), models.QueryRequest{
			ObjectAPIName: "SearchDeal", FilterExpr: "NOT(FLOWISBLANK(Score))", SelectFields: []string{constants.FieldID, constants.FieldName}, Limit: 5, SkipTotalSize: true,
			OrderBy: []models.SortCriterion{{Field: constants.FieldID, Direction: constants.SortASC}},
		}, []string{constants.FieldID, constants.FieldName, "Score", constants.FieldIsDeleted})
		if got, want := queryReadSourceIDs(nonblank.Records), []string{"deal-large", "deal-small"}; !slices.Equal(got, want) {
			t.Fatalf("nonblank formula record IDs = %v, want %v", got, want)
		}
	})

	t.Run("compound projection and RLS choose one consistent read target", func(t *testing.T) {
		repo := querySearchRepositoryWithView(db, "v_SearchPerson")
		rls := &domainrls.Result{
			ObjectAPIName: "SearchPerson", CriteriaSQLFragments: []string{"`FirstName` = 'Ada'"}, CriteriaExpressions: []string{"FirstName = 'Ada'"},
		}
		viewResult := runQueryReadSourceFind(t, repo, querySearchPersonSchema(), models.QueryRequest{
			ObjectAPIName: "SearchPerson", SelectFields: []string{constants.FieldID, constants.FieldName}, Limit: 5, SkipTotalSize: true,
		}, []string{constants.FieldID, constants.FieldName, "FirstName", constants.FieldIsDeleted}, rls)
		if len(viewResult.Records) != 1 || viewResult.Records[0][constants.FieldName] != "Ada Lovelace" {
			t.Fatalf("compound view result = %#v", viewResult.Records)
		}

		baseResult := runQueryReadSourceFind(t, repo, querySearchPersonSchema(), models.QueryRequest{
			ObjectAPIName: "SearchPerson", SelectFields: []string{constants.FieldID, "FirstName"}, Limit: 5, SkipTotalSize: true,
		}, []string{constants.FieldID, "FirstName", constants.FieldIsDeleted}, rls)
		if len(baseResult.Records) != 1 || baseResult.Records[0]["FirstName"] != "Ada" {
			t.Fatalf("physical projection result = %#v", baseResult.Records)
		}
	})
}

func TestQueryRepositoryReadSourceAnalyticsOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()
	createQueryReadSourceTables(t, db)
	repo := NewQueryRepository(db)
	schema := queryReadSourceSchema()

	for _, tc := range []struct {
		name    string
		scanAll bool
		want    string
	}{
		{name: "deleted rows are excluded", want: "3"},
		{name: "queryAll includes deleted rows", scanAll: true, want: "4"},
	} {
		t.Run(tc.name, func(t *testing.T) {
			got, err := repo.RunAnalytics(t.Context(), schema, models.AnalyticsQuery{Operation: OpCount, ScanAll: tc.scanAll}, nil)
			if err != nil {
				t.Fatalf("RunAnalytics count: %v", err)
			}
			if fmt.Sprint(got) != tc.want {
				t.Fatalf("count = %v, want %s", got, tc.want)
			}
		})
	}

	groupBy := constants.FieldName
	grouped, err := repo.RunAnalytics(t.Context(), schema, models.AnalyticsQuery{
		Operation: OpGroupBy, GroupBy: &groupBy, Having: `Name != 'Master'`, SortField: "name", SortDirection: constants.SortASC,
	}, nil)
	if err != nil {
		t.Fatalf("RunAnalytics having: %v", err)
	}
	rows := queryReadSourceAnalyticsRows(t, grouped)
	if len(rows) != 1 || rows[0]["name"] != "Prospecting" || fmt.Sprint(rows[0]["val"]) != "2" {
		t.Fatalf("grouped having result = %#v, want Prospecting=2", rows)
	}

	paged, err := repo.RunAnalytics(t.Context(), schema, models.AnalyticsQuery{
		Operation: OpGroupBy, GroupBy: &groupBy, Limit: 1, Offset: 1,
	}, nil)
	if err != nil {
		t.Fatalf("RunAnalytics page: %v", err)
	}
	pageRows := queryReadSourceAnalyticsRows(t, paged)
	if len(pageRows) != 1 || pageRows[0]["name"] != "Master" {
		t.Fatalf("grouped page = %#v, want second group Master", pageRows)
	}

	multi, err := repo.RunAnalytics(t.Context(), schema, models.AnalyticsQuery{
		Operation: OpGroupBy, GroupBy: &groupBy,
		Aggregates: []models.AnalyticsAggregate{
			{Type: "COUNT", Field: constants.FieldID},
			{Type: "SUM", Field: "Amount"},
			{Type: "AVG", Field: "Amount"},
			{Type: "MIN", Field: "Amount"},
			{Type: "MAX", Field: "Amount"},
		},
	}, nil)
	if err != nil {
		t.Fatalf("RunAnalytics aggregates: %v", err)
	}
	multiRows := queryReadSourceAnalyticsRows(t, multi)
	if len(multiRows) != 2 || multiRows[0]["name"] != "Prospecting" || fmt.Sprint(multiRows[0]["agg0"]) != "2" || fmt.Sprint(multiRows[0]["agg1"]) != "125.5" || fmt.Sprint(multiRows[0]["agg2"]) != "62.75" || fmt.Sprint(multiRows[0]["agg3"]) != "50" || fmt.Sprint(multiRows[0]["agg4"]) != "75.5" {
		t.Fatalf("multi-aggregate result = %#v", multiRows)
	}
}

func createQueryReadSourceTables(t *testing.T, db *sql.DB) {
	t.Helper()
	for _, statement := range []string{
		`CREATE TABLE _System_QueryReadSource (Id VARCHAR(255) PRIMARY KEY, record_type_name VARCHAR(255), amount DECIMAL(18,2), IsDeleted BOOL NOT NULL DEFAULT 0)`,
		`INSERT INTO _System_QueryReadSource (Id, record_type_name, amount, IsDeleted) VALUES
			('rt-master', 'Master', 10, 0),
			('rt-prospect-a', 'Prospecting', 50, 0),
			('rt-prospect-b', 'Prospecting', 75.5, 0),
			('rt-archived', 'Archived', 9, 1)`,
		`CREATE TABLE StaticLookup__mdt (Id VARCHAR(255) PRIMARY KEY, Name VARCHAR(255))`,
		`INSERT INTO StaticLookup__mdt (Id, Name) VALUES ('meta-1', 'Static')`,
	} {
		if _, err := db.ExecContext(t.Context(), statement); err != nil {
			t.Fatalf("prepare query read-source fixture: %v\n%s", err, statement)
		}
	}
}

func queryReadSourceSchema() *models.ObjectMetadata {
	schema := testRecordTypeQuerySchema(&models.ObjectReadSourceConfig{Kind: "physical_table", PhysicalTable: "_System_QueryReadSource"})
	schema.Fields[1].PhysicalName = "record_type_name"
	schema.Fields = append(schema.Fields, models.FieldMetadata{APIName: "Amount", Type: constants.FieldTypeDouble, PhysicalName: "amount"})
	return schema
}

func runQueryReadSourceFind(t *testing.T, repo *QueryRepository, schema *models.ObjectMetadata, req models.QueryRequest, visible []string, rls ...*domainrls.Result) *models.QueryResult {
	t.Helper()
	var resultRLS *domainrls.Result
	if len(rls) > 0 {
		resultRLS = rls[0]
	}
	result, err := repo.Find(t.Context(), schema, req, resultRLS, visible)
	if err != nil {
		t.Fatalf("Find: %v", err)
	}
	return result
}

func queryReadSourceIDs(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 queryReadSourceAnalyticsRows(t *testing.T, got interface{}) []models.SObject {
	t.Helper()
	rows, ok := got.([]models.SObject)
	if !ok {
		t.Fatalf("analytics result type = %T, want []models.SObject", got)
	}
	return rows
}