import {
  pgTable, text, timestamp, uuid, primaryKey, integer, jsonb, pgEnum, boolean,
  uniqueIndex, index,
} from "drizzle-orm/pg-core";

export const userStatusEnum = pgEnum("user_status", ["pending", "active", "rejected", "suspended"]);
export const userRoleEnum = pgEnum("user_role", ["user", "admin"]);
export const applicationStatusEnum = pgEnum("application_status", ["pending", "approved", "rejected", "withdrawn"]);

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  emailVerified: timestamp("email_verified", { mode: "date" }),
  passwordHash: text("password_hash"),
  name: text("name").notNull(),
  country: text("country").notNull(),
  institution: text("institution").notNull(),
  professionalRole: text("professional_role").notNull(),
  orcidUrl: text("orcid_url"),
  linkedinUrl: text("linkedin_url"),
  status: userStatusEnum("status").notNull().default("pending"),
  role: userRoleEnum("role").notNull().default("user"),
  createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
  approvedAt: timestamp("approved_at", { mode: "date" }),
  approvedBy: uuid("approved_by"),
  image: text("image"),
  /**
   * Incremented whenever the user (or an admin) wants to invalidate every
   * outstanding session — e.g. "sign out all devices" or a stolen-device
   * scenario. The JWT callback rejects any token whose embedded version is
   * lower than the current one.
   */
  tokenVersion: integer("token_version").notNull().default(0),
  // --- optional profile fields, populated on /join or /account/profile ---
  /** Path to the member's uploaded avatar (null uses initials placeholder). */
  avatarPath: text("avatar_path"),
  /** Short bio rendered on the member directory + profile page. */
  bio: text("bio").notNull().default(""),
  /** e.g. "Shoulder", "Hip & knee", "Paediatric orthopaedics". */
  specialty: text("specialty").notNull().default(""),
  /** Years of post-qualification experience. Null when unstated. */
  yearsExperience: integer("years_experience"),
  websiteUrl: text("website_url"),
  twitterUrl: text("twitter_url"),
  researchInterests: jsonb("research_interests")
    .$type<string[]>()
    .notNull()
    .default([]),
  /** Members can opt out of the internal directory without deleting their account. */
  visibleInDirectory: boolean("visible_in_directory").notNull().default(true),
});

export const applications = pgTable("applications", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  motivationText: text("motivation_text").notNull(),
  status: applicationStatusEnum("status").notNull().default("pending"),
  reviewedBy: uuid("reviewed_by"),
  reviewedAt: timestamp("reviewed_at", { mode: "date" }),
  reviewNotes: text("review_notes"),
  createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
});

export const accounts = pgTable("accounts", {
  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  type: text("type").notNull(),
  provider: text("provider").notNull(),
  providerAccountId: text("provider_account_id").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) => ({ pk: primaryKey({ columns: [t.provider, t.providerAccountId] }) }));

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

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

export const adminAudit = pgTable("admin_audit", {
  id: uuid("id").primaryKey().defaultRandom(),
  adminUserId: uuid("admin_user_id").notNull().references(() => users.id),
  action: text("action").notNull(),
  targetId: uuid("target_id"),
  metadata: jsonb("metadata"),
  createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
});

/**
 * Key/value store for site-wide admin settings — branding, contact details,
 * membership SLA, feature toggles. Schemaless on purpose so new settings can
 * be added without a migration. The lib/settings layer validates reads/writes.
 */
export const adminSettings = pgTable("admin_settings", {
  key: text("key").primaryKey(),
  value: jsonb("value").notNull(),
  updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  updatedBy: uuid("updated_by").references(() => users.id, {
    onDelete: "set null",
  }),
});

// -------------------------------------------------------------------------
// Phase 5 — Academy LMS
// -------------------------------------------------------------------------

export const courseLevelEnum = pgEnum("course_level", [
  "Foundation",
  "Intermediate",
  "Advanced",
]);
export const courseStatusEnum = pgEnum("course_status", ["draft", "published", "archived"]);
export const lessonTypeEnum = pgEnum("lesson_type", ["video", "reading", "quiz"]);
export const videoProviderEnum = pgEnum("video_provider", [
  "youtube",
  "vimeo",
  "mp4",
  "none",
]);

export const courses = pgTable(
  "courses",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    slug: text("slug").notNull(),
    title: text("title").notNull(),
    level: courseLevelEnum("level").notNull().default("Foundation"),
    topic: text("topic").notNull().default("General"),
    shortDescription: text("short_description").notNull().default(""),
    description: text("description").notNull().default(""),
    outcomes: jsonb("outcomes").$type<string[]>().notNull().default([]),
    prerequisites: jsonb("prerequisites").$type<string[]>().notNull().default([]),
    instructorName: text("instructor_name"),
    instructorTitle: text("instructor_title"),
    instructorBio: text("instructor_bio"),
    coverPhoto: text("cover_photo"),
    coverPhotoAlt: text("cover_photo_alt"),
    status: courseStatusEnum("status").notNull().default("draft"),
    position: integer("position").notNull().default(0),
    publishedAt: timestamp("published_at", { mode: "date" }),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    slugIdx: uniqueIndex("courses_slug_idx").on(t.slug),
    statusIdx: index("courses_status_idx").on(t.status),
  }),
);

