package persistence

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"fmt"
	"sort"
	"strings"

	"github.com/nexuscrm/shared/pkg/constants"
)

const GroupMembershipClosureLockName = "nexuscrm_group_membership_closure"

type groupMembershipDirectEdge struct {
	groupID    string
	memberID   string
	memberType string
}

type activeGroupMembershipSource struct {
	groupType  string
	userRoleID string
}

type activeUserMembershipSource struct {
	userRoleID string
}

type groupMembershipClosureRow struct {
	groupID    string
	memberID   string
	memberType string
	depth      int
}

// RebuildGroupMembershipClosureWithLock serializes a standalone full-table
// closure rebuild with runtime source-table writes and replaces the derived
// table inside one SQL transaction.
func RebuildGroupMembershipClosureWithLock(ctx context.Context, db *sql.DB) error {
	if db == nil {
		return fmt.Errorf("group membership closure rebuild requires database access")
	}

	return withGroupMembershipClosureLockedTx(ctx, db, func(txCtx context.Context, tx *sql.Tx) error {
		return RebuildGroupMembershipClosure(txCtx, tx)
	})
}

func withGroupMembershipClosureLockedTx(ctx context.Context, db *sql.DB, fn func(context.Context, *sql.Tx) error) error {
	if db == nil {
		return fmt.Errorf("group membership closure transaction requires database access")
	}

	return WithGroupMembershipClosureLock(ctx, db, func(lockCtx context.Context) error {
		tx, err := db.BeginTx(lockCtx, nil)
		if err != nil {
			return fmt.Errorf("begin group membership closure transaction: %w", err)
		}

		committed := false
		defer func() {
			if !committed {
				_ = tx.Rollback()
			}
		}()

		if err := fn(lockCtx, tx); err != nil {
			return err
		}
		if err := tx.Commit(); err != nil {
			return fmt.Errorf("commit group membership closure transaction: %w", err)
		}
		committed = true
		return nil
	})
}

// WithGroupMembershipClosureLock holds the named advisory lock for the full
// duration of fn. Use it around every standalone closure rebuild or source
// mutation transaction that can race with another full-table rebuild.
func WithGroupMembershipClosureLock(ctx context.Context, db *sql.DB, fn func(context.Context) error) error {
	if db == nil {
		return fmt.Errorf("group membership closure lock requires database access")
	}

	lockConn, err := db.Conn(ctx)
	if err != nil {
		return fmt.Errorf("acquiring group membership closure lock connection: %w", err)
	}
	defer lockConn.Close()

	var lockResult int
	if err := lockConn.QueryRowContext(ctx, "SELECT GET_LOCK(?, 60)", GroupMembershipClosureLockName).Scan(&lockResult); err != nil {
		return fmt.Errorf("acquiring group membership closure lock: %w", err)
	}
	if lockResult != 1 {
		return fmt.Errorf("acquiring group membership closure lock: result=%d", lockResult)
	}
	defer func() {
		_, _ = lockConn.ExecContext(groupMembershipClosureUnlockContext(ctx), "SELECT RELEASE_LOCK(?)", GroupMembershipClosureLockName)
	}()

	return fn(ctx)
}

func groupMembershipClosureUnlockContext(_ context.Context) context.Context {
	return context.Background()
}

// RebuildGroupMembershipClosure replaces the derived transitive GroupMember
// closure from active User, Group, and GroupMember rows. Callers pass either a
// *sql.DB or the active *sql.Tx so the rebuild lands atomically with the write
// that changed a closure source table. Standalone repair/rebuild callers must
// use RebuildGroupMembershipClosureWithLock so they do not race runtime writes.
func RebuildGroupMembershipClosure(ctx context.Context, exec Executor) error {
	if exec == nil {
		return nil
	}

	activeGroups, err := loadActiveGroups(ctx, exec)
	if err != nil {
		return fmt.Errorf("load active groups: %w", err)
	}
	if err := rejectLegacyRoleBackedGroupsMissingUserRoleID(activeGroups); err != nil {
		return err
	}
	activeUsers, err := loadActiveUsers(ctx, exec)
	if err != nil {
		return fmt.Errorf("load active users: %w", err)
	}
	roleParents, err := loadActiveRoleParents(ctx, exec)
	if err != nil {
		return fmt.Errorf("load active role hierarchy: %w", err)
	}
	directEdges, err := loadActiveGroupMemberEdges(ctx, exec, activeGroups, activeUsers)
	if err != nil {
		return fmt.Errorf("load active group members: %w", err)
	}
	directEdges = append(directEdges, buildRoleBackedGroupEdges(activeGroups, activeUsers, roleParents)...)

	closureRows := buildGroupMembershipClosure(activeGroups, directEdges)
	if _, err := exec.ExecContext(ctx, fmt.Sprintf("DELETE FROM `%s`", constants.TableGroupMembershipClosure)); err != nil {
		return fmt.Errorf("clear group membership closure: %w", err)
	}
	if len(closureRows) == 0 {
		return nil
	}
	if err := insertGroupMembershipClosureRows(ctx, exec, closureRows); err != nil {
		return fmt.Errorf("insert group membership closure: %w", err)
	}
	return nil
}

