time chart int

app/api/events/summary/route.ts

@@ -11,6 +11,12 @@ export async function GET(request: NextRequest) {
     const projectIds = searchParams.getAll('projectId');
     const tagIds = searchParams.getAll('tagId');
     
+    // Add debug log to check if linkId is being received
+    const linkId = searchParams.get('linkId');
+    console.log('Summary API received linkId:', linkId);
+    console.log('Summary API full parameters:', Object.fromEntries(searchParams.entries()));
+    console.log('Summary API URL:', request.url);
+    
     const summary = await getEventsSummary({
       startTime: searchParams.get('startTime') || undefined,
       endTime: searchParams.get('endTime') || undefined,

app/api/events/track/route.ts

@@ -81,6 +81,8 @@ export async function POST(req: NextRequest) {
       utm_source: eventData.utm_source || '',
       utm_medium: eventData.utm_medium || '',
       utm_campaign: eventData.utm_campaign || '',
+      utm_term: eventData.utm_term || '',
+      utm_content: eventData.utm_content || '',
       
       // Interaction information
       time_spent_sec: eventData.time_spent_sec || 0,

app/api/events/utm/route.ts

@@ -0,0 +1,157 @@
+import { NextRequest, NextResponse } from 'next/server';
+import clickhouse from '@/lib/clickhouse';
+import type { ApiResponse } from '@/lib/types';
+
+interface UtmData {
+  utm_value: string;
+  clicks: number;
+  visitors: number;
+  avg_time_spent: number;
+  bounces: number;
+  conversions: number;
+}
+
+// 格式化日期时间字符串为ClickHouse支持的格式
+const formatDateTime = (dateStr: string): string => {
+  return dateStr.replace('T', ' ').replace('Z', '');
+};
+
+export async function GET(request: NextRequest) {
+  try {
+    const searchParams = request.nextUrl.searchParams;
+    
+    // 获取过滤参数
+    const startTime = searchParams.get('startTime');
+    const endTime = searchParams.get('endTime');
+    const linkId = searchParams.get('linkId');
+    
+    // 获取团队、项目和标签筛选参数
+    const teamIds = searchParams.getAll('teamId');
+    const projectIds = searchParams.getAll('projectId');
+    const tagIds = searchParams.getAll('tagId');
+    const tagNames = searchParams.getAll('tagName');
+    
+    // 获取UTM类型参数
+    const utmType = searchParams.get('utmType') || 'source';
+    
+    // 构建WHERE子句
+    let whereClause = '';
+    const conditions = [];
+    
+    if (startTime) {
+      conditions.push(`event_time >= toDateTime('${formatDateTime(startTime)}')`);
+    }
+    
+    if (endTime) {
+      conditions.push(`event_time <= toDateTime('${formatDateTime(endTime)}')`);
+    }
+    
+    if (linkId) {
+      conditions.push(`link_id = '${linkId}'`);
+    }
+    
+    // 添加团队筛选
+    if (teamIds && teamIds.length > 0) {
+      // 如果只有一个团队ID
+      if (teamIds.length === 1) {
+        conditions.push(`team_id = '${teamIds[0]}'`);
+      } else {
+        // 多个团队ID
+        conditions.push(`team_id IN ('${teamIds.join("','")}')`);
+      }
+    }
+    
+    // 添加项目筛选
+    if (projectIds && projectIds.length > 0) {
+      // 如果只有一个项目ID
+      if (projectIds.length === 1) {
+        conditions.push(`project_id = '${projectIds[0]}'`);
+      } else {
+        // 多个项目ID
+        conditions.push(`project_id IN ('${projectIds.join("','")}')`);
+      }
+    }
+    
+    // 添加标签筛选
+    if ((tagIds && tagIds.length > 0) || (tagNames && tagNames.length > 0)) {
+      // 优先使用tagNames，如果有的话
+      const tagsToUse = tagNames.length > 0 ? tagNames : tagIds;
+      
+      // 使用与buildFilter函数相同的处理方式
+      const tagConditions = tagsToUse.map(tag => 
+        `link_tags LIKE '%${tag}%'`
+      );
+      conditions.push(`(${tagConditions.join(' OR ')})`);
+    }
+    
+    if (conditions.length > 0) {
+      whereClause = `WHERE ${conditions.join(' AND ')}`;
+    }
+    
+    // 确定要分组的UTM字段
+    let utmField;
+    switch (utmType) {
+      case 'source':
+        utmField = 'utm_source';
+        break;
+      case 'medium':
+        utmField = 'utm_medium';
+        break;
+      case 'campaign':
+        utmField = 'utm_campaign';
+        break;
+      case 'term':
+        utmField = 'utm_term';
+        break;
+      case 'content':
+        utmField = 'utm_content';
+        break;
+      default:
+        utmField = 'utm_source';
+    }
+    
+    // 构建SQL查询
+    const query = `
+      SELECT 
+        ${utmField} AS utm_value,
+        COUNT(*) AS clicks,
+        uniqExact(visitor_id) AS visitors,
+        round(AVG(time_spent_sec), 2) AS avg_time_spent,
+        countIf(is_bounce = 1) AS bounces,
+        countIf(conversion_type IN ('visit', 'stay', 'interact', 'signup', 'subscription', 'purchase')) AS conversions
+      FROM shorturl_analytics.events
+      ${whereClause}
+      ${whereClause ? 'AND' : 'WHERE'} ${utmField} != ''
+      GROUP BY utm_value
+      ORDER BY clicks DESC
+      LIMIT 100
+    `;
+    
+    // 执行查询
+    const result = await clickhouse.query({
+      query,
+      format: 'JSONEachRow',
+    });
+    
+    // 获取查询结果
+    const rows = await result.json();
+    const data = rows as UtmData[];
+    
+    // 返回数据
+    const response: ApiResponse<UtmData[]> = {
+      success: true,
+      data
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error fetching UTM data:', error);
+    
+    const response: ApiResponse<null> = {
+      success: false,
+      error: error instanceof Error ? error.message : 'Unknown error occurred'
+    };
+    
+    return NextResponse.json(response, { status: 500 });
+  }
+}

app/api/shortlinks/[id]/route.ts

@@ -0,0 +1,140 @@
+import { NextRequest, NextResponse } from 'next/server';
+import { executeQuery } from '@/lib/clickhouse';
+import type { ApiResponse } from '@/lib/types';
+
+export async function GET(
+  request: NextRequest,
+  { params }: { params: { id: string } }
+) {
+  try {
+    // Get the id from the URL parameters
+    const { id } = params;
+    
+    if (!id) {
+      return NextResponse.json({
+        success: false,
+        error: 'ID parameter is required'
+      }, { status: 400 });
+    }
+    
+    console.log('Fetching shortlink by ID:', id);
+    
+    // Query to fetch a single shortlink by id
+    const query = `
+      SELECT 
+        id,
+        external_id,
+        type,
+        slug,
+        original_url,
+        title,
+        description,
+        attributes,
+        schema_version,
+        creator_id,
+        creator_email,
+        creator_name,
+        created_at,
+        updated_at,
+        deleted_at,
+        projects,
+        teams,
+        tags,
+        qr_codes AS qr_codes,
+        channels,
+        favorites,
+        expires_at,
+        click_count,
+        unique_visitors
+      FROM shorturl_analytics.shorturl
+      WHERE id = '${id}' AND deleted_at IS NULL
+      LIMIT 1
+    `;
+    
+    console.log('Executing query:', query);
+    
+    // Execute the query
+    const result = await executeQuery(query);
+    
+    // If no shortlink found with the specified ID
+    if (!Array.isArray(result) || result.length === 0) {
+      return NextResponse.json({
+        success: false,
+        error: 'Shortlink not found'
+      }, { status: 404 });
+    }
+    
+    // Process the shortlink data
+    const shortlink = result[0] as any;
+    
+    // Extract shortUrl from attributes
+    let shortUrl = '';
+    try {
+      if (shortlink.attributes && typeof shortlink.attributes === 'string') {
+        const attributes = JSON.parse(shortlink.attributes) as { shortUrl?: string };
+        shortUrl = attributes.shortUrl || '';
+      }
+    } catch (e) {
+      console.error('Error parsing shortlink attributes:', e);
+    }
+    
+    // Process teams
+    let teams: any[] = [];
+    try {
+      if (shortlink.teams && typeof shortlink.teams === 'string') {
+        teams = JSON.parse(shortlink.teams);
+      }
+    } catch (e) {
+      console.error('Error parsing teams:', e);
+    }
+    
+    // Process tags
+    let tags: any[] = [];
+    try {
+      if (shortlink.tags && typeof shortlink.tags === 'string') {
+        tags = JSON.parse(shortlink.tags);
+      }
+    } catch (e) {
+      console.error('Error parsing tags:', e);
+    }
+    
+    // Process projects
+    let projects: any[] = [];
+    try {
+      if (shortlink.projects && typeof shortlink.projects === 'string') {
+        projects = JSON.parse(shortlink.projects);
+      }
+    } catch (e) {
+      console.error('Error parsing projects:', e);
+    }
+    
+    // Format the data to match what our store expects
+    const formattedShortlink = {
+      id: shortlink.id || '',
+      externalId: shortlink.external_id || '',
+      slug: shortlink.slug || '',
+      originalUrl: shortlink.original_url || '',
+      title: shortlink.title || '',
+      shortUrl: shortUrl,
+      teams: teams,
+      projects: projects,
+      tags: tags.map((tag: any) => tag.tag_name || ''),
+      createdAt: shortlink.created_at,
+      domain: new URL(shortUrl || 'https://example.com').hostname
+    };
+    
+    const response: ApiResponse<typeof formattedShortlink> = {
+      success: true,
+      data: formattedShortlink
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error fetching shortlink by ID:', error);
+    const response: ApiResponse<null> = {
+      success: false,
+      error: error instanceof Error ? error.message : 'Unknown error occurred'
+    };
+    return NextResponse.json(response, { status: 500 });
+  }
+} 

app/api/shortlinks/byUrl/route.ts

@@ -0,0 +1,142 @@
+import { NextRequest, NextResponse } from 'next/server';
+import { executeQuery } from '@/lib/clickhouse';
+import type { ApiResponse } from '@/lib/types';
+
+export async function GET(request: NextRequest) {
+  try {
+    // Get the url from query parameters
+    const searchParams = request.nextUrl.searchParams;
+    const url = searchParams.get('url');
+    
+    if (!url) {
+      return NextResponse.json({
+        success: false,
+        error: 'URL parameter is required'
+      }, { status: 400 });
+    }
+    
+    console.log('Fetching shortlink by URL:', url);
+    
+    // Query to fetch a single shortlink by shortUrl in attributes
+    const query = `
+      SELECT 
+        id,
+        external_id,
+        type,
+        slug,
+        original_url,
+        title,
+        description,
+        attributes,
+        schema_version,
+        creator_id,
+        creator_email,
+        creator_name,
+        created_at,
+        updated_at,
+        deleted_at,
+        projects,
+        teams,
+        tags,
+        qr_codes AS qr_codes,
+        channels,
+        favorites,
+        expires_at,
+        click_count,
+        unique_visitors
+      FROM shorturl_analytics.shorturl
+      WHERE JSONHas(attributes, 'shortUrl') 
+        AND JSONExtractString(attributes, 'shortUrl') = '${url}'
+        AND deleted_at IS NULL
+      LIMIT 1
+    `;
+    
+    console.log('Executing query:', query);
+    
+    // Execute the query
+    const result = await executeQuery(query);
+    
+    // If no shortlink found with the specified URL
+    if (!Array.isArray(result) || result.length === 0) {
+      return NextResponse.json({
+        success: false,
+        error: 'Shortlink not found'
+      }, { status: 404 });
+    }
+    
+    // Process the shortlink data
+    const shortlink = result[0];
+    
+    // Extract shortUrl from attributes
+    let shortUrl = '';
+    try {
+      if (shortlink.attributes && typeof shortlink.attributes === 'string') {
+        const attributes = JSON.parse(shortlink.attributes);
+        shortUrl = attributes.shortUrl || '';
+      }
+    } catch (e) {
+      console.error('Error parsing shortlink attributes:', e);
+    }
+    
+    // Process teams
+    let teams = [];
+    try {
+      if (shortlink.teams && typeof shortlink.teams === 'string') {
+        teams = JSON.parse(shortlink.teams);
+      }
+    } catch (e) {
+      console.error('Error parsing teams:', e);
+    }
+    
+    // Process tags
+    let tags = [];
+    try {
+      if (shortlink.tags && typeof shortlink.tags === 'string') {
+        tags = JSON.parse(shortlink.tags);
+      }
+    } catch (e) {
+      console.error('Error parsing tags:', e);
+    }
+    
+    // Process projects
+    let projects = [];
+    try {
+      if (shortlink.projects && typeof shortlink.projects === 'string') {
+        projects = JSON.parse(shortlink.projects);
+      }
+    } catch (e) {
+      console.error('Error parsing projects:', e);
+    }
+    
+    // Format the data to match what our store expects
+    const formattedShortlink = {
+      id: shortlink.id || '',
+      externalId: shortlink.external_id || '',
+      slug: shortlink.slug || '',
+      originalUrl: shortlink.original_url || '',
+      title: shortlink.title || '',
+      shortUrl: shortUrl,
+      teams: teams,
+      projects: projects,
+      tags: tags.map((tag) => tag.tag_name || ''),
+      createdAt: shortlink.created_at,
+      domain: new URL(shortUrl || 'https://example.com').hostname
+    };
+    
+    console.log('Shortlink data formatted with externalId:', shortlink.external_id, 'Final object:', formattedShortlink);
+    
+    const response: ApiResponse<typeof formattedShortlink> = {
+      success: true,
+      data: formattedShortlink
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error fetching shortlink by URL:', error);
+    const response: ApiResponse<null> = {
+      success: false,
+      error: error instanceof Error ? error.message : 'Unknown error occurred'
+    };
+    return NextResponse.json(response, { status: 500 });
+  }
+} 

app/api/shortlinks/exact/route.ts

@@ -0,0 +1,142 @@
+import { NextRequest, NextResponse } from 'next/server';
+import { executeQuery } from '@/lib/clickhouse';
+import type { ApiResponse } from '@/lib/types';
+
+export async function GET(request: NextRequest) {
+  try {
+    // Get the url from query parameters
+    const searchParams = request.nextUrl.searchParams;
+    const shortUrl = searchParams.get('shortUrl');
+    
+    if (!shortUrl) {
+      return NextResponse.json({
+        success: false,
+        error: 'shortUrl parameter is required'
+      }, { status: 400 });
+    }
+    
+    console.log('Fetching shortlink by exact shortUrl:', shortUrl);
+    
+    // Query to fetch a single shortlink by shortUrl in attributes
+    const query = `
+      SELECT 
+        id,
+        external_id,
+        type,
+        slug,
+        original_url,
+        title,
+        description,
+        attributes,
+        schema_version,
+        creator_id,
+        creator_email,
+        creator_name,
+        created_at,
+        updated_at,
+        deleted_at,
+        projects,
+        teams,
+        tags,
+        qr_codes AS qr_codes,
+        channels,
+        favorites,
+        expires_at,
+        click_count,
+        unique_visitors
+      FROM shorturl_analytics.shorturl
+      WHERE JSONHas(attributes, 'shortUrl') 
+        AND JSONExtractString(attributes, 'shortUrl') = '${shortUrl}'
+        AND deleted_at IS NULL
+      LIMIT 1
+    `;
+    
+    console.log('Executing query:', query);
+    
+    // Execute the query
+    const result = await executeQuery(query);
+    
+    // If no shortlink found with the specified URL
+    if (!Array.isArray(result) || result.length === 0) {
+      return NextResponse.json({
+        success: false,
+        error: 'Shortlink not found'
+      }, { status: 404 });
+    }
+    
+    // Process the shortlink data
+    const shortlink = result[0] as Record<string, any>;
+    
+    // Extract shortUrl from attributes
+    let shortUrlValue = '';
+    try {
+      if (shortlink.attributes && typeof shortlink.attributes === 'string') {
+        const attributes = JSON.parse(shortlink.attributes) as { shortUrl?: string };
+        shortUrlValue = attributes.shortUrl || '';
+      }
+    } catch (e) {
+      console.error('Error parsing shortlink attributes:', e);
+    }
+    
+    // Process teams
+    let teams: any[] = [];
+    try {
+      if (shortlink.teams && typeof shortlink.teams === 'string') {
+        teams = JSON.parse(shortlink.teams);
+      }
+    } catch (e) {
+      console.error('Error parsing teams:', e);
+    }
+    
+    // Process tags
+    let tags: any[] = [];
+    try {
+      if (shortlink.tags && typeof shortlink.tags === 'string') {
+        tags = JSON.parse(shortlink.tags);
+      }
+    } catch (e) {
+      console.error('Error parsing tags:', e);
+    }
+    
+    // Process projects
+    let projects: any[] = [];
+    try {
+      if (shortlink.projects && typeof shortlink.projects === 'string') {
+        projects = JSON.parse(shortlink.projects);
+      }
+    } catch (e) {
+      console.error('Error parsing projects:', e);
+    }
+    
+    // Format the data to match what our store expects
+    const formattedShortlink = {
+      id: shortlink.id || '',
+      externalId: shortlink.external_id || '',
+      slug: shortlink.slug || '',
+      originalUrl: shortlink.original_url || '',
+      title: shortlink.title || '',
+      shortUrl: shortUrlValue,
+      teams: teams,
+      projects: projects,
+      tags: tags.map((tag: any) => tag.tag_name || ''),
+      createdAt: shortlink.created_at,
+      domain: new URL(shortUrlValue || 'https://example.com').hostname
+    };
+    
+    console.log('Formatted shortlink with externalId:', shortlink.external_id);
+    
+    const response: ApiResponse<typeof formattedShortlink> = {
+      success: true,
+      data: formattedShortlink
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error fetching shortlink by exact URL:', error);
+    const response: ApiResponse<null> = {
+      success: false,
+      error: error instanceof Error ? error.message : 'Unknown error occurred'
+    };
+    return NextResponse.json(response, { status: 500 });
+  }
+} 

app/api/shortlinks/route.ts

@@ -0,0 +1,103 @@
+import { NextResponse } from 'next/server';
+import { executeQuery } from '@/lib/clickhouse';
+import { NextRequest } from 'next/server';
+
+export async function GET(request: NextRequest) {
+  try {
+    // Get pagination and filter parameters from the URL
+    const searchParams = request.nextUrl.searchParams;
+    const page = parseInt(searchParams.get('page') || '1', 10);
+    const pageSize = parseInt(searchParams.get('page_size') || '10', 10);
+    const search = searchParams.get('search');
+    const team = searchParams.get('team');
+
+    // Calculate OFFSET
+    const offset = (page - 1) * pageSize;
+
+    // Build WHERE conditions
+    const whereConditions = ['deleted_at IS NULL'];
+    
+    if (search) {
+      // Expand search to include more fields: slug, shortUrl in attributes, team name, tag name, original_url
+      whereConditions.push(`(
+        slug ILIKE '%${search}%' OR 
+        original_url ILIKE '%${search}%' OR 
+        title ILIKE '%${search}%' OR
+        JSONHas(attributes, 'shortUrl') AND JSONExtractString(attributes, 'shortUrl') ILIKE '%${search}%' OR
+        arrayExists(x -> JSONExtractString(x, 'team_name') ILIKE '%${search}%', JSONExtractArrayRaw(teams)) OR
+        arrayExists(x -> JSONExtractString(x, 'tag_name') ILIKE '%${search}%', JSONExtractArrayRaw(tags))
+      )`);
+    }
+    
+    if (team) {
+      whereConditions.push(`arrayExists(x -> JSONExtractString(x, 'team_id') = '${team}', JSONExtractArrayRaw(teams))`);
+    }
+    
+    const whereClause = whereConditions.join(' AND ');
+
+    // First query to get total count
+    const countQuery = `
+      SELECT count(*) as total
+      FROM shorturl_analytics.shorturl
+      WHERE ${whereClause}
+    `;
+
+    const countResult = await executeQuery(countQuery);
+    // Handle the result safely by using an explicit type check
+    const total = Array.isArray(countResult) && countResult.length > 0 && typeof countResult[0] === 'object' && countResult[0] !== null && 'total' in countResult[0]
+      ? Number(countResult[0].total)
+      : 0;
+    const totalPages = Math.ceil(total / pageSize);
+
+    // Main query with pagination
+    const query = `
+      SELECT 
+        id,
+        external_id,
+        type,
+        slug,
+        original_url,
+        title,
+        description,
+        attributes,
+        schema_version,
+        creator_id,
+        creator_email,
+        creator_name,
+        created_at,
+        updated_at,
+        deleted_at,
+        projects,
+        teams,
+        tags,
+        qr_codes AS qr_codes,
+        channels,
+        favorites,
+        expires_at,
+        click_count,
+        unique_visitors
+      FROM shorturl_analytics.shorturl
+      WHERE ${whereClause}
+      ORDER BY created_at DESC
+      LIMIT ${pageSize} OFFSET ${offset}
+    `;
+
+    // Execute the query using the shared client
+    const rows = await executeQuery(query);
+
+    // Return the data with pagination metadata
+    return NextResponse.json({ 
+      links: rows,
+      total: total,
+      total_pages: totalPages,
+      page: page,
+      page_size: pageSize
+    });
+  } catch (error) {
+    console.error('Error fetching shortlinks from ClickHouse:', error);
+    return NextResponse.json(
+      { error: 'Failed to fetch shortlinks' },
+      { status: 500 }
+    );
+  }
+} 

app/api/types.ts

@@ -54,6 +54,8 @@ export interface Event {
   utm_source: string;
   utm_medium: string;
   utm_campaign: string;
+  utm_term: string;
+  utm_content: string;
   
   // 交互信息
   time_spent_sec: number;

app/components/analytics/UtmAnalytics.tsx

@@ -0,0 +1,203 @@
+"use client";
+
+import { useState, useEffect } from 'react';
+
+interface UtmData {
+  utm_value: string;
+  clicks: number;
+  visitors: number;
+  avg_time_spent: number;
+  bounces: number;
+  conversions: number;
+}
+
+interface UtmAnalyticsProps {
+  startTime?: string;
+  endTime?: string;
+  linkId?: string;
+  teamIds?: string[];
+  projectIds?: string[];
+  tagIds?: string[];
+}
+
+export default function UtmAnalytics({ startTime, endTime, linkId, teamIds, projectIds, tagIds }: UtmAnalyticsProps) {
+  const [activeTab, setActiveTab] = useState<string>('source');
+  const [utmData, setUtmData] = useState<UtmData[]>([]);
+  const [isLoading, setIsLoading] = useState<boolean>(false);
+  const [error, setError] = useState<string | null>(null);
+  
+  // 加载UTM数据
+  useEffect(() => {
+    const fetchUtmData = async () => {
+      setIsLoading(true);
+      setError(null);
+      
+      try {
+        // 构建URL参数
+        const params = new URLSearchParams();
+        if (startTime) params.append('startTime', startTime);
+        if (endTime) params.append('endTime', endTime);
+        if (linkId) params.append('linkId', linkId);
+        params.append('utmType', activeTab);
+        
+        // 添加团队ID参数
+        if (teamIds && teamIds.length > 0) {
+          teamIds.forEach(id => params.append('teamId', id));
+        }
+        
+        // 添加项目ID参数
+        if (projectIds && projectIds.length > 0) {
+          projectIds.forEach(id => params.append('projectId', id));
+        }
+        
+        // 添加标签名称参数
+        if (tagIds && tagIds.length > 0) {
+          tagIds.forEach(tagName => params.append('tagName', tagName));
+        }
+        
+        // 发送请求
+        const response = await fetch(`/api/events/utm?${params}`);
+        
+        if (!response.ok) {
+          throw new Error('Failed to fetch UTM data');
+        }
+        
+        const result = await response.json();
+        
+        if (result.success) {
+          setUtmData(result.data || []);
+        } else {
+          throw new Error(result.error || 'Failed to fetch UTM data');
+        }
+      } catch (err) {
+        setError(err instanceof Error ? err.message : 'Unknown error occurred');
+        console.error('Error fetching UTM data:', err);
+      } finally {
+        setIsLoading(false);
+      }
+    };
+    
+    fetchUtmData();
+  }, [activeTab, startTime, endTime, linkId, teamIds, projectIds, tagIds]);
+  
+  // 安全地格式化数字
+  const formatNumber = (value: number | undefined | null): string => {
+    if (value === undefined || value === null) return '0';
+    return value.toLocaleString();
+  };
+  
+  return (
+    <div className="bg-white rounded-lg shadow p-6 mb-8">
+      <h2 className="text-lg font-semibold text-gray-900 mb-4">UTM Parameters</h2>
+      
+      <div className="mb-4 border-b">
+        <div className="flex">
+          <button 
+            onClick={() => setActiveTab('source')} 
+            className={`px-4 py-2 ${activeTab === 'source' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Source
+          </button>
+          <button 
+            onClick={() => setActiveTab('medium')} 
+            className={`px-4 py-2 ${activeTab === 'medium' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Medium
+          </button>
+          <button 
+            onClick={() => setActiveTab('campaign')} 
+            className={`px-4 py-2 ${activeTab === 'campaign' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Campaign
+          </button>
+          <button 
+            onClick={() => setActiveTab('term')} 
+            className={`px-4 py-2 ${activeTab === 'term' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Term
+          </button>
+          <button 
+            onClick={() => setActiveTab('content')} 
+            className={`px-4 py-2 ${activeTab === 'content' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Content
+          </button>
+        </div>
+      </div>
+      
+      {isLoading ? (
+        <div className="flex justify-center items-center py-8">
+          <div className="animate-spin rounded-full h-6 w-6 border-b-2 border-blue-500"></div>
+          <span className="ml-2 text-gray-500">Loading...</span>
+        </div>
+      ) : error ? (
+        <div className="text-red-500 text-center py-8">
+          Error: {error}
+        </div>
+      ) : utmData.length === 0 ? (
+        <div className="text-gray-500 text-center py-8">
+          No data available
+        </div>
+      ) : (
+        <div className="overflow-x-auto">
+          <table className="min-w-full divide-y divide-gray-200">
+            <thead className="bg-gray-50">
+              <tr>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  {activeTab === 'source' ? 'Source' : 
+                   activeTab === 'medium' ? 'Medium' : 
+                   activeTab === 'campaign' ? 'Campaign' : 
+                   activeTab === 'term' ? 'Term' : 'Content'}
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Clicks
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Visitors
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Avg. Time
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Bounce Rate
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Conversions
+                </th>
+              </tr>
+            </thead>
+            <tbody className="bg-white divide-y divide-gray-200">
+              {utmData.map((item, index) => {
+                const bounceRate = item.clicks > 0 ? (item.bounces / item.clicks) * 100 : 0;
+                const conversionRate = item.clicks > 0 ? (item.conversions / item.clicks) * 100 : 0;
+                
+                return (
+                  <tr key={index} className={index % 2 === 0 ? 'bg-white' : 'bg-gray-50'}>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {item.utm_value || 'Unknown'}
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {formatNumber(item.clicks)}
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {formatNumber(item.visitors)}
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {item.avg_time_spent.toFixed(1)}s
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {bounceRate.toFixed(1)}%
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {formatNumber(item.conversions)} ({conversionRate.toFixed(1)}%)
+                    </td>
+                  </tr>
+                );
+              })}
+            </tbody>
+          </table>
+        </div>
+      )}
+    </div>
+  );
+} 

app/components/charts/TimeSeriesChart.tsx

@@ -137,6 +137,11 @@ export default function TimeSeriesChart({ data }: TimeSeriesChartProps) {
                   return date.toLocaleDateString();
                 }
                 return '';
+              },
+              label: (context) => {
+                const label = context.dataset.label || '';
+                const value = context.parsed.y;
+                return `${label}: ${Math.round(value)}`;
               }
             }
           }
@@ -160,9 +165,9 @@ export default function TimeSeriesChart({ data }: TimeSeriesChartProps) {
               callback: (value: number) => {
                 if (!value && value !== 0) return '';
                 if (value >= 1000) {
-                  return `${(value / 1000).toFixed(1)}k`;
+                  return `${Math.round(value / 1000)}k`;
                 }
-                return value;
+                return Math.round(value);
               }
             }
           }

app/components/layout/Header.tsx

@@ -14,7 +14,7 @@ export default function Header() {
     <header className="w-full py-4 border-b border-gray-200 bg-white">
       <div className="container flex items-center justify-between px-4 mx-auto">
         <div className="flex items-center space-x-4">
-          <Link href="/" className="flex items-center space-x-2">
+          <Link href="/analytics" className="flex items-center space-x-2">
             <svg
               className="w-6 h-6 text-blue-500"
               xmlns="http://www.w3.org/2000/svg"
@@ -30,6 +30,23 @@ export default function Header() {
             </svg>
             <span className="text-xl font-bold text-gray-900">ShortURL Analytics</span>
           </Link>
+          
+          {user && (
+            <nav className="ml-6">
+              <ul className="flex space-x-4">
+                <li>
+                  <Link href="/analytics" className="text-sm text-gray-700 hover:text-blue-500">
+                    Analytics
+                  </Link>
+                </li>
+                <li>
+                  <Link href="/links" className="text-sm text-gray-700 hover:text-blue-500">
+                    Short Links
+                  </Link>
+                </li>
+              </ul>
+            </nav>
+          )}
         </div>
         
         {user && (

app/ip-test/page.tsx

@@ -1,10 +0,0 @@
-import IpLocationTest from '../components/ipLocationTest';
-
-export default function IpTestPage() {
-  return (
-    <div className="container mx-auto p-4 max-w-4xl">
-      <h1 className="text-2xl font-bold mb-6">IP to Location Test</h1>
-      <IpLocationTest />
-    </div>
-  );
-} 

app/links/page.tsx

@@ -0,0 +1,653 @@
+"use client";
+
+import { useEffect, useState } from 'react';
+import { getSupabaseClient } from '../utils/supabase';
+import { AuthChangeEvent } from '@supabase/supabase-js';
+import { Loader2, ExternalLink, Search } from 'lucide-react';
+import { TeamSelector } from '@/app/components/ui/TeamSelector';
+import { useRouter } from 'next/navigation';
+import { useShortUrlStore, ShortUrlData } from '@/app/utils/store';
+
+// Define attribute type to avoid using 'any'
+interface LinkAttributes {
+  title?: string;
+  name?: string;
+  slug?: string;
+  original_url?: string;
+  originalUrl?: string;
+  visits?: number;
+  click_count?: number;
+  team_id?: string;
+  team_name?: string;
+  tags?: string[];
+  [key: string]: unknown;
+}
+
+// 更新 ShortLink 类型定义以匹配 ClickHouse 数据结构
+interface ShortLink {
+  id: string;
+  external_id?: string;
+  type?: string;
+  slug?: string;
+  original_url?: string;
+  title?: string;
+  description?: string;
+  attributes: string | Record<string, unknown>;
+  schema_version?: number;
+  creator_id?: string;
+  creator_email?: string;
+  creator_name?: string;
+  created_at: string;
+  updated_at?: string;
+  deleted_at?: string | null;
+  projects?: string | Record<string, unknown>[];
+  teams?: string | Record<string, unknown>[];
+  tags?: string | Record<string, unknown>[];
+  qr_codes?: string | Record<string, unknown>[];
+  channels?: string | Record<string, unknown>[];
+  favorites?: string | Record<string, unknown>[];
+  expires_at?: string | null;
+  click_count?: number;
+  unique_visitors?: number;
+}
+
+// Define ClickHouse shorturl type
+interface ClickHouseShortUrl {
+  id: string;
+  external_id: string;
+  type: string;
+  slug: string;
+  original_url: string;
+  title: string;
+  description: string;
+  attributes: string; // JSON string
+  schema_version: number;
+  creator_id: string;
+  creator_email: string;
+  creator_name: string;
+  created_at: string;
+  updated_at: string;
+  deleted_at: string | null;
+  projects: string; // JSON string
+  teams: string; // JSON string
+  tags: string; // JSON string
+  qr_codes: string; // JSON string
+  channels: string; // JSON string
+  favorites: string; // JSON string
+  expires_at: string | null;
+  click_count: number;
+  unique_visitors: number;
+  link_attributes?: string; // Optional JSON string containing link-specific attributes
+}
+
+// 示例团队数据 - 实际应用中应从API获取
+const teams = [
+  { id: 'marketing', name: 'Marketing' },
+  { id: 'sales', name: 'Sales' },
+  { id: 'product', name: 'Product' },
+  { id: 'engineering', name: 'Engineering' }
+];
+
+// 将 ClickHouse 数据转换为 ShortLink 格式
+const convertClickHouseToShortLink = (data: Record<string, unknown>): ShortLink => {
+  return {
+    ...data as any, // 使用类型断言处理泛型记录转换
+    // 确保关键字段存在
+    id: data.id as string || '',
+    created_at: data.created_at as string || new Date().toISOString(),
+    attributes: data.attributes || '{}'
+  };
+};
+
+export default function LinksPage() {
+  const [loading, setLoading] = useState(true);
+  const [error, setError] = useState<string | null>(null);
+  const [links, setLinks] = useState<ShortLink[]>([]);
+  const [searchQuery, setSearchQuery] = useState('');
+  const [teamFilter, setTeamFilter] = useState<string | null>(null);
+  const [currentPage, setCurrentPage] = useState(1);
+  const [pageSize, setPageSize] = useState(10);
+  const [totalLinks, setTotalLinks] = useState(0);
+  const [totalPages, setTotalPages] = useState(0);
+  const [searchDebounce, setSearchDebounce] = useState<NodeJS.Timeout | null>(null);
+  const router = useRouter();
+  
+  // 使用 Zustand store
+  const { setSelectedShortUrl } = useShortUrlStore();
+
+  // 处理点击链接行
+  const handleRowClick = (link: any) => {
+    // 解析 attributes 字符串为对象
+    let attributes: Record<string, any> = {};
+    try {
+      if (link.attributes && typeof link.attributes === 'string') {
+        attributes = JSON.parse(link.attributes || '{}');
+      }
+    } catch (e) {
+      console.error('Error parsing link attributes:', e);
+    }
+    
+    // 解析 teams 字符串为数组
+    let teams: any[] = [];
+    try {
+      if (link.teams && typeof link.teams === 'string') {
+        teams = JSON.parse(link.teams || '[]');
+      }
+    } catch (e) {
+      console.error('Error parsing teams:', e);
+    }
+    
+    // 解析 projects 字符串为数组
+    let projects: any[] = [];
+    try {
+      if (link.projects && typeof link.projects === 'string') {
+        projects = JSON.parse(link.projects || '[]');
+      }
+    } catch (e) {
+      console.error('Error parsing projects:', e);
+    }
+    
+    // 解析 tags 字符串为数组
+    let tags: string[] = [];
+    try {
+      if (link.tags && typeof link.tags === 'string') {
+        const parsedTags = JSON.parse(link.tags);
+        if (Array.isArray(parsedTags)) {
+          tags = parsedTags.map((tag: { tag_name?: string }) => tag.tag_name || '');
+        }
+      }
+    } catch (e) {
+      console.error('Error parsing tags:', e);
+    }
+
+    // 确保 shortUrl 存在
+    const shortUrlValue = attributes.shortUrl || '';
+    
+    // 提取用于显示的字段
+    const shortUrlData = {
+      id: link.id,
+      externalId: link.external_id, // 明确添加 externalId 字段
+      slug: link.slug,
+      originalUrl: link.original_url,
+      title: link.title,
+      shortUrl: shortUrlValue,
+      teams: teams,
+      projects: projects,
+      tags: tags,
+      createdAt: link.created_at,
+      domain: shortUrlValue ? new URL(shortUrlValue).hostname : 'shorturl.example.com'
+    };
+    
+    // 打印完整数据，确保 externalId 被包含
+    console.log('Setting shortURL data in store with externalId:', link.external_id);
+    
+    // 将数据保存到 Zustand store
+    setSelectedShortUrl(shortUrlData);
+    
+    // 导航到分析页面，并在 URL 中包含 shortUrl 参数
+    router.push(`/analytics?shorturl=${encodeURIComponent(shortUrlValue)}`);
+  };
+
+  // Extract link metadata from attributes
+  const getLinkMetadata = (link: ShortLink) => {
+    try {
+      // Parse attributes if it's a string
+      const attributes = typeof link.attributes === 'string'
+        ? JSON.parse(link.attributes)
+        : link.attributes || {};
+      
+      // Parse attributes to get domain if available
+      let domain = 'shorturl.example.com';
+      try {
+        // Extract domain from shortUrl in attributes if available
+        const attributesObj = typeof link.attributes === 'string'
+          ? JSON.parse(link.attributes)
+          : link.attributes || {};
+        
+        if (attributesObj.shortUrl) {
+          try {
+            const urlObj = new URL(attributesObj.shortUrl);
+            domain = urlObj.hostname;
+          } catch (e) {
+            console.error('Error parsing shortUrl:', e);
+          }
+        }
+      } catch (e) {
+        console.error('Error parsing attributes:', e);
+      }
+      
+      // Get team names
+      const teamNames: string[] = [];
+      try {
+        if (link.teams) {
+          const teams = typeof link.teams === 'string'
+            ? JSON.parse(link.teams)
+            : link.teams || [];
+          
+          if (Array.isArray(teams)) {
+            teams.forEach(team => {
+              if (team.team_name) {
+                teamNames.push(team.team_name);
+              }
+            });
+          }
+        }
+      } catch (e) {
+        console.error('Error parsing teams:', e);
+      }
+      
+      // Get project names
+      const projectNames: string[] = [];
+      try {
+        if (link.projects) {
+          const projects = typeof link.projects === 'string'
+            ? JSON.parse(link.projects)
+            : link.projects || [];
+          
+          if (Array.isArray(projects)) {
+            projects.forEach(project => {
+              if (project.project_name) {
+                projectNames.push(project.project_name);
+              }
+            });
+          }
+        }
+      } catch (e) {
+        console.error('Error parsing projects:', e);
+      }
+      
+      // Get tag names
+      const tagNames: string[] = [];
+      try {
+        if (link.tags) {
+          const tags = typeof link.tags === 'string'
+            ? JSON.parse(link.tags)
+            : link.tags || [];
+          
+          if (Array.isArray(tags)) {
+            tags.forEach(tag => {
+              if (tag.tag_name) {
+                tagNames.push(tag.tag_name);
+              }
+            });
+          }
+        }
+      } catch (e) {
+        console.error('Error parsing tags:', e);
+      }
+      
+      return {
+        title: link.title || attributes.title || 'Untitled',
+        slug: link.slug || attributes.slug || '',
+        domain: domain,
+        originalUrl: link.original_url || attributes.originalUrl || attributes.original_url || '',
+        teamNames: teamNames,
+        projectNames: projectNames,
+        tagNames: tagNames,
+        teamName: teamNames[0] || '',  // Keep for backward compatibility
+        createdAt: new Date(link.created_at).toLocaleDateString(),
+        visits: link.click_count || 0
+      };
+    } catch (error) {
+      console.error('Error parsing link metadata:', error);
+      return {
+        title: 'Error parsing data',
+        slug: '',
+        domain: 'shorturl.example.com',
+        originalUrl: '',
+        teamNames: [],
+        projectNames: [],
+        tagNames: [],
+        teamName: '',
+        createdAt: '',
+        visits: 0
+      };
+    }
+  };
+
+  useEffect(() => {
+    let isMounted = true;
+
+    const fetchLinks = async () => {
+      if (!isMounted) return;
+      setLoading(true);
+      setError(null);
+      
+      try {
+        // Fetch data from ClickHouse API with pagination parameters
+        const response = await fetch(`/api/shortlinks?page=${currentPage}&page_size=${pageSize}${searchQuery ? `&search=${encodeURIComponent(searchQuery)}` : ''}${teamFilter ? `&team=${encodeURIComponent(teamFilter)}` : ''}`);
+        
+        if (!response.ok) {
+          throw new Error(`Failed to fetch links: ${response.status} ${response.statusText}`);
+        }
+        
+        const data = await response.json();
+        
+        if (!data || !data.links || data.links.length === 0) {
+          if (isMounted) {
+            setLinks([]);
+            setTotalLinks(0);
+            setTotalPages(0);
+          }
+          return;
+        }
+        
+        // Convert ClickHouse data format to ShortLink format
+        const convertedLinks = data.links.map(convertClickHouseToShortLink);
+        
+        if (isMounted) {
+          setLinks(convertedLinks);
+          setTotalLinks(data.total || convertedLinks.length);
+          setTotalPages(data.total_pages || Math.ceil(data.total / pageSize) || 1);
+        }
+      } catch (err) {
+        if (isMounted) {
+          setError(err instanceof Error ? err.message : 'Failed to load short URLs');
+          console.error("Error fetching links:", err);
+        }
+      } finally {
+        if (isMounted) {
+          setLoading(false);
+        }
+      }
+    };
+
+    // Subscribe to user auth state
+    const supabase = getSupabaseClient();
+    const { data: { subscription } } = supabase.auth.onAuthStateChange(
+      (event: AuthChangeEvent) => {
+        if (event === 'SIGNED_IN' || event === 'USER_UPDATED') {
+          fetchLinks();
+        }
+        if (event === 'SIGNED_OUT') {
+          setLinks([]);
+        }
+      }
+    );
+
+    fetchLinks();
+
+    return () => {
+      isMounted = false;
+      subscription.unsubscribe();
+    };
+  }, [currentPage, pageSize, searchQuery, teamFilter]);
+
+  // Handle search input with debounce
+  const handleSearchChange = (e: React.ChangeEvent<HTMLInputElement>) => {
+    const value = e.target.value;
+    
+    // Clear any existing timeout
+    if (searchDebounce) {
+      clearTimeout(searchDebounce);
+    }
+    
+    // Set the input value immediately for UI feedback
+    setSearchQuery(value);
+    
+    // Set a timeout to actually perform the search
+    setSearchDebounce(setTimeout(() => {
+      setCurrentPage(1); // Reset to page 1 when searching
+    }, 500)); // 500ms debounce
+  };
+
+  if (loading && links.length === 0) {
+    return (
+      <div className="flex h-96 w-full items-center justify-center">
+        <Loader2 className="h-8 w-8 animate-spin text-gray-500" />
+      </div>
+    );
+  }
+
+  if (error) {
+    return (
+      <div className="flex h-96 w-full flex-col items-center justify-center text-red-500">
+        <p>Error loading shortcuts: {error}</p>
+        <button 
+          onClick={() => window.location.reload()} 
+          className="mt-4 rounded-md bg-blue-500 px-4 py-2 text-white hover:bg-blue-600"
+        >
+          Retry
+        </button>
+      </div>
+    );
+  }
+
+  return (
+    <div className="container mx-auto px-4 py-8">
+      <h1 className="mb-6 text-2xl font-bold text-gray-900">Short URL Links</h1>
+      
+      {/* Search and filters */}
+      <div className="mb-6 flex flex-wrap items-center gap-4">
+        <div className="relative flex-grow">
+          <Search className="absolute left-3 top-1/2 h-4 w-4 -translate-y-1/2 text-gray-400" />
+          <input
+            type="text"
+            placeholder="Search links..."
+            value={searchQuery}
+            onChange={handleSearchChange}
+            onKeyDown={(e) => {
+              if (e.key === 'Enter') {
+                setCurrentPage(1); // Reset to page 1 when searching
+              }
+            }}
+            className="w-full rounded-md border border-gray-300 py-2 pl-10 pr-4 text-sm focus:border-blue-500 focus:outline-none focus:ring-1 focus:ring-blue-500"
+          />
+        </div>
+        
+        <div className="flex items-center gap-2">
+          <TeamSelector 
+            value={teamFilter || ''}
+            onChange={(value) => {
+              // 如果是多选模式，值将是数组。对于空数组，设置为 null
+              if (Array.isArray(value)) {
+                setTeamFilter(value.length > 0 ? value[0] : null);
+              } else {
+                setTeamFilter(value || null);
+              }
+              setCurrentPage(1); // Reset to page 1 when filtering
+            }}
+            className="w-64"
+            multiple={true}
+          />
+        </div>
+      </div>
+      
+      {/* Links table */}
+      <div className="overflow-hidden rounded-lg border border-gray-200 shadow">
+        <table className="min-w-full divide-y divide-gray-200">
+          <thead className="bg-gray-50">
+            <tr>
+              <th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Link</th>
+              <th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Original URL</th>
+              <th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Team</th>
+              <th scope="col" className="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider text-gray-500">Created</th>
+            </tr>
+          </thead>
+          <tbody className="divide-y divide-gray-200 bg-white">
+            {links.map(link => {
+              const metadata = getLinkMetadata(link);
+              const shortUrl = `https://${metadata.domain}/${metadata.slug}`;
+              
+              return (
+                <tr key={link.id} className="hover:bg-gray-50 cursor-pointer" onClick={() => handleRowClick(link)}>
+                  <td className="px-6 py-4">
+                    <div className="flex flex-col space-y-1">
+                      <span className="font-medium text-gray-900">{metadata.title}</span>
+                      <span className="text-xs text-blue-500">{shortUrl}</span>
+                      
+                      {/* Tags */}
+                      {metadata.tagNames.length > 0 && (
+                        <div className="flex flex-wrap gap-1.5 mt-1">
+                          {metadata.tagNames.map((tag, index) => (
+                            <span key={index} className="inline-flex items-center rounded-full bg-gray-100 px-2 py-0.5 text-xs font-medium text-gray-800">
+                              {tag}
+                            </span>
+                          ))}
+                        </div>
+                      )}
+                    </div>
+                  </td>
+                  <td className="px-6 py-4 text-sm text-gray-500">
+                    <a 
+                      href={metadata.originalUrl} 
+                      target="_blank" 
+                      rel="noopener noreferrer"
+                      className="flex items-center hover:text-blue-500"
+                    >
+                      <span className="max-w-xs truncate">{metadata.originalUrl}</span>
+                      <ExternalLink className="ml-1 h-3 w-3" />
+                    </a>
+                  </td>
+                  <td className="px-6 py-4 text-sm text-gray-500">
+                    <div className="flex flex-col space-y-1">
+                      {/* Teams */}
+                      {metadata.teamNames.length > 0 ? (
+                        <div className="flex flex-wrap gap-1.5">
+                          {metadata.teamNames.map((team, index) => (
+                            <span key={index} className="inline-flex items-center rounded-full bg-blue-100 px-2 py-0.5 text-xs font-medium text-blue-800">
+                              {team}
+                            </span>
+                          ))}
+                        </div>
+                      ) : (
+                        <span>-</span>
+                      )}
+                      
+                      {/* Projects */}
+                      {metadata.projectNames.length > 0 && (
+                        <div className="flex flex-wrap gap-1.5 mt-1">
+                          {metadata.projectNames.map((project, index) => (
+                            <span key={index} className="inline-flex items-center rounded-full bg-green-100 px-2 py-0.5 text-xs font-medium text-green-800">
+                              {project}
+                            </span>
+                          ))}
+                        </div>
+                      )}
+                    </div>
+                  </td>
+                  <td className="px-6 py-4 text-sm text-gray-500">
+                    {metadata.createdAt}
+                  </td>
+                </tr>
+              );
+            })}
+          </tbody>
+        </table>
+      </div>
+      
+      {/* Pagination */}
+      {totalPages > 0 && (
+        <div className="mt-6 flex items-center justify-between">
+          <div className="text-sm text-gray-500">
+            Showing {((currentPage - 1) * pageSize) + 1} to {Math.min(currentPage * pageSize, totalLinks)} of {totalLinks} results
+          </div>
+          <div className="flex items-center space-x-2">
+            <button
+              onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
+              disabled={currentPage === 1}
+              className="rounded-md border border-gray-300 px-3 py-1.5 text-sm disabled:opacity-50"
+            >
+              Previous
+            </button>
+            {Array.from({ length: Math.min(totalPages, 5) }, (_, i) => {
+              // Create a window of 5 pages around current page
+              let pageNumber;
+              if (totalPages <= 5) {
+                pageNumber = i + 1;
+              } else {
+                const start = Math.max(1, currentPage - 2);
+                const end = Math.min(totalPages, start + 4);
+                pageNumber = start + i;
+                if (pageNumber > end) return null;
+              }
+              
+              return (
+                <button
+                  key={pageNumber}
+                  onClick={() => setCurrentPage(pageNumber)}
+                  className={`h-8 w-8 rounded-md text-sm ${
+                    currentPage === pageNumber 
+                      ? 'bg-blue-500 text-white' 
+                      : 'border border-gray-300 text-gray-700 hover:bg-gray-50'
+                  }`}
+                >
+                  {pageNumber}
+                </button>
+              );
+            })}
+            <button
+              onClick={() => setCurrentPage(prev => Math.min(prev + 1, totalPages))}
+              disabled={currentPage === totalPages}
+              className="rounded-md border border-gray-300 px-3 py-1.5 text-sm disabled:opacity-50"
+            >
+              Next
+            </button>
+            
+            {/* Page input */}
+            <div className="ml-4 flex items-center space-x-1">
+              <span className="text-sm text-gray-500">Go to:</span>
+              <input
+                type="number"
+                min="1"
+                max={totalPages}
+                value={currentPage}
+                onChange={(e) => {
+                  // Allow input to be cleared for typing
+                  if (e.target.value === '') {
+                    e.target.value = '';
+                  }
+                }}
+                onBlur={(e) => {
+                  // Ensure a valid value on blur
+                  const value = parseInt(e.target.value, 10);
+                  if (isNaN(value) || value < 1) {
+                    setCurrentPage(1);
+                  } else if (value > totalPages) {
+                    setCurrentPage(totalPages);
+                  } else {
+                    setCurrentPage(value);
+                  }
+                }}
+                onKeyDown={(e) => {
+                  if (e.key === 'Enter') {
+                    const value = parseInt(e.currentTarget.value, 10);
+                    if (!isNaN(value) && value >= 1 && value <= totalPages) {
+                      setCurrentPage(value);
+                    } else if (!isNaN(value) && value < 1) {
+                      setCurrentPage(1);
+                    } else if (!isNaN(value) && value > totalPages) {
+                      setCurrentPage(totalPages);
+                    }
+                  }
+                }}
+                className="w-16 rounded-md border border-gray-300 px-2 py-1 text-sm text-center"
+              />
+              <span className="text-sm text-gray-500">of {totalPages}</span>
+            </div>
+            
+            <select
+              value={pageSize}
+              onChange={(e) => {
+                setPageSize(Number(e.target.value));
+                setCurrentPage(1); // Reset to page 1 when changing page size
+              }}
+              className="ml-4 rounded-md border border-gray-300 py-1.5 pl-3 pr-8 text-sm"
+            >
+              <option value="10">10 per page</option>
+              <option value="25">25 per page</option>
+              <option value="50">50 per page</option>
+              <option value="100">100 per page</option>
+            </select>
+          </div>
+        </div>
+      )}
+      
+      {links.length === 0 && (
+        <div className="mt-6 rounded-md bg-gray-50 p-6 text-center text-gray-500">
+          No links match your search criteria
+        </div>
+      )}
+    </div>
+  );
+} 

app/page.tsx

@@ -1,803 +1,5 @@
-"use client";
+import { redirect } from 'next/navigation';
 
-import { useState, useEffect } from 'react';
-import { format, subDays } from 'date-fns';
-import { DateRangePicker } from '@/app/components/ui/DateRangePicker';
-import TimeSeriesChart from '@/app/components/charts/TimeSeriesChart';
-import GeoAnalytics from '@/app/components/analytics/GeoAnalytics';
-import DevicePieCharts from '@/app/components/charts/DevicePieCharts';
-import { EventsSummary, TimeSeriesData, GeoData, DeviceAnalytics as DeviceAnalyticsType } from '@/app/api/types';
-import { TeamSelector } from '@/app/components/ui/TeamSelector';
-import { ProjectSelector } from '@/app/components/ui/ProjectSelector';
-import { TagSelector } from '@/app/components/ui/TagSelector';
-
-// 事件类型定义
-interface Event {
-  event_id?: string;
-  url_id: string;
-  url: string;
-  event_type: string;
-  visitor_id: string;
-  created_at: string;
-  event_time?: string;
-  referrer?: string;
-  browser?: string;
-  os?: string;
-  device_type?: string;
-  country?: string;
-  city?: string;
-  event_attributes?: string;
-  link_attributes?: string;
-  user_attributes?: string;
-  link_label?: string;
-  link_original_url?: string;
-  team_name?: string;
-  project_name?: string;
-  link_id?: string;
-  link_slug?: string;
-  link_tags?: string;
-  ip_address?: string;
-}
-
-// 格式化日期函数
-const formatDate = (dateString: string | undefined) => {
-  if (!dateString) return '';
-  try {
-    return format(new Date(dateString), 'yyyy-MM-dd HH:mm:ss');
-  } catch {
-    return dateString;
-  }
-};
-
-// 解析JSON字符串
-const parseJsonSafely = (jsonString: string) => {
-  if (!jsonString) return null;
-  try {
-    return JSON.parse(jsonString);
-  } catch {
-    return null;
-  }
-};
-
-// 获取用户可读名称
-const getUserDisplayName = (user: Record<string, unknown> | null) => {
-  if (!user) return '-';
-  if (typeof user.full_name === 'string') return user.full_name;
-  if (typeof user.name === 'string') return user.name;
-  if (typeof user.email === 'string') return user.email;
-  return '-';
-};
-
-// 提取链接和事件的重要信息
-const extractEventInfo = (event: Event) => {
-  // 解析事件属性
-  const eventAttrs = parseJsonSafely(event.event_attributes || '{}');
-  
-  // 解析链接属性
-  const linkAttrs = parseJsonSafely(event.link_attributes || '{}');
-  
-  // 解析用户属性
-  const userAttrs = parseJsonSafely(event.user_attributes || '{}');
-  
-  // 解析标签信息
-  let tags: string[] = [];
-  try {
-    if (event.link_tags) {
-      const parsedTags = JSON.parse(event.link_tags);
-      if (Array.isArray(parsedTags)) {
-        tags = parsedTags;
-      }
-    }
-  } catch {
-    // 解析失败则保持空数组
-  }
-  
-  return {
-    eventTime: event.created_at || event.event_time,
-    linkName: event.link_label || linkAttrs?.name || eventAttrs?.link_name || event.link_slug || '-',
-    originalUrl: event.link_original_url || eventAttrs?.origin_url || '-',
-    eventType: event.event_type || '-',
-    visitorId: event.visitor_id?.substring(0, 8) || '-',
-    referrer: eventAttrs?.referrer || '-',
-    ipAddress: event.ip_address || '-',
-    location: event.country ? (event.city ? `${event.city}, ${event.country}` : event.country) : '-',
-    device: event.device_type || '-',
-    browser: event.browser || '-',
-    os: event.os || '-',
-    userInfo: getUserDisplayName(userAttrs),
-    teamName: event.team_name || '-',
-    projectName: event.project_name || '-',
-    tags: tags
-  };
-};
-
-export default function HomePage() {
-  // 默认日期范围为最近7天
-  const today = new Date();
-  const [dateRange, setDateRange] = useState({
-    from: subDays(today, 7), // 7天前
-    to: today // 今天
-  });
-  
-  // 添加团队选择状态 - 使用数组支持多选
-  const [selectedTeamIds, setSelectedTeamIds] = useState<string[]>([]);
-  // 添加项目选择状态 - 使用数组支持多选
-  const [selectedProjectIds, setSelectedProjectIds] = useState<string[]>([]);
-  // 添加标签选择状态 - 使用数组支持多选
-  const [selectedTagIds, setSelectedTagIds] = useState<string[]>([]);
-  
-  // 添加分页状态
-  const [currentPage, setCurrentPage] = useState<number>(1);
-  const [pageSize, setPageSize] = useState<number>(10);
-  const [totalEvents, setTotalEvents] = useState<number>(0);
-  
-  const [loading, setLoading] = useState(true);
-  const [error, setError] = useState<string | null>(null);
-  const [summary, setSummary] = useState<EventsSummary | null>(null);
-  const [timeSeriesData, setTimeSeriesData] = useState<TimeSeriesData[]>([]);
-  const [geoData, setGeoData] = useState<GeoData[]>([]);
-  const [deviceData, setDeviceData] = useState<DeviceAnalyticsType | null>(null);
-  const [events, setEvents] = useState<Event[]>([]);
-
-  useEffect(() => {
-    const fetchData = async () => {
-      setLoading(true);
-      setError(null);
-
-      try {
-        const startTime = format(dateRange.from, "yyyy-MM-dd'T'HH:mm:ss'Z'");
-        const endTime = format(dateRange.to, "yyyy-MM-dd'T'HH:mm:ss'Z'");
-
-        // 构建基础URL和查询参数
-        const baseUrl = '/api/events';
-        const params = new URLSearchParams({
-          startTime,
-          endTime,
-          page: currentPage.toString(),
-          pageSize: pageSize.toString()
-        });
-        
-        // 添加团队ID参数 - 支持多个团队
-        if (selectedTeamIds.length > 0) {
-          selectedTeamIds.forEach(teamId => {
-            params.append('teamId', teamId);
-          });
-        }
-        
-        // 添加项目ID参数 - 支持多个项目
-        if (selectedProjectIds.length > 0) {
-          selectedProjectIds.forEach(projectId => {
-            params.append('projectId', projectId);
-          });
-        }
-        
-        // 添加标签ID参数 - 支持多个标签
-        if (selectedTagIds.length > 0) {
-          selectedTagIds.forEach(tagId => {
-            params.append('tagId', tagId);
-          });
-        }
-
-        // 并行获取所有数据
-        const [summaryRes, timeSeriesRes, geoRes, deviceRes, eventsRes] = await Promise.all([
-          fetch(`${baseUrl}/summary?${params.toString()}`),
-          fetch(`${baseUrl}/time-series?${params.toString()}`),
-          fetch(`${baseUrl}/geo?${params.toString()}`),
-          fetch(`${baseUrl}/devices?${params.toString()}`),
-          fetch(`${baseUrl}?${params.toString()}`)
-        ]);
-
-        const [summaryData, timeSeriesData, geoData, deviceData, eventsData] = await Promise.all([
-          summaryRes.json(),
-          timeSeriesRes.json(),
-          geoRes.json(),
-          deviceRes.json(),
-          eventsRes.json()
-        ]);
-
-        if (!summaryRes.ok) throw new Error(summaryData.error || 'Failed to fetch summary data');
-        if (!timeSeriesRes.ok) throw new Error(timeSeriesData.error || 'Failed to fetch time series data');
-        if (!geoRes.ok) throw new Error(geoData.error || 'Failed to fetch geo data');
-        if (!deviceRes.ok) throw new Error(deviceData.error || 'Failed to fetch device data');
-        if (!eventsRes.ok) throw new Error(eventsData.error || 'Failed to fetch events data');
-
-        setSummary(summaryData.data);
-        setTimeSeriesData(timeSeriesData.data);
-        setGeoData(geoData.data);
-        setDeviceData(deviceData.data);
-        setEvents(eventsData.data || []);
-        
-        // 设置总事件数量用于分页
-        if (eventsData.meta) {
-          // 确保将total转换为数字，无论它是字符串还是数字
-          const totalCount = parseInt(String(eventsData.meta.total), 10);
-          if (!isNaN(totalCount)) {
-            setTotalEvents(totalCount);
-          }
-        }
-      } catch (err) {
-        setError(err instanceof Error ? err.message : 'An error occurred while fetching data');
-      } finally {
-        setLoading(false);
-      }
-    };
-
-    fetchData();
-  }, [dateRange, selectedTeamIds, selectedProjectIds, selectedTagIds, currentPage, pageSize]);
-
-  if (loading) {
-    return (
-      <div className="flex items-center justify-center min-h-screen">
-        <div className="animate-spin rounded-full h-12 w-12 border-t-2 border-b-2 border-blue-500" />
-      </div>
-    );
-  }
-
-  if (error) {
-    return (
-      <div className="flex items-center justify-center min-h-screen">
-        <div className="text-red-500">{error}</div>
-      </div>
-    );
-  }
-
-  return (
-    <div className="container mx-auto px-4 py-8">
-      <div className="flex justify-between items-center mb-8">
-        <h1 className="text-2xl font-bold text-gray-900">Analytics Dashboard</h1>
-        <div className="flex flex-col gap-4 md:flex-row md:items-center">
-          <TeamSelector
-            value={selectedTeamIds}
-            onChange={(value) => {
-              const newTeamIds = Array.isArray(value) ? value : [value];
-              
-              // Check if team selection has changed
-              if (JSON.stringify(newTeamIds) !== JSON.stringify(selectedTeamIds)) {
-                // Clear project selection when team changes
-                setSelectedProjectIds([]);
-                
-                // Update team selection
-                setSelectedTeamIds(newTeamIds);
-              }
-            }}
-            className="w-[250px]"
-            multiple={true}
-          />
-          <ProjectSelector
-            value={selectedProjectIds}
-            onChange={(value) => setSelectedProjectIds(Array.isArray(value) ? value : [value])}
-            className="w-[250px]"
-            multiple={true}
-            teamIds={selectedTeamIds.length > 0 ? selectedTeamIds : undefined}
-          />
-          <TagSelector
-            value={selectedTagIds}
-            onChange={(value) => setSelectedTagIds(Array.isArray(value) ? value : [value])}
-            className="w-[250px]"
-            multiple={true}
-            teamIds={selectedTeamIds.length > 0 ? selectedTeamIds : undefined}
-          />
-          <DateRangePicker
-            value={dateRange}
-            onChange={setDateRange}
-          />
-        </div>
-      </div>
-
-      {/* 显示团队选择信息 */}
-      {selectedTeamIds.length > 0 && (
-        <div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
-          <span className="text-blue-700 font-medium mr-2">
-            {selectedTeamIds.length === 1 ? 'Team filter:' : 'Teams filter:'}
-          </span>
-          <div className="flex flex-wrap gap-2">
-            {selectedTeamIds.map(teamId => (
-              <span key={teamId} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
-                {teamId}
-                <button
-                  onClick={() => setSelectedTeamIds(selectedTeamIds.filter(id => id !== teamId))}
-                  className="ml-1 text-blue-600 hover:text-blue-800"
-                >
-                  ×
-                </button>
-              </span>
-            ))}
-            {selectedTeamIds.length > 0 && (
-              <button
-                onClick={() => setSelectedTeamIds([])}
-                className="text-xs text-gray-500 hover:text-gray-700 underline"
-              >
-                Clear all
-              </button>
-            )}
-          </div>
-        </div>
-      )}
-
-      {/* 显示项目选择信息 */}
-      {selectedProjectIds.length > 0 && (
-        <div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
-          <span className="text-blue-700 font-medium mr-2">
-            {selectedProjectIds.length === 1 ? 'Project filter:' : 'Projects filter:'}
-          </span>
-          <div className="flex flex-wrap gap-2">
-            {selectedProjectIds.map(projectId => (
-              <span key={projectId} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
-                {projectId}
-                <button
-                  onClick={() => setSelectedProjectIds(selectedProjectIds.filter(id => id !== projectId))}
-                  className="ml-1 text-blue-600 hover:text-blue-800"
-                >
-                  ×
-                </button>
-              </span>
-            ))}
-            {selectedProjectIds.length > 0 && (
-              <button
-                onClick={() => setSelectedProjectIds([])}
-                className="text-xs text-gray-500 hover:text-gray-700 underline"
-              >
-                Clear all
-              </button>
-            )}
-          </div>
-        </div>
-      )}
-
-      {/* 显示标签选择信息 */}
-      {selectedTagIds.length > 0 && (
-        <div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
-          <span className="text-blue-700 font-medium mr-2">
-            {selectedTagIds.length === 1 ? 'Tag filter:' : 'Tags filter:'}
-          </span>
-          <div className="flex flex-wrap gap-2">
-            {selectedTagIds.map(tagName => (
-              <span key={tagName} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
-                {tagName}
-                <button
-                  onClick={() => setSelectedTagIds(selectedTagIds.filter(name => name !== tagName))}
-                  className="ml-1 text-blue-600 hover:text-blue-800"
-                >
-                  ×
-                </button>
-              </span>
-            ))}
-            {selectedTagIds.length > 0 && (
-              <button
-                onClick={() => setSelectedTagIds([])}
-                className="text-xs text-gray-500 hover:text-gray-700 underline"
-              >
-                Clear all
-              </button>
-            )}
-          </div>
-        </div>
-      )}
-
-      {/* 仪表板内容 - 现在放在事件列表之后 */}
-      <>
-        {summary && (
-          <div className="grid grid-cols-1 md:grid-cols-4 gap-6 mb-8">
-            <div className="bg-white rounded-lg shadow p-6">
-              <h3 className="text-sm font-medium text-gray-500">Total Events</h3>
-              <p className="text-2xl font-semibold text-gray-900">
-                {typeof summary.totalEvents === 'number' ? summary.totalEvents.toLocaleString() : summary.totalEvents}
-              </p>
-                  </div>
-            <div className="bg-white rounded-lg shadow p-6">
-              <h3 className="text-sm font-medium text-gray-500">Unique Visitors</h3>
-              <p className="text-2xl font-semibold text-gray-900">
-                {typeof summary.uniqueVisitors === 'number' ? summary.uniqueVisitors.toLocaleString() : summary.uniqueVisitors}
-              </p>
-                </div>
-            <div className="bg-white rounded-lg shadow p-6">
-              <h3 className="text-sm font-medium text-gray-500">Total Conversions</h3>
-              <p className="text-2xl font-semibold text-gray-900">
-                {typeof summary.totalConversions === 'number' ? summary.totalConversions.toLocaleString() : summary.totalConversions}
-              </p>
-              </div>
-            <div className="bg-white rounded-lg shadow p-6">
-              <h3 className="text-sm font-medium text-gray-500">Avg. Time Spent</h3>
-              <p className="text-2xl font-semibold text-gray-900">
-                {summary.averageTimeSpent?.toFixed(1) || '0'}s
-              </p>
-            </div>
-          </div>
-        )}
-
-<div className="bg-white rounded-lg shadow overflow-hidden mb-8">
-        <div className="p-6 border-b border-gray-200">
-          <h2 className="text-lg font-semibold text-gray-900 mb-4">Recent Events</h2>
-        </div>
-
-        <div className="overflow-x-auto">
-          <table className="min-w-full divide-y divide-gray-200">
-            <thead className="bg-gray-50">
-              <tr>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Time
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Link Name
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Original URL
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Event Type
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Tags
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  User
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Team/Project
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  IP/Location
-                </th>
-                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
-                  Device Info
-                </th>
-              </tr>
-            </thead>
-            <tbody className="bg-white divide-y divide-gray-200">
-              {events.map((event, index) => {
-                const info = extractEventInfo(event);
-                return (
-                  <tr key={event.event_id || index} className={index % 2 === 0 ? 'bg-white' : 'bg-gray-50'}>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      {formatDate(info.eventTime)}
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
-                      <span className="font-medium">{info.linkName}</span>
-                      <div className="text-xs text-gray-500 mt-1 truncate max-w-xs">
-                        ID: {event.link_id?.substring(0, 8) || '-'}
-                      </div>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-blue-600">
-                      <a href={info.originalUrl} className="hover:underline truncate max-w-xs block" target="_blank" rel="noopener noreferrer">
-                        {info.originalUrl}
-                      </a>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm">
-                      <span className={`px-2 inline-flex text-xs leading-5 font-semibold rounded-full ${
-                        info.eventType === 'click' 
-                          ? 'bg-green-100 text-green-800' 
-                          : 'bg-blue-100 text-blue-800'
-                      }`}>
-                        {info.eventType}
-                      </span>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      <div className="flex flex-wrap gap-1">
-                        {info.tags && info.tags.length > 0 ? (
-                          info.tags.map((tag, idx) => (
-                            <span 
-                              key={idx} 
-                              className="bg-gray-100 text-gray-800 text-xs px-2 py-0.5 rounded"
-                            >
-                              {tag}
-                            </span>
-                          ))
-                        ) : (
-                          <span className="text-gray-400">-</span>
-                        )}
-                      </div>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      <div className="font-medium">{info.userInfo}</div>
-                      <div className="text-xs text-gray-400 mt-1">{info.visitorId}...</div>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      <div className="font-medium">{info.teamName}</div>
-                      <div className="text-xs text-gray-400 mt-1">{info.projectName}</div>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      <div className="flex flex-col">
-                        <span className="text-xs inline-flex items-center mb-1">
-                          <span className="font-medium">IP:</span> 
-                          <span className="ml-1">{info.ipAddress}</span>
-                        </span>
-                        <span className="text-xs inline-flex items-center">
-                          <span className="font-medium">Location:</span> 
-                          <span className="ml-1">{info.location}</span>
-                        </span>
-                      </div>
-                    </td>
-                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
-                      <div className="flex flex-col">
-                        <span className="text-xs inline-flex items-center mb-1">
-                          <span className="font-medium">Device:</span> 
-                          <span className="ml-1">{info.device}</span>
-                        </span>
-                        <span className="text-xs inline-flex items-center mb-1">
-                          <span className="font-medium">Browser:</span> 
-                          <span className="ml-1">{info.browser}</span>
-                        </span>
-                        <span className="text-xs inline-flex items-center">
-                          <span className="font-medium">OS:</span> 
-                          <span className="ml-1">{info.os}</span>
-                        </span>
-                      </div>
-                    </td>
-                  </tr>
-                );
-              })}
-            </tbody>
-          </table>
-        </div>
-
-        {/* 表格为空状态 */}
-        {!loading && events.length === 0 && (
-          <div className="flex justify-center items-center p-8 text-gray-500">
-            No events found
-          </div>
-        )}
-        
-        {/* 分页控件 - 删除totalEvents > 0条件，改为events.length > 0 */}
-        {!loading && events.length > 0 && (
-          <div className="px-6 py-4 flex items-center justify-between border-t border-gray-200">
-            <div className="flex-1 flex justify-between sm:hidden">
-              <button
-                onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
-                disabled={currentPage === 1}
-                className={`relative inline-flex items-center px-4 py-2 border border-gray-300 text-sm font-medium rounded-md ${
-                  currentPage === 1 
-                    ? 'text-gray-300 bg-gray-50' 
-                    : 'text-gray-700 bg-white hover:bg-gray-50'
-                }`}
-              >
-                Previous
-              </button>
-              <button
-                onClick={() => setCurrentPage(prev => (currentPage < Math.ceil(totalEvents / pageSize)) ? prev + 1 : prev)}
-                disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
-                className={`ml-3 relative inline-flex items-center px-4 py-2 border border-gray-300 text-sm font-medium rounded-md ${
-                  currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize
-                    ? 'text-gray-300 cursor-not-allowed' 
-                    : 'text-gray-700 bg-white hover:bg-gray-50'
-                }`}
-              >
-                Next
-              </button>
-            </div>
-            <div className="hidden sm:flex-1 sm:flex sm:items-center sm:justify-between">
-              <div>
-                <p className="text-sm text-gray-700">
-                  Showing <span className="font-medium">{events.length > 0 ? ((currentPage - 1) * pageSize) + 1 : 0}</span> to <span className="font-medium">{events.length > 0 ? ((currentPage - 1) * pageSize) + events.length : 0}</span> of{' '}
-                  <span className="font-medium">{totalEvents}</span> results
-                </p>
-              </div>
-              <div className="flex items-center">
-                <div className="mr-4">
-                  <select
-                    className="px-3 py-1 border border-gray-300 rounded-md text-sm"
-                    value={pageSize}
-                    onChange={(e) => {
-                      setPageSize(Number(e.target.value));
-                      setCurrentPage(1); // 重置到第一页
-                    }}
-                  >
-                    <option value="5">5 / page</option>
-                    <option value="10">10 / page</option>
-                    <option value="20">20 / page</option>
-                    <option value="50">50 / page</option>
-                  </select>
-                </div>
-                
-                {/* 添加直接跳转到指定页的输入框 */}
-                <div className="mr-4 flex items-center">
-                  <span className="text-sm text-gray-700 mr-2">Go to:</span>
-                  <input
-                    type="number"
-                    min="1"
-                    max={Math.max(1, Math.ceil(totalEvents / pageSize))}
-                    value={currentPage}
-                    onChange={(e) => {
-                      const page = parseInt(e.target.value);
-                      if (!isNaN(page) && page >= 1 && page <= Math.ceil(totalEvents / pageSize)) {
-                        setCurrentPage(page);
-                      }
-                    }}
-                    onKeyDown={(e) => {
-                      if (e.key === 'Enter') {
-                        const input = e.target as HTMLInputElement;
-                        const page = parseInt(input.value);
-                        if (!isNaN(page) && page >= 1 && page <= Math.ceil(totalEvents / pageSize)) {
-                          setCurrentPage(page);
-                        }
-                      }
-                    }}
-                    className="w-16 px-3 py-1 border border-gray-300 rounded-md text-sm"
-                  />
-                  <span className="text-sm text-gray-700 ml-2">
-                    of {Math.max(1, Math.ceil(totalEvents / pageSize))}
-                  </span>
-                </div>
-                
-                <nav className="relative z-0 inline-flex rounded-md shadow-sm -space-x-px" aria-label="Pagination">
-                  {/* 首页按钮 */}
-                  <button
-                    onClick={() => setCurrentPage(1)}
-                    disabled={currentPage === 1}
-                    className={`relative inline-flex items-center px-2 py-2 rounded-l-md border border-gray-300 bg-white text-sm font-medium ${
-                      currentPage === 1 
-                        ? 'text-gray-300 cursor-not-allowed' 
-                        : 'text-gray-500 hover:bg-gray-50'
-                    }`}
-                  >
-                    <span className="sr-only">First</span>
-                    <svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor">
-                      <path fillRule="evenodd" d="M15.707 15.707a1 1 0 01-1.414 0l-5-5a1 1 0 010-1.414l5-5a1 1 0 111.414 1.414L11.414 10l4.293 4.293a1 1 0 010 1.414zm-6 0a1 1 0 01-1.414 0l-5-5a1 1 0 010-1.414l5-5a1 1 0 011.414 1.414L5.414 10l4.293 4.293a1 1 0 010 1.414z" clipRule="evenodd" />
-                    </svg>
-                  </button>
-                  
-                  {/* 上一页按钮 */}
-                  <button
-                    onClick={() => setCurrentPage(prev => Math.max(prev - 1, 1))}
-                    disabled={currentPage === 1}
-                    className={`relative inline-flex items-center px-2 py-2 border border-gray-300 bg-white text-sm font-medium ${
-                      currentPage === 1 
-                        ? 'text-gray-300 cursor-not-allowed' 
-                        : 'text-gray-500 hover:bg-gray-50'
-                    }`}
-                  >
-                    <span className="sr-only">Previous</span>
-                    <svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor" aria-hidden="true">
-                      <path fillRule="evenodd" d="M12.707 5.293a1 1 0 010 1.414L9.414 10l3.293 3.293a1 1 0 01-1.414 1.414l-4-4a1 1 0 010-1.414l4-4a1 1 0 011.414 0z" clipRule="evenodd" />
-                    </svg>
-                  </button>
-                  
-                  {/* 页码按钮 */}
-                  {(() => {
-                    const totalPages = Math.max(1, Math.ceil(totalEvents / pageSize));
-                    const pageNumbers = [];
-                    
-                    // 如果总页数小于等于7，显示所有页码
-                    if (totalPages <= 7) {
-                      for (let i = 1; i <= totalPages; i++) {
-                        pageNumbers.push(i);
-                      }
-                    } else {
-                      // 总是显示首页
-                      pageNumbers.push(1);
-                      
-                      // 根据当前页显示中间页码
-                      if (currentPage <= 3) {
-                        // 当前页靠近开始
-                        pageNumbers.push(2, 3, 4);
-                        pageNumbers.push('ellipsis1');
-                      } else if (currentPage >= totalPages - 2) {
-                        // 当前页靠近结束
-                        pageNumbers.push('ellipsis1');
-                        pageNumbers.push(totalPages - 3, totalPages - 2, totalPages - 1);
-                      } else {
-                        // 当前页在中间
-                        pageNumbers.push('ellipsis1');
-                        pageNumbers.push(currentPage - 1, currentPage, currentPage + 1);
-                        pageNumbers.push('ellipsis2');
-                      }
-                      
-                      // 总是显示尾页
-                      pageNumbers.push(totalPages);
-                    }
-                    
-                    return pageNumbers.map((pageNum, idx) => {
-                      if (pageNum === 'ellipsis1' || pageNum === 'ellipsis2') {
-                        return (
-                          <div key={`ellipsis-${idx}`} className="relative inline-flex items-center px-4 py-2 border border-gray-300 bg-white text-sm font-medium text-gray-700">
-                            ...
-                          </div>
-                        );
-                      }
-                      
-                      return (
-                        <button
-                          key={pageNum}
-                          onClick={() => setCurrentPage(Number(pageNum))}
-                          className={`relative inline-flex items-center px-4 py-2 border text-sm font-medium ${
-                            currentPage === pageNum
-                              ? 'z-10 bg-blue-50 border-blue-500 text-blue-600'
-                              : 'bg-white border-gray-300 text-gray-500 hover:bg-gray-50'
-                          }`}
-                        >
-                          {pageNum}
-                        </button>
-                      );
-                    });
-                  })()}
-                  
-                  {/* 下一页按钮 */}
-                  <button
-                    onClick={() => setCurrentPage(prev => (currentPage < Math.ceil(totalEvents / pageSize)) ? prev + 1 : prev)}
-                    disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
-                    className={`relative inline-flex items-center px-2 py-2 border border-gray-300 bg-white text-sm font-medium ${
-                      currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize
-                        ? 'text-gray-300 cursor-not-allowed' 
-                        : 'text-gray-500 hover:bg-gray-50'
-                    }`}
-                  >
-                    <span className="sr-only">Next</span>
-                    <svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor" aria-hidden="true">
-                      <path fillRule="evenodd" d="M7.293 14.707a1 1 0 010-1.414L10.586 10 7.293 6.707a1 1 0 011.414-1.414l4 4a1 1 0 010 1.414l-4 4a1 1 0 01-1.414 0z" clipRule="evenodd" />
-                    </svg>
-                  </button>
-                  
-                  {/* 尾页按钮 */}
-                  <button
-                    onClick={() => setCurrentPage(Math.ceil(totalEvents / pageSize))}
-                    disabled={currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize}
-                    className={`relative inline-flex items-center px-2 py-2 rounded-r-md border border-gray-300 bg-white text-sm font-medium ${
-                      currentPage >= Math.ceil(totalEvents / pageSize) || events.length < pageSize 
-                        ? 'text-gray-300 cursor-not-allowed' 
-                        : 'text-gray-500 hover:bg-gray-50'
-                    }`}
-                  >
-                    <span className="sr-only">Last</span>
-                    <svg className="h-5 w-5" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20" fill="currentColor">
-                      <path fillRule="evenodd" d="M4.293 15.707a1 1 0 001.414 0l5-5a1 1 0 000-1.414l-5-5a1 1 0 00-1.414 1.414L8.586 10 4.293 14.293a1 1 0 000 1.414zm6 0a1 1 0 001.414 0l5-5a1 1 0 000-1.414l-5-5a1 1 0 00-1.414 1.414L15.586 10l-4.293 4.293a1 1 0 000 1.414z" clipRule="evenodd" />
-                    </svg>
-                  </button>
-                </nav>
-              </div>
-            </div>
-          </div>
-        )}
-      </div>
-
-        <div className="bg-white rounded-lg shadow p-6 mb-8">
-          <h2 className="text-lg font-semibold text-gray-900 mb-4">Event Trends</h2>
-          <div className="h-96">
-            <TimeSeriesChart data={timeSeriesData} />
-          </div>
-        </div>
-
-        <div className="mb-8">
-          <h2 className="text-lg font-semibold text-gray-900 mb-4">Device Analytics</h2>
-          {deviceData && <DevicePieCharts data={deviceData} />}
-        </div>
-
-        <div className="bg-white rounded-lg shadow p-6 mb-8">
-          <h2 className="text-lg font-semibold text-gray-900 mb-4">Geographic Distribution</h2>
-          <GeoAnalytics 
-            data={geoData} 
-            onViewModeChange={(mode) => {
-              // 构建查询参数
-              const params = new URLSearchParams({
-                startTime: format(dateRange.from, "yyyy-MM-dd'T'HH:mm:ss'Z'"),
-                endTime: format(dateRange.to, "yyyy-MM-dd'T'HH:mm:ss'Z'"),
-                groupBy: mode
-              });
-              
-              // 添加其他筛选参数
-              if (selectedTeamIds.length > 0) {
-                selectedTeamIds.forEach(id => params.append('teamId', id));
-              }
-              
-              if (selectedProjectIds.length > 0) {
-                selectedProjectIds.forEach(id => params.append('projectId', id));
-              }
-              
-              if (selectedTagIds.length > 0) {
-                selectedTagIds.forEach(id => params.append('tagId', id));
-              }
-              
-              // 刷新地理位置数据
-              fetch(`/api/events/geo?${params}`)
-                .then(res => res.json())
-                .then(data => {
-                  if (data.success) {
-                    setGeoData(data.data);
-                  }
-                })
-                .catch(error => console.error('Failed to fetch geo data:', error));
-            }}
-          />
-        </div>
-      </>
-    </div>
-  );
+export default function Home() {
+  redirect('/analytics');
 }

app/utils/store.ts

@@ -0,0 +1,52 @@
+import { create } from 'zustand';
+import { persist } from 'zustand/middleware';
+
+// Define interface for team, project and tag objects
+interface TeamData {
+  team_id: string;
+  team_name: string;
+  [key: string]: unknown;
+}
+
+interface ProjectData {
+  project_id: string;
+  project_name: string;
+  [key: string]: unknown;
+}
+
+// 定义 ShortUrl 数据类型
+export interface ShortUrlData {
+  id: string;
+  externalId: string;
+  slug: string;
+  originalUrl: string;
+  title?: string;
+  shortUrl: string;
+  teams?: TeamData[];
+  projects?: ProjectData[];
+  tags?: string[];
+  createdAt?: string;
+  domain?: string;
+}
+
+// 定义 store 类型
+interface ShortUrlStore {
+  selectedShortUrl: ShortUrlData | null;
+  setSelectedShortUrl: (shortUrl: ShortUrlData | null) => void;
+  clearSelectedShortUrl: () => void;
+}
+
+// 创建 store 并使用 persist 中间件保存到 localStorage
+export const useShortUrlStore = create<ShortUrlStore>()(
+  persist(
+    (set) => ({
+      selectedShortUrl: null,
+      setSelectedShortUrl: (shortUrl) => set({ selectedShortUrl: shortUrl }),
+      clearSelectedShortUrl: () => set({ selectedShortUrl: null }),
+    }),
+    {
+      name: 'shorturl-storage', // localStorage 中的 key 名称
+      partialize: (state) => ({ selectedShortUrl: state.selectedShortUrl }), // 只持久化 selectedShortUrl
+    }
+  )
+);

lib/analytics.ts

@@ -67,7 +67,9 @@ export async function getEventsSummary(params: {
   projectIds?: string[];
   tagIds?: string[];
 }): Promise<EventsSummary> {
+  console.log('getEventsSummary received params:', params);
   const filter = buildFilter(params);
+  console.log('getEventsSummary built filter:', filter);
   
   // 获取基本统计数据
   const baseQuery = `

lib/clickhouse.ts

@@ -26,6 +26,7 @@ function buildDateFilter(startTime?: string, endTime?: string): string {
 
 // 构建通用过滤条件
 export function buildFilter(params: Partial<EventsQueryParams>): string {
+  console.log('buildFilter received params:', JSON.stringify(params));
   const filters = [];
   
   // 添加日期过滤条件
@@ -43,6 +44,7 @@ export function buildFilter(params: Partial<EventsQueryParams>): string {
   
   // 添加链接ID过滤条件
   if (params.linkId) {
+    console.log('Adding link_id filter:', params.linkId);
     filters.push(`link_id = '${params.linkId}'`);
   }
   
@@ -100,7 +102,7 @@ export function buildOrderBy(sortBy: string = 'event_time', sortOrder: string =
 
 // 执行查询
 export async function executeQuery(query: string) {
-  console.log('执行查询:', query); // 查询日志
+  console.log('Executing query:', query); // 查询日志
   try {
     const resultSet = await clickhouse.query({
       query,
@@ -117,7 +119,7 @@ export async function executeQuery(query: string) {
 
 // 执行返回单一结果的查询
 export async function executeQuerySingle(query: string) {
-  console.log('执行单一结果查询:', query); // 查询日志
+  console.log('Executing single result query:', query); // 查询日志
   try {
     const resultSet = await clickhouse.query({
       query,

package.json

@@ -37,11 +37,13 @@
     "date-fns": "^4.1.0",
     "lucide-react": "^0.486.0",
     "next": "15.2.3",
+    "process": "^0.11.10",
     "react": "^19.0.0",
     "react-dom": "^19.0.0",
     "recharts": "^2.15.1",
     "tailwind-merge": "^3.1.0",
-    "uuid": "^10.0.0"
+    "uuid": "^10.0.0",
+    "zustand": "^5.0.3"
   },
   "devDependencies": {
     "@eslint/eslintrc": "^3",

pnpm-lock.yaml

@@ -50,6 +50,9 @@ importers:
       next:
         specifier: 15.2.3
         version: 15.2.3(react-dom@19.0.0(react@19.0.0))(react@19.0.0)
+      process:
+        specifier: ^0.11.10
+        version: 0.11.10
       react:
         specifier: ^19.0.0
         version: 19.0.0
@@ -65,6 +68,9 @@ importers:
       uuid:
         specifier: ^10.0.0
         version: 10.0.0
+      zustand:
+        specifier: ^5.0.3
+        version: 5.0.3(@types/react@19.0.12)(react@19.0.0)
     devDependencies:
       '@eslint/eslintrc':
         specifier: ^3
@@ -2548,6 +2554,10 @@ packages:
     resolution: {integrity: sha512-vkcDPrRZo1QZLbn5RLGPpg/WmIQ65qoWWhcGKf/b5eplkkarX0m9z8ppCat4mlOqUsWpyNuYgO3VRyrYHSzX5g==}
     engines: {node: '>= 0.8.0'}
 
+  process@0.11.10:
+    resolution: {integrity: sha512-cdGef/drWFoydD1JsMzuFf8100nZl+GT+yacc2bEced5f9Rjk4z+WtFUTBu9PhOi9j/jfmBPu0mMEY4wIdAF8A==}
+    engines: {node: '>= 0.6.0'}
+
   prop-types@15.8.1:
     resolution: {integrity: sha512-oj87CgZICdulUohogVAR7AjlC0327U4el4L6eAvOqCeudMDVU0NThNaV+b9Df4dXgSP1gXMTnPdhfe/2qDH5cg==}
 
@@ -3035,6 +3045,24 @@ packages:
     resolution: {integrity: sha512-rVksvsnNCdJ/ohGc6xgPwyN8eheCxsiLM8mxuE/t/mOVqJewPuO1miLpTHQiRgTKCLexL4MeAFVagts7HmNZ2Q==}
     engines: {node: '>=10'}
 
+  zustand@5.0.3:
+    resolution: {integrity: sha512-14fwWQtU3pH4dE0dOpdMiWjddcH+QzKIgk1cl8epwSE7yag43k/AD/m4L6+K7DytAOr9gGBe3/EXj9g7cdostg==}
+    engines: {node: '>=12.20.0'}
+    peerDependencies:
+      '@types/react': '>=18.0.0'
+      immer: '>=9.0.6'
+      react: '>=18.0.0'
+      use-sync-external-store: '>=1.2.0'
+    peerDependenciesMeta:
+      '@types/react':
+        optional: true
+      immer:
+        optional: true
+      react:
+        optional: true
+      use-sync-external-store:
+        optional: true
+
 snapshots:
 
   '@alloc/quick-lru@5.2.0': {}
@@ -5650,6 +5678,8 @@ snapshots:
 
   prelude-ls@1.2.1: {}
 
+  process@0.11.10: {}
+
   prop-types@15.8.1:
     dependencies:
       loose-envify: 1.4.0
@@ -6304,3 +6334,8 @@ snapshots:
   ws@8.18.1: {}
 
   yocto-queue@0.1.0: {}
+
+  zustand@5.0.3(@types/react@19.0.12)(react@19.0.0):
+    optionalDependencies:
+      '@types/react': 19.0.12
+      react: 19.0.0

scripts/db/db-reports/clickhouse-schema-2025-03-20T13-57-59-013Z.log

@@ -1,225 +0,0 @@
-
-获取所有表...
-数据库 limq 中找到以下表:
-  - .inner_id.5d9e5f95-ad7d-4750-ae56-bffea63e14fb
-  - .inner_id.711eb652-7c90-4f9a-80a0-8979011080cc
-  - .inner_id.abec445d-1704-4482-bc72-66c9eb67ecd1
-  - .inner_id.c1eb844d-7f11-4cfc-8931-c433faaa16b0
-  - .inner_id.f9640e70-5b7f-444c-80de-bc5b25848024
-  - .inner_id.fe81eeba-acc5-4260-ac9a-973c2f9ce1ea
-  - link_daily_stats
-  - link_events
-  - link_hourly_patterns
-  - links
-  - platform_distribution
-  - project_daily_stats
-  - projects
-  - qr_scans
-  - qrcode_daily_stats
-  - qrcodes
-  - sessions
-  - team_daily_stats
-  - team_members
-  - teams
-
-所有ClickHouse表:
-.inner_id.5d9e5f95-ad7d-4750-ae56-bffea63e14fb, .inner_id.711eb652-7c90-4f9a-80a0-8979011080cc, .inner_id.abec445d-1704-4482-bc72-66c9eb67ecd1, .inner_id.c1eb844d-7f11-4cfc-8931-c433faaa16b0, .inner_id.f9640e70-5b7f-444c-80de-bc5b25848024, .inner_id.fe81eeba-acc5-4260-ac9a-973c2f9ce1ea, link_daily_stats, link_events, link_hourly_patterns, links, platform_distribution, project_daily_stats, projects, qr_scans, qrcode_daily_stats, qrcodes, sessions, team_daily_stats, team_members, teams
-
-获取表 .inner_id.5d9e5f95-ad7d-4750-ae56-bffea63e14fb 的结构...
-
-获取表 .inner_id.711eb652-7c90-4f9a-80a0-8979011080cc 的结构...
-
-获取表 .inner_id.abec445d-1704-4482-bc72-66c9eb67ecd1 的结构...
-
-获取表 .inner_id.c1eb844d-7f11-4cfc-8931-c433faaa16b0 的结构...
-
-获取表 .inner_id.f9640e70-5b7f-444c-80de-bc5b25848024 的结构...
-
-获取表 .inner_id.fe81eeba-acc5-4260-ac9a-973c2f9ce1ea 的结构...
-
-获取表 link_daily_stats 的结构...
-表 link_daily_stats 的列:
-  - date (Date, 无默认值)
-  - link_id (String, 无默认值)
-  - total_clicks (UInt64, 无默认值)
-  - unique_visitors (UInt64, 无默认值)
-  - unique_sessions (UInt64, 无默认值)
-  - total_time_spent (UInt64, 无默认值)
-  - avg_time_spent (Float64, 无默认值)
-  - bounce_count (UInt64, 无默认值)
-  - conversion_count (UInt64, 无默认值)
-  - unique_referrers (UInt64, 无默认值)
-  - mobile_count (UInt64, 无默认值)
-  - tablet_count (UInt64, 无默认值)
-  - desktop_count (UInt64, 无默认值)
-  - qr_scan_count (UInt64, 无默认值)
-  - total_conversion_value (Float64, 无默认值)
-
-获取表 link_events 的结构...
-表 link_events 的列:
-  - event_id (UUID, 默认值: generateUUIDv4())
-  - event_time (DateTime64(3), 默认值: now64())
-  - date (Date, 默认值: toDate(event_time))
-  - link_id (String, 无默认值)
-  - channel_id (String, 无默认值)
-  - visitor_id (String, 无默认值)
-  - session_id (String, 无默认值)
-  - event_type (Enum8('click' = 1, 'redirect' = 2, 'conversion' = 3, 'error' = 4), 无默认值)
-  - ip_address (String, 无默认值)
-  - country (String, 无默认值)
-  - city (String, 无默认值)
-  - referrer (String, 无默认值)
-  - utm_source (String, 无默认值)
-  - utm_medium (String, 无默认值)
-  - utm_campaign (String, 无默认值)
-  - user_agent (String, 无默认值)
-  - device_type (Enum8('mobile' = 1, 'tablet' = 2, 'desktop' = 3, 'other' = 4), 无默认值)
-  - browser (String, 无默认值)
-  - os (String, 无默认值)
-  - time_spent_sec (UInt32, 默认值: 0)
-  - is_bounce (Bool, 默认值: true)
-  - is_qr_scan (Bool, 默认值: false)
-  - qr_code_id (String, 默认值: '')
-  - conversion_type (Enum8('visit' = 1, 'stay' = 2, 'interact' = 3, 'signup' = 4, 'subscription' = 5, 'purchase' = 6), 默认值: 'visit')
-  - conversion_value (Float64, 默认值: 0)
-  - custom_data (String, 默认值: '{}')
-
-获取表 link_hourly_patterns 的结构...
-表 link_hourly_patterns 的列:
-  - date (Date, 无默认值)
-  - hour (UInt8, 无默认值)
-  - link_id (String, 无默认值)
-  - visits (UInt64, 无默认值)
-  - unique_visitors (UInt64, 无默认值)
-
-获取表 links 的结构...
-表 links 的列:
-  - link_id (String, 无默认值)
-  - original_url (String, 无默认值)
-  - created_at (DateTime64(3), 无默认值)
-  - created_by (String, 无默认值)
-  - title (String, 无默认值)
-  - description (String, 无默认值)
-  - tags (Array(String), 无默认值)
-  - is_active (Bool, 默认值: true)
-  - expires_at (Nullable(DateTime), 无默认值)
-  - team_id (String, 默认值: '')
-  - project_id (String, 默认值: '')
-
-获取表 platform_distribution 的结构...
-表 platform_distribution 的列:
-  - date (Date, 无默认值)
-  - utm_source (String, 无默认值)
-  - device_type (Enum8('mobile' = 1, 'tablet' = 2, 'desktop' = 3, 'other' = 4), 无默认值)
-  - visits (UInt64, 无默认值)
-  - unique_visitors (UInt64, 无默认值)
-
-获取表 project_daily_stats 的结构...
-表 project_daily_stats 的列:
-  - date (Date, 无默认值)
-  - project_id (String, 无默认值)
-  - total_clicks (UInt64, 无默认值)
-  - unique_visitors (UInt64, 无默认值)
-  - conversion_count (UInt64, 无默认值)
-  - links_used (UInt64, 无默认值)
-  - qr_scan_count (UInt64, 无默认值)
-
-获取表 projects 的结构...
-表 projects 的列:
-  - project_id (String, 无默认值)
-  - team_id (String, 无默认值)
-  - name (String, 无默认值)
-  - created_at (DateTime, 无默认值)
-  - created_by (String, 无默认值)
-  - description (String, 默认值: '')
-  - is_archived (Bool, 默认值: false)
-  - links_count (UInt32, 默认值: 0)
-  - total_clicks (UInt64, 默认值: 0)
-  - last_updated (DateTime, 默认值: now())
-
-获取表 qr_scans 的结构...
-表 qr_scans 的列:
-  - scan_id (UUID, 默认值: generateUUIDv4())
-  - qr_code_id (String, 无默认值)
-  - link_id (String, 无默认值)
-  - scan_time (DateTime64(3), 无默认值)
-  - visitor_id (String, 无默认值)
-  - location (String, 无默认值)
-  - device_type (Enum8('mobile' = 1, 'tablet' = 2, 'desktop' = 3, 'other' = 4), 无默认值)
-  - led_to_conversion (Bool, 默认值: false)
-
-获取表 qrcode_daily_stats 的结构...
-表 qrcode_daily_stats 的列:
-  - date (Date, 无默认值)
-  - qr_code_id (String, 无默认值)
-  - total_scans (UInt64, 无默认值)
-  - unique_scanners (UInt64, 无默认值)
-  - conversions (UInt64, 无默认值)
-  - mobile_scans (UInt64, 无默认值)
-  - tablet_scans (UInt64, 无默认值)
-  - desktop_scans (UInt64, 无默认值)
-  - unique_locations (UInt64, 无默认值)
-
-获取表 qrcodes 的结构...
-表 qrcodes 的列:
-  - qr_code_id (String, 无默认值)
-  - link_id (String, 无默认值)
-  - team_id (String, 无默认值)
-  - project_id (String, 默认值: '')
-  - name (String, 无默认值)
-  - description (String, 默认值: '')
-  - created_at (DateTime, 无默认值)
-  - created_by (String, 无默认值)
-  - updated_at (DateTime, 默认值: now())
-  - qr_type (Enum8('standard' = 1, 'custom' = 2, 'dynamic' = 3), 默认值: 'standard')
-  - image_url (String, 默认值: '')
-  - design_config (String, 默认值: '{}')
-  - is_active (Bool, 默认值: true)
-  - total_scans (UInt64, 默认值: 0)
-  - unique_scanners (UInt32, 默认值: 0)
-
-获取表 sessions 的结构...
-表 sessions 的列:
-  - session_id (String, 无默认值)
-  - visitor_id (String, 无默认值)
-  - link_id (String, 无默认值)
-  - started_at (DateTime64(3), 无默认值)
-  - last_activity (DateTime64(3), 无默认值)
-  - ended_at (Nullable(DateTime64(3)), 无默认值)
-  - duration_sec (UInt32, 默认值: 0)
-  - session_pages (UInt8, 默认值: 1)
-  - is_completed (Bool, 默认值: false)
-
-获取表 team_daily_stats 的结构...
-表 team_daily_stats 的列:
-  - date (Date, 无默认值)
-  - team_id (String, 无默认值)
-  - total_clicks (UInt64, 无默认值)
-  - unique_visitors (UInt64, 无默认值)
-  - conversion_count (UInt64, 无默认值)
-  - links_used (UInt64, 无默认值)
-  - qr_scan_count (UInt64, 无默认值)
-
-获取表 team_members 的结构...
-表 team_members 的列:
-  - team_id (String, 无默认值)
-  - user_id (String, 无默认值)
-  - role (Enum8('owner' = 1, 'admin' = 2, 'editor' = 3, 'viewer' = 4), 无默认值)
-  - joined_at (DateTime, 默认值: now())
-  - invited_by (String, 无默认值)
-  - is_active (Bool, 默认值: true)
-  - last_active (DateTime, 默认值: now())
-
-获取表 teams 的结构...
-表 teams 的列:
-  - team_id (String, 无默认值)
-  - name (String, 无默认值)
-  - created_at (DateTime, 无默认值)
-  - created_by (String, 无默认值)
-  - description (String, 默认值: '')
-  - avatar_url (String, 默认值: '')
-  - is_active (Bool, 默认值: true)
-  - plan_type (Enum8('free' = 1, 'pro' = 2, 'enterprise' = 3), 无默认值)
-  - members_count (UInt32, 默认值: 1)
-
-ClickHouse数据库结构检查完成

scripts/db/sql/clickhouse/add_req_full_path.sql

@@ -0,0 +1,9 @@
+-- add_req_full_path.sql
+-- Add req_full_path column to the shorturl_analytics.events table
+ALTER TABLE
+  shorturl_analytics.events
+ADD
+  COLUMN IF NOT EXISTS req_full_path String COMMENT 'Full request path including query parameters';
+
+-- Display the updated table structure
+DESCRIBE TABLE shorturl_analytics.events;

scripts/db/sql/clickhouse/add_utm_fields.sql

@@ -0,0 +1,41 @@
+-- 添加缺失的UTM参数字段到shorturl_analytics.events表
+-- 创建日期: 2024-07-02
+-- 用途: 增强UTM参数追踪能力
+-- 添加utm_term字段 (用于跟踪付费搜索关键词)
+ALTER TABLE
+  shorturl_analytics.events
+ADD
+  COLUMN utm_term String DEFAULT '' AFTER utm_campaign;
+
+-- 添加utm_content字段 (用于区分相同广告的不同版本或A/B测试)
+ALTER TABLE
+  shorturl_analytics.events
+ADD
+  COLUMN utm_content String DEFAULT '' AFTER utm_term;
+
+-- 验证字段添加成功
+DESCRIBE TABLE shorturl_analytics.events;
+
+-- 示例查询: 查看UTM参数分析数据
+SELECT
+  utm_source,
+  utm_medium,
+  utm_campaign,
+  utm_term,
+  utm_content,
+  COUNT(*) as clicks
+FROM
+  shorturl_analytics.events
+WHERE
+  event_type = 'click'
+  AND utm_source != ''
+GROUP BY
+  utm_source,
+  utm_medium,
+  utm_campaign,
+  utm_term,
+  utm_content
+ORDER BY
+  clicks DESC
+LIMIT
+  10;

scripts/db/sql/clickhouse/create_shorturl_table.sql

@@ -0,0 +1,46 @@
+-- 使用shorturl_analytics数据库
+USE shorturl_analytics;
+
+-- 删除已存在的shorturl表
+DROP TABLE IF EXISTS shorturl_analytics.shorturl;
+
+-- 创建shorturl表
+CREATE TABLE IF NOT EXISTS shorturl_analytics.shorturl (
+  -- 短链接基本信息（来源于resources表）
+  id String COMMENT '资源ID (resources.id)',
+  external_id String COMMENT '外部ID (resources.external_id)',
+  type String COMMENT '类型，值为shorturl',
+  slug String COMMENT '短链接slug (存储在attributes中)',
+  original_url String COMMENT '原始URL (存储在attributes中)',
+  title String COMMENT '标题 (存储在attributes中)',
+  description String COMMENT '描述 (存储在attributes中)',
+  attributes String DEFAULT '{}' COMMENT '资源属性JSON',
+  schema_version Int32 COMMENT 'Schema版本',
+  -- 创建者信息
+  creator_id String COMMENT '创建者ID (resources.creator_id)',
+  creator_email String COMMENT '创建者邮箱 (来自users表)',
+  creator_name String COMMENT '创建者名称 (来自users表)',
+  -- 时间信息
+  created_at DateTime64(3) COMMENT '创建时间 (resources.created_at)',
+  updated_at DateTime64(3) COMMENT '更新时间 (resources.updated_at)',
+  deleted_at Nullable(DateTime64(3)) COMMENT '删除时间 (resources.deleted_at)',
+  -- 项目关联 (project_resources表)
+  projects String DEFAULT '[]' COMMENT '项目关联信息数组。结构: [{project_id: String, project_name: String, project_description: String, assigned_at: DateTime64}]',
+  -- 团队关联 (通过项目关联到团队)
+  teams String DEFAULT '[]' COMMENT '团队关联信息数组。结构: [{team_id: String, team_name: String, team_description: String, via_project_id: String}]',
+  -- 标签关联 (resource_tags表)
+  tags String DEFAULT '[]' COMMENT '标签关联信息数组。结构: [{tag_id: String, tag_name: String, tag_type: String, created_at: DateTime64}]',
+  -- QR码关联 (qr_code表)
+  qr_codes String DEFAULT '[]' COMMENT 'QR码信息数组。结构: [{qr_id: String, scan_count: Int32, url: String, template_name: String, created_at: DateTime64}]',
+  -- 渠道关联 (channel表)
+  channels String DEFAULT '[]' COMMENT '渠道信息数组。结构: [{channel_id: String, channel_name: String, channel_path: String, is_user_created: Boolean}]',
+  -- 收藏关联 (favorite表)
+  favorites String DEFAULT '[]' COMMENT '收藏信息数组。结构: [{favorite_id: String, user_id: String, user_name: String, created_at: DateTime64}]',
+  -- 自定义过期时间 (存储在attributes中)
+  expires_at Nullable(DateTime64(3)) COMMENT '过期时间',
+  -- 统计信息 (分析时聚合计算)
+  click_count UInt32 DEFAULT 0 COMMENT '点击次数',
+  unique_visitors UInt32 DEFAULT 0 COMMENT '唯一访问者数'
+) ENGINE = MergeTree() PARTITION BY toYYYYMM(created_at)
+ORDER BY
+  (id, created_at) SETTINGS index_granularity = 8192 COMMENT '用于存储所有shorturl类型资源的统一表，集成了相关联的项目、团队、标签、QR码、渠道和收藏信息';

windmill/sync_shorturl_to_clickhouse.ts

@@ -0,0 +1,641 @@
+// Windmill script to sync shorturl data from PostgreSQL to ClickHouse
+// 作者: AI Assistant
+// 创建日期: 2023-10-30
+// 描述: 此脚本从PostgreSQL数据库获取所有shorturl类型的资源及其关联数据，并同步到ClickHouse
+
+import { Pool } from "https://deno.land/x/postgres@v0.17.0/mod.ts";
+import { getResource, getVariable } from "https://deno.land/x/windmill@v1.183.0/mod.ts";
+
+// 资源属性接口
+interface ResourceAttributes {
+  slug?: string;
+  original_url?: string;
+  originalUrl?: string;
+  title?: string;
+  description?: string;
+  expires_at?: string;
+  expiresAt?: string;
+  [key: string]: unknown;
+}
+
+// ClickHouse配置接口
+interface ChConfig {
+  clickhouse_host: string;
+  clickhouse_port: number;
+  clickhouse_user: string;
+  clickhouse_password: string;
+  clickhouse_url: string;
+}
+
+// PostgreSQL配置接口
+interface PgConfig {
+  host: string;
+  port: number;
+  user: string;
+  password: string;
+  dbname?: string;
+  [key: string]: unknown;
+}
+
+// Windmill函数定义
+export async function main(
+  /** PostgreSQL和ClickHouse同步脚本 */
+  params: {
+    /** 同步的资源数量限制，默认500 */
+    limit?: number;
+    /** 是否包含已删除资源 */
+    includeDeleted?: boolean;
+    /** 是否执行实际写入操作 */
+    dryRun?: boolean;
+    /** 开始时间（ISO格式）*/
+    startTime?: string;
+    /** 结束时间（ISO格式）*/
+    endTime?: string;
+  }
+) {
+  // 设置默认参数
+  const limit = params.limit || 500;
+  const includeDeleted = params.includeDeleted || false;
+  const dryRun = params.dryRun || false;
+  const startTime = params.startTime ? new Date(params.startTime) : undefined;
+  const endTime = params.endTime ? new Date(params.endTime) : undefined;
+
+  console.log(`开始同步PostgreSQL shorturl数据到ClickHouse`);
+  console.log(`参数: limit=${limit}, includeDeleted=${includeDeleted}, dryRun=${dryRun}`);
+  if (startTime) console.log(`开始时间: ${startTime.toISOString()}`);
+  if (endTime) console.log(`结束时间: ${endTime.toISOString()}`);
+
+  // 获取数据库配置
+  console.log("获取PostgreSQL数据库配置...");
+  const pgConfig = await getResource('f/limq/postgresql') as PgConfig;
+  console.log(`数据库连接配置: host=${pgConfig.host}, port=${pgConfig.port}, database=${pgConfig.dbname || 'postgres'}, user=${pgConfig.user}`);
+  
+  let pgPool: Pool | null = null;
+  
+  try {
+    console.log("创建PostgreSQL连接池...");
+    
+    pgPool = new Pool({
+      hostname: pgConfig.host,
+      port: pgConfig.port,
+      user: pgConfig.user,
+      password: pgConfig.password,
+      database: pgConfig.dbname || 'postgres'
+    }, 3);
+    
+    console.log("PostgreSQL连接池创建完成，尝试连接...");
+
+    // 测试连接
+    const client = await pgPool.connect();
+    try {
+      console.log("连接成功，执行测试查询...");
+      const testResult = await client.queryObject(`SELECT 1 AS test`);
+      console.log(`测试查询结果: ${JSON.stringify(testResult.rows)}`);
+    } finally {
+      client.release();
+    }
+
+    // 获取所有shorturl类型的资源
+    const shorturls = await fetchShorturlResources(pgPool, {
+      limit,
+      includeDeleted,
+      startTime,
+      endTime,
+    });
+    
+    console.log(`获取到 ${shorturls.length} 个shorturl资源`);
+    
+    if (shorturls.length === 0) {
+      return { synced: 0, message: "没有找到需要同步的shorturl资源" };
+    }
+
+    // 为每个资源获取关联数据
+    const enrichedShorturls = await enrichShorturlData(pgPool, shorturls);
+    console.log(`已丰富 ${enrichedShorturls.length} 个shorturl资源的关联数据`);
+
+    // 转换为ClickHouse格式
+    const clickhouseData = formatForClickhouse(enrichedShorturls);
+    
+    if (!dryRun) {
+      // 写入ClickHouse
+      const inserted = await insertToClickhouse(clickhouseData);
+      console.log(`成功写入 ${inserted} 条记录到ClickHouse`);
+      return { synced: inserted, message: "同步完成" };
+    } else {
+      console.log("Dry run模式 - 不执行实际写入");
+      console.log(`将写入 ${clickhouseData.length} 条记录到ClickHouse`);
+      // 输出示例数据
+      if (clickhouseData.length > 0) {
+        console.log("示例数据:");
+        console.log(JSON.stringify(clickhouseData[0], null, 2));
+      }
+      return { synced: 0, dryRun: true, sampleData: clickhouseData.slice(0, 1) };
+    }
+  } catch (error: unknown) {
+    console.error(`同步过程中发生错误: ${(error as Error).message}`);
+    console.error(`错误类型: ${(error as Error).name}`);
+    if ((error as Error).stack) {
+      console.error(`错误堆栈: ${(error as Error).stack}`);
+    }
+    throw error;
+  } finally {
+    if (pgPool) {
+      await pgPool.end();
+      console.log("PostgreSQL连接池已关闭");
+    }
+  }
+}
+
+// 从PostgreSQL获取所有shorturl资源
+async function fetchShorturlResources(
+  pgPool: Pool,
+  options: {
+    limit: number;
+    includeDeleted: boolean;
+    startTime?: Date;
+    endTime?: Date;
+  }
+) {
+  let query = `
+    SELECT 
+      r.id, 
+      r.external_id, 
+      r.type, 
+      r.attributes, 
+      r.schema_version,
+      r.creator_id,
+      r.created_at,
+      r.updated_at,
+      r.deleted_at,
+      u.email as creator_email,
+      u.first_name as creator_first_name,
+      u.last_name as creator_last_name
+    FROM 
+      limq.resources r
+    LEFT JOIN 
+      limq.users u ON r.creator_id = u.id
+    WHERE 
+      r.type = 'shorturl'
+  `;
+
+  const params = [];
+  let paramCount = 1;
+
+  if (!options.includeDeleted) {
+    query += ` AND r.deleted_at IS NULL`;
+  }
+
+  if (options.startTime) {
+    query += ` AND r.created_at >= $${paramCount}`;
+    params.push(options.startTime);
+    paramCount++;
+  }
+
+  if (options.endTime) {
+    query += ` AND r.created_at <= $${paramCount}`;
+    params.push(options.endTime);
+    paramCount++;
+  }
+
+  query += ` ORDER BY r.created_at DESC LIMIT $${paramCount}`;
+  params.push(options.limit);
+
+  const client = await pgPool.connect();
+  try {
+    const result = await client.queryObject(query, params);
+    
+    // 添加调试日志 - 显示获取的数据样本
+    if (result.rows.length > 0) {
+      console.log(`获取到 ${result.rows.length} 条shorturl记录`);
+      console.log(`第一条记录ID: ${result.rows[0].id}`);
+      console.log(`attributes类型: ${typeof result.rows[0].attributes}`);
+      console.log(`attributes内容示例: ${JSON.stringify(String(result.rows[0].attributes)).substring(0, 100)}...`);
+    }
+    
+    return result.rows;
+  } finally {
+    client.release();
+  }
+}
+
+// 为每个shorturl资源获取关联数据
+async function enrichShorturlData(pgPool: Pool, shorturls: Record<string, unknown>[]) {
+  const client = await pgPool.connect();
+  const enriched = [];
+
+  try {
+    for (const shorturl of shorturls) {
+      // 1. 获取项目关联
+      const projectsResult = await client.queryObject(`
+        SELECT 
+          pr.resource_id, pr.project_id, 
+          p.name as project_name, p.description as project_description,
+          pr.assigned_at
+        FROM 
+          limq.project_resources pr
+        JOIN 
+          limq.projects p ON pr.project_id = p.id
+        WHERE 
+          pr.resource_id = $1
+      `, [shorturl.id]);
+      
+      // 2. 获取团队关联（通过项目）
+      const teamIds = projectsResult.rows.map((p: Record<string, unknown>) => p.project_id);
+      const teamsResult = teamIds.length > 0 ? await client.queryObject(`
+        SELECT 
+          tp.team_id, tp.project_id,
+          t.name as team_name, t.description as team_description
+        FROM 
+          limq.team_projects tp
+        JOIN 
+          limq.teams t ON tp.team_id = t.id
+        WHERE 
+          tp.project_id = ANY($1::uuid[])
+      `, [teamIds]) : { rows: [] };
+      
+      // 3. 获取标签关联
+      const tagsResult = await client.queryObject(`
+        SELECT 
+          rt.resource_id, rt.tag_id, rt.created_at,
+          t.name as tag_name, t.type as tag_type
+        FROM 
+          limq.resource_tags rt
+        JOIN 
+          limq.tags t ON rt.tag_id = t.id
+        WHERE 
+          rt.resource_id = $1
+      `, [shorturl.id]);
+      
+      // 4. 获取QR码关联
+      const qrCodesResult = await client.queryObject(`
+        SELECT 
+          id as qr_id, scan_count, url, template_name, created_at
+        FROM 
+          limq.qr_code
+        WHERE 
+          resource_id = $1
+      `, [shorturl.id]);
+      
+      // 5. 获取渠道关联
+      const channelsResult = await client.queryObject(`
+        SELECT 
+          id as channel_id, name as channel_name, path as channel_path, 
+          "isUserCreated" as is_user_created
+        FROM 
+          limq.channel
+        WHERE 
+          "shortUrlId" = $1
+      `, [shorturl.id]);
+      
+      // 6. 获取收藏关联
+      const favoritesResult = await client.queryObject(`
+        SELECT 
+          f.id as favorite_id, f.user_id, f.created_at,
+          u.first_name, u.last_name
+        FROM 
+          limq.favorite f
+        JOIN 
+          limq.users u ON f.user_id = u.id
+        WHERE 
+          f.favoritable_id = $1 AND f.favoritable_type = 'resource'
+      `, [shorturl.id]);
+
+      // 调试日志
+      console.log(`\n处理资源ID: ${shorturl.id}`);
+      console.log(`attributes类型: ${typeof shorturl.attributes}`);
+      
+      // 改进的attributes解析逻辑
+      let attributes: ResourceAttributes = {};
+      try {
+        if (typeof shorturl.attributes === 'string') {
+          // 如果是字符串，尝试解析为JSON
+          console.log(`尝试解析attributes字符串，长度: ${shorturl.attributes.length}`);
+          attributes = JSON.parse(shorturl.attributes);
+        } else if (typeof shorturl.attributes === 'object' && shorturl.attributes !== null) {
+          // 如果已经是对象，直接使用
+          console.log('attributes已经是对象类型');
+          attributes = shorturl.attributes as ResourceAttributes;
+        } else {
+          console.log(`无效的attributes类型: ${typeof shorturl.attributes}`);
+          attributes = {};
+        }
+      } catch (err) {
+        const error = err as Error;
+        console.warn(`无法解析资源 ${shorturl.id} 的attributes JSON:`, error.message);
+        // 尝试进行更多原始数据分析
+        if (typeof shorturl.attributes === 'string') {
+          console.log(`原始字符串前100字符: ${shorturl.attributes.substring(0, 100)}`);
+        }
+        attributes = {};
+      }
+      
+      // 尝试从QR码获取数据
+      let slugFromQr = "";
+      const urlFromQr = "";
+      
+      if (qrCodesResult.rows.length > 0 && qrCodesResult.rows[0].url) {
+        const qrUrl = qrCodesResult.rows[0].url as string;
+        console.log(`找到QR码URL: ${qrUrl}`);
+        
+        try {
+          const urlParts = qrUrl.split('/');
+          slugFromQr = urlParts[urlParts.length - 1];
+          console.log(`从QR码URL提取的slug: ${slugFromQr}`);
+        } catch (err) {
+          const error = err as Error;
+          console.log('无法从QR码URL提取slug:', error.message);
+        }
+      }
+
+      // 日志输出实际字段值
+      console.log(`提取字段 - name: ${attributes.name || 'N/A'}, slug: ${attributes.slug || 'N/A'}`);
+      console.log(`提取字段 - originalUrl: ${attributes.originalUrl || 'N/A'}, original_url: ${attributes.original_url || 'N/A'}`);
+      
+      // 整合所有数据
+      const slug = attributes.slug || attributes.name || slugFromQr || "";
+      const originalUrl = attributes.originalUrl || attributes.original_url || urlFromQr || "";
+      
+      console.log(`最终使用的slug: ${slug}`);
+      console.log(`最终使用的originalUrl: ${originalUrl}`);
+      
+      enriched.push({
+        ...shorturl,
+        attributes,
+        projects: projectsResult.rows,
+        teams: teamsResult.rows,
+        tags: tagsResult.rows,
+        qrCodes: qrCodesResult.rows,
+        channels: channelsResult.rows,
+        favorites: favoritesResult.rows,
+        // 从attributes中提取特定字段 - 使用改进的顺序和QR码备选
+        slug,
+        originalUrl,
+        title: attributes.title || "",
+        description: attributes.description || "",
+        expiresAt: attributes.expires_at || attributes.expiresAt || null
+      });
+    }
+  } finally {
+    client.release();
+  }
+
+  return enriched;
+}
+
+// 将PostgreSQL数据格式化为ClickHouse格式
+function formatForClickhouse(shorturls: Record<string, unknown>[]) {
+  // 将日期格式化为ClickHouse兼容的DateTime64(3)格式
+  const formatDateTime = (date: Date | string | number | null | undefined): string | null => {
+    if (!date) return null;
+    // 转换为Date对象
+    const dateObj = date instanceof Date ? date : new Date(date);
+    // 返回格式化的字符串: YYYY-MM-DD HH:MM:SS.SSS
+    return dateObj.toISOString().replace('T', ' ').replace('Z', '');
+  };
+
+  console.log(`\n准备格式化 ${shorturls.length} 条记录为ClickHouse格式`);
+  
+  return shorturls.map(shorturl => {
+    // 调试日志：输出关键字段
+    console.log(`处理资源: ${shorturl.id}`);
+    console.log(`slug: ${shorturl.slug || 'EMPTY'}`);
+    console.log(`originalUrl: ${shorturl.originalUrl || 'EMPTY'}`);
+    
+    // 记录attributes状态
+    const attributesStr = JSON.stringify(shorturl.attributes || {});
+    const attributesPrev = attributesStr.length > 100 ? 
+                         attributesStr.substring(0, 100) + '...' : 
+                         attributesStr;
+    console.log(`attributes: ${attributesPrev}`);
+    
+    const creatorName = [shorturl.creator_first_name, shorturl.creator_last_name]
+      .filter(Boolean)
+      .join(" ");
+    
+    // 格式化项目数据为JSON数组
+    const projects = JSON.stringify((shorturl.projects as Record<string, unknown>[]).map((p) => ({
+      project_id: p.project_id,
+      project_name: p.project_name,
+      project_description: p.project_description,
+      assigned_at: p.assigned_at
+    })));
+    
+    // 格式化团队数据为JSON数组
+    const teams = JSON.stringify((shorturl.teams as Record<string, unknown>[]).map((t) => ({
+      team_id: t.team_id,
+      team_name: t.team_name,
+      team_description: t.team_description,
+      via_project_id: t.project_id
+    })));
+    
+    // 格式化标签数据为JSON数组
+    const tags = JSON.stringify((shorturl.tags as Record<string, unknown>[]).map((t) => ({
+      tag_id: t.tag_id,
+      tag_name: t.tag_name,
+      tag_type: t.tag_type,
+      created_at: t.created_at
+    })));
+    
+    // 格式化QR码数据为JSON数组
+    const qrCodes = JSON.stringify((shorturl.qrCodes as Record<string, unknown>[]).map((q) => ({
+      qr_id: q.qr_id,
+      scan_count: q.scan_count,
+      url: q.url,
+      template_name: q.template_name,
+      created_at: q.created_at
+    })));
+    
+    // 格式化渠道数据为JSON数组
+    const channels = JSON.stringify((shorturl.channels as Record<string, unknown>[]).map((c) => ({
+      channel_id: c.channel_id,
+      channel_name: c.channel_name,
+      channel_path: c.channel_path,
+      is_user_created: c.is_user_created
+    })));
+    
+    // 格式化收藏数据为JSON数组
+    const favorites = JSON.stringify((shorturl.favorites as Record<string, unknown>[]).map((f) => ({
+      favorite_id: f.favorite_id,
+      user_id: f.user_id,
+      user_name: `${f.first_name || ""} ${f.last_name || ""}`.trim(),
+      created_at: f.created_at
+    })));
+
+    // 统计信息（可通过events表聚合或在其他地方设置）
+    const clickCount = (shorturl.attributes as ResourceAttributes).click_count as number || 0;
+    const uniqueVisitors = 0;
+
+    // 返回ClickHouse格式数据
+    return {
+      id: shorturl.id,
+      external_id: shorturl.external_id || "",
+      type: shorturl.type,
+      slug: shorturl.slug || "",
+      original_url: shorturl.originalUrl || "",
+      title: shorturl.title || "",
+      description: shorturl.description || "",
+      attributes: JSON.stringify(shorturl.attributes || {}),
+      schema_version: shorturl.schema_version || 1,
+      creator_id: shorturl.creator_id || "",
+      creator_email: shorturl.creator_email || "",
+      creator_name: creatorName,
+      created_at: formatDateTime(shorturl.created_at as Date),
+      updated_at: formatDateTime(shorturl.updated_at as Date),
+      deleted_at: formatDateTime(shorturl.deleted_at as Date | null),
+      projects,
+      teams,
+      tags,
+      qr_codes: qrCodes,
+      channels,
+      favorites,
+      expires_at: formatDateTime(shorturl.expiresAt as Date | null),
+      click_count: clickCount,
+      unique_visitors: uniqueVisitors
+    };
+  });
+}
+
+// 获取ClickHouse配置
+async function getClickHouseConfig(): Promise<ChConfig> {
+  try {
+    // 使用getVariable而不是getResource获取ClickHouse配置
+    const chConfigJson = await getVariable("f/shorturl_analytics/clickhouse");
+    console.log("原始ClickHouse配置:", typeof chConfigJson);
+    
+    // 确保配置不为空
+    if (!chConfigJson) {
+      throw new Error("未找到ClickHouse配置");
+    }
+    
+    // 解析JSON字符串为对象
+    let chConfig: ChConfig;
+    if (typeof chConfigJson === 'string') {
+      try {
+        chConfig = JSON.parse(chConfigJson);
+      } catch (parseError) {
+        console.error("解析JSON失败:", parseError);
+        throw new Error("ClickHouse配置不是有效的JSON");
+      }
+    } else {
+      chConfig = chConfigJson as ChConfig;
+    }
+    
+    // 验证配置
+    if (!chConfig.clickhouse_url && chConfig.clickhouse_host && chConfig.clickhouse_port) {
+      chConfig.clickhouse_url = `http://${chConfig.clickhouse_host}:${chConfig.clickhouse_port}`;
+      console.log(`已构建ClickHouse URL: ${chConfig.clickhouse_url}`);
+    }
+    
+    if (!chConfig.clickhouse_url) {
+      throw new Error("ClickHouse配置缺少URL");
+    }
+    
+    return chConfig;
+  } catch (error) {
+    console.error("获取ClickHouse配置失败:", error);
+    throw error;
+  }
+}
+
+// 写入数据到ClickHouse
+async function insertToClickhouse(data: Record<string, unknown>[]) {
+  if (data.length === 0) return 0;
+
+  // 获取ClickHouse连接信息
+  const chConfig = await getClickHouseConfig();
+  
+  // 确保URL有效
+  if (!chConfig.clickhouse_url) {
+    throw new Error("无效的ClickHouse URL: 未定义");
+  }
+  
+  console.log(`准备写入数据到ClickHouse: ${chConfig.clickhouse_url}`);
+  
+  // 构建INSERT查询
+  const columns = Object.keys(data[0]).join(", ");
+  
+  // 收集所有记录的ID
+  const recordIds = data.map(record => record.id as string);
+  console.log(`需要处理的记录数: ${recordIds.length}`);
+  
+  // 先删除可能存在的重复记录
+  try {
+    console.log(`删除可能存在的重复记录...`);
+    
+    // 按批次处理删除，避免请求过大
+    const deleteBatchSize = 100;
+    for (let i = 0; i < recordIds.length; i += deleteBatchSize) {
+      const idBatch = recordIds.slice(i, i + deleteBatchSize);
+      const formattedIds = idBatch.map(id => `'${id}'`).join(', ');
+      
+      const deleteQuery = `
+        ALTER TABLE shorturl_analytics.shorturl
+        DELETE WHERE id IN (${formattedIds})
+      `;
+      
+      const response = await fetch(chConfig.clickhouse_url, {
+        method: "POST",
+        headers: {
+          "Content-Type": "application/x-www-form-urlencoded",
+          "Authorization": `Basic ${btoa(`${chConfig.clickhouse_user}:${chConfig.clickhouse_password}`)}`
+        },
+        body: deleteQuery,
+      });
+      
+      if (!response.ok) {
+        const errorText = await response.text();
+        console.warn(`删除记录时出错 (批次 ${i/deleteBatchSize + 1}): ${errorText}`);
+        // 继续执行，不中断流程
+      } else {
+        console.log(`成功删除批次 ${i/deleteBatchSize + 1}/${Math.ceil(recordIds.length/deleteBatchSize)}的潜在重复记录`);
+      }
+    }
+  } catch (error) {
+    console.warn(`删除重复记录时出错: ${(error as Error).message}`);
+    // 继续执行，不因为删除失败而中断整个过程
+  }
+
+  const query = `
+    INSERT INTO shorturl_analytics.shorturl (${columns})
+    FORMAT JSONEachRow
+  `;
+
+  // 批量插入
+  let inserted = 0;
+  const batchSize = 100;
+  
+  for (let i = 0; i < data.length; i += batchSize) {
+    const batch = data.slice(i, i + batchSize);
+    
+    // 使用JSONEachRow格式
+    const rows = batch.map(row => JSON.stringify(row)).join('\n');
+    
+    // 使用HTTP接口执行查询
+    try {
+      console.log(`正在发送请求到: ${chConfig.clickhouse_url}`);
+      console.log(`认证信息: ${chConfig.clickhouse_user}:***`);
+      
+      const response = await fetch(chConfig.clickhouse_url, {
+        method: "POST",
+        headers: {
+          "Content-Type": "application/x-www-form-urlencoded",
+          "Authorization": `Basic ${btoa(`${chConfig.clickhouse_user}:${chConfig.clickhouse_password}`)}`
+        },
+        body: `${query}\n${rows}`,
+      });
+      
+      if (!response.ok) {
+        const errorText = await response.text();
+        throw new Error(`ClickHouse插入失败: ${errorText}`);
+      }
+      
+      inserted += batch.length;
+      console.log(`已插入 ${inserted}/${data.length} 条记录`);
+    } catch (error) {
+      console.error(`请求ClickHouse时出错:`, error);
+      throw error;
+    }
+  }
+  
+  return inserted;
+}