SELECT ab.user_name, ai.item, ai.trait, ai.guild, ai.rarity, ai.timestamp, ab.bid_amount, ab.use_case  FROM auction_items ai
  LEFT JOIN (SELECT item_id, user_name, bid_amount, use_case FROM auction_bids WHERE user_id = "test_user_id" AND deleted == 0 GROUP BY item_id) ab
  ON ai.id = ab.item_id
  WHERE ai.guild = "Y" AND ai.rarity = "P"

SELECT ab.user_name, ai.item, ai.trait, ai.guild, ai.rarity, ai.timestamp, ab.bid_amount, ab.use_case  FROM auction_items ai
  LEFT JOIN (SELECT item_id, user_name, bid_amount, use_case FROM auction_bids WHERE user_id = "test_user_id" AND deleted == 0 GROUP BY item_id) ab
  ON ai.id = ab.item_id
  WHERE ai.guild = "Y" AND ai.rarity = "P"

  const items = await drizzle.run(sql`SELECT * FROM ${auctionItems} ai
      LEFT JOIN (SELECT * FROM ${auctionBids} WHERE ${auctionBids.userId} = ${userId} AND ${auctionBids.deleted} = 0 GROUP BY ${auctionBids.itemId}) ab
      ON ${auctionItems.id} = ${auctionBids.itemId}
      WHERE ${auctionItems.guild} = ${guild} AND ${auctionItems.rarity} = ${rarity}`);

  const items = await drizzle.run(sql`SELECT * FROM ${auctionItems} ai
      LEFT JOIN (SELECT * FROM ${auctionBids} WHERE ${auctionBids.userId} = ${userId} AND ${auctionBids.deleted} = 0 GROUP BY ${auctionBids.itemId}) ab
      ON ${auctionItems.id} = ${auctionBids.itemId}
      WHERE ${auctionItems.guild} = ${guild} AND ${auctionItems.rarity} = ${rarity}`);

  const bidSubQuery = drizzle
    .select({
      itemId: auctionBids.itemId,
      username: auctionBids.userName,
      bidAmount: auctionBids.bidAmount,
      useCase: auctionBids.useCase,
    })
    .from(auctionBids)
    .where(eq(auctionBids.userId, userId))
    .groupBy(auctionBids.itemId)
    .as("bidSubQuery");

  const items = (await drizzle
    .select({
      item: auctionItems.item,
      trait: auctionItems.trait,
      guild: auctionItems.guild,
      rarity: auctionItems.rarity,
      timestamp: auctionItems.expiration,
      username: bidSubQuery.username,
      bidAmount: bidSubQuery.bidAmount,
      useCase: bidSubQuery.useCase,
    })
    .from(auctionItems)
    .leftJoin(bidSubQuery, eq(auctionItems.id, bidSubQuery.itemId))
    .where(
      and(
        eq(auctionItems.guild, guild),
        eq(auctionItems.rarity, rarity),
        gt(auctionItems.expiration, currentTimestamp),
      ),
    )
    .orderBy(auctionItems.expiration)) as AuctionItem[];

  const bidSubQuery = drizzle
    .select({
      itemId: auctionBids.itemId,
      username: auctionBids.userName,
      bidAmount: auctionBids.bidAmount,
      useCase: auctionBids.useCase,
    })
    .from(auctionBids)
    .where(eq(auctionBids.userId, userId))
    .groupBy(auctionBids.itemId)
    .as("bidSubQuery");

  const items = (await drizzle
    .select({
      item: auctionItems.item,
      trait: auctionItems.trait,
      guild: auctionItems.guild,
      rarity: auctionItems.rarity,
      timestamp: auctionItems.expiration,
      username: bidSubQuery.username,
      bidAmount: bidSubQuery.bidAmount,
      useCase: bidSubQuery.useCase,
    })
    .from(auctionItems)
    .leftJoin(bidSubQuery, eq(auctionItems.id, bidSubQuery.itemId))
    .where(
      and(
        eq(auctionItems.guild, guild),
        eq(auctionItems.rarity, rarity),
        gt(auctionItems.expiration, currentTimestamp),
      ),
    )
    .orderBy(auctionItems.expiration)) as AuctionItem[];