const ProductMenu = require("../../models/ProductMenu");
const Products = require("../../models/Products");
const ProductVariation = require("../../models/ProductVariation");
const ProductsSubVariation = require("../../models/ProductSubVariation");
const SellersCompany = require("../../models/SellersCompany");
const ShopRadius = require("../../models/ShopRadius");
const {
  getSellersCompanyExcludes,
  getProductMenusExcludes,
  getProductsExcludes,
  getProductVariationsExcludes,
  getProductSubVariationExcludes,
} = require("../../helpers/model-excludes-attributes/excludes");
const errorHandler = require("../../util/errorHandler");
const Sequelize = require("sequelize");

const {
  sellerProductImageBuilder,
  sellerImageBuilder,
} = require("../../helpers/image-src-builder");
const MicrositeHandles = require("../../models/MicrositeHandles");
const ProductImages = require("../../models/ProductImages");

const PRODUCTS_PER_SELLER = 2;

exports.findProductsByCategory = async (req, res, next) => {
  try {
    const { categoryId } = req.params;
    const { page, limit } = req.pagination;
    const offset = (page - 1) * limit;
    const {
      productKeyword = "",
      globalSearch = "false",
      mid = null,
    } = req.query;

    const isGlobalSearch = globalSearch.toLowerCase() === "true";
    const isLoggedIn = req.loggedIn;

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

    /* ---------------- SELLER FILTERS ---------------- */

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

    let distanceCondition = "";
    if (isLoggedIn && shopRadius && !isGlobalSearch) {
      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}
      `;
    }

    /* ---------------- PRODUCT FILTERS ---------------- */

    const productWhere = {
      available: 1,
      product_status: true,
      menu_id: categoryId,
      ...(productKeyword && {
        [Sequelize.Op.and]: Sequelize.where(
          Sequelize.fn("LOWER", Sequelize.col("products.menu")),
          {
            [Sequelize.Op.like]: `%${productKeyword.toLowerCase()}%`,
          },
        ),
      }),
    };

    /* ---------------- MAIN QUERY ---------------- */

    const rankedProducts = await Products.findAll({
      where: productWhere,
      attributes: {
        exclude: getProductsExcludes(),
        include: [
          [
            Sequelize.literal(`
              ROW_NUMBER() OVER (
                PARTITION BY products.seller_id
                ORDER BY products.id DESC
              )
            `),
            "seller_rank",
          ],
          [
            Sequelize.literal(
              `(SELECT COUNT(*) FROM detail_product_reviews WHERE detail_product_reviews.product_id = products.id)`,
            ),
            "totalReviews",
          ],
          [
            Sequelize.literal(
              `(SELECT COALESCE(SUM(rate) / NULLIF(COUNT(*), 0), 0)
               FROM detail_product_reviews
               WHERE detail_product_reviews.product_id = products.id)`,
            ),
            "avgRating",
          ],
        ],
      },
      include: [
        {
          model: SellersCompany,
          as: "seller",
          required: true,
          where: {
            ...sellerWhere,
            ...(distanceCondition && {
              [Sequelize.Op.and]: Sequelize.literal(distanceCondition),
            }),
          },
          attributes: {
            exclude: getSellersCompanyExcludes(),
          },
        },
        {
          model: ProductMenu,
          as: "menus",
          attributes: {
            exclude: getProductMenusExcludes(),
          },
        },
        {
          model: ProductVariation,
          as: "product_variations",
          attributes: {
            exclude: getProductVariationsExcludes(),
          },
          include: [
            {
              model: ProductsSubVariation,
              as: "ProductSubVariations",
              attributes: {
                exclude: getProductSubVariationExcludes(),
              },
            },
          ],
        },
        {
          model: ProductImages,
          as: "product_images",
        },
      ],
      subQuery: false,
    });

    /* ---------------- SELLER BALANCING ---------------- */

    const balancedProducts = rankedProducts
      .filter((p) => p.get("seller_rank") <= PRODUCTS_PER_SELLER)
      .slice(offset, offset + limit);

    /* ---------------- RESPONSE MAPPING ---------------- */

    const processedProducts = balancedProducts.map((product) => {
      let thumbnailImage = "";

      const productImages =
        Array.isArray(product.product_images) && product.product_images.length
          ? product.product_images.map((img) => ({
              id: img.id,
              url: sellerProductImageBuilder(img.file_name, true),
              is_thumbnail: img.is_thumbnail,
            }))
          : [];

      if (!product.photo) {
        const thumb = productImages.find((p) => p.is_thumbnail);
        thumbnailImage = thumb ? thumb.url : "";
      } else {
        thumbnailImage = sellerProductImageBuilder(
          product.photo,
          product.seller.seller_dashboard_src,
        );
      }

      product.photo = thumbnailImage;

      if (product.seller) {
        product.seller.seller_logo = product.seller.seller_logo
          ? sellerImageBuilder(
              product.seller.seller_logo,
              product.seller.seller_dashboard_src,
            )
          : null;

        product.seller.seller_banner_image = product.seller.seller_banner_image
          ? sellerImageBuilder(
              product.seller.seller_banner_image,
              product.seller.seller_dashboard_src,
            )
          : null;

        product.seller.company_name =
          product.seller.shop_name || product.seller.company_name;
      }

      product.menus.photo = `https://buildhub.ph/img/products/${product.menus.photo}`;

      product.product_variations = product.product_variations.map((v) => {
        v.variationImage = sellerProductImageBuilder(v.variationImage, true);
        v.ProductSubVariations = v.ProductSubVariations.map((sv) => {
          sv.photo = sellerProductImageBuilder(sv.photo, true);
          return sv;
        });
        return v;
      });

      return product;
    });

    res.status(200).json({
      success: true,
      currentPage: page,
      hasMore: processedProducts.length === limit,
      products: processedProducts,
    });
  } 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.searchProducts = async (req, res, next) => {
  const { keyword, mid = null } = req.query;

  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;
      }
    }
  }

  let whereClause = {
    status: true,
  };

  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}
    `
    : "";

  if (isLoggedIn) {
    if (!whereClause[Sequelize.Op.and]) {
      whereClause[Sequelize.Op.and] = [];
    }
    whereClause[Sequelize.Op.and].push(Sequelize.literal(radiusLiteralSql));
  }

  if (mid) {
    whereClause.microsite_handle_id = mid;
  }

  const sellerCompanies = await SellersCompany.findAll({
    where: whereClause,
  });

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

  Products.findAll({
    where: {
      menu: {
        [Sequelize.Op.like]: `%${keyword}%`,
      },
      seller_id: {
        [Sequelize.Op.in]: filteredSellerCompanyIds,
      },
      available: 1,
      product_status: true,
    },
    attributes: {
      exclude: getProductsExcludes(),
    },
    include: [
      {
        model: ProductMenu,
        as: "menus",
        attributes: {
          exclude: getProductMenusExcludes(),
        },
      },
      {
        model: ProductVariation,
        as: "product_variations",
        attributes: {
          exclude: getProductVariationsExcludes(),
        },
        include: [
          {
            model: ProductsSubVariation,
            as: "ProductSubVariations",
            attributes: {
              exclude: getProductSubVariationExcludes(),
            },
          },
        ],
      },
    ],
  })
    .then((products) => {
      for (const product of products) {
        product.photo = sellerProductImageBuilder(
          product.photo,
          product.seller_dashboard_src,
        );

        for (const productVariation of product.product_variations) {
          productVariation.variationImage = sellerProductImageBuilder(
            productVariation.variationImage,
            true,
          );

          for (const productSubVariation of productVariation.ProductSubVariations) {
            productSubVariation.photo = sellerProductImageBuilder(
              productSubVariation.photo,
              true,
            );
          }
        }
      }

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

exports.sortProductsByCategory = (req, res, next) => {
  const { categoryId } = req.params;
  const { page, limit } = req.pagination;
  let order = [];

  if (req.query.sort === "lowest-to-highest") {
    order.push(["price", "ASC"]);
  } else if (req.query.sort === "highest-to-lowest") {
    order.push(["price", "DESC"]);
  } else if (req.query.sort === "highest-rated") {
    order.push([
      Sequelize.literal(`(
        SELECT COALESCE(SUM(rate) / NULLIF(COUNT(*), 0), 0) 
        FROM detail_product_reviews 
        WHERE detail_product_reviews.product_id = products.id
      )`),
      "DESC",
    ]);
  } else if (req.query.sort === "lowest-rated") {
    order.push([
      Sequelize.literal(`(
        SELECT COALESCE(SUM(rate) / NULLIF(COUNT(*), 0), 0) 
        FROM detail_product_reviews 
        WHERE detail_product_reviews.product_id = products.id
      )`),
      "ASC",
    ]);
  } else {
    order.push(["price", "ASC"]);
  }

  Products.findAndCountAll({
    where: {
      menu_id: categoryId,
      available: 1,
      product_status: true,
    },
    attributes: {
      exclude: getProductsExcludes(),
      include: [
        [
          Sequelize.literal(
            "(SELECT COUNT(*) FROM detail_product_reviews WHERE detail_product_reviews.product_id = products.id)",
          ),
          "totalReviews",
        ],
        [
          Sequelize.literal(
            "(SELECT COALESCE(SUM(rate) / NULLIF(COUNT(*), 0), 0) FROM detail_product_reviews WHERE detail_product_reviews.product_id = products.id)",
          ),
          "avgRating",
        ],
      ],
    },
    include: [
      {
        model: SellersCompany,
        as: "seller",
        attributes: {
          exclude: getSellersCompanyExcludes(),
        },
      },
      {
        model: ProductMenu,
        as: "menus",
        attributes: {
          exclude: getProductMenusExcludes(),
        },
      },
      {
        model: ProductVariation,
        as: "product_variations",
        attributes: {
          exclude: getProductVariationsExcludes(),
        },
        include: [
          {
            model: ProductsSubVariation,
            as: "ProductSubVariations",
            attributes: {
              exclude: getProductSubVariationExcludes(),
            },
          },
        ],
      },
    ],
    limit: limit,
    offset: (page - 1) * limit,
    order: order,
  })
    .then((result) => {
      const { count, rows: products } = result;

      for (const product of products) {
        product.photo = sellerProductImageBuilder(
          product.photo,
          product.seller_dashboard_src,
        );

        if (product.seller) {
          product.seller.seller_logo = product.seller.seller_logo
            ? sellerImageBuilder(
                product.seller.seller_logo,
                product.seller.seller_dashboard_src,
              )
            : null;
          product.seller.seller_banner_image = product.seller
            .seller_banner_image
            ? sellerImageBuilder(
                product.seller.seller_banner_image,
                product.seller.seller_dashboard_src,
              )
            : null;
        } else {
          product.seller = { seller_logo: null };
        }

        product.menus.photo = `https://buildhub.ph/img/products/${product.menus.photo}`;

        for (const productVariation of product.product_variations) {
          productVariation.variationImage = sellerProductImageBuilder(
            productVariation.variationImage,
            true,
          );

          for (const productSubVariation of productVariation.ProductSubVariations) {
            productSubVariation.photo = sellerProductImageBuilder(
              productSubVariation.photo,
              true,
            );
          }
        }
      }

      const totalPages = Math.ceil(count / limit);
      const hasNextPage = page < totalPages;
      const hasPreviousPage = page > 1;

      res.status(200).json({
        success: true,
        totalItems: count,
        totalPages: totalPages,
        currentPage: page,
        hasNextPage: hasNextPage,
        hasPreviousPage: hasPreviousPage,
        products,
      });
    })
    .catch((err) => {
      next(err);
    });
};

