#!/usr/bin/env node
'use strict';

/**
 * migrate_applications.js
 *
 * Merged replacement for:
 *   - scripts/backfill_application_datetimes.js
 *   - scripts/migrate_app_aggregates.js
 *
 * What it does:
 *   Pass 1 — Per-application streaming pass (aggregates + datetime in a single AppDetail scan)
 *     A. Aggregate metrics:
 *          Application.avgSpraySpeed      — weighted avg ground speed (m/s) across spray-on records
 *          Application.totalSprLength     — cumulative geodesic spray distance (m)
 *          Application.totalFlightLength  — cumulative flight distance (m)
 *          Application.avgXtError         — weighted avg abs cross-track error (m) over spray-on records
 *          Application.avgHdop            — weighted avg HDOP over spray-on records
 *          AppFile.totalSprLength         — per-file spray distance (m)
 *          AppFile.totalFlightLength      — per-file flight distance (m)
 *     B. Datetime fields (only when app.startDateTime exists AND a lat/lon coordinate is found):
 *          Application.utcOffset          — UTC offset in minutes for the mission location
 *          Application.startDateTimeUTC   — UTC wall-clock start time
 *          Application.endDateTimeUTC     — UTC wall-clock end time
 *
 *   Pass 2 — Application-level flowAccuracyPct (no AppDetail needed):
 *          Application.flowAccuracyPct    — (totalSprayMat/totalSprayed / appRate) × 100
 *
 * Merge optimisation:
 *   In the default (aggregates) mode the script already streams all AppDetail records for each
 *   file sorted by gpsTime ASC.  The first valid lat/lon encountered during that stream is
 *   captured as firstLat/firstLon and reused for datetime computation — eliminating the separate
 *   findOne query that the old backfill script required.
 *
 * ─── Usage ────────────────────────────────────────────────────────────────────
 *
 *   # Full migration (aggregates + datetime), newest apps first
 *   DEBUG=agm:migrate-applications node scripts/migrate_applications.js
 *
 *   # Tier 1 — most recent 90 days first
 *   DEBUG=agm:migrate-applications node scripts/migrate_applications.js --tier-days=90
 *
 *   # Tier 2 — everything from 2023 onward
 *   DEBUG=agm:migrate-applications node scripts/migrate_applications.js --from-date=2023-01-01
 *
 *   # Dry-run (logs what would change, no writes)
 *   DEBUG=agm:migrate-applications node scripts/migrate_applications.js --dry-run --tier-days=7
 *
 *   # Recompute already-set fields (e.g. after algorithm fix)
 *   DEBUG=agm:migrate-applications node scripts/migrate_applications.js --force --tier-days=30
 *
 *   # One-time repair for xTrack decode change — run in this exact order:
 *   #  Step 1 (with OLD server still running): fix AppDetail raw cm → metres, unset avgXtError
 *   DEBUG=agm:migrate-applications node scripts/migrate_applications.js --repair-agn-xt
 *   #  Step 2: restart the server with the new code
 *   #  Step 3 (with NEW server running): recompute Application.avgXtError
 *   DEBUG=agm:migrate-applications node scripts/migrate_applications.js
 *
 *   # Datetime fields only (skip aggregate streaming — uses lightweight findOne)
 *   DEBUG=agm:migrate-applications node scripts/migrate_applications.js --skip-aggregates
 *
 *   # Aggregate fields only (skip datetime computation)
 *   DEBUG=agm:migrate-applications node scripts/migrate_applications.js --skip-datetime
 *
 *   # Custom env file
 *   node scripts/migrate_applications.js --env ./environment_prod.env --tier-days=90
 *
 * ─── Options ──────────────────────────────────────────────────────────────────
 *
 *   --env <path>            Environment file path (default: ./environment.env)
 *   --dry-run               Report only; make no DB writes
 *   --force                 Recompute even when fields are already set
 *   --batch-size=N          Applications per bulkWrite flush (default: 50)
 *   --tier-days=N           Only process apps created in the last N days
 *   --from-date=YYYY-MM-DD  Only process apps created on/after this date (UTC)
 *   --concurrency=N         Parallel file-level cursors per application (default: 3)
 *   --missing-limit=N       Max apps to list in the missing-legacy-datetime report (default: 100)
 *   --skip-datetime         Skip datetime backfill (pass 1B); still runs aggregates + pass 2
 *   --skip-aggregates       Skip aggregate metrics and flowAccuracyPct (passes 1A + 2);
 *                           uses a lightweight findOne for datetime instead of full streaming
 *   --repair-agn-xt         One-time fix: identifies LQD AgNav binary AppFiles (.nt extension,
 *                           non-DRY), multiplies their AppDetail.xTrack by 0.01 (raw cm → m),
 *                           clears avgXtError on those apps, then implies --force for recompute.
 *                           Run ONCE before restarting the server with the updated code.
 *
 * ─── Fields updated ───────────────────────────────────────────────────────────
 *
 *   A. Datetime fields (Application):
 *        utcOffset, startDateTimeUTC, endDateTimeUTC
 *
 *   B. Aggregate fields:
 *        Application: avgSpraySpeed, totalSprLength, totalFlightLength,
 *                     avgXtError, avgHdop, flowAccuracyPct
 *        AppFile:     totalSprLength, totalFlightLength
 */

// ─── Environment bootstrap ────────────────────────────────────────────────────
const path = require('path');
const args = process.argv.slice(2);

let envFile = './environment.env';
for (let i = 0; i < args.length; i++) {
  if (args[i] === '--env' && args[i + 1]) { envFile = args[i + 1]; i++; }
  else if (args[i].startsWith('--env='))   { envFile = args[i].split('=')[1]; }
}
require('dotenv').config({ path: path.resolve(process.cwd(), envFile) });

// ─── Imports (after env is loaded) ───────────────────────────────────────────
const debug    = require('debug')('agm:migrate-applications');
const mongoose = require('mongoose');
const { DBConnection }      = require('../helpers/db/connect');
const Application           = require('../model/application');
const AppFile               = require('../model/application_file');
const AppDetail             = require('../model/application_detail');
const { App, Job }          = require('../model/index.js');
const appDateTime           = require('../helpers/application_datetime');
const { RecTypes }          = require('../helpers/work_record');
const { rateInfoFromFileMeta } = require('../helpers/utils');

// ─── Argument parsing ─────────────────────────────────────────────────────────
const cfg = {
  dryRun:          false,
  force:           false,
  repairAgnXt:     false,  // One-time: fix LQD AgNav binary AppDetail.xTrack from raw cm to metres
  revertRepairAgnXt: false, // One-time: reverse a partial --repair-agn-xt run (× 100) for first N files
  revertRepairN:   0,      // Number of LQD files to revert (from the aborted repair run)
  fixDecodedXt:    false,  // One-time: fix AppDetail.xTrack wrongly decoded by _readAgnBinary (× 100 non-integers)
  fixXtApps:       false,  // One-time: unset avgXtError on LQD NT apps where value is in impossible range (0, 1.0)
  batchSize:       50,
  tierDays:        null,   // Number  — only apps newer than N days
  fromDate:        null,   // String  — ISO date, only apps on/after this date
  concurrency:     3,      // parallel file cursors per app
  missingLimit:    100,    // max apps to show in missing-legacy-datetime report
  skipDatetime:    false,  // skip datetime computation (pass 1B)
  skipAggregates:  false,  // skip aggregates + flowAccuracyPct (passes 1A + 2)
};

