import { int, mysqlEnum, mysqlTable, text, timestamp, varchar } from "drizzle-orm/mysql-core";

/**
 * Core user table backing auth flow.
 * Extend this file with additional tables as your product grows.
 * Columns use camelCase to match both database fields and generated types.
 */
export const users = mysqlTable("users", {
  /**
   * Surrogate primary key. Auto-incremented numeric value managed by the database.
   * Use this for relations between tables.
   */
  id: int("id").autoincrement().primaryKey(),
  /** Manus OAuth identifier (openId) returned from the OAuth callback. Unique per user. */
  openId: varchar("openId", { length: 64 }).notNull().unique(),
  name: text("name"),
  email: varchar("email", { length: 320 }),
  loginMethod: varchar("loginMethod", { length: 64 }),
  role: mysqlEnum("role", ["user", "admin"]).default("user").notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
  lastSignedIn: timestamp("lastSignedIn").defaultNow().notNull(),
});

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

/**
 * Khayo's personality directive - stored and loaded per session
 * This contains the system prompt instructions that define Khayo's behavior
 */
export const khayoPersonality = mysqlTable("khayo_personality", {
  id: int("id").autoincrement().primaryKey(),
  /** The current personality/behavior directive for Khayo */
  directive: text("directive").notNull(),
  /** Version number for tracking personality evolution */
  version: int("version").default(1).notNull(),
  /** When this personality was last updated */
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
  /** When this personality was created */
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

export type KhayoPersonality = typeof khayoPersonality.$inferSelect;
export type InsertKhayoPersonality = typeof khayoPersonality.$inferInsert;

/**
 * Conversation sessions - each user can have multiple conversations with Khayo
 */
export const conversations = mysqlTable("conversations", {
  id: int("id").autoincrement().primaryKey(),
  /** Reference to the user who owns this conversation */
  userId: int("userId").notNull().references(() => users.id),
  /** Title/name of the conversation */
  title: varchar("title", { length: 255 }).default("New Chat").notNull(),
  /** When the conversation was created */
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  /** When the conversation was last updated */
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

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

/**
 * Messages within a conversation
 * Stores both user messages and Khayo's responses
 */
export const messages = mysqlTable("messages", {
  id: int("id").autoincrement().primaryKey(),
  /** Reference to the conversation this message belongs to */
  conversationId: int("conversationId").notNull().references(() => conversations.id, { onDelete: "cascade" }),
  /** Role: 'user' for user messages, 'assistant' for Khayo's responses */
  role: mysqlEnum("role", ["user", "assistant"]).notNull(),
  /** The actual message content */
  content: text("content").notNull(),
  /** When this message was created */
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

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

/**
 * Self-update directives - tracks when Edson updates Khayo's personality
 * Stored separately from regular messages for audit trail
 */
export const selfUpdates = mysqlTable("self_updates", {
  id: int("id").autoincrement().primaryKey(),
  /** The update directive provided by Edson */
  directive: text("directive").notNull(),
  /** The previous personality version before this update */
  previousVersion: int("previousVersion").notNull(),
  /** The new personality version after this update */
  newVersion: int("newVersion").notNull(),
  /** When this self-update was applied */
  appliedAt: timestamp("appliedAt").defaultNow().notNull(),
});

export type SelfUpdate = typeof selfUpdates.$inferSelect;
export type InsertSelfUpdate = typeof selfUpdates.$inferInsert;