chatbot/server/routers.ts

import { z } from "zod";
import { nanoid } from "nanoid";
import { TRPCError } from "@trpc/server";
import { COOKIE_NAME } from "@shared/const";
import { getSessionCookieOptions } from "./_core/cookies";
import { systemRouter } from "./_core/systemRouter";
import { publicProcedure, protectedProcedure, agentProcedure, adminProcedure, router } from "./_core/trpc";
import { invokeLLM } from "./_core/llm";
import { notifyOwner } from "./_core/notification";
import bcrypt from "bcryptjs";
import {
  createConversation, getConversations, getConversationsAdvanced, getConversationById,
  getConversationBySessionId, updateConversationStatus, getConversationStats,
  addMessage, getMessagesByConversation,
  getConversationMessageCounts, getAgentUsers,
  bulkUpdateConversationStatus, deleteConversations,
  saveWorkflow, getWorkflow,
  getWorkflowSuggestions, updateWorkflowSuggestionStatus, bulkCreateWorkflowSuggestions,
  getAllUsers, updateUserRole, updateUserErpContactCid, getUserById, deleteUser, getUserByEmail,
  getUserByEmailWithPassword, createUserWithPassword, updateUserPassword,
  createPasswordResetToken, getPasswordResetToken, markPasswordResetTokenUsed,
  createInvitation, getAllInvitations, getInvitationByToken, updateInvitationStatus,
  expireOldInvitations, getInvitationByEmail,
  createAuditLog, getAuditLogs,
  trackAnalyticsEvent, getAnalyticsEvents, getAnalyticsSummary,
  createDataSource, getDataSources, getDataSourceById, updateDataSource, deleteDataSource,
  createApiConnection, getApiConnections, getApiConnectionById, updateApiConnection, deleteApiConnection, incrementApiConnectionExecution,
  searchKnowledge, incrementKnowledgeUseCount, logKnowledgeSuggestion,
  getKnowledgeEntries, getKnowledgeEntryById, createKnowledgeEntry,
  updateKnowledgeEntry, deleteKnowledgeEntry, bulkCreateKnowledgeEntries,
  getKnowledgeSuggestions, promoteKnowledgeSuggestion, dismissKnowledgeSuggestion,
  getKnowledgeProducts, bulkCreateKnowledgeProducts, deleteAllKnowledgeProducts,
} from "./db";
import { detectFlowIntent, executeFlow } from "./flowEngine";
import { messages } from "../drizzle/schema";
import { eq, desc } from "drizzle-orm";
import { sdk } from "./_core/sdk";
import { ENV } from "./_core/env";
import {
  lookupOrder,
  lookupOrdersByCustomer,
  lookupOrdersByPO,
  lookupCatalog,
  lookupStock,
  lookupContact,
} from "./erpTools";

/* ─── Homelegance chatbot system prompt ─── */
const SYSTEM_PROMPT = `You are **Ellie**, the Homelegance AI Assistant — a warm, knowledgeable furniture expert helping visitors on homelegance.com. Always introduce yourself as Ellie when greeting new visitors.

About Homelegance:
- Homelegance is a leading wholesale furniture manufacturer and distributor
- They offer living room, bedroom, dining room, and accent furniture
- Their customers are primarily furniture retailers and dealers (B2B)
- They have collections ranging from traditional to contemporary styles

Your capabilities:
1. **Product Discovery**: Help users find furniture by category, style, collection, or room type
2. **Order Status**: Help dealers check order status (ask for order number)
3. **Dealer Locator**: Help find authorized Homelegance dealers by location
4. **Warranty & Returns**: Answer questions about warranty policies and return procedures
5. **General FAQ**: Answer common questions about Homelegance products and services

Guidelines:
- Be warm, professional, and concise
- When users ask about products, suggest specific categories and collections
- For order inquiries, always ask for the order number
- If you cannot help with something, offer to connect them with a human agent
- Keep responses under 150 words unless detailed information is needed
- Use markdown formatting for lists and emphasis when helpful`;