for (let i = 0; i < args.length; i++) {
  const a = args[i];
  if      (a === '--dry-run')                   cfg.dryRun         = true;
  else if (a === '--force')                     cfg.force          = true;
  else if (a === '--repair-agn-xt')             cfg.repairAgnXt       = true;
  else if (a.startsWith('--revert-repair-agn-xt=')) { cfg.revertRepairAgnXt = true; cfg.revertRepairN = parseInt(a.split('=')[1], 10) || 0; }
  else if (a === '--fix-decoded-xt')            cfg.fixDecodedXt      = true;
  else if (a === '--fix-xt-apps')               cfg.fixXtApps         = true;
  else if (a === '--skip-datetime')             cfg.skipDatetime   = true;
  else if (a === '--skip-aggregates')           cfg.skipAggregates = true;
  else if (a.startsWith('--batch-size='))       cfg.batchSize      = parseInt(a.split('=')[1], 10) || 50;
  else if (a.startsWith('--tier-days='))        cfg.tierDays       = parseInt(a.split('=')[1], 10) || null;
  else if (a.startsWith('--from-date='))        cfg.fromDate       = a.split('=')[1] || null;
  else if (a.startsWith('--concurrency='))      cfg.concurrency    = parseInt(a.split('=')[1], 10) || 3;
  else if (a === '--missing-limit' && args[i + 1]) {
    const parsed = parseInt(args[i + 1], 10);
    if (!Number.isNaN(parsed) && parsed > 0) cfg.missingLimit = parsed;
    i++;
  } else if (a.startsWith('--missing-limit=')) {
    const parsed = parseInt(a.split('=')[1], 10);
    if (!Number.isNaN(parsed) && parsed > 0) cfg.missingLimit = parsed;
  }
  // --env already consumed above
}

// ─── Constants ────────────────────────────────────────────────────────────────
const MAX_SEGMENT_METERS   = 1000; // Sanity cap matching job_worker readSatLogAsc
const PROGRESS_LOG_INTERVAL = 100; // Log a line every N apps

// ─── Helpers ──────────────────────────────────────────────────────────────────

/**
 * Fast Haversine distance in metres between two WGS-84 points.
 * Avoids turf object allocation in tight loops.
 */
function haversineMeters(lon1, lat1, lon2, lat2) {
  const R  = 6371000;
  const φ1 = lat1 * Math.PI / 180;
  const φ2 = lat2 * Math.PI / 180;
  const Δφ = (lat2 - lat1) * Math.PI / 180;
  const Δλ = (lon2 - lon1) * Math.PI / 180;
  const a  = Math.sin(Δφ / 2) ** 2 + Math.cos(φ1) * Math.cos(φ2) * Math.sin(Δλ / 2) ** 2;
  return R * 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
}

/**
 * Create a minimal ObjectId whose timestamp equals `date`.
 */
function objectIdFromDate(date) {
  const ts  = Math.floor(new Date(date).getTime() / 1000);
  const hex = ts.toString(16).padStart(8, '0') + '0000000000000000';
  return new mongoose.Types.ObjectId(hex);
}

// ─── AppDetail helpers ────────────────────────────────────────────────────────

/**
 * Lightweight single-record lookup used in --skip-aggregates mode.
 * Returns the first AppDetail record with a valid lat/lon, sorted by gpsTime ASC.
 * Mirrors getReferenceDetail from backfill_application_datetimes.js.
 */
async function getReferenceDetail(appId) {
  const files = await AppFile.find({ appId, markedDelete: { $ne: true } }, { _id: 1 }).lean();
  const fileIds = files.map(f => f._id);

  if (fileIds.length) {
    return AppDetail.findOne({
      fileId: { $in: fileIds },
      lat: { $type: 'number' },
      lon: { $type: 'number' }
    }).sort({ gpsTime: 1, _id: 1 }).lean();
  }

  return AppDetail.findOne({
    appId,
    lat: { $type: 'number' },
    lon: { $type: 'number' }
  }).sort({ gpsTime: 1, _id: 1 }).lean();
}

/**
 * Convert an AppFile.meta application rate to metric (L/ha or Kg/ha).
 * Mirrors the unit conversion in job_worker.js::getAppliedRate().
 * RateUnit codes: 0=oz/acre, 1=gal/acre, 2=lbs/acre, 3=L/ha, 4=Kg/ha
 */
function metricAppRateFromMeta(meta) {
  const rate = meta.appRate;
  if (!rate || rate <= 0) return 0;
  let unit = typeof meta.rateUnit === 'number' ? meta.rateUnit : -1;
  if (unit < 0 && typeof meta.appRateUnitStr === 'string') {
    const s = meta.appRateUnitStr.toLowerCase();
    if (s.includes('oz'))       unit = 0;
    else if (s.includes('gal')) unit = 1;
    else if (s.includes('lb'))  unit = 2;
    else if (s.includes('l/ha') || s.includes('lit')) unit = 3;
    else if (s.includes('kg'))  unit = 4;
  }
  if (unit === 0) return rate * 0.0730778;  // oz/acre → L/ha
  if (unit === 1) return rate * 9.35396;    // gal/acre → L/ha
  if (unit === 2) return rate * 1.12085;    // lbs/acre → Kg/ha
  return rate;                              // L/ha or Kg/ha — already metric
}

// ─── One-time LQD AgNav xTrack repair ────────────────────────────────────────

/**
 * One-time repair: convert AppDetail.xTrack from raw cm to metres for AgNav
 * LQD binary files.
 *
 * Background: AppDetail.xTrack has been inconsistent across file types:
 *   - AgNav binary LQD (.nt): raw cm (integer) — needs ÷ 100
 *   - AgNav binary DRY (.nt): already metres (AMS DRY decode × 1E-2 + merge)
 *   - SatLoc ASCII (.asc):    already metres (X-Track field is in metres)
 *   - AgNav Shape (.shp):     already metres (XTRACK field is in metres)
 *
 * The fix in work_record.js now decodes _readAgnBinary xTrack cm → m so all
 * types are consistent metres.  This function converts the existing raw-cm
 * AppDetail records for LQD AgNav binary files to match.
 *
 * Run ONCE before restarting the server with the updated code.
 * LQD binary files are identified by:
 *   - file name ending in .nt (case-insensitive)
 *   - rateInfoFromFileMeta does NOT return AGN_BIN_DRY
 */
