chatbot/drizzle/schema.ts

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

// ── Schema ───────────────────────────────────────────────────────────────────
// All chatbot objects live in the "chatbot" PostgreSQL schema.
// DBA setup (run once):
//   CREATE SCHEMA chatbot;
//   GRANT ALL ON SCHEMA chatbot TO chatbot_user;
export const chatbotSchema = pgSchema("chatbot");

// ── Enums (scoped to chatbot schema) ─────────────────────────────────────────

export const roleEnum               = chatbotSchema.enum("role",                ["user", "agent", "admin"]);
export const conversationStatusEnum = chatbotSchema.enum("conversation_status", ["active", "escalated", "resolved", "closed"]);
export const senderEnum             = chatbotSchema.enum("sender",              ["visitor", "bot", "agent"]);
export const workflowNodeTypeEnum   = chatbotSchema.enum("workflow_node_type",  [
  "greeting", "intent", "response", "condition", "escalation",
  "action", "end", "customer_data", "sales_order", "guardrail",
]);
export const inviteStatusEnum       = chatbotSchema.enum("invite_status",       ["pending", "accepted", "expired", "revoked"]);
export const suggestionStatusEnum   = chatbotSchema.enum("suggestion_status",   ["pending", "approved", "declined", "waiting"]);
export const eventTypeEnum          = chatbotSchema.enum("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         = chatbotSchema.enum("source_type",         ["url", "file", "qa_pair", "api"]);
export const sourceStatusEnum       = chatbotSchema.enum("source_status",       ["active", "inactive", "syncing", "error"]);
export const httpMethodEnum         = chatbotSchema.enum("http_method",         ["GET", "POST", "PUT", "DELETE"]);

// ── Tables (all inside chatbot schema) ───────────────────────────────────────

/**
 * Core user table backing auth flow.
 */
export const users = chatbotSchema.table("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(),
  erpContactCid:  varchar("erpContactCid", { length: 64 }),   // links user to ERP ContactID for permission filtering
  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 = chatbotSchema.table("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 = chatbotSchema.table("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 = chatbotSchema.table("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 = chatbotSchema.table("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 = chatbotSchema.table("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 = chatbotSchema.table("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 = chatbotSchema.table("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 = chatbotSchema.table("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 = chatbotSchema.table("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 = chatbotSchema.table("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 = chatbotSchema.table("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;

/**
 * Knowledge base Q&A entries — first-line answer engine before the LLM.
 */
export const knowledgeEntries = chatbotSchema.table("knowledge_entries", {
  id:          serial("id").primaryKey(),
  question:    text("question").notNull(),
  answer:      text("answer").notNull(),
  category:    varchar("category", { length: 100 }),
  source:      varchar("source", { length: 50 }).default("manual"), // "manual"|"csv"|"url"
  useCount:    integer("use_count").default(0).notNull(),
  status:      varchar("status", { length: 20 }).default("active").notNull(), // "active"|"inactive"
  createdAt:   timestamp("created_at").defaultNow().notNull(),
  updatedAt:   timestamp("updated_at").defaultNow().notNull(),
});

/**
 * Auto-captured unanswered questions from Ellie conversations.
 */
export const knowledgeSuggestions = chatbotSchema.table("knowledge_suggestions", {
  id:              serial("id").primaryKey(),
  question:        text("question").notNull(),
  occurrenceCount: integer("occurrence_count").default(1).notNull(),
  lastSeen:        timestamp("last_seen").defaultNow().notNull(),
  status:          varchar("status", { length: 20 }).default("pending").notNull(), // "pending"|"promoted"|"dismissed"
  promotedToId:    integer("promoted_to_id"),
  createdAt:       timestamp("created_at").defaultNow().notNull(),
});

/**
 * Product catalog imported from Excel/CSV.
 */
export const knowledgeProducts = chatbotSchema.table("knowledge_products", {
  id:           serial("id").primaryKey(),
  model:        varchar("model", { length: 100 }).notNull(),
  description:  text("description"),
  categories:   text("categories"),
  collection:   varchar("collection", { length: 100 }),
  price:        varchar("price", { length: 50 }),
  availability: varchar("availability", { length: 50 }),
  features:     text("features"),
  dimensions:   text("dimensions"),
  imageUrl:     text("image_url"),
  status:       varchar("status", { length: 20 }).default("active").notNull(),
  createdAt:    timestamp("created_at").defaultNow().notNull(),
});

export type KnowledgeEntry            = typeof knowledgeEntries.$inferSelect;
export type InsertKnowledgeEntry      = typeof knowledgeEntries.$inferInsert;
export type KnowledgeSuggestion       = typeof knowledgeSuggestions.$inferSelect;
export type InsertKnowledgeSuggestion = typeof knowledgeSuggestions.$inferInsert;
export type KnowledgeProduct          = typeof knowledgeProducts.$inferSelect;
export type InsertKnowledgeProduct    = typeof knowledgeProducts.$inferInsert;