package message

import (
	"context"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

// ChannelSummary is the app-compatible channel list/detail item.
type ChannelSummary struct {
	ChannelID         string
	Slug              string
	DisplayName       string
	Description       string
	Visibility        string
	Subsystem         string
	Tutorial          bool
	TeamID            string
	TeamHandle        string
	TeamDisplayName   string
	Joined            bool
	CanInvite         bool
	CanManage         bool
	Archived          bool
	SubscriptionState string
	PinnedAt          *time.Time
	LastMessageAt     *time.Time
}

// ChannelMemberSummary is one member returned by GET /channels/:channelId/members.
type ChannelMemberSummary struct {
	ID               string
	Name             string
	DisplayName      string
	MemberID         string
	LegacyTargetKeys []string
	AvatarURL        string
	OwnerProfile     AuthorProfile
	OwnerUserID      string
	Model            string
	Visibility       string
	CanRemove        bool
}

// ChannelPrivateAgentSummary is one owner-scoped private agent entry returned
// by GET /channels/:channelId/members.
type ChannelPrivateAgentSummary struct {
	ID           string
	Name         string
	DisplayName  string
	MemberID     string
	AvatarURL    string
	Model        string
	Visibility   string
	OwnerProfile AuthorProfile
	// OwnerMemberID/OwnerUserID/OwnerName/OwnerDisplayName are legacy
	// compatibility aliases. New UI code must derive owner identity from
	// OwnerProfile.
	OwnerMemberID    string
	OwnerUserID      string
	OwnerName        string
	OwnerDisplayName string
}

// ChannelMemberList is the response shape returned by GET /channels/:channelId/members.
const (
	DefaultChannelMemberListLimit = 50
	MaxChannelMemberListLimit     = 100
)

type ChannelMemberListOptions struct {
	Limit  int
	Cursor string
	Query  string
}

type ChannelMemberList struct {
	Agents        []ChannelMemberSummary
	Humans        []ChannelMemberSummary
	Services      []ChannelMemberSummary
	PrivateAgents []ChannelPrivateAgentSummary
	TotalCount    int
	HasMore       bool
	NextCursor    string
}

// ChannelMemberAddRequest is one target in a channel member add mutation.
// Exactly one of AgentID, UserID, or ServiceMemberID must be set by callers.
type ChannelMemberAddRequest struct {
	AgentID         string
	UserID          string
	ServiceMemberID string
	Visibility      string
}

const (
	ChannelMemberEventMetadataKey = "channelMemberEvent"

	ChannelMemberEventActionAdded   = "added"
	ChannelMemberEventActionRemoved = "removed"
	ChannelMemberEventActionUpdated = "updated"

	ChannelMemberEventTargetHuman   = "human"
	ChannelMemberEventTargetAgent   = "agent"
	ChannelMemberEventTargetService = "service"
)

// ChannelMemberEventResult is returned by channel member mutations that may
// append a human-visible system message. Changed is false for idempotent
// add/remove requests that did not change the visible channel roster.
type ChannelMemberEventResult struct {
	Changed           bool
	ChangedCount      int
	AffectedMemberIDs []string
	Message           AppMessage
	Delivery          MessageDelivery
}

func preferredProjectionDisplayName(values ...string) string {
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		if trimmed != "" {
			return trimmed
		}
	}
	return ""
}

func projectionLegacyTargetKeys(preferred string, aliases ...string) []string {
	normalizedPreferred := strings.ToLower(strings.TrimSpace(preferred))
	if normalizedPreferred == "" {
		return nil
	}

	keys := make([]string, 0, len(aliases))
	seen := make(map[string]struct{}, len(aliases))
	for _, alias := range aliases {
		trimmed := strings.TrimSpace(alias)
		if trimmed == "" {
			continue
		}
		normalized := strings.ToLower(trimmed)
		if normalized == normalizedPreferred {
			continue
		}
		if _, exists := seen[normalized]; exists {
			continue
		}
		seen[normalized] = struct{}{}
		keys = append(keys, trimmed)
	}
	if len(keys) == 0 {
		return nil
	}
	return keys
}