func rejectLegacyRoleBackedGroupsMissingUserRoleID(activeGroups map[string]activeGroupMembershipSource) error {
	if len(activeGroups) == 0 {
		return nil
	}

	legacyGroups := make([]string, 0)
	for groupID, group := range activeGroups {
		if !isRoleBackedGroupType(group.groupType) || strings.TrimSpace(group.userRoleID) != "" {
			continue
		}
		legacyGroups = append(legacyGroups, fmt.Sprintf("%s(%s)", groupID, strings.TrimSpace(group.groupType)))
	}
	if len(legacyGroups) == 0 {
		return nil
	}
	sort.Strings(legacyGroups)
	return fmt.Errorf(
		"legacy role-backed Group rows missing UserRoleId block group membership closure rebuild; operator repair required before rebuild: %s",
		strings.Join(legacyGroups, ", "),
	)
}

func loadActiveGroups(ctx context.Context, exec Executor) (map[string]activeGroupMembershipSource, error) {
	rows, err := exec.QueryContext(ctx,
		fmt.Sprintf(
			"SELECT `Id`, `Type`, `UserRoleId` FROM `%s` WHERE (`IsDeleted` = 0 OR `IsDeleted` IS NULL)",
			constants.TableGroup,
		),
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	groups := make(map[string]activeGroupMembershipSource)
	for rows.Next() {
		var id, groupType sql.NullString
		var userRoleID sql.NullString
		if err := rows.Scan(&id, &groupType, &userRoleID); err != nil {
			return nil, err
		}
		groupID := strings.TrimSpace(id.String)
		if groupID == "" {
			continue
		}
		groups[groupID] = activeGroupMembershipSource{
			groupType:  strings.TrimSpace(groupType.String),
			userRoleID: strings.TrimSpace(userRoleID.String),
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return groups, nil
}

func loadActiveUsers(ctx context.Context, exec Executor) (map[string]activeUserMembershipSource, error) {
	rows, err := exec.QueryContext(ctx,
		fmt.Sprintf(
			"SELECT `Id`, `UserRoleId` FROM `%s` WHERE (`IsDeleted` = 0 OR `IsDeleted` IS NULL)",
			constants.TableUser,
		),
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	users := make(map[string]activeUserMembershipSource)
	for rows.Next() {
		var id, userRoleID sql.NullString
		if err := rows.Scan(&id, &userRoleID); err != nil {
			return nil, err
		}
		userID := strings.TrimSpace(id.String)
		if userID == "" {
			continue
		}
		users[userID] = activeUserMembershipSource{
			userRoleID: strings.TrimSpace(userRoleID.String),
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return users, nil
}

func loadActiveRoleParents(ctx context.Context, exec Executor) (map[string]string, error) {
	rows, err := exec.QueryContext(ctx,
		fmt.Sprintf(
			"SELECT `Id`, `ParentRoleId` FROM `%s` WHERE (`IsDeleted` = 0 OR `IsDeleted` IS NULL)",
			constants.TableUserRole,
		),
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	roleParents := make(map[string]string)
	for rows.Next() {
		var id, parentRoleID sql.NullString
		if err := rows.Scan(&id, &parentRoleID); err != nil {
			return nil, err
		}
		roleID := strings.TrimSpace(id.String)
		if roleID == "" {
			continue
		}
		roleParents[roleID] = strings.TrimSpace(parentRoleID.String)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return roleParents, nil
}

func loadActiveGroupMemberEdges(ctx context.Context, exec Executor, activeGroups map[string]activeGroupMembershipSource, activeUsers map[string]activeUserMembershipSource) ([]groupMembershipDirectEdge, error) {
	rows, err := exec.QueryContext(ctx,
		fmt.Sprintf("SELECT `GroupId`, `UserOrGroupId` FROM `%s` WHERE (`IsDeleted` = 0 OR `IsDeleted` IS NULL)", constants.TableGroupMember),
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var edges []groupMembershipDirectEdge
	for rows.Next() {
		var groupID, memberID string
		if err := rows.Scan(&groupID, &memberID); err != nil {
			return nil, err
		}
		groupID = strings.TrimSpace(groupID)
		memberID = strings.TrimSpace(memberID)
		if groupID == "" || memberID == "" {
			continue
		}
		if _, ok := activeGroups[groupID]; !ok {
			continue
		}
		memberType := ""
		if _, ok := activeGroups[memberID]; ok {
			memberType = constants.GroupMembershipClosureMemberTypeGroup
		} else if _, ok := activeUsers[memberID]; ok {
			memberType = constants.GroupMembershipClosureMemberTypeUser
		}
		if memberType == "" {
			continue
		}
		edges = append(edges, groupMembershipDirectEdge{
			groupID:    groupID,
			memberID:   memberID,
			memberType: memberType,
		})
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return edges, nil
}

func buildRoleBackedGroupEdges(activeGroups map[string]activeGroupMembershipSource, activeUsers map[string]activeUserMembershipSource, roleParents map[string]string) []groupMembershipDirectEdge {
	usersByRole := make(map[string][]string)
	for userID, user := range activeUsers {
		if user.userRoleID == "" {
			continue
		}
		usersByRole[user.userRoleID] = append(usersByRole[user.userRoleID], userID)
	}
	for roleID := range usersByRole {
		sort.Strings(usersByRole[roleID])
	}

	roleDescendants := buildRoleDescendantIndex(roleParents)

	var edges []groupMembershipDirectEdge
	for groupID, group := range activeGroups {
		groupType := strings.TrimSpace(group.groupType)
		if !isRoleBackedGroupType(groupType) || group.userRoleID == "" {
			continue
		}

		roleIDs := []string{group.userRoleID}
		if constants.GroupTypeIncludesSubordinateRoles(groupType) {
			roleIDs = append(roleIDs, roleDescendants[group.userRoleID]...)
		}

		seenUsers := make(map[string]struct{})
		for _, roleID := range roleIDs {
			for _, userID := range usersByRole[roleID] {
				if _, seen := seenUsers[userID]; seen {
					continue
				}
				seenUsers[userID] = struct{}{}
				edges = append(edges, groupMembershipDirectEdge{
					groupID:    groupID,
					memberID:   userID,
					memberType: constants.GroupMembershipClosureMemberTypeUser,
				})
			}
		}
	}

	return edges
}

func buildRoleDescendantIndex(roleParents map[string]string) map[string][]string {
	childrenByRole := make(map[string][]string)
	for roleID, parentRoleID := range roleParents {
		parentRoleID = strings.TrimSpace(parentRoleID)
		if parentRoleID == "" {
			continue
		}
		childrenByRole[parentRoleID] = append(childrenByRole[parentRoleID], roleID)
	}
	for roleID := range childrenByRole {
		sort.Strings(childrenByRole[roleID])
	}

	descendants := make(map[string][]string)
	rootRoleIDs := make(map[string]struct{}, len(roleParents)+len(childrenByRole))
	for rootRoleID := range roleParents {
		rootRoleIDs[rootRoleID] = struct{}{}
	}
	for rootRoleID := range childrenByRole {
		rootRoleIDs[rootRoleID] = struct{}{}
	}
	for rootRoleID := range rootRoleIDs {
		queue := append([]string(nil), childrenByRole[rootRoleID]...)
		visited := make(map[string]struct{})
		for len(queue) > 0 {
			roleID := queue[0]
			queue = queue[1:]
			if _, seen := visited[roleID]; seen {
				continue
			}
			visited[roleID] = struct{}{}
			descendants[rootRoleID] = append(descendants[rootRoleID], roleID)
			queue = append(queue, childrenByRole[roleID]...)
		}
	}
	return descendants
}

func buildGroupMembershipClosure(activeGroups map[string]activeGroupMembershipSource, directEdges []groupMembershipDirectEdge) []groupMembershipClosureRow {
	adjacency := make(map[string][]groupMembershipDirectEdge)
	for _, edge := range directEdges {
		adjacency[edge.groupID] = append(adjacency[edge.groupID], edge)
	}

	groupIDs := make([]string, 0, len(activeGroups))
	for groupID := range activeGroups {
		groupIDs = append(groupIDs, groupID)
	}
	sort.Strings(groupIDs)

	var out []groupMembershipClosureRow
	for _, rootGroupID := range groupIDs {
		type queueItem struct {
			edge  groupMembershipDirectEdge
			depth int
		}
		queue := make([]queueItem, 0, len(adjacency[rootGroupID]))
		for _, edge := range adjacency[rootGroupID] {
			queue = append(queue, queueItem{edge: edge, depth: 1})
		}

		seenMembers := make(map[string]struct{})
		traversedGroups := map[string]struct{}{rootGroupID: {}}
		for len(queue) > 0 {
			item := queue[0]
			queue = queue[1:]

			memberKey := item.edge.memberType + "\x00" + item.edge.memberID
			if item.edge.memberType == constants.GroupMembershipClosureMemberTypeGroup && item.edge.memberID == rootGroupID {
				// Cycles are traversed through other groups, but a group must
				// never appear as its own member in the derived closure.
			} else if _, seen := seenMembers[memberKey]; !seen {
				seenMembers[memberKey] = struct{}{}
				out = append(out, groupMembershipClosureRow{
					groupID:    rootGroupID,
					memberID:   item.edge.memberID,
					memberType: item.edge.memberType,
					depth:      item.depth,
				})
			}

			if item.edge.memberType != constants.GroupMembershipClosureMemberTypeGroup {
				continue
			}
			if _, traversed := traversedGroups[item.edge.memberID]; traversed {
				continue
			}
			traversedGroups[item.edge.memberID] = struct{}{}
			for _, child := range adjacency[item.edge.memberID] {
				queue = append(queue, queueItem{edge: child, depth: item.depth + 1})
			}
		}
	}

	sort.Slice(out, func(i, j int) bool {
		if out[i].groupID != out[j].groupID {
			return out[i].groupID < out[j].groupID
		}
		if out[i].depth != out[j].depth {
			return out[i].depth < out[j].depth
		}
		if out[i].memberType != out[j].memberType {
			return out[i].memberType < out[j].memberType
		}
		return out[i].memberID < out[j].memberID
	})
	return out
}

func isRoleBackedGroupType(groupType string) bool {
	return constants.IsRoleBackedGroupType(groupType)
}

func insertGroupMembershipClosureRows(ctx context.Context, exec Executor, rows []groupMembershipClosureRow) error {
	const batchSize = 200
	for start := 0; start < len(rows); start += batchSize {
		end := start + batchSize
		if end > len(rows) {
			end = len(rows)
		}
		batch := rows[start:end]
		placeholders := make([]string, 0, len(batch))
		args := make([]interface{}, 0, len(batch)*5)
		for _, row := range batch {
			placeholders = append(placeholders, "(?, ?, ?, ?, ?, NOW(), NOW())")
			args = append(args,
				groupMembershipClosureID(row.groupID, row.memberType, row.memberID),
				row.groupID,
				row.memberID,
				row.memberType,
				row.depth,
			)
		}
		sql := fmt.Sprintf(
			"INSERT INTO `%s` (`Id`, `GroupId`, `MemberId`, `MemberType`, `Depth`, `CreatedDate`, `LastModifiedDate`) VALUES %s",
			constants.TableGroupMembershipClosure,
			strings.Join(placeholders, ", "),
		)
		if _, err := exec.ExecContext(ctx, sql, args...); err != nil {
			return err
		}
	}
	return nil
}

func groupMembershipClosureID(groupID, memberType, memberID string) string {
	sum := sha256.Sum256([]byte(groupID + "\x00" + memberType + "\x00" + memberID))
	return "gmc_" + hex.EncodeToString(sum[:])
}