Event Services Many-to-Many Architecture

// Old way: Create service for specific event
await db.insertEventService({
  event_id: 'event-a',
  name: 'Haircuts',
  description: 'Professional haircuts',
  capacity: 20
})

// If you wanted the same service on another event, you had to create it again
await db.insertEventService({
  event_id: 'event-b',
  name: 'Haircuts', // Duplicate definition
  description: 'Professional haircuts',
  capacity: 20
})

// New way: Service exists in global catalog
const catalogResponse = await fetch('/api/services')
const services = await catalogResponse.json()
const haircutsService = services.find(s => s.name === 'Haircuts')

// Assign existing service to event A
await fetch('/api/services/assign', {
  method: 'POST',
  body: JSON.stringify({
    event_id: 'event-a',
    service_id: haircutsService.id,
    csrf_token: csrfToken
  })
})

// Assign same service to event B (reuse, not duplicate)
await fetch('/api/services/assign', {
  method: 'POST',
  body: JSON.stringify({
    event_id: 'event-b',
    service_id: haircutsService.id, // Same service
    csrf_token: csrfToken
  })
})

// Get only active services
const activeServices = await fetch('/api/services?status=active')
  .then(r => r.json())

// Get services sorted by display order
const sortedServices = await fetch('/api/services?order_by=display_order')
  .then(r => r.json())

// First assignment succeeds
await assignService(eventId, 'haircuts-service-id') // ✅ OK

// Second assignment fails
await assignService(eventId, 'haircuts-service-id') // ❌ 409 Conflict

// Update service capacity
await db.updateService('haircuts-service-id', {
  capacity: 30 // Increase from 20 to 30
})

// Result: All 11 events (parent + 10 children) now show capacity 30
// No need to update each instance separately

-- Single batch INSERT query (not 10 separate queries)
INSERT INTO event_service_assignments (event_id, service_id, assigned_at)
VALUES
  ('child-1-uuid', 'service-uuid', CURRENT_TIMESTAMP),
  ('child-2-uuid', 'service-uuid', CURRENT_TIMESTAMP),
  -- ... all children in one query

✅ super_admin (level 7) - Full access
✅ admin (level 6) - Full access
✅ team_admin (level 4) - Full access
❌ team_manager (level 3) - View only
❌ volunteer (level 2) - View only
❌ member (level 1) - View only

import { canManageEventServices } from '#utils/event-service-authorization'

const canAssign = canManageEventServices({
  userRole: 'team_admin', // User's role
  userDbId: 'user-uuid-123', // User's database ID
  event: {
    id: 'event-uuid',
    created_by: 'user-uuid-123' // Event creator
  }
})

if (canAssign) {
  // User can assign services to this event
} else {
  // User cannot assign services (not owner, not admin)
}

-- event_services table: 50 rows
-- All with name='Haircuts', description='Professional haircuts', capacity=20

SELECT COUNT(*) FROM event_services
WHERE name = 'Haircuts'; -- Returns: 50

-- services table: 1 row (deduplicated)
SELECT COUNT(*) FROM services
WHERE name = 'Haircuts'; -- Returns: 1

-- event_service_assignments table: 50 rows
SELECT COUNT(*) FROM event_service_assignments
WHERE service_id IN (
  SELECT id FROM services WHERE name = 'Haircuts'
); -- Returns: 50 (all assignments preserved)

// Create service for event
await fetch('/api/event-services/create', {
  method: 'POST',
  body: JSON.stringify({
    event_id: eventId,
    name: 'Haircuts',
    description: 'Professional haircuts',
    capacity: 20,
    csrf_token: csrfToken
  })
})

// Step 1: Check if service exists in catalog
const catalogResponse = await fetch('/api/services')
const services = await catalogResponse.json()

let serviceId = services.find(s => s.name === 'Haircuts')?.id

if (!serviceId) {
  // Step 2a: Create service in catalog (admin only)
  const createResponse = await fetch('/api/services/create', {
    method: 'POST',
    body: JSON.stringify({
      name: 'Haircuts',
      description: 'Professional haircuts',
      capacity: 20,
      csrf_token: csrfToken
    })
  })
  const { id } = await createResponse.json()
  serviceId = id
}

