import {
  DataSource,
  EntityManager,
  In
} from 'typeorm';
import {
  ReactionEntity,
  RoomChannelEntity,
  RoomMemberEntity,
  RoomRoleEntity,
  RoomUserRoleEntity,
  RoomChannelPermissionEntity
} from '../entities';
import {
  AccessRolePayload,
  ChannelPermissionPayload,
  PermissionStatePayload,
  ReactionPayload,
  RoleAssignmentPayload,
  RoomPayload,
  RoomPermissionKeyPayload
} from './types';

type ChannelType = 'text' | 'voice';
type RoomMemberRole = 'host' | 'admin' | 'moderator' | 'member';

interface LegacyRoomPermissions {
  adminsManageRooms?: boolean;
  moderatorsManageRooms?: boolean;
  adminsManageIcon?: boolean;
  moderatorsManageIcon?: boolean;
  allowVoice?: boolean;
  allowScreenShare?: boolean;
  allowFileUploads?: boolean;
  slowModeInterval?: number;
}

const ROOM_PERMISSION_KEYS: RoomPermissionKeyPayload[] = [
  'manageServer',
  'manageRoles',
  'manageChannels',
  'manageIcon',
  'kickMembers',
  'banMembers',
  'manageBans',
  'deleteMessages',
  'joinVoice',
  'shareScreen',
  'uploadFiles'
];
const SYSTEM_ROLE_IDS = {
  everyone: 'system-everyone',
  moderator: 'system-moderator',
  admin: 'system-admin'
} as const;

export interface RoomChannelRecord {
  id: string;
  name: string;
  type: ChannelType;
  position: number;
}

export interface RoomMemberRecord {
  id: string;
  oderId?: string;
  username: string;
  displayName: string;
  avatarUrl?: string;
  role: RoomMemberRole;
  roleIds?: string[];
  joinedAt: number;
  lastSeenAt: number;
}

export type RoomRoleRecord = AccessRolePayload;

export type RoomRoleAssignmentRecord = RoleAssignmentPayload;

export type RoomChannelPermissionRecord = ChannelPermissionPayload;

interface RoomRelationRecord {
  channels: RoomChannelRecord[];
  members: RoomMemberRecord[];
  roles: RoomRoleRecord[];
  roleAssignments: RoomRoleAssignmentRecord[];
  channelPermissions: RoomChannelPermissionRecord[];
}

function isFiniteNumber(value: unknown): value is number {
  return typeof value === 'number' && Number.isFinite(value);
}

function normalizeChannelName(name: string): string {
  return name.trim().replace(/\s+/g, ' ');
}

function channelNameKey(type: ChannelType, name: string): string {
  return `${type}:${normalizeChannelName(name).toLocaleLowerCase()}`;
}

function memberKey(member: { id?: string; oderId?: string }): string {
  return member.oderId?.trim() || member.id?.trim() || '';
}

function compareText(firstValue: string, secondValue: string): number {
  return firstValue.localeCompare(secondValue, undefined, { sensitivity: 'base' });
}

function uniqueStrings(values: readonly string[] | undefined): string[] {
  return Array.from(new Set((values ?? [])
    .filter((value): value is string => typeof value === 'string' && value.trim().length > 0)
    .map((value) => value.trim())));
}

function trimmedString(record: Record<string, unknown>, key: string): string {
  return typeof record[key] === 'string' ? record[key].trim() : '';
}

function resolveRoomMemberTimes(rawMember: Record<string, unknown>, now: number): Pick<RoomMemberRecord, 'joinedAt' | 'lastSeenAt'> {
  const lastSeenAt = isFiniteNumber(rawMember['lastSeenAt'])
    ? rawMember['lastSeenAt']
    : isFiniteNumber(rawMember['joinedAt'])
      ? rawMember['joinedAt']
      : now;

  return {
    joinedAt: isFiniteNumber(rawMember['joinedAt']) ? rawMember['joinedAt'] : lastSeenAt,
    lastSeenAt
  };
}

function normalizePermissionState(value: unknown): PermissionStatePayload {
  return value === 'allow' || value === 'deny' || value === 'inherit'
    ? value
    : 'inherit';
}