export const courseModules = pgTable(
  "course_modules",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    courseId: uuid("course_id")
      .notNull()
      .references(() => courses.id, { onDelete: "cascade" }),
    title: text("title").notNull(),
    position: integer("position").notNull().default(0),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({ courseIdx: index("course_modules_course_idx").on(t.courseId) }),
);

export const lessons = pgTable(
  "lessons",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    moduleId: uuid("module_id")
      .notNull()
      .references(() => courseModules.id, { onDelete: "cascade" }),
    slug: text("slug").notNull(),
    title: text("title").notNull(),
    type: lessonTypeEnum("type").notNull().default("reading"),
    durationMin: integer("duration_min").notNull().default(0),
    content: text("content").notNull().default(""),
    transcript: text("transcript"),
    keyPoints: jsonb("key_points").$type<string[]>().notNull().default([]),
    videoProvider: videoProviderEnum("video_provider").notNull().default("none"),
    videoUrl: text("video_url"),
    videoPoster: text("video_poster"),
    position: integer("position").notNull().default(0),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    moduleSlugIdx: uniqueIndex("lessons_module_slug_idx").on(t.moduleId, t.slug),
  }),
);

export const enrollments = pgTable(
  "enrollments",
  {
    userId: uuid("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    courseId: uuid("course_id")
      .notNull()
      .references(() => courses.id, { onDelete: "cascade" }),
    enrolledAt: timestamp("enrolled_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({ pk: primaryKey({ columns: [t.userId, t.courseId] }) }),
);

export const lessonProgress = pgTable(
  "lesson_progress",
  {
    userId: uuid("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    lessonId: uuid("lesson_id")
      .notNull()
      .references(() => lessons.id, { onDelete: "cascade" }),
    completedAt: timestamp("completed_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({ pk: primaryKey({ columns: [t.userId, t.lessonId] }) }),
);

// -------------------------------------------------------------------------
// Phase 6 — Store
// -------------------------------------------------------------------------

export const productStatusEnum = pgEnum("product_status", ["draft", "published", "archived"]);
export const stockStatusEnum = pgEnum("stock_status", [
  "in_stock",
  "out_of_stock",
  "preorder",
  "coming_soon",
]);
export const storeOrderStatusEnum = pgEnum("store_order_status", [
  "pending_invoice",
  "invoiced",
  "paid",
  "shipped",
  "delivered",
  "cancelled",
]);

export const storeCategories = pgTable(
  "store_categories",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    slug: text("slug").notNull(),
    name: text("name").notNull(),
    description: text("description").notNull().default(""),
    position: integer("position").notNull().default(0),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({ slugIdx: uniqueIndex("store_categories_slug_idx").on(t.slug) }),
);

export const storeProducts = pgTable(
  "store_products",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    slug: text("slug").notNull(),
    title: text("title").notNull(),
    shortDescription: text("short_description").notNull().default(""),
    description: text("description").notNull().default(""),
    categoryId: uuid("category_id").references(() => storeCategories.id, {
      onDelete: "set null",
    }),
    priceMinor: integer("price_minor").notNull().default(0),
    currency: text("currency").notNull().default("GBP"),
    sku: text("sku"),
    stockStatus: stockStatusEnum("stock_status").notNull().default("coming_soon"),
    coverImage: text("cover_image"),
    coverImageAlt: text("cover_image_alt"),
    gallery: jsonb("gallery")
      .$type<Array<{ path: string; alt: string }>>()
      .notNull()
      .default([]),
    memberOnly: boolean("member_only").notNull().default(false),
    status: productStatusEnum("status").notNull().default("draft"),
    publishedAt: timestamp("published_at", { mode: "date" }),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    slugIdx: uniqueIndex("store_products_slug_idx").on(t.slug),
    statusIdx: index("store_products_status_idx").on(t.status),
  }),
);

/**
 * Orders placed against the OrthoGlobe store. Payment isn't collected at
 * checkout — an admin reviews the order and issues an invoice manually. Once
 * paid (bank transfer or an external payment link the admin generates), the
 * admin marks the order paid + shipped with tracking info. This keeps the
 * flow shippable today without Stripe credentials.
 */
export const storeOrders = pgTable(
  "store_orders",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    /** Sequential human-readable order number — e.g. OG-00042. */
    orderNumber: text("order_number").notNull(),
    userId: uuid("user_id").references(() => users.id, {
      onDelete: "set null",
    }),
    customerName: text("customer_name").notNull(),
    customerEmail: text("customer_email").notNull(),
    shippingAddress: text("shipping_address").notNull(),
    shippingCountry: text("shipping_country").notNull(),
    customerPhone: text("customer_phone"),
    notes: text("notes").notNull().default(""),
    subtotalMinor: integer("subtotal_minor").notNull().default(0),
    shippingMinor: integer("shipping_minor").notNull().default(0),
    totalMinor: integer("total_minor").notNull().default(0),
    currency: text("currency").notNull().default("GBP"),
    status: storeOrderStatusEnum("status").notNull().default("pending_invoice"),
    /** URL to an invoice PDF uploaded or generated externally. */
    invoiceUrl: text("invoice_url"),
    trackingUrl: text("tracking_url"),
    trackingNumber: text("tracking_number"),
    adminNotes: text("admin_notes").notNull().default(""),
    invoicedAt: timestamp("invoiced_at", { mode: "date" }),
    paidAt: timestamp("paid_at", { mode: "date" }),
    shippedAt: timestamp("shipped_at", { mode: "date" }),
    deliveredAt: timestamp("delivered_at", { mode: "date" }),
    cancelledAt: timestamp("cancelled_at", { mode: "date" }),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    orderNumberIdx: uniqueIndex("store_orders_order_number_idx").on(t.orderNumber),
    userIdx: index("store_orders_user_idx").on(t.userId),
    statusIdx: index("store_orders_status_idx").on(t.status),
    createdIdx: index("store_orders_created_idx").on(t.createdAt),
  }),
);

export const storeOrderLines = pgTable(
  "store_order_lines",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    orderId: uuid("order_id")
      .notNull()
      .references(() => storeOrders.id, { onDelete: "cascade" }),
    productId: uuid("product_id").references(() => storeProducts.id, {
      onDelete: "set null",
    }),
    /** Frozen at order time so product edits don't rewrite history. */
    productTitle: text("product_title").notNull(),
    productSku: text("product_sku"),
    productSlug: text("product_slug"),
    unitPriceMinor: integer("unit_price_minor").notNull(),
    quantity: integer("quantity").notNull(),
    lineTotalMinor: integer("line_total_minor").notNull(),
    position: integer("position").notNull().default(0),
  },
  (t) => ({
    orderIdx: index("store_order_lines_order_idx").on(t.orderId),
  }),
);

export type StoreOrder = typeof storeOrders.$inferSelect;
export type NewStoreOrder = typeof storeOrders.$inferInsert;
export type StoreOrderLine = typeof storeOrderLines.$inferSelect;

// -------------------------------------------------------------------------
// Phase 7 — Research Portal
// -------------------------------------------------------------------------

export const studyPhaseEnum = pgEnum("study_phase", [
  "Planned",
  "Recruiting",
  "Active",
  "Completed",
]);
export const studyStatusEnum = pgEnum("study_status", ["draft", "published", "archived"]);

export const researchStudiesTable = pgTable(
  "research_studies",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    slug: text("slug").notNull(),
    title: text("title").notNull(),
    phase: studyPhaseEnum("phase").notNull().default("Planned"),
    nctId: text("nct_id"),
    sponsor: text("sponsor").notNull().default("OrthoGlobe Charity Collaboration"),
    principalInvestigator: text("principal_investigator").notNull().default(""),
    conditionKeyword: text("condition_keyword").notNull().default(""),
    targetEnrolment: integer("target_enrolment").notNull().default(0),
    currentEnrolment: integer("current_enrolment").notNull().default(0),
    countries: jsonb("countries").$type<string[]>().notNull().default([]),
    background: text("background").notNull().default(""),
    design: text("design").notNull().default(""),
    intervention: text("intervention").notNull().default(""),
    control: text("control").notNull().default(""),
    inclusion: jsonb("inclusion").$type<string[]>().notNull().default([]),
    exclusion: jsonb("exclusion").$type<string[]>().notNull().default([]),
    primaryOutcomes: jsonb("primary_outcomes").$type<string[]>().notNull().default([]),
    secondaryOutcomes: jsonb("secondary_outcomes").$type<string[]>().notNull().default([]),
    status: studyStatusEnum("status").notNull().default("draft"),
    isRecruitingCollaborators: boolean("is_recruiting_collaborators")
      .notNull()
      .default(false),
    collaborationEmail: text("collaboration_email"),
    authorshipOpportunities: text("authorship_opportunities").notNull().default(""),
    openRoles: jsonb("open_roles")
      .$type<Array<{ title: string; description: string; commitment: string; compensation: string }>>()
      .notNull()
      .default([]),
    /** Featured on the Research Portal landing page. */
    featured: boolean("featured").notNull().default(false),
    /** Free-text specialty for filtering (e.g. "Shoulder", "Hip", "Trauma"). */
    specialty: text("specialty").notNull().default(""),
    /** Thematic tags rendered on the card + used for cross-study filtering. */
    themes: jsonb("themes").$type<string[]>().notNull().default([]),
    /** Ordered list of milestones rendered as a timeline on the detail page. */
    timelineMilestones: jsonb("timeline_milestones")
      .$type<Array<{ title: string; date: string; description: string; status: "done" | "active" | "upcoming" }>>()
      .notNull()
      .default([]),
    ethicsRef: text("ethics_ref").notNull().default(""),
    protocolVersion: text("protocol_version").notNull().default(""),
    publishedAt: timestamp("published_at", { mode: "date" }),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    slugIdx: uniqueIndex("research_studies_slug_idx").on(t.slug),
    statusIdx: index("research_studies_status_idx").on(t.status),
    specialtyIdx: index("research_studies_specialty_idx").on(t.specialty),
    featuredIdx: index("research_studies_featured_idx").on(t.featured),
  }),
);

