import fs from "node:fs/promises";
import path from "node:path";
import { spawnSync } from "node:child_process";
import { SpreadsheetFile, Workbook } from "@oai/artifact-tool";

const workspaceRoot = "/Users/lingniu/project/ai-coding/lingniu-vehicle-ingest";
const workDir = path.join(
  workspaceRoot,
  "tmp/vehicle-mileage-selection-019fa1ac-db39-7933-bb0e-30dd63cc25bd",
);
const outputDir = path.join(
  workspaceRoot,
  "outputs/019fa1ac-db39-7933-bb0e-30dd63cc25bd",
);
const outputPath = path.join(
  outputDir,
  "江浙沪四类车型近三个月GPS里程清单_20260427-20260726.xlsx",
);
const masterPath = path.join(
  workspaceRoot,
  "outputs/jt808-provider-audit-20260717/oneos-vehicle-master.json",
);

const dateFrom = "2026-04-27";
const dateTo = "2026-07-26";
const periodDays = 91;
const queryAsOf = "2026-07-28";
const mileageProtocol = "JT808";

function assert(condition, message) {
  if (!condition) throw new Error(message);
}

function postProductionAPI(endpoint, body) {
  const remoteCommand =
    `. /opt/lingniu-vehicle-platform/env/access-tokens.env; ` +
    `curl -fsS -H "Authorization: Bearer $ADMIN_TOKEN" ` +
    `-H "Content-Type: application/json" --data-binary @- ` +
    `http://127.0.0.1:20300${endpoint}`;
  const result = spawnSync(
    "ssh",
    ["-o", "BatchMode=yes", "root@115.29.187.205", remoteCommand],
    {
      input: JSON.stringify(body),
      encoding: "utf8",
      maxBuffer: 64 * 1024 * 1024,
    },
  );
  if (result.status !== 0) {
    throw new Error(
      `Production API failed for ${endpoint}: ${result.stderr || result.stdout}`,
    );
  }
  const payload = JSON.parse(result.stdout);
  if (!payload.data) {
    throw new Error(`Production API returned no data for ${endpoint}`);
  }
  return payload.data;
}

function excelDate(isoDate) {
  return new Date(`${isoDate}T00:00:00+08:00`);
}

function datesBetween(start, end) {
  const result = [];
  const cursor = new Date(`${start}T00:00:00+08:00`);
  const last = new Date(`${end}T00:00:00+08:00`);
  while (cursor <= last) {
    result.push(cursor.toLocaleDateString("en-CA", { timeZone: "Asia/Shanghai" }));
    cursor.setDate(cursor.getDate() + 1);
  }
  return result;
}

function residentLocation(rawCity) {
  const parts = String(rawCity || "").split("-");
  const province = parts[0] || "";
  const city = parts.slice(1).join("-") || province;
  return { province, city };
}

function vehicleCategory(model) {
  const normalized = String(model || "");
  if (normalized.includes("4.5吨") && !normalized.includes("冷链")) {
    return "4.5T普货";
  }
  if (normalized.includes("冷链")) return "冷链";
  if (normalized.includes("18吨")) return "18T";
  if (normalized.includes("49吨")) return "49T";
  return "";
}

function isJiangZheHu(city) {
  return ["江苏省-", "浙江省-", "上海市-"].some((prefix) =>
    String(city || "").startsWith(prefix),
  );
}