// ListChannelsForMember returns the complete app-compatible channel authority list for one workspace member.
func ListChannelsForMember(ctx context.Context, queryer coredb.Queryer, memberID string, workspaceID string) ([]ChannelSummary, error) {
	rows, err := queryer.Query(ctx, `
WITH active_scope_memberships AS (
    SELECT
        scope_id,
        member_id,
        SUM(CASE WHEN archived_at IS NULL THEN 1 ELSE 0 END) = 0 AS archived
    FROM scope_memberships
    WHERE member_id = $2
      AND visibility = 'public'
      AND removed_at IS NULL
      AND effective_to_seq IS NULL
    GROUP BY scope_id, member_id
)
SELECT s.id, p.slug, COALESCE(NULLIF(p.display_name, ''), p.slug), COALESCE(p.description, ''),
       COALESCE(NULLIF(p.visibility, ''), 'private') AS visibility,
       CASE
         WHEN p.subsystem = 'tutorial' AND onboarding.status = 'completed' THEN 'im'
         ELSE COALESCE(NULLIF(p.subsystem, ''), 'im')
       END AS subsystem,
       p.subsystem = 'tutorial' AS tutorial,
       COALESCE(wt.id, '') AS team_id,
       COALESCE(wt.handle, '') AS team_handle,
       COALESCE(wt.display_name, '') AS team_display_name,
       TRUE AS joined,
       wt.id IS NULL AS can_invite,
       COALESCE(p.owner_member_id = $2 AND wt.id IS NULL, FALSE) AS can_manage,
       sm.archived AS archived,
       COALESCE(sns.state, 'active') AS subscription_state,
       cms.pinned_at,
       s.latest_message_at
FROM scopes s
JOIN scope_channel_profiles p
  ON p.scope_id = s.id
 AND p.workspace_id = s.workspace_id
JOIN active_scope_memberships sm
  ON sm.scope_id = s.id
 AND sm.member_id = $2
LEFT JOIN scope_notification_settings sns
  ON sns.scope_id = s.id
 AND sns.member_id = $2
LEFT JOIN conversation_member_states cms
  ON cms.scope_id = s.id
 AND cms.member_id = $2
LEFT JOIN workspace_teams wt
  ON wt.workspace_id = s.workspace_id
 AND wt.channel_id = s.id
 AND wt.status = 'active'
LEFT JOIN desktop_onboarding_states onboarding
  ON onboarding.workspace_id = s.workspace_id
 AND onboarding.owner_member_id = $2
 AND onboarding.tutorial_channel_id = s.id
 AND onboarding.flow_version = 'v1'
WHERE s.workspace_id = $1
  AND s.type = 'channel'
  AND s.status = 'active'
  AND s.archived_at IS NULL
  AND p.subsystem IN ('im', 'team', 'tutorial')
  AND (p.slug = 'all' OR p.owner_member_id IS NOT NULL OR wt.id IS NOT NULL)
ORDER BY p.slug ASC
`, workspaceID, memberID)
	if err != nil {
		return nil, fmt.Errorf("list channels: %w", err)
	}
	defer rows.Close()

	channels := make([]ChannelSummary, 0)
	for rows.Next() {
		var channel ChannelSummary
		if err := rows.Scan(
			&channel.ChannelID,
			&channel.Slug,
			&channel.DisplayName,
			&channel.Description,
			&channel.Visibility,
			&channel.Subsystem,
			&channel.Tutorial,
			&channel.TeamID,
			&channel.TeamHandle,
			&channel.TeamDisplayName,
			&channel.Joined,
			&channel.CanInvite,
			&channel.CanManage,
			&channel.Archived,
			&channel.SubscriptionState,
			&channel.PinnedAt,
			&channel.LastMessageAt,
		); err != nil {
			return nil, fmt.Errorf("scan channel: %w", err)
		}
		channels = append(channels, channel)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate channels: %w", err)
	}

	return channels, nil
}

// ListDMChannelsForMember returns the app-compatible DM channel list for one workspace member.
func ListDMChannelsForMember(ctx context.Context, queryer coredb.Queryer, memberID string, workspaceID string) ([]DMChannel, error) {
	return ListDMChannelsForMemberWithAgentOwnerLimit(ctx, queryer, memberID, workspaceID, "", 0)
}

// ListDMParticipantUsers returns active human user participants who can receive
// app realtime events for one DM scope. Agent-DM participants are owner-filtered
// to match the app DM read projections.
func ListDMParticipantUsers(ctx context.Context, queryer coredb.Queryer, workspaceID string, dmThreadID string) ([]DMParticipantUser, error) {
	rows, err := queryer.Query(ctx, `
SELECT DISTINCT m.user_id, m.id
FROM scopes s
JOIN scope_memberships sm
  ON sm.scope_id = s.id
 AND sm.removed_at IS NULL
 AND sm.archived_at IS NULL
 AND sm.effective_to_seq IS NULL
JOIN members m
  ON m.id = sm.member_id
 AND m.type = 'human'
 AND m.status = 'active'
JOIN scope_memberships peer_sm
  ON peer_sm.scope_id = s.id
 AND peer_sm.member_id <> m.id
 AND peer_sm.removed_at IS NULL
 AND peer_sm.archived_at IS NULL
 AND peer_sm.effective_to_seq IS NULL
JOIN members peer ON peer.id = peer_sm.member_id
LEFT JOIN agents a
  ON peer.type = 'agent'
 AND a.workspace_id = s.workspace_id
 AND a.member_id = peer.id
LEFT JOIN machines mac
  ON peer.type = 'agent'
 AND mac.workspace_id = a.workspace_id
 AND mac.id = a.machine_id
WHERE s.workspace_id = $1
  AND s.id = $2
  AND s.type = 'dm'
  AND s.status = 'active'
  AND s.archived_at IS NULL
  AND COALESCE(m.user_id, '') <> ''
  AND (
      peer.type <> 'agent'
      OR (
          a.status <> 'archived'
          AND mac.status = 'active'
          AND mac.created_by_user_id = m.user_id
      )
  )
ORDER BY m.id ASC
`, strings.TrimSpace(workspaceID), strings.TrimSpace(dmThreadID))
	if err != nil {
		return nil, fmt.Errorf("list dm participant users: %w", err)
	}
	defer rows.Close()

	participants := make([]DMParticipantUser, 0, 2)
	for rows.Next() {
		var participant DMParticipantUser
		if err := rows.Scan(&participant.UserID, &participant.MemberID); err != nil {
			return nil, fmt.Errorf("scan dm participant user: %w", err)
		}
		participants = append(participants, participant)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate dm participant users: %w", err)
	}
	return participants, nil
}