export const researchSites = pgTable(
  "research_sites",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    studyId: uuid("study_id")
      .notNull()
      .references(() => researchStudiesTable.id, { onDelete: "cascade" }),
    city: text("city").notNull(),
    country: text("country").notNull(),
    piName: text("pi_name").notNull(),
    enrolment: integer("enrolment").notNull().default(0),
    position: integer("position").notNull().default(0),
  },
  (t) => ({ studyIdx: index("research_sites_study_idx").on(t.studyId) }),
);

export const researchInvestigators = pgTable(
  "research_investigators",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    studyId: uuid("study_id")
      .notNull()
      .references(() => researchStudiesTable.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    institution: text("institution").notNull(),
    role: text("role").notNull(),
    position: integer("position").notNull().default(0),
  },
  (t) => ({ studyIdx: index("research_investigators_study_idx").on(t.studyId) }),
);

export const researchDocuments = pgTable(
  "research_documents",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    studyId: uuid("study_id")
      .notNull()
      .references(() => researchStudiesTable.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    type: text("type").notNull(),
    url: text("url"),
    position: integer("position").notNull().default(0),
  },
  (t) => ({ studyIdx: index("research_documents_study_idx").on(t.studyId) }),
);

// -------------------------------------------------------------------------
// Phase 14 — Research Portal expansion: publications, updates, applications,
// and proposals. Backs the new public pages and admin review queues.
// -------------------------------------------------------------------------

