const Customers = require("../models/Customers");
const ShopRadius = require("../models/ShopRadius");
const Sequelize = require("sequelize");
const Products = require("../models/Products");
const SellersCompany = require("../models/SellersCompany");
const {
  getProductsExcludes,
  getSellersCompanyExcludes,
  getProductMenusExcludes,
  getProductVariationsExcludes,
  getProductSubVariationExcludes,
} = require("./model-excludes-attributes/excludes");
const ProductMenu = require("../models/ProductMenu");
const ProductVariation = require("../models/ProductVariation");
const ProductSubVariation = require("../models/ProductSubVariation");
const { sellerProductImageBuilder } = require("./image-src-builder");
const CustomerQuotation = require("../models/CustomerQuotation");
const { generateRandomString, generateSecureRandomSixDigit } = require("./common");
const CustomerQuotationDetails = require("../models/CustomerQuotationDetails");

async function radiusService() {
  try {
    const radius = await ShopRadius.findAll();

    let shopRadius = 0;
    if (radius.length > 0) {
      const { dataValues } = radius[0];
      if (dataValues) {
        shopRadius = dataValues.radius;
      }
    }

    return shopRadius;
  } catch (err) {
    return { error: err };
  }
}

async function customerLocationService(userId) {
  try {
    let longitude = 0;
    let latitude = 0;
    let address = "";
    const customer = await Customers.findByPk(userId);

    if (customer && customer.longitude && customer.latitude && customer.address) {
      longitude = customer.longitude;
      latitude = customer.latitude;
      address = customer.address;
    }

    return { longitude, latitude, address };
  } catch (err) {
    return { error: err };
  }
}

async function nearestSellerService(query) {
  try {
    let filteredSellerCompanyIds = [];

    const sellerCompanies = await SellersCompany.findAll({
      where: query,
      attributes: ["id"],
    });
    filteredSellerCompanyIds = sellerCompanies.map((sellerCompany) => sellerCompany.id);

    return filteredSellerCompanyIds;
  } catch (err) {
    return { error: err };
  }
}

async function sellerProductsService(query, quantity) {
  try {
    const products = await Products.findAll({
      where: query,
      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",
          ],
        ],
      },
      order: [["price", "ASC"]],
      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: ProductSubVariation,
              as: "ProductSubVariations",
              attributes: {
                exclude: getProductSubVariationExcludes(),
              },
            },
          ],
        },
      ],
    });

    const modifiedProducts = products.map((product) => {
      const productPrice =
        product.discounted_price && product.discounted_price != 0
          ? parseFloat(product.discounted_price)
          : parseFloat(product.price);
      return {
        id: product.id,
        product_name: product.menu,
        category: product.menus.product_menu,
        price: productPrice,
        quantity: quantity,
        subtotal: parseFloat(productPrice) * parseFloat(quantity),
        photo: sellerProductImageBuilder(product.photo, product.seller_dashboard_src),
        location: `${product.seller.shop_location_municipality} ${product.seller.shop_location_province}`,
      };
    });

    return modifiedProducts;
  } catch (err) {
    return { error: err };
  }
}
exports.recommendedItemsService = async (userId, requestQuotation) => {
  try {
    const customerLocationPromise = customerLocationService(userId);
    const radiusPromise = radiusService();

    const [customerLocation, radius] = await Promise.all([customerLocationPromise, radiusPromise]);
    const { error, longitude, latitude } = customerLocation;

    if (error) return { error: error };

    const radiusLiteralSql = `
    6371 * 2 * ASIN(
        SQRT(
          POWER(SIN((${latitude} - latitude) * PI()/180 / 2), 2) +
          COS(${latitude} * PI()/180) * COS(latitude * PI()/180) *
          POWER(SIN((${longitude} - longitude) * PI()/180 / 2), 2)
        )
      ) <= ${radius}
    `;
    let whereClause = {};
    whereClause[Sequelize.Op.and] = [];
    whereClause[Sequelize.Op.and].push(Sequelize.literal(radiusLiteralSql));
    const sellerCompanyIds = await nearestSellerService(whereClause);

    let quotationReturn = {};
    for (const request of requestQuotation) {
      const { category_id, quantity } = request;

      const category = await ProductMenu.findByPk(category_id);

      let _whereClause = {
        available: 1,
        menu_id: category_id,
        price: {
          [Sequelize.Op.ne]: 0,
        },
        seller_id: {
          [Sequelize.Op.in]: sellerCompanyIds,
        },
      };

      const recommendedProducts = await sellerProductsService(_whereClause, quantity);

      if (recommendedProducts && recommendedProducts.length > 0) {
        if (!quotationReturn[category.product_menu]) {
          quotationReturn[category.product_menu] = [];
        }

        quotationReturn[category.product_menu].push(...recommendedProducts);
      }
    }
    return { quotation: quotationReturn };
  } catch (err) {
    return { error: err };
  }
};

