import { relations } from "drizzle-orm";
import { pgTable, pgEnum, text, timestamp, integer, boolean, doublePrecision, primaryKey, uniqueIndex, index } from "drizzle-orm/pg-core";
import type { AdapterAccountType } from "next-auth/adapters";

const id = () => text("id").primaryKey().$defaultFn(() => crypto.randomUUID());
const now = (name: string) => timestamp(name, { mode: "date" }).defaultNow().notNull();

// ---------- Auth (Auth.js Drizzle adapter shape) ----------

export const roleEnum = pgEnum("role", ["STAFF", "EDITOR", "ADMIN"]);

export const users = pgTable("user", {
  id: id(),
  name: text("name"),
  email: text("email").notNull().unique(),
  emailVerified: timestamp("emailVerified", { mode: "date" }),
  image: text("image"),
  role: roleEnum("role").default("STAFF").notNull(),
  createdAt: now("createdAt"),
});

export const accounts = pgTable("account", {
  userId: text("userId").notNull().references(() => users.id, { onDelete: "cascade" }),
  type: text("type").$type<AdapterAccountType>().notNull(),
  provider: text("provider").notNull(),
  providerAccountId: text("providerAccountId").notNull(),
  refresh_token: text("refresh_token"),
  access_token: text("access_token"),
  expires_at: integer("expires_at"),
  token_type: text("token_type"),
  scope: text("scope"),
  id_token: text("id_token"),
  session_state: text("session_state"),
}, (t) => [primaryKey({ columns: [t.provider, t.providerAccountId] })]);

export const sessions = pgTable("session", {
  sessionToken: text("sessionToken").primaryKey(),
  userId: text("userId").notNull().references(() => users.id, { onDelete: "cascade" }),
  expires: timestamp("expires", { mode: "date" }).notNull(),
});

export const verificationTokens = pgTable("verificationToken", {
  identifier: text("identifier").notNull(),
  token: text("token").notNull(),
  expires: timestamp("expires", { mode: "date" }).notNull(),
}, (t) => [primaryKey({ columns: [t.identifier, t.token] })]);

// ---------- People ----------

export const officeEnum = pgEnum("office", ["DUBLIN", "BOSTON", "VANCOUVER", "REMOTE"]);
export type Office = (typeof officeEnum.enumValues)[number];

export const people = pgTable("person", {
  id: id(),
  slug: text("slug").notNull().unique(),
  userId: text("user_id").unique().references(() => users.id),
  workamajigId: text("workamajig_id").unique(),
  firstName: text("first_name").notNull(),
  lastName: text("last_name").notNull(),
  email: text("email").notNull().unique(),
  title: text("title").notNull(),
  department: text("department").notNull(),
  office: officeEnum("office").default("DUBLIN").notNull(),
  photoUrl: text("photo_url"),
  linkedinUrl: text("linkedin_url"),
  bio: text("bio"),
  interests: text("interests").array().default([]).notNull(),
  startedAt: timestamp("started_at", { mode: "date" }),
  isActive: boolean("is_active").default(true).notNull(),
  createdAt: now("created_at"),
  updatedAt: now("updated_at"),
}, (t) => [index("person_department_idx").on(t.department), index("person_office_idx").on(t.office)]);

export const skills = pgTable("skill", {
  id: id(),
  name: text("name").notNull().unique(),
  category: text("category").notNull(), // craft | tool | vertical | language
});

export const personSkills = pgTable("person_skill", {
  personId: text("person_id").notNull().references(() => people.id, { onDelete: "cascade" }),
  skillId: text("skill_id").notNull().references(() => skills.id, { onDelete: "cascade" }),
  level: integer("level").default(3).notNull(),
}, (t) => [primaryKey({ columns: [t.personId, t.skillId] })]);

// ---------- Projects (mirrored from Workamajig) ----------

export const clients = pgTable("client", {
  id: id(),
  workamajigId: text("workamajig_id").unique(),
  name: text("name").notNull().unique(),
  vertical: text("vertical"),
});

export const projects = pgTable("project", {
  id: id(),
  workamajigId: text("workamajig_id").unique(),
  number: text("number"),
  name: text("name").notNull(),
  description: text("description"),
  status: text("status"),
  startDate: timestamp("start_date", { mode: "date" }),
  endDate: timestamp("end_date", { mode: "date" }),
  clientId: text("client_id").references(() => clients.id),
  services: text("services").array().default([]).notNull(),
  syncedAt: now("synced_at"),
}, (t) => [index("project_client_idx").on(t.clientId), index("project_status_idx").on(t.status)]);