function normalizePermissionMatrix(rawMatrix: unknown): Partial<Record<RoomPermissionKeyPayload, PermissionStatePayload>> {
  const matrix = rawMatrix && typeof rawMatrix === 'object'
    ? rawMatrix as Record<string, unknown>
    : {};
  const normalized: Partial<Record<RoomPermissionKeyPayload, PermissionStatePayload>> = {};

  for (const key of ROOM_PERMISSION_KEYS) {
    const value = normalizePermissionState(matrix[key]);

    if (value !== 'inherit') {
      normalized[key] = value;
    }
  }

  return normalized;
}

function fallbackDisplayName(member: Partial<RoomMemberRecord>): string {
  return member.displayName || member.username || member.oderId || member.id || 'User';
}

function fallbackUsername(member: Partial<RoomMemberRecord>): string {
  const base = fallbackDisplayName(member)
    .trim()
    .toLowerCase()
    .replace(/\s+/g, '_');

  return base || member.oderId || member.id || 'user';
}

function normalizeRoomMemberRole(value: unknown): RoomMemberRole {
  return value === 'host' || value === 'admin' || value === 'moderator' || value === 'member'
    ? value
    : 'member';
}

function mergeRoomMemberRole(
  existingRole: RoomMemberRole,
  incomingRole: RoomMemberRole,
  preferIncoming: boolean
): RoomMemberRole {
  if (existingRole === incomingRole) {
    return existingRole;
  }

  if (incomingRole === 'member' && existingRole !== 'member') {
    return existingRole;
  }

  if (existingRole === 'member' && incomingRole !== 'member') {
    return incomingRole;
  }

  return preferIncoming ? incomingRole : existingRole;
}

function compareRoomMembers(firstMember: RoomMemberRecord, secondMember: RoomMemberRecord): number {
  const displayNameCompare = compareText(firstMember.displayName, secondMember.displayName);

  if (displayNameCompare !== 0) {
    return displayNameCompare;
  }

  return compareText(memberKey(firstMember), memberKey(secondMember));
}

function compareRoles(firstRole: RoomRoleRecord, secondRole: RoomRoleRecord): number {
  if (firstRole.position !== secondRole.position) {
    return firstRole.position - secondRole.position;
  }

  return compareText(firstRole.name, secondRole.name);
}

function compareAssignments(firstAssignment: RoomRoleAssignmentRecord, secondAssignment: RoomRoleAssignmentRecord): number {
  return compareText(firstAssignment.oderId || firstAssignment.userId, secondAssignment.oderId || secondAssignment.userId);
}

function parseLegacyPermissions(rawPermissions: unknown): LegacyRoomPermissions {
  if (!rawPermissions || typeof rawPermissions !== 'object') {
    return {};
  }

  const permissions = rawPermissions as Record<string, unknown>;

  return {
    adminsManageRooms: permissions['adminsManageRooms'] === true,
    moderatorsManageRooms: permissions['moderatorsManageRooms'] === true,
    adminsManageIcon: permissions['adminsManageIcon'] === true,
    moderatorsManageIcon: permissions['moderatorsManageIcon'] === true,
    allowVoice: permissions['allowVoice'] !== false,
    allowScreenShare: permissions['allowScreenShare'] !== false,
    allowFileUploads: permissions['allowFileUploads'] !== false,
    slowModeInterval: isFiniteNumber(permissions['slowModeInterval']) ? permissions['slowModeInterval'] : 0
  };
}

function buildDefaultRoomRoles(legacyPermissions: LegacyRoomPermissions): RoomRoleRecord[] {
  return [
    {
      id: SYSTEM_ROLE_IDS.everyone,
      name: '@everyone',
      color: '#6b7280',
      position: 0,
      isSystem: true,
      permissions: {
        joinVoice: legacyPermissions.allowVoice === false ? 'deny' : 'allow',
        shareScreen: legacyPermissions.allowScreenShare === false ? 'deny' : 'allow',
        uploadFiles: legacyPermissions.allowFileUploads === false ? 'deny' : 'allow'
      }
    },
    {
      id: SYSTEM_ROLE_IDS.moderator,
      name: 'Moderator',
      color: '#10b981',
      position: 200,
      isSystem: true,
      permissions: {
        kickMembers: 'allow',
        deleteMessages: 'allow',
        manageChannels: legacyPermissions.moderatorsManageRooms ? 'allow' : 'inherit',
        manageIcon: legacyPermissions.moderatorsManageIcon ? 'allow' : 'inherit'
      }
    },
    {
      id: SYSTEM_ROLE_IDS.admin,
      name: 'Admin',
      color: '#60a5fa',
      position: 300,
      isSystem: true,
      permissions: {
        kickMembers: 'allow',
        banMembers: 'allow',
        manageBans: 'allow',
        deleteMessages: 'allow',
        manageChannels: legacyPermissions.adminsManageRooms ? 'allow' : 'inherit',
        manageIcon: legacyPermissions.adminsManageIcon ? 'allow' : 'inherit'
      }
    }
  ];
}

