package persistence

import (
	"context"
	"regexp"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

func TestEnsureProfilePermissionSet_InsertsOnceAcrossRepeatedCalls(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	repo := NewPermissionRepository(db)
	ctx := context.Background()

	profileID := constants.ProfileStandardUser
	profileName := constants.ProfileDisplayName(profileID)
	permSetID := "permset-standard-user"

	selectActivePattern := regexp.QuoteMeta("SELECT Id FROM PermissionSet WHERE ProfileId = ? AND IsOwnedByProfile = 1 AND IsDeleted = 0 LIMIT 1")
	selectAnyPattern := regexp.QuoteMeta("SELECT Id, IsDeleted FROM PermissionSet WHERE ProfileId = ? AND IsOwnedByProfile = 1 ORDER BY IsDeleted ASC, LastModifiedDate DESC, Id DESC LIMIT 1")
	insertPattern := regexp.QuoteMeta("INSERT IGNORE INTO PermissionSet (Id, Name, Label, ProfileId, IsOwnedByProfile, IsActive, IsCustom, CreatedDate) VALUES (?, ?, ?, ?, 1, 1, 0, ?)")

	mock.ExpectQuery(selectActivePattern).
		WithArgs(profileID).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(selectAnyPattern).
		WithArgs(profileID).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "IsDeleted"}))
	mock.ExpectExec(insertPattern).
		WithArgs(sqlmock.AnyArg(), "CRM_"+profileID, profileName+" (Profile)", profileID, sqlmock.AnyArg()).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectQuery(selectActivePattern).
		WithArgs(profileID).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(permSetID))

	firstID, err := repo.EnsureProfilePermissionSet(ctx, profileID, profileName)
	require.NoError(t, err)
	assert.Equal(t, permSetID, firstID)

	mock.ExpectQuery(selectActivePattern).
		WithArgs(profileID).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(permSetID))

	secondID, err := repo.EnsureProfilePermissionSet(ctx, profileID, profileName)
	require.NoError(t, err)
	assert.Equal(t, permSetID, secondID)

	require.NoError(t, mock.ExpectationsWereMet())
}

func TestEnsureProfilePermissionSet_ReactivatesSoftDeletedRow(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	repo := NewPermissionRepository(db)
	ctx := context.Background()

	profileID := constants.ProfileStandardUser
	profileName := constants.ProfileDisplayName(profileID)
	permSetID := "permset-soft-deleted"

	selectActivePattern := regexp.QuoteMeta("SELECT Id FROM PermissionSet WHERE ProfileId = ? AND IsOwnedByProfile = 1 AND IsDeleted = 0 LIMIT 1")
	selectAnyPattern := regexp.QuoteMeta("SELECT Id, IsDeleted FROM PermissionSet WHERE ProfileId = ? AND IsOwnedByProfile = 1 ORDER BY IsDeleted ASC, LastModifiedDate DESC, Id DESC LIMIT 1")
	reactivatePattern := regexp.QuoteMeta("UPDATE PermissionSet SET IsDeleted = 0, IsActive = 1, LastModifiedDate = ? WHERE Id = ?")

	mock.ExpectQuery(selectActivePattern).
		WithArgs(profileID).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(selectAnyPattern).
		WithArgs(profileID).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "IsDeleted"}).AddRow(permSetID, 1))
	mock.ExpectExec(reactivatePattern).
		WithArgs(sqlmock.AnyArg(), permSetID).
		WillReturnResult(sqlmock.NewResult(1, 1))

	id, err := repo.EnsureProfilePermissionSet(ctx, profileID, profileName)
	require.NoError(t, err)
	assert.Equal(t, permSetID, id)
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestBulkEnsureProfilePermissionSets_ReactivatesDeletedAndReturnsActiveIDs(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	repo := NewPermissionRepository(db)
	ctx := context.Background()

	profiles := []struct{ ID, Name string }{
		{ID: constants.ProfileSystemAdmin, Name: constants.ProfileDisplayName(constants.ProfileSystemAdmin)},
		{ID: constants.ProfileStandardUser, Name: constants.ProfileDisplayName(constants.ProfileStandardUser)},
	}

	selectAllPattern := regexp.QuoteMeta("SELECT `ProfileId`, `Id`, `IsDeleted` FROM `PermissionSet` WHERE `ProfileId` IN (?,?) AND `IsOwnedByProfile` = 1 ORDER BY `ProfileId` ASC, `IsDeleted` ASC, `LastModifiedDate` DESC, `Id` DESC")
	reactivatePattern := regexp.QuoteMeta("UPDATE `PermissionSet` SET `IsDeleted` = 0, `IsActive` = 1, `LastModifiedDate` = ? WHERE `Id` IN (?)")

	mock.ExpectQuery(selectAllPattern).
		WithArgs(profiles[0].ID, profiles[1].ID).
		WillReturnRows(
			sqlmock.NewRows([]string{"ProfileId", "Id", "IsDeleted"}).
				AddRow(profiles[0].ID, "ps-admin-active", 0).
				AddRow(profiles[1].ID, "ps-standard-deleted", 1),
		)
	mock.ExpectExec(reactivatePattern).
		WithArgs(sqlmock.AnyArg(), "ps-standard-deleted").
		WillReturnResult(sqlmock.NewResult(1, 1))
	result, err := repo.BulkEnsureProfilePermissionSets(ctx, profiles)
	require.NoError(t, err)
	assert.Equal(t, "ps-admin-active", result[profiles[0].ID])
	assert.Equal(t, "ps-standard-deleted", result[profiles[1].ID])
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestCountObjectPermissionsByProfile_UsesOwnedPermissionSetSubquery(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	repo := NewPermissionRepository(db)
	ctx := context.Background()

	queryPattern := regexp.QuoteMeta("SELECT COUNT(*) FROM `ObjectPermissions` WHERE `ParentId` IN (SELECT `Id` FROM `PermissionSet` WHERE `ProfileId` = ? AND `IsOwnedByProfile` = 1)")
	mock.ExpectQuery(queryPattern).
		WithArgs(constants.ProfileStandardUser).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(2))

	count, err := repo.CountObjectPermissionsByProfile(ctx, constants.ProfileStandardUser)
	require.NoError(t, err)
	assert.Equal(t, 2, count)

	require.NoError(t, mock.ExpectationsWereMet())
}

