import fs from "node:fs/promises";
import {
  ensureParentDir,
  resolveOutputFilePath,
} from "./runtime-paths.mjs";

const NULL_SENTINEL = "__CODEX_NULL__";
const MONETARY_FIELD_KEYS = new Set(["bookingValue", "opportunityValue", "workloadAmount", "pipelineAmount"]);
const THOUSANDS_DISPLAY_KEYS = new Set([
  ...MONETARY_FIELD_KEYS,
  "totalBookingValue",
  "totalOpportunityValue",
  "totalWorkloadAmount",
  "totalPipelineAmount",
]);
const THOUSANDS_DISPLAY_SOURCE_FIELDS = new Set(MONETARY_FIELD_KEYS);
const ORACLE_MONTHS = {
  JAN: "01",
  FEB: "02",
  MAR: "03",
  APR: "04",
  MAY: "05",
  JUN: "06",
  JUL: "07",
  AUG: "08",
  SEP: "09",
  OCT: "10",
  NOV: "11",
  DEC: "12",
};

export function buildWarnings({ requestedLimit, recordsCount, customSql, customSqlColumnsResolved = false }) {
  const warnings = [];
  if (requestedLimit > 0 && recordsCount >= requestedLimit) {
    warnings.push(
      `The query returned ${recordsCount} rows, which matches the requested limit (${requestedLimit}). Results may be truncated; rerun with a higher --limit if you need a complete extract.`
    );
  }
  if (customSql && !customSqlColumnsResolved) {
    warnings.push("Custom SQL records are returned as raw row arrays because the Oracle DV response does not guarantee stable field aliases.");
  }
  return warnings;
}

export function parseNumericValue(value) {
  if (typeof value === "number") return Number.isFinite(value) ? value : 0;
  const normalized = String(value ?? "")
    .replace(/,/g, "")
    .trim();
  if (!normalized) return 0;
  const parsed = Number(normalized);
  return Number.isFinite(parsed) ? parsed : 0;
}

function isLikelyMonetaryFieldName(fieldName) {
  return /booking|workload|pipeline|opportunityvalue/i.test(String(fieldName || ""));
}

function formatThousandsDisplayValue(value) {
  const thousands = Math.trunc(parseNumericValue(value) / 1000);
  const grouped = String(thousands).replace(/\B(?=(\d{3})+(?!\d))/g, ".");
  return `${grouped}k`;
}

function formatDisplaySection(node, displayKeys = THOUSANDS_DISPLAY_KEYS) {
  if (Array.isArray(node)) {
    return node.map((item) => formatDisplaySection(item, displayKeys));
  }
  if (!node || typeof node !== "object") {
    return node;
  }
  return Object.fromEntries(
    Object.entries(node).map(([key, value]) => {
      if ((displayKeys.has(key) || isLikelyMonetaryFieldName(key)) && value !== null && value !== undefined && value !== "") {
        return [key, formatThousandsDisplayValue(value)];
      }
      return [key, formatDisplaySection(value, displayKeys)];
    })
  );
}

function collectThousandsDisplayKeys(payload) {
  const displayKeys = new Set(THOUSANDS_DISPLAY_KEYS);
  for (const columnName of payload?.columns || []) {
    if (MONETARY_FIELD_KEYS.has(columnName) || isLikelyMonetaryFieldName(columnName)) {
      displayKeys.add(columnName);
    }
  }
  const querySpec = payload?.request?.query;
  if (querySpec && typeof querySpec === "object") {
    for (const item of [...(querySpec.select || []), ...(querySpec.measure || [])]) {
      if (!item || typeof item !== "object") continue;
      if (!THOUSANDS_DISPLAY_SOURCE_FIELDS.has(item.fieldName)) continue;
      if (item.outputName) displayKeys.add(item.outputName);
    }
  }

  for (const inferredColumn of payload?.request?.inferredColumns || []) {
    if (MONETARY_FIELD_KEYS.has(inferredColumn) || isLikelyMonetaryFieldName(inferredColumn)) {
      displayKeys.add(inferredColumn);
    }
  }

  return displayKeys;
}

function rowsPulledFromApi(result) {
  return Array.isArray(result?.aResults) ? result.aResults.length : 0;
}

function buildApiQueryMetric({
  label,
  result = null,
  rowsReturned = null,
  source = null,
  countedInTotal = true,
} = {}) {
  const resolvedRowsReturned = Number.isFinite(rowsReturned) ? rowsReturned : rowsPulledFromApi(result);
  return {
    label: label || "query",
    rowsPulledFromApi: resolvedRowsReturned,
    source: source || null,
    countedInTotal: countedInTotal !== false,
  };
}

export function buildApiMetrics(entries = []) {
  const queries = entries
    .filter(Boolean)
    .map((entry) =>
      buildApiQueryMetric({
        label: entry.label,
        result: entry.result,
        rowsReturned: entry.rowsReturned,
        source: entry.source,
        countedInTotal: entry.countedInTotal,
      })
    );
  const totalRowsPulledFromApi = queries.reduce(
    (sum, query) => sum + (query.countedInTotal ? query.rowsPulledFromApi : 0),
    0
  );
  return {
    totalRowsPulledFromApi,
    queries,
    explanation: `Raw rows pulled from API: ${totalRowsPulledFromApi}.`,
  };
}

export function preparePayloadForOutput(payload) {
  const displayKeys = collectThousandsDisplayKeys(payload);
  return {
    ...payload,
    apiMetrics: payload?.apiMetrics || buildApiMetrics(),
    ...(Object.prototype.hasOwnProperty.call(payload, "records")
      ? { records: formatDisplaySection(payload.records, displayKeys) }
      : {}),
    ...(payload.summary && typeof payload.summary === "object"
      ? { summary: formatDisplaySection(payload.summary, displayKeys) }
      : {}),
  };
}

function normalizeOracleDateString(value) {
  if (typeof value !== "string") return value;
  const match = /^(\d{2})-([A-Z]{3})-(\d{2}|\d{4})$/.exec(value.trim().toUpperCase());
  if (!match) return value;
  const [, day, monthToken, yearToken] = match;
  const month = ORACLE_MONTHS[monthToken];
  if (!month) return value;
  const year =
    yearToken.length === 4
      ? yearToken
      : Number(yearToken) >= 70
        ? `19${yearToken}`
        : `20${yearToken}`;
  return `${year}-${month}-${day}`;
}

export function normalizeCell(value) {
  if (value === NULL_SENTINEL) return null;
  return normalizeOracleDateString(value ?? null);
}

export function mapRows(fields, rows) {
  return rows.map((row) =>
    Object.fromEntries(fields.map((field, index) => [field, normalizeCell(row[index])]))
  );
}

export function emitJson(args, payload, runtimeHome) {
  const json = JSON.stringify(preparePayloadForOutput(payload), null, 2);
  const outputFilePath = resolveOutputFilePath(args["output-file"], runtimeHome);
  if (outputFilePath) {
    return ensureParentDir(outputFilePath)
      .then(() => fs.writeFile(outputFilePath, `${json}\n`, "utf8"))
      .then(() => json);
  }
  return Promise.resolve(json);
}