function normalizeRoomRole(rawRole: Partial<RoomRoleRecord>, fallbackRole?: RoomRoleRecord): RoomRoleRecord | null {
  const id = typeof rawRole.id === 'string' ? rawRole.id.trim() : fallbackRole?.id ?? '';
  const name = typeof rawRole.name === 'string' ? rawRole.name.trim().replace(/\s+/g, ' ') : fallbackRole?.name ?? '';

  if (!id || !name) {
    return null;
  }

  return {
    id,
    name,
    color: typeof rawRole.color === 'string' && rawRole.color.trim() ? rawRole.color.trim() : fallbackRole?.color,
    position: isFiniteNumber(rawRole.position) ? rawRole.position : fallbackRole?.position ?? 0,
    isSystem: typeof rawRole.isSystem === 'boolean' ? rawRole.isSystem : fallbackRole?.isSystem,
    permissions: normalizePermissionMatrix(rawRole.permissions ?? fallbackRole?.permissions)
  };
}

export function normalizeRoomRoles(rawRoles: unknown, rawPermissions: unknown): RoomRoleRecord[] {
  const defaults = buildDefaultRoomRoles(parseLegacyPermissions(rawPermissions));
  const rolesById = new Map<string, RoomRoleRecord>();

  if (Array.isArray(rawRoles)) {
    for (const rawRole of rawRoles) {
      if (!rawRole || typeof rawRole !== 'object') {
        continue;
      }

      const normalizedRole = normalizeRoomRole(rawRole as Record<string, unknown>);

      if (normalizedRole) {
        rolesById.set(normalizedRole.id, normalizedRole);
      }
    }
  }

  for (const defaultRole of defaults) {
    const mergedRole = normalizeRoomRole(rolesById.get(defaultRole.id) ?? defaultRole, defaultRole) ?? defaultRole;

    rolesById.set(defaultRole.id, mergedRole);
  }

  return Array.from(rolesById.values()).sort(compareRoles);
}

function normalizeRoomMember(rawMember: Record<string, unknown>, now: number): RoomMemberRecord | null {
  const normalizedId = trimmedString(rawMember, 'id');
  const normalizedOderId = trimmedString(rawMember, 'oderId');
  const normalizedKey = normalizedOderId || normalizedId;

  if (!normalizedKey) {
    return null;
  }

  const { joinedAt, lastSeenAt } = resolveRoomMemberTimes(rawMember, now);
  const username = trimmedString(rawMember, 'username');
  const displayName = trimmedString(rawMember, 'displayName');
  const avatarUrl = trimmedString(rawMember, 'avatarUrl');

  return {
    id: normalizedId || normalizedKey,
    oderId: normalizedOderId || undefined,
    username: username || fallbackUsername({ id: normalizedId || normalizedKey, oderId: normalizedOderId || undefined, displayName }),
    displayName: displayName || fallbackDisplayName({ id: normalizedId || normalizedKey, oderId: normalizedOderId || undefined, username }),
    avatarUrl: avatarUrl || undefined,
    role: normalizeRoomMemberRole(rawMember['role']),
    roleIds: uniqueStrings(Array.isArray(rawMember['roleIds']) ? rawMember['roleIds'] as string[] : undefined),
    joinedAt,
    lastSeenAt
  };
}

