import { readFile } from "node:fs/promises";
import * as XLSX from "xlsx";
import {
  CATEGORY_CONFIG,
  DIMENSION_CONFIG,
} from "./config.mjs";

const METRIC_WEIGHTS = {
  roi: 0.4,
  ordersPer10k: 0.4,
  gmv: 0.2,
};

const EXCLUSION_RULES = {
  cycling_sop: [
    { canonical: "航拍", aliases: ["航拍"] },
    {
      canonical: "C2C骑行-定制",
      aliases: ["C2C骑行-定制"],
    },
  ],
  cycling_sku: [
    {
      canonical: "骑行2000到3000",
      aliases: ["骑行2000到3000", "骑行2000～3000", "骑行2000-3000"],
    },
    {
      canonical: "骑行2000以内",
      aliases: ["骑行2000以内", "骑行2000内"],
    },
  ],
  billiards_sop: [
    { canonical: "赛事", aliases: ["赛事"] },
    { canonical: "明星同款", aliases: ["明星同款"] },
    {
      canonical: "C2C运动-定制",
      aliases: ["C2C运动-定制"],
    },
  ],
  billiards_sku: [
    {
      canonical: "低客单价300以下",
      aliases: ["低客单价300以下"],
    },
    {
      canonical: "12月测试心灵",
      aliases: ["12月测试心灵", "12月测试星菱600", "12月测试星菱"],
    },
    {
      canonical: "1月测试加点500",
      aliases: ["1月测试加点500", "1月测试嘉点500"],
    },
    {
      canonical: "2月测试皮尔力1000",
      aliases: ["2月测试皮尔力1000"],
    },
    {
      canonical: "多款推荐",
      aliases: ["多款推荐", "多款合集"],
    },
    { canonical: "台球百元杆", aliases: ["台球百元杆"] },
    {
      canonical: "临时SKU测试",
      aliases: ["临时SKU测试", "临时sku测试"],
    },
    { canonical: "台球1000", aliases: ["台球1000"] },
    { canonical: "台球1500", aliases: ["台球1500"] },
    {
      canonical: "10000+",
      aliases: [
        "10000+",
        "10000＋",
        "台球10000+",
        "台球10000＋",
        "10000以上",
        "台球10000以上",
      ],
    },
  ],
};

const UNCLASSIFIED_LABELS = new Set(["", "-", "—", "–", "未分类", "未归类"]);

function normalizedLabel(value) {
  return String(value ?? "")
    .normalize("NFKC")
    .trim()
    .toLowerCase()
    .replace(/\s+/g, "")
    .replace(/[～~—–−至到]/g, "-");
}

function normalizedHeader(value) {
  return String(value ?? "")
    .normalize("NFKC")
    .trim()
    .toLowerCase()
    .replace(/\s+/g, "");
}

function parseNumber(value) {
  if (typeof value === "number") {
    return Number.isFinite(value) ? value : 0;
  }

  const original = String(value ?? "").trim();
  if (!original || ["-", "—", "–"].includes(original)) {
    return 0;
  }

  const negative = /^\(.*\)$/.test(original);
  const cleaned = original
    .replace(/[(),，¥￥\s]/g, "")
    .replace(/%$/, "");
  const match = cleaned.match(/-?\d+(?:\.\d+)?/);
  if (!match) {
    return 0;
  }

  let result = Number(match[0]);
  if (cleaned.includes("亿")) {
    result *= 100_000_000;
  } else if (cleaned.includes("万")) {
    result *= 10_000;
  }

  return negative ? -Math.abs(result) : result;
}

function findHeaderRow(values) {
  const maximumRows = Math.min(values.length, 20);
  for (let rowIndex = 0; rowIndex < maximumRows; rowIndex += 1) {
    const headers = values[rowIndex].map(normalizedHeader);
    const hasRoi = headers.some((header) => header === "roi");
    const hasGmv = headers.some((header) => header.includes("净支付金额"));
    const hasOrders = headers.some(
      (header) =>
        header.includes("万播订单") ||
        header.includes("万播售卖单") ||
        header.includes("万播售单"),
    );
    if (hasRoi && hasGmv && hasOrders) {
      return rowIndex;
    }
  }
  throw new Error("导出文件中没有找到 ROI、万播订单和净支付金额列。");
}

