import { FileBlob, SpreadsheetFile } from "@oai/artifact-tool";

const inputPath =
  "/Users/lingniu/Downloads/智能管车_车辆里程日报-105652096.xlsx";
const input = await FileBlob.load(inputPath);
const workbook = await SpreadsheetFile.importXlsx(input);

const summary = await workbook.inspect({
  kind: "workbook,sheet,table",
  maxChars: 12000,
  tableMaxRows: 8,
  tableMaxCols: 40,
  tableMaxCellChars: 120,
});

const sheet = workbook.worksheets.getItemAt(0);
const usedRange = sheet.getUsedRange(true);
const rowCount = usedRange?.rowCount ?? 0;
const columnCount = usedRange?.columnCount ?? 0;
const values = usedRange?.values ?? [];
const headers = values[0] ?? [];
const dateStartIndex = 3;
const dateEndIndex = Math.max(dateStartIndex, headers.indexOf("运行时长"));
let positiveDailyCellCount = 0;
let nonZeroVehicleCount = 0;
let noteVehicleCount = 0;
let mismatchVehicleCount = 0;
let totalDailyKm = 0;
let totalColumnKm = 0;
const nonZeroSamples = [];
const noteSamples = [];
for (const row of values.slice(1)) {
  const dailyValues = row
    .slice(dateStartIndex, dateEndIndex)
    .map(numberValue);
  const positiveValues = dailyValues.filter((value) => value > 0);
  const rowDailyKm = dailyValues.reduce((sum, value) => sum + value, 0);
  const rowTotalKm = numberValue(row[2]);
  const note = row[dateEndIndex + 1];
  positiveDailyCellCount += positiveValues.length;
  totalDailyKm += rowDailyKm;
  totalColumnKm += rowTotalKm;
  if (positiveValues.length > 0) {
    nonZeroVehicleCount += 1;
    if (nonZeroSamples.length < 20) {
      nonZeroSamples.push({
        plate: row[0],
        organization: row[1],
        totalKm: rowTotalKm,
        dailyKm: rowDailyKm,
        positiveDayCount: positiveValues.length,
      });
    }
  }
  if (note !== null && note !== undefined && String(note).trim() !== "") {
    noteVehicleCount += 1;
    if (noteSamples.length < 20) {
      noteSamples.push({ plate: row[0], note });
    }
  }
  if (Math.abs(rowTotalKm - rowDailyKm) > 0.011) {
    mismatchVehicleCount += 1;
  }
}
const inspectRange = rowCount > 0 && columnCount > 0
  ? `A1:${columnName(Math.min(columnCount, 40))}${Math.min(rowCount, 15)}`
  : "A1:A1";
const preview = await workbook.inspect({
  kind: "table",
  sheetId: sheet.name,
  range: inspectRange,
  include: "values,formulas",
  maxChars: 25000,
  tableMaxRows: 15,
  tableMaxCols: 40,
  tableMaxCellChars: 120,
});

console.log(JSON.stringify({
  inputPath,
  sheetName: sheet.name,
  rowCount,
  columnCount,
  dateColumnCount: Math.max(0, dateEndIndex - dateStartIndex),
  positiveDailyCellCount,
  nonZeroVehicleCount,
  noteVehicleCount,
  mismatchVehicleCount,
  totalDailyKm: round(totalDailyKm),
  totalColumnKm: round(totalColumnKm),
  nonZeroSamples,
  noteSamples,
  summary: summary.ndjson,
  preview: preview.ndjson,
}, null, 2));

function columnName(columnCount) {
  let value = columnCount;
  let name = "";
  while (value > 0) {
    value -= 1;
    name = String.fromCharCode(65 + (value % 26)) + name;
    value = Math.floor(value / 26);
  }
  return name;
}

function numberValue(value) {
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : 0;
}

function round(value) {
  return Math.round(value * 1000) / 1000;
}