import { json } from "@remix-run/node";
import prisma from "../db.server";

export const loader = async ({ request }) => {
  const url = new URL(request.url);

  // Parse and default query params
  const userId = Number(url.searchParams.get("user_id"));
  const status = url.searchParams.get("status");
  const query = url.searchParams.get("query") ?? "";
  const order = url.searchParams.get("orderBy") ?? "";
  const parPage = Number(url.searchParams.get("parPage")) || 20;
  const page = Math.max(Number(url.searchParams.get("page")) || 1, 1);
  const skip = parPage * (page - 1);

  // Keywords from query
  const keywords = query ? query.split(/\s+/).filter(Boolean) : [];

  // Ordering flags
  let orderByTitle = false;
  let orderDirection = "desc";
  if (order === "Title asc" || order === "Title desc") {
    orderByTitle = true;
    orderDirection = order === "Title asc" ? "asc" : "desc";
  } else if (order === "Product asc" || order === "Product desc") {
    // interpret "Product" ordering as created_at ordering
    orderDirection = order === "Product asc" ? "asc" : "desc";
  }

  // Status map and normalization
  const statusMap = {
    "already exist": "Already_Exist",
    "ready to import": "Ready_to_Import",
    "import in progress": "Import_in_progress",
    "imported": "Imported",
    "error": "error",
    "reimport in progress": "reimport_in_progress",
    "linked": "linked",
  };
  const normalizedStatus = status?.toLowerCase();
  const prismaStatus =
    normalizedStatus && normalizedStatus !== "all"
      ? statusMap[normalizedStatus]
      : undefined;

  // Build where clause (apply user_id, status, keywords)
  const where = {
    user_id: userId,
    ...(prismaStatus && { status: prismaStatus }),
    ...(keywords.length && {
      AND: keywords.map((word) => ({
        title: { contains: word.trim() },
      })),
    }),
  };

  // Total counts
  const totalCount = await prisma.products.count({ where });
  const totalProduct = await prisma.products.count({
    where: { user_id: userId },
  });

  // Fetch products with pagination and ordering (use 'where' filters)
  const products = await prisma.products.findMany({
    where,
    select: {
      product_id: true,
      title: true,
      block:true,
      status: true,
    },
    skip,
    take: parPage,
    orderBy: orderByTitle
      ? { title: orderDirection }
      : { created_at: orderDirection },
  });



  // Collect product IDs
  const productIds = products.map((p) => p.product_id);

  // Fetch all variants for these products (one or more per product)
  const variants = await prisma.product_variants.findMany({
    where: { product_id: { in: productIds } },
    select: {
      id: true,
      product_id: true,
      price: true,
      asin: true,
      sku: true,
      quantity: true,
      option1val: true,
      option2val: true,
      option3val: true,
    },
  });

  // Collect unique ASINs from variants (filter out falsy)
  const asins = [
    ...new Set(variants.map((v) => v.asin).filter((a) => a != null && a !== "")),
  ];

  // Fetch images matching those ASINs
  const images = asins.length
    ? await prisma.pc_images.findMany({
        where: { asin: { in: asins } },
        select: {
          asin: true,
          imageArr: true, // assuming this is an array of image URLs
        },
      })
    : [];

  // Map products -> pick first variant and the image that matches that variant's ASIN
  const productsWithData = products.map((p) => {
    const productVariants = variants.filter((v) => v.product_id === p.product_id);
    // choose the first variant (you could change selection logic here)
    const firstVariant = productVariants.length ? productVariants : [];
    const variantAsin = firstVariant[0]?.asin ?? null;

    // find pc_images entry for that asin
    const matchedImages = images.find((img) => img.asin === variantAsin);
    // pick first image url if imageArr is an array; otherwise return imageArr as-is
    const firstImage =
      Array.isArray(matchedImages?.imageArr) && matchedImages.imageArr.length
        ? matchedImages.imageArr[0]
        : matchedImages?.imageArr ?? null;

    return {
      ...p,
      variant: firstVariant,
      firstImage, // single image URL (or null)
    };
  });

  return json({ productsWithData, totalCount, totalProduct });
};