async function repairAgnXtData() {
  debug('repair-agn-xt: scanning AppFiles for LQD AgNav binary type…');

  // AgNav NT binary: nYMMDDHH.tMM or nYMMDDHH-N.tMM  (e.g. n6021809.t15)
  const RE_AGN_NT = /^n\d{7}(-\d+)?\.t\d{2}$/i;

  const lqdFileIds = [];
  let totalScanned = 0, skippedNoName = 0, skippedExtension = 0, skippedDry = 0;
  const fileCursor = AppFile.find().select('_id name meta').lean().cursor();
  for await (const f of fileCursor) {
    totalScanned++;
    if (!f.name) { skippedNoName++; continue; }
    const base = require('path').basename(f.name);

    if (!RE_AGN_NT.test(base)) { skippedExtension++; continue; }
    // Skip DRY NT: AMS DRY decode already stored metres
    const rateInfo = rateInfoFromFileMeta(f.meta || {}, RecTypes.AGN_BIN_LQD);
    if (rateInfo.recType === RecTypes.AGN_BIN_DRY) { skippedDry++; continue; }
    lqdFileIds.push(f._id);
  }

  debug(`repair-agn-xt: scanned ${totalScanned} — noName:${skippedNoName} other:${skippedExtension} dry:${skippedDry} lqd-nt:${lqdFileIds.length}`);

  if (!lqdFileIds.length) {
    debug('repair-agn-xt: no LQD AgNav binary AppFiles found — nothing to repair');
    return;
  }

  const BATCH = 500;
  let totalRepaired = 0, totalApps = new Set();

  if (cfg.dryRun) {
    let dryCount = 0;
    for (let i = 0; i < lqdFileIds.length; i += BATCH) {
      const batch = lqdFileIds.slice(i, i + BATCH);
      dryCount += await AppDetail.countDocuments({ fileId: { $in: batch }, xTrack: { $ne: 0 } });
    }
    debug(`[dry-run] repair-agn-xt: would repair ${dryCount} AppDetail xTrack record(s) (× 1E-2)`);
  } else {
    for (let i = 0; i < lqdFileIds.length; i += BATCH) {
      const batch = lqdFileIds.slice(i, i + BATCH);
      const result = await AppDetail.updateMany(
        { fileId: { $in: batch }, xTrack: { $ne: 0 } },
        [{ $set: { xTrack: { $multiply: ['$xTrack', 0.01] } } }]
      );
      totalRepaired += result.modifiedCount;

      const appIds = await AppFile.distinct('appId', { _id: { $in: batch } });
      appIds.forEach(id => totalApps.add(String(id)));

      if ((i / BATCH) % 20 === 0)
        debug(`repair-agn-xt: progress ${i + batch.length}/${lqdFileIds.length} files, ${totalRepaired} records repaired so far`);
    }

    debug(`repair-agn-xt: repaired ${totalRepaired} AppDetail xTrack record(s)`);

    // Unset avgXtError on affected apps so plain migration recomputes them
    const appIdArr = [...totalApps].map(id => require('mongoose').Types.ObjectId(id));
    if (appIdArr.length) {
      await Application.updateMany(
        { _id: { $in: appIdArr } },
        { $unset: { avgXtError: 1 } }
      );
      debug(`repair-agn-xt: unset avgXtError on ${appIdArr.length} Application(s) — run migration to recompute`);
    }
  }
}

/**
 * Reverse a partial --repair-agn-xt run.
 *
 * The repair mistakenly multiplied FlightData.xtrack (already metres) by 0.01.
 * This function scans LQD NT AppFiles in natural order (same as repairAgnXtData),
 * takes the first N files, and multiplies their AppDetail.xTrack by 100 to restore
 * the original integer-metres values.  Also unsets avgXtError so migration recomputes.
 *
 * Usage: --revert-repair-agn-xt=170500
 */
async function revertRepairAgnXtData(n) {
  debug(`revert-repair-agn-xt: scanning to collect first ${n} LQD NT AppFiles (same order as repair)…`);

  const RE_AGN_NT = /^n\d{7}(-\d+)?\.t\d{2}$/i;
  const targetIds = [];
  const fileCursor = AppFile.find().select('_id name meta').lean().cursor();
  for await (const f of fileCursor) {
    if (targetIds.length >= n) break;
    if (!f.name) continue;
    const base = require('path').basename(f.name);
    if (!RE_AGN_NT.test(base)) continue;
    const rateInfo = rateInfoFromFileMeta(f.meta || {}, RecTypes.AGN_BIN_LQD);
    if (rateInfo.recType === RecTypes.AGN_BIN_DRY) continue;
    targetIds.push(f._id);
  }

  debug(`revert-repair-agn-xt: reverting ${targetIds.length} files (× 100)…`);

  const BATCH = 500;
  let totalReverted = 0;
  const totalApps = new Set();

  for (let i = 0; i < targetIds.length; i += BATCH) {
    const batch = targetIds.slice(i, i + BATCH);
    const result = await AppDetail.updateMany(
      { fileId: { $in: batch }, xTrack: { $ne: 0 } },
      { $mul: { xTrack: 100 } }
    );
    totalReverted += result.modifiedCount;

    if (result.modifiedCount) {
      const appIds = await AppFile.distinct('appId', { _id: { $in: batch } });
      appIds.forEach(id => totalApps.add(String(id)));
    }

    if ((i / BATCH) % 20 === 0)
      debug(`revert-repair-agn-xt: progress ${i + batch.length}/${targetIds.length} files, ${totalReverted} records restored`);
  }

  debug(`revert-repair-agn-xt: restored ${totalReverted} AppDetail xTrack record(s)`);

  const appIdArr = [...totalApps].map(id => require('mongoose').Types.ObjectId(id));
  if (appIdArr.length) {
    await Application.updateMany(
      { _id: { $in: appIdArr } },
      { $unset: { avgXtError: 1 } }
    );
    debug(`revert-repair-agn-xt: unset avgXtError on ${appIdArr.length} Application(s) — run migration to recompute`);
  }
}

/**
 * Fix AppDetail.xTrack records that were wrongly decoded by the brief _readAgnBinary
 * bug (which applied × 1E-2 to FlightData.xtrack, which is already integer metres).
 *
 * Detection: FlightData.xtrack is always a rounded integer. Any non-integer xTrack
 * value for a LQD NT file is definitively a wrongly-decoded record (× 0.01 applied).
 * Multiplying back by 100 restores the original integer-metres value.
 *
 * Safe to run after --revert-repair-agn-xt — the revert restores old records to
 * integers, so only newly-uploaded wrongly-decoded records remain as decimals.
 */
