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;

/**
 * Templates table - stores protocol templates
 */
export const templates = mysqlTable("templates", {
  id: int("id").autoincrement().primaryKey(),
  userId: int("userId").notNull(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  content: text("content").notNull(), // HTML template content
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

export type Template = typeof templates.$inferSelect;
export type InsertTemplate = typeof templates.$inferInsert;

/**
 * Template fields - stores field definitions for templates
 */
export const templateFields = mysqlTable("template_fields", {
  id: int("id").autoincrement().primaryKey(),
  templateId: int("templateId").notNull(),
  fieldName: varchar("fieldName", { length: 255 }).notNull(),
  fieldLabel: varchar("fieldLabel", { length: 255 }).notNull(),
  fieldType: mysqlEnum("fieldType", ["text", "textarea", "date", "number", "select", "checkbox"]).notNull(),
  fieldOptions: text("fieldOptions"), // JSON array for select/checkbox options
  isRequired: int("isRequired").default(0).notNull(),
  order: int("order").notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

export type TemplateField = typeof templateFields.$inferSelect;
export type InsertTemplateField = typeof templateFields.$inferInsert;

/**
 * Protocols table - stores issued protocols
 */
export const protocols = mysqlTable("protocols", {
  id: int("id").autoincrement().primaryKey(),
  userId: int("userId").notNull(),
  templateId: int("templateId").notNull(),
  protocolNumber: varchar("protocolNumber", { length: 255 }).notNull(),
  status: mysqlEnum("status", ["draft", "issued", "archived"]).default("draft").notNull(),
  issuedAt: timestamp("issuedAt"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

export type Protocol = typeof protocols.$inferSelect;
export type InsertProtocol = typeof protocols.$inferInsert;

/**
 * Protocol values - stores filled field values for protocols
 */
export const protocolValues = mysqlTable("protocol_values", {
  id: int("id").autoincrement().primaryKey(),
  protocolId: int("protocolId").notNull(),
  fieldId: int("fieldId").notNull(),
  value: text("value"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

export type ProtocolValue = typeof protocolValues.$inferSelect;
export type InsertProtocolValue = typeof protocolValues.$inferInsert;