// ListDMChannelsForMemberWithAgentOwnerLimit returns DMs whose agent peers are
// managed by agentOwnerUserID before applying the result limit.
func ListDMChannelsForMemberWithAgentOwnerLimit(ctx context.Context, queryer coredb.Queryer, memberID string, workspaceID string, agentOwnerUserID string, limit int) ([]DMChannel, error) {
	args := []any{workspaceID, memberID}
	nextParam := 3
	agentOwnerFilter := ""
	if agentOwnerUserID = strings.TrimSpace(agentOwnerUserID); agentOwnerUserID != "" {
		agentOwnerFilter = fmt.Sprintf("  AND (peer.type <> 'agent' OR (a.status <> 'archived' AND mac.status = 'active' AND mac.created_by_user_id = $%d))\n", nextParam)
		args = append(args, agentOwnerUserID)
		nextParam++
	}
	limitClause := ""
	if limit > 0 {
		limitClause = fmt.Sprintf("LIMIT $%d", nextParam)
		args = append(args, limit)
	}

	query := fmt.Sprintf(`
SELECT
    s.id,
    peer.type,
    COALESCE(u.id, ''),
    CASE WHEN peer.type = 'human' THEN peer.id ELSE '' END,
    COALESCE(NULLIF(u.display_name, ''), NULLIF(peer.display_name, ''), ''),
    COALESCE(u.avatar_url, ''),
    COALESCE(a.id, ''),
    COALESCE(a.name, ''),
    COALESCE(peer.display_name, ''),
    COALESCE(a.avatar_url, ''),
	COALESCE(service_member.service_installation_id, ''),
	COALESCE(service_member.handle, ''),
	COALESCE(NULLIF(service_member.display_name, ''), NULLIF(peer.display_name, ''), NULLIF(service_definition.name, ''), NULLIF(service_member.handle, ''), ''),
	COALESCE(NULLIF(service_member.avatar_url, ''), service_definition.avatar_url, ''),
    s.latest_message_at,
    CASE
        WHEN COALESCE(dvs.hidden_through_seq, 0) > 0
         AND COALESCE(s.latest_seq, 0) <= dvs.hidden_through_seq
        THEN TRUE
        ELSE FALSE
    END AS sidebar_hidden
FROM scopes s
JOIN scope_memberships self_sm
  ON self_sm.scope_id = s.id
 AND self_sm.member_id = $2
 AND self_sm.removed_at IS NULL
 AND self_sm.archived_at IS NULL
 AND self_sm.effective_to_seq IS NULL
JOIN scope_memberships peer_sm
  ON peer_sm.scope_id = s.id
 AND peer_sm.member_id <> $2
 AND peer_sm.removed_at IS NULL
 AND peer_sm.archived_at IS NULL
 AND peer_sm.effective_to_seq IS NULL
JOIN members peer ON peer.id = peer_sm.member_id
LEFT JOIN users u ON peer.type = 'human' AND u.id = peer.user_id
LEFT JOIN agents a
  ON peer.type = 'agent'
 AND a.workspace_id = s.workspace_id
 AND a.member_id = peer.id
LEFT JOIN machines mac
  ON peer.type = 'agent'
 AND mac.workspace_id = a.workspace_id
 AND mac.id = a.machine_id
LEFT JOIN service_members service_member
  ON peer.type = 'service'
 AND service_member.workspace_id = s.workspace_id
 AND service_member.member_id = peer.id
LEFT JOIN service_definitions service_definition
  ON service_definition.id = service_member.service_definition_id
 AND service_definition.service_kind = service_member.service_kind
LEFT JOIN dm_view_states dvs ON dvs.dm_thread_id = s.id AND dvs.member_id = $2
WHERE s.workspace_id = $1
  AND s.type = 'dm'
  AND s.status = 'active'
  AND s.archived_at IS NULL
%s
ORDER BY CASE WHEN s.latest_message_at IS NULL THEN 1 ELSE 0 END, s.latest_message_at DESC, s.id ASC
%s
`, agentOwnerFilter, limitClause)
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list dm channels: %w", err)
	}
	defer rows.Close()

	dms := make([]DMChannel, 0)
	for rows.Next() {
		var dm DMChannel
		var peerType string
		var userID string
		var humanPublicName string
		var userDisplayName string
		var userAvatarURL string
		var agentID string
		var agentName string
		var agentDisplayName string
		var agentAvatarURL string
		var serviceInstallationID string
		var serviceHandle string
		var serviceDisplayName string
		var serviceAvatarURL string
		if err := rows.Scan(&dm.ID, &peerType, &userID, &humanPublicName, &userDisplayName, &userAvatarURL, &agentID, &agentName, &agentDisplayName, &agentAvatarURL, &serviceInstallationID, &serviceHandle, &serviceDisplayName, &serviceAvatarURL, &dm.LastMessageAt, &dm.SidebarHidden); err != nil {
			return nil, fmt.Errorf("scan dm channel: %w", err)
		}

		switch peerType {
		case "human":
			dm.PeerType = "user"
			dm.PeerID = userID
			dm.PeerName = humanPublicName
			dm.PeerDisplayName = userDisplayName
			dm.PeerAvatarURL = userAvatarURL
		case "agent":
			dm.PeerType = "agent"
			dm.PeerID = agentID
			dm.PeerName = agentName
			dm.PeerDisplayName = agentDisplayName
			dm.PeerAvatarURL = agentAvatarURL
		case "service":
			dm.PeerType = "service"
			dm.PeerID = firstNonEmpty(serviceInstallationID, serviceHandle)
			dm.PeerName = firstNonEmpty(serviceHandle, dm.PeerID)
			dm.PeerDisplayName = firstNonEmpty(serviceDisplayName, dm.PeerName)
			dm.PeerAvatarURL = serviceAvatarURL
		default:
			return nil, fmt.Errorf("invalid dm peer type %q", peerType)
		}

		dms = append(dms, dm)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate dm channels: %w", err)
	}

	return dms, nil
}