function findColumn(headers, candidates, fallback = -1) {
  for (const candidate of candidates) {
    const index = headers.findIndex((header) =>
      typeof candidate === "string"
        ? header === candidate
        : candidate.test(header),
    );
    if (index >= 0) {
      return index;
    }
  }
  return fallback;
}

function buildExclusionMap(configKey) {
  const map = new Map();
  for (const rule of EXCLUSION_RULES[configKey] ?? []) {
    for (const alias of rule.aliases) {
      map.set(normalizedLabel(alias), rule.canonical);
    }
  }
  return map;
}

async function readExportedRows(filePath, sheetName, configKey) {
  const buffer = await readFile(filePath);
  const workbook = XLSX.read(buffer, { type: "buffer" });
  const worksheetName =
    workbook.SheetNames.find(
      (name) => normalizedHeader(name) === normalizedHeader(sheetName),
    ) ?? workbook.SheetNames[0];
  const worksheet = worksheetName ? workbook.Sheets[worksheetName] : null;

  if (!worksheet) {
    throw new Error(`文件 ${filePath} 中没有可读取的工作表。`);
  }

  const values = XLSX.utils.sheet_to_json(worksheet, {
    defval: "",
    header: 1,
    raw: false,
  });
  const headerRowIndex = findHeaderRow(values);
  const headers = values[headerRowIndex].map(normalizedHeader);
  const labelIndex = findColumn(headers, [
    "订单话术",
    "投放品类",
    /话术/,
    /品类/,
  ], 0);
  const postsIndex = findColumn(headers, ["总发布条数", /发布条数/], 1);
  const gmvIndex = findColumn(headers, ["净支付金额", /净支付金额/], 11);
  const roiIndex = findColumn(headers, ["roi"], 13);
  const ordersIndex = findColumn(
    headers,
    ["万播订单", "万播售卖单", "万播售单", /万播.*单/],
    14,
  );
  const exclusions = buildExclusionMap(configKey);

  const rows = [];
  const removed = [];
  for (let index = headerRowIndex + 1; index < values.length; index += 1) {
    const source = values[index];
    const label = String(source[labelIndex] ?? "").trim();
    const normalized = normalizedLabel(label);

    if (!label) {
      continue;
    }

    const canonical = exclusions.get(normalized);
    const unclassified = UNCLASSIFIED_LABELS.has(label.trim());
    const row = {
      label,
      historicalPosts: parseNumber(source[postsIndex]),
      roi: parseNumber(source[roiIndex]),
      ordersPer10k: parseNumber(source[ordersIndex]),
      gmv: parseNumber(source[gmvIndex]),
    };

    if (canonical || unclassified) {
      removed.push({
        ...row,
        rule: canonical ?? "未归类项目",
      });
      continue;
    }
    rows.push(row);
  }

  if (rows.length === 0) {
    throw new Error(`${sheetName} 导出文件清洗后没有可参与分配的数据。`);
  }

  return { rows, removed, sheetName: worksheetName };
}

function distributeIntegers(rows, totalVideos) {
  const allocations = rows.map((row) => ({
    ...row,
    theoreticalVideos: row.score * totalVideos,
    finalVideos: Math.max(0, Math.floor(row.score * totalVideos)),
  }));
  let remaining =
    totalVideos -
    allocations.reduce((sum, row) => sum + row.finalVideos, 0);

  const ranked = [...allocations].sort(
    (left, right) =>
      right.theoreticalVideos -
        Math.floor(right.theoreticalVideos) -
        (left.theoreticalVideos - Math.floor(left.theoreticalVideos)) ||
      right.score - left.score ||
      left.label.localeCompare(right.label, "zh-CN"),
  );

  let index = 0;
  while (remaining > 0 && ranked.length > 0) {
    ranked[index % ranked.length].finalVideos += 1;
    remaining -= 1;
    index += 1;
  }

  if (remaining < 0) {
    const reverseRanked = [...ranked].reverse();
    index = 0;
    while (remaining < 0 && reverseRanked.length > 0) {
      const target = reverseRanked[index % reverseRanked.length];
      if (target.finalVideos > 0) {
        target.finalVideos -= 1;
        remaining += 1;
      }
      index += 1;
    }
  }

  return allocations.sort(
    (left, right) =>
      right.finalVideos - left.finalVideos ||
      right.score - left.score ||
      left.label.localeCompare(right.label, "zh-CN"),
  );
}