function mergeRoomMembers(existingMember: RoomMemberRecord | undefined, incomingMember: RoomMemberRecord): RoomMemberRecord {
  if (!existingMember) {
    return incomingMember;
  }

  const preferIncoming = incomingMember.lastSeenAt >= existingMember.lastSeenAt;

  return {
    id: existingMember.id || incomingMember.id,
    oderId: incomingMember.oderId || existingMember.oderId,
    username: preferIncoming
      ? (incomingMember.username || existingMember.username)
      : (existingMember.username || incomingMember.username),
    displayName: preferIncoming
      ? (incomingMember.displayName || existingMember.displayName)
      : (existingMember.displayName || incomingMember.displayName),
    avatarUrl: preferIncoming
      ? (incomingMember.avatarUrl || existingMember.avatarUrl)
      : (existingMember.avatarUrl || incomingMember.avatarUrl),
    role: mergeRoomMemberRole(existingMember.role, incomingMember.role, preferIncoming),
    roleIds: preferIncoming
      ? (incomingMember.roleIds || existingMember.roleIds)
      : (existingMember.roleIds || incomingMember.roleIds),
    joinedAt: Math.min(existingMember.joinedAt, incomingMember.joinedAt),
    lastSeenAt: Math.max(existingMember.lastSeenAt, incomingMember.lastSeenAt)
  };
}

export function normalizeRoomMembers(rawMembers: unknown, now: number = Date.now()): RoomMemberRecord[] {
  if (!Array.isArray(rawMembers)) {
    return [];
  }

  const membersByKey = new Map<string, RoomMemberRecord>();

  for (const rawMember of rawMembers) {
    if (!rawMember || typeof rawMember !== 'object') {
      continue;
    }

    const member = normalizeRoomMember(rawMember as Record<string, unknown>, now);

    if (!member) {
      continue;
    }

    const key = memberKey(member);

    membersByKey.set(key, mergeRoomMembers(membersByKey.get(key), member));
  }

  return Array.from(membersByKey.values()).sort(compareRoomMembers);
}

function buildRoleAssignmentsFromMembers(members: readonly RoomMemberRecord[], roles: readonly RoomRoleRecord[]): RoomRoleAssignmentRecord[] {
  const validRoleIds = new Set(roles.map((role) => role.id).filter((roleId) => roleId !== SYSTEM_ROLE_IDS.everyone));

  return members.flatMap((member) => {
    const roleIds = uniqueStrings(member.roleIds)
      .filter((roleId) => validRoleIds.has(roleId));
    const fallbackRoleIds = roleIds.length > 0
      ? roleIds
      : member.role === 'admin'
        ? [SYSTEM_ROLE_IDS.admin]
        : member.role === 'moderator'
          ? [SYSTEM_ROLE_IDS.moderator]
          : [];

    if (fallbackRoleIds.length === 0) {
      return [];
    }

    return [
      {
        userId: member.id,
        oderId: member.oderId,
        roleIds: fallbackRoleIds
      }
    ];
  }).sort(compareAssignments);
}

export function normalizeRoomRoleAssignments(
  rawAssignments: unknown,
  members: readonly RoomMemberRecord[],
  roles: readonly RoomRoleRecord[]
): RoomRoleAssignmentRecord[] {
  const validRoleIds = new Set(roles.map((role) => role.id).filter((roleId) => roleId !== SYSTEM_ROLE_IDS.everyone));
  const assignmentsByKey = new Map<string, RoomRoleAssignmentRecord>();

  if (Array.isArray(rawAssignments)) {
    for (const rawAssignment of rawAssignments) {
      if (!rawAssignment || typeof rawAssignment !== 'object') {
        continue;
      }

      const assignment = rawAssignment as Record<string, unknown>;
      const userId = typeof assignment['userId'] === 'string' ? assignment['userId'].trim() : '';
      const oderId = typeof assignment['oderId'] === 'string' ? assignment['oderId'].trim() : undefined;
      const key = oderId || userId;

      if (!key) {
        continue;
      }

      const roleIds = uniqueStrings(Array.isArray(assignment['roleIds']) ? assignment['roleIds'] as string[] : undefined)
        .filter((roleId) => validRoleIds.has(roleId));

      if (roleIds.length === 0) {
        continue;
      }

      assignmentsByKey.set(key, {
        userId: userId || key,
        oderId,
        roleIds
      });
    }
  }

  if (assignmentsByKey.size === 0) {
    return buildRoleAssignmentsFromMembers(members, roles);
  }

  return Array.from(assignmentsByKey.values()).sort(compareAssignments);
}

