chatbot/drizzle/schema.ts

import {
  boolean,
  integer,
  jsonb,
  pgEnum,
  pgTable,
  serial,
  text,
  timestamp,
  varchar,
} from "drizzle-orm/pg-core";

// ── Enum definitions ─────────────────────────────────────────────────────────

export const roleEnum = pgEnum("role", ["user", "agent", "admin"]);
export const conversationStatusEnum = pgEnum("conversation_status", ["active", "escalated", "resolved", "closed"]);
export const senderEnum = pgEnum("sender", ["visitor", "bot", "agent"]);
export const workflowNodeTypeEnum = pgEnum("workflow_node_type", [
  "greeting", "intent", "response", "condition", "escalation",
  "action", "end", "customer_data", "sales_order", "guardrail",
]);
export const inviteStatusEnum = pgEnum("invite_status", ["pending", "accepted", "expired", "revoked"]);
export const suggestionStatusEnum = pgEnum("suggestion_status", ["pending", "approved", "declined", "waiting"]);
export const eventTypeEnum = pgEnum("event_type", [
  "session_start", "message_sent", "message_received",
  "intent_detected", "flow_triggered", "escalated",
  "resolved_by_bot", "resolved_by_agent", "abandoned",
  "button_clicked", "feedback_positive", "feedback_negative",
]);
export const sourceTypeEnum = pgEnum("source_type", ["url", "file", "qa_pair", "api"]);
export const sourceStatusEnum = pgEnum("source_status", ["active", "inactive", "syncing", "error"]);
export const httpMethodEnum = pgEnum("http_method", ["GET", "POST", "PUT", "DELETE"]);

// ── Tables ───────────────────────────────────────────────────────────────────

/**
 * Core user table backing auth flow.
 */
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  openId: varchar("openId", { length: 64 }).notNull().unique(),
  name: text("name"),
  email: varchar("email", { length: 320 }),
  loginMethod: varchar("loginMethod", { length: 64 }),
  role: roleEnum("role").default("user").notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().notNull(),
  lastSignedIn: timestamp("lastSignedIn").defaultNow().notNull(),
  passwordHash: varchar("passwordHash", { length: 255 }),
});

export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;

/**
 * Password reset tokens — for forgot password flow.
 */
