const { Op } = require("sequelize");
const Sequelize = require("sequelize");
const OrderForm = require("../../models/OrderForm");
const OrderFormDetails = require("../../models/OrderFormDetails");
const Products = require("../../models/Products");
const ProductVariation = require("../../models/ProductVariation");
const ProductSubVariation = require("../../models/ProductSubVariation");
const {
  customerAttachmentProofBuilder,
  sellerProductImageBuilder,
  sellerReleaseOrderBuilder,
} = require("../../helpers/image-src-builder");
const MultipleDr = require('../../models/MultipleDR');

const statuses = [
  "Pending",
  "Cancelled",
  "Verified",
  "For Preparation",
  "Release Order - Delivery",
  "Release Order - Customer Pickup",
  "For Delivery - Warehouse",
  "Delivered",
  "Received",
  "Denied",
];

exports.getAllOrderForm = async (req, res, next) => {
  // Sort by status w/ count
  // Sort by date
  // Pagination
  const { seller_id } = req;
  const { orderStatus = "", orderDate = "" } = req.query;
  const { limit, page } = req.pagination;

  const whereClause = {
    seller_id,
  };

  if (orderStatus) {
    whereClause.status = orderStatus;
  }

  if (orderDate) {
    const dates = orderDate.split("-");
    if (dates.length === 2) {
      const [startDate, endDate] = dates.map((date) => new Date(date.trim()));
      whereClause.date_created = {
        [Op.between]: [startDate, endDate],
      };
    }
  }

  const offset = (page - 1) * limit;

  let orderForms;
  OrderForm.findAll({
    where: whereClause,
    limit,
    offset,
    order: [["date_created", "DESC"]],
  })
    .then((orders) => {
      orderForms = orders.map(
        ({
          id,
          email,
          name,
          customer_id,
          contact_number,
          reference_no,
          note,
          total_amount,
          proof_of_payment,
          status,
          total_qty,
          address,
          longitude,
          latitude,
          payment_method,
          date_created,
          delivery_fee,
          shipping_type,
          release_order_image,
          release_order_image_new_src,
          dateTime_verified,
          dateTime_for_preparation,
          dateTime_ready_pickup,
          dateTime_for_delivery,
          is_v2_order,
        }) => ({
          id,
          customer: {
            id: customer_id,
            name,
            email,
            contact_number,
            delivery_address: {
              address,
              longitude,
              latitude,
            },
            remarks: proof_of_payment,
          },
          reference_no,
          bank_proof_attachment: note !== "" ? customerAttachmentProofBuilder(note, is_v2_order) : null,
          release_order_image:
            release_order_image !== null
              ? sellerReleaseOrderBuilder(release_order_image, release_order_image_new_src)
              : null,
          total_amount,
          date_created,
          total_order_qty: total_qty,
          delivery_fee,
          shipping_type,
          payment_method,
          timestamp: {
            verified: dateTime_verified,
            for_preparation: dateTime_for_preparation,
            ready_pickup: dateTime_ready_pickup,
            for_delivery: dateTime_for_delivery,
          },
          order_status: status,
        })
      );

      return OrderForm.findAll({
        attributes: ["status", [Sequelize.fn("COUNT", Sequelize.col("status")), "count"]],
        where: {
          seller_id,
          status: {
            [Op.in]: statuses,
          },
        },
        group: ["status"],
      });
    })
    .then(async (orderFormCounts) => {
      const counts = {};
      statuses.forEach((status) => (counts[status] = 0));
      orderFormCounts.forEach((row) => (counts[row.status] = row.get("count")));

      const orderFormCount = await OrderForm.count({
        where: whereClause,
      });

      const totalPages = Math.ceil(orderFormCount / limit);
      const hasNextPage = (page - 1) * limit + orderForms.length < totalPages;
      const hasPreviousPage = page > 1;
      res.status(200).json({
        success: true,
        orderForms,
        pagination: {
          totalPages,
          currentPage: page,
          hasNextPage,
          hasPreviousPage,
        },
        statusCount: counts,
      });
    })
    .catch((err) => {
      next(err);
    });
};

