Method ListUserAnalysis

internal/store/postgres/accesslogs.go:133–181

(since, until time.Time)

}

func (s *AccessLogStore) ListUserAnalysis(since, until time.Time) ([]accesslogs.UserAnalysis, error) {
	ctx := context.Background()
	// access_logs 按用户聚合连接数和独立 IP，JOIN user_node_daily_usage 汇总流量
	const query = `
WITH access_stats AS (
  SELECT
    SPLIT_PART(username, '@', 1)   AS username,
    COUNT(*)                        AS connections,
    COUNT(DISTINCT source_ip)       AS distinct_ips,
    MAX(created_at)                 AS last_seen
  FROM access_logs
  WHERE created_at >= $1 AND created_at <= $2
  GROUP BY 1
),
traffic_stats AS (
  SELECT
    u.username,
    COALESCE(SUM(unu.upload_bytes + unu.download_bytes), 0) AS total_bytes
  FROM user_node_daily_usage unu
  JOIN users u ON u.id = unu.user_id
  WHERE unu.date >= TO_CHAR($1::timestamptz AT TIME ZONE 'UTC', 'YYYY-MM-DD')
    AND unu.date <= TO_CHAR($2::timestamptz AT TIME ZONE 'UTC', 'YYYY-MM-DD')
  GROUP BY u.username
)
SELECT
  a.username,
  a.connections,
  a.distinct_ips,
  COALESCE(t.total_bytes, 0) AS total_bytes,
  a.last_seen
FROM access_stats a
LEFT JOIN traffic_stats t ON t.username = a.username
ORDER BY a.distinct_ips DESC, total_bytes DESC`

	rows, err := s.db.Query(ctx, query, since, until)
	if err != nil {
		return nil, fmt.Errorf("list user analysis: %w", err)
	}
	defer rows.Close()
	var out []accesslogs.UserAnalysis
	for rows.Next() {
		var a accesslogs.UserAnalysis
		if err := rows.Scan(&a.Username, &a.Connections, &a.DistinctIPs, &a.TotalBytes, &a.LastSeen); err != nil {
			return nil, fmt.Errorf("scan user analysis: %w", err)
		}
		out = append(out, a)
	}
	return out, rows.Err()
}

func (s *AccessLogStore) Count() (int64, error) {
	ctx := context.Background()