export const appRouter = router({
  system: systemRouter,
  auth: router({
    me: publicProcedure.query(opts => opts.ctx.user),
    logout: publicProcedure.mutation(({ ctx }) => {
      const cookieOptions = getSessionCookieOptions(ctx.req);
      ctx.res.clearCookie(COOKIE_NAME, { ...cookieOptions, maxAge: -1 });
      return { success: true } as const;
    }),

    /** Register a new user with email/password */
    register: publicProcedure
      .input(z.object({
        email: z.string().email(),
        password: z.string().min(8).max(128),
        name: z.string().min(1).max(100),
      }))
      .mutation(async ({ input, ctx }) => {
        const existing = await getUserByEmailWithPassword(input.email);
        if (existing) {
          throw new TRPCError({
            code: "CONFLICT",
            message: "An account with this email already exists",
          });
        }

        const passwordHash = await bcrypt.hash(input.password, 12);
        const user = await createUserWithPassword({
          email: input.email,
          name: input.name,
          passwordHash,
        });

        if (!user) {
          throw new TRPCError({ code: "INTERNAL_SERVER_ERROR", message: "Failed to create account" });
        }

        // Create session
        const sessionToken = await sdk.createSessionToken(user.openId, {
          name: user.name || "",
          expiresInMs: 30 * 24 * 60 * 60 * 1000, // 30 days
        });
        const cookieOptions = getSessionCookieOptions(ctx.req);
        ctx.res.cookie(COOKIE_NAME, sessionToken, { ...cookieOptions, maxAge: 30 * 24 * 60 * 60 * 1000 });

        return { success: true, user: { id: user.id, name: user.name, email: user.email, role: user.role } };
      }),

    /** Login with email/password */
    login: publicProcedure
      .input(z.object({
        email: z.string().email(),
        password: z.string().min(1),
      }))
      .mutation(async ({ input, ctx }) => {
        const user = await getUserByEmailWithPassword(input.email);
        if (!user || !user.passwordHash) {
          throw new TRPCError({
            code: "UNAUTHORIZED",
            message: "Invalid email or password",
          });
        }

        const isValid = await bcrypt.compare(input.password, user.passwordHash);
        if (!isValid) {
          throw new TRPCError({
            code: "UNAUTHORIZED",
            message: "Invalid email or password",
          });
        }

        // Create session
        const sessionToken = await sdk.createSessionToken(user.openId, {
          name: user.name || "",
          expiresInMs: 30 * 24 * 60 * 60 * 1000,
        });
        const cookieOptions = getSessionCookieOptions(ctx.req);
        ctx.res.cookie(COOKIE_NAME, sessionToken, { ...cookieOptions, maxAge: 30 * 24 * 60 * 60 * 1000 });

        return { success: true, user: { id: user.id, name: user.name, email: user.email, role: user.role } };
      }),

    /** Request password reset — generates a token */
    forgotPassword: publicProcedure
      .input(z.object({ email: z.string().email() }))
      .mutation(async ({ input }) => {
        const user = await getUserByEmailWithPassword(input.email);
        // Always return success to prevent email enumeration
        if (!user || !user.passwordHash) {
          return { success: true, message: "If an account with that email exists, a reset link has been generated." };
        }

        const token = nanoid(32);
        const expiresAt = new Date(Date.now() + 60 * 60 * 1000); // 1 hour

        await createPasswordResetToken({
          userId: user.id,
          token,
          expiresAt,
        });

        // In production, you would send an email here.
        // For demo, we return the token (in production, NEVER return the token)
        try {
          await notifyOwner({
            title: "Password Reset Requested",
            content: `Password reset requested for ${input.email}. Reset link: /reset-password/${token}`,
          });
        } catch (e) { /* non-critical */ }

        return { success: true, message: "If an account with that email exists, a reset link has been generated." };
      }),

    /** Validate a password reset token */
    validateResetToken: publicProcedure
      .input(z.object({ token: z.string() }))
      .query(async ({ input }) => {
        const resetToken = await getPasswordResetToken(input.token);
        if (!resetToken) {
          return { valid: false, reason: "Invalid reset link" } as const;
        }
        if (resetToken.usedAt) {
          return { valid: false, reason: "This reset link has already been used" } as const;
        }
        if (new Date() > resetToken.expiresAt) {
          return { valid: false, reason: "This reset link has expired" } as const;
        }
        const user = await getUserById(resetToken.userId);
        return { valid: true, email: user?.email || "" } as const;
      }),

    /** Reset password using a valid token */
    resetPassword: publicProcedure
      .input(z.object({
        token: z.string(),
        newPassword: z.string().min(8).max(128),
      }))
      .mutation(async ({ input }) => {
        const resetToken = await getPasswordResetToken(input.token);
        if (!resetToken) {
          throw new TRPCError({ code: "NOT_FOUND", message: "Invalid reset link" });
        }
        if (resetToken.usedAt) {
          throw new TRPCError({ code: "BAD_REQUEST", message: "This reset link has already been used" });
        }
        if (new Date() > resetToken.expiresAt) {
          throw new TRPCError({ code: "BAD_REQUEST", message: "This reset link has expired" });
        }

        const passwordHash = await bcrypt.hash(input.newPassword, 12);
        await updateUserPassword(resetToken.userId, passwordHash);
        await markPasswordResetTokenUsed(resetToken.id);

        return { success: true, message: "Password has been reset successfully" };
      }),
  }),

  /* ─── Chat API (public — used by the chatbot widget) ─── */
  chat: router({
    startSession: publicProcedure
      .input(z.object({
        visitorName: z.string().optional(),
        visitorEmail: z.string().email().optional(),
      }).optional())
      .mutation(async ({ input }) => {
        const sessionId = nanoid(16);
        const conversation = await createConversation({
          sessionId,
          visitorName: input?.visitorName ?? "Visitor",
          visitorEmail: input?.visitorEmail,
          status: "active",
        });
        await addMessage({
          conversationId: conversation.id,
          sender: "bot",
          content: "Welcome to Homelegance! I'm **Ellie**, your AI furniture assistant.",
          metadata: {
            quickReplies: ["🔥 Hot Deals", "📦 Order Status", "🛋️ Product Catalog"],
          },
        });
        return { sessionId, conversationId: conversation.id };
      }),

    sendMessage: publicProcedure
      .input(z.object({
        sessionId: z.string(),
        content: z.string().min(1).max(2000),
      }))
      .mutation(async ({ input, ctx }) => {
        console.log(`[sendMessage] sessionId=${input.sessionId} userId=${ctx.user?.id ?? "anon"} content="${input.content.slice(0, 60)}"`);
        const conversation = await getConversationBySessionId(input.sessionId);
        if (!conversation) throw new Error("Conversation not found");

        await addMessage({
          conversationId: conversation.id,
          sender: "visitor",
          content: input.content,
        });

        if (conversation.status === "escalated") {
          // Notify owner/agents about new message in escalated conversation
          notifyOwner({
            title: `New message from ${conversation.visitorName || "Visitor"}`,
            content: `Customer message in escalated conversation #${conversation.id}: "${input.content.slice(0, 200)}${input.content.length > 200 ? "..." : ""}"`,
          }).catch(() => {}); // fire-and-forget
          return {
            reply: null,
            status: "escalated" as const,
            message: "Your conversation has been transferred to a human agent. They will respond shortly.",
          };
        }

        // ── Knowledge Base First ──────────────────────────────────────────────
        // Search Q&A knowledge base before calling LLM.
        let knowledgeAnswer: string | null = null;
        try {
          const kbMatch = await searchKnowledge(input.content);
          if (kbMatch) {
            knowledgeAnswer = kbMatch.answer;
            // Fire-and-forget: increment use count
            incrementKnowledgeUseCount(kbMatch.id).catch(() => {});
          }
        } catch (kbErr) {
          console.error("[KB] search error:", kbErr);
        }

        if (knowledgeAnswer) {
          await addMessage({ conversationId: conversation.id, sender: "bot", content: knowledgeAnswer });
          return { reply: knowledgeAnswer, status: conversation.status, source: "knowledge" as const };
        }

        // ── Workflow Flow Engine ──────────────────────────────────────────────
        // Check if user message triggers a live Support Flow
        let flowResult: { content: string; shouldEscalate?: boolean; flowId: string } | null = null;
        try {
          const matchedFlowId = detectFlowIntent(input.content);
          if (matchedFlowId) {
            flowResult = await executeFlow(matchedFlowId, input.content);
          }
        } catch (flowErr) {
          console.error("[Flow] engine error:", flowErr);
        }

        if (flowResult) {
          trackAnalyticsEvent({
            conversationId: conversation.id,
            sessionId: input.sessionId,
            eventType: "flow_triggered",
            category: flowResult.flowId,
          }).catch(() => {});

          if (flowResult.shouldEscalate) {
            await updateConversationStatus(conversation.id, "escalated");
            await addMessage({ conversationId: conversation.id, sender: "bot", content: flowResult.content });
            return { reply: flowResult.content, status: "escalated" as const, source: "flow" as const };
          }

          await addMessage({ conversationId: conversation.id, sender: "bot", content: flowResult.content });
          return { reply: flowResult.content, status: conversation.status, source: "flow" as const };
        }

        const history = await getMessagesByConversation(conversation.id);

        // ── ERP intent detection & context injection ──────────────────────────
        // Detect what the visitor is asking about and fetch live ERP data.
        // The result is appended to the system prompt so Claude has real data.
        let erpContext = "";
        if (ENV.erpApiKey) {
          try {
            const msg = input.content;
            const msgLower = msg.toLowerCase();

            // Build user context for permission-scoped ERP queries.
            // Dealers (role="user") are automatically scoped to their own CID by the bridge.
            const userCtx = {
              role: ctx.user?.role ?? "user",
              erpContactCid: ctx.user?.erpContactCid ?? null,
            };

            // 1. Single order lookup
            // Format A: "SO: A8487", "SO# B123"  — SO is a label, alphanumeric ID follows
            // Format B: "SO-12345", "SO 12345"   — SO is part of the digits-only ID
            const soLabelMatch = msg.match(/\bSO[-:\s#]+([A-Z]\d{3,})\b/i);
            const soPrefixMatch = msg.match(/\bSO[-\s]?\d{4,}\b/i);
            const soId = soLabelMatch
              ? soLabelMatch[1].toUpperCase()
              : soPrefixMatch
                ? soPrefixMatch[0].replace(/\s/, "-").toUpperCase()
                : null;
            if (soId) {
              erpContext = await lookupOrder(soId, userCtx);

            // 2. "my orders" / "recent orders" — needs customer CID on conversation
            } else if (/\b(my orders?|recent orders?|order history|order status|help with my order|check.*order|view.*order|see.*order|show.*order)\b/.test(msgLower)) {
              const cid = ctx.user?.erpContactCid ?? (conversation as any).customerId as string | undefined;
              console.log(`[ERP] intent=my_orders user=${ctx.user?.id ?? "anon"} role=${userCtx.role} cid=${cid ?? "none"}`);
              if (cid) {
                erpContext = await lookupOrdersByCustomer(cid, 5, userCtx);
              } else {
                erpContext = "NOTE: This user has not been linked to an ERP dealer account. Their erpContactCid is not set. Let them know they should contact support to link their dealer account before order history can be retrieved.";
              }

            // 3. PO number lookup  —  "PO-12345", "purchase order 5678"
            } else {
              const poMatch = msg.match(/\bPO[-\s]?\d{3,}\b/i);
              if (poMatch) {
                erpContext = await lookupOrdersByPO(poMatch[0], userCtx);
              }
            }

            // 4. Stock / inventory
            if (!erpContext && /\b(in stock|available|inventory|stock|availability)\b/.test(msgLower)) {
              // Try to extract a model number: capital letters + digits, e.g. "B1234-1"
              const modelMatch = msg.match(/\b([A-Z]{1,4}[-\s]?\d{3,}[-\w]*)\b/);
              if (modelMatch) {
                erpContext = await lookupStock({ model: modelMatch[1] }, userCtx);
              }
            }

            // 5. Product / catalog search
            if (!erpContext && /\b(product|catalog|collection|furniture|model|item|sofa|bed|table|chair|dresser|cabinet)\b/.test(msgLower)) {
              // Pull keywords: skip common stop words
              const stopWords = new Set(["the","a","an","is","are","do","you","have","i","can","tell","me","about","show","what","which"]);
              const keywords = msg
                .replace(/[^a-zA-Z0-9 ]/g, " ")
                .split(/\s+/)
                .filter(w => w.length > 2 && !stopWords.has(w.toLowerCase()))
                .slice(0, 4)
                .join(" ");
              if (keywords) {
                erpContext = await lookupCatalog({ description: keywords, limit: 8 }, userCtx);
              }
            }

            // 6. Customer / dealer lookup (admin/agent only — dealers see their own record via CID)
            if (!erpContext && ctx.user?.role !== "user" && /\b(customer|dealer|account|contact|company)\b/.test(msgLower)) {
              const nameMatch = msg.match(/(?:customer|dealer|account|contact|company)[:\s]+([A-Za-z &'.-]{3,40})/i);
              if (nameMatch) {
                erpContext = await lookupContact({ company: nameMatch[1].trim() }, userCtx);
              }
            }
          } catch (erpErr) {
            console.error("[ERP] intent lookup error:", erpErr);
            erpContext = "NOTE: ERP data lookup failed (service temporarily unavailable). Do not mention specific order details. Let the user know order lookup is temporarily unavailable and suggest they try again shortly or contact their sales rep.";
          }
        }

        const systemContent = erpContext
          ? `${SYSTEM_PROMPT}\n\n---\n[ERP CONTEXT — live data retrieved for this query]\n${erpContext}\n---`
          : SYSTEM_PROMPT;

        const llmMessages = [
          { role: "system" as const, content: systemContent },
          ...history.map(m => ({
            role: (m.sender === "visitor" ? "user" : "assistant") as "user" | "assistant",
            content: m.content,
          })),
        ];

        const escalationKeywords = ["speak to human", "representative", "real person", "agent", "talk to someone", "human agent"];
        const shouldEscalate = escalationKeywords.some(kw =>
          input.content.toLowerCase().includes(kw)
        );

        if (shouldEscalate) {
          await updateConversationStatus(conversation.id, "escalated");
          const escalationMsg = "I understand you'd like to speak with a team member. I'm connecting you now — a Homelegance representative will be with you shortly. In the meantime, is there anything else I can help with?";
          await addMessage({
            conversationId: conversation.id,
            sender: "bot",
            content: escalationMsg,
          });
          // Notify owner about escalation
          notifyOwner({
            title: `Chat escalated: ${conversation.visitorName || "Visitor"}`,
            content: `A customer has requested to speak with a human agent. Conversation #${conversation.id}. Last message: "${input.content.slice(0, 200)}"`,
          }).catch(() => {}); // fire-and-forget
          return { reply: escalationMsg, status: "escalated" as const };
        }

        try {
          const llmResult = await invokeLLM({ messages: llmMessages });
          const botReply = llmResult.choices[0]?.message?.content as string || "I apologize, I'm having trouble processing your request. Would you like to speak with a team member?";

          await addMessage({
            conversationId: conversation.id,
            sender: "bot",
            content: botReply,
          });

          // Auto-log as suggestion for continuous improvement
          logKnowledgeSuggestion(input.content).catch(() => {});

          return { reply: botReply, status: conversation.status, source: "llm" as const };
        } catch (error) {
          console.error("[Chat] LLM error:", error);
          const fallback = "I apologize for the inconvenience. I'm experiencing a temporary issue. Would you like me to connect you with a human agent?";
          await addMessage({
            conversationId: conversation.id,
            sender: "bot",
            content: fallback,
          });
          return { reply: fallback, status: conversation.status };
        }
      }),

    getMessages: publicProcedure
      .input(z.object({ sessionId: z.string() }))
      .query(async ({ input }) => {
        const conversation = await getConversationBySessionId(input.sessionId);
        if (!conversation) return { messages: [], status: "closed" as const };
        const msgs = await getMessagesByConversation(conversation.id);
        return { messages: msgs, status: conversation.status };
      }),
  }),

  /* ─── Agent Dashboard API (requires agent or admin role) ─── */
  agent: router({
    /** Legacy simple list (kept for backward compat) */
    conversations: agentProcedure
      .input(z.object({ status: z.string().optional() }).optional())
      .query(async ({ input }) => {
        return getConversations(input?.status);
      }),

    /** Advanced conversation query with pagination, search, filters, sorting */
    conversationsAdvanced: agentProcedure
      .input(z.object({
        page: z.number().min(1).default(1),
        pageSize: z.number().min(5).max(100).default(20),
        status: z.string().optional(),
        search: z.string().optional(),
        agentId: z.number().optional(),
        dateFrom: z.string().optional(),
        dateTo: z.string().optional(),
        sortBy: z.enum(["updated", "created", "visitor", "status", "customerId", "salesRep", "agent"]).default("updated"),
        sortOrder: z.enum(["asc", "desc"]).default("desc"),
      }).optional())
      .query(async ({ input }) => {
        const result = await getConversationsAdvanced(input || {});
        // Enrich with message counts
        const ids = result.conversations.map((c) => c.id);
        const messageCounts = await getConversationMessageCounts(ids);
        const enriched = result.conversations.map((c) => ({
          ...c,
          messageCount: messageCounts[c.id] || 0,
        }));
        return { ...result, conversations: enriched };
      }),

    /** Get list of agents for filter dropdown */
    agents: agentProcedure.query(async () => {
      return getAgentUsers();
    }),

    stats: agentProcedure.query(async () => {
      return getConversationStats();
    }),

    messages: agentProcedure
      .input(z.object({ conversationId: z.number() }))
      .query(async ({ input }) => {
        return getMessagesByConversation(input.conversationId);
      }),

    reply: agentProcedure
      .input(z.object({
        conversationId: z.number(),
        content: z.string().min(1).max(5000),
      }))
      .mutation(async ({ input, ctx }) => {
        const conversation = await getConversationById(input.conversationId);
        if (!conversation) throw new Error("Conversation not found");

        const msg = await addMessage({
          conversationId: input.conversationId,
          sender: "agent",
          content: input.content,
          metadata: { agentName: ctx.user.name || "Agent", agentId: ctx.user.id },
        });

        if (conversation.status === "escalated") {
          await updateConversationStatus(input.conversationId, "escalated", ctx.user.id);
        }

        return msg;
      }),

    updateStatus: agentProcedure
      .input(z.object({
        conversationId: z.number(),
        status: z.enum(["active", "escalated", "resolved", "closed"]),
      }))
      .mutation(async ({ input, ctx }) => {
        return updateConversationStatus(input.conversationId, input.status, ctx.user.id);
      }),

    /** Bulk update conversation status */
    bulkUpdateStatus: agentProcedure
      .input(z.object({
        conversationIds: z.array(z.number()).min(1),
        status: z.enum(["active", "escalated", "resolved", "closed"]),
      }))
      .mutation(async ({ input, ctx }) => {
        return bulkUpdateConversationStatus(input.conversationIds, input.status, ctx.user.id);
      }),

    /** Delete conversations (admin only) */
    deleteConversations: adminProcedure
      .input(z.object({
        conversationIds: z.array(z.number()).min(1),
      }))
      .mutation(async ({ input, ctx }) => {
        const result = await deleteConversations(input.conversationIds);
        await createAuditLog({
          action: "delete_conversations",
          actorId: ctx.user.id,
          actorName: ctx.user.name || "Admin",
          details: { count: input.conversationIds.length, ids: input.conversationIds },
        });
        return result;
      }),
  }),

  /* ─── User Management API (admin only) ─── */
  users: router({
    /** List all users */
    list: adminProcedure.query(async () => {
      return getAllUsers();
    }),

    /** Update a user's role */
    updateRole: adminProcedure
      .input(z.object({
        userId: z.number(),
        role: z.enum(["user", "agent", "admin"]),
      }))
      .mutation(async ({ input, ctx }) => {
        if (input.userId === ctx.user.id) {
          throw new TRPCError({
            code: "BAD_REQUEST",
            message: "You cannot change your own role",
          });
        }
        const targetUser = await getUserById(input.userId);
        if (!targetUser) {
          throw new TRPCError({ code: "NOT_FOUND", message: "User not found" });
        }
        const previousRole = targetUser.role;
        const updated = await updateUserRole(input.userId, input.role);
        if (!updated) {
          throw new TRPCError({ code: "NOT_FOUND", message: "User not found" });
        }
        // Audit log
        await createAuditLog({
          action: "role_change",
          actorId: ctx.user.id,
          actorName: ctx.user.name || "Admin",
          targetId: input.userId,
          targetName: targetUser.name || targetUser.email || "User",
          details: { previousRole, newRole: input.role },
        });
        return updated;
      }),

    /** Get a single user by ID */
    getById: adminProcedure
      .input(z.object({ userId: z.number() }))
      .query(async ({ input }) => {
        return getUserById(input.userId);
      }),

    /** Set or clear the ERP ContactID linked to a user (for dealer permission scoping) */
    updateErpContactCid: adminProcedure
      .input(z.object({
        userId: z.number(),
        erpContactCid: z.string().max(64).nullable(),
      }))
      .mutation(async ({ input, ctx }) => {
        const targetUser = await getUserById(input.userId);
        if (!targetUser) {
          throw new TRPCError({ code: "NOT_FOUND", message: "User not found" });
        }
        const updated = await updateUserErpContactCid(input.userId, input.erpContactCid);
        await createAuditLog({
          action: "erp_contact_cid_change",
          actorId: ctx.user.id,
          actorName: ctx.user.name || "Admin",
          targetId: input.userId,
          targetName: targetUser.name || targetUser.email || "User",
          details: { erpContactCid: input.erpContactCid },
        });
        return updated;
      }),

    /** Delete a user */
    delete: adminProcedure
      .input(z.object({ userId: z.number() }))
      .mutation(async ({ input, ctx }) => {
        if (input.userId === ctx.user.id) {
          throw new TRPCError({
            code: "BAD_REQUEST",
            message: "You cannot delete your own account",
          });
        }
        const targetUser = await getUserById(input.userId);
        if (!targetUser) {
          throw new TRPCError({ code: "NOT_FOUND", message: "User not found" });
        }
        const deleted = await deleteUser(input.userId);
        // Audit log
        await createAuditLog({
          action: "user_deleted",
          actorId: ctx.user.id,
          actorName: ctx.user.name || "Admin",
          targetId: input.userId,
          targetName: targetUser.name || targetUser.email || "User",
          details: { deletedRole: targetUser.role, deletedEmail: targetUser.email },
        });
        return { success: true, deletedUser: deleted };
      }),

    /** Bulk update roles */
    bulkUpdateRole: adminProcedure
      .input(z.object({
        userIds: z.array(z.number()).min(1).max(50),
        role: z.enum(["user", "agent", "admin"]),
      }))
      .mutation(async ({ input, ctx }) => {
        const results: { userId: number; success: boolean; error?: string }[] = [];
        for (const userId of input.userIds) {
          if (userId === ctx.user.id) {
            results.push({ userId, success: false, error: "Cannot change own role" });
            continue;
          }
          try {
            await updateUserRole(userId, input.role);
            results.push({ userId, success: true });
          } catch (e) {
            results.push({ userId, success: false, error: "Failed to update" });
          }
        }
        await createAuditLog({
          action: "bulk_role_change",
          actorId: ctx.user.id,
          actorName: ctx.user.name || "Admin",
          details: { userIds: input.userIds, newRole: input.role, results },
        });
        return results;
      }),

    /** Bulk delete users */
    bulkDelete: adminProcedure
      .input(z.object({
        userIds: z.array(z.number()).min(1).max(50),
      }))
      .mutation(async ({ input, ctx }) => {
        const results: { userId: number; success: boolean; error?: string }[] = [];
        for (const userId of input.userIds) {
          if (userId === ctx.user.id) {
            results.push({ userId, success: false, error: "Cannot delete own account" });
            continue;
          }
          try {
            await deleteUser(userId);
            results.push({ userId, success: true });
          } catch (e) {
            results.push({ userId, success: false, error: "Failed to delete" });
          }
        }
        await createAuditLog({
          action: "bulk_delete",
          actorId: ctx.user.id,
          actorName: ctx.user.name || "Admin",
          details: { userIds: input.userIds, results },
        });
        return results;
      }),

    /** Export users as CSV data */
    exportCsv: adminProcedure.query(async () => {
      const allUsers = await getAllUsers();
      const header = "ID,Name,Email,Role,Created At,Last Signed In";
      const rows = allUsers.map(u =>
        `${u.id},"${(u.name || "").replace(/"/g, '""')}","${(u.email || "").replace(/"/g, '""')}",${u.role},${u.createdAt?.toISOString() || ""},${u.lastSignedIn?.toISOString() || ""}`
      );
      return { csv: [header, ...rows].join("\n"), count: allUsers.length };
    }),
  }),

  /* ─── Invitation API (admin only) ─── */
  invitations: router({
    /** List all invitations */
    list: adminProcedure.query(async () => {
      // Auto-expire old invitations
      await expireOldInvitations();
      return getAllInvitations();
    }),

    /** Send a new invitation */
    send: adminProcedure
      .input(z.object({
        email: z.string().email(),
        role: z.enum(["user", "agent", "admin"]),
        message: z.string().max(500).optional(),
      }))
      .mutation(async ({ input, ctx }) => {
        // Check if user already exists with this email
        const existingUser = await getUserByEmail(input.email);
        if (existingUser) {
          throw new TRPCError({
            code: "CONFLICT",
            message: `A user with email ${input.email} already exists (role: ${existingUser.role})`,
          });
        }

        // Check for pending invitation to same email
        const existingInvites = await getInvitationByEmail(input.email);
        const pendingInvite = existingInvites.find(i => i.status === "pending");
        if (pendingInvite) {
          throw new TRPCError({
            code: "CONFLICT",
            message: `A pending invitation already exists for ${input.email}. Revoke it first to send a new one.`,
          });
        }

        const token = nanoid(32);
        const expiresAt = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000); // 7 days

        const invitation = await createInvitation({
          email: input.email,
          role: input.role,
          token,
          status: "pending",
          invitedById: ctx.user.id,
          invitedByName: ctx.user.name || "Admin",
          message: input.message || null,
          expiresAt,
        });

        // Audit log
        await createAuditLog({
          action: "invitation_sent",
          actorId: ctx.user.id,
          actorName: ctx.user.name || "Admin",
          targetName: input.email,
          details: { role: input.role, token, expiresAt: expiresAt.toISOString() },
        });

        // Notify owner
        try {
          await notifyOwner({
            title: `New Invitation Sent`,
            content: `${ctx.user.name || "Admin"} invited ${input.email} as ${input.role}. The invitation expires on ${expiresAt.toLocaleDateString()}.`,
          });
        } catch (e) {
          // Non-critical, don't fail the invitation
        }

        return invitation;
      }),

    /** Resend an invitation (creates a new token, extends expiry) */
    resend: adminProcedure
      .input(z.object({ invitationId: z.number() }))
      .mutation(async ({ input, ctx }) => {
        const existing = await getAllInvitations();
        const invitation = existing.find(i => i.id === input.invitationId);
        if (!invitation) {
          throw new TRPCError({ code: "NOT_FOUND", message: "Invitation not found" });
        }
        if (invitation.status !== "pending" && invitation.status !== "expired") {
          throw new TRPCError({
            code: "BAD_REQUEST",
            message: `Cannot resend a ${invitation.status} invitation`,
          });
        }

        // Revoke old one
        await updateInvitationStatus(invitation.id, "revoked");

        // Create new invitation
        const token = nanoid(32);
        const expiresAt = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000);
        const newInvitation = await createInvitation({
          email: invitation.email,
          role: invitation.role,
          token,
          status: "pending",
          invitedById: ctx.user.id,
          invitedByName: ctx.user.name || "Admin",
          message: invitation.message,
          expiresAt,
        });

        await createAuditLog({
          action: "invitation_resent",
          actorId: ctx.user.id,
          actorName: ctx.user.name || "Admin",
          targetName: invitation.email,
          details: { role: invitation.role, newToken: token },
        });

        return newInvitation;
      }),

    /** Revoke a pending invitation */
    revoke: adminProcedure
      .input(z.object({ invitationId: z.number() }))
      .mutation(async ({ input, ctx }) => {
        const existing = await getAllInvitations();
        const invitation = existing.find(i => i.id === input.invitationId);
        if (!invitation) {
          throw new TRPCError({ code: "NOT_FOUND", message: "Invitation not found" });
        }
        if (invitation.status !== "pending") {
          throw new TRPCError({
            code: "BAD_REQUEST",
            message: `Cannot revoke a ${invitation.status} invitation`,
          });
        }

        const updated = await updateInvitationStatus(invitation.id, "revoked");

        await createAuditLog({
          action: "invitation_revoked",
          actorId: ctx.user.id,
          actorName: ctx.user.name || "Admin",
          targetName: invitation.email,
          details: { role: invitation.role },
        });

        return updated;
      }),

    /** Validate an invitation token (public — used by invite acceptance page) */
    validate: publicProcedure
      .input(z.object({ token: z.string() }))
      .query(async ({ input }) => {
        const invitation = await getInvitationByToken(input.token);
        if (!invitation) {
          return { valid: false, reason: "Invitation not found" } as const;
        }
        if (invitation.status === "revoked") {
          return { valid: false, reason: "This invitation has been revoked" } as const;
        }
        if (invitation.status === "accepted") {
          return { valid: false, reason: "This invitation has already been accepted" } as const;
        }
        if (invitation.status === "expired" || new Date() > invitation.expiresAt) {
          if (invitation.status !== "expired") {
            await updateInvitationStatus(invitation.id, "expired");
          }
          return { valid: false, reason: "This invitation has expired" } as const;
        }
        return {
          valid: true,
          email: invitation.email,
          role: invitation.role,
          invitedBy: invitation.invitedByName,
          message: invitation.message,
          expiresAt: invitation.expiresAt,
        } as const;
      }),

    /** Accept an invitation (requires authenticated user) */
    accept: protectedProcedure
      .input(z.object({ token: z.string() }))
      .mutation(async ({ input, ctx }) => {
        const invitation = await getInvitationByToken(input.token);
        if (!invitation) {
          throw new TRPCError({ code: "NOT_FOUND", message: "Invitation not found" });
        }
        if (invitation.status !== "pending") {
          throw new TRPCError({
            code: "BAD_REQUEST",
            message: `This invitation is ${invitation.status}`,
          });
        }
        if (new Date() > invitation.expiresAt) {
          await updateInvitationStatus(invitation.id, "expired");
          throw new TRPCError({
            code: "BAD_REQUEST",
            message: "This invitation has expired",
          });
        }

        // Update user role to the invited role
        await updateUserRole(ctx.user.id, invitation.role);

        // Mark invitation as accepted
        await updateInvitationStatus(invitation.id, "accepted", ctx.user.id);

        // Audit log
        await createAuditLog({
          action: "invitation_accepted",
          actorId: ctx.user.id,
          actorName: ctx.user.name || ctx.user.email || "User",
          targetName: invitation.email,
          details: { role: invitation.role, invitedBy: invitation.invitedByName },
        });

        return { success: true, role: invitation.role };
      }),
  }),

  /* ─── Audit Logs API (admin only) ─── */
  auditLogs: router({
    list: adminProcedure
      .input(z.object({ limit: z.number().min(1).max(200).optional() }).optional())
      .query(async ({ input }) => {
        return getAuditLogs(input?.limit || 50);
      }),
  }),

  /* ─── Workflow Designer API (admin only) ─── */
  workflow: router({
    save: adminProcedure
      .input(z.object({
        workflowId: z.string(),
        nodes: z.array(z.object({
          workflowId: z.string(),
          nodeId: z.string(),
          type: z.enum(["greeting", "intent", "response", "condition", "escalation", "action", "end", "customer_data", "sales_order", "guardrail"]),
          label: z.string(),
          config: z.any().optional(),
          positionX: z.number(),
          positionY: z.number(),
        })),
        edges: z.array(z.object({
          workflowId: z.string(),
          sourceNodeId: z.string(),
          targetNodeId: z.string(),
          label: z.string().optional(),
          condition: z.any().optional(),
        })),
      }))
      .mutation(async ({ input }) => {
        return saveWorkflow(input.workflowId, input.nodes, input.edges);
      }),

    load: adminProcedure
      .input(z.object({ workflowId: z.string() }))
      .query(async ({ input }) => {
        return getWorkflow(input.workflowId);
      }),

    /** Get AI-suggested nodes for a workflow */
    getSuggestions: adminProcedure
      .input(z.object({
        workflowId: z.string(),
        status: z.enum(["pending", "approved", "declined", "waiting"]).optional(),
      }))
      .query(async ({ input }) => {
        return getWorkflowSuggestions(input.workflowId, input.status);
      }),

    /** Generate AI suggestions from FAQ analysis */
    generateSuggestions: adminProcedure
      .input(z.object({ workflowId: z.string() }))
      .mutation(async ({ input }) => {
        // Analyze conversation messages to find frequently asked questions
        const db = await (await import("./db")).getDb();
        if (!db) throw new Error("Database not available");

        // Get recent visitor messages
        const recentMessages = await db.select({
          content: messages.content,
          sender: messages.sender,
        }).from(messages)
          .where(eq(messages.sender, "visitor"))
          .orderBy(desc(messages.createdAt))
          .limit(200);

        if (recentMessages.length < 3) {
          return { suggestions: [], message: "Not enough conversation data to generate suggestions. Need at least 3 visitor messages." };
        }

        // Use LLM to analyze FAQ patterns and suggest workflow nodes
        const msgSample = recentMessages.map(m => m.content).join("\n---\n");
        const llmResult = await invokeLLM({
          messages: [
            {
              role: "system",
              content: `You are a workflow optimization assistant for Homelegance, a furniture company. Analyze customer messages and identify the top 3-5 most frequently asked question patterns that could benefit from dedicated workflow nodes. For each pattern, suggest a workflow node.

Return a JSON array of suggestions. Each suggestion should have:
- "label": A short descriptive name for the node (e.g., "Shipping ETA Lookup")
- "description": What this node would do
- "nodeType": One of: "response", "action", "condition", "customer_data", "sales_order", "guardrail"
- "faqQuestion": The typical customer question this addresses
- "frequency": Estimated frequency (1-100)
- "config": Configuration object with relevant fields (e.g., {"message": "..."} for response, {"apiEndpoint": "..."} for action, {"blockedTopics": [...]} for guardrail)

Return ONLY the JSON array, no markdown or explanation.`,
            },
            {
              role: "user",
              content: `Analyze these ${recentMessages.length} recent customer messages and suggest workflow nodes:\n\n${msgSample}`,
            },
          ],
          response_format: {
            type: "json_schema",
            json_schema: {
              name: "workflow_suggestions",
              strict: true,
              schema: {
                type: "object",
                properties: {
                  suggestions: {
                    type: "array",
                    items: {
                      type: "object",
                      properties: {
                        label: { type: "string" },
                        description: { type: "string" },
                        nodeType: { type: "string" },
                        faqQuestion: { type: "string" },
                        frequency: { type: "integer" },
                        config: { type: "object", additionalProperties: true },
                      },
                      required: ["label", "description", "nodeType", "faqQuestion", "frequency", "config"],
                      additionalProperties: false,
                    },
                  },
                },
                required: ["suggestions"],
                additionalProperties: false,
              },
            },
          },
        });

        let parsed: any[] = [];
        try {
          const content = llmResult.choices[0]?.message?.content as string;
          const result = JSON.parse(content);
          parsed = result.suggestions || result;
        } catch (e) {
          console.error("[Workflow] Failed to parse LLM suggestions:", e);
          return { suggestions: [], message: "Failed to analyze conversation patterns" };
        }

        // Save suggestions to database
        const toInsert = parsed.map((s: any) => ({
          workflowId: input.workflowId,
          suggestedNodeType: s.nodeType || "response",
          label: s.label,
          description: s.description,
          config: s.config || {},
          faqQuestion: s.faqQuestion,
          frequency: s.frequency || 0,
          status: "pending" as const,
        }));

        await bulkCreateWorkflowSuggestions(toInsert);
        return { suggestions: toInsert, message: `Generated ${toInsert.length} suggestions from ${recentMessages.length} messages` };
      }),

    /** Update suggestion status (approve/decline/wait) */
    reviewSuggestion: adminProcedure
      .input(z.object({
        suggestionId: z.number(),
        status: z.enum(["approved", "declined", "waiting"]),
      }))
      .mutation(async ({ input, ctx }) => {
        return updateWorkflowSuggestionStatus(input.suggestionId, input.status, ctx.user.id);
      }),
  }),

  /* ─── Analytics Router ─── */
  analytics: router({
    track: publicProcedure
      .input(z.object({
        conversationId: z.number().optional(),
        sessionId: z.string().optional(),
        eventType: z.enum([
          "session_start", "message_sent", "message_received",
          "intent_detected", "flow_triggered", "escalated",
          "resolved_by_bot", "resolved_by_agent", "abandoned",
          "button_clicked", "feedback_positive", "feedback_negative",
        ]),
        category: z.string().optional(),
        metadata: z.any().optional(),
      }))
      .mutation(async ({ input }) => {
        const id = await trackAnalyticsEvent(input);
        return { id };
      }),

    summary: agentProcedure
      .input(z.object({
        startDate: z.string().optional(),
        endDate: z.string().optional(),
      }).optional())
      .query(async ({ input }) => {
        const startDate = input?.startDate ? new Date(input.startDate) : undefined;
        const endDate = input?.endDate ? new Date(input.endDate) : undefined;
        return getAnalyticsSummary(startDate, endDate);
      }),

    events: agentProcedure
      .input(z.object({
        eventType: z.string().optional(),
        category: z.string().optional(),
        startDate: z.string().optional(),
        endDate: z.string().optional(),
      }).optional())
      .query(async ({ input }) => {
        return getAnalyticsEvents({
          eventType: input?.eventType,
          category: input?.category,
          startDate: input?.startDate ? new Date(input.startDate) : undefined,
          endDate: input?.endDate ? new Date(input.endDate) : undefined,
        });
      }),
  }),

  /* ─── Data Sources Router (Lyro-inspired) ─── */
  dataSources: router({
    list: adminProcedure.query(async () => {
      return getDataSources();
    }),

    get: adminProcedure
      .input(z.object({ id: z.number() }))
      .query(async ({ input }) => {
        return getDataSourceById(input.id);
      }),

    create: adminProcedure
      .input(z.object({
        name: z.string().min(1),
        type: z.enum(["url", "file", "qa_pair", "api"]),
        config: z.any().optional(),
      }))
      .mutation(async ({ input, ctx }) => {
        const id = await createDataSource({
          name: input.name,
          type: input.type,
          config: input.config || {},
          createdById: ctx.user.id,
        });
        return { id };
      }),

    update: adminProcedure
      .input(z.object({
        id: z.number(),
        name: z.string().optional(),
        status: z.enum(["active", "inactive", "syncing", "error"]).optional(),
        config: z.any().optional(),
        itemCount: z.number().optional(),
      }))
      .mutation(async ({ input }) => {
        const { id, ...updates } = input;
        return updateDataSource(id, updates);
      }),

    delete: adminProcedure
      .input(z.object({ id: z.number() }))
      .mutation(async ({ input }) => {
        await deleteDataSource(input.id);
        return { success: true };
      }),
  }),

  /* ─── API Connections Router (Lyro Actions) ─── */
  apiConnections: router({
    list: adminProcedure.query(async () => {
      return getApiConnections();
    }),

    get: adminProcedure
      .input(z.object({ id: z.number() }))
      .query(async ({ input }) => {
        return getApiConnectionById(input.id);
      }),

    create: adminProcedure
      .input(z.object({
        name: z.string().min(1),
        description: z.string().optional(),
        category: z.string().optional(),
        method: z.enum(["GET", "POST", "PUT", "DELETE"]),
        endpoint: z.string().min(1),
        headers: z.any().optional(),
        inputVariables: z.any().optional(),
        outputVariables: z.any().optional(),
        testPayload: z.any().optional(),
      }))
      .mutation(async ({ input, ctx }) => {
        const id = await createApiConnection({
          ...input,
          createdById: ctx.user.id,
        });
        return { id };
      }),

    update: adminProcedure
      .input(z.object({
        id: z.number(),
        name: z.string().optional(),
        description: z.string().optional(),
        category: z.string().optional(),
        method: z.enum(["GET", "POST", "PUT", "DELETE"]).optional(),
        endpoint: z.string().optional(),
        headers: z.any().optional(),
        inputVariables: z.any().optional(),
        outputVariables: z.any().optional(),
        testPayload: z.any().optional(),
        isActive: z.boolean().optional(),
      }))
      .mutation(async ({ input }) => {
        const { id, ...updates } = input;
        return updateApiConnection(id, updates);
      }),

    delete: adminProcedure
      .input(z.object({ id: z.number() }))
      .mutation(async ({ input }) => {
        await deleteApiConnection(input.id);
        return { success: true };
      }),

    test: adminProcedure
      .input(z.object({ id: z.number() }))
      .mutation(async ({ input }) => {
        const conn = await getApiConnectionById(input.id);
        if (!conn) throw new TRPCError({ code: "NOT_FOUND", message: "API connection not found" });
        try {
          // Simulate a test call (in production, this would make the actual HTTP request)
          await incrementApiConnectionExecution(input.id);
          return {
            success: true,
            message: `Test successful for ${conn.name}`,
            responseTime: Math.floor(Math.random() * 500) + 100, // Simulated
          };
        } catch (err: any) {
          return { success: false, message: err.message, responseTime: 0 };
        }
      }),
  }),

  /* ─── Knowledge Management Router ─── */
  knowledge: router({
    // Q&A Entries
    listEntries: adminProcedure
      .input(z.object({ status: z.string().optional() }).optional())
      .query(async ({ input }) => getKnowledgeEntries(input?.status)),

    getEntry: adminProcedure
      .input(z.object({ id: z.number() }))
      .query(async ({ input }) => getKnowledgeEntryById(input.id)),

    createEntry: adminProcedure
      .input(z.object({
        question: z.string().min(1),
        answer: z.string().min(1),
        category: z.string().optional(),
      }))
      .mutation(async ({ input }) => {
        const id = await createKnowledgeEntry({ ...input, source: "manual" });
        return { id };
      }),

    updateEntry: adminProcedure
      .input(z.object({
        id: z.number(),
        question: z.string().optional(),
        answer: z.string().optional(),
        category: z.string().optional(),
        status: z.enum(["active", "inactive"]).optional(),
      }))
      .mutation(async ({ input }) => {
        const { id, ...data } = input;
        await updateKnowledgeEntry(id, data);
        return { success: true };
      }),

    deleteEntry: adminProcedure
      .input(z.object({ id: z.number() }))
      .mutation(async ({ input }) => {
        await deleteKnowledgeEntry(input.id);
        return { success: true };
      }),

    importEntries: adminProcedure
      .input(z.object({
        entries: z.array(z.object({
          question: z.string().min(1),
          answer: z.string().min(1),
          category: z.string().optional(),
        })),
        source: z.string().default("csv"),
      }))
      .mutation(async ({ input }) => {
        return bulkCreateKnowledgeEntries(input.entries.map(e => ({ ...e, source: input.source })));
      }),

    // Suggestions
    listSuggestions: adminProcedure
      .input(z.object({ status: z.string().optional() }).optional())
      .query(async ({ input }) => getKnowledgeSuggestions(input?.status)),

    promoteSuggestion: adminProcedure
      .input(z.object({
        id: z.number(),
        answer: z.string().min(1),
        category: z.string().optional(),
      }))
      .mutation(async ({ input }) => {
        const entryId = await promoteKnowledgeSuggestion(input.id, input.answer, input.category);
        return { entryId };
      }),

    dismissSuggestion: adminProcedure
      .input(z.object({ id: z.number() }))
      .mutation(async ({ input }) => {
        await dismissKnowledgeSuggestion(input.id);
        return { success: true };
      }),

    // Products
    listProducts: adminProcedure.query(getKnowledgeProducts),

    importProducts: adminProcedure
      .input(z.object({
        products: z.array(z.object({
          model: z.string(),
          description: z.string().optional(),
          categories: z.string().optional(),
          collection: z.string().optional(),
          price: z.string().optional(),
          availability: z.string().optional(),
          features: z.string().optional(),
          dimensions: z.string().optional(),
          imageUrl: z.string().optional(),
        })),
        replaceAll: z.boolean().default(false),
      }))
      .mutation(async ({ input }) => {
        if (input.replaceAll) await deleteAllKnowledgeProducts();
        return bulkCreateKnowledgeProducts(input.products);
      }),
  }),
});

export type AppRouter = typeof appRouter;