import { router, protectedProcedure } from '@/src/trpc/trpc-index'
import { z } from 'zod'
import { ApiError } from '@/src/lib/api-error'
import { generateSignedUrlsFromS3Urls, scaffoldAssetUrl, claimUploadUrl, extractKeyFromPresignedUrl, deleteImageUtil } from '@/src/lib/s3-client'
import { scheduleStoreInitialization } from '@/src/stores/store-initializer'
import {
  getAllProducts as getAllProductsInDb,
  getProductById as getProductByIdInDb,
  deleteProduct as deleteProductInDb,
  toggleProductOutOfStock as toggleProductOutOfStockInDb,
  updateSlotProducts as updateSlotProductsInDb,
  getSlotProductIds as getSlotProductIdsInDb,
  getSlotsProductIds as getSlotsProductIdsInDb,
  getProductReviews as getProductReviewsInDb,
  respondToReview as respondToReviewInDb,
  getAllProductGroups as getAllProductGroupsInDb,
  createProductGroup as createProductGroupInDb,
  updateProductGroup as updateProductGroupInDb,
  deleteProductGroup as deleteProductGroupInDb,
  updateProductPrices as updateProductPricesInDb,
  checkProductExistsByName,
  checkUnitExists,
  createProduct as createProductInDb,
  createSpecialDealsForProduct,
  replaceProductTags,
  getProductImagesById,
  updateProduct as updateProductInDb,
  updateProductDeals,
  checkProductTagExistsByName,
  createProductTag as createProductTagInDb,
  updateProductTag as updateProductTagInDb,
  deleteProductTag as deleteProductTagInDb,
  getAllProductTagInfos as getAllProductTagInfosInDb,
  getProductTagInfoById as getProductTagInfoByIdInDb,
} from '@/src/dbService'
import type {
  AdminProduct,
  AdminSpecialDeal,
  AdminProductGroupsResult,
  AdminProductGroupResponse,
  AdminProductReviewsResult,
  AdminProductReviewResponse,
  AdminProductListResponse,
  AdminProductResponse,
  AdminDeleteProductResult,
  AdminToggleOutOfStockResult,
  AdminUpdateSlotProductsResult,
  AdminSlotProductIdsResult,
  AdminSlotsProductIdsResult,
  AdminUpdateProductPricesResult,
} from '@packages/shared'