// OpenDMChannelForMember loads or creates the DM thread between two members and returns its app projection.
func OpenDMChannelForMember(ctx context.Context, queryer coredb.QueryExecer, actor MemberActor, peerMemberID string) (DMChannel, error) {
	target, err := EnsureDMThread(ctx, queryer, actor, peerMemberID)
	if err != nil {
		return DMChannel{}, err
	}

	return LoadDMChannelByID(ctx, queryer, actor.WorkspaceID, actor.MemberID, target.DMThreadID)
}

// ListChannelMembers returns the app-compatible member list projection for one channel.
func ListChannelMembers(ctx context.Context, queryer coredb.Queryer, channelID string) (ChannelMemberList, error) {
	// ListChannelMembersPage reads scope_memberships and applies the bounded page.
	return ListChannelMembersPage(ctx, queryer, channelID, ChannelMemberListOptions{Limit: MaxChannelMemberListLimit})
}

func ListChannelMembersPage(ctx context.Context, queryer coredb.Queryer, channelID string, options ChannelMemberListOptions) (ChannelMemberList, error) {
	limit := normalizeChannelMemberListLimit(options.Limit)
	offset, err := decodeChannelMemberListCursor(options.Cursor)
	if err != nil {
		return ChannelMemberList{}, err
	}
	totalCount, err := countChannelMembers(ctx, queryer, channelID)
	if err != nil {
		return ChannelMemberList{}, err
	}
	normalizedQuery := strings.ToLower(strings.TrimSpace(options.Query))
	rows, err := queryer.Query(ctx, `
SELECT
    m.type,
    COALESCE(u.id, ''),
    m.id,
    COALESCE(NULLIF(u.display_name, ''), NULLIF(m.display_name, ''), ''),
    COALESCE(u.name, ''),
    COALESCE(u.avatar_url, ''),
    COALESCE(a.id, ''),
    COALESCE(a.name, ''),
    COALESCE(m.display_name, ''),
    COALESCE(a.avatar_url, ''),
    COALESCE(owner_member.id, ''),
    COALESCE(owner_user.id, ''),
    COALESCE(NULLIF(owner_user.display_name, ''), NULLIF(owner_member.display_name, ''), owner_member.id, ''),
    COALESCE(a.model, ''),
    COALESCE(NULLIF(service_member.handle, ''), m.id),
    COALESCE(
        NULLIF(service_member.display_name, ''),
        NULLIF(m.display_name, ''),
        NULLIF(service_definition.name, ''),
        NULLIF(service_member.handle, ''),
        m.id
    ),
    COALESCE(NULLIF(service_member.avatar_url, ''), service_definition.avatar_url, '')
FROM scope_memberships sm
JOIN members m ON m.id = sm.member_id
LEFT JOIN users u ON m.type = 'human' AND u.id = m.user_id
LEFT JOIN agents a ON m.type = 'agent' AND a.member_id = m.id
LEFT JOIN machines agent_machine
  ON m.type = 'agent'
 AND agent_machine.workspace_id = a.workspace_id
 AND agent_machine.id = a.machine_id
LEFT JOIN members owner_member
  ON m.type = 'agent'
 AND owner_member.workspace_id = a.workspace_id
 AND owner_member.type = 'human'
 AND owner_member.user_id = agent_machine.created_by_user_id
 AND owner_member.status = 'active'
LEFT JOIN users owner_user
  ON owner_user.id = agent_machine.created_by_user_id
LEFT JOIN service_members service_member
  ON m.type = 'service'
 AND service_member.workspace_id = m.workspace_id
 AND service_member.member_id = m.id
LEFT JOIN service_definitions service_definition
  ON service_definition.id = service_member.service_definition_id
 AND service_definition.service_kind = service_member.service_kind
WHERE sm.scope_id = $1
  AND sm.visibility = 'public'
  AND sm.removed_at IS NULL
  AND sm.archived_at IS NULL
  AND sm.effective_to_seq IS NULL
  AND ($4 = ''
       OR LOCATE($4, LOWER(CONCAT(
            m.id, ' ',
            COALESCE(NULLIF(m.display_name, ''), ''), ' ',
            COALESCE(NULLIF(u.name, ''), ''), ' ',
            COALESCE(NULLIF(u.display_name, ''), ''), ' ',
            COALESCE(NULLIF(a.name, ''), ''), ' ',
            COALESCE(NULLIF(a.member_id, ''), ''), ' ',
            COALESCE(NULLIF(service_member.handle, ''), ''), ' ',
            COALESCE(NULLIF(service_member.display_name, ''), ''), ' ',
            COALESCE(NULLIF(service_definition.name, ''), '')
       ))) = 1
       OR LOCATE(CONCAT(' ', $4), LOWER(CONCAT(
            m.id, ' ',
            COALESCE(NULLIF(m.display_name, ''), ''), ' ',
            COALESCE(NULLIF(u.name, ''), ''), ' ',
            COALESCE(NULLIF(u.display_name, ''), ''), ' ',
            COALESCE(NULLIF(a.name, ''), ''), ' ',
            COALESCE(NULLIF(a.member_id, ''), ''), ' ',
            COALESCE(NULLIF(service_member.handle, ''), ''), ' ',
            COALESCE(NULLIF(service_member.display_name, ''), ''), ' ',
            COALESCE(NULLIF(service_definition.name, ''), '')
       ))) > 0
       OR LOCATE(CONCAT('-', $4), LOWER(CONCAT(
            m.id, ' ',
            COALESCE(NULLIF(m.display_name, ''), ''), ' ',
            COALESCE(NULLIF(u.name, ''), ''), ' ',
            COALESCE(NULLIF(u.display_name, ''), ''), ' ',
            COALESCE(NULLIF(a.name, ''), ''), ' ',
            COALESCE(NULLIF(a.member_id, ''), ''), ' ',
            COALESCE(NULLIF(service_member.handle, ''), ''), ' ',
            COALESCE(NULLIF(service_member.display_name, ''), ''), ' ',
            COALESCE(NULLIF(service_definition.name, ''), '')
       ))) > 0
       OR LOCATE(CONCAT('_', $4), LOWER(CONCAT(
            m.id, ' ',
            COALESCE(NULLIF(m.display_name, ''), ''), ' ',
            COALESCE(NULLIF(u.name, ''), ''), ' ',
            COALESCE(NULLIF(u.display_name, ''), ''), ' ',
            COALESCE(NULLIF(a.name, ''), ''), ' ',
            COALESCE(NULLIF(a.member_id, ''), ''), ' ',
            COALESCE(NULLIF(service_member.handle, ''), ''), ' ',
            COALESCE(NULLIF(service_member.display_name, ''), ''), ' ',
            COALESCE(NULLIF(service_definition.name, ''), '')
       ))) > 0
       OR LOCATE(CONCAT('.', $4), LOWER(CONCAT(
            m.id, ' ',
            COALESCE(NULLIF(m.display_name, ''), ''), ' ',
            COALESCE(NULLIF(u.name, ''), ''), ' ',
            COALESCE(NULLIF(u.display_name, ''), ''), ' ',
            COALESCE(NULLIF(a.name, ''), ''), ' ',
            COALESCE(NULLIF(a.member_id, ''), ''), ' ',
            COALESCE(NULLIF(service_member.handle, ''), ''), ' ',
            COALESCE(NULLIF(service_member.display_name, ''), ''), ' ',
            COALESCE(NULLIF(service_definition.name, ''), '')
       ))) > 0
       OR LOCATE(CONCAT('/', $4), LOWER(CONCAT(
            m.id, ' ',
            COALESCE(NULLIF(m.display_name, ''), ''), ' ',
            COALESCE(NULLIF(u.name, ''), ''), ' ',
            COALESCE(NULLIF(u.display_name, ''), ''), ' ',
            COALESCE(NULLIF(a.name, ''), ''), ' ',
            COALESCE(NULLIF(a.member_id, ''), ''), ' ',
            COALESCE(NULLIF(service_member.handle, ''), ''), ' ',
            COALESCE(NULLIF(service_member.display_name, ''), ''), ' ',
            COALESCE(NULLIF(service_definition.name, ''), '')
       ))) > 0
       OR LOCATE(CONCAT(':', $4), LOWER(CONCAT(
            m.id, ' ',
            COALESCE(NULLIF(m.display_name, ''), ''), ' ',
            COALESCE(NULLIF(u.name, ''), ''), ' ',
            COALESCE(NULLIF(u.display_name, ''), ''), ' ',
            COALESCE(NULLIF(a.name, ''), ''), ' ',
            COALESCE(NULLIF(a.member_id, ''), ''), ' ',
            COALESCE(NULLIF(service_member.handle, ''), ''), ' ',
            COALESCE(NULLIF(service_member.display_name, ''), ''), ' ',
            COALESCE(NULLIF(service_definition.name, ''), '')
       ))) > 0)
ORDER BY CASE
    WHEN $4 = '' THEN 0
    WHEN LOWER(m.id) = $4
      OR LOWER(COALESCE(NULLIF(m.display_name, ''), '')) = $4
      OR LOWER(COALESCE(NULLIF(u.name, ''), '')) = $4
      OR LOWER(COALESCE(NULLIF(u.display_name, ''), '')) = $4
      OR LOWER(COALESCE(NULLIF(a.name, ''), '')) = $4
      OR LOWER(COALESCE(NULLIF(a.member_id, ''), '')) = $4
      OR LOWER(COALESCE(NULLIF(service_member.handle, ''), '')) = $4
      OR LOWER(COALESCE(NULLIF(service_member.display_name, ''), '')) = $4
      OR LOWER(COALESCE(NULLIF(service_definition.name, ''), '')) = $4
      THEN 0
    WHEN LOCATE($4, LOWER(CONCAT(
            m.id, ' ',
            COALESCE(NULLIF(m.display_name, ''), ''), ' ',
            COALESCE(NULLIF(u.name, ''), ''), ' ',
            COALESCE(NULLIF(u.display_name, ''), ''), ' ',
            COALESCE(NULLIF(a.name, ''), ''), ' ',
            COALESCE(NULLIF(a.member_id, ''), ''), ' ',
            COALESCE(NULLIF(service_member.handle, ''), ''), ' ',
            COALESCE(NULLIF(service_member.display_name, ''), ''), ' ',
            COALESCE(NULLIF(service_definition.name, ''), '')
       ))) = 1 THEN 1
    ELSE 2
END ASC,
m.type ASC, sm.created_at ASC, m.id ASC
LIMIT $2 OFFSET $3
`, channelID, limit+1, offset, normalizedQuery)
	if err != nil {
		return ChannelMemberList{}, fmt.Errorf("list channel members: %w", err)
	}
	defer rows.Close()

	list := ChannelMemberList{
		Agents:        make([]ChannelMemberSummary, 0),
		Humans:        make([]ChannelMemberSummary, 0),
		Services:      make([]ChannelMemberSummary, 0),
		PrivateAgents: make([]ChannelPrivateAgentSummary, 0),
		TotalCount:    totalCount,
	}
	publicRowsSeen := 0
	for rows.Next() {
		var memberType string
		var userID string
		var memberID string
		var humanDisplayName string
		var humanLegacyName string
		var userAvatarURL string
		var agentID string
		var agentName string
		var agentDisplayName string
		var agentAvatarURL string
		var agentOwnerMemberID string
		var agentOwnerUserID string
		var agentOwnerDisplayName string
		var agentModel string
		var serviceName string
		var serviceDisplayName string
		var serviceAvatarURL string
		if err := rows.Scan(&memberType, &userID, &memberID, &humanDisplayName, &humanLegacyName, &userAvatarURL, &agentID, &agentName, &agentDisplayName, &agentAvatarURL, &agentOwnerMemberID, &agentOwnerUserID, &agentOwnerDisplayName, &agentModel, &serviceName, &serviceDisplayName, &serviceAvatarURL); err != nil {
			return ChannelMemberList{}, fmt.Errorf("scan channel member: %w", err)
		}
		publicRowsSeen++
		if publicRowsSeen > limit {
			list.HasMore = true
			continue
		}

		switch memberType {
		case "human":
			list.Humans = append(list.Humans, ChannelMemberSummary{
				ID:               userID,
				Name:             memberID,
				DisplayName:      preferredProjectionDisplayName(humanDisplayName, memberID),
				MemberID:         memberID,
				LegacyTargetKeys: projectionLegacyTargetKeys(memberID, humanLegacyName),
				AvatarURL:        userAvatarURL,
			})
		case "agent":
			ownerProfile := channelAgentOwnerProfile(agentOwnerMemberID, agentOwnerUserID, agentOwnerDisplayName)
			list.Agents = append(list.Agents, ChannelMemberSummary{
				ID:           agentID,
				Name:         agentName,
				DisplayName:  agentDisplayName,
				MemberID:     strings.TrimSpace(memberID),
				AvatarURL:    agentAvatarURL,
				OwnerProfile: ownerProfile,
				OwnerUserID:  strings.TrimSpace(agentOwnerUserID),
				Model:        strings.TrimSpace(agentModel),
				Visibility:   "public",
			})
		case "service":
			memberID := strings.TrimSpace(memberID)
			list.Services = append(list.Services, ChannelMemberSummary{
				ID:          memberID,
				Name:        strings.TrimSpace(serviceName),
				DisplayName: preferredProjectionDisplayName(serviceDisplayName, serviceName, memberID),
				MemberID:    memberID,
				AvatarURL:   strings.TrimSpace(serviceAvatarURL),
			})
		default:
			return ChannelMemberList{}, fmt.Errorf("invalid member type %q", memberType)
		}
	}
	if err := rows.Err(); err != nil {
		return ChannelMemberList{}, fmt.Errorf("iterate channel members: %w", err)
	}

	if offset == 0 || normalizedQuery != "" {
		privateRows, err := queryer.Query(ctx, `
SELECT
    a.id,
    COALESCE(NULLIF(a.name, ''), a.member_id),
    COALESCE(NULLIF(agent_member.display_name, ''), NULLIF(a.name, ''), a.member_id),
    a.member_id,
    COALESCE(a.avatar_url, ''),
    COALESCE(a.model, ''),
    owner_member.id,
    COALESCE(owner_user.id, ''),
    COALESCE(owner_user.name, ''),
    COALESCE(NULLIF(owner_user.display_name, ''), NULLIF(owner_member.display_name, ''), owner_member.id)
FROM scope_memberships sm
JOIN agents a
  ON a.member_id = sm.member_id
JOIN members agent_member
  ON agent_member.id = sm.member_id
JOIN members owner_member
  ON owner_member.id = sm.owner_member_id
LEFT JOIN users owner_user
  ON owner_user.id = owner_member.user_id
WHERE sm.scope_id = $1
  AND sm.visibility = 'private'
  AND sm.owner_member_id IS NOT NULL
  AND sm.removed_at IS NULL
  AND sm.archived_at IS NULL
  AND sm.effective_to_seq IS NULL
  AND NOT EXISTS (
    SELECT 1
    FROM scope_memberships public_sm
    WHERE public_sm.scope_id = sm.scope_id
      AND public_sm.member_id = sm.member_id
      AND public_sm.visibility = 'public'
      AND public_sm.removed_at IS NULL
      AND public_sm.archived_at IS NULL
      AND public_sm.effective_to_seq IS NULL
  )
  AND ($2 = ''
       OR LOCATE($2, LOWER(CONCAT(
            a.name, ' ',
            a.member_id, ' ',
            COALESCE(NULLIF(agent_member.display_name, ''), '')
       ))) = 1
       OR LOCATE(CONCAT(' ', $2), LOWER(CONCAT(
            a.name, ' ',
            a.member_id, ' ',
            COALESCE(NULLIF(agent_member.display_name, ''), '')
       ))) > 0
       OR LOCATE(CONCAT('-', $2), LOWER(CONCAT(
            a.name, ' ',
            a.member_id, ' ',
            COALESCE(NULLIF(agent_member.display_name, ''), '')
       ))) > 0
       OR LOCATE(CONCAT('_', $2), LOWER(CONCAT(
            a.name, ' ',
            a.member_id, ' ',
            COALESCE(NULLIF(agent_member.display_name, ''), '')
       ))) > 0
       OR LOCATE(CONCAT('.', $2), LOWER(CONCAT(
            a.name, ' ',
            a.member_id, ' ',
            COALESCE(NULLIF(agent_member.display_name, ''), '')
       ))) > 0
       OR LOCATE(CONCAT('/', $2), LOWER(CONCAT(
            a.name, ' ',
            a.member_id, ' ',
            COALESCE(NULLIF(agent_member.display_name, ''), '')
       ))) > 0
       OR LOCATE(CONCAT(':', $2), LOWER(CONCAT(
            a.name, ' ',
            a.member_id, ' ',
            COALESCE(NULLIF(agent_member.display_name, ''), '')
       ))) > 0)
ORDER BY owner_member.id ASC, sm.created_at ASC, a.id ASC
`, channelID, normalizedQuery)
		if err != nil {
			return ChannelMemberList{}, fmt.Errorf("list private channel agents: %w", err)
		}
		defer privateRows.Close()

		for privateRows.Next() {
			var agent ChannelPrivateAgentSummary
			var ownerMemberID, ownerUserID, ownerName, ownerDisplayName string
			if err := privateRows.Scan(
				&agent.ID,
				&agent.Name,
				&agent.DisplayName,
				&agent.MemberID,
				&agent.AvatarURL,
				&agent.Model,
				&ownerMemberID,
				&ownerUserID,
				&ownerName,
				&ownerDisplayName,
			); err != nil {
				return ChannelMemberList{}, fmt.Errorf("scan private channel agent: %w", err)
			}
			agent.OwnerProfile = privateChannelAgentOwnerProfile(ownerMemberID, ownerUserID, ownerDisplayName)
			agent.Visibility = "private"
			agent.OwnerName = ownerName
			derivePrivateChannelAgentOwnerCompat(&agent)
			list.PrivateAgents = append(list.PrivateAgents, agent)
		}
		if err := privateRows.Err(); err != nil {
			return ChannelMemberList{}, fmt.Errorf("iterate private channel agents: %w", err)
		}
	}

	if list.HasMore {
		list.NextCursor = strconv.Itoa(offset + limit)
	}

	return list, nil
}

