package persistence

import (
	"context"
	"database/sql"
	"errors"
	"slices"
	"sort"
	"strings"
	"testing"

	database "github.com/nexuscrm/db"
	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestListViewRepositoryOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()

	createCanonicalMetadataTables(t, db,
		constants.TableProfile,
		constants.TableUserRole,
		constants.TableUser,
		constants.TableListView,
	)
	seedListViewUsers(t, db)
	repository := NewMetadataRepository(db)

	mine := &models.ListView{
		ID: "view-mine", ObjectAPIName: "Account", Label: "My Accounts",
		FilterExpr: "Name != ''", FilterLogic: "1", SortField: "Name", SortDirection: "ASC",
		Fields: []string{"Name", "OwnerId"}, OwnerID: listViewString("user-a"),
		Visibility: listViewString("Private"), DeveloperName: listViewString("Mine"),
	}
	if err := repository.CreateListView(t.Context(), mine); err != nil {
		t.Fatalf("CreateListView: %v", err)
	}
	seedListViewRow(t, db, "view-foreign", "Account", "Foreign", "user-b", "Private", "Foreign")
	seedListViewRow(t, db, "view-public", "Account", "Public", "user-a", "Public", "Public")
	seedListViewRow(t, db, "view-legacy", "Account", "Legacy", "", "", "")
	seedListViewRow(t, db, "view-contact", "Contact", "Contact Mine", "user-a", "Private", "Mine")

	t.Run("reads enforce owner visibility object and legacy boundaries", func(t *testing.T) {
		private, err := repository.GetPrivateListViews(t.Context(), "Account", "user-a")
		if err != nil {
			t.Fatalf("GetPrivateListViews: %v", err)
		}
		requireListViewIDs(t, private, "view-mine")

		if _, err := repository.GetPrivateListView(t.Context(), "Account", "view-mine", "user-b"); !errors.Is(err, sql.ErrNoRows) {
			t.Fatalf("foreign GetPrivateListView = %v, want sql.ErrNoRows", err)
		}
		got, err := repository.GetPrivateListView(t.Context(), "Account", "view-mine", "user-a")
		if err != nil {
			t.Fatalf("owned GetPrivateListView: %v", err)
		}
		requireListViewDefinition(t, got, mine)

		legacy, err := repository.GetListViews(t.Context(), "Account")
		if err != nil {
			t.Fatalf("GetListViews: %v", err)
		}
		requireListViewIDs(t, legacy, "view-legacy")

		filtered, err := repository.GetListViewsWithReadFilter(
			t.Context(), "Account", "`_System_ListView`.`OwnerId` = 'user-a' AND LOWER(`_System_ListView`.`Visibility`) = 'private'",
		)
		if err != nil {
			t.Fatalf("GetListViewsWithReadFilter: %v", err)
		}
		requireListViewIDs(t, filtered, "view-legacy", "view-mine")

		bypass, err := repository.GetListViewsWithReadFilter(t.Context(), "Account", "")
		if err != nil {
			t.Fatalf("GetListViewsWithReadFilter bypass: %v", err)
		}
		requireListViewIDs(t, bypass, "view-foreign", "view-legacy", "view-mine", "view-public")
	})

	t.Run("developer names are object scoped case insensitive and non leaking", func(t *testing.T) {
		got, err := repository.GetListViewByDeveloperName(t.Context(), "Account", " mInE ")
		if err != nil {
			t.Fatalf("GetListViewByDeveloperName: %v", err)
		}
		if got.ID != mine.ID {
			t.Fatalf("developer-name lookup ID = %q, want %q", got.ID, mine.ID)
		}

		err = repository.CreateListView(t.Context(), &models.ListView{
			ID: "view-duplicate", ObjectAPIName: "Account", Label: "Duplicate",
			Fields: []string{}, OwnerID: listViewString("user-a"), Visibility: listViewString("Private"),
			DeveloperName: listViewString("MINE"),
		})
		if err == nil || !apperrors.IsConflict(err) || errors.Is(err, sql.ErrNoRows) {
			t.Fatalf("duplicate developer name = %T %v, want conflict", err, err)
		}
		if strings.Contains(err.Error(), "view-mine") || strings.Contains(err.Error(), "My Accounts") {
			t.Fatalf("duplicate developer-name conflict leaked row details: %v", err)
		}
		if _, err := repository.GetListViewByDeveloperName(t.Context(), "Contact", "mine"); err != nil {
			t.Fatalf("same developer name in another object: %v", err)
		}
	})

	t.Run("patch and delete stay fenced to the private owner", func(t *testing.T) {
		label := "Not Allowed"
		if err := repository.UpdateListViewPatch(t.Context(), mine.ID, "user-b", models.ListViewPatch{Label: &label}); !errors.Is(err, sql.ErrNoRows) {
			t.Fatalf("foreign UpdateListViewPatch = %v, want sql.ErrNoRows", err)
		}

		label = "My Accounts Updated"
		emptyLogic := ""
		emptyFields := []string{}
		developerName := "MineRenamed"
		collision := "FOREIGN"
		if err := repository.UpdateListViewPatch(t.Context(), mine.ID, "user-a", models.ListViewPatch{DeveloperName: &collision}); err == nil || !apperrors.IsConflict(err) {
			t.Fatalf("hidden developer-name collision = %T %v, want conflict", err, err)
		} else if strings.Contains(err.Error(), "view-foreign") || strings.Contains(err.Error(), "Foreign") {
			t.Fatalf("hidden developer-name conflict leaked row details: %v", err)
		}
		if err := repository.UpdateListViewPatch(t.Context(), mine.ID, "user-a", models.ListViewPatch{
			Label: &label, FilterLogic: &emptyLogic, Fields: &emptyFields, DeveloperName: &developerName,
		}); err != nil {
			t.Fatalf("UpdateListViewPatch: %v", err)
		}
		updated, err := repository.GetListView(t.Context(), mine.ID)
		if err != nil {
			t.Fatalf("GetListView after patch: %v", err)
		}
		if updated.Label != label || updated.FilterLogic != "" || len(updated.Fields) != 0 ||
			updated.FilterExpr != mine.FilterExpr || updated.SortField != mine.SortField ||
			updated.OwnerID == nil || *updated.OwnerID != "user-a" ||
			updated.Visibility == nil || *updated.Visibility != "Private" ||
			updated.DeveloperName == nil || *updated.DeveloperName != developerName {
			t.Fatalf("patched list view = %+v", updated)
		}

		if err := repository.DeletePrivateListView(t.Context(), mine.ID, "user-b"); !errors.Is(err, sql.ErrNoRows) {
			t.Fatalf("foreign DeletePrivateListView = %v, want sql.ErrNoRows", err)
		}
		if err := repository.DeletePrivateListView(t.Context(), "view-public", "user-a"); !errors.Is(err, sql.ErrNoRows) {
			t.Fatalf("public DeletePrivateListView = %v, want sql.ErrNoRows", err)
		}
		if _, err := repository.GetListView(t.Context(), "view-public"); err != nil {
			t.Fatalf("private delete removed public row: %v", err)
		}
		if _, err := repository.GetListView(t.Context(), mine.ID); err != nil {
			t.Fatalf("foreign delete removed row: %v", err)
		}
		if err := repository.DeletePrivateListView(t.Context(), mine.ID, "user-a"); err != nil {
			t.Fatalf("DeletePrivateListView: %v", err)
		}
		if _, err := repository.GetListView(t.Context(), mine.ID); !errors.Is(err, sql.ErrNoRows) {
			t.Fatalf("GetListView after delete = %v, want sql.ErrNoRows", err)
		}
	})

	t.Run("ambient transaction owns list view writes and rollback", func(t *testing.T) {
		manager := NewTransactionManager(database.NewConnectionFromDB(db))
		forcedRollback := errors.New("force list-view rollback")
		err := manager.WithTransaction(t.Context(), func(txCtx context.Context) error {
			view := &models.ListView{
				ID: "view-rollback", ObjectAPIName: "Account", Label: "Transient", Fields: []string{"Name"},
				OwnerID: listViewString("user-a"), Visibility: listViewString("Private"), DeveloperName: listViewString("Transient"),
			}
			if err := repository.CreateListView(txCtx, view); err != nil {
				return err
			}
			inside, err := repository.GetListView(txCtx, view.ID)
			if err != nil || inside.Label != view.Label {
				return errors.New("transaction did not observe its list-view write")
			}
			return forcedRollback
		})
		if !errors.Is(err, forcedRollback) {
			t.Fatalf("WithTransaction = %v, want forced rollback", err)
		}
		if _, err := repository.GetListView(t.Context(), "view-rollback"); !errors.Is(err, sql.ErrNoRows) {
			t.Fatalf("rolled-back list view = %v, want sql.ErrNoRows", err)
		}
	})
}

