chatbot/server/db.ts

import { eq, desc, asc, and, sql, like, or, lt, gte, lte, isNotNull, inArray } from "drizzle-orm";
import { drizzle } from "drizzle-orm/postgres-js";
import {
  InsertUser, users,
  conversations, InsertConversation, Conversation,
  messages, InsertMessage,
  workflowNodes, InsertWorkflowNode,
  workflowEdges, InsertWorkflowEdge,
  workflowSuggestions, InsertWorkflowSuggestion,
  invitations, InsertInvitation,
  auditLogs, InsertAuditLog,
  passwordResetTokens, InsertPasswordResetToken,
  analyticsEvents, InsertAnalyticsEvent,
  dataSources, InsertDataSource,
  apiConnections, InsertApiConnection,
  knowledgeEntries, InsertKnowledgeEntry,
  knowledgeSuggestions, InsertKnowledgeSuggestion,
  knowledgeProducts, InsertKnowledgeProduct,
} from "../drizzle/schema";
import { ENV } from './_core/env';

let _db: ReturnType<typeof drizzle> | null = null;

export async function getDb() {
  if (!_db && process.env.DATABASE_URL) {
    try {
      // Set search_path so unqualified queries resolve to the chatbot schema
      _db = drizzle(process.env.DATABASE_URL, {
        connection: { options: "-c search_path=chatbot,public" },
      });
    } catch (error) {
      console.warn("[Database] Failed to connect:", error);
      _db = null;
    }
  }
  return _db;
}

/* ─── User helpers ─── */

export async function upsertUser(user: InsertUser): Promise<void> {
  if (!user.openId) {
    throw new Error("User openId is required for upsert");
  }
  const db = await getDb();
  if (!db) { console.warn("[Database] Cannot upsert user: database not available"); return; }

  try {
    const values: InsertUser = { openId: user.openId };
    const updateSet: Record<string, unknown> = {};
    const textFields = ["name", "email", "loginMethod"] as const;
    type TextField = (typeof textFields)[number];
    const assignNullable = (field: TextField) => {
      const value = user[field];
      if (value === undefined) return;
      const normalized = value ?? null;
      values[field] = normalized;
      updateSet[field] = normalized;
    };
    textFields.forEach(assignNullable);
    if (user.lastSignedIn !== undefined) { values.lastSignedIn = user.lastSignedIn; updateSet.lastSignedIn = user.lastSignedIn; }
    if (user.role !== undefined) { values.role = user.role; updateSet.role = user.role; }
    else if (user.openId === ENV.ownerOpenId) { values.role = 'admin'; updateSet.role = 'admin'; }
    if (!values.lastSignedIn) { values.lastSignedIn = new Date(); }
    if (Object.keys(updateSet).length === 0) { updateSet.lastSignedIn = new Date(); }
    await db.insert(users).values(values).onConflictDoUpdate({ target: users.openId, set: updateSet });
  } catch (error) { console.error("[Database] Failed to upsert user:", error); throw error; }
}

export async function getUserByOpenId(openId: string) {
  const db = await getDb();
  if (!db) { console.warn("[Database] Cannot get user: database not available"); return undefined; }
  const result = await db.select().from(users).where(eq(users.openId, openId)).limit(1);
  return result.length > 0 ? result[0] : undefined;
}

/* ─── User management helpers ─── */

export async function getAllUsers() {
  const db = await getDb();
  if (!db) return [];
  return db.select({
    id: users.id,
    openId: users.openId,
    name: users.name,
    email: users.email,
    role: users.role,
    erpContactCid: users.erpContactCid,
    createdAt: users.createdAt,
    lastSignedIn: users.lastSignedIn,
  }).from(users).orderBy(desc(users.lastSignedIn));
}

export async function updateUserRole(userId: number, role: "user" | "agent" | "admin") {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(users).set({ role }).where(eq(users.id, userId));
  const result = await db.select().from(users).where(eq(users.id, userId)).limit(1);
  return result[0];
}

export async function updateUserErpContactCid(userId: number, erpContactCid: string | null) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(users).set({ erpContactCid }).where(eq(users.id, userId));
  const result = await db.select().from(users).where(eq(users.id, userId)).limit(1);
  return result[0];
}

