// 更新被缓冲，定期批量刷新
enqueueMessageRequestUpdate(id, { durationMs, costUsd });

// 同步模式：直接更新数据库
await db.update(messageRequest).set({ durationMs })
  .where(eq(messageRequest.id, id));

type ActionResult<T> = 
  | { ok: true; data: T } 
  | { ok: false; error: string };

// 获取使用日志（带分页和统计）
getUsageLogs(filters): Promise<ActionResult<UsageLogsResult>>

// 游标分页查询（优化大数据集性能）
getUsageLogsBatch(filters): Promise<ActionResult<UsageLogsBatchResult>>

// 独立获取聚合统计（按需加载）
getUsageLogsStats(filters): Promise<ActionResult<UsageLogSummary>>

// 导出 CSV
exportUsageLogs(filters): Promise<ActionResult<string>>

// 获取筛选器选项（模型、状态码、端点）
getFilterOptions(): Promise<ActionResult<FilterOptions>>

// Session ID 自动补全建议
getUsageLogSessionIdSuggestions(input): Promise<ActionResult<string[]>>

interface UsageLogFilters {
  userId?: number;                    // 用户 ID（admin 可筛选任意用户）
  keyId?: number;                     // API Key ID
  providerId?: number;                // 供应商 ID（admin 可用）
  sessionId?: string;                 // Session ID（精确匹配）
  startTime?: number;                 // 开始时间戳（毫秒）
  endTime?: number;                   // 结束时间戳（毫秒）
  statusCode?: number;                // HTTP 状态码（精确匹配）
  excludeStatusCode200?: boolean;     // 排除 200 状态码（查看错误）
  model?: string;                     // 模型名称（精确匹配）
  endpoint?: string;                  // 端点路径（精确匹配）
  minRetryCount?: number;             // 最小重试次数
  page?: number;                      // 页码（从 1 开始）
  pageSize?: number;                  // 每页大小
}

interface UsageLogSessionIdSuggestionInput {
  term: string;                       // 搜索关键词
  userId?: number;                    // 限制特定用户
  keyId?: number;                     // 限制特定密钥
  providerId?: number;                // 限制特定供应商
  limit?: number;                     // 返回数量限制（默认 20）
}

const { page = 1, pageSize = 50 } = filters;
const offset = (page - 1) * pageSize;

// 同时查询总数和统计数据
const [summaryResult] = await db.select({
  totalRows: sql<number>`count(*)::double precision`,
  totalCost: sql<string>`COALESCE(sum(cost_usd), 0)`,
}).from(messageRequest).where(...);

// 查询分页数据
const results = await db.select({...})
  .orderBy(desc(messageRequest.createdAt))
  .limit(pageSize)
  .offset(offset);

// 游标条件：(created_at, id) < (cursor_created_at, cursor_id)
if (cursor) {
  conditions.push(
    sql`(${createdAt}, ${id}) < (${cursor.createdAt}, ${cursor.id})`
  );
}

// 查询 limit + 1 条判断是否还有更多
const results = await db.select({...})
  .orderBy(desc(createdAt), desc(id))
  .limit(limit + 1);

export async function getUsageLogs(filters) {
  const session = await getSession();
  if (!session) {
    return { ok: false, error: "未登录" };
  }
  
  // 如果不是 admin，强制过滤为当前用户
  const finalFilters =
    session.user.role === "admin" 
      ? filters 
      : { ...filters, userId: session.user.id };
      
  return findUsageLogsWithDetails(finalFilters);
}

const EXCLUDE_WARMUP_CONDITION = 
  sql`(${messageRequest.blockedBy} IS NULL OR ${messageRequest.blockedBy} <> 'warmup')`;

const [summaryResult] = await db.select({
  // total：用于分页/审计，必须包含 warmup
  totalRows: sql<number>`count(*)::double precision`,
  // summary：所有统计字段必须排除 warmup
  totalRequests: sql<number>`count(*) FILTER (WHERE ${EXCLUDE_WARMUP_CONDITION})`,
  totalCost: sql<string>`COALESCE(sum(cost_usd) FILTER (WHERE ${EXCLUDE_WARMUP_CONDITION}), 0)`,
});

// POST /api/admin/log-cleanup/manual
const conditions: CleanupConditions = {
  beforeDate: new Date('2024-01-01'),  // 删除此日期之前的日志
  afterDate: new Date('2023-01-01'),   // 删除此日期之后的日志
  userIds: [1, 2, 3],                  // 仅删除指定用户的日志
  providerIds: [1, 2],                 // 仅删除指定供应商的日志
  statusCodes: [500, 503],             // 仅删除指定状态码的日志
  statusCodeRange: { min: 400, max: 499 }, // 状态码范围
  onlyBlocked: true,                   // 仅删除被拦截的请求
};