function calculateDimension(rows, totalVideos) {
  const totals = rows.reduce(
    (result, row) => ({
      roi: result.roi + row.roi,
      ordersPer10k: result.ordersPer10k + row.ordersPer10k,
      gmv: result.gmv + row.gmv,
    }),
    { roi: 0, ordersPer10k: 0, gmv: 0 },
  );

  const scored = rows.map((row) => {
    const roiShare = totals.roi ? row.roi / totals.roi : 0;
    const ordersShare = totals.ordersPer10k
      ? row.ordersPer10k / totals.ordersPer10k
      : 0;
    const gmvShare = totals.gmv ? row.gmv / totals.gmv : 0;
    return {
      ...row,
      roiShare,
      ordersShare,
      gmvShare,
      weightedScore:
        roiShare * METRIC_WEIGHTS.roi +
        ordersShare * METRIC_WEIGHTS.ordersPer10k +
        gmvShare * METRIC_WEIGHTS.gmv,
    };
  });

  const scoreTotal = scored.reduce(
    (sum, row) => sum + row.weightedScore,
    0,
  );
  if (!(scoreTotal > 0)) {
    throw new Error("参与计算的数据综合得分合计为 0，无法分配视频条数。");
  }

  const normalizedScores = scored.map((row) => ({
    ...row,
    score: row.weightedScore / scoreTotal,
  }));
  const allocated = distributeIntegers(normalizedScores, totalVideos);

  return {
    totals,
    scoreTotal,
    rows: allocated,
    allocatedVideos: allocated.reduce(
      (sum, row) => sum + row.finalVideos,
      0,
    ),
  };
}

export async function calculateAllocations({
  files,
  budgets,
  unitPrices,
  period,
}) {
  const result = {
    period,
    generatedAt: new Date().toISOString(),
    weights: METRIC_WEIGHTS,
    categories: {},
  };

  for (const category of Object.values(CATEGORY_CONFIG)) {
    const budget = Math.max(0, Number(budgets[category.key]) || 0);
    const unitPrice = Math.max(
      1,
      Number(unitPrices[category.key]) || category.defaultUnitPrice,
    );
    const totalVideos = Math.floor(budget / unitPrice);
    const categoryResult = {
      key: category.key,
      name: category.name,
      budget,
      unitPrice,
      totalVideos,
      usedBudget: totalVideos * unitPrice,
      unusedBudget: budget - totalVideos * unitPrice,
      dimensions: {},
    };

    for (const dimension of Object.values(DIMENSION_CONFIG)) {
      const configKey = `${category.key}_${dimension.key}`;
      const sourcePath = files[configKey];
      if (!sourcePath) {
        throw new Error(`缺少 ${category.name} ${dimension.name} 导出文件。`);
      }
      const extracted = await readExportedRows(
        sourcePath,
        dimension.sheetName,
        configKey,
      );
      categoryResult.dimensions[dimension.key] = {
        key: dimension.key,
        name: dimension.name,
        sourceFile: sourcePath,
        sourceSheet: extracted.sheetName,
        excluded: extracted.removed,
        ...calculateDimension(extracted.rows, totalVideos),
      };
    }
    result.categories[category.key] = categoryResult;
  }

  return result;
}

export const __testables = {
  calculateDimension,
  normalizedLabel,
  parseNumber,
};
