leaderboardRoute.get('/top-creators', async (c) => {
  // --- Params & Validation ---
  const limit = Math.max(Number(c.req.query('limit')) || 50, 50) // does nothing for now
  const after = c.req.query('after')
  const sortBy = c.req.query('sortBy') as
    | 'volume'
    | 'fame'
    | 'price'
    | 'streak'
    | undefined
  const sortOrder =
    (c.req.query('sortOrder') as 'asc' | 'desc' | undefined) ?? 'desc'

  if (sortBy && !['volume', 'streak', 'fame', 'price'].includes(sortBy)) {
    return c.text(
      'Invalid sortBy parameter. Allowed values: volume, fame, price, streak',
      400
    )
  }
  if (sortOrder !== 'asc' && sortOrder !== 'desc') {
    return c.text('Invalid sortOrder parameter. Allowed values: asc, desc', 400)
  }

  const oneDayAgo = DateTime.now().minus({ days: 1 }).toUnixInteger()
  const keySubscriptionEventsWithinTheLastDay = db
    .select()
    .from(keySubscriptionEvent)
    .where(gte(keySubscriptionEvent.timestamp, oneDayAgo))
    .as('keySubscriptionEventsWithinTheLastDay')

  const volumeColSub =
    sql<string>`COALESCE(SUM(${keySubscriptionEventsWithinTheLastDay.hypeAmount}::numeric), 0::numeric)`.as(
      'volume'
    )
  const volumeSubquery = db
    .select({ userId: usersTable.id, volume: volumeColSub })
    .from(usersTable)
    .leftJoin(
      keySubscriptionEventsWithinTheLastDay,
      eq(
        lower(usersTable.address),
        lower(keySubscriptionEventsWithinTheLastDay.creator)
      )
    )
    .groupBy(usersTable.id)
    .as('volume_data')

  // new: ups as +1, downs as –1
  const fameSubquery = db
    .select({
      userId: fameTable.toUserId,
      fameCount: sql<number>`
      SUM(
        CASE 
          WHEN ${fameTable.isPositive} THEN  1 
          ELSE                            -1 
        END
      )::int
    `.as('fame_count')
    })
    .from(fameTable)
    .groupBy(fameTable.toUserId)
    .as('fame_data')

  const streakSubquery = db
    .select({
      streak: sql<number>`COALESCE(streak_data.streak, 0)`.as('streak'),
      userId: usersTable.id
    })
    .from(usersTable)
    .leftJoin(
      sql<{
        streak: number
      }>`LATERAL (
      WITH ordered AS (
        SELECT 
          date,
          LAG(date) OVER (ORDER BY date DESC) AS prev
        FROM ${userActivityDatesTable}
        WHERE user_id = ${usersTable.id} AND date <= CURRENT_DATE
      ),
      gaps AS (
        SELECT 
          date,
          CASE 
            WHEN prev IS NULL OR prev = date + INTERVAL '1 day' 
            THEN 0 
            ELSE 1 
          END AS is_gap
        FROM ordered
      ),
      running AS (
        SELECT 
          date,
          SUM(is_gap) OVER (ORDER BY date DESC) AS g
        FROM gaps
      )
      SELECT 
        -- Only count streak if the last date was within the last day
        CASE 
          WHEN MAX(date) >= CURRENT_DATE - INTERVAL '1 day' 
          THEN COUNT(*)::int 
          ELSE 0 
        END AS streak
      FROM running
      WHERE g = 0
    ) as streak_data`,
      sql`TRUE`
    )
    .as('streak_data')

  const fameExpr = sql<number>`COALESCE(fame_data.fame_count, 0)`
  const fameColSub = fameExpr.as('fame')

  const lastPriceExpr = sql`COALESCE(${creator.lastPrice}, '0')`
  const lastPriceCol = lastPriceExpr.mapWith(String).as('lastPrice')

  const streakExpr = sql<number>`COALESCE(streak_data.streak, 0)`
  const streakCol = streakExpr.as('streak')

  const conditions: (SQL<unknown> | undefined)[] = []

  if (after) {
    const cursorDecoded = after ? leaderboardCursorCodec.decode(after) : null
    if (cursorDecoded) {
      const cmp = sortOrder === 'desc' ? lt : gt
      if (sortBy === 'volume') {
        conditions.push(
          or(
            cmp(volumeSubquery.volume, cursorDecoded.value),
            and(
              eq(volumeSubquery.volume, cursorDecoded.value),
              cmp(usersTable.id, cursorDecoded.id)
            )
          )
        )
      } else if (sortBy === 'price') {
        conditions.push(
          or(
            cmp(lastPriceExpr, cursorDecoded.value),
            and(
              eq(lastPriceExpr, cursorDecoded.value),
              cmp(usersTable.id, cursorDecoded.id)
            )
          )
        )
      } else if (sortBy === 'fame') {
        conditions.push(
          or(
            cmp(fameExpr, cursorDecoded.value),
            and(
              eq(fameExpr, cursorDecoded.value),
              cmp(usersTable.id, cursorDecoded.id)
            )
          )
        )
      } else {
        conditions.push(
          or(
            cmp(streakExpr, cursorDecoded.value),
            and(
              eq(streakExpr, cursorDecoded.value),
              cmp(usersTable.id, cursorDecoded.id)
            )
          )
        )
      }
    }
  }

  const orderDirection = sortOrder === 'asc' ? asc : desc
  const orderByArgs: SQL[] = []
  const fameSortCol = fameExpr // Use expression for ordering
  const volumeSortCol = volumeSubquery.volume
  const streakSortCol = streakSubquery.streak
  const lastPriceSortCol = lastPriceCol

  if (sortBy === 'volume') {
    orderByArgs.push(orderDirection(volumeSortCol))
  } else if (sortBy === 'price') {
    orderByArgs.push(orderDirection(lastPriceSortCol))
  } else if (sortBy === 'fame') {
    orderByArgs.push(orderDirection(fameSortCol))
  } else {
    orderByArgs.push(orderDirection(streakSortCol))
  }
  orderByArgs.push(
    sortOrder === 'asc' ? asc(usersTable.id) : desc(usersTable.id)
  )

  const countQuery = db
    .select({ count: sql<number>`count(*)::int` })
    .from(usersTable)
    .innerJoin(
      buysUnlockedEvent,
      eq(lower(usersTable.address), lower(buysUnlockedEvent.creator))
    )

  // --- Main Data Query ---
  const dataQueryBuilder = db
    .select({
      ...getTableColumns(usersTable),
      volume: volumeSubquery.volume,
      lastPrice: lastPriceCol,
      fame: fameColSub,
      streak: streakCol,
      twitter: {
        twitterId: twitterProfilesTable.twitterId,
        username: twitterProfilesTable.username,
        name: twitterProfilesTable.name,
        pfpUrl: twitterProfilesTable.pfpUrl,
        description: twitterProfilesTable.description
      }
    })
    .from(usersTable)
    .leftJoin(streakSubquery, eq(usersTable.id, streakSubquery.userId))
    .leftJoin(volumeSubquery, eq(usersTable.id, volumeSubquery.userId))
    .leftJoin(fameSubquery, eq(usersTable.id, fameSubquery.userId))
    .leftJoin(creator, eq(lower(usersTable.address), lower(creator.address)))
    .leftJoin(
      twitterProfilesTable,
      eq(usersTable.id, twitterProfilesTable.userId)
    )
    .innerJoin(
      buysUnlockedEvent,
      eq(lower(usersTable.address), lower(buysUnlockedEvent.creator))
    )
    .where(and(...conditions))
    .orderBy(...orderByArgs)

  // Apply limit
  const dataQuery = dataQueryBuilder.limit(limit + 1)

  // --- Execute Queries ---
  const [usersResult, countResult] = await Promise.all([dataQuery, countQuery])

  const totalCount = countResult[0]?.count ?? 0
  const hasMore = usersResult.length > limit
  const items = usersResult.slice(0, limit)

  const hasNextPage = hasMore

  const pick = (item: (typeof items)[0]) =>
    sortBy === 'volume'
      ? item.volume
      : sortBy === 'price'
        ? item.lastPrice
        : sortBy === 'fame'
          ? item.fame
          : item.streak

  const startCursor = items[0]
    ? leaderboardCursorCodec.encode({ value: pick(items[0]), id: items[0].id })
    : null
  const endCursor = items[items.length - 1]
    ? leaderboardCursorCodec.encode({
        value: pick(items[items.length - 1]),
        id: items[items.length - 1].id
      })
    : null

  return c.json({
    data: items.map((item) => ({
      id: item.id,
      privyId: item.privyId,
      name: item.name,
      username: item.username,
      bio: item.bio,
      email: item.email,
      address: item.address,
      pfpUrl: item.pfpUrl,
      createdAt: item.createdAt,
      updatedAt: item.updatedAt,
      location: item.location,
      website: item.website,
      volume: item.volume,
      lastPrice: item.lastPrice,
      fame: item.fame,
      streak: item.streak,
      twitter: item.twitter
    })),
    pageInfo: {
      hasNextPage,
      hasPreviousPage: false,
      startCursor,
      endCursor,
      totalCount
    }
  })
})