export async function getUserById(userId: number) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(users).where(eq(users.id, userId)).limit(1);
  return result[0];
}

export async function deleteUser(userId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  // Get user before deleting
  const user = await getUserById(userId);
  if (!user) return null;
  await db.delete(users).where(eq(users.id, userId));
  return user;
}

export async function getUserByEmail(email: string) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(users).where(eq(users.email, email)).limit(1);
  return result[0];
}

export async function getUserByEmailWithPassword(email: string) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(users).where(eq(users.email, email)).limit(1);
  return result[0]; // includes passwordHash
}

export async function createUserWithPassword(data: {
  email: string;
  name: string;
  passwordHash: string;
  role?: "user" | "agent" | "admin";
}) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const openId = `local_${data.email}`; // local users get a synthetic openId
  await db.insert(users).values({
    openId,
    email: data.email,
    name: data.name,
    passwordHash: data.passwordHash,
    loginMethod: "email",
    role: data.role || "user",
    lastSignedIn: new Date(),
  });
  const result = await db.select().from(users).where(eq(users.openId, openId)).limit(1);
  return result[0];
}

export async function updateUserPassword(userId: number, passwordHash: string) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(users).set({ passwordHash }).where(eq(users.id, userId));
}

/* ─── Password reset token helpers ─── */

export async function createPasswordResetToken(data: InsertPasswordResetToken) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.insert(passwordResetTokens).values(data);
  const result = await db.select().from(passwordResetTokens).where(eq(passwordResetTokens.token, data.token)).limit(1);
  return result[0];
}

export async function getPasswordResetToken(token: string) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(passwordResetTokens).where(eq(passwordResetTokens.token, token)).limit(1);
  return result[0];
}

export async function markPasswordResetTokenUsed(tokenId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(passwordResetTokens).set({ usedAt: new Date() }).where(eq(passwordResetTokens.id, tokenId));
}

/* ─── Invitation helpers ─── */

export async function createInvitation(data: InsertInvitation) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.insert(invitations).values(data);
  const result = await db.select().from(invitations).where(eq(invitations.token, data.token)).limit(1);
  return result[0];
}

export async function getAllInvitations() {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(invitations).orderBy(desc(invitations.createdAt));
}

export async function getInvitationByToken(token: string) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(invitations).where(eq(invitations.token, token)).limit(1);
  return result[0];
}

export async function getInvitationByEmail(email: string) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(invitations)
    .where(eq(invitations.email, email))
    .orderBy(desc(invitations.createdAt));
}

export async function updateInvitationStatus(
  id: number,
  status: "pending" | "accepted" | "expired" | "revoked",
  acceptedByUserId?: number,
) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const updateData: Record<string, unknown> = { status };
  if (status === "accepted") {
    updateData.acceptedAt = new Date();
    if (acceptedByUserId) updateData.acceptedByUserId = acceptedByUserId;
  }
  await db.update(invitations).set(updateData).where(eq(invitations.id, id));
  const result = await db.select().from(invitations).where(eq(invitations.id, id)).limit(1);
  return result[0];
}

export async function expireOldInvitations() {
  const db = await getDb();
  if (!db) return 0;
  const result = await db.update(invitations)
    .set({ status: "expired" })
    .where(
      and(
        eq(invitations.status, "pending"),
        lt(invitations.expiresAt, new Date()),
      )
    );
  return 0; // PostgreSQL: affected row count not available without RETURNING
}

/* ─── Audit log helpers ─── */

export async function createAuditLog(data: InsertAuditLog) {
  const db = await getDb();
  if (!db) { console.warn("[Database] Cannot create audit log: database not available"); return; }
  await db.insert(auditLogs).values(data);
}

export async function getAuditLogs(limit = 50) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(auditLogs).orderBy(desc(auditLogs.createdAt)).limit(limit);
}

/* ─── Conversation helpers ─── */

export async function createConversation(data: InsertConversation) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.insert(conversations).values(data);
  const result = await db.select().from(conversations).where(eq(conversations.sessionId, data.sessionId)).limit(1);
  return result[0];
}

