spider/internal/store/group_member_repo.go

package store

import (
	"spider/internal/model"
	"strings"
	"time"
)

// SaveGroupMember records a group-member relationship (idempotent via unique index).
func (s *Store) SaveGroupMember(groupUsername, memberUsername, groupTitle, sourceType string, taskID uint) {
	groupUsername = strings.TrimPrefix(strings.TrimSpace(groupUsername), "@")
	memberUsername = strings.TrimPrefix(strings.TrimSpace(memberUsername), "@")
	if groupUsername == "" || memberUsername == "" || groupUsername == memberUsername {
		return
	}
	gm := model.GroupMember{
		GroupUsername:   groupUsername,
		MemberUsername: memberUsername,
		GroupTitle:     groupTitle,
		SourceType:    sourceType,
		TaskID:        taskID,
		DiscoveredAt:  time.Now(),
	}
	// Use unique index for idempotency
	s.DB.Where("group_username = ? AND member_username = ?", groupUsername, memberUsername).
		FirstOrCreate(&gm)
}

// BatchSaveGroupMembers saves multiple group-member relationships (idempotent).
// Returns the count of newly created records.
func (s *Store) BatchSaveGroupMembers(groupUsername, groupTitle, sourceType string, memberUsernames []string) int {
	groupUsername = strings.TrimPrefix(strings.TrimSpace(groupUsername), "@")
	if groupUsername == "" {
		return 0
	}

	created := 0
	for _, mu := range memberUsernames {
		mu = strings.TrimPrefix(strings.TrimSpace(mu), "@")
		if mu == "" || mu == groupUsername {
			continue
		}
		gm := model.GroupMember{
			GroupUsername:   groupUsername,
			MemberUsername: mu,
			GroupTitle:     groupTitle,
			SourceType:    sourceType,
			DiscoveredAt:  time.Now(),
		}
		result := s.DB.Where("group_username = ? AND member_username = ?", groupUsername, mu).
			FirstOrCreate(&gm)
		if result.RowsAffected > 0 {
			created++
		}
	}
	return created
}

// ListMembersByGroup returns all members found in a group. Supports search by member username.
func (s *Store) ListMembersByGroup(groupUsername string, page, pageSize int, search string) ([]model.GroupMember, int64, error) {
	var items []model.GroupMember
	var total int64
	q := s.DB.Model(&model.GroupMember{}).Where("group_username = ?", groupUsername)
	if search != "" {
		like := "%" + strings.TrimPrefix(strings.TrimSpace(search), "@") + "%"
		q = q.Where("member_username LIKE ?", like)
	}
	q.Count(&total)
	offset := (page - 1) * pageSize
	err := q.Order("discovered_at DESC").Offset(offset).Limit(pageSize).Find(&items).Error
	return items, total, err
}

// ListGroupsByMember returns all groups a member belongs to.
func (s *Store) ListGroupsByMember(memberUsername string) ([]model.GroupMember, error) {
	var items []model.GroupMember
	err := s.DB.Where("member_username = ?", memberUsername).
		Order("discovered_at DESC").Find(&items).Error
	return items, err
}

// ListGroups returns all unique groups with member counts. Supports search by group username or title.
func (s *Store) ListGroups(page, pageSize int, search string) ([]GroupSummary, int64, error) {
	base := s.DB.Model(&model.GroupMember{})
	if search != "" {
		like := "%" + search + "%"
		base = base.Where("group_username LIKE ? OR group_title LIKE ?", like, like)
	}

	var total int64
	base.Select("group_username").Group("group_username").Count(&total)

	var summaries []GroupSummary
	offset := (page - 1) * pageSize
	q := s.DB.Model(&model.GroupMember{})
	if search != "" {
		like := "%" + search + "%"
		q = q.Where("group_username LIKE ? OR group_title LIKE ?", like, like)
	}
	err := q.Select("group_username, MAX(group_title) as group_title, COUNT(*) as member_count, MAX(source_type) as source_type").
		Group("group_username").
		Order("member_count DESC").
		Offset(offset).Limit(pageSize).
		Scan(&summaries).Error
	return summaries, total, err
}

// SearchMembers searches for members by username across all groups.
func (s *Store) SearchMembers(pattern string, page, pageSize int) ([]MemberSummary, int64, error) {
	like := "%" + strings.TrimPrefix(strings.TrimSpace(pattern), "@") + "%"

	var total int64
	s.DB.Model(&model.GroupMember{}).
		Where("member_username LIKE ?", like).
		Select("member_username").
		Group("member_username").
		Count(&total)

	var summaries []MemberSummary
	offset := (page - 1) * pageSize
	err := s.DB.Model(&model.GroupMember{}).
		Where("member_username LIKE ?", like).
		Select("member_username, COUNT(DISTINCT group_username) as group_count, MAX(discovered_at) as last_seen").
		Group("member_username").
		Order("group_count DESC").
		Offset(offset).Limit(pageSize).
		Scan(&summaries).Error
	return summaries, total, err
}

// GroupSummary is a summary of a group with member count.
type GroupSummary struct {
	GroupUsername string `json:"group_username"`
	GroupTitle    string `json:"group_title"`
	MemberCount  int64  `json:"member_count"`
	SourceType   string `json:"source_type"`
}

// MemberSummary is a summary of a member across groups.
type MemberSummary struct {
	MemberUsername string    `json:"member_username"`
	GroupCount     int64     `json:"group_count"`
	LastSeen       time.Time `json:"last_seen"`
}