async function fixDecodedXtData() {
  debug('fix-decoded-xt: scanning LQD NT AppFiles for wrongly-decoded xTrack (non-integer)…');

  const RE_AGN_NT = /^n\d{7}(-\d+)?\.t\d{2}$/i;
  const lqdFileIds = [];
  const fileCursor = AppFile.find().select('_id name meta').lean().cursor();
  for await (const f of fileCursor) {
    if (!f.name) continue;
    const base = require('path').basename(f.name);
    if (!RE_AGN_NT.test(base)) continue;
    const rateInfo = rateInfoFromFileMeta(f.meta || {}, RecTypes.AGN_BIN_LQD);
    if (rateInfo.recType === RecTypes.AGN_BIN_DRY) continue;
    lqdFileIds.push(f._id);
  }

  debug(`fix-decoded-xt: found ${lqdFileIds.length} LQD NT files — checking for non-integer xTrack…`);

  // Non-integer xTrack → was decoded with the wrong × 0.01 → multiply back by 100.
  //
  // Detection via aggregation pipeline update with $floor:
  //   { $ne: ['$xTrack', { $floor: '$xTrack' }] }  →  true when xTrack has a fractional part
  //
  // Why NOT $mod query operator: MongoDB's $mod TRUNCATES floats to int before computing,
  // so { $mod: [1, 0] } matches ALL numbers (trunc(6.89) % 1 = 6 % 1 = 0). This silently
  // makes $nor: [{ $mod:[1,0] }] find nothing. Discovered after 4512 apps still had wrong
  // avgXtError despite "fix" reporting zero records (2026-06-23).
  //
  // Why NOT $expr in the filter: $expr prevents fileId index pushdown on 1.5B docs → DB crash.
  //
  // Safe approach: simple filter { fileId: { $in: batch } } for index pushdown, then
  // aggregation pipeline update with $cond+$floor to selectively multiply fractional values.
  // The pipeline runs AFTER index lookup — no index interference.
  const BATCH = 500;
  let totalFixed = 0;
  const totalApps = new Set();

  for (let i = 0; i < lqdFileIds.length; i += BATCH) {
    const batch = lqdFileIds.slice(i, i + BATCH);

    if (cfg.dryRun) {
      // No AppDetail queries in dry-run — any count/find on 1.5B docs risks server load.
    } else {
      // Pipeline update: only modifies records where xTrack ≠ floor(xTrack) (fractional).
      // Integer values (floor(n) == n) are left unchanged by the else branch.
      const result = await AppDetail.updateMany(
        { fileId: { $in: batch } },
        [{
          $set: {
            xTrack: {
              $cond: {
                if: { $and: [
                  { $ne: ['$xTrack', 0] },
                  { $ne: ['$xTrack', { $floor: '$xTrack' }] }
                ]},
                then: { $multiply: ['$xTrack', 100] },
                else: '$xTrack'
              }
            }
          }
        }]
      );
      if (result.modifiedCount) {
        totalFixed += result.modifiedCount;
        const appIds = await AppFile.distinct('appId', { _id: { $in: batch } });
        appIds.forEach(id => totalApps.add(String(id)));
      }
    }

    if ((i / BATCH) % 20 === 0)
      debug(`fix-decoded-xt: progress ${i + batch.length}/${lqdFileIds.length} files${cfg.dryRun ? '' : `, ${totalFixed} records fixed so far`}`);
  }

  if (cfg.dryRun) {
    debug(`fix-decoded-xt: [dry-run] scanned ${lqdFileIds.length} LQD NT files — run without --dry-run to apply fix`);
    return;
  }

  debug(`fix-decoded-xt: fixed ${totalFixed} AppDetail xTrack record(s)`);

  if (totalApps.size) {
    const appIdArr = [...totalApps].map(id => require('mongoose').Types.ObjectId(id));
    await Application.updateMany(
      { _id: { $in: appIdArr } },
      { $unset: { avgXtError: 1 } }
    );
    debug(`fix-decoded-xt: unset avgXtError on ${appIdArr.length} Application(s) — run migration to recompute`);
  } else {
    debug('fix-decoded-xt: no wrongly-decoded records found — nothing to fix');
  }
}

/**
 * Unset avgXtError on LQD NT applications where its value is in the impossible
 * range (0, 1.0) — which is the wrong-decode signature at the application level.
 *
 * FlightData.xtrack is integer metres, so the minimum meaningful avgXtError for
 * an LQD NT app is 1.0 m.  Any value in (0, 1.0) was computed from fractional
 * AppDetail.xTrack values (wrong × 0.01 decode).  After unsetting, run migration
 * to recompute from the now-corrected AppDetail records.
 *
 * DRY apps are explicitly excluded — AMS xTrack has 0.01 m precision so small
 * avgXtError values are valid there.
 *
 * Usage: --fix-xt-apps
 */
async function fixXtApps() {
  debug('fix-xt-apps: scanning LQD NT AppFiles to collect app IDs…');

  const RE_AGN_NT = /^n\d{7}(-\d+)?\.t\d{2}$/i;
  const appIdSet = new Set();
  const fileCursor = AppFile.find().select('_id name meta appId').lean().cursor();
  for await (const f of fileCursor) {
    if (!f.name || !f.appId) continue;
    const base = require('path').basename(f.name);
    if (!RE_AGN_NT.test(base)) continue;
    const rateInfo = rateInfoFromFileMeta(f.meta || {}, RecTypes.AGN_BIN_LQD);
    if (rateInfo.recType === RecTypes.AGN_BIN_DRY) continue;
    appIdSet.add(String(f.appId));
  }

  const appIds = [...appIdSet].map(id => require('mongoose').Types.ObjectId(id));
  debug(`fix-xt-apps: found ${appIds.length} LQD NT app IDs — scanning for avgXtError in (0, 1.0)…`);

  // avgXtError in (0, 1.0) is physically impossible for LQD (integer metres).
  // Process in batches — Application._id is indexed.
  const BATCH = 500;
  let totalUnset = 0;

  for (let i = 0; i < appIds.length; i += BATCH) {
    const batch = appIds.slice(i, i + BATCH);
    const filter = { _id: { $in: batch }, avgXtError: { $gt: 0, $lt: 1.0 } };

    if (cfg.dryRun) {
      const count = await Application.countDocuments(filter);
      if (count) debug(`[dry-run] fix-xt-apps: batch ${i}-${i + batch.length}: ${count} apps would be reset`);
    } else {
      const result = await Application.updateMany(filter, { $unset: { avgXtError: 1 } });
      totalUnset += result.modifiedCount;
    }

    if ((i / BATCH) % 20 === 0)
      debug(`fix-xt-apps: progress ${i + batch.length}/${appIds.length} apps scanned${cfg.dryRun ? '' : `, ${totalUnset} reset so far`}`);
  }

  if (cfg.dryRun) {
    debug('fix-xt-apps: [dry-run] complete — run without --dry-run to apply');
  } else {
    debug(`fix-xt-apps: unset avgXtError on ${totalUnset} Application(s) — run migration to recompute`);
  }
}

/**
 * Process all AppDetail records for one AppFile in a single streaming pass.
 *
 * @param {ObjectId} fileId
 *
 * Returns aggregate accumulators AND the first valid coordinate encountered:
 *   { sprLength, flightLength, speedSum, speedCount, xtSum, xtCount,
 *     hdopSum, hdopCount, firstLat, firstLon }
 *
 * firstLat/firstLon capture the first record whose lat and lon are valid numbers,
 * regardless of sprayStat.  They are used for timezone lookup when computing
 * datetime fields, eliminating a separate findOne query.
 *
 * Validity gates (matching job_worker conventions):
 *   sprLength    — spray-ON segment (prevStat > 0 OR curStat > 0) AND distance ≤ 1000 m AND time gap ≤ 120 s
 *   flightLength — distance ≤ 1000 m AND time gap ≤ 120 s (all GPS movements)
 *   avgXtError   — spray-on (sprayStat 1 or 3) records within valid segments, xTrack ≠ 0
 *   avgHdop      — spray-on (sprayStat > 0) records within valid segments, stdHdop > 0
 */
