// Berechnet die Straßenentfernung (km) zwischen der fixen Sternwarte Welzheim
// und dem Wohnort jedes BEOs und schreibt das Ergebnis in die Spalte beos.km.
//
// Der DB-Zugriff läuft über den PHP-Proxy (DB4js_all.php), da die echte
// Sternwarte-Datenbank nicht direkt erreichbar ist:
//   Lesen:    cmd LB_GET_BEO_ADRESSEN
//   Schreiben: cmd LB_UPDATE_BEO_KM { id, km }
//
// Geocoding: OpenStreetMap Nominatim  |  Routing: öffentlicher OSRM-Server.
// Beide kostenlos, ohne API-Key. Ausführen mit:
//   node --env-file=.env berechne-km.mjs
//
// Optionen:
//   --dry-run   nichts in die DB schreiben, nur berechnen und anzeigen

import { readFile, writeFile } from 'node:fs/promises';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';

const __dirname = dirname(fileURLToPath(import.meta.url));
const CACHE_FILE = join(__dirname, 'geocode-cache.json');
const USER_AGENT = 'sternwarte-welzheim-km-berechnen/1.0 (rxf@gmx.de)';
const DRY_RUN = process.argv.includes('--dry-run');

const PHP_DB_URL = process.env.PHP_DB_URL || 'http://localhost:8080/DB4js_all.php';

// Fallback-Referenzpunkt: Ortsmitte Welzheim (nur Näherung, per .env überschreibbar)
const DEFAULT_STERNWARTE = { lat: 48.8756, lon: 9.6377 };

const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

// ---- PHP-Proxy (DB4js) ----

async function callProxy(cmd, params = {}) {
  const headers = { 'Content-Type': 'application/json' };
  if (process.env.API_USER && process.env.API_PASS) {
    const token = Buffer.from(`${process.env.API_USER}:${process.env.API_PASS}`).toString('base64');
    headers.Authorization = `Basic ${token}`;
  }
  const res = await fetch(PHP_DB_URL, {
    method: 'POST',
    headers,
    body: JSON.stringify({ cmd, ...params }),
  });
  const text = await res.text();
  let json;
  try {
    json = JSON.parse(text);
  } catch {
    throw new Error(`DB4js ${cmd} HTTP ${res.status}: ${text.slice(0, 200)}`);
  }
  if (json && typeof json === 'object' && !Array.isArray(json) && 'error' in json) {
    throw new Error(`DB4js ${cmd}: ${json.error}`);
  }
  if (!res.ok) throw new Error(`DB4js ${cmd} HTTP ${res.status}`);
  return json;
}

// ---- Geocode-Cache (Adresse -> {lat, lon} | null) ----

async function loadCache() {
  try {
    return JSON.parse(await readFile(CACHE_FILE, 'utf8'));
  } catch {
    return {};
  }
}

async function saveCache(cache) {
  await writeFile(CACHE_FILE, JSON.stringify(cache, null, 2), 'utf8');
}

// ---- Nominatim Geocoding (max. 1 Anfrage/Sekunde) ----

async function geocode(query, cache) {
  if (Object.prototype.hasOwnProperty.call(cache, query)) {
    return cache[query]; // Treffer (auch gecachte Fehlschläge = null)
  }
  const url =
    'https://nominatim.openstreetmap.org/search?format=json&limit=1&q=' +
    encodeURIComponent(query);
  const res = await fetch(url, { headers: { 'User-Agent': USER_AGENT } });
  if (!res.ok) throw new Error(`Nominatim HTTP ${res.status}`);
  const json = await res.json();
  const hit = Array.isArray(json) && json[0]
    ? { lat: parseFloat(json[0].lat), lon: parseFloat(json[0].lon) }
    : null;
  cache[query] = hit;
  await saveCache(cache);
  await sleep(1100); // Rate-Limit einhalten
  return hit;
}

// ---- OSRM Straßenrouting -> Distanz in km ----

