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

/**
 * SHARED MySQL schema for ALZAR FZE.
 * This single database is used by BOTH the CRM (writes content) and the public
 * website (reads only approved+published content). Viewable in phpMyAdmin.
 */

/* ---------------------------------------------------------------- Users --- */
/** Admin/staff auth. Shared with the CRM login. */
export const users = mysqlTable("users", {
  id: int("id").autoincrement().primaryKey(),
  email: varchar("email", { length: 320 }).notNull().unique(),
  passwordHash: text("password_hash"),
  name: text("name"),
  loginMethod: varchar("login_method", { length: 64 }).default("password"),
  // 'admin' can approve content; 'editor' can create/submit; 'user' = basic.
  role: mysqlEnum("role", ["user", "editor", "admin"]).default("user").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().onUpdateNow().notNull(),
  lastSignedIn: timestamp("last_signed_in").defaultNow().notNull(),
});
export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;

/* ------------------------------------------------ Contact submissions --- */
export const contactSubmissions = mysqlTable("contact_submissions", {
  id: int("id").autoincrement().primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 320 }).notNull(),
  company: varchar("company", { length: 255 }),
  industry: varchar("industry", { length: 100 }),
  message: text("message").notNull(),
  status: mysqlEnum("status", ["new", "read", "replied", "archived"])
    .default("new")
    .notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().onUpdateNow().notNull(),
});
export type ContactSubmission = typeof contactSubmissions.$inferSelect;
export type InsertContactSubmission = typeof contactSubmissions.$inferInsert;

/* ------------------------------------------ Content (CRM ↔ website) --- */
/**
 * Unified content table for articles, products, principals, job postings.
 * The CRM writes here; the website reads ONLY rows with status='published'.
 * The approval gate lives in this column — enforced at the data layer.
 */
export const contentItems = mysqlTable(
  "content_items",
  {
    id: int("id").autoincrement().primaryKey(),
    type: mysqlEnum("type", ["article", "product", "principal", "job"]).notNull(),
    slug: varchar("slug", { length: 255 }).notNull(),
    // Approval workflow: draft → pending_review → approved → published (or rejected)
    status: mysqlEnum("status", [
      "draft",
      "pending_review",
      "approved",
      "published",
      "rejected",
    ])
      .default("draft")
      .notNull(),
    // Bilingual fields (EN + AR)
    titleEn: varchar("title_en", { length: 512 }).notNull(),
    titleAr: varchar("title_ar", { length: 512 }),
    bodyEn: text("body_en"),
    bodyAr: text("body_ar"),
    excerptEn: varchar("excerpt_en", { length: 1024 }),
    excerptAr: varchar("excerpt_ar", { length: 1024 }),
    // SEO
    metaTitle: varchar("meta_title", { length: 512 }),
    metaDescription: varchar("meta_description", { length: 1024 }),
    ogImage: varchar("og_image", { length: 1024 }),
    canonicalUrl: varchar("canonical_url", { length: 1024 }),
    // Media / structured extras (JSON stored as text for portability)
    heroImage: varchar("hero_image", { length: 1024 }),
    dataJson: text("data_json"), // type-specific fields (product specs, job details…)
    // Workflow bookkeeping
    createdBy: int("created_by"), // users.id
    reviewedBy: int("reviewed_by"), // users.id (the approver)
    reviewNote: text("review_note"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().onUpdateNow().notNull(),
    publishedAt: timestamp("published_at"),
  },
  (t) => ({
    statusTypeIdx: index("idx_content_status_type").on(t.status, t.type),
    slugIdx: index("idx_content_slug").on(t.slug),
  })
);
export type ContentItem = typeof contentItems.$inferSelect;
export type InsertContentItem = typeof contentItems.$inferInsert;

/* --------------------------------------------------- Audit trail --- */
/** Immutable log of every content state change. Protects owner + staff. */
export const contentAudit = mysqlTable("content_audit", {
  id: int("id").autoincrement().primaryKey(),
  contentId: int("content_id").notNull(),
  action: varchar("action", { length: 64 }).notNull(), // created/submitted/approved/rejected/published/edited
  actorId: int("actor_id"), // users.id
  note: text("note"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});
export type ContentAudit = typeof contentAudit.$inferSelect;
export type InsertContentAudit = typeof contentAudit.$inferInsert;
