const XLSX = require("xlsx");
const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");

const MAX_COMPETITOR_COLUMNS = 5;

module.exports = {
  getSellingInReport,
  getSellingInFilterOptions,
  exportSellingInReport,
};

function toArrayParam(value) {
  if (value == null || value === "") return [];
  if (Array.isArray(value)) {
    return value
      .flatMap((v) => String(v).split(","))
      .map((v) => v.trim())
      .filter(Boolean);
  }

  return String(value)
    .split(",")
    .map((v) => v.trim())
    .filter(Boolean);
}

function toNumberSafe(value, defaultValue = 0) {
  if (value == null || value === "") return defaultValue;
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : defaultValue;
}

function parseJsonArray(value) {
  if (value == null) return [];
  if (Array.isArray(value)) return value;
  if (typeof value === "object") return [value];
  if (typeof value !== "string") return [];

  try {
    const parsed = JSON.parse(value);
    if (Array.isArray(parsed)) return parsed;
    if (parsed && typeof parsed === "object") return [parsed];
    return [];
  } catch (_) {
    return [];
  }
}

function normalizeText(value) {
  if (value == null) return "";
  return String(value).trim();
}

function buildFilterIds(params) {
  return {
    companyIds: toArrayParam(params.company_id).map((v) => Number(v)).filter(Number.isFinite),
    territoryIds: toArrayParam(params.territory_id).map((v) => Number(v)).filter(Number.isFinite),
    periodIds: toArrayParam(params.period_id).map((v) => Number(v)).filter(Number.isFinite),
    yearIds: toArrayParam(params.year_id).map((v) => Number(v)).filter(Number.isFinite),
    outletIds: toArrayParam(params.outlet_id).map((v) => Number(v)).filter(Number.isFinite),
    productFilters: toArrayParam(params.product_id).map((v) => v.toLowerCase()),
    therapyClassFilters: toArrayParam(params.therapy_class).map((v) => v.toLowerCase()),
  };
}

function buildSearchQuery(params) {
  return normalizeText(params.search).toLowerCase();
}

function buildSort(params) {
  const sortBy = normalizeText(params.sort_by) || "outlet";
  const sortOrder = String(params.sort_order || "asc").toLowerCase() === "desc" ? "desc" : "asc";
  return { sortBy, sortOrder };
}

function buildPaging(params) {
  const page = Math.max(1, toNumberSafe(params.page, 1));
  const pageSize = Math.min(200, Math.max(1, toNumberSafe(params.page_size, 25)));
  return { page, pageSize };
}

function applyInFilter(where, key, values) {
  if (Array.isArray(values) && values.length > 0) {
    where[key] = { [Op.in]: values };
  }
}

function uniqueNumericIds(values) {
  return [...new Set((values || []).map((v) => Number(v)).filter(Number.isFinite))];
}