async function loadMasterVehicles() {
  const source = JSON.parse(await fs.readFile(masterPath, "utf8"));
  const rows = source.sheets?.[0]?.rows || [];
  assert(rows.length > 1, "OneOS vehicle master is empty");
  const headers = rows[0].values;
  const headerIndex = Object.fromEntries(headers.map((value, index) => [value, index]));
  const requiredHeaders = [
    "车牌号",
    "VIN",
    "运营城市",
    "品牌",
    "型号",
    "运营状态",
    "车辆状态",
    "客户名称",
  ];
  for (const header of requiredHeaders) {
    assert(headerIndex[header] !== undefined, `Missing master header: ${header}`);
  }
  const result = [];
  for (const row of rows.slice(1)) {
    const values = [...row.values, ...Array(headers.length).fill("")];
    const cityRaw = values[headerIndex["运营城市"]];
    const model = values[headerIndex["型号"]];
    const category = vehicleCategory(model);
    if (!category || !isJiangZheHu(cityRaw)) continue;
    const vin = String(values[headerIndex["VIN"]] || "").trim();
    const plate = String(values[headerIndex["车牌号"]] || "").trim();
    if (!vin || !plate) continue;
    const location = residentLocation(cityRaw);
    result.push({
      category,
      plate,
      vin,
      brand: String(values[headerIndex["品牌"]] || "").trim(),
      model: String(model || "").trim(),
      province: location.province,
      city: location.city,
      cityRaw: String(cityRaw || "").trim(),
      operationStatus: String(values[headerIndex["运营状态"]] || "").trim(),
      vehicleStatus: String(values[headerIndex["车辆状态"]] || "").trim(),
      customerName: ["租赁", "自营"].includes(
        String(values[headerIndex["运营状态"]] || "").trim(),
      )
        ? String(values[headerIndex["客户名称"]] || "").trim()
        : "",
    });
  }
  return result;
}

function queryRanking(vins) {
  if (vins.length === 0) return { ranking: [], asOf: "" };
  return postProductionAPI("/api/v2/statistics/mileage", {
    dateFrom,
    dateTo,
    vins,
    protocol: mileageProtocol,
  });
}

function rankingMap(data) {
  return new Map((data.ranking || []).map((row) => [row.vin, row]));
}

function selectCategoryVehicles(category, candidates) {
  const categoryCandidates = candidates.filter((row) => row.category === category);
  assert(
    categoryCandidates.length >= 10,
    `${category} has only ${categoryCandidates.length} Jiang-Zhe-Hu candidates`,
  );
  const masterByVIN = new Map(categoryCandidates.map((row) => [row.vin, row]));
  const suzhouVINs = categoryCandidates
    .filter((row) => row.city === "苏州市")
    .map((row) => row.vin);
  const otherVINs = categoryCandidates
    .filter((row) => row.city !== "苏州市")
    .map((row) => row.vin);
  const suzhouRanking = queryRanking(suzhouVINs);
  const otherRanking = queryRanking(otherVINs);
  const combined = [
    ...(suzhouRanking.ranking || []).map((row) => ({
      ...row,
      suzhouPreferred: true,
    })),
    ...(otherRanking.ranking || []).map((row) => ({
      ...row,
      suzhouPreferred: false,
    })),
  ];
  assert(combined.length >= 10, `${category} has fewer than 10 vehicles with mileage`);
  return combined.slice(0, 10).map((ranked, index) => ({
    ...masterByVIN.get(ranked.vin),
    rank: index + 1,
    sourcePeriodMileageKm: Number(ranked.mileageKm || 0),
    sourceActiveDays: Number(ranked.activeDays || 0),
    suzhouPreferred: ranked.suzhouPreferred,
    statisticsAsOf: suzhouRanking.asOf || otherRanking.asOf || "",
  }));
}

function queryDailyMileage(vins) {
  const items = [];
  let offset = 0;
  const limit = 1000;
  let total = Number.POSITIVE_INFINITY;
  while (offset < total) {
    const page = postProductionAPI("/api/mileage/daily", {
      dateFrom,
      dateTo,
      vins,
      protocol: mileageProtocol,
      deduplicate: true,
      limit,
      offset,
    });
    const pageItems = page.items || [];
    items.push(...pageItems);
    total = Number(page.total || 0);
    offset += pageItems.length;
    if (pageItems.length === 0) break;
  }
  return items;
}

function styleTitle(sheet, rangeAddress, title) {
  const range = sheet.getRange(rangeAddress);
  range.merge();
  range.values = [[title]];
  range.format = {
    fill: "#0F4C5C",
    font: { bold: true, color: "#FFFFFF", size: 16 },
    verticalAlignment: "center",
    horizontalAlignment: "left",
  };
  range.format.rowHeight = 34;
}

function styleHeader(range) {
  range.format = {
    fill: "#DCEEF2",
    font: { bold: true, color: "#12343B" },
    verticalAlignment: "center",
    horizontalAlignment: "center",
    wrapText: true,
    borders: {
      bottom: { style: "medium", color: "#78A7B2" },
    },
  };
  range.format.rowHeight = 30;
}

