// The observed-movements database: its own SQLite file (RW_ADSB_DB), so the web app's database stays small. Written
// by the nightly ingest, read by the schedule service. Holds only derived movements (who took off or landed where
// and when), never adsb.lol's raw positions.
import Database from "better-sqlite3";
import { existsSync, mkdirSync } from "node:fs";
import path from "node:path";
import { distanceKm } from "./geo.mjs";

function envNumber(name, fallback) {
  const value = Number(process.env[name]);
  return Number.isFinite(value) && value > 0 ? value : fallback;
}

function dataDir() {
  return process.env.RW_DATA_DIR || path.resolve(process.cwd(), "data");
}

export function adsbSettings() {
  return {
    db: process.env.RW_ADSB_DB || path.join(dataDir(), "adsb.db"),
    work: process.env.RW_ADSB_WORK || path.join(dataDir(), "adsb-work"),
    retentionDays: envNumber("RW_ADSB_RETENTION_DAYS", 35),
    catchUpDays: envNumber("RW_ADSB_CATCHUP_DAYS", 8),
    waitHours: Number.isFinite(Number(process.env.RW_ADSB_WAIT_HOURS)) ? Number(process.env.RW_ADSB_WAIT_HOURS) : 3,
    coverageMin: envNumber("RW_ADSB_COVERAGE_MIN", 10),
  };
}

const MIGRATIONS = [
  `
  -- One row per observed takeoff (D) or landing (A). 'day' is the UTC day of adsb.lol's file it came from (and the
  -- day utc falls on). 'other' is the airport at the far end and 'via' says how it is known: O both ends observed,
  -- X observed across midnight (the other end is in the neighbouring day's file), R route data, '' unknown.
  CREATE TABLE movements(
    airport TEXT NOT NULL,
    day TEXT NOT NULL,
    utc INTEGER NOT NULL,
    kind TEXT NOT NULL CHECK(kind IN ('A','D')),
    hex TEXT NOT NULL,
    callsign TEXT NOT NULL,
    airline TEXT NOT NULL,
    registration TEXT NOT NULL,
    type TEXT NOT NULL,
    other TEXT NOT NULL,
    via TEXT NOT NULL,
    PRIMARY KEY(airport, day, utc, kind, hex)
  ) WITHOUT ROWID;
  CREATE TABLE ingested(
    day TEXT PRIMARY KEY,
    tag TEXT NOT NULL,
    started_at TEXT NOT NULL,
    finished_at TEXT NOT NULL,
    seconds REAL NOT NULL,
    movements INTEGER NOT NULL,
    airports INTEGER NOT NULL,
    stats TEXT NOT NULL
  );
  -- The coverage endpoints read only this summary, written with each day.
  CREATE TABLE airport_days(
    airport TEXT NOT NULL,
    day TEXT NOT NULL,
    arrivals INTEGER NOT NULL,
    departures INTEGER NOT NULL,
    with_type INTEGER NOT NULL,
    with_reg INTEGER NOT NULL,
    with_other INTEGER NOT NULL,
    PRIMARY KEY(airport, day)
  ) WITHOUT ROWID;
  CREATE INDEX airport_days_day ON airport_days(day);
  -- Reference data as of the latest ingest, so serving needs nothing but this file.
  CREATE TABLE airports(
    icao TEXT PRIMARY KEY,
    iata TEXT NOT NULL,
    name TEXT NOT NULL,
    city TEXT NOT NULL,
    country TEXT NOT NULL,
    lat REAL NOT NULL,
    lon REAL NOT NULL,
    elev INTEGER NOT NULL,
    kind TEXT NOT NULL,
    tz TEXT NOT NULL
  ) WITHOUT ROWID;
  CREATE TABLE airlines(
    icao TEXT PRIMARY KEY,
    iata TEXT NOT NULL,
    name TEXT NOT NULL
  ) WITHOUT ROWID;
  `,
];