export function normalizeRoomChannelPermissions(
  rawChannelPermissions: unknown,
  roles: readonly RoomRoleRecord[]
): RoomChannelPermissionRecord[] {
  if (!Array.isArray(rawChannelPermissions)) {
    return [];
  }

  const validRoleIds = new Set(roles.map((role) => role.id));
  const overridesByKey = new Map<string, RoomChannelPermissionRecord>();

  for (const rawOverride of rawChannelPermissions) {
    if (!rawOverride || typeof rawOverride !== 'object') {
      continue;
    }

    const override = rawOverride as Record<string, unknown>;
    const channelId = typeof override['channelId'] === 'string' ? override['channelId'].trim() : '';
    const targetType = override['targetType'] === 'role' || override['targetType'] === 'user' ? override['targetType'] : null;
    const targetId = typeof override['targetId'] === 'string' ? override['targetId'].trim() : '';
    const permission = ROOM_PERMISSION_KEYS.find((key) => key === override['permission']);
    const value = normalizePermissionState(override['value']);

    if (!channelId || !targetType || !targetId || !permission || value === 'inherit') {
      continue;
    }

    if (targetType === 'role' && !validRoleIds.has(targetId)) {
      continue;
    }

    const key = `${channelId}:${targetType}:${targetId}:${permission}`;

    overridesByKey.set(key, {
      channelId,
      targetType,
      targetId,
      permission,
      value
    });
  }

  return Array.from(overridesByKey.values()).sort((firstOverride, secondOverride) => {
    const channelCompare = compareText(firstOverride.channelId, secondOverride.channelId);

    if (channelCompare !== 0) {
      return channelCompare;
    }

    if (firstOverride.targetType !== secondOverride.targetType) {
      return compareText(firstOverride.targetType, secondOverride.targetType);
    }

    const targetCompare = compareText(firstOverride.targetId, secondOverride.targetId);

    if (targetCompare !== 0) {
      return targetCompare;
    }

    return compareText(firstOverride.permission, secondOverride.permission);
  });
}

function normalizeReactionPayloads(rawReactions: unknown, messageId: string): ReactionPayload[] {
  if (!Array.isArray(rawReactions)) {
    return [];
  }

  const seen = new Set<string>();
  const reactions: ReactionPayload[] = [];

  for (const rawReaction of rawReactions) {
    if (!rawReaction || typeof rawReaction !== 'object') {
      continue;
    }

    const reaction = rawReaction as Record<string, unknown>;
    const emoji = typeof reaction['emoji'] === 'string' ? reaction['emoji'] : '';
    const userId = typeof reaction['userId'] === 'string' ? reaction['userId'] : '';
    const dedupeKey = `${userId}:${emoji}`;

    if (!emoji || seen.has(dedupeKey)) {
      continue;
    }

    seen.add(dedupeKey);
    reactions.push({
      id: typeof reaction['id'] === 'string' && reaction['id'].trim() ? reaction['id'] : `${messageId}:${dedupeKey}`,
      messageId,
      oderId: typeof reaction['oderId'] === 'string' ? reaction['oderId'] : '',
      userId,
      emoji,
      timestamp: isFiniteNumber(reaction['timestamp']) ? reaction['timestamp'] : 0
    });
  }

  return reactions;
}

export function normalizeRoomChannels(rawChannels: unknown): RoomChannelRecord[] {
  if (!Array.isArray(rawChannels)) {
    return [];
  }

  const seenIds = new Set<string>();
  const seenNames = new Set<string>();
  const channels: RoomChannelRecord[] = [];

  for (const [index, rawChannel] of rawChannels.entries()) {
    if (!rawChannel || typeof rawChannel !== 'object') {
      continue;
    }

    const channel = rawChannel as Record<string, unknown>;
    const id = typeof channel['id'] === 'string' ? channel['id'].trim() : '';
    const name = typeof channel['name'] === 'string' ? normalizeChannelName(channel['name']) : '';
    const type = channel['type'] === 'text' || channel['type'] === 'voice' ? channel['type'] : null;
    const position = isFiniteNumber(channel['position']) ? channel['position'] : index;
    const nameKey = type ? channelNameKey(type, name) : '';

    if (!id || !name || !type || seenIds.has(id) || seenNames.has(nameKey)) {
      continue;
    }

    seenIds.add(id);
    seenNames.add(nameKey);
    channels.push({
      id,
      name,
      type,
      position
    });
  }

  return channels;
}