function styleNote(range) {
  range.format = {
    fill: "#F3F8FA",
    font: { color: "#385A64", size: 10 },
    wrapText: true,
    verticalAlignment: "center",
  };
}

function addCategoryConditionalFormatting(range) {
  range.conditionalFormats.addCustom('=$A5="4.5T普货"', {
    fill: "#FFF4CC",
    font: { bold: true, color: "#7A5300" },
  });
  range.conditionalFormats.addCustom('=$A5="冷链"', {
    fill: "#DDF3FF",
    font: { bold: true, color: "#075985" },
  });
  range.conditionalFormats.addCustom('=$A5="18T"', {
    fill: "#E8E1FF",
    font: { bold: true, color: "#5B21B6" },
  });
  range.conditionalFormats.addCustom('=$A5="49T"', {
    fill: "#DFF5E6",
    font: { bold: true, color: "#166534" },
  });
}

const masterVehicles = await loadMasterVehicles();
const categories = ["4.5T普货", "冷链", "18T", "49T"];
const selectedVehicles = categories.flatMap((category) =>
  selectCategoryVehicles(category, masterVehicles),
);
assert(selectedVehicles.length === 40, "Expected exactly 40 selected vehicles");
assert(
  new Set(selectedVehicles.map((row) => row.vin)).size === 40,
  "Selected VINs are not unique",
);

const dailySourceRows = queryDailyMileage(selectedVehicles.map((row) => row.vin));
const dailyByVehicleDate = new Map();
for (const row of dailySourceRows) {
  assert(
    row.source === mileageProtocol,
    `Unexpected mileage source for ${row.vin} on ${row.date}: ${row.source}`,
  );
  const key = `${row.vin}|${row.date}`;
  assert(!dailyByVehicleDate.has(key), `Duplicate deduplicated daily row: ${key}`);
  dailyByVehicleDate.set(key, row);
}

const dates = datesBetween(dateFrom, dateTo);
assert(dates.length === periodDays, `Expected ${periodDays} dates, got ${dates.length}`);

const dailyRows = [];
for (const vehicle of selectedVehicles) {
  for (const date of dates) {
    const source = dailyByVehicleDate.get(`${vehicle.vin}|${date}`);
    dailyRows.push({
      date,
      ...vehicle,
      dailyMileageKm: source ? Number(source.dailyMileageKm || 0) : null,
      source: source?.source || "",
      recordStatus: source ? "有记录" : "缺失",
    });
  }
}
assert(dailyRows.length === 40 * periodDays, "Daily row count mismatch");

for (const vehicle of selectedVehicles) {
  const computed = dailyRows
    .filter((row) => row.vin === vehicle.vin)
    .reduce((sum, row) => sum + (row.dailyMileageKm ?? 0), 0);
  const difference = Math.abs(computed - vehicle.sourcePeriodMileageKm);
  assert(
    difference < 0.02,
    `${vehicle.plate} total mismatch: daily=${computed}, stats=${vehicle.sourcePeriodMileageKm}`,
  );
}

const workbook = Workbook.create();
const notesSheet = workbook.worksheets.add("口径说明");
const summarySheet = workbook.worksheets.add("车辆汇总");
const dailySheet = workbook.worksheets.add("每日里程");

for (const sheet of [notesSheet, summarySheet, dailySheet]) {
  sheet.showGridLines = false;
}