// Step 3: Assign service to event
await fetch('/api/services/assign', {
  method: 'POST',
  body: JSON.stringify({
    event_id: eventId,
    service_id: serviceId,
    csrf_token: csrfToken
  })
})

// ✅ Good: Clear, professional name
name: 'Haircuts'

// ❌ Bad: Lowercase, informal
name: 'haircut service'

// ❌ Bad: Too specific (should be in description)
name: 'Haircuts by Bob the Barber'

// ✅ Good: Helpful details
description: 'Professional haircut service provided by certified barbers. Includes trim and styling.'

// ❌ Bad: Redundant with name
description: 'Haircuts'

// ❌ Bad: Empty when context would help
description: ''

// ✅ Good: Based on actual resources
capacity: 20 // We have 2 barbers, 10 slots each

// ❌ Bad: Arbitrary or unrealistic
capacity: 1000 // We don't have 1000 slots

// ✅ Good: Service ready to use
status: 'active'

// ❌ Bad: Creating inactive service (clutter)
status: 'inactive'

-- Find potential duplicates
SELECT name, description, capacity, COUNT(*) as count
FROM services
GROUP BY name, description, capacity
HAVING COUNT(*) > 1;

// ✅ Good: Update affects all events
await db.updateService(serviceId, {
  description: 'Updated description with more details'
})

// ❌ Bad: Creating new service instead of updating
await db.insertService({
  name: 'Haircuts', // Duplicate!
  description: 'New description'
})

// ✅ Good: Preserves history
await db.updateService(serviceId, { status: 'inactive' })

// ❌ Bad: Loses all assignments
await db.deleteService(serviceId) // CASCADE deletes all assignments!

// ✅ Good: Check existing services first
const services = await db.getServices({ status: 'active' })
const existingService = services.find(s => s.name === 'Haircuts')

if (existingService) {
  // Use existing service
  await assignServiceToEvent(eventId, existingService.id)
} else {
  // Create new service (if admin)
  const newServiceId = await createService({ name: 'Haircuts', ... })
  await assignServiceToEvent(eventId, newServiceId)
}

// ❌ Bad: Always creating without checking
const newServiceId = await createService({ name: 'Haircuts', ... })

// ✅ Good: Reuse catalog service
await assignServiceToEvent(eventId, 'haircuts-service-id')

// ❌ Bad: Trying to create event-specific service (not supported)
// This requires admin privileges and creates global service
await createService({ name: 'Haircuts for Event A', ... })

// ✅ Good: Assign to parent (auto-copies to children)
await assignServiceToEvent(parentEventId, serviceId)

// ❌ Bad: Manually assigning to each child instance
for (const childEvent of childEvents) {
  await assignServiceToEvent(childEvent.id, serviceId)
}

409 Conflict: Service has already been assigned to this event

// ✅ Check if service is already assigned before attempting
const assignments = await fetch(`/api/services/assignments?event_id=${eventId}`)
  .then(r => r.json())

const alreadyAssigned = assignments.some(a => a.service_id === serviceId)

if (!alreadyAssigned) {
  await assignService(eventId, serviceId)
} else {
  console.log('Service already assigned')
}

403 Forbidden: Insufficient permissions to manage service catalog

// For catalog management
if (userRole === 'team_admin' || userRole === 'admin' || userRole === 'super_admin') {
  // User can create/update/delete services
  await createService({ name: 'New Service', ... })
} else {
  console.error('Service catalog management requires admin privileges')
}

// For event assignments
const canAssign = event.created_by === userId || userRole.includes('admin')
if (canAssign) {
  await assignService(eventId, serviceId)
} else {
  console.error('Must own event or be admin to assign services')
}

404 Not Found: Service does not exist

// ✅ Verify service exists before using
const service = await fetch(`/api/services/${serviceId}`)
  .then(r => r.ok ? r.json() : null)

if (service) {
  await assignService(eventId, serviceId)
} else {
  console.error('Service not found in catalog')
  // Browse catalog to find correct service
  const allServices = await fetch('/api/services').then(r => r.json())
  console.log('Available services:', allServices)
}

// ✅ Use inactive status instead of deleting
await db.updateService(serviceId, { status: 'inactive' })
// Service hidden from UI but assignments preserved

// ❌ Avoid: Deleting service
await db.deleteService(serviceId)
// CASCADE deletes all event_service_assignments!