export const researchPublicationTypeEnum = pgEnum("research_publication_type", [
  "journal",
  "preprint",
  "conference",
  "poster",
  "dataset",
  "report",
]);

export const researchPublications = pgTable(
  "research_publications",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    studyId: uuid("study_id")
      .notNull()
      .references(() => researchStudiesTable.id, { onDelete: "cascade" }),
    title: text("title").notNull(),
    authors: jsonb("authors").$type<string[]>().notNull().default([]),
    journal: text("journal").notNull().default(""),
    year: integer("year"),
    doi: text("doi"),
    pmid: text("pmid"),
    url: text("url"),
    abstract: text("abstract").notNull().default(""),
    type: researchPublicationTypeEnum("type").notNull().default("journal"),
    position: integer("position").notNull().default(0),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    studyIdx: index("research_publications_study_idx").on(t.studyId),
    yearIdx: index("research_publications_year_idx").on(t.year),
  }),
);

export const researchUpdates = pgTable(
  "research_updates",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    studyId: uuid("study_id")
      .notNull()
      .references(() => researchStudiesTable.id, { onDelete: "cascade" }),
    title: text("title").notNull(),
    body: text("body").notNull().default(""),
    postedAt: timestamp("posted_at", { mode: "date" }).notNull().defaultNow(),
    postedByName: text("posted_by_name").notNull().default(""),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    studyIdx: index("research_updates_study_idx").on(t.studyId),
    postedIdx: index("research_updates_posted_idx").on(t.postedAt),
  }),
);

export const researchApplicationStatusEnum = pgEnum(
  "research_application_status",
  ["pending", "accepted", "declined", "withdrawn"],
);

export const researchApplications = pgTable(
  "research_applications",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    /** Null when the applicant is expressing general interest without a specific study. */
    studyId: uuid("study_id").references(() => researchStudiesTable.id, {
      onDelete: "set null",
    }),
    name: text("name").notNull(),
    email: text("email").notNull(),
    institution: text("institution").notNull().default(""),
    country: text("country").notNull().default(""),
    professionalRole: text("professional_role").notNull().default(""),
    orcidUrl: text("orcid_url"),
    linkedinUrl: text("linkedin_url"),
    rolePreference: text("role_preference").notNull().default(""),
    experience: text("experience").notNull().default(""),
    motivation: text("motivation").notNull().default(""),
    status: researchApplicationStatusEnum("status").notNull().default("pending"),
    reviewedBy: uuid("reviewed_by").references(() => users.id, {
      onDelete: "set null",
    }),
    reviewedAt: timestamp("reviewed_at", { mode: "date" }),
    reviewNotes: text("review_notes"),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    studyIdx: index("research_applications_study_idx").on(t.studyId),
    statusIdx: index("research_applications_status_idx").on(t.status),
    createdIdx: index("research_applications_created_idx").on(t.createdAt),
  }),
);

export const researchProposalStatusEnum = pgEnum(
  "research_proposal_status",
  ["pending", "accepted", "declined"],
);

export const researchProposals = pgTable(
  "research_proposals",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    title: text("title").notNull(),
    proposerName: text("proposer_name").notNull(),
    email: text("email").notNull(),
    institution: text("institution").notNull().default(""),
    country: text("country").notNull().default(""),
    orcidUrl: text("orcid_url"),
    specialty: text("specialty").notNull().default(""),
    background: text("background").notNull().default(""),
    hypothesis: text("hypothesis").notNull().default(""),
    design: text("design").notNull().default(""),
    resourcesNeeded: text("resources_needed").notNull().default(""),
    fundingStatus: text("funding_status").notNull().default(""),
    status: researchProposalStatusEnum("status").notNull().default("pending"),
    reviewedBy: uuid("reviewed_by").references(() => users.id, {
      onDelete: "set null",
    }),
    reviewedAt: timestamp("reviewed_at", { mode: "date" }),
    reviewNotes: text("review_notes"),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    statusIdx: index("research_proposals_status_idx").on(t.status),
    createdIdx: index("research_proposals_created_idx").on(t.createdAt),
  }),
);