// 口径说明
styleTitle(notesSheet, "A1:H1", "江浙沪四类车型近三个月 GPS 里程清单");
notesSheet.getRange("A3:B11").values = [
  ["项目", "口径"],
  ["统计区间", `${dateFrom} 至 ${dateTo}（${periodDays} 个自然日，完整日）`],
  ["区域范围", "常驻市（OneOS 运营城市）属于江苏、浙江、上海"],
  ["选车规则", "每类先按苏州区间总里程降序选取，不足 10 辆时按江浙沪其他城市区间总里程降序补齐"],
  ["车型定义", "4.5T普货=型号含4.5吨且不含冷链；冷链=型号含冷链；18T=型号含18吨；49T=型号含49吨"],
  ["里程口径", "仅使用 JT808 定位终端/GPS 侧累计里程计算，不使用 GB32960、宇通 MQTT 等仪表/车端累计里程"],
  ["缺失处理", "逐日表保留全部车辆×日期组合；无生产记录的日期留空，并标记为“缺失”，不擅自按0计算"],
  ["主数据快照", "OneOS车辆信息快照：2026-07-17；常驻市取“运营城市”字段"],
  ["里程数据截至", `${queryAsOf} 查询，统计截止至 ${dateTo}；来源协议固定为 ${mileageProtocol}`],
];
styleHeader(notesSheet.getRange("A3:B3"));
styleNote(notesSheet.getRange("A4:B11"));
notesSheet.getRange("A4:A11").format.font = { bold: true, color: "#12343B" };
notesSheet.getRange("A3:B11").format.borders = {
  outside: { style: "thin", color: "#B9D3DA" },
  insideHorizontal: { style: "thin", color: "#DCE8EC" },
};
notesSheet.getRange("A13:E18").values = [
  ["分类", "车辆数", "苏州优先数", "GPS区间总里程(km)", "GPS日均里程(km/天)"],
  ["4.5T普货", null, null, null, null],
  ["冷链", null, null, null, null],
  ["18T", null, null, null, null],
  ["49T", null, null, null, null],
  ["合计", null, null, null, null],
];
styleHeader(notesSheet.getRange("A13:E13"));
notesSheet.getRange("B14").formulas = [["=COUNTIF('车辆汇总'!$A$5:$A$44,A14)"]];
notesSheet.getRange("B14:B17").fillDown();
notesSheet.getRange("C14").formulas = [
  ["=COUNTIFS('车辆汇总'!$A$5:$A$44,A14,'车辆汇总'!$I$5:$I$44,\"苏州市\")"],
];
notesSheet.getRange("C14:C17").fillDown();
notesSheet.getRange("D14").formulas = [
  ["=SUMIF('车辆汇总'!$A$5:$A$44,A14,'车辆汇总'!$O$5:$O$44)"],
];
notesSheet.getRange("D14:D17").fillDown();
notesSheet.getRange("E14").formulas = [["=D14/(B14*'车辆汇总'!$N$5)"]];
notesSheet.getRange("E14:E17").fillDown();
notesSheet.getRange("B18").formulas = [["=SUM(B14:B17)"]];
notesSheet.getRange("C18").formulas = [["=SUM(C14:C17)"]];
notesSheet.getRange("D18").formulas = [["=SUM(D14:D17)"]];
notesSheet.getRange("E18").formulas = [["=D18/(B18*'车辆汇总'!$N$5)"]];
notesSheet.getRange("D14:E18").format.numberFormat = "#,##0.0";
notesSheet.getRange("A18:E18").format = {
  fill: "#E8F1F4",
  font: { bold: true, color: "#12343B" },
  borders: { top: { style: "double", color: "#78A7B2" } },
};
notesSheet.getRange("A13:E18").format.borders = {
  outside: { style: "thin", color: "#B9D3DA" },
  insideHorizontal: { style: "thin", color: "#DCE8EC" },
};
notesSheet.getRange("A:A").format.columnWidth = 18;
notesSheet.getRange("B:B").format.columnWidth = 64;
notesSheet.getRange("C:E").format.columnWidth = 18;
notesSheet.getRange("A4:B11").format.rowHeight = 34;
notesSheet.freezePanes.freezeRows(3);