export async function getConversations(status?: string) {
  const db = await getDb();
  if (!db) return [];
  if (status) {
    return db.select().from(conversations)
      .where(eq(conversations.status, status as any))
      .orderBy(desc(conversations.updatedAt));
  }
  return db.select().from(conversations).orderBy(desc(conversations.updatedAt));
}

/** Advanced conversation query with pagination, search, agent filter, date range, and sorting */
export async function getConversationsAdvanced(params: {
  page?: number;
  pageSize?: number;
  status?: string;
  search?: string;
  agentId?: number;
  dateFrom?: string;
  dateTo?: string;
  sortBy?: string;
  sortOrder?: "asc" | "desc";
}) {
  const db = await getDb();
  if (!db) return { conversations: [], total: 0, page: 1, pageSize: 20, totalPages: 0 };

  const page = params.page || 1;
  const pageSize = params.pageSize || 20;
  const offset = (page - 1) * pageSize;

  // Build conditions
  const conditions = [];
  if (params.status) {
    conditions.push(eq(conversations.status, params.status as any));
  }
  if (params.agentId) {
    conditions.push(eq(conversations.assignedAgentId, params.agentId));
  }
  if (params.search) {
    const searchTerm = `%${params.search}%`;
    conditions.push(
      or(
        like(conversations.visitorName, searchTerm),
        like(conversations.visitorEmail, searchTerm),
        like(conversations.sessionId, searchTerm),
        like(conversations.customerId, searchTerm),
        like(conversations.salesRep, searchTerm),
      )!
    );
  }
  if (params.dateFrom) {
    conditions.push(gte(conversations.createdAt, new Date(params.dateFrom)));
  }
  if (params.dateTo) {
    const endDate = new Date(params.dateTo);
    endDate.setHours(23, 59, 59, 999);
    conditions.push(lte(conversations.createdAt, endDate));
  }

  const whereClause = conditions.length > 0 ? and(...conditions) : undefined;

  // Sort
  const sortFn = params.sortOrder === "asc" ? asc : desc;
  let orderByCol;
  switch (params.sortBy) {
    case "created": orderByCol = sortFn(conversations.createdAt); break;
    case "visitor": orderByCol = sortFn(conversations.visitorName); break;
    case "status": orderByCol = sortFn(conversations.status); break;
    case "customerId": orderByCol = sortFn(conversations.customerId); break;
    case "salesRep": orderByCol = sortFn(conversations.salesRep); break;
    case "agent": orderByCol = sortFn(users.name); break;
    default: orderByCol = sortFn(conversations.updatedAt); break;
  }

  // Count
  const countResult = whereClause
    ? await db.select({ count: sql<number>`COUNT(*)` }).from(conversations).where(whereClause)
    : await db.select({ count: sql<number>`COUNT(*)` }).from(conversations);
  const total = Number(countResult[0]?.count || 0);
  const totalPages = Math.ceil(total / pageSize);

  // Fetch page with agent name join
  const baseQuery = db.select({
    id: conversations.id,
    sessionId: conversations.sessionId,
    visitorName: conversations.visitorName,
    visitorEmail: conversations.visitorEmail,
    customerId: conversations.customerId,
    salesRep: conversations.salesRep,
    status: conversations.status,
    assignedAgentId: conversations.assignedAgentId,
    metadata: conversations.metadata,
    createdAt: conversations.createdAt,
    updatedAt: conversations.updatedAt,
    agentName: users.name,
  })
    .from(conversations)
    .leftJoin(users, eq(conversations.assignedAgentId, users.id));

  const rows = whereClause
    ? await baseQuery.where(whereClause).orderBy(orderByCol).limit(pageSize).offset(offset)
    : await baseQuery.orderBy(orderByCol).limit(pageSize).offset(offset);

  return { conversations: rows, total, page, pageSize, totalPages };
}

/** Get all agents (users with agent or admin role) for filter dropdown */
export async function getAgentUsers() {
  const db = await getDb();
  if (!db) return [];
  return db.select({
    id: users.id,
    name: users.name,
    email: users.email,
    role: users.role,
  }).from(users).where(
    or(
      eq(users.role, "agent"),
      eq(users.role, "admin"),
    )!
  ).orderBy(users.name);
}