export type ResearchPublication = typeof researchPublications.$inferSelect;
export type ResearchUpdate = typeof researchUpdates.$inferSelect;
export type ResearchApplication = typeof researchApplications.$inferSelect;
export type ResearchProposal = typeof researchProposals.$inferSelect;

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Application = typeof applications.$inferSelect;
export type NewApplication = typeof applications.$inferInsert;
export type Course = typeof courses.$inferSelect;
export type NewCourse = typeof courses.$inferInsert;
export type CourseModule = typeof courseModules.$inferSelect;
export type NewCourseModule = typeof courseModules.$inferInsert;
export type Lesson = typeof lessons.$inferSelect;
export type NewLesson = typeof lessons.$inferInsert;
export type StoreCategory = typeof storeCategories.$inferSelect;
export type NewStoreCategory = typeof storeCategories.$inferInsert;
export type StoreProduct = typeof storeProducts.$inferSelect;
export type NewStoreProduct = typeof storeProducts.$inferInsert;
export type ResearchStudyRow = typeof researchStudiesTable.$inferSelect;
export type NewResearchStudy = typeof researchStudiesTable.$inferInsert;

// -------------------------------------------------------------------------
// Phase 10 — OrthoPrep question bank (DB-backed)
// -------------------------------------------------------------------------

export const questionStatusEnum = pgEnum("question_status", ["draft", "published", "archived"]);
export const questionDifficultyEnum = pgEnum("question_difficulty", [
  "Easy",
  "Medium",
  "Hard",
]);

export const orthoprepQuestions = pgTable(
  "orthoprep_questions",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    /** Human-readable sequence number, e.g. 1..300 for the FRCS bank. */
    number: integer("number").notNull().default(0),
    part: text("part").notNull().default(""),
    chapter: text("chapter").notNull().default(""),
    stem: text("stem").notNull(),
    options: jsonb("options").$type<string[]>().notNull().default([]),
    answer: text("answer").notNull().default("A"),
    explanation: text("explanation").notNull().default(""),
    distractors: text("distractors").notNull().default(""),
    takeHome: text("take_home").notNull().default(""),
    difficulty: questionDifficultyEnum("difficulty").notNull().default("Medium"),
    tags: jsonb("tags").$type<string[]>().notNull().default([]),
    status: questionStatusEnum("status").notNull().default("draft"),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    numberIdx: uniqueIndex("orthoprep_questions_number_idx").on(t.number),
    statusIdx: index("orthoprep_questions_status_idx").on(t.status),
    partIdx: index("orthoprep_questions_part_idx").on(t.part),
  }),
);

export type OrthoprepQuestion = typeof orthoprepQuestions.$inferSelect;
export type NewOrthoprepQuestion = typeof orthoprepQuestions.$inferInsert;

// -------------------------------------------------------------------------
// Phase 10 — ConsentIQ procedures (admin-editable)
// -------------------------------------------------------------------------

export type ConsentProcedureOptionChoice = {
  value: string;
  label: string;
};

export type ConsentProcedureOption = {
  id: string;
  label: string;
  kind: "select" | "radio" | "text";
  help?: string;
  choices?: ConsentProcedureOptionChoice[];
  defaultValue?: string;
};

export type ConsentProcedureRisk = {
  title: string;
  detail: string;
  severity: "common" | "rare" | "serious" | "";
};

export const consentProcedureStatusEnum = pgEnum("consent_procedure_status", [
  "draft",
  "published",
  "archived",
]);

export const consentiqProcedures = pgTable(
  "consentiq_procedures",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    slug: text("slug").notNull(),
    name: text("name").notNull(),
    region: text("region").notNull().default(""),
    approxDurationMin: integer("approx_duration_min").notNull().default(0),
    anaesthesia: jsonb("anaesthesia").$type<string[]>().notNull().default([]),
    summary: text("summary").notNull().default(""),
    involves: jsonb("involves").$type<string[]>().notNull().default([]),
    options: jsonb("options")
      .$type<ConsentProcedureOption[]>()
      .notNull()
      .default([]),
    risks: jsonb("risks")
      .$type<ConsentProcedureRisk[]>()
      .notNull()
      .default([]),
    benefits: jsonb("benefits").$type<string[]>().notNull().default([]),
    alternatives: jsonb("alternatives").$type<string[]>().notNull().default([]),
    recovery: jsonb("recovery").$type<string[]>().notNull().default([]),
    warnings: jsonb("warnings").$type<string[]>().notNull().default([]),
    status: consentProcedureStatusEnum("status").notNull().default("draft"),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    slugIdx: uniqueIndex("consentiq_procedures_slug_idx").on(t.slug),
    statusIdx: index("consentiq_procedures_status_idx").on(t.status),
    regionIdx: index("consentiq_procedures_region_idx").on(t.region),
  }),
);

export type ConsentiqProcedure = typeof consentiqProcedures.$inferSelect;
export type NewConsentiqProcedure = typeof consentiqProcedures.$inferInsert;

// -------------------------------------------------------------------------
// Phase 10 — Academy resources / attachments
// -------------------------------------------------------------------------

export const courseResourceTypeEnum = pgEnum("course_resource_type", [
  "pdf",
  "poster",
  "slide_deck",
  "article",
  "link",
  "image",
  "video",
  "other",
]);