func TestBulkCountObjectPermissionsByProfilesAndTypes_FiltersByObjectAPIName(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	repo := NewPermissionRepository(db)
	ctx := context.Background()

	queryPattern := regexp.QuoteMeta("SELECT ps.`ProfileId`, COUNT(*) FROM `ObjectPermissions` op JOIN `PermissionSet` ps ON op.`ParentId` = ps.`Id` AND ps.`IsOwnedByProfile` = 1 WHERE ps.`ProfileId` IN (?,?) AND op.`IsDeleted` = 0 AND op.`SobjectType` IN (?,?) GROUP BY ps.`ProfileId`")
	mock.ExpectQuery(queryPattern).
		WithArgs(constants.ProfileSystemAdmin, constants.ProfileStandardUser, "Account", "Quote").
		WillReturnRows(sqlmock.NewRows([]string{"ProfileId", "COUNT(*)"}).
			AddRow(constants.ProfileSystemAdmin, 2).
			AddRow(constants.ProfileStandardUser, 1))

	counts, err := repo.BulkCountObjectPermissionsByProfilesAndTypes(ctx, []string{constants.ProfileSystemAdmin, constants.ProfileStandardUser}, []string{"Account", "Quote"})
	require.NoError(t, err)
	assert.Equal(t, 2, counts[constants.ProfileSystemAdmin])
	assert.Equal(t, 1, counts[constants.ProfileStandardUser])
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestBulkCountFieldPermissionsByProfilesAndTypes_FiltersByObjectAPIName(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	repo := NewPermissionRepository(db)
	ctx := context.Background()

	queryPattern := regexp.QuoteMeta("SELECT ps.`ProfileId`, COUNT(*) FROM `FieldPermissions` fp JOIN `PermissionSet` ps ON fp.`ParentId` = ps.`Id` AND ps.`IsOwnedByProfile` = 1 WHERE ps.`ProfileId` IN (?,?) AND fp.`IsDeleted` = 0 AND fp.`SobjectType` IN (?,?) GROUP BY ps.`ProfileId`")
	mock.ExpectQuery(queryPattern).
		WithArgs(constants.ProfileSystemAdmin, constants.ProfileStandardUser, "Account", "Quote").
		WillReturnRows(sqlmock.NewRows([]string{"ProfileId", "COUNT(*)"}).
			AddRow(constants.ProfileSystemAdmin, 8).
			AddRow(constants.ProfileStandardUser, 6))

	counts, err := repo.BulkCountFieldPermissionsByProfilesAndTypes(ctx, []string{constants.ProfileSystemAdmin, constants.ProfileStandardUser}, []string{"Account", "Quote"})
	require.NoError(t, err)
	assert.Equal(t, 8, counts[constants.ProfileSystemAdmin])
	assert.Equal(t, 6, counts[constants.ProfileStandardUser])
	require.NoError(t, mock.ExpectationsWereMet())
}