async function processFile(fileId) {
  let sprLength    = 0;
  let flightLength = 0;
  let speedSum     = 0;
  let speedCount   = 0;
  let xtSum        = 0;
  let xtCount      = 0;
  let hdopSum      = 0;
  let hdopCount    = 0;
  let reqRateSum   = 0;  // For computing appRate from existing AppDetail lhaReq (SatLoc backfill)
  let reqRateCount = 0;
  let firstLat     = null;
  let firstLon     = null;

  let prevLon     = null;
  let prevLat     = null;
  let prevGpsTime = null;
  let prevStat    = -999;

  // Sort by gpsTime for correct consecutive-point distance calculation.
  // Tie-break by _id for determinism when gpsTime == 0 (legacy).
  const cursor = AppDetail
    .find({ fileId })
    .select('lat lon gpsTime sprayStat grSpeed xTrack stdHdop lhaReq')
    .sort({ gpsTime: 1, _id: 1 })
    .lean()
    .cursor();

  for await (const rec of cursor) {
    const curLon     = rec.lon;
    const curLat     = rec.lat;
    const curGpsTime = rec.gpsTime;
    const curStat    = rec.sprayStat || 0;

    // ── Capture first valid coordinate for datetime timezone lookup ──────────
    if (firstLat === null && typeof curLat === 'number' && typeof curLon === 'number') {
      firstLat = curLat;
      firstLon = curLon;
    }

    if (prevStat !== -999 &&
        (typeof curLon === 'number') && (typeof curLat === 'number') &&
        (typeof prevLon === 'number') && (typeof prevLat === 'number')) {

      const d = haversineMeters(prevLon, prevLat, curLon, curLat);

      // Time gap with midnight-rollover handling (gpsTime is seconds-of-day)
      let timeDif = (typeof curGpsTime === 'number' && typeof prevGpsTime === 'number')
        ? curGpsTime - prevGpsTime
        : Infinity;
      if (timeDif < 0 && Math.abs(timeDif) >= 80000) timeDif = 86400 - prevGpsTime + curGpsTime;

      // ── Valid segment: both distance AND time gate ───────────────────────────
      const segValid = d <= MAX_SEGMENT_METERS && timeDif > 0 && timeDif <= 120;

      // ── Spray length: spray-ON segment within valid (both-gate) window ────────
      // Matches job_worker _computeSprLength which applies both distance AND time gates.
      if (segValid && (prevStat > 0 || curStat > 0)) {
        sprLength += d;
      }

      // ── Flight length: all GPS movements within valid segments ───────────────
      if (segValid) {
        flightLength += d;
      }

      // ── XT error: spray-on records (stat 1 = on-swath, 3 = swath entry) ──────
      // Only within valid segments; exclude zero (no reading) and non-numeric values.
      if (segValid && (curStat === 1 || curStat === 3) &&
          typeof rec.xTrack === 'number' && rec.xTrack !== 0) {
        xtSum   += Math.abs(rec.xTrack);
        xtCount += 1;
      }

      // ── HDOP: spray-on records within valid segments, stdHdop > 0 ────────────
      if (segValid && curStat > 0 && typeof rec.stdHdop === 'number' && rec.stdHdop > 0) {
        hdopSum   += rec.stdHdop;
        hdopCount += 1;
      }

    }

    // ── Speed accumulation (all spray-on, no segment gate needed) ────────────
    if (curStat > 0 && typeof rec.grSpeed === 'number' && rec.grSpeed > 0) {
      speedSum   += rec.grSpeed;
      speedCount += 1;
    }

    // ── Prescribed rate: all spray-on records, no segment gate ───────────────
    // lhaReq is a configured target value (same for all records from the same
    // flow controller setup), not a position measurement.  No segment gate needed.
    // Used to backfill appRate for SatLoc apps where it was hardcoded to 0 at import.
    if (curStat > 0 && typeof rec.lhaReq === 'number' && rec.lhaReq > 0) {
      reqRateSum   += rec.lhaReq;
      reqRateCount += 1;
    }

    prevLon     = curLon;
    prevLat     = curLat;
    prevGpsTime = curGpsTime;
    prevStat    = curStat;
  }

  return { sprLength, flightLength, speedSum, speedCount, xtSum, xtCount, hdopSum, hdopCount, reqRateSum, reqRateCount, firstLat, firstLon };
}

/**
 * Process all files for one Application with bounded parallelism.
 *
 * Returns:
 *   appSprLength      — total spray length across all files (m)
 *   appFlightLength   — total flight length across all files (m)
 *   avgSpraySpeed     — weighted avg ground speed across all files (m/s, null if no data)
 *   avgXtError        — weighted avg abs cross-track error (m, null if no data)
 *   avgHdop           — weighted avg HDOP over spray-on records (null if no data)
 *   fileOps           — array of { fileId, sprLength, flightLength } for per-file bulkWrite
 *   firstLat          — first valid latitude found across all files (null if none)
 *   firstLon          — first valid longitude found across all files (null if none)
 */
async function processApplication(_app, appFiles) {
  if (!appFiles.length) {
    return {
      appSprLength: 0, appFlightLength: 0,
      avgSpraySpeed: null, avgXtError: null, avgHdop: null,
      appRate: null,
      fileOps: [],
      firstLat: null, firstLon: null,
    };
  }

  // Process files in small parallel batches (bounded concurrency)
  const fileResults = [];
  for (let i = 0; i < appFiles.length; i += cfg.concurrency) {
    const slice   = appFiles.slice(i, i + cfg.concurrency);
    const results = await Promise.all(slice.map(f => processFile(f._id)));
    for (let j = 0; j < slice.length; j++) {
      fileResults.push({ file: slice[j], result: results[j] });
    }
  }

  let appSprLength    = 0;
  let appFlightLength = 0;
  let totalSpeedSum   = 0;
  let totalSpeedCnt   = 0;
  let totalXtSum      = 0;
  let totalXtCnt      = 0;
  let totalHdopSum    = 0;
  let totalHdopCnt    = 0;
  let totalReqRateSum = 0;
  let totalReqRateCnt = 0;
  let firstLat        = null;
  let firstLon        = null;
  const fileOps       = [];

  for (const { file, result } of fileResults) {
    appSprLength    += result.sprLength;
    appFlightLength += result.flightLength;
    totalSpeedSum   += result.speedSum;
    totalSpeedCnt   += result.speedCount;
    totalXtSum      += result.xtSum;
    totalXtCnt      += result.xtCount;
    totalHdopSum    += result.hdopSum;
    totalHdopCnt    += result.hdopCount;
    totalReqRateSum += result.reqRateSum;
    totalReqRateCnt += result.reqRateCount;

    // Keep the first valid coordinate found across all files (files are processed
    // in array order, which mirrors the order AppFile returned them).
    if (firstLat === null && result.firstLat !== null) {
      firstLat = result.firstLat;
      firstLon = result.firstLon;
    }

    fileOps.push({ fileId: file._id, sprLength: result.sprLength, flightLength: result.flightLength });
  }

  const avgSpraySpeed = totalSpeedCnt > 0 ? totalSpeedSum / totalSpeedCnt : null;
  const avgXtError    = totalXtCnt > 0 ? totalXtSum / totalXtCnt : null;
  const avgHdop       = totalHdopCnt > 0 ? totalHdopSum / totalHdopCnt : null;
  // null = no spray-on records with lhaReq; 0 treated same as null by caller
  const appRate       = totalReqRateCnt > 0 ? totalReqRateSum / totalReqRateCnt : null;

  return { appSprLength, appFlightLength, avgSpraySpeed, avgXtError, avgHdop, appRate, fileOps, firstLat, firstLon };
}