async function resolveTerritoryHierarchy(filterIds) {
  const selectedTerritoryIds = uniqueNumericIds(filterIds.territoryIds);
  if (selectedTerritoryIds.length === 0) {
    return {
      success: true,
      expandedTerritoryIds: [],
    };
  }

  if (filterIds.periodIds.length !== 1) {
    return {
      success: false,
      message: "Territory hierarchy membutuhkan tepat 1 period. Silakan pilih hanya satu period.",
    };
  }

  const periodId = filterIds.periodIds[0];
  const period = await dbSAPPS.Period.findByPk(periodId, {
    attributes: ["id", "start_date", "end_date"],
  });

  if (!period) {
    return {
      success: false,
      message: "Period tidak ditemukan untuk perhitungan hierarchy territory.",
    };
  }

  const versionWhere = {
    [Op.and]: [{ valid_from: { [Op.lte]: period.end_date } }, { [Op.or]: [{ valid_to: { [Op.gte]: period.start_date } }, { valid_to: null }] }],
  };

  const companyIds = uniqueNumericIds(filterIds.companyIds);
  if (companyIds.length === 1) {
    versionWhere.company_id = companyIds[0];
  } else if (companyIds.length > 1) {
    versionWhere.company_id = { [Op.in]: companyIds };
  }

  const versions = await dbSAPPS.TerritoryVersion.findAll({
    attributes: ["id", "company_id", "valid_from", "valid_to"],
    where: versionWhere,
    include: [
      {
        model: dbSAPPS.TerritoryVersionChild,
        as: "territoryversionchilds",
        attributes: ["id", "parent_id", "territory_id", "company_id"],
      },
    ],
  });

  const childRows = versions.reduce((acc, version) => {
    const rows = Array.isArray(version?.territoryversionchilds) ? version.territoryversionchilds : [];
    if (companyIds.length === 0) return [...acc, ...rows];
    const filtered = rows.filter((row) => row?.company_id == null || companyIds.includes(Number(row.company_id)));
    return [...acc, ...filtered];
  }, []);

  const byParent = childRows.reduce((acc, row) => {
    const key = row?.parent_id == null ? "__root__" : String(row.parent_id);
    if (!acc.has(key)) acc.set(key, []);
    acc.get(key).push(row);
    return acc;
  }, new Map());

  const collected = new Set(selectedTerritoryIds.map((id) => String(id)));
  const queue = [...selectedTerritoryIds];
  const MAX_VISIT = 20000;
  let visitCount = 0;

  while (queue.length > 0 && visitCount < MAX_VISIT) {
    const currentId = queue.shift();
    const children = byParent.get(String(currentId)) || [];
    for (const child of children) {
      const childTerritoryId = Number(child?.territory_id);
      if (!Number.isFinite(childTerritoryId)) continue;
      const key = String(childTerritoryId);
      if (collected.has(key)) continue;
      collected.add(key);
      queue.push(childTerritoryId);
    }
    visitCount += 1;
  }

  return {
    success: true,
    expandedTerritoryIds: [...collected].map((v) => Number(v)).filter(Number.isFinite),
  };
}

function resolveCompetitorName(item, competitorValue) {
  if (competitorValue == null || competitorValue === "" || competitorValue === "Not Set") return "";

  const competitors = Array.isArray(item.itemcompetitors) ? item.itemcompetitors : [];
  const fromArray = competitors.find((comp) => String(comp?.id) === String(competitorValue));
  if (fromArray?.display_name) return fromArray.display_name;

  if (typeof competitorValue === "string") return competitorValue;
  return String(competitorValue);
}

function sanitizeTherapyClass(therapy) {
  const raw = normalizeText(therapy);
  if (!raw) return "Unknown";
  const firstPart = raw.split("-")[0]?.trim();
  return firstPart || raw;
}

function rowHasSearch(row, search) {
  if (!search) return true;
  return [row.outlet, row.product, row.therapy_class, row.competitor]
    .map((v) => String(v || "").toLowerCase())
    .some((v) => v.includes(search));
}

function compareRows(a, b, sortBy, sortOrder) {
  const left = a?.[sortBy];
  const right = b?.[sortBy];

  const leftNum = Number(left);
  const rightNum = Number(right);
  const bothNumeric = Number.isFinite(leftNum) && Number.isFinite(rightNum);

  let compared;
  if (bothNumeric) {
    compared = leftNum - rightNum;
  } else {
    compared = String(left || "").localeCompare(String(right || ""));
  }

  return sortOrder === "asc" ? compared : -compared;
}