exports.getOrderFormByIdWithOrderDetails = (req, res, next) => {
  const { seller_id } = req;
  const { reference_no } = req.params;
  OrderForm.findOne({
    where: {
      seller_id,
      reference_no,
    },
    include: [
      {
        model: OrderFormDetails,
        as: "order_form_details",
        include: [
          {
            model: Products,
            as: "product",
            include: [
              {
                model: ProductVariation,
                as: "product_variations",
                attributes: {
                  exclude: ["product_id"],
                },
                include: [
                  {
                    model: ProductSubVariation,
                    as: "ProductSubVariations",
                    required: false,
                    attributes: {
                      exclude: ["product_variation_id"],
                    },
                  },
                ],
              },
            ],
          },
        ],
      },
      {
        model: MultipleDr,
        as: "delivery_receipts",
        attributes: ["id", "reference_no", "delivered_picture", "date_created"]
      }
    ],
  })
    .then((orderForm) => {
      if (!orderForm) return res.status(404).json({ success: false, msg: "Order not found." });
      const orderFormDetails = orderForm.order_form_details.map(
        ({
          id,
          quantity,
          discounted_price,
          price,
          amount,
          has_sub_variation_chosen,
          product_id,
          product_variation_id,
          product_sub_variation_id,
          product,
        }) => {
          const orderFormDetailStructure = {
            id,
            quantity,
            discounted_price,
            price,
            total_amount: amount,
            has_sub_variation_chosen: has_sub_variation_chosen !== "" ? false : true,
            product_variation_id,
            product_sub_variation_id,
            product_detail: {
              id: product?.id,
              name: product?.menu,
              description: product?.description,
              price: product?.price,
              discounted_price: product?.discounted_price,
              photo:
                product?.photo !== null ? sellerProductImageBuilder(product.photo, product.seller_dashboard_src) : null,
              qty_limit: product?.quantity_limit,
              stocks: product?.stocks,
              available: product?.available,
              product_variation_id: product?.product_variation_id,
              product_sub_variation_id: product?.product_sub_variation_id,
              product_variation_detail: [],
            },
          };

          const productVariationDetail = product.product_variations.map((productVariation) => ({
            id: productVariation.id,
            variation: productVariation.variation,
            description: productVariation.description,
            qty_limit: productVariation.quantity_limit,
            discounted_price: productVariation.discounted_price,
            has_sub_variation: productVariation.hasSubVariation,
            isAvailable: productVariation.isAvailable,
            image:
              productVariation.variationImage !== ""
                ? sellerProductImageBuilder(productVariation.variationImage, true)
                : null,
            stocks: productVariation.stocks,
            sku: productVariation.sku,
            product_sub_variation_detail: productVariation.ProductSubVariations,
          }));

          orderFormDetailStructure.product_detail.product_variation_detail = productVariationDetail;

          return orderFormDetailStructure;
        }
      );

      let deliveryReceipts = [];
      for(const deliveryReceipt of orderForm.delivery_receipts) {
        deliveryReceipts.push({
          id: deliveryReceipt.id,
          delivered_picture: orderForm.shipping_type === "Pick-up" ? `https://buildhub.ph/uploads/${deliveryReceipt.delivered_picture}` : `https://buildhub.ph/img/delivery/${deliveryReceipt.delivered_picture}`
        })
      }
      const response = {
        id: orderForm.id,
        reference_no: orderForm.reference_no,
        agent: {
          code: orderForm.agentCode,
        },
        date_created: orderForm.date_created,
        pickup_date: orderForm.pickup_date,
        delivered_date: orderForm.date_delivered,
        shipping_type: orderForm.shipping_type,
        order_status: orderForm.status,
        customer: {
          id: orderForm.customer_id,
          name: orderForm.name,
          email: orderForm.email,
          contact_number: orderForm.contact_number,
          delivery_address: {
            address: orderForm.address,
            longitude: orderForm.longitude,
            latitude: orderForm.latitude,
          },
        },
        billing: {
          payment_method: orderForm.payment_method,
          delivery_fee: orderForm.delivery_fee,
          total_amount: orderForm.total_amount,
        },
        order_instruction: orderForm.proof_of_payment,
        orderFormDetails,
        delivery_receipts: deliveryReceipts,
      };
      res.status(200).json({ success: true, order_detail: response });
    })
    .catch((err) => {
      next(err);
    });
};