func seedListViewUsers(t *testing.T, db *sql.DB) {
	t.Helper()
	metadataReadExec(t, db, "INSERT INTO Profile (Id, Name) VALUES ('profile-test', 'Test Profile')")
	for _, id := range []string{"user-a", "user-b"} {
		metadataReadExec(t, db, `INSERT INTO User (Id, Username, Email, Password, LastName, ProfileId)
			VALUES (?, ?, ?, 'unused', 'User', 'profile-test')`, id, id+"@example.test", id+"@example.test")
	}
}

func seedListViewRow(t *testing.T, db *sql.DB, id, objectAPIName, label, ownerID, visibility, developerName string) {
	t.Helper()
	var owner, visible, developer any
	if ownerID != "" {
		owner = ownerID
	}
	if visibility != "" {
		visible = visibility
	}
	if developerName != "" {
		developer = developerName
	}
	metadataReadExec(t, db, `INSERT INTO _System_ListView
		(Id, ObjectApiName, Label, Fields, OwnerId, Visibility, DeveloperName)
		VALUES (?, ?, ?, '[]', ?, ?, ?)`, id, objectAPIName, label, owner, visible, developer)
}

func requireListViewIDs(t *testing.T, views []*models.ListView, want ...string) {
	t.Helper()
	got := make([]string, 0, len(views))
	for _, view := range views {
		got = append(got, view.ID)
	}
	sort.Strings(got)
	sort.Strings(want)
	if !slices.Equal(got, want) {
		t.Fatalf("list view IDs = %v, want %v", got, want)
	}
}

func requireListViewDefinition(t *testing.T, got, want *models.ListView) {
	t.Helper()
	if got.ID != want.ID || got.ObjectAPIName != want.ObjectAPIName || got.Label != want.Label ||
		got.FilterExpr != want.FilterExpr || got.FilterLogic != want.FilterLogic ||
		got.SortField != want.SortField || got.SortDirection != want.SortDirection ||
		!slices.Equal(got.Fields, want.Fields) || got.OwnerID == nil || want.OwnerID == nil || *got.OwnerID != *want.OwnerID ||
		got.Visibility == nil || want.Visibility == nil || *got.Visibility != *want.Visibility ||
		got.DeveloperName == nil || want.DeveloperName == nil || *got.DeveloperName != *want.DeveloperName {
		t.Fatalf("list view = %+v, want %+v", got, want)
	}
}

func listViewString(value string) *string {
	return &value
}