func countChannelMembers(ctx context.Context, queryer coredb.QueryRower, channelID string) (int, error) {
	var totalCount int
	if err := queryer.QueryRow(ctx, `
SELECT COUNT(DISTINCT sm.member_id)
FROM scope_memberships sm
JOIN members m ON m.id = sm.member_id
WHERE sm.scope_id = $1
  AND sm.visibility IN ('public', 'private')
  AND sm.removed_at IS NULL
  AND sm.archived_at IS NULL
  AND sm.effective_to_seq IS NULL
  AND m.type IN ('human', 'agent', 'service')
  AND (
      sm.visibility = 'public'
      OR (sm.visibility = 'private' AND m.type = 'agent' AND sm.owner_member_id IS NOT NULL)
  )
`, strings.TrimSpace(channelID)).Scan(&totalCount); err != nil {
		return 0, fmt.Errorf("count channel members: %w", err)
	}
	return totalCount, nil
}

func normalizeChannelMemberListLimit(limit int) int {
	if limit <= 0 {
		return DefaultChannelMemberListLimit
	}
	if limit > MaxChannelMemberListLimit {
		return MaxChannelMemberListLimit
	}
	return limit
}

func decodeChannelMemberListCursor(raw string) (int, error) {
	trimmed := strings.TrimSpace(raw)
	if trimmed == "" {
		return 0, nil
	}
	offset, err := strconv.Atoi(trimmed)
	if err != nil || offset < 0 {
		return 0, fmt.Errorf("cursor must be a non-negative integer")
	}
	return offset, nil
}