/** Get message count per conversation */
export async function getConversationMessageCounts(conversationIds: number[]) {
  const db = await getDb();
  if (!db || conversationIds.length === 0) return {};
  const result = await db.select({
    conversationId: messages.conversationId,
    count: sql<number>`COUNT(*)`,
  }).from(messages)
    .where(sql`${messages.conversationId} IN (${sql.join(conversationIds.map(id => sql`${id}`), sql`, `)})`)
    .groupBy(messages.conversationId);
  const counts: Record<number, number> = {};
  for (const row of result) {
    counts[row.conversationId] = Number(row.count);
  }
  return counts;
}

/** Bulk update conversation status */
export async function bulkUpdateConversationStatus(
  ids: number[],
  status: "active" | "escalated" | "resolved" | "closed",
  agentId?: number
) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  if (ids.length === 0) return { updated: 0 };
  const updateData: Record<string, unknown> = { status };
  if (agentId !== undefined) updateData.assignedAgentId = agentId;
  await db.update(conversations).set(updateData).where(inArray(conversations.id, ids));
  return { updated: ids.length };
}

/** Delete conversations */
export async function deleteConversations(ids: number[]) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  if (ids.length === 0) return { deleted: 0 };
  // Delete messages first, then conversations — both in a single statement each
  await db.delete(messages).where(inArray(messages.conversationId, ids));
  await db.delete(conversations).where(inArray(conversations.id, ids));
  return { deleted: ids.length };
}

export async function getConversationById(id: number) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(conversations).where(eq(conversations.id, id)).limit(1);
  return result[0];
}

export async function getConversationBySessionId(sessionId: string) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(conversations).where(eq(conversations.sessionId, sessionId)).limit(1);
  return result[0];
}

export async function updateConversationStatus(id: number, status: "active" | "escalated" | "resolved" | "closed", agentId?: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const updateData: Record<string, unknown> = { status };
  if (agentId !== undefined) updateData.assignedAgentId = agentId;
  // Record when a conversation is escalated for response-time analytics
  if (status === "escalated") updateData.escalatedAt = new Date();
  await db.update(conversations).set(updateData).where(eq(conversations.id, id));
  return getConversationById(id);
}

export async function rateConversation(sessionId: string, rating: number, comment?: string) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(conversations)
    .set({ csatRating: rating, csatComment: comment ?? null })
    .where(eq(conversations.sessionId, sessionId));
  return { success: true };
}

export async function getConversationStats() {
  const db = await getDb();
  if (!db) return { total: 0, active: 0, escalated: 0, resolved: 0, closed: 0 };
  const result = await db.select({
    status: conversations.status,
    count: sql<number>`COUNT(*)`,
  }).from(conversations).groupBy(conversations.status);
  const stats = { total: 0, active: 0, escalated: 0, resolved: 0, closed: 0 };
  for (const row of result) {
    const count = Number(row.count);
    stats[row.status as keyof typeof stats] = count;
    stats.total += count;
  }
  return stats;
}

/* ─── Message helpers ─── */

export async function addMessage(data: InsertMessage) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const [inserted] = await db.insert(messages).values(data).returning({ id: messages.id });
  const convUpdate: Record<string, unknown> = { updatedAt: new Date() };
  // Record first agent reply timestamp for response-time analytics (COALESCE — set only once)
  if (data.sender === "agent") {
    convUpdate.firstAgentReplyAt = sql`COALESCE("first_agent_reply_at", NOW())`;
  }
  await db.update(conversations).set(convUpdate).where(eq(conversations.id, data.conversationId));
  return { id: inserted.id, ...data };
}

export async function getMessagesByConversation(conversationId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(messages)
    .where(eq(messages.conversationId, conversationId))
    .orderBy(messages.createdAt);
}

/* ─── Workflow helpers ─── */