const result = await cleanupLogs(
  conditions, 
  { dryRun: true },
  { type: "manual", userId: session.user.id, username: session.user.name }
);
// dryRun: true 仅预览，不实际删除

async function deleteBatch(whereConditions: SQL[], batchSize: number) {
  const result = await db.execute(sql`
    WITH ids_to_delete AS (
      SELECT id FROM message_request
      WHERE ${and(...whereConditions)}
      ORDER BY created_at ASC
      LIMIT ${batchSize}
      FOR UPDATE SKIP LOCKED
    )
    DELETE FROM message_request
    WHERE id IN (SELECT id FROM ids_to_delete)
  `);
  return result.rowCount || 0;
}

export function setLogLevel(newLevel: LogLevel): void {
  logger.level = newLevel;
  logger.info(`日志级别已调整为: ${newLevel}`);
}

// GET /api/admin/log-level - 获取当前日志级别
// 返回: { level: "info" }

// POST /api/admin/log-level - 设置日志级别
// 请求体: { level: "debug" }
// 返回: { success: true, level: "debug" }

LOG_LEVEL=info  # 可选: fatal, error, warn, info, debug, trace

export async function findUsageLogSessionIdSuggestions(filters) {
  const { term, userId, keyId, providerId } = filters;
  const limit = Math.min(50, Math.max(1, filters.limit ?? 20));
  
  // 使用 LIKE 前缀匹配，配合 ESCAPE 防止注入
  const pattern = `${escapeLike(term.trim())}%`;
  
  const results = await db
    .select({
      sessionId: messageRequest.sessionId,
      firstSeen: sql<Date>`min(${messageRequest.createdAt})`,
    })
    .from(messageRequest)
    .where(and(
      isNull(messageRequest.deletedAt),
      EXCLUDE_WARMUP_CONDITION,
      sql`${messageRequest.sessionId} LIKE ${pattern} ESCAPE '\\'`,
    ))
    .groupBy(messageRequest.sessionId)
    .orderBy(desc(sql`min(${messageRequest.createdAt})`))
    .limit(limit);
    
  return results.map((r) => r.sessionId).filter(Boolean);
}

function escapeCsvField(field: string): string {
  const dangerousChars = ["=", "+", "-", "@", "\t", "\r"];
  let safeField = field;
  
  // 如果字段以危险字符开头，添加单引号前缀
  if (dangerousChars.some((char) => field.startsWith(char))) {
    safeField = `'${field}`;
  }
  
  // 如果字段包含逗号、引号或换行，用引号包裹
  if (/[",\n\r]/.test(safeField)) {
    safeField = `"${safeField.replace(/"/g, '""')}"`;
  }
  
  return safeField;
}

type FilterPreset = "today" | "this-week" | "errors-only" | "show-retries";

const presets = [
  { id: "today", label: "今天", icon: Calendar },
  { id: "this-week", label: "本周", icon: CalendarDays },
  { id: "errors-only", label: "仅错误", icon: AlertCircle },
  { id: "show-retries", label: "显示重试", icon: RefreshCw },
];

const rowVirtualizer = useVirtualizer({
  count: hasNextPage ? allLogs.length + 1 : allLogs.length,
  getScrollElement: () => parentRef.current,
  estimateSize: () => 52, // 每行高度 52px
  overscan: 10,
});

// 无限滚动加载
const { data, fetchNextPage, hasNextPage } = useInfiniteQuery({
  queryKey: ["usage-logs-batch", filters],
  queryFn: async ({ pageParam }) => {
    return getUsageLogsBatch({ ...filters, cursor: pageParam, limit: 50 });
  },
  getNextPageParam: (lastPage) => lastPage.nextCursor ?? undefined,
  refetchInterval: autoRefreshEnabled ? 5000 : false, // 默认 5s 自动刷新
});

interface LogsUrlFilters {
  userId?: number;              // 用户 ID
  keyId?: number;               // 密钥 ID
  providerId?: number;          // 供应商 ID
  sessionId?: string;           // Session ID
  startTime?: number;           // 开始时间戳（毫秒）
  endTime?: number;             // 结束时间戳（毫秒）
  statusCode?: number | string; // 状态码（或 "!200" 表示非 200）
  model?: string;               // 模型名称
  endpoint?: string;            // 端点路径
  minRetryCount?: number;       // 最小重试次数
  page?: number;                // 页码
}