restart increment for refNumber column starting from 1

export const orderItems = pgTable(
  "order_items",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    orderId: uuid("order_id").references(() => orders.id).notNull(),
    refNumber: integer("ref_number")
          .generatedAlwaysAsIdentity({ startWith: 1 }),
    productType: productTypeEnum("product_type").notNull(),
    color: colorEnum("color").notNull(),
    size: sizeEnum("size").notNull(),
    position: positionEnum("position").notNull(),
    quantity: integer("quantity").notNull(),
    nftData: json("nft_data").notNull().$type<ValidatedNFT>(),
    unitPrice: decimal("unit_price", { precision: 10, scale: 2 }).notNull(),
    createdAt: timestamp("created_at").defaultNow(),
  }
);

export async function createOrder({
  userId,
  email,
  items
}: CreateOrderParams): Promise<CreateOrderResult> {
  return db.transaction(async (tx) => {
    const [order] = await tx
      .insert(orders)
      .values({
        userId,
        customerEmail: email,
        subtotalAmount: 0,
        taxAmount: 0,
        shippingAmount: 0,
        totalAmount: 0,
        providerFees: 0,
      })
      .returning();

    if (!order) {
      throw new Error('Order does not exist');
    }

    /** amke sure item.nft is a valid jsonb object through req zod validation */
    const orderItems = await tx
      .insert(orderItemsTable)
      .values(
        items.map(item => ({
          orderId: order.id,
          productType: item.type,
          color: item.color,
          size: item.size,
          position: item.position,
          quantity: item.quantity,
          nftData: sql`${JSON.stringify(item.nft)}::jsonb`,
          unitPrice: 0,
          createdAt: new Date(),
        }))
      )
      .returning();

    return {
      order,
      orderItems
    };
  });
}  

export const orderItems = pgTable(
  "order_items",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    orderId: uuid("order_id").references(() => orders.id).notNull(),
    refNumber: integer("ref_number")
          .generatedAlwaysAsIdentity({ startWith: 1 }),
    productType: productTypeEnum("product_type").notNull(),
    color: colorEnum("color").notNull(),
    size: sizeEnum("size").notNull(),
    position: positionEnum("position").notNull(),
    quantity: integer("quantity").notNull(),
    nftData: json("nft_data").notNull().$type<ValidatedNFT>(),
    unitPrice: decimal("unit_price", { precision: 10, scale: 2 }).notNull(),
    createdAt: timestamp("created_at").defaultNow(),
  }
);

export async function createOrder({
  userId,
  email,
  items
}: CreateOrderParams): Promise<CreateOrderResult> {
  return db.transaction(async (tx) => {
    const [order] = await tx
      .insert(orders)
      .values({
        userId,
        customerEmail: email,
        subtotalAmount: 0,
        taxAmount: 0,
        shippingAmount: 0,
        totalAmount: 0,
        providerFees: 0,
      })
      .returning();

    if (!order) {
      throw new Error('Order does not exist');
    }

    /** amke sure item.nft is a valid jsonb object through req zod validation */
    const orderItems = await tx
      .insert(orderItemsTable)
      .values(
        items.map(item => ({
          orderId: order.id,
          productType: item.type,
          color: item.color,
          size: item.size,
          position: item.position,
          quantity: item.quantity,
          nftData: sql`${JSON.stringify(item.nft)}::jsonb`,
          unitPrice: 0,
          createdAt: new Date(),
        }))
      )
      .returning();

    return {
      order,
      orderItems
    };
  });
}