// ─── Flow accuracy backfill (Application-level only) ─────────────────────────

/**
 * Backfill Application.flowAccuracyPct for documents that already have
 * totalSprayMat > 0, totalSprayed > 0, and appRate > 0 but no flowAccuracyPct.
 * No AppDetail queries needed — all three source fields live on Application.
 */
async function backfillFlowAccuracy() {
  debug('─'.repeat(60));
  debug('Pass 2: backfill flowAccuracyPct …');

  const filter = {
    markedDelete:    { $ne: true },
    flowAccuracyPct: { $exists: false },
    totalSprayed:    { $gt: 0 },
    totalSprayMat:   { $gt: 0 },
    appRate:         { $gt: 0 },
  };

  if (cfg.tierDays) {
    const cutoff = new Date(Date.now() - cfg.tierDays * 86400 * 1000);
    filter._id   = { $gte: objectIdFromDate(cutoff) };
  } else if (cfg.fromDate) {
    filter._id   = { $gte: objectIdFromDate(cfg.fromDate + 'T00:00:00Z') };
  }

  debug('flowAccuracy filter: %o', filter);

  if (cfg.dryRun) {
    const count = await Application.countDocuments(filter);
    debug(`[dry-run] Would update ${count} Application docs with flowAccuracyPct`);
    debug('─'.repeat(60));
    return;
  }

  // MongoDB 4.2+ aggregation pipeline update — computes the field server-side
  const result = await Application.updateMany(filter, [
    {
      $set: {
        flowAccuracyPct: {
          $round: [
            { $multiply: [{ $divide: [{ $divide: ['$totalSprayMat', '$totalSprayed'] }, '$appRate'] }, 100] },
            2]
        }
      }
    }
  ]);

  debug(`flowAccuracyPct backfill: matched=${result.matchedCount} modified=${result.modifiedCount}`);
  debug('─'.repeat(60));
}

// ─── Diagnostic: apps missing legacy start/end datetime ──────────────────────

/**
 * Report apps that are missing legacy startDateTime/endDateTime and therefore
 * cannot have their UTC fields backfilled by this script.
 * Kept from backfill_application_datetimes.js for operator visibility.
 */
async function reportMissingLegacyDateApps(limit = 100) {
  const missingLegacyQuery = {
    $or: [
      { startDateTime: { $exists: false } },
      { startDateTime: null },
      { endDateTime:   { $exists: false } },
      { endDateTime:   null }
    ]
  };

  const totalMissingLegacy = await App.countDocuments(missingLegacyQuery);
  if (!totalMissingLegacy) return;

  const apps = await App.find(
    missingLegacyQuery,
    '_id jobId status proStatus startDateTime endDateTime createdDate updateDate errorMsg'
  ).sort({ _id: 1 }).limit(limit).lean();

  // Job._id is a numeric auto-increment field (mongoose-sequence, inc_field: '_id').
  // Application.jobId stores that same numeric _id directly.
  const jobIds = [
    ...new Set(
      apps
        .map(a => a.jobId)
        .filter(v => v !== null && v !== undefined && Number.isFinite(Number(v)))
        .map(v => Number(v))
    )
  ];

  const jobs = jobIds.length
    ? await Job.find({ _id: { $in: jobIds } }, '_id name status').lean()
    : [];
  const jobMapById = new Map(jobs.map(j => [Number(j._id), j]));

  console.log('');
  console.log(
    `[migrate] Apps missing legacy start/end datetime (cannot be backfilled): ` +
    `total=${totalMissingLegacy}, showing=${apps.length}`
  );

  for (const app of apps) {
    const files = await AppFile.find({ appId: app._id, markedDelete: { $ne: true } }, { _id: 1 }).lean();
    const fileIds = files.map(f => f._id);
    // appId has no index and obsoleted — never fall back to it on a billion-doc collection
    const detailCount = fileIds.length
      ? await AppDetail.countDocuments({ fileId: { $in: fileIds } })
      : 0;

    const job = (app.jobId !== null && app.jobId !== undefined)
      ? (jobMapById.get(Number(app.jobId)) || null)
      : null;
    const likelyNoDataFiles = files.length === 0 || detailCount === 0;

    console.log(
      `[migrate] appId=${app._id}`
      + ` App-jobId=${app.jobId || 'null'}`
      + ` jobId=${job ? job._id : 'n/a'}`
      + ` jobStatus=${job && job.status !== undefined ? job.status : 'n/a'}`
      + ` appStatus=${app.status !== undefined ? app.status : 'n/a'}`
      + ` proStatus=${app.proStatus !== undefined ? app.proStatus : 'n/a'}`
      + ` startDateTime=${app.startDateTime || 'null'}`
      + ` endDateTime=${app.endDateTime || 'null'}`
      + ` appFiles=${files.length}`
      + ` appDetails=${detailCount}`
      + ` likelyNoDataFiles=${likelyNoDataFiles ? 'yes' : 'no'}`
    );
  }

  if (totalMissingLegacy > apps.length) {
    console.log(
      `[migrate] ... ${totalMissingLegacy - apps.length} more apps omitted. ` +
      `Use --missing-limit <n> to show more.`
    );
  }
}

