CREATE OR REPLACE VIEW "RoutingFormResponse" AS
WITH attendees_agg AS (
  SELECT 
    a."bookingId",
    json_agg(
      json_build_object(
        'name', a.name,
        'timeZone', a."timeZone",
        'email', a.email
      )
    ) as attendees
  FROM "Attendee" a
  GROUP BY a."bookingId"
),
assignment_reasons_agg AS (
  SELECT 
    ar."bookingId",
    json_agg(
      json_build_object(
        'reasonString', ar."reasonString"
      )
    ) as reasons
  FROM "AssignmentReason" ar
  GROUP BY ar."bookingId"
)
SELECT 
  r.id,
  r.response,
  (
    SELECT jsonb_object_agg(
      key,
      CASE 
        WHEN jsonb_typeof(value->'value') = 'string' 
        THEN jsonb_build_object(
          'label', value->'label',
          'value', lower((value->>'value')::text)
        )
        ELSE value
      END
    )
    FROM jsonb_each(r.response::jsonb)
  ) as "responseLowercase",
  f.id as "formId",
  f.name as "formName",
  f."teamId" as "formTeamId",
  f."userId" as "formUserId",
  b.uid as "bookingUid",
  b.status as "bookingStatus",
  CASE b.status
    WHEN 'accepted' THEN 1
    WHEN 'pending' THEN 2
    WHEN 'awaiting_host' THEN 3
    WHEN 'cancelled' THEN 4
    WHEN 'rejected' THEN 5
  END as "bookingStatusOrder",
  b."createdAt" as "bookingCreatedAt",
  b."startTime" as "bookingStartTime",
  b."endTime" as "bookingEndTime",
  att.attendees as "bookingAttendees",
  u.id as "bookingUserId",
  u.name as "bookingUserName",
  u.email as "bookingUserEmail",
  u."avatarUrl" as "bookingUserAvatarUrl",
  COALESCE((ar.reasons->0)->>'reasonString', '') as "bookingAssignmentReason",
  LOWER(COALESCE((ar.reasons->0)->>'reasonString', '')) as "bookingAssignmentReasonLowercase",
  r."createdAt" as "createdAt"
FROM "App_RoutingForms_FormResponse" r
LEFT JOIN "App_RoutingForms_Form" f ON r."formId" = f.id
LEFT JOIN "Booking" b ON r."routedToBookingUid" = b.uid
LEFT JOIN attendees_agg att ON b.id = att."bookingId"
LEFT JOIN "users" u ON b."userId" = u.id
LEFT JOIN assignment_reasons_agg ar ON b.id = ar."bookingId";