export const courseResources = pgTable(
  "course_resources",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    courseId: uuid("course_id")
      .notNull()
      .references(() => courses.id, { onDelete: "cascade" }),
    /** Optional — attach to a specific lesson. Null means course-level. */
    lessonId: uuid("lesson_id").references(() => lessons.id, {
      onDelete: "set null",
    }),
    type: courseResourceTypeEnum("type").notNull().default("link"),
    title: text("title").notNull(),
    description: text("description").notNull().default(""),
    url: text("url").notNull(),
    fileSize: integer("file_size"),
    mimeType: text("mime_type"),
    position: integer("position").notNull().default(0),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    courseIdx: index("course_resources_course_idx").on(t.courseId),
    lessonIdx: index("course_resources_lesson_idx").on(t.lessonId),
  }),
);

export type CourseResource = typeof courseResources.$inferSelect;
export type NewCourseResource = typeof courseResources.$inferInsert;

// -------------------------------------------------------------------------
// Phase 11 — Orthopaedic Atlas (admin-editable)
// -------------------------------------------------------------------------

export const atlasStatusEnum = pgEnum("atlas_status", [
  "draft",
  "published",
  "archived",
]);

export const atlasCategoryEnum = pgEnum("atlas_category", [
  "Degenerative",
  "Traumatic",
  "Inflammatory",
  "Congenital",
  "Infectious",
  "Neoplastic",
]);

export const atlasRegions = pgTable(
  "atlas_regions",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    slug: text("slug").notNull(),
    name: text("name").notNull(),
    summary: text("summary").notNull().default(""),
    icon: text("icon"),
    photoUrl: text("photo_url"),
    photoAlt: text("photo_alt"),
    status: atlasStatusEnum("status").notNull().default("published"),
    position: integer("position").notNull().default(0),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    slugIdx: uniqueIndex("atlas_regions_slug_idx").on(t.slug),
  }),
);

export type AtlasConditionSections = {
  overview: string;
  epidemiology: string;
  symptoms: string;
  imaging: string;
  classification: string;
  management: string;
  outcomes: string;
};

export const atlasConditions = pgTable(
  "atlas_conditions",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    slug: text("slug").notNull(),
    name: text("name").notNull(),
    regionSlug: text("region_slug").notNull().default(""),
    category: atlasCategoryEnum("category").notNull().default("Degenerative"),
    summary: text("summary").notNull().default(""),
    sections: jsonb("sections")
      .$type<AtlasConditionSections>()
      .notNull()
      .default({
        overview: "",
        epidemiology: "",
        symptoms: "",
        imaging: "",
        classification: "",
        management: "",
        outcomes: "",
      }),
    referencesList: jsonb("references_list")
      .$type<string[]>()
      .notNull()
      .default([]),
    status: atlasStatusEnum("status").notNull().default("published"),
    position: integer("position").notNull().default(0),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    slugIdx: uniqueIndex("atlas_conditions_slug_idx").on(t.slug),
    regionIdx: index("atlas_conditions_region_idx").on(t.regionSlug),
    statusIdx: index("atlas_conditions_status_idx").on(t.status),
  }),
);

export type AtlasRegionRow = typeof atlasRegions.$inferSelect;
export type NewAtlasRegion = typeof atlasRegions.$inferInsert;
export type AtlasConditionRow = typeof atlasConditions.$inferSelect;
export type NewAtlasCondition = typeof atlasConditions.$inferInsert;

// -------------------------------------------------------------------------
// Phase 8 — Editorial: blog, team, notifications
// -------------------------------------------------------------------------

export const blogStatusEnum = pgEnum("blog_status", ["draft", "published", "archived"]);
export const teamStatusEnum = pgEnum("team_status", ["active", "archived"]);
export const teamRoleEnum = pgEnum("team_role", ["lead", "committee", "contributor"]);
export const notificationLevelEnum = pgEnum("notification_level", [
  "info",
  "warning",
  "success",
]);

export const blogPosts = pgTable(
  "blog_posts",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    slug: text("slug").notNull(),
    title: text("title").notNull(),
    excerpt: text("excerpt").notNull().default(""),
    content: text("content").notNull().default(""),
    coverImage: text("cover_image"),
    coverImageAlt: text("cover_image_alt"),
    authorUserId: uuid("author_user_id").references(() => users.id, {
      onDelete: "set null",
    }),
    authorName: text("author_name").notNull().default(""),
    tags: jsonb("tags").$type<string[]>().notNull().default([]),
    status: blogStatusEnum("status").notNull().default("draft"),
    publishedAt: timestamp("published_at", { mode: "date" }),
    scheduledAt: timestamp("scheduled_at", { mode: "date" }),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    slugIdx: uniqueIndex("blog_posts_slug_idx").on(t.slug),
    statusIdx: index("blog_posts_status_idx").on(t.status),
  }),
);

export const teamMembers = pgTable(
  "team_members",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    slug: text("slug").notNull(),
    name: text("name").notNull(),
    title: text("title").notNull().default(""),
    role: teamRoleEnum("role").notNull().default("contributor"),
    bio: text("bio").notNull().default(""),
    photo: text("photo"),
    photoAlt: text("photo_alt"),
    country: text("country").notNull().default(""),
    institution: text("institution").notNull().default(""),
    orcidUrl: text("orcid_url"),
    linkedinUrl: text("linkedin_url"),
    websiteUrl: text("website_url"),
    position: integer("position").notNull().default(0),
    status: teamStatusEnum("status").notNull().default("active"),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    slugIdx: uniqueIndex("team_members_slug_idx").on(t.slug),
    statusIdx: index("team_members_status_idx").on(t.status),
  }),
);