async function roadKm(from, to) {
  const url =
    `https://router.project-osrm.org/route/v1/driving/` +
    `${from.lon},${from.lat};${to.lon},${to.lat}?overview=false`;
  const res = await fetch(url, { headers: { 'User-Agent': USER_AGENT } });
  if (!res.ok) throw new Error(`OSRM HTTP ${res.status}`);
  const json = await res.json();
  if (json.code !== 'Ok' || !json.routes?.[0]) return null;
  return Math.round((json.routes[0].distance / 1000) * 10) / 10;
}

function buildAddress(row) {
  const parts = [row.Adresse, [row.Plz, row.Ort].filter(Boolean).join(' ').trim()]
    .map((s) => (s || '').trim())
    .filter(Boolean);
  return parts.length ? parts.join(', ') + ', Deutschland' : '';
}

async function resolveSternwarte(cache) {
  const envLat = parseFloat(process.env.STERNWARTE_LAT ?? '');
  const envLon = parseFloat(process.env.STERNWARTE_LON ?? '');
  if (Number.isFinite(envLat) && Number.isFinite(envLon)) {
    return { lat: envLat, lon: envLon, source: '.env' };
  }
  const hit = await geocode('Sternwarte, Welzheim, Deutschland', cache);
  if (hit) return { ...hit, source: 'Nominatim' };
  return { ...DEFAULT_STERNWARTE, source: 'Fallback (Ortsmitte Welzheim)' };
}

async function main() {
  const cache = await loadCache();

  const ref = await resolveSternwarte(cache);
  console.log(`Proxy: ${PHP_DB_URL}`);
  console.log(
    `Referenzpunkt Sternwarte Welzheim: ${ref.lat}, ${ref.lon}  [${ref.source}]\n`
  );

  const rows = await callProxy('LB_GET_BEO_ADRESSEN');

  const done = [];
  const skipped = [];

  for (const row of rows) {
    const address = buildAddress(row);
    if (!address) {
      skipped.push({ row, reason: 'keine Adresse' });
      if (!DRY_RUN) await callProxy('LB_UPDATE_BEO_KM', { id: row.ID, km: null });
      continue;
    }

    let km = null;
    let reason = '';
    try {
      const geo = await geocode(address, cache);
      if (!geo) {
        reason = 'Geocoding ohne Treffer';
      } else {
        km = await roadKm(ref, geo);
        if (km == null) reason = 'keine Route gefunden';
      }
    } catch (err) {
      reason = err.message;
    }

    if (!DRY_RUN) await callProxy('LB_UPDATE_BEO_KM', { id: row.ID, km });

    if (km == null) skipped.push({ row, reason });
    else done.push({ row, km });
  }

  // ---- Zusammenfassung ----
  console.log('Berechnete Entfernungen:');
  console.log('Kürzel  Name                      Ort                        km');
  console.log('─'.repeat(66));
  done
    .sort((a, b) => a.km - b.km)
    .forEach(({ row, km }) => {
      console.log(
        (row.Kuerzel || '').padEnd(7) +
          ' ' +
          (row.Name || '').padEnd(24) +
          ' ' +
          (row.Ort || '').padEnd(24) +
          ' ' +
          String(km).padStart(6)
      );
    });

  if (skipped.length) {
    console.log('\nÜbersprungen / ohne Ergebnis (km = NULL):');
    skipped.forEach(({ row, reason }) =>
      console.log(`  ${(row.Kuerzel || '?').padEnd(7)} ${(row.Name || '').padEnd(24)} — ${reason}`)
    );
  }

  console.log(
    `\nFertig. ${done.length} berechnet, ${skipped.length} übersprungen.` +
      (DRY_RUN ? ' (DRY-RUN: nichts in die DB geschrieben)' : ' In Spalte beos.km gespeichert.')
  );
}

main().catch((err) => {
  console.error('Fehler:', err.message);
  process.exit(1);
});