exports.updateOrderForm = (req, res, next) => {
  const { seller_id } = req;
  const { release_order_image } = req.files;
  const { reference_no } = req.params;
  const { data } = req.body;

  const { status: updatedStatus } = data;
  let roFileName;
  if (release_order_image) roFileName = release_order_image[0].filename;

  if (!statuses.includes(updatedStatus)) return res.status(404).json({ success: false, msg: "Invalid status" });

  let isPreparation = false,
    roDelivery = false,
    roCustomerPickup = false;
  switch (updatedStatus) {
    case "For Preparation":
      isPreparation = true;
      break;
    case "Release Order - Delivery":
      roDelivery = true;
      break;
    case "Release Order - Customer Pickup":
      roCustomerPickup = true;
      break;
  }

  OrderForm.findOne({
    where: {
      reference_no,
      seller_id,
    },
  })
    .then((orderForm) => {
      if (!orderForm) return res.status(404).json({ success: false, msg: "Order not found." });

      const dateNow = new Date();

      orderForm.status = updatedStatus;
      orderForm.release_order_image = roFileName;
      orderForm.release_order_image_new_src = true;

      isPreparation ? (orderForm.dateTime_for_preparation = new Date()) : null;
      roCustomerPickup ? (orderForm.dateTime_ready_pickup = new Date()) : null;
      return orderForm.save();
    })
    .then(() => {
      res.status(200).json({ success: true, msg: "Record updated successfully." });
    })
    .catch((err) => {
      next(err);
    });
};

exports.dashboardOrderData = async (req, res, next) => {
  const { seller_id } = req;

  try {
    const today = new Date();
    today.setHours(0, 0, 0, 0);

    const endOfToday = new Date();
    endOfToday.setDate(today.getDate() + 1);

    const upcomingDate = new Date();
    upcomingDate.setDate(endOfToday.getDate() + 2);

    const responseData = {
      ordersCount: [],
      today_orders: [],
      upcoming_orders: [],
    };

    const orderFormsPromise = OrderForm.findAll({
      attributes: ["status", [Sequelize.fn("COUNT", Sequelize.col("status")), "count"]],
      where: {
        seller_id,
        status: {
          [Op.in]: statuses,
        },
      },
      group: ["status"],
    });

    const orderFormsTodayPromise = OrderForm.findAll({
      where: {
        seller_id,
        status: {
          [Op.in]: statuses,
        },
        pickup_date: {
          [Op.between]: [today, endOfToday],
        },
      },
      attributes: ["id", "reference_no", "name", "pickup_date"],
      order: [["id", "ASC"]],
    });

    const orderFormsUpcomingPromise = OrderForm.findAll({
      where: {
        seller_id,
        status: {
          [Op.in]: statuses,
        },
        pickup_date: {
          [Op.between]: [endOfToday, upcomingDate],
        },
      },
      attributes: ["id", "reference_no", "name", "pickup_date"],
      order: [["id", "ASC"]],
    });

    const [orderForms, orderFormsToday, orderFormsUpcoming] = await Promise.all([
      orderFormsPromise,
      orderFormsTodayPromise,
      orderFormsUpcomingPromise,
    ]);

    responseData.ordersCount = orderForms;
    responseData.today_orders = orderFormsToday;
    responseData.upcoming_orders = orderFormsUpcoming;

    const currentYear = new Date().getFullYear();
    const lastYear = currentYear - 1;

    const salesReportCurrentYearPromise = getYearlySalesReport(seller_id, currentYear);
    const salesReportPreviousYearPromise = getYearlySalesReport(seller_id, lastYear);

    const [salesReportCurrent, salesReportPrevious] = await Promise.all([
      salesReportCurrentYearPromise,
      salesReportPreviousYearPromise,
    ]);

    res.status(200).json({
      success: true,
      data: responseData,
      sales_report_current_year: salesReportCurrent,
      sales_report_previous_year: salesReportPrevious,
    });
  } catch (err) {
    next(err);
  }
};

const getYearlySalesReport = async (seller_id, year) => {
  try {
    const startOfYear = new Date(year, 0, 1);
    const endOfYear = new Date(year, 11, 31, 23, 59, 59);

    const salesReport = await OrderForm.findAll({
      where: {
        seller_id,
        date_created: {
          [Op.between]: [startOfYear, endOfYear],
        },
      },
      attributes: [
        [Sequelize.fn("MONTH", Sequelize.col("date_created")), "month"],
        [Sequelize.fn("SUM", Sequelize.col("total_amount")), "total_sales"],
        [Sequelize.fn("COUNT", Sequelize.col("id")), "total_orders"],
      ],
      group: [Sequelize.fn("MONTH", Sequelize.col("date_created"))],
      order: [[Sequelize.fn("MONTH", Sequelize.col("date_created")), "ASC"]],
      raw: true,
    });

    return salesReport;
  } catch (error) {
    console.error("Error fetching yearly sales report:", error);
    throw error;
  }
};
