package repository

import (
	"authorization/db"
	"database/sql"
	"errors"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
)

func setupMockDB(t *testing.T) (sqlmock.Sqlmock, func()) {
	mockDB, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("Failed to create mock database: %v", err)
	}

	// Store original DB and replace with mock
	originalDB := db.DB
	db.DB = mockDB

	cleanup := func() {
		db.DB = originalDB
		mockDB.Close()
	}

	return mock, cleanup
}

func TestGetPolicyAttributesByPermissionSuccess(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"id", "attribute_name", "attribute_type", "comparison", "attribute_value", "permission_id"}).
		AddRow(1, "department", "user", "=", "engineering", 1).
		AddRow(2, "level", "user", ">=", "5", 1)

	mock.ExpectQuery("SELECT id, attribute_name, attribute_type, comparison, attribute_value, permission_id FROM uess_user_management.policy_attributes WHERE permission_id = ?").
		WithArgs(1).
		WillReturnRows(rows)

	attrs, err := GetPolicyAttributesByPermission(1)

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(attrs) != 2 {
		t.Errorf("Expected 2 attributes, got %d", len(attrs))
	}
	if attrs[0].AttributeName != "department" {
		t.Errorf("Expected attribute name 'department', got '%s'", attrs[0].AttributeName)
	}
}

func TestGetPolicyAttributesByPermissionEmpty(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"id", "attribute_name", "attribute_type", "comparison", "attribute_value", "permission_id"})

	mock.ExpectQuery("SELECT id, attribute_name, attribute_type, comparison, attribute_value, permission_id FROM policy_attributes WHERE permission_id = \\?").
		WithArgs(999).
		WillReturnRows(rows)

	attrs, err := GetPolicyAttributesByPermission(999)

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(attrs) != 0 {
		t.Errorf("Expected 0 attributes, got %d", len(attrs))
	}
}

func TestGetUserAttributesSuccess(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"attribute_name", "attribute_value"}).
		AddRow("department", "engineering").
		AddRow("level", "5")

	mock.ExpectQuery("SELECT attribute_name, attribute_value FROM user_attributes WHERE users_id = \\?").
		WithArgs("user123").
		WillReturnRows(rows)

	attrs, err := GetUserAttributes("user123")

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(attrs) != 2 {
		t.Errorf("Expected 2 attributes, got %d", len(attrs))
	}
	if attrs["department"] != "engineering" {
		t.Errorf("Expected department 'engineering', got '%s'", attrs["department"])
	}
	if attrs["level"] != "5" {
		t.Errorf("Expected level '5', got '%s'", attrs["level"])
	}
}

func TestGetUserByIDSuccess(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"users_id", "email_address", "role_id", "is_deleted"}).
		AddRow("user123", "john@example.com", 7, "0")

	mock.ExpectQuery(`SELECT u.users_id, u.email_address, COALESCE\(MIN\(ur.role_id\), 0\) AS role_id, u.is_deleted`).
		WithArgs("user123").
		WillReturnRows(rows)

	user, err := GetUserByID("user123")

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if user == nil {
		t.Fatal("Expected user, got nil")
	}
	if user.UsersID != "user123" {
		t.Errorf("Expected UsersID 'user123', got '%s'", user.UsersID)
	}
	if user.EmailAddress != "john@example.com" {
		t.Errorf("Expected EmailAddress 'john@example.com', got '%s'", user.EmailAddress)
	}
	if user.RoleID != 7 {
		t.Errorf("Expected RoleID 7, got %d", user.RoleID)
	}
}

func TestGetUserByIDNotFound(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	mock.ExpectQuery(`SELECT u.users_id, u.email_address, COALESCE\(MIN\(ur.role_id\), 0\) AS role_id, u.is_deleted`).
		WithArgs("nonexistent").
		WillReturnError(sql.ErrNoRows)

	user, err := GetUserByID("nonexistent")

	if err == nil {
		t.Error("Expected error for non-existent user")
	}
	if user != nil {
		t.Error("Expected nil user")
	}
}

func TestGetUserByIDDeletedUserFilteredOut(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"users_id", "email_address", "role_id", "is_deleted"}).
		AddRow("deleted-user", "deleted@example.com", 3, "1")

	mock.ExpectQuery(`SELECT u.users_id, u.email_address, COALESCE\(MIN\(ur.role_id\), 0\) AS role_id, u.is_deleted`).
		WithArgs("deleted-user").
		WillReturnRows(rows)

	user, err := GetUserByID("deleted-user")

	if !errors.Is(err, ErrUserDeleted) {
		t.Errorf("Expected ErrUserDeleted, got %v", err)
	}
	if user != nil {
		t.Error("Expected nil user for deleted user")
	}
}