function deriveLegacyPermissions(roles: readonly RoomRoleRecord[], slowModeInterval: number): LegacyRoomPermissions {
  const everyoneRole = roles.find((role) => role.id === SYSTEM_ROLE_IDS.everyone);
  const moderatorRole = roles.find((role) => role.id === SYSTEM_ROLE_IDS.moderator);
  const adminRole = roles.find((role) => role.id === SYSTEM_ROLE_IDS.admin);
  const toBoolean = (role: RoomRoleRecord | undefined, permission: RoomPermissionKeyPayload, fallbackValue: boolean): boolean => {
    const state = role?.permissions?.[permission];

    if (state === 'allow') {
      return true;
    }

    if (state === 'deny') {
      return false;
    }

    return fallbackValue;
  };

  return {
    allowVoice: toBoolean(everyoneRole, 'joinVoice', true),
    allowScreenShare: toBoolean(everyoneRole, 'shareScreen', true),
    allowFileUploads: toBoolean(everyoneRole, 'uploadFiles', true),
    adminsManageRooms: adminRole?.permissions?.manageChannels === 'allow',
    moderatorsManageRooms: moderatorRole?.permissions?.manageChannels === 'allow',
    adminsManageIcon: adminRole?.permissions?.manageIcon === 'allow',
    moderatorsManageIcon: moderatorRole?.permissions?.manageIcon === 'allow',
    slowModeInterval
  };
}

export async function replaceMessageReactions(
  manager: EntityManager,
  messageId: string,
  rawReactions: unknown
): Promise<void> {
  const repo = manager.getRepository(ReactionEntity);

  await repo.delete({ messageId });

  const reactions = normalizeReactionPayloads(rawReactions, messageId);

  if (reactions.length === 0) {
    return;
  }

  await repo.insert(
    reactions.map((reaction) => ({
      id: reaction.id,
      messageId,
      oderId: reaction.oderId || null,
      userId: reaction.userId || null,
      emoji: reaction.emoji,
      timestamp: reaction.timestamp
    }))
  );
}

export async function loadMessageReactionsMap(
  dataSource: DataSource,
  messageIds: readonly string[]
): Promise<Map<string, ReactionPayload[]>> {
  const groupedReactions = new Map<string, ReactionPayload[]>();

  if (messageIds.length === 0) {
    return groupedReactions;
  }

  const rows = await dataSource.getRepository(ReactionEntity).find({
    where: { messageId: In([...messageIds]) }
  });

  for (const row of rows) {
    const reactions = groupedReactions.get(row.messageId) ?? [];

    reactions.push({
      id: row.id,
      messageId: row.messageId,
      oderId: row.oderId ?? '',
      userId: row.userId ?? '',
      emoji: row.emoji,
      timestamp: row.timestamp
    });

    groupedReactions.set(row.messageId, reactions);
  }

  for (const reactions of groupedReactions.values()) {
    reactions.sort((firstReaction, secondReaction) => firstReaction.timestamp - secondReaction.timestamp);
  }

  return groupedReactions;
}

