drizzle/schema.ts

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;

// Projects table
export const projects = mysqlTable("projects", {
  id: int("id").autoincrement().primaryKey(),
  userId: int("userId").notNull(),
  name: text("name").notNull(),
  description: text("description"),
  mode: mysqlEnum("mode", ["qwen", "deepseek", "loop", "auto"]).default("auto").notNull(),
  contentType: mysqlEnum("contentType", ["code", "exploit", "payload", "information", "strategy"]).default("code").notNull(),
  originalPrompt: text("originalPrompt").notNull(),
  finalOutput: text("finalOutput"),
  finalScore: int("finalScore").default(0),
  status: mysqlEnum("status", ["pending", "in_progress", "completed", "failed"]).default("pending").notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  completedAt: timestamp("completedAt"),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

export type Project = typeof projects.$inferSelect;
export type InsertProject = typeof projects.$inferInsert;

// Iterations table
export const iterations = mysqlTable("iterations", {
  id: int("id").autoincrement().primaryKey(),
  projectId: int("projectId").notNull(),
  version: int("version").notNull(),
  qwenOutput: text("qwenOutput"),
  deepseekAnalysis: text("deepseekAnalysis"),
  score: int("score").default(0),
  passed: int("passed").default(0),
  scorecard: text("scorecard"), // JSON
  feedback: text("feedback"), // JSON array
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

export type Iteration = typeof iterations.$inferSelect;
export type InsertIteration = typeof iterations.$inferInsert;

// Code References table (for file uploads)
export const codeReferences = mysqlTable("codeReferences", {
  id: int("id").autoincrement().primaryKey(),
  userId: int("userId").notNull(),
  filename: varchar("filename", { length: 255 }).notNull(),
  category: mysqlEnum("category", ["library", "exploit", "payload", "example", "utility"]).notNull(),
  description: text("description"),
  content: text("content"),
  s3Key: varchar("s3Key", { length: 500 }),
  s3Url: varchar("s3Url", { length: 1000 }),
  mimeType: varchar("mimeType", { length: 100 }),
  fileSize: int("fileSize"),
  analysis: text("analysis"), // JSON - extracted patterns and info
  tags: text("tags"), // JSON array
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

export type CodeReference = typeof codeReferences.$inferSelect;
export type InsertCodeReference = typeof codeReferences.$inferInsert;

// Statistics table
export const statistics = mysqlTable("statistics", {
  id: int("id").autoincrement().primaryKey(),
  date: varchar("date", { length: 10 }).notNull().unique(),
  totalProjects: int("totalProjects").default(0),
  successfulProjects: int("successfulProjects").default(0),
  failedProjects: int("failedProjects").default(0),
  avgScore: int("avgScore").default(0),
  avgIterations: int("avgIterations").default(0),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

export type Statistic = typeof statistics.$inferSelect;
export type InsertStatistic = typeof statistics.$inferInsert;