const Products = require("../../models/Products");
const ProductMenu = require("../../models/ProductMenu");
const Seller = require("../../models/Seller");
const SellersCompany = require("../../models/SellersCompany");
const DetailProductReviews = require("../../models/DetailProductReviews");
const ShopRadius = require("../../models/ShopRadius");
const Sequelize = require("sequelize");
const errorHandler = require("../../util/errorHandler");
const {
  getSellersCompanyExcludes,
} = require("../../helpers/model-excludes-attributes/excludes");
const {
  sellerProductImageBuilder,
  sellerImageBuilder,
} = require("../../helpers/image-src-builder");
const MicrositeHandles = require("../../models/MicrositeHandles");
const { isBase64, generateCacheKey } = require("../../helpers/common");
const ProductImages = require("../../models/ProductImages");

exports.findAllSellersCompany = async (req, res, next) => {
  try {
    const {
      keyword = "",
      globalSearch = "false",
      lng = 0,
      lat = 0,
      mid = null,
      productLimit = 1,
    } = req.query;

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

    const { page } = req.pagination;
    const sellerCompanyLimit =
      parseInt(req.pagination.sellerCompanyLimit, 10) || 15;
    const offset = (page - 1) * sellerCompanyLimit;

    const cacheKey = generateCacheKey("sellersCompany", {
      keyword,
      globalSearch,
      lng,
      lat,
      mid,
      productLimit,
      page,
      sellerCompanyLimit,
    });

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

    /* ---------------- SHOP RADIUS ---------------- */

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

    const finalLat = isLoggedIn ? req.latitude : lat;
    const finalLng = isLoggedIn ? req.longitude : lng;

    let distanceCondition = null;
    if (!isGlobalSearch && shopRadius && finalLat && finalLng) {
      distanceCondition = Sequelize.literal(`
        (6371 * 2 * ASIN(
          SQRT(
            POWER(SIN((${finalLat} - latitude) * PI()/180 / 2), 2) +
            COS(${finalLat} * PI()/180) * COS(latitude * PI()/180) *
            POWER(SIN((${finalLng} - longitude) * PI()/180 / 2), 2)
          )
        )) <= ${shopRadius}
      `);
    }

    /* ---------------- SELLER WHERE ---------------- */

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

    if (distanceCondition) {
      whereClause[Sequelize.Op.and] = [distanceCondition];
    }

    let orderClause = [];

    if (keyword) {
      const sanitize = (t) =>
        t.replace(/[^a-zA-Z0-9\s\/.\-]/g, "").toLowerCase();

      const terms = keyword.split(/\s+/).map(sanitize).filter(Boolean);

      /* ---------- FILTERING (same behavior) ---------- */

      whereClause[Sequelize.Op.and] = [
        ...(whereClause[Sequelize.Op.and] || []),
        {
          [Sequelize.Op.or]: terms.map((term) => ({
            [Sequelize.Op.or]: [
              Sequelize.where(
                Sequelize.fn("LOWER", Sequelize.col("company_name")),
                { [Sequelize.Op.like]: `%${term}%` },
              ),
              Sequelize.where(
                Sequelize.fn("LOWER", Sequelize.col("shop_name")),
                { [Sequelize.Op.like]: `%${term}%` },
              ),
            ],
          })),
        },
      ];

      /* ---------- RANKING (NEW) ---------- */

      const scoreFragments = terms
        .map(
          (term) => `
        (
          CASE
            WHEN LOWER(company_name) = '${term}' THEN 10
            WHEN LOWER(shop_name) = '${term}' THEN 10

            WHEN LOWER(company_name) LIKE '${term}%' THEN 7
            WHEN LOWER(shop_name) LIKE '${term}%' THEN 7

            WHEN LOWER(company_name) LIKE '%${term}%' THEN 4
            WHEN LOWER(shop_name) LIKE '%${term}%' THEN 4

            ELSE 0
          END
        )
      `,
        )
        .join(" + ");

      const relevanceScore = Sequelize.literal(`(${scoreFragments})`);

      orderClause.push([relevanceScore, "DESC"]);
    }

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

    const { rows, count } = await SellersCompany.findAndCountAll({
      where: whereClause,
      limit: sellerCompanyLimit,
      offset,
      distinct: true,
      attributes: [
        "id",
        "shop_name",
        "company_name",
        "company_description",
        "address",
        "shop_location_municipality",
        "shop_location_province",
        "seller_logo",
        "seller_banner_image",
        "emailAddress",
        "url_handle",
        "seller_dashboard_src",
        "isParentCompany",
        "longitude",
        "latitude",
      ],
      include: [
        {
          model: MicrositeHandles,
          as: "microsite_handle",
          where: { status: true },
          required: false,
        },
        {
          model: Products,
          as: "products",
          separate: true,
          where: {
            available: 1,
            product_status: true,
            [Sequelize.Op.or]: [{ parent_id: null }, { parent_id: "" }],
          },
          required: false,
          attributes: [
            "id",
            "menu",
            "photo",
            "description",
            "seller_dashboard_src",
          ],
          include: [
            {
              model: ProductImages,
              as: "product_images",
              required: false,
            },
          ],
          limit: parseInt(productLimit),
        },
      ],
      order: [...(orderClause.length ? orderClause : []), ["id", "DESC"]],
    });

    /* ---------------- RESPONSE BUILD ---------------- */

    const response = rows.map((seller) => {
      const products = seller.products.map((product) => {
        let thumbnail = null;

        if (product.product_images?.length) {
          const t = product.product_images.find((i) => i.is_thumbnail);

          if (t?.file_name) {
            thumbnail = sellerProductImageBuilder(t.file_name, true);
          }
        }

        if (!thumbnail && product.photo) {
          thumbnail = sellerProductImageBuilder(
            product.photo,
            product.seller_dashboard_src,
          );
        }

        return {
          id: product.id,
          menu: product.menu,
          description: product.description,
          photo: thumbnail,
          avgRating: product.get("avgRating"),
        };
      });

      return {
        id: seller.id,
        name: seller.shop_name || seller.company_name,
        description: seller.company_description,
        location: `${seller.shop_location_municipality || ""}${
          seller.shop_location_province
            ? `, ${seller.shop_location_province}`
            : ""
        }`,
        address: seller.address,
        logo: seller.seller_logo
          ? sellerImageBuilder(seller.seller_logo, seller.seller_dashboard_src)
          : null,
        banner: seller.seller_banner_image
          ? sellerImageBuilder(
              seller.seller_banner_image,
              seller.seller_dashboard_src,
            )
          : null,
        emailAddress: seller.emailAddress,
        url_handle: seller.url_handle,
        microsite_handle: seller.microsite_handle,
        isParentCompany: seller.isParentCompany,
        longitude: seller.longitude,
        latitude: seller.latitude,
        products,
      };
    });

    const apiResponse = {
      success: true,
      response,
      pagination: {
        page,
        pageSize: sellerCompanyLimit,
        totalPages: Math.ceil(count / sellerCompanyLimit),
        totalCount: count,
      },
    };
    if (req.redisClient && req.redisClient.isConnected) {
      console.log("cacheKey: ", cacheKey);
      await req.redisClient.set(cacheKey, JSON.stringify(apiResponse), 60 * 60); // Cache for 1 hour
    }
    res.status(200).json(apiResponse);
  } catch (error) {
    next(error);
  }
};

