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, 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,
} from "./db";
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.", resetToken: token };
      }),

    /** 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 your AI furniture assistant. I can help you with:\n\n- **Product Discovery** — Find furniture by style, room, or collection\n- **Order Status** — Check your order details\n- **Dealer Locator** — Find authorized retailers near you\n- **Warranty & Returns** — Get policy information\n\nHow can I help you today?",
        });
        return { sessionId, conversationId: conversation.id };
      }),

    sendMessage: publicProcedure
      .input(z.object({
        sessionId: z.string(),
        content: z.string().min(1).max(2000),
      }))
      .mutation(async ({ input }) => {
        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.",
          };
        }

        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();

            // 1. Single order lookup  —  "SO-12345", "order SO12345", "#SO-99"
            const soMatch = msg.match(/\bSO[-\s]?\d{4,}\b/i);
            if (soMatch) {
              const soId = soMatch[0].replace(/\s/, "-").toUpperCase();
              erpContext = await lookupOrder(soId);

            // 2. "my orders" / "recent orders" — needs customer CID on conversation
            } else if (/\b(my orders?|recent orders?|order history|order status)\b/.test(msgLower)) {
              const cid = (conversation as any).customerId as string | undefined;
              if (cid) {
                erpContext = await lookupOrdersByCustomer(cid, 5);
              }

            // 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]);
              }
            }

            // 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] });
              }
            }

            // 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 });
              }
            }

            // 6. Customer / dealer lookup
            if (!erpContext && /\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() });
              }
            }
          } catch (erpErr) {
            // ERP errors must never break the chat — just log and continue without context
            console.error("[ERP] intent lookup error:", erpErr);
          }
        }

        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,
          });

          return { reply: botReply, status: conversation.status };
        } 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);
      }),

    /** 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 };
        }
      }),
  }),
});

export type AppRouter = typeof appRouter;