exports.sortAllProducts = (req, res, next) => {
  const { page, limit } = req.pagination;
  let order = [];

  if (req.query.sort === "lowest-to-highest") {
    order.push(["price", "ASC"]);
  } else if (req.query.sort === "highest-to-lowest") {
    order.push(["price", "DESC"]);
  } else if (req.query.sort === "highest-rated") {
    order.push([
      Sequelize.literal(`(
        SELECT COALESCE(SUM(rate) / NULLIF(COUNT(*), 0), 0) 
        FROM detail_product_reviews 
        WHERE detail_product_reviews.product_id = products.id
      )`),
      "DESC",
    ]);
  } else if (req.query.sort === "lowest-rated") {
    order.push([
      Sequelize.literal(`(
        SELECT COALESCE(SUM(rate) / NULLIF(COUNT(*), 0), 0) 
        FROM detail_product_reviews 
        WHERE detail_product_reviews.product_id = products.id
      )`),
      "ASC",
    ]);
  } else {
    order.push(["price", "ASC"]);
  }

  Products.findAndCountAll({
    where: {
      available: 1,
      product_status: true,
    },
    attributes: {
      exclude: getProductsExcludes(),
      include: [
        [
          Sequelize.literal(
            "(SELECT COUNT(*) FROM detail_product_reviews WHERE detail_product_reviews.product_id = products.id)",
          ),
          "totalReviews",
        ],
        [
          Sequelize.literal(
            "(SELECT COALESCE(SUM(rate) / NULLIF(COUNT(*), 0), 0) FROM detail_product_reviews WHERE detail_product_reviews.product_id = products.id)",
          ),
          "avgRating",
        ],
      ],
    },
    include: [
      {
        model: SellersCompany,
        as: "seller",
        attributes: {
          exclude: getSellersCompanyExcludes(),
        },
      },
      {
        model: ProductMenu,
        as: "menus",
        attributes: {
          exclude: getProductMenusExcludes(),
        },
      },
      {
        model: ProductVariation,
        as: "product_variations",
        attributes: {
          exclude: getProductVariationsExcludes(),
        },
        include: [
          {
            model: ProductsSubVariation,
            as: "ProductSubVariations",
            attributes: {
              exclude: getProductSubVariationExcludes(),
            },
          },
        ],
      },
    ],
    limit: limit,
    offset: (page - 1) * limit,
    order: order,
  })
    .then((result) => {
      const { count, rows: products } = result;

      for (const product of products) {
        product.photo = `https://buildhub.ph/img/products/${product.photo}`;

        if (product.seller) {
          product.seller.seller_logo = product.seller.seller_logo
            ? sellerProductImageBuilder(
                product.seller.seller_logo,
                product.seller.seller_dashboard_src,
              )
            : null;

          product.seller.seller_banner_image = product.seller
            .seller_banner_image
            ? sellerProductImageBuilder(
                product.seller.seller_banner_image,
                product.seller.seller_dashboard_src,
              )
            : null;
        } else {
          product.seller = { seller_logo: null };
          product.seller = { seller_banner_image: null };
        }

        product.menus.photo = `https://buildhub.ph/img/products/${product.menus.photo}`;

        for (const productVariation of product.product_variations) {
          productVariation.variationImage = sellerProductImageBuilder(
            productVariation.variationImage,
            true,
          );

          for (const productSubVariation of productVariation.ProductSubVariations) {
            productSubVariation.photo = sellerProductImageBuilder(
              productSubVariation.photo,
              true,
            );
          }
        }
      }

      const totalPages = Math.ceil(count / limit);
      const hasNextPage = page < totalPages;
      const hasPreviousPage = page > 1;

      res.status(200).json({
        success: true,
        totalItems: count,
        totalPages: totalPages,
        currentPage: page,
        hasNextPage: hasNextPage,
        hasPreviousPage: hasPreviousPage,
        products,
      });
    })
    .catch((err) => {
      next(err);
    });
};