function migrate(db) {
  const at = db.pragma("user_version", { simple: true });
  for (let i = at; i < MIGRATIONS.length; i++) {
    db.transaction(() => {
      db.exec(MIGRATIONS[i]);
      db.pragma(`user_version = ${i + 1}`);
    })();
  }
}

// For the ingest: creates the file when needed.
export function openForWriting(file = adsbSettings().db) {
  mkdirSync(path.dirname(file), { recursive: true });
  const db = new Database(file);
  db.pragma("journal_mode = WAL");
  db.pragma("busy_timeout = 10000");
  db.pragma("synchronous = NORMAL");
  migrate(db);
  return db;
}

// For serving: null until the first ingest has created the file. One connection per process and file; WAL lets it
// read while the ingest writes.
const readers = new Map();
export function openForReading(file = adsbSettings().db) {
  if (readers.has(file)) return readers.get(file);
  if (!existsSync(/*turbopackIgnore: true*/ file)) return null;
  const db = new Database(file, { fileMustExist: true });
  db.pragma("busy_timeout = 5000");
  if (db.pragma("user_version", { simple: true }) < MIGRATIONS.length) {
    db.close();
    return null;
  }
  readers.set(file, db);
  return db;
}

export function closeReaders() {
  for (const db of readers.values()) db.close();
  readers.clear();
}

// Per airport: arrivals, departures and how complete the records are.
export function summarise(movements) {
  const by = new Map();
  for (const m of movements) {
    let s = by.get(m.airport);
    if (!s) by.set(m.airport, (s = { arrivals: 0, departures: 0, with_type: 0, with_reg: 0, with_other: 0 }));
    if (m.kind === "A") s.arrivals++;
    else s.departures++;
    if (m.type) s.with_type++;
    if (m.registration) s.with_reg++;
    if (m.other) s.with_other++;
  }
  return by;
}