exports.getAllProductsBySellerCompany = async (req, res, next) => {
  try {
    const { sellerId: seller_id } = req.params;
    const { page, limit } = req.pagination;
    const { category, nameSort, priceSort, productKeyword = "" } = req.query;

    const offset = (page - 1) * limit;

    /* =========================
       Decode keyword if base64
    ========================== */
    let decodedKeyword = productKeyword;
    if (productKeyword && isBase64(productKeyword)) {
      try {
        decodedKeyword = decodeURIComponent(
          Buffer.from(productKeyword, "base64").toString("utf-8"),
        );
      } catch {
        decodedKeyword = productKeyword;
      }
    }

    const cacheKey = generateCacheKey("sellerCompanyStorefront", {
      seller_id,
      page,
      limit,
      category,
      nameSort,
      priceSort,
      decodedKeyword,
      offset,
    });

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

    /* =========================
       Validate sorting params
    ========================== */
    const validSort = ["asc", "desc"];
    if (nameSort && !validSort.includes(nameSort)) {
      return res.status(200).json({
        success: false,
        msg: "Invalid name sorting strategy ['asc', 'desc']",
      });
    }

    if (priceSort && !validSort.includes(priceSort)) {
      return res.status(200).json({
        success: false,
        msg: "Invalid pricing sorting strategy ['asc', 'desc']",
      });
    }

    /* =========================
       Resolve category
    ========================== */
    let productCategoryId = null;
    if (category) {
      const productMenu = await ProductMenu.findOne({
        where: Sequelize.where(
          Sequelize.fn("LOWER", Sequelize.col("product_menu")),
          { [Sequelize.Op.like]: `%${category.toLowerCase()}%` },
        ),
        attributes: ["id"],
      });

      productCategoryId = productMenu?.id || null;
    }

    /* =========================
       Seller company
    ========================== */
    const sellersCompanyDetail = await SellersCompany.findOne({
      where: {
        [Sequelize.Op.or]: [{ id: seller_id }, { url_handle: seller_id }],
        status: true
      },
      attributes: { exclude: getSellersCompanyExcludes() },
      include: [
        {
          model: MicrositeHandles,
          as: "microsite_handle",
          where: { status: true },
          required: false,
        },
      ],
    });

    if (!sellersCompanyDetail) {
      return res.status(200).json({
        success: false,
        msg: "Invalid url handle or seller id",
      });
    }

    const sellerCompany = {
      id: sellersCompanyDetail.id,
      seller_name:
        sellersCompanyDetail.shop_name || sellersCompanyDetail.company_name,
      seller_description: sellersCompanyDetail.company_description,
      address: sellersCompanyDetail.address,
      location: `${sellersCompanyDetail.shop_location_municipality}, ${sellersCompanyDetail.shop_location_province}`,
      latitude: sellersCompanyDetail.latitude,
      longitude: sellersCompanyDetail.longitude,
      owner: sellersCompanyDetail.owner,
      seller_logo: sellersCompanyDetail.seller_logo
        ? sellerImageBuilder(
            sellersCompanyDetail.seller_logo,
            sellersCompanyDetail.seller_dashboard_src,
          )
        : null,
      seller_banner_img: sellersCompanyDetail.seller_banner_image
        ? sellerImageBuilder(
            sellersCompanyDetail.seller_banner_image,
            sellersCompanyDetail.seller_dashboard_src,
          )
        : null,
      emailAddress: sellersCompanyDetail.emailAddress,
      url_handle: sellersCompanyDetail.url_handle,
      microsite_handle: sellersCompanyDetail.microsite_handle,
    };

    /* =========================
       Base where clause
    ========================== */
    const whereClause = {
      seller_id: sellersCompanyDetail.id,
      available: 1,
      product_status: true,
      [Sequelize.Op.or]: [{ parent_id: null }, { parent_id: "" }],
    };

    if (productCategoryId) {
      whereClause.menu_id = productCategoryId;
    }

    /* =========================
       Keyword search + ranking
    ========================== */
    const orderClause = [];
    let relevanceScore = null;

    if (decodedKeyword) {
      const sanitize = (t) =>
        t.replace(/[^a-zA-Z0-9\s\/.\-]/g, "").toLowerCase();

      const terms = decodedKeyword.split(/\s+/).map(sanitize).filter(Boolean);

      if (terms.length) {
        whereClause[Sequelize.Op.and] = terms.map((term) => ({
          [Sequelize.Op.or]: [
            Sequelize.where(Sequelize.fn("LOWER", Sequelize.col("menu")), {
              [Sequelize.Op.like]: `%${term}%`,
            }),
            Sequelize.where(
              Sequelize.fn("LOWER", Sequelize.col("description")),
              { [Sequelize.Op.like]: `%${term}%` },
            ),
          ],
        }));

        const scoreSql = terms
          .map(
            (term) => `
            (CASE
              WHEN LOWER(menu) = '${term}' THEN 20
              WHEN LOWER(menu) LIKE '${term}%' THEN 15
              WHEN LOWER(menu) LIKE '%${term}%' THEN 10
              WHEN LOWER(description) LIKE '${term}%' THEN 5
              WHEN LOWER(description) LIKE '%${term}%' THEN 2
              ELSE 0
            END)
          `,
          )
          .join(" + ");

        relevanceScore = Sequelize.literal(`(${scoreSql})`);
        orderClause.push([relevanceScore, "DESC"]);
      }
    }

    /* =========================
       Sorting
    ========================== */
    if (nameSort) orderClause.push(["menu", nameSort]);
    if (priceSort) orderClause.push(["price", priceSort]);

    if (!orderClause.length) {
      orderClause.push(["menu", "ASC"]);
    }

    /* =========================
       Fetch products (FAST)
    ========================== */
    const { rows: products, count } = await Products.findAndCountAll({
      where: whereClause,
      include: [
        {
          model: ProductImages,
          as: "product_images",
        },
        {
          model: DetailProductReviews,
          as: "product_review",
          attributes: [],
          required: false,
        },
      ],
      attributes: {
        include: [
          [
            Sequelize.fn(
              "COALESCE",
              Sequelize.fn("AVG", Sequelize.col("product_review.rate")),
              0,
            ),
            "average_rating",
          ],
        ],
      },
      group: ["products.id", "product_images.id"],
      order: orderClause,
      limit,
      offset,
      subQuery: false,
    });

    /* =========================
       Format response
    ========================== */
    const sortedProduct = products.map((p) => {
      const images =
        p.product_images?.map((img) => ({
          id: img.id,
          url: sellerProductImageBuilder(img.file_name, true),
          is_thumbnail: img.is_thumbnail,
        })) || [];

      let photo = sellerProductImageBuilder(p.photo, p.seller_dashboard_src);

      if (!photo) {
        const thumb = images.find((i) => i.is_thumbnail);
        if (thumb) photo = thumb.url;
      }

      return {
        id: p.id,
        menuId: p.menu_id,
        menu: p.menu,
        variant: p.variant,
        description: p.description,
        price: p.price,
        discountedPrice: p.discounted_price,
        seller_dashboard_src: p.seller_dashboard_src,
        photo,
        product_images: images,
        quantityLimit: p.quantity_limit,
        stocks: p.stocks,
        available: p.available,
        sku: p.sku,
        productVariationId: p.product_variation_id,
        productSubVariationId: p.product_sub_variation_id,
        rating: Number(p.get("average_rating") || 0),
      };
    });

    const apiResponse = {
      success: true,
      seller_company: sellerCompany,
      sortedProduct,
      total_products: count.length ? count.length : count,
      total_pages: Math.ceil((count.length ? count.length : count) / limit),
      currentPage: page,
    };

    if (req.redisClient && req.redisClient.isConnected) {
      await req.redisClient.set(cacheKey, JSON.stringify(apiResponse), 60 * 60); // Cache for 1 hour
    }
    res.status(200).json(apiResponse);
  } catch (err) {
    next(err);
  }
};

