package persistence

import (
	"context"
	"database/sql"
	"sort"
	"testing"

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

type rlsIntegrationViewResolver map[string]string

func (r rlsIntegrationViewResolver) ResolveTableName(_ context.Context, schema *models.ObjectMetadata) string {
	if schema == nil {
		return ""
	}
	if table := r[schema.APIName]; table != "" {
		return table
	}
	return schema.APIName
}

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

	repo := NewQueryRepository(db)
	repo.SetViewResolver(rlsIntegrationViewResolver{
		"RLSTarget":      "v_RLSTarget",
		"RLSParent":      "v_RLSParent",
		"RLSAliasRoot":   "v_RLSAliasRoot",
		"RLSAliasTarget": "v_RLSAliasTarget",
	})

	t.Run("polymorphic target visibility", func(t *testing.T) {
		targetSchema := rlsIntegrationTargetSchema("RLSTarget")
		result, err := repo.Find(t.Context(), rlsIntegrationActivitySchema(), models.QueryRequest{
			ObjectAPIName: "RLSActivity", SelectFields: []string{constants.FieldID}, Limit: 20, SkipTotalSize: true,
		}, &domainrls.Result{
			ObjectAPIName: "RLSActivity",
			TargetRecordFilters: []domainrls.TargetRecordFilter{{
				TargetObjectField: "TargetType", TargetRecordField: "TargetId",
				Alternatives: []domainrls.TargetRecordFilterAlternative{{
					TargetObjectAPIName: "RLSTarget", TargetSchema: targetSchema,
					TargetRLSResult: &domainrls.Result{
						ObjectAPIName: "RLSTarget", HasOwnerField: true, OwnerUserIDs: []string{"reader"},
					},
				}},
			}},
		}, []string{constants.FieldID, "TargetType", "TargetId", constants.FieldIsDeleted})
		if err != nil {
			t.Fatalf("Find polymorphic target visibility: %v", err)
		}
		if got, want := rlsIntegrationRecordIDs(result.Records), []string{"activity-visible"}; !equalRLSIntegrationStrings(got, want) {
			t.Fatalf("record IDs = %v, want %v", got, want)
		}
	})

	t.Run("controlled-parent visibility", func(t *testing.T) {
		parentSchema := rlsIntegrationTargetSchema("RLSParent")
		result, err := repo.Find(t.Context(), rlsIntegrationChildSchema(), models.QueryRequest{
			ObjectAPIName: "RLSChild", SelectFields: []string{constants.FieldID}, Limit: 20, SkipTotalSize: true,
		}, &domainrls.Result{
			ObjectAPIName: "RLSChild",
			ParentFilter: &domainrls.ParentFilter{
				ParentObjectAPIName: "RLSParent", ForeignKeyField: "ParentId", ParentSchema: parentSchema,
				ParentDerivedOnly: true,
				ParentRLSResult: &domainrls.Result{
					ObjectAPIName: "RLSParent", HasOwnerField: true, OwnerUserIDs: []string{"reader"},
				},
			},
		}, []string{constants.FieldID, "ParentId", constants.FieldIsDeleted})
		if err != nil {
			t.Fatalf("Find controlled-parent visibility: %v", err)
		}
		if got, want := rlsIntegrationRecordIDs(result.Records), []string{"child-visible"}; !equalRLSIntegrationStrings(got, want) {
			t.Fatalf("record IDs = %v, want %v", got, want)
		}
	})

	t.Run("root direct fields fall back to logical view", func(t *testing.T) {
		schema := &models.ObjectMetadata{
			APIName: "RLSAliasRoot",
			Fields: []models.FieldMetadata{
				{APIName: constants.FieldID, PhysicalName: "record_id", Type: constants.FieldTypeString, IsSystem: true},
				{APIName: constants.FieldOwnerID, PhysicalName: "owner_id", Type: constants.FieldTypeReference},
				{APIName: constants.FieldIsDeleted, PhysicalName: "is_deleted", Type: constants.FieldTypeBoolean, IsSystem: true},
			},
		}
		result, err := repo.Find(t.Context(), schema, models.QueryRequest{
			ObjectAPIName: "RLSAliasRoot", SelectFields: []string{constants.FieldID}, Limit: 20, SkipTotalSize: true,
		}, &domainrls.Result{
			ObjectAPIName: "RLSAliasRoot", HasOwnerField: true, OwnerUserIDs: []string{"reader"},
		}, []string{constants.FieldID, constants.FieldOwnerID, constants.FieldIsDeleted})
		if err != nil {
			t.Fatalf("Find aliased root visibility: %v", err)
		}
		if got, want := rlsIntegrationRecordIDs(result.Records), []string{"root-visible"}; !equalRLSIntegrationStrings(got, want) {
			t.Fatalf("record IDs = %v, want %v", got, want)
		}
	})

	t.Run("target view uses logical id and deleted columns", func(t *testing.T) {
		formulaExpr := "OwnerId"
		active := "active"
		targetSchema := &models.ObjectMetadata{
			APIName: "RLSAliasTarget",
			Fields: []models.FieldMetadata{
				{APIName: constants.FieldID, PhysicalName: "record_id", Type: constants.FieldTypeString, IsSystem: true},
				{APIName: constants.FieldOwnerID, PhysicalName: "owner_id", Type: constants.FieldTypeReference},
				{APIName: constants.FieldIsDeleted, PhysicalName: "is_deleted", Type: constants.FieldTypeBoolean, IsSystem: true},
				{APIName: "ComputedAccess", Type: constants.FieldTypeFormula, Formula: &formulaExpr, FormulaMode: &active},
			},
		}
		result, err := repo.Find(t.Context(), rlsIntegrationActivitySchema(), models.QueryRequest{
			ObjectAPIName: "RLSActivity", SelectFields: []string{constants.FieldID}, Limit: 20, SkipTotalSize: true,
		}, &domainrls.Result{
			ObjectAPIName: "RLSActivity",
			TargetRecordFilters: []domainrls.TargetRecordFilter{{
				TargetObjectField: "TargetType", TargetRecordField: "TargetId",
				Alternatives: []domainrls.TargetRecordFilterAlternative{{
					TargetObjectAPIName: "RLSAliasTarget", TargetSchema: targetSchema,
					TargetRLSResult: &domainrls.Result{
						ObjectAPIName:        "RLSAliasTarget",
						CriteriaExpressions:  []string{`ComputedAccess == "reader"`},
						CriteriaSQLFragments: []string{"`ComputedAccess` = 'reader'"},
					},
				}},
			}},
		}, []string{constants.FieldID, "TargetType", "TargetId", constants.FieldIsDeleted})
		if err != nil {
			t.Fatalf("Find aliased target visibility: %v", err)
		}
		if got, want := rlsIntegrationRecordIDs(result.Records), []string{"activity-alias-visible"}; !equalRLSIntegrationStrings(got, want) {
			t.Fatalf("record IDs = %v, want %v", got, want)
		}
	})
}

