const Products = require("../models/Products");
const ProductVariation = require("../models/ProductVariation");
const ProductSubVariation = require("../models/ProductSubVariation");
const ProductMenu = require("../models/ProductMenu");
const DetailProductReviews = require("../models/DetailProductReviews");
const errorHandler = require("../util/errorHandler");
const Seller = require("../models/Seller");
const SellersCompany = require("../models/SellersCompany");
const computeDistance = require("../helpers/computeDistance");
const Customers = require("../models/Customers");
const ShopRadius = require("../models/ShopRadius");
const Sequelize = require("sequelize");
const {
  getSellersCompanyExcludes,
  getProductMenusExcludes,
} = require("../helpers/model-excludes-attributes/excludes");
const {
  sellerImageBuilder,
  sellerProductImageBuilder,
} = require("../helpers/image-src-builder");
const ProductImages = require("../models/ProductImages");
const ProductVideos = require("../models/ProductVideos");
const {
  sellerEnabledPurchaseOrders,
  checkPOItemStock,
} = require("../services/purchase-order.service");
const { generateCacheKey } = require("../helpers/common");

exports.createProducts = (req, res) => {
  const { data } = req.body;

  const { productName, productType, description, seller_id } = data;

  if (!req.files || !req.files.productPhoto) {
    return res.status(500).json({
      success: false,
      message: "Product photo is missing from the request.",
    });
  }

  const productPhoto = req.files.productPhoto;

  Seller.findOne({
    where: {
      id: seller_id,
    },
  })
    .then((seller) => {
      if (!seller) {
        throw errorHandler("Seller ID does not exist!", 404);
      }

      return ProductMenu.findOne({
        where: {
          id: productType,
        },
      });
    })
    .then((menu) => {
      if (!menu) {
        throw errorHandler("Menu ID does not exist!", 404);
      }

      return Products.findOne({
        where: {
          menu: productName,
          seller_id: seller_id,
        },
      });
    })
    .then((product) => {
      if (product) {
        throw errorHandler("Product ID already exists!", 401);
      }

      return Products.create({
        seller_id: seller_id,
        menu: productName,
        menu_id: productType,
        description: description,
        photo: productPhoto[0].filename || null,
        available: true,
      });
    })
    .then((createdProduct) => {
      res.status(200).json({
        success: true,
        message: "Product added successfully.",
        data: {
          id: createdProduct.id,
          productName: productName,
          productType: productType,
          description: description,
          seller_id: seller_id,
          photo: productPhoto[0].filename || null,
        },
      });
    })
    .catch((error) => {
      return res
        .status(error.statusCode || 500)
        .json({ success: false, message: error.message });
    });
};

exports.createVariation = async (req, res) => {
  try {
    const productId = req.params.productId;

    const { data } = req.body;

    console.log("Request body:", req.body);

    if (!data || typeof data !== "object") {
      console.log("Invalid data format. Data:", data);
      return res.status(400).json({
        success: false,
        message: "Invalid data format for variationData.",
      });
    }

    console.log("Extracted data:", data);

    const {
      variation,
      Description,
      QuantityLimit,
      Price,
      DiscountedPrice,
      HasSubVariation,
      IsAvailable,
      sku,
      Stocks,
    } = data;

    if (!req.files || !req.files.productVariationPhoto) {
      return res.status(400).json({
        success: false,
        message: "productVariationPhoto is missing from the request.",
      });
    }

    const productVariationPhoto = req.files.productVariationPhoto;

    const hasSubVariation =
      typeof HasSubVariation === "boolean" ? HasSubVariation : false;

    const variationImage = productVariationPhoto[0]
      ? productVariationPhoto[0].filename
      : null;

    const product = await Products.findByPk(productId);

    if (!product) {
      return res
        .status(404)
        .json({ success: false, message: "Product not found." });
    }

    const createdVariation = await ProductVariation.create({
      product_id: productId,
      variation: variation,
      description: Description,
      quantity_limit: QuantityLimit,
      price: Price,
      discounted_price: DiscountedPrice,
      hasSubVariation: hasSubVariation,
      isAvailable: IsAvailable !== undefined ? IsAvailable : true,
      variationImage: variationImage,
      sku: sku,
      stocks: Stocks,
    });

    await Products.update(
      { stocks: createdVariation.stocks },
      { where: { id: productId } },
    );

    const createdProduct = await Products.create({
      seller_id: product.seller_id,
      menu: product.menu,
      menu_id: product.menu_id,
      description: product.description,
      photo: product.photo,
      available: product.available,
      stocks: createdVariation.stocks,
      product_variation_id: createdVariation.id,
    });

    res.status(201).json({
      success: true,
      message: "Variation created successfully.",
      data: {
        product_variation_id: createdVariation.id,
        variation: variation,
        description: Description,
        price: Price,
        discounted_price: DiscountedPrice,
        quantity_limit: QuantityLimit,
        sku: sku,
        stocks: Stocks,
        hasSubVariation: hasSubVariation,
        isAvailable: IsAvailable !== undefined ? IsAvailable : true,
        variationImage: variationImage,
      },
    });
  } catch (error) {
    const statusCode = error.statusCode || 500;
    const message = "Error creating variation: " + error.message;
    res.status(statusCode).json({ success: false, message: message });
  }
};

exports.createSubVariation = async (req, res) => {
  try {
    const variationId = req.params.variationId;
    const { data } = req.body;

    const {
      custom,
      description,
      price,
      discounted_price,
      quantity_limit,
      stocks,
      sku,
      isAvailable,
    } = data;

    if (
      !req.files ||
      !req.files.productSubVariationPhoto ||
      !req.files.productSubVariationPhoto.length
    ) {
      return res.status(400).json({
        success: false,
        message: "Product photo is missing from the request.",
      });
    }

    const productSubVariationPhoto = req.files.productSubVariationPhoto;
    const photo =
      productSubVariationPhoto[0] && productSubVariationPhoto[0].filename
        ? productSubVariationPhoto[0].filename
        : null;

    const createdSubVariation = await ProductSubVariation.create({
      product_variation_id: variationId,
      custom: custom,
      description: description,
      price: price,
      discounted_price: discounted_price,
      quantity_limit: quantity_limit,
      stocks: stocks,
      sku: sku,
      isAvailable: isAvailable !== undefined ? isAvailable : true,
      photo: photo,
    });

    const productVariation = await ProductVariation.findByPk(variationId);

    if (!productVariation) {
      return res
        .status(404)
        .json({ success: false, message: "Product variation not found." });
    }

    const product = await Products.findByPk(productVariation.product_id);

    if (!product) {
      return res
        .status(404)
        .json({ success: false, message: "Product not found." });
    }

    const updatedStocks = product.stocks + stocks;
    await Products.update(
      { stocks: updatedStocks },
      { where: { id: product.id } },
    );

    const createdProduct = await Products.create({
      seller_id: product.seller_id,
      menu: product.menu,
      menu_id: product.menu_id,
      description: product.description,
      photo: product.photo,
      available: product.available,
      stocks: updatedStocks,
      product_sub_variation_id: createdSubVariation.id,
    });

    await Products.update(
      { product_sub_variation_id: createdSubVariation.id },
      { where: { id: createdProduct.id } },
    );

    res.status(201).json({
      success: true,
      message: "Sub-variation created successfully.",
      data: {
        product_sub_variation_id: createdSubVariation.id,
        custom: custom,
        description: description,
        price: price,
        discounted_price: discounted_price,
        quantity_limit: quantity_limit,
        stocks: stocks,
        sku: sku,
        isAvailable: isAvailable !== undefined ? isAvailable : true,
        photo: photo,
      },
    });
  } catch (error) {
    const statusCode = error.statusCode || 500;
    const message = "Error creating sub-variation: " + error.message;
    res.status(statusCode).json({ success: false, message: message });
  }
};

exports.updateProduct = (req, res) => {
  const { data } = req.body;

  const { productId, productName, productType, description, seller_id } = data;

  if (!req.files || !req.files.productPhoto) {
    return res.status(500).json({
      success: false,
      message: "Product photo is missing from the request.",
    });
  }

  const productPhoto = req.files.productPhoto;

  Seller.findOne({
    where: {
      id: seller_id,
    },
  })
    .then((seller) => {
      if (!seller) {
        throw errorHandler("Seller ID does not exist!", 404);
      }

      return ProductMenu.findOne({
        where: {
          id: productType,
        },
      });
    })
    .then((menu) => {
      if (!menu) {
        throw errorHandler("Menu ID does not exist!", 404);
      }

      return Products.findOne({
        where: {
          id: productId,
          seller_id: seller_id,
        },
      });
    })
    .then((product) => {
      if (!product) {
        throw errorHandler("Product ID does not exist!", 404);
      }

      product.menu = productName;
      product.menu_id = productType;
      product.description = description;
      product.photo = productPhoto[0].filename || null;

      return product.save();
    })
    .then(() => {
      res
        .status(200)
        .json({ success: true, message: "Product updated successfully." });
    })
    .catch((error) => {
      return res
        .status(error.statusCode || 500)
        .json({ success: false, message: error.message });
    });
};

exports.updateVariation = async (req, res) => {
  try {
    const variationId = req.params.variationId;
    console.log("Received variationId:", variationId);
    const { data } = req.body;

    const updatedVariation = await ProductVariation.findByPk(variationId);

    if (!updatedVariation) {
      throw errorHandler("Variation not found!", 404);
    }

    updatedVariation.variation = data.variation || updatedVariation.variation;
    updatedVariation.description =
      data.Description || updatedVariation.description;
    updatedVariation.quantity_limit =
      data.QuantityLimit || updatedVariation.quantity_limit;
    updatedVariation.price = data.Price || updatedVariation.price;
    updatedVariation.discounted_price =
      data.DiscountedPrice || updatedVariation.discounted_price;
    updatedVariation.hasSubVariation =
      data.HasSubVariation || updatedVariation.hasSubVariation;
    updatedVariation.isAvailable =
      data.IsAvailable || updatedVariation.isAvailable;
    updatedVariation.sku = data.sku || updatedVariation.sku;
    updatedVariation.stocks = data.Stocks || updatedVariation.stocks;

    if (
      req.files &&
      req.files.productVariationPhoto &&
      req.files.productVariationPhoto.length > 0
    ) {
      const productVariationPhoto = req.files.productVariationPhoto;
      updatedVariation.variationImage = productVariationPhoto[0].filename;
    }

    await updatedVariation.save();

    const productId = updatedVariation.product_id;
    const product = await Products.findByPk(productId);

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

    product.stocks = data.Stocks || product.stocks;
    await product.save();

    res.status(200).json({
      success: true,
      message: "Variation updated successfully.",
      data: updatedVariation,
    });
  } catch (error) {
    res
      .status(error.statusCode || 500)
      .json({ success: false, message: error.message });
  }
};

exports.updateSubVariation = async (req, res) => {
  try {
    const subVariationId = req.params.subVariationId;
    const { data } = req.body;

    const updatedSubVariation =
      await ProductSubVariation.findByPk(subVariationId);

    if (!updatedSubVariation) {
      throw errorHandler("Sub-variation not found!", 404);
    }

    updatedSubVariation.custom = data.custom || updatedSubVariation.custom;
    updatedSubVariation.description =
      data.description || updatedSubVariation.description;
    updatedSubVariation.price = data.price || updatedSubVariation.price;
    updatedSubVariation.discounted_price =
      data.discounted_price || updatedSubVariation.discounted_price;
    updatedSubVariation.quantity_limit =
      data.quantity_limit || updatedSubVariation.quantity_limit;
    updatedSubVariation.stocks = data.stocks || updatedSubVariation.stocks;
    updatedSubVariation.sku = data.sku || updatedSubVariation.sku;
    updatedSubVariation.isAvailable =
      data.isAvailable || updatedSubVariation.isAvailable;

    if (
      req.files &&
      req.files.productSubVariationPhoto &&
      req.files.productSubVariationPhoto.length > 0
    ) {
      const productSubVariationPhoto = req.files.productSubVariationPhoto;
      updatedSubVariation.photo = productSubVariationPhoto[0].filename;
    }

    await updatedSubVariation.save();

    const productVariationId = updatedSubVariation.product_variation_id;
    const productVariation =
      await ProductVariation.findByPk(productVariationId);

    if (!productVariation) {
      throw errorHandler("Product Variation not found!", 404);
    }

    productVariation.stocks = data.stocks || productVariation.stocks;
    await productVariation.save();

    const productId = productVariation.product_id;
    const product = await Products.findByPk(productId);

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

    const totalStocks = product.stocks + productVariation.stocks;

    product.stocks = totalStocks;
    await product.save();

    res.status(200).json({
      success: true,
      message: "Sub-variation updated successfully.",
      data: updatedSubVariation,
    });
  } catch (error) {
    res
      .status(error.statusCode || 500)
      .json({ success: false, message: error.message });
  }
};

exports.deleteProducts = async (req, res, next) => {
  try {
    const { productId } = req.params;

    const product = await Products.findOne({
      where: {
        id: productId,
      },
    });

    if (!product) {
      return res
        .status(404)
        .json({ success: false, message: "Product ID does not exist!" });
    }

    const variations = await ProductVariation.findAll({
      where: {
        product_id: product.id,
      },
      attributes: ["id"],
    });

    const variationIds = variations.map((variation) => variation.id);

    await ProductSubVariation.destroy({
      where: {
        product_variation_id: variationIds,
      },
    });

    await ProductVariation.destroy({
      where: {
        product_id: product.id,
      },
    });

    await product.destroy();

    res.status(204).send();
  } catch (err) {
    next(err);
  }
};

exports.findProductsById = async (req, res, next) => {
  const { productId } = req.params;

  try {
    const product = await Products.findOne({
      where: {
        id: productId,
        available: 1,
        product_status: true,
      },
      include: [
        {
          model: ProductVariation,
          as: "product_variations",
          include: [
            {
              model: ProductSubVariation,
              as: "ProductSubVariations",
            },
          ],
        },
        {
          model: ProductImages,
          as: "product_images",
        },
        {
          model: ProductVideos,
          as: "product_videos",
        },
        {
          model: SellersCompany,
          as: "seller",
          attributes: {
            exclude: getSellersCompanyExcludes(),
          },
        },
        {
          model: ProductMenu,
          as: "menus",
          attributes: {
            exclude: getProductMenusExcludes(),
          },
        },
      ],
    });

    if (!product) {
      return res.status(404).json({
        success: false,
        message: `Product with ID ${productId} not found.`,
      });
    }

    // Fetch average rating from DetailProductReviews
    const averageRating = await DetailProductReviews.findOne({
      attributes: [
        [
          DetailProductReviews.sequelize.fn(
            "COALESCE",
            DetailProductReviews.sequelize.fn(
              "AVG",
              DetailProductReviews.sequelize.col("rate"),
            ),
            0,
          ),
          "avgRating",
        ],
      ],
      where: { product_id: productId },
    });

    const productImages = [];
    const productVideos = [];
    if (
      product.product_images &&
      Array.isArray(product.product_images) &&
      product.product_images.length > 0
    ) {
      for (const image of product.product_images) {
        const i = {
          id: image.id,
          url: sellerProductImageBuilder(image.file_name, true),
          is_thumbnail: image.is_thumbnail,
        };

        productImages.push(i);
      }
    }
    if (
      product.product_videos &&
      Array.isArray(product.product_videos) &&
      product.product_videos.length > 0
    ) {
      for (const vid of product.product_videos) {
        const i = {
          id: vid.id,
          url: sellerProductImageBuilder(vid.file_name, true),
        };

        productVideos.push(i);
      }
    }

    const sellerEnablePO = await sellerEnabledPurchaseOrders(product.seller_id);

    let itemStock = parseFloat(product.stocks);

    if (sellerEnablePO) {
      const poItemStock = await checkPOItemStock(product.id);
      itemStock = poItemStock.availableStock;
    }

    const result = {
      seller: {
        id: product.seller.id,
        company_name:
          product.seller.shop_name === "" || product.seller.shop_name === null
            ? product.seller.company_name
            : product.seller.shop_name,
        seller_banner_image: product.seller.seller_banner_image
          ? sellerImageBuilder(
              product.seller.seller_banner_image,
              product.seller.seller_dashboard_src,
            )
          : null,
        seller_logo: product.seller.seller_logo
          ? sellerImageBuilder(
              product.seller.seller_logo,
              product.seller.seller_dashboard_src,
            )
          : null,
        url_handle: product.seller.url_handle,
        description: product.seller.company_description,
      },
      productName: product.menu,
      variant: product.variant,
      description: product.description,
      price: product.price,
      discountedPrice: product.discounted_price,
      photo: sellerProductImageBuilder(
        product.photo,
        product.seller_dashboard_src,
      ),
      product_images: productImages,
      product_videos: productVideos,
      quantityLimit: product.quantity_limit,
      stocks: itemStock,
      available: product.available,
      category: product.menus,
      seller_dashboard_src: product.seller_dashboard_src,
      avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
      variations: product.product_variations.map((variation) => ({
        variationId: variation.id,
        variation: variation.variation,
        description: variation.description,
        quantityLimit: variation.quantity_limit,
        price: variation.price,
        discountedPrice: variation.discounted_price,
        hasSubVariation: variation.hasSubVariation,
        isAvailable: variation.isAvailable,
        variationImage: sellerProductImageBuilder(
          variation.variationImage,
          true,
        ),
        sku: variation.sku,
        stocks: variation.stocks,
        subVariations: variation.ProductSubVariations.map((subVariation) => ({
          subVariationId: subVariation.id,
          custom: subVariation.custom,
          description: subVariation.description,
          price: subVariation.price,
          discountedPrice: subVariation.discounted_price,
          quantityLimit: subVariation.quantity_limit,
          stocks: subVariation.stocks,
          sku: subVariation.sku,
          isAvailable: subVariation.isAvailable,
          photo: sellerProductImageBuilder(subVariation.photo, true),
        })),
      })),
    };

    if (product.photo === null || product.photo === "") {
      const thumbnailImage = productImages.find((p) => p.is_thumbnail);
      result.photo =
        thumbnailImage && thumbnailImage.url ? thumbnailImage.url : "";
    }
    res.status(200).json({ success: true, result });
  } catch (err) {
    next(err);
  }
};

exports.findProductsByAvailability = async (req, res, next) => {
  try {
    const { page, limit } = req.pagination;
    const offset = (page - 1) * limit;

    const productsResult = await Products.findAndCountAll({
      where: {
        available: 1,
        product_status: true,
      },
      include: [
        {
          model: ProductVariation,
          as: "product_variations",
          include: [
            {
              model: ProductSubVariation,
              as: "ProductSubVariations",
            },
          ],
        },
        {
          model: SellersCompany,
          as: "seller",
          attributes: {
            exclude: getSellersCompanyExcludes(),
          },
        },
      ],
      offset,
      limit,
    });

    const products = productsResult.rows;
    const totalCount = productsResult.count;

    const productsArray = await Promise.all(
      products.map(async (product) => {
        const id = product.menu_id; // Assuming menu_id is the product ID

        // Fetch average rating from DetailProductReviews
        const averageRating = await DetailProductReviews.findOne({
          attributes: [
            [
              DetailProductReviews.sequelize.fn(
                "COALESCE",
                DetailProductReviews.sequelize.fn(
                  "AVG",
                  DetailProductReviews.sequelize.col("rate"),
                ),
                0,
              ),
              "avgRating",
            ],
          ],
          where: { product_id: id },
        });

        const seller = product.seller;
        const sellerData = seller
          ? {
              id: seller.id,
              company_name:
                seller.shop_name === "" || seller.shop_name === null
                  ? seller.company_name
                  : seller.shop_name,
              seller_banner_image: seller.seller_banner_image
                ? sellerImageBuilder(
                    seller.seller_banner_image,
                    seller.seller_dashboard_src,
                  )
                : null,
            }
          : null;

        return {
          productId: product.menu_id,
          productName: product.menu,
          productType: product.variant,
          description: product.description,
          price: product.price,
          discountedPrice: product.discounted_price,
          productPhoto: sellerImageBuilder(
            product.photo,
            product.seller_dashboard_src,
          ),
          quantityLimit: product.quantity_limit,
          stocks: product.stocks,
          available: product.available,
          avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
          seller_dashboard_src: product.seller_dashboard_src,
          variations: product.product_variations.map((variation) => ({
            variationId: variation.id,
            variation: variation.variation,
            description: variation.description,
            quantityLimit: variation.quantity_limit,
            price: variation.price,
            discountedPrice: variation.discounted_price,
            hasSubVariation: variation.hasSubVariation,
            isAvailable: variation.isAvailable,
            variationImage: sellerImageBuilder(variation.variationImage, true),
            sku: variation.sku,
            stocks: variation.stocks,
            subVariations: variation.ProductSubVariations.map(
              (subVariation) => ({
                subVariationId: subVariation.id,
                custom: subVariation.custom,
                description: subVariation.description,
                price: subVariation.price,
                discountedPrice: subVariation.discounted_price,
                quantityLimit: subVariation.quantity_limit,
                stocks: subVariation.stocks,
                sku: subVariation.sku,
                isAvailable: subVariation.isAvailable,
                photo: sellerImageBuilder(subVariation.photo, true),
              }),
            ),
          })),
          seller: sellerData,
        };
      }),
    );

    const totalPages = Math.ceil(totalCount / limit);
    const hasNextPage = (page - 1) * limit + products.length < totalCount;
    const hasPreviousPage = page > 1;

    res.status(200).json({
      success: true,
      productsArray,
      pagination: {
        totalPages,
        currentPage: page,
        hasNextPage,
        hasPreviousPage,
      },
    });
  } catch (err) {
    next(err);
  }
};

// exports.findProductsPerSeller = async (req, res, next) => {
//   const { page, limit } = req.pagination;
//   const startIndex = (page - 1) * limit;
//   const endIndex = page * limit;

//   const { sellerId } = req.params;

//   try {
//     const seller = await SellersCompany.findByPk(sellerId);

//     if (!seller) {
//       return res
//         .status(404)
//         .json({ success: false, message: "Seller not found" });
//     }

//     const products = await Products.findAll({
//       offset: startIndex,
//       limit: limit,
//       where: {
//         seller_id: sellerId,
//       },
//       include: [
//         {
//           model: ProductVariation,
//           as: "product_variations",
//           include: [
//             {
//               model: ProductSubVariation,
//               as: "ProductSubVariations",
//             },
//           ],
//         },
//         {
//           model: SellersCompany,
//           as: "seller",
//           where: {
//             id: sellerId,
//           },
//         },
//       ],
//     });

//     const totalProducts = await Products.count({
//       where: {
//         seller_id: sellerId,
//       },
//     });

//     const totalPages = Math.ceil(totalProducts / limit);
//     const hasNextPage = endIndex < totalProducts;
//     const hasPreviousPage = startIndex > 0;

//     const productsArray = await Promise.all(
//       products.map(async (product) => {
//         const id = product.menu_id; // Assuming menu_id is the product ID

//         // Fetch average rating from DetailProductReviews
//         const averageRating = await DetailProductReviews.findOne({
//           attributes: [
//             [
//               DetailProductReviews.sequelize.fn(
//                 "COALESCE",
//                 DetailProductReviews.sequelize.fn(
//                   "AVG",
//                   DetailProductReviews.sequelize.col("rate")
//                 ),
//                 0
//               ),
//               "avgRating",
//             ],
//           ],
//           where: { product_id: id },
//         });

//         return {
//           productId: product.menu_id,
//           seller: {
//             id: seller.id,
//             name: seller.company_name,
//             logo: `https://buildhub.ph/img/seller/${product.seller.seller_logo}`,
//             latitude: seller.latitude,
//             longitude: seller.longitude,
//             address: seller.address,
//             // isAuthorizedWarehouse: seller.isAuthorizedWarehouse,
//           },
//           productName: product.menu,
//           productType: product.variant,
//           description: product.description,
//           price: product.price,
//           discountedPrice: product.discounted_price,
//           productPhoto: `https://buildhub.ph/img/products/${product.photo}`,
//           quantityLimit: product.quantity_limit,
//           stocks: product.stocks,
//           available: product.available,
//           avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
//           variations: product.product_variations.map((variation) => ({
//             variationId: variation.id,
//             variation: variation.variation,
//             description: variation.description,
//             quantityLimit: variation.quantity_limit,
//             price: variation.price,
//             discountedPrice: variation.discounted_price,
//             hasSubVariation: variation.hasSubVariation,
//             isAvailable: variation.isAvailable,
//             variationImage: `public/product-variation/${variation.variationImage}`,
//             sku: variation.sku,
//             stocks: variation.stocks,
//             subVariations: variation.ProductSubVariations.map(
//               (subVariation) => ({
//                 subVariationId: subVariation.id,
//                 custom: subVariation.custom,
//                 description: subVariation.description,
//                 price: subVariation.price,
//                 discountedPrice: subVariation.discounted_price,
//                 quantityLimit: subVariation.quantity_limit,
//                 stocks: subVariation.stocks,
//                 sku: subVariation.sku,
//                 isAvailable: subVariation.isAvailable,
//                 photo: `public/product-sub-variation/${subVariation.photo}`,
//               })
//             ),
//           })),
//         };
//       })
//     );

//     res.status(200).json({
//       success: true,
//       productsArray,
//       pagination: {
//         totalProducts,
//         totalPages,
//         hasNextPage,
//         hasPreviousPage,
//       },
//     });
//   } catch (err) {
//     next(err);
//   }
// };

exports.findProductsByCategory = async (req, res, next) => {
  try {
    const { categoryId } = req.params; // menu id

    let isLoggedIn = req.loggedIn;
    let shopRadius;

    if (isLoggedIn) {
      const radius = await ShopRadius.findAll();
      if (radius.length > 0) {
        const { dataValues } = radius[0];
        if (dataValues) {
          shopRadius = dataValues.radius;
        }
      }
    }

    const radiusLiteralSql = isLoggedIn
      ? `
    6371 * 2 * ASIN(
        SQRT(
          POWER(SIN((${req.latitude} - latitude) * PI()/180 / 2), 2) +
          COS(${req.latitude} * PI()/180) * COS(latitude * PI()/180) *
          POWER(SIN((${req.longitude} - longitude) * PI()/180 / 2), 2)
        )
      ) <= ${shopRadius}
    `
      : "";

    const sellerCompanies = await SellersCompany.findAll({
      where: Sequelize.literal(radiusLiteralSql),
    });

    const filteredSellerCompanyIds = sellerCompanies.map(
      (sellerCompany) => sellerCompany.id,
    );

    const products = await Products.findAll({
      where: {
        menu_id: categoryId,
        available: true,
        product_status: true,
        seller_id: {
          [Sequelize.Op.in]: filteredSellerCompanyIds,
        },
      },
      include: [
        {
          model: SellersCompany,
          as: "seller",
        },
        {
          model: ProductVariation,
          as: "product_variations",
          include: [
            {
              model: ProductSubVariation,
              as: "ProductSubVariations",
            },
          ],
        },
      ],
    });

    if (!products) {
      return errorHandler("Category ID does not exist!", 404);
    }

    const productsArray = await Promise.all(
      products.map(async (product) => {
        const id = product.id;

        // Fetch average rating from DetailProductReviews
        const averageRating = await DetailProductReviews.findOne({
          attributes: [
            [
              DetailProductReviews.sequelize.fn(
                "COALESCE",
                DetailProductReviews.sequelize.fn(
                  "AVG",
                  DetailProductReviews.sequelize.col("rate"),
                ),
                0,
              ),
              "avgRating",
            ],
          ],
          where: { product_id: id },
        });

        // Include seller_banner_image from Seller model
        const seller = product.seller;
        const sellerData = seller
          ? {
              id: seller.id,
              name:
                seller.shop_name === "" || seller.shop_name === null
                  ? seller.company_name
                  : seller.shop_name,
              sellerLogo: seller.seller_logo,
              seller_banner_image: seller.seller_banner_image
                ? sellerImageBuilder(
                    seller.seller_banner_image,
                    seller.seller_dashboard_src,
                  )
                : null,
              latitude: seller.lat,
              longitude: seller.long,
            }
          : null;

        return {
          id: product.id,
          seller: sellerData,
          menu: product.menu,
          variant: product.variant,
          description: product.description,
          price: product.price,
          discountedPrice: product.discounted_price,
          photo: sellerProductImageBuilder(
            product.photo,
            product.seller_dashboard_src,
          ),
          quantityLimit: product.quantity_limit,
          stocks: product.stocks,
          available: product.available,
          sku: product.sku,
          seller_dashboard_src: product.seller_dashboard_src,
          variations:
            product.product_variations.length > 0
              ? product.product_variations.map((product_variations) => ({
                  id: product_variations.id,
                  variation: product_variations.variation,
                  description: product_variations.description,
                  quantityLimit: product_variations.quantity_limit,
                  price: product_variations.price,
                  discountedPrice: product_variations.discounted_price,
                  isAvailable: product_variations.isAvailable,
                  image: sellerProductImageBuilder(
                    product_variations.variationImage,
                    true,
                  ),
                  sku: product_variations.sku,
                  stocks: product_variations.stocks,
                  hasSubVariation: product_variations.hasSubVariation,

                  subVariations:
                    product_variations.ProductSubVariations.length > 0
                      ? product_variations.ProductSubVariations.map(
                          (product_subvariations) => ({
                            id: product_subvariations.id,
                            custom: product_subvariations.custom,
                            description: product_subvariations.description,
                            price: product_subvariations.price,
                            discountedPrice:
                              product_subvariations.discounted_price,
                            quantityLimit: product_subvariations.quantity_limit,
                            stocks: product_subvariations.stocks,
                            sku: product_subvariations.sku,
                            isAvailable: product_subvariations.isAvailable,
                            photo: sellerProductImageBuilder(
                              product_subvariations.photo,
                              true,
                            ),
                          }),
                        )
                      : null,
                }))
              : null,
          avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
        };
      }),
    );

    res.status(200).json({ success: true, productsArray });
  } catch (err) {
    next(err);
  }
};

const PRODUCTS_PER_SELLER = 2;
exports.findAllProducts = async (req, res, next) => {
  try {
    const { page, limit } = req.pagination;
    const offset = (page - 1) * limit;
    const { mid = null } = req.query;

    const isLoggedIn = req.loggedIn;
    let shopRadius = null;

    if (isLoggedIn) {
      const radius = await ShopRadius.findOne({ attributes: ["radius"] });
      shopRadius = radius?.radius || null;
    }

    const sellerWhere = {
      status: true,
      ...(mid && { microsite_handle_id: mid }),
    };

    let distanceCondition = "";
    if (isLoggedIn && shopRadius && req.latitude && req.longitude) {
      distanceCondition = `
        (6371 * 2 * ASIN(
          SQRT(
            POWER(SIN((${req.latitude} - seller.latitude) * PI()/180 / 2), 2) +
            COS(${req.latitude} * PI()/180) * COS(seller.latitude * PI()/180) *
            POWER(SIN((${req.longitude} - seller.longitude) * PI()/180 / 2), 2)
          )
        )) <= ${shopRadius}
      `;
    }

    const cacheKey = generateCacheKey("findAllProducts", {
      page,
      limit,
      offset,
      isLoggedIn,
      shopRadius,
      latitude: req.latitude,
      longitude: req.longitude,
      mid,
    });

    if (req.redisClient && req.redisClient.isConnected) {
      const cachedData = await req.redisClient.get(cacheKey);
      if (cachedData) {
        console.log("Serving find all products from cache");
        return res.status(200).json(JSON.parse(cachedData));
      }
    }

    /**
     * STEP 1: Subquery that ranks products PER SELLER
     */
    const rankedProducts = await Products.findAll({
      attributes: {
        include: [
          [
            Sequelize.literal(`
              ROW_NUMBER() OVER (
                PARTITION BY products.seller_id
                ORDER BY products.id DESC
              )
            `),
            "seller_rank",
          ],
        ],
      },
      where: {
        available: 1,
        product_status: true,
      },
      include: [
        {
          model: SellersCompany,
          as: "seller",
          required: true,
          where: {
            ...sellerWhere,
            ...(distanceCondition && {
              [Sequelize.Op.and]: Sequelize.literal(distanceCondition),
            }),
          },
          attributes: [
            "id",
            "shop_name",
            "company_name",
            "seller_banner_image",
            "seller_logo",
            "latitude",
            "longitude",
            "seller_dashboard_src",
          ],
        },
        {
          model: ProductVariation,
          as: "product_variations",
          include: [
            {
              model: ProductSubVariation,
              as: "ProductSubVariations",
            },
          ],
        },
        {
          model: ProductImages,
          as: "product_images",
        },
      ],
      subQuery: false,
      raw: false,
    });

    /**
     * STEP 2: Enforce product-per-seller cap
     */
    const filteredProducts = rankedProducts
      .filter((p) => p.get("seller_rank") <= PRODUCTS_PER_SELLER)
      .slice(offset, offset + limit);

    /**
     * STEP 3: Format response
     */
    const productList = filteredProducts.map((product) => {
      const thumbnail = product.photo
        ? sellerProductImageBuilder(
            product.photo,
            product.seller.seller_dashboard_src,
          )
        : product.product_images?.find((img) => img.is_thumbnail)
          ? sellerProductImageBuilder(
              product.product_images.find((img) => img.is_thumbnail).file_name,
              true,
            )
          : "";

      return {
        id: product.id,
        productName: product.menu,
        description: product.description,
        price: product.price,
        discountedPrice: product.discounted_price,
        photo: thumbnail,
        stocks: product.stocks,
        seller: product.seller,
        avgRating: 5,
        variations: product.product_variations,
      };
    });

    const apiResponse = {
      success: true,
      products: productList,
      pagination: {
        currentPage: page,
        hasMore: filteredProducts.length === limit,
      },
    };

    if (req.redisClient && req.redisClient.isConnected) {
      console.log("cacheKey: ", cacheKey);
      await req.redisClient.set(cacheKey, JSON.stringify(apiResponse), 60 * 60);
    }
    res.status(200).json(apiResponse);
  } catch (err) {
    next(err);
  }
};

function checkRadiusCondition(product, req, shopRadius) {
  const lat1 = req.latitude;
  const lon1 = req.longitude;
  const lat2 = product.seller.latitude;
  const lon2 = product.seller.longitude;

  const R = 6371; // Radius of the earth in km
  const dLat = deg2rad(lat2 - lat1);
  const dLon = deg2rad(lon2 - lon1);
  const a =
    Math.sin(dLat / 2) * Math.sin(dLat / 2) +
    Math.cos(deg2rad(lat1)) *
      Math.cos(deg2rad(lat2)) *
      Math.sin(dLon / 2) *
      Math.sin(dLon / 2);
  const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
  const d = R * c; // Distance in km
  return d <= shopRadius;
}

function deg2rad(deg) {
  return deg * (Math.PI / 180);
}

exports.nearestSellersToCustomer = async (req, res, next) => {
  try {
    const loggedInCustomerId = req.customerId;

    // Fetch the details of the logged-in customer
    const loggedInCustomer = await Customers.findByPk(loggedInCustomerId, {
      attributes: ["id", "agent_code", "latitude", "longitude"],
    });

    // Check if the logged-in customer exists
    if (!loggedInCustomer) {
      return errorHandler("Logged-in customer not found", 404, next);
    }

    // Extract the latitude and longitude of the logged-in customer
    const { latitude: customerLatitude, longitude: customerLongitude } =
      loggedInCustomer;

    console.log("Customer Latitude:", customerLatitude);
    console.log("Customer Longitude:", customerLongitude);

    // Fetch all sellers' companies
    const sellersCompanies = await SellersCompany.findAll();

    if (!sellersCompanies || sellersCompanies.length === 0) {
      return res.status(404).json({ error: "No sellers found" });
    }

    // Calculate distances for each seller's company
    const sellersWithProducts = await Promise.all(
      sellersCompanies.map(async (sellerCompany) => {
        try {
          // Check if latitude and longitude are valid for both customer and seller's company
          if (
            customerLatitude &&
            customerLongitude &&
            sellerCompany.latitude &&
            sellerCompany.longitude
          ) {
            console.log("Seller Company Latitude:", sellerCompany.latitude);
            console.log("Seller Company Longitude:", sellerCompany.longitude);
            const distance = await computeDistance(
              `${customerLatitude},${customerLongitude}`,
              `${sellerCompany.latitude},${sellerCompany.longitude}`,
            );

            // Use findAllProductsForSeller function to get all products for the seller's company
            const products = await findAllProductsForSeller(sellerCompany.id);

            return { sellerCompany, distance, products };
          } else {
            // If latitude or longitude is missing, set distance to a large value
            return { sellerCompany, distance: Infinity, products: [] };
          }
        } catch (error) {
          console.error("Error calculating distance:", error.message);
          return { sellerCompany, distance: Infinity, products: [] };
        }
      }),
    );

    // Sort sellers by distance, nearest to farthest
    sellersWithProducts.sort((a, b) => a.distance - b.distance);

    // Return sorted list of sellers with products
    res.json({
      nearestSellers_Company: sellersWithProducts.map((item) => ({
        sellerCompanyId: item.sellerCompany.id,
        sellerCompanyName:
          item.sellerCompany.shop_name === "" ||
          item.sellerCompany.shop_name === null
            ? item.sellerCompany.company_name
            : item.sellerCompany.shop_name,
        sellerCompanyLogo: `https://buildhub.ph/img/seller/${item.sellerCompany.seller_logo}`,
        sellerBannerImage: `https://buildhub.ph/img/seller/${item.sellerCompany.seller_banner_image}`, // Include seller_banner_image
        sellerCompanyLongitude: item.sellerCompany.longitude,
        sellerCompanyLatitude: item.sellerCompany.latitude,
        distance: isFinite(item.distance)
          ? `${item.distance.toFixed(2)} km`
          : null, // Check if distance is finite
        products: item.products, // Include products for each seller
      })),
    });
  } catch (error) {
    console.error("Error fetching or calculating distances:", error.message);
    res.status(500).json({ error: "Internal Server Error" });
  }
};

async function findAllProductsForSeller(sellerId) {
  try {
    const products = await Products.findAll({
      include: [
        {
          model: ProductVariation,
          as: "product_variations",
          include: [
            {
              model: ProductSubVariation,
              as: "ProductSubVariations",
            },
          ],
        },
        {
          model: SellersCompany,
          as: "seller",
          where: {
            id: sellerId, // Filter by sellerId
            status: true,
          },
        },
      ],
      where: {
        seller_id: sellerId, // Additional filter for sellerId
      },
    });

    const productsResponse = await Promise.all(
      products.map(async (product) => {
        const id = product.id;

        // Fetch average rating from DetailProductReviews
        const averageRating = await DetailProductReviews.findOne({
          attributes: [
            [
              DetailProductReviews.sequelize.fn(
                "COALESCE",
                DetailProductReviews.sequelize.fn(
                  "AVG",
                  DetailProductReviews.sequelize.col("rate"),
                ),
                0,
              ),
              "avgRating",
            ],
          ],
          where: { product_id: id },
        });

        // Constructing the image URL
        const photoUrl = sellerProductImageBuilder(
          product.photo,
          product.seller_dashboard_src,
        );

        return {
          id: product.id,
          productName: product.menu,
          description: product.description,
          price: product.price,
          discountedPrice: product.discounted_price,
          photo: photoUrl,
          quantityLimit: product.quantity_limit,
          stocks: product.stocks,
          available: product.available,
          seller_dashboard_src: product.seller_dashboard_src,
          variations: product.product_variations.map((variation) => ({
            variationId: variation.id,
            variation: variation.variation,
            description: variation.description,
            price: variation.price,
            discountedPrice: variation.discounted_price,
            hasSubVariation: variation.hasSubVariation,
            isAvailable: variation.isAvailable,
            variationImage: sellerProductImageBuilder(
              variation.variationImage,
              true,
            ),
            sku: variation.sku,
            stocks: variation.stocks,
            subVariations: variation.ProductSubVariations.map(
              (subVariation) => ({
                subVariationId: subVariation.id,
                custom: subVariation.custom,
                description: subVariation.description,
                price: subVariation.price,
                discountedPrice: subVariation.discounted_price,
                quantityLimit: subVariation.quantity_limit,
                stocks: subVariation.stocks,
                sku: subVariation.sku,
                isAvailable: subVariation.isAvailable,
                photo: sellerProductImageBuilder(subVariation.photo, true),
              }),
            ),
          })),
          avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
        };
      }),
    );

    return productsResponse;
  } catch (error) {
    console.error("Error fetching products for seller:", error.message);
    return [];
  }
}
