const mongoose = require('mongoose');
const axios = require('axios');
const xlsx = require('xlsx');
const { cloudinary } = require('../config/cloudinary');
const Product = require('../models/productModel');
const Category = require('../models/categoryModel');
const Provider = require('../models/providerModel');
const Brand = require('../models/brandModel');
const User = require('../models/userModel');
const APIFeatures = require('../utils/apiFeatures');
const {
  createSmartSearchRegex,
  convertArabicNumerals,
} = require('../utils/arabicSearch');
const {
  notifyPastBuyersProductBackInStock,
  notifyStockSubscribers,
} = require('../utils/notificationService');

// Paste your Cloudinary logo URL here to force it as import fallback cover.
// Example: https://res.cloudinary.com/<cloud-name>/image/upload/v1234567890/samoulla/system/logo-cover.png
const HARDCODED_IMPORT_LOGO_COVER =
  'https://res.cloudinary.com/dm9ym99zh/image/upload/v1771470584/Asset_16_wfto7q.png';

const IMPORT_FALLBACK_COVER_IMAGE =
  HARDCODED_IMPORT_LOGO_COVER ||
  process.env.IMPORT_FALLBACK_COVER_IMAGE ||
  'https://placehold.co/1000x1000/png?text=No+Image';

const isImageUrlReachable = async (url) => {
  if (!url || typeof url !== 'string' || !url.startsWith('http')) return false;

  try {
    const response = await axios.head(url, {
      timeout: 7000,
      maxRedirects: 5,
      validateStatus: () => true,
    });

    if (response.status === 405) {
      const fallbackResponse = await axios.get(url, {
        timeout: 7000,
        maxRedirects: 5,
        headers: { Range: 'bytes=0-0' },
        validateStatus: () => true,
      });
      return fallbackResponse.status >= 200 && fallbackResponse.status < 400;
    }

    return response.status >= 200 && response.status < 400;
  } catch (_) {
    return false;
  }
};