func createRLSExistsTables(t *testing.T, db *sql.DB) {
	t.Helper()
	statements := []string{
		`CREATE TABLE RLSTarget (Id VARCHAR(64) PRIMARY KEY, OwnerId VARCHAR(64), IsDeleted BOOL NOT NULL DEFAULT 0)`,
		`CREATE TABLE RLSParent (Id VARCHAR(64) PRIMARY KEY, OwnerId VARCHAR(64), IsDeleted BOOL NOT NULL DEFAULT 0)`,
		`CREATE TABLE RLSActivity (Id VARCHAR(64) PRIMARY KEY, TargetType VARCHAR(64), TargetId VARCHAR(64), IsDeleted BOOL NOT NULL DEFAULT 0)`,
		`CREATE TABLE RLSChild (Id VARCHAR(64) PRIMARY KEY, ParentId VARCHAR(64), IsDeleted BOOL NOT NULL DEFAULT 0)`,
		`CREATE TABLE RLSDisplay (TargetId VARCHAR(64), Label VARCHAR(64))`,
		`CREATE TABLE RLSAliasRoot (record_id VARCHAR(64) PRIMARY KEY, owner_id VARCHAR(64), is_deleted BOOL NOT NULL DEFAULT 0)`,
		`CREATE TABLE RLSAliasTarget (record_id VARCHAR(64) PRIMARY KEY, owner_id VARCHAR(64), is_deleted BOOL NOT NULL DEFAULT 0)`,
		`INSERT INTO RLSTarget VALUES ('target-visible', 'reader', 0), ('target-hidden', 'other', 0), ('target-deleted', 'reader', 1)`,
		`INSERT INTO RLSParent VALUES ('parent-visible', 'reader', 0), ('parent-hidden', 'other', 0)`,
		`INSERT INTO RLSActivity VALUES
			('activity-visible', 'RLSTarget', 'target-visible', 0),
			('activity-hidden', 'RLSTarget', 'target-hidden', 0),
			('activity-deleted-target', 'RLSTarget', 'target-deleted', 0),
			('activity-wrong-type', 'OtherTarget', 'target-visible', 0),
			('activity-null', NULL, NULL, 0),
			('activity-dangling', 'RLSTarget', 'missing', 0)`,
		`INSERT INTO RLSChild VALUES
			('child-visible', 'parent-visible', 0),
			('child-hidden', 'parent-hidden', 0),
			('child-null', NULL, 0),
			('child-dangling', 'missing', 0)`,
		`INSERT INTO RLSAliasRoot VALUES ('root-visible', 'reader', 0), ('root-hidden', 'other', 0)`,
		`INSERT INTO RLSAliasTarget VALUES ('alias-visible', 'reader', 0), ('alias-hidden', 'other', 0), ('alias-deleted', 'reader', 1)`,
		`INSERT INTO RLSActivity VALUES
			('activity-alias-visible', 'RLSAliasTarget', 'alias-visible', 0),
			('activity-alias-hidden', 'RLSAliasTarget', 'alias-hidden', 0),
			('activity-alias-deleted', 'RLSAliasTarget', 'alias-deleted', 0)`,
		`INSERT INTO RLSDisplay VALUES ('target-visible', 'first'), ('target-visible', 'duplicate')`,
		`CREATE VIEW v_RLSTarget AS SELECT t.Id, t.OwnerId, t.IsDeleted, d.Label AS DisplayOnly FROM RLSTarget t LEFT JOIN RLSDisplay d ON d.TargetId = t.Id`,
		`CREATE VIEW v_RLSParent AS SELECT Id, OwnerId, IsDeleted, Id AS DisplayOnly FROM RLSParent`,
		`CREATE VIEW v_RLSAliasRoot AS SELECT record_id AS Id, owner_id AS OwnerId, is_deleted AS IsDeleted FROM RLSAliasRoot`,
		`CREATE VIEW v_RLSAliasTarget AS SELECT record_id AS Id, owner_id AS OwnerId, is_deleted AS IsDeleted, owner_id AS ComputedAccess FROM RLSAliasTarget`,
	}
	for _, statement := range statements {
		if _, err := db.ExecContext(t.Context(), statement); err != nil {
			t.Fatalf("prepare RLS EXISTS fixture: %v\n%s", err, statement)
		}
	}
}