export async function saveWorkflow(workflowId: string, nodes: InsertWorkflowNode[], edges: InsertWorkflowEdge[]) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.delete(workflowEdges).where(eq(workflowEdges.workflowId, workflowId));
  await db.delete(workflowNodes).where(eq(workflowNodes.workflowId, workflowId));
  if (nodes.length > 0) await db.insert(workflowNodes).values(nodes);
  if (edges.length > 0) await db.insert(workflowEdges).values(edges);
  return { workflowId, nodeCount: nodes.length, edgeCount: edges.length };
}

export async function getWorkflow(workflowId: string) {
  const db = await getDb();
  if (!db) return { nodes: [], edges: [] };
  const nodes = await db.select().from(workflowNodes).where(eq(workflowNodes.workflowId, workflowId));
  const edges = await db.select().from(workflowEdges).where(eq(workflowEdges.workflowId, workflowId));
  return { nodes, edges };
}

/* ─── Workflow suggestion helpers ─── */

export async function createWorkflowSuggestion(data: InsertWorkflowSuggestion) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.insert(workflowSuggestions).values(data);
  return data;
}

export async function getWorkflowSuggestions(workflowId: string, status?: string) {
  const db = await getDb();
  if (!db) return [];
  const conditions = [eq(workflowSuggestions.workflowId, workflowId)];
  if (status) conditions.push(eq(workflowSuggestions.status, status as any));
  return db.select().from(workflowSuggestions).where(and(...conditions)).orderBy(desc(workflowSuggestions.frequency));
}

export async function updateWorkflowSuggestionStatus(id: number, status: "approved" | "declined" | "waiting", reviewedById: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(workflowSuggestions)
    .set({ status, reviewedById, reviewedAt: new Date() })
    .where(eq(workflowSuggestions.id, id));
  return { id, status };
}

export async function bulkCreateWorkflowSuggestions(suggestions: InsertWorkflowSuggestion[]) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  if (suggestions.length === 0) return { created: 0 };
  await db.insert(workflowSuggestions).values(suggestions);
  return { created: suggestions.length };
}

/* ─── Analytics helpers ─── */
export async function trackAnalyticsEvent(event: Omit<InsertAnalyticsEvent, "id" | "createdAt">) {
  const db = await getDb();
  if (!db) return null;
  const [inserted] = await db.insert(analyticsEvents).values(event as any).returning({ id: analyticsEvents.id });
  return inserted?.id ?? null;
}

export async function getAnalyticsEvents(filters?: {
  eventType?: string;
  category?: string;
  startDate?: Date;
  endDate?: Date;
}) {
  const db = await getDb();
  if (!db) return [];
  const conditions: any[] = [];
  if (filters?.eventType) conditions.push(eq(analyticsEvents.eventType, filters.eventType as any));
  if (filters?.category) conditions.push(eq(analyticsEvents.category, filters.category));
  if (filters?.startDate) conditions.push(gte(analyticsEvents.createdAt, filters.startDate));
  if (filters?.endDate) conditions.push(lte(analyticsEvents.createdAt, filters.endDate));

  const query = db.select().from(analyticsEvents);
  if (conditions.length > 0) {
    return query.where(and(...conditions)).orderBy(desc(analyticsEvents.createdAt)).limit(1000);
  }
  return query.orderBy(desc(analyticsEvents.createdAt)).limit(1000);
}