// Helper to auto-upload external image URLs to Cloudinary during import
const uploadExternalImage = async (url, productId, isCover = true) => {
  if (!url || typeof url !== 'string') return '';

  // Skip non-URL values
  if (!url.startsWith('http')) return '';

  // Reuse URL only if it's already hosted on the current Cloudinary cloud.
  // Legacy Cloudinary URLs (e.g., old project cloud) are re-hosted to avoid 404s.
  const currentCloudName = process.env.CLOUDINARY_CLOUD_NAME;
  let sourceCloudName = null;

  try {
    const parsed = new URL(url);
    const match = parsed.hostname.match(/^res\.cloudinary\.com$/i)
      ? parsed.pathname.match(/^\/([^/]+)\//)
      : null;
    sourceCloudName = match ? match[1] : null;

    const inTargetFolderRegex = new RegExp(
      `/samoulla/products/${String(productId).replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}/`,
    );
    const isAlreadyInTargetFolder = inTargetFolderRegex.test(parsed.pathname);

    if (sourceCloudName && sourceCloudName === currentCloudName) {
      const reachable = await isImageUrlReachable(url);
      if (!reachable) return '';

      // Keep existing URL only when it's already inside the product _id folder.
      // Otherwise, re-host to enforce folder consistency for this product.
      if (isAlreadyInTargetFolder) return url;
    }
  } catch (_) {
    return '';
  }

  try {
    const prefix = isCover ? 'cover' : 'img';
    const result = await cloudinary.uploader.upload(url, {
      folder: `samoulla/products/${productId}`,
      public_id: `${prefix}-${Date.now()}`,
      transformation: [
        { width: 1000, height: 1000, crop: 'limit' },
        { quality: 'auto' },
        { fetch_format: 'auto' },
      ],
    });
    return result.secure_url;
  } catch (err) {
    console.error(`Auto-upload failed for ${url}:`, err.message);
    return '';
  }
};

// Helper to delete a single image from Cloudinary by its secure URL
const deleteCloudinaryImage = async (url) => {
  if (!url || typeof url !== 'string' || !url.includes('cloudinary.com'))
    return;
  const currentCloudName = cloudinary.config().cloud_name;
  if (!url.includes(`/${currentCloudName}/`)) return; // Not our cloud, don't touch it
  try {
    // Extract public_id: everything after /upload/(v<digits>/) and before the file extension
    const match = url.match(/\/upload\/(?:v\d+\/)?(.+?)(?:\.[a-z0-9]+)?$/i);
    if (!match) return;
    const publicId = match[1];
    await cloudinary.uploader.destroy(publicId);
  } catch (err) {
    console.error(`Failed to delete Cloudinary image ${url}:`, err.message);
  }
};

// Helper to notify the AI service to reload its product cache
const triggerAiReload = async () => {
  try {
    const aiUrl = process.env.AI_SERVICE_URL || 'http://localhost:9001';
    await axios.post(`${aiUrl}/api/reload-products`);
    console.log('AI product cache reload triggered successfully');
  } catch (err) {
    // Fail silently in production, just log it
    console.error('Failed to trigger AI product reload:', err.message);
  }
};

// Get all products
exports.getAllProducts = async (req, res) => {
  try {
    // 1) Filter by Category (Hierarchy-aware)
    if (req.query.category !== undefined) {
      if (
        req.query.category === 'جميع المنتجات' ||
        req.query.category === '' ||
        req.query.category === 'all'
      ) {
        delete req.query.category;
      } else {
        const categoryValues = req.query.category.split(',');
        const objectIds = [];
        const numericIds = [];
        const nameValues = [];

        categoryValues.forEach((val) => {
          if (mongoose.Types.ObjectId.isValid(val)) objectIds.push(val);
          else if (/^\d+$/.test(val)) numericIds.push(val);
          else nameValues.push(val);
        });

        // Find initial categories in bulk
        const foundCategories = await Category.find({
          $or: [
            { _id: { $in: objectIds } },
            { id: { $in: numericIds } },
            { nameAr: { $in: nameValues } },
            { nameEn: { $in: nameValues } },
          ],
        });

        const categoryIds = foundCategories.map((c) => c._id);

        if (categoryIds.length > 0) {
          // Get descendants (Level 1)
          const subCategories = await Category.find({
            parent: { $in: categoryIds },
          });
          const subIds = subCategories.map((c) => c._id);
          categoryIds.push(...subIds);

          if (subIds.length > 0) {
            // Level 2 descendants
            const subSubCategories = await Category.find({
              parent: { $in: subIds },
            });
            categoryIds.push(...subSubCategories.map((c) => c._id));
          }
        }

        if (categoryIds.length > 0) {
          req.query.category = { $in: [...new Set(categoryIds)] };
        } else {
          req.query.category = { $in: [new mongoose.Types.ObjectId()] };
        }
      }
    }

    // 2) Filter by Brand (if provided)
    if (req.query.brand) {
      const brandValues = req.query.brand.split(',');
      const isObjectId = mongoose.Types.ObjectId.isValid(brandValues[0]);
      const isNumericId = /^\d+$/.test(brandValues[0]);

      if (isObjectId) {
        req.query.brand = { $in: brandValues };
      } else if (isNumericId) {
        // Find brand object by numeric ID
        const brands = await Brand.find({ id: { $in: brandValues } });
        if (brands.length > 0) {
          // If product stores name, use names. If it stores ID, use IDs.
          // For now, let's support both or prioritize names since the current model uses strings.
          req.query.brand = { $in: brands.map((b) => b.nameEn) };
        } else {
          req.query.brand = { $in: ['Unknown Brand'] };
        }
      } else {
        // It's a name
        req.query.brand = { $in: brandValues };
      }
    }

    // 3) Filter by Provider (if provided) - Supports multiple providers by name or slug
    if (req.query.provider) {
      const providerValues = req.query.provider.split(',');
      const providers = await Provider.find({
        $or: [
          { storeName: { $in: providerValues } },
          { slug: { $in: providerValues } },
          {
            _id: {
              $in: providerValues.filter((id) =>
                mongoose.Types.ObjectId.isValid(id),
              ),
            },
          },
        ],
      });

      if (providers.length > 0) {
        req.query.provider = { $in: providers.map((p) => p._id) };
      } else {
        req.query.provider = { $in: ['000000000000000000000000'] };
      }
    }

    // 3) Filter by Sale Price (if onSale is true)
    let salePriceQuery = {};
    if (req.query.onSale === 'true') {
      salePriceQuery = { salePrice: { $gt: 0 } };
      delete req.query.onSale;
    }

    // Store original search term for relevance scoring (before it gets deleted)
    const originalSearchTerm = req.query.search
      ? convertArabicNumerals(req.query.search.trim())
      : null;
    const hasExplicitSort = !!req.query.sort;

    // 4) Search by Name, Description, Barcode, or ID (if provided)
    let searchQuery = {};
    let relatedQuery = null; // $or query for "related" products (any-word match)
    if (req.query.search) {
      // TRACK SEARCH: Save the user's last 5 search terms in their profile if logged in
      if (req.user) {
        const searchTerm = req.query.search.trim();
        if (searchTerm) {
          // Remove if already exists to move it to the end (most recent)
          const searchHistory = (req.user.recentSearches || []).filter(
            (s) => s.toLowerCase() !== searchTerm.toLowerCase(),
          );
          searchHistory.push(searchTerm);

          // Keep only last 5
          if (searchHistory.length > 5) {
            searchHistory.shift();
          }

          req.user.recentSearches = searchHistory;
          // Non-blocking update
          User.findByIdAndUpdate(req.user._id, {
            recentSearches: searchHistory,
          }).catch(() => {});
        }
      }

      const searchTerms = req.query.search
        .split(' ')
        .map((term) => convertArabicNumerals(term.trim()))
        .filter((term) => term.length > 0);

      if (searchTerms.length > 0) {
        // Pre-fetch all matching categories for all terms at once
        const allSearchRegexes = searchTerms.map((term) =>
          createSmartSearchRegex(term),
        );
        const allMatchingCategories = await Category.find({
          $or: allSearchRegexes.flatMap((regex) => [
            { nameAr: regex },
            { nameEn: regex },
          ]),
        }).select('_id nameAr nameEn');

        const searchConditions = searchTerms.map((term) => {
          const searchRegex = createSmartSearchRegex(term);

          const matchingCategoryIds = allMatchingCategories
            .filter(
              (cat) =>
                (cat.nameAr && cat.nameAr.match(searchRegex)) ||
                (cat.nameEn && cat.nameEn.match(searchRegex)),
            )
            .map((cat) => cat._id);

          // Build search conditions array
          const conditions = [
            { nameAr: searchRegex },
            { nameEn: searchRegex },
            { descriptionAr: searchRegex },
            { descriptionEn: searchRegex },
            { barCode: searchRegex },
          ];

          if (matchingCategoryIds && matchingCategoryIds.length > 0) {
            conditions.push({ category: { $in: matchingCategoryIds } });
          }

          // Check if term looks like a hex string (potential partial ObjectId)
          if (/^[0-9a-fA-F]+$/.test(term)) {
            conditions.push({
              $expr: {
                $regexMatch: {
                  input: { $toString: '$_id' },
                  regex: term,
                  options: 'i',
                },
              },
            });
          }

          return { $or: conditions };
        });

        searchQuery = { $and: searchConditions };

        // Also build an $or query for "related" results (match ANY word).
        // When multiple words are searched, this lets us append products that
        // match some — but not all — of the words after the primary matches.
        if (searchTerms.length > 1) {
          // Flatten each per-word condition into one big $or
          const relatedOrConditions = searchConditions.flatMap(
            (c) => c.$or || [],
          );
          relatedQuery = { $or: relatedOrConditions, ...salePriceQuery };
        }
      }

      // Remove search from query so it doesn't interfere with APIFeatures
      delete req.query.search;
    }

    // Combine search and salePrice queries
    const combinedQuery = { ...searchQuery, ...salePriceQuery };

    // Calculate pagination params up front
    const page = req.query.page * 1 || 1;
    const limit = req.query.limit * 1 || 100;

    // When search is active and no explicit sort, fetch ALL matches so we can
    // score every result for relevance before paginating. This ensures the best
    // match is always on page 1 regardless of insertion order in the DB.
    let products;
    let totalCount;

    if (originalSearchTerm && !hasExplicitSort) {
      // Fetch ALL primary matching products ($and — all words must match)
      const allFeaturesQuery = new APIFeatures(
        Product.find(combinedQuery)
          .populate('category', 'nameAr _id')
          .populate('provider', 'storeName _id'),
        req.query,
      )
        .filter()
        .sort()
        .limitFields();

      const primaryProducts = await allFeaturesQuery.query.lean();
      const primaryIds = new Set(primaryProducts.map((p) => String(p._id)));

      // Fetch related products ($or — match ANY word) that aren't already in primary set
      let relatedProducts = [];
      if (relatedQuery) {
        relatedProducts = await Product.find({
          ...relatedQuery,
          _id: { $nin: [...primaryIds] }, // exclude already-found primary products
        })
          .populate('category', 'nameAr _id')
          .populate('provider', 'storeName _id')
          .select('-__v')
          .lean();
      }

      // Merge: primary first, related appended
      // Scoring below will rank primary products higher (they match all words)
      products = [...primaryProducts, ...relatedProducts];
      totalCount = products.length;
    } else {
      // Regular path: paginate in DB (faster when no search or explicit sort)
      const countFeatures = new APIFeatures(
        Product.find(combinedQuery)
          .populate('category', 'nameAr _id')
          .populate('provider', 'storeName _id'),
        req.query,
      )
        .filter()
        .sort()
        .limitFields();

      totalCount = await Product.countDocuments(
        countFeatures.query.getFilter(),
      );

      const features = new APIFeatures(
        Product.find(combinedQuery)
          .populate('category', 'nameAr _id')
          .populate('provider', 'storeName _id'),
        req.query,
      )
        .filter()
        .sort()
        .limitFields()
        .paginate();

      products = await features.query.lean();
    }

    // Apply intelligent relevance scoring if search is active and no explicit sort
    if (originalSearchTerm && !hasExplicitSort && products.length > 0) {
      // Normalize for Arabic-aware comparisons
      const { normalizeArabic } = require('../utils/arabicSearch');
      const normalizedSearchTerm = normalizeArabic(originalSearchTerm);
      const searchWords = normalizedSearchTerm
        .split(/\s+/)
        .filter((w) => w.length > 0);
      const isMultiWord = searchWords.length > 1;

      /**
       * Score a single string field against the search term.
       * Returns a score 0-100 for that field.
       */
      const IS_PURE_NUMBER = /^\d+(\.\d+)?$/;
      const scoreField = (rawText) => {
        if (!rawText) return 0;
        const norm = normalizeArabic(rawText);

        // --- Full-phrase matching ---
        // Exact full match (always valid)
        if (norm === normalizedSearchTerm) return 100;

        // For pure-number queries (e.g. "5"), don't use startsWith/includes
        // because "5" would falsely match "1500", "50", etc.
        if (!IS_PURE_NUMBER.test(normalizedSearchTerm)) {
          // Starts with full phrase
          if (norm.startsWith(normalizedSearchTerm)) return 85;
          // Contains the full phrase as a substring
          if (norm.includes(normalizedSearchTerm)) return 65;
        }

        if (isMultiWord) {
          // For multi-word queries: score based on how many words match in the name.
          // Numbers must match as exact whole words (so "5" does NOT match "1500").
          const IS_NUMBER = /^\d+(\.\d+)?$/;
          let wordMatchCount = 0;
          let allWordsMatched = true;
          const normWords = norm.split(/\s+/);

          for (const word of searchWords) {
            let matched = false;
            if (IS_NUMBER.test(word)) {
              // Numeric token: require exact equality only
              matched = normWords.some((nw) => nw === word);
            } else {
              // Text token: allow starts-with / contains (fuzzy)
              matched = normWords.some(
                (nw) => nw === word || nw.startsWith(word) || nw.includes(word),
              );
            }
            if (matched) {
              wordMatchCount++;
            } else {
              allWordsMatched = false;
            }
          }

          if (wordMatchCount === 0) return 0;
          const ratio = wordMatchCount / searchWords.length;
          // All words matched → high score
          if (ratio === 1) return allWordsMatched ? 80 : 72;
          // Partial match: scale 10-58
          return 10 + Math.round(ratio * 48);
        }

        // Single-word fallback
        // Whole-word boundary match
        try {
          const wbRegex = new RegExp(
            `(^|\\s)${normalizedSearchTerm.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}(\\s|$)`,
          );
          if (wbRegex.test(norm)) return 60;
        } catch (_) {
          // ignore regex errors
        }

        return 0;
      };

      /**
       * Combined scorer for a product.
       * Name scores are weighted heavily; description/category as fallback.
       */
      const scoreProduct = (product) => {
        const nameArScore = scoreField(product.nameAr);
        const nameEnScore = scoreField(product.nameEn);
        const nameScore = Math.max(nameArScore, nameEnScore);

        // If we got a solid name match, return it directly
        if (nameScore >= 60) return nameScore;

        // Check description
        const descArText = Array.isArray(product.descriptionAr)
          ? product.descriptionAr.join(' ')
          : product.descriptionAr || '';
        const descEnText = Array.isArray(product.descriptionEn)
          ? product.descriptionEn.join(' ')
          : product.descriptionEn || '';
        const descScore = Math.max(
          scoreField(descArText),
          scoreField(descEnText),
        );

        if (descScore > 0)
          return Math.max(nameScore, Math.round(descScore * 0.4));

        // Matched via category/barcode — keep it low but above 0
        return nameScore > 0 ? nameScore : 8;
      };

      // Sort products by score (descending)
      products = products.sort((a, b) => scoreProduct(b) - scoreProduct(a));

      // Apply manual pagination after scoring (only for search path)
      if (originalSearchTerm && !hasExplicitSort) {
        const skip = (page - 1) * limit;
        products = products.slice(skip, skip + limit);
      }
    }

    const totalPages = Math.ceil(totalCount / limit);

    res.status(200).json({
      status: 'success',
      results: products.length,
      totalCount,
      totalPages,
      currentPage: page,
      data: {
        products,
      },
    });
  } catch (err) {
    console.error('getAllProducts error:', err);
    res.status(500).json({
      status: 'fail',
      message: 'Error fetching products',
      error: err.message,
    });
  }
};

// Get search suggestions
exports.getSuggestions = async (req, res) => {
  try {
    const { search } = req.query;
    if (!search) {
      return res
        .status(200)
        .json({ status: 'success', data: { products: [], categories: [] } });
    }

    const {
      createSmartSearchRegex,
      getSimilarity,
      convertArabicNumerals,
    } = require('../utils/arabicSearch');

    const searchTerms = search
      .split(' ')
      .map((term) => convertArabicNumerals(term.trim()))
      .filter((term) => term.length > 0);
    let productQuery = {};
    let categoryQuery = {};

    if (searchTerms.length > 0) {
      const searchConditions = searchTerms.map((term) => {
        const searchRegex = createSmartSearchRegex(term);
        return {
          $or: [{ nameAr: searchRegex }, { nameEn: searchRegex }],
        };
      });

      productQuery = { $and: searchConditions };
      categoryQuery = { $and: searchConditions };
    }

    // Fetch matching products (fetch more for ranking)
    let products = await Product.find(productQuery)
      .select('nameAr nameEn imageCover price slug _id')
      .limit(100)
      .lean();

    // Rank products by similarity
    products = products
      .map((p) => ({
        ...p,
        score: Math.max(
          getSimilarity(search, p.nameAr),
          getSimilarity(search, p.nameEn),
        ),
      }))
      .sort((a, b) => b.score - a.score)
      .slice(0, 5);

    // Fetch matching categories
    let categories = await Category.find(categoryQuery)
      .select('nameAr nameEn _id id')
      .limit(50)
      .lean();

    // Rank categories by similarity
    categories = categories
      .map((c) => ({
        ...c,
        score: Math.max(
          getSimilarity(search, c.nameAr),
          getSimilarity(search, c.nameEn),
        ),
      }))
      .sort((a, b) => b.score - a.score)
      .slice(0, 3);

    res.status(200).json({
      status: 'success',
      data: {
        products,
        categories,
      },
    });
  } catch (err) {
    res.status(500).json({
      status: 'fail',
      message: 'Error fetching suggestions',
    });
  }
};

// Get a single product by ID
exports.getProduct = async (req, res) => {
  try {
    const { id } = req.params;

    // Check if the provided ID is a valid MongoDB ObjectId
    const isObjectId = mongoose.Types.ObjectId.isValid(id);

    let product;
    if (isObjectId) {
      product = await Product.findById(id)
        .populate('category', 'nameAr nameEn _id id')
        .populate('provider', 'storeName name logo _id')
        .populate('reviews')
        .lean();
    } else {
      // If not an ObjectId, assume it's a slug
      product = await Product.findOne({ slug: id })
        .populate('category', 'nameAr nameEn _id id')
        .populate('provider', 'storeName name logo _id')
        .populate('reviews')
        .lean();
    }

    if (!product) {
      return res.status(404).json({
        status: 'fail',
        message: 'No product found with that ID or slug',
      });
    }

    res.status(200).json({
      status: 'success',
      data: {
        product,
      },
    });
  } catch (err) {
    res.status(500).json({
      status: 'fail',
      message: err.message,
    });
  }
};

// Create a new product
exports.createProduct = async (req, res) => {
  try {
    const categoryIds = Array.isArray(req.body.category)
      ? req.body.category
      : [req.body.category];

    const categories = await Category.find({ _id: { $in: categoryIds } });
    if (!categories || categories.length === 0) {
      return res.status(400).json({ message: 'Invalid category ids' });
    }

    const provider = await Provider.findById(req.body.provider);
    if (!provider) {
      return res.status(400).json({ message: 'Invalid provider id' });
    }

    const productData = {
      // eslint-disable-next-line node/no-unsupported-features/es-syntax
      ...req.body,
      category: categories.map((cat) => cat._id),
      provider: provider._id,
    };

    const newProduct = await Product.create(productData);

    // Notify AI service to reload cache
    triggerAiReload();

    res.status(201).json({
      status: 'success',
      data: {
        product: newProduct,
      },
    });
  } catch (err) {
    res.status(400).json({
      status: 'fail',
      message: err.message,
    });
  }
};

// Update a product
exports.updateProduct = async (req, res) => {
  try {
    const oldProduct = await Product.findById(req.params.id);
    if (!oldProduct) {
      return res.status(404).json({
        status: 'fail',
        message: 'Product not found',
      });
    }

    const updatedProduct = await Product.findByIdAndUpdate(
      req.params.id,
      req.body,
      { new: true, runValidators: true },
    );

    // Check for stock replenish (Back in Stock)
    if (req.body.stock > 0 && (!oldProduct.stock || oldProduct.stock <= 0)) {
      notifyPastBuyersProductBackInStock(updatedProduct).catch((err) =>
        console.error(
          'Failed to send back-in-stock notification (past buyers):',
          err.message,
        ),
      );
      notifyStockSubscribers(updatedProduct).catch((err) =>
        console.error(
          'Failed to send back-in-stock notification (subscribers):',
          err.message,
        ),
      );
    }

    // Notify AI service to reload cache (any data change)
    triggerAiReload();

    res.status(200).json({
      status: 'success',
      data: {
        product: updatedProduct,
      },
    });
  } catch (err) {
    res.status(500).json({
      status: 'fail',
      message: err.message,
    });
  }
};

// Delete a product
const { deleteProductFolder } = require('../config/cloudinary');

// ... (existing imports)

// Delete a product
exports.deleteProduct = async (req, res) => {
  try {
    // Delete product from MongoDB
    const product = await Product.findByIdAndDelete(req.params.id);

    if (!product) {
      return res.status(404).json({
        status: 'fail',
        message: 'No product found with that ID',
      });
    }

    // Delete associated images folder from Cloudinary
    try {
      await deleteProductFolder(req.params.id);
    } catch (imageError) {
      console.error(
        `Failed to delete images for product ${req.params.id}:`,
        imageError,
      );
      // We don't stop the response here, as the product is already deleted
    }

    res.status(204).json({
      status: 'success',
      data: null,
    });
  } catch (err) {
    res.status(404).json({
      status: 'fail',
      message: err.message,
    });
  }
};

// Get product statistics (Admin)
exports.getProductStats = async (req, res) => {
  try {
    const stats = await Product.aggregate([
      {
        $facet: {
          totalProducts: [{ $count: 'count' }],
          available: [{ $match: { stock: { $gt: 10 } } }, { $count: 'count' }],
          lowStock: [
            { $match: { stock: { $gt: 0, $lte: 10 } } },
            { $count: 'count' },
          ],
          outOfStock: [{ $match: { stock: 0 } }, { $count: 'count' }],
        },
      },
    ]);

    const result = {
      total:
        stats[0].totalProducts && stats[0].totalProducts[0]
          ? stats[0].totalProducts[0].count
          : 0,
      available:
        stats[0].available && stats[0].available[0]
          ? stats[0].available[0].count
          : 0,
      lowStock:
        stats[0].lowStock && stats[0].lowStock[0]
          ? stats[0].lowStock[0].count
          : 0,
      outOfStock:
        stats[0].outOfStock && stats[0].outOfStock[0]
          ? stats[0].outOfStock[0].count
          : 0,
    };

    res.status(200).json({
      status: 'success',
      data: result,
    });
  } catch (err) {
    res.status(500).json({
      status: 'fail',
      message: 'Error fetching product statistics',
    });
  }
};

// Get featured products
exports.getFeaturedProducts = async (req, res) => {
  try {
    const products = await Product.find({ isFeatured: true })
      .populate('category', 'nameAr _id')
      .populate('provider', 'storeName _id')
      .limit(20)
      .lean(); // Limit to 20 for performance (Home Page Carousel)

    res.status(200).json({
      status: 'success',
      results: products.length,
      data: {
        products,
      },
    });
  } catch (err) {
    res.status(500).json({
      status: 'fail',
      message: 'Error fetching featured products',
    });
  }
};

// Import products from Excel file
exports.importProducts = async (req, res) => {
  try {
    if (!req.file) {
      return res
        .status(400)
        .json({ status: 'fail', message: 'No file uploaded' });
    }

    const { preview } = req.query;
    const isPreview = preview === 'true';

    // Read from buffer instead of file path (for Vercel serverless)
    const workbook = xlsx.read(req.file.buffer, { type: 'buffer' });
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    const rows = xlsx.utils.sheet_to_json(worksheet);

    const newProducts = [];
    const updatedProducts = [];
    const missingProducts = [];
    const errors = [];

    // Pre-fetch all needed providers and categories to optimize
    // However, for simplicity and ensuring exact matches per row logic, we'll keep it loop-based
    // or we could optimize if performance becomes an issue.
    const isVendor = req.user.role === 'vendor';
    let vendorProvider = null;

    if (isVendor) {
      if (!req.user.provider) {
        return res.status(400).json({
          status: 'fail',
          message: 'Vendor user is not associated with any provider',
        });
      }
      // eslint-disable-next-line no-await-in-loop
      vendorProvider = await Provider.findById(req.user.provider);
      if (!vendorProvider) {
        return res.status(400).json({
          status: 'fail',
          message: 'Associated provider not found',
        });
      }
    }

    // Deduplicate rows: if the same barCode+provider combo appears more than once in the
    // sheet, only the LAST occurrence is processed (later row is the intended final version).
    const lastOccurrenceMap = new Map();
    rows.forEach((row, idx) => {
      const providerKey = isVendor
        ? '__vendor__'
        : row.provider
          ? row.provider.toString().trim()
          : '';
      const key = `${(row.barCode || '').toString().trim()}||${providerKey}`;
      lastOccurrenceMap.set(key, idx);
    });

    for (const [index, row] of rows.entries()) {
      try {
        // Skip rows superseded by a later row with the same barCode+provider combo
        const dupProviderKey = isVendor
          ? '__vendor__'
          : row.provider
            ? row.provider.toString().trim()
            : '';
        const dupKey = `${(row.barCode || '').toString().trim()}||${dupProviderKey}`;
        if (lastOccurrenceMap.get(dupKey) !== index) {
          // A later row covers this barCode+provider — skip this one
          // eslint-disable-next-line no-continue
          continue;
        }

        // ── DETECT MODE FIRST ──────────────────────────────────────────────────
        // A "category-only" sheet has just barCode + category and nothing else.
        // We must detect this BEFORE provider resolution so we don't block on it.
        const isCategoryOnlyUpdate =
          row.barCode &&
          row.category &&
          !row.nameAr &&
          !row.brand &&
          !row.provider &&
          !row.price;

        const hasPurchaseOrPrice =
          row.purchasePrice !== undefined || row.price !== undefined;

        const isPriceOnlyUpdate =
          row.barCode &&
          hasPurchaseOrPrice &&
          !row.nameAr &&
          !row.nameEn &&
          !row.brand &&
          !row.category &&
          !row.salePrice &&
          !row.stock &&
          !row.descriptionAr &&
          !row.descriptionEn &&
          !row.imageCover &&
          !row.images &&
          !row.isFeatured &&
          !row.ratingsAverage &&
          !row.ratingsQuantity;

        const isPriceOnlyMissingRequired =
          row.barCode &&
          !hasPurchaseOrPrice &&
          row.costPrice !== undefined &&
          !row.nameAr &&
          !row.nameEn &&
          !row.brand &&
          !row.category &&
          !row.price &&
          !row.salePrice &&
          !row.stock &&
          !row.descriptionAr &&
          !row.descriptionEn &&
          !row.imageCover &&
          !row.images &&
          !row.isFeatured &&
          !row.ratingsAverage &&
          !row.ratingsQuantity;

        const isBroadBarcodeUpdate =
          !isVendor &&
          (isCategoryOnlyUpdate || (isPriceOnlyUpdate && !row.provider));

        if (isPriceOnlyMissingRequired) {
          errors.push({
            row: index + 2,
            message:
              'Price-only update requires purchasePrice or price (costPrice is optional)',
            name: row.nameAr || 'Unknown',
          });
          // eslint-disable-next-line no-continue
          continue;
        }

        // 1. Resolve Provider (skipped entirely for category-only updates)
        let provider = vendorProvider;
        if (!isVendor && !isCategoryOnlyUpdate) {
          const pIdentifier = row.provider
            ? row.provider.toString().trim()
            : '';
          let foundProvider = null;

          if (/^\d+$/.test(pIdentifier)) {
            foundProvider = await Provider.findOne({ id: Number(pIdentifier) });
          }

          if (!foundProvider && mongoose.Types.ObjectId.isValid(pIdentifier)) {
            foundProvider = await Provider.findById(pIdentifier);
          }

          if (!foundProvider) {
            foundProvider = await Provider.findOne({
              $or: [{ storeName: pIdentifier }, { name: pIdentifier }],
            });
          }

          provider = foundProvider;

          if (!provider) {
            errors.push({
              row: index + 2,
              message: `Invalid provider: ${row.provider}`,
              name: row.nameAr || 'Unknown',
            });
            // eslint-disable-next-line no-continue
            continue;
          }
        }

        // Validation: Required fields only for NEW products or full-row updates
        // For partial/category-only updates, barcode alone is enough to find the product
        const hasMissingFields = isCategoryOnlyUpdate || isPriceOnlyUpdate
          ? false // skip full validation for partial updates
          : isVendor
            ? !row.barCode || !row.nameAr || !row.brand || !row.category
            : !row.barCode ||
              !row.nameAr ||
              !row.brand ||
              !row.category ||
              !row.provider;

        if (hasMissingFields) {
          errors.push({
            row: index + 2,
            message: isVendor
              ? 'Missing required fields: Barcode, Name, Brand, or Category'
              : 'Missing required fields: Barcode, Name, Brand, Category, or Provider',
          });
          // eslint-disable-next-line no-continue
          continue;
        }

        // Search for existing product(s) by barcode.
        // For category-only updates: find ALL products with this barcode (across all providers)
        // For full updates: find by barcode+provider to enforce uniqueness per provider
        // eslint-disable-next-line no-await-in-loop
        const existingProducts = isBroadBarcodeUpdate
          ? await Product.find({ barCode: row.barCode.toString().trim() })
          : null;

        // eslint-disable-next-line no-await-in-loop
        const existingProduct = isBroadBarcodeUpdate
          ? existingProducts && existingProducts.length > 0
            ? existingProducts[0]
            : null
          : await Product.findOne({
              barCode: row.barCode,
              provider: provider._id,
            });

        // (Redundant but safe: If vendor, they only see products for their own provider anyway due to search above)
        if (
          existingProduct &&
          isVendor &&
          !existingProduct.provider.equals(vendorProvider._id)
        ) {
          errors.push({
            row: index + 2,
            message:
              'You do not have permission to update this product (belongs to another provider)',
            name: row.nameAr,
          });
          // eslint-disable-next-line no-continue
          continue;
        }

        if (existingProduct) {
          if (isPriceOnlyUpdate) {
            const normalizedBarcode = row.barCode.toString().trim();
            const nextPurchasePrice =
              row.purchasePrice !== undefined && row.purchasePrice !== null
                ? Number(row.purchasePrice) || 0
                : undefined;
            const nextCostPrice =
              row.costPrice !== undefined && row.costPrice !== null
                ? Number(row.costPrice) || 0
                : undefined;
            const nextPrice =
              row.price !== undefined && row.price !== null
                ? Number(row.price) || 0
                : undefined;

            if (isPreview) {
              updatedProducts.push({
                barCode: normalizedBarcode,
                changes: {
                  ...(nextPurchasePrice !== undefined
                    ? {
                        purchasePrice: {
                          old: existingProduct.purchasePrice,
                          new: nextPurchasePrice,
                        },
                      }
                    : {}),
                  ...(nextCostPrice !== undefined
                    ? {
                        costPrice: {
                          old: existingProduct.costPrice,
                          new: nextCostPrice,
                        },
                      }
                    : {}),
                  ...(nextPrice !== undefined
                    ? {
                        price: {
                          old: existingProduct.price,
                          new: nextPrice,
                        },
                      }
                    : {}),
                },
                providerName:
                  (provider && provider.storeName) || 'All Providers',
              });
            } else {
              const updateFields = {};
              if (nextPurchasePrice !== undefined)
                updateFields.purchasePrice = nextPurchasePrice;
              if (nextCostPrice !== undefined)
                updateFields.costPrice = nextCostPrice;
              if (nextPrice !== undefined) updateFields.price = nextPrice;

              if (isBroadBarcodeUpdate) {
                // eslint-disable-next-line no-await-in-loop
                await Product.updateMany(
                  { barCode: normalizedBarcode },
                  { $set: updateFields },
                );
              } else {
                // eslint-disable-next-line no-await-in-loop
                await Product.findByIdAndUpdate(existingProduct._id, {
                  $set: updateFields,
                });
              }

              updatedProducts.push(normalizedBarcode);
            }

            // eslint-disable-next-line no-continue
            continue;
          }

          // EXISTING PRODUCT: Update all fields
          // SMART CATEGORY RESOLUTION
          const rawCategoryValue = (row.category || '').toString().trim();
          const categoryIdentifiers = rawCategoryValue
            ? [
                ...new Set(
                  rawCategoryValue
                    .split(',')
                    .map((val) => val.trim())
                    .filter(Boolean),
                ),
              ]
            : [];

          const categoryObjectIds = [];
          const categoryNamesForPreview = [];

          for (const identifier of categoryIdentifiers) {
            let foundCategory = null;

            // 1. Try Numeric ID
            if (/^\d+$/.test(identifier)) {
              foundCategory = await Category.findOne({
                id: Number(identifier),
              });
            }

            // 2. Try ObjectId
            if (!foundCategory && mongoose.Types.ObjectId.isValid(identifier)) {
              foundCategory = await Category.findById(identifier);
            }

            // 3. Try Name (Ar/En) or Slug
            if (!foundCategory) {
              foundCategory = await Category.findOne({
                $or: [
                  { nameAr: identifier },
                  { nameEn: identifier },
                  { slug: identifier },
                ],
              });
            }

            if (foundCategory) {
              categoryObjectIds.push(foundCategory._id);
              categoryNamesForPreview.push(foundCategory.nameAr);
            }
          }

          // Validation: Ensure at least one category is found
          if (
            categoryIdentifiers.length > 0 &&
            categoryObjectIds.length === 0
          ) {
            errors.push({
              row: index + 2,
              message: `None of the provided category identifiers were found: ${row.category}`,
              name: row.nameAr,
            });
            // eslint-disable-next-line no-continue
            continue;
          }

          // SMART BRAND RESOLUTION
          let brandName = row.brand;
          if (row.brand) {
            let foundBrand = null;
            const bIdentifier = row.brand.toString().trim();

            if (/^\d+$/.test(bIdentifier)) {
              foundBrand = await Brand.findOne({ id: Number(bIdentifier) });
            }

            if (!foundBrand && mongoose.Types.ObjectId.isValid(bIdentifier)) {
              foundBrand = await Brand.findById(bIdentifier);
            }

            if (!foundBrand) {
              foundBrand = await Brand.findOne({
                $or: [
                  { nameEn: bIdentifier },
                  { nameAr: bIdentifier },
                  { slug: bIdentifier },
                ],
              });
            }

            if (foundBrand) brandName = foundBrand.nameEn;
          }

          // Handle Image Uploads to Cloudinary (ONLY if not preview)
          let finalImageCover = row.imageCover || existingProduct.imageCover;
          let finalImages = row.images
            ? row.images.split(',')
            : existingProduct.images || [];

          if (!isPreview) {
            if (row.imageCover) {
              // eslint-disable-next-line no-await-in-loop
              finalImageCover = await uploadExternalImage(
                row.imageCover,
                existingProduct._id,
                true,
              );
            }

            if (row.images) {
              const imgArray = row.images.split(',');
              // eslint-disable-next-line no-await-in-loop
              finalImages = await Promise.all(
                imgArray.map((img) =>
                  uploadExternalImage(img.trim(), existingProduct._id, false),
                ),
              );

              finalImages = finalImages.filter(Boolean);

              // Delete any old extra images that are no longer in the updated list
              if (finalImages.length > 0) {
                const oldImages = existingProduct.images || [];
                for (const oldImg of oldImages) {
                  if (oldImg && !finalImages.includes(oldImg)) {
                    // eslint-disable-next-line no-await-in-loop
                    await deleteCloudinaryImage(oldImg);
                  }
                }
              }
            }

            // Keep updates resilient: use first valid gallery image, otherwise keep old cover.
            if (!finalImageCover && finalImages.length > 0) {
              [finalImageCover] = finalImages;
            }
            if (!finalImageCover) {
              // eslint-disable-next-line no-await-in-loop
              const existingCoverIsReachable = await isImageUrlReachable(
                existingProduct.imageCover,
              );
              finalImageCover = existingCoverIsReachable
                ? existingProduct.imageCover
                : IMPORT_FALLBACK_COVER_IMAGE;
            }

            // If cover changed, cleanup old cover on our Cloudinary cloud.
            if (
              finalImageCover &&
              existingProduct.imageCover &&
              existingProduct.imageCover !== finalImageCover
            ) {
              // eslint-disable-next-line no-await-in-loop
              await deleteCloudinaryImage(existingProduct.imageCover);
            }
          }

          // PARTIAL UPDATE: Only include fields that are present in the Excel row
          const updateData = {};

          // Category is always updated when provided (required for category-only mode)
          if (categoryObjectIds.length > 0)
            updateData.category = categoryObjectIds;

          // Only update other fields if they are actually present in the row
          if (
            row.nameEn !== undefined &&
            row.nameEn !== null &&
            row.nameEn !== ''
          )
            updateData.nameEn = row.nameEn;
          if (
            row.nameAr !== undefined &&
            row.nameAr !== null &&
            row.nameAr !== ''
          )
            updateData.nameAr = row.nameAr;
          if (row.descriptionEn)
            updateData.descriptionEn = row.descriptionEn.split(',');
          if (row.descriptionAr)
            updateData.descriptionAr = row.descriptionAr.split(',');
          if (brandName && row.brand) updateData.brand = brandName;
          if (provider) updateData.provider = provider._id;
          if (row.stock !== undefined && row.stock !== null && row.stock !== '')
            updateData.stock = Number(row.stock) || 0;
          if (
            row.isFeatured !== undefined &&
            row.isFeatured !== null &&
            row.isFeatured !== ''
          )
            updateData.isFeatured = Boolean(Number(row.isFeatured));
          if (row.price !== undefined && row.price !== null && row.price !== '')
            updateData.price = Number(row.price) || 0;
          if (
            row.purchasePrice !== undefined &&
            row.purchasePrice !== null &&
            row.purchasePrice !== ''
          )
            updateData.purchasePrice = Number(row.purchasePrice) || 0;
          if (
            row.salePrice !== undefined &&
            row.salePrice !== null &&
            row.salePrice !== ''
          )
            updateData.salePrice = Number(row.salePrice) || 0;
          if (row.ratingsAverage !== undefined && row.ratingsAverage !== '')
            updateData.ratingsAverage = Number(row.ratingsAverage) || 0;
          if (row.ratingsQuantity !== undefined && row.ratingsQuantity !== '')
            updateData.ratingsQuantity = Number(row.ratingsQuantity) || 0;
          if (finalImageCover) updateData.imageCover = finalImageCover;
          if (finalImages && finalImages.length > 0)
            updateData.images = finalImages;

          if (isPreview) {
            // Calculate changes for preview — only show fields that are actually in updateData
            const changes = {};
            if (
              'nameAr' in updateData &&
              existingProduct.nameAr !== updateData.nameAr
            )
              changes.nameAr = {
                old: existingProduct.nameAr,
                new: updateData.nameAr,
              };
            if (
              'nameEn' in updateData &&
              existingProduct.nameEn !== updateData.nameEn
            )
              changes.nameEn = {
                old: existingProduct.nameEn,
                new: updateData.nameEn,
              };
            if (
              'price' in updateData &&
              existingProduct.price !== updateData.price
            )
              changes.price = {
                old: existingProduct.price,
                new: updateData.price,
              };
            if (
              'purchasePrice' in updateData &&
              existingProduct.purchasePrice !== updateData.purchasePrice
            )
              changes.purchasePrice = {
                old: existingProduct.purchasePrice,
                new: updateData.purchasePrice,
              };
            if (
              'salePrice' in updateData &&
              existingProduct.salePrice !== updateData.salePrice
            )
              changes.salePrice = {
                old: existingProduct.salePrice,
                new: updateData.salePrice,
              };
            if (
              'stock' in updateData &&
              existingProduct.stock !== updateData.stock
            )
              changes.stock = {
                old: existingProduct.stock,
                new: updateData.stock,
              };
            if (
              'brand' in updateData &&
              existingProduct.brand !== updateData.brand
            )
              changes.brand = {
                old: existingProduct.brand,
                new: updateData.brand,
              };
            if ('category' in updateData)
              changes.category = {
                old: '(previous)',
                new: categoryNamesForPreview.join(', '),
              };

            updatedProducts.push({
              nameAr: updateData.nameAr || existingProduct.nameAr,
              barCode: row.barCode,
              changes,
              categoryNames: categoryNamesForPreview.join(', '),
              providerName: (provider && provider.storeName) || 'All Providers',
            });
          } else if (isCategoryOnlyUpdate) {
            // CATEGORY-ONLY: update ALL products with this barcode across all providers
            // eslint-disable-next-line no-await-in-loop
            await Product.updateMany(
              { barCode: row.barCode.toString().trim() },
              { $set: { category: categoryObjectIds } },
            );
            updatedProducts.push(row.barCode);
          } else {
            // Update single product (full update path)
            // eslint-disable-next-line no-await-in-loop
            const updatedProd = await Product.findByIdAndUpdate(
              existingProduct._id,
              { $set: updateData },
              { new: true },
            );

            // Check if restocked
            if (
              updateData.stock > 0 &&
              (!existingProduct.stock || existingProduct.stock <= 0)
            ) {
              notifyPastBuyersProductBackInStock(updatedProd).catch((err) =>
                console.error(
                  'Failed to send back-in-stock notification during import:',
                  err.message,
                ),
              );
            }

            updatedProducts.push(existingProduct.barCode);
          }
        } else {
          if (isPriceOnlyUpdate) {
            missingProducts.push({
              row: index + 2,
              barCode: row.barCode.toString().trim(),
              provider: row.provider ? row.provider.toString().trim() : '',
              purchasePrice:
                row.purchasePrice !== undefined && row.purchasePrice !== null
                  ? Number(row.purchasePrice) || 0
                  : undefined,
              costPrice:
                row.costPrice !== undefined && row.costPrice !== null
                  ? Number(row.costPrice) || 0
                  : undefined,
              price:
                row.price !== undefined && row.price !== null
                  ? Number(row.price) || 0
                  : undefined,
            });
            // eslint-disable-next-line no-continue
            continue;
          }

          // NEW PRODUCT: Create
          // SMART CATEGORY RESOLUTION (NEW PRODUCT)
          const rawCategoryValueNew = (row.category || '').toString().trim();
          const categoryIdentifiersNew = rawCategoryValueNew
            ? [
                ...new Set(
                  rawCategoryValueNew
                    .split(',')
                    .map((val) => val.trim())
                    .filter(Boolean),
                ),
              ]
            : [];

          const categoryObjectIdsNew = [];
          const categoryNamesForPreviewNew = [];

          for (const identifier of categoryIdentifiersNew) {
            let foundCategory = null;

            if (/^\d+$/.test(identifier)) {
              foundCategory = await Category.findOne({
                id: Number(identifier),
              });
            }

            if (!foundCategory && mongoose.Types.ObjectId.isValid(identifier)) {
              foundCategory = await Category.findById(identifier);
            }

            if (!foundCategory) {
              foundCategory = await Category.findOne({
                $or: [
                  { nameAr: identifier },
                  { nameEn: identifier },
                  { slug: identifier },
                ],
              });
            }

            if (foundCategory) {
              categoryObjectIdsNew.push(foundCategory._id);
              categoryNamesForPreviewNew.push(foundCategory.nameAr);
            }
          }

          if (
            categoryIdentifiersNew.length > 0 &&
            categoryObjectIdsNew.length === 0
          ) {
            errors.push({
              row: index + 2,
              message: `None of the provided category identifiers were found: ${row.category}`,
              name: row.nameAr,
            });
            // eslint-disable-next-line no-continue
            continue;
          }

          // SMART BRAND RESOLUTION (NEW PRODUCT)
          let brandNameForNew = row.brand;
          if (row.brand) {
            let foundBrand = null;
            const bIdentifier = row.brand.toString().trim();

            if (/^\d+$/.test(bIdentifier)) {
              foundBrand = await Brand.findOne({ id: Number(bIdentifier) });
            }

            if (!foundBrand && mongoose.Types.ObjectId.isValid(bIdentifier)) {
              foundBrand = await Brand.findById(bIdentifier);
            }

            if (!foundBrand) {
              foundBrand = await Brand.findOne({
                $or: [
                  { nameEn: bIdentifier },
                  { nameAr: bIdentifier },
                  { slug: bIdentifier },
                ],
              });
            }

            if (foundBrand) brandNameForNew = foundBrand.nameEn;
          }

          // Generate potential ID for new product to use in folder name
          const newProductId = new mongoose.Types.ObjectId();

          // Handle Image Uploads to Cloudinary (ONLY if not preview)
          let finalImageCoverNew = row.imageCover;
          let finalImagesNew = row.images ? row.images.split(',') : [];

          if (!isPreview) {
            if (row.imageCover) {
              // eslint-disable-next-line no-await-in-loop
              finalImageCoverNew = await uploadExternalImage(
                row.imageCover,
                newProductId,
                true,
              );
            }

            if (row.images) {
              const imgArray = row.images.split(',');
              // eslint-disable-next-line no-await-in-loop
              finalImagesNew = await Promise.all(
                imgArray.map((img) =>
                  uploadExternalImage(img.trim(), newProductId, false),
                ),
              );
            }

            // Ensure new products always have a valid cover image to satisfy schema validation.
            finalImagesNew = finalImagesNew.filter(Boolean);
            if (!finalImageCoverNew && finalImagesNew.length > 0) {
              [finalImageCoverNew] = finalImagesNew;
            }
            if (!finalImageCoverNew) {
              finalImageCoverNew = IMPORT_FALLBACK_COVER_IMAGE;
            }
          }

          const productData = {
            _id: newProductId,
            nameEn: row.nameEn,
            nameAr: row.nameAr,
            descriptionEn: row.descriptionEn
              ? row.descriptionEn.split(',')
              : [],
            descriptionAr: row.descriptionAr
              ? row.descriptionAr.split(',')
              : [],
            barCode: row.barCode,
            brand: brandNameForNew,
            category: categoryObjectIdsNew,
            provider: provider._id,
            stock: Number(row.stock) || 0,
            isFeatured: Boolean(Number(row.isFeatured)),
            price: Number(row.price) || 0,
            purchasePrice: Number(row.purchasePrice) || 0,
            salePrice: Number(row.salePrice) || 0,
            ratingsAverage: Number(row.ratingsAverage) || 0,
            ratingsQuantity: Number(row.ratingsQuantity) || 0,
            imageCover: finalImageCoverNew,
            images: finalImagesNew,
          };

          if (isPreview) {
            newProducts.push({
              ...productData,
              categoryNames: categoryNamesForPreviewNew.join(', '),
              providerName: provider.storeName,
            });
          } else {
            // eslint-disable-next-line no-await-in-loop
            await Product.create(productData);
            newProducts.push(row.barCode);
          }
        }
      } catch (err) {
        errors.push({
          row: index + 2,
          message: err.message,
          name: row.nameAr || 'Unknown',
        });
      }
    }

    // No file cleanup needed - using memory storage

    const { missingExport } = req.query;
    const shouldExportMissing =
      missingExport === 'true' && missingProducts.length > 0;

    if (shouldExportMissing) {
      const templateRows = missingProducts.map((item) => ({
        barCode: item.barCode || '',
        provider: item.provider || '',
        purchasePrice:
          item.purchasePrice !== undefined ? item.purchasePrice : '',
        costPrice: item.costPrice !== undefined ? item.costPrice : '',
        price: item.price !== undefined ? item.price : '',
        nameAr: '',
        nameEn: '',
        brand: '',
        category: '',
        stock: '',
        imageCover: '',
        images: '',
      }));

      const workbook = xlsx.utils.book_new();
      const worksheet = xlsx.utils.json_to_sheet(templateRows);
      xlsx.utils.book_append_sheet(workbook, worksheet, 'MissingProducts');

      const buffer = xlsx.write(workbook, { type: 'buffer', bookType: 'xlsx' });

      res.setHeader(
        'Content-Type',
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      );
      res.setHeader(
        'Content-Disposition',
        `attachment; filename=missing-products-${Date.now()}.xlsx`,
      );

      res.send(buffer);
      return;
    }

    res.status(200).json({
      status: 'success',
      mode: isPreview ? 'preview' : 'execute',
      summary: {
        newCount: newProducts.length,
        updatedCount: updatedProducts.length,
        missingCount: missingProducts.length,
        errorsCount: errors.length,
      },
      data: {
        newProducts,
        updatedProducts,
        missingProducts,
        errors,
      },
    });

    // Notify AI service to reload cache after batch import
    if (!isPreview && (newProducts.length > 0 || updatedProducts.length > 0)) {
      triggerAiReload();
    }
  } catch (error) {
    // No file cleanup needed - using memory storage

    console.error(error);
    res.status(500).json({
      status: 'error',
      message: 'Something went wrong',
      error: error.message,
    });
  }
};

// Export products to Excel file
exports.exportProducts = async (req, res) => {
  try {
    const isVendor = req.user.role === 'vendor';
    const query = {};

    if (isVendor) {
      if (!req.user.provider) {
        return res.status(400).json({
          status: 'fail',
          message: 'Vendor user is not associated with any provider',
        });
      }
      query.provider = req.user.provider;
    }

    // Add category filter if provided
    if (req.query.category) {
      query.category = req.query.category;
    }

    // Add brand filter if provided
    if (req.query.brand) {
      query.brand = req.query.brand;
    }

    // Fetch products with populated category and provider
    const products = await Product.find(query)
      .populate('category', 'id nameAr nameEn')
      .populate('provider', 'id storeName');

    // Prepare data for Excel
    const excelData = products.map((product) => ({
      nameEn: product.nameEn || '',
      nameAr: product.nameAr || '',
      descriptionEn: product.descriptionEn
        ? product.descriptionEn.join(',')
        : '',
      descriptionAr: product.descriptionAr
        ? product.descriptionAr.join(',')
        : '',
      barCode: product.barCode || '',
      brand:
        typeof product.brand === 'string' &&
        /^[0-9a-fA-F]{24}$/.test(product.brand)
          ? product.brand
          : product.brand || '',
      category:
        product.category && product.category.length > 0
          ? product.category.map((cat) => cat.id).join(',')
          : '',
      provider: product.provider ? product.provider.id : '',
      stock: product.stock || 0,
      isFeatured: product.isFeatured ? 1 : 0,
      price: product.price || 0,
      purchasePrice: product.purchasePrice || 0,
      salePrice: product.salePrice || 0,
      ratingsAverage: product.ratingsAverage || 0,
      ratingsQuantity: product.ratingsQuantity || 0,
      imageCover: product.imageCover || '',
      images:
        product.images && product.images.length > 0
          ? product.images.join(',')
          : '',
    }));

    // Create workbook and worksheet
    const workbook = xlsx.utils.book_new();
    const worksheet = xlsx.utils.json_to_sheet(excelData);

    // Add worksheet to workbook
    xlsx.utils.book_append_sheet(workbook, worksheet, 'Products');

    // Generate buffer
    const buffer = xlsx.write(workbook, { type: 'buffer', bookType: 'xlsx' });

    // Set headers for file download
    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    );
    res.setHeader(
      'Content-Disposition',
      `attachment; filename=products-export-${Date.now()}.xlsx`,
    );

    // Send file
    res.send(buffer);
  } catch (error) {
    console.error('Export error:', error);
    res.status(500).json({
      status: 'error',
      message: 'Failed to export products',
      error: error.message,
    });
  }
};