export const passwordResetTokens = pgTable("password_reset_tokens", {
  id: serial("id").primaryKey(),
  userId: integer("userId").notNull(),
  token: varchar("token", { length: 64 }).notNull().unique(),
  expiresAt: timestamp("expiresAt").notNull(),
  usedAt: timestamp("usedAt"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

export type PasswordResetToken = typeof passwordResetTokens.$inferSelect;
export type InsertPasswordResetToken = typeof passwordResetTokens.$inferInsert;

/**
 * Chat conversations — each visitor session creates one conversation.
 */
export const conversations = pgTable("conversations", {
  id: serial("id").primaryKey(),
  sessionId: varchar("sessionId", { length: 64 }).notNull().unique(),
  visitorName: varchar("visitorName", { length: 255 }),
  visitorEmail: varchar("visitorEmail", { length: 320 }),
  customerId: varchar("customerId", { length: 64 }),
  salesRep: varchar("salesRep", { length: 255 }),
  status: conversationStatusEnum("status").default("active").notNull(),
  assignedAgentId: integer("assignedAgentId"),
  metadata: jsonb("metadata"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().notNull(),
});

export type Conversation = typeof conversations.$inferSelect;
export type InsertConversation = typeof conversations.$inferInsert;

/**
 * Chat messages — each message belongs to a conversation.
 */
export const messages = pgTable("messages", {
  id: serial("id").primaryKey(),
  conversationId: integer("conversationId").notNull(),
  sender: senderEnum("sender").notNull(),
  content: text("content").notNull(),
  metadata: jsonb("metadata"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

export type Message = typeof messages.$inferSelect;
export type InsertMessage = typeof messages.$inferInsert;

/**
 * Workflow nodes — each node in the chatbot conversation flow.
 */
export const workflowNodes = pgTable("workflow_nodes", {
  id: serial("id").primaryKey(),
  workflowId: varchar("workflowId", { length: 64 }).notNull(),
  nodeId: varchar("nodeId", { length: 64 }).notNull(),
  type: workflowNodeTypeEnum("type").notNull(),
  label: varchar("label", { length: 255 }).notNull(),
  config: jsonb("config"),
  positionX: integer("positionX").default(0).notNull(),
  positionY: integer("positionY").default(0).notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().notNull(),
});

export type WorkflowNode = typeof workflowNodes.$inferSelect;
export type InsertWorkflowNode = typeof workflowNodes.$inferInsert;

/**
 * Workflow edges — connections between nodes.
 */
export const workflowEdges = pgTable("workflow_edges", {
  id: serial("id").primaryKey(),
  workflowId: varchar("workflowId", { length: 64 }).notNull(),
  sourceNodeId: varchar("sourceNodeId", { length: 64 }).notNull(),
  targetNodeId: varchar("targetNodeId", { length: 64 }).notNull(),
  label: varchar("label", { length: 255 }),
  condition: jsonb("condition"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

export type WorkflowEdge = typeof workflowEdges.$inferSelect;
export type InsertWorkflowEdge = typeof workflowEdges.$inferInsert;

/**
 * Invitations — track sent invitations and their acceptance status.
 */
export const invitations = pgTable("invitations", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 320 }).notNull(),
  role: roleEnum("role").default("agent").notNull(),
  token: varchar("token", { length: 64 }).notNull().unique(),
  status: inviteStatusEnum("status").default("pending").notNull(),
  invitedById: integer("invitedById").notNull(),
  invitedByName: varchar("invitedByName", { length: 255 }),
  acceptedByUserId: integer("acceptedByUserId"),
  message: text("message"),
  expiresAt: timestamp("expiresAt").notNull(),
  acceptedAt: timestamp("acceptedAt"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().notNull(),
});

export type Invitation = typeof invitations.$inferSelect;
export type InsertInvitation = typeof invitations.$inferInsert;

/**
 * Audit logs — track important user management actions.
 */
export const auditLogs = pgTable("audit_logs", {
  id: serial("id").primaryKey(),
  action: varchar("action", { length: 64 }).notNull(),
  actorId: integer("actorId").notNull(),
  actorName: varchar("actorName", { length: 255 }),
  targetId: integer("targetId"),
  targetName: varchar("targetName", { length: 255 }),
  details: jsonb("details"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

export type AuditLog = typeof auditLogs.$inferSelect;
export type InsertAuditLog = typeof auditLogs.$inferInsert;

/**
 * AI workflow suggestions — AI-recommended nodes based on FAQ analysis.
 */
export const workflowSuggestions = pgTable("workflow_suggestions", {
  id: serial("id").primaryKey(),
  workflowId: varchar("workflowId", { length: 64 }).notNull(),
  suggestedNodeType: varchar("suggestedNodeType", { length: 64 }).notNull(),
  label: varchar("label", { length: 255 }).notNull(),
  description: text("description"),
  config: jsonb("config"),
  faqQuestion: text("faqQuestion"),
  frequency: integer("frequency").default(0).notNull(),
  status: suggestionStatusEnum("status").default("pending").notNull(),
  reviewedById: integer("reviewedById"),
  reviewedAt: timestamp("reviewedAt"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().notNull(),
});

export type WorkflowSuggestion = typeof workflowSuggestions.$inferSelect;
export type InsertWorkflowSuggestion = typeof workflowSuggestions.$inferInsert;

/**
 * Analytics events — track chatbot interactions for resolution rate and metrics.
 */
export const analyticsEvents = pgTable("analytics_events", {
  id: serial("id").primaryKey(),
  conversationId: integer("conversationId"),
  sessionId: varchar("sessionId", { length: 64 }),
  eventType: eventTypeEnum("eventType").notNull(),
  category: varchar("category", { length: 64 }),
  metadata: jsonb("metadata"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

export type AnalyticsEvent = typeof analyticsEvents.$inferSelect;
export type InsertAnalyticsEvent = typeof analyticsEvents.$inferInsert;

/**
 * Data sources — knowledge base for the AI agent.
 */
export const dataSources = pgTable("data_sources", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  type: sourceTypeEnum("type").notNull(),
  status: sourceStatusEnum("status").default("active").notNull(),
  config: jsonb("config"),
  lastSyncedAt: timestamp("lastSyncedAt"),
  itemCount: integer("itemCount").default(0).notNull(),
  createdById: integer("createdById"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().notNull(),
});

export type DataSource = typeof dataSources.$inferSelect;
export type InsertDataSource = typeof dataSources.$inferInsert;

/**
 * API connections — external API endpoints for Actions.
 */
export const apiConnections = pgTable("api_connections", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  category: varchar("category", { length: 64 }),
  method: httpMethodEnum("method").default("GET").notNull(),
  endpoint: varchar("endpoint", { length: 1024 }).notNull(),
  headers: jsonb("headers"),
  inputVariables: jsonb("inputVariables"),
  outputVariables: jsonb("outputVariables"),
  testPayload: jsonb("testPayload"),
  isActive: boolean("isActive").default(true).notNull(),
  executionCount: integer("executionCount").default(0).notNull(),
  lastExecutedAt: timestamp("lastExecutedAt"),
  createdById: integer("createdById"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().notNull(),
});

export type ApiConnection = typeof apiConnections.$inferSelect;
export type InsertApiConnection = typeof apiConnections.$inferInsert;