export const notifications = pgTable(
  "notifications",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    title: text("title").notNull(),
    body: text("body").notNull().default(""),
    href: text("href"),
    level: notificationLevelEnum("level").notNull().default("info"),
    active: boolean("active").notNull().default(true),
    startsAt: timestamp("starts_at", { mode: "date" }),
    endsAt: timestamp("ends_at", { mode: "date" }),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({ activeIdx: index("notifications_active_idx").on(t.active) }),
);

export type BlogPost = typeof blogPosts.$inferSelect;
export type NewBlogPost = typeof blogPosts.$inferInsert;
export type TeamMember = typeof teamMembers.$inferSelect;
export type NewTeamMember = typeof teamMembers.$inferInsert;
export type Notification = typeof notifications.$inferSelect;
export type NewNotification = typeof notifications.$inferInsert;

// -------------------------------------------------------------------------
// Phase 9 — Product page CMS
//
// One row per product landing page (slug-keyed). Hero/CTA are scalar fields,
// repeating sections (features, FAQs, audience cards, access tiles) live in
// jsonb arrays so editing is a single table roundtrip with no joins.
// -------------------------------------------------------------------------

export type ProductPageFeature = {
  title: string;
  body: string;
  iconName: string;
};

export type ProductPageFaq = {
  question: string;
  answer: string;
};

export type ProductPageAudience = {
  title: string;
  body: string;
};

export type ProductPageAccessTile = {
  href: string;
  label: string;
  description: string;
  iconName: string;
};

/**
 * Free-form content block an admin can add between the fixed sections of a
 * product landing page. One row per block, rendered in the order stored.
 */
export type ProductPageCustomSection = {
  type: "text" | "image" | "video" | "quote";
  heading: string;
  body: string;
  imagePath: string;
  imageAlt: string;
  videoUrl: string;
  videoProvider: string;
  ctaLabel: string;
  ctaHref: string;
};

export const productPages = pgTable("product_pages", {
  slug: text("slug").primaryKey(),
  heroEyebrow: text("hero_eyebrow").notNull().default(""),
  heroTitle: text("hero_title").notNull().default(""),
  heroTagline: text("hero_tagline").notNull().default(""),
  heroDescription: text("hero_description").notNull().default(""),
  heroImagePath: text("hero_image_path"),
  heroImageAlt: text("hero_image_alt"),
  heroPrimaryCtaLabel: text("hero_primary_cta_label").notNull().default(""),
  heroPrimaryCtaHref: text("hero_primary_cta_href").notNull().default(""),
  heroSecondaryCtaLabel: text("hero_secondary_cta_label").notNull().default(""),
  heroSecondaryCtaHref: text("hero_secondary_cta_href").notNull().default(""),
  accessRailTagline: text("access_rail_tagline").notNull().default(""),
  accessRailMembersOnly: boolean("access_rail_members_only").notNull().default(false),
  accessTiles: jsonb("access_tiles")
    .$type<ProductPageAccessTile[]>()
    .notNull()
    .default([]),
  sectionEyebrow: text("section_eyebrow").notNull().default(""),
  sectionHeading: text("section_heading").notNull().default(""),
  sectionBody: text("section_body").notNull().default(""),
  showcaseImagePath: text("showcase_image_path"),
  showcaseImageAlt: text("showcase_image_alt"),
  showcaseEyebrow: text("showcase_eyebrow").notNull().default(""),
  showcaseCaption: text("showcase_caption").notNull().default(""),
  features: jsonb("features")
    .$type<ProductPageFeature[]>()
    .notNull()
    .default([]),
  audienceHeading: text("audience_heading").notNull().default(""),
  audienceBody: text("audience_body").notNull().default(""),
  audiences: jsonb("audiences")
    .$type<ProductPageAudience[]>()
    .notNull()
    .default([]),
  faqs: jsonb("faqs").$type<ProductPageFaq[]>().notNull().default([]),
  ctaTitle: text("cta_title").notNull().default(""),
  ctaBody: text("cta_body").notNull().default(""),
  ctaPrimaryLabel: text("cta_primary_label").notNull().default(""),
  ctaPrimaryHref: text("cta_primary_href").notNull().default(""),
  ctaSecondaryLabel: text("cta_secondary_label").notNull().default(""),
  ctaSecondaryHref: text("cta_secondary_href").notNull().default(""),
  customSections: jsonb("custom_sections")
    .$type<ProductPageCustomSection[]>()
    .notNull()
    .default([]),
  updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
  updatedBy: uuid("updated_by").references(() => users.id, {
    onDelete: "set null",
  }),
});

export type ProductPage = typeof productPages.$inferSelect;
export type NewProductPage = typeof productPages.$inferInsert;