func rlsIntegrationTargetSchema(apiName string) *models.ObjectMetadata {
	formulaExpr := "Id"
	active := "active"
	return &models.ObjectMetadata{
		APIName: apiName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldOwnerID, Type: constants.FieldTypeReference},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean, IsSystem: true},
			{APIName: "DisplayOnly", Type: constants.FieldTypeFormula, Formula: &formulaExpr, FormulaMode: &active},
		},
	}
}

func rlsIntegrationActivitySchema() *models.ObjectMetadata {
	return &models.ObjectMetadata{
		APIName: "RLSActivity",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: "TargetType", Type: constants.FieldTypeString},
			{APIName: "TargetId", Type: constants.FieldTypeReference},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean, IsSystem: true},
		},
	}
}

func rlsIntegrationChildSchema() *models.ObjectMetadata {
	return &models.ObjectMetadata{
		APIName: "RLSChild",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: "ParentId", Type: constants.FieldTypeReference},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean, IsSystem: true},
		},
	}
}

func rlsIntegrationRecordIDs(records []models.SObject) []string {
	ids := make([]string, 0, len(records))
	for _, record := range records {
		if id, ok := record[constants.FieldID].(string); ok {
			ids = append(ids, id)
		}
	}
	sort.Strings(ids)
	return ids
}

func equalRLSIntegrationStrings(left, right []string) bool {
	if len(left) != len(right) {
		return false
	}
	for i := range left {
		if left[i] != right[i] {
			return false
		}
	}
	return true
}