func channelAgentOwnerProfile(ownerMemberID, ownerUserID, ownerDisplayName string) AuthorProfile {
	return AuthorProfile{
		ActorType:   AuthorActorTypeHuman,
		PrincipalID: ownerUserID,
		MemberID:    ownerMemberID,
		TargetID:    ownerMemberID,
		DisplayName: ownerDisplayName,
	}.Normalize()
}

func privateChannelAgentOwnerProfile(ownerMemberID, ownerUserID, ownerDisplayName string) AuthorProfile {
	return channelAgentOwnerProfile(ownerMemberID, ownerUserID, ownerDisplayName)
}

func derivePrivateChannelAgentOwnerCompat(agent *ChannelPrivateAgentSummary) {
	if agent == nil {
		return
	}
	profile := agent.OwnerProfile.Normalize()
	agent.OwnerProfile = profile
	if !profile.Valid() {
		return
	}
	agent.OwnerMemberID = firstNonEmpty(profile.MemberID, agent.OwnerMemberID)
	agent.OwnerUserID = firstNonEmpty(profile.PrincipalID, agent.OwnerUserID)
	agent.OwnerDisplayName = firstNonEmpty(profile.DisplayName, agent.OwnerDisplayName)
	agent.OwnerName = firstNonEmpty(profile.DisplayName, agent.OwnerName)
}