func TestGetAllPermissionsSuccess(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"id", "permission_name", "description", "resource", "action"}).
		AddRow(1, "read_document", "Read document", "document", "read").
		AddRow(2, "write_document", "Write document", "document", "write")

	mock.ExpectQuery("SELECT id, permission_name, description, resource, action FROM permissions ORDER BY id").
		WillReturnRows(rows)

	perms, err := GetAllPermissions()

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(perms) != 2 {
		t.Errorf("Expected 2 permissions, got %d", len(perms))
	}
}

func TestGetAllPolicyAttributesSuccess(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"policy_attributes_id", "attribute_name", "attribute_type", "comparison", "attribute_value", "permission_id"}).
		AddRow(1, "department", "user", "=", "engineering", 1).
		AddRow(2, "level", "user", ">=", "5", 1).
		AddRow(3, "role", "user", "=", "admin", 2)

	mock.ExpectQuery("SELECT policy_attributes_id, attribute_name, attribute_type, comparison, attribute_value, permission_id FROM policy_attributes ORDER BY permission_id, policy_attributes_id").
		WillReturnRows(rows)

	attrs, err := GetAllPolicyAttributes()

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(attrs) != 2 {
		t.Errorf("Expected 2 permission groups, got %d", len(attrs))
	}
	if len(attrs[1]) != 2 {
		t.Errorf("Expected 2 attributes for permission 1, got %d", len(attrs[1]))
	}
	if len(attrs[2]) != 1 {
		t.Errorf("Expected 1 attribute for permission 2, got %d", len(attrs[2]))
	}
}

func TestGetAllPolicyAttributesEmpty(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"policy_attributes_id", "attribute_name", "attribute_type", "comparison", "attribute_value", "permission_id"})

	mock.ExpectQuery("SELECT policy_attributes_id, attribute_name, attribute_type, comparison, attribute_value, permission_id FROM policy_attributes ORDER BY permission_id, policy_attributes_id").
		WillReturnRows(rows)

	attrs, err := GetAllPolicyAttributes()

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(attrs) != 0 {
		t.Errorf("Expected 0 permission groups, got %d", len(attrs))
	}
}

func TestGetPolicyAttributesByPermissionInvalidID(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"id", "attribute_name", "attribute_type", "comparison", "attribute_value", "permission_id"})

	// Match the actual query with proper whitespace handling
	mock.ExpectQuery(`SELECT id, attribute_name, attribute_type, comparison, attribute_value, permission_id\s+FROM policy_attributes\s+WHERE permission_id = \?`).
		WithArgs(-1).
		WillReturnRows(rows)

	attrs, err := GetPolicyAttributesByPermission(-1)

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(attrs) != 0 {
		t.Errorf("Expected 0 attributes for invalid ID, got %d", len(attrs))
	}
}

func TestGetPolicyAttributesByPermissionDatabaseError(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	mock.ExpectQuery(`SELECT id, attribute_name, attribute_type, comparison, attribute_value, permission_id\s+FROM policy_attributes\s+WHERE permission_id = \?`).
		WithArgs(1).
		WillReturnError(errors.New("database error"))

	attrs, err := GetPolicyAttributesByPermission(1)

	if err == nil {
		t.Error("Expected error, got nil")
	}
	if attrs != nil {
		t.Error("Expected nil attributes on error")
	}
}

func TestGetUserAttributesEmptyUserID(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"attribute_name", "attribute_value"})

	// Match the actual query format
	mock.ExpectQuery(`SELECT attribute_name, attribute_value\s+FROM user_attributes\s+WHERE users_id = \?`).
		WithArgs("").
		WillReturnRows(rows)

	attrs, err := GetUserAttributes("")

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(attrs) != 0 {
		t.Errorf("Expected 0 attributes for empty user ID, got %d", len(attrs))
	}
}

func TestGetUserAttributesMultipleAttributes(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"attribute_name", "attribute_value"}).
		AddRow("department", "IT").
		AddRow("level", "5").
		AddRow("location", "US").
		AddRow("clearance", "high")

	// Match the actual query
	mock.ExpectQuery(`SELECT attribute_name, attribute_value\s+FROM user_attributes\s+WHERE users_id = \?`).
		WithArgs("user123").
		WillReturnRows(rows)

	attrs, err := GetUserAttributes("user123")

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}

	if len(attrs) != 4 {
		t.Errorf("Expected 4 attributes, got %d", len(attrs))
	}
}