export async function replaceRoomRelations(
  manager: EntityManager,
  roomId: string,
  options: {
    channels?: unknown;
    members?: unknown;
    roles?: unknown;
    roleAssignments?: unknown;
    channelPermissions?: unknown;
    permissions?: unknown;
  }
): Promise<void> {
  const normalizedMembers = options.members !== undefined
    ? normalizeRoomMembers(options.members)
    : [];
  const normalizedRoles = options.roles !== undefined
    ? normalizeRoomRoles(options.roles, options.permissions)
    : [];

  if (options.channels !== undefined) {
    const channelRepo = manager.getRepository(RoomChannelEntity);
    const channels = normalizeRoomChannels(options.channels);

    await channelRepo.delete({ roomId });

    if (channels.length > 0) {
      await channelRepo.insert(
        channels.map((channel) => ({
          roomId,
          channelId: channel.id,
          name: channel.name,
          type: channel.type,
          position: channel.position
        }))
      );
    }
  }

  if (options.members !== undefined) {
    const memberRepo = manager.getRepository(RoomMemberEntity);

    await memberRepo.delete({ roomId });

    if (normalizedMembers.length > 0) {
      await memberRepo.insert(
        normalizedMembers.map((member) => ({
          roomId,
          memberKey: memberKey(member),
          id: member.id,
          oderId: member.oderId ?? null,
          username: member.username,
          displayName: member.displayName,
          avatarUrl: member.avatarUrl ?? null,
          role: member.role,
          joinedAt: member.joinedAt,
          lastSeenAt: member.lastSeenAt
        }))
      );
    }
  }

  if (options.roles !== undefined) {
    const roleRepo = manager.getRepository(RoomRoleEntity);

    await roleRepo.delete({ roomId });

    if (normalizedRoles.length > 0) {
      await roleRepo.insert(
        normalizedRoles.map((role) => ({
          roomId,
          roleId: role.id,
          name: role.name,
          color: role.color ?? null,
          position: role.position,
          isSystem: role.isSystem ? 1 : 0,
          manageServer: normalizePermissionState(role.permissions?.manageServer),
          manageRoles: normalizePermissionState(role.permissions?.manageRoles),
          manageChannels: normalizePermissionState(role.permissions?.manageChannels),
          manageIcon: normalizePermissionState(role.permissions?.manageIcon),
          kickMembers: normalizePermissionState(role.permissions?.kickMembers),
          banMembers: normalizePermissionState(role.permissions?.banMembers),
          manageBans: normalizePermissionState(role.permissions?.manageBans),
          deleteMessages: normalizePermissionState(role.permissions?.deleteMessages),
          joinVoice: normalizePermissionState(role.permissions?.joinVoice),
          shareScreen: normalizePermissionState(role.permissions?.shareScreen),
          uploadFiles: normalizePermissionState(role.permissions?.uploadFiles)
        }))
      );
    }
  }

  if (options.roleAssignments !== undefined) {
    const assignmentRepo = manager.getRepository(RoomUserRoleEntity);
    const assignments = normalizeRoomRoleAssignments(
      options.roleAssignments,
      normalizedMembers,
      normalizedRoles.length > 0 ? normalizedRoles : normalizeRoomRoles([], options.permissions)
    );

    await assignmentRepo.delete({ roomId });

    const rows = assignments.flatMap((assignment) =>
      assignment.roleIds.map((roleId) => ({
        roomId,
        userKey: assignment.oderId?.trim() || assignment.userId.trim(),
        roleId,
        userId: assignment.userId,
        oderId: assignment.oderId ?? null
      }))
    );

    if (rows.length > 0) {
      await assignmentRepo.insert(rows);
    }
  }

  if (options.channelPermissions !== undefined) {
    const channelPermissionRepo = manager.getRepository(RoomChannelPermissionEntity);
    const channelPermissions = normalizeRoomChannelPermissions(
      options.channelPermissions,
      normalizedRoles.length > 0 ? normalizedRoles : normalizeRoomRoles([], options.permissions)
    );

    await channelPermissionRepo.delete({ roomId });

    if (channelPermissions.length > 0) {
      await channelPermissionRepo.insert(
        channelPermissions.map((channelPermission) => ({
          roomId,
          channelId: channelPermission.channelId,
          targetType: channelPermission.targetType,
          targetId: channelPermission.targetId,
          permission: channelPermission.permission,
          value: channelPermission.value
        }))
      );
    }
  }
}