// One day, replacing whatever was stored for it before, in one transaction: readers see the old day or the new one.
export function writeDay(db, { day, tag, startedAt, seconds, stats, movements, airports, airlines }) {
  const summary = summarise(movements);
  db.transaction(() => {
    db.prepare("DELETE FROM movements WHERE day = ?").run(day);
    db.prepare("DELETE FROM airport_days WHERE day = ?").run(day);
    const insert = db.prepare(
      `INSERT OR IGNORE INTO movements(airport, day, utc, kind, hex, callsign, airline, registration, type, other, via)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    );
    for (const m of movements) insert.run(m.airport, day, m.utc, m.kind, m.hex, m.callsign, m.airline, m.registration, m.type, m.other, m.via);
    const sum = db.prepare(
      "INSERT INTO airport_days(airport, day, arrivals, departures, with_type, with_reg, with_other) VALUES (?, ?, ?, ?, ?, ?, ?)",
    );
    for (const [airport, s] of summary) sum.run(airport, day, s.arrivals, s.departures, s.with_type, s.with_reg, s.with_other);
    db.prepare(
      `INSERT OR REPLACE INTO ingested(day, tag, started_at, finished_at, seconds, movements, airports, stats)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    ).run(day, tag, startedAt, new Date().toISOString().replace(/\.\d{3}Z$/, "Z"), seconds, movements.length, summary.size, JSON.stringify(stats));
    if (airports) {
      db.prepare("DELETE FROM airports").run();
      const a = db.prepare("INSERT INTO airports(icao, iata, name, city, country, lat, lon, elev, kind, tz) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
      for (const x of airports) a.run(x.icao, x.iata, x.name, x.city, x.country, x.lat, x.lon, Math.round(x.elev), x.kind, x.tz || "");
    }
    if (airlines) {
      db.prepare("DELETE FROM airlines").run();
      const l = db.prepare("INSERT INTO airlines(icao, iata, name) VALUES (?, ?, ?)");
      for (const [icao, x] of airlines) l.run(icao, x.iata, x.name);
    }
  })();
  return { airports: summary.size };
}

const dayStart = (day) => Date.parse(`${day}T00:00:00Z`) / 1000;
export const addDays = (day, n) => new Date(Date.parse(`${day}T00:00:00Z`) + n * 86400000).toISOString().slice(0, 10);

// A flight that takes off before midnight UTC lands in the next day's file. When both days are stored, an aircraft
// whose last movement of the first day is a one-ended departure and whose first movement of the next day is a
// one-ended arrival, under the same callsign, within 18 hours and at a speed an airliner can fly, made one flight:
// each end gets the other's airport. Route-data guesses are replaced by it.
export function pairAcrossMidnight(db, firstDay) {
  const second = addDays(firstDay, 1);
  const rows = db.prepare("SELECT airport, utc, kind, hex, callsign, via FROM movements WHERE day = ?");
  const last = new Map();
  for (const m of rows.iterate(firstDay)) if (!last.has(m.hex) || last.get(m.hex).utc < m.utc) last.set(m.hex, m);
  if (!last.size) return 0;
  const first = new Map();
  for (const m of rows.iterate(second)) if (!first.has(m.hex) || first.get(m.hex).utc > m.utc) first.set(m.hex, m);
  const where = db.prepare("SELECT lat, lon FROM airports WHERE icao = ?");
  const update = db.prepare("UPDATE movements SET other = ?, via = 'X' WHERE airport = ? AND day = ? AND utc = ? AND kind = ? AND hex = ?");
  const pairs = [];
  for (const [hex, d] of last) {
    const a = first.get(hex);
    if (!a || d.kind !== "D" || a.kind !== "A" || d.airport === a.airport || d.callsign !== a.callsign) continue;
    if (!["", "R"].includes(d.via) || !["", "R"].includes(a.via)) continue;
    const hours = (a.utc - d.utc) / 3600;
    if (hours <= 0.25 || hours > 18) continue;
    const p = where.get(d.airport);
    const q = where.get(a.airport);
    if (p && q && distanceKm(p.lat, p.lon, q.lat, q.lon) / 1.852 / hours > 750) continue;
    pairs.push([d, a]);
  }
  if (!pairs.length) return 0;
  db.transaction(() => {
    for (const [d, a] of pairs) {
      update.run(a.airport, d.airport, firstDay, d.utc, "D", d.hex);
      update.run(d.airport, a.airport, second, a.utc, "A", a.hex);
    }
    // The summary's "with other" counts change with it.
    const recount = db.prepare(
      "UPDATE airport_days SET with_other = (SELECT COUNT(*) FROM movements m WHERE m.airport = ? AND m.day = ? AND m.other != '') WHERE airport = ? AND day = ?",
    );
    const touched = new Set();
    for (const [d, a] of pairs) {
      touched.add(`${d.airport}|${firstDay}`);
      touched.add(`${a.airport}|${second}`);
    }
    for (const k of touched) {
      const [airport, day] = k.split("|");
      recount.run(airport, day, airport, day);
    }
  })();
  return pairs.length;
}

// Days older than the retention are deleted; the space is reused by the next days.
export function purgeOld(db, today, retentionDays = adsbSettings().retentionDays) {
  const cutoff = addDays(today, -retentionDays);
  const days = db.prepare("SELECT day FROM ingested WHERE day < ?").all(cutoff).map((r) => r.day);
  db.transaction(() => {
    db.prepare("DELETE FROM movements WHERE day < ?").run(cutoff);
    db.prepare("DELETE FROM airport_days WHERE day < ?").run(cutoff);
    db.prepare("DELETE FROM ingested WHERE day < ?").run(cutoff);
  })();
  return days;
}

export function ingestedDays(db) {
  return db.prepare("SELECT day, tag, started_at, finished_at, seconds, movements, airports, stats FROM ingested ORDER BY day DESC").all();
}