export const projectAssignments = pgTable("project_assignment", {
  id: id(),
  personId: text("person_id").notNull().references(() => people.id, { onDelete: "cascade" }),
  projectId: text("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
  role: text("role"),
  hours: doublePrecision("hours").default(0).notNull(),
  firstSeen: timestamp("first_seen", { mode: "date" }),
  lastSeen: timestamp("last_seen", { mode: "date" }),
}, (t) => [uniqueIndex("assignment_person_project_idx").on(t.personId, t.projectId)]);

// ---------- Content ----------

export const caseStudies = pgTable("case_study", {
  id: id(),
  slug: text("slug").notNull().unique(),
  title: text("title").notNull(),
  clientName: text("client_name").notNull(),
  vertical: text("vertical"),
  summary: text("summary").notNull(),
  challenge: text("challenge"),
  approach: text("approach"),
  results: text("results"),
  heroUrl: text("hero_url"),
  videoUrl: text("video_url"),
  tags: text("tags").array().default([]).notNull(),
  year: integer("year"),
  projectId: text("project_id").unique().references(() => projects.id),
  published: boolean("published").default(true).notNull(),
  createdAt: now("created_at"),
});

export const showcaseDisciplineEnum = pgEnum("showcase_discipline", ["UX", "CREATIVE", "DEVELOPMENT", "ANALYTICS", "BRAND"]);
export const showcaseKindEnum = pgEnum("showcase_kind", ["EXPERIMENT", "GAME", "IDEA", "PROTOTYPE"]);
export type ShowcaseDiscipline = (typeof showcaseDisciplineEnum.enumValues)[number];
export type ShowcaseKind = (typeof showcaseKindEnum.enumValues)[number];

export const showcaseItems = pgTable("showcase_item", {
  id: id(),
  slug: text("slug").notNull().unique(),
  title: text("title").notNull(),
  blurb: text("blurb").notNull(),
  body: text("body"),
  discipline: showcaseDisciplineEnum("discipline").notNull(),
  kind: showcaseKindEnum("kind").notNull(),
  url: text("url"),
  embedUrl: text("embed_url"),
  imageUrl: text("image_url"),
  authorId: text("author_id").references(() => people.id),
  reactions: integer("reactions").default(0).notNull(),
  createdAt: now("created_at"),
});

export const educationResources = pgTable("education_resource", {
  id: id(),
  title: text("title").notNull(),
  description: text("description"),
  url: text("url").notNull(),
  kind: text("kind").notNull(), // library | ebook | video | course | article | podcast
  specialty: text("specialty").notNull(),
  source: text("source").default("Google Drive").notNull(),
  featured: boolean("featured").default(false).notNull(),
  createdAt: now("created_at"),
}, (t) => [index("education_specialty_idx").on(t.specialty)]);

export const historyEvents = pgTable("history_event", {
  id: id(),
  date: timestamp("date", { mode: "date" }).notNull(),
  title: text("title").notNull(),
  body: text("body"),
  mediaUrl: text("media_url"),
  mediaKind: text("media_kind"), // image | video
  era: text("era"),
  sortOrder: integer("sort_order").default(0).notNull(),
});

// ---------- Relations ----------

export const usersRelations = relations(users, ({ one }) => ({ person: one(people, { fields: [users.id], references: [people.userId] }) }));
export const peopleRelations = relations(people, ({ one, many }) => ({
  user: one(users, { fields: [people.userId], references: [users.id] }),
  skills: many(personSkills),
  assignments: many(projectAssignments),
  showcaseItems: many(showcaseItems),
}));
export const skillsRelations = relations(skills, ({ many }) => ({ people: many(personSkills) }));
export const personSkillsRelations = relations(personSkills, ({ one }) => ({
  person: one(people, { fields: [personSkills.personId], references: [people.id] }),
  skill: one(skills, { fields: [personSkills.skillId], references: [skills.id] }),
}));
export const clientsRelations = relations(clients, ({ many }) => ({ projects: many(projects) }));
export const projectsRelations = relations(projects, ({ one, many }) => ({
  client: one(clients, { fields: [projects.clientId], references: [clients.id] }),
  assignments: many(projectAssignments),
  caseStudy: one(caseStudies, { fields: [projects.id], references: [caseStudies.projectId] }),
}));
export const projectAssignmentsRelations = relations(projectAssignments, ({ one }) => ({
  person: one(people, { fields: [projectAssignments.personId], references: [people.id] }),
  project: one(projects, { fields: [projectAssignments.projectId], references: [projects.id] }),
}));
export const caseStudiesRelations = relations(caseStudies, ({ one }) => ({ project: one(projects, { fields: [caseStudies.projectId], references: [projects.id] }) }));
export const showcaseItemsRelations = relations(showcaseItems, ({ one }) => ({ author: one(people, { fields: [showcaseItems.authorId], references: [people.id] }) }));

export type Person = typeof people.$inferSelect;
export type Skill = typeof skills.$inferSelect;
export type Project = typeof projects.$inferSelect;
export type CaseStudy = typeof caseStudies.$inferSelect;
export type ShowcaseItem = typeof showcaseItems.$inferSelect;
export type HistoryEvent = typeof historyEvents.$inferSelect;