// Generate Facebook Product Catalog XML Feed
exports.getFacebookCatalog = async (req, res) => {
  try {
    // 1. Fetch products (limit to in-stock or all? Facebook usually wants all with availability status)
    const products = await Product.find()
      .populate('category', 'nameAr nameEn')
      .populate('brand', 'nameEn nameAr')
      .limit(2000); // Increased limit for Meta catalog

    // 2. Format as RSS 2.0 (Facebook XML standard)
    const frontendUrl = (
      process.env.FRONTEND_URL || 'https://www.samoulla.com'
    ).replace(/\/$/, '');

    let xml = `<?xml version="1.0"?>
<rss xmlns:g="http://base.google.com/ns/1.0" version="2.0">
  <channel>
    <title>Samoulla Product Catalog</title>
    <link>${frontendUrl}</link>
    <description>Quality tools, hardware, and equipment from Samoulla</description>`;

    products.forEach((product) => {
      const id = product._id.toString();
      const title = (product.nameEn || product.nameAr || '').replace(
        /[&<>"']/g,
        (m) =>
          ({
            '&': '&amp;',
            '<': '&lt;',
            '>': '&gt;',
            '"': '&quot;',
            "'": '&apos;',
          })[m],
      );
      const description = (
        (product.descriptionEn && product.descriptionEn[0]) ||
        (product.descriptionAr && product.descriptionAr[0]) ||
        title
      ).replace(
        /[&<>"']/g,
        (m) =>
          ({
            '&': '&amp;',
            '<': '&lt;',
            '>': '&gt;',
            '"': '&quot;',
            "'": '&apos;',
          })[m],
      );
      const link = `${frontendUrl}/product/${product.slug || id}`;
      const imageLink = product.imageCover || '';
      const price = `${product.price} EGP`;
      const availability = product.stock > 0 ? 'in stock' : 'out of stock';
      const brand = (product.brand || 'Samoulla').replace(
        /[&<>"']/g,
        (m) =>
          ({
            '&': '&amp;',
            '<': '&lt;',
            '>': '&gt;',
            '"': '&quot;',
            "'": '&apos;',
          })[m],
      );
      const categoryName =
        (product.category &&
          product.category[0] &&
          product.category[0].nameEn) ||
        'Tools & Equipment';
      const category = categoryName.replace(
        /[&<>"']/g,
        (m) =>
          ({
            '&': '&amp;',
            '<': '&lt;',
            '>': '&gt;',
            '"': '&quot;',
            "'": '&apos;',
          })[m],
      );

      xml += `
    <item>
      <g:id>${id}</g:id>
      <g:title>${title}</g:title>
      <g:description>${description}</g:description>
      <g:link>${link}</g:link>
      <g:image_link>${imageLink}</g:image_link>
      <g:condition>new</g:condition>
      <g:availability>${availability}</g:availability>
      <g:price>${price}</g:price>
      <g:brand>${brand}</g:brand>
      <g:google_product_category>${category}</g:google_product_category>
    </item>`;
    });

    xml += `
  </channel>
</rss>`;

    // 3. Send response with XML header
    res.set('Content-Type', 'text/xml');
    res.status(200).send(xml);
  } catch (error) {
    console.error('Facebook Catalog Error:', error);
    res.status(500).json({
      status: 'error',
      message: 'Failed to generate catalog',
    });
  }
};