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

/**
 * Core user table backing auth flow.
 */
// Core user table backing auth flow.
export const users = mysqlTable("users", {
  id: int("id").autoincrement().primaryKey(),
  username: varchar("username", { length: 64 }).notNull().unique(),
  password: varchar("password", { length: 255 }).notNull(), // Hashed password
  name: text("name"),
  email: varchar("email", { length: 320 }),
  role: mysqlEnum("role", ["user", "admin"]).default("user").notNull(),
  apiKeyHash: varchar("apiKeyHash", { length: 64 }).unique(),
  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;

/**
 * Portfolio projects table
 */
export const projects = mysqlTable("projects", {
  id: int("id").autoincrement().primaryKey(),
  slug: varchar("slug", { length: 255 }).notNull().unique(),
  title: varchar("title", { length: 255 }).notNull(),
  shortDescription: text("shortDescription").notNull(),
  fullDescription: text("fullDescription").notNull(),
  category: mysqlEnum("category", ["vitrine", "ecommerce", "webapp"]).notNull(),
  sector: varchar("sector", { length: 100 }).notNull(),
  clientName: varchar("clientName", { length: 255 }),
  clientLogo: varchar("clientLogo", { length: 500 }),
  mainImage: varchar("mainImage", { length: 500 }).notNull(),
  images: text("images"), // JSON array of image URLs
  technologies: text("technologies"), // JSON array of tech stack
  siteUrl: varchar("siteUrl", { length: 500 }),
  completionDate: timestamp("completionDate"),
  testimonial: text("testimonial"),
  metrics: text("metrics"), // JSON object with performance metrics
  featured: boolean("featured").default(false),
  published: boolean("published").default(true),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

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

/**
 * Blog articles table
 */
export const articles = mysqlTable("articles", {
  id: int("id").autoincrement().primaryKey(),
  slug: varchar("slug", { length: 255 }).notNull().unique(),
  title: varchar("title", { length: 255 }).notNull(),
  excerpt: text("excerpt").notNull(),
  content: text("content").notNull(), // Markdown or HTML content
  heroImage: varchar("heroImage", { length: 500 }).notNull(),
  category: varchar("category", { length: 100 }).notNull(),
  tags: text("tags"), // JSON array of tags
  author: varchar("author", { length: 100 }).default("Leifo").notNull(),
  readingTime: int("readingTime").default(5), // in minutes
  published: boolean("published").default(true),
  publishedAt: timestamp("publishedAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
  seoTitle: varchar("seoTitle", { length: 255 }),
  seoDescription: varchar("seoDescription", { length: 300 }),
  seoKeywords: text("seoKeywords"), // JSON array
});

export type Article = typeof articles.$inferSelect;
export type InsertArticle = typeof articles.$inferInsert;

/**
 * Contact form submissions table
 */
export const contacts = mysqlTable("contacts", {
  id: int("id").autoincrement().primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 320 }).notNull(),
  phone: varchar("phone", { length: 20 }),
  projectType: mysqlEnum("projectType", ["vitrine", "ecommerce", "webapp", "autre"]).notNull(),
  message: text("message").notNull(),
  newsletter: boolean("newsletter").default(false),
  status: mysqlEnum("status", ["new", "read", "replied", "archived"]).default("new").notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

export type Contact = typeof contacts.$inferSelect;
export type InsertContact = typeof contacts.$inferInsert;

/**
 * Article views tracking table
 */
export const articleViews = mysqlTable("article_views", {
  id: int("id").autoincrement().primaryKey(),
  articleId: int("articleId").notNull(),
  viewedAt: timestamp("viewedAt").defaultNow().notNull(),
  userAgent: varchar("userAgent", { length: 500 }),
  ipHash: varchar("ipHash", { length: 64 }), // Hashed IP for privacy
  referrer: varchar("referrer", { length: 500 }),
});

export type ArticleView = typeof articleViews.$inferSelect;
export type InsertArticleView = typeof articleViews.$inferInsert;