// 车辆汇总
styleTitle(summarySheet, "A1:Q1", "四类车型各10辆｜近三个月 GPS 里程汇总");
summarySheet.getRange("A2:Q2").merge();
summarySheet.getRange("A2").values = [[
  `范围：江浙沪常驻车辆；苏州优先；统计区间 ${dateFrom} 至 ${dateTo}。仅使用 JT808 GPS 里程，区间总里程由“每日里程”明细公式汇总。`,
]];
styleNote(summarySheet.getRange("A2:Q2"));
const summaryHeaders = [
  "分类",
  "类内排名",
  "车牌",
  "VIN",
  "品牌",
  "型号",
  "常驻省",
  "常驻城市原值",
  "常驻市",
  "苏州优先",
  "运营状态",
  "车辆状态",
  "客户名称",
  "区间天数",
  "GPS区间总里程(km)",
  "GPS日均里程(km/天)",
  "有记录天数",
];
summarySheet.getRange("A4:Q4").values = [summaryHeaders];
styleHeader(summarySheet.getRange("A4:Q4"));
const summaryValues = selectedVehicles.map((vehicle) => [
  vehicle.category,
  vehicle.rank,
  vehicle.plate,
  vehicle.vin,
  vehicle.brand,
  vehicle.model,
  vehicle.province,
  vehicle.cityRaw,
  vehicle.city,
  vehicle.suzhouPreferred ? "是" : "否",
  vehicle.operationStatus,
  vehicle.vehicleStatus,
  vehicle.customerName,
  periodDays,
  null,
  null,
  null,
]);
summarySheet.getRange(`A5:Q${4 + summaryValues.length}`).values = summaryValues;
const dailyEndRow = 4 + dailyRows.length;
summarySheet.getRange("O5").formulas = [[
  `=SUMIFS('每日里程'!$J$5:$J$${dailyEndRow},'每日里程'!$E$5:$E$${dailyEndRow},D5)`,
]];
summarySheet.getRange("O5:O44").fillDown();
summarySheet.getRange("P5").formulas = [["=O5/N5"]];
summarySheet.getRange("P5:P44").fillDown();
summarySheet.getRange("Q5").formulas = [[
  `=COUNTIFS('每日里程'!$E$5:$E$${dailyEndRow},D5,'每日里程'!$L$5:$L$${dailyEndRow},"有记录")`,
]];
summarySheet.getRange("Q5:Q44").fillDown();
summarySheet.getRange("B5:B44").format.numberFormat = "0";
summarySheet.getRange("N5:N44").format.numberFormat = "0";
summarySheet.getRange("O5:P44").format.numberFormat = "#,##0.0";
summarySheet.getRange("Q5:Q44").format.numberFormat = "0";
summarySheet.getRange("A5:Q44").format.verticalAlignment = "center";
summarySheet.getRange("A5:Q44").format.borders = {
  insideHorizontal: { style: "thin", color: "#E2ECEF" },
};
addCategoryConditionalFormatting(summarySheet.getRange("A5:A44"));
summarySheet.getRange("O5:O44").conditionalFormats.add("dataBar", {
  color: "#2A9D8F",
  gradient: true,
});
summarySheet.getRange("J5:J44").conditionalFormats.add("containsText", {
  text: "是",
  format: {
    fill: "#E5F7ED",
    font: { bold: true, color: "#166534" },
  },
});
const summaryTable = summarySheet.tables.add("A4:Q44", true, "VehicleMileageSummary");
summaryTable.style = "TableStyleMedium2";
summaryTable.showFilterButton = true;
summarySheet.freezePanes.freezeRows(4);
summarySheet.freezePanes.freezeColumns(4);
const summaryWidths = [
  14, 10, 14, 22, 12, 30, 12, 20, 12, 10, 12, 12, 30, 10, 18, 18, 14,
];
summaryWidths.forEach((width, index) => {
  summarySheet.getRangeByIndexes(0, index, 44, 1).format.columnWidth = width;
});
summarySheet.getRange("A5:Q44").format.rowHeight = 23;

