Slug Architecture

/{locale}/{org-slug}/{tour-slug}

organization.slug  →  "museum-zurich"   (globally unique)
tour.slug          →  "giacometti"          (unique per organization)

organizationSlug  →  { organizationId, slug: "old-name", createdAt }
tourSlug          →  { tourId, organizationId, slug: "old-name", createdAt }

# Organization page
valguide.com/{locale}/{org-slug}

# Tour page  
valguide.com/{locale}/{org-slug}/{tour-slug}

# Stop page (uses nanoId, not slug)
valguide.com/{locale}/{org-slug}/{tour-slug}/{stop-nanoId}

studio.valguide.com/tours/{nanoId}
studio.valguide.com/tours/{nanoId}/stops/{stopNanoId}

export const organization = studioSchema.table('organization', {
  id: uuid('id').defaultRandom().primaryKey(),
  nanoId: varchar('nano_id', { length: 21 }).notNull().unique(),
  name: varchar('name', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 100 }).notNull().unique('unique_org_slug'),
  // ...
})

export const tour = studioSchema.table(
  'tour',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    nanoId: varchar('nano_id', { length: 21 }).notNull().unique(),
    organizationId: uuid('organization_id').notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    slug: varchar('slug', { length: 200 }).notNull(),
    // ...
  },
  (t) => ({
    tourOrgSlugIdx: uniqueIndex('tour_org_slug_unique')
      .on(t.organizationId, t.slug),
  }),
)

export const organizationSlug = studioSchema.table(
  'organization_slug',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    organizationId: uuid('organization_id').notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    slug: varchar('slug', { length: 100 }).notNull(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
  },
  (t) => ({
    uniqueSlug: uniqueIndex('organization_slug_unique').on(t.slug),
  }),
)

export const tourSlug = studioSchema.table(
  'tour_slug',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    tourId: uuid('tour_id').notNull()
      .references(() => tour.id, { onDelete: 'cascade' }),
    organizationId: uuid('organization_id').notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    slug: varchar('slug', { length: 200 }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => ({
    uniqueOrgSlug: uniqueIndex('tour_slug_org_unique')
      .on(t.organizationId, t.slug),
  }),
)

// createTeam() — sets organization.slug directly
const slug = await generateUniqueOrgSlug(dbClient, name)

const [newTeam] = await tx.insert(organization).values({
  nanoId: valguideId(),
  name,
  slug,  // ← canonical slug, directly on entity
}).returning()

await db.transaction(async (tx) => {
  // 1. Read current slug
  const [current] = await tx
    .select({ slug: organization.slug })
    .from(organization)
    .where(eq(organization.id, organizationId))

  // 2. Insert old slug into redirect table
  if (current && current.slug !== newSlug) {
    await tx.insert(organizationSlug).values({
      organizationId,
      slug: current.slug,
    })
  }

  // 3. Update canonical slug
  await tx.update(organization)
    .set({ slug: newSlug })
    .where(eq(organization.id, organizationId))
})

// resolve-org.server.ts
const org = await db.query.organization.findFirst({
  where: or(
    eq(organization.nanoId, idOrSlug),
    eq(organization.slug, idOrSlug),
  ),
})

if (org) return { found: true, needsRedirect: false, ... }

// Fall back to redirect table
const redirect = await db.query.organizationSlug.findFirst({
  where: eq(organizationSlug.slug, idOrSlug),
})

if (redirect) return { found: true, needsRedirect: true, ... }

const GERMAN_MAP: Record<string, string> = {
  'ä': 'ae', 'Ä': 'Ae', 'ö': 'oe', 'Ö': 'Oe',
  'ü': 'ue', 'Ü': 'Ue', 'ß': 'ss', 'ẞ': 'Ss',
}

export function generateSlug(text: string): string {
  let result = text.normalize()

  // German replacements (before NFD strips them)
  for (const [char, replacement] of Object.entries(GERMAN_MAP)) {
    result = result.replaceAll(char, replacement)
  }

  return result
    .normalize('NFD').replaceAll(/\p{Diacritic}/gu, '')  // Strip accents
    .replaceAll(/\p{Dash_Punctuation}/gu, '-')           // Normalize dashes
    .replaceAll(/([a-zA-Z\d])['\u2019]([ts])\b/g, '$1$2') // Contractions
    .toLowerCase()
    .replaceAll(/[^a-z0-9]+/g, '-')  // Non-alphanumeric → hyphen
    .replace(/^-+|-+$/g, '')         // Trim hyphens
}

// "Museum Zurich"   → "museum-zurich"
// "Highlights-Führung" → "highlights-fuehrung"
// "Musée d'Orsay"      → "musee-dorsay"
// "En–dash em—dash"    → "en-dash-em-dash"

const RESERVED_SLUGS = [
  'admin', 'api', 'app', 'auth', 'login', 'logout', 'signup',
  'settings', 'help', 'support', 'about', 'contact', 'terms',
  'privacy', 'tours', 'stops', 'assets', 'new', 'edit', 'delete',
  'studio', 'links', 'explore', 'search', 'dashboard', 'profile',
  'account', 'billing', 'invite', 'join', 'team', 'teams',
  'org', 'orgs', 'organization', 'organizations',
]

/en/museum-zurich/giacometti  →  /de/museum-zurich/giacometti