// ─── Main migration ───────────────────────────────────────────────────────────
async function migrate() {
  debug('Config: %o', { ...cfg, envFile });

  // ── One-time LQD AgNav xTrack repair ─────────────────────────────────────────
  if (cfg.revertRepairAgnXt) {
    if (!cfg.revertRepairN) {
      debug('revert-repair-agn-xt: --revert-repair-agn-xt=N requires a file count (e.g. --revert-repair-agn-xt=170500)');
      return;
    }
    await revertRepairAgnXtData(cfg.revertRepairN);
    if (!cfg.force) {
      debug('revert complete. Run migration to recompute avgXtError:');
      debug('  node scripts/migrate_applications.js');
      return;
    }
  }

  if (cfg.repairAgnXt) {
    await repairAgnXtData();
    if (!cfg.force) {
      debug('repair-agn-xt complete.  Restart the server with the new code, then run the migration:');
      debug('  node scripts/migrate_applications.js');
      return;
    }
  }

  if (cfg.fixDecodedXt) {
    await fixDecodedXtData();
    if (!cfg.force) {
      debug('fix-decoded-xt complete. Run migration to recompute avgXtError:');
      debug('  node scripts/migrate_applications.js');
      return;
    }
  }

  if (cfg.fixXtApps) {
    await fixXtApps();
    if (!cfg.force) {
      debug('fix-xt-apps complete. Run migration to recompute avgXtError:');
      debug('  node scripts/migrate_applications.js');
      return;
    }
  }

  // ── Build Application filter ────────────────────────────────────────────────
  const appFilter = { markedDelete: { $ne: true } };

  // Tier by recency
  if (cfg.tierDays) {
    const cutoff  = new Date(Date.now() - cfg.tierDays * 86400 * 1000);
    appFilter._id = { $gte: objectIdFromDate(cutoff) };
    debug(`Tier: apps from last ${cfg.tierDays} days (>= ${cutoff.toISOString()})`);
  } else if (cfg.fromDate) {
    appFilter._id = { $gte: objectIdFromDate(cfg.fromDate + 'T00:00:00Z') };
    debug(`Tier: apps from ${cfg.fromDate} onward`);
  }

  // Unless --force, build a union $or from the conditions of both original scripts,
  // controlled by the skip flags so we only match what we intend to process.
  if (!cfg.force) {
    const orConditions = [];

    if (!cfg.skipDatetime) {
      // Datetime conditions: fields not yet written.
      // NOTE: utcOffset: 0 is intentionally excluded — it is a valid computed value for
      // UTC+0 locations (UK, Ireland, Portugal). Including it causes infinite re-processing.
      // Use --force to recompute apps that have utcOffset: 0 from a buggy prior run.
      orConditions.push(
        { utcOffset:        { $exists: false } },
        { startDateTimeUTC: { $exists: false } },
        { endDateTimeUTC:   { $exists: false } },
        { $expr:            { $gt: ['$startDateTimeUTC', '$endDateTimeUTC'] } } // Inverted dates
      );
    }

    if (!cfg.skipAggregates) {
      // Aggregate conditions: fields not yet written (existence check only).
      // null and 0 variants are intentionally excluded:
      //   - null means "processed, no spray/flight data found" (valid result for empty apps)
      //   - 0 means "processed, computed to be zero" (valid for apps with no movement)
      // Including null/0 here causes infinite re-processing of apps with no spray records.
      // Use --force to recompute apps whose values are suspected to be incorrect.
      orConditions.push(
        { avgSpraySpeed:      { $exists: false } },
        { totalSprLength:     { $exists: false } },
        { totalFlightLength:  { $exists: false } },
        { avgXtError:         { $exists: false } },
        { avgHdop:            { $exists: false } },
        { totalSprayMatUnit:  { $exists: false } },
        // SatLoc apps: appRate was hardcoded to 0 at import time.
        // Re-select when rate data is present (totalSprayMat > 0) so we can compute
        // appRate from AppDetail lhaReq and then allow Pass 2 to set flowAccuracyPct.
        { appRate: 0, totalSprayed: { $gt: 0 }, totalSprayMat: { $gt: 0 } }
      );
    }

    if (orConditions.length) {
      appFilter.$or = orConditions;
    }
    // If both passes are skipped and no orConditions, filter remains as-is (matches none
    // in normal usage — operator will see 0 apps processed).
  }

  debug('App filter: %o', appFilter);

  const total = await Application.countDocuments(appFilter);
  debug(`Applications to process: ${total}`);

  if (!total) {
    debug('No applications matched selection criteria.');
    if (!cfg.skipDatetime) {
      // Check whether any apps lack the legacy dates that this script requires
      const diag = {
        totalApps:             await Application.countDocuments({}),
        missingStartDateTime:  await Application.countDocuments({ startDateTime: { $exists: false } }),
        nullStartDateTime:     await Application.countDocuments({ startDateTime: null }),
        missingStartDateTimeUTC: await Application.countDocuments({ startDateTimeUTC: { $exists: false } }),
        missingEndDateTimeUTC:   await Application.countDocuments({ endDateTimeUTC:   { $exists: false } }),
        missingUtcOffset:        await Application.countDocuments({ utcOffset:        { $exists: false } }),
        zeroUtcOffset:           await Application.countDocuments({ utcOffset: 0 }),
      };
      debug(`Diagnostics: ${JSON.stringify(diag)}`);

      if (
        diag.missingStartDateTime > 0 || diag.nullStartDateTime > 0
      ) {
        await reportMissingLegacyDateApps(cfg.missingLimit);
      }
    }
    return;
  }

  // ── Stats ───────────────────────────────────────────────────────────────────
  const stats = {
    examined:  0,
    updated:   0,
    skipped:   0,  // no AppFile records / no AppDetail data
    errors:    0,
    startedAt: Date.now(),
  };

  // ── Pending bulk-write buffers ───────────────────────────────────────────────
  let appBulk  = [];  // Application updateOne ops
  let fileBulk = [];  // AppFile updateOne ops

  async function flushBulk() {
    if (cfg.dryRun) {
      debug(`[dry-run] Would write ${appBulk.length} Application + ${fileBulk.length} AppFile ops`);
      appBulk  = [];
      fileBulk = [];
      return;
    }

    const writes = [];
    if (appBulk.length)  writes.push(Application.bulkWrite(appBulk,  { ordered: false }));
    if (fileBulk.length) writes.push(AppFile.bulkWrite(fileBulk,     { ordered: false }));
    await Promise.all(writes);

    appBulk  = [];
    fileBulk = [];
  }

  // ── Stream Applications — newest first ──────────────────────────────────────
  // Select startDateTime and endDateTime in addition to aggregate fields so that
  // we can compute datetime fields in the same pass.
  const appCursor = Application
    .find(appFilter)
    .select('_id startDateTime endDateTime avgSpraySpeed totalSprLength totalSprayMatUnit')
    .sort({ _id: -1 })
    .lean()
    .cursor();

  for await (const app of appCursor) {
    stats.examined++;

    try {
      const $set = {};

      if (!cfg.skipAggregates) {
        // ── Pass 1A + 1B (aggregates mode): full AppDetail streaming pass ────────
        // firstLat/firstLon are captured for free during the streaming pass.
        const appFiles = await AppFile
          .find({ appId: app._id, markedDelete: { $ne: true } })
          .select('_id meta totalSprayMat totalSprayMatUnit')
          .lean();

        if (!appFiles.length) {
          stats.skipped++;
        } else {
          const {
            appSprLength, appFlightLength,
            avgSpraySpeed, avgXtError, avgHdop,
            appRate,
            fileOps,
            firstLat, firstLon,
          } = await processApplication(app, appFiles);

          // Aggregate fields — always write all values, including null.
          // Writing null explicitly marks the app as "processed" so the $exists: false
          // selection condition stops matching it on subsequent runs.
          $set.totalSprLength    = appSprLength;
          $set.totalFlightLength = appFlightLength;
          $set.avgSpraySpeed     = avgSpraySpeed;   // null when no spray-on records
          $set.avgXtError        = avgXtError;      // null when no XT data
          $set.avgHdop           = avgHdop;         // null when no HDOP data
          // Only overwrite appRate when we computed a positive value from lhaReq.
          // This avoids stomping a correctly-set AgNav appRate with null.
          if (appRate !== null && appRate > 0) $set.appRate = appRate;

          // For AgNav apps where lhaReq=0 in all binary records (device firmware didn't
          // record it), fall back to the Q-file / job planned rate stored in AppFile.meta.
          // Applies regardless of flow controller usage — the planned rate is always the
          // prescribed reference for flowAccuracyPct.
          if (!$set.appRate) {
            for (const f of appFiles) {
              const meta = f.meta;
              if (meta && meta.appRate > 0) {
                const metricRate = metricAppRateFromMeta(meta);
                if (metricRate > 0) {
                  $set.appRate = Math.round(metricRate * 100) / 100;
                  break;
                }
              }
            }
          }

          // totalSprayMatUnit: take from the first AppFile that has both positive
          // totalSprayMat and a valid unit — mirrors the aggregation rule in job_worker.
          if (!app.totalSprayMatUnit) {
            for (const f of appFiles) {
              if (f.totalSprayMat > 0 && f.totalSprayMatUnit) {
                $set.totalSprayMatUnit = f.totalSprayMatUnit;
                break;
              }
            }
          }

          // Per-file updates
          for (const op of fileOps) {
            fileBulk.push({
              updateOne: {
                filter: { _id: op.fileId },
                update: { $set: { totalSprLength: op.sprLength, totalFlightLength: op.flightLength } },
              },
            });
          }

          // ── Pass 1B: datetime fields (reuse coordinate from streaming pass) ────
          if (!cfg.skipDatetime && app.startDateTime && firstLat !== null) {
            const dateFields = appDateTime.buildApplicationDateFields({
              startDateTime: app.startDateTime,
              endDateTime:   app.endDateTime,
              latitude:      firstLat,
              longitude:     firstLon,
            });
            $set.utcOffset        = dateFields.utcOffset;
            $set.startDateTimeUTC = dateFields.startDateTimeUTC;
            $set.endDateTimeUTC   = dateFields.endDateTimeUTC;
          } else if (!cfg.skipDatetime && app.startDateTime && firstLat === null) {
            debug(`Skip datetime for app ${app._id}: no coordinate found in AppDetail records`);
          }

          stats.updated++;
        }
      } else {
        // ── Pass 1B only (--skip-aggregates): lightweight findOne for datetime ──
        if (!cfg.skipDatetime && app.startDateTime) {
          const referenceDetail = await getReferenceDetail(app._id);
          if (!referenceDetail) {
            stats.skipped++;
            debug(`Skip ${app._id}: no reference detail with coordinates found`);
          } else {
            const dateFields = appDateTime.buildApplicationDateFields({
              startDateTime: app.startDateTime,
              endDateTime:   app.endDateTime,
              latitude:      referenceDetail.lat,
              longitude:     referenceDetail.lon,
            });
            $set.utcOffset        = dateFields.utcOffset;
            $set.startDateTimeUTC = dateFields.startDateTimeUTC;
            $set.endDateTimeUTC   = dateFields.endDateTimeUTC;

            stats.updated++;
          }
        } else if (!cfg.skipDatetime && !app.startDateTime) {
          // No startDateTime — nothing to compute; not counted as a skip
          // (the query may have matched on aggregate conditions for a different app)
          stats.skipped++;
          debug(`Skip datetime for app ${app._id}: no startDateTime`);
        }
      }

      // Queue Application update if we have anything to write
      if (Object.keys($set).length) {
        appBulk.push({
          updateOne: {
            filter: { _id: app._id },
            update: { $set },
          },
        });
      }
    } catch (err) {
      debug(`Error processing app ${app._id}: ${err.message}`);
      stats.errors++;
    }

    // Flush when batch is full
    if (appBulk.length >= cfg.batchSize) {
      await flushBulk();
    }

    // Progress log
    if (stats.examined % PROGRESS_LOG_INTERVAL === 0) {
      const elapsed = ((Date.now() - stats.startedAt) / 1000).toFixed(1);
      const rate    = (stats.examined / parseFloat(elapsed)).toFixed(1);
      debug(
        `Progress: examined=${stats.examined}/${total} updated=${stats.updated} ` +
        `skipped=${stats.skipped} errors=${stats.errors} ` +
        `elapsed=${elapsed}s rate=${rate} apps/s`
      );
    }
  }

  // Final flush
  await flushBulk();

  const elapsed = ((Date.now() - stats.startedAt) / 1000).toFixed(1);
  debug('─'.repeat(60));
  debug('Pass 1 complete.');
  debug(`  Examined : ${stats.examined}`);
  debug(`  Updated  : ${stats.updated}`);
  debug(`  Skipped  : ${stats.skipped}  (no files, no detail data, or no startDateTime)`);
  debug(`  Errors   : ${stats.errors}`);
  debug(`  Duration : ${elapsed}s`);
  if (!cfg.dryRun && stats.updated > 0) {
    if (!cfg.skipAggregates) {
      debug('  Fields set (aggregates): Application.avgSpraySpeed, totalSprLength, totalFlightLength, avgXtError, avgHdop');
      debug('                           AppFile.totalSprLength, AppFile.totalFlightLength');
    }
    if (!cfg.skipDatetime) {
      debug('  Fields set (datetime):   Application.utcOffset, startDateTimeUTC, endDateTimeUTC');
    }
  }
  if (cfg.dryRun) debug('  (DRY RUN — no writes performed)');
  debug('─'.repeat(60));

  // ── Pass 2: backfill flowAccuracyPct (Application-level only, skipped with --skip-aggregates) ──
  if (!cfg.skipAggregates) {
    await backfillFlowAccuracy();
  }

  // ── Post-run diagnostic: report apps that can never have datetime backfilled ──
  if (!cfg.skipDatetime) {
    await reportMissingLegacyDateApps(cfg.missingLimit);
  }

  return stats;
}

// ─── Entry point ──────────────────────────────────────────────────────────────
process
  .on('uncaughtException',  err => { debug('Uncaught:', err); process.exit(1); })
  .on('unhandledRejection', err => { debug('Unhandled rejection:', err); process.exit(1); });

async function main() {
  const dbConn = new DBConnection('migrate-applications');
  try {
    await dbConn.initialize({ setupExitHandlers: false });
    debug('DB connected');
    await migrate();
  } catch (err) {
    debug('Fatal error:', err);
  } finally {
    await dbConn.close();
    process.exit(0);
  }
}

main();