func TestGetUserByIDEmptyID(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"users_id", "email_address"})

	mock.ExpectQuery(`SELECT u.users_id, u.email_address, COALESCE\(MIN\(ur.role_id\), 0\) AS role_id, u.is_deleted`).
		WithArgs("").
		WillReturnRows(rows)

	user, err := GetUserByID("")

	// Should get an error (empty ID returns error from function logic)
	if err == nil {
		t.Error("Expected error for empty ID, got nil")
	}
	if user != nil {
		t.Error("Expected nil user for empty ID")
	}
}

func TestGetUserByIDDatabaseError(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	mock.ExpectQuery(`SELECT u.users_id, u.email_address, COALESCE\(MIN\(ur.role_id\), 0\) AS role_id, u.is_deleted`).
		WithArgs("user123").
		WillReturnError(errors.New("database connection failed"))

	user, err := GetUserByID("user123")

	if err == nil {
		t.Error("Expected error, got nil")
	}
	if user != nil {
		t.Error("Expected nil user on error")
	}
}

func TestGetAllPermissionsDatabaseError(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	mock.ExpectQuery("SELECT id, permission_name, description, resource, action FROM permissions ORDER BY id").
		WillReturnError(errors.New("database error"))

	perms, err := GetAllPermissions()

	if err == nil {
		t.Error("Expected error, got nil")
	}
	if perms != nil {
		t.Error("Expected nil permissions on error")
	}
}

func TestGetAllPermissionsEmpty(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"id", "permission_name", "description", "resource", "action"})

	mock.ExpectQuery("SELECT id, permission_name, description, resource, action FROM permissions ORDER BY id").
		WillReturnRows(rows)

	perms, err := GetAllPermissions()

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(perms) != 0 {
		t.Errorf("Expected 0 permissions, got %d", len(perms))
	}
}

func TestGetAllPermissionsLargeDataset(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"id", "permission_name", "description", "resource", "action"})
	for i := 1; i <= 1000; i++ {
		rows.AddRow(i, "perm"+string(rune(i)), "description", "resource", "action")
	}

	mock.ExpectQuery("SELECT id, permission_name, description, resource, action FROM permissions ORDER BY id").
		WillReturnRows(rows)

	perms, err := GetAllPermissions()

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(perms) != 1000 {
		t.Errorf("Expected 1000 permissions, got %d", len(perms))
	}
}

func TestGetAllPolicyAttributesDatabaseError(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	mock.ExpectQuery("SELECT policy_attributes_id, attribute_name, attribute_type, comparison, attribute_value, permission_id FROM policy_attributes ORDER BY permission_id, policy_attributes_id").
		WillReturnError(errors.New("connection lost"))

	attrs, err := GetAllPolicyAttributes()

	if err == nil {
		t.Error("Expected error, got nil")
	}
	if attrs != nil {
		t.Error("Expected nil attributes on error")
	}
}

func TestGetAllPolicyAttributesManyPermissions(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	rows := sqlmock.NewRows([]string{"policy_attributes_id", "attribute_name", "attribute_type", "comparison", "attribute_value", "permission_id"})

	// Add attributes for multiple permissions
	for permID := 1; permID <= 50; permID++ {
		for attrID := 1; attrID <= 3; attrID++ {
			rows.AddRow(attrID, "attr", "string", "equals", "value", permID)
		}
	}

	mock.ExpectQuery("SELECT policy_attributes_id, attribute_name, attribute_type, comparison, attribute_value, permission_id FROM policy_attributes ORDER BY permission_id, policy_attributes_id").
		WillReturnRows(rows)

	attrs, err := GetAllPolicyAttributes()

	if err != nil {
		t.Errorf("Expected no error, got %v", err)
	}
	if len(attrs) != 50 {
		t.Errorf("Expected 50 permission groups, got %d", len(attrs))
	}

	// Check that each permission has 3 attributes
	for permID := 1; permID <= 50; permID++ {
		if len(attrs[permID]) != 3 {
			t.Errorf("Expected 3 attributes for permission %d, got %d", permID, len(attrs[permID]))
		}
	}
}

func TestGetUserAttributesDatabaseError(t *testing.T) {
	mock, cleanup := setupMockDB(t)
	defer cleanup()

	mock.ExpectQuery("SELECT attribute_name, attribute_value FROM user_attributes WHERE users_id = \\?").
		WithArgs("user123").
		WillReturnError(errors.New("timeout"))

	attrs, err := GetUserAttributes("user123")

	if err == nil {
		t.Error("Expected error, got nil")
	}
	if attrs != nil {
		t.Error("Expected nil attributes on error")
	}
}