exports.saveQuotation = async (userId, requestQuotation) => {
  try {
    const customerLocation = await customerLocationService(userId);

    const referenceNumber = `${generateRandomString()}-${generateSecureRandomSixDigit()}`;
    const customerQuotation = await CustomerQuotation.create({
      quotation_reference_no: referenceNumber,
      customer_id: userId,
      longitude: customerLocation.longitude,
      latitude: customerLocation.latitude,
      address: customerLocation.address,
      shipping_fee: 0,
      quotation_status: "Customer Request for Quotation",
    });

    const customerQuotationDetailsData = requestQuotation.map((quotationRequest) => ({
      customer_quotation_id: customerQuotation.id,
      item_name: quotationRequest.item_name,
      quantity: quotationRequest.quantity,
      unit_price: 0,
      total_price: 0,
      quotation_detail_status: "For Items Assigning",
    }));

    await CustomerQuotationDetails.bulkCreate(customerQuotationDetailsData);

    const _queryCustomerQuotation = await this.getQuotationDetails(customerQuotation.id);
    return { quotation: _queryCustomerQuotation };
  } catch (err) {
    return { error: err };
  }
};

exports.getQuotationDetails = async (quotation_id, customer_id, statusFilter) => {
  try {
    if (!quotation_id && !customer_id) {
      return { error: "Both quotation_id and customer_id are null or undefined." };
    }

    const whereClause = quotation_id
      ? { id: quotation_id, ...(statusFilter && { quotation_status: statusFilter }) }
      : { customer_id, ...(statusFilter && { quotation_status: statusFilter }) };

    const customerQuotation = await CustomerQuotation.findAll({
      where: whereClause,
      include: [
        {
          model: CustomerQuotationDetails,
          as: "customer_quotation_details",
          include: [
            {
              model: SellersCompany,
              as: "sellers_company",
              attributes: ["id", "company_name", "shop_name"],
            },
            {
              model: Products,
              as: "product",
              attributes: ["id", "menu", "description"],
            },
          ],
        },
      ],
    });

    return quotation_id ? customerQuotation[0] : customerQuotation;
  } catch (err) {
    return { error: err.message || "An error occurred while fetching quotations." };
  }
};

exports.updateQuotationStatus = async (quotation_id, accepted = true) => {
  try {
    let newQuotationStatus = accepted ? "Quotation For Checkout" : "Customer Request for Quotation";
    let newQuotationDetailStatus = accepted ? "Item Quotation For Checkout" : "For Items Assigning";
    const updatedQuotationPromise = CustomerQuotation.update(
      {
        quotation_status: newQuotationStatus,
      },
      {
        where: {
          id: quotation_id,
        },
      }
    );

    const updatedQuotationDetailPromise = CustomerQuotationDetails.update(
      {
        quotation_detail_status: newQuotationDetailStatus,
      },
      {
        where: {
          customer_quotation_id: quotation_id,
        },
      }
    );

    const [updatedQuotation, updatedQuotationDetail] = await Promise.all([
      updatedQuotationPromise,
      updatedQuotationDetailPromise,
    ]);

    const _updatedQuotation = await this.getQuotationDetails(quotation_id);

    return { quotation: _updatedQuotation };
  } catch (err) {
    return { error: err };
  }
};

exports.updateQuotationDetailStatus = async (quotation_detail_id, accepted = true) => {
  try {
    let newQuotationDetailStatus = accepted ? "Item Quotation For Checkout" : "For Items Assigning";

    const updatedQuotation = await CustomerQuotationDetails.update(
      {
        quotation_detail_status: newQuotationDetailStatus,
      },
      {
        where: {
          id: quotation_detail_id,
        },
      }
    );
    return { quotation: updatedQuotation };
  } catch (err) {
    return { error: err };
  }
};