exports.getMicrositeHandleByName = async (req, res, next) => {
  const { microsite_handle } = req.query;

  if (!microsite_handle)
    return res
      .status(400)
      .json({ success: false, message: "Invalid microsite handle." });

  try {
    const micrositeHandle = await MicrositeHandles.findOne({
      where: {
        microsite: microsite_handle,
        status: true,
      },
    });

    if (!micrositeHandle)
      return res
        .status(400)
        .json({ success: false, message: "Invalid microsite handle." });

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

exports.searchModuleSellerCompany = async (req, res, next) => {
  try {
    const sellersCompany = await SellersCompany.findAll({
      attributes: ["id", "company_name", "shop_name", "address"],
    });

    const modifiedSellersCompany = sellersCompany.map((sellerCompany) => ({
      id: sellerCompany.id,
      name:
        sellerCompany.shop_name === "" || sellerCompany.shop_name === null
          ? sellerCompany.company_name
          : sellerCompany.shop_name,
      display_name:
        sellerCompany.shop_name === "" || sellerCompany.shop_name === null
          ? `${sellerCompany.company_name} (${sellerCompany.address})`
          : `${sellerCompany.shop_name} (${sellerCompany.address})`,
    }));
    res
      .status(200)
      .json({ success: true, sellers_company: modifiedSellersCompany });
  } catch (err) {
    next(err);
  }
};