async function fetchRawSellingInRows(filterIds, expandedTerritoryIds = []) {
  const surveyWhere = {};
  const sellingInWhere = {};

  applyInFilter(surveyWhere, "company_id", filterIds.companyIds);
  applyInFilter(surveyWhere, "territory_id", expandedTerritoryIds.length > 0 ? expandedTerritoryIds : filterIds.territoryIds);
  applyInFilter(surveyWhere, "period_id", filterIds.periodIds);
  applyInFilter(surveyWhere, "year_id", filterIds.yearIds);

  applyInFilter(sellingInWhere, "outlet_id", filterIds.outletIds);

  return dbSAPPS.SurveySellingIn.findAll({
    where: sellingInWhere,
    attributes: ["id", "survey_id", "outlet_id", "company_id", "sellingin_details", "creation"],
    include: [
      {
        model: dbSAPPS.Survey,
        as: "survey",
        required: true,
        where: surveyWhere,
        attributes: ["id", "company_id", "territory_id", "period_id", "year_id"],
        include: [
          { model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name"] },
          { model: dbSAPPS.Territory, as: "territory", attributes: ["id", "display_name"] },
          { model: dbSAPPS.Period, as: "period", attributes: ["id", "display_name"] },
          { model: dbSAPPS.Year, as: "year", attributes: ["id", "display_name"] },
        ],
      },
      {
        model: dbSAPPS.Outlet,
        as: "outlet",
        attributes: ["id", "display_name"],
      },
      {
        model: dbSAPPS.Company,
        as: "company",
        attributes: ["id", "display_name"],
      },
    ],
    order: [["id", "DESC"]],
  });
}

function flattenSellingInRows(rawRows, filterIds) {
  const flatRows = [];
  const productNoCompetitorSet = new Set();
  const outletSet = new Set();
  const productSet = new Set();

  for (const record of rawRows) {
    const survey = record?.survey;
    const outletId = record?.outlet_id;
    const outletName = record?.outlet?.display_name || "Unknown Outlet";
    const companyName = survey?.company?.display_name || record?.company?.display_name || "Unknown Company";
    const territoryName = survey?.territory?.display_name || "Unknown Territory";
    const periodName = survey?.period?.display_name || "Unknown Period";
    const yearName = survey?.year?.display_name || "Unknown Year";

    const details = parseJsonArray(record?.sellingin_details);

    outletSet.add(String(outletId));

    for (const item of details) {
      if (!item || typeof item !== "object") continue;

      const productName = normalizeText(item.display_name) || "Unknown Product";
      const therapyClass = sanitizeTherapyClass(item.therapy_class);
      const sapPrice = toNumberSafe(item.price, 0);
      const sapQty = toNumberSafe(item.qty_sap, 0);

      productSet.add(productName);

      let hasCompetitor = false;

      for (let i = 1; i <= MAX_COMPETITOR_COLUMNS; i += 1) {
        const competitorValue = item[`compt_${i}`];
        const competitorName = resolveCompetitorName(item, competitorValue);
        const competitorQty = toNumberSafe(item[`qty_compt_${i}`], 0);
        const competitorPrice = toNumberSafe(item[`compt_${i}_price`], 0);

        const isMeaningfulCompetitor = Boolean(competitorName) || competitorQty > 0 || competitorPrice > 0;
        if (!isMeaningfulCompetitor) continue;

        hasCompetitor = true;

        flatRows.push({
          survey_id: record?.survey_id,
          survey_selling_in_id: record?.id,
          outlet_id: outletId,
          outlet: outletName,
          company_id: survey?.company_id,
          company: companyName,
          territory_id: survey?.territory_id,
          territory: territoryName,
          period_id: survey?.period_id,
          period: periodName,
          year_id: survey?.year_id,
          year: yearName,
          product: productName,
          product_key: productName.toLowerCase(),
          therapy_class: therapyClass,
          therapy_class_key: therapyClass.toLowerCase(),
          sap_price: sapPrice,
          sap_qty: sapQty,
          competitor: competitorName || "Unknown Competitor",
          competitor_qty: competitorQty,
          competitor_price: competitorPrice,
          has_competitor: true,
        });
      }

      if (!hasCompetitor) {
        const opportunityKey = `${productName.toLowerCase()}|${therapyClass.toLowerCase()}`;
        productNoCompetitorSet.add(opportunityKey);

        flatRows.push({
          survey_id: record?.survey_id,
          survey_selling_in_id: record?.id,
          outlet_id: outletId,
          outlet: outletName,
          company_id: survey?.company_id,
          company: companyName,
          territory_id: survey?.territory_id,
          territory: territoryName,
          period_id: survey?.period_id,
          period: periodName,
          year_id: survey?.year_id,
          year: yearName,
          product: productName,
          product_key: productName.toLowerCase(),
          therapy_class: therapyClass,
          therapy_class_key: therapyClass.toLowerCase(),
          sap_price: sapPrice,
          sap_qty: sapQty,
          competitor: "",
          competitor_qty: 0,
          competitor_price: 0,
          has_competitor: false,
        });
      }
    }
  }

  let filteredRows = flatRows;

  if (filterIds.productFilters.length > 0) {
    filteredRows = filteredRows.filter((row) => filterIds.productFilters.includes(row.product_key));
  }

  if (filterIds.therapyClassFilters.length > 0) {
    filteredRows = filteredRows.filter((row) => filterIds.therapyClassFilters.includes(row.therapy_class_key));
  }

  return {
    rows: filteredRows,
    allRows: flatRows,
    productNoCompetitorSet,
    totalOutlets: outletSet.size,
    totalProducts: productSet.size,
  };
}

function buildAnalytics(rows) {
  const competitorRows = rows.filter((row) => row.has_competitor);

  const outletWithCompetitorSet = new Set(competitorRows.map((row) => String(row.outlet_id)));
  const outletSet = new Set(rows.map((row) => String(row.outlet_id)));
  const productSet = new Set(rows.map((row) => row.product));

  const competitorCountByBrand = new Map();
  const pressureByProduct = new Map();
  const intensityByOutlet = new Map();
  const therapyDistribution = new Map();

  let totalCompetitorPrice = 0;
  let totalCompetitorPriceCount = 0;
  let totalSapQty = 0;
  let totalCompetitorQty = 0;

  for (const row of competitorRows) {
    competitorCountByBrand.set(row.competitor, (competitorCountByBrand.get(row.competitor) || 0) + 1);
    pressureByProduct.set(row.product, (pressureByProduct.get(row.product) || 0) + 1);
    intensityByOutlet.set(row.outlet, (intensityByOutlet.get(row.outlet) || 0) + 1);
    therapyDistribution.set(row.therapy_class, (therapyDistribution.get(row.therapy_class) || 0) + 1);

    if (row.competitor_price > 0) {
      totalCompetitorPrice += row.competitor_price;
      totalCompetitorPriceCount += 1;
    }

    totalSapQty += toNumberSafe(row.sap_qty, 0);
    totalCompetitorQty += toNumberSafe(row.competitor_qty, 0);
  }

  const topCompetitorBrands = [...competitorCountByBrand.entries()]
    .map(([name, count]) => ({ name, count }))
    .sort((a, b) => b.count - a.count)
    .slice(0, 10);

  const productCompetitionPressure = [...pressureByProduct.entries()]
    .map(([product, competitor_count]) => ({ product, competitor_count }))
    .sort((a, b) => b.competitor_count - a.competitor_count);

  const outletCompetitionIntensity = [...intensityByOutlet.entries()]
    .map(([outlet, competitor_count]) => ({ outlet, competitor_count }))
    .sort((a, b) => b.competitor_count - a.competitor_count);

  const therapyClassCompetitionDistribution = [...therapyDistribution.entries()]
    .map(([therapy_class, competitor_count]) => ({ therapy_class, competitor_count }))
    .sort((a, b) => b.competitor_count - a.competitor_count);

  const averageCompetitorPrice = totalCompetitorPriceCount > 0 ? totalCompetitorPrice / totalCompetitorPriceCount : 0;
  const totalOutlets = outletSet.size;

  const competitorPenetrationRate = totalOutlets > 0 ? (outletWithCompetitorSet.size / totalOutlets) * 100 : 0;
  const competitorPenetrationScore = totalOutlets > 0 ? competitorRows.length / totalOutlets : 0;

  const marketDominanceIndicator = {
    sap_qty: totalSapQty,
    competitor_qty: totalCompetitorQty,
    ratio: totalCompetitorQty > 0 ? totalSapQty / totalCompetitorQty : null,
    delta: totalSapQty - totalCompetitorQty,
  };

  return {
    totalOutletSurveyed: totalOutlets,
    totalProductsSurveyed: productSet.size,
    totalCompetitorEntries: competitorRows.length,
    topCompetitorBrands,
    competitorPenetrationRate,
    productsWithHighestCompetitorPressure: productCompetitionPressure.slice(0, 10),
    outletCompetitionIntensity: outletCompetitionIntensity.slice(0, 10),
    averageCompetitorPrice,
    therapyClassCompetitionDistribution,

    competitorPenetrationScore,
    marketDominanceIndicator,
    competitiveHeatmap: outletCompetitionIntensity,
    therapyCompetitionMap: therapyClassCompetitionDistribution,

    chartData: {
      competitorDistribution: topCompetitorBrands,
      productCompetitionPressure,
      outletCompetitionIntensity,
      therapyClassCompetition: therapyClassCompetitionDistribution,
      priceComparison: competitorRows
        .slice(0, 200)
        .map((row) => ({
          product: row.product,
          competitor: row.competitor,
          sap_price: row.sap_price,
          competitor_price: row.competitor_price,
        })),
    },
  };
}

function buildFilterOptionsFromRows(rows) {
  const outletMap = new Map();
  const productMap = new Map();
  const therapyMap = new Map();

  for (const row of rows) {
    if (row.outlet_id != null && !outletMap.has(row.outlet_id)) {
      outletMap.set(row.outlet_id, { id: row.outlet_id, display_name: row.outlet });
    }

    if (row.product && !productMap.has(row.product_key)) {
      productMap.set(row.product_key, { id: row.product_key, display_name: row.product });
    }

    if (row.therapy_class && !therapyMap.has(row.therapy_class_key)) {
      therapyMap.set(row.therapy_class_key, { id: row.therapy_class_key, display_name: row.therapy_class });
    }
  }

  return {
    outlets: [...outletMap.values()].sort((a, b) => a.display_name.localeCompare(b.display_name)),
    products: [...productMap.values()].sort((a, b) => a.display_name.localeCompare(b.display_name)),
    therapyClasses: [...therapyMap.values()].sort((a, b) => a.display_name.localeCompare(b.display_name)),
  };
}

async function buildDbFilterOptions(filterIds, expandedTerritoryIds = []) {
  const surveyWhere = {};
  applyInFilter(surveyWhere, "company_id", filterIds.companyIds);
  applyInFilter(surveyWhere, "territory_id", expandedTerritoryIds.length > 0 ? expandedTerritoryIds : filterIds.territoryIds);
  applyInFilter(surveyWhere, "period_id", filterIds.periodIds);
  applyInFilter(surveyWhere, "year_id", filterIds.yearIds);

  const surveys = await dbSAPPS.Survey.findAll({
    where: surveyWhere,
    attributes: ["company_id", "territory_id", "period_id", "year_id"],
    raw: true,
  });

  const companyIds = [...new Set(surveys.map((s) => s.company_id).filter(Boolean))];
  const territoryIds = [...new Set(surveys.map((s) => s.territory_id).filter(Boolean))];
  const periodIds = [...new Set(surveys.map((s) => s.period_id).filter(Boolean))];
  const yearIds = [...new Set(surveys.map((s) => s.year_id).filter(Boolean))];

  const [companies, territories, periods, years] = await Promise.all([
    companyIds.length > 0
      ? dbSAPPS.Company.findAll({ where: { id: { [Op.in]: companyIds } }, attributes: ["id", "display_name"] })
      : [],
    territoryIds.length > 0
      ? dbSAPPS.Territory.findAll({ where: { id: { [Op.in]: territoryIds } }, attributes: ["id", "display_name"] })
      : [],
    periodIds.length > 0
      ? dbSAPPS.Period.findAll({ where: { id: { [Op.in]: periodIds } }, attributes: ["id", "display_name"] })
      : [],
    yearIds.length > 0
      ? dbSAPPS.Year.findAll({ where: { id: { [Op.in]: yearIds } }, attributes: ["id", "display_name"] })
      : [],
  ]);

  return {
    companies: companies.map((c) => ({ id: c.id, display_name: c.display_name })).sort((a, b) => a.display_name.localeCompare(b.display_name)),
    territories: territories.map((t) => ({ id: t.id, display_name: t.display_name })).sort((a, b) => a.display_name.localeCompare(b.display_name)),
    periods: periods.map((p) => ({ id: p.id, display_name: p.display_name })).sort((a, b) => a.display_name.localeCompare(b.display_name)),
    years: years.map((y) => ({ id: y.id, display_name: y.display_name })).sort((a, b) => a.display_name.localeCompare(b.display_name)),
  };
}

async function getSellingInReport(params = {}) {
  const filterIds = buildFilterIds(params);
  const search = buildSearchQuery(params);
  const { sortBy, sortOrder } = buildSort(params);
  const { page, pageSize } = buildPaging(params);

  const territoryResolution = await resolveTerritoryHierarchy(filterIds);
  if (!territoryResolution.success) {
    return {
      success: false,
      message: territoryResolution.message,
      data: [],
      pagination: {
        page: 1,
        page_size: pageSize,
        total_rows: 0,
        total_pages: 1,
      },
      cards: {},
      analytics: {},
      filters: {
        companies: [],
        territories: [],
        periods: [],
        years: [],
        outlets: [],
        products: [],
        therapyClasses: [],
      },
    };
  }

  const rawRows = await fetchRawSellingInRows(filterIds, territoryResolution.expandedTerritoryIds);
  const flattened = flattenSellingInRows(rawRows, filterIds);

  const searchedRows = flattened.rows.filter((row) => rowHasSearch(row, search));
  const sortedRows = [...searchedRows].sort((a, b) => compareRows(a, b, sortBy, sortOrder));

  const totalRows = sortedRows.length;
  const totalPages = Math.max(1, Math.ceil(totalRows / pageSize));
  const normalizedPage = Math.min(page, totalPages);
  const startIndex = (normalizedPage - 1) * pageSize;
  const endIndex = startIndex + pageSize;

  const pageRows = sortedRows.slice(startIndex, endIndex).map((row, index) => ({
    id: `${row.survey_selling_in_id}-${row.product_key}-${row.competitor || "none"}-${startIndex + index}`,
    outlet: row.outlet,
    product: row.product,
    therapy_class: row.therapy_class,
    sap_price: row.sap_price,
    competitor: row.competitor || "-",
    competitor_qty: row.competitor_qty,
    competitor_price: row.competitor_price,
    company: row.company,
    territory: row.territory,
    period: row.period,
    year: row.year,
  }));

  const analytics = buildAnalytics(sortedRows);
  const filterOptions = buildFilterOptionsFromRows(flattened.rows);
  const dbFilterOptions = await buildDbFilterOptions(filterIds, territoryResolution.expandedTerritoryIds);

  const productsWithoutCompetitors = sortedRows
    .filter((row) => !row.has_competitor)
    .reduce((acc, row) => {
      const key = `${row.product_key}|${row.therapy_class_key}`;
      if (!acc.some((item) => item.key === key)) {
        acc.push({ key, product: row.product, therapy_class: row.therapy_class });
      }
      return acc;
    }, [])
    .map(({ product, therapy_class }) => ({ product, therapy_class }));

  return {
    success: true,
    message: "Survey selling-in report loaded successfully.",
    data: pageRows,
    pagination: {
      page: normalizedPage,
      page_size: pageSize,
      total_rows: totalRows,
      total_pages: totalPages,
    },
    cards: {
      totalOutlets: analytics.totalOutletSurveyed,
      totalProducts: analytics.totalProductsSurveyed,
      totalCompetitors: analytics.totalCompetitorEntries,
      averageCompetitorPrice: analytics.averageCompetitorPrice,
      highestCompetitionProduct: analytics.productsWithHighestCompetitorPressure[0]?.product || "-",
      outletWithMostCompetition: analytics.outletCompetitionIntensity[0]?.outlet || "-",
    },
    analytics: {
      ...analytics,
      productsWithoutCompetitors,
    },
    filters: {
      ...dbFilterOptions,
      outlets: filterOptions.outlets,
      products: filterOptions.products,
      therapyClasses: filterOptions.therapyClasses,
    },
    debug: {
      territory_ids_input: uniqueNumericIds(filterIds.territoryIds),
      territory_ids_hierarchy: uniqueNumericIds(territoryResolution.expandedTerritoryIds),
      period_ids: uniqueNumericIds(filterIds.periodIds),
    },
  };
}

async function getSellingInFilterOptions(params = {}) {
  const field = normalizeText(params.field).toLowerCase();
  const query = normalizeText(params.q).toLowerCase();

  const filterIds = buildFilterIds(params);

  const reportData = await getSellingInReport({ ...params, page: 1, page_size: 1 });
  if (!reportData?.success) {
    return {
      success: false,
      message: reportData?.message || "Gagal memuat filter options Selling In.",
      field,
      options: [],
      active_filters: filterIds,
    };
  }

  const filterCollection = reportData?.filters || {};

  const fieldMap = {
    company: filterCollection.companies || [],
    territory: filterCollection.territories || [],
    period: filterCollection.periods || [],
    year: filterCollection.years || [],
    outlet: filterCollection.outlets || [],
    product: filterCollection.products || [],
    therapy_class: filterCollection.therapyClasses || [],
  };

  const baseOptions = fieldMap[field] || [];
  const options = baseOptions
    .filter((opt) => String(opt.display_name || "").toLowerCase().includes(query))
    .slice(0, 100);

  return {
    success: true,
    message: "Selling-in filter options loaded successfully.",
    field,
    options,
    active_filters: filterIds,
  };
}

function resolveExportMonth(rawRows) {
  const firstSurveyPeriod = rawRows?.[0]?.survey?.period?.display_name || "";
  const monthFromLabel = firstSurveyPeriod.match(/\d{1,2}/)?.[0];
  const month = Number(monthFromLabel);
  if (Number.isFinite(month) && month >= 1 && month <= 12) {
    return String(month).padStart(2, "0");
  }

  const now = new Date();
  return String(now.getMonth() + 1).padStart(2, "0");
}

function ensureRowsForSheet(rows, emptyLabel = "No data") {
  if (Array.isArray(rows) && rows.length > 0) return rows;
  return [{ info: emptyLabel }];
}

function appendSheet(workbook, sheetName, rows) {
  const worksheet = XLSX.utils.json_to_sheet(ensureRowsForSheet(rows));
  XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
}

async function exportSellingInReport(params = {}) {
  const filterIds = buildFilterIds(params);
  const search = buildSearchQuery(params);
  const { sortBy, sortOrder } = buildSort(params);

  const territoryResolution = await resolveTerritoryHierarchy(filterIds);
  if (!territoryResolution.success) {
    const error = new Error(territoryResolution.message);
    error.status = 400;
    throw error;
  }

  const rawRows = await fetchRawSellingInRows(filterIds, territoryResolution.expandedTerritoryIds);
  const flattened = flattenSellingInRows(rawRows, filterIds);

  const rows = [...flattened.rows]
    .filter((row) => rowHasSearch(row, search))
    .sort((a, b) => compareRows(a, b, sortBy, sortOrder));

  const analytics = buildAnalytics(rows);
  const productsWithoutCompetitors = rows
    .filter((row) => !row.has_competitor)
    .reduce((acc, row) => {
      const key = `${row.product_key}|${row.therapy_class_key}`;
      if (!acc.some((item) => item.key === key)) {
        acc.push({ key, product: row.product, therapy_class: row.therapy_class });
      }
      return acc;
    }, [])
    .map(({ product, therapy_class }) => ({ product, therapy_class }));

  const cardRows = [
    { metric: "Total Outlets", value: analytics.totalOutletSurveyed },
    { metric: "Total Products", value: analytics.totalProductsSurveyed },
    { metric: "Total Competitors", value: analytics.totalCompetitorEntries },
    { metric: "Average Competitor Price", value: analytics.averageCompetitorPrice },
    {
      metric: "Highest Competition Product",
      value: analytics.productsWithHighestCompetitorPressure?.[0]?.product || "-",
    },
    {
      metric: "Outlet With Most Competition",
      value: analytics.outletCompetitionIntensity?.[0]?.outlet || "-",
    },
    { metric: "Competitor Penetration Rate", value: analytics.competitorPenetrationRate },
    { metric: "Competitor Penetration Score", value: analytics.competitorPenetrationScore },
  ];

  const exportRows = rows.map((row) => ({
    Outlet: row.outlet,
    Territory: row.territory,
    Period: row.period,
    Year: row.year,
    Company: row.company,
    Product: row.product,
    Therapy: row.therapy_class,
    "SAP Price": row.sap_price,
    Competitor: row.competitor || "-",
    Qty: row.competitor_qty,
    Price: row.competitor_price,
  }));

  const workbook = XLSX.utils.book_new();

  appendSheet(workbook, "DetailedData", exportRows);
  appendSheet(workbook, "Cards", cardRows);
  appendSheet(
    workbook,
    "Chart_CompetitorDist",
    (analytics.chartData?.competitorDistribution || []).map((item) => ({
      competitor: item.name,
      occurrence: item.count,
    })),
  );
  appendSheet(
    workbook,
    "Chart_ProductPressure",
    (analytics.chartData?.productCompetitionPressure || []).map((item) => ({
      product: item.product,
      competitor_count: item.competitor_count,
    })),
  );
  appendSheet(
    workbook,
    "Chart_OutletIntensity",
    (analytics.chartData?.outletCompetitionIntensity || []).map((item) => ({
      outlet: item.outlet,
      competitor_count: item.competitor_count,
    })),
  );
  appendSheet(
    workbook,
    "Chart_TherapyClass",
    (analytics.chartData?.therapyClassCompetition || []).map((item) => ({
      therapy_class: item.therapy_class,
      competitor_count: item.competitor_count,
    })),
  );
  appendSheet(
    workbook,
    "Chart_PriceCompare",
    (analytics.chartData?.priceComparison || []).map((item) => ({
      product: item.product,
      competitor: item.competitor,
      sap_price: item.sap_price,
      competitor_price: item.competitor_price,
    })),
  );
  appendSheet(
    workbook,
    "ProductsNoCompetitor",
    productsWithoutCompetitors,
  );
  appendSheet(workbook, "MarketDominance", [
    {
      sap_qty: analytics.marketDominanceIndicator?.sap_qty || 0,
      competitor_qty: analytics.marketDominanceIndicator?.competitor_qty || 0,
      ratio: analytics.marketDominanceIndicator?.ratio,
      delta: analytics.marketDominanceIndicator?.delta || 0,
    },
  ]);

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

  const now = new Date();
  const year = now.getFullYear();
  const month = resolveExportMonth(rawRows);
  const fileName = `selling-in-report-${year}-${month}.xlsx`;

  return {
    success: true,
    fileName,
    contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    buffer,
  };
}