export async function getAnalyticsSummary(startDate?: Date, endDate?: Date) {
  const db = await getDb();
  if (!db) return null;
  const conditions: any[] = [];
  if (startDate) conditions.push(gte(analyticsEvents.createdAt, startDate));
  if (endDate) conditions.push(lte(analyticsEvents.createdAt, endDate));

  const baseQuery = conditions.length > 0
    ? db.select().from(analyticsEvents).where(and(...conditions))
    : db.select().from(analyticsEvents);

  const allEvents = await baseQuery;

  const totalSessions = allEvents.filter(e => e.eventType === "session_start").length;
  const resolvedByBot = allEvents.filter(e => e.eventType === "resolved_by_bot").length;
  const resolvedByAgent = allEvents.filter(e => e.eventType === "resolved_by_agent").length;
  const escalated = allEvents.filter(e => e.eventType === "escalated").length;
  const abandoned = allEvents.filter(e => e.eventType === "abandoned").length;
  const messagesSent = allEvents.filter(e => e.eventType === "message_sent").length;
  const messagesReceived = allEvents.filter(e => e.eventType === "message_received").length;
  const buttonClicks = allEvents.filter(e => e.eventType === "button_clicked").length;
  const positiveFeedback = allEvents.filter(e => e.eventType === "feedback_positive").length;
  const negativeFeedback = allEvents.filter(e => e.eventType === "feedback_negative").length;

  // Category breakdown
  const categories = ["orders", "shipping", "returning", "cancelling"];
  const categoryBreakdown = categories.map(cat => ({
    category: cat,
    count: allEvents.filter(e => e.category === cat).length,
    resolved: allEvents.filter(e => e.category === cat && (e.eventType === "resolved_by_bot" || e.eventType === "resolved_by_agent")).length,
  }));

  return {
    totalSessions,
    resolvedByBot,
    resolvedByAgent,
    escalated,
    abandoned,
    messagesSent,
    messagesReceived,
    buttonClicks,
    positiveFeedback,
    negativeFeedback,
    resolutionRate: totalSessions > 0 ? Math.round(((resolvedByBot + resolvedByAgent) / totalSessions) * 100) : 0,
    botResolutionRate: totalSessions > 0 ? Math.round((resolvedByBot / totalSessions) * 100) : 0,
    categoryBreakdown,
    totalEvents: allEvents.length,
  };
}

export async function getIntentStats(startDate?: Date, endDate?: Date) {
  const db = await getDb();
  if (!db) return [];
  const conditions: any[] = [eq(analyticsEvents.eventType, "intent_detected" as any)];
  if (startDate) conditions.push(gte(analyticsEvents.createdAt, startDate));
  if (endDate) conditions.push(lte(analyticsEvents.createdAt, endDate));
  const rows = await db.select({
    category: analyticsEvents.category,
    count: sql<number>`COUNT(*)`,
  })
    .from(analyticsEvents)
    .where(and(...conditions))
    .groupBy(analyticsEvents.category)
    .orderBy(sql`COUNT(*) DESC`)
    .limit(20);
  return rows.map(r => ({ category: r.category ?? "unclassified", count: Number(r.count) }));
}

export async function getResponseTimeStats(startDate?: Date, endDate?: Date) {
  const db = await getDb();
  if (!db) return { avgSeconds: null, p50Seconds: null, sampleSize: 0 };
  const conditions: any[] = [
    isNotNull(conversations.escalatedAt),
    isNotNull(conversations.firstAgentReplyAt),
  ];
  if (startDate) conditions.push(gte(conversations.createdAt, startDate));
  if (endDate) conditions.push(lte(conversations.createdAt, endDate));
  const rows = await db.select({
    seconds: sql<number>`EXTRACT(EPOCH FROM ("first_agent_reply_at" - "escalated_at"))`,
  })
    .from(conversations)
    .where(and(...conditions));
  if (!rows.length) return { avgSeconds: null, p50Seconds: null, sampleSize: 0 };
  const times = rows.map(r => Number(r.seconds)).filter(n => n >= 0).sort((a, b) => a - b);
  const avg = times.reduce((s, v) => s + v, 0) / times.length;
  const p50 = times[Math.floor(times.length * 0.5)];
  return { avgSeconds: Math.round(avg), p50Seconds: Math.round(p50), sampleSize: times.length };
}

/* ─── Data Sources helpers ─── */
export async function createDataSource(source: Omit<InsertDataSource, "id" | "createdAt" | "updatedAt">) {
  const db = await getDb();
  if (!db) return null;
  const [inserted] = await db.insert(dataSources).values(source as any).returning({ id: dataSources.id });
  return inserted?.id ?? null;
}

export async function getDataSources() {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(dataSources).orderBy(desc(dataSources.createdAt));
}

export async function getDataSourceById(id: number) {
  const db = await getDb();
  if (!db) return null;
  const [source] = await db.select().from(dataSources).where(eq(dataSources.id, id));
  return source || null;
}

export async function updateDataSource(id: number, updates: Partial<InsertDataSource>) {
  const db = await getDb();
  if (!db) return null;
  await db.update(dataSources).set(updates as any).where(eq(dataSources.id, id));
  return getDataSourceById(id);
}