export const productRouter = router({
  getProducts: protectedProcedure
    .query(async (): Promise<AdminProductListResponse> => {
      const products = await getAllProductsInDb()

      /*
      // Old implementation - direct DB query:
      const products = await db.query.productInfo.findMany({
        orderBy: productInfo.name,
        with: {
          unit: true,
          store: true,
        },
      });
      */

      const productsWithSignedUrls = await Promise.all(
        products.map(async (product) => ({
          ...product,
          images: await generateSignedUrlsFromS3Urls((product.images as string[]) || []),
        }))
      )

      return {
        products: productsWithSignedUrls,
        count: productsWithSignedUrls.length,
      }
    }),

  getProductById: protectedProcedure
    .input(z.object({
      id: z.number(),
    }))
    .query(async ({ input }): Promise<AdminProductResponse> => {
      const { id } = input;

      const product = await getProductByIdInDb(id)

      /*
      // Old implementation - direct DB queries:
      const product = await db.query.productInfo.findFirst({
        where: eq(productInfo.id, id),
        with: {
          unit: true,
        },
      });

      if (!product) {
        throw new ApiError("Product not found", 404);
      }

      // Fetch special deals for this product
      const deals = await db.query.specialDeals.findMany({
        where: eq(specialDeals.productId, id),
        orderBy: specialDeals.quantity,
      });

      // Fetch associated tags for this product
      const productTagsData = await db.query.productTags.findMany({
        where: eq(productTags.productId, id),
        with: {
          tag: true,
        },
      });

      // Generate signed URLs for product images
      const productWithSignedUrls = {
        ...product,
        images: await generateSignedUrlsFromS3Urls((product.images as string[]) || []),
        deals,
        tags: productTagsData.map(pt => pt.tag),
      };

      return {
        product: productWithSignedUrls,
      };
      */

      if (!product) {
        throw new ApiError('Product not found', 404)
      }

      const productWithSignedUrls = {
        ...product,
        images: await generateSignedUrlsFromS3Urls((product.images as string[]) || []),
      }

      return {
        product: productWithSignedUrls,
      }
    }),

  deleteProduct: protectedProcedure
    .input(z.object({
      id: z.number(),
    }))
    .mutation(async ({ input }): Promise<AdminDeleteProductResult> => {
      const { id } = input;

      const deletedProduct = await deleteProductInDb(id)

      /*
      // Old implementation - direct DB query:
      const [deletedProduct] = await db
        .delete(productInfo)
        .where(eq(productInfo.id, id))
        .returning();

      if (!deletedProduct) {
        throw new ApiError("Product not found", 404);
      }
      */

      if (!deletedProduct) {
        throw new ApiError('Product not found', 404)
      }

      // Reinitialize stores to reflect changes
      await scheduleStoreInitialization()

      return {
        message: 'Product deleted successfully',
      }
    }),

  toggleOutOfStock: protectedProcedure
    .input(z.object({
      id: z.number(),
    }))
    .mutation(async ({ input }): Promise<AdminToggleOutOfStockResult> => {
      const { id } = input;

      const updatedProduct = await toggleProductOutOfStockInDb(id)

      /*
      // Old implementation - direct DB queries:
      const product = await db.query.productInfo.findFirst({
        where: eq(productInfo.id, id),
      });

      if (!product) {
        throw new ApiError("Product not found", 404);
      }

      const [updatedProduct] = await db
        .update(productInfo)
        .set({
          isOutOfStock: !product.isOutOfStock,
        })
        .where(eq(productInfo.id, id))
        .returning();
      */

      if (!updatedProduct) {
        throw new ApiError('Product not found', 404)
      }

      await scheduleStoreInitialization()

      return {
        product: updatedProduct,
        message: `Product marked as ${updatedProduct.isOutOfStock ? 'out of stock' : 'in stock'}`,
      }
    }),

  createProduct: protectedProcedure
    .input(z.object({
      name: z.string().min(1, 'Name is required'),
      shortDescription: z.string().optional(),
      longDescription: z.string().optional(),
      unitId: z.number().min(1, 'Unit is required'),
      storeId: z.number().min(1, 'Store is required'),
      price: z.number().positive('Price must be positive'),
      marketPrice: z.number().optional(),
      incrementStep: z.number().optional().default(1),
      productQuantity: z.union([z.number(), z.string()]).optional().default(1),
      isSuspended: z.boolean().optional().default(false),
      isFlashAvailable: z.boolean().optional().default(false),
      flashPrice: z.number().optional(),
      uploadUrls: z.array(z.string()).optional().default([]),
      deals: z.array(z.object({
        quantity: z.number(),
        price: z.number(),
        validTill: z.string(),
      })).optional(),
      tagIds: z.array(z.number()).optional().default([]),
    }))
    .mutation(async ({ input }): Promise<{ product: AdminProduct; deals: AdminSpecialDeal[]; message: string }> => {
      const { name, shortDescription, longDescription, unitId, storeId, price, marketPrice, incrementStep, productQuantity, isSuspended, isFlashAvailable, flashPrice, uploadUrls, deals, tagIds } = input

      const existingProduct = await checkProductExistsByName(name.trim())
      if (existingProduct) {
        throw new ApiError('A product with this name already exists', 400)
      }

      const unitExists = await checkUnitExists(unitId)
      if (!unitExists) {
        throw new ApiError('Invalid unit ID', 400)
      }

      const imageKeys = uploadUrls.map(url => extractKeyFromPresignedUrl(url))

      const newProduct = await createProductInDb({
        name,
        shortDescription,
        longDescription,
        unitId,
        storeId,
        price: price.toString(),
        marketPrice: marketPrice?.toString(),
        incrementStep,
        productQuantity: productQuantity as any,
        isSuspended,
        isFlashAvailable,
        flashPrice: flashPrice?.toString(),
        images: imageKeys,
      })

      let createdDeals: AdminSpecialDeal[] = []
      if (deals && deals.length > 0) {
        createdDeals = await createSpecialDealsForProduct(newProduct.id, deals)
      }

      if (tagIds.length > 0) {
        await replaceProductTags(newProduct.id, tagIds)
      }

      if (uploadUrls.length > 0) {
        await Promise.all(uploadUrls.map(url => claimUploadUrl(url)))
      }

      await scheduleStoreInitialization()

      return {
        product: newProduct,
        deals: createdDeals,
        message: 'Product created successfully',
      }
    }),

  updateProduct: protectedProcedure
    .input(z.object({
      id: z.number(),
      name: z.string().min(1, 'Name is required'),
      shortDescription: z.string().optional(),
      longDescription: z.string().optional(),
      unitId: z.number().min(1, 'Unit is required'),
      storeId: z.number().min(1, 'Store is required'),
      price: z.number().positive('Price must be positive'),
      marketPrice: z.number().optional(),
      incrementStep: z.number().optional().default(1),
      productQuantity: z.union([z.number(), z.string()]).optional().default(1),
      isSuspended: z.boolean().optional().default(false),
      isFlashAvailable: z.boolean().optional().default(false),
      flashPrice: z.number().nullable().optional(),
      uploadUrls: z.array(z.string()).optional().default([]),
      imagesToDelete: z.array(z.string()).optional().default([]),
      deals: z.array(z.object({
        quantity: z.number(),
        price: z.number(),
        validTill: z.string(),
      })).optional(),
      tagIds: z.array(z.number()).optional().default([]),
    }))
    .mutation(async ({ input }): Promise<{ product: AdminProduct; message: string }> => {
      const { id, name, shortDescription, longDescription, unitId, storeId, price, marketPrice, incrementStep, productQuantity, isSuspended, isFlashAvailable, flashPrice, uploadUrls, imagesToDelete, deals, tagIds } = input

      const unitExists = await checkUnitExists(unitId)
      if (!unitExists) {
        throw new ApiError('Invalid unit ID', 400)
      }

      const currentImages = await getProductImagesById(id)
      if (!currentImages) {
        throw new ApiError('Product not found', 404)
      }

      let updatedImages = currentImages || []
      if (imagesToDelete.length > 0) {
        const imagesToRemove = updatedImages.filter(img => imagesToDelete.includes(img))
        await deleteImageUtil({ keys: imagesToRemove })
        updatedImages = updatedImages.filter(img => !imagesToRemove.includes(img))
      }

      const newImageKeys = uploadUrls.map(url => extractKeyFromPresignedUrl(url))
      const finalImages = [...updatedImages, ...newImageKeys]

      const updatedProduct = await updateProductInDb(id, {
        name,
        shortDescription,
        longDescription,
        unitId,
        storeId,
        price: price.toString(),
        marketPrice: marketPrice?.toString(),
        incrementStep,
        productQuantity: productQuantity as any,
        isSuspended,
        isFlashAvailable,
        flashPrice: flashPrice?.toString() ?? null,
        images: finalImages,
      })

      if (!updatedProduct) {
        throw new ApiError('Product not found', 404)
      }

      if (deals && deals.length > 0) {
        await updateProductDeals(id, deals)
      }

      if (tagIds.length > 0) {
        await replaceProductTags(id, tagIds)
      }

      if (uploadUrls.length > 0) {
        await Promise.all(uploadUrls.map(url => claimUploadUrl(url)))
      }

      await scheduleStoreInitialization()

      return {
        product: updatedProduct,
        message: 'Product updated successfully',
      }
    }),

  updateSlotProducts: protectedProcedure
    .input(z.object({
      slotId: z.string(),
      productIds: z.array(z.string()),
    }))
    .mutation(async ({ input }): Promise<AdminUpdateSlotProductsResult> => {
      const { slotId, productIds } = input;

      if (!Array.isArray(productIds)) {
        throw new ApiError("productIds must be an array", 400);
      }

      const result = await updateSlotProductsInDb(slotId, productIds)

      /*
      // Old implementation - direct DB queries:
      // Get current associations
      const currentAssociations = await db.query.productSlots.findMany({
        where: eq(productSlots.slotId, parseInt(slotId)),
        columns: {
          productId: true,
        },
      });

      const currentProductIds = currentAssociations.map(assoc => assoc.productId);
      const newProductIds = productIds.map((id: string) => parseInt(id));

      // Find products to add and remove
      const productsToAdd = newProductIds.filter(id => !currentProductIds.includes(id));
      const productsToRemove = currentProductIds.filter(id => !newProductIds.includes(id));

      // Remove associations for products that are no longer selected
      if (productsToRemove.length > 0) {
        await db.delete(productSlots).where(
          and(
            eq(productSlots.slotId, parseInt(slotId)),
            inArray(productSlots.productId, productsToRemove)
          )
        );
      }

      // Add associations for newly selected products
      if (productsToAdd.length > 0) {
        const newAssociations = productsToAdd.map(productId => ({
          productId,
          slotId: parseInt(slotId),
        }));

        await db.insert(productSlots).values(newAssociations);
      }

      // Reinitialize stores to reflect changes
      await scheduleStoreInitialization()

      return {
        message: "Slot products updated successfully",
        added: productsToAdd.length,
        removed: productsToRemove.length,
      };
      */

      await scheduleStoreInitialization()

      return {
        message: 'Slot products updated successfully',
        added: result.added,
        removed: result.removed,
      }
    }),

  getSlotProductIds: protectedProcedure
    .input(z.object({
      slotId: z.string(),
    }))
    .query(async ({ input }): Promise<AdminSlotProductIdsResult> => {
      const { slotId } = input;

      const productIds = await getSlotProductIdsInDb(slotId)

      /*
      // Old implementation - direct DB queries:
      const associations = await db.query.productSlots.findMany({
        where: eq(productSlots.slotId, parseInt(slotId)),
        columns: {
          productId: true,
        },
      });

      const productIds = associations.map(assoc => assoc.productId);

      return {
        productIds,
      };
      */

      return {
        productIds,
      }
    }),

  getSlotsProductIds: protectedProcedure
    .input(z.object({
      slotIds: z.array(z.number()),
    }))
    .query(async ({ input }): Promise<AdminSlotsProductIdsResult> => {
      const { slotIds } = input;

      if (!Array.isArray(slotIds)) {
        throw new ApiError("slotIds must be an array", 400);
      }

      const result = await getSlotsProductIdsInDb(slotIds)

      /*
      // Old implementation - direct DB queries:
      if (slotIds.length === 0) {
        return {};
      }

      // Fetch all associations for the requested slots
      const associations = await db.query.productSlots.findMany({
        where: inArray(productSlots.slotId, slotIds),
        columns: {
          slotId: true,
          productId: true,
        },
      });

      // Group by slotId
      const result = associations.reduce((acc, assoc) => {
        if (!acc[assoc.slotId]) {
          acc[assoc.slotId] = [];
        }
        acc[assoc.slotId].push(assoc.productId);
        return acc;
      }, {} as Record<number, number[]>);

      // Ensure all requested slots have entries (even if empty)
      slotIds.forEach(slotId => {
        if (!result[slotId]) {
          result[slotId] = [];
        }
      });

      return result;
      */

      return result
    }),

  getProductReviews: protectedProcedure
    .input(z.object({
      productId: z.number().int().positive(),
      limit: z.number().int().min(1).max(50).optional().default(10),
      offset: z.number().int().min(0).optional().default(0),
    }))
    .query(async ({ input }): Promise<AdminProductReviewsResult> => {
      const { productId, limit, offset } = input;

      const { reviews, totalCount } = await getProductReviewsInDb(productId, limit, offset)

      /*
      // Old implementation - direct DB queries:
      const reviews = await db
        .select({
          id: productReviews.id,
          reviewBody: productReviews.reviewBody,
          ratings: productReviews.ratings,
          imageUrls: productReviews.imageUrls,
          reviewTime: productReviews.reviewTime,
          adminResponse: productReviews.adminResponse,
          adminResponseImages: productReviews.adminResponseImages,
          userName: users.name,
        })
        .from(productReviews)
        .innerJoin(users, eq(productReviews.userId, users.id))
        .where(eq(productReviews.productId, productId))
        .orderBy(desc(productReviews.reviewTime))
        .limit(limit)
        .offset(offset);

      // Generate signed URLs for images
      const reviewsWithSignedUrls = await Promise.all(
        reviews.map(async (review) => ({
          ...review,
          signedImageUrls: await generateSignedUrlsFromS3Urls((review.imageUrls as string[]) || []),
          signedAdminImageUrls: await generateSignedUrlsFromS3Urls((review.adminResponseImages as string[]) || []),
        }))
      );

      // Check if more reviews exist
      const totalCountResult = await db
        .select({ count: sql`count(*)` })
        .from(productReviews)
        .where(eq(productReviews.productId, productId));

      const totalCount = Number(totalCountResult[0].count);
      const hasMore = offset + limit < totalCount;

      return { reviews: reviewsWithSignedUrls, hasMore };
      */

      const reviewsWithSignedUrls = await Promise.all(
        reviews.map(async (review) => ({
          ...review,
          signedImageUrls: await generateSignedUrlsFromS3Urls((review.imageUrls as string[]) || []),
          signedAdminImageUrls: await generateSignedUrlsFromS3Urls((review.adminResponseImages as string[]) || []),
        }))
      )

      const hasMore = offset + limit < totalCount

      return { reviews: reviewsWithSignedUrls, hasMore }
    }),

  respondToReview: protectedProcedure
    .input(z.object({
      reviewId: z.number().int().positive(),
      adminResponse: z.string().optional(),
      adminResponseImages: z.array(z.string()).optional().default([]),
      uploadUrls: z.array(z.string()).optional().default([]),
    }))
    .mutation(async ({ input }): Promise<AdminProductReviewResponse> => {
      const { reviewId, adminResponse, adminResponseImages, uploadUrls } = input;

      const updatedReview = await respondToReviewInDb(reviewId, adminResponse, adminResponseImages)

      /*
      // Old implementation - direct DB queries:
      const [updatedReview] = await db
        .update(productReviews)
        .set({
          adminResponse,
          adminResponseImages,
        })
        .where(eq(productReviews.id, reviewId))
        .returning();

      if (!updatedReview) {
        throw new ApiError('Review not found', 404);
      }

      // Claim upload URLs
      if (uploadUrls && uploadUrls.length > 0) {
        // const { claimUploadUrl } = await import('@/src/lib/s3-client');
        await Promise.all(uploadUrls.map(url => claimUploadUrl(url)));
      }

      return { success: true, review: updatedReview };
      */

      if (!updatedReview) {
        throw new ApiError('Review not found', 404)
      }

      if (uploadUrls && uploadUrls.length > 0) {
        await Promise.all(uploadUrls.map(url => claimUploadUrl(url)))
      }

      return { success: true, review: updatedReview }
    }),

  getGroups: protectedProcedure
    .query(async (): Promise<AdminProductGroupsResult> => {
      const groups = await getAllProductGroupsInDb()

      /*
      // Old implementation - direct DB queries:
      const groups = await db.query.productGroupInfo.findMany({
        with: {
          memberships: {
            with: {
              product: true,
            },
          },
        },
        orderBy: desc(productGroupInfo.createdAt),
      });
      */

      return {
        groups: groups.map(group => ({
          ...group,
          products: group.memberships.map((m: any) => ({
            ...(m.product as AdminProduct),
            images: (m.product.images as string[]) || null,
          })),
          productCount: group.memberships.length,
        })),
      }
    }),

  createGroup: protectedProcedure
    .input(z.object({
      group_name: z.string().min(1),
      description: z.string().optional(),
      product_ids: z.array(z.number()).default([]),
    }))
    .mutation(async ({ input }): Promise<AdminProductGroupResponse> => {
      const { group_name, description, product_ids } = input;

      const newGroup = await createProductGroupInDb(group_name, description, product_ids)

      /*
      // Old implementation - direct DB queries:
      const [newGroup] = await db
        .insert(productGroupInfo)
        .values({
          groupName: group_name,
          description,
        })
        .returning();

      if (product_ids.length > 0) {
        const memberships = product_ids.map(productId => ({
          productId,
          groupId: newGroup.id,
        }));

        await db.insert(productGroupMembership).values(memberships);
      }

      // Reinitialize stores to reflect changes
      await scheduleStoreInitialization()

      return {
        group: newGroup,
        message: 'Group created successfully',
      };
      */

      await scheduleStoreInitialization()

      return {
        group: newGroup,
        message: 'Group created successfully',
      }
    }),

  updateGroup: protectedProcedure
    .input(z.object({
      id: z.number(),
      group_name: z.string().optional(),
      description: z.string().optional(),
      product_ids: z.array(z.number()).optional(),
    }))
    .mutation(async ({ input }): Promise<AdminProductGroupResponse> => {
      const { id, group_name, description, product_ids } = input;

      const updatedGroup = await updateProductGroupInDb(id, group_name, description, product_ids)

      /*
      // Old implementation - direct DB queries:
      const updateData: any = {};
      if (group_name !== undefined) updateData.groupName = group_name;
      if (description !== undefined) updateData.description = description;

      const [updatedGroup] = await db
        .update(productGroupInfo)
        .set(updateData)
        .where(eq(productGroupInfo.id, id))
        .returning();

      if (!updatedGroup) {
        throw new ApiError('Group not found', 404);
      }

      if (product_ids !== undefined) {
        // Delete existing memberships
        await db.delete(productGroupMembership).where(eq(productGroupMembership.groupId, id));

        // Insert new memberships
        if (product_ids.length > 0) {
          const memberships = product_ids.map(productId => ({
            productId,
            groupId: id,
          }));

          await db.insert(productGroupMembership).values(memberships);
        }
      }

      // Reinitialize stores to reflect changes
      await scheduleStoreInitialization()

      return {
        group: updatedGroup,
        message: 'Group updated successfully',
      };
      */

      if (!updatedGroup) {
        throw new ApiError('Group not found', 404)
      }

      await scheduleStoreInitialization()

      return {
        group: updatedGroup,
        message: 'Group updated successfully',
      }
    }),

   deleteGroup: protectedProcedure
     .input(z.object({
       id: z.number(),
     }))
     .mutation(async ({ input }): Promise<AdminDeleteProductResult> => {
       const { id } = input;

       const deletedGroup = await deleteProductGroupInDb(id)

       /*
       // Old implementation - direct DB queries:
       // Delete memberships first
       await db.delete(productGroupMembership).where(eq(productGroupMembership.groupId, id));

       // Delete group
       const [deletedGroup] = await db
         .delete(productGroupInfo)
         .where(eq(productGroupInfo.id, id))
         .returning();

       if (!deletedGroup) {
         throw new ApiError('Group not found', 404);
       }

        // Reinitialize stores to reflect changes
        await scheduleStoreInitialization()

        return {
          message: 'Group deleted successfully',
        };
        */

        if (!deletedGroup) {
          throw new ApiError('Group not found', 404)
        }

        await scheduleStoreInitialization()

        return {
          message: 'Group deleted successfully',
        }
      }),

      updateProductPrices: protectedProcedure
       .input(z.object({
         updates: z.array(z.object({
           productId: z.number(),
           price: z.number().optional(),
           marketPrice: z.number().nullable().optional(),
           flashPrice: z.number().nullable().optional(),
           isFlashAvailable: z.boolean().optional(),
         })),
       }))
      .mutation(async ({ input }): Promise<AdminUpdateProductPricesResult> => {
        const { updates } = input;

       if (updates.length === 0) {
         throw new ApiError('No updates provided', 400)
       }

       const result = await updateProductPricesInDb(updates)

       /*
       // Old implementation - direct DB queries:
       if (updates.length === 0) {
         throw new ApiError('No updates provided', 400);
       }

       // Validate that all productIds exist
       const productIds = updates.map(u => u.productId);
       const existingProducts = await db.query.productInfo.findMany({
         where: inArray(productInfo.id, productIds),
         columns: { id: true },
       });

       const existingIds = new Set(existingProducts.map(p => p.id));
       const invalidIds = productIds.filter(id => !existingIds.has(id));

       if (invalidIds.length > 0) {
         throw new ApiError(`Invalid product IDs: ${invalidIds.join(', ')}`, 400);
       }

        // Perform batch update
        const updatePromises = updates.map(async (update) => {
          const { productId, price, marketPrice, flashPrice, isFlashAvailable } = update;
          const updateData: any = {};
          if (price !== undefined) updateData.price = price;
          if (marketPrice !== undefined) updateData.marketPrice = marketPrice;
          if (flashPrice !== undefined) updateData.flashPrice = flashPrice;
          if (isFlashAvailable !== undefined) updateData.isFlashAvailable = isFlashAvailable;

          return db
            .update(productInfo)
            .set(updateData)
            .where(eq(productInfo.id, productId));
        });

       await Promise.all(updatePromises);

         // Reinitialize stores to reflect changes
         await scheduleStoreInitialization()

         return {
           message: `Updated prices for ${updates.length} product(s)`,
           updatedCount: updates.length,
         };
         */

       if (result.invalidIds.length > 0) {
         throw new ApiError(`Invalid product IDs: ${result.invalidIds.join(', ')}`, 400)
       }

        await scheduleStoreInitialization()

        return {
          message: `Updated prices for ${result.updatedCount} product(s)`,
          updatedCount: result.updatedCount,
        }
      }),

      getProductTags: protectedProcedure
        .query(async (): Promise<{ tags: Array<{ id: number; tagName: string; tagDescription: string | null; imageUrl: string | null; isDashboardTag: boolean; relatedStores: unknown; createdAt: Date }>; message: string }> => {
          const tags = await getAllProductTagInfosInDb()

          const tagsWithSignedUrls = await Promise.all(
            tags.map(async (tag) => ({
              ...tag,
              imageUrl: tag.imageUrl ? scaffoldAssetUrl(tag.imageUrl) : null,
            }))
          )

          return {
            tags: tagsWithSignedUrls,
            message: 'Tags retrieved successfully',
          }
        }),

      getProductTagById: protectedProcedure
        .input(z.object({ id: z.number() }))
        .query(async ({ input }): Promise<{ tag: { id: number; tagName: string; tagDescription: string | null; imageUrl: string | null; isDashboardTag: boolean; relatedStores: unknown; createdAt: Date }; message: string }> => {
          const tag = await getProductTagInfoByIdInDb(input.id)

          if (!tag) {
            throw new ApiError('Tag not found', 404)
          }

          const tagWithSignedUrl = {
            ...tag,
            imageUrl: tag.imageUrl ? scaffoldAssetUrl(tag.imageUrl) : null,
          }

          return {
            tag: tagWithSignedUrl,
            message: 'Tag retrieved successfully',
          }
        }),

      createProductTag: protectedProcedure
        .input(z.object({
          tagName: z.string().min(1, 'Tag name is required'),
          tagDescription: z.string().optional(),
          imageUrl: z.string().optional().nullable(),
          isDashboardTag: z.boolean().optional().default(false),
          relatedStores: z.array(z.number()).optional().default([]),
          uploadUrls: z.array(z.string()).optional().default([]),
        }))
        .mutation(async ({ input }): Promise<{ tag: { id: number; tagName: string; tagDescription: string | null; imageUrl: string | null; isDashboardTag: boolean; relatedStores: unknown; createdAt: Date }; message: string }> => {
          const { tagName, tagDescription, imageUrl, isDashboardTag, relatedStores, uploadUrls } = input

          const existingTag = await checkProductTagExistsByName(tagName.trim())
          if (existingTag) {
            throw new ApiError('A tag with this name already exists', 400)
          }

          const createdTag = await createProductTagInDb({
            tagName: tagName.trim(),
            tagDescription,
            imageUrl: imageUrl ?? null,
            isDashboardTag,
            relatedStores,
          })

          if (uploadUrls.length > 0) {
            await Promise.all(uploadUrls.map((url) => claimUploadUrl(url)))
          }

          await scheduleStoreInitialization()

          const { products, ...createdTagInfo } = createdTag

          return {
            tag: {
              ...createdTagInfo,
              imageUrl: createdTagInfo.imageUrl ? scaffoldAssetUrl(createdTagInfo.imageUrl) : null,
            },
            message: 'Tag created successfully',
          }
        }),

      updateProductTag: protectedProcedure
        .input(z.object({
          id: z.number(),
          tagName: z.string().min(1).optional(),
          tagDescription: z.string().optional().nullable(),
          imageUrl: z.string().optional().nullable(),
          isDashboardTag: z.boolean().optional(),
          relatedStores: z.array(z.number()).optional(),
          uploadUrls: z.array(z.string()).optional().default([]),
        }))
        .mutation(async ({ input }): Promise<{ tag: { id: number; tagName: string; tagDescription: string | null; imageUrl: string | null; isDashboardTag: boolean; relatedStores: unknown; createdAt: Date }; message: string }> => {
          const { id, tagName, tagDescription, imageUrl, isDashboardTag, relatedStores, uploadUrls } = input

          const currentTag = await getProductTagInfoByIdInDb(id)

          if (!currentTag) {
            throw new ApiError('Tag not found', 404)
          }

          if (imageUrl !== undefined && imageUrl !== currentTag.imageUrl) {
            if (currentTag.imageUrl) {
              await deleteImageUtil({ keys: [currentTag.imageUrl] })
            }
          }

          const updatedTag = await updateProductTagInDb(id, {
            tagName: tagName?.trim(),
            tagDescription: tagDescription ?? undefined,
            imageUrl: imageUrl ?? undefined,
            isDashboardTag,
            relatedStores,
          })

          if (uploadUrls.length > 0) {
            await Promise.all(uploadUrls.map((url) => claimUploadUrl(url)))
          }

          await scheduleStoreInitialization()

          const { products, ...updatedTagInfo } = updatedTag

          return {
            tag: {
              ...updatedTagInfo,
              imageUrl: updatedTagInfo.imageUrl ? scaffoldAssetUrl(updatedTagInfo.imageUrl) : null,
            },
            message: 'Tag updated successfully',
          }
        }),

      deleteProductTag: protectedProcedure
        .input(z.object({ id: z.number() }))
        .mutation(async ({ input }): Promise<{ message: string }> => {
          const tag = await getProductTagInfoByIdInDb(input.id)

          if (!tag) {
            throw new ApiError('Tag not found', 404)
          }

          if (tag.imageUrl) {
            await deleteImageUtil({ keys: [tag.imageUrl] })
          }

          await deleteProductTagInDb(input.id)

          await scheduleStoreInitialization()

          return { message: 'Tag deleted successfully' }
        }),
   });