// 每日里程
styleTitle(dailySheet, "A1:L1", "40辆车逐日 GPS 里程明细");
dailySheet.getRange("A2:L2").merge();
dailySheet.getRange("A2").values = [[
  `共 ${selectedVehicles.length} 辆 × ${periodDays} 天 = ${dailyRows.length.toLocaleString("zh-CN")} 行；仅统计 JT808 GPS 里程，缺失日期留空并单独标记。`,
]];
styleNote(dailySheet.getRange("A2:L2"));
const dailyHeaders = [
  "日期",
  "分类",
  "类内排名",
  "车牌",
  "VIN",
  "品牌",
  "型号",
  "常驻市",
  "客户名称",
  "GPS每日里程(km)",
  "来源",
  "记录状态",
];
dailySheet.getRange("A4:L4").values = [dailyHeaders];
styleHeader(dailySheet.getRange("A4:L4"));
const dailyValues = dailyRows.map((row) => [
  excelDate(row.date),
  row.category,
  row.rank,
  row.plate,
  row.vin,
  row.brand,
  row.model,
  row.city,
  row.customerName,
  row.dailyMileageKm,
  row.source,
  row.recordStatus,
]);
dailySheet.getRange(`A5:L${dailyEndRow}`).values = dailyValues;
dailySheet.getRange(`A5:A${dailyEndRow}`).format.numberFormat = "yyyy-mm-dd";
dailySheet.getRange(`C5:C${dailyEndRow}`).format.numberFormat = "0";
dailySheet.getRange(`J5:J${dailyEndRow}`).format.numberFormat = "#,##0.0";
dailySheet.getRange(`A5:L${dailyEndRow}`).format.borders = {
  insideHorizontal: { style: "thin", color: "#EDF2F4" },
};
dailySheet.getRange(`L5:L${dailyEndRow}`).conditionalFormats.add("containsText", {
  text: "缺失",
  format: {
    fill: "#FFF0F0",
    font: { bold: true, color: "#B42318" },
  },
});
dailySheet.getRange(`J5:J${dailyEndRow}`).conditionalFormats.add("dataBar", {
  color: "#5CA4A9",
  gradient: true,
});
const dailyTable = dailySheet.tables.add(
  `A4:L${dailyEndRow}`,
  true,
  "VehicleDailyMileage",
);
dailyTable.style = "TableStyleMedium2";
dailyTable.showFilterButton = true;
dailySheet.freezePanes.freezeRows(4);
dailySheet.freezePanes.freezeColumns(5);
const dailyWidths = [14, 14, 10, 14, 22, 12, 30, 12, 30, 18, 16, 12];
dailyWidths.forEach((width, index) => {
  dailySheet.getRangeByIndexes(0, index, dailyEndRow, 1).format.columnWidth = width;
});
dailySheet.getRange(`A5:L${dailyEndRow}`).format.rowHeight = 21;

// Compact verification before export.
const summaryInspect = await workbook.inspect({
  kind: "table",
  range: "车辆汇总!A1:Q16",
  include: "values,formulas",
  tableMaxRows: 16,
  tableMaxCols: 17,
  maxChars: 12000,
});
await fs.writeFile(path.join(workDir, "summary-inspect.ndjson"), summaryInspect.ndjson);

const dailyInspect = await workbook.inspect({
  kind: "table",
  range: "每日里程!A1:L20",
  include: "values,formulas",
  tableMaxRows: 20,
  tableMaxCols: 12,
  maxChars: 12000,
});
await fs.writeFile(path.join(workDir, "daily-inspect.ndjson"), dailyInspect.ndjson);

const formulaErrors = await workbook.inspect({
  kind: "match",
  searchTerm: "#REF!|#DIV/0!|#VALUE!|#NAME\\?|#N/A",
  options: { useRegex: true, maxResults: 300 },
  summary: "final formula error scan",
  maxChars: 12000,
});
await fs.writeFile(path.join(workDir, "formula-errors.ndjson"), formulaErrors.ndjson);

for (const [sheetName, range, fileName] of [
  ["口径说明", "A1:H19", "preview-notes.png"],
  ["车辆汇总", "A1:Q18", "preview-summary.png"],
  ["每日里程", "A1:L26", "preview-daily.png"],
]) {
  const preview = await workbook.render({
    sheetName,
    range,
    scale: 1.25,
    format: "png",
  });
  await fs.writeFile(
    path.join(workDir, fileName),
    new Uint8Array(await preview.arrayBuffer()),
  );
}

await fs.mkdir(outputDir, { recursive: true });
const output = await SpreadsheetFile.exportXlsx(workbook);
await output.save(outputPath);

console.log(
  JSON.stringify(
    {
      outputPath,
      selected: selectedVehicles.map((vehicle) => ({
        category: vehicle.category,
        rank: vehicle.rank,
        plate: vehicle.plate,
        vin: vehicle.vin,
        brand: vehicle.brand,
        model: vehicle.model,
        residentCity: vehicle.city,
        suzhouPreferred: vehicle.suzhouPreferred,
        periodMileageKm: vehicle.sourcePeriodMileageKm,
      })),
      sourceDailyRows: dailySourceRows.length,
      expandedDailyRows: dailyRows.length,
      missingDailyRows: dailyRows.filter((row) => row.recordStatus === "缺失").length,
      customerFilledVehicles: selectedVehicles.filter((row) => row.customerName).length,
    },
    null,
    2,
  ),
);