// -------------------------------------------------------------------------
// Phase 12 — Auth & Admin hardening
//
// password_reset_tokens: self-service + admin-initiated password reset.
// email_templates:       admin-editable email copy, keyed by slug.
// email_log:             delivery history for observability + admin viewer.
// redirects:             admin-managed 301/302 URL redirects applied in proxy.
// seo_defaults:          global SEO defaults (lives in admin_settings now via
//                        the settings service, not a separate table).
// rate_limit_entries:    DB-backed replacement for in-memory rate limit Map.
// media_assets:          catalogue of uploaded images for the media library.
// -------------------------------------------------------------------------

export const passwordResetTokens = pgTable(
  "password_reset_tokens",
  {
    tokenHash: text("token_hash").primaryKey(),
    userId: uuid("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    expiresAt: timestamp("expires_at", { mode: "date" }).notNull(),
    usedAt: timestamp("used_at", { mode: "date" }),
    /** "self" = user used /forgot-password, "admin" = admin-initiated. */
    issuedBy: text("issued_by").notNull().default("self"),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    userIdx: index("password_reset_tokens_user_idx").on(t.userId),
    expiresIdx: index("password_reset_tokens_expires_idx").on(t.expiresAt),
  }),
);

export type PasswordResetToken = typeof passwordResetTokens.$inferSelect;
export type NewPasswordResetToken = typeof passwordResetTokens.$inferInsert;

export const emailTemplates = pgTable(
  "email_templates",
  {
    slug: text("slug").primaryKey(),
    name: text("name").notNull(),
    description: text("description").notNull().default(""),
    subject: text("subject").notNull(),
    body: text("body").notNull(),
    /** Mustache-style variables the template supports, e.g. ["name","loginUrl"]. */
    variables: jsonb("variables").$type<string[]>().notNull().default([]),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
    updatedBy: uuid("updated_by").references(() => users.id, {
      onDelete: "set null",
    }),
  },
);

export type EmailTemplate = typeof emailTemplates.$inferSelect;
export type NewEmailTemplate = typeof emailTemplates.$inferInsert;

export const emailDeliveryStatusEnum = pgEnum("email_delivery_status", [
  "queued",
  "sent",
  "failed",
  "dev_console",
]);

export const emailLog = pgTable(
  "email_log",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    recipient: text("recipient").notNull(),
    subject: text("subject").notNull(),
    templateSlug: text("template_slug"),
    status: emailDeliveryStatusEnum("status").notNull().default("queued"),
    providerId: text("provider_id"),
    errorMessage: text("error_message"),
    bodyPreview: text("body_preview").notNull().default(""),
    sentAt: timestamp("sent_at", { mode: "date" }),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    createdIdx: index("email_log_created_idx").on(t.createdAt),
    statusIdx: index("email_log_status_idx").on(t.status),
  }),
);

export type EmailLogEntry = typeof emailLog.$inferSelect;
export type NewEmailLogEntry = typeof emailLog.$inferInsert;

export const redirectKindEnum = pgEnum("redirect_kind", ["permanent", "temporary"]);

export const redirects = pgTable(
  "redirects",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    /** Source path, must start with "/". Stored lowercase. */
    source: text("source").notNull(),
    /** Destination — absolute path or full URL. */
    destination: text("destination").notNull(),
    kind: redirectKindEnum("kind").notNull().default("permanent"),
    active: boolean("active").notNull().default(true),
    hits: integer("hits").notNull().default(0),
    lastHitAt: timestamp("last_hit_at", { mode: "date" }),
    notes: text("notes").notNull().default(""),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
    createdBy: uuid("created_by").references(() => users.id, {
      onDelete: "set null",
    }),
  },
  (t) => ({
    sourceIdx: uniqueIndex("redirects_source_idx").on(t.source),
    activeIdx: index("redirects_active_idx").on(t.active),
  }),
);

export type Redirect = typeof redirects.$inferSelect;
export type NewRedirect = typeof redirects.$inferInsert;

export const rateLimitEntries = pgTable(
  "rate_limit_entries",
  {
    key: text("key").notNull(),
    windowStart: timestamp("window_start", { mode: "date" }).notNull(),
    hits: integer("hits").notNull().default(1),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.key, t.windowStart] }),
    keyIdx: index("rate_limit_entries_key_idx").on(t.key),
  }),
);

export const mediaAssets = pgTable(
  "media_assets",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    path: text("path").notNull(),
    /** Generated 320px-wide WebP thumbnail for gallery/grids, same folder. */
    thumbnailPath: text("thumbnail_path"),
    originalName: text("original_name").notNull().default(""),
    altText: text("alt_text").notNull().default(""),
    caption: text("caption").notNull().default(""),
    mimeType: text("mime_type").notNull().default(""),
    width: integer("width"),
    height: integer("height"),
    fileSize: integer("file_size"),
    /** Hash of file bytes for dedupe — same hash means same file. */
    sha256: text("sha256"),
    uploadedBy: uuid("uploaded_by").references(() => users.id, {
      onDelete: "set null",
    }),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
  },
  (t) => ({
    pathIdx: uniqueIndex("media_assets_path_idx").on(t.path),
    sha256Idx: index("media_assets_sha256_idx").on(t.sha256),
    createdIdx: index("media_assets_created_idx").on(t.createdAt),
  }),
);

export type MediaAsset = typeof mediaAssets.$inferSelect;
export type NewMediaAsset = typeof mediaAssets.$inferInsert;