export async function loadRoomRelationsMap(
  dataSource: DataSource,
  roomIds: readonly string[]
): Promise<Map<string, RoomRelationRecord>> {
  const groupedRelations = new Map<string, RoomRelationRecord>();

  if (roomIds.length === 0) {
    return groupedRelations;
  }

  const [
    channelRows,
    memberRows,
    roleRows,
    assignmentRows,
    channelPermissionRows
  ] = await Promise.all([
    dataSource.getRepository(RoomChannelEntity).find({
      where: { roomId: In([...roomIds]) }
    }),
    dataSource.getRepository(RoomMemberEntity).find({
      where: { roomId: In([...roomIds]) }
    }),
    dataSource.getRepository(RoomRoleEntity).find({
      where: { roomId: In([...roomIds]) }
    }),
    dataSource.getRepository(RoomUserRoleEntity).find({
      where: { roomId: In([...roomIds]) }
    }),
    dataSource.getRepository(RoomChannelPermissionEntity).find({
      where: { roomId: In([...roomIds]) }
    })
  ]);

  for (const roomId of roomIds) {
    groupedRelations.set(roomId, {
      channels: [],
      members: [],
      roles: [],
      roleAssignments: [],
      channelPermissions: []
    });
  }

  for (const row of channelRows) {
    groupedRelations.get(row.roomId)?.channels.push({
      id: row.channelId,
      name: row.name,
      type: row.type,
      position: row.position
    });
  }

  for (const row of memberRows) {
    groupedRelations.get(row.roomId)?.members.push({
      id: row.id,
      oderId: row.oderId ?? undefined,
      username: row.username,
      displayName: row.displayName,
      avatarUrl: row.avatarUrl ?? undefined,
      role: row.role,
      joinedAt: row.joinedAt,
      lastSeenAt: row.lastSeenAt
    });
  }

  for (const row of roleRows) {
    groupedRelations.get(row.roomId)?.roles.push({
      id: row.roleId,
      name: row.name,
      color: row.color ?? undefined,
      position: row.position,
      isSystem: !!row.isSystem,
      permissions: normalizePermissionMatrix({
        manageServer: row.manageServer,
        manageRoles: row.manageRoles,
        manageChannels: row.manageChannels,
        manageIcon: row.manageIcon,
        kickMembers: row.kickMembers,
        banMembers: row.banMembers,
        manageBans: row.manageBans,
        deleteMessages: row.deleteMessages,
        joinVoice: row.joinVoice,
        shareScreen: row.shareScreen,
        uploadFiles: row.uploadFiles
      })
    });
  }

  for (const row of assignmentRows) {
    const relation = groupedRelations.get(row.roomId);

    if (!relation) {
      continue;
    }

    const existing = relation.roleAssignments.find((assignment) =>
      assignment.userId === row.userId || assignment.oderId === row.oderId || (row.oderId == null && assignment.userId === row.userKey)
    );

    if (existing) {
      existing.roleIds = uniqueStrings([...existing.roleIds, row.roleId]);
      continue;
    }

    relation.roleAssignments.push({
      userId: row.userId,
      oderId: row.oderId ?? undefined,
      roleIds: [row.roleId]
    });
  }

  for (const row of channelPermissionRows) {
    groupedRelations.get(row.roomId)?.channelPermissions.push({
      channelId: row.channelId,
      targetType: row.targetType,
      targetId: row.targetId,
      permission: row.permission as RoomPermissionKeyPayload,
      value: normalizePermissionState(row.value)
    });
  }

  for (const relation of groupedRelations.values()) {
    relation.channels.sort(
      (firstChannel, secondChannel) => firstChannel.position - secondChannel.position || compareText(firstChannel.name, secondChannel.name)
    );

    relation.members.sort(compareRoomMembers);
    relation.roles.sort(compareRoles);
    relation.roleAssignments.sort(compareAssignments);
  }

  return groupedRelations;
}

export function relationRecordToRoomPayload(
  row: Pick<RoomPayload, 'slowModeInterval'>,
  relations: RoomRelationRecord
): Pick<RoomPayload, 'permissions' | 'channels' | 'members' | 'roles' | 'roleAssignments' | 'channelPermissions'> {
  const roleAssignmentsByKey = new Map(relations.roleAssignments.map((assignment) => [assignment.oderId || assignment.userId, assignment.roleIds]));

  return {
    permissions: deriveLegacyPermissions(relations.roles, row.slowModeInterval ?? 0),
    channels: relations.channels,
    members: relations.members.map((member) => ({
      ...member,
      roleIds: roleAssignmentsByKey.get(member.oderId || member.id)
    })),
    roles: relations.roles,
    roleAssignments: relations.roleAssignments,
    channelPermissions: relations.channelPermissions
  };
}