export async function deleteDataSource(id: number) {
  const db = await getDb();
  if (!db) return;
  await db.delete(dataSources).where(eq(dataSources.id, id));
}

/* ─── API Connections helpers ─── */
export async function createApiConnection(conn: Omit<InsertApiConnection, "id" | "createdAt" | "updatedAt">) {
  const db = await getDb();
  if (!db) return null;
  const [inserted] = await db.insert(apiConnections).values(conn as any).returning({ id: apiConnections.id });
  return inserted?.id ?? null;
}

export async function getApiConnections() {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(apiConnections).orderBy(desc(apiConnections.createdAt));
}

export async function getApiConnectionById(id: number) {
  const db = await getDb();
  if (!db) return null;
  const [conn] = await db.select().from(apiConnections).where(eq(apiConnections.id, id));
  return conn || null;
}

export async function updateApiConnection(id: number, updates: Partial<InsertApiConnection>) {
  const db = await getDb();
  if (!db) return null;
  await db.update(apiConnections).set(updates as any).where(eq(apiConnections.id, id));
  return getApiConnectionById(id);
}

export async function deleteApiConnection(id: number) {
  const db = await getDb();
  if (!db) return;
  await db.delete(apiConnections).where(eq(apiConnections.id, id));
}

export async function incrementApiConnectionExecution(id: number) {
  const db = await getDb();
  if (!db) return;
  const conn = await getApiConnectionById(id);
  if (conn) {
    await db.update(apiConnections).set({
      executionCount: conn.executionCount + 1,
      lastExecutedAt: new Date(),
    } as any).where(eq(apiConnections.id, id));
  }
}

/* ─── Knowledge Base helpers ─────────────────────────────── */

/**
 * Simple keyword-based knowledge search.
 * Splits the user question into words, filters stop-words, searches
 * question text case-insensitively. Returns the best match or null.
 */
export async function searchKnowledge(userQuestion: string): Promise<{ id: number; question: string; answer: string; category: string | null } | null> {
  const db = await getDb();
  if (!db) return null;

  const entries = await db
    .select({ id: knowledgeEntries.id, question: knowledgeEntries.question, answer: knowledgeEntries.answer, category: knowledgeEntries.category })
    .from(knowledgeEntries)
    .where(eq(knowledgeEntries.status, "active"))
    .limit(1000); // safety cap — migrate to DB full-text search when KB exceeds this

  if (!entries.length) return null;

  const stopWords = new Set(["the","a","an","is","are","do","you","have","i","can","tell","me","about","how","what","when","where","why","which","my","your","our"]);
  const queryWords = userQuestion.toLowerCase().replace(/[^a-z0-9 ]/g, " ").split(/\s+/).filter(w => w.length > 2 && !stopWords.has(w));

  if (!queryWords.length) return null;

  let best: { entry: typeof entries[0]; score: number } | null = null;
  for (const entry of entries) {
    const text = entry.question.toLowerCase();
    let score = 0;
    for (const word of queryWords) {
      if (text.includes(word)) score++;
    }
    if (score > 0 && (!best || score > best.score)) {
      best = { entry, score };
    }
  }

  if (!best) return null;
  const threshold = queryWords.some(w => w.length > 5) ? 1 : 2;
  if (best.score < threshold) return null;

  return best.entry;
}

export async function incrementKnowledgeUseCount(id: number) {
  const db = await getDb();
  if (!db) return;
  await db.update(knowledgeEntries)
    .set({ useCount: sql`${knowledgeEntries.useCount} + 1`, updatedAt: new Date() })
    .where(eq(knowledgeEntries.id, id));
}

/**
 * Log an unanswered question as a Suggestion.
 * If the same question (fuzzy) already exists, increment its count.
 */
export async function logKnowledgeSuggestion(question: string) {
  const db = await getDb();
  if (!db) return;

  const key = question.slice(0, 80).toLowerCase();
  const existing = await db.select().from(knowledgeSuggestions)
    .where(eq(knowledgeSuggestions.status, "pending"))
    .orderBy(desc(knowledgeSuggestions.createdAt))
    .limit(50);

  const match = existing.find(s => s.question.slice(0, 80).toLowerCase() === key);
  if (match) {
    await db.update(knowledgeSuggestions)
      .set({ occurrenceCount: sql`${knowledgeSuggestions.occurrenceCount} + 1`, lastSeen: new Date() })
      .where(eq(knowledgeSuggestions.id, match.id));
  } else {
    await db.insert(knowledgeSuggestions).values({ question });
  }
}

export async function getKnowledgeEntries(status?: string) {
  const db = await getDb();
  if (!db) return [];
  const conditions = status ? [eq(knowledgeEntries.status, status)] : [];
  return db.select().from(knowledgeEntries)
    .where(conditions.length ? and(...conditions) : undefined)
    .orderBy(desc(knowledgeEntries.createdAt));
}

export async function getKnowledgeEntryById(id: number) {
  const db = await getDb();
  if (!db) return null;
  const [entry] = await db.select().from(knowledgeEntries).where(eq(knowledgeEntries.id, id));
  return entry ?? null;
}

export async function createKnowledgeEntry(data: { question: string; answer: string; category?: string; source?: string }) {
  const db = await getDb();
  if (!db) return null;
  const [inserted] = await db.insert(knowledgeEntries).values({ ...data, useCount: 0, status: "active" }).returning({ id: knowledgeEntries.id });
  return inserted?.id ?? null;
}

export async function updateKnowledgeEntry(id: number, data: Partial<{ question: string; answer: string; category: string; status: string }>) {
  const db = await getDb();
  if (!db) return;
  await db.update(knowledgeEntries).set({ ...data, updatedAt: new Date() }).where(eq(knowledgeEntries.id, id));
}

export async function deleteKnowledgeEntry(id: number) {
  const db = await getDb();
  if (!db) return;
  await db.delete(knowledgeEntries).where(eq(knowledgeEntries.id, id));
}

export async function bulkCreateKnowledgeEntries(entries: { question: string; answer: string; category?: string; source?: string }[]) {
  const db = await getDb();
  if (!db) return { created: 0 };
  if (!entries.length) return { created: 0 };
  await db.insert(knowledgeEntries).values(entries.map(e => ({ ...e, useCount: 0, status: "active" })));
  return { created: entries.length };
}

export async function getKnowledgeSuggestions(status?: string) {
  const db = await getDb();
  if (!db) return [];
  const conditions = status ? [eq(knowledgeSuggestions.status, status)] : [];
  return db.select().from(knowledgeSuggestions)
    .where(conditions.length ? and(...conditions) : undefined)
    .orderBy(desc(knowledgeSuggestions.occurrenceCount));
}

export async function promoteKnowledgeSuggestion(id: number, answer: string, category?: string) {
  const db = await getDb();
  if (!db) return null;
  const [suggestion] = await db.select().from(knowledgeSuggestions).where(eq(knowledgeSuggestions.id, id));
  if (!suggestion) return null;

  const entryId = await createKnowledgeEntry({ question: suggestion.question, answer, category, source: "suggestion" });
  await db.update(knowledgeSuggestions)
    .set({ status: "promoted", promotedToId: entryId ?? undefined })
    .where(eq(knowledgeSuggestions.id, id));
  return entryId;
}

export async function dismissKnowledgeSuggestion(id: number) {
  const db = await getDb();
  if (!db) return;
  await db.update(knowledgeSuggestions).set({ status: "dismissed" }).where(eq(knowledgeSuggestions.id, id));
}

// Knowledge Products
export async function getKnowledgeProducts() {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(knowledgeProducts).where(eq(knowledgeProducts.status, "active")).orderBy(knowledgeProducts.model);
}

export async function bulkCreateKnowledgeProducts(products: Omit<InsertKnowledgeProduct, "id" | "createdAt" | "status">[]) {
  const db = await getDb();
  if (!db) return { created: 0 };
  if (!products.length) return { created: 0 };
  await db.insert(knowledgeProducts).values(products.map(p => ({ ...p, status: "active" })));
  return { created: products.length };
}

export async function deleteAllKnowledgeProducts() {
  const db = await getDb();
  if (!db) return;
  await db.delete(knowledgeProducts);
}