const Sequelize = require("sequelize");
const sequelizeConnect = require("../connection/db");
const FeatureProducts = require("../models/FeatureProducts");
const Products = require("../models/Products");
const ProductVariation = require("../models/ProductVariation");
const OrderFormDetails = require("../models/OrderFormDetails");
const SellersCompany = require("../models/SellersCompany");
const DetailProductReviews = require("../models/DetailProductReviews");
const ProductMenu = require("../models/ProductMenu"); // Import ProductMenu model
const jwt = require("jsonwebtoken");
const errorHandler = require("../util/errorHandler");

// Get Featured Products
exports.getFeaturedProducts = async (req, res, next) => {
  try {
    const featuredproducts = await FeatureProducts.findAll({
      limit: 6,
    });

    const featuredproductResponse = await Promise.all(
      featuredproducts.map(async (featured_product) => {
        const { special_products, vendor } = featured_product;

        console.log("Special Product ID:", special_products);

        if (special_products !== undefined) {
          try {
            const product = await Products.findByPk(special_products, {
              attributes: ["id", "price", "discounted_price", "photo", "menu"],
            });

            if (product) {
              // Fetch company_name using the vendor as an ID
              const sellerCompany = await SellersCompany.findByPk(vendor, {
                attributes: ["id", "company_name", "shop_name"], // Include 'id' in attributes
              });

              // 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: special_products },
              });

              console.log("Featured Product:", featured_product);
              console.log("Product:", product);

              return {
                menu_id: featured_product.menu_id,
                branch_id: featured_product.branch_id,
                pickup_location_id: featured_product.pickup_location_id,
                special_products: product.id,
                price: product.price,
                discounted_price: product.discounted_price,
                photo: product.photo,
                menu: product.menu,
                vendor: sellerCompany
                  ? {
                      id: sellerCompany.id,
                      company_name:
                        sellerCompany.shop_name === "" || sellerCompany.shop_name === null
                          ? sellerCompany.company_name
                          : sellerCompany.shop_name,
                    }
                  : null,
                avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
              };
            } else {
              console.error(`Product not found for special_products: ${special_products}`);
            }
          } catch (error) {
            console.error("Error fetching product or seller company:", error);
          }
        } else {
          console.error("special_products is undefined:", featured_product);
        }
      })
    );

    const validResponses = featuredproductResponse.filter((response) => response !== null);

    res.status(200).json(validResponses);
  } catch (error) {
    next(error);
  }
};

exports.getBestSellerProducts = async (req, res, next) => {
  try {
    const { filter } = req.query;

    console.log("Filter:", filter); // Log filter value for debugging

    // Check if filter is provided
    if (filter) {
      // Find the ProductMenu record corresponding to the filter
      const productMenu = await ProductMenu.findOne({
        attributes: ["id"],
        where: { product_menu: filter }, // Filter by product_menu
      });

      if (!productMenu) {
        return res.status(404).json({ message: "Product menu not found" });
      }

      // Get the product_ids with the highest count for the specified product_menu
      const bestSellers = await OrderFormDetails.findAll({
        attributes: ["product_id", [sequelizeConnect.fn("COUNT", "product_id"), "count"]],
        include: [
          {
            model: Products,
            as: "product",
            attributes: [],
            where: { menu_id: productMenu.id }, // Filter by menu_id from ProductMenu
          },
        ],
        group: ["product_id"],
        order: [[sequelizeConnect.literal("count"), "DESC"]],
        limit: 8,
      });

      // Check if there are no products available for the specified filter
      if (bestSellers.length === 0) {
        return res.status(404).json({ message: "No products available for the specified filter" });
      }

      // Process best seller response
      const bestSellerResponse = await Promise.all(
        bestSellers.map(async (bestSeller) => {
          const { product_id, count } = bestSeller;

          try {
            // Fetch the product details
            const product = await Products.findByPk(product_id);

            if (product) {
              // Fetch company_name and seller_banner_image using the vendor as an ID
              const sellerCompany = await SellersCompany.findByPk(product.seller_id, {
                attributes: ["id", "company_name", "shop_name", "seller_banner_image"], // Include 'id' in attributes
              });

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

              console.log("Best Seller Product:", bestSeller);

              return {
                product_id: product.id,
                count: count,
                price: product.price,
                discounted_price: product.discounted_price,
                photo: product && product.photo ? `https://buildhub.ph/img/products/${product.photo}` : null,
                menu: product.menu,
                vendor: sellerCompany
                  ? {
                      id: sellerCompany.id,
                      company_name:
                        sellerCompany.shop_name === "" || sellerCompany.shop_name === null
                          ? sellerCompany.company_name
                          : sellerCompany.shop_name,
                      seller_banner_image:
                        sellerCompany && sellerCompany.seller_banner_image
                          ? `https://buildhub.ph/img/seller/${sellerCompany.seller_banner_image}`
                          : null, // Add image link to seller_banner_image
                    }
                  : null,
                avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
              };
            } else {
              console.error(`Product not found for product_id: ${product_id}`);
              return null; // Return null for products not found
            }
          } catch (error) {
            console.error("Error fetching product, seller company, or average rating:", error);
            return null;
          }
        })
      );

      // Filter out null values (products not found) from the response
      const filteredResponse = bestSellerResponse.filter((product) => product !== null);

      return res.status(200).json(filteredResponse);
    } else {
      // Fetch best sellers without filter
      const bestSellersWithoutFilter = await OrderFormDetails.findAll({
        attributes: ["product_id", [sequelizeConnect.fn("COUNT", "product_id"), "count"]],
        group: ["product_id"],
        order: [[sequelizeConnect.literal("count"), "DESC"]],
        limit: 8,
      });

      // Check if there are no products available without filter
      if (bestSellersWithoutFilter.length === 0) {
        return res.status(404).json({ message: "No products available" });
      }

      // Process best seller response without filter
      const bestSellerResponseWithoutFilter = await Promise.all(
        bestSellersWithoutFilter.map(async (bestSeller) => {
          const { product_id, count } = bestSeller;

          try {
            // Fetch the product details
            const product = await Products.findByPk(product_id);

            if (product) {
              // Fetch company_name and seller_banner_image using the vendor as an ID
              const sellerCompany = await SellersCompany.findByPk(product.seller_id, {
                attributes: ["id", "company_name", "shop_name", "seller_banner_image"], // Include 'id' in attributes
              });

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

              console.log("Best Seller Product without filter:", bestSeller);

              return {
                product_id: product.id,
                count: count,
                price: product.price,
                discounted_price: product.discounted_price,
                photo: product && product.photo ? `https://buildhub.ph/img/products/${product.photo}` : null,
                menu: product.menu,
                vendor: sellerCompany
                  ? {
                      id: sellerCompany.id,
                      company_name:
                        sellerCompany.shop_name === "" || sellerCompany.shop_name === null
                          ? sellerCompany.company_name
                          : sellerCompany.shop_name,
                      seller_banner_image:
                        sellerCompany && sellerCompany.seller_banner_image
                          ? `https://buildhub.ph/img/seller/${sellerCompany.seller_banner_image}`
                          : null, // Add image link to seller_banner_image
                    }
                  : null,
                avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
              };
            } else {
              console.error(`Product not found for product_id: ${product_id}`);
              return null; // Return null for products not found
            }
          } catch (error) {
            console.error("Error fetching product, seller company, or average rating:", error);
            return null;
          }
        })
      );

      // Filter out null values (products not found) from the response without filter
      const filteredResponseWithoutFilter = bestSellerResponseWithoutFilter.filter((product) => product !== null);

      return res.status(200).json(filteredResponseWithoutFilter);
    }
  } catch (error) {
    next(error);
  }
};

// Get Discounted Products
exports.getDiscountedProducts = async (req, res, next) => {
  try {
    const allProducts = await Products.findAll({
      limit: 20,
    });

    const discountedProducts = allProducts.filter(
      (product) => product.discounted_price !== null && product.discounted_price !== "0.00"
    );

    const selectedDiscountedProducts = discountedProducts.slice(0, 6);

    const discountedProductResponse = await Promise.all(
      selectedDiscountedProducts.map(async (discountedProduct) => {
        const { id, price, discounted_price, photo, menu, vendor } = discountedProduct;

        try {
          // Fetch company_name and id using the vendor as an ID
          const sellerCompany = await SellersCompany.findByPk(vendor, {
            attributes: ["id", "company_name", "shop_name"],
          });

          // 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 },
          });

          console.log("Discounted Product:", discountedProduct);

          return {
            product_id: id,
            price: price,
            discounted_price: discounted_price,
            photo: photo,
            menu: menu,
            vendor: sellerCompany
              ? {
                  id: sellerCompany.id, // Include 'id' in the vendor object
                  company_name:
                    sellerCompany.shop_name === "" || sellerCompany.shop_name === null
                      ? sellerCompany.company_name
                      : sellerCompany.shop_name,
                }
              : null,
            avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
          };
        } catch (error) {
          console.error("Error fetching seller company or average rating:", error);
          return null;
        }
      })
    );

    const validResponses = discountedProductResponse.filter((response) => response !== null);

    res.status(200).json(validResponses);
  } catch (error) {
    next(error);
  }
};

// Get New Products (Latest 6 products based on ID)
exports.getNewProducts = async (req, res, next) => {
  try {
    const newProducts = await Products.findAll({
      order: [["id", "DESC"]],
      limit: 6,
    });

    const newProductResponse = await Promise.all(
      newProducts.map(async (newProduct) => {
        const { id, price, discounted_price, photo, menu, vendor } = newProduct;

        try {
          // Fetch company_name and id using the vendor as an ID
          const sellerCompany = await SellersCompany.findByPk(vendor, {
            attributes: ["id", "company_name", "shop_name"],
          });

          // 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 },
          });

          console.log("New Product:", newProduct);

          return {
            product_id: id,
            price: price,
            discounted_price: discounted_price,
            photo: photo,
            menu: menu,
            vendor: sellerCompany
              ? {
                  id: sellerCompany.id, // Include 'id' in the vendor object
                  company_name:
                    sellerCompany.shop_name === "" || sellerCompany.shop_name === null
                      ? sellerCompany.company_name
                      : sellerCompany.shop_name,
                }
              : null,
            avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
          };
        } catch (error) {
          console.error("Error fetching seller company or average rating:", error);
          return null;
        }
      })
    );

    const validResponses = newProductResponse.filter((response) => response !== null);

    res.status(200).json(validResponses);
  } catch (error) {
    next(error);
  }
};

// Get Best Seller Products by Menu ID
exports.getBestSellerProductsByMenuId = async (req, res, next) => {
  try {
    const { menu_id } = req.params;

    const bestSellers = await OrderFormDetails.findAll({
      attributes: ["product_id", [OrderFormDetails.sequelize.fn("COUNT", "product_id"), "count"]],
      include: [
        {
          model: Products,
          as: "product",
          attributes: [],
          where: { menu_id: menu_id },
        },
      ],
      group: ["product_id"],
      order: [[OrderFormDetails.sequelize.literal("count"), "DESC"]],
      limit: 8,
    });

    const bestSellerResponse = await Promise.all(
      bestSellers.map(async (bestSeller) => {
        const { product_id, count } = bestSeller;

        try {
          const product = await Products.findOne({
            attributes: ["id", "price", "discounted_price", "photo", "menu", "seller_id"],
            include: [
              {
                model: ProductVariation,
                as: "product_variations",
              },
            ],
            where: { id: product_id, menu_id: menu_id }, // Filter by both id and menu_id
          });

          if (product) {
            const sellerCompany = await SellersCompany.findByPk(product.seller_id, {
              attributes: ["id", "company_name", "shop_name"],
            });

            const averageRating = await DetailProductReviews.findOne({
              attributes: [
                [
                  sequelizeConnect.fn("COALESCE", sequelizeConnect.fn("AVG", sequelizeConnect.col("rate")), 0),
                  "avgRating",
                ],
              ],
              where: { product_id: product_id },
            });

            console.log("Best Seller Product:", bestSeller);
            console.log("Product:", product);

            return {
              product_id: product.id,
              count: count,
              price: product.price,
              discounted_price: product.discounted_price,
              photo: product.photo,
              menu: product.menu,
              vendor: sellerCompany
                ? {
                    id: sellerCompany.id,
                    company_name:
                      sellerCompany.shop_name === "" || sellerCompany.shop_name === null
                        ? sellerCompany.company_name
                        : sellerCompany.shop_name,
                  }
                : null,
              avgRating: averageRating ? averageRating.get("avgRating") : "0.00",
            };
          } else {
            console.error(`Product not found or does not match the menu_id: ${menu_id}`);
            return null; // Return null for products not found or not matching menu_id
          }
        } catch (error) {
          console.error("Error fetching product, seller company, or average rating:", error);
          return null;
        }
      })
    );

    // Filter out null values (products not found or not matching menu_id) from the response
    const filteredResponse = bestSellerResponse.filter((product) => product !== null);

    if (filteredResponse.length > 0) {
      res.status(200).json(filteredResponse);
    } else {
      res.status(404).json({ message: `No best seller products found for menu_id: ${menu_id}` });
